【Excel】条件にマッチした値の平均値を求める関数★
こんにちは、HARUです！
実務では、マスターデータに集約されたデータのうち、指定の条件に当てはまる値の平均を求めることがよくあります。
たとえば2004年～2023年の月別売上実績が蓄積された表において、特定の月の平均売上金額を表示したいとなったとします。
このとき、約20年分の各月実績を１つ１つ手作業で足し合わせて平均を求めるのは相当手間がかかってしまいます。
そこで今回は、こうしたケースで瞬時に指定した条件の平均値を求める関数をご紹介します。
「4月」や「8月」の単一条件だけでなく、さらに「2020年以降」などといった複数条件の対応方法についても触れますので、ぜひご一読ください！
↓SUMIF/SUMIFS関数、COUNTIF/COUNTIFS関数を解説した記事もチェック！↓
条件を満たす値を合算する
AVERAGEIF関数
条件を満たす値の平均値を求めるには、AVERAGEIF関数を使います。
「AVERAGE（平均）」と「IF（もし～だったら）」が組み合わさった関数なので、用途がイメージしやすいですね！
早速入力していきましょう。
①AVERAGEIF関数を挿入する。
②第１引数「範囲」には、指定する条件が含まれる範囲を選択する。
今回の場合は対象月が検索条件となるため、月の列を参照する。
※この数式を下へコピーすることを考慮し、絶対参照にしておく。
③第２引数「条件」には、取り出したい条件を入力する。
今回はそれぞれの対象月が入力されたセルを参照する。
④第３引数「平均対象範囲」には、平均を求める値が含まれる範囲を選択する。
今回は売上金額の平均を求めるため、売上の列を参照する。
※このセル参照も絶対参照にしておく。
AVERAGEIF関数の設定はこれで完了です。
この状態で決定すると、過年度の4月平均売上金額が返されます。
数式を下へコピーします。
すべての月で過去平均売上金額を求めることができました！
AVERAGEIFS関数
単一条件を満たす値を合計するAVERAGEIF関数に対して、AVERAGEIFS関数は複数の条件にマッチする値の平均を算出できます。
たとえば月ごとに分類し、さらに「2020年以降」の期間に絞って各月の平均売上金額を求めたいとなったとします。
こうして計算対象の条件が追加されたときに便利なのがAVERAGEIFS関数です。
早速入力していきましょう。
①AVERAGEIFS関数を挿入する。
②第１引数「平均対象範囲」には、平均を求める値が含まれる範囲を選択する。
今回は売上金額の平均を返したいため、売上の列を参照する。
※この数式を下へコピーすることを考慮し、絶対参照にしておく。
AVERAGEIFS関数はAVERAGEIF関数と異なり、先に「平均対象範囲」を指定する。
多くの複数条件が設定できるため、その前にあらかじめどの範囲の値の平均を返すかの設定を済ませておくためだ。
③第２引数「条件範囲１」は検索条件となる月の列を参照する。
※このセル参照も絶対参照にしておく。
④第３引数「条件１」には、取り出したい条件を入力する。
今回はそれぞれの月が入力されたセルを参照する。
⑤第４引数「条件範囲２」は追加の検索条件となる年の列を参照する。
※このセル範囲参照も絶対参照にしておく。
⑥第５引数「条件２」には、計算対象の基準となる年を指示する。
今回は2020年以降を対象とするため、">=2020"と入力する。
SUMIFS関数やCOUNTIFS関数でも使ったこの「等号/不等号と値の組み合わせをダブルコーテーションマークで囲む」ことによる"ズバリ"条件の設定は様々な関数で使えるテクニックだから、確実におさえよう！
AVERAGEIFS関数の設定はこれで完了です。
この状態で決定すると、2020年以降の4月の平均売上金額が返されます。
数式を下へコピーします。
いかがでしたか？
今回は指定した条件を満たす値の平均を求める方法をご紹介しました。
データ集計のマストスキルなので、積極的に使ってみてくださいね！
なお、後半に解説したAVERAGEIFS関数は単一の条件設定も可能なので、AVERAGEIF関数の完全上位互換となります。最初からAVERAGEIFS関数のみを活用するのもおススメです。
↓↓Excel操作をとにかく高速化したい方へ↓↓
この記事が気に入ったらサポートをしてみませんか？