はじめに：このカオスな表、合計できますか？
Excelで集計をしているとき、参照範囲に「#DIV/0!」や「#VALUE!」などのエラー値が一つでも混じっていると、SUM関数の結果もエラーになってしまって困ったことはありませんか？
今回は、そんな状況をあえて極端にした、カオスな配列をご用意しました！
今回の挑戦者（配列データ）はこちら！
表が必要な方は、以下の数式をA1セルに貼り付けてくださいね！
={95,21,"あ","","57";#DIV/0!,28,56,#NULL!,98;" ",#N/A,13,9,77;#NAME?,57,"#スピル!",49,#VALUE!;45,"#CALC!","16","$",61}
うーん、見事なまでにバラバラですね！
数値だけでなく、エラー値、文字列、空白、そして「”57″」のような文字列扱いの数値まで混在しています。
エラー値については、以下の記事で詳しく解説しています。
Excelエラー9選！わざとエラーを出して原因と意味を学ぼう
今回のゴール
この配列の中から、「数値」および「数値に変換可能な文字列」だけを抜き出して合計し、正解の「682」を導き出すこと！
今回は「一度使った関数（SUM以外）は、次の数式からは使用禁止！」という縛りプレイで、6つの異なるアプローチを紹介します。
さあ、あなたならどうやってこのカオスを攻略しますか？一緒に考えてみましょう！
本記事では、無料のWeb版Excelを使用して検証および画像の作成を行っています。Windowsはもちろん、MacやLinuxの方もブラウザさえあれば挑戦できます！
6つのアプローチで、「682」を導き出せ！
アプローチ1：王道！IFERROR関数
【使用禁止関数】
なし（最初なので使い放題！）
考えかた
まずは、実務で最も推奨される、王道中の王道アプローチです。
「計算してみて、エラーが出たら0にしちゃえばいいじゃない」という発想です。
数式と解説
=SUM(IFERROR((A1:E5)*1,0))
1. (A1:E5)*1: まず、配列全体に「1」を掛け算します。これが重要な下準備です。
- 純粋な数値（95など）は、そのまま数値になります。
- 数値の文字列（”57″など）は、数値（57）に変換されます。
- 完全な空白は、数値（0）に変換されます。
- 計算不能な文字列（”あ”や”半角スペースなど）は、
#VALUE!エラーに変わります。 - 元々のエラー値（#DIV/0!など）は、そのままエラーとして残ります。
2. IFERROR(..., 0): IFERROR関数が、エラーになっている部分（元々のエラー＋変換で生じたエラー）をすべて「0」に置き換えます。
3. SUM(...): 最後に、きれいになった数値を合計します。
メリット: 最もシンプルで処理も高速。Excel 2007以降なら迷わずこれです。
デメリット: Excel 2003以前の古い環境では動きません。
アプローチ2：古いExcelでもOK！ISERROR関数
【使用禁止関数】
IFERROR
考えかた
IFERROR関数がなかった時代、私たちはどうしていたでしょうか？
そうです、IF関数とISERROR関数を組み合わせて、手動でエラー判定をしていましたね！
数式と解説
=SUM(IF(NOT(ISERROR(A1:E5*1)),A1:E5)*1)
1. A1:E5*1: アプローチ1と同様に、まず数値化を試みます。
2. ISERROR(...): ISERROR関数で、エラーかどうかを判定します。
エラーならTRUE、数値ならFALSEが返ります。
3. NOT(...): NOT関数で判定を反転させます。つまり、数値ならTRUE（計算できる！）、エラーならFALSE（ダメだ！）となります。
4. IF(..., A1:E5): IF関数で、TRUE（数値）の場所だけ元の値を取り出します。
FALSEの場所はFALSEのまま残ります。
5. ... * 1: 取り出した値に再度1を掛けて、文字列の数値を数値化しつつ、FALSEを0に変換します。
6. SUM(...): 最後に合計します。
メリット: 非常に古いバージョンのExcel（2003以前）でも動作します。
デメリット: IFERROR関数の数式より長くなってしまいます。
アプローチ3：ISERRとISNA関数のダブルチェック！
【使用禁止関数】
IFERROR, ISERROR, IF
考えかた
「ISERROR禁止？ ならば、エラーの種類を細かく見てやろうじゃないか！」
Excelには、#N/A以外のエラーを検知するISERR関数と、#N/Aだけを検知するISNA関数があります。
これらを組み合わせれば、全方位のエラーをカバーできます。
数式と解説
=SUM(SWITCH(ISERR(A1:E5*1)+ISNA(A1:E5),0,A1:E5,1,0)*1)
1. ISERR(A1:E5*1): 数値化計算で発生するエラー（#VALUE!など）と、元のエラー（#DIV/0!など）を検知します。
ただし、#N/Aだけはスルーします。
2. ISNA(A1:E5): 元データに含まれる #N/A エラーをピンポイントで検知します。
3. ... + ...: この2つの判定結果（TRUE=1, FALSE=0）を足し算します。
- エラーなし（数値）なら
0 + 0 = 0 - 何らかのエラーなら
1 + 0または0 + 1で1
4. SUM(SWITCH(..., 0,A1:E5, 1,0)*1): SWITCH関数で分岐します。
「判定結果が0（数値）なら元の値を返す」「1（エラー）なら0を返す」
文字列の数値を数値化し、SUM関数で合計します。
今回はIF関数が禁止なので、SWITCHが活躍しましたね！
メリット: エラーの種類によって処理を分けたい場合に応用できます。
デメリット: SWITCH関数はExcel 2019以降でないと使えません。そして何より、面倒くさい！（笑）
SWITCH関数とは？
「この値がAなら〇〇、Bなら△△、Cなら□□…」というように、一つの値（式）の結果に応じて、次々と条件分岐させたい時に使う関数です。 これまでIF関数を何度も重ねて書いていたような複雑な条件分岐も、SWITCH関数ならスッキリと見やすく書くことができます。
使い方のイメージ： =SWITCH(判定したい式, 値1, 結果1, 値2, 結果2, ..., [どれにも当てはまらない場合の結果])
まるで「スイッチ」を切り替えるように、値に対応した結果をポンと返してくれます！
アプローチ4：ERROR.TYPEでエラーを数値に変換
【使用禁止関数】
IFERROR, ISERROR, IF, SWITCH, ISERR, ISNA
考えかた
エラー値には、それぞれ固有の「番号」があることをご存知ですか？
ERROR.TYPE関数を使えば、エラーを数値に変換できます。
数式と解説
=SUM(IFNA(ERROR.TYPE(A1:E5*1)*0,A1:E5)*1)
1. ERROR.TYPE(A1:E5*1): 数値化を試みた配列に対し、エラーであればその種類に応じた番号（今回は1～14）を返します。
そして重要なのは、エラーでない（正常な数値の）場合は #N/A エラーを返すという性質です。
2. ... * 0: エラー番号（1～8）に0を掛けて、すべて「0」にします。
これで、「元々エラーだった場所」は「0」になりました。
一方、「元々数値だった場所」は #N/A * 0 で相変わらず #N/A のままです。
3. IFNA(..., A1:E5)*1: ここでIFNA関数の出番！
「#N/A（＝元々数値だった場所）」を検知し、その部分に「元の値（A1:E5）」を代入します。
すでに「0」になっている部分はそのまま残ります。1を掛けて数値化します。
4. SUM(...): 最後に合計します。
逆転の発想を使った、見事なロジックですね！
メリット: エラーを数値情報として扱えるため、分析に役立ちます。
デメリット: ERROR.TYPEの「正常値だと#N/Aになる」という挙動を知らないと、解読不能です。
アプローチ5：AGGREGATE関数を利用
【使用禁止関数】
IFERROR, ISERROR, IF, SWITCH, ISERR, ISNA, ERROR.TYPE, IFNA
考えかた
ここからは、エラー判定関数を使わずに攻略します！
集計の万能選手AGGREGATE関数には、「エラー値を無視する」という強力なオプション（第2引数=6）があります。
これを使えば勝てる…と思いきや、単純な合計（第1引数=9）では「文字列の数値（”57″）」を計算してくれません。
さらに、AGGREGATE関数の合計に計算済みの配列を渡すと#VALUE!エラーになります。
そこで、LARGE（第1引数=14）という機能を悪用（？）します。
SUM（第1引数=9）では、第3引数に「A1:E5*1」を渡すとエラーになりますが、
LARGE（第1引数=14）では、問題なく動作します。
数式と解説
=SUM(AGGREGATE(14,6,A1:E5*1,SEQUENCE(COUNT(A1:E5*1))))
1. A1:E5*1: 数値化計算を行います。エラーも発生します。
2. AGGREGATE(14, 6, ..., k): 第1引数「14」はLARGE（大きい順にk個取り出す）機能です。
第2引数「6」は「エラー値を無視する」オプションです。
この組み合わせなら、計算過程でエラーが発生しても、それを無視して数値だけを抽出できます！
3. SEQUENCE(COUNT(A1:E5*1)): LARGE関数の「k（順位）」として、「1位からN位まで全部」を指定するための連番を作ります。
COUNT関数は数値の個数だけを数えるので、エラーを除いた有効なデータの数だけ連番が生成されます。
4. 結果として、エラーを除いたすべての数値が配列として返され、それをSUMで合計します。
メリット: エラー処理関数を使わずに、計算機能のオプションだけで解決できます。
デメリット: 「合計したいのにLARGE？」という、直感に反する数式になるため、可読性は低いです。
アプローチ6：ISNUMBER関数で数値だけを抜き出し
【使用禁止関数】
IFERROR, ISERROR, IF, SWITCH, ISERR, ISNA, ERROR.TYPE, IFNA, AGGREGATE, SEQUENCE, COUNT
考えかた
最後は、配列操作のパズルです。
INDEX関数を使って、ピンポイントで「数値の場所」だけを参照しに行きます。
数式と解説
=LET(a,A1:E5,b,ISNUMBER(a*1),SUM((0&INDEX(a,b*ROW(a)+(b*1=0)*3,b*COLUMN(a)+(b*1=0)*1))*1))
この長い数式、LET関数で整理しながら解読しましょう。
1. b,ISNUMBER(a*1): 配列aを数値化したものが「数値かどうか」を判定し、TRUE/FALSEの配列bを作ります。
2. INDEX(a, 行番号, 列番号): ここがクライマックス！参照する行と列を計算でコントロールします。
・b*ROW(a): 数値（TRUE）なら「その行番号」を、エラー（FALSE）なら「0」を返します。
・(b*1=0)*3: 数値（TRUE）なら「0」を、エラー（FALSE）なら「3」を足します。
※ここで「3」行目を指定しているのは、今回のデータの3行1列目が「スペース（空白）」だからです！
つまり、「数値ならそのセルを、エラーなら3行1列目の空白セルを参照しろ！」という命令を作っているのです。
b*COLUMN(a)+(b*1=0)*1の部分でINDEX関数に渡す列番号を作っています。
ロジックは同じなので説明は省略します。
3. 0&...: 取得した値の先頭に「0」という文字をくっつけます。
空白セルを参照した場合、「0 」（ゼロとスペース）となり、数値化すると「0」になります。
数値の場合は「095」のようになりますが、数値化すれば「95」に戻るので問題ありません。
4. SUM(...*1): 文字列を数値化して合計します。
メリット: どんな関数禁止縛りでも戦える（かも）。
デメリット: 実用性は皆無です（笑）。「空白セル」が特定の位置（今回は3行1列）にあることを利用しているため、データの配置が変わると破綻する可能性があります。
まとめ：エラーは、消すだけが能じゃない！
実務では迷わずアプローチ1のIFERRORを使うべきですが、こうして制限を設けて考えてみると、エラー値の性質や、各関数の意外な挙動が見えてきますよね。
「エラーが出たら終わり」ではなく、「エラーの種類を見分ける」「エラーを逆手に取る」という発想を持てば、Excelスキルはもっと自由になります。
次回も、あっと驚くようなテーマで、皆さんの暇つぶし（とスキルアップ）をお手伝いします！
お楽しみに！
