Excelでエラーを無視して合計するには?AGGREGATEと未確定の確認
※本記事にはPRを含みます。
こんにちは。「脱会社員」脱サラを応援し続けるブログ、運営者の「なかびー」です。
Excelで金額列のエラー値を除いて合計するなら、例えば対象がD2からD9のとき、集計欄に =AGGREGATE(9,6,D2:D9) と入力します。最初の9は合計、次の6はエラー値を無視する指定です。手動で非表示にした行も合計から外す場合は、同じ範囲で第2引数を7にします。元の列にはエラーが残るので、金額を確認できた分の暫定合計と、まだ集計に入れられない明細を並べて管理できます。
例えば八件の精算表のうち、二件がエラー、一件が数字に見える文字列、一件が空欄というとき、単に合計だけ出すと、残りも確認済みに見えてしまいます。この記事では、書式を変更しないコピーに八件を入れ、確定した0円、負数、入力待ちを見分け、合計から外れたIDを追う一連の手順を紹介します。
集計のセルと、未確認の明細を両方残します。 エラーを飛ばす式を入れた後で、除外した行のID、理由、次に見直す資料を控えます。合計の数字だけで全件を確認したとは判断しません。
Windowsのデスクトップ版Excelを中心に、2026年11月15日に確認したMicrosoftのAGGREGATE関数の説明を参照します。数値はこの記事用の架空の明細で計算した結果です。掲載画像は操作画面ではなく説明用の生成イメージです。
合計が止まる範囲と、最初から対象外にする行を確認する

エラーを見つけたら、金額を上書きする前にコピーを作る
作業の出発点は、合計が表示されなかったセルの数式を見て、どの範囲を参照しているかを控えることです。例えば数式バーに =SUM(D2:D9) と表示されているなら、合計が読みに行くのはD列の2行目から9行目です。表の見た目で末尾と思う位置と、式の最後の行が一致するか見ます。新しい行を追加した後に式を変えていない場合には、エラーと参照範囲の問題が同時に起きることもあります。
元ファイルを複製し、明細ごとの式や入力値が見える状態で作業します。書類の確認中だからといって、エラーを0に置き換えて元の金額を消すと、その行で何が起きていたかを追いにくくなります。例えば商品コードと単価表の照合がうまくいかなかった明細なら、金額の欄だけ書き換えても照合は直りません。入力値と式の両方が確認できる状態を元ファイルに残します。
集計欄は、例では明細の範囲の外に置きます。金額と同じD列の直下に置くときは範囲に自分のセルを含めず、後で追加した行まで参照するかも確認します。フィルターや手動での行の表示を変えて比較したい場合、表示状態の影響を受けない行に集計欄を置けば、値を確かめやすくなります。
八件を、金額の表示とIDで照合する
下の例は、C列に用途などを記録し、A列にID、D列に金額を置いた精算の控えです。想定するすべての明細がD2:D9にあります。文字列やエラーを含むので、表の見たままを足すのではなく、その値が数値かどうかを後の章で確かめます。
| セル | ID | 金額欄の見え方 | この例での状態 |
|---|---|---|---|
| D2 | R01 | 1,200 | 数値 |
| D3 | R02 | #N/A | 照合先を確認中。エラー値 |
| D4 | R03 | 0 | 今回の計算で確定したゼロ |
| D5 | R04 | 300 | 数値。後でこの行だけ手動で隠す例に使う |
| D6 | R05 | -200 | すでに確認した返金の数値 |
| D7 | R06 | 900 | 数字に見える文字列 |
| D8 | R07 | 空欄 | 式も入っていない空のセル |
| D9 | R08 | #VALUE! | 入力元を確認中。エラー値 |
練習ブックでエラーを準備するときは、D3に =NA()、D9に =VALUE("未入力") を入れれば、意図的にエラー値を作れます。D7は '900 と入力して文字列にし、D8は未入力のままにします。表の数値は計算結果の比較用です。実務で見つかったエラーの原因は、表示名から一つに決められるとは限らないので、自分のファイルでは数式と参照先を見ます。
例えばD3が #N/A でも、取引そのものがなかったと判断するのではなく、照合できていないIDとして記録します。返金のマイナス200円は確定済みの金額なので残し、確定した0円もそのまま数値として扱います。エラー、空欄、文字列を一つの色に塗って「全部ゼロ」として片付けると、後から確かめるべき資料を区別できません。
どの合計を頼まれたか、対象の条件を一行で書く
依頼が「確認できた金額だけ先に教えて」なら、金額を確認できた明細と未確定の明細が同じ期間に含まれていても、集計額は前者だけを使えます。依頼が「今月分の最終額を締めて」なら、後者が解消しているかを別に見ます。同じExcelの表を使うときも、「対象はID R01からR08。今朝の時点で確定した金額だけ」など、集計欄の隣に対象を書くと、範囲を変えたとき説明を更新できます。
月の途中に追加された申請は、空欄のまま受領しているものと、そもそも今月の対象に入っていないものを区別します。例えばR07にIDが入力済みなら、金額の欄が空でも今月の一覧で確認する一件として扱えます。一覧に入れることと暫定額へ加えることを分ければ、未確定の件数を少なく見積もらずに済みます。対象外の行まで数に入れないよう、先に対象のIDと期間を確認します。
AGGREGATEの9と6で数値だけの暫定合計を出す

