エクセルで複数条件検索

質問

元のデータが、この様になっています。
　　　Ａ　　 　Ｂ　　　　 Ｃ　 　　Ｄ
１　取引月　部門　取引先　金額
２　　９月　関東　東京○　　10
３　１０月　近畿　京都○　　50
４　　９月　関東　○東京　　30
５　１１月　近畿　大阪○　　20

５以降もデータが入っています。
○の部分には、文字が数文字入っています。
例：東京サービス等

別のシートに、下記のＤ２：Ｆ４に算式を入れて、この様な結果が得られるようにしたいのですが、どの様に算式を組めば良いのかわかりません。お手数掛けしますが、よろしくお願い致します。
　　　Ａ　　 Ｂ　　　　 Ｃ　　 　Ｄ　　　Ｅ　　　　Ｆ
１　部門　取引先　取引月　９月　１０月　１１月
２　関東　東京　　　　　　　　40
３　近畿　京都　　　　　　　　　　　　　50
４　近畿　大阪　　　　　　　　　　　　　　　　　20

回答No.4

AloneAgain · ベストアンサー

こんばんは、No.2です。

SUMPRODUCT関数は、複数条件に合致するデータの個数や合計値を求めることができます。↓のような書式です。
=SUMPRODUCT((条件1)*(条件2)*(条件3))

条件が一致すれば "1"、一致しなければ "0" がそれぞれの条件に返ります。

試しに B1セルに"3"、C1セルに"5"、D1セルに"50" と入れて、
2行目の適当なセルに下の数式を入力してください。
=SUMPRODUCT((B1=3)*(C1=5)*(D1))

数式の結果は "50" となるはずです。
"B1=3" と "C1=5"　の条件がともに一致しているので、それぞれに "1" が返り、
1 * 1 * 50 の計算となります。

次に、B1セルの "3" を "1" に変更すると、計算結果は "0" になります。
"B1=3" の条件に一致しないので、この条件は "0" となり、
0 * 1 * 50 の計算となるからです。

上のようなことを、"A2：A30" と範囲を指定すれば、1行分のデータだけではなく表全体のすべての行について調べることができます。
-------------------------
ご質問のケースで説明します。

条件を "9月" および "「東京」という文字列を含む" という2つだとします。
2行目は、2つとも条件が一致するので、　→　1 * 1 * 10　で "10"
3行目は、2つとも条件が一致しないので、→　0 * 0 * 50　で "0"
4行目は、2つとも条件が一致するので、　→　1 * 1 * 30　で "30"
5行目は、2つとも条件が一致しないので、→　0 * 0 * 20　で "0"

結果として合計値"40"が返るわけです。
---------------------------------
こんな説明でわかっていただけましたでしょうか？
実はわたしも機械的に覚えているだけでよくわかっていません(^^ゞ

このあたりの理屈を知るためには "配列" という考え方を理解する必要があります。
"配列" については↓に詳しく説明されていますので、時間があれば一読してみてください。
http://pc21.nikkeibp.co.jp/special/hr/

ついでに言っておきますと、No.1さんの書式は少し異なっていますが、同じことです。

参考URL：

http://pc21.nikkeibp.co.jp/special/hr/

お礼

再度ご回答頂きまして、ありがとうございます。
配列数式はたまに使っていたんですが、あまり理解していませんでした。
ご回答頂いた算式は配列数式と同じ考え方なんですね。
とても詳しく解説を頂きましたので、理解できました。
また、参考ＵＲＬもとっても参考になりました。
本当にありがとうございました。

回答No.3

telescope

数式を使わなくても、ピボットテーブルで簡単に出来ます。

元のデータ範囲内のどれか１つのセルを選択して、
「でーた」－「ピボットテーブルとピボットグラフ　レポート」を選択します。

そのまま「完了」ボタンを押します。

「行のフィールド」に部門と取引先を、「列のフィールド」には取引月を、「データアイテム」に金額をそれぞれドラッグします。
出来上がりです。

10月、11月、9月の順になっているかもしれません。
その場合は、9月のセルをドラッグして所定の位置に持っていきます。

お礼

ご回答頂きまして、ありがとうございます。
ピボットテーブルでも集計できるのは、知っていたんですが、ご質問させて頂いた内容よりも少し複雑な集計をしていまして、
ピボットテーブルでは集計することができないので、算式を使って何か良い方法がないかと思い、質問させて頂きました。
説明不足で申し訳ございませんでした。

回答No.2

AloneAgain

こんにちは。

↓の式をコピーして、Sheet2のD2セルに貼り付けてください。
念のため＜形式を選択して貼り付け＞－＜テキスト＞で。
-------------------------------------------------------
=SUMPRODUCT(ISNUMBER(SEARCH($B2,Sheet1!$C$2:$C$30))*(Sheet1!$A$2:$A$30=D$1)*(Sheet1!$D$2:$D$30))
-------------------------------------------------------

Sheet1の表はA1：D30と仮定しています。
30行より下にもデータがある場合は、Sheet1!のあとの範囲（30）を実際の表に合わせて修正してください。（D2セルを修正して、以下コピー）
-------------------------------------------
★上の式では関東などの＜部門＞は考慮していません。
もし＜取引先名＞に"東京"の文字を含んでいても、"近畿"など"関東"以外の場合もありうるのであれば、
↓の式を使ってください。
--------------------------------------------
=SUMPRODUCT(ISNUMBER(SEARCH($B2,Sheet1!$C$2:$C$30))*(Sheet1!$B$2:$B$30=$A2)*(Sheet1!$A$2:$A$30=D$1)*(Sheet1!$D$2:$D$30))
--------------------------------------------

お礼

ご回答頂きまして、ありがとうございます。
算式をコピーして貼り付けて試してみたら、見事に計算できました。
しかし、この算式の意味が今ひとつわかりません。
もしよろしければ、ご説明頂けないでしょうか。

回答No.1

BLUEPIXY

シート２のＤ２に
=SUMPRODUCT((Sheet1!$A$2:$A$5=D$1)*(Sheet1!$B$2:$B$5=$A2)*(LEN(Sheet1!$C$2:$C$5)<>LEN(SUBSTITUTE(Sheet1!$C$2:$C$5,$B2,""))),Sheet1!$D$2:$D$5)
をコピーし
Ｄ２：Ｆ４に貼り付け

お礼

ご回答頂きまして、ありがとうございます。
算式をコピーして貼り付けて試してみたら、見事に計算できました。
しかし、この算式の意味が今ひとつわかりません。
言葉で説明すると(Sheet1の取引月の列と９月が同じ)で(Sheet1の部門の列と関東が同じ)で
(Sheet1の取引先の文字数とSheet1の取引先の中で東京があれば空白にした文字数？が等しくない場合)×９月の列？
説明を書けば書くほど、恥ずかしながらわからなくなってきました。
もしよろしければ、ご説明頂けないでしょうか。