【Excel】VALUE関数・TEXT関数で数値と文字列を相互変換する方法

Excelで数値データが文字列として入力されてしまい、計算や並べ替えができずに困った経験はありませんか。

または、数値を特定の書式で表示したいけれど、そのままではうまくいかないといった悩みもあるでしょう。

本記事では、ExcelのVALUE関数とTEXT関数を使って、数値と文字列を相互に変換する具体的な方法を解説します。

これらの関数を使いこなすことで、データの整合性を保ち、より柔軟なデータ処理が可能になります。

VALUE関数とTEXT関数の役割と違い

Excelでは、入力されたデータが数値なのか文字列なのかによって、その後の扱いが大きく変わります。数値として認識されているデータは、合計や平均などの計算に使えますが、文字列として認識されているデータは、計算対象になりません。VALUE関数は、この「文字列として誤認識されている数値」を、Excelが正しく数値として扱えるように変換する役割を担います。

一方、TEXT関数は、Excelが正しく数値として認識しているデータを、特定の表示形式を持つ文字列に変換します。例えば、数値を「¥1,234」という通貨形式の文字列にしたり、「2023/10/27」という日付形式の文字列にしたりする場合に使われます。これは、セルの表示形式を変更するだけでは実現できない、データそのものを文字列に変換する処理です。

つまり、VALUE関数は「文字列→数値」への変換、TEXT関数は「数値→文字列」への変換という、逆方向の役割を持っています。どちらもデータの形式を整えるために不可欠な関数と言えます。

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

VALUE関数は、主にデータ入力時のミスや、外部から取り込んだデータが意図せず文字列として扱われている場合に役立ちます。例えば、セルの左上に緑色の三角が表示されている場合、それはExcelが数値として認識できない文字列である可能性を示唆しています。VALUE関数を使うことで、これらのデータを計算可能な数値に戻すことができます。

1. 変換したい文字列が含まれるセルを選択する
   例えば、セルA1に「12345」という文字列が入っているとします。
2. 別のセルにVALUE関数を入力する
   変換結果を表示したいセル(例えばB1セル)を選択し、数式バーに「=VALUE(A1)」と入力します。
3. Enterキーを押して確定する
   B1セルに「12345」という数値が表示されます。これで、B1セルの値は計算に利用できるようになります。
4. 必要に応じてフィルハンドルでコピーする
   他のセルにも同じ変換を適用したい場合は、B1セルの右下にあるフィルハンドル(■)をドラッグしてコピーします。

TEXT関数で数値を指定書式の文字列に変換する手順

TEXT関数は、数値を単に表示するだけでなく、特定の書式を適用した文字列として扱いたい場合に便利です。例えば、レポートで「¥1,234」のように通貨記号や桁区切りを付けたい場合や、日付を「2023年10月27日」のように和暦や特定の表記で表示したい場合などに活用できます。これにより、見た目を整えるだけでなく、後続の処理で特定の文字列形式が必要な場合にも対応できます。

1. 変換したい数値が含まれるセルを選択する
   例えば、セルA1に数値「12345」が入っているとします。
