この記事では「エクセルにて空白以外平均（空欄を除く）や空白を含む平均を出す方法」について解説していきます。
方法としては下記のようなものが一般的に利用できます。
・AVERAGE関数
・AVERAGEA関数
・VBAマクロ
それでは、具体例を用いて解説しましょう。
目次
エクセルにて空白以外の平均の計算方法【空欄を除く】
空白以外の値の平均を求める際はAVERAGE関数が適しています。
下記サンプルにて、任意の値を10個用意しました。ただし、No.4及び7は値が入力されていません。
セルE1に「=AVERAGE(B2:B11)」 と入力します。
なお、セル参照を行っているので、”(ダブルクォーテーション)で括ってしまうと” B2:B11”という文字列として扱われ、エラーとなります。
計算結果が意図したものと異なっている場合は”(ダブルクォーテーション)で括る/括らないを間違えていないか確認しましょう。
AVERAGE関数について解説します。
当関数は、指定したセルにおいて、空白でないものの値の平均を求める際に使用し、
=AVERAGE (数値1, [数値2], …)
と入力します。
ここでは「B2:B11」としているので、B2, B3, … B11で空白でないセルの値の平均を求めることになります。
ENTERを押すと、No.4及び7を除いた8データ分の平均値が計算されます。
このようにして空欄以外の平均を計算できるのです。
エクセルの空白を含む平均の計算方法【空欄を含む】
次は空白を含む→0とみなす平均値を求める場合について解説します。
セルE2に「=AVERAGEA(B2:B11)」と入力します。
なお、サンプルデータについて、No.4及び7については「=””」を入力しておきましょう。
AVERAGEA関数について解説します。
当関数は、指定したセルにおいて空白等の数値以外であるものについては0とみなして値の平均を求める際に使用し、
=AVERAGEA (数値1, [数値2], …)
と入力します。
前例と同様に「B2:B11」としているので、B2, B3,…B5(=0),…B8(=0), … B11の値の平均を求めることになります。
ENTERを押すと、No.4及び7を0とみなした10データ分の平均値が計算されます。
エクセルの空白を含む平均の計算方法(VBAマクロで両方を実装する)
少し敷居は高いですが、VBAマクロでの実現方法についてお話しします。
最初にVBEを起動させます。
キーボードでAlt+11と押してみましょう。下記のようなウィンドウが開いたら成功です。
左端に表示されているツリーを右クリックし、「挿入」-「標準モジュール」と進みます。
白紙のテキストエディタが開きます。
コードはここに実装します。
なお、下記では「Option Explicit」と書かれていますが、これはエクセルの設定次第によってデフォルトで書かれるものであります。
直接動作に影響を与えるものではないので、無視して構いません。
記述するコードは下記の通りです。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 |
'関数名、シートにおいて、この名前で表示される Sub Work() '内部変数、ループした回数を格納する(Integer型: 整数値を扱う際に使用) Dim k As Integer '内部変数、Range("B2:B11")に含まれるセルを一つ格納する(Range型: セルを扱う際に使用) Dim rng As Range '内部変数、空白を含めない平均値を格納する(Double型: 実数を扱う際に使用) Dim Ave1 As Double '内部変数、空白を0とみなす平均値を格納する(Double型: 実数を扱う際に使用) Dim Ave2 As Double '空白を含めない平均値を求める 'WorksheetFunction.Averageはシートで使用しているAverage関数と同等 Ave1 = WorksheetFunction.Average(Range("B2:B11")) 'セルB2, B3, ... B11の順でrngに格納 For Each rng In Range("B2:B11") 'rngが空でない場合のみに下記を実行する If (rng.Value <> "") Then 'Ave2にrngで取得されてきた値を加算する Ave2 = Ave2 + rng.Value End If '1ループにつき1加算 k = k + 1 'ループの最後はNextを書く Next 'どちらが希望か確認する 'MsgBox : メッセージを表示するためのメソッド 'vbYesNo: メッセージに「はい」「いいえ」のボタンを表示する 'vbYes: 「はい」が選択された際にMsgBoxが出力する値 If MsgBox("空白を含めますか?", vbYesNo, "Confirm") = vbYes Then '「はい」が選択された Range("E1").Value = Ave2 / 10 Else '「いいえ」が選択された Range("E1").Value = Ave1 '関数の終わり End If |
なお、’(シングルクォーテーション)の後ろに書いているのはコメントです。
書かなくても動作に影響を与えるものではないですが、後々のメンテナンス、引継ぎの際には必要となるので、書く習慣をつけておいた方が無難です。
今回は、当コード内における解説を記載しておきましたので、しっかり確認しましょう。
ここまでできたらVBEを閉じて構いません。シート上でAlt+F8と押します。
下記ダイアログに作成したマクロ「Work」を選択して、実行をクリックします。
下記メッセージが表示されればコードに誤りはありません。
「はい」を選択すると「空白を含める」平均値が「いいえ」を選択すると「空白を含めない」平均値がそれぞれ出力されます。
まとめ エクセルにて空白を除く平均や空白を含む平均を出す方法
この記事では「エクセルにて空白を除く平均や空白を含む平均を出す方法」について解説しました。
空白をどのような扱いにするかはその時々で変わることではありますが、大事なのはお互いの認識が合っているかといったことになります。
場合によっては想定していない値が出てきたとクレームに発展することもあるでしょう。
ロジックを作りだす能力も勿論ですが、依頼部署とのコミュニケーションはそれ以上に大事であることを忘れないようにしたいものです。
