文字列を数値に日付をシリアル値に変換する|VALUE関数の使い方

Excel(エクセル)で文字列を数値に日付をシリアル値に変換する|VALUE関数の使い方
Excel関数

Excel(エクセル)で文字列を数値に日付をシリアル値に変換する|VALUE関数の使い方

使用関数:VALUE

VALUE(バリュー)関数とは?

ExcelでVALUEと言うとエラーの#VALUE!を考えますが、VALUE関数はエラーとは関係ありません。文字列として入力されている数値を本来の「数値」に変換する文字列操作の関数です。日付や時刻をシリアル値に戻すのにも使われます。

ExcelのVALUE関数の「VALUE」は「値」という意味です。文字列の値を数字の値に置換する機能を持った関数という意味のネーミングですね。

VALUE関数の引数と書式

「数式」タブ「関数ライブラリ」→「文字列操作」→ VALUEをクリックします。
「関数ライブラリ」→「文字列操作」→ VALUE
VALUEの引数は「文字列」だけです。

書式の構成はこうなります。
VALUE関数の書式の構成

エラーチェックで文字列を数値に変換する方法

文字列か数値かを判定する

同じ数字でも入力された値が文字列なのか数値なのかパッと見で判断できないこともあります。

Excelでは文字列はセル内で左寄せになり、数値は右寄せになります。それに加えて、数字が文字列になっているセルの左上には緑の三角マークが付いて、エラーである可能性を示しています。

緑の三角マークが付いたセルを選択すると、黄色い逆三角マークが出現します。ここにポイントすると「このセルにある数値が、テキスト形式か、またはアポストロフィで始まっています。」とポップアップされます。表示形式が文字列になっている、またはアポストロフィが最初に入力されているため文字列として認識されているという意味です。
セルの左上にある緑の三角マーク=エラーチェック

黄色い逆三角マークをクリックするとエラーの修正を促すコンテキストメニューが開きます。

数値が文字列として保存されています

エラーチェックのコンテキストメニュー
メニューの冒頭にはこう表示されており、どういうタイプのエラーかがこの一文で分かります。この場合の選択肢は2つ。「数値に変換する」と「エラーを無視する」です。

文字列のセル範囲を一括で変換する

数値に変換したいセル範囲をすべて選択して、エラーチェックの「数値に変換する」をクリックして一括で修正します。
エラーチェックの「数値に変換する」をクリックして一括で修正
文字列の数字が数値に変換されて右寄せになり、緑の三角マークが消えました。
文字列のセル範囲が一括で数値に変換された
エラーを無視する」を選択した場合は文字列のままで緑の三角マークが消えます。

VALUE関数で文字列を数値に変換する

VALUE関数の引数ダイアログを使って、文字列や空白のセルを数値に変換してみましょう。

変換された数値の入るセルを選択してVALUE関数の引数ダイアログを開き、「文字列」に最初の文字列のセルをクリックで指定します。
VALUE関数の引数ダイアログで文字列を指定

他のセルにもオートフィルコピーすると、全角数字12345は半角の「12345」に、35%は「0.35」、空白のセルは「0」が返り、2300円だけはエラー#VALUE!になりました。
VALUE関数をオートフィルコピーした結果
VALUE関数では数値を表している文字列であるとExcelが認識したものだけが変換の対象になります。「2300円」はそのまま文字列として認識されるため、数値に変換されず、エラーが返ったということです。「2300円」を数値に変換するためには末尾の「円」を消す必要があります。

文字列から数字だけを取り出す

単価の列のデータに「円」を付けてしまい、単価✕数量(=A2*B2)で価格を計算しようとするとエラーになります。数字と文字が混在する値は「数値を表していない文字列」と判定されるので足し算も引き算もできません。
数字と文字が混在するセルのデータ表
この文字列から「円」を消して数字だけを取り出しましょう。単価の右横に数値だけの単価を表示させる列を増やしておきます。
文字列から取り出した数字を表示させる列を増やす

