【Excel VBA】マクロを使って集計関数を更新したり、セルに式を挿入する方法を紹介

今回はマクロを使って集計関数を更新したり、式をセルに挿入する方法を紹介したいと思います。SUM関数やAVERAGE関数といった複数のデータから結果を取得する関数のことですね。

また不定期でVBAに関する記事を投稿していますので、是非こちらも併せてご覧ください！

マクロで集計関数を使う方法

マクロで集計関数を使用してセル上に出力するには、以下の方法があります。

WorksheetFunctionメソッドを使う方法
直接書き込む方法

例えば生徒の小テストの平均点数を出したい場合があったとします。

マクロを使わずに計算する場合、セルにAVERAGE関数を挿入して平均点を算出しますよね。WorksheetFunctionは、それをマクロ側で実行する版だと思っていただければOKです。マクロ内で計算して、その結果だけ返すイメージです。

逆に直接書き込む方法は、文字通りセルに書き込む時と同じようにコードに埋め込む方法です。マクロを使わずに計算する方法とほぼ同じで、エディターに”＝AVERAGE(“B3:B10”)”とそのまま入力するイメージです。

以前こちらの方法を使用した方法について紹介していますので、是非こちらも併せてご覧ください！

どちらも一長一短ではあると思うので、お好きなほうを選んでいただければと思います。

コードの紹介

それぞれ関数毎にWorksheetFunctionを使用したパターン,直接書き込むパターンを紹介します。それぞれコピペして実際に動作を確認してみるのをお勧めします。

SUM関数

Sub test_Calc_SUM()
    
    Dim lastCell                As String
    
    With ThisWorkbook.ActiveSheet
        
'WorksheetFunctionの場合
        .Range("F3") = WorksheetFunction.Sum(.Range(.Cells(3, 2), .Cells(Rows.Count, 2).End(xlUp)))

'直接書き込む場合
        '最後のデータが入ったセル番地を取得
        lastCell = .Cells(Rows.Count, 2).End(xlUp).Address(RowAbsolute:=False, ColumnAbsolute:=False)
        .Range("F3") = "=SUM(B3:" & lastCell & ")"
    End With
    
End Sub

AVERAGE関数

Sub test_Calc_AVERAGE()
    
    Dim lastCell                As String
    
    With ThisWorkbook.ActiveSheet
        
'WorksheetFunctionの場合
        .Range("F4") = WorksheetFunction.Average(.Range(.Cells(3, 2), .Cells(Rows.Count, 2).End(xlUp)))

'直接書き込む場合
        '最後のデータが入ったセル番地を取得
        lastCell = .Cells(Rows.Count, 2).End(xlUp).Address(RowAbsolute:=False, ColumnAbsolute:=False)
        .Range("F4") = "=AVERAGE(B3:" & lastCell & ")"
    End With
    
End Sub

MAX関数

Sub test_Calc_MAX()
    
    Dim lastCell                As String
    
    With ThisWorkbook.ActiveSheet
        
'WorksheetFunctionの場合
        .Range("F5") = WorksheetFunction.Max(.Range(.Cells(3, 2), .Cells(Rows.Count, 2).End(xlUp)))

'直接書き込む場合
        '最後のデータが入ったセル番地を取得
        lastCell = .Cells(Rows.Count, 2).End(xlUp).Address(RowAbsolute:=False, ColumnAbsolute:=False)
        .Range("F5") = "=MAX(B3:" & lastCell & ")"
    End With
    
End Sub

MIN関数

Sub test_Calc_MIN()
    
    Dim lastCell                As String
    
    With ThisWorkbook.ActiveSheet
        
'WorksheetFunctionの場合
        .Range("F6") = WorksheetFunction.Min(.Range(.Cells(3, 2), .Cells(Rows.Count, 2).End(xlUp)))

'直接書き込む場合
        '最後のデータが入ったセル番地を取得
        lastCell = .Cells(Rows.Count, 2).End(xlUp).Address(RowAbsolute:=False, ColumnAbsolute:=False)
        .Range("F6") = "=MIN(B3:" & lastCell & ")"
    End With
    
End Sub

コードの説明

続いてコードの説明です。とは言っても変わる部分が関数名だけなので、共通部分の説明をします。

WorksheetFunctionの場合

WorksheetFunctionの書き方は、以下のようになります。

【結果を入れたいセル・変数名】 ＝ WorksheetFunction.【関数名】(【セル範囲・計算条件】)

結果を入れたいセル・変数名

セルの場所や変数名を指定します。Range(“A1”)やCells(1,2)、とかですね。

関数名

使用する関数名を指定します。Sum,Average,Count等色々あります。

WorksheetFunctionを入力して．(ドット)を入れると関数一覧が出てくるのでそこから選ぶのがいいと思います。

セル範囲・計算条件

計算したいセル範囲・条件を指定します。セル範囲が固定ならRange(“A1:A10”)のように指定すればよいですし、固定でないならRange(.Cells(3, 2), .Cells(Rows.Count, 2).End(xlUp))のように指定すれば動的にセル範囲を取得することが出来ます。

また、セル範囲でなく数字,配列も指定することができます。

Sub test_Calc_vals()
    Dim target_vals()           As Long
    
    ReDim target_vals(6)
    
    target_vals(0) = 90
    target_vals(1) = 95
    target_vals(2) = 80
    target_vals(3) = 70
    target_vals(4) = 50
    target_vals(5) = 60

    MsgBox WorksheetFunction.Sum(target_vals) & vbCrLf & _
           WorksheetFunction.Sum(1, 2, 3, 4, 5, 6, 7, 8, 9, 10)
End Sub

セル範囲を動的に取得する方法については以下の記事で紹介・説明していますので、良ければ併せてご覧ください！

直接書き込む場合

次に直接書き込む場合についてです。セルに直接入力する要領でコードに入力します。

セル範囲が固定の場合は以下のような感じで入力すればOKです。

Range("A1") = "=SUM(B3:B10)"

動的に取得したい場合は終点セル(ここまで範囲にする)を別に取得しておく必要があります。取得するにはCells().Addressを使ってセル名を取得します。

Dim lastCell                As String

lastCell = Cells(Rows.Count, 2).End(xlUp).Address(RowAbsolute:=False, ColumnAbsolute:=False) '最後のデータが入ったセル番地を取得
Range("F3") = "=SUM(B3:" & lastCell & ")"

ちなみにCells().Addressのカッコ内のRowAbsoluteとColumnAbsoluteは、行・列毎に絶対参照にするか(＄(ダラー)をつけるかどうか)を指定します。

絶対参照については以下記事で紹介・説明していますので、良ければ併せてご覧ください！