SUMPRODUCT関数について

質問

アンケートの集計をするにあたって、
SUMPRODUCT関数を使いました。
シートが2つあります。

【データ】シート・・・アンケートのデータ
A:No.　B：データ
1　　　0
2　　　1
3　　　2
4　　　3
5　　空欄が入っている
・
・

【集計】シート・・・B列の空欄、1～3の個数を集計する
B列に数式
=SUMPRODUCT((データ!$A$4:$A$40000<>"")*(データ!B$4:B$40000=$A4))
を入れました。
A　　B
0　　2　・・・・※
1　　1
2　　1
3　　1
空欄　2・・・・※

０（ゼロ）と空欄を区別しないで同じと計算してしまいます。
データに、ゼロと空欄が混在している場合、SUMPRODUCTは使えないのでしょうか？
この場合、どの数式を使うのがベストでしょうか？

分かりにくくて申し訳ありませんが、
宜しくお願い致します。

回答No.1

maruru01 · ベストアンサー

こんにちは。maruru01です。

=SUMPRODUCT((データ!$A$4:$A$40000<>"")*(データ!B$4:B$40000=$A4))

↓

=SUMPRODUCT((データ!$B$4:$B$40000<>"")*(データ!B$4:B$40000=$A4))

は、単なる記述ミスですか？
下の式なら、「0」と空欄は区別されると思いますが。
ちなみに、空欄の方は、別の数式で、

=SUMPRODUCT((データ!$B$4:$B$40000="")*1)

になります。

ひょっとして、

=SUMPRODUCT((データ!$A$4:$A$40000<>"")*(データ!$B$4:$B$40000<>"")*(データ!B$4:B$40000=$A4))

=SUMPRODUCT((データ!$A$4:$A$40000<>"")*(データ!$B$4:$B$40000=""))

ということかな。

お礼

早速の回答、そして、maruru01さん、以前もお世話になり、ありがとうございます。
（行番号初めをずっと$4としていましたが、$1の間違いです。申し訳ありません。）

>単なる記述ミスですか？

いえ、そうではなく
【（データ!$A$4:$A$40000<>""）】に関しては、
うまく説明できないのですが
私の考えとしては、
「4000レコードのうちデータがここまであります」と判断させるために、
【（データ!$A$4:$A$40000<>""）】を数式に付け足しました。

>=SUMPRODUCT((データ!$A$4:$A$40000<>"")*(データ!$B$4:$B$40000<>"")*(データ!B$4:B$40000=$A4))
>ということかな。
【(データ!$B$4:$B$40000<>"")】
を付け足せばよかっただけだったのですね。

>ちなみに、空欄の方は、別の数式で、
>=SUMPRODUCT((データ!$B$4:$B$40000="")*1)

【""】だとちゃんと計算されるのですが、
【""】を他の式同様【$A5】とすると、ゼロも一緒に数えてしまうのです。

空欄のみ数式をかえればいいのですが、
こういうことになると、キチッキチッとしてるのが好きなもので・・・。（普段はだらしないですが）

ありがとうございました。

回答No.5

kaisendon

２です

＞（計算方法手動は他のブックにも反映されてしまうので

勘違いでした。

他のブックには反映されません。
他のシートには反映されます。

失礼しました～ o(_ _)o

回答No.4

mshr1962

=SUMPRODUCT((データ!$A$4:$A$40000<>"")*(データ!B$4:B$40000&""=$A4&""))
でブランクを0にしないようにするか

=SUMPRODUCT((データ!$A$4:$A$40000<>"")*(データ!B$4:B$40000<>"")*(データ!B$4:B$40000=$A4))
とブランク行を排除する。

お礼

早速の回答、ありがとうございます。
（行番号初めをずっと$4としていましたが、$1の間違いです。申し訳ありません。）

>=SUMPRODUCT((データ!$A$4:$A$40000<>"")*(データ!B$4:B$40000&""=$A4&""))
>でブランクを0にしないようにするか

すっきり出来ました！
色々できるのですね。

ありがとうございました。

回答No.3

BLUEPIXY

=SUMPRODUCT((データ!$A$4:$A$40000<>"")*(EXACT(データ!B$4:B$40000,$A4)))
でどうでしょう？

お礼

早速の回答、ありがとうございます。
そして、BLUEPIXYさんも、以前お世話になりました。ありがとうございます。
（行番号初めをずっと$4としていましたが、$1の間違いです。申し訳ありません。）

同じ数式でB1～B5がしっかり計算できました！

ありがとうございました。

回答No.2

kaisendon

こんにちは
=SUMPRODUCT((データ!$A$4:$A$40000<>"")*(データ!B$4:B$40000=""))

という式を作ってその数を引くというのは如何でしょうか？

同様の考え方で

集計用シートのB4だけ（B5以降はご質問の式でいいです）
=SUMPRODUCT((データ!$A$4:$A$40000<>"")*(データ!B$4:B$40000=$A4))-SUMPRODUCT((データ!$A$4:$A$40000<>"")*(データ!B$4:B$40000=""))

蛇足ですが。。。
ただでさえ重いと思います。
オプションで計算方法を手動に設定して
F9で再計算させた方が入力が楽になると思います。
（計算方法手動は他のブックにも反映されてしまうので
　処理が終わったら「自動」に戻しておく事をお忘れなく＾＾）

お礼

早速の回答、ありがとうございます。
（行番号初めをずっと$4としていましたが、$1の間違いです。申し訳ありません。）

>集計用シートのB4だけ（B5以降はご質問の式でいいです）
>=SUMPRODUCT((データ!$A$4:$A$40000<>"")*(データ!B$4:B$40000=$A4))-SUMPRODUCT((データ!$A$4:$A$40000<>"")*(データ!B$4:B$40000=""))

うまく出来ませんでした・・・。

>ただでさえ重いと思います。

そうなんです！重いんです！
計算している時、すごいパソコンががんばってるんです！
気をつけます。
ありがとうございました。