【エクセル】AVERAGEIFS関数の使い方。複数の列に異なる条件で平均値を求める方法

エクセルのAVERAGEIFS関数で複数の条件で平均値を算出する方法を解説

Excel(エクセル)で条件に合う平均値を求めるときはAVERAGEIF関数を使うと便利です。

ただ、複数の条件を設定したい場合は、AVERAGEIF関数では対応できません。

複数条件に合う平均値を求める時は、AVERAGEIFS関数を利用しましょう。

この記事では、AVERAGEIFS関数の使い方と、AVERAGEIFS関数を使った実例OR条件での平均値の算出方法、エラーの解消方法を紹介します。

エクセルのAVERAGEIFS関数とは

AVERAGEIFSの読み方は、アベレージ・イフスです。

アベレージが「平均」、イフが「もし」、スが「複数系を表すS」を意味しているので、AVERAGEIFS関数は、複数の条件に一致する平均値を求める関数す。

例えば、下の例のように、「30歳以上」でかつ、「男性」という2つの条件で平均体重を求めることができます。

AVERAGEIFS関数の使い方(イメージ)
AVERAGEIFS関数の使い方(イメージ)

AVERAGEIFS関数は、AND条件(かつ)で平均値を算出します。

下の図でいうと、赤色の円と黄色の円が重なった青色の部分が平均値を算出する範囲です。

AVERAGEIFS関数はAND条件で平均値を算出
AVERAGEIFS関数はAND条件で平均値を算出

AVERAGEIFS関数の構文と引数

エクセルのAVERAGEIFS関数の構文(書式)と引数は、以下のとおりです。

条件は合計127個まで設定することができます。

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

それぞれの引数の入力内容は、以下のとおりです。

第1引数平均範囲」:入力必須です。平均値を求めたい値が入力されたセル範囲を指定します(例:D2:D10)

第2,4・・引数条件範囲」:条件を設定する対象のセル範囲を指定します。第1引数とセル範囲のサイズと位置関係を合わせる必要があります。(例:B2:B10)

第3,5・・引数条件」:「条件範囲」から、どんな条件で平均値を算出するかを設定します。セルを指定せず、文字列を指定する場合は「”」で囲みます。(例:2、"男"、">=32")

まずは、AVERAGEIFS関数の引数がどのセル範囲を指定し、条件はどのように指定しているか、下の図を参考にしてみてください。

図解.AVERAGEIFS関数の構文と引数のイメージ図
図解.AVERAGEIFS関数の構文と引数のイメージ図

「条件」に使う記号(比較演算子やワイルドカード)

AVERAGEIFS関数に設定する条件をどう設定したらいいか悩む人は多いです。

以上』『より上』『以下』『未満『等しい』『以外』を条件に設定するためには、下表の比較演算子(記号)を利用してください。

 条件 符号 直接入力する場合セルを指定する場合
以上 >=">=30"">="&E11
より大きい >">30"">"&E11
未満 <"<30"“<"&E11
以下 <="<=30"“<="&E11
等しい ="=30“ or "30““=”&E11 or E11
以外 <>"<>30“"<>"&E11

また、記号は半角で入力します。全角で入力すると、関数の結果でエラー「#N/A」が表示されるので注意が必要です。

また、ワイルドカードと呼ばれる「*」(アスタリスク)と「?」(疑問符)という記号を用いることで、「を含む」という条件を設定することも可能です。

ワイルドカード文字入力例説明
*(アスタリスク)*県「県」で終わる文字列
?(疑問符)? ?県2文字+「県」の文字列

ワイルドカードの使い方と実例は以下の記事で紹介していますので、参考にしてみてください。

AVERAGEIFS関数の使い方と手順を紹介

それでは、実際にAVERAGEIFS関数を使う手順を紹介します。

今回は、「30歳以上の男性」の2つの複数条件で平均値を算出します。

手順1.AVERAGEIFS関数を挿入

平均値を表示したいF3セルに、AVERAGEIFS関数を挿入します。

セルに「=average」と入力すると、利用する関数の候補が表示されます。

その中からAVERAGEIFS関数を選択し、 「Tab 」キーを押します。(ダブルクリックで選択も可)

手順1.AVERAGEIFS関数の使い方(関数の候補を表示)
手順1.AVERAGEIFS関数の使い方(関数の候補を表示)

そうすると、AVERAGEIFS関数を挿入することができます。

