[[20160927000825]]　『sumproductとcountifの挙動がどうしても分かりまａx（ｔ）　ページの最後に飛ぶ

『sumproductとcountifの挙動がどうしても分かりません』（ｔ）

  A  B  C  D  E  F  G  H  I  J
1 a  b  a  c     c        a  1
2                         b  3
3                         c  6

A1:F1の文字をI1:J3の表のように数字に変換して合計したいと思い、
いろいろ検索しましたところ、

=sumproduct(countif(A1:F1,I1:I3)*J1:J3)

という関数で結果を求めることができました。

ですが、この関数がどういう動きをしてこの結果が出るのか、まったく分からないのです。

countif(A1:F1,I1:I3)だけでは答えが０になりますし・・・
A1:AZ1とかI1:J100など範囲が大きくなった時に正しく動作するのかも不安です。
（30列、30行程度で検証はしてみましたが）

このsumproductとcountifの動きについて、どうぞご教授ください。
よろしくお願いします。

＜使用 Excel：Excel2013、使用 OS：Windows10 ＞

 ご提示の数式は「本来」配列数式ですが(つまり、Ctrl+Shift+Enterで入力しなければならない）、
 Sumproduct関数は(多くの場合）、普通Enter入力でも、引数を配列として取り扱ってくれます

 すると、
 countif(A1:F1,I1:I3)は　→　{2;1;2}　の配列を返し、
 J1:J3は　　　　単純に　 →　{1;3;6}  の配列なので、
 その積は                →  {2;3;12} となって、合計「17」が算出されます。

 ＞countif(A1:F1,I1:I3)だけでは答えが０になりますし
 これは、入力するセルの位置(1行目とか5行目とか）で、違った値が返ります。

 ＞A1:AZ1とかI1:J100など範囲が大きくなった時に正しく動作するのかも不安です。 
 何も問題ないですよ。どれくらい大きな範囲にするのか分かりませんけど。

（半平太） 2016/09/27(火) 07:33

M1セルに次式を入力してください。
(使用中の場合は、別のセルに入力してください）

=COUNTIF($A$1:$F$1,I1)*J1

↑の式をM3までコピペします。

M1:M3を合計する数式を入力します。
=SUM(M1:M3)

↑の計算を一つの数式でまとめたものが次式になります。

=SUMPRODUCT(COUNTIF(A1:F1,I1:I3)*J1:J3)

'■■
以下は、データ数を多くした場合の検証用です。
新規シートに入力してください。

A1:AZ1
=CHAR(RANDBETWEEN(97,122))

アルファベット(小文字）がランダムに配置されます。

数式の結果を【値】に変換してください。

BA1:BA26
=CHAR(ROW()+96)

26行目まで、アルファベットが配置されます。

BB1:BB26
=1

BC1:BC26
=COUNTIF($A$1:$AZ$1,BA1)*BB1

各アルファベットの個数が表示されます。

BD1(式1)
=SUM(BC:BC)
この数式の戻り値は52になります。
(a:az)の列数=52

BE1(式2)
=SUMPRODUCT((COUNTIF(A1:AZ1,BA1:BA26)*BB1:BB26))
この式の戻り値も52になります。

BB1:BB26の任意のセルを1以外の値に変更します。

式1と式2は、同じ結果を返します。

列数や行数をもっと大きくしても計算できます。
BA1:BA26
BB1:BB26

↑の箇所の行数が一致していない場合はエラーになります。

（x-ab） 2016/09/27(火) 07:43

コメント遅くなって申し訳ありません。

恥ずかしながら、
配列という概念がまったく分かっていなかったようです。

countifのなかで何が起こっているのか、
sumproductが（）の中のものをどう捉えているのか、
とてもよくわかりました。

｛｝の使い方も一から勉強してみたいと思います。

半平太様、x-ab様、本当にありがとうございました。

（ｔ） 2016/09/28(水) 01:04

コメント返信：