複数の条件に合うデータの平均を求める…AVERAGEIFS関数

[Excel 2016/2013/2010/2007]

AVERAGEIFS関数を使用すると、表の中から条件に合うデータを探して平均値を求められます。「AVERAGEIF」の末尾に「S」を付けた、言わばAVERAGEIF関数の複数形版で、条件を複数指定できます。

スポンサーリンク

作例…「女性」かつ「B」会員の平均購入金額を求める

図1の売上分析表の「性別」欄(セルB3~B12)から「女」、「会員区分」欄(セルC3~C12)から「B」を検索して、「金額」欄(セルE3~E12)の数値の平均を求めます。

AVERAGEIFS関数の引数[平均対象範囲]に「金額」欄の「E3:E12」、[条件範囲1]に「性別」欄の「B3:B12」、[条件1]に「女」が入力されているセル「H2」、[条件範囲2]に「会員区分」欄の「C3:C12」、[条件2]に「B」が入力されている「H3」を指定すると、女性B会員の金額の平均値を計算できます。

[条件範囲]から[条件]を探し、見つかった行の[平均対象範囲]のデータの平均を求める
=AVERAGEIFS(合計対象範囲, 条件範囲1条件1[, 条件範囲2条件2, …])

セルH6 | =AVERAGEIFS(E3:E12,B3:B12,H2,C3:C12,H3)

図1 AVERAGEIFS関数を使用して、性別が「女」かつ会員区分が「B」である会員の購入金額の平均を求める。

関数解説…AVERAGEIFS関数

書式

=AVERAGEIFS(平均対象範囲, 条件範囲1条件1[, 条件範囲2条件2, …])

条件範囲]から[条件]に一致するデータを探し、見つかった行の[平均対象範囲]のデータの平均を求めます。[条件範囲]と[条件]は必ずペアで指定します。最大127組のペアを指定できます。

引数

引数 指定 説明
平均対象範囲 必須 計算対象の数値が入力されているセル範囲を指定する
条件範囲 1組以上指定 条件判定の対象となるデータが入力されているセル範囲を指定する
条件 1組以上指定 条件判定のための条件を指定する

条件の指定例

図2 文字列、数値、日付の条件で集計してみる。

■文字列条件:条件が入力されているセルを指定するか、条件となる文字列を「”」(ダブルクォーテーション)で囲んで入力します。

=AVERAGEIFS(D3:D9,C3:C9,F3)
=AVERAGEIFS(D3:D9,C3:C9,"旅費交通費")

■数値条件:条件が入力されているセルを指定するか、条件となる数値を直接入力します。

=AVERAGEIFS(D3:D9,B3:B9,F4)
=AVERAGEIFS(D3:D9,B3:B9,18)

■日付条件:条件が入力されているセルを指定するか、条件となる日付を「"」(ダブルクォーテーション)で囲んで入力するか、DATE関数で「DATE(年,月,日)」のように入力します。

=AVERAGEIFS(D3:D9,A3:A9,F5)
=AVERAGEIFS(D3:D9,A3:A9,"2017/4/10")
=AVERAGEIFS(D3:D9,A3:A9,DATE(2017,4,10))

STEPUP…AVERAGEIFS関数をコピーしてクロス集計表を作成するには

AVERAGEIFS関数を使用してクロス集計表を作成する場合は、数式をコピーしたときに引数[平均対象範囲][条件範囲1][条件範囲2]がずれないように、絶対参照(「$A$1」形式)で指定します。引数[条件1][条件2]は複合参照(行と列の一方が絶対参照、もう一方が相対参照、「A$1」「$A1」形式)で指定して、数式をコピーしたときに適切に条件が変化するようにします。

図3の例では、引数[条件1]に指定する「男」「女」はいずれもワークシートの2行目に入力されているので、行のみ絶対参照(H$2)で指定します。また、引数[条件2]に指定する「S」「A」「B」はいずれもワークシートのG列に入力されているので、列のみ絶対参照($G3)で指定します。

先頭のセルに入力する数式
セルH3 | =AVERAGEIFS($E$3:$E$12,$B$3:$B$12,H$2,$C$3:$C$12,$G3)

図3 引数[平均対象範囲][条件範囲1][条件範囲2]を絶対参照で指定。[条件1]を行のみ絶対参照、[条件2]を列のみ絶対参照で指定すると、数式をコピーするだけでクロス集計表を作成できる。

メモ

絶対参照や相対参照を指定するには
数式の入力中に、セルをクリック、またはセル範囲をドラッグした直後に[F4]キーを押すと、指定したセルやセル範囲が絶対参照になります。[F4]キーを押すごとに、行のみ絶対参照、列のみ絶対参照、相対参照と変化します。
スポンサーリンク

関連記事