エクセルで複数sumifの検索範囲の連動

質問

画像のように太線での区切りごとの合計金額を算出したいと思います。
一旦品名ごとに

お菓子Aは100円
お菓子Bは20円
ガムは…

と小計を算出してから、ワンクッションおいてそれらを合計してもいいのですが、
紙面のスペースの関係上1セルで合計したいと思います。そこでD列のセルには

=SUMIF(A2:A5,$A$16,B2:B5)*$B$16+SUMIF(A2:A5,$A$17,B2:B5)*$B$17+　　　以下略

=お菓子Aの数量*お菓子Aの単価+お菓子Bの数量*お菓子Bの単価　　　以下略

とSUMIFで品名ごとに数量を合計してから*その品の単価を足していきたいと思います。
そうするとこの場合一つのセルにSUMIFが四回使われ、検索範囲の設定が面倒です。
1セル中の全てのSUMIFの検索範囲を統一する便利な方法はないでしょうか。

或いは別の関数を使ったまったく異なる方法でも構いません。
どなたかご教授いただければ。

回答No.1

keithin · ベストアンサー

=SUMPRODUCT(SUMIF(A2:A5,A16:A19,B2:B5),B16:B19)
といった具合で出来ます。

補足

早速のご回答ありがとうございます。
これは便利ですね。試してみると、まさに希望していた通りのものでした。

エクセルのデータはうまく完成したのですが、自分はご教授いただいた
=SUMPRODUCT(SUMIF(A2:A5,A16:A19,B2:B5),B16:B19)
の表面しか理解できていないので、重ねて質問します。
差し支えなければご回答お願いします。

SUMIFの検索条件の部分には、セル番地、文字列、比較演算子を入力できるのは存じていたのですが、
ここではA16:A19と、単一の番地ではなく範囲を指定していますよね。
そうすると、その範囲に含まれる項目それぞれについて検索し、個別に合計範囲B2:B5の値を加算する
という理解で正しいでしょうか。

そしてその個別の加算結果にSUMPRODUCTでそれぞれの単価を掛け算しているということでしょうか。

最後にもう一点、自分が掲載した画像の470円の塊の部分では
「アメ」の数量が0、なのではなく、そもそもアメの項目がありませんが、
この場合SUMIFの検索結果では数量0の時と同様に処理されている
→SUMPRODUCTでアメの単価を掛け算しても0*単価＝0
ということでよいのでしょうか。

回答No.6

keithin

＞vlookupを使用している行にて「型が一致しません。」のエラーが発生してしまいます。

既に回答済みですが
再掲：
＞言い換えると「金額一覧にない品目が載っている」と、おかしなことになります。

つまり「データが間違ってる」のが直接の原因なので，あなたのエクセルのデータをしっかり確認して下さい。
マクロ以前に，最初に回答した関数による方式でも，「見た目意図した計算結果と違う」ことは確認できるはずです。

＞どこかで選択範囲を変更しているのでしょうか。

んーと？？こちらもマクロの中に回答が既にありますが，
selection.offset(1).select
としてありますが？

エラーが起こったので，そこで見るのを止めたんでしょうか。

お礼

ご返答ありがとうございます。
品目をアメだけ、あるいはガムだけにしてもまだ金額一覧にない品目が載っているというのか、
と思っていたら、おっしゃる通りデータをよく確認すると
質問時の画像の「以下略」のセルが結合されたままであるのが原因でした。
横方向の結合があると、ループ後に選択範囲が広がり、全データが消去されていました。

しかし、その点を修正しても無限ループでエクセルフリーズ…。
とりあえず↓の自分で組んだVBAがありますし、これ以上お手を煩わせる訳にはいきませんので、
自分なりに研究していくことにします。

自分はネストの概念やマクロ・VBAの存在を知りませんでしたが、あなたのおかげで世界が広がりました。
とても感謝しています。

回答No.5

keithin

＞その範囲に含まれる項目それぞれについて検索し、個別に合計範囲B2:B5の値を加算する

アメの個数を合算する×アメの単価を掛ける＝アメの合計金額が求まる
を、品目ごとにそれぞれ求めて全体を合計する
というだけです。アメが無ければアメの個数合計はゼロ個ですから、金額もゼロ円です。
合算の対象はご質問の画像の上半分の品目一覧じゃなく、「品目金額表に記載のある品目」が基準になっている事に留意してください。