二つの番号は、何をするかと何を除くかを表す
範囲がD2:D9なら、空いている集計欄へ =AGGREGATE(9,6,D2:D9) を入力します。Microsoftの関数表では、最初の9がSUMに当たる集計、二つ目の6がエラーを無視する指定です。第3引数は金額の参照範囲です。例では金額を縦に並べたので、明細が並ぶD列のセルを範囲として指定します。式を入力するときは、D1の見出しや別の集計欄まで選んでいないかを数式バーで確かめます。
この例で加わる値は、R01の1,200円、R03の0円、R04の300円、R05のマイナス200円です。合わせると1,300円です。参照先のD7は値が数値ではなく文字列のため、金額を示しているように見えても合計へは入りません。D8の空セルにも足す値がありません。数値の0は合計を変えませんが、明細として確認済みの種類には含められます。
集計額を見てから「いまの範囲のうち数値が入っている件数はいくつか」を別に調べます。例では数値が四件、エラーが二件、文字列が一件、空欄が一件です。1,300円には数値が入った四件の内容だけが反映されています。取引の件数が八件であることと、八件全部の金額が確定したことを区別します。金額だけを上司へ渡すより、未確定を何件含むかを添えれば、後で金額が変わる理由を伝えられます。
SUMのエラーを集計値でもう一度見つけるための比較欄
確認用のコピーでは、AGGREGATEの式と別に、元の =SUM(D2:D9) を表示しておきます。エラーの入った範囲を通常のSUMで合計すると、この例では集計結果にエラーが出ます。エラーが出る方は、まだ入力や計算を直す場所があるという目印に使えます。入力元を修正しても、どの欄でも常に数値が出ればよいとは考えず、最後に通常の合計でも値が出るかを確かめます。
空欄や文字列が残っている状態で、SUM側の式からエラーが消えることはあります。エラーを取り除いたことと、すべての金額が数値で入ったことは別に確認します。たとえばD3とD9だけ正しい数値に直し、D7が文字列の900のままなら、SUMは数値の行を足しても、900円を含む予定の明細と一致しません。D7を確認する必要があることは、集計額とは別に状態の欄で追います。
修正後の数字を比較するときは、合計を一つだけ控えるより、加わったIDと増減額を書くと確認しやすくなります。例えばR02が800円で確定したら、ほかの値が動いていない条件で、暫定額が1,300円から2,100円になるという計算を先にできます。ファイル上でも同じ差額が見えれば、照合した一件が正しい範囲へ入ったかを判断する手がかりになります。
複数の合計が並ぶときは数字と範囲を一組で見る
確認用に同じシート上で通常のSUMとAGGREGATEを並べた場合は、値の位置が見分けられるよう列名を付けます。たとえば「明細のSUMでエラーを確認」「明細の数値だけを暫定集計」と書き、どちらもD2:D9を参照します。隣のセルへ数式をコピーすると参照先が移動することがあるので、比較用の二つの式は、式の表示を読んで同じ範囲を参照していることを確認します。
一人が金額を修正し、別の人が集計する場合は、変更時点を揃えて比べます。集計用のコピーが数時間前のもので、入力元が先に直っていたら、同じIDに違う内容が表示されることがあります。例えばR02の金額を800円と確認した時刻を記録し、その値を使ったコピーかどうかも明示します。金額が想定通りでも、未確定の記録だけが前の版のままなら、差額の説明に使えません。
非表示の行や小計があるときに、6・7・2を使い分ける

