EXCEL VBA 連想配列でクロス集計・項目別集計・グループ集計 (Scripting.Dictionary) テクニック

 

EXCEL VBA 連想配列でクロス集計・項目別集計・グループ集計 (Scripting.Dictionary) テクニック

 

 

 ●はじめに

データを集計する際に、Dictionaryオブジェクトを利用してデータを集計すると、とても便利です。今回は、Dictionaryオブジェクトを利用して連想配列化して、クロス集計する方法を説明いたします。なお、連想配列は、簡単に言いますと数値以外のキーと要素がセットになった配列の事です。それでは、EXCEL VBAでDictionaryオブジェクトを利用した連想配列化した、クロス集計の方法を説明いたします。

 

●【基礎的な連想配列は、下記を参照して下さい。】

EXCEL VBA 連想配列で合計・グループ集計・別シート転記・重複削除 (Scripting.Dictionary) 

 ●配列の使い方(Array LBound UBound) 基礎については、下記を参照してください。

EXCEL VBA 配列の使い方(Array LBound UBound)

●2次元配列・セル範囲の内容を配列に格納・配列の内容をセルに代入(一括・高速化・セルの指定範囲)については、下記を参照して下さい。

EXCEL VBA  2次元配列・セル範囲の内容を配列に格納・配列の内容をセルに代入(一括・高速化・セルの指定範囲)

 

 

 

 

EXCEL VBA 連想配列でクロス集計・項目別集計①

 

 

 ●プログラム説明 (サンプル①)

下記のサンプルプログラムは、毎月発生する勘定科目毎の金額を年月と勘定科目毎に集計するサンプルプログラムです。Dictionaryオブジェクトを利用して連想配列化することで、簡単なプログラムでも、項目毎のクロス集計が可能になります。それでは、順番に内容を説明いたします。


【プログラム実行条件】
・ワークシート名を【売上明細】・【売上集計】に設定します。
・ワークシート名【売上明細】には、【A列:年月 B列:勘定科目 C列:金額】を入力します。
・ワークシート名【売上集計】のセルB2~以降(横)に年月を登録します。
・ワークシート名【売上集計】のセルA3~以降(下)に勘定科目を登録します。

 

 

 

 

 ●実行前~実行後 ※プログラム実行後、シート「売上明細」に記載されている一覧データを元に、シート「売上集計」にクロス集計された売上集計表が作成されました。

 

 

 

 

EXCEL VBA 連想配列でクロス集計・項目別集計②(縦・横の項目を自動作成)

 

 ●プログラム説明 (サンプル②)

下記のサンプルプログラムは、上記サンプル①の応用編になります。上記サンプルプログラムでは、集計結果が表示されるシート【売上集計】に年月と勘定科目を記入する必要があります。事前にデータ内容を精査していれば可能ですが、大量のデータですと精査するのも大変なので、これもDictionaryオブジェクトを利用して一意データの年月と一意の勘定科目を抽出させます。この一意のデータを元に、データを集計します。


【プログラム実行条件】
・ワークシート名を【売上明細】・【売上集計】に設定します。
・ワークシート名【売上明細】には、【A列:年月 B列:勘定科目 C列:金額】を入力します。

 

 

 

 

●実行前~実行後 ※プログラム実行後、シート「売上明細」に記載されている一覧データを元に、シート「売上集計」にクロス集計された売上集計表が作成されました。

 

 

 

 

EXCEL VBA 連想配列でクロス集計・項目別集計③(複数項目を管理)

 

 

 ●プログラム説明 (サンプル③)

下記のサンプルプログラムは、今まで説明した上記サンプルプログラムの応用編になります。今回説明するプログラムは、集計するデータに複数項目が有る場合に、項目ごとに振り分けて集計するサンプルプログラムです。シート人件費データには、約1000件のデータを登録しています。このデータを横軸として、支店名・縦軸として、所属・役職・性別ごとに纏めるクロス集計表を作成します。

【プログラム動作 実行前⇒実行後】

【クロス集計の項目設定】

【プログラム実行条件】
・ワークシート名を【人事データ】・【売上集計】に設定します。
・ワークシート名【人事データ】には、【A列:社員番号 B列:支店名 C列:所属 D列:役職 E列:性別 F列:氏名 G列:人件費】を入力します。

 

 

 

 

 ●実行前~実行後 ※プログラム実行後、シート【人事データ】を元に、横軸に【支店名】・縦軸に「所属」・「役職」・「性別」別に該当する人件費が集計されて、クロス集計表が作成されました。

 

 

 

最後まで、ご覧いただきまして誠に有難うございました。
また、VBAに関するテクニックや便利な手法などをこのサイトに掲載していきますので、定期的に参照していただけると幸いです。