EXCEL VBA 連想配列で選択範囲を集計・別シートに取り出し・自由に選択した範囲を集計(テクニック)

 

EXCEL VBA 連想配列で選択範囲を集計・別シートに取り出し・自由に選択した範囲を集計(テクニック)

 

 ●はじめに

今回は、以前にも紹介しましたDictionary(連想配列)の使い方を説明いたします。連想配列を理解する事で、特にデータを集計する際には、とても重宝されるので、便利な利用方法をサンプルプログラムを交えて順番に説明いたします。なお、下記のリンク先にも連想配列に関する内容を記載しておりますので、参考にして下さい。

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

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

 

●連想配列の応用:クロス集計表等は下記を参照してください。

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

 

 

 

 

EXCEL VBA 連想配列を利用して選択範囲のデータを集計し同じシートへ集計結果を表示

 

 

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

下記のサンプルプログラムは、連想配列を利用して選択範囲のデータを集計して同じシートへ集計結果を表示するサンプルプログラムです。元のデータにある支店名と品目の組み合わせで集計表を作成します。正し、支店名と品目名の組み合わせで合計値が0円の場合は、集計結果に反映しません。


【集計条件】
・元データから支店名・品目の2種の組み合わせに該当する金額を集計します。
・支店名・品目の組み合わせで集計した結果、金額が0円の場合は、集計結果には、反映させない。

【プログラムの流れ】

① 新しいディクショナリーを登録します(支店名&品目の組み合わせを対象)
② 集計結果を表示するG列からI列を削除します。
③ セル「A1」を基準に元となるデータを配列に登録します。
③ 配列データの最後まで繰り返します。(2行目から最終行まで繰り返します。)
④ 配列データに取り込んだ支店名・品目の組み合わせを作成します。
⑤ 支店名・品目名の組み合わせで未登録の組み合わせをディクショナリーへ登録します。
⑥ 既に支店名・品目の組み合わせが既にディクショナリーに登録されている場合は、同じ組み合わせに該当するディクショナリーへ金額を加算する。
⑦ 配列データの最後まで繰り返します。③へ
⑧ 配列登録したキーの配列変数を転記します。
⑨ 集計結果を表示するセル位置を指定します。2行目より
⑩ 集計した支店名・品目分の組み合わせ分のデータを繰り返します。
⑪ 集計した支店名・品目名の組み合わせの合計が0円以外を集計結果として表示対象とします。(集計結果が0円の場合は、表示させない)
⑫ 集計結果をG列:支店名・H列:品目・I列:合計を表示します。
⑬ 集計結果を表示位置を+1行下へ加算します。データが無くなるまで⑪へ
⑭ 集計結果用のタイトルと背景色を表示します。
⑮ 集計結果に対して格子罫線を引きます。

 

 

 

 

 ●実行前~実行後 ※プログラム実行後、データ一覧を元に支店名と品目の組み合わせで集計表が同じシート内に作成されました。
(画面クリックして拡大)

 

 

 

EXCEL VBA 連想配列を利用してアクティブセルで選択範囲を集計し、別シートに集計結果を表示(自由にデータ範囲を選択)

 

 

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

下記のサンプルプログラムは、サンプルプログラム①の応用編となります。サンプル①では、集計元のデータについては、表範囲の全件が集計範囲になりましたが、今回のサンプル②では、データ範囲をアクティブセルで選択した行範囲でデータを集計して同じシートへ集計結果を表示するサンプルプログラムになります。集計条件は、サンプル①と同じ、支店名と品目の組み合わせで集計表を作成しますが、今回は、実行ボタンを作成「集計」し実行後、別シート「結果」に集計結果を表示するサンプルプログラムになります。

 

【集計条件】
・シート【データ】より、集計元となる範囲をアクティブセルで選択します。
・アクティブセルで選択した行範囲が集計元データとなり、この範囲から支店名・品目の2種の組み合わせと該当する金額を集計します。
・支店名・品目の組み合わせで集計した結果、金額が0円の場合は、集計結果には、反映させない。

 