手動で隠した確定金額を、意図した通りに扱う
例のR04は300円が入っている行です。この行だけを手動で非表示とした場合、それでも全明細からエラーを除いた額を見たいなら6を使い、手動で隠した行も合計から除きたいなら7を使います。二つとも最初の引数は9です。この明細の例でR04の300円を外すと、対象はR01、R03、R05になり、合計は1,000円です。6の1,300円と7の1,000円との違いが、ちょうど隠したR04に対応していることを確認できます。
この比較では、フィルターを解除した上で、一行だけを手動で隠す状況を使います。フィルターで絞っている状態から始めると、どの行が抽出されず、どの行を手で隠したかを見分けにくくなります。全件を対象にした精算額が必要なのか、表示中の範囲に絞った途中の確認なのかを集計欄の表示に書きます。結論を出した後は、フィルター条件と手動非表示を記録し、今後どちらの範囲を使うのかも決めておきます。
前提より大きな差が出た場合は、名前と金額で差を追います。例えば期待した差額は300円なのに、別の額になったなら、同時に別の行が隠れていないか、参照範囲が二つの式で違わないかを確認します。二つの式を並べることの目的は、常に二種類の金額を使うことではなく、一つの変更が集計結果にどう反映されたか理由を読めるようにすることです。
内訳として置いた小計が、明細範囲に入っていないかを見る
行を追加するうち、本来は集計対象の外に置いた小計行が参照範囲へ入り込むことがあります。例えばD10に =SUBTOTAL(9,D2,D4:D6) を入れ、全行を表示しているとき、この小計は1200+0+300-200で1,300円です。元の明細と同じ数値を足しているため、D10を明細と一緒に足せば意味が重なります。AGGREGATEのオプション6はエラーを無視する指定で、この入れ子の小計を無視する指定は含みません。
この例で =AGGREGATE(9,6,D2:D10) とすると、D10の数値も含めた計算は2,600円になります。入れ子のSUBTOTALとエラーを除くオプション2なら、同じ範囲でもこの小計を除いた1,300円です。さらに非表示の行も外す目的のときは公式表の3を確認します。小計が手入力の数字や通常のSUMで作られている場合、入れ子のSUBTOTALやAGGREGATEを除く設定だけに頼らず、明細と小計を参照範囲で分けます。
| この例の条件 | 式 | 計算例 | 確認すること |
|---|---|---|---|
| 全行表示、明細のみ | =AGGREGATE(9,6,D2:D9) |
1,300円 | エラー値を除いた数値 |
| R04を手動で隠す、明細のみ | =AGGREGATE(9,7,D2:D9) |
1,000円 | 隠した300円も外れる |
| 全行表示、小計D10を範囲に含む | =AGGREGATE(9,6,D2:D10) |
2,600円 | 小計も加わるので範囲を見直す |
| 全行表示、入れ子のSUBTOTALを除く | =AGGREGATE(9,2,D2:D10) |
1,300円 | 手入力の小計には同じ判断を当てはめない |
この例では、第3引数に縦の直接参照を使います。Microsoftは、配列の引数で掛け算などを同時に行うと、オプションが指定されていても、非表示の行や入れ子になった小計などの無視が働かない場合を説明しています。範囲と条件を組み合わせるために式を増やす前に、金額列をそのまま参照できるか考えます。エラーを除外せずに表示行とテーブルの集計行を使い分ける用途は、Excelテーブルの集計行の使い方で確認できます。
横方向の表や、明細と小計が混在する表は構成を読む
AGGREGATEの案内は、データの列や縦の範囲を対象にする関数として説明しています。横向きの集計表で、列を隠したらこの章の非表示行と同じ結果になると考えず、元の明細がどちら向きに並んでいるかを確認します。例えば月ごとの合計を横に置いた表なら、明細を縦に持つ別のシートを集計し、月別の表示側でその合計を読む方が、集計がどの方向の非表示に応じるのか説明しやすくなります。
支店ごとの小計が明細の間に入る場合は、最初の一ブロックだけを読むと何が小計か分からないことがあります。金額が数値として表示されている行を見つけたら、その式がSUBTOTAL、AGGREGATE、通常のSUM、手入力のどれかを見ます。すでに明細で足した値を一緒に拾っていないか、一つの支店だけで手計算し、その差が金額と一致するかを確かめられます。設定番号をすべて試す前に、集計対象外の行を明細の外へ置けるかを考えます。
エラー・文字列・空欄を補助列で見分ける

