EXCEL VBA FormulaArrayプロパティ 複数条件の集計・結果合計・条件によるカウント集計・集計結果( 配列数式で簡単集計)

 

 

EXCEL VBA FormulaArrayプロパティ 複数条件の集計・結果合計・条件によるカウント集計・集計結果( 配列数式で簡単集計)

 

 

 

 ●はじめに

今回説明するプログラムは、FormulaArrayプロパティです。EXCELの配列数式をVBAで利用する際は、このFormulaArrayプロパティを使い、指定したセルに配列数式を登録します。VBAで配列数式を使うメリットとして、データ件数が変動したり、集計条件などを変更する時には、VBAを使う事で柔軟に対応する事ができます。また、配列数式を再定義する事も可能となります。それでは、順番にFormulaArrayプロパティの使い方をサンプルプログラムを交えて順番に説明いたします。

 

●【FormulaArray プロパティ (Excel)についての参照資料】
https://docs.microsoft.com/ja-jp/office/vba/api/excel.range.formulaarray

 

 

 

 ●配列数式とは


● FormulaArray プロパティを説明する前に、配列数式の説明をします。

下図は、D列セル(D2~D6)に商品ごとに、単価×数量の合計を求める配列数式の登録方法です。
登録手順は、①~④の順番に行う事でD列に配列数式を登録する事ができます。

D列(D2:D6)に「B:単価」×「C:数量」の配列数式が登録されました。

● FormulaArray プロパティを使ってVBAプログラムを作成
上記の配列数式をVBAプログラムにて作成すると下記のプログラムになります。

【サンプルプログラム】

 

【実行結果】
実行後、選択範囲に指定したセル(D2:D6)に、配列数式が登録されました。なお、配列数式で登録されますと数式に” { 数式 } ”で登録されます。

 

 

 ●書式の説明  FormulaArray プロパティ

● FormulaArray プロパティを利用するには、下記の通りに設定を行います。

 

【構文】
● Rangeオブジェクト.FormulaArray = 配列数式の設定

・FormulaArrayプロパティの最大文字数は、255文字に制限させています。

● Rangeオブジェクト.FormulaArray  ’登録された配列数式の確認

【使用例】
・下記の使用例は、SUM関数とIF関数を使い配列数式で指定したセルに設定します。セル「E2」には、{=SUM(IF(A2:A10=”東京”,B2:B10))}  が登録されています。この処理は、セル「D2」に東京が登録されていて、この「東京」に対してA列に東京と一致するB列「売上金額」を集計する計算式になります。



【サンプルプログラム】

 

【プログラム実行後】

 

 

 

 

EXCEL VBA FormulaArrayプロパティ ドロップダウンリストと組み合わせた集計「平均・最大・最小・合計( 配列数式で簡単集計)」

 

 

 

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

下記のサンプルプログラムは、配列数式とドロップダウンリストと組み合わせた「平均・最大・最小・合計」を配列数式で集計するサンプルプログラムです。

【プログラムの流れ】
① セル(F1)にドロップダウンリストを作成します。(東京支店・神奈川支店)
② ドロップダウンリストの初期設定(東京支店)
③ セル(G3)に、該当(選択)したデータの「売上平均」を算出する配列数式を登録します。
④ セル(G4)に、該当(選択)したデータの「最大平均」を算出する配列数式を登録します。
⑤ セル(G5)に、該当(選択)したデータの「最小平均」を算出する配列数式を登録します。
⑥ セル(G6)に、該当(選択)したデータの「売上合計」を算出する配列数式を登録します。

【プログラム実行条件】 

・A列~D列のデータを作成する。


●ドロップダウンリスト作成方法については、下記のページを参照して下さい。

EXCEL VBA ドロップダウンリスト・プルダウンリスト・コンボボックスの作成(リスト選択)

 

 

 

 

 

 

●実行前~実行後  ※プログラム実行後、ドロップダウンリスト選択した支店を元に、「平均・最大・最小・合計」の該当データが集計されました。
※(画面クリックして拡大)

 

 

 

 

EXCEL VBA FormulaArrayプロパティ ドロップダウンリスト(複数条件)と組み合わせた集計「平均・最大・最小・合計( 配列数式で簡単集計)」

 

 

 

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

下記のサンプルプログラムは、配列数式とドロップダウンリストを組み合わせて、抽出合計に配列数式を登録して集計するサンプルプログラムです。今回は、複数の抽出条件に対応しており、また、ドロップダウンリストの内容を自動登録する方法も兼ねて作成しています。

【プログラムの流れ】
① セル(G1)にA列のデータを元に一意データを抽出してドロップダウンリストを作成します。(東京支店・神奈川支店)
② セル(H1)にB列のデータを元に一意データを抽出してドロップダウンリストを作成します。(備消品費・雑費・交通費・図書新聞費・・)
③ セル(I1)にC列のデータを元に一意データを抽出してドロップダウンリストを作成します。(三菱UFJ銀行・三井住友銀行・みずほ銀行)
④ セル(G3)に、セル(G2:支店名・H1:勘定科目・I1:取引銀行)で選択したデータの「売上金額」を算出する配列数式を登録します。

【プログラム実行条件】 

・A列~D列のデータを作成する。

 

 

 

 

 

 ●実行前~実行後 ※プログラム実行後、セル「G1:支店名・H1:勘定科目・I1:取引銀行」を選択して、結果がセル「G3」に抽出金額として、該当する売上金額の合計が表示されました。
(画面クリックして拡大)

 

 

 

 

EXCEL VBA FormulaArrayプロパティ 曜日ごとに集計( 配列数式で簡単集計)」

 

 

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

下記のサンプルプログラムは、配列数式使って曜日ごとに売上金額を集計するサンプルプログラムです。曜日ごとのデータを集計する際には、とても便利だと思います。

 

【プログラムの流れ】

① A列の最終行を取得します。
② セル(G2)にセル(F2)で指定している曜日の集計を算出する配列数式を登録します。
③ セル(G3)にセル(F3)で指定している曜日の集計を算出する配列数式を登録します。

 

【プログラム実行条件】 

・A列~C列のデータを作成する。
・セル(F2・F3)集計を曜日を設定します。(月・火・水・木・金・土・日)

 

 

 

 

 ●実行前~実行後 ※プログラム実行後、セル(F2/F3)に登録されている曜日に応じで売上金額が集計されました。
※セルの(F2/F3)を変更する事で、該当する売上金額が集計されます。
(画面クリックして拡大)

 

 

 

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