AGGREGATE関数とは？SUMやAVERAGEとの違いをサクッと理解
Excelで売上表や勤怠データ、アンケート結果などを集計していると、よく使うのがSUM関数やAVERAGE関数です。合計を出すならSUM、平均を出すならAVERAGE。基本的な集計であれば、この2つだけでも十分に対応できます。
しかし実務では、そう簡単にきれいなデータばかり扱えるわけではありません。たとえば、表の中に#DIV/0!や#N/Aといったエラー値が混ざっていたり、フィルターで一部の行だけを表示していたり、手動で行を非表示にしていたりするケースがあります。
このような状態でSUM関数やAVERAGE関数を使うと、エラーに巻き込まれて集計結果が表示されなかったり、非表示にした行まで含めて計算されてしまったりします。そこで便利なのが、AGGREGATE関数です。
AGGREGATE関数は、ひと言でいうと「エラー値や非表示行を無視するかどうかを指定しながら集計できる関数」です。合計、平均、最大値、最小値、件数など、複数の集計方法に対応しており、状況に応じて柔軟に使い分けることができます。
たとえば、通常のSUM関数では、集計範囲にエラー値が1つでも含まれていると、結果もエラーになってしまいます。一方でAGGREGATE関数を使えば、エラー値を無視して正常な数値だけを合計できます。データの一部に未入力や計算ミスがあっても、とりあえず集計結果を確認したい場面で非常に役立ちます。
また、AVERAGE関数との違いも重要です。AVERAGE関数は範囲内の数値を平均しますが、フィルターで非表示になっている行も基本的には計算対象に含まれます。AGGREGATE関数なら、表示されている行だけを対象にして平均を出すことも可能です。フィルター後のデータだけを分析したいときに便利です。
つまり、AGGREGATE関数はSUMやAVERAGEの上位互換というより、「集計時に邪魔になる要素をコントロールできる関数」と考えるとわかりやすいでしょう。単純な合計ならSUM、単純な平均ならAVERAGEで問題ありません。ただし、エラーや非表示行が絡む実務データでは、AGGREGATE関数を知っているかどうかで作業効率が大きく変わります。
Excelで資料作成やデータ分析を行う機会が多いビジネスパーソンにとって、AGGREGATE関数は覚えておくとかなり頼れる存在です。次章では、このAGGREGATE関数の基本構文と、どのように「集計方法」や「無視する対象」を指定するのかを解説していきます。
基本構文を解説：集計方法と無視する対象を指定する仕組み
AGGREGATE関数は、指定する引数が少し多いため、最初は難しく見えるかもしれません。ですが、基本の考え方はシンプルです。「何の集計をするか」と「何を無視するか」を番号で指定し、最後に集計したい範囲を選ぶだけです。
基本構文は次のとおりです。
=AGGREGATE(集計方法, オプション, 範囲)たとえば、A2からA10までの数値を合計したい場合は、次のように入力します。
=AGGREGATE(9, 6, A2:A10)この数式では、最初の「9」が集計方法を表しています。AGGREGATE関数では、集計方法を番号で指定します。よく使う番号は以下のとおりです。
- 1：AVERAGE（平均）
- 2：COUNT（数値の個数）
- 3：COUNTA（空白以外の個数）
- 4：MAX（最大値）
- 5：MIN（最小値）
- 9：SUM（合計）
つまり、合計を出したいなら「9」、平均を出したいなら「1」、最大値を出したいなら「4」を指定します。SUM関数やAVERAGE関数の名前を書く代わりに、番号で集計内容を選ぶイメージです。
次に重要なのが、2つ目の引数であるオプションです。ここでは、エラー値や非表示行など、集計時に無視したい対象を番号で指定します。実務で特によく使うオプションは以下です。
- 0：何も無視しない
- 1：非表示行を無視する
- 2：エラー値を無視する
- 3：非表示行とエラー値を無視する
- 5：非表示行を無視する
- 6：エラー値を無視する
- 7：非表示行とエラー値を無視する
「1」と「5」、「3」と「7」は似ていますが、違いは入れ子になったSUBTOTAL関数やAGGREGATE関数を無視するかどうかです。通常の集計で迷ったら、まずは6：エラー値を無視、または7：非表示行とエラー値を無視を覚えておくと使いやすいでしょう。
たとえば、エラー値を無視してA2:A10の合計を出したい場合は、次のようになります。
=AGGREGATE(9, 6, A2:A10)また、非表示行とエラー値の両方を無視して平均を出したい場合は、次のように指定します。
=AGGREGATE(1, 7, A2:A10)このように、AGGREGATE関数は1つ目の番号で「集計方法」、2つ目の番号で「無視する対象」を決めます。最初は番号を覚えるのが少し面倒に感じるかもしれませんが、よく使う組み合わせは限られています。
まずは、合計なら「9」、平均なら「1」、エラーを無視するなら「6」、非表示行とエラーの両方を無視するなら「7」と押さえておきましょう。次章では、この仕組みを使って、実際にエラー値を無視しながら合計や平均を出す方法を解説します。
エラー値を無視して合計・平均を出す方法
ここからは、AGGREGATE関数を使ってエラー値を無視しながら合計や平均を出す方法を具体的に見ていきましょう。実務では、計算式が入った表の一部にエラーが出ているケースがよくあります。
たとえば、売上データの中に次のような値が入っているとします。
A2：10000
A3：15000
A4：#DIV/0!
A5：20000
A6：#N/A
この状態で通常のSUM関数を使うと、次のようになります。
=SUM(A2:A6)一見すると、数値だけを合計してくれそうですが、範囲内に#DIV/0!や#N/Aなどのエラー値が含まれているため、結果もエラーになってしまいます。つまり、正常な数値が入っていても、1つのエラーに集計全体が引っ張られてしまうわけです。
そこで使うのが、AGGREGATE関数です。エラー値を無視して合計したい場合は、次のように入力します。
=AGGREGATE(9, 6, A2:A6)この数式では、「9」で合計、「6」でエラー値を無視する設定にしています。そのため、A4やA6にエラーがあっても、それらを除外して10000、15000、20000だけを合計できます。
同じ考え方で、エラー値を無視して平均を出したい場合は、集計方法の番号を「1」に変更します。
=AGGREGATE(1, 6, A2:A6)これで、エラー値を除いた数値だけを対象に平均を計算できます。上記の例であれば、10000、15000、20000の3つの数値をもとに平均が算出されます。
ポイントは、エラーになっているセルを削除したり、別の値に置き換えたりしなくても集計できることです。たとえば、他部署から受け取ったExcelファイルや、まだ作成途中の管理表では、エラーの原因をすぐに直せないこともあります。そのような場面でも、AGGREGATE関数を使えば、ひとまず現時点で確認できる数値だけを集計できます。
似た方法として、IFERROR関数でエラーを0に変換してからSUMやAVERAGEを使うやり方もあります。ただし、平均を出す場合は注意が必要です。エラーを0に置き換えると、0も平均の対象に含まれるため、実際より平均値が低くなる可能性があります。
一方、AGGREGATE関数でエラーを無視すれば、エラーセルそのものを計算対象から外せます。特に平均を出すときは、「エラーを0として扱う」のか、「エラーを除外する」のかで結果が変わるため、目的に合わせて使い分けることが大切です。
エラーが混ざったデータを安全に集計したい場合は、まず合計なら「=AGGREGATE(9, 6, 範囲)」、平均なら「=AGGREGATE(1, 6, 範囲)」を覚えておきましょう。エラーに邪魔されず、必要な数値だけをすばやく確認できます。
非表示行・フィルター後のデータだけを集計する方法
AGGREGATE関数が便利なのは、エラー値だけでなく、非表示になっている行を除外して集計できる点です。Excelでフィルターを使って「特定の部署だけ」「今月分だけ」「未対応の案件だけ」といった形でデータを絞り込む場面は多いですよね。
このとき注意したいのが、通常のSUM関数やAVERAGE関数では、画面上で見えていない行まで集計対象に含まれてしまうことがある点です。見えているデータだけを集計しているつもりでも、実際には非表示の行も含めた合計になっていると、資料の数値がズレる原因になります。
たとえば、C列に売上金額が入っていて、C2:C100の範囲を集計したいとします。フィルターで「営業部」だけを表示した状態で、表示されている行だけの売上合計を出したい場合は、次のように入力します。
=AGGREGATE(9, 5, C2:C100)この数式では、「9」で合計、「5」で非表示行を無視する設定にしています。これにより、フィルターで非表示になっている行を除外し、画面に表示されているデータだけを対象に合計できます。
もし、非表示行に加えてエラー値も無視したい場合は、オプションを「7」にします。
=AGGREGATE(9, 7, C2:C100)実務では、フィルター後のデータにエラーが混ざっているケースもあるため、迷ったら「7」を使うと安心です。非表示行とエラー値の両方を無視できるので、集計結果がエラーで止まるリスクを減らせます。
平均を出したい場合は、集計方法の番号を「1」に変更します。
=AGGREGATE(1, 7, C2:C100)これで、フィルターで表示されている行だけを対象に、エラー値を除外しながら平均を算出できます。たとえば、担当者別の売上表で特定メンバーだけを表示し、そのメンバーの平均売上を確認したいときに便利です。
また、AGGREGATE関数は手動で非表示にした行を除外したい場合にも使えます。たとえば、確認不要な行を右クリックから「非表示」にしている場合でも、オプションに5や7を指定すれば、その行を除いて集計できます。
ただし、注意点として、AGGREGATE関数が無視できるのは基本的に非表示の行です。列を非表示にした場合の扱いは目的どおりにならないことがあるため、「見えている列だけを集計したい」という用途では別の方法を検討しましょう。
フィルター後のデータだけを集計したい場合は、合計なら「=AGGREGATE(9, 7, 範囲)」、平均なら「=AGGREGATE(1, 7, 範囲)」を覚えておくと実務で使いやすいです。表示中のデータだけを正確に集計できるため、Excelでの分析や報告資料作成のミスを減らせます。
実務で使えるAGGREGATE関数の活用例と注意点
AGGREGATE関数は、単に「エラーを無視できる便利な関数」として覚えるだけでなく、実務の集計表に組み込むことでかなり効果を発揮します。ここでは、20代のビジネスパーソンがExcel作業で使いやすい活用例と、使う前に知っておきたい注意点を整理します。
活用例1：フィルター後の売上合計をダッシュボードに表示する
営業成績表や売上管理表では、部署・担当者・月などでフィルターをかけて数値を確認することが多いです。その際、集計セルに次のような数式を入れておくと、フィルター条件を変えるたびに表示中データだけの合計を確認できます。
=AGGREGATE(9, 7, D2:D1000)D列が売上金額だとすると、非表示行とエラー値を除外して合計できます。資料作成前に「今表示している条件の売上はいくらか」をすぐ確認できるため、ピボットテーブルを作るほどではない簡易分析にも便利です。
活用例2：未完成の管理表でも平均値を確認する
進捗管理表やKPI管理表では、計算式が未完成だったり、参照先データが未入力だったりして、一部のセルにエラーが出ることがあります。その状態でも平均値を見たい場合は、次のように使えます。
=AGGREGATE(1, 6, E2:E100)エラーを含む行をいちいち修正しなくても、入力済みの正常な数値だけで平均を出せます。特に、週次レポートや月次報告の途中確認で役立ちます。
活用例3：最大値・最小値の確認にも使える
AGGREGATE関数は合計や平均だけでなく、最大値・最小値の確認にも使えます。たとえば、エラーを無視して最高売上を出したい場合は次のとおりです。
=AGGREGATE(4, 6, D2:D100)最小値を出したい場合は、集計方法を「5」に変更します。エラーが混ざったデータから最高値・最低値を探したいときにも、AGGREGATE関数は有効です。
使うときの注意点
- 数式の意味をメモしておく：「9」や「7」だけだと、後から見たときに意味がわかりにくくなります。近くのセルに「表示行のみ合計」など補足を入れると親切です。
- 平均では“除外”の影響を意識する：エラーや非表示行を除外すると、対象データ数が変わります。報告資料では「何を除外した平均なのか」を確認しましょう。
- 列の非表示には注意する：AGGREGATE関数は主に行の非表示を扱います。列を隠した場合の集計には向かないことがあります。
- 元データのエラー原因も放置しない：AGGREGATE関数はエラーを無視できますが、エラーそのものを直す関数ではありません。最終提出前には原因確認も必要です。
AGGREGATE関数は、きれいに整ったデータよりも、エラーや非表示行が混ざりやすい実務データでこそ真価を発揮します。まずは「9,7で合計」、「1,7で平均」を基本パターンとして覚え、売上表や進捗表の集計セルに取り入れてみましょう。集計ミスを減らしながら、Excel作業をよりスムーズに進められます。
コメント