Excel業務カイゼンブログ

Excelは表示形式を変更することで、様々な表示をすることができます。この表示形式を関数でも変更することができます。関数はTEXT関数です。TEXT関数の使い方と、TEXT関数のメリットについて解説します。実践するファイルがあります。こちらからダウンロードしてください。

TEXT関数

TEXT関数の書式は次の通りです。
=TEXT(データ,表示形式)
表示形式は、セルの書式設定のユーザー定義の表示形式と同じ書式を使います。表示形式を直接入力する場合は" "で囲みます。セルA1に表示形式を小数点以下2桁の％表示にする場合は、
=TEXT(A1,"0.00%")
となります。

TEXT関数の実践

ダウンロードしたファイル「TEXT関数」シートをご覧ください。

セルA1からA4までに様々なデータが入力されています。では、早速、B列にそれぞれをTEXT関数で処理した結果を表示してみましょう。

数値の整数化

セルB1には整数部分だけの表示形式のデータに変換する次の計算式を入力します。
=TEXT(A1,"0")

日付の書式

セルB2には年月日を/で区切った表示形式のデータに変換する次の計算式を入力します。
=TEXT(A2,"yyyy/mm/dd")

金額の書式

セルB3には金額形式の表示形式のデータに変換する次の計算式を入力します。
=TEXT(A3,"金#,##0円也")

この書式は請求書などに活用できます。

曜日に変換

TEXT関数の有効な利用方法の一つは曜日への変換です。セルB4に曜日形式の表示形式のデータに変換する次の計算式を入力します。
=TEXT(A4,"aaaa")

もしTEXT関数を使わなければ

&の文字列演算子で文字列やセル内のデータを結合した文字列を作成することができます。例えばセルF2に
="販売日は"&A2&"です。"
とすれば、「販売日は6月7日です。」という文字列になることが期待できます。では、下図のように、C1からC5の範囲に数式を設定してみましょう。

セルC1：="答えは"&A1&"です。"
セルC2：="販売日は"&A2&"です。"
セルC3：="金額は"&A3&"です。"
セルC4：="曜日は"&A4&"です。"
セルC5:=A5&"÷"&B5&"="&A5/B5
と入力しましたが、結果は下図の通り想定したものとは違いました。

特に注目すべきは日付が顕著に想定していない形になっています。日付の正体は、1900年1月1日を1とした日数の「日付シリアル値」が正体で、計算式上はこの値を表示します。数値データも同様で、セルC5の計算結果も、小数点以下２桁くらいでいいと思っていたところが、「10÷3＝3.33333333333333」という多い桁数で表示されます。TEXT関数を使わない場合はこのようなことが起き、セルの参照が2つ以上ある場合はどんなにセルの書式設定で設定しても対応できない場合もあります。上記の10÷3の例でもそうなのですが、例えば「6月7日：気温25℃・湿度70%」のような表示の場合は、「6月7日」と「25」と「70」の3つの値があるため、１つのセルに対する１つの書式設定では対応できません。この場合にTEXT関数が有用なのです。D１からD5の範囲にTEXT関数を組み合わせた数式を設定してみましょう。

セルD1：="答えは"&TEXT(A1,"0")&" です。"
セルD2：="販売日は"&TEXT(A2,"yyyy/mm/dd")&"です。"
セルD3：="金額は"&TEXT(A3,"金#,##0円也")&"です。"
セルD4：="曜日は"&TEXT(A4,"aaaa")&"です。"
セルD5：=A5&"÷"&B5&"＝"&TEXT(A5/B5,"0.00")

これで想定通りのデータになります。

TEXT関数の結果は文字列

TEXT関数の結果は文字列データになります。セルB7にセルA7の値を整数表示する次の計算式を入力しましょう。
=TEXT(A7,"0")

答えが123となりますが、文字の配置が左側になっています。これはデータが文字列になっている証拠です。セルC7に１を足して、セルB7に結果を表示してみましょう。
=B7+1

すると、結果が124になります。文字列であっても数値として計算できるものは自動でExcelは計算してくれます。しかし、VLOOKUP関数など、一部の関数では、文字列データと数値データが厳密に区分けされているので注意が必要です。

A11からB14の範囲に商品データが入力されており、A列の数値は数値データです。セルC8にセルB7のTEXT関数の結果をA11からA14の範囲から検索するVLOOKUP関数を入力してみましょう。
=VLOOKUP(B7,A11:B14,2,0)

結果、検索値を見つけることができず#N/Aエラーとなりました。TEXT関数の結果である文字列では、数値データと一致しないと判断されているのです。解決するにはTEXT関数の結果のB7を数値データに変換するVALUE関数が必要になりますので、最終的に次のような計算式になります。セルC9に入力してみましょう。
=VLOOKUP(VALUE(B7),A11:B14,2,0)

きちんと計算できました。

まとめ

TEXT関数は、様々なシーンで有効活用できます。例えば、毎日日報をメールで提出する際、文章を毎回入力しているのでは効率が悪いです。そこで、必要項目のみを入力すると自動で文章が出来上がるようにしておき、それをメール画面へコピペするだけで日報が出来上がるというようにしておけば、効率がよいでしょう。日報の例は、「日報への応用」シートに作成しています。セルC2に氏名、C6からG10に作業の内容を記入すると、セルB12に文面が出来上がります。セルC12の数式を確認してみましょう。計算や関数の結果の書式にTEXT関数を使って整えているいるところに注目です。