WorksheetFunction.SumIfを使用して、複数シートから条件別にデータ抽出、それを集計シートに転記したい

Question

前提・実現したいこと

当方、マクロの勉強を始めて１週間です。
急遽、下記のような集計をすることになりました。
期限内に作業を完成させるべくマクロを使用したいと思って色々と調べていますが、なかなかうまく動かすことができません。
そこで皆様のお知恵をお借りできたらと思い、質問させていただきます。
どうぞよろしくお願いいたします。（※編集機能で初心者マークを追加しました。）

「実現したいこと」
エクセルのマクロを使用して、１０ある部署の売り上げを商品別に一枚のシートに集計したいと思っています。(実際にはもっとたくさんの部署があります)

そこでWorksheetFunction.SumIfを使用して、複数シートからデータ抽出、それを集計シートに転記しようと考えました。

「シートの構成と内容」
・wstemplate(集計用シート、Ａ列に商品コード一覧、Ｂ列から列ごとに１０の部署が入っている（ＢＣ列が関東、ＤＥ列が東北など）
・wsdata（部署別の売り上げで10枚に分かれている）

wstemplateのＡ列の商品コードと、全てのwsdataの商品コード（k2:k400）をWorksheetFunction.SumIfで冊数（wsdataのw2:w400）と売上金額（wsdataのx2:x400)で条件抽出し、合計をwstemplateのＢ列（冊数合計）とＣ列（売上金額合計）：Ｂ列とＣ列でひとつの部署の集計としている）、Ｅ列とＦ列、以下全部署の列に転記したい。

発生している問題・エラーメッセージ

以下に記載したコードで、ひとつのwsdataからなら抽出、転記できたのですが、すべてのwsdataシートから一括で条件抽出することができていません。条件はすべてのシートで同じです。

そこで質問なのですが、すべてのシートの条件抽出を一度にやって、それを指定の各列に転記するというのは一つのマクロで可能でしょうか？
以下の該当のソースコードではひとつひとつ転記場所を記述しないといけないので、もっとシンプルなコードが書ければと思いました。

「試したこと」でも書きましたが、「ひとつのwsdataから抽出する」というコードをすべてのシートに適用すること、もしくは上記コードをループして抽出、転記する際に転記場所をずらしていけないか（stepを使う）と考えましたが、このような考え方で希望するマクロができますでしょうか？

プログラムを書くのも初めてなので、考え方や前提条件から間違っているかもしれません。
解決するためのコードをご提示いただかなくても、考え方のアドバイスだけでもいただけますと大変助かります。

どうぞよろしくお願いいたします。

8/30 実現したい集計表のサンプル画像をアップしました。レイアウトは別のものが作り、そのレイアウトは変更できないので、実際にはサンプル画像より商品コードの開始行が違ったりする可能性があります。部署名はＣ列に入力されており、シート内にセル結合は一切ありません。
Ａ２０などの商品区分ごとの合計行は、マクロで商品の集計を行った後、手入力で追加していってもいいと思っています。

VBA

Sub 集計表作成()

'変数を定義する
Dim i As Long  '商品コードの最後の行を求める変数
Dim n As Long '最後の行番号を調べる
Dim m As Long  '商品コード
Dim wstemplate As Worksheet, wsdata As Worksheet

Set wstemplate = Worksheets("wstemplate")
Set wsdata = Worksheets("wsdata")

n = wstemplate.Range("A65536").End(xlUp).Row ' 最後の行を求める

    For i = 4 To n
    m = wstemplate.Range("A" & i)

    '冊数と金額集計
     wstemplate.Range("B" & i) = _
            WorksheetFunction.SumIf(wsdata.Range("k2:k400"), _
            m, wsdata.Range("w2:w400"))

            wstemplate.Range("C" & i) = _
            WorksheetFunction.SumIf(wsdata.Range("k2:k400"), _
            m, wsdata.Range("x2:x400"))
            
            Next

End Sub

試したこと

for each wsdate in worksheetsを使って上記のコードを繰り返し処理を考えました。→すべてのシートから抽出できない。

次に、以下のコードのように変数を使って上記のコードを繰り返すことを考えました。
転記の部分でwsData.Cells(i, j).ValueをWorksheetFunction.SumIf(wsdata.Range("k2:k400"), m（別途商品コード一覧を定義したもの）, wsdata.Range("w2:w400"))に書き換える方法を検討。（その他の部分は適宜wstemplateに合わせる。）→繰り返しがうまくいかない。

rowsData = wsData.Cells(Rows.Count, 1).End(xlUp).Row '最後の行数を取得

