Microsoft Excelでセルのデータが文字列になっていて困ったことがある方は多いのではないでしょうか。
データが文字列だと数値だと判断されず、関数や数式に使用することが出来ません。
そんな時に便利なのが、VALUE関数です。
VALUE関数は、文字列を数値に変換する関数で、文字列による数式エラーの解決に非常に役立ちます。
特に、VLOOKUP関数と組み合わせることで、VLOOKUP関数でよく発生する文字列エラーを解消することができます。
この記事では、エクセルのVALUE関数の使い方とVLOOKUP関数と組み合わる方法、その他の事例を紹介します。
VALUE関数とは、文字列を数値に変換する関数
エクセルのVALUE関数は、文字列を数値に変換する関数です。
セルに数値が入力されているのに、「文字列」であることが原因で数式が反映されないケースに役立つ関数です。
右クリックメニューの「セルの書式設定」画面を開いたことがある方は多いと思いますが、セルの表示形式には、「数値」以外にも「標準」「通貨」「日付」「文字列」「パーセンテージ」など様々あります。
セルの書式が異なるC列の各セルに「100」を入力すると、書式ごとに「¥100」や「1900/4/9」や「100％」など表示が変わります。
C列の値に対してD列にVALUE関数を使うと、パーセンテージ以外の書式は、「100」と数値で表示することができます。
このように、VALUE関数は、「文字列」の書式のみを数値に変換するのではなく、さまざまな書式のデータを数値に変換する関数です。
以降の見出しからは、VALUE関数が具体的にどんな場面で役立つかを実例と合わせながら、さらに詳しく紹介していきます。
VALUE関数を使う方法
エクセルのVALUE関数は、Microsoft365、Excel2010以降のバージョンであれば利用できます。
エクセルのVALUE関数の使い方や書式について解説します。
VALUE関数の書式と引数
VALUE関数の書式と引数は以下のとおりで、対象のセルを指定するだけのシンプルなものです。
＝VALUE(文字列)
引数「文字列」：入力必須です。数値(文字列)、日付、時刻のセルを指定します。直接文字を指定する場合は、ダブルクォーテーション(”)で囲います。(例：B2、”2024/10/8”)
数値に変換できないセルを指定した場合は、エラー値「 #VALUE!」 が表示されます。
つまり、VALUE関数の仕組みをフロー図で表すと以下となります。
VALUE関数の使い方と手順
VALUE関数を使って、文字列のセルを数値に変換する方法を解説します。
下の表のB3～B6セルに数値が入力されていますがセルの表示形式が「文字列」のため、B7セルのSUM関数の結果が「0」になっています。
この表にVALUE関数を挿入して、SUM関数の結果が正しく反映されるようにします。
手順１．VALUE関数を挿入
文字列を数値で表示したいセルに、VALUE関数を挿入します。
＝VALUE(
手順２．VALUE関数の引数を指定
数値に変換したいB3セルを指定します。セルをクリックするか、B3と入力してください。
＝VALUE(B3)
手順３．VALUE関数を確定
Enterキーを押してVALUE関数を確定すると、数値「100」が表示されます。一見、B3セルと同じように感じますが、数値セルに変換されています。
手順４．VALUE関数をコピペ
C3セルのVALUE関数をコピーし、下のセルに貼り付けすると、残りのセルも数値に変換します。
手順５．SUM関数を挿入
VALUE関数で数値に変換したセルに対してSUM関数を利用すると、合計を表示することができます。このように、VALUE関数は、文字列を数値に変換し、その結果を数式に使用するため際に利用されることが多いです。
VALUE関数でVLOOKUP関数のエラーを解消する方法
VALUE関数をVLOOKUP関数と組み合わせて利用する方法を紹介します。
下の表のF列に以下のVLOOKUP関数を挿入して、B列、C列のセル範囲から商品名を抽出しようとしています。
=VLOOKUP(E3,$B$3:$C$6,2,FALSE)
しかし、E列の書式が「文字列」で、B列の書式が「数値」と書式が異なるため、VLOOKUP関数の結果はエラー値「#N/A」が表示されます。
このような書式が異なることで発生するVLOOKUP関数のエラーを解決する際に、VALUE関数が役立ちます。
VLOOKUP関数の第１引数にVALUE関数を挿入して、E列のセルの値を文字列から数値に変換します。そうすると、VLOOKUP関数のエラーを消すことができます。
=VLOOKUP(VALUE(E3),$B$3:$C$6,2,FALSE)
逆に、E列の書式が「数値」で、B列の書式が「文字列」でVLOOKUP関数のエラーが発生する場合もあります。
その場合は、B列の右のC列にVALUE関数を挿入し、B列の文字列を数値に変換します。
そうすれば、C列の数値に対してVLOOKUP関数を利用することで、正しく商品名を表示させることができます。
=VLOOKUP(F3,$C$3:$D$6,2,FALSE)
VALUE関数の事例を４つ紹介
VALUE関数を使った事例を４つ紹介します。
VALUE関数とXLOOKUP関数を組み合わせる
上記で紹介したVLOOKUP関数の事例のように、XLOOKUP関数でも検索値とセル範囲のセルの書式が異なる場合はエラーが発生します。
下の表のE列の書式が「文字列」で、B列の書式が「数値」と書式が異なるため、XLOOKUP関数の結果はエラー値「#N/A」が表示されます。
XLOOKUP関数の第１引数にVALUE関数を挿入して、E列のセルの値を文字列から数値に変換します。そうすると、XLOOKUP関数のエラーを消すことができます。
=XLOOKUP(VALUE(E3),$B$3:$B$6,$C$3:$C$6)
XLOOKUP関数は、VLOOKUP関数の後継関数です。使い方が分からない方は、以下の記事で詳しい使い方を紹介しているので、参考にしてください。
VALUE関数とLEFT関数を組み合わせる
続いて、VALUE関数とLEFT関数を組み合わせる方法を紹介します。
LEFT関数は、文字列の左から指定した文字数分の文字を抽出する関数です。
下の表のD列にLEFT関数「＝LEFT(C3,3」を挿入し、C列の文字の先頭３文字の数値を抽出します。その値をSUM関数で合計しようとしても、LEFT関数の結果は文字列となるため、結果は0となってしまいます。
こういった場合は、VALUE関数の中にLEFT関数を挿入することで、LEFT関数の結果を直接、数値に変換することが出来きるので、SUM関数の結果も正しく表示されます。
=VALUE(LEFT(C3,3))
LEFT関数以外にも、RIGHT関数やMID関数もVALUE関数と組み合わせることができます。
以下の記事で、これらの関数の使い方を紹介しているので、参考にしてください。
VALUE関数とTEXTSPLIT関数を組み合わせる
VALUE関数とTEXTSPLIT関数を組み合わせる方法を紹介します。
TEXTSPLIT関数とは、キーとなる文字を指定して文字を分割する関数です。
例えば、TEXTSPLIT関数で「100-200」を「100」と「200」に分割しても、分割した文字は文字列に変換されるので、SUM関数などに利用できません。
こんな時、VALUE関数の中にTEXTSPLIT関数を挿入することで、分割した文字列を数値に変換することが出来ます。
=VALUE(TEXTSPLIT(B3,"-"))
TEXTSPLIT関数の使い方は以下の記事で詳しく紹介しているので、参考にしてください。
VALUE関数で先頭の「0」を削除
VALUE関数を使うことで、数値の先頭の「0」を削除することができます。
文字列ではなく数値に変換したい場合に利用します。
VALUE関数を使わず、文字列を一括で数値に変換する方法
VALUE関数を使わずに、文字列を数値に一括で変換することもできます。
下の表のB列の文字列を一括で数値に変換するため、B列の列番号を選択し、「データ」タブの「区切り位置」ボタンを選択します。
「区切り位置指定ウィザード1/3」画面が表示されるので、「コンマやタブなどの区切り文字によって・・」の形式を選択し、「完了」ボタンを押して設定を確定させます。
そうすると、B列の文字列を一括で数値に変換することができます。
本来、区切り位置コマンドは、特定の区切り文字をキーに文字を分割する機能ですが、書式を数値に変換する時にも役立ちます。
