Chiquilin Site■07.06.12_Excel：ゼロを除いた平均値を求める
ChiquilinSite
Excel2007以降であれば AVERAGEIF関数を使えばいいので下記のページを参考にする必要はありません。
★その１：何故「0」を無視して計算したいのか
平均値を求める際 「ゼロを計算対象から外したい」という要望は時々あります。これは文字通り「0」を除外して計算したいという場合もありますが そうでない場合もあります。例えば数式やセル参照などで空白が「0」として返っているものを除外したい時などです。条件付き書式・表示形式・オプションの表示で[ゼロ値]のチェックを切る
など 様々な方法で見た目だけは「0」を非表示にできますが 実際の空白とは違うので COUNTや AVERAGEの計算対象からは外れません。
上の表の場合 普通に平均値を求めれば（AVERAGE関数）「3.7」になります。しかし「0」を無視して計算するなら計算結果は「4.625」にならなければなりません。今回はその計算方法をご紹介します。
★その２：「合計÷ゼロ以外の個数」で求める
まずは基本に立ち返ります。平均値はどのように求められているでしょうか。
平均 ＝ 合計 ÷ 個数
小学生じゃあるまいしと云われそうですが ここから考えればゼロを除外して計算する方法が難しくないことがお分かり頂けるのではないでしょうか。
ゼロを除いた平均 ＝ ゼロを除いた合計 ÷ ゼロを除いた個数
です。「ゼロを除いた合計」といいますが ゼロを合計しても所詮はゼロですから これはそのまま足しても問題ありません。つまり SUM関数と COUNTIF関数で
合計とゼロ以外の個数を求めれば ゼロを除いた平均値は求まります。
■C5セル
=SUM(A2:A11)/COUNTIF(A2:A11,"<>0")
分かってしまえば簡単ですね。
★その３：配列計算で求める
どうしても AVERAGE関数で求めたい場合の方法です。
■C5セル
=AVERAGE(IF(A2:A11<>0,A2:A11))
Ctrl + Shift + Enterで確定
便利かどうかは分かりませんが 式は簡潔になります。
他にデータベース関数を使う手もあります。
B1セルに「数値」 B2セルに「<>0」と入力
■C11セル
=DAVERAGE(A1:A11,A1,B1:B2)
普通にEnterで確定
データベース関数は複数条件にも対応させやすいのでお勧めです。
→参考ファイル(033.xls)
