
この表で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 |
(50 + 40 + 60) ÷ 3 = 50 というわけですね!
ところでしろねこくん、どうしてこの式で「0以外の平均」にできたかわかる?
"<>0"の部分で条件を指定しているんでしょうか?
じゃあしろねこくん、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つ)で囲む必要があります。
ここで「ん?」と思ったかもしれません。
もういちど構文を見てみましょう。
構文では[平均対象範囲]とありますね。
上記の式では、こちらが省略されたものとなっています。
これを省略せずに使う例を見てみましょう。
平均対象範囲を省略する場合としない場合
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")
- 範囲=平均対象範囲の場合、平均対象範囲を省略できる