Excel）SUMPRODUCT関数で・・・

質問

今まで困ったときにこちらで色々調べて答えを見出していた者です。
いつも参考にさせていただきありがとうございます。
ついに、うまいこと調べたいことが見つけ出せなくなってしまったため質問をさせてください。

■基データ
　　　A　　　　　B　　　　C　　　　　 D　　　
―――――――――――――――――――――
1　氏名　　　形式　　　内容　　　　金額
2　やまだ　　　Ａ　　　りんご　　　50,000
3　さとう　　　Ｂ　　　バナナ　　　 8,000
4　やまだ　　（Ａ）　　みかん　　▲30,000
5　すずき　　　Ｂ　　　もも　　　　 6,000
6　たなか　　　Ｃ　　　ぶどう　　　 5,000
7　さとう　　（Ｂ）　　キウイ　　 ▲4,000

というデータがあるとします。(明細みたいなもの)
これを氏名(A列)と形式(B)列の条件で金額(D列)の合計を
別の表(集計表みたいなもの)に結果を出したいと思っています。
以前こちらで調べて、「SUMPRODUCT」関数が適していると思い
試してみました。そして結果も得られていました。
ですが、条件がちょっと複雑になってしまい、その関数での
対応が可能かどうかお知恵をお貸しいただきたいと思います。
または、別の方法(ピボットテーブルは対象外とさせてください)
がありましたらご教示下さい。よろしくおねがいします。

【今までの条件設定(結果表示したいセルへの入力式)】
　＜氏名がやまだで形式がAの場合の金額の合計＞
　=SUMPRODUCT(($A$2:$A$7="やまだ")*($B$2:$B$7="Ａ"),$D$2:$D$7)
　　※さとう・すずき・たなかもそれぞれ求めます。
　　※形式Ｂ・Ｃもそれぞれ求めます。(そして当初は形式に「（）」の表示はありませんでした。)

【条件の変更によって結果を求めたいこと】
　■氏名別は変わらず、形式は「（）」のあるなし関わらず
　　[Ａも（Ａ）も同じ扱い]として金額の合計を求めたい。
　　上記の場合ですと、「やまだのＡ・（Ａ）の合計値」と
　　「さとうのＢ・（Ｂ）の合計値」を算出する数式を知りたいです。

Bookの特徴ですが、　
　・結果を出させるセルは同一シート内にあり、また印刷時は同一用紙に表示させる
　・結果を出させるセルは見栄えの問題で結合させてある
という具合です。。。

何かよい方法はありますでしょうか？
宜しくお願いいたします。

回答No.2

ka_na_de · ベストアンサー

こんにちは。

作業列を使っていいのなら、見通しも良く簡単ですよ。

１）Ｅ２に
　　=IF(LEFT($B2,1)="（",MID($B2,2,1),MID($B2,1,1))
　　と入力し、Ｅ３～Ｅ７にコピー＆ペースト
２）=SUMPRODUCT(($A$2:$A$7="やまだ")*($E$2:$E$7="Ａ"),$D$2:$D$7)
で計算する。
　※）B→Eに変更しているだけです。
３）もし、Ｅ列が見苦しいならＥ列を選択し、右クリックして
　　「表示しない」を選択してください。印刷時も非表示です。

お礼

早急な返答ありがとうございます。
この方法で乗り切れそうです！(現在数式当てはめてデータ検証中)
大変参考になりました。ありがとうございました！

回答No.4

imogasi

#3です。
＃３のデータを使って
SUMORODUCTでは
=SUMPRODUCT(($A$2:$A$10="やまだ")*(($B$2:$B$10="Ａ")+($B$2:$B$10="（Ａ）"))*($D$2:$D$10))
です。
結果
２２０００
ーー
私の持論で合計より、
やまだ
さとう
すずき
たなか
のリストをどうして作るか、の方が大変と思う。
これができれば、”やまだ"のところはA3とかのように、番地で指定できる。
「さとう」の場合も式複写でできる。
ーー
＞結合させてある
セル結合は関数式にとって、式の複写で０の扱いになるようだが、個別にチェックが必要で、アンシンできない。

補足

早速ありがとうございます。
質問も長くてすみません。。

SUMPRODUCTで上記の式で試してみたのですが、「やまだ」の場合はうまく行っても他の名前のひとで同様の式を入れた場合、
求めたい数字と異なる答えが出るのです…。(他の人の金額も計算されている様子)

もう一度チャレンジしてみます。

回答No.3

imogasi

質問が長いが、
AND条件にOR条件が加わった問題といえよう。
配列数式の法が早くできたので乗せます。
やまだでＡか（Ａ）のD列合計。
例データ
氏名 形式 内容 金額
やまだ Ａ りんご 50,000 *
さとう Ｂ バナナ 8,000
やまだ （Ａ） みかん -30,000 *
すずき Ｂ もも 6,000
たなか Ｃ ぶどう 5,000
さとう （Ｂ） キウイ -4,000
やまだ （Ａ） りんご 2,000 *
すずき Ａ りんご 1,000
やまだ Ｃ りんご 3,000
空きセルに
=SUM(IF(($A$2:$A$10="やまだ")*(($B$2:$B$10="Ａ")+($B$2:$B$10="（Ａ）")),$D$2:$D$10))
と入れてSHIFT+CTRL+ENTER３つ同時押し。
＋に式の中の注意。OR条件です。
結果
２２０００
ーー
SUMPRODUCTで考えて見ます。

お礼

今回、別方法(No.2様の方法)で結果を得られました。
上記で教えていただいたことも今後の参考にしていきたいと思います。
ありがとうございました。

補足

ありがとうございます。
試してみました。
・・・ですが、SUMPRODUCT時同様、たとえばさとうのところの式に
=SUM(IF(($A$2:$A$10="さとう")*(($B$2:$B$10="Ａ")+($B$2:$B$10="（Ａ）")),$D$2:$D$10))
としてもやまだの金額も計算されてしまうのです。困りました…。

回答No.1

grumpy_the_dwarf

not(iserror(find("A",$B$2:$B$7,1)))
みたいな感じで書くと文字列の長さにかかわらず「A」を含んでる場
合にTRUEになります。他には、

mid($B$2:$B$7, (len($B$2:$B$7)+1)/2, 1)="A"
とすると、形式が1文字の場合には1文字目、3文字の場合には2文字
目だけを「A」と比較出来ます。

とりあえず思いついたのだけ。

お礼

早急なご返答ありがとうございました。