[EXCEL] 交集，聯集，差集

　
　　兩組資料，如何求它們的交集，聯集，和差集？（什麼叫差集？維基百科有詳細說明。）

　　如上圖，有兩組資料，集合Ａ是九月請假名單，集合Ｂ是十月請假名單，

• 它們的交集，就是「兩個月都請假的名單」，如Ｃ欄；
• Ａ對Ｂ的差集，就是「只有九月請假，十月沒有請假的人」，如Ｄ欄；
• Ｂ對Ａ的差集，就是「只有十月請假，九月沒有請假的人」，如Ｅ欄。

　　怎麼算出來的？且聽我慢慢道來。

##CONTINUE##

　　之前我有一篇文章，叫做「排除重複的資料」，它主要是在過濾同一組資料，讓重複的資料只列出一次。這次的公式原理和它大同小異，但是會使用網友夏日大大修改過的進階版本，說明如下。

　　求交集的公式是 C3

　　=INDEX($A:$A,SMALL(IF(COUNTIF($B$3:$B$6,$A$3:$A$11)>0,ROW($A$3:$A$11),65536),ROW(A1)))&""

　　這是一個陣列公式，請用 CTRL+SHIFT+ENTER 完成輸入。公式可往下複製。出現 #NUM! 時，表示已經超出合理範圍；出現空白時，表示名單已經列完了。

　　這個公式的原理，是以Ａ集合為主，利用 COUNTIF($B$3:$B$6,$A$3:$A$11)>0 找出Ａ欄的資料是否在 $B$3:$B$6 的範圍中出現過，如果出現過，表示兩邊都出現過，是交集的一部份，則傳回Ａ欄資料的列數。反之，則傳回 65536，這是最大的列數，其內容通常沒有值。

　　A 欄資料的列數會形成陣列，再用 SMALL() 做排序，INDEX() 把列數轉為資料，就可以得到 C 欄的名單了。

　　如果交集的名單已經列完了，接下去會列出第 65536 列的資料，Excel 會自動把它轉成 0。因為出現 0 有點奇怪，為了把 0 變成空白，所以在公式最後加上 &""。

　　交集看懂了，差集應該就沒問題了。D3 是利用 COUNTIF()=0，以Ａ集合為主，找出在 $B$3:$B$6 的範圍中沒有出現過的資料，就是Ａ集合有，Ｂ集合沒有的差集了。

　　=INDEX($A:$A,SMALL(IF(COUNTIF($B$3:$B$6,$A$3:$A$11)=0,ROW($A$3:$A$11),65536),ROW(A1)))&""

　　E3 則是把Ａ集合和Ｂ集合對調，以Ｂ集合為主，找出在 $A$3:$A$11 的範圍中沒有出現過的資料，就是Ｂ集合有，Ａ集合沒有的差集了。

　　=INDEX($B:$B,SMALL(IF(COUNTIF($A$3:$A$11,$B$3:$B$6)=0,ROW($B$3:$B$6),65536),ROW(A1)))&""

　　至於聯集呢？聯集就是集合和差集的總和，也就是Ａ欄加Ｅ欄，或是Ｂ欄加Ｄ欄的總合。