Excelで指定範囲の平均値を求める方法！VBAで一括処理も！

Excelで指定範囲の平均値を求めたいときはないでしょうか。

そんな中で悩むことは、

ですよね。

今回はそんなお悩みを解決する、

まとめます！

Excelで指定範囲の平均値を算出するイメージ

Excelで指定範囲の平均値を算出するイメージについて説明をします。

平均値を算出したい値を入力し表を用意します。

サンプルは以下のようになります。

まずは標準のExcelワークシート関数で平均値を算出する方法について説明をします。

次にVBAで平均値の数式または平均値算出結果を一括で挿入する方法について説明をします。

ワンクリックで算出から挿入までできるので便利ですね！

それでは早速使ってみましょう！

AVERAGE関数の書式と機能説明

平均値を求める関数はAVERAGE関数となります。
AVERAGE関数の書式と機能については以下の通りです。

参照：https://support.microsoft.com/ja-jp/office/average-%E9%96%A2%E6%95%B0-047bac88-d466-426c-a32b-8f33eb960cf6

指定範囲の平均値を算出する表を用意する

指定範囲の平均値を算出する表を用意しましょう。

内容はなんでもよいですが、平均値を算出できる整数を入力してください。

算出結果は11行目に入れるようにしますが、現段階では空欄にします。

セルの関数で平均値を求める

まずは標準のExcelワークシート関数であるAVERAGE関数を使い、平均値を算出してみましょう。

C11セルに以下の数式を入力します。

平均値が算出されましたね。

次はE11セルまでドラッグします。

はい、3列すべての平均値が算出されました。

VBAで指定範囲の平均値を一括挿入する

これまで関数を手入力する必要がありましたが、次はVBAを使い、関数を挿入する処理や計算結果を挿入する処理を一括で行っていきましょう。

タカヒロ

ここからこの記事のメインとなります。

Formulaで数式を挿入する

Formulaを使い、数式のままで挿入をしてみましょう。

Formulaはその名の通り数式を入れるプロパティとなります。
値として受け渡す情報は文字情報でしかないですが、Formulaを使うことにより数式としてセルへ代入をしてくれます。

タカヒロ

Rangeオブジェクトに値をれるプロパティはValueプロパティがよく使われますがこちらは数式ではなく計算結果の値となります。

VBAは以下の通りです。

Sub 指定範囲の平均値を一括挿入する()
    Range("C11").Formula = "=AVERAGE(C2:C10)"
    Range("C11").AutoFill Destination:=Range("C11:E11"), Type:=xlFillDefault
End Sub

C11セルのFormulaプロパティに”=AVERAGE(C2:C10)”を代入します。

オートフィルを使いE11セルまで数式をコピーします。

はい、できましたね。

FormulaR1C1の相対参照で数式を挿入する

FormulaR1C1の相対参照で数式を挿入してみましょう。

FormulaR1C1はR1C1の参照形式で数式を入れるプロパティとなります。

RはRow、CはColumnの頭文字でいわゆる行、列を意味します。
今いる位置から何行目、何列目という形でターゲットのセルの位置を指定していきます。

タカヒロ

相対参照は絶対参照とくらべ表の可変があっても数字を変えるだけで修正が楽なのがメリットですね。

VBAは以下の通りです。

Sub 指定範囲の平均値を一括挿入する()
    Range("C11").FormulaR1C1 = "=AVERAGE(R[-9]C:R[-1]C)"
    Range("C11").AutoFill Destination:=Range("C11:E11"), Type:=xlFillDefault
End Sub

C11セルのFormulaプロパティに”=AVERAGE(R[-9]C:R[-1]C)”を代入します。
C11セルから9行戻った位置から1行戻ったセルを範囲とする意味となります。

オートフィルを使いE11セルまで数式をコピーします。

はい、できましたね。

数式の結果を個々に挿入する

数式の算出結果を個々に挿入してみましょう。

Sub 指定範囲の平均値を一括挿入する()
    Range("C11").Value = WorksheetFunction.Average(Range("C2:C10"))
    Range("D11").Value = WorksheetFunction.Average(Range("D2:D10"))
    Range("E11").Value = WorksheetFunction.Average(Range("E2:E10"))
