【Excel】AGGREGATE関数でエラーを無視して集計する!Excelの非表示行除外オプション活用法

ExcelでSUM関数などを使う際、セルにエラー値が含まれていると計算結果もエラーになってしまいます。また、非表示行やエラー値を意図的に集計から除外したい場面も多いでしょう。このような場合に役立つのがAGGREGATE関数です。AGGREGATE関数を使えば、エラー値や非表示行を無視した集計が可能です。この記事では、AGGREGATE関数の使い方と、非表示行を除外するオプションについて詳しく解説します。

AGGREGATE関数を使いこなすことで、より柔軟で正確なデータ集計が可能になります。集計作業の効率化と精度向上に、ぜひお役立てください。

AGGREGATE関数がエラーと非表示行を無視できる仕組み

AGGREGATE関数は、Excelの標準的な集計関数(SUM、AVERAGE、COUNTなど)の機能を拡張したものです。この関数は、第2引数で指定するオプションによって、計算対象から特定の条件(エラー値、非表示行など)を満たすセルを除外できます。これにより、データにエラーが含まれていたり、一部の行が非表示になっていたりしても、意図した通りの集計結果を得ることが可能になります。例えば、SUM関数ではエラー値があると計算全体がエラーになりますが、AGGREGATE関数ではエラー値を無視して数値部分のみを合計できます。

AGGREGATE関数でエラーを無視して集計する手順

AGGREGATE関数は、集計したい関数、無視したいオプション、集計対象のセル範囲などを引数として指定します。ここでは、SUM関数(関数番号1)を使って、セル範囲内のエラー値を無視して合計を計算する手順を説明します。

1. 集計したいセルを選択する
   合計を表示したいセルを選択します。
2. AGGREGATE関数を入力する
   選択したセルに「=AGGREGATE(」と入力します。
3. 関数番号を指定する
   最初の引数に、実行したい集計関数に対応する番号を入力します。合計の場合は「1」(SUM関数)を指定します。入力後、「,」を入力します。
4. 無視するオプションを指定する
   2番目の引数に、無視したい項目を指定する番号を入力します。エラー値を無視する場合は「4」を指定します。入力後、「,」を入力します。
5. 集計対象のセル範囲を指定する
   3番目の引数に、集計したい数値データが含まれるセル範囲を指定します。例えば「A1:A10」のように入力します。
6. 関数を完了する
   引数の指定が終わったら、「)」を入力してEnterキーを押します。

例えば、セルA1からA10に数値とエラー値が混在している場合、セルB1に「=AGGREGATE(1,4,A1:A10)」と入力すると、エラー値を無視した合計値がB1に表示されます。

AGGREGATE関数で非表示行を除外して集計する手順

AGGREGATE関数では、第2引数に「7」を指定することで、非表示になっている行の値を集計対象から除外できます。これは、フィルター機能などで一時的に非表示にした行も対象となります。

1. 集計結果を表示するセルを選択する
   非表示行を除いた集計値が表示されるセルを選びます。
2. AGGREGATE関数を入力する
   「=AGGREGATE(」と入力します。
3. 関数番号を指定する
   合計を計算したいので、1番目の引数に「1」(SUM関数)を指定します。入力後、「,」を入力します。
4. 非表示行を除外するオプションを指定する
   2番目の引数に「7」を入力します。これにより、非表示の行が無視されます。入力後、「,」を入力します。
5. 集計対象のセル範囲を指定する
   3番目の引数に、集計したいデータが含まれるセル範囲を指定します。
6. 関数を完了する
   「)」を入力してEnterキーを押します。

この手順で、例えばセルA1からA10の範囲で非表示になっている行の値を無視して、表示されている値のみを合計できます。

AGGREGATE関数の第2引数(オプション)一覧

AGGREGATE関数の第2引数には、様々なオプションが用意されています。これらのオプションを組み合わせることで、より細かく集計対象を制御できます。

以下に、主なオプション番号とその意味をまとめました。

オプション番号	無視する値
0	なし(エラー値、非表示行、手動非表示行も無視しない)
1	非表示の行と手動で非表示にした行の値
2	エラー値
3	非表示の行、手動で非表示にした行の値、およびエラー値
4	なし(エラー値、非表示行、手動非表示行も無視しない)
5	手動で非表示にした行の値
6	エラー値
7	非表示の行
8	非表示の行とエラー値
9	非表示の行、手動で非表示にした行の値、およびエラー値
10	手動で非表示にした行の値
11	非表示の行と手動で非表示にした行の値
12	非表示の行とエラー値
13	非表示の行、手動で非表示にした行の値、およびエラー値
14	構造化参照(テーブル)の値
15	構造化参照(テーブル)の値とエラー値

※Excelのバージョンにより、一部オプションの挙動や名称が異なる場合があります。Excel for Microsoft 365では、上記リストの番号が一般的に使用されます。

AGGREGATE関数で使える集計関数(第1引数)

AGGREGATE関数は、第1引数に指定する番号によって、様々な集計関数として機能します。以下に、主な関数番号とその対応するExcel関数を示します。

関数番号	対応するExcel関数	機能
1	AGGREGATE(1, …)	SUM(合計)
2	AGGREGATE(2, …)	AVERAGE(平均)
3	AGGREGATE(3, …)	MAX(最大値)
4	AGGREGATE(4, …)	MIN(最小値)
5	AGGREGATE(5, …)	PRODUCT(積)
6	AGGREGATE(6, …)	STDEV(標準偏差)
7	AGGREGATE(7, …)	STDEVP(母標準偏差)
8	AGGREGATE(8, …)	VAR(分散)
9	AGGREGATE(9, …)	VARP(母分散)
10	AGGREGATE(10, …)	LARGE(k番目に大きい値)
11	AGGREGATE(11, …)	SMALL(k番目に小さい値)

AGGREGATE関数とSUBTOTAL関数の違い

AGGREGATE関数と似た機能を持つ関数にSUBTOTAL関数があります。SUBTOTAL関数も、集計対象から非表示行を除外するオプションを持っています。しかし、SUBTOTAL関数はエラー値を無視する機能はありません。

SUBTOTAL関数で非表示行を除外するには、第1引数に「101」から「111」の数値を指定します。例えば、SUM関数で非表示行を除外して合計を計算する場合は「SUBTOTAL(101, A1:A10)」のように記述します。

一方、AGGREGATE関数は、第2引数でエラー値の無視と非表示行の無視を同時に指定できる点が大きな違いです。例えば、「=AGGREGATE(1, 5, A1:A10)」と入力すれば、非表示行とエラー値の両方を無視して合計を計算できます。この柔軟性が、AGGREGATE関数がより強力な集計ツールとなる理由です。

AGGREGATE関数でよくある失敗パターンと対処法

AGGREGATE関数で期待通りの結果にならない

AGGREGATE関数を使っても期待通りの結果にならない場合、主に以下の原因が考えられます。

第2引数のオプション指定ミス

最も多い原因は、第2引数に指定するオプション番号が間違っていることです。例えば、エラー値のみを無視したいのに、非表示行も無視するオプションを指定してしまっている場合などです。上記の「AGGREGATE関数でエラーを無視して集計する手順」や「AGGREGATE関数で非表示行を除外して集計する手順」を参考に、意図したオプション番号が正しく指定されているか確認してください。特に、オプション「4」は「エラー値を無視する」ですが、オプション「6」も同様に「エラー値を無視する」機能を持っています。しかし、Excelのバージョンによっては微妙な挙動の違いがあるため、一般的には「4」か「6」を使用することが推奨されます。

非表示行の定義の誤解

AGGREGATE関数で非表示行を除外する場合、オプション「7」は「フィルター機能などで自動的に非表示になった行」を対象とします。一方、オプション「5」や「10」は「手動で右クリックメニューから『非表示』を選択した行」を対象とします。意図した行が除外されない場合、どちらの非表示設定になっているのかを確認し、適切なオプション番号を選択してください。両方を無視したい場合は、オプション「9」などを指定します。

集計対象範囲の指定ミス

当然ですが、集計対象のセル範囲が正しく指定されていないと、期待通りの結果は得られません。数式バーで関数に指定されているセル範囲が、実際に集計したいデータ範囲と一致しているか確認してください。

AGGREGATE関数で#VALUE!エラーが表示される

AGGREGATE関数自体が#VALUE!エラーを返す場合、通常は引数の指定方法に誤りがあります。例えば、関数番号やオプション番号に数値以外のものが指定されている、あるいはセル範囲の指定が不正な場合などが考えられます。数式を再確認し、各引数が正しいデータ型(数値、セル範囲)で入力されているかを確認してください。

まとめ

AGGREGATE関数は、エラー値や非表示行を無視して集計できる非常に便利な関数です。第2引数のオプションを適切に設定することで、SUM、AVERAGEなどの関数をより柔軟に活用できます。特に、データにエラーが含まれる場合や、フィルターなどで非表示にした行を除外して集計したい場合に強力な効果を発揮します。今回解説したAGGREGATE関数の使い方をマスターすれば、Excelでのデータ集計作業の精度と効率を大幅に向上させることができるでしょう。まずは、エラー値無視や非表示行無視のオプションを試してみて、その便利さを実感してください。

📊

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

🏆

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