エクセルで平均値を求める際、「小計行は除外したい」「0は平均に含めたくない」というケースは非常によくあります。特にフィルター機能や小計機能を使った表では、通常のAVERAGE関数では意図しない値になることがあります。ここでは、小計と0を除外して正しく平均値を求める方法をわかりやすく解説します。
1. 0を除外して平均を求める基本関数
まず、単純に0を除外した平均を求める場合は「AVERAGEIF関数」を使用します。
例：A列の0以外の平均を求める場合
| 関数 | =AVERAGEIF(A:A,”<>0″) |
この式では、A列の中で「0ではない値」だけを対象に平均を計算します。
2. 小計行も除外したい場合
エクセルの「小計」機能で追加された行を除外したい場合は、SUBTOTAL関数を活用します。
ただし、AVERAGEIF単体では小計行を完全には除外できないため、フィルターや補助列を組み合わせる方法が実用的です。
フィルター後の表示セルだけを平均したい場合。
| 関数 | =SUBTOTAL(101,A2:A100) |
「101」はAVERAGE（平均）を意味し、非表示行を除外します。
3. 小計と0の両方を除外する実践例
最もよく使われる方法は、補助列を作る方法です。
例えばB列に次の式を入れます。
| 式 | =IF(AND(A2<>0,SUBTOTAL(103,A2)),A2,””) |
その後、平均を求めるセルに以下を入力します。
| 式 | =AVERAGE(B:B) |
これで「0ではない」「非表示ではない」「小計を除外した」値だけの平均が求められます。
4. なぜ通常のAVERAGEではダメなのか
AVERAGE関数は、基本的に範囲内の数値をすべて対象にします。
- 0も計算対象になる
- 小計行も含まれる
- フィルターで非表示にしても平均対象になる場合がある
そのため、集計表では条件付き平均を使うのが重要です。
5. よくあるミスと注意点
条件付き平均では、次のようなミスがよくあります。
- 空白セルと0を混同する
- SUBTOTALの番号を間違える
- 小計行自体を平均範囲に含めてしまう
- フィルター非表示と手動非表示を混同する
特にSUBTOTALは番号によって動作が変わります。
| 番号 | 意味 |
|---|---|
| 1 | 平均（手動非表示含む） |
| 101 | 平均（手動非表示除外） |
まとめ
エクセルで「小計」と「0」を除外した平均値を求めるには、AVERAGEIFだけでは不十分な場合があります。単純な0除外なら「=AVERAGEIF(A:A,”<>0″)」、フィルターや小計も考慮するならSUBTOTALや補助列を組み合わせる方法がおすすめです。用途に応じて使い分けることで、正確な平均値を簡単に求められるようになります。
コメント