エクセル関数 SUMPRODUCTについて

質問

エクセル関数でSUMPRODUCTについて質問させていただきます。

↓Ａ1
コーラ　 A
ポカリ　 A
珈琲　　 A
石鹸　　 B
洗剤　　 B
ポカリ　 A
食パン　 C
菓子パ　 C
洗剤　 B

　　3←Ｃ11（Aの数を数えています。）

と、入力されているデータにおいては、
=SUMPRODUCT((MATCH($A$1:$A$9&$B$1:$B$9,$A$1:$A$9&$B$1:$B$9,0)=ROW($A$1:$A$9))*($B$1:$B$9="A"))
の関数で求めるデータが出ることはわかりました。
（Ａの数は4個ですが、ポカリがダブっているので3個として数えるように設定したいのです。同じ様にＢの数は洗剤が2つあるので2個として計算します。）

しかし、Ａ1～Ｃ11のデータをすべて切り取りして
例えばＡ11からＣ21に貼り付けた場合はＣ21の計算結果が”0”になってしまいます。
その際のＣ21の関数は
=SUMPRODUCT((MATCH($A$11:$A$19&$B$11:$B$19,$A$11:$A$19&$B$11:$B$19,0)=ROW($A$11:$A$19))*($B$11:$B$19="A"))
になっています。

この場合だと具体的にどのように関数を変化させればいいのでしょうか？
ご指導いただければありがたいです。よろしくお願いします。<m(__)m>

回答No.4

Wendy02 · ベストアンサー

こんばんは。

>実は実際のデータにおいては（Ａ15：Ｂ15）～（Ａ200：Ｂ200）近くまでデータが入っており、しかもそのデータは毎日数が微妙にへんかします。そこで、いっその事関数にでている数字をすべて（Ａ１；Ｂ220）位で固定してしまえればいいなと思ったのです。

=SUMPRODUCT((MATCH($A$11:$A$220&$B$11:$B$220,$A$11:$A$220&$B$11:$B$220,0)=ROW($A$1:$A$210))*($B$11:$B$220="A"))

