VBAで項目ごとに数量や合計額を集計する方法

これまでしばらく英語関連の記事が続いていましたが、久々にVBAについて取り上げていきたいと思います。

今回は数量や合計額を項目ごとに集計する方法について見ていきます。

下のエクセル画像をご覧ください。

イメージとしては鉛筆や消しゴムといった【品名】があり、その隣に【数量】が記載されている状況で、

バラバラに記載されている各品名をまとめて、数量を合計して集計する、というものです。

実際の業務でもこういった処理を行う必要が比較的あるのではないかと思いますので、

VBAにおける一つの記述例を紹介したいと思います。

今回紹介するVBAを記述することによって、ボタン一つで上記のように品名ごとの数量の集計が可能になります。

完成コード
まとめ

完成コード

後ほど解説をしようと思いますが、まずは完成したコードを紹介します。

Sub Total()

Dim i As Long, Total, ItemName

'連想配列を宣言("品名"をキー、"数量"をデータとする)
Set Total = CreateObject("Scripting.Dictionary")

'最終行を求める 
LastRow = Cells(Rows.Count, "A").End(xlUp).Row

'"Sheet1"の部分は、その時のシート名に合わせて都度変更
With Sheets("Sheet1")

    'A1セルの見出しをコピーして、D1セルに貼り付け
    .Range("A1").Copy
    .Range("D1").PasteSpecial (xlPasteFormats)
    
    .Range("D1") = .Range("A1")
    
    'B1セルの見出しをコピーして、E1セルに貼り付け
    .Range("B1").Copy
    .Range("E1").PasteSpecial (xlPasteFormats)
    
    .Range("E1") = .Range("B1")
     
    Application.CutCopyMode = False

    For i = 2 To LastRow
    
        ItemName = .Cells(i, "A")      
                           '品名　　　　　　       '数量
        Total(ItemName) = Total(ItemName) + .Cells(i, "B")
        
    Next
    
    '配列を縦方向の各セルに代入する Resize(引数)の形であれば引数の数の行に変更する
    .Cells(2, "D").Resize(Total.Count) = Application.Transpose(Total.keys)
    .Cells(2, "E").Resize(Total.Count) = Application.Transpose(Total.items)
    
    'フレームをつける
    .Range("D1").CurrentRegion.Borders.LineStyle = True
    
    'シート全体の列幅を自動調整
    Cells.EntireColumn.AutoFit
    
End With

End Sub

集計に直接関係のないフレームをつける処理や列幅を自動調整する処理が含まれていますが、

ひとまずは上記のコードで、品名ごとに数量をまとめて、集計することが可能です。

よって上記のコードでほぼ項目ごとに数量や合計額を集計する処理は完了します。

配列用の変数を用意

項目別に集計するために、連想配列を用意します。

Dim i As Long, Total, ItemName
'連想配列を宣言("品名"をキー、"数量"をデータとする)
Set Total = CreateObject("Scripting.Dictionary")

【CreateObject(“Scripting.Dictionary”)】で、Dictionaryオブジェクトを宣言し、変数Totalに代入します。

項目の見出しをコピー&ペーストする

“品名”と”数量”の見出しをコピーして新しいセルに貼り付けます。

A1セルに”品名”、B1セルに”数量”があるのでコピーして、

それぞれD1セル、E1セルにPasteSpecialでメソッド貼り付けます。

'A1セルの見出しをコピーして、D1セルに貼り付け 
.Range("A1").Copy .Range("D1").PasteSpecial (xlPasteFormats) 
.Range("D1") = .Range("A1")

'B1セルの見出しをコピーして、E1セルに貼り付け 
.Range("B1").Copy .Range("E1").PasteSpecial (xlPasteFormats) 
.Range("E1") = .Range("B1")

元の表の各項目を配列に格納する

元の集計前の表の各項目を配列に格納していきます。

元の表とは赤い四角で囲まれている表です。

For i = 2 To LastRow

  ItemName = .Cells(i, "A") 

                     '品名　　　　　　 '数量 
  Total(ItemName) = Total(ItemName) + .Cells(i, "B")

Next

For文で2行目から表の最後の行(13行目)まで繰り返し処理が行われ、

“ItemName”には”鉛筆”,”消しゴム”,”はさみ”,”ボールペン”がそれぞれ格納されていきます。

そして”Total(ItemName)”でキーに”品名”が、データに”数量”がセットで連想配列に格納されていきます。

格納したキーとデータを縦方向のセルに代入

配列の値を縦(下)方向のセルへ代入するために、Transpose関数を利用します。

keys関数で各”品名”(キー)、items関数で各”数量”(要素)を取り出して、

Transpose関数でセルの縦方向へ代入する処理を施しています。

'配列を縦方向の各セルに代入する Resize(引数)の形であれば引数の数の行に変更する
.Cells(2, "D").Resize(Total.Count) = Application.Transpose(Total.keys) 
.Cells(2, "E").Resize(Total.Count) = Application.Transpose(Total.items)

そしてResize関数で、セル範囲のサイズ変更を行い、Total配列の要素の数だけ行を拡張(縦方向へ移動)しています。(今回は品名が4項目なので、Total.Countは4)

Resize(4)

Resize関数は引数の数で拡張する行・列が変わり、

Resize(num)・・・行を拡張(縦方向へ移動)
Resize(, num)・・・列を拡張(横方向へ移動)

となります。

今回はResize(Total.Count)、すなわちResize(4)になるので、行を拡張する処理、縦方向へ値を入力していくことになります。

引数の分だけ値をセルに入れていくので、for文やforeach文を使うようなイメージです。

このように拡張させたいのは列なのか行なのかを確認して、Resize関数に引数を指定する必要があります。

あとはセルの自動調整やフレームの設定がありますが、これらは直接集計には関係ないので説明は省きます。

まとめ

今回はVBAで項目ごとに数量や合計額を集計する方法について取り上げてきました。

集計はピボットテーブルを行う方が一般的かもしれませんが、VBAでも紹介した内容であればすぐ行うことが可能です。

実際の業務で項目別に集計する作業が生じたら、今回のコードを試しに使ってみてはいかがでしょうか。

普段僕が使用しているVBAのテキストについても掲載しておきます。

リンク

ページ数も多く一見とっつきにくい印象がありますが、説明もわかりやすく想像以上に理解を深めながらスラスラ読んでいくことができます。

基礎から応用までカバーされていて、ユーザーフォームやクラスモジュールといった各項目の網羅度も非常に高いので、

これ一冊でVBAスキルを段階的に高めていくことが可能です。