今回は、ExcelのAGGREGATE関数を使って、エラー値や非表示の行を除いたうえで合計・平均・最大値などを求める方法を紹介します。
この記事は、Windows版のExcel for Microsoft 365を前提にしています。AGGREGATE関数はデスクトップ版のExcelで以前から使える関数ですが、関数の入力補助の表示などは環境によって異なる場合があります。
AGGREGATE関数とは
AGGREGATE関数は、1つの関数の中で、使う集計方法と無視する値の種類を指定できる関数です。SUMやAVERAGE、MAXなどの集計を切り替えながら、次のような値を集計の対象から外せます。
- #DIV/0!や#N/Aなどのエラー値
- 非表示にした行
- 範囲内に含まれるSUBTOTAL関数やAGGREGATE関数の結果
通常のSUM関数やMAX関数は、範囲内にエラー値が1つでもあると、結果もエラーになります。AGGREGATE関数を使えば、元データのエラーを修正する前でも、ひとまず正しい値だけで集計結果を確認できます。
AGGREGATE関数の書式
AGGREGATE関数には、2つの書式があります。
- セル範囲形式：=AGGREGATE(集計方法, オプション, 範囲1, [範囲2], …)
- 配列形式：=AGGREGATE(集計方法, オプション, 配列, [順位などの値])
1つ目の引数で「何を計算するか」、2つ目の引数で「何を無視するか」を数値で指定するのが基本の考え方です。
集計方法の番号
集計方法には1～19の番号を指定します。よく使うものを挙げると次のとおりです。
- 1：AVERAGE（平均）
- 2：COUNT（数値の個数）
- 3：COUNTA（空白以外の個数）
- 4：MAX（最大値）
- 5：MIN（最小値）
- 9：SUM（合計）
- 12：MEDIAN（中央値）
- 14：LARGE（大きいほうから○番目）
- 15：SMALL（小さいほうから○番目）
このほか、PRODUCT、STDEV.S、VAR.S、MODE.SNGL、PERCENTILE.INC、QUARTILE.INCなども指定できます。セルに「=AGGREGATE(」と入力すると、指定できる集計方法の一覧が候補として表示されるので、番号を覚えていなくても選べます。
オプションの番号
2つ目の引数には0～7の番号で、無視する値の種類を指定します。
- 0または省略：入れ子になったSUBTOTAL関数・AGGREGATE関数を無視
- 1：非表示の行と、入れ子のSUBTOTAL関数・AGGREGATE関数を無視
- 2：エラー値と、入れ子のSUBTOTAL関数・AGGREGATE関数を無視
- 3：非表示の行、エラー値、入れ子のSUBTOTAL関数・AGGREGATE関数を無視
- 4：何も無視しない
- 5：非表示の行を無視
- 6：エラー値を無視
- 7：非表示の行とエラー値を無視
迷ったときは、エラー値だけを除きたいなら6、非表示の行も除きたいなら7と覚えておくと使い分けやすくなります。
エラー値を無視して集計する例
たとえば、B2:B7に次の値が入っているとします。B4は、割り算の数式で0除算が起きて#DIV/0!になっている想定です。
- B2：1200
- B3：800
- B4：#DIV/0!
- B5：1500
- B6：950
- B7：600
この範囲を=SUM(B2:B7)で合計すると、結果は#DIV/0!になります。AGGREGATE関数を使うと、エラーのセルを除いて計算できます。
- =AGGREGATE(9,6,B2:B7)：エラー値を除いた合計（結果は5050）
- =AGGREGATE(1,6,B2:B7)：エラー値を除いた平均（結果は1010）
- =AGGREGATE(4,6,B2:B7)：エラー値を除いた最大値（結果は1500）
- =AGGREGATE(5,6,B2:B7)：エラー値を除いた最小値（結果は600）
IFERROR関数でエラーを0や空白に置き換えてから集計する方法もありますが、AGGREGATE関数なら元の列に手を加えずに済みます。エラーの原因は残したまま、集計だけを先に確認したいという場面に向いています。
○番目に大きい値・小さい値を求める
集計方法14（LARGE）や15（SMALL）を使う場合は、配列形式で4つ目の引数に「何番目か」を指定します。
- =AGGREGATE(14,6,B2:B7,2)：エラー値を除いて2番目に大きい値（結果は1200）
- =AGGREGATE(15,6,B2:B7,1)：エラー値を除いて最も小さい値（結果は600）
LARGE、SMALL、PERCENTILE.INC、QUARTILE.INC、PERCENTILE.EXC、QUARTILE.EXCの6つは、この4つ目の引数が必須です。省略すると#VALUE!エラーになるので、結果がおかしいときはまずここを確認しましょう。
非表示の行を除いて集計する
オプション5や7を指定すると、非表示にした行を集計から外せます。フィルターで絞り込んだ結果だけを集計したいときや、一時的に行を隠して比較したいときに便利です。
- =AGGREGATE(9,5,B2:B100)：非表示の行を除いた合計
- =AGGREGATE(9,7,B2:B100)：非表示の行とエラー値を除いた合計
表示されている行だけを集計する関数としてはSUBTOTAL関数もありますが、SUBTOTAL関数はエラー値を無視できません。非表示の行とエラー値の両方を除きたい場合は、AGGREGATE関数のオプション7を使うと1つの数式で対応できます。
縦方向の範囲で使う
AGGREGATE関数の「非表示を無視する」働きは、縦方向の範囲（列のデータ）を前提としています。横に並んだ範囲を集計して列を非表示にしても、結果は変わりません。横方向のデータを扱う表では、行と列の向きを見直すか、別の方法を検討しましょう。
小計を含む表での二重計算を防ぐ
表の途中にSUBTOTAL関数やAGGREGATE関数で小計を入れていると、最後に全体を合計したとき、小計まで足し込んでしまうことがあります。
AGGREGATE関数は、オプション0～3を指定すると、範囲内に入れ子になっているSUBTOTAL関数・AGGREGATE関数の結果を無視します。そのため、小計行を含む範囲をまとめて指定しても、明細だけを合計できます。部署別の小計と全体の合計を同じ列に並べる表などで役立ちます。
使うときの注意点
- 3-D参照は使えない：複数のシートをまたいだ参照（Sheet1:Sheet3!B2など）を指定すると、#VALUE!エラーになります。シートごとに集計してから合計しましょう。
- 配列に計算式を含めると非表示行などは無視されない：たとえば範囲に条件を掛け合わせた計算を配列引数に入れた場合、非表示の行や入れ子の小計は無視の対象になりません。
- エラーを隠すだけで解決はしない：AGGREGATE関数はエラーを集計から外すだけです。エラーの原因となる元の数式は、別途確認して修正しておくと安心です。
AGGREGATE関数を使いこなすためのヒント
- 番号は関数の候補一覧から選ぶ：引数を入力する段階で一覧が表示されるので、番号を暗記しなくても目的の集計方法を選べます。
- よく使う組み合わせをメモしておく：「9,6」でエラーを除いた合計、「9,7」で非表示とエラーを除いた合計のように、自分がよく使う組み合わせを決めておくと入力が速くなります。
- 通常の関数の結果と並べて確認する：SUM関数などの結果とAGGREGATE関数の結果を隣に並べると、エラーや非表示行が集計にどう影響しているかを確認しやすくなります。
まとめ
AGGREGATE関数は、集計方法と無視する値の種類を指定して、柔軟に集計できる関数です。
- 1つ目の引数で集計方法（9=合計、1=平均、4=最大など）を指定する
- 2つ目の引数で無視する値を指定し、エラー値だけなら6、非表示の行も除くなら7を使う
- LARGEやSMALLなどは4つ目の引数で「何番目か」を指定する
- 入れ子の小計を無視できるため、小計を含む表の合計にも使える
- 3-D参照には対応しておらず、横方向の範囲では非表示の無視が働かない
エラーが混ざった表や、フィルターで絞り込んで使う表を扱うときに、SUM関数やMAX関数の代わりとして試してみてください。