数値が見た目通りか、一行ずつ確かめる
集計結果とは別に、E列を状態の欄として使います。E1に「金額の状態」と書き、E2へ =IF(ISERROR(D2),"数式エラー",IF(ISNUMBER(D2),"数値",IF(ISBLANK(D2),"空欄","その他"))) を入力します。E2をE9まで下へコピーすると、行ごとに同じ基準で金額の状態を見られます。数式に慣れていない場合は、まずE2とE3を見て、D2は数値、D3は数式エラーと表示されることを確認します。その後で他の行へ移れば、参照が一行分動いたかを見分けやすくなります。
この式は、はじめにISERRORでエラー値かを調べ、次にISNUMBERで数値か、ISBLANKで空のセルかを調べます。MicrosoftのIS関数の説明では、ISNUMBERなどは調べる値を別のデータ型に変換しません。式の結果として空文字列が入っているセルは、見た目が空白でも、最初から何も入っていない空セルと同じ表示結果になるとは限りません。この式では空文字列などは「その他」に入ります。
E7で「その他」と出た場合は、D7がこの例では文字列の900だと分かっています。実務表では、数字に見える文字、文章、空文字列、別の種類の値が同じ区分へ入る可能性があります。すべて数値へ変換する前に、その列が金額として使われているか、入力元に何が書かれていたかを確認します。番号の列にまで同じ処理を広げると、先頭のゼロなど必要な情報を失うことがあるため、確認する範囲はD列の該当行に絞ります。
八件のうち、合計に使えた行と確認が残った行を並べる
すべての行を表示した状態で、E列に書かれた状態を一覧にします。例では数値四件、数式エラー二件、空欄一件、その他一件です。確認するIDを拾うには、表を一行ずつ読んでも、E列を状態で絞り込んでも構いません。絞った表からIDを控える場合は、控えが終わったら絞り込みを解除し、暫定額を全件表示の状態で再確認します。数値の行だけを見ていると、控え忘れたエラーのIDに気づきにくくなります。
| 状態 | 件数 | この例のID | 次に確かめるもの |
|---|---|---|---|
| 数値 | 4 | R01、R03、R04、R05 | 0円と返金を含む。除外しない |
| 数式エラー | 2 | R02、R08 | 照合表、入力元、数式の参照先 |
| 空欄 | 1 | R07 | 金額が0と決まったのか、資料がまだないのか |
| その他 | 1 | R06 | 元の表で900が金額なのか、数値として入力すべきか |
状態の件数を別欄に出す場合は、例えば数式エラーを =COUNTIF(E2:E9,"数式エラー") で数えます。例では二件です。「数値」の数は4、「空欄」と「その他」は各1です。四つの件数を足して八件になることを確認すると、E列に式をコピーし忘れた行がないかも点検できます。すべての行にIDが入っていることを見たうえで、D列とE列の最終行がそろっているかを確かめます。
ここで使うCOUNTIFの件数は、指定範囲の全体を数えるための例です。画面でフィルターをかけた行数と同じ意味ではありません。表示中の明細だけを数えたと思い込まず、「全明細の状態別件数」と欄名を付けます。金額側で非表示の行も外した場合には、全件の状態表と集計額が別の条件を使っていることを明示してください。提出する集計では、件数と金額の条件をそろえるか、それぞれが何を表すか書き添えます。
エラーを取り除く作業と、エラーを飛ばす集計を別に続ける
R02の照合先が見つからない場合は、元資料のIDが同じか、余分な空白が付くなどして値が変わっていないかを調べます。R08で文字から数値へ変換できない場合は、それが正しい金額なのか、単に未入力を示す表示なのかを調べます。例の二件は説明のため意図的に作った式で、実物にはさまざまな原因があります。表示名だけで直す操作を決めず、セルの式と資料を照らします。
Excelにエラーチェックの[エラーを無視]という操作が表示される場合があります。Microsoftの説明では、エラーチェックの通知を扱う操作です。ここで使うAGGREGATEの集計オプションとは役割が違います。通知の印が減ったことを集計範囲のエラーがなくなった根拠にせず、D列の値とE列の状態を読み直します。
入力元を自分で直せない場合は、まずその行がどの資料待ちかを書きます。例えばR02は単価表の確認待ち、R08は申請元が入力内容を確認中という控えを残せば、明細を送る相手も次の確認先が分かります。日付まで確定していないなら、未確定のまま同じ欄に残し、次の集計で確かめる対象にします。まだ資料がないことを示すために、金額欄へ仮の0を入れる必要はありません。
「エラーなし」と「状態が確定」を別の完了条件にする
状態の補助列には、あくまでセルの種類が表示されます。たとえば金額欄に数値のゼロを入れれば「数値」と表示されますが、ゼロ円と判断した資料があるかどうかを、この式で調べることはできません。D4のように確定のゼロなら集計に含め、判断待ちなのに便宜的に数値を入れたのであれば、確認の記録上はまだ確定としません。状態が数値の行についても、必要なら資料と値を照合します。
補助列の分類では「その他」の件数が一件だったとしても、該当する行が複数のシートにまたがらないか、集計の範囲が正しいかは別に調べます。例えば明細の最後に一件が増え、E列の式がないのにCOUNTIFだけ更新されていた場合、その一件が四分類のどれにも数えられません。四つの件数の合計と予定のID件数を比べれば、式が入っていない行に戻れます。
保留が解消したら、合計と件数の両方を照合する

