複数条件の合計のやり方について

Question

|支店|年度|科目|売上|分類|
|:--|:--:|--:|
|
|横浜|2017|作業料|1000|一般|
|埼玉|2017|配送料|5000|特殊|
|横浜|2017|預かり|500|一時|
|千葉|2018|作業料|1500|一般|
|千葉|2018|配送料|1000|特殊|
|埼玉|2018|事務料|1500|一時|
|千葉|2018|配送料|2000|一般|
|本社|2018|作業料|1000|特殊|

仮に上記のようなエクセルファイルがあるとします。
（データ用エクセルブックは20，000行程で6MB近い容量があります。）
これを年度別、支店別、科目別、分類別に集計したい場合
（実際の列は30列程あります）

END文で最終行を取得しfor文でif文をループさせ合計することを考えました。

for i = 2 to 最終行変数
if cells(i,1) = "横浜" _and
if cells(i,2) = "2017" _and
if cells(i,3) = "作業料" _and
if cells(i,5) = "一般" then
sum = sum + cells(i,4)
end if
next

集計結果シートの指定セルにrange分でsumを書き込み

支店、年度などは変数を用意して代入する予定です。

この場合だと集計条件分のfor分が必要になり集計作業時間がかかりそうなことの気が付きました。

このような場合、ピボットテーブルで集計しコピーしたほうがいいのでしょうか？

勉強のためピボットテーブルを使用しないで集計する方法はないものかと考えております。

また、マクロ上で元データブックを開き集計用ブックに一旦コピーしてから集計してから集計後ファイルが重くメール等で送信できない為消去しております。
データファイルを閉じる時に容量が大きい為かエクセルが固まってしまい。5分くらい待つケースがあります。
これは仕方ないことなのでしょうか？

Answer 1

質問の内容からすると勉強のためとのことなので、別回答にあるSUMPRODUCTを使う方法は少し目的と異なるかと思われます。
またSUMPRODUCTでは、あらかじめ集計する項目名（例えば支店の「横浜」）とかがわかっていないと集計できません。
というわけでコードを書いてみました。
連想配列で行ってみましたが、VBAの連想配列はこの手の用途には使いづらいですね。。。
30列もやろうとなったら気が遠くなりそうです。
あと質問の例が全ての行でユニークなので、結果的に集計されていませんがあしからず。

Sub test()
    Set dic1 = CreateObject("Scripting.Dictionary")
    
    For r = 2 To 9
        ' 年度別
        If dic1.exists(Cells(r, 2).Value) Then
            Set dic2 = dic1(Cells(r, 2).Value)
        Else
            Set dic2 = CreateObject("Scripting.Dictionary")
        End If
        ' 支店別
        If dic2.exists(Cells(r, 1).Value) Then
            Set dic3 = dic2(Cells(r, 1).Value)
        Else
            Set dic3 = CreateObject("Scripting.Dictionary")
        End If
        ' 科目別
        If dic3.exists(Cells(r, 3).Value) Then
            Set dic4 = dic3(Cells(r, 3).Value)
        Else
            Set dic4 = CreateObject("Scripting.Dictionary")
        End If
        ' 分類別
        If dic4.exists(Cells(r, 5).Value) Then
            dic4(Cells(r, 5).Value) = dic4(Cells(r, 5).Value) + Cells(r, 4).Value
        Else
            dic4(Cells(r, 5).Value) = Cells(r, 4).Value
        End If
        
        Set dic3(Cells(r, 3).Value) = dic4
        Set dic2(Cells(r, 1).Value) = dic3
        Set dic1(Cells(r, 2).Value) = dic2
    Next
    
    For Each k1 In dic1
        Set dic2 = dic1(k1)
        For Each k2 In dic2
            Set dic3 = dic2(k2)
            For Each k3 In dic3
                Set dic4 = dic3(k3)
                For Each k4 In dic4
                    Debug.Print k1 & " " & k2 & " " & k3 & " " & k4 & " " & dic4(k4)
                Next
            Next
        Next
    Next
    
End Sub

2017 横浜 作業料 一般 1000
2017 横浜 預かり 一時 500
2017 埼玉 配送料 特殊 5000
2018 千葉 作業料 一般 1500
2018 千葉 配送料 特殊 1000
2018 千葉 配送料 一般 2000
2018 埼玉 事務料 一時 1500
2018 本社 作業料 特殊 1000

Comments

[shinyakita]

ご丁寧にコードまで書いていただきありがとうございます。
SUMPRODUCTを使う事で目的は達成できそうですが、仰る通りVBAの勉強中で結果でなくコーディングのコツを身に着けたく、自分で課題を設定しております。結果だけであればピボットテーブル使ってしまいます。連想配列を勉強してみます。やはり30列と20,000行となるとかなり大変な作業ですよね。やはりVBAでフィルターもしくはピボットテーブルで集計したものを集計シートにコピーの手順の方が簡素で集計速度も速いのでしょうか？

[ttyp03]

行数はともかく30列もあると提示したコードだと厳しいですね。
再帰呼び出しとかで工夫すればコード量は抑えられると思いますが、あまりお勧めはしません。
Excelで処理するのであれば、最初から備わっている機能を使ったほうがよいでしょう。
結果も確実ですしね。