(#2さんのご指摘は、私からコメントを差し上げておきますが、絶対参照にしている理由は、この数式は、="A",="B" と代わるからで、ひとつの数式で使うわけではなく、オートフィルでドラッグするので、必要だと思いました。）

私としては、範囲が変化しようがしまいが、数百列ぐらでしたら、その範囲を大目にとっておいて困る問題でもないと思うのです。ROW の中は、1から始まるの同じセルの数のインクリメントですから、

$A$11:$A$220　は、
(220-11)+1=210　(+ 1は、初期値）

ROW($A$1:$A$210))

>ＩＮＤＩＲＥＣＴですが実は実際のデータにおいては使えない事情がありまして困っています。

また、別件ですが、"INDIRECT" を使わないというのは、どういう理由かは分かりませんが、しかし、それらの関数に対して、何年も前の問題で、そうした制限を持ち出してくると、きりがないというか、水掛け論になってしまうような気がするのです。確かに、過去から引きずっているExcelの問題はないとは言いません。しかし、大きな問題にもなっていません。

そうした制限とは、Excel自体の本質的な問題に代わってくるのと同時に、技術的なレベルの違いでもあるのですが、回答自体にも制限を加えていくと、実際の解答や解決の意義を失ってくることが多いと思います。ですから、なるべく、Excelの全体的な使用方法に対しては、制限を加えないほうがよいかと思います。もちろん、必要がなければ使う必要はないのですし、そうした問題でお困りなら、別の質問を立てたほうがよいと思います。ここでの回答者の一般的な多くの人たちは、実務上の経験の中で、なんらかの解決策は持っているはずです。

前回の内容で、ほぼすべての回答は出尽くしたはずだと私は思っています。だから、今、前回と同じように、扱いに困難な状況に陥っているのでしたら、基本的な方法に戻って、補助列を使ったほうがよいと思います。そのほうが全体としては安定しているし、分かりやすいと思います。

配列数式の解決というのは、掲示板で好まれるひとつの解法のパターンであって、実務的に、必ずしも正解ではなく、結果が出ればよいのです。その過程の美さを競うのは、本来は、あまり意味がありません。汚く出る補助列は、非表示の列にしてもよいです。

A11 からなら、以下のような数式を、オートフィルタなどで、コピーします。

その同じ行の空いている場所で、

=REPT(B11,COUNTIF($A$11:A11,A11)<2)

IF構文を使えば、
=IF(COUNTIF($A$11:A11,A11)=1,B11,"")

それで、COUNTIF関数で、数を取れば済みます。
以下のようになりますが、

A
A
A
B
B

C
C

仮に、その列が、D列にあるのでしたら、
=COUNTIF($D$11:$D$19,"A")

として取ればよいです。

お礼

Wendy02さん御回答ありがとうございます。<m(__)m>

なにぶんあまりエクセルに詳しくないもので本を片手に色々ためしてみたのですが、なかなか解決できなくて困っていました。^_^;

SUMPRODUCT((MATCH($A$11:$A$220&$B$11:$B$220,$A$11:$A$220&$B$11:$B$220,0)=ROW($A$1:$A$210))*($B$11:$B$220="A"))

なるほどこれで解決できるのですね。
実は一つの関数で収めたかったのには、データ自体が実はＢＪ位までありましたので何とか一つの関数で収まればいいな、と考えたからです。（ただ、今回必要なデータはＢとＣだけなんですが）
しかし、知識がついていってないのにうまくいくわけないですよね^_^;
反省してます。

”$A$11:$A$220　は、
(220-11)+1=210　(+ 1は、初期値）

ROW($A$1:$A$210))”

一つだけ数字が違うのに違和感があったのですが、やっと理解できました。
INDIRECTに関してですが、実はSUMPRODUCT以下の教えていただいた関数の意味を理解するのにこの週末時間を費やしたことがあり、新しい関数を勉強していたら、明日までに資料が間に合わないというあせりがあったというのが本音です。^_^;
（汗顔ばかりですが・・・）

色々とお手数をおかけしました。本当にありがとうございました。

プロフィールを読ませていただきました。長い間色々な方にアドバイスをされてこられたのですね。敬服いたします。6月いっぱいで中断されるのは非常に残念です。長い間お疲れ様でした。

回答No.3

noname#64531

#1です。回答後試してみてROW($A$1:$A$9)ＯＫでした。
何ででしょうね。

お礼

knaokiさん色々とありがとうございました。

何とか問題も解決できたようです。本日までに何とか仕上げないとだめだったもので、色々とご無理なご相談をしましたことをお詫び申し上げます。
まだ、何かご質問をさせていただくこともあるかと思いますが、その際はよろしくお願いいたします。

補足

knaokiさん迅速な御回答に大変感謝いたします。
申し訳ありません、こちらの間違いでした。おっしゃるように確かにＯＫのようです。

ただ、実は実際のデータにおいては（Ａ15：Ｂ15）～（Ａ200：Ｂ200）近くまでデータが入っており、しかもそのデータは毎日数が微妙にへんかします。そこで、いっその事関数にでている数字をすべて（Ａ１；Ｂ220）位で固定してしまえればいいなと思ったのです。具体的には

=SUMPRODUCT((MATCH($A$1:$A$220&$B$1:$B$220,$A$1:$A$220&$B$1:$B$220,0)=ROW($A$1:$A$220))*($B$1:$B$220="A"))

という具合です。　ＲＯＷの部分だけを変化させるのではなく、全部の数字を変化させても意味は一緒なのでしょうか？
質問の仕方が要領を得ずに申し訳ありませんが、もし、ご存知なら教えていただければありがたいです。

回答No.2

noname#204879

》 入力されているデータにおいては、
》 =SUMPRODUCT((MATCH($A$1:$A$9&$B$1:$B$9,$A$1:$A$9&$B$1:$B$9,0)=ROW($A$1:$A$9))*($B$1:$B$9="A"))

$ を付けるのがお好きなようですが、今回の場合は全く無意味なので、それを取っ払った次式でも同じ結果を出すことはご存知でしょうか？
=SUMPRODUCT((MATCH(A1:A9&B1:B9,A1:A9&B1:B9,0)=ROW(A1:A9))*(B1:B9="A"))

「Ａ1～Ｃ11のデータをすべて切り取りして」別の場所に“移動”するのなら、「ROW(A1:A9)」の部分を変化させなくするために、そこンとこだけを「ROW(INDIRECT("A1:A9"))」にしておけばよろしいかと。つまり、式全体は次のようにするのです。
=SUMPRODUCT((MATCH(A1:A9&B1:B9,A1:A9&B1:B9,0)=ROW(INDIRECT("A1:A9")))*(B1:B9="A"))
無論、お好みで $ を付加するのは構いません。

お礼

mike_gさん御回答感謝します。
おっしゃるように確かに＆はいらないですよね。
ＩＮＤＩＲＥＣＴですが実は実際のデータにおいては使えない事情がありまして困っています。

参考にさせていただきます。ありがとうございました。

補足

すいません＆ではなく＄です。

回答No.1

noname#64531

検証はしていませんが、
おそらく、ROW関数でしょう。

$11:$19 -> $1:$9

補足

さっそくご回答ありがとうございます。
検証してみましたが、＄1：＄9ではやはり0になってしましますが、
＄1：＄19にしてみると3が返ってくるようです。
つねに＄1からデータのあるところまでという認識でいいのでしょうか？（例え上部行にデータがなかったとしても）

もし、ご存知のようなら教えてください。