Excel(エクセル）で条件に合う平均値を求めるときはAVERAGEIF関数を使うと便利です。
ただ、複数の条件を設定したい場合は、AVERAGEIF関数では対応できません。
複数条件に合う平均値を求める時は、AVERAGEIFS関数を利用しましょう。
この記事では、AVERAGEIFS関数の使い方と、AVERAGEIFS関数を使った実例、OR条件での平均値の算出方法、エラーの解消方法を紹介します。
エクセルのAVERAGEIFS関数とは
AVERAGEIFSの読み方は、アベレージ・イフスです。
アベレージが「平均」、イフが「もし」、スが「複数系を表すS」を意味しているので、AVERAGEIFS関数は、複数の条件に一致する平均値を求める関数です。
例えば、下の例のように、「30歳以上」でかつ、「男性」という２つの条件で平均体重を求めることができます。
AVERAGEIFS関数は、AND条件(かつ)で平均値を算出します。
下の図でいうと、赤色の円と黄色の円が重なった青色の部分が平均値を算出する範囲です。
AVERAGEIFS関数の構文と引数
エクセルのAVERAGEIFS関数の構文(書式)と引数は、以下のとおりです。
条件は合計127個まで設定することができます。
＝AVERAGEIFS(平均範囲, 条件範囲 1, 条件 1, [条件範囲 2, 条件 2], ...)
それぞれの引数の入力内容は、以下のとおりです。
第１引数「平均範囲」：入力必須です。平均値を求めたい値が入力されたセル範囲を指定します（例：D2:D10）
第２,４・・引数「条件範囲」：条件を設定する対象のセル範囲を指定します。第１引数とセル範囲のサイズと位置関係を合わせる必要があります。（例：B2:B10）
第３,５・・引数「条件」：「条件範囲」から、どんな条件で平均値を算出するかを設定します。セルを指定せず、文字列を指定する場合は「”」で囲みます。（例：2、"男"、">=32"）
まずは、AVERAGEIFS関数の引数がどのセル範囲を指定し、条件はどのように指定しているか、下の図を参考にしてみてください。
「条件」に使う記号(比較演算子やワイルドカード)
AVERAGEIFS関数に設定する条件をどう設定したらいいか悩む人は多いです。
『以上』『より上』『以下』『未満』『等しい』『以外』を条件に設定するためには、下表の比較演算子(記号)を利用してください。
| 条件 | 符号 | 直接入力する場合 | セルを指定する場合 |
| 以上 | >= | ">=30" | ">="&E11 |
| より大きい | > | ">30" | ">"&E11 |
| 未満 | < | "<30" | “<"&E11 |
| 以下 | <= | "<=30" | “<="&E11 |
| 等しい | = | "=30“ or "30“ | “=”&E11 or E11 |
| 以外 | <> | "<>30“ | "<>"&E11 |
また、記号は半角で入力します。全角で入力すると、関数の結果でエラー「#N/A」が表示されるので注意が必要です。
また、ワイルドカードと呼ばれる「*」(アスタリスク)と「?」(疑問符)という記号を用いることで、「を含む」という条件を設定することも可能です。
| ワイルドカード文字 | 入力例 | 説明 |
| *（アスタリスク） | *県 | 「県」で終わる文字列 |
| ?(疑問符) | ? ?県 | ２文字＋「県」の文字列 |
ワイルドカードの使い方と実例は以下の記事で紹介していますので、参考にしてみてください。
AVERAGEIFS関数の使い方と手順を紹介
それでは、実際にAVERAGEIFS関数を使う手順を紹介します。
今回は、「30歳以上の男性」の２つの複数条件で平均値を算出します。
手順１．AVERAGEIFS関数を挿入
平均値を表示したいF3セルに、AVERAGEIFS関数を挿入します。
セルに「=average」と入力すると、利用する関数の候補が表示されます。
その中からAVERAGEIFS関数を選択し、 「Tab 」キーを押します。(ダブルクリックで選択も可）
そうすると、AVERAGEIFS関数を挿入することができます。
=AVERAGEIFS(
手順２．第１引数「平均範囲」を指定
次に、第１引数「平均範囲」を指定します。
平均値を算出したいセル範囲はD列にあるので、D3セルからD8セルまでドラッグして範囲を選択します。ドラッグしたら、次の引数を選択するため「,」(カンマ)を入力します。
手順３．第２，３引数に第１条件と範囲を指定
第２引数「条件範囲１」を指定します。
条件１の対象のセル範囲はB列にあるので、B3セルからB8セルまでドラッグして範囲を選択します。ドラッグしたら、次の引数を選択するため「,」(カンマ)を入力します。
次に、「条件1」を指定するため、数式に「”男”」と入力して、「,」(カンマ)を入力します。
=AVERAGEIFS(D3:D8,B3:B8,"男",
手順４．第４，５引数に第２条件と範囲を指定
手順３と同様の手順で、第4引数「条件範囲２」と第５引数「条件２」を指定します。
30歳以上を条件にしたいので、条件は「">=30"」と入力します。
=AVERAGEIFS(D3:D8,B3:B8,"男",C3:C8,">=30")
手順５．AVERAGEIFS関数が完成(結果を確認)
AVERAGEIFS関数が完成したので、「Enter」キーを押してください。
そうすると、「30歳以上の男性」の平均体重が「63.3kg」と算出することができました。
AVERAGEIFS関数が書きづらい(指定しづらい)場合
AVERAGEIFS関数は、条件を増やすと、「条件範囲１、条件１」「条件範囲２、条件２」「条件範囲３、条件３」といったように、引数が増えて、関数が書きづらくなります。
そんなときは、無理をしてセルに直接入力しようとせず、数式バーの横にある「関数の挿入」ボタンを使いましょう。
「関数の挿入」ダイアログボックスでは、下のように、１つずつの引数を設定しやく、引数間の「,」の入力も不要なので、関数が作成しやすいです。
AVERAGEIFS関数を使った実例１(単一条件)
AVERAGEIFS関数は、AVERAGEIF関数としても利用できます。
つまり、複数の条件ではなく、１つの条件(単一条件)で平均値を求めることができます。
下の例のように、第２引数に「条件範囲」、第３引数「条件」に「"<>"&"小学１年生"」と入力することで、C列が「小学１年生以外」の平均体重をD列から求めることができます。
=AVERAGEIFS(D3:D8,C3:C8,"<>"&"小学１年生")
AVERAGEIFS関数を使った実例２(異なる列に複数条件)
AVERAGEIFS関数で、異なる列にそれぞれ条件を設定して、平均値を求める方法を紹介します。
下の例のように、C列は「小学１年生以外」を条件に、D列は「男性」を条件にして、平均値を算出します。
AVERAGEIFS関数はAND条件(かつ)で平均値を算出するので、５行目と７行目のデータが条件に当てはまる対象となります。
=AVERAGEIFS(E3:E8,C3:C8,"<>"&"小学１年生",D3:D8,"男")
AVERAGEIFS関数を使った実例３(数値0を除く)
AVERAGEIFS関数は空白セルは計算の対象から外してくれますが、数値0は平均値の計算の対象に含まれます。
もし、数値0を計算から除いて平均値を算出する場合は、「0を除く」という条件を引数に追加します。
引数「条件」に「"<>0"」と入力すると、数値が0以外のセルの平均値を算出することができます。
=AVERAGEIFS(E3:E8,C3:C8,"<>"&"小学１年生",D3:D8,"男",E3:E8,"<>0")
AVERAGEIFS関数を使った実例４(同じ条件範囲に複数の条件)
AVERAGEIFS関数で、同じ条件範囲に複数の条件を設定することは出来ません。
引数に同じ条件範囲を設定すると、「DIV/0!」というエラーが表示されます。
それは、AVERAGEIFS関数がAND条件で利用されるように出来ているので、すべての条件が重なるものが存在しなくなるからです。
例えば、下の図のように、C列から「小学２年生」「小学３年生」のものの平均値を算出しようとしても、エラーが表示されます。
そのため、OR条件(かつ)で平均値を算出したい場合は、SUMIF関数でとCOUNTIF関数を使って算出します。
=(SUMIF(C3:C8,"小学２年生",D3:D8)+SUMIF(C3:C8,"小学３年生",D3:D8))/(COUNTIF(C3:C8,"小学２年生")+COUNTIF(C3:C8,"小学３年生"))
１．SUMIF関数を２回使って「小学２年生」と「小学３年生」の体重の合計値を算出
２．COUNTIF関数を２回使って「小学２年生」と「小学３年生」のセルの数を算出
３．１の結果を２の結果で割ることで平均値を算出
関数が長くなるのを防ぎたいという場合は、下の見出しで紹介するDAVERAGE関数を使った方法を利用してください。
OR条件で平均値を算出する方法(DAVERAGE関数を利用)
OR条件で平均値を算出するときは、DAVERAGE関数(ディーアベレージ)を利用します。
DAVERAGE関数はAND条件、OR条件、その複合条件など複雑な条件指定に対応して、平均値を求める関数です。
DAVERAGE関数の書式と引数は以下のとおりです。
=DAVERAGE(データベース,フィールド,条件)
今回は、OR条件に絞って、同じ列に複数の条件を指定する方法を紹介します。
同じ列に条件を指定する場合は、DAVERAGE関数の引数を以下の図解を参考に設定してください。
=DAVERAGE(B2:D9,D2,G7:G9)
第３引数「条件」に使用する条件表は、G7～G9セルのように、見出しも含めて必ず上下のセルに並べて設定します。
そうすると、G3セルに「担当者A」と「担当者B」の平均販売高がOR条件で「412.5」と算出することができます。
AVERAGEIFS関数でエラー「＃VALUE!」「DIV/0!」が表示される場合
AVERAGEIFS関数で「＃VALUE!」や「DIV/0!」のエラーが表示されたり、警告(注意)が表示される原因は、引数の設定方法が誤っているためです。
引数「条件」の設定に利用する「>=」「<=」「>」「<」の記号を全角で入力すると、「DIV/0!」というエラーが発生します。必ず、半角で入力しましょう。
下の例の場合、全角の「＞」が数式に混在しているためエラーが発生しています。
=AVERAGEIFS(D3:D8,C3:C8,"<＞"&"小学１年生")
また、平均範囲や条件範囲で指定しているセル範囲のサイズが異なっていたり、位置関係がズレていると、下の図のように、「＃VALUE!」というエラーが発生します。
=AVERAGEIFS(D3:D9,C3:C8,"<>"&"小学１年生")
また、条件に利用する「”」「&」などの記号の位置を間違っていると、「この数式には問題があります。」というエラーが発生するので、注意しましょう。