スポンサーリンク
下図で縦横に配置されたデータにおいて、何種類のデータがあるのか(答え：5種類)というのを関数でカウントするのは、意外に難しい。
ひとまず先に答えだけ書くと、最もポピュラーな解法でさえ
という難解な式になる。
今回は、この式について中身を見ていく。
COUNTIFS関数
今回の数式
=SUMPRODUCT( 1 / COUNTIFS( $A$1:$D$5 , $A$1:$D$5) )
で最も大事なのがCOUNTIFS関数で、これをじっくり見ていく。
COUNTIFS関数の構成は
=COUNTIFS( 範囲01 , 検索条件01 ,範囲02 , 検索条件02 ,・・・)
というものだが、まずはA1セル「東京都」が全体のセル範囲A1:D5の中に幾つあるかカウントする式は
=COUNTIFS( $A$1:$D$5 , A1)
となる。
そして、対象セル範囲A1:D5と同じサイズの縦5行×横4列に、今のCOUNTIFS式をコピペしたものが下の図1。
スピルさせる
では、いったんF1:I5のセル範囲に入力された式は消しておく。
元の式
=COUNTIFS( $A$1:$D$5 , A1)
について、検索条件であるA1は単一セルを指定しているわけだが、これを範囲拡張して
=COUNTIFS( $A$1:$D$5 , $A$1:$D$5)
という原則外れの書き方でF1セルに入力すると、「スピル」が発生して下の図2のようになる。
これは、数式をコピペした図1と同じ状態だ。
スピルした式の逆数
ではこの、スピルしたCOUNTIFS式について、逆数を取ってみよう。つまり、このCOUNTIFS式で1を割り
=1 / COUNTIFS( $A$1:$D$5 , $A$1:$D$5)
という式にするわけだ。
すると結果は下の図3のようになる。
たとえばF1セルの0.142857というのは、スピル前の図2でF1セルに入っていた7の逆数である1/7と合致する。
そして図3のF1～I5セルには、「東京都」に対応するセルが7箇所あって、それらの合計が7箇所×1/7 = 1となる。
同様に、「栃木県」なら5箇所が合致して、F2セルなど該当箇所の値がそれぞれ0.2=1/5 なので、5箇所×1/5=1となる。
したがって、ユニークな値が5種類あるなら、合計は5となる。実際、図3でも選択範囲の合計が5と表示されている。
では、このスピルしたセル範囲、すなわち配列をいっぺんに合計してしまうのがSUMPRODUCT関数だというわけだ。
そこで、冒頭の数式
で、COUNTIFS関数の逆数が全て合計されるということになる。
まあSUMPRODUCT関数は、配列を合計するって機能は確かにあるんだけど、ここまでの使い方をする機会ってまず無い。
普通はSUMPRODUCT関数は、縦横が入り混じった表の合計を出すとか、そういう使い方を押さえておけば良い。
スポンサーリンク
