
【QuickTips 34】Googleスプレッドシート ブック内の全ての数式エラーを見つける方法(全エラー値解説も)
シンプルでちょっと便利な Quick Tipsシリーズです。これまでのTipsをマガジンにまとめてます。
前回に続き、Googleスプレッドシートの「検索と置換」を使ったQuick Tipsです。今回は
ブック内の全数式エラーを見つける方法
を紹介したいと思います。
Googleスプレッドシートの数式エラーを見つける方法
Googleスプレッドシートのブック(ファイル)内で、数式エラーが無いかチェックしたい。
データ量が少なければ目視でもいいんですが、行数・列数が膨大でシート数も多かったりすると「簡単にエラーを見つける方法がないものか?」って考えますよね。
Googleスプレッドシートは、Excelと違って エラー値にジャンプする機能がありません。

しかしエラー値は、エラー値の文字列で検索が出来ます。

この特性とGoogleスプレッドシートで使える 正規表現を組み合わせた検索で、全エラー値を一発で見つけちゃおう!っていうTipsです。
Googleスプレッドシートで ブック内の全ての数式エラーを見つける手順
さっそく手順を紹介しましょう。
1️⃣「検索と置換」を立ち上げる
Ctrl+Hまたは、メニューから 編集 > 検索と置換 として、検索と置換のダイアログを立ち上げます。

2️⃣検索値に以下を入れて「正規表現を使用した検索」にチェックをいれて検索
^#(?:NULL!|DIV/0!|VALUE!|REF!|NAME\?|NUM!|N/A|ERROR!)$

これだけです。

検索範囲を「すべてのシート」にしておけば、全シートのエラーを見つけ、順番にエラーのセルにジャンプすることが出来ます。
簡単ですね!
全エラー検索 正規表現解説
^#(?:NULL!|DIV/0!|VALUE!|REF!|NAME\?|NUM!|N/A|ERROR!)$
AIに聞けば教えてくれますが、ちょっとだけ解説を。
まずエラー値は全て #から始まります。というわけで正規表現でテキストの先頭を意味する ^ と組み合わせて 出だしは ^# とします。
その後 複数のエラー値を OR検索 する部分が
(?:a|b|c|d) ・・・ a b c d のいずれか
この部分です。
2文字以上の単語を | (パイプ)で区切って記述することで、OR検索となります。その際、先頭の ^#を区別する為に () カッコで OR検索したい箇所を括っています。
ただし 正規表現は () で括るとキャプチャグループ化(カッコで括った正規表現とマッチした文字列を記憶し、置換後の文字列指定の際に $1で呼び出す機能)されてしまいます。
今回は置換する必要が無いので (?: ) とすることで、 非キャプチャグループにしています。
※非キャプチャグループとした方が軽量といわれますが、検索と置換で利用する程度なら気にする必要はないので ?: は無しでも問題ありません
また、エラー値の中で唯一 #NAME ? だけは ? で終わるのですが、正規表現において ? は 前の文字の0または1回の繰り返しを意味するメタ文字である為、これをエスケープする必要があります。
NAME\?
👆それがこの部分です。?の前に \(バックスラッシュ)を入れてエスケープしています。
最後に文末を意味する $ を付けることで、エラー値をテキストとして含むセルを除外し、エラー値と完全一致するセルだけを検索しています。

Gemini in Sheets が使えるならサイドバーで聞いてもOK
ここまで読んでくれた方の感想としては、
便利だとは思うけど・・・
^#(?:NULL!|DIV/0!|VALUE!|REF!|NAME\?|NUM!|N/A|ERROR!)$
こんな正規表現、覚えられないし、いざという時にサクッと作れない!!
ってのが多いんじゃないでしょうか?
ま、そらそうですよね。
AI登場前だったらGoogle Keepあたりに保存して、いざという時にコピペして使ってくださいと言うんですが、今やAIでだいたい解決できちゃう時代。
もし Googleスプレッドシートのサイドバーで Geminiが使える(Gemini in Sheets が使える)環境なら
全てのシートからエラーになってるセルを見つけて
こんな指示を入れるだけでOK

シート毎にどのセルにどんなエラーがあるかをまとめてくれます。
これで十分てなりますよね。。
実は今回のようなAIが世に出る前に発信しておけば・・・ってネタが結構ありますw
でも Gemini in Sheetsが使えない環境だったり、もれなく確実に全エラーを見つけたいって場合は、今回紹介したTipsが役に立つはず!
是非、活用ください。
Googleスプレッドシートの 数式エラー値ガイド
ここからはGoogleスプレッドシート雑学なんで、小技(Tips)だけ知りたい方は「スキ」ボタンを押して終了でOKです。
上の正規表現で網羅した、Googleスプレッドシートの 全エラータイプについて解説します。
Googleスプレッドシートの エラー値 ガイド

Googleスプレッドシートには、数式エラーとして表示されるエラー値が 全部で 8種類あります。(漏れがあればお知らせください)
Googleスプレッドシートには ERROR.TYPE関数という エラー値を判別して数値で返す関数があり、ここにエラータイプの一覧があります。
ERROR.TYPE関数の結果は 1~7までは Excelのものを踏襲してますね。


https://support.microsoft.com/ja-jp/excel/functions/error-type-function
それぞれのエラーについて解説します。
エラー #DIV/0!(ERROR_TYPE 2)

これは 0で割った時に発生するエラーです。
よくあるのが、 =A1/B1 で割合を出そうとして、B1が空白セルというケース。
=1/0
で発生させることが出来ます。
※ERROR_TYPE 2 から始めてる理由は後で触れます
エラー #VALUE !(ERROR_TYPE 3)

文字列を数値計算しようとした時に発生するのが #VALUE !エラーです。
単位がついたまま足したり引いたり掛けたりすると、これが出ることがあります。
="abc"+1
こんな式で発生させることが出来ます。
エラー #REF!(ERROR_TYPE 4)
参照、または出力の不具合によるエラー値です。
幾つかケースがあって
存在しないシート名を参照していたり、参照していたシートや行、列を削除してしまうことで発生するケースや

=VLOOKUP(1,A:B,5) として、A:Bの2列を参照しているのに、第3引数を5として 範囲外を取得しようとするケース

スピル式を出力するセル範囲が既に埋まっているケース(Excelの場合は #SPILL ! または #スピル ! エラー)

さらに、式を入れた自身のセルを参照してしまう 循環参照になっているケースやIMPORTRANGE関数でアクセスを承認する前など、意外とこのエラーを目にするケースは多いです。
簡単に発生させるには 以下の式がおススメです。
={}
エラー #NAME ?(ERROR_TYPE 5)

存在しない、定義されていない関数を使った時に発生します。
よくあるのが長い関数名の時のスペルミスです。

=abc()
こんな式で発生させることが出来ます。
エラー #NUM !(ERROR_TYPE 6)

関数の引数の数値が、数学的に許される範囲外である場合に発生するエラーが #NUM !です。
👆上の例だと SQRTという平方根(ルート)を返す関数で発生しています。
=SQRT(4) は 2 を返します。
しかし、 =SQRT(-1) のように 引数にマイナスの値を入れてしまった場合、2乗してマイナスになる数は存在しない(虚数になる)ので、エラーが返ります。
他にも主に数学系の関数で発生するエラーなんですが、それ以外だと

YEAR関数で 1899/12/30 以前の日付から 年を取得しようとして #NUM !エラーとなるケースがあります。
Googleスプレッドシートは 1899/12/30(シリアル値 0)より前の日付もマイナスのシリアル値として 日付データとして扱えるのですが、YEAR関数では年を取り出すことが出来ません。
余談ですが、1899/12/30 以前の日付から 年の数値を取得したい場合は

=--TEXT(A2,"yyyy")
テキスト関数で年部分を取り出してから数値化(*1でも VALUE関数でもOK)で対処できます。
エラー #N /A(ERROR_TYPE 7)

条件にマッチするものが無かった時のエラーが #N /A です。
VLOOUPや、MATCH、XMATCH、FILTERなどでよく見かけます。
また、LET関数で 引数が偶数個の場合(最後の出力の記述漏れ)でも表示されます。

#N /Aエラーは NA関数 =NA() を使うことで意図的に出力できます。ただし、セル右上の 赤◥ のエラーメッセージは表示されません。

他のエラー値とは違って、検索して見つからなかった結果ともいえる為、NAだけを区別して
ISNA関数 ・・・ #N /Aエラー であるか?を検証
IFNA関数 ・・・ 第1引数が #N /Aエラー の時は 第2引数の値を返す
ISERR関数 ・・・ #N /A以外のエラーであるか?を検証
これらの関数が用意されていたりします。
エラー #ERROR !(ERROR_TYPE 8)
#ERROR ! は はExcelには存在しない、Googleスプレッドシート専用のエラー値で、数式の記述ミスで表示されます。
よくあるのが、テキストを連結する際に &を書き忘れるケース。

インストール版のExcelの場合は、このエラー値が無い代わりに、「この数式には問題があります」とダイアログが出て入力が確定出来ません。

Web版Excelの場合は、入力は確定するんですが エラー表示ではなく

赤い点線で強調され、数式のまま表示となります。(一見なにが間違いなのかわからないので厄介です)
エラー #NULL!(ERROR_TYPE 1)
最後に エラータイプ 1 #NULL !エラーを紹介します。
これはExcelの 共通範囲演算子(スペース)を利用した際に表示されるエラー値で、Googleスプレッドシートでは発生しないエラーと考えられます。
Excelの 共通範囲を出力する演算子とは、

=A1:C2 B2:D4
こんな感じで セル範囲を 半角スペースで連結することで、2つ以上のセル範囲の共通する(交差する)部分を出力することが出来ます。
👆上の場合は B2:C3 が出力されてますね。

この時、範囲に 重なり合う部分がないと

「数式の範囲が交差しません。」となり、 #NULL !エラーになります。
これはWeb版Excelでも利用できるのですが、残念ながら Googleスプレッドシートには、重なり合う範囲を出力する この演算子がありません。

その為、Googleスプレッドシートでは #NULL !エラーが出力されるケースは無いと言ってよいでしょう。
恐らく ERROR_TYPE関数の結果(数字)の 互換性を保持する為に 用意されたエラーと思われます。
エラー値は 手入力できる
しかしGoogleスプレッドシートで、この #NULL !エラーを意図的に出力することは可能です。

単純に セルに直接エラー値 #NULL !を 入力・確定するだけでOK。
文字が中央寄せになり エラー値として扱われているのがわかりますね。
ISERROR関数によるB2セルの判定結果も TRUEに変化しています。
他のエラー値や TRUE、FALSEといったブール値も同様で、Googleスプレッドシートは、エラー値やブール値(Boolean型)を 直接入力 出来るようになっています。(Excelでも同様)
Googleスプレッドシートのエラー値、見つける方法と それぞれの発生原因が理解出来ましたね!

といっても数式が複雑だと、「ここからがほんとうの地獄だ」ってことが多いんですが・・・😅
今回のQuickTips関連note
Googleスプレッドシートで使った「検索と置換」の超絶テクニックをさらに学びたい方は 👇こちらのマガジンをチェック!
Gemini in Sheetsをお試しで使う方法や 活用方法(なにが出来るか?)については 👇こちらの note をチェック
直接出力すると #REF !エラーになる TOCOL(,1)を 数式に組み込むと便利だったりするケースもあります。