[shinyakita]

こういう集計方法はVBAだと難しいのでしょうか？
列は多いのですが、実際集計に必要な列は10行ほどです。
超初心者なので業務で使うようなパターン自分で考えて解を考えております。
エクセルで処理した方が早い事は承知してですがVBAではどうコーディングするのか？
おそらくこのようなパターンは日常的にあると思いますが、VBA部分とエクセル部分と切り分けて作るという事でしょうか？得意、不得意がありますよね。

[ttyp03]

私の場合、Excelの成果物がそのまま仕事の成果物になることはほぼないので、このような集計業務とかをやられている人に意見聞きたいですね。
あったとしても私ならピボット使っちゃうかな。

VBAで難しかったのは、連想配列が多重配列で書けなかったからです。
例えば提示したコードのループ内の処理をPHPに置き換えると、
for(r = 2; r <= 9; r++){
　集計[Cells(r,2)][Cells(r,1)][Cells(r,3)][Cells(r,5)] += Cells(r,4)
}
これくらいのコーディング量でできるので、やはりVBAだと面倒ですね。

[shinyakita]

ありがとうございます。
PHPだとこんなに短くなるのですね。
VBAは面倒面倒ですね。

[sazi]

>ttyp03 さん
エクセルでこういった年度別、支店別、科目別、分類別などという場合、マスタがあるか組み合わせた表が事前にあるのが殆どだと思います。
要は目的の表がすでにあって、集計する項目を埋めていく、という考えのほうが理解されやすい。
自動化されていない場合は、その表に数字を埋めるのを手作業で行うから、事務方の方にとって、それが自然なんだと思います。
そういった意味で、sumproduct()を使用した回答をしました。

[sazi]

また、表を条件にすると必要なものだけを取り出せるというメリットがあります。

[shinyakita]

ありがとうございます。VBAとは別ですが、昨日簡単な表を作成し試してみました。用途によっては便利だと思います。
マスターはホスト上ではもっておりますが、システム担当者がCSVもしくはエクセルとして作成し配布される。またはホスト上から専用のメニューからデータが落とせるようになっております。

[sazi]

別にどの方法でも得手不得手を理解した上で使われれば良いと思いますよ。
単に、説明を兼ねて補足的にコメントしただけですので。

[shinyakita]

はい。
まさにそのとおりですね。なんでも関数、なんでもVBAではなく得手不得手を理解した上でベストなものを選択する事が重要ですね。

Answer 2

ADOを使えばエクセルでもSQLが使えます。下記参考。

Excelの集計をSQLでおこなう方法。VLOOKUP()やSUBTOTAL()の限界を超えろ！！ - むかぁ～ どっと　こむ

今回の要件なら、上記のVBAコードの SQL変数を下記のようにすればOKです。

SQL = "SELECT 支店, 年度, 科目, 分類, Sum(売上) AS [売上計] " & _
      "FROM [A$]"
      "GROUP BY 支店, 年度, 科目, 分類;"

たぶん、速度的に高速だと思います。
これで重いようでしたら、データをAccessなどのデータベースに移行させればいいでしょう。

Comments

[shinyakita]

ありがとうございます。
エクセルでSQL構文が使えるのですね。
SQL構文が使えれば速そうですね。
参考ページを見て検討してみます。

[ttyp03]

データベースに慣れ親しんでいる人なら、これがいいですね。
なかなか使う機会はないですけど覚えておくとよさそう。
ちなみにSELECTのスペルが間違ってます。

[hatena19]

スペル間違いご指摘感謝、あわてて修正(;^ω^)

[shinyakita]

ADOの使い方を勉強してみます。
これなら長いコード書かなくて済みますね。
やはりVBA単体では難しいのですよね？

[hatena19]

ADOを使わずにという意味なら、ttyp03さんの回答のようなコードを書くことになりますね。

[shinyakita]

ありがとうございます。
連想配列（辞書）を使うのですね。
勉強してみます。

Answer 3

高速化は頻出なので、調べればすぐ分かると思いますが、
大きく下記の２点で大幅に処理時間が短縮できるはずです。

・Excel の自動機能を止める(画面更新、数式、イベント)
※処理終了時には、必ず元に戻してください

・データを配列に入れて処理する
※そもそもセルに何回もアクセス(.Value等)しているのが遅いんです。
データ量が多いので、全データが１つの配列に入らない場合は、
列単位で分けて配列に入れたりします。
自分が作った関数ですが、 ↓の fRange_Value を使うと配列の処理がしやすくなります。
http://www.excelsystem.jp/Library/archives/3

ご参考まで。

Comments

[shinyakita]

ありがとうございます。
参考させていただきます。

Answer 4

sumproduct()を使用すれば、式のみで高速に結果が出せます。

集計用の表を作ってその内容を条件とする式を組めば、VBAは不要です。

以下のリンク先だと最後あたりが参考になるかと思います。
SUMPRODUCT関数の使い方

Comments

[shinyakita]

ありがとうございます。
このような関数があることは知りませんでした。
エクセルは奥が深いですね。
VBAの勉強とは別にこの関数も便利そうなので、サイト確認して勉強しようと思います。