2. 別のセルにTEXT関数を入力する
   変換結果を表示したいセル(例えばB1セル)を選択し、数式バーに「=TEXT(A1,”¥#,##0″)」と入力します。この例では、A1の数値を通貨形式(¥)と桁区切り(,)を付けて表示します。
3. Enterキーを押して確定する
   B1セルに「¥12,345」という文字列が表示されます。
4. 書式コードを変更して様々な変換を行う
   TEXT関数の第二引数(書式コード)を変更することで、様々な形式に変換できます。
   • 日付の場合: =TEXT(A1,”yyyy/mm/dd”) → 「2023/10/27」
   • パーセントの場合: =TEXT(A1,”0.00%”) → 「12345.00%」
   • ゼロを表示しない場合: =TEXT(A1,”#”) → 「12345」
5. 必要に応じてフィルハンドルでコピーする
   他のセルにも同じ変換を適用したい場合は、B1セルの右下にあるフィルハンドル(■)をドラッグしてコピーします。

VALUE関数とTEXT関数の組み合わせ活用例

VALUE関数とTEXT関数は、それぞれ単独でも強力ですが、組み合わせることでさらに高度なデータ処理が可能になります。例えば、外部から取り込んだデータに、数値と認識されない「2023/10/27」のような日付文字列が含まれているとします。この文字列をExcelで日付として扱いたい場合、まずVALUE関数で一時的に日付の数値表現に変換し、その後TEXT関数で希望する書式の文字列に変換するといった操作が考えられます。

また、計算結果を特定の形式で表示したいが、その結果をさらに文字列として扱いたい場合にも、これらの関数を組み合わせます。例えば、ある数値を計算し、その結果を「完了」という文字列と共に表示したい場合、計算結果をTEXT関数で文字列化してから、文字列連結演算子(&)で「完了」と結合するといった応用が可能です。

VALUE関数とTEXT関数を使う上での注意点

VALUE関数とTEXT関数は非常に便利ですが、いくつか注意しておきたい点があります。まず、VALUE関数は、変換しようとする文字列が、Excelが認識できる数値形式(整数、小数、日付、時刻など)でない場合、エラー(#VALUE!)を返します。例えば、「ABC」のような明らかに数値ではない文字列や、「1,234円」のように通貨記号や単位が混在している文字列は、そのままではVALUE関数で変換できません。このような場合は、事前に文字列操作関数(LEFT, RIGHT, MID, SUBSTITUTEなど)を使って、不要な文字を取り除いてからVALUE関数を適用する必要があります。

TEXT関数については、第二引数で指定する書式コードの記述方法に注意が必要です。Excelの表示形式でよく使われるコードが利用できますが、間違ったコードを指定すると意図しない結果になったり、エラーになったりします。特に日付や時刻の書式コードは、Excelのバージョンや地域設定によって微妙に異なる場合があるため、事前に確認しておくと良いでしょう。また、TEXT関数で変換された結果はあくまで文字列なので、変換後に再度数値として計算したい場合は、再度VALUE関数で変換し直す必要がある点も覚えておきましょう。

VALUE関数で#VALUE!エラーが出るときの対処法

VALUE関数で#VALUE!エラーが発生する場合、その原因は主に以下の2つが考えられます。

1. 変換対象が数値や日付として認識できない文字列である
   セルに「123円」や「2023年10月」のように、数値や日付以外の文字が含まれている場合、VALUE関数はエラーを返します。この場合は、SUBSTITUTE関数やREPLACE関数を使って、不要な文字を削除してからVALUE関数を適用します。例えば、セルA1に「123円」と入っている場合、「=VALUE(SUBSTITUTE(A1,”円”,””))」のように記述します。
2. 参照先のセルが空欄またはエラー値である
   VALUE関数の引数として指定したセルが空欄だったり、既にエラー値(#N/A, #DIV/0!など)を含んでいたりする場合も、#VALUE!エラーの原因になります。空欄の場合は、IFERROR関数などを使ってエラーを回避するか、IF関数で判定して処理を分岐させると良いでしょう。

TEXT関数で意図しない書式になる場合の確認点

TEXT関数で指定した書式と異なる結果が表示される場合、原因は主に書式コードの指定ミスか、Excelの地域設定との不一致が考えられます。

1. 書式コードの誤り
   例えば、日付の年を4桁で表示したいのに「yy」と指定してしまうと、2桁になってしまいます。また、通貨記号や桁区切り記号も、意図したものが正しく記述されているか確認が必要です。Excelの「セルの書式設定」ダイアログで「表示形式」タブを確認し、そこで使われているコードを参考にすると間違いが少ないでしょう。
2. 地域設定との影響
   Excelの地域設定(Windowsの地域と言語設定など)によっては、日付の区切り文字(/なのか-なのか)や、数値の桁区切り記号(,なのか.なのか)などが標準で異なる場合があります。TEXT関数の書式コードでこれらの記号を直接指定する場合、Excelの地域設定と合わせないと、意図しない表示になることがあります。例えば、日本では「yyyy/mm/dd」が一般的ですが、地域によっては「yyyy-mm-dd」が標準となる場合があります。

VALUE関数とTEXT関数の比較表

項目	VALUE関数	TEXT関数
主な目的	文字列形式の数値を数値形式に変換	数値形式のデータを指定書式の文字列形式に変換
変換方向	文字列 → 数値	数値 → 文字列
主な用途	データ入力ミス修正、外部データ取込時の数値化	レポート作成、特定の表示形式でのデータ出力
エラー発生時	#VALUE!(変換できない文字列の場合)	意図しない書式になる、エラーにならない場合が多い
書式指定	不要(Excelの標準形式に準拠)	必須(第二引数で書式コードを指定)

VALUE関数とTEXT関数は、Excelで数値と文字列のデータを扱う上で、それぞれ異なる重要な役割を果たします。VALUE関数は、誤って文字列として入力された数値を、計算や分析に使える本来の数値形式に戻すための関数です。これにより、データの整合性を保ち、正確な集計や分析が可能になります。一方、TEXT関数は、数値データを特定の書式(通貨、日付、パーセントなど)を持つ文字列に変換する際に使用します。これは、レポート作成時など、見た目を整えたい場合に特に有効です。

これらの関数を理解し、適切に使い分けることで、データ処理の幅が大きく広がります。まずは、VALUE関数で文字列の数値を数値に変換する練習、次にTEXT関数で数値を様々な書式の文字列に変換する練習をしてみましょう。さらに、これらの関数を組み合わせることで、より複雑なデータ変換にも対応できるようになります。

📊

Excelトラブル完全解決データベースこの記事以外にも、様々なエラー解決策をまとめています。困った時の逆引きに活用してください。

🏆

超解決 Excel検定 あなたのExcel実務能力を3分で測定！【1級・2級・3級】