AVERAGEIFSとは？条件に合うデータだけ平均できる便利関数
Excelで売上データや商品リストを扱っていると、「全体の平均」ではなく、特定の条件に合うデータだけの平均を出したい場面があります。たとえば、「商品Aだけの平均単価を知りたい」「東京支店の平均販売価格を出したい」「今月分だけの平均単価を集計したい」といったケースです。
そんなときに便利なのが、AVERAGEIFS関数です。AVERAGEIFSは、指定した条件に一致するデータだけを対象にして、平均値を計算できるExcel関数です。読み方は「アベレージイフエス」で、複数条件に対応しているのが大きな特徴です。
似た関数にAVERAGEIFがありますが、こちらは基本的に条件を1つだけ指定して平均を出す関数です。一方、AVERAGEIFSでは「商品名がA」かつ「担当者が田中さん」かつ「日付が4月」といったように、複数の条件を組み合わせて集計できます。
たとえば営業データで、次のような分析をしたいときに役立ちます。
- 商品別の平均単価を出す
- 担当者別の平均販売価格を比較する
- 期間を指定して平均単価を確認する
- エリア別・店舗別の単価傾向を把握する
ビジネスでは、単に売上合計を見るだけでは判断できないことが多くあります。たとえば売上金額が高くても、実は単価が低く、販売数量でカバーしているだけかもしれません。逆に、販売数は少なくても平均単価が高い商品は、利益率の高い重要商品である可能性があります。
AVERAGEIFSを使えば、こうした条件別の平均単価をすばやく確認できるため、会議資料の作成や営業分析、在庫管理、価格戦略の検討にも活用できます。毎回フィルターをかけて手作業で平均を出す必要がなくなるので、作業時間の短縮にもつながります。
まずはAVERAGEIFSを「条件に合う行だけを自動で探して、その平均を出してくれる関数」と覚えておくとわかりやすいです。次の章では、平均単価を正しく計算するために、事前に確認しておきたい表の作り方について見ていきましょう。
平均単価を出す前に確認したい表の作り方
AVERAGEIFSで平均単価を正しく出すには、関数を書く前に集計しやすい表になっているかを確認することが大切です。表の作り方が曖昧だと、数式は合っているのに結果がズレたり、エラーになったりします。
まず、平均単価を出すための元データには、最低限次のような項目を用意しておくと扱いやすくなります。
| 日付 | 商品名 | 担当者 | 数量 | 売上金額 | 単価 |
|---|---|---|---|---|---|
| 2025/4/1 | 商品A | 田中 | 10 | 50,000 | 5,000 |
ここで重要なのが、平均を取りたい列を明確にしておくことです。今回のテーマである「平均単価」を出したい場合は、「単価」列が必要です。もし単価列がない場合は、売上金額を数量で割って単価を求める列を追加しておきましょう。
たとえば、売上金額がE列、数量がD列にある場合、単価列には次のような計算式を入れます。
=E2/D2このようにして1行ごとの単価を出しておくと、AVERAGEIFSではその単価列を平均対象として指定できます。
次に注意したいのが、商品名や担当者名の表記ゆれです。「商品A」と「商品Ａ」、「田中」と「田中さん」のように表記が混ざっていると、Excel上では別のデータとして扱われます。条件別に正しく平均を出すためには、商品名・担当者名・支店名などの入力ルールを統一しておきましょう。
また、空白行や結合セルも避けるのがおすすめです。見た目を整えるためにセルを結合している表は、関数やフィルターで扱いにくくなります。データ集計用の表では、1行に1件の取引データ、1列に1種類の情報を入れる形にすると、後から分析しやすくなります。
日付を条件に使う場合は、日付が文字列になっていないかも確認しましょう。見た目は「2025/4/1」でも、文字列として入力されていると期間指定の条件がうまく働かないことがあります。日付列はExcelの日付形式で入力しておくと安心です。
可能であれば、元データをExcelのテーブル機能に変換しておくとさらに便利です。表の中を選択して「Ctrl + T」を押すとテーブル化でき、データを追加したときに数式の範囲も自動で広がりやすくなります。
AVERAGEIFSは便利な関数ですが、元の表が整理されていてこそ力を発揮します。まずは「単価列があるか」「条件に使う列の表記が統一されているか」「空白や結合セルがないか」を確認してから、実際の計算式を作っていきましょう。
基本式：AVERAGEIFSで条件別の平均単価を計算する方法
ここからは、実際にAVERAGEIFSで条件別の平均単価を出す基本式を見ていきましょう。まずは一番シンプルな例として、「商品名が商品Aのデータだけを対象にして、単価の平均を出す」ケースで考えます。
AVERAGEIFSの基本的な書き方は、次の通りです。
=AVERAGEIFS(平均対象範囲, 条件範囲1, 条件1)それぞれの意味は以下のようになります。
- 平均対象範囲：平均したい数値が入っている範囲
- 条件範囲1：条件を判定する範囲
- 条件1：平均対象に含めたい条件
たとえば、商品名がB列、単価がF列に入っている表で、「商品A」の平均単価を出したい場合は、次のように入力します。
=AVERAGEIFS(F:F, B:B, "商品A")この式では、F列の単価を平均する対象にしながら、B列の商品名が「商品A」と一致する行だけを集計しています。つまり、商品Bや商品Cの単価は計算から除外され、商品Aだけの平均単価が表示されます。
ポイントは、最初に指定するのが「条件を見る列」ではなく、平均したい列であることです。ここを間違えて商品名の列などを指定してしまうと、正しい平均単価は出せません。平均単価を求めるなら、まず単価列を指定すると覚えておきましょう。
条件をセル参照にすると、さらに使いやすくなります。たとえば、H2セルに商品名を入力し、I2セルに平均単価を表示したい場合は、次のようにします。
=AVERAGEIFS(F:F, B:B, H2)この形にしておけば、H2セルの内容を「商品A」から「商品B」に変更するだけで、計算結果も自動で切り替わります。集計表を作るときは、条件を直接数式に書き込むよりも、セル参照にしておく方が修正しやすく実務向きです。
| 商品名 | 平均単価 |
|---|---|
| 商品A | =AVERAGEIFS(F:F, B:B, H2) |
| 商品B | =AVERAGEIFS(F:F, B:B, H3) |
また、数式を下にコピーして使う場合は、元データの範囲がズレないように注意しましょう。列全体を指定する方法でも問題ありませんが、データ量が多い場合は処理が重くなることがあります。その場合は、次のように必要な範囲だけを指定します。
=AVERAGEIFS($F$2:$F$100, $B$2:$B$100, H2)「$」を付けて範囲を固定しておくと、数式をコピーしても参照範囲がズレません。一方で、条件セルのH2は固定していないため、下にコピーするとH3、H4と自動で変わります。
AVERAGEIFSの基本は、「どの列を平均するか」→「どの列で条件を判定するか」→「条件は何か」の順番で考えることです。この流れを押さえておけば、商品別の平均単価だけでなく、担当者別や支店別の集計にも応用しやすくなります。
複数条件で集計する実践例｜商品別・担当者別・期間別に平均単価を出す
AVERAGEIFSの強みは、複数の条件を組み合わせて平均単価を出せることです。実務では「商品A全体の平均」だけでなく、「商品Aを田中さんが販売したときの平均単価」「4月に販売した商品Aの平均単価」のように、条件を掛け合わせて見たい場面がよくあります。
複数条件を指定する場合の基本形は、次のようになります。
=AVERAGEIFS(平均対象範囲, 条件範囲1, 条件1, 条件範囲2, 条件2, ...)たとえば、A列に日付、B列に商品名、C列に担当者、F列に単価が入っているとします。この表から「商品A」かつ「担当者が田中」の平均単価を出す場合は、次のように入力します。
=AVERAGEIFS(F:F, B:B, "商品A", C:C, "田中")この式では、F列の単価を平均しながら、B列が「商品A」で、さらにC列が「田中」の行だけを対象にしています。つまり、商品Aであっても担当者が佐藤さんのデータは除外され、田中さんが担当した商品Aだけの平均単価を確認できます。
集計表として使うなら、条件は直接入力するよりセル参照にするのがおすすめです。たとえば、H2セルに商品名、I2セルに担当者名を入れて、J2セルに平均単価を表示する場合は次の式になります。
=AVERAGEIFS($F$2:$F$100, $B$2:$B$100, H2, $C$2:$C$100, I2)| 商品名 | 担当者 | 平均単価 |
|---|---|---|
| 商品A | 田中 | =AVERAGEIFS($F$2:$F$100,$B$2:$B$100,H2,$C$2:$C$100,I2) |
| 商品A | 佐藤 | 同じ式をコピー |
期間を条件に含めたい場合は、日付列に対して「開始日以上」「終了日以下」という2つの条件を指定します。たとえば、2025年4月1日から2025年4月30日までの商品Aの平均単価を出すなら、次のようにします。
=AVERAGEIFS(F:F, B:B, "商品A", A:A, ">=2025/4/1", A:A, "<=2025/4/30")日付もセル参照にすると、月を変えるたびに数式を書き換える必要がありません。H2セルに商品名、I2セルに開始日、J2セルに終了日を入力しておけば、次のように管理できます。
=AVERAGEIFS($F$2:$F$100, $B$2:$B$100, H2, $A$2:$A$100, ">="&I2, $A$2:$A$100, "<="&J2)ここでポイントになるのが、比較演算子とセル参照をつなげるときに&を使うことです。「>=I2」と書くのではなく、「”>=”&I2」と書くことで、Excelに「I2の日付以上」という条件として認識させられます。
さらに、商品別・担当者別・期間別をすべて組み合わせることもできます。
=AVERAGEIFS($F$2:$F$100, $B$2:$B$100, H2, $C$2:$C$100, I2, $A$2:$A$100, ">="&J2, $A$2:$A$100, "<="&K2)この式なら、「指定した商品」「指定した担当者」「指定した期間」に一致するデータだけの平均単価を一発で集計できます。営業資料や月次レポートで、担当者ごとの価格傾向を比較したいときに非常に便利です。
複数条件のAVERAGEIFSは、条件を追加するほど難しく見えますが、考え方はシンプルです。平均する列を最初に決め、その後に条件範囲と条件をセットで追加していくだけです。商品別、担当者別、期間別の3パターンを押さえておけば、実務で使う平均単価の集計にはかなり対応しやすくなります。
エラーを防ぐコツと、仕事で使える応用テクニック
AVERAGEIFSは便利な関数ですが、実務で使っていると「エラーになる」「思った平均単価と違う」といったことがあります。最後に、よくあるミスを防ぐコツと、仕事でそのまま使える応用テクニックを押さえておきましょう。
まず注意したいのが、条件に一致するデータがない場合です。AVERAGEIFSは平均対象が1件も見つからないと、#DIV/0!エラーを返します。たとえば「商品A」「田中」「4月」という条件に合うデータが存在しない場合、平均する数値がないためエラーになります。
レポートでエラー表示を見せたくない場合は、IFERROR関数で囲むのがおすすめです。
=IFERROR(AVERAGEIFS($F$2:$F$100, $B$2:$B$100, H2), "")この式なら、条件に合うデータがないときは空白を表示できます。空白ではなく「該当なし」と表示したい場合は、次のようにします。
=IFERROR(AVERAGEIFS($F$2:$F$100, $B$2:$B$100, H2), "該当なし")次に確認したいのが、平均対象範囲と条件範囲の行数がそろっているかです。AVERAGEIFSでは、平均対象範囲と条件範囲のサイズが違うとエラーの原因になります。たとえば、平均対象はF2:F100なのに、条件範囲をB2:B90にしているようなケースです。
=AVERAGEIFS($F$2:$F$100, $B$2:$B$90, H2)このように範囲がズレていると正しく計算できません。数式を作るときは、すべての範囲を同じ行番号でそろえるようにしましょう。
また、単価列に文字列が混ざっている場合も注意が必要です。「5,000円」のように単位付きで入力されていたり、数値が文字列として保存されていたりすると、平均対象として正しく扱われないことがあります。単価列は、表示形式で「通貨」や「桁区切り」に設定し、セルの中身は数値だけにしておくのが基本です。
応用テクニックとして便利なのが、ワイルドカードを使った条件指定です。たとえば、商品名に「Pro」を含む商品の平均単価を出したい場合は、次のように書けます。
=AVERAGEIFS($F$2:$F$100, $B$2:$B$100, "*Pro*")「*」は任意の文字列を表します。そのため、「Proプラン」「商品Pro」「Pro版」など、名前の一部にProを含むデータをまとめて集計できます。商品名のバリエーションが多い資料ではかなり使いやすい方法です。
さらに実務でおすすめなのが、条件入力セルにプルダウンリストを設定することです。商品名や担当者名を手入力すると、表記ゆれによって集計ミスが起きやすくなります。データの入力規則で選択式にしておけば、「田中」と「田中さん」のようなズレを防げます。
最後に覚えておきたいのが、平均単価の考え方です。AVERAGEIFSで出せるのは、各行の単価を単純に平均した値です。一方で、数量の多い取引を重視した平均単価を出したい場合は、売上金額の合計 ÷ 数量の合計で計算する方が実態に近くなります。
=SUMIFS($E$2:$E$100, $B$2:$B$100, H2) / SUMIFS($D$2:$D$100, $B$2:$B$100, H2)会議資料や営業分析では、「単純平均なのか」「数量を加味した平均なのか」で結果の意味が変わります。AVERAGEIFSを使うときは、エラー対策だけでなく、何を知りたい平均なのかも意識して使い分けましょう。
コメント