Excelで配列数式を使って差集合の要素数を取得する方法
目次
2023/05/21 更新
問題定義・・・差集合をいかにして求めるか
以下の二つの表があるとする。
［表１］ ［表２］
Ａ１ Ａ１
Ａ２ Ａ３
Ａ３ Ａ４
Ｂ１ Ｃ１
Ｂ２
Ｃ１
Excelで、表１にあって表２にないデータの件数をA, B, Cごとに取得することを考える。 図1のベン図だと、黄色の要素を割り出してA, B, Cごとの件数を求める。 期待結果は、Aは１件、Ｂは２件、Ｃは０件だ。 これは、表１と表２の差集合をいかにして求めるか・・・という問題になっている。
「Aは１件」という結果を得るための数式の一つは以下だ。=SUM(IF(LEFT(表１,1)=C2,1,0))-SUM(IF(COUNTIF(表２,表１),TRUE,FALSE)*(LEFT(表１,1)=C2))
数式を分解して確認する
数式を分解して何をしているか確認する。 図を見ただけで理解できれば、以下の説明はくどく感じるかもしれないので、読まなくてもよい。
図の数式の構成は ① - SUM( ② * ③ ) となっている。 大雑把に説明すると、① は表１の先頭1桁目=A の個数、 SUM( ② * ③ ) は表１と表２の共通部分における先頭1桁目=A の個数となっている。 図１のベン図で言えば、先頭1桁目=A の要素について、 表１（黄色＋赤色）から共通部分（赤色）を差し引いて、黄色のみを取り出そうとしているわけだ。
図の①は、表１のセル範囲で先頭1桁目=A の場合のみカウントして集計している。 上図なら A1, A2, A3 がヒットするので、①の値は３だ。 なお、数式中の「表１」はA2:A7、「表２」はA10:A13のように、セル範囲を指すよう名前付き定義されている。
図の②は、表１と表２の共通部分を表現する数式となっている。
COUNTIF の引数は (セル範囲, 条件)の形式であり、第１引数のセル範囲に対して第２引数の条件にマッチする個数を戻す。それが COUNTIF の外側にある IF の第１引数となり、COUNTIFの戻り値が 0 以外なら TRUE , そうでなければ FALSE となる。 ②は結局、図の矢印の先のようなテーブルを返す。
図の③は、表１の先頭1桁目が A であるか否かをチェックし、boolean型で返す。結果として ③ の矢印の先のようなテーブルを返す。
② * ③ はboolean型の演算となり、以下に基づき計算され、図の④ となる。
TRUE * TRUE = 1
TRUE * FALSE = 0
FALSE * TRUE = 0
FALSE * FALSE = 0
SUM( ② * ③ ) = SUM( ④ ) = 2 となる。 結局、① - SUM( ② * ③ ) = 1 となり、期待結果となる。
上記説明は A (=C2セル) についてのものだったが、 B (=C3セル)、C (=C4セル) についても同様に期待結果に導ける。
さて、ここから下は過去に記した配列定数を使う方法だが、備忘録として残しておく。（上の方法が簡単と思われる）
配列数式を使う場合は、以下数式により「Aは1件」という結果を導ける。（詳細は図４以降で説明する）={ SUM(IF(LEFT(表１,1)=C2,1,0)) - SUM((表１=TRANSPOSE(表２))*(LEFT(表１,1)=C2)) }
配列数式
この式の意味を理解するには、配列数式を知っている必要があるので、ここで簡単におさらいする。 例えば、スーパーでりんごを３個、梨を２個、アボカドを１個買った場合の合計金額の算出方法としては、いったん各商品ごとの小計を求めて、次に小計を合計するというのが、最もオーソドックスだと思う。（図２）
配列数式を使えば小計の計算は不要であり、ダイレクトに合計金額を求めることができる（図３）。配列数式にするには、数式を入力した後、Ctrl+Shift+Enterすることだ。普通にEnterしても配列数式にならない。図３の赤枠内のように、波括弧｛｝で数式が挟まれていれば、配列数式になっている。直接手入力で波括弧を打っても配列数式にはならないので要注意。
数式を解剖する
それでは元の話に戻って、差集合の要素数を求める数式の説明を試みる。Excelの様子は図４だ。D列に配列数式が設定されており、期待通りの結果が得られている。
図５のように配列数式の内部を色分けして、各部分を個別に見ていく。なお、数式中の「表１」はA2:A7、「表２」はA10:A13のように、セル範囲を指すよう名前付き定義されている。
黄色から緑色を引き算している。大雑把に説明すると、 黄色が表１の集合、緑色は表１と表２の共通部分となっている。図１のベン図で言えば、表１（黄色＋赤色）から共通部分（赤色）を差し引いて、黄色のみ取り出そうとしているわけだ。
●黄色
数式の黄色部分を理解するのは、そんなに難しくないと思う。表１のセル範囲にて、１文字目が A の場合のみカウントして集計している。図４ならA1, A2, A3がヒットするので、黄色の値は３だ。
●茶色
一番の難関がこの茶色部分の理解だ。
まず、TRANSPOSEは行列を転置すると思えばよい。TRANSPOSE(表２)は {A1; A3; A4; C1} を {A1, A3, A4, C1} に変換している。これらは配列定数と呼ばれ、配列を数式内で表現したものだ。セミコロンは行区切り、カンマは列区切りを示している。
さて「表１= TRANSPOSE(表２) 」は何をしているかというと、表１の各要素に対して、 転置された表２のいずれかの要素に一致しているか？・・・という条件式になっており、一致していれば論理値のTrueを返しているのだ。このようにカンマ区切りの配列定数を使えば OR条件 となる。TRANSPOSEを使うのはカンマ区切りの配列定数にしてOR条件を生成するためだ。
例えば、表１の最初の要素 A1 の場合、{A1, A3, A4, C1} に含まれているので True となる。
●青色
青色はそんなに難しくなくて、表１の各要素の１文字目が A の場合は論理値のTrueを返している。
例えば、表１の最初の要素 A1 の場合、１文字目が A なので True となる。
●茶色＊青色
論理値の演算。以下のようになる。
True * True = 1
True * False = 0
False * True = 0
False * False = 0
例えば表１の最初の要素 “A1” の場合、茶色はTrueで青色もTrueなので、茶色＊青色は 1 となる。同じ考え方で表１のすべての結果を示すと、
A1 : 茶色True * 青色True = 1
A2 : 茶色False * 青色True = 0
A3 : 茶色True * 青色True = 1
B1 : 茶色False * 青色False = 0
B2 : 茶色False * 青色False = 0
C1 : 茶色True * 青色False = 0
●緑色、黄色－緑色
緑色は、茶色＊青色の合計値（SUM)。
上の例なら、緑色＝ 2 となり、黄色－緑色＝ 1 となる。
ここまで A を例示して見てきたが、B と C も同様に期待結果を導ける。
おわりに
今回は配列数式で考えたが、配列数式が使えるならSUMPRODUCT関数ももちろん使える。他にも差集合を求める簡単な方法があるかもしれない。
参考サイト
配列数式については、以下が非常に詳しい。
配列数式－予備集計表を使わずに一気に集計
配列定数の詳細はこちら。
配列定数、 表引きの例、九九の掛け算表