【プログラムの流れ】

① 新しいディクショナリーを登録します(支店名&品目の組み合わせを対象)
② ワークシートを設定します。(データ・結果)
③ 選択した範囲を取得(先頭行と最終行を取得します)※自由に選択した範囲の行番号を取得
④ 選択した範囲を配列へ登録する。
⑤ 登録した配列データの最後まで繰り返します。(先頭行から最終行まで繰り返します。)
⑥ 配列データに取り込んだ支店名・品目の組み合わせを作成します。
⑦ 支店名・品目名の組み合わせで未登録の組み合わせをディクショナリーへ登録します。
⑧ 既に支店名・品目の組み合わせが既にディクショナリーに登録されている場合は、同じ組み合わせに該当するディクショナリーへ金額を加算する。
⑨ 配列データの最後まで繰り返します。⑤へ
⑩ 配列登録したキーの配列変数を転記します。
⑪ 集計結果を表示するシート「集計」のセルA列~C列の文字列をクリアする。
⑫ 集計した支店名・品目分の組み合わせ分をデータに繰り返します。(シート「集計」へ転記)
⑬ 集計した支店名・品目名の組み合わせの合計が0円以外を集計結果として表示対象とします。(集計結果が0円の場合は、表示させない)
⑭ 集計結果をA列:支店名・B列:品目・C列:合計を表示します。
⑮ 集計結果を表示位置を+1行下へ加算します。データが無くなるまで⑪へ
⑯ 集計結果用のタイトルと背景色を表示します。
⑰ 集計結果に対して格子罫線を引きます。

※【実行ボタン】の作成方法は、下記を参照して下さい。

EXCEL VBA Sheet(シート)上に実行ボタンの設置(VBAプログラムの実行)

 

 

 

 

 

 ●実行前~実行後 ※プログラム実行後、自由に選択したセル範囲のデータを元に、別シート「集計」に支店名と品目の組み合わせの合計表がで集計表が作成されました。
※何度も行範囲を再選択して実行する事で、集計データを分析する時などで便利だと思います。
(画面クリックして拡大)

 

 

 

 

EXCEL VBA 連想配列を利用して列を選択して集計項目選択を設定し、同じシートに集計結果を表示(自由に集計項目を選択)

 

 

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

下記のサンプルプログラムは、上記のサンプルプログラムの応用になりますが、今回は、列(集計項目)を選択してデータを集計します。選択した項目ごとの組み合わせで集計する事が出来るので、情報を分析する際には、とても便利だと思います。選択する列(集計項目)は、複数列選択する事ができます。ただし、連続した列を選択した時のみ集計する事ができます。今回も「実行」ボタンを設置する事で、何度でも繰り返してプログラムを実行する事ができます。

 

①実行結果「年月」A列のみ選択して、月ごとにデータが集計されました。

②実行結果「年月・支店名」A列・B列を選択して、実行すると年月・支店名ごとにデータが集計されました。

 

③実行結果「品目・担当者」C列・D列を選択して、実行すると品目・担当者ごとにデータが集計されました。

【集計条件】
・シート【データ】より、集計する列(項目)を選択します。
・選択した列を元に、列データの組み合わせでデータを集計します。
※集計を選択する列は、連続した列を選択した場合のみ集計されます。

 

※【実行ボタン】の作成方法は、下記を参照して下さい。

EXCEL VBA Sheet(シート)上に実行ボタンの設置(VBAプログラムの実行)

 

 

 

 

 

 

 ●実行前~実行後 ※プログラム実行後、選択列に応じた組み合わせにより、データが集計され同じシートへ組み合わせごとの項目のデータが集計されました。連続した列を選択した時のみ集計する事ができます。今回も「実行」ボタンを設置する事で、何度でも繰り返してプログラムを実行する事ができます。
(画面クリックして拡大)

 

 

 

 

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