Excelで特定の文字を含む列の数値と、特定列の掛け算を合計したい

Excelで、特定の文字を含む列にある数値と、
その数値のある行の特定列の掛け算の合計を、
集計してくれる関数はあるでしょうか。

SUMPRODUCTやmatch関数で計算しようとしているのですが、うまくできません。

１行目はタイトル行で、左から、商品名、単価、以下たくさん支店名（○支店、×支店、△支店、□支店...）のように並んでいます。

ただし、１か月の集計期間にまったく販売がなかった支店は、表記がなくなります。
つまり、どの列に、どの支店が掲載されるかは、月によって異なります。

２行目より下は、販売商品がずらっと並んでいます。
こちらも販売がなかった商品は掲載されませんが、最大でも2000個です。
最大2000行の指定で足ります。

ある月の集計表（SheetBとします）が、次のようだったとします。
支店によって販売がなかった商品は空白となっています。

商品名　単価　○支店　×支店　□支店
りんご　100円　1　　　（空白）　 2
みかん　 80円（空白）　 2　　　（空白）
バナナ　120円　2　　　　1　　　　1

それをたとえばSheetAから参照して、
次のように各支店別の売上高を、関数で自動計算したいのです。

○支店　340円
×支店　280円
△支店　　0円
□支店　320円
▽支店　　0円

よい方法があれば、教えていただけますか。

No.2 ベストアンサー 回答者： tom04 回答日時： 2015/05/26 08:46

No.1です。

とりあえずは解決したようなので良かったです。

＞OFFSET関数が理解できていませんし、
＞MATCH関数のあとにある-1も意味がわかりませんが...。

というコトなので若干の説明を・・・
実は前回
＞最大でも2000個です。
の部分を見逃していましたので、
数式を↓のように変更してください。

=IFERROR(SUMPRODUCT((Sheet1!B$2:B$3000)*(OFFSET(Sheet1!A$2:A$3000,,MATCH(A2,Sheet1!$1:$1,0)-1))),"")

（前回の1000の部分を3000に変更しただけです）
SUMPRODUCT関数は配列数式になってしまいますので、極端にデータ量が多い場合はオススメしませんが、
この程度の範囲であれば問題ないと思います。

さてOFFSET関数についてですが、
仮に前回の配置で「○支店」の場合を考えてください。
数式は
=SUMPRODUCT((Sheet1!B2:B3000)*(Sheet1!C2:C3000))
で集計ができます。
今回は後半部分の
＞(Sheet1!C2:C3000)
が行によって変動しますので、ここにOFFSET関数を使っています。

OFFSET関数は
（参照,行数,列数,高さ,幅)
というコトになりますので
参照（基準）はA2～A3000　の範囲となります。
今回行数は無視していますので、列数だけが必要になります。
=MATCH(A2,Sheet1!$1:$1,0)
としてしまうと、「3」という値が返ってきます。
先ほど書いたように参照（基準）はA列にしていますので、
その3列右になればD列となり1列ずれてしまいます。
本来であれば「2」という結果がここでほしいので「-1」を付け加えているだけです。

以上長々と書きましたが、この程度で・・・m(_ _)m

この回答へのお礼

続けて詳しいご説明をしてくださり、ありがとうございます！

> 今回は後半部分の
> ＞(Sheet1!C2:C3000)
> が行によって変動しますので、ここにOFFSET関数を使っています。

という部分と、

> 参照（基準）はA列にしていますので、
> その3列右になればD列となり1列ずれてしまいます。

というご説明が、自分の勘違いを見事に補ってくださいました。

これからは自分でもOFFSET関数を使えそうな気がして、うれしい気分です。
本当にありがとうございました！！

お礼日時：2015/05/26 23:48

No.1 回答者： tom04 回答日時： 2015/05/25 09:06

こんにちは！

一例です。

↓の画像のように元データがSheet1にあり、Sheet2に集計するとします。
Sheet2のA列は入力済みだという前提で・・・

Sheet2のB2セルに
=IFERROR(SUMPRODUCT((OFFSET(Sheet1!A$2:A$1000,,MATCH(A2,Sheet1!$1:$1,0)-1)*Sheet1!B$2:B$1000)),"")
という数式を入れ、フィルハンドルで下へコピーしています。m(_ _)m

この回答へのお礼

できました！　ありがとうございます！
こんなにすぐ回答いただけるなんて、すごいですね。

自分では、OFFSET関数が理解できていませんし、
MATCH関数のあとにある-1も意味がわかりませんが...。

それでも集計したかったことは見事にできました。
助かりました。ありがとうございます！

お礼日時：2015/05/25 23:35