Dim i As Long, j As Long, k As Long
k = 21
For i = 2 To rowsData
    For j = 1 To 3
        wsTemplate.Cells(k, j).Value = wsData.Cells(i, j).Value
    Next j
    k = k + 1
Next i

End Sub

###FW/ツールのバージョンなど）

windows10、excel2013を使用

Comments

[tatsu99]

wsdataというシートは部署ごとに１０個あるわけですよね。
その具体的な名称はなんでしょうか。提示されたマクロでは、"wsdata"となっていますが、
これは１０個のうちの１つなのですか。
又、部署は実際にはもっとたくさんあるとも書いてますが、そうすると、wsdataも１０個ではなくて
もっとたくさんあるのですか？

[qmk]

ご覧頂きありがとうございます！
wsdataの件は、ご指摘のとおりです。
wsdataシートはwsdata1,wsdata2...と数字で分けて、すべての部署の数だけあります。

[tatsu99]

部署名とwadataとの関連が明確になっていれば、可能です。
例えば、
B列の部署名＝部署1
C列の部署名＝部署2
D列の部署名＝部署3
とするとき、
wsdataのシートの名称が、ws部署1、ws部署2、ws部署3、のようになっていれば、
wsdataのシート名＝"ws"＋部署名　で関連付けができます。

[tatsu99]

部署名とwsdataを紐付ける規則はないのですか？

[qmk]

何度もご質問いただいてしまって申し訳ありません。
部署名とシート名は連動しています。
自分で調べてマクロを書いてみていた時に参考にしたものがwsdataになっていたので、そのまま使用していました。実際には、北海道一営業部、北海道二営業部、東北…などとしようと思っています。

[tatsu99]

>部署名とシート名は連動しています。
どのように連動していますか。部署名とシート名は一致しているということですか。
部署名＝北海道一営業部　のとき
シート名＝北海道一営業部　が対応する。

[qmk]

そのとおりです！分かりにくい説明になってしまってすみません。

[tatsu99]

B,C列のB列へ冊数、C列へ金額をセットする場合、部署名はどこに格納されていますか。
B1、C1のどちらでしょうか（それともほかの行B2,B3,C2,C3等）。レイアウトがよくわかりません。

[qmk]

分かりにくい説明になってしまって申し訳ありません。
上記「シートの構成と内容」にも書きましたが、合計を転記するのはwstemplateのＢ列（冊数合計）とＣ列（売上金額合計）としていまして、Ｂ列とＣ列でひとつの部署の集計となっています。
レイアウトのスクリーンショットを添付できなくて申し訳ないのですが、レイアウトの列を表記すると、wstemplateシートのA列=商品コード、B列=商品コードに対する北海道一営業部の冊数合計、C列=商品コードに対する北海道一営業部の売上合計金額、D列=商品コードに対する北海道二営業部の冊数合計、E列=商品コードに対する北海道二営業部の売上合計金額、以下すべての部署の冊数合計と売上合計金額、というレイアウトになっています。
冊数と売上金額の集計をするための元データは各wsdataのk列（商品コード）を元に、w列とx列を参照する形です。

明確にお伝えすることができず申しわけありません。上のような書き方で、レイアウト等お伝えできますでしょうか？

どうぞよろしくお願いいたします。

[tatsu99]

回答のほうにも書きましたが、レイアウトを画像で提示してください。
質問の意図は、
部署名が記入されているセルの位置が知りたい。（結合セルになっているかどうかも含めて）
部署の数を知りたいために、最大の列を知る必要があるが、どの行の最大列を求めればよいかを知りたい。
ということです。

[qmk]

頂いた回答へのコメントと同じ内容で失礼いたします。
明日になり申しわけありませんが、画像でレイアウトをご提示させていただきます。

マクロを作成するにあたって、何の情報が必要かという部分の分かりが悪く、頓珍漢な返信を差し上げているかと思い恐縮です。
どうぞよろしくお願いいたします。

Answer 1

以下のようにしてください。
wstemplateの３行目の部署名のセルは結合セル（CD,EF,・・・）である。
wstemplateの合計行（20行等）のB列（商品コード）は空白である。
上記の前提で作成しています。

Option Explicit

Sub 集計表作成()

