HOME > 即効テクニック集 > Excel VBA > その他関連のテクニック > 単位が付いた数値を計算する
その他関連のテクニック

単位が付いた数値を計算する

(Excel 97/2000/2002/2003/2007)
実務とは、教科書通りにはいかないものです。今回は、そんなお話。

セルに数値が入力されていた場合、それを計算するのは簡単です。
セルA1に「120」、セルA2に「230」が入力されていたとき、次のコードは合計をセルA3に書き込みます。

Sub Sample1()
    Range("A3") = Range("A1") + Range("A2")
End Sub
セルに入力された数値に"円"などの単位をつけたいとき、少しExcelを知っている人なら「セルの書式設定」を設定します。 表示形式で"#,##0円"のように設定すると、セルに入力された「120」は「120円」と表示されます。 "円"は表示されているだけで、セルに入力されている実体は「120」という数値です。 したがって、表示形式で"円"を表示したセルは、数値を計算するのと同じように扱えます。 では、セルに入力した人がExcelに不慣れで。表示形式という機能を知らなくて、それでも何とか"円"をつけたいと、セルに「120円」と入力してしまったらどうでしょう。 言うまでもなく「120円」は全体が文字列として扱われます。 セルB1に「120円」、セルB2に「230円」が入力されていたとき、次のコードは望まない結果になります。
Sub Sample2()
    Range("B3") = Range("B1") + Range("B2")
End Sub
セルB3には「120円230円」と文字列が結合されて入力されます。 +演算子は、データが文字列のとき、加算ではなく結合されるからです。 文字列の「120円」を数値の「120」に変換するには、末尾の"円"を消さなければなりません。 これには、いろいろな手が考えられます。 たとえば
Sub Sample3()
    Range("B3") = Left(Range("B1"), Len(Range("B1")) - 1) + _
                  Left(Range("B2"), Len(Range("B2")) - 1)
End Sub
あるいは
Sub Sample4()
    Range("B3") = Replace(Range("B1"), "円", "") + _
                  Replace(Range("B2"), "円", "")
End Sub
いずれにしても、"円"は取り除かれましたが、セルB3には「120230」という数値が入力されます。 これは、Left関数とReplace関数が「文字列を返す」ためです。 では、Left関数とReplace関数の返り値を数値に変換してはどうでしょう。
Sub Sample5()
    Range("B3") = Val(Replace(Range("B1"), "円", "")) + _
                  Val(Replace(Range("B2"), "円", ""))
End Sub
今度はうまくいきました。しかし、何とも美しくありません。 実は、このケースでは、Replace関数は必要ありません。 次のコードでも、同じようにセルB3には「250」が入力されます。
Sub Sample6()
    Range("B3") = Val(Range("B1")) + _
                  Val(Range("B2"))
End Sub
Val関数は文字列を数値に変換する関数ですが、その際、文字列の左端から「数値と認識できる部分まで」を数値に変換します。 つまり「120円」のうち、数値と認識できる"120"だけを数値に変換してくれるのです。 この動作は、結果的に右端の"円"を取り除く働きをします。 Val関数の素晴らしいところは、単位が"円"に限らないことです。 「120人」はもちろん「120回目」でも「120セット」でも「120個くらい」でも、とにかく、左から数値と認識できる部分を取り出してくれます。 さて、Val関数で解決したかに思えましたが、実はうまくいかないケースがあります。 それは「1,200円」のように桁区切りの「,」まで入力されている場合です。 カンマも"円"と同じ立派な文字ですから、Val関数は「,」の手前までしか数値に変換してくれません。 「1,200」のようにカンマだけが含まれている場合は、CCur関数を使います。 CCur関数は引数の文字列を通貨型に変換する関数で「\」や「,」の記号を除去してくれます。
Sub Sample7()
    MsgBox CCur("\1,200")
End Sub
このCCur関数を使えば「\1,200」や「\1,200,000」でも数値に変換できます。 しかし、残念ながらCCur関数は「円」などの文字には対応していません。 困りました。Val関数を使えば右端にある「円」などの単位を除去できます。 しかしVal関数は「,」に対応できません。 「,」を取り除くCCur関数は、「円」などの単位でエラーになります。 結局のところ「1,200円」のように入力されてしまった文字列を「1200」に変換するのは、一発で簡単に…という訳にはいかないようです。 しかたないので、次のような関数を自作して対応することにしましょう。
Function CleanNumber(StrNumber As String) As Long
    CleanNumber = Val(Replace(StrNumber, ",", ""))
End Function

Sub Sample8()
    Range("C3") = CleanNumber(Range("C1")) + CleanNumber(Range("C2"))
End Sub
とりあえずは正しく計算できるようです。 しかし、これで終わりではありません。セルに「1,200円」と入力してしまう人に、セルの書式設定を教えてあげなければなりません。 そうした教育の方が、Functionプロシージャを作るよりはるかに重要です。 放っておくと、その人は今後、何をしでかすか予測できないのですから。