SUMPRODUCT 複数条件設定で、計算式結果がおかしいです。

Question

下記のような表があったと仮定します。

　　A　　B　　　C　　　D 
1　月度　店舗　品目　売上金額 
2　4　　　A　　　あ　　22300 
3　4　　　B　　　あ　　18700 
4　4　　　C　　　あ　　14500 
5　4　　　A　　　い　　17950 
6　5　　　B　　　あ　　44000 
7　5　　　C　　　い　　35000 
8　5　　　A　　　う　　12000 
9　5　　　B　　　い　　13400 

この表から、
店舗「A」、品目「あ」の売上合計は、22300になり、
数式「=SUMPRODUCT(($B$2:$B$9="A")*($C$2:$C$9="あ")*$D$2:$D$9)」で計算できます。

店舗「A,B」、品目「あ」の売上合計は、85000になり、
数式「=SUMPRODUCT(($B$2:$B$9={"A","B"})*($C$2:$C$9="あ")*$D$2:$D$9)」で計算できます。

店舗「A」、品目「あ,い」の売上合計は、40250になり、
数式「=SUMPRODUCT(($B$2:$B$9="A")*($C$2:$C$9={"あ","い"})*$D$2:$D$9)で計算できます。

しかし、
店舗「A,B」、品目「あ,い」の売上合計は、116350なのですが、
数式「=SUMPRODUCT(($B$2:$B$9={"A","B"})*($C$2:$C$9={"あ","い"})*$D$2:$D$9)」では、数値がおかしく（35700に）なります。

数式の使い方が間違っているのでしょうか？
アドバイスをお願いします。

papayuka · Accepted Answer

「A」が「あ」と、「B」が「い」の合計ですね。
こちらの数式の原理
http://miyahorinn.fc2web.com/faq/faq100.html

SUMPRODUCTとか配列関数とかを熟知してませんので、もっとましな方法があるかも知れませんけど、、、
=SUMPRODUCT(($B$2:$B$9={"A","A","B","B"})*($C$2:$C$9={"あ","い","あ","い"})*$D$2:$D$9)
とか。

sige1701 · Answer

No1さんが回答していますので理由については略

こんな方法でも
=SUMPRODUCT((($B$2:$B$9="A")+($B$2:$B$9="B"))*(($C$2:$C$9="あ")+($C$2:$C$9="い"))*$D$2:$D$9)

imogasi · Answer

>店舗「A,B」、品目「あ,い」の売上合計は
この意味があいまいでしょう。質問者には判っているが、一般的に、こう書いても判らない。文章で正確に表現すべし。
ただ合計116350だと質問者が言うので、それに当たる組み合わせを、回答者が探さないといけない。
"→第２条件
↓第１条件"あいう
A○○✕
B○○✕
C✕✕✕
らしい。
ーー
配列数式でやると
=SUM(IF(((B2:B9="A")+(+B2:B9="B"))*((C2:C9="あ")+(C2:C9="い")),D2:D9))
と入れて
SHIFTとCTRKとENTERキーを押す。　結果　116350
ーーー
SUMPRODUCTの書き方に書き換えると
=SUMPRODUCT(((B2:B9="A")+(+B2:B9="B"))*((C2:C9="あ")+(C2:C9="い"))*(D2:D9))
結果　116350
両方とも「＋」は「または」の場合に使う。「＊」は「および」の意味で使うのは、ご存知でしょう。
｛"A","B"}は確かに両方の要素について行うことになると思うが、
なぜダメなのか、小生の知識が整理できていないので、自分流の回答をとりあえず出す。
選択条件A,Bなどの項目数が多いと、そういう表現ができればよいですが。

grumpy_the_dwarf · Answer

($B$2:$B$9={"A","B"})という式が返す結果は
{TRUE,FALSE;FALSE,TRUE;FALSE,FALSE;TRUE,FALSE;FALSE,TRUE;...}
という2列の配列になります。これを踏まえて、

($C$2:$C$9="あ")という式は
{TRUE;TRUE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE}
という1列の配列なのですが、それでは計算出来ないので列数の一番
大きな配列に揃えて
{TRUE,TRUE;TRUE,TRUE;TRUE,TRUE;FALSE,FALSE;TRUE,TRUE;...}
と扱います。これが2番目と3番目の式で一見or条件での計算が成り
立っていた理由です。

ここで、($B$2:$B$9={"A","B"})*($C$2:$C$9={"あ","い"})とすると
{TRUE,FALSE;FALSE,TRUE;FALSE,FALSE;TRUE,FALSE;FALSE,TRUE;...}
と
{TRUE,FALSE;TRUE,FALSE;TRUE,FALSE;FALSE,TRUE;TRUE,FALSE;...}
のANDになるので、結果はpapayukaさんやimogasiさんの喝破したと
おり、(A and あ) or (B and い)だけがTRUEになってしまいます。

というわけで、中括弧は配列の記述だと決まっているのにor条件の
つもりで使うのは危険であることの解説でした。解決法は、他の回
答者の皆さんがおっしゃるとおり、マジメにor条件を評価していく
ことですね。

Wendy02 · Answer

こんにちは。

----------------------------------
1. データベース関数なら、

DataBase:
=$A$1:$D$9

Criteria:
=$H$1:$I$5

H　　　I
店舗　　品目
A　　　　あ
B　　　　い
A　　　　い
B　　　　あ

D1: (念のため）
売上金額

 =DSUM(database,D1,Criteria)
----------------------------------------
配列数式なら、

=SUMPRODUCT(($B$2:$B$9&$C$2:$C$9={"Aあ","Aい","Bあ","Bい"})*$D$2:$D$9)

複雑になると、ミスが多くなりそうです。
----------------------------------------
補助列を使って、

E2: ～下へ
=AND(OR(B2="A",B2="B"),OR(C2="あ",C2="い"))

=SUMIF($E$2:$E$9,TRUE,$D$2:$D$9)

という式でもよいと思います。
-----------------------------------------

Wendy02 · Answer

こんにちは。
#5の回答者です。

>いろいろな方法があるのですね。
>なぜそうなるのかは理解できていないため、これから勉強していきます。

ちょっと思うことですが、いくつかの方法は、掲示板ならではの回答だと思います。
掲示板の回答って、省スペースなものになる傾向が強いようです。

ただ、やっぱり

>補助列を使って、
>
>E2: ～下へ
>=AND(OR(B2="A",B2="B"),OR(C2="あ",C2="い"))
>
>=SUMIF($E$2:$E$9,TRUE,$D$2:$D$9)

の方法が、オーソドックスではないでしょうか？
自分の書いたものでも、後で読みきれない数式がありますから、納まりの良さというものは、実務でも、必ずしも必要ないと違いますか？

SUMPRODUCT 複数条件設定で、計算式結果がおかしいです。

「A」が「あ」と、「B」が「い」の合計ですね。

No1さんが回答していますので理由については略

>店舗「A,B」、品目「あ,い」の売上合計は

($B$2:$B$9={"A","B"})という式が返す結果は

こんにちは。

こんにちは。

似たような質問が見つかりました

このQ&Aを見た人はこんなQ&Aも見ています

関連するカテゴリからQ&Aを探す

このQ&Aを見た人がよく見るQ&A

デイリーランキングこのカテゴリの人気デイリーQ&Aランキング

マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング