Excelで数値かどうかを判断するのにISNUMBERは不適

先日、「セルに入力されている値が数値かどうか調べる」という方法について調べておりました。

Googleで検索を行うと、下記のように「ISNUMBER関数」を使用するべきというサイトが大量にヒットします。

「標準で用意されている関数で調査できるなら楽でいいな」と思い、私もISNUMBER関数を使用してみました。

しかし、私が実装したい要件の場合ですと、単純にISNUMBER関数を使うだけだと問題が発生する場合があることがわかりました。

その問題内容と解決方法について記載をしてみたいと思います。

ISNUMBER関数を使用すると発生する問題

ISNUMBER関数は非常に簡単な関数で、引数で与えたセルの値が数値かどうかを返却してくれます。

↑「1」と「0.01」の場合は数値なのでTRUE、「あ」は数値ではないのでFALSEが返却されます。

一見良さそうに見えますが、以下の場合は期待した結果が得られません。

上記は、A1セルの値が「1」なのにも関わらず、ISNUMBER関数の戻り値がFALSEになってしまっています。

ISNUMBER関数の戻り値がおかしくなる原因

何故このようなことが起きるかというと、上記画像のA1セルには「文字列型で1」が格納されています。

ISNUMBER関数は、数値型の1の場合にしかTRUEを返却してくれないようなので、セルを文字列にした場合はFALSEになってしまいます。

ちなみに問題が発生するのは、数値の戦闘にシングルクォーテーションを付けて文字列型のセルにした場合のみです。セルの書式で文字列型にした場合には、正常に返却されます。

(同じ文字列型に変更する方法なのに何故だろう…)

とりあえず、セルの型も考慮して数値であるかどうかを判別したい場合は、ISNUMBERを使用する形で問題ないと思います。

しかし、セルの型を考慮せずに、純粋に値が数値であるかどうかを判別したいときには望んでいない結果となります。


解決方法:ISERROR関数とVALUE関数を組み合わせる

純粋に値が数値であるかどうかを判別したい時ですが、以下の式で対応することができました。

=NOT(ISERROR(VALUE(A1)))

VALUE関数は、セルに入力されている文字列を数値として返却してくれる関数になります。

この時、数値に変換できない文字であった場合にはエラーが発生します。

ISERROR関数はエラーが発生しているかどうかを検査する関数で、エラーが発生していたらTRUEを返します。

よって

  • 数値→VALUE関数で変換可能→ISERROR関数でFALSEとなる
  • 数値以外→VALUE関数で変換不可(エラー)→ISERROR関数でTRUEとなる

という動きになり、数値かどうかを判別することができます。

※ISNUMBER関数と判定結果が同じになるように、NOTをつけて反転させています。


6 件のコメント

  • どうも参考になりました。文字数字昆合の文字列は処置が難しいです。
    抽出した数字は文字列となりましたが最後にvalue関数で数値になりました。

  • おお、確かに抽出すると文字列になりますね。
    抽出して、更に抽出したものがちゃんと数値かどうか調べるのは以下になる感じですかね。
    抽出方法自体よくわからなかったので調べてみたら勉強になりました。

    =NOT(ISERROR(VALUE(TEXTJOIN(“”,TRUE,IF(ISERROR(VALUE(MID(A1,SEQUENCE(LEN(A1)),1))),””,MID(A1,SEQUENCE(LEN(A1)),1))))))
    ※A1が混合文字列の場合

  • 記事中で紹介された式
    =NOT(ISERROR(VALUE(A1)))

    ですが、空白セルを参照した場合にISNUMBER関数と結果が異なります(ISNUMBER関数はFALSE、紹介された式はTRUEを返します)。
    空白セルの場合にFALSEを返すようにしたい場合は以下の式のようにCONCATENATE関数で参照セルの値を文字列化した方が良いです。

    =NOT(ISERROR(VALUE(CONCATENATE(A1))))

    • ご指摘ありがとうございます。
      記載いただいた式を用いたほうが良さそうですね。
      確か空白セルに対して数値評価を行うと0で評価されるので、VALUE関数も数値の0を返却してそうです。

    • おー、なるほど実際に掛け算してみれば文字列はエラーになるのでその方法はスマートですね。
      後記事の方でも考慮不足だったんですが空のセルは0で評価されてしまうので、そこも補正するなら「=IF(A1<>“”,IFERROR(A1*1,””),””)」ですかね。

  • コメントを残す

    メールアドレスが公開されることはありません。

    日本語が含まれない投稿は無視されますのでご注意ください。(スパム対策)