広告

AVERAGEIFS関数の使い方|複数条件の範囲の平均値を求める

2021年4月2日

=AVERAGEIFS(平均範囲, 条件範囲1, 条件1, [条件範囲2, 条件2, ...])

averageifs_kekka001

AVERAGEIFS(アベレージイフス)関数は、複数の基準に応じた範囲の平均を返します

参考:AVERAGEIFS関数ドキュメントエディタヘルプ

AVERAGEIFS関数の使い方. 構文

AVERAGEIFS関数の構文は以下のとおりです。

=AVERAGEIFS(平均範囲, 条件範囲1, 条件1, [条件範囲2, 条件2, ...])

AVERAGEIFS関数の構文内訳

用語 意味
平均範囲(必須) 必ず指定。平均化する範囲です。
条件範囲1 かならず指定。条件1 に対して検証する範囲です。
条件1 条件範囲1 に適用するパターンまたはテストです。
条件範囲2, 条件2, ... [省略可] - 検証対象となる追加の範囲と条件です。

AVERAGEIFS関数を使うときの注意点

注意1. 条件を囲む時は「"」で囲む

検索条件にあたる条件を指定する際は、「"」で囲みます。シングルクォーテーションで囲むと#ERROR!エラーを返します。

OYAKUN
OYAKUN
この注意点はAVERAGEIF関数と同じですね

以下にサンプルを記載します。

サンプル式 AVERAGEIFS関数結果
=AVERAGEIFS(B4:B8,A4:A8,'<>りんご') #ERROR!
区分 出荷数
りんご 19
りんご 58
ぶどう 55
バナナ 27
ぶどう 71

averageifs_error1

シングルクォーテーションで条件を指定すると、AVERAGEIFS関数の結果がエラーとなってしまいました。

注意2. 範囲指定の行数を一致させる

AVERAGEIFS関数では、平均範囲と条件範囲の範囲指定行数(または列数)を一致させないと、正しい結果が反映されません

以下にサンプルを記載します。

サンプル式 AVERAGEIFS関数結果
=AVERAGEIFS(B4:B8,A4:A6,"<>りんご") #VALUE!
区分 出荷数
りんご 19
りんご 58
ぶどう 55
バナナ 27
ぶどう 71

averageifs_error2

AVERAGEIFS関数でりんご出荷数の平均値を算出したいのに、条件範囲と平均範囲の指定行数が異なるため、VALUE!表示になってしまいました。

  • 条件をシングルクォーテーションで囲む
  • 条件と平均範囲の行数を一致させない

どちらにしてもエラーとして返されます。

OYAKUN
OYAKUN
AVERAGEIF関数と同様に、テキストも囲みは「"」で、範囲は同じ行列数を指定しましょう

AVERAGEIFS関数の使い方. Excelとスプシの違い

AVERAGEIFS関数は、複数の検索条件に一致するすべてのセルの平均値を算出します。

ExcelとGoogleスプレッドシートでは、条件1以降に定義できる条件の数が異なります。

Excel Spreadsheet
1~127個の条件を数値、式、セル参照、文字列で指定する。 検証対象となる追加の範囲と条件。数値、式、セル参照、文字列で指定する。

以上のように、スプレッドシートでAVERAGEIFS関数を使う場合は条件に制限が設けられていません。

AVERAGEIFS関数の実行サンプル

AVERAGEIFS関数を実行するサンプル。

人気 現在価値 販売台数 メーカー
1位 N-BOX 14000 ホンダ
2位 タント 8200 ダイハツ
3位 ムーヴ 6800 ダイハツ
4位 スペーシア 6400 ダイハツ
5位 ミラ 5500 ダイハツ
6位 N-WGN 4600 ホンダ
7位 ハスラー 4100 ダイハツ
8位 ワゴンR 3500 ダイハツ
9位 アルト 3400 スズキ
10位 デイズ 3200 日産

▼人気TOP5のうちダイハツの平均販売台数
【AVERAGEIFSサンプル式】 結果↓
=averageifs(C4:C13,A4:A13,"<=5",D4:D13,"ダイハツ") 6725台
1位 N-BOX 14000 ホンダ
2位 タント 8200 ダイハツ
3位 ムーヴ 6800 ダイハツ
4位 スペーシア 6400 ダイハツ
5位 ミラ 5500 ダイハツ
6位 N-WGN 4600 ホンダ
7位 ハスラー 4100 ダイハツ
8位 ワゴンR 3500 ダイハツ
9位 アルト 3400 スズキ
10位 デイズ 3200 日産

GIFで↓

averageifs_kekka001

AVERAGEIFS関数の実行サンプル結果

画像でAVERAGEIFS実行サンプル結果を確認↓

averageifs_kekka002

AVERAGEIFS関数 × GASのサンプル

AVERAGEIFS関数をGASで利用する際の、かんたんなサンプルを作成してみました。

実装する表のサンプル。

▼人気TOP~のうち~の平均販売台数
X台
1位 N-BOX 14000 ホンダ
2位 タント 8200 ダイハツ
3位 ムーヴ 6800 ダイハツ
4位 スペーシア 6400 ダイハツ
5位 ミラ 5500 ダイハツ
6位 N-WGN 4600 ホンダ
7位 ハスラー 4100 ダイハツ
8位 ワゴンR 3500 ダイハツ
9位 アルト 3400 スズキ
10位 デイズ 3200 日産

実装するGAS

function AVERAGE_IF() {
var ss,sht,lastrw,val01,val02
ss = SpreadsheetApp.getActiveSpreadsheet();
sht = ss.getActiveSheet();
lastrw = sht.getLastRow();
Logger.log(lastrw);
val01 = Browser.inputBox('平均販売台数を知りたいメーカー名を入力',Browser.Buttons.OK_CANCEL);
val02 = Browser.inputBox('1~10までの順位を入力',Browser.Buttons.OK_CANCEL);
if(val01 == "cancel"|| val02 == "cancel"){
Browser.msgBox("処理を中止しました");
return;
} else {
sht.getRange("A1").setValue("▼人気TOP"+val02+"のうち"+val01+"の平均販売台数");
}
// AVERAGEIF関数をC1セルにセット
sht.getRange("A2").setFormula('=AVERAGEIFS('+'C4'+':'+'C'+lastrw+','+'A4'+':'+'A'+lastrw+','+'"<='+val02+'"'+','+'D4'+':'+'D'+lastrw+','+'"'+val01+'"'+')');
}

実行結果をGIFで

averagifs_gas

OYAKUN
OYAKUN
人気車種の販売台数がすぐに分かるようになりました。他にも売れた期間を入れて季節ごとの平均販売台数を出しても面白そうですね。

AVERAGEIFS関数の使い方 まとめ

AVERAGEIFS関数の使い方でした。 有効に利用するには、

  • 指定する値をなるべく隣接させる
  • 条件で文字列を指定する場合は「"」で囲む
  • 平均範囲と条件範囲の行列数は一致させる

といった感じですね。

AVERAGEIFS関数では、複数の条件を指定して平均値を算出できる点がとても便利です。

ただしAVERAGEIFS関数は、AVERAGEIF関数と平均範囲の設置場所が異なる点は注意が必要です。

OYAKUN
OYAKUN
複数条件の指定をうまく使えば、かなり応用の効く関数だと思います。KPI設定時などで活用できるのではないでしょうか。

関連記事:AVERAGE関数の使い方|数値・データの平均値を求める
関数関連:AVEDEV関数の使い方|平均値からデータ偏差の平均を求める
関連:ASC関数の使い方|全角または半角文字に変換する