確認済みの値だけを一行ずつ戻す
例として、最初の暫定額が1,300円だった明細で、照合が済んだR02が800円、入力元を調べたR08が500円だったとします。先にR02を直すと、全行表示で同じ範囲を使う場合の計算は1,300+800=2,100円です。次にR08の500円も確認できたら2,600円です。エラーが二件から零件に変わっても、この時点ではまだR06の文字列とR07の空欄が残っています。
R06を照合し、900円が正しいと確認してD7を数値として扱うと、2,600+900=3,500円です。R07は空欄だった理由を調べ、資料で0円と確定した場合にだけ0を入力します。この場合、最新の合計は3,500円のままです。R07に確定した0円が入ったため、数値が入った件数は一件増えます。この例のように、合計が変わらなくても明細の調査が一件終わったことを、件数で読み取れます。
| 確認した条件 | 暫定または確定合計 | 数値の件数 | 残っている確認 |
|---|---|---|---|
| 初めの八件 | 1,300円 | 4 | R02、R06、R07、R08 |
| R02を800円で確認 | 2,100円 | 5 | R06、R07、R08 |
| R08を500円で確認 | 2,600円 | 6 | R06、R07 |
| R06を900円の数値に修正 | 3,500円 | 7 | R07 |
| R07を資料と照合して0円入力 | 3,500円 | 8 | この例ではなし |
この表は同じ八件と同じ参照範囲で、行を全部表示している場合の計算です。式が参照する最後の行を後から増やした場合や、数値をいくつか同時に変えた場合には、表の差額をそのまま期待してはいけません。自分の帳票では、変更の前後で一件ずつ比較できる適切なタイミングを選びます。件数と合計の両方が一気に動いた場合は、結果のセルだけを修正せず、どの明細を確認済みにしたかを数え直します。
暫定額と確定額の見せ方を分けて渡す
最終の精算に使うなら、元の資料に照らして残りの行を解消し、通常のSUMも含めて結果を確認します。コピーでエラーを飛ばした1,300円を先に共有する場合には、「八件中、数値が確認できた四件の暫定合計」という意味を書き、外したIDを併記します。相手に必要な情報がどの状態の数字かを考え、未確定分が反映されていないものを最終額として渡さないようにします。
提出用に数式を含める必要がない場合も、数式が残る作業用と、提出する値のコピーは別にしておけます。Excelの数式を値に変換して提出版を確認する手順では、元の数式や値の書式を残しながら受渡しを考えます。値に変換した提出版へ追加の明細が反映されるわけではないので、提出時点の件数と確認日も控えます。
入力元を直さずに暫定額だけ集計した場合は、そのことが伝わるように名前を付けて保存します。例えば「明細八件・暫定・確認待ち四件」と書けば、別の人が開いたときに件数の意味を読み取れます。作り直すときは、残した作業用から現在の資料の値を確かめ、6と7のうち目的に合う方、式の範囲、隠した行の有無を一緒に見ます。
同じ手順を翌月も使うなら、どこまでが明細かを確定する
前月のシートを複製すると、集計式や状態の判断式にも前月の最後の行が残ることがあります。新しい明細が一件増えたら、IDを追加できたことと、D列の参照範囲、E列の式の範囲がそろったことを確かめます。今月の合計が前の計算と偶然同じになっても、末尾の行を読んだ証拠にはならないため、最後に追加したIDとその金額を一件見ると差に気づけます。
仮に末尾に数値の明細が一件追加され、E列には状態が出たのに合計が動かない場合は、集計式の最後の参照位置を確認します。逆に合計が増えて状態別件数が変わらなければ、E列がコピーされていない行を疑います。一つの式だけを見て問題がないと判断せず、明細が入っている最終行から両方の式を比べます。古い小計セルを含めていれば、参照を明細へ絞り直してから全体を照合します。
エラーを無視して合計を出す作業は、入力が確定していない行を探すためにも使えます。合計が表示されたら、まず金額に反映した対象と、その後に確認するIDを読みます。全行の状態が判断できたら、確認した値だけで計算した結果を通常の合計と比べて提出します。
差額が足りないときに見る二つの例
例の最後の合計3,500円に対して2,600円になる場合は、差額900円を見て、まずR06がまだ文字列か、参照範囲の外にないかを確かめられます。金額が同じ行が複数ある表では、差額だけで原因を断定せず、IDと状態の記録を使って照らします。逆に3,500円が表示されていても数値が七件なら、0円と確定したR07に入力があるか調べます。件数も一致することが、金額が偶然同じケースを区別する手掛かりになります。
今月の作業用の表を他の担当者が次に開くなら、参照範囲のほかに、手動で隠した行を戻したかを渡します。「表示上は七件、全件は八件。暫定額は全件範囲の数値だけ」といった記録があれば、開いた直後の見た目に合わせて数え直す必要がありません。行の表示を変えた場合は、全件の合計に戻って結果が変わったか、明細の値はそのままかを一つずつ読みます。
参照したMicrosoft公式資料