
画像のように太線での区切りごとの合計金額を算出したいと思います。
一旦品名ごとに
お菓子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ベストアンサー
- 回答日時:
=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
- 回答日時:
>vlookupを使用している行にて「型が一致しません。
」のエラーが発生してしまいます。既に回答済みですが
再掲:
>言い換えると「金額一覧にない品目が載っている」と、おかしなことになります。
つまり「データが間違ってる」のが直接の原因なので,あなたのエクセルのデータをしっかり確認して下さい。
マクロ以前に,最初に回答した関数による方式でも,「見た目意図した計算結果と違う」ことは確認できるはずです。
>どこかで選択範囲を変更しているのでしょうか。
んーと??こちらもマクロの中に回答が既にありますが,
selection.offset(1).select
としてありますが?
エラーが起こったので,そこで見るのを止めたんでしょうか。
ご返答ありがとうございます。
品目をアメだけ、あるいはガムだけにしてもまだ金額一覧にない品目が載っているというのか、
と思っていたら、おっしゃる通りデータをよく確認すると
質問時の画像の「以下略」のセルが結合されたままであるのが原因でした。
横方向の結合があると、ループ後に選択範囲が広がり、全データが消去されていました。
しかし、その点を修正しても無限ループでエクセルフリーズ…。
とりあえず↓の自分で組んだVBAがありますし、これ以上お手を煩わせる訳にはいきませんので、
自分なりに研究していくことにします。
自分はネストの概念やマクロ・VBAの存在を知りませんでしたが、あなたのおかげで世界が広がりました。
とても感謝しています。
No.5
- 回答日時:
>その範囲に含まれる項目それぞれについて検索し、個別に合計範囲B2:B5の値を加算する
アメの個数を合算する×アメの単価を掛ける=アメの合計金額が求まる
を、品目ごとにそれぞれ求めて全体を合計する
というだけです。アメが無ければアメの個数合計はゼロ個ですから、金額もゼロ円です。
合算の対象はご質問の画像の上半分の品目一覧じゃなく、「品目金額表に記載のある品目」が基準になっている事に留意してください。
マクロが使いたいのなら
金額表がA18:B21にあるとして
D2から結合セルが開始しているとして
sub macro1()
dim h as range
range("D2").select
’A列にデータがある範囲で
do until cells(activecell.row, "A") = ""
selection.clearcontents
’D列の結合セルごとに
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を使用している行にて「型が一致しません。」のエラーが発生してしまいます。
また、
’D列の結合セルごとに
for each h in selection
の箇所についてですが、ここでのselectionの対象は、初めに選択しているD2ということでしょうか。
for eachの処理の対象なので複数セルの範囲かと思いましたが、
そうするとD2を選択後、for eachまでのどこかで選択範囲を変更しているのでしょうか。
自分の勉強不足故にすんなりと理解できず、ご迷惑をおかけしますが、
選択範囲とエラーについてご教授いただけると嬉しく思います。
一往、vlookup等を利用することで複雑な処理せずとも為し得ますよ
という肝は理解したつもりです。
No.4
- 回答日時:
こんばんは!
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
- 回答日時:
回答No.1についても内側のSUMIF関数で範囲(A2:A5、B2:B5)を他の集計範囲(A6:A9)へコピーすると誤りが生じますので各集計範囲で照合範囲と集計範囲を定義し直す必要があります。
また、単価を抽出する範囲(A16:A19、B16:B19)は絶対アドレス($A$16:$A$19、$B$16:$B19)を使用しないと他の集計範囲に式をコピーできません。
単純にコピーすると失敗しますので注意してください。
これらのことを考慮すれば回答No.1が最も有効な手段と評価できます。
No.2
- 回答日時:
>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に合わせると少々上下方向に長くなってしまいますが、大変参考になりました。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Excel(エクセル) エクセル 条件に合う日付に入力された時間数の合計したい 4 2022/06/17 22:18
- その他(Microsoft Office) SUMIFとCOUNTIFの違いについて 4 2022/09/29 14:13
- Excel(エクセル) SUMIFのIF分岐について 4 2023/04/15 12:57
- Excel(エクセル) SUBTOTAL SUMIF?? 2 2023/03/16 11:25
- Excel(エクセル) Excelで特定の文字列を判定し計算する数式を教えて下さい。 1 2022/05/01 12:04
- Excel(エクセル) エクセルの関数いれれますか? 1 2022/03/25 20:39
- Excel(エクセル) スプレッドシート 関数で集計したい 2 2023/01/08 17:09
- Excel(エクセル) 単価シートから単価をエクセル関数で自動取得する方法 1 2023/07/02 22:00
- Excel(エクセル) スプレッドシートの関数 2 2022/11/16 17:36
- その他(お金・保険・資産運用) 至急!【Wolt】各メニューの価格設定の簡単な計算方法 3 2023/03/05 11:58
このQ&Aに関連する記事
関連するカテゴリからQ&Aを探す
おすすめ情報
- ・漫画をレンタルでお得に読める!
- ・一番好きなみそ汁の具材は?
- ・泣きながら食べたご飯の思い出
- ・「これはヤバかったな」という遅刻エピソード
- ・初めて自分の家と他人の家が違う、と意識した時
- ・いちばん失敗した人決定戦
- ・思い出すきっかけは 音楽?におい?景色?
- ・あなたなりのストレス発散方法を教えてください!
- ・もし10億円当たったら何に使いますか?
- ・何回やってもうまくいかないことは?
- ・今年はじめたいことは?
- ・あなたの人生で一番ピンチに陥った瞬間は?
- ・初めて見た映画を教えてください!
- ・今の日本に期待することはなんですか?
- ・集中するためにやっていること
- ・テレビやラジオに出たことがある人、いますか?
- ・【お題】斜め上を行くスキー場にありがちなこと
- ・人生でいちばんスベッた瞬間
- ・コーピングについて教えてください
- ・あなたの「プチ贅沢」はなんですか?
- ・コンビニでおにぎりを買うときのスタメンはどの具?
- ・おすすめの美術館・博物館、教えてください!
- ・【お題】大変な警告
- ・洋服何着持ってますか?
- ・みんなの【マイ・ベスト積読2024】を教えてください。
- ・「これいらなくない?」という慣習、教えてください
- ・今から楽しみな予定はありますか?
- ・AIツールの活用方法を教えて
- ・最強の防寒、あったか術を教えてください!
- ・歳とったな〜〜と思ったことは?
- ・モテ期を経験した方いらっしゃいますか?
- ・好きな人を振り向かせるためにしたこと
- ・スマホに会話を聞かれているな!?と思ったことありますか?
- ・それもChatGPT!?と驚いた使用方法を教えてください
- ・見学に行くとしたら【天国】と【地獄】どっち?
- ・これまでで一番「情けなかったとき」はいつですか?
- ・この人頭いいなと思ったエピソード
- ・あなたの「必」の書き順を教えてください
- ・14歳の自分に衝撃の事実を告げてください
- ・人生最悪の忘れ物
- ・あなたの習慣について教えてください!!
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
Excel 偶数月の15日(土日祝...
-
Excelの数式について教えてくだ...
-
Excelのメニューについて
-
VLOOKUP FALSEのこと
-
エクセル内に読み込んが画像の...
-
【マクロ】1回目の実行後、2...
-
勤務外時間を出す表が作りたい
-
Excelで作成した出欠表から日付...
-
エクセルの数式について教えて...
-
【マクロ】参照渡しとモジュー...
-
Excelの条件付書式について教え...
-
【マクロ】シート追加時に同じ...
-
マクロを実行すると、セル範囲...
-
【マクロ】参照渡しについて。...
-
Excel 日付の表示が直せません...
-
エクセルで、数字の下4桁の0を...
-
【マクロ】Call関数で呼び出し...
-
別のシートの指定列の最終行を...
-
Excelのデーターバーについて
-
Excelでの文字入力について
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
【マクロ】重複する同じ行を、...
-
Excelの条件付き書式のコピーと...
-
vba 印刷設定でのカラー印刷と...
-
VBA の単語の意味を教えて下さい。
-
Excel 日付の表示が直せません...
-
エクセル 同じ行の隣り合う数字...
-
エクセル条件付き書式について。
-
エクセルの数式につきまして
-
ファイル名の変更
-
エクセル 数字のみ抽出につて
-
Excelの開始ブックを固定したい...
-
エクセルの数式について教えて...
-
エクセルのセルをクリックする...
-
=INDIRECT(RIGHT(CELL("filenam...
-
エクスプローラーで見ることは...
-
Excelの関数で質問です
-
至急お願いいたします 屋上の備...
-
エクセルでセルに入力する前は...
-
関数を教えて下さい
-
Excel 関数での質問です
おすすめ情報