'変数を定義する
    Dim maxrow As Long    '最後の行番号を調べる
    Dim maxcol As Long    '最後の行番号を調べる
    Dim wrow As Long       '行番号
    Dim wcol As Long       '列番号
    Dim busho_count As Long '部署数
    Dim i As Long
    Dim scode As String     '商品コード
    Dim wstemplate As Worksheet, wsdata As Worksheet

    Set wstemplate = Worksheets("wstemplate")

    maxrow = wstemplate.Cells(Rows.Count, "B").End(xlUp).Row    ' B列最後の行を求める
    maxcol = wstemplate.Cells(3, Columns.Count).End(xlToLeft).Column   '3行目の最終列を求める
    If maxcol Mod 2 = 0 Then
        MsgBox ("３行最終列不正")
        Exit Sub
    End If
    busho_count = (maxcol - 1) \ 2
    For i = 1 To busho_count
        wcol = 3 + (i - 1) * 2
        Set wsdata = Worksheets(wstemplate.Cells(3, wcol).Value)
        For wrow = 6 To maxrow
            scode = wstemplate.Cells(wrow, "B").Value
            If scode <> "" Then
                '冊数と金額集計
                wstemplate.Cells(wrow, wcol).Value = _
                WorksheetFunction.SumIf(wsdata.Range("k2:k400"), _
                                        scode, wsdata.Range("w2:w400"))
                wstemplate.Cells(wrow, wcol + 1).Value = _
                WorksheetFunction.SumIf(wsdata.Range("k2:k400"), _
                                        scode, wsdata.Range("x2:x400"))
    
            End If
        Next
    Next
    MsgBox ("完了")
End Sub

Comments

[qmk]

コードをご提示くださり本当にありがとうございます！

しかし、以下の部分で、エラーが出てしまいました。
「実行時エラー９　インデックスが有効範囲にありません」
Set wsdata = Worksheets(wstemplate.Cells(3, wcol).Value)

この部分は、wstemplateの3行目の最終列の値（部署の数）をwsdataに代入するという意味であってますでしょうか？

私のwstemplateのレイアウトがずれていたりして参照する範囲が間違っているのだろうと思うのですが、自力で間違いを見つけることができませんでした。（部署ごとのシートのオブジェクト名をwsdata1,wsdata2,wsdata3としましたが、この部分が誤りでしょうか？）

お手数をおかけしてしまい大変申し訳ありませんが、再度ご教授いただけますと幸いです。どうぞよろしくお願いいたします。

[tatsu99]

>この部分は、wstemplateの3行目の最終列の値（部署の数）をwsdataに代入するという意味であってますでしょうか？
wcolは処理中の部署の列です。（最終列ではありません）
wcolの値はいくつになってますか。実行エラーで停止時、wcolにマウスをあてると値が表示されます。
その値が例えば、５とすると、3行5列（E3）の内容がその部署名になっています。
その部署名に一致するシートがないとエラーになります。

[tatsu99]

シート名は、北海道-営業、埼玉-営業のようになっていないとエラーになります。

[tatsu99]

>何度もご質問いただいてしまって申し訳ありません。
>部署名とシート名は連動しています。
>自分で調べてマクロを書いてみていた時に参考にしたものがwsdataになっていたので、そのまま使用して>いました。実際には、北海道一営業部、北海道二営業部、東北…などとしようと思っています。
以前のQ&Aであなたは、このように述べています。
シート名は、wsdata1,wsdata2,wsdata3にしてはいけません。

[qmk]

wstemplateの部署名とシート名を一致させたところ、希望通りの集計をすることができました！
何度もご教授くださり本当に本当にありがとうございました！

上記のように、どの部分を一致させないといけないのか、この変数は何を表しているのか、などといった基本的な部分もまだよく分かっていないので、ご提示くださったコードを理解できるように良く勉強して、自分でこのコードをメンテナンスできるように頑張ります。

重ねてお礼申し上げます。本当にありがとうございました！

Answer 2

このような考え方で希望するマクロができますでしょうか？

自分で一からアプリを作る覚悟があるなら、やりたいように作ればいいです。
でも、僕なら、せっかくエクセルを使っているのですから、
エクセル君に任せられるところは、「お願い！」って任せますけどね。
具体的に言えば、エクセルは1つのシート上の1つの固まったセル範囲に、
1行1件でデータが並んでいることを想定されて色々な機能が用意されているので、
それらの機能が使えるようにデータをシート上に配置整形して、
お願い！って書きます。

---

ひとつのwsdataからなら抽出、転記できたのですが、
すべてのwsdataシートから一括で条件抽出することができていません。

んと、手動の時でもマクロで自動でやるときも同じなのですが、
途中経過を空いているセルや作業用のシート等に書いてやると、
考え方が簡単になります。

今回の件の場合。
各シートのSumifの結果を合計すれば（または累計していけば）、
いいのではないですか？

