複数の条件下で「平均」を算出する方法‐関数AVERAGEIFS

前回は、「条件付きの合計」を求める関数SUMIFの使い方を紹介した。これとよく似た関数として、「条件付きの平均」を求めるAVERAGEIFという関数も用意されている。さらに、「複数の条件」を指定できるAVERAGEIFSという関数もある。ということで、今回はxxxIF系やxxxIFS系関数の具体的な使用例をいくつか紹介していこう。→連載「定時で上がろう! Excel関数の底力」のこれまでの回はこちらを参照。

条件付きで「平均」を算出できる関数AVERAGEIF

条件に合致するデータについてのみ「平均」を算出したい場合もあるだろう。このような場合に活用できるのが「関数AVERAGEIF」となる。さらに、2つ以上の条件を指定できる「関数AVERAGEIFS」も用意されている。これらの使い方を学ぶことで、xxxIF系やxxxIFS系の関数を自由に使いこなせるようになる。ぜひ、覚えておこう。

• 

  関数AVERAGEIFとAVERAGEIFSの使い方

まずは、関数AVERAGEIFの使い方から紹介していこう。といっても、計算方法が「合計」から「平均」に変化しているだけで、基本的な使い方は関数SUMIFと同じである。第1引数に「条件とするセル範囲」、第2引数に「条件」、第3引数に「平均するセル範囲」を指定すればよい。

■関数AVERAGEIFの書式
　=AVERAGEIF（条件とするセル範囲, 条件, 平均するセル範囲）

今回も、あるショップサイトの受注履歴をまとめた表を使って解説していこう。このショップでは、パソコン向けの「PCサイト」と、スマホ・タブレット向けの「モバイルサイト」の2種類のWebサイトを運営している。ただし、その受注履歴は1つのデータベースにまとめて管理されている。

• 

  受注履歴をまとめた表

このような場合に関数AVERAGEIFを活用すると、サイト別の「1注文あたりの平均金額」を簡単に調べることができる。

例えば、「PCサイト」の平均金額を求めるときは、「条件とするセル範囲」にD3:D12、条件に"PCサイト"、「平均するセル範囲」にE3:E12を指定すればよい。よって、関数ABERAGEIFを以下のように記述する。

• 

  「PCサイト」の平均金額を算出する場合

同様に、「モバイルサイト」の平均金額は、条件を"モバイルサイト"に変更すると求められる。

• 

  「モバイルサイト」の平均金額を算出する場合

今回の例では、それぞれの平均値として以下の図のような結果が表示された。この結果を見ると、「PCサイト」のほうが「1注文あたりの平均金額」が2倍以上も高いことを確認できる。

• 

  関数AVERAGEIFにより算出された平均金額

このように関数AVERAGEIFを使って「条件別の平均値」を求めることで、多角的にデータを分析できる場合もある。応用範囲は幅広いので、ぜひ使い方をマスターしておこう。

複数の条件を指定して「平均」を算出できる関数AVERAGEIFS

SUMIFやAVERAGEIFを使って合計や平均を求めるときに、「複数の条件」を指定したい場合もあるだろう。このような場合は「関数SUMIFS」や「関数AVERAGEIFS」といった関数を利用する。どちらも、関数名の最後をIFSに変更した「xxxIFS系の関数」として覚えておくと、頭を整理しやすいだろう。

xxxIFS系の関数を使用するときは、以下のような書式で関数を記述する決まりになっている。

■関数SUMIFSの書式
　=SUMIFS（合計する範囲, 条件範囲1, 条件1, 条件範囲2, 条件2,・・・）

■関数AVERAGEIFSの書式
　=AVERAGEIFS（平均する範囲, 条件範囲1, 条件1, 条件範囲2, 条件2,・・・）

どちらも「合計（または平均）するセル範囲」を最初に記述する。続けて、「条件とするセル範囲」と「その条件」をペアにして列記していく仕組みになっている。

これまでの連載で紹介したxxxIF系の関数は、合計（または平均）するセル範囲を「第3引数」に指定するのが基本であった。一方、xxxIFS系の関数では「第1引数」に指定する仕様になっている。

よく似た関数なのに「計算するセル範囲」を指定する位置が異なっており、非常に紛らわしくなっている。勘違いしやすい部分なので、間違えないように注意しておこう。分類別に関数の書式をまとめておくと、以下のようになる。

■xxxIF系の関数
　=xxxIF（条件範囲, 条件, 計算する範囲）

■xxxIFS系の関数
　=xxxIFS（計算する範囲, 条件範囲1, 条件1, 条件範囲2, 条件2,・・・）

ここでは、xxxIFS系の具体的な活用例として、関数AVERAGEIFSの使い方を紹介していこう。今度は、ある店舗の1ヵ月の売上を日別にまとめた表を例に解説を進めていく。

• 

  日別の売上をまとめた表

この店舗は「休日」の来客数が多いため、「平日よりも休日のほうが売上は大きい」と考えられる。では実際に、どれくらい売上は変化しているのだろうか？　1日あたりの平均売上を「平日」と「休日」に分けて比較してみよう。

まずは「平日」の平均売上を関数AVERAGEIFSで求めてみる。平日となる曜日は、月、火、水、木、金の5通りが考えられるが、条件を5つも列記するのは面倒なので、逆に考えて「曜日データが土、日、祝ではない」を条件に指定してみよう。

（※）データは全部で30日分あり、ワークシートの11～40行目にデータが記録されている。

• 

  曜日が「土、日、祝」以外の平均売上を算出する場合

「平均を算出するセル範囲」はF11～F40なので、第1引数にはF11:F40を指定する。続けて、1番目の条件となる「曜日が"土"ではない」を指定する。第2引数に「曜日データが記録されているセル範囲」（C11:C40）を指定し、その条件として「"土"ではない」（"<>土"）を第3引数に指定する。以降も同様に、C11:C40のセル範囲に対して「"日"ではない」、「"祝"ではない」の条件を指定すればよい。

これで「平日」の平均売上を算出できる。今回の例では、17万7633円という結果が表示された（小数点以下は四捨五入）。

• 

  関数AVERAGEIFSにより算出された平均売上

続いて、「祝日」の平均売上を求めてみよう。今度は「曜日データが土、日、祝」を条件に指定すればよいので、以下のように関数AVERAGEIFSを記述した。

• 

  曜日が「土、日、祝」の平均売上を算出する場合（失敗例）

しかし、この結果は「#DIV/0!」のエラー表示になってしまう。つまり、正しく平均値を算出できていないことになる。

• 

  関数AVERAGEIFSにより算出された平均売上（失敗例）

このような結果になってしまうのは、「複数の条件がどのように結ばれるか？」を理解できていないことが原因だ。xxxIFS系の関数では、それぞれの条件が「かつ」で結ばれる仕様になっている。

先ほどの例の場合、曜日データが「"土"かつ"日"かつ"祝"に等しい」という条件になる。とはいえ、このような曜日データは実在しない。よって、「#DIV/0!」（0で割り算できない）のエラーになってしまう。

念のため、「平日」の平均売上を求めたときの条件についても再検討しておこう。こちらは、以下の3つを条件に指定した。

　・曜日データが"土"ではない
　・曜日データが"日"ではない
　・曜日データが"祝"ではない

これらを「かつ」で連結すると、「曜日データが"土"でも"日"でも"祝"でもない」と解釈できる。つまり、「平日」のデータについてのみ平均売上を計算する、という指定になる。

一方、「休日」の平均売上を求めるときは、「曜日データが"土"、または"日"、または"祝"に等しい」を条件に指定しなければならない。このように、複数の条件を「または」で結ぶときは、そのままxxxIFS系の関数を利用するのではなく、何らかの工夫が必要となる。

条件を「または」で結ぶ、少し強引な解決策

ということで、複数の条件を「または」で結ぶときの解決策を紹介しておこう。ただし、これは最もスマートな解決策ではなく、少し強引な解決策となる。xxxIF系やxxxIFS系の関数を理解するための一例として捉えていただければ幸いだ。

まずは、基本に戻って「平均値」の算出方法を復習しておこう。平均値は、（データの合計）÷（データの個数）を計算すると求められる。そこで、以下のように計算用のセルを準備した。

• 

  計算用のセルを用意

最初に、「土、日、祝の売上の合計」を求める。これは「土曜日の売上の合計」＋「日曜日の売上の合計」＋「祝日の売上の合計」で計算できる。それぞれの合計は関数SUMIFで求められるので、それらを足し算すればよい、という考え方だ。

• 

  「土、日、祝」の合計売上を関数SUMIFの足し算で算出

続いて、「土、日、祝の日数」を求める。こちらは、曜日データが「"土"のセルの個数」＋「"日"のセルの個数」＋「"祝"のセルの個数」で計算できる。よって、関数COUNTIFを使って以下のように数式を記述すればよい。

• 

  「土、日、祝」の日数を関数COUNTIFの足し算で算出

あとは、「土、日、祝の売上の合計」÷「土、日、祝の日数」を計算するだけ。これで「休日」の平均売上を求めることができる。

• 

  「土、日、祝」の平均売上を数式で算出

• 

  算出された「土、日、祝」の平均売上

今回の例では、「休日」の平均売上は19万8306円という結果が表示された。「平日」の平均売上よりは高いが、その差は2万円程度しかない、というのが実情のようだ。

このように複数の条件を「または」で結びたいときは、各条件の「合計」を関数SUMIFで求めて、それを足し算する、という手法もある。さらには、（各条件の合計）÷（各条件の個数）を計算して「平均」を求めることも可能である。

少し強引ではあるが、数式の「足し算」を利用して「または」の条件を再現する方法もある、と覚えておくと、いずれ役に立つだろう。

分類用の列（フィールド）を用意する方法

もう少しスマートな解決策も紹介しておこう。それは「平日」と「休日」を分類するために、新しい列（フィールド）を用意する方法だ。たとえば、以下の図のように「平/休」の列を追加して、それぞれに「平日」または「休日」の文字データを入力する。

• 

  分類用に「平/休」の列を追加

このような列を用意しておけば、条件は1つだけになるので、関数AVERAGEIFで簡単に「条件付きの平均」を求めることができる。例えば、「平日」の平均売上を求めるときは、以下のように関数AVERAGEIFを記述すればよい。

• 

  「平日」の平均売上を関数AVERAGEIFで算出

関数AVERAGEIFの条件を"休日"に変更して、「休日」の平均売上を求めることも可能だ。

• 

  「休日」の平均売上を関数AVERAGEIFで算出

それぞれの計算結果は、以下の図のようになる。先ほど算出した平均売上と同じ数値が表示されているのを確認できるだろう。

• 

  関数AVERAGEIFにより算出された平均売上

このように「分類用の列」を新たに用意するのも、一つの解決策となる。「平日」や「休日」のデータを入力するのが面倒な作業になるが、そのぶん関数の記述はシンプルになる。Excel関数に詳しい方であれば、「平日」や「休日」のデータを関数で自動入力することも可能だ。各自のスキルや状況に応じて、「どのような手法で計算するか？」を検討するとよいだろう。選択肢が多いほど、状況に合った手法を採用できるはずだ。

そのほか、DSUMやDAVERAGEといった関数を利用する方法も考えられる。ということで次回は、条件を柔軟に指定できる「Dxxx系の関数」の使い方を紹介していこう。