この記事について
この記事は、生成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列に、次の架空データを入力します。商品名などの実データは不要です。
| セル | 入力 | 表示の想定 |
|---|---|---|
| B2 | 100 | 100 |
| B3 | =1/0 | #DIV/0! |
| B4 | 200 | 200 |
| B5 | =NA() | #N/A |
| B6 | 50 | 50 |
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) | 200 | 4は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の言語・地域設定によって異なる場合があります。
公式情報・一次情報
Microsoft Support:AGGREGATE関数 — 関数番号、オプション、配列形式
Microsoft Support:SUM/AVERAGEの#VALUE!エラー対処 — エラーを含む範囲
Microsoft Support:エラー表示の扱い — エラー表示と原因の違い
