【Excel】平均値から0を除くにはAVERAGEIF関数を使おう
スポンサーリンク

しろねこ
くろねこさん!
この表で0を除いた在庫数の平均を出したいです!
A B C D
1 商品 種類 価格 在庫数
2 りんご 果物 100 50
3 みかん 果物 80 40
4 バナナ 果物 70 0
5 キャベツ 野菜 150 0
6 レタス 野菜 120 60
平均(0除)

くろねこ
まず結論からいこうか!
この表で0を除いた平均を出すには、次の式を使うよ

=AVERAGEIF(D2:D6,"<>0")

A B C D
1 商品 種類 価格 在庫数
2 りんご 果物 100 50
3 みかん 果物 80 40
4 バナナ 果物 70 0
5 キャベツ 野菜 150 0
6 レタス 野菜 120 60
平均(0除) 60

しろねこ
おおっ、0を除いた平均が出ましたね!
(50 + 40 + 60) ÷ 3 = 50 というわけですね!

くろねこ
そうだね!
ところでしろねこくん、どうしてこの式で「0以外の平均」にできたかわかる?

えっと…なんだか見慣れない記述がありますね
"<>0"の部分で条件を指定しているんでしょうか?

くろねこ
そのとおり!
じゃあしろねこくん、AVERAGEIF関数の構文と、詳しい使い方を見ていこうか

しろねこ
おねがいします!

条件付きの平均値を求めるAVERAGEIF関数の構文と使い方

AVERAGEIF関数の構文
=AVERAGEIF(範囲, 条件, [平均対象範囲])

AVERAGEIF関数では、「0以外を平均する」といった条件を指定することができます。
構文の「条件」という部分です。

冒頭でくろねこ君としろねこ君が使った表と式をもう一度見てみましょう。

A B C D
1 商品 種類 価格 在庫数
2 りんご 果物 100 50
3 みかん 果物 80 40
4 バナナ 果物 70 0
5 キャベツ 野菜 150 0
6 レタス 野菜 120 60
平均(0除) 60

=AVERAGEIF(D2:D6,"<>0")

この式では、「0以外」ということを指定するために、「"<>0"」と入力しています。
これを指定することで、D3~D7セルの数値で、さらに0以外のものが平均されています。
(50 + 40 + 60) ÷ 3 = 60 というわけですね。
条件は" "(ダブルクォーテーション2つ)で囲む必要があります。

ここで「ん?」と思ったかもしれません。
もういちど構文を見てみましょう。

AVERAGEIF関数の構文
=AVERAGEIF(範囲, 条件, [平均対象範囲])

構文では[平均対象範囲]とありますね。
上記の式では、こちらが省略されたものとなっています。
これを省略せずに使う例を見てみましょう。

平均対象範囲を省略する場合としない場合

A B C D
1 商品 種類 価格 在庫数
2 りんご 果物 100 50
3 みかん 果物 80 40
4 バナナ 果物 70 0
5 キャベツ 野菜 150 0
6 レタス 野菜 120 60
平均
(みかん除)
110

この表で、「みかん以外の価格の平均」を出す方法を見てみましょう。
次の式になります。
=AVERAGEIF(A2:A6, "<>みかん", C2:C6)

A2:A6が範囲"<>みかん"が条件C2:C6が平均対象範囲になります。
条件をつけたい範囲を1つめ、つける条件を2つめ、実際に平均をとる数値の範囲を3つめに指定します。

では「在庫数の0を除いた平均を求める」式をあらためて見てみましょう。

A B C D
1 商品 種類 価格 在庫数
2 りんご 果物 100 50
3 みかん 果物 80 40
4 バナナ 果物 70 0
5 キャベツ 野菜 150 0
6 レタス 野菜 120 60
平均(0除) 60

=AVERAGEIF(D2:D6,"<>0")

こちらの式では、条件を付ける範囲=数値を平均する範囲 となっています。
つまり範囲=平均対象範囲となる場合、3つめの入力値を省略できるのです。

ではもうひとつ例を見てみましょう。

A B C D
1 商品 種類 価格 在庫数
2 りんご 果物 100 50
3 みかん 果物 80 40
4 バナナ 果物 70 0
5 キャベツ 野菜 150 0
6 レタス 野菜 120 60
平均
(野菜)
135

この表で、「野菜の価格の平均」と求める式は、以下になります。
=AVERAGEIF(B2:B6, "野菜", C2:C6)

B2:B6が範囲"野菜"が条件C2:C6が平均対象範囲になります。
キャベツとレタスの価格の平均、(150 + 120) ÷ 2 = 135 というわけですね。

じゃあ商品名はみかん以外、なおかつ在庫数が0以外の平均はどうやってとるの?と思うかもしれません。
その場合、Excel2007以降にてAVERAGEIFS関数というものがあります。
AVERAGEIFS関数についてはこちら。

AVERAGEIF関数の使い方 まとめ

  • 構文は =AVERAGEIF(範囲, 条件, [平均対象範囲])
  • 条件は" "で囲って指定する(例 「0以外」→"<>0")
  • 範囲=平均対象範囲の場合、平均対象範囲を省略できる

 

しろねこ
平均をだすとき、条件を指定できるのはすごく便利ですね!

くろねこ
そうだね、特に「0を除いた平均を出す」機会はよくあるから、覚えておくとすぐ対応できるね
スポンサーリンク
おすすめの記事