Excel のいくつかのセルに値が入力されているとき，値が何種類あるかを計算する方法です。
例えば以下の 4 × 3 のセルに果物の名前が入力されています。何種類の果物があるかを求めたいという場合にどうすれば良いでしょうか。ちなみに答えを先に書くと，リンゴ，カキ，モモ，バナナ，ブドウ，パイナップル，マンゴーがあるので「7」になります。
| A | B | C | |
|---|---|---|---|
| 1 | リンゴ | カキ | モモ |
| 2 | モモ | リンゴ | バナナ |
| 3 | バナナ | ブドウ | パイナップル |
| 4 | モモ | マンゴー | リンゴ |
答え: SUMPRODUCT(1/COUNTIF(A1:C4, A1:C4))
要素の個数の逆数を要素の個数回加えると 1 になるので要素の種類が計算できます。
以下解説です。
COUNTIF(RANGE, VALUE) は範囲 RANGE 内に含まれる値 VALUE の数を求めます。例の場合では COUNTIF(A1:C4, A1:C4) は以下のようになります。
| リンゴの個数 = 3 | カキの個数 = 1 | モモの個数 = 3 |
| モモの個数 = 3 | リンゴの個数 = 3 | バナナの個数 = 2 |
| バナナの個数 = 2 | ブドウの個数 = 1 | パイナップルの個数 = 1 |
| モモの個数 = 3 | マンゴーの個数 = 1 | リンゴの個数 = 3 |
1/COUNTIF で逆数をとっています。 SUMPRODUCT はその逆数の合計を計算することになります。各果物ごとに整理すると以下のようになります。
| 果物 | 個数の逆数 | 計 | ||
|---|---|---|---|---|
| 合計 | 7 | |||
| リンゴ | 1/3 | 1/3 | 1/3 | 1 |
| カキ | 1/1 | 1 | ||
| モモ | 1/3 | 1/3 | 1/3 | 1 |
| バナナ | 1/2 | 1/2 | 1 | |
| ブドウ | 1/1 | 1 | ||
| パイナップル | 1/1 | 1 | ||
| マンゴー | 1/1 | 1 | ||
なかなか巧妙にできていますね。