Aさん 商品別や日別などの条件で平均値を集計したいですが、AVERAGEだと限界あります。。
こうした場合の集計に役立つ関数はありますか？
森田 その場合は、関数の「AVERAGEIFS」を活用すると良いですよ！
では、AVERAGEIFSの使い方について解説していきますね。
はじめに
この記事はAVERAGEの使い方を把握していることが前提です。
AVERAGEの詳細は以下の記事をご参照ください。
はじめに この記事はSUMの使い方を把握していることが前提です。 参考記事 SUMの詳細は以下の記事をご参照ください。 数値データの「平均値」を自動計算したい場合は「AVERAGE」が有効 Excelで最も手軽に数値デー …
○○別の「平均値」を集計する場合は「AVERAGEIFS」が有効
実務では、データの特徴や傾向を把握するために、商品別や日別などの切り口で集計する機会は非常に多いもの。
こうした条件別の平均値を集計する際、商品名や日付などの条件に一致するレコードの数値データのみで平均値を計算すれば良いです。
頑張れば、次のようにAVERAGEでも集計は可能です。
ただし、指定するセル数が多ければ多いほど、手作業は増え、誤ったセルを選択するリスクも増えてしまうため、この方法は非効率です。
こんな場合、関数の「AVERAGEIFS」を使いましょう。
AVERAGEIFSは「アベレージ・イフス」と読む。
AVERAGEIFSを使うことで、条件に一致するレコードのみを対象とし、数値データの平均値を計算できます。
AVERAGEIFSの構文
AVERAGEIFSの構文は以下の通りです。
=AVERAGEIFS(平均対象範囲,条件範囲1,条件1,[条件範囲2,条件2],…)
特定の条件に一致する数値の平均（算術平均）を計算します。
| 引数名 | 必須 | データ型 | 説明 |
|---|---|---|---|
| 平均対象範囲 | ◯ | 参照 | 平均値の計算対象のセル範囲を指定します。 |
| 条件範囲1 | ◯ | 参照 | 「条件1」の検索対象のセル範囲を指定します。 |
| 条件1 | ◯ | すべて | 「条件範囲1」内のどのセルを計算対象にするかの条件（値や単一セル）を指定します。 |
| 条件範囲2 ※条件範囲3以降も同様 |
－ | 参照 | 「条件2」の検索対象のセル範囲を指定します。 |
| 条件2 ※数値3以降も同様 |
－ | すべて | 「条件範囲2」内のどのセルを計算対象にするかの条件（値や単一セル）を指定します。 |
引数[条件範囲n,条件n]は条件の数に応じてセットで追加可能（最大127セット）。
引数「平均対象範囲」で指定した値のうち数値だけが計算対象となり、空白セル、論理値、文字列はすべて無視される。
※「0」は計算対象。
引数「平均対象範囲」で指定した値がすべて数値以外の場合、エラー値「#DIV/0!」が表示。
引数「条件n」の値が、引数「条件範囲n」で指定した値の中に存在しない場合、エラー値「#DIV/0!」が表示。
引数「平均対象範囲」と引数「条件範囲n」で指定するセル数は一致している必要あり（不一致の場合、エラー値「#VALUE!」が表示）。
引数「平均対象範囲」にエラー値が含まれる場合、そのエラー値が表示。
【参考】AVERAGEIFSは「統計関数」
あくまで参考情報となりますが、AVERAGEIFSはリボン「数式」タブの関数ライブラリの「統計」に分類されています。
実際にAVERAGEIFSを活用する際は、以下で解説しているように直接入力で挿入していきましょう。
AVERAGEIFSの使用結果イメージ
AVERAGEIFSで条件別の平均値を集計したイメージは以下の通りです。
今回は商品別で「金額」列の平均値を計算しました。
ポイントは、引数の「平均対象範囲」と「条件範囲1」はそれぞれ同じ範囲で固定すること。
詳細は記事下部の「【参考】AVERAGEIFSの集計漏れを防ぐTIPS」を参照。
そして、引数「条件1」は1行ごとにスライドさせることです。
こうすることで、ベースとなる数式を以降のセルへ使い回せるわけですね。
参照形式（絶対参照/相対参照）の詳細は以下の記事をご参照ください。
関数の数式を直接入力できるようになってきました！ もっと関数を実務で役立てていくために、他に覚えておいた方が良いことはありますか？？ …
AVERAGEIFSの数式の挿入手順
上記の結果を得るための手順は以下の通りです。
- 関数を挿入するセルを選択
- 「=aver」等と入力
- サジェストから「AVERAGEIFS」を選択し、「Tab」キーで確定
- 元データのシートへ移動
※今回は「注文テーブル」シート - 平均値の計算対象のセル範囲を選択
※今回は「金額」列（G:G） - コンマ（,）を入力
- 条件の検索対象のセル範囲を選択
※今回は「商品名」列（D:D） - コンマ（,）を入力
- 集計表のシートへ移動
※今回は「集計表」シート - 条件となるセルを選択
※今回はA4セル - 「Enter」キーで確定
- 1行目の数式をコピーし、以降のセルへペースト
※今回はB5~B12セルへペースト
手順②の際にIMEを半角英数モードにすること。
手順⑩も必要に応じて参照形式を変更すること（今回は指定しなくともOK）。
2種類以上の条件を設定したい場合、手順⑩の後に手順⑥~⑩を繰り返すこと。
【注意】AVERAGEIFSとAVERAGEIFの使い分け問題
AVERAGEIFSと兄弟的な関数としてAVERAGEIFがあります。
1~127種類の条件に対応できるAVERAGEIFSと違い、AVERAGEIFは1種類の条件しか指定できません。
また、ややこしいのが、平均の対象範囲を指定する引数の位置が異なる点です。
こうしたことから、原則は上位機能となるAVERAGEIFSをメインで使うと良いでしょう。
AVERAGEIFSなら、条件が1種類から使用できますし、後から条件を増やしたい場合の工数も最小限にできます。
なお、SUMIFやCOUNTIFと異なり、AVERAGEIFはAVERAGEIFSと同じExcel2007から登場した関数のため、関数をセットする対象のExcelブックの拡張子によって使い分けるということはできません。
あえて使い分けるなら、次のツイートにあるように、1種類の条件の数式をシンプルにまとめたい場合にAVERAGEIFを、それ以外はAVERAGEIFSを基本使いするイメージですね。
構文としてはSUMIF/SUMIFSと同じ関係なので、
・条件が一つであることを明示する意味として～IFを使う
・平均対象範囲＝条件範囲のとき平均対象範囲を省略できる
・合計範囲と平均対象範囲が違っていてもエラーにならない（揮発性になる）という差での使い分けかと #Excel https://t.co/lx6dmn7tKe pic.twitter.com/IiDSNBv9bG
— 光希桃 (@mikimomo_as) October 8, 2023
【参考】AVERAGEIFSの集計漏れを防ぐTIPS
AVERAGEIFSのよくあるリスクは、関数のセット後に元データへ追加されたレコードが集計範囲から漏れてしまうというケースです。
この原因は、手順⑤⑦の際、セット時点での元データのレコード数で範囲指定しているためです。
よって、AVERAGEIFSのセット時点で、元データにレコードが追加されても問題ない範囲指定を行いましょう（詳細は以下参照）。
通常のセル範囲が対象
元データが通常のセル範囲の場合は、列単位で指定しましょう。
ワークシート上の列番号（アルファベット部分）をクリックすればOKです。
列全体を対象範囲にしているため、当然レコードが増えても計算対象に含まれます。
なお、AVERAGEIFSの数式を横方向にコピペする際、引数「条件範囲n」を横にスライドさせたくない（固定にしたい）場合は、列を絶対参照にしましょう。
テーブルの列が対象
元データがテーブルの場合は、普通にその列を指定すればOKです。
対象の列名が数式上にセットされ、テーブルにレコード追加されても、集計対象に含まれます（集計対象範囲が自動拡張される）。
特に指定がなければ、元データは事前にテーブルにしておくことがおすすめ。
【応用】AVERAGEIFSで完全一致以外の条件で集計するには
AVERAGEIFSの引数「条件n」は「完全一致」が原則です。
もし、完全一致以外の条件で集計したい場合は、「ワイルドカード」と「比較演算子」を活用すればOKです。
AVERAGEIFSで完全一致以外の条件で集計する方法の詳細は以下の記事をご参照ください。
はじめに この記事はSUMIFS・COUNTIFSの使い方を把握していることが前提です。 参考記事 SUMIF …
サンプルファイルで練習しよう！
可能であれば、以下のサンプルファイルをダウンロードして、実際に操作練習をしてみてください。
サンプルファイル_ワークシート関数_AVERAGEIFS.xlsx
※サンプルファイルのダウンロードには無料メルマガに登録いただく必要があります。
（上記リンクから登録フォームへ遷移します）
ブックを開いたら、次の手順を実施してください。（今までの解説のまとめです）
- 関数を挿入するセルを選択
- 「=aver」等と入力
- サジェストから「AVERAGEIFS」を選択し、「Tab」キーで確定
- 元データのシートへ移動
※今回は「注文テーブル」シート - 平均値の計算対象のセル範囲を選択
※今回は「金額」列（G:G） - コンマ（,）を入力
- 条件の検索対象のセル範囲を選択
※今回は「商品名」列（D:D） - コンマ（,）を入力
- 集計表のシートへ移動
※今回は「集計表」シート - 条件となるセルを選択
※今回はA4セル - 「Enter」キーで確定
- 1行目の数式をコピーし、以降のセルへペースト
※今回はB5~B12セルへペースト
本記事の解説と同じ結果になればOKです！
さいごに
いかがでしたでしょうか？
AVERAGEIFSは条件別の平均値を集計できる関数です。
集計表で条件別の平均値を掲載する機会がある方は、この機会に覚えておきましょう。
なお、AVERAGEIFS以外にもExcelでのデータ集計の各種テクニックを拙著で解説していますので、こちらも参考にしてみてください。
ご参考になれば幸いですm(_ _)m
森田 AVERAGEIFSとセットで覚えておいた方が良い関数は、条件別の合計を集計できる「SUMIFS」と条件別の個数を集計できる「COUNTIFS」です。
特にSUMIFSの方は、使い方がAVERAGEIFSとほぼ一緒なので、どちらかを覚えれば自然と◯◯IFS系の関数は全般的に使えるようになります。
◯◯IFS系の関数は実務で利用頻度が高いので、ぜひマスターしていきましょう！