┌─────┬──────┬─────┬────┬─────┬──┐
│商品コード│北海道-営業 │埼玉-営業 │東京-総務 │大阪-営業 │合計│
├─────┼──────┼─────┼────┼─────┼───┤
│ A000100 │ 1 　 │ 1 　 │ 1 　 │ 1 　 │ 4 　 │
├─────┼──────┼─────┼─────┼─────┼──┤
│ A000202 │ 2 　 │ 2 　 │ 2 　 │ 2 　 │ 8 │
├─────┼──────┼─────┼─────┼─────┼──┤
│ A000300 │ 3 　 │ 3 　 │ 3 　 │ 3 　 │ 12 │
├─────┼──────┼─────┼─────┼─────┼──┤
│ B000101 │ 4 　 │ 4 　 │ 4 　　 │ 4 　 │ 16 │
├─────┼──────┼─────┼─────┼─────┼──┤
│ B000555 │ 5 　 │ 5 　 │ 5 　 │ 5 　 │ 20 │
└─────┴──────┴─────┴─────┴─────┴──┘

---

期 営業所 商品区分 商品Co 数量 単価 金額
70 北海道1営業 B 100101 60 500 30000
70 北海道2営業 A 100104 60 400 24000
70 東北2営業 A 100107 80 700 56000
70 東北1営業 A 100105 100 500 50000
70 北海道1営業 B 100103 50 500 25000
70 東北2営業 B 100106 100 700 70000
70 北海道2営業 A 100102 50 200 10000
70 東北1営業 B 100104 80 700 56000
70 北海道1営業 B 100105 60 500 30000
70 北海道2営業 A 100106 50 600 30000
70 東京本社 A 100101 100 100 10000
70 東北1営業 A 100103 80 300 24000
70 東京本社 B 100102 100 700 70000

こういった表なら、ピボットテーブルで、

![]

難しく考えなくても期待した表ができるので、
そういうリスト（データベース）を作る方に注力するのもありかなぁと思いますが、
最終の出来上がりの表を気に入ったように編集するのも結構大変なので、
（とはいえ一回マクロを作ってしまえば、後は一瞬でお手軽に出力できるのですが）
数式を表に埋め込んでしまえば、マクロは要らないですし、
シート上の数式がじゃまなら、一旦数式を自動で入力し、
後で値に直すようなマクロを作るといいかなと思いますが、いかがでしょうか？
その辺は、いろいろな制約や開発者の思いもあると思いますので、
どれが良いとは言い切れませんが、
他の回答者の意見も参考にしながら、アプリの設計を考えて行かれたらいいと思います。

Comments

[qmk]

ご丁寧に、図示下さりありがとうございます。

希望するマクロの説明の部分では、言葉が足りず申し訳ありません。
私もエクセルの機能内で希望するマクロが組めればと思って、質問いたしました。全体的にエクセルの知識も足りていないため、エクセルに任せるためのデータのつくり方でも右往左往している状態です。

途中経過を別のシートに書き出していく方法をご提示くださり、ありがとうございます。
確かに、今回は一度にすべてやろうとせずに、部署のシートごとにsumifで商品コード別に集計し、その結果を列指定して集計表に反映させる方法を考えてみます。

[qmk]

追加でご提案くださりありがとうございます！

集計元のデータはデーターベースからＤＬできるものなので、ご指摘いただいた通りピボットテーブルで集計できれば、今回このように慌てなくてよかったのですが、この集計を取りまとめる方がピボットテーブルが使えないということで（自分でピボットを使って表の編集ができない）、ピボットは却下になりました…。

今まで集計していた方はセルにひとつひとつ関数を入力するという手法で集計していたそうですが、そうするとデータが重すぎて動かない状態でした。そこで今回、マクロを使えればと思った次第です。

追加でご提案いただいたように、マクロで一度にセルに関数を入力して集計、その後値に変換、という方法もいいなと思ったので、こちらのマクロも作れるように頑張ってみます。

社内にこういったマクロやエクセルで扱いやすいデータのつくり方などを質問できる方がいないため、今回いくつかご提案いただいたこと、大変勉強になりました。どうもありがとうございました！

Answer 3

添付画像のようなwstemplateのレイアウトを提示していただけませんでしょうか？
画像は、アクセサリのsnipping toolでキャプチャ可能です。（これもsnipping toolを使っています）

Comments

[qmk]

遅い時間までご覧下さりありがとうございます！
明日になってしまい恐縮ですが、画像を添付してレイアウトをご提示いたします。
どうぞよろしくお願いいたします。