AGGREGATE関数の使い方！エラー値や非表示行を無視して合計・平均を出す

ExcelのAGGREGATE関数の使い方をお探しですね。

Excelで「#DIV/0!」や「#N/A」のエラーがあっても合計・平均を出す方法【AGGREGATE関数の使い方】

Excelで売上表や在庫表を集計していると、「#DIV/0!」や「#N/A」といったエラーが混ざっていて、合計や平均を出そうとしたらエラーになってしまった…という経験はありませんか？また、フィルターで絞り込んだときに、表示されている行だけを集計したいこともありますよね。

そんなときに役立つのが「AGGREGATE関数」です。

この記事では、エラーや非表示行を無視して集計する方法を、初心者の方にも分かりやすく解説します。

AGGREGATE関数って何？エラーや非表示行をスルーして集計できる便利な関数

AGGREGATE関数は、合計・平均・最大値・最小値など、いろいろな集計を1つの関数でできる便利な関数です。

普通のSUM関数やAVERAGE関数だと、集計範囲にエラーが1つでもあると、結果全体がエラーになってしまいます。

でもAGGREGATE関数なら、「エラーは無視する」「非表示の行は計算に入れない」といった設定ができるので、実際の仕事で使いやすいんです。

基本の書き方は「=AGGREGATE(集計方法, オプション, 参照, [k])」です。

集計方法には、平均なら「1」、合計なら「9」、最大値なら「4」のように数字を入れます。

オプションには、何を無視するかを数字で指定します。

例えば、エラーだけ無視したいなら「6」、非表示行もエラーも両方無視したいなら「7」を使います。

最初は数式が難しそうに見えるかもしれませんが、「何を計算するか」と「何を除外するか」を数字で選ぶだけと考えれば、意外とシンプルです。

よく使う集計方法の番号を覚えておくと便利です。

平均は「1」、数値の個数は「2」、最大値は「4」、最小値は「5」、合計は「9」です。

他にも、中央値の「12」、上から何番目かを取り出す「14」、下から何番目かを取り出す「15」なども使えます。

単純な合計や平均だけでなく、エラーを含むデータから上位3件を取り出すような使い方もできるので、SUBTOTAL関数よりも幅広く使える関数なんです。

エラー値を無視して合計・平均を出す方法

エラーを無視して合計を出したいときは、集計方法に「9」、オプションに「6」を指定します。

例えば、A2からA10に数値とエラーが混ざっていて、数値だけを合計したいなら「=AGGREGATE(9,6,A2:A10)」と入力します。

「9」が合計（SUMと同じ）、「6」がエラーを無視するという意味です。

範囲内に「#N/A」や「#DIV/0!」があっても、それらを飛ばして正常な数値だけを合計してくれます。

平均を出したいときは、集計方法を「1」に変えるだけです。

「=AGGREGATE(1,6,A2:A10)」と入力すると、A2からA10の中のエラーを除いて平均値を計算できます。

普通のAVERAGE関数だと、範囲にエラーがあると平均値自体が表示されませんが、AGGREGATE関数ならエラーをいちいち直さなくても集計できます。

急いで集計したいときや、元データに一時的なエラーが残っているときに重宝します。

ただし、AGGREGATE関数でエラーを無視できるからといって、エラーの原因を確認しなくていいわけではありません。

例えば、VLOOKUP関数やXLOOKUP関数の結果が「#N/A」になっている場合、本来取得すべきデータが見つかっていない可能性があります。

集計値だけを一時的に出したいときはAGGREGATE関数が便利ですが、最終的な報告書や会計資料で使うなら、エラーの理由もちゃんと確認しておきましょう。

エラーを「消す」のではなく、「集計では除外する」関数だと理解しておくことが大切です。

よく使う数式をまとめておきます。

フィルターで絞り込んだ表示データだけを集計する使い方

AGGREGATE関数は、非表示の行を無視して集計したいときにも使えます。

例えば、売上一覧で特定の担当者や商品だけをフィルター表示して、その表示されている行だけの合計や平均を確認したい場合です。

普通のSUM関数やAVERAGE関数だと、非表示になっている行も含めて計算されてしまうことがありますが、AGGREGATE関数なら適切な設定で、見えている行だけを集計できます。

非表示の行を無視して合計したいなら「=AGGREGATE(9,5,B2:B20)」のように入力します。

「9」は合計、「5」は非表示行を無視するという意味です。

平均なら「=AGGREGATE(1,5,B2:B20)」とします。

