Excel 複数の検索値の合計を出したいのですが

質問

Excel2010でどのように関数を使えばよいかわからずに困っています。
どなたかご教示ください。

　　sheet2 sheet３
　　　　　 　　a1 111
　　　a1 　　　a2 222
　　　a2 　　　b1 333
　　　b1 　　　b2 444
　　　b2 　　　a2　555
　　　　　　　z3 666
　　　　　　　c1 777
　　　　　　　a2 888
　　このようなbookがあります。
　　検索値はsheet２の列の4つ（実際には20程）で、この4つをsheet３の列（実際には40程）か　　ら全て抽出しての列右の数値の合計を出したいのですがどうしてもできません。

　　今回の例の場合　(a1/111)(a2/222)(b1/333)(b2/444)(a2/555)(a2/888)　の合計2,453を
　　任意のセルに返したいのです。
　　sheet２のようなsheetがたくさんあるので関数を使ってコピーして算出したいのですが
　　vlookup・sumif・sumifs・countif・dsum等々調べましたが私にはどうしてもできません。

　　ピボットテーブルやマクロまで詳しくないので関数で何とか算出できないかとご相談です。
　　お詳しい方どうぞよろしくお願いいたします。

回答No.5

mt2015 · ベストアンサー

添付の図を見てください。
左がSheet3、右がSheet2ですが、こういう事ですか？
であれば、合計は2553ですよね？

Sheet2のB1セルには↓の式が入っています
=SUMPRODUCT(COUNTIF(A:A,Sheet3!A1:A8)*(Sheet3!B1:B8))

お礼

mt2015 様

有難うございます、これでやっとすっきりします。
これからはこの数式を有効に使ってアレンジしてゆきます、
大変有難うございました。

回答No.4

bunjii

>検索値はsheet２の列の4つ（実際には20程）で、この4つをsheet３の列（実際には40程）から全て抽出しての列右の数値の合計を出したいのですがどうしてもできません。
ExcelのSheetでは列をA、B、C等の英字で表示し、行を1、2、3等の数字で表示することをご存知と思います。
検索値がSheet2のA列1行目からT列1行目の20個と仮定して良いでしょうか？
集計対象のSheet3にはA1:AN2の40列×2行の内1行目が検索範囲で集計範囲は2行目と考えて良いのですか？
稀に、行と列を取り違える方がいますので確認した上で回答したいと思います。
考え方としては検索範囲（40）×検索値（20）のマトリクスに集計値のスカラーを乗じたマトリクスの合計を求めることになります。
行と列を取り違えると数式の扱いが異なることになりますので検証するには数表の形を明確にしなければなりません。
数式に使う組み込み関数はSUMとTRANSPOSEで良いことになります。
検索値がSheet2!A1:T1で集計対象データがSheet3!A1:B40であればTRANSPOSE関数を必要としません。

>sheet２のようなsheetがたくさんあるので関数を使ってコピーして算出したいのですが
集計対象データはSheet3の1つだけでしょうか？
また、Sheet2以外の検索値のSheetはすべて同じ形でしょうか？
集計対象データが1つでも検索値が同じ形になっていないと数式をコピーしても修正が必要になります。

具体的な数式を組み立てるための情報を提示してください。

お礼

bunjii 様

ご丁寧な回答大変有難うございます。
いろいろと試してExcelもっと勉強したいと思います。

回答No.3

tsubu-yuki

２です。
失礼しました。ＳＵＭＩＦＳ関数はＡＮＤ条件（且つ）だから不適ですね。

単純な関数でお茶を濁すのであれば、
Sheet3の邪魔にならない列（例としてＣ列）に
　Ｃ２セル：=COUNTIF(Sheet2!A:A,Sheet3!A2)
これをコピー。
Sheet2の任意のセルに
　=SUMIF(Sheet3!C:C,">0",Sheet3!B:B)
これで完成。

おそらく、これが簡単だと思いますよ。

お礼

tsubu-yuki 様

有難うございます、いろいろと試してみたいと思います。

回答No.2

tsubu-yuki

少々ご質問文の中に曖昧さを感じるのです。
なので、的から外れていたら申し訳ないです。

例えば、ここ
> sheet３の列（実際には40程）か　　ら全て抽出して
これは
・実際は（例えば）Ｂ列に４０行ほどデータがある
・実際は（例えば）Ｂ列～ＡＯ列まで４０列ほどデータがある
どちらでしょう？
前者なら単純にＳＵＭＩＦ関数で
　　=SUMIF(Sheet3!$A$2:$A$41,Sheet2!A1,Sheet3!$B$2:$B$41)
でいけるかもしれません。
後者の場合は作業用の列にＳＵＭ関数で合計を出しておいて、
同様にＳＵＭＩＦ関数で何とかなるかもしれません。
コレを
> 検索値はsheet２の列の4つ（実際には20程）
この数だけ準備して、最終的に合計してやればＯＫだと思われます。

前者でＳＵＭＩＦＳ関数で一度に処理するのであれば、
　　=SUMIFS(Sheet3!$B$2:$B$41,Sheet3!$A$2:$A$41,Sheet2!A2,Sheet3!$A$2:$A$41,Sheet3!A3・・・２０ほど繰り返す)
で強引にできないこともないです。
式が長くなりすぎて何が何だか分からなくなっちゃいますけどね。
なので個人的にはＳＵＭＩＦＳ関数は好みでなかったりします。

※ココでは半角スペース・タブの大半は消去されて詰まってしまいますね。
　全角スペースに置き換えてみるとうまくいくかもしれませんよ。

補足

ご回答有難うございます。
> sheet３の列（実際には40程）か　　ら全て抽出して
これは
・実際は（例えば）Ｂ列に４０行ほどデータがある
ということです。
Ａ列　Ｂ列　約40行程　データが入力されていまして、この40行の中から
sheet２のa1・a2・b1・b2を全て抽出してその合計を出したいと言う質問でした。分かりずらくて申し訳ありませんでした。

ご案内の　
=SUMIF(Sheet3!$A$2:$A$41,Sheet2!A1,Sheet3!$B$2:$B$41)ですとsheet2!A1　でこの場合　a1の値しか出てこないと思います。
sheet2のa1・a2・b1・b2の全て合計値が出ればうれしいのですが、出来ますでしょうか。

回答No.1

f272

=SUM(SUMIF(Sheet3の左の文字列の範囲,Sheet2の文字列の範囲,Sheet3の右の数値の範囲))
でどうでしょう。

補足

ご回答有難うございます。
私も多分関数＋関数で解決できるものと思って試しておりましたがどうしてもうまくいきませんでした。

ご案内の
=SUM(SUMIF(Sheet3の左の文字列の範囲,Sheet2の文字列の範囲,Sheet3の右の数値の範囲))も早速く試してみましたが、どこかが
いけないらしく帰ってくる数値は０でした。
なかなか難しくて手に負えません、何とかなるものでしょうか。