今回説明するプログラムは、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プログラムにて作成すると下記のプログラムになります。
【サンプルプログラム】
' ' Sub FormulaArray00() '「単価」×「数量」の配列数式を登録 Range("D2:D6").Select '配列数式の登録先を選択します。 Selection.FormulaArray = "=B2:B6*C2:C6" '選択しているD2~D6に、単価×数量の配列数式を登録します。 End Sub '
【実行結果】
●実行後、選択範囲に指定したセル(D2:D6)に、配列数式が登録されました。なお、配列数式で登録されますと数式に” { 数式 } ”で登録されます。
● FormulaArray プロパティを利用するには、下記の通りに設定を行います。
【構文】
● Rangeオブジェクト.FormulaArray = 配列数式の設定
・FormulaArrayプロパティの最大文字数は、255文字に制限させています。
● Rangeオブジェクト.FormulaArray ’登録された配列数式の確認
【使用例】
・下記の使用例は、SUM関数とIF関数を使い配列数式で指定したセルに設定します。セル「E2」には、{=SUM(IF(A2:A10=”東京”,B2:B10))} が登録されています。この処理は、セル「D2」に東京が登録されていて、この「東京」に対してA列に東京と一致するB列「売上金額」を集計する計算式になります。
【サンプルプログラム】
' ' Sub FomulaArray01() Range("E2").FormulaArray = "=SUM(IF(A2:A10=""東京"",B2:B10))" 'SUM関数とIF関数を配列書式でセル「E2」に登録します。 MsgBox Range("E2").FormulaArray '登録された配列数式の確認(表示) End Sub '
【プログラム実行後】
下記のサンプルプログラムは、配列数式とドロップダウンリストと組み合わせた「平均・最大・最小・合計」を配列数式で集計するサンプルプログラムです。
【プログラムの流れ】
① セル(F1)にドロップダウンリストを作成します。(東京支店・神奈川支店)
② ドロップダウンリストの初期設定(東京支店)
③ セル(G3)に、該当(選択)したデータの「売上平均」を算出する配列数式を登録します。
④ セル(G4)に、該当(選択)したデータの「最大平均」を算出する配列数式を登録します。
⑤ セル(G5)に、該当(選択)したデータの「最小平均」を算出する配列数式を登録します。
⑥ セル(G6)に、該当(選択)したデータの「売上合計」を算出する配列数式を登録します。
【プログラム実行条件】
・A列~D列のデータを作成する。
●ドロップダウンリスト作成方法については、下記のページを参照して下さい。
' ' Sub FomulaArraySamp1() With Range("F1").Validation 'セル「F1」にドロップダウンリストを作成 .Delete 'ドロップダウンリストを削除 .Add Type:=xlValidateList, Formula1:="東京支店,神奈川支店" 'セル「F1」にドロップダウンリスト作成 (東京支店・神奈川支店)を登録 End With Range("F1") = "東京支店" 'セル「F1」ドロップダウンリストの初期設定 Range("G3").FormulaArray = "=AVERAGE(IF(B2:B19=F1,D2:D19))" '売上平均 Range("G4").FormulaArray = "=MAX(IF(B2:B19=F1,D2:D19))" '売上最大 Range("G5").FormulaArray = "=MIN(IF(B2:B19=F1,D2:D19))" '売上最小 Range("G6").FormulaArray = "=SUM(IF(B2:B19=F1,D2:D19))" '売上合計 End Sub '
下記のサンプルプログラムは、配列数式とドロップダウンリストを組み合わせて、抽出合計に配列数式を登録して集計するサンプルプログラムです。今回は、複数の抽出条件に対応しており、また、ドロップダウンリストの内容を自動登録する方法も兼ねて作成しています。
【プログラムの流れ】
① セル(G1)にA列のデータを元に一意データを抽出してドロップダウンリストを作成します。(東京支店・神奈川支店)
② セル(H1)にB列のデータを元に一意データを抽出してドロップダウンリストを作成します。(備消品費・雑費・交通費・図書新聞費・・)
③ セル(I1)にC列のデータを元に一意データを抽出してドロップダウンリストを作成します。(三菱UFJ銀行・三井住友銀行・みずほ銀行)
④ セル(G3)に、セル(G2:支店名・H1:勘定科目・I1:取引銀行)で選択したデータの「売上金額」を算出する配列数式を登録します。
【プログラム実行条件】
・A列~D列のデータを作成する。
' ' Sub FomulaArraySamp2() '複数条件で条件抽出(支店名・勘定科目・取引銀行) Dim i, L, mRow As Long Dim DList As Variant For i = 1 To 3 '支店名・勘定科目・取引銀行 Dim List As Collection Set List = New Collection With Cells(1, i + 6).Validation 'セル「G1~I1」にドロップダウンリストを作成 .Delete 'ドロップダウンリストを削除 mRow = Cells(Rows.Count, i).End(xlUp).Row '列の最終行を設定 On Error Resume Next 'エラーが発生しても続行する。 For L = 2 To mRow '列の最終行データまで繰り返す List.Add Cells(L, i), Cells(L, i) 'コレクションに追記(ドロップダウンリストの準備一意データ) Next L On Error GoTo 0 For L = 1 To List.Count '一意データ分繰り返す。 DList = DList + List(L) & "," 'ドロップダウンリストの一意データの作成 Next L .Add Type:=xlValidateList, Formula1:=DList 'ドロップダウンリストの作成 End With DList = "" 'ドロップダウンリストの一意データを削除 Next i Range("G3").FormulaArray = "=SUM(IF((A:A=G1) * (B:B =H1) * (C:C=I1),D:D,0))" 'セル「G3」に配列数式を登録します。「G1:支店名・H1:勘定科目・I1:取引銀行」に一致するデータの売上金額をセル「G3」へ集計します。 End Sub '
下記のサンプルプログラムは、配列数式使って曜日ごとに売上金額を集計するサンプルプログラムです。曜日ごとのデータを集計する際には、とても便利だと思います。
【プログラムの流れ】
① A列の最終行を取得します。
② セル(G2)にセル(F2)で指定している曜日の集計を算出する配列数式を登録します。
③ セル(G3)にセル(F3)で指定している曜日の集計を算出する配列数式を登録します。
【プログラム実行条件】
・A列~C列のデータを作成する。
・セル(F2・F3)集計を曜日を設定します。(月・火・水・木・金・土・日)
' ' Sub FomulaArraySamp3() '指定した曜日ごとに集計します。 Dim lRow As Long lRow = Cells(Rows.Count, "A").End(xlUp).Row 'A列の最終行を取得 Range("G2").FormulaArray = "=SUM(IF(TEXT(B2:B" & lRow & ",""aaa"") = F2,C2:C" & lRow & ",0))" 'セル「G2」に月曜日(F2)の売上金額を集計する配列数式を登録します。 Range("G3").FormulaArray = "=SUM(IF(TEXT(B2:B" & lRow & ",""aaa"") = F3,C2:C" & lRow & ",0))" 'セル「G2」に日曜日(F3)の売上金額を集計する配列数式を登録します。 End Sub '