質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

ただいまの
回答率

87.34%

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

解決済

回答 3

投稿 編集

  • 評価
  • クリップ 0
  • VIEW 4,708

score 5

前提・実現したいこと

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

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

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

「シートの構成と内容」
・wstemplate(集計用シート、A列に商品コード一覧、B列から列ごとに10の部署が入っている(BC列が関東、DE列が東北など) 
・wsdata(部署別の売り上げで10枚に分かれている)

wstemplateのA列の商品コードと、全てのwsdataの商品コード(k2:k400)をWorksheetFunction.SumIfで冊数(wsdataのw2:w400)と売上金額(wsdataのx2:x400)で条件抽出し、合計をwstemplateのB列(冊数合計)とC列(売上金額合計):B列とC列でひとつの部署の集計としている)、E列とF列、以下全部署の列に転記したい。

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

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

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

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

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

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

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

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を使用

  • 気になる質問をクリップする

    クリップした質問は、後からいつでもマイページで確認できます。

    またクリップした質問に回答があった際、通知やメールを受け取ることができます。

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 過去に投稿した質問と同じ内容の質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

質問への追記・修正、ベストアンサー選択の依頼

  • qmk

    2019/08/29 20:19

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

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

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

    キャンセル

  • tatsu99

    2019/08/29 21:56

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

    キャンセル

  • qmk

    2019/08/29 22:19

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

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

    キャンセル

回答 3

checkベストアンサー

+1

以下のようにしてください。
wstemplateの3行目の部署名のセルは結合セル(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 ("3行最終列不正")
        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

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2019/08/30 14:53

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

    キャンセル

  • 2019/08/30 15:02

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

    キャンセル

  • 2019/08/30 15:56

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

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

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

    キャンセル

+1

>このような考え方で希望するマクロができますでしょうか?
自分で一からアプリを作る覚悟があるなら、やりたいように作ればいいです。
でも、僕なら、せっかくエクセルを使っているのですから、
エクセル君に任せられるところは、「お願い!」って任せますけどね。
具体的に言えば、エクセルは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

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

![イメージ説明]

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

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2019/08/30 10:14

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

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

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

    キャンセル

  • 2019/08/30 14:35

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

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

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

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

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

    キャンセル

0

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

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2019/08/29 20:29

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

    キャンセル

15分調べてもわからないことは、teratailで質問しよう!

  • ただいまの回答率 87.34%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる

関連した質問

同じタグがついた質問を見る

  • トップ
  • VBAに関する質問
  • WorksheetFunction.SumIfを使用して、複数シートから条件別にデータ抽出、それを集計シートに転記したい