エクセルの関数には平均を求めるAVERAGE/AVERAGEIF/AVERAGEIFS関数が存在します。
この関数は、なんとマクロVBAでも使用することができます。
使用方法は関数と同じです。
関数は下記リンクを参照してください。
【AVERAGE関数】エクセルで平均値を求める方法について説明します！ ► 独学エクセル塾 (dokugakuexcel.com)
【AVERAGEIF関数】エクセルで条件に当てはまる平均値を求める ► 独学エクセル塾 (dokugakuexcel.com)
【AVERAGEIFS関数】エクセルで複数の条件に当てはまる平均を求める ► 独学エクセル塾 (dokugakuexcel.com)
平均はよく使用する機能ですよね。
ぜひ身に着けて活用してみてください。
それでは見ていきましょう。
■AVERAGE関数で指定範囲の平均を求める
・プログラムの仕組み
Application.WorksheetFunction.Average(平均を求めたい範囲)
・使用方法
では下図の例をもとに進めていきましょう。
AさんからKさんまでの得点をＣ列に入力しています。
今回はその平均得点を「E3」に入力してみましょう。
ではプログラムを組んでみましょう。
平均を求めたい範囲は「C3からC13」ですので下記のようになります。
Sub 平均()
Cells(3, 5) = Application.WorksheetFunction.Average(Range(“C3:C13”))
End Sub
それでは実行した結果を見てみましょう。
平均を求めることができました。
ちなみにCellsで表示するとこのようになります。
Cells(3, 5) = Application.WorksheetFunction.Average(Range(Cells(3, 3), Cells(13, 3)))
さらにRange変数を使用しても平均を求めることは可能です。
Sub 平均()
Dim A As Range
Set A = Range(“C3:C13”)
Cells(3, 5) = Application.WorksheetFunction.Average(A)
End Sub
■AverageIF関数で条件に当てはまる平均を求める
・プログラムの仕組み
Application.WorksheetFunction.AverageIf(条件の範囲,絞り込む条件,平均の範囲)
・使用方法
では下図の例をもとに進めていきましょう
先ほどのシートに学年が加わりました。
今回は２年の平均を求めてみましょう。
条件の範囲は「Ｃ3からＣ13」になります。
絞り込む条件は「２年」ですね。
平均の範囲は得点なので「Ｄ3からＤ13」ですね。
プログラムを組むとこのようになります。
Sub 条件に当てはまる平均()
Cells(3, 6) = Application.WorksheetFunction.AverageIf(Range(“C3:C13”), “２年”, Range(“D3:D13”))
End Sub
それでは実行してみましょう。
機能していますね。
こちらもCellsで表してみましょう。
Cells(3, 6) = Application.WorksheetFunction.AverageIf(Range(Cells(3, 3), Cells(13, 3)), “２年”, Range(Cells(3, 4), Cells(13, 4)))
Range変数を使用するとこのようになります。
Sub 条件に当てはまる平均()
Dim A As Range, B As Range
Set A = Range(“C3:C13”)
Set B = Range(“D3:D13”)Cells(3, 6) = Application.WorksheetFunction.AverageIf(A, “２年”, B)
End Sub
■AverageIFS関数で条件に当てはまる平均を求める
・プログラムの仕組み
Application.WorksheetFunction.AverageIfs(平均したい範囲,絞り込む範囲1,絞り込む条件1,絞り込む範囲2,絞り込む条件2….)
・使用方法
では下図の例をもとに進めていきましょう
今回はさらに「組」も追加しました。
では実際にプログラムを組んでみましょう。
平均したい範囲は「E3からE13」です。
絞り込む条件の範囲は１つ目は学年なので「C3からC12」です。
絞り込む条件の１つ目は「２年」ですね。
絞り込む条件の範囲は2つ目は学年なので「D3からD12」です。
絞り込む条件の2つ目は「は組」ですね。
それでは作成したプログラムを見てみましょう。
Sub 複数の条件に当てはまる平均()
Cells(10, 7) = Application.WorksheetFunction.AverageIfs(Range(“E3:E13”), Range(“C3:C13”), “２年”, Range(“D3:D13”), “は組”)
End Sub
実行するとこのようになります。
「２年は組」に当てはまる「45」と「60」が平均されていますね。
ではCellsでもプログラムを見てみましょう。
Cells(10, 7) = Application.WorksheetFunction.AverageIfs(Range(Cells(3, 5), Cells(13, 5)), Range(Cells(3, 3), Cells(13, 3)), “２年”, Range(Cells(3, 4), Cells(13, 4)), “は組”)
長くなりますが、変数を使う場合はこちらを活用してみましょう。
Range変数を使用するとこのようになります。
Sub 複数の条件に当てはまる平均()
Dim A As Range, B As Range, C As Range
Set A = Range(“C3:C13”)
Set B = Range(“D3:D13”)
Set C = Range(“E3:E13”)Cells(10, 7) = Application.WorksheetFunction.AverageIfs(C, A, “２年”, B, “は組”)
End Sub
■よくあるエラーと対処法
・WorksheetFunctionクラスのAverageifsプロパティを取得できません
こちらは平均の範囲にエラーが生じているかもしれません。
下図の見本を見てみましょう。
得点にエラーが存在しています。
先ほどの「２年は組」の条件で平均を求めるとエラーになってます。
こちらの解決方法は平均範囲のエラーをなくすしかありません。
IFERROR関数などを使用してエラーにならない出力を検討してみましょう。
・平均の結果が正しくない
こちらはセルの参照が間違っていないかを確認してください。
それでも結果が異なる場合は、表示形式が「文字列」になっている可能性があります。
下図を見てみましょう。
平均範囲が「文字列」で入力されている場合は数値としてみなされず
文字として判断されるため、平均されなくなります。
解決方法は表示形式を「標準」「数値」に戻して入力しなおすと解決します。
■練習問題
練習問題も別の記事でまとめているので是非参考にしてみてください！
⬛︎公式の説明
わかりやすいように説明したため公式と使用する語句が異なりますが
マイクロソフト公式の説明については下記のリンクを参照してください。☟
WorksheetFunction オブジェクト (Excel) | Microsoft Learn
■まとめ
いかがだったでしょうか。
関数で使用する平均の機能ですがVBAでも使用することができるので
是非今回の内容を活用してみてください。
次回はカウント関数をVBAで使用する方法についてまとめています。記事はこちらから☟
【マクロVBA知識 20】カウントをCOUNT/COUNTIF/COUNTIFSで求める ► 独学エクセル塾 (dokugakuexcel.com)
それでは次回の記事でお会いしましょう。