End Sub

Excelワークシート関数をVBAで使うにはWorksheetFunctionをワークシート関数の頭につけます。引数の部分はRangeオブジェクトを使います。

ワークシート関数の算出結果を各セルのValueプロパティへ代入していきます。

はい、数値のみ入力されましたね。

For文とCellsで連続して数式の結果を挿入する

次は個々に指定していた内容をFor文の繰り返し処理で1行に絞ってみましょう。

Sub 指定範囲の平均値を一括挿入する()
    For i = 1 To 3
        Cells(11, 2 + i).Value = WorksheetFunction.Average(Range(Cells(2, 2 + i), Cells(10, 2 + i)))
    Next
End Sub

3列分処理を繰り返します。

RangeからCellsへ変更し、数字でセルの位置を指定していきます。
Cellsの引数は（行番号、列番号）となります。

ワークシート関数の算出結果を各セルのValueプロパティへ代入していきます。
Range(Cells(2, 2 + i), Cells(10, 2 + i))は変数iが1から3まで変動しますので、Range(“C2:C10”)、Range(“D2:D10”)、Range(“E2:E10”)を意味します。

はい、数値のみ入力されましたね。

Rangeを二次元配列に格納しFor文で数式の結果を挿入する

Rangeオブジェクトを変数に代入すると、二次元配列の形式で値が代入されます。
この仕様を利用し、同じく数式の算出結果をセルへ挿入していきます。

Sub 指定範囲の平均値を一括挿入する()
    arrTemp = Range("C11:E11")
    For i = LBound(arrTemp, 2) To UBound(arrTemp, 2)
        Cells(11, 2 + i).Value = WorksheetFunction.Average(Range(Cells(2, 2 + i), Cells(10, 2 + i)))
    Next
End Sub

Rangeオブジェクトを配列へ格納し、二次元配列へ変換します。

二次元配列の2次（Column、列）を指定し、はじめから終わりまで処理を繰り返します。

ワークシート関数の算出結果を各セルのValueプロパティへ代入していきます。

はい、数値のみ入力されましたね。

RangeオブジェクトをForEach文で取得し数式の結果を挿入する

最後はRangeオブジェクトそのものを利用して同じく数式の算出結果をセルへ挿入していきます。

Sub 指定範囲の平均値を一括挿入する()
    Set objRange = Range("C11:E11")
    For Each objTemp In objRange
        objTemp.Value = WorksheetFunction.Average(Range(Cells(objTemp.Row - 9, objTemp.Column), Cells(objTemp.Row - 1, objTemp.Column)))
    Next
End Sub

objRangeへRangeオブジェクトをセットします。

objRangeの個々の要素（つまりセル）を取得します。

ワークシート関数の算出結果を各オブジェクトのValueプロパティへ代入していきます。
objTemp.Rowでオブジェクトの行番号が取得できますので、R1C1方式と同じく、RangeのRowへ-9番目から-1番目を、objTemp.Columnで列番号を取得できますので、ColumnへobjTemp.Rowを設定し、範囲して指定ます。

はい、数値のみ入力されましたね。

VBAの実装手順

実装手順は以下の通りです。

Excel側にVBAを実装していきます。

①Excelを新規に開き、「開発」タブをクリックし、「VisualBasic」をクリックします。
もしくはショートカットキー「Alt」＋「F11」でもOKです。

②標準モジュールを追加します。
左ペインのVBAProjectを右クリックし、「挿入」、「標準モジュール」を選択します。

③右ペインのウインドウに上記のVBAを入力します。

こちらで完了です。

VBAを実行する

では早速VBAの実行をしてみましょう。

①「開発」タブの「VBA」をクリックし実行したいマクロを選択し、「実行」をクリックします。

②処理がされたことが確認できれば完了です。

さいごに

いかがでしょうか。

今回は、

まとめました。

また、他にも便利な方法がありますので、よろしければご参照頂ければと思います。