Excelで配列数式を使って差集合の要素数を取得する方法

問題定義・・・差集合をいかにして求めるか

以下の二つの表があるとする。
[表1]  [表2]
 A1    A1
 A2    A3
 A3    A4
 B1    C1
 B2
 C1

Excelで、表1にあって表2にないデータの件数をA, B, Cごとに取得することを考える。図1のベン図だと、黄色の要素を割り出してA, B, Cごとの件数を求める。期待結果は、Aは1件、Bは2件、Cは0件だ。 これは、表1と表2の差集合をいかにして求めるか・・・という問題になっている。

図1:ベン図

「Aは1件」という結果を得るための数式の一つは以下だ。(詳細は図4以降で説明する) ={ SUM(IF(LEFT(表1,1)=C2,1,0)) – SUM((表1=TRANSPOSE(表2))*(LEFT(表1,1)=C2)) }

配列数式

この式の意味を理解するには、配列数式を知っている必要があるので、ここで簡単におさらいする。 例えば、スーパーでりんごを3個、梨を2個、アボカドを1個買った場合の合計金額の算出方法としては、いったん各商品ごとの小計を求めて、次に小計を合計するというのが、最もオーソドックスだと思う。(図2)

図2:オーソドックスな合計金額の求め方

配列数式を使えば小計の計算は不要であり、ダイレクトに合計金額を求めることができる(図3)。配列数式にするには、数式を入力した後、Ctrl+Shift+Enterすることだ。普通にEnterしても配列数式にならない。図3の赤枠内のように、波括弧{}で数式が挟まれていれば、配列数式になっている。直接手入力で波括弧を打っても配列数式にはならないので要注意。

図3:配列数式を使った合計金額の求め方

数式を解剖する

それでは元の話に戻って、差集合の要素数を求める数式の説明を試みる。Excelの様子は図4だ。D列に配列数式が設定されており、期待通りの結果が得られている。

図4:配列数式を使って差集合の要素数を求める

図5のように配列数式の内部を色分けして、各部分を個別に見ていく。なお、数式中の「表1」はA2:A7、「表2」はA10:A13のように、セル範囲を指すよう名前付き定義されている。

図5:配列数式の色分け

黄色から緑色を引き算している。大雑把に説明すると、 黄色が表1の集合、緑色は表1と表2の共通部分となっている。図1のベン図で言えば、表1(黄色+赤色)から共通部分(赤色)を差し引いて、黄色のみ取り出そうとしているわけだ。

●黄色
数式の黄色部分を理解するのは、そんなに難しくないと思う。表1のセル範囲にて、1文字目が A の場合のみカウントして集計している。図4ならA1, A2, A3がヒットするので、黄色の値は3だ。

●茶色
一番の難関がこの茶色部分の理解だ。
まず、TRANSPOSEは行列を転置すると思えばよい。TRANSPOSE(表2)は {A1; A3; A4; C1} を {A1, A3, A4, C1} に変換している。これらは配列定数と呼ばれ、配列を数式内で表現したものだ。セミコロンは行区切り、カンマは列区切りを示している。

さて「表1= TRANSPOSE(表2) 」は何をしているかというと、表1の各要素に対して、 転置された表2のいずれかの要素に一致しているか?・・・という条件式になっており、一致していれば論理値のTrueを返しているのだ。このようにカンマ区切りの配列定数を使えば OR条件 となる。TRANSPOSEを使うのはカンマ区切りの配列定数にしてOR条件を生成するためだ。

例えば、表1の最初の要素 A1 の場合、{A1, A3, A4, C1} に含まれているので True となる。

●青色
青色はそんなに難しくなくて、表1の各要素の1文字目が A の場合は論理値のTrueを返している。
例えば、表1の最初の要素 A1 の場合、1文字目が A なので True となる。

茶色*青色
論理値の演算。以下のようになる。
 True * True = 1
 True * False = 0
 False * True = 0
 False * False = 0

例えば表1の最初の要素 “A1” の場合、茶色はTrueで青色もTrueなので、茶色*青色は 1 となる。同じ考え方で表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関数ももちろん使える。他にも差集合を求める簡単な方法があるかもしれない。

参考サイト

配列数式については、以下が非常に詳しい。
[http://mt-soft.sakura.ne.jp/kyozai/excel_high/230_special/02_hairetu_susiki.htm][1]

配列定数の詳細はこちら。
[http://mt-soft.sakura.ne.jp/kyozai/excel_high/230_special/02_hairetu_kiso.htm?kiso_01][2]

**配列数式の簡単動画説明**

関連ページ