メインコンテンツへスキップ
見出し画像

【QuickTips 34】Googleスプレッドシート ブック内の全ての数式エラーを見つける方法(全エラー値解説も)

    シンプルでちょっと便利な Quick Tipsシリーズです。これまでのTipsをマガジンにまとめてます。


    前回に続き、Googleスプレッドシートの「検索と置換」を使ったQuick Tipsです。今回は

    • ブック内の全数式エラーを見つける方法

    を紹介したいと思います。



    Googleスプレッドシートの数式エラーを見つける方法

    Googleスプレッドシートのブック(ファイル)内で、数式エラーが無いかチェックしたい。

    データ量が少なければ目視でもいいんですが、行数・列数が膨大でシート数も多かったりすると「簡単にエラーを見つける方法がないものか?」って考えますよね。

    Googleスプレッドシートは、Excelと違って エラー値にジャンプする機能がありません。

    画像
    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のものを踏襲してますね。

    画像
    Googleスプレッドシートの ERROR.TYPE関数
    画像
    Excelの ERROR.TYPE関数
    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の 共通範囲を出力する演算子とは、

    画像
    Web版Excelの画面

    =A1:C2 B2:D4

    こんな感じで セル範囲を 半角スペースで連結することで、2つ以上のセル範囲の共通する(交差する)部分を出力することが出来ます。

    👆上の場合は B2:C3 が出力されてますね。

    画像
    3つ以上でもOK

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

    画像

    「数式の範囲が交差しません。」となり、 #NULL !エラーになります。

    これはWeb版Excelでも利用できるのですが、残念ながら Googleスプレッドシートには、重なり合う範囲を出力する この演算子がありません。

    画像
    利用できない演算子なので、数式の誤りで #ERROR !となる

    その為、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)を 数式に組み込むと便利だったりするケースもあります。



     
     

    mir

     
     
    元Excel職人・VBA使いから、Googleスプレッドシート職人・GAS使いにジョブチェンジ。謎解き感覚で お題(課題)を解決していくような記事を書こうかなと。その他、AIやらGeminiやらGoogleWorkspaceネタ全般

    あなたへのおすすめ