=AVERAGEIFS(

手順1.AVERAGEIFS関数の使い方(関数の挿入)
手順1.AVERAGEIFS関数の使い方(関数の挿入)

手順2.第1引数「平均範囲」を指定

次に、第1引数「平均範囲」を指定します。

平均値を算出したいセル範囲はD列にあるので、D3セルからD8セルまでドラッグして範囲を選択します。ドラッグしたら、次の引数を選択するため「,」(カンマ)を入力します。

手順2.AVERAGEIFS関数の使い方(平均範囲を設定)
手順2.AVERAGEIFS関数の使い方(平均範囲を設定)

手順3.第2,3引数に第1条件と範囲を指定

第2引数「条件範囲1」を指定します。

条件1の対象のセル範囲はA列にあるので、A3セルからA8セルまでドラッグして範囲を選択します。ドラッグしたら、次の引数を選択するため「,」(カンマ)を入力します。

次に、「条件1」を指定するため、数式に「”男”」と入力して、「,」(カンマ)を入力します。

=AVERAGEIFS(D3:D8,B3:B8,"男",

手順3.AVERAGEIFS関数の使い方(条件1、条件範囲1を設定)
手順3.AVERAGEIFS関数の使い方(条件1、条件範囲1を設定)

手順4.第4,5引数に第2条件と範囲を指定

手順3と同様の手順で、第4引数「条件範囲2」と第5引数「条件2」を指定します。

30歳以上を条件にしたいので、条件は「">=30"」と入力します。

=AVERAGEIFS(D3:D8,B3:B8,"男",C3:C8,">=30")

手順4.AVERAGEIFS関数の使い方(条件2、条件範囲2を設定)
手順4.AVERAGEIFS関数の使い方(条件2、条件範囲2を設定)

手順5.AVERAGEIFS関数が完成(結果を確認)

AVERAGEIFS関数が完成したので、「Enter」キーを押してください。

そうすると、「30歳以上の男性」の平均体重が「63.3kg」と算出することができました。

手順5.AVERAGEIFS関数の使い方(完成を確認)
手順5.AVERAGEIFS関数の使い方(完成を確認)

AVERAGEIFS関数が書きづらい(指定しづらい)場合

AVERAGEIFS関数は、条件を増やすと、「条件範囲1、条件1」「条件範囲2、条件2」「条件範囲3、条件3」といったように、引数が増えて、関数が書きづらくなります。

そんなときは、無理をしてセルに直接入力しようとせず、数式バーの横にある「関数の挿入」ボタンを使いましょう。

AVERAGEIFS関数を「関数の挿入」ボタン
AVERAGEIFS関数を「関数の挿入」ボタン

「関数の挿入」ダイアログボックスでは、下のように、1つずつの引数を設定しやく、引数間の「,」の入力も不要なので、関数が作成しやすいです。

「関数の挿入」ダイアログボックスは関数の作成を容易にする
「関数の挿入」ダイアログボックスは関数の作成を容易にする

AVERAGEIFS関数を使った実例1(単一条件)

AVERAGEIFS関数は、AVERAGEIF関数としても利用できます。

つまり、複数の条件ではなく、1つの条件(単一条件)で平均値を求めることができます。

下の例のように、第2引数に「条件範囲」、第3引数「条件」に「"<>"&"小学1年生"」と入力することで、C列が「小学1年生以外」の平均体重をD列から求めることができます。

=AVERAGEIFS(D3:D8,C3:C8,"<>"&"小学1年生")

実例1.AVERAGEIFS関数を単一条件で利用
実例1.AVERAGEIFS関数を単一条件で利用

AVERAGEIFS関数を使った実例2(異なる列に複数条件)

AVERAGEIFS関数で、異なる列にそれぞれ条件を設定して、平均値を求める方法を紹介します。

下の例のように、C列は「小学1年生以外」を条件に、D列は「男性」を条件にして、平均値を算出します。

AVERAGEIFS関数はAND条件(かつ)で平均値を算出するので、5行目と7行目のデータが条件に当てはまる対象となります。

=AVERAGEIFS(E3:E8,C3:C8,"<>"&"小学1年生",D3:D8,"男")

実例2.AVERAGEIFS関数を使って、異なる列に複数条件を指定
実例2.AVERAGEIFS関数を使って、異なる列に複数条件を指定

AVERAGEIFS関数を使った実例3(数値0を除く)

AVERAGEIFS関数は空白セルは計算の対象から外してくれますが、数値0は平均値の計算の対象に含まれます。

もし、数値0を計算から除いて平均値を算出する場合は、「0を除く」という条件を引数に追加します。

引数「条件」に「"<>0"」と入力すると、数値が0以外のセルの平均値を算出することができます。

=AVERAGEIFS(E3:E8,C3:C8,"<>"&"小学1年生",D3:D8,"男",E3:E8,"<>0")

AVERAGEIFS関数を使った実例3(数値0を除く)
AVERAGEIFS関数を使った実例3(数値0を除く)

AVERAGEIFS関数を使った実例4(同じ条件範囲に複数の条件)

AVERAGEIFS関数で、同じ条件範囲に複数の条件を設定することは出来ません

引数に同じ条件範囲を設定すると、「DIV/0!」というエラーが表示されます。

それは、AVERAGEIFS関数がAND条件で利用されるように出来ているので、すべての条件が重なるものが存在しなくなるからです。

例えば、下の図のように、C列から「小学2年生」「小学3年生」のものの平均値を算出しようとしても、エラーが表示されます。

AVERAGEIFS関数は同じ条件範囲を設定するとエラーが表示される
AVERAGEIFS関数は同じ条件範囲を設定するとエラーが表示される

そのため、OR条件(かつ)で平均値を算出したい場合は、SUMIF関数でとCOUNTIF関数を使って算出します。

=(SUMIF(C3:C8,"小学2年生",D3:D8)+SUMIF(C3:C8,"小学3年生",D3:D8))/(COUNTIF(C3:C8,"小学2年生")+COUNTIF(C3:C8,"小学3年生"))

同じセル範囲から複数の条件で平均値を算出する方法
同じセル範囲から複数の条件で平均値を算出する方法

1.SUMIF関数を2回使って「小学2年生」と「小学3年生」の体重の合計値を算出
2.COUNTIF関数を2回使って「小学2年生」と「小学3年生」のセルの数を算出
3.1の結果を2の結果で割ることで平均値を算出

関数が長くなるのを防ぎたいという場合は、下の見出しで紹介するDAVERAGE関数を使った方法を利用してください。

OR条件で平均値を算出する方法(DAVERAGE関数を利用)

OR条件で平均値を算出するときは、DAVERAGE関数(ディーアベレージ)を利用します。

DAVERAGE関数はAND条件、OR条件、その複合条件など複雑な条件指定に対応して、平均値を求める関数です。

DAVERAGE関数の書式と引数は以下のとおりです。

=DAVERAGE(データベース,フィールド,条件)

今回は、OR条件に絞って、同じ列に複数の条件を指定する方法を紹介します。

同じ列に条件を指定する場合は、DAVERAGE関数の引数を以下の図解を参考に設定してください。

=DAVERAGE(B2:D9,D2,G7:G9)

OR条件で平均値を算出する方法(DAVERAGE関数を利用) 同じ列に複数条件
OR条件で平均値を算出する方法(DAVERAGE関数を利用) 同じ列に複数条件

第3引数「条件」に使用する条件表は、G7~G9セルのように、見出しも含めて必ず上下のセルに並べて設定します。

そうすると、G3セルに「担当者A」と「担当者B」の平均販売高がOR条件で「412.5」と算出することができます。

AVERAGEIFS関数でエラー「#VALUE!」「DIV/0!」が表示される場合

AVERAGEIFS関数で「#VALUE!」や「DIV/0!」のエラーが表示されたり、警告(注意)が表示される原因は、引数の設定方法が誤っているためです。

引数「条件」の設定に利用する「>=」「<=」「>」「<」の記号を全角で入力すると、DIV/0!というエラーが発生します。必ず、半角で入力しましょう。

下の例の場合、全角の「>」が数式に混在しているためエラーが発生しています。

=AVERAGEIFS(D3:D8,C3:C8,"<"&"小学1年生")

AVERAGEIFS関数で「DIV/0!」のエラーが表示される原因
AVERAGEIFS関数で「DIV/0!」のエラーが表示される原因

また、平均範囲や条件範囲で指定しているセル範囲のサイズが異なっていたり、位置関係がズレていると、下の図のように、「#VALUE!」というエラーが発生します。

=AVERAGEIFS(D3:D9,C3:C8,"<>"&"小学1年生")

AVERAGEIFS関数で「#VALUE!」のエラーが表示される原因
AVERAGEIFS関数で「#VALUE!」のエラーが表示される原因

また、条件に利用する「”」「&」などの記号の位置を間違っていると、「この数式には問題があります。」というエラーが発生するので、注意しましょう。