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

IT
スポンサーリンク

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

 

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

 

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

 

 

イメージとしては鉛筆や消しゴムといった【品名】があり、その隣に【数量】が記載されている状況で、バラバラに記載されている各品名をまとめて、数量を合計して集計する、というものです。

 

実際の業務でもこういった処理を行う必要が比較的あるのではないかと思いますので、VBAにおける一つの記述例を紹介したいと思います。

 

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

スポンサーリンク

完成コード

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

 

 

集計に直接関係のないフレームをつける処理や列幅を自動調整する処理が含まれていますが、ひとまずは上記のコードで、品名ごとに数量をまとめて、集計することが可能です。

 

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

配列用の変数を用意

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

 

 

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

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

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

 

A1セルに”品名”、B1セルに”数量”があるのでコピーして、それぞれD1セル、E1セルにPasteSpecialでメソッド貼り付けます。

 

 

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

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

 

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

 

 

For文で2行目から表の最後の行(13行目)まで繰り返し処理が行われ、”ItemName”には”鉛筆”,”消しゴム”,”はさみ”,”ボールペン”がそれぞれ格納されていきます。

 

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

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

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

 

keys関数で各”品名”(キー)、items関数で各”数量”(要素)を取り出して、Transpose関数でセルの縦方向へ代入する処理を施しています。

 

 

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

 

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

 

  • Resize(num)・・・行を拡張
  • Resize(, num)・・・列を拡張

 

となります。

 

今回はResize(Total.Count)、すなわちResize(4)になるので、行を拡張する処理になります。

 

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

 

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

スポンサーリンク

まとめ

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

 

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

 

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

IT
スポンサーリンク
Yumaをフォローする
Yuma

愛知県在住の29歳会社員。

同志社大学卒業後、約3年間のメーカーでの営業職としての勤務経験を経て、フィリピン・セブ島へ3ヵ月プログラミング×英語留学。

帰国後、愛知のIT企業でWEBプログラマーとして勤務開始。

保有資格:TOEIC865点、日商簿記検定2級

Yumaをフォローする
Daybreak

コメント