こんにちはゲストさん。会員登録(無料)して質問・回答してみよう!

解決済みの質問

SUMPRODUCTとワイルドカード

こんばんは。

<例>
    A     B   C    (D)
1 北海道  500  100  =B1*C1
2 青森県  400  150  =B2*C2
3 新潟県  200  200  =B3*C3
4 東京都  200  100  =B4*C4
5 沖縄県  250  100  =B5*C5
             合計=SUM(D1:D5)


作業列(D列)を作らず合計を求めるには、
=SUMPRODUCT(B1:B5,C1:C5)
でできると思います。

ただ、ここに「県のみ合計」という条件が入った場合について質問します。
例のように作業列(D列)を作り、SUMIFですれば簡単にできるのですが、「作業列を作らず、SUMPRODUCTとワイルドカード(*県)を使って」数式を組み立てるにはどうすればいいのでしょうか?
ステップ(D列)を踏まず、ひとつの数式だけで完結したいのです。
試行錯誤していますが、簡単そうなのになかなかうまくいきません…。
D1=COUNTIF(A1,"*県")で以下オートフィルコピーし、
=SUMPRODUCT(B1:B5,C1:C5,D1:D5)
とすればできますが、結局作業列(D列)を作ってしまっていますし…。


あと…たとえば最初に示した数式なら、
=SUMPRODUCT((B1:B5)*(C1:C5))
という数式の組み方を回答でよく見かけますが、
=SUMPRODUCT(B1:B5,C1:C5)
=SUMPRODUCT(B1:B5*C1:C5)
としないのは、何か意味の違いがあるのでしょうか?

お詳しい方よろしくお願いします。

Vista Excel2007です。

投稿日時 - 2013-03-19 18:07:59

QNo.8001341

暇なときに回答ください

質問者が選んだベストアンサー

こんばんは!
ワイルドカートではないのですが・・・

表示したいセルに
=SUMPRODUCT((ISNUMBER(FIND("県",A1:A5))*(B1:B5)*(C1:C5)))
または
=SUMPRODUCT((ISNUMBER(FIND("県",A1:A5)))*1,B1:B5,C1:C5)

としてみてはどうでしょうか?
都道府県名以降に○○市などがあっても対応できます。

※ 後者の数式は各列を配列にしていますので、
A列を「1」または「0」にするために *1 としています。
前者の数式は「TRUE」「FALSE」に直接数値をかけていますので、*1 は必要ありません。m(_ _)m

投稿日時 - 2013-03-19 19:09:58

補足

いい回答を二つもいただけましたので、そろそろ締め切らせていただきます。
ベストアンサー悩みましたが、No2様とさせていただきます。
ISNUMBERとFINDは頭に浮かんでいたのに、ネストのやり方が悪くダメだと思ってしまっていました…。ちょっと情けなかったです。
No2様の数式でばっちりでした。
わかりやすい解説、ありがとうございました。

この場を借りて…
No1様、素晴らしい回答をいただきましたのにごめんなさい。
No1様の回答内容を先に頭に入れたことで、No2様の回答がすんなり頭に入ってきました。
エラー表示云々は今後役立つことと思います。
これに懲りず、今後ともよろしくお願いします。
気持ちの上ではお二人ともベストアンサーでした。
ありがとうございました。
お二人に感謝です!

投稿日時 - 2013-03-19 22:12:02

お礼

早々と回答いただき感謝です。
日頃のご活躍拝見させていただいております。

No1様の補足欄の完了後確認しますと、No2様の回答をいただいているのに気付きました。
ちょっと行き違いでしたね。
補足の質問より先に回答していただいた気分です!(実際そうなんですが…)

>都道府県名以降に○○市などがあっても対応できます。
>後者の数式は各列を配列にしていますので、A列を「1」または「0」にするために *1 としています。
→まさにお聞きしたかったことを回答してくださっていたのでびっくりしました。
説明してくだっさたことをしっかり読んで、頭に入れようと思います。

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

投稿日時 - 2013-03-19 19:37:18

ANo.2

このQ&Aは役に立ちましたか?

1人が「このQ&Aが役に立った」と投票しています

回答(2)

ANo.1

一例です。
=SUMPRODUCT((RIGHT(A1:A5,1)="県")*1,B1:B5,C1:C5)

=SUMPRODUCT((B1:B5)*(C1:C5))や=SUMPRODUCT(B1:B5*C1:C5)は、単に配列*配列であり、文字列(""の空も含む)が入るとエラーになりますが、=SUMPRODUCT(B1:B5,C1:C5)では文字列をゼロとして処理するのでエラーになりません。
今回答も、条件列と集計列は「*」で連結しないで=SUMPRODUCT(B1:B5,C1:C5)の様に分けて記述するようにして下さい。

投稿日時 - 2013-03-19 18:29:45

補足

>=SUMPRODUCT((RIGHT(A1:A5,1)="県")*1,B1:B5,C1:C5)
→RIGHTに関してはわかるのですが、「*1」は何を意味するのでしょうか?


あと…
今回はA列には都道府県まででしたが、「市」以降も入力されていた場合、どうすればいいですか?
試しにFINDやISNUMBER、ワイルドカードを使ってみましたが、うまくいきません。
もうちょっとやってみます。

投稿日時 - 2013-03-19 19:18:00

お礼

早々と回答していただき、ありがとうございます。

なるほどです。
今回の例では確かに最後の文字が都道府県のいずれかになっていましたね。

>=SUMPRODUCT(B1:B5,C1:C5)では文字列をゼロとして処理するのでエラーになりません。
→試してみましたところ、教えていただいた通りの結果になりました。
 今後このあたりを意識して使い分けたいです。

わかりやすく説明してくださり頭が下がります。
ありがとうございました。

投稿日時 - 2013-03-19 19:08:55

あなたにオススメの質問