【Excel】AGGREGATE関数でエラーや非表示行を除外して集計!Excelの万能集計関数の使い方

ExcelでSUM関数などを使って集計を行う際、エラー値が含まれていたり、非表示になっている行を除外したい場面があります。これらの条件を考慮せずに集計すると、意図しない結果になってしまうことがあります。この記事では、ExcelのAGGREGATE関数を使うことで、これらの問題を解決し、正確な集計を行う方法を解説します。

AGGREGATE関数は、様々な集計や統計計算を行えるだけでなく、エラー値や非表示行を無視するオプションを持っているため、複雑なデータ処理に非常に役立ちます。これにより、データ分析の精度を高めることができます。

AGGREGATE関数がエラーや非表示行を考慮できる仕組み

AGGREGATE関数は、Excelの標準的な集計関数とは異なり、集計対象のデータに対する特別な処理を指定できる点が最大の特徴です。この関数は、第一引数で実行したい集計の種類(合計、平均、最大値など)を選択し、第二引数で無視したいデータの種類(エラー値、非表示行、ネストされた SUBTOTAL関数など)を指定します。これにより、ユーザーは集計の条件を細かく制御できます。

具体的には、第二引数に設定する数値が、AGGREGATE関数にどのようなデータを無視して処理を進めるかを指示します。例えば、数値の「4」を指定すると、計算結果として表示されるエラー値(#DIV/0!、#N/Aなど)を無視します。また、数値の「2」を指定すると、フィルタリングなどで非表示になっている行のデータを無視します。これらを組み合わせることで、より目的に合った正確な集計が可能になります。

AGGREGATE関数を使った集計の手順

AGGREGATE関数は、その柔軟性から様々な集計に対応できます。ここでは、一般的なSUM関数を例に、エラー値や非表示行を除外して合計を計算する手順を解説します。この関数を理解することで、他の集計関数への応用も容易になります。

1. 集計したい範囲を準備する
   まず、合計を計算したいデータ範囲を用意します。このデータには、意図的にエラー値を含めたり、一部の行を非表示にしておくと、AGGREGATE関数の効果を確認しやすくなります。例えば、A1からA10の範囲に数値を入力し、A5セルに「#DIV/0!」のようなエラー値を、A8の行を非表示にしてみましょう。
2. AGGREGATE関数を入力するセルを選択する
   集計結果を表示したいセルを選択します。ここでは、例えばB1セルを選択します。
3. AGGREGATE関数を入力する
   選択したセルに、以下の構文で関数を入力します。

   構文: AGGREGATE(function_num, options, array, [if_num], [step], [step2], …)

   例: エラー値と非表示行を除外して合計を計算する場合
   =AGGREGATE(101, 6, A1:A10)

   この例では、第一引数「101」はSUM関数を意味し、第二引数「6」は「エラー値と非表示行の両方を無視する」というオプションを指定しています。第三引数「A1:A10」は集計対象の範囲です。

4. Enterキーを押して結果を確認する
   関数を入力したらEnterキーを押します。指定した範囲A1:A10から、エラー値と非表示行を除いた値の合計が計算され、セルB1に表示されます。SUM関数を直接A1:A10に適用した場合と結果を比較してみると、AGGREGATE関数の挙動が理解できます。

AGGREGATE関数で使える集計の種類とオプション

AGGREGATE関数は、第一引数に指定する数値によって、様々な集計処理を実行できます。これらの数値は、Excelの他の関数名に対応しており、例えば「1」はSUM、「2」はAVERAGE、「3」はMAX、「4」はMIN、「5」はPRODUCT、「6」はSTDEV.S、「7」はVAR.S、「8」はSUBTOTAL、「9」はLARGE、「10」はSMALL、「11」はMAX、「12」はMIN、「13」はPRODUCT、「14」はSTDEV.P、「15」はVAR.P、「16」はLARGE、「17」はSMALL、「18」はMEDIAN、「19」はMODE.SNGL、「20」はPERCENTILE.INC、「21」はQUARTILE.INC、「22」はPERCENTILE.EXC、「23」はQUARTILE.EXC、「24」はMODE.MULTです。

さらに重要なのが第二引数で指定する「options(オプション)」です。このオプションによって、集計時に無視するデータの種類が決まります。主なオプションは以下の通りです。

無視するオプション一覧

以下の表は、AGGREGATE関数で利用できる第二引数のオプションとその意味を示しています。

数値	意味
0	ネストされた SUBTOTAL関数とAGGREGATE関数を除くすべての値
1	ネストされたSUBTOTAL関数とAGGREGATE関数を除くすべての値
2	非表示行を除くすべての値
3	非表示行とエラー値を除くすべての値
4	エラー値を除くすべての値
5	ネストされたSUBTOTAL関数とAGGREGATE関数およびエラー値を除くすべての値
6	ネストされたSUBTOTAL関数とAGGREGATE関数、非表示行、エラー値を除くすべての値
7	ネストされたSUBTOTAL関数とAGGREGATE関数および非表示行を除くすべての値
8	ネストされたSUBTOTAL関数とAGGREGATE関数およびエラー値を除くすべての値
9	ネストされたSUBTOTAL関数とAGGREGATE関数、非表示行、エラー値を除くすべての値
10	ネストされたSUBTOTAL関数とAGGREGATE関数および非表示行を除くすべての値

これらのオプションを組み合わせることで、例えば「非表示行は無視するが、エラー値は集計に含めたい」といった、より複雑な条件での集計も可能になります。Excel 2010以前のバージョンではこの関数は利用できないため、注意が必要です。

AGGREGATE関数でよくある入力ミスと対処法

AGGREGATE関数は非常に便利ですが、その多機能さゆえに、設定を誤って意図しない結果になることがあります。ここでは、よくある入力ミスとその対処法を解説します。

第一引数(集計の種類)の指定間違い

AGGREGATE関数は、第一引数に1から24までの数値を指定することで、様々な集計関数(SUM、AVERAGE、MAXなど)の機能を持たせます。この数値の指定を間違えると、全く異なる計算結果が表示されます。例えば、合計を計算したいのに「2」(AVERAGE)を指定してしまうと、平均値が表示されてしまいます。

対処法: 第一引数に指定する数値が、どの集計関数に対応しているかをExcelのヘルプや上記表で確認し、正確に指定してください。特に、SUM関数を使いたい場合は「101」ではなく「1」や「101」を指定しますが、「101」はエラー値や非表示行を無視するSUM関数として扱われます。SUM関数のみを単純に実行したい場合は「101」、エラー値や非表示行を無視したSUM関数を実行したい場合は「101」を指定します。

第二引数(オプション)の指定間違い

第二引数で指定するオプションは、AGGREGATE関数の挙動を決定する重要な要素です。この数値を誤ると、エラー値や非表示行が意図せず集計に含まれたり、逆に除外されてしまったりします。例えば、「エラー値だけ除外したい」のに「非表示行のみ除外する」オプション(数値2)を指定してしまうと、エラー値が集計に含まれてしまいます。

対処法: どのオプションがどのデータを無視するかを正確に理解することが重要です。上記「無視するオプション一覧」を参考に、目的に合った数値を指定してください。例えば、エラー値と非表示行の両方を無視したい場合は「6」を指定します。

配列引数に誤った範囲を指定

AGGREGATE関数の第三引数以降で指定する「array(配列)」は、集計対象のデータ範囲です。この範囲指定を誤ると、当然ながら正しい集計は行えません。例えば、本来集計したい範囲より狭い範囲を指定してしまうと、一部のデータが計算から漏れてしまいます。

対処法: 集計したいデータ範囲全体を正確に指定してください。セル範囲の指定が不明確な場合は、マウスでドラッグして選択するか、セル参照の誤りがないか確認しましょう。また、データが動的に増減する場合は、Excelテーブル機能を利用するか、OFFSET関数やINDIRECT関数と組み合わせることも検討できます。

AGGREGATE関数とSUBTOTAL関数の比較

AGGREGATE関数は、SUBTOTAL関数と似た機能を持っていますが、いくつかの重要な違いがあります。SUBTOTAL関数も、集計の種類を指定し、非表示行を無視するオプションを持っています。しかし、SUBTOTAL関数はエラー値を無視する機能がありません。

機能	SUBTOTAL関数	AGGREGATE関数
集計の種類	1-11 (SUM, AVERAGEなど)	1-24 (SUM, AVERAGE, MAX, MIN, STDEV, VARなど、より豊富)
非表示行の無視	可能 (function_numに101-111を指定)	可能 (optionsに2, 3, 6, 7, 9, 10を指定)
エラー値の無視	不可	可能 (optionsに3, 4, 6, 8, 9, 10を指定)
ネストされた関数	SUBTOTAL関数自身は無視する (function_numに100番台を指定)	SUBTOTAL関数とAGGREGATE関数自身を無視可能 (optionsに0, 5, 6, 7, 8, 9, 10を指定)
利用可能バージョン	Excel 2007以降	Excel 2010以降

この比較からわかるように、AGGREGATE関数はSUBTOTAL関数よりも高度なオプションを持ち、特にエラー値の無視や、より多くの種類の集計関数に対応している点が優れています。データにエラー値が含まれる可能性が高い場合や、より多様な集計を行いたい場合には、AGGREGATE関数が適しています。Excel 2010以降のバージョンであれば、AGGREGATE関数を利用することで、より柔軟なデータ集計が可能になります。

AGGREGATE関数を使いこなすことで、Excelでのデータ集計作業の効率と精度が格段に向上します。エラー値や非表示行といった、従来は手間がかかっていた条件も簡単に処理できるようになります。ぜひ、日々の業務で活用してみてください。

📊

Excelトラブル完全解決データベースこの記事以外にも、様々なエラー解決策をまとめています。困った時の逆引きに活用してください。

🏆

超解決 Excel検定 あなたのExcel実務能力を3分で測定！【1級・2級・3級】