種類数をカウントする =SUMPRODUCT(1/COUNTIF(データ範囲,データ範囲)) は =SUM(1/COUNTIF(データ範囲,データ範囲)) ではだめなのでしょうか

こんにちは。

＞なぜSUMPRODUCTを使うのでしょうか？

これについてですが・・・、

おそらく、最新版の Excel（ Microsoft365 の Excel、Excel2021 など）をお使いなのではありませんか？。

誤解を恐れずに簡単に言えば、旧来の Excel では、数式中の関数の引数が配列になるような場合で、且つ、最終的な計算結果が一つだけの場合に限り、配列数式にすることで正しい答えを得ることが出来ていました。

SUMPRODUCT 関数は、そういった場合に配列数式にせずとも正しい答えを得られる関数として使えました。

例えば、旧来の Excel では、

=SUM((A1:A5<>"")*1)

という数式は配列数式にしないと正しい答えは得られませんが、

=SUMPRODUCT((A1:A5<>"")*1)

というような数式にすることで、配列数式にせずとも正しい答えを得ることが出来ました。

最新版の Excel の場合、「スピル」が組み込まれていますので配列数式にせずとも正しい答えを得られる数式を書くことが出来ます。

上記の例で言えば、

=SUM((A1:A5<>"")*1)

という数式は配列数式にしなくても良くなりましたし、

=SUMPRODUCT((A1:A5<>"")*1)

という数式も問題なく使えます。

つまり、旧来の Excel では配列数式にしないと使えない数式も、最新版の Excel であれば、そのまま普通に使えるということです。

もっと言えば、Excel のバージョンによって数式の扱い方が異なってくる、ということです。

もちろん、どちらのバージョンの Excel であっても、SUMPRODUCT 関数を使わなければならない場合もあるでしょうし、SUMPRODUCT 関数で置き換えられない場合もあるかと思いますが・・・。

これで、なぜ SUMPRODUCT 関数を使うのか、といった疑問の答えになっていますでしょうか？。

次に、

＞SUMPRODUCT関数のはたらきである「積を合計する」という動作は行われていない

という点についてですが・・・、以下、推測に基づく私見であることを最初にお断りしておきます。

=SUMPRODUCT(1/COUNTIF(データ範囲,データ範囲))

これに実際のセル範囲を当てはめると、

=SUMPRODUCT(1/COUNTIF(A1:B5,A1:B5))

というような数式になるかと思います。

この中で、

1/COUNTIF(A1:B5,A1:B5)

この部分は、提示されている表を参照すると仮定すると、{0.5,0.5;0.333333333333333,0.5;0.333333333333333,1;0.5,0.333333333333333;0.5,0.5}

という配列を返します。

これは、列 2、行 5、の 2次元配列ですが、全体としてただ一つの配列になっています。

SUMPRODUCT 関数では、引数として {配列}*{配列} や {配列},{配列} といった書式がありますが、{配列} がただ一つしかない場合にはどのように扱われるか改めて考えてみました。

これまでの経験からして、おそらくですが、{配列}*1 というような計算を行っているのではないかと考えています。

つまり、

{0.5,0.5;0.333333333333333,0.5;0.333333333333333,1;0.5,0.333333333333333;0.5,0.5}*{1,1;1,1;1,1;1,1;1,1}

です。

これならば、

=SUMPRODUCT(1/COUNTIF(A1:B5,A1:B5))

という数式が正常に計算されるのも納得できるかと思いますが、いかがでしょう？。

最初のほうで書いている、

=SUMPRODUCT((A1:A5<>"")*1)

という数式も、提示されている表を参照すると仮定すると、

=SUMPRODUCT({TRUE;TRUE;TRUE;TRUE;TRUE}*1)

となり、更に展開すると、

=SUMPRODUCT({1;1;1;1;1})

となります。

これも引数はただ一つの配列ですので、

=SUMPRODUCT({1;1;1;1;1}*1)

というようになると推測し、更に展開して、

=SUMPRODUCT({1;1;1;1;1}*{1;1;1;1;1})

というような計算を行っていると考えれば良いのではないかと思っています。

あくまで考え方の一例です。

※Excel の内部動作については推測するしかありませんので、上記で書いていることは結果から導き出した推測（私見）に過ぎませんが、SUMPRODUCT 関数の引数がただ一つの配列であった場合の考え方の一つとして捉えていただければと思います。

何らかの公式な文書を参照して書いているのではないことも改めてお断りしておきます。

・Windows11 と Excel2021

・Windows10 と Excel2016

この組み合わせで動作確認しています。

ご参考になれば幸いです。