VALUE関数とMID関数で数値を抽出

このデータでは数字は4桁のみなのでMID関数で数字部分だけ取り出し、VALUE関数で数値に置き換えます。

新しい単価の最初のセルを選択してVALUE関数の引数「文字列」にMID関数を入れ子します。
VALUE関数の引数「文字列」にMID関数を入れ子
MID関数の指定は以下のようになります。「文字列」には元の単価セルを「A2」と指定、抽出する数字の「開始位置」は文字列の最初からなので「1」と指定、抽出する数字の「文字数」は4桁なので「4」と指定します。
MID関数で文字列から数字だけを抽出する指定

MID関数で数字だけ取得し、VALUE関数で数値化することができました。単価✕数量で価格も計算できています。
VALUE関数で単価を数値化したデータ表

区切り位置指定ウィザードで数字と文字を切り分ける

関数を使わずに区切り位置の指定で数字と文字を切り分ける方法もあります。
文字列になっている単価のセル範囲をすべて選択し、「データ」タブ「データツール」の「区切り位置」ボタンをクリックします。
セル範囲をすべて選択し、「データ」タブ「データツール」→「区切り位置」をクリック
区切り位置指定ウィザードの最初の画面で「スペースによって右または左に揃えられた固定長フィールドのデータ」を選択し「次へ」
区切り位置指定ウィザード1で「スペースによって右または左に揃えられた固定長フィールドのデータ」を選択
次の画面で下部の「データのプレビュー」のメモリ上で数字と文字を切り分ける位置をクリックして次へ。抽出する数字と文字の境目に区切り線があることを確認して下さい。
区切り位置指定ウィザード2で数字と文字を切り分ける位置をクリック
最後の画面で「列のデータ形式」が「G/標準」で、「表示先」が選択したセル範囲の最初のセルであることを確認して「OK」。
区切り位置指定ウィザード3でデータ形式と表示先を確認
「既にデータがありますが、置き換えますか?」のアラートが出たらEnter。
「既にデータがありますが、置き換えますか?」のアラート
区切り位置指定ウィザードで数字と文字が切り分けられました。
区切り位置指定ウィザードで数字と文字が切り分けられた

VALUE関数で日付・時刻をシリアル値に変換する

日付や時刻の表示をVALUE関数を使ってシリアル値に戻すことができます。

VALUE関数の引数の指定は上記の文字列を数値に変換したのと同じです。数式は=VALUE(A2)のように、とてもシンプルです。
VALUE関数でシリアル値に変換する日付のセルをクリックで指定

他のセルにもオートフィルコピーすると、和暦の令和3年7月14日、西暦の2022年2月22日、時刻の15:45はシリアル値が返りましたが、時刻の午後4時5分だけはエラー#VALUE!になりました。
日付・時刻のVALUE関数をオートフィルコピーした結果
「午後4時5分」からMID関数で時刻の表記だけを抽出し、VALUE関数でシリアル値を取得しました。
MID関数で時刻の表記だけを抽出し、VALUE関数でシリアル値を取得

文字列を数値に変換する|VALUE関数 まとめ
  • 入力された数字が文字列か数値かを判定するのに、セルの左上にある緑の三角マーク(エラーチェック)が役立ちます
  • 「数値が文字列として保存されています」のエラーチェックで文字列のセル範囲を一括で数値に変換できます
  • 文字列の数字を残し、数値化したデータも取得したい場合は文字列関数のVALUE関数を使います
  • VALUE関数は日付や時刻からシリアル値を求めるのにも使われます
  • VALUE関数は「数値を表す文字列」の数値化はできますが、文字が含まれる値(2300円など)は数値化できません
  • 文字が含まれる値(2300円など)はMID関数や区切り位置指定ウィザードで数字だけを抽出してから数値に置き換えます
タイトルとURLをコピーしました