今回は、ExcelのAVERAGEIFSで条件に合う平均を出す方法を紹介します。
AVERAGEIFSでできること
AVERAGEIFSは、指定した条件に合うデータだけを対象にして平均を求める関数です。部署別、担当者別、商品別、期間別など、条件を組み合わせて確認したいときに使えます。単純な平均では全体の値しか分かりませんが、条件を付けることで、見たい範囲に絞った確認ができます。統計的な分析ではなく、日常の集計表を扱う場面でも使いやすい関数です。
基本の考え方
AVERAGEIFSでは、平均を出したい範囲と、条件を判定する範囲を分けて指定します。たとえば、金額の平均を出したい場合、平均対象範囲は金額の列です。条件範囲には部署の列や日付の列を指定します。条件範囲と条件を組にして追加していくため、関数を読むときは「何を平均するか」「どの条件で絞るか」を分けて見ると理解しやすくなります。
式を組み立てる順番
- 平均を出したい数値の列を決める
- 絞り込みに使う列を決める
- 条件の値を入力するセルを用意する
- AVERAGEIFSに範囲と条件を順番に指定する
- 結果が想定する対象だけを含んでいるか確認する
条件をセル参照にする
条件を数式内に直接書くこともできますが、セル参照にしておくと再利用しやすくなります。部署名や担当者名を入力するセルを作り、そのセルを条件に指定します。すると、条件セルの値を変えるだけで結果も変わります。集計表を他の人が使う場合も、どこを変更すればよいか分かりやすくなります。
- 条件セルにはラベルを付ける
- 入力候補が決まっている場合はプルダウンを使う
- 条件セルと結果セルを近くに置く
- 数式内に長い条件文字を直接書きすぎない
日付条件を扱う
AVERAGEIFSでは、日付を条件に使うこともできます。開始日以上、終了日以下のように条件を分けて指定すると、一定期間の平均を出せます。日付を直接式に書くと入力ミスに気付きにくいため、開始日セルと終了日セルを用意し、条件式とセル参照を組み合わせる方法が扱いやすいです。
日付条件の例
- 開始日以上を指定する
- 終了日以下を指定する
- 日付セルの表示形式をそろえる
- 条件に使う日付列が正しい日付データか確認する
空白や文字列に注意する
平均対象範囲に空白や文字列が混ざっていると、思った結果にならないことがあります。空白は対象外として扱われますが、入力漏れなのか対象外なのかを確認しておく必要があります。文字列として入力された数値も注意が必要です。集計前に、数値列が数値として扱われているか確認すると、後から原因を探す時間を減らせます。
条件範囲の行数をそろえる
AVERAGEIFSでは、平均対象範囲と条件範囲の大きさをそろえる必要があります。平均対象がB2:B100なら、条件範囲も同じ行数にします。範囲の開始行や終了行がずれていると、エラーや誤った集計につながります。表を作るときは、テーブル機能を使うと範囲が広がったときにも数式を管理しやすくなります。
条件が見つからない場合の表示
条件に合うデータがない場合、AVERAGEIFSはエラーになることがあります。集計表として見せる場合は、IFERRORと組み合わせて「該当なし」や空欄を表示する方法があります。ただし、エラーをすべて隠すと原因が分かりにくくなるため、作成中はエラーのまま確認し、完成後に表示を整えるほうが原因を追いやすくなります。
集計表を読みやすくする工夫
関数だけでなく、表の配置も大切です。条件セル、結果セル、元データの位置が離れすぎると、何を集計しているか分かりにくくなります。集計条件を上部にまとめ、結果をその下に置くと、使う人が流れを追いやすくなります。元データには見出し行を付け、フィルターで条件と結果を目視確認できるようにしておきます。
確認しやすい配置
- 上部に条件入力欄を置く
- その下に平均結果を置く
- 元データには見出しとフィルターを設定する
- 条件セルには入力例を添える
- 結果が空欄になる場合の意味を決める
よく使う組み合わせ
AVERAGEIFSは、SUMIFSやCOUNTIFSと並べて使うと、同じ条件で合計、件数、平均を確認できます。条件セルを共通にしておけば、部署名や期間を変えたときに複数の結果が同時に切り替わります。月次資料や確認用シートでは、同じ条件を複数の関数で使い回す形にすると、表の見方がそろいます。
条件セルの入力ミスを防ぐ
AVERAGEIFSは条件が少し違うだけで結果が変わります。部署名や担当者名を手入力する場合、全角と半角、余分な空白、表記ゆれに注意が必要です。条件セルに入力規則のプルダウンを設定すると、元データと同じ表記を選びやすくなります。条件に使う値が頻繁に増える場合は、候補リストの更新方法も決めておきます。
ワイルドカードを使う場面
文字条件では、完全一致だけでなく、特定の語を含む条件を使いたい場合があります。そのときはワイルドカードを使えます。ただし、似た名前まで含まれることがあるため、集計結果を確認する習慣が必要です。商品名や案件名の一部で集計する場合は、抽出条件が広すぎないか、フィルターで対象行を見てから使うと安心です。
元データを整えてから関数を作る
関数で集計する前に、元データの列見出し、日付、数値、空白を確認します。元データが整っていない状態で関数だけを直そうとすると、原因が関数なのかデータなのか分かりにくくなります。入力欄、計算欄、確認欄を分け、不要な結合セルを避けると、AVERAGEIFS以外の集計関数にも使いやすい表になります。
確認用に対象件数も並べる
AVERAGEIFSの結果だけを見ても、どのくらいのデータが対象になっているか分かりにくい場合があります。平均の横にCOUNTIFSで対象件数を表示しておくと、条件が狭すぎる、または広すぎることに気付きやすくなります。件数が空欄や想定外の値になったときは、条件セルや元データの表記を確認します。
集計条件を見出しに残す
集計結果をコピーして報告資料に貼る場合、条件が分からない結果だけが残ると誤解につながります。シート上では、部署名、期間、対象区分などの条件を見出しとして結果の近くに置きます。資料へ転記するときも、平均値だけでなく条件名を添えると、後から見返したときに何を示す値か分かりやすくなります。
まとめ
ExcelのAVERAGEIFSは、条件に合うデータの平均を求めるときに役立ちます。平均対象範囲、条件範囲、条件の関係を分けて考え、条件はセル参照にしておくと再利用しやすくなります。日付条件、空白、範囲のずれに注意しながら、SUMIFSやCOUNTIFSと組み合わせると、日常の集計表を扱いやすくできます。