さらに、非表示行とエラーの両方を無視したいときは、オプションを「7」にして「=AGGREGATE(9,7,B2:B20)」と入力します。

実務では、元データにエラーが混ざっていて、しかもフィルターで絞り込んだ表示行だけを集計したい場面が多いので、オプション「7」は特に覚えておくと便利です。

SUBTOTAL関数もフィルター後の表示行を集計できる関数ですが、AGGREGATE関数はエラー値の無視や順位系の集計にも対応している点が違います。

例えば、SUBTOTAL関数ではエラー値が含まれると集計結果に影響することがありますが、AGGREGATE関数ならオプションでエラーを除外できます。

また、AGGREGATE関数は上位・下位の値を取り出す機能もあるので、表示中のデータから上位や下位の値を取り出す用途にも向いています。

注意点として、AGGREGATE関数で無視できるのは主に非表示の「行」です。

非表示の列を除外したい場合は、関数の指定だけでは期待通りにならないことがあるので、表の作り方や集計範囲を見直す必要があります。

また、フィルターで非表示になった行と、右クリックなどで手動で非表示にした行では、関数やオプションによって動きが変わることがあるので確認したほうが安心です。

大事な集計表では、数式を入れた後に何件かのデータで結果が想定通りか確認してから使うとミスを防げます。

AGGREGATE関数を使うときの注意点と使い分けのコツ

AGGREGATE関数で迷いやすいのは、集計方法とオプションの番号です。

特に、合計の「9」、平均の「1」、エラーを無視する「6」、非表示行とエラーを無視する「7」は使う機会が多いので、まずはこの4つを覚えておくと実務で使いやすくなります。

全部の番号を暗記する必要はありません。

Excelで関数を入力するときに候補や説明が表示されるので、よく使うパターンから慣れていけば大丈夫です。

もう1つの注意点は、集計表の中に小計行がある場合です。

AGGREGATE関数は、オプションによっては範囲内にあるSUBTOTAL関数やAGGREGATE関数を無視できます。

そのため、小計と総合計を含む表では、SUM関数だけで組むよりも二重集計を避けやすくなります。

ただし、小計行にSUM関数を使って、総合計にAGGREGATE関数を使うような混ぜた作り方にすると、小計行まで集計対象に入ってしまう可能性があります。

小計と合計を同じ表で扱う場合は、SUBTOTAL関数かAGGREGATE関数に統一すると管理しやすくなります。

AGGREGATE関数には「k」という引数を使う形式もあります。

これは、「何番目の値を取り出すか」を指定する場合に使います。

例えば「=AGGREGATE(14,6,A2:A10,3)」と入力すると、エラーを無視して3番目に大きい値を取り出せます。

合計や平均ではk引数は不要ですが、上位・下位の値を求める場合には必要です。

エラーを含むランキング集計を作りたいときに便利な使い方です。

AGGREGATE関数を使うべき場面は、エラーや非表示行を除外しながら、合計・平均・最大値・最小値などを安定して求めたいときです。

一方、条件に合うデータだけを集計したいならSUMIF関数やSUMIFS関数、複数条件で平均を出したいならAVERAGEIFS関数のほうが分かりやすいこともあります。

フィルター表示に連動したいならSUBTOTAL関数、エラーも柔軟に除外したいならAGGREGATE関数、条件で集計対象を選びたいならSUMIFS関数というように使い分けると、数式が読みやすくなります。

最後に、実務でよく使うAGGREGATE関数のパターンを押さえておきましょう。

エラーを無視した合計は「=AGGREGATE(9,6,範囲)」、エラーを無視した平均は「=AGGREGATE(1,6,範囲)」、非表示行とエラーを無視した合計は「=AGGREGATE(9,7,範囲)」です。

この3つを覚えておくだけでも、SUM関数やAVERAGE関数で集計結果がエラーになる悩みを大きく減らせます。

Excelの集計作業で「エラーがあるから計算できない」「非表示行を含めたくない」と感じたときは、AGGREGATE関数を試してみてください。

Excelで「#DIV/0!」や「#N/A」のエラーがあっても合計・平均を出す方法【AGGREGATE関数の使い方】

AGGREGATE関数って何？エラーや非表示行をスルーして集計できる便利な関数

エラー値を無視して合計・平均を出す方法

フィルターで絞り込んだ表示データだけを集計する使い方

AGGREGATE関数を使うときの注意点と使い分けのコツ

関連記事