マクロが使いたいのなら
　金額表がＡ１８：Ｂ２１にあるとして
　Ｄ２から結合セルが開始しているとして

sub macro1()
　dim h as range
　range("D2").select

’Ａ列にデータがある範囲で
　do until cells(activecell.row, "A") = ""
　selection.clearcontents

’Ｄ列の結合セルごとに
　　for each h in selection

　’上から順に個数×金額を合算していく
　　activecell = activecell + cells(h.row, "B") * application.vlookup(cells(h.row, "A"), range("A18:B21"),2,false)
　　next
　　selection.offset(1).select
　loop
end sub

とかでも十分です。
こちらのやり方は前述と逆に、ご質問画像上半分の表の品目一覧が集計基準になっているのが違ってる事に留意してください。
言い換えると「金額一覧にない品目が載っている」と、おかしなことになります。

補足

何度もお答え頂きありがとうございます。
上記のマクロを実行してみたところ、
vlookupを使用している行にて「型が一致しません。」のエラーが発生してしまいます。

また、

’Ｄ列の結合セルごとに
　　for each h in selection

の箇所についてですが、ここでのselectionの対象は、初めに選択しているD2ということでしょうか。
for eachの処理の対象なので複数セルの範囲かと思いましたが、
そうするとD2を選択後、for eachまでのどこかで選択範囲を変更しているのでしょうか。

自分の勉強不足故にすんなりと理解できず、ご迷惑をおかけしますが、
選択範囲とエラーについてご教授いただけると嬉しく思います。

一往、vlookup等を利用することで複雑な処理せずとも為し得ますよ
という肝は理解したつもりです。

回答No.4

tom04

こんばんは！
VBAでの一例です。
D列の結合セル行数がバラバラでも対応できるようにしてみました。

前提条件として
（1）↓の画像のようにSheet1のD列は必ず結合されていて、結合されている行が一つのまとまりとする。
（2）Sheet2のA・B列に単価表を作成しておく。
（3）両Sheetともデータは2行目からある。
（4）SUMIFS関数を使用していますので、Excel2007以降のバージョンである。

以上の条件で・・・
Alt＋F11キー　→　メニュー　→　挿入　→　標準モジュール　→　VBE画面に
↓のコードをコピー＆ペースト　→　Excel画面に戻りマクロを実行してみてください。
（Alt＋F8キー　→　マクロ　→　マクロ実行です）

Sub Sample1() 'この行から
Dim i As Long, k As Long, endRow As Long, wS As Worksheet
Set wS = Worksheets("Sheet2")

Application.ScreenUpdating = False
With Worksheets("Sheet1")
endRow = .Cells(Rows.Count, "A").End(xlUp).Row
For i = 2 To endRow
With .Cells(i, "D")
.Value = .Row
End With
Next i
.Range("A:A").Insert
For i = 2 To endRow
If .Cells(i, "E") <> "" Then
.Cells(i, "A") = .Cells(i, "E")
Else
.Cells(i, "A") = .Cells(i - 1, "A")
End If
Next i
.Range("A:A").AdvancedFilter Action:=xlFilterInPlace, unique:=True
.Range("A:A").Copy wS.Range("D1")
.ShowAllData
Range(.Cells(2, "E"), .Cells(endRow, "E")).ClearContents
For i = 2 To wS.Cells(Rows.Count, "D").End(xlUp).Row
For k = 2 To wS.Cells(Rows.Count, "A").End(xlUp).Row
wS.Cells(i, "E") = wS.Cells(i, "E") + WorksheetFunction.SumIfs(.Range("C:C"), _
.Range("A:A"), wS.Cells(i, "D"), .Range("B:B"), wS.Cells(k, "A")) * wS.Cells(k, "B")
Next k
Next i
For i = 2 To wS.Cells(Rows.Count, "D").End(xlUp).Row
.Cells(wS.Cells(i, "D"), "E") = wS.Cells(i, "E")
Next i
.Range("A:A").Delete
wS.Range("D:E").Clear
End With
Application.ScreenUpdating = True
End Sub 'この行まで

こんな感じではどうでしょうか？m(_ _)m

補足

返事が遅れてすみません。
頂いた回答を理解するために、マクロとVBAを一から学んできました。
が、五日間のにわか勉強では、中ほどの「.ShowAllData」辺りから何が起こっているのかよく理解できませんでした。
個別のオブジェクト（？）やステートメント（？）単位ではまだ何とか理解できたのですが、
全体の流れをつかめないと言ったところです。

お忙しい中お手数おかけしてまでせっかく提供して頂いたものを使用しないのも申し訳ないのですが、
理解できない中で予期せぬ事態が発生しても対応できないため、また後学のために
頂いた記述と、他の回答者さんからのアドバイスも参考に、
自分で理解できる程度の自分なりのVBAを記述してみました。
大変勉強になり、なにより自分に勉強するきっかけをくださったことに感謝します。

VBAを組み立てる中で疑問が生じましたので、差し支えなければ一点ご教授いただければと思います。

前提条件として
（1）D列は必ず結合されていない。
（2）合計金額を算出するまとまりごとに罫線を引いてある。
（3）両Sheetともデータは2行目からある。
（4）単価表は、質問時の画像の位置から F1:G5 へ移動してある。
（5）SUMIFS関数を使用しているので、Excel2007以降のバージョンである。

Sub formula使用()
Dim endrow As Long, i As Long, a As Long
Application.ScreenUpdating = False
endrow = Cells(Rows.Count, "B").End(xlUp).Row
i = 2
a = 1
Do While i <= endrow
If Cells(i, "D").Borders(xlEdgeBottom).LineStyle = xlContinuous Then
Cells(i, "D").Formula = "=SumProduct(SumIf(A" & a + 1 & ":A" & i & ",F2:F5,B" & a + 1 & ":B" & i & "),G2:G5)"
a = i
End If
i = 1 + i
Loop
End Sub

実行してみるとうまくいきました。
上記のものではFormulaを使っていますので、セルには関数が記入されます。

元は
Cells(i, "D") = _
WorksheetFunction.SumProduct(WorksheetFunction.SumIf(Range("A" & a + 1 & ":A" & i), _
Range("F2:F5"), Range("B" & a + 1 & ":B" & i)), Range("G2:G5"))

のように関数自体ではなく、関数で得た値をセルに入力しようとしていました。
ですがどうも 「型が一致しません」 というエラーに見舞われてしまいます。
関数の中に関数を組み込むのがうまくいっていないのでしょうか。
宜しければお願いします。

回答No.3

bunjii

回答No.1についても内側のSUMIF関数で範囲（A2:A5、B2:B5）を他の集計範囲（A6:A9）へコピーすると誤りが生じますので各集計範囲で照合範囲と集計範囲を定義し直す必要があります。
また、単価を抽出する範囲（A16:A19、B16:B19）は絶対アドレス（$A$16:$A$19、$B$16:$B19）を使用しないと他の集計範囲に式をコピーできません。
単純にコピーすると失敗しますので注意してください。

これらのことを考慮すれば回答No.1が最も有効な手段と評価できます。

回答No.2

bunjii

>1セル中の全てのSUMIFの検索範囲を統一する便利な方法はないでしょうか。
集計範囲が異なるのでコピーすると正しい結果を得られません。
最大の行数に合わせて空欄の行を増やせば1つの式を他の集計範囲へコピーできます。

貼付画像は提示された集計範囲の大きい方に合わせて作成しました。
SUMIF関数を加算する手段はSUM関数で対応すれば式のデバックが容易になります。

=SUM(SUMIF(A2:A6,$A$16,B2:B5)*$B$16,SUMIF(A2:A6,$A$17,B2:B5)*$B$17,SUMIF(A2:A6,$A$18,B2:B5)*$B$18,SUMIF(A2:A6,$A$19,B2:B5)*$B$19)

お礼

関数内にさらに関数（この場合ではSUM内にSUMIF）を配置するのは、初心の自分にとっては新鮮でした。
今まではこのような使い方はできていなかったので、とても勉強になりました。
ありがとうございます。

空欄を設ける点については、質問では省略して述べていませんでしたが、
実は品名の項目が1行のみの箇所から、10行程度の箇所まで様々です。
ですので、全てを10に合わせると少々上下方向に長くなってしまいますが、大変参考になりました。