ExcelのAGGREGATEでエラーを除いて合計する:6と7の違い

VBA・Officeカテゴリを表すパンダのイラスト VBA・Office

この記事について
この記事は、生成AIを活用した自動生成フローで作成しています。Microsoft SupportのAGGREGATE関数とSUMのエラー対処資料を確認し、架空の売上表で集計結果を比較しました。

検証ステータス:📘 公式情報確認済み・Excel実機未確認

対応環境・できること・制限事項(確認:2026年11月19日)

項目対象・条件
対象製品Excel for Microsoft 365、Excel 2024/2021/2019/2016、Excel Web App(公式対象)
操作場所Excelのワークシート。追加のCopilotライセンス不要
データブック内の数値とエラーを含むセル範囲。クラウド接続不要
できることエラー値を除外したSUM、非表示行も除外する集計
制限エラー原因は直らない。計算付き配列引数は除外条件に例外がある
注意エラー除外した数字を、欠損のない確定売上と混同しない

売上表に #DIV/0! や #N/A が混じると、SUM もエラーになることがあります。=AGGREGATE(9,6,B2:B6) はエラーを無視して数値だけを合計できます。ただし、エラーを直したわけではありません。

小さな売上表で試す

新しいシートのB列に、次の架空データを入力します。商品名などの実データは不要です。

セル入力表示の想定
B2100100
B3=1/0#DIV/0!
B4200200
B5=NA()#N/A
B65050

C2に =SUM(B2:B6)、C3に =AGGREGATE(9,6,B2:B6) を入力して比較します。前者はエラー、後者は350が期待値です。100、200、50だけが合計されます。9はSUMに相当する集計方法、6は「エラー値を無視する」オプションです。

式期待値意味
=SUM(B2:B6)エラー範囲のエラーが伝わる
=AGGREGATE(9,6,B2:B6)350エラーを除外して合計
=AGGREGATE(4,6,B2:B6)2004はMAX
=AGGREGATE(9,4,B2:B6)エラー4は何も無視しない

引数の意味はMicrosoft SupportのAGGREGATE関数で確認できます。数字を暗記するより、第1引数は何を計算するか、第2引数は何を除くかと分けると理解しやすくなります。

6と7の違い:行を隠す

6行目を右クリックし、行全体を「非表示」にします。そのまま =AGGREGATE(9,6,B2:B6) と =AGGREGATE(9,7,B2:B6) を比べます。

オプションエラー非表示行期待する合計
6無視含む350
7無視無視300
2無視含む350
3無視無視300

2と3は入れ子のSUBTOTALやAGGREGATEも除外する点で6と7とは異なります。今回の表には入れ子の小計がないため、結果はそれぞれ一致します。これは手動で行を隠した場合の想定です。フィルターや複雑な入れ子を含む表では、範囲と計算形式を個別に確認してください。

flowchart TD
 A["B2:B6を集計"] --> B{"第2引数"}
 B -->|6| C["エラーだけ除外"]
 B -->|7| D["エラーと非表示行を除外"]
 C --> E["350"]
 D --> F["300"]

図の差は、正常な50がある6行目を隠したことによります。エラーのある行は両方とも合計しません。

1か所変更して確認する

B3の =1/0 を 150 に変更します。B6は隠したままにしてください。オプション6は500、7は450になる想定です。エラーだった150は合計へ戻り、隠した50は7で引き続き除外されるからです。次にB6を再表示すると、7も500になる想定です。

この変更で分かるのは、AGGREGATEがエラーを修復する関数ではなく、集計するセルを選ぶ関数だということです。B3を元の式へ戻すと再びエラーを含む状態になります。

SUM、IFERRORとの使い分け

通常の集計ではSUMが素直です。エラーがあれば気付けることも重要です。AGGREGATEは速報値や確認途中の表など、異常を含む範囲から暫定的な合計を出したいときに役立ちます。

元データの式を IFERROR(式,0) に置き換える方法もありますが、「実際に0」と「計算できなかった」を同じ表示にすると原因調査が難しくなります。Microsoft SupportのSUM/AVERAGEエラー対処でも、エラーを含む範囲の扱いを説明しています。

エラー件数を併記するなら、別セルで =SUMPRODUCT(--ISERROR(B2:B6)) を使う方法があります。今回の最初の表では2件、B3を150に直した後は1件が期待値です。合計値だけでなく、集計から外した異常が何件あるかを記録すると確認しやすくなります。

間違えやすい点

9と6を逆にしないこと。 9はSUM、6はエラー除外です。AGGREGATEにはMAXやAVERAGEなど複数の計算方法があります。関数番号を変更すると合計ではなく別の結果になります。

列を非表示にしても同じ動作とは限りません。 AGGREGATEは縦方向の行を想定しています。横並びの範囲で列を隠しても、行の非表示と同じようには扱われません。

計算付き配列は別扱いです。 公式資料では、配列引数に計算が含まれると、非表示行や入れ子の小計などを無視しない場合があると注意されています。単純な B2:B6 と、B2:B6*(B2:B6>0) のような式を同一視しないでください。

全件エラーでも、数字だけで判断しないこと。 エラーを除外した合計は、データの完全性を保証しません。参照切れ、分母ゼロ、検索不一致など原因を確認し、修正後に再集計します。

業務で使うとき

速報値には「エラー2件を除外」と記載し、確定値はエラー解消後の再計算を使う運用が安全です。行の非表示が意図的な除外か、一時的な作業かも確認してください。元データを破壊せず別セルで比較することが大切です。

ここで示した数値は公式仕様に基づく期待値であり、Excel実機での入力や印刷結果は未確認です。区切り文字などはExcelの言語・地域設定によって異なる場合があります。

公式情報・一次情報

文書情報

記事タイトル
ExcelのAGGREGATEでエラーを除いて合計する:6と7の違い
作成日
更新日
Source URL
https://papanda925.com/?p=18750

ライセンス: 本記事のうち、当サイトが権利を有する本文・自作図表は、特記なき限り CC BY 4.0 で利用できます。生成AIを活用して作成・編集した内容を含みます。コードについて、別途ライセンス表示またはリンク先GitHubリポジトリのライセンスがある場合は、その条件を優先します。引用・第三者資料・画像・商標等は本ライセンスの対象外です。 利用ポリシー

タイトルとURLをコピーしました