セルに入力された値をExcel VBA IsNumeric関数と×1で数値判定する

ExcelのTips
この記事は約15分で読めます。

「ExcelのTips」ではExcelを使用していた気付いたことを取り上げて行きます。

今回はVBAでのIsNumeric関数と×1の掛け算を使用して、セルに入力された値が「混じりっけのある数値」ではないことを判定する方法についてご紹介いたします。

なお「混じりっけのある数値」には「数値が文字列として保存されている」場合も含まれていますのでお含み置きいただければ幸いです。

まずはその前にワークシートで数値と文字を決められた並びで入力した時に自動変換する機能について確認して置きたく存じます。

Excel 2016バージョン2010(ビルド 13328.20292)を使用しています。

スポンサーリンク

セル入力時の自動変換機能

ご存じのようにExcelではセルに数値と文字を定められた組み合わせで入力すると自動変換する機能が備わっています。

では「どのような組み合わせがあるのか?」なのですが下記のグレープシティー株式会社Excel業務カイゼンブログのページで記載されていましたので、そちらをご参照いただければ幸いです。

このようにExcelのワークシートには入力されている値を自動変換する仕組みが働くのですが、実はVBAにおいても内容は異なりますが計算時に自動変換する機能が働きます。

VBAで計算する時に働く自動変換機能

左図のA列が実際の入力値ですが、数値と文字が組み合わさって入力されています。

このA列の値をVBAで読み込んで、その値に×1の掛け算をした時の結果をD列に表示させています。

もちろん左図に示しているのは一例でしかありませんが、「入力値が×1の掛け算により自動変換されている」機能が働いている事が解ります。

5・6行目は同じ数字が返っているので分かり難いのですが、「数値が文字列として保存されている」ケースになります。

B列の値は次のセル関数で表示させています。

=CELL("format",A2)
'
'

A列の該当行に設定されているセル書式を書式コードで返します。

「G」は標準、「D9」は時刻、「.0-」は数値の書式設定を表しています。

先頭にアポストロフィー(‘)を付けないとセルに入力値をセットできない場合があり、付けているものはC列に(‘)を表示しています。

なおエラーインジケーターは「文字列に数値と判断できる値が入力されている」ことを示していますが、入力値にそれが付いている場合、VBAでは数値で扱われるものと文字列と認識されるものがあることが分かります。

「文字列で認識」されたものは、その旨をD列に表記しています。

具体的には後述いたしますが、上記の17個の入力値は「文字列で認識」された2個を除き、残りすべてIsNumeric関数での判定はTrueが返ってきます。

入力値に文字が含まれているように見えるのですが、なぜTrueが返るのでしょうか?

なぜVBAのIsNumeric関数は「混じりっけのある数値」にTrueを返すのか?

上記のようにExcelでは基本的に入力された数値と文字の組み合わせによっては自動変換する機能が働いています。

それを踏まえたうえで、MicrosoftドキュメントのIsNumeric関数の注釈に書かれた内容を見ていただきたいのですが、実際の記述内容を下記に引用いたします。

・IsNumeric は、expression 全体が数値として認識される場合は True を返し、それ以外の場合は False を返します。
・IsNumeric は、式が日付式の場合は、False を返します。

「数値が文字列として保存されている」ケースであれば、この内容は理解できるのですが、そうでない場合は理解に苦しみます。

注釈の内容に従うのであれば、先の章の自動変換事例の1つの「\1.,100」は「全体が数値として認識」されていると解釈すべきなのですが、しゃくし定規に文字面だけを見るとそれがとても正しい記述とは思えません。

ただしこれを自動変換により置き換えられた後の数値「1.1」だと仮定するのであれば納得がいきます。

以上の事から『IsNUmeric関数は自動変換後の数値を使って判定しているために「混じりっけのある数値」にTrueを返しているのではないか?』という仮説が成り立ちます。

検証結果から見ると「仮説ではない」と思えるのですが、注釈ではこのことに何も触れられていないので「仮説」とせざるを得ない認識です。

「混じりっけのある数値」を自動変換されては困る場合の対処法

しかしこの「自動変換後の数値で判定される」ことが往々にして有り難くないケースがあります。

例えば「\1.,100」にしても「1.1」というよりかは「\1,100」の方が本来入力したかった値である可能性が高いのではないでしょうか?

将来的にはAI技術により前後のセルや誤入力パターンの解析からもう少し精度の高い変換ができるようになるものと思いますが、現時点では「混ざりっけのある数値」は人が見て判断する必要がありそうです。

そのために、「混じりっけのある数値」はTrueで通すのではなく、Falseで除外して欲しいのですがIsNUmeric関数ではそのようなオプションは提供されていません。

そこでIsNUmeric関数がTrueを返した場合は、「どんな数値を見て判定したのか?」を調べるために、元の値に「×1」を掛けて自動変換させた後、元の値との間に違いが生じていないかを確認することで「混じりっけのある数値」だったかどうかの判断をすることができます。

ただその場合、「数値が文字列として保存されている」ケースではどうなるのでしょうか?

「1を掛けても同じ値が返るので判断できないのでは?」と思われるかもしれません。

でも大丈夫です。「”12345″×1」をすると文字列が数値に自動変換されて「12345」(数値)になりますので、元の値である「”12345″」(文字列)とは違いが生じていると判断されます。

対処法に基づいたVBAコーディング

これを実装すためのVBAコーディングはとても簡単です。なお下記コーディングでは条件文ごとに該当した値をイミディエイトウィンドウに出力する際のフォーマットを設定しているために行数が増えていますことをお含み置きください。

Option Explicit
Sub NumericCheck()
 Dim rw As Long, cl As Long, cnt As Integer: cnt = 0
 Dim cladrs As String, frmt As String, NowCell As Range
 Const separator As String = "~"
    ActiveSheet.UsedRange.Select
    For rw = Selection(1).Row To Selection(Selection.Count).Row
        cl = Selection(1).Column
        Set NowCell = Cells(rw, cl)        '【※】処理対象セルをオブジェクト変数にセット
        cladrs = NowCell.Address(False, False)
        If IsNumeric(NowCell.Value) = False Then
            cnt = cnt + 1
            Debug.Print Format(cnt, "000") & "-1 " & separator & cladrs & separator & NowCell.Value & separator
        ElseIf NowCell.Value <> NowCell.Value * 1 Then
            cnt = cnt + 1
            Debug.Print Format(cnt, "000") & "-2 " & separator & cladrs & separator & NowCell.Value & separator & NowCell.Value * 1
        ElseIf NowCell.Value <> "" And InStr(NowCell.NumberFormatLocal, "mm") <> 0 Then
            cnt = cnt + 1: frmt = Format(NowCell.Value, "Short Time")
            Debug.Print Format(cnt, "000") & "-3 " & separator & cladrs & separator & frmt & separator & NowCell.Value * 1
        ElseIf NowCell.Value <> "" Then
            cnt = cnt + 1
            Debug.Print Format(cnt, "000") & "-4 " & separator & cladrs & separator & NowCell.Value & separator & NowCell.Value * 1
        End If
    Next rw
    ActiveSheet.Range("A1").Select
End Sub

このコーディングはワークシート上で入力された最初の列に入力されているセルの値を順番に読み込んですべてを判定してイミディエイトウィンドウに出力します。

判定するIF文は11行、14行、17行、20行目で条件判定していますが、14行目が本題の「混じりっけのある数値」を抽出するための条件文になります。

条件文

If NowCell.Value <> NowCell.Value * 1 Then

※もっともまず最初に11行目のIsNumeric関数でFalseが返る入力値を除外した後に、14行目のELSEIF条件文を実行することが絶対条件です。
※従いまして11行目の条件文に入った時のイミディエイトウィンドウへの出力には「計算後の値」はありません。

17行目の条件文はセルの書式設定が時刻になっている場合の処理になりますが、イミディエイトウィンドウに出力する際のフォーマットを”Short Time”に決め打ちしているので、入力値の見え方が実際と異なっているかもしれませんが、お含み置きいただければ幸いです。

なおこの時刻判定は標準で組み込まれている表示形式のみを使用している場合に有効な判定式になりますのでご注意ください。

最後の20行目はIsNumeric関数はセルが空文字でもTrueで返るので、それは出力しないようにするために設定しています。

【補足】「日付」・「時刻」は数値であると見なす場合

「日付」も「時刻」もセルの書式設定の中の「表示形式」によって「日付」や「時刻」の形で表現されているたけで、データ的には整数と小数点以下の数値なのだから数値に含めた方が良いという考え方もある事と存じます。

その場合はお手数ですが、17行目から19行目を削除した上で、11行目の条件文を下記に書き換えていだければ幸いです。

        If IsNumeric(NowCell.Value) = False Then
            cnt = cnt + 1
            If IsDate(NowCell.Value) = True And Left(Evaluate("Cell(""Format""," & NowCell.Address & ")"), 1) = "D" Then
                Debug.Print Format(cnt, "000") & "-4 " & separator & cladrs & separator & NowCell.Value & separator & NowCell.Value * 1
            Else
                Debug.Print Format(cnt, "000") & "-1 " & separator & cladrs & separator & NowCell.Value & separator
            End If

前章のVBAコーディングを実行した結果

「VBAで計算する時の自動変換機能」の章で使用した入力値を上記VBAコーディングでイミディエイトウィンドウ出力した結果をB列(赤枠)に貼り付けてあります。

出力フォーマットは下記になります。

連番-判定番号~セル番号~入力値~計算後の値

判定番号は各条件文ごとの連番です。

5・6行目の「数値が文字列として保存されている」ケースでも前章の「条件文」に該当して判定番号「2」が返ってきています。

13行目の「$」は書式設定の記号に設定されているも「見た目上のもの、つまり入力値には含まれていないもの」なので、上記コーディングでは抽出できていません。データそのものは数値でしかなので、悪しからずご了承ください。

IsNumericの代わりにワークシート関数IsNumberを使用した場合

Excelワークシート関数の中に、IsNumericと同じように値が数値を参照しているか判断してくれるIsNumber関数があります。

実は先の章でご紹介したVBAコーディングの11行目を次の条件式に置き換えることで同じように動かすことができます。

なおIsNumberは空文字をFalseと判定するために、11行目には含めないように空文字を除外する条件を追加しています。

'    以前省略
        If NowCell.Value <> "" and WorksheetFunction.IsNumber(NowCell.Value) = False Then
'    以降省略

IsNumberに変えた状態で実際に動かしていただくと分かるのですが、この場合「混じりっけのある数値」を判定する14行目のELSEIF条件文に該当する入力値はゼロとなります。

14行目に入って来ていた入力値はすべて上記11行目のIF文に吸収されてしまいます。従って14行目の条件文は外してしまっても問題がありません。

それであれば、「IsNumericではなくIsNumberにすれば良いのでは?」と思われるかもしれません。

ただIsNumberをVBAで使用する際には大きな弱点があって、それは「セル書式に通貨型が設定されている場合はスルーする」という点です。

つまり、通貨型のセルの場合は、そこに数値が入っていようが、「混じりっけのある数値」が入っていようがすべて「数値ではない」Falseを返してしまいます。

左図でC列がIsNumberでの実行結果、D列はIsNumericでの実行結果になります。

新たに19行から22行に通貨型と日付型を追加しています。

  • 前章で確認した18行目までのD列で判定番号「2」の値はC列ではすべて「1」に変わっていますが、それ以外は時刻も含めて同じ結果です。
  • 追加した通貨型はIsNumberではスルーされていますが、IsNumericでは数値の判定です。
  • 日付型両者ともに文字列の判定です。

ワークシート関数IsNumberはなぜ「混じりっけのある数値」をはじくのか?

IsNumeric関数の時と同様にMicrosoftドキュメントで「WorksheetFunction メソッドのIsNumberの注釈に書かれた内容を見ていただきたいのですが、実際の記述内容を下記に引用いたします。

IS 関数に対する引数は自動的に変換されません。 たとえば数値が必要なその他のほとんどの関数では、テキスト値19が数値19に変換されます。 ただし、数式ISNUMBER("19")では、19はテキスト値から変換されず、 IsNumber関数はFalseを返します。

上記でIS関数と表現されているもの一覧は、「IS関数」のドキュメントに記載されていますが、IsNumberの他には「IsBlank・IsError・IsNA・IsNonText・IsText」などの関数が含まれています。

微妙な英文和訳であるために上記注釈で黄色いアンダーラインを引いた箇所は、「たとえば」の前と後に一見真逆の事が書かれているように読めますが、「ただし」以降を含めた全体で解釈すると次のような趣旨になる認識です。

『IS関数は引数の自動変換をしないけれど、「数値が文字列として保存されている」場合はIsNumber関数を除いて数値に変換する』

これをIsNumber関数だけの表現に置き換えると「IsNumber関数はいかなる引数も自動変換しない」となります。

従いまして「数値が文字列として保存されている」ケースでもFalseが返ることになります。

ところで、今まで見てきたようにIsNumber関数は「混じりっけのある数値」をFalseにするという検証結果がでています。

そのため当たり前の事ではありますが、これまでの検証結果と合わせると『自動変換されなければ「数値が文字列として保存されている」ケースも含めて「混じりっけのある数値」は数値ではない』という推論が成り立ちます。

そしてこの推論を裏返すと『「混じりっけのある数値」が数値であるためには、自動変換されている必要がある』というまさに仮説を裏付けるような論式が見えてきます。

最後に

あまり「憶測でものを言うべきではない」と思いますが、先の章のVBAコーディングで実行した結果の入力値を見ていただきたいのですが、下記のようにワークシートとVBAコーディングで「ちぐはぐな状態」になっていることが解ります。

  • 7行目の「\1.,100」はワークシート上ではエラーインジケータが付いていないので文字列判断ですが、方やVBAコーディング結果では数値とみなされ「1.1」に自動変換されています。
  • 8行目の「%5」はワークシート上ではエラーインジケータが付いて「数値が文字列として保存されている」という判断ですが、方やVBAコーディング結果では文字列として判断されています。

上記の1列だけでは何とも言い難いのですが、ただし「ワークシートとVBAコーディングで動きに差がある」ことは事実であり、注意が必要な認識です。

従ってもしも仮にVBAコーディンクで自動変換する必要がある場合は、『「混じりっけのある数値」であり、かつ、エラーインジケータを取得する関数がTrueの場合』に条件を絞れば、自動変換した時の影響は少ないものと思われます。

ただし2行目の「1e4」は「10000」に自動変換されることになりますが…

If NowCell.Errors.Item(xlNumberAsText).Value = True Then
'
'NowCellはRange変数

自動変換機能は使う側のハードルを下げる意味では必要な機能であるとは思いますが、「過ぎたるは猶及ばざるが如し」ということわざがあるように「やりすぎるのも良くない」事と存じます。

可能であれば自動変換の「判定仕様の公開」なり「オプション設定」など、今後何かしらの対応がなされることを期待いたしますが、今までがそうであったように、これから先もそのような対応はなされないのかもしれません…

以上最後までご一読いただき誠にありがとうございました。

ExcelのTips
スポンサーリンク
スポンサーリンク
シェアする
∞ワークスKenをフォローする
∞ワークス