わえなび ワード&エクセル問題集

当サイトはWordとExcelの実力を鍛える最高水準の特訓問題集です。リンクを張るのは自由です。みんなで勉強しましょう!

【Excel】TEXT関数で数値を文字列に変換するときの注意点と練習問題

ExcelのTEXT関数は、数値に表示形式を適用して文字列に変換する関数です。表示形式は書式記号で表します。

ところで、セルの書式設定で表示形式を変えることができるのに、わざわざTEXT関数を使って表示形式を変えるだけであれば全く意味がありません。TEXT関数は、数式の一部として利用したり、別の関数の中に入れて入れ子にしたりします。

今回は、TEXT関数の使い方、文字列への変換、数値への変換、日付や時刻との関係などについて出題します。

 

 

目次

0.表示形式の書式記号まとめ(復習)

書式記号についてはこちらの記事をご覧ください。

 

1.TEXT関数の基本

(1)表示形式を適用する

問題

A列に7桁以内の整数を入力した。B列に、TEXT関数を用いて「000-0000」の表示形式の文字列に変換しなさい。

f:id:waenavi:20191029104255j:plain

 

解説

TEXT関数は、数値に表示形式を適用して、その結果を文字列として返します。表示形式を指定するにはユーザー定義表示形式の書式記号を使います(上記記事参照)。ダブルクォーテーションで囲みます。

  • =TEXT(数値,"表示形式の書式記号")

「=text(a1,"000-0000")」と入力します。「0」は数字1桁を表し、桁数が足りない場合は0で埋めるという意味です。3桁目と4桁目の間にハイフンを入れます。

f:id:waenavi:20191029105118j:plain

 

TEXT関数挿入の画面を用いてもかまいません。

f:id:waenavi:20191029105145j:plain

 

7桁の郵便番号の形式になります。

f:id:waenavi:20191029105214j:plain

 

(2)セルの書式設定とTEXT関数の違い

問題

A列の表示形式を「000-0000」にしなさい。また、セルの書式設定によって表示形式を設定するのと、TEXT関数を表示形式を適用するのでは何が違うか述べなさい。

f:id:waenavi:20191029104255j:plain

 

解説

A列を選択します。

f:id:waenavi:20191029105239j:plain

 

セルの書式設定、表示形式のユーザー定義で「000-0000」にします。

f:id:waenavi:20191029105306j:plain

 

これで、A列の表示形式が「000-0000」となり、7桁の郵便番号の形式になります。

f:id:waenavi:20191029105332j:plain

 

A列とB列は全く同じに見えますが、まったく違うものです。A列はセルの値を変えることなく、表示形式を変えています。数式バーには元の数値が表示されています。

f:id:waenavi:20191029105410j:plain

 

表示形式を「標準」にすると元の数値に戻ります。

f:id:waenavi:20191029105437j:plain

 

B列は「標準」でこの状態なので、他の表示形式に変えても表示は変わりません。TEXT関数を使うとその計算結果は「文字列」なので表示形式は適用されません。

f:id:waenavi:20191029105621j:plain

 

A列には数値が保存されています。10倍すると、10倍した値が出ます。

f:id:waenavi:20191029110147j:plain

 

一方、B列を10倍するとエラーです。TEXT関数は文字列なので掛け算することができません。

f:id:waenavi:20191029110223j:plain

 

後ろに「円」を連結します(参考:【Excel】文字列結合、スペース・改行・ダブルクォーテーションの連結)。A列のほうは元の数値に「円」が付きます。掛け算にしても、文字列連結にしてもその計算で利用される値は元の数値であり、表示形式は無関係です。

f:id:waenavi:20191029110336j:plain

 

一方、B列に「円」を連結すると、郵便番号の後に円がつきます。TEXT関数は元の数値の情報を持たないので、表示形式で変換された後の文字列が演算に使われます。

f:id:waenavi:20191029110505j:plain

 

2.換算や文字列削除はできない

問題

TEXT関数を用いてmをcmに変換することは可能か。また、TEXT関数を用いて「円」を削除することは可能か。

f:id:waenavi:20191029111105j:plain

 

解説

表示形式は、セルの値を変えずにセルの表示を変えるものであって、原則として元のデータの大きさを変えるものではありません。したがって、TEXT関数のみで元の値を変えることはできません。

mをcmに換算するには100倍すればよく、cmをつけたければ「&"cm"」とすればよいです。このように、桁数を増やす、減らす、換算するといった掛け算・割り算でできる計算をTEXT関数でやろうとしてはいけません

f:id:waenavi:20191029111349j:plain

 

ただし、換算したものに対して、TEXT関数を用いることは可能です。例えば、100倍してから「0m00cm」の表示形式にしたい場合は、「=TEXT(A1*100,"0!m00c!m")」とすればよいです(!については後述)。

f:id:waenavi:20191029111508j:plain

 

また、TEXT関数で指定できるセルは「数値」であり、文字列を指定することはできません。したがって、「円」を削除することはできません。この場合はLEFT関数で「=LEFT(C1,LEN(C1)-1)」とするべきです(参考:Excelで特定の文字を検索して文字列の一部を抽出する【LEN、FIND、LEFT、RIGHT、MID】)。

f:id:waenavi:20191029111615j:plain

 

この他、全角半角の変換(JIS関数、ASC関数)、大文字小文字の変換(UPPER関数、LOWER関数)、セル内改行の追加(CHAR(10)の連結)などの文字列操作は、それぞれ文字列操作関数や演算子を用いるべきであり、TEXT関数を使ってはいけません。

 

3.TEXT関数の文字列を数値に変換する

問題

(1)数式「=TEXT(123,"000")」で得られる123と、数値の123は同じか。
(2)「=TEXT(123,"000")+TEXT(123,"000")」を計算することは可能か。 

解説

123と入力します。

f:id:waenavi:20191029111854j:plain

 

「=TEXT(A1,"000")」と入力します。123を3桁の表示「000」にするだけなので123のままです。見た目は同じですが左揃えになります。

f:id:waenavi:20191029111929j:plain

 

イコールで比較するとFALSEになります。TEXT関数で得られる答えは文字列であり、数値の123とは異なるものです。

f:id:waenavi:20191029112000j:plain

 

1を掛けます。1を掛けると通常はエラーになりますが、数値に変換できるものは数値化されます。

  • =TEXT(A1,"000")*1

f:id:waenavi:20191029112032j:plain

 

TEXT関数の文字列を数値化するには次のような方法があります。

  • 1をかける TEXT*1
  • 0をたす TEXT+0
  • VALUE関数で囲む VALUE(TEXT)

「=TEXT(123,"000")+TEXT(123,"000")」と入力します。246になります。

f:id:waenavi:20191029112313j:plain

 

通常は、文字列同士の演算をすることはできませんが、数値化できる文字列は、その直前か直後に四則演算(足し算引き算掛け算割り算)があれば数値とみなされるため、計算をすることができます。

*補足*

数値と文字列の違い、返還方法の基本についてはこちらの記事をご覧ください。

 

4.カンマ桁区切り、指数表示

問題

セルA1に12345と入力した。TEXT関数を用いて「残り12,345円となります。」「1.23E+04」という形式にしなさい。

f:id:waenavi:20191029112427j:plain

 

解説

カンマ桁区切りにします。

f:id:waenavi:20191029112510j:plain

 

これに「円」を連結しても「12,345円」にはなりません。参照元のセルに表示形式を設定しても、それを参照した数式にその表示形式は反映されません。

f:id:waenavi:20191029112535j:plain

 

カンマ区切りの表示形式の記号は「#,##0」です。「="残り"&TEXT(A1,"#,##0")&"円となります。"」とします。これで「残り12,345円となります。」となります。

f:id:waenavi:20191029112633j:plain

 

なお、日本語の部分をTEXT関数の中に入れて「=TEXT(A1,"残り#,##0円となります!。")」としてもかまいません。この場合は&は不要です。

f:id:waenavi:20191029112749j:plain

 

また、本来なら「"""残り""#,##0""円となります。"""」とダブルクォーテーション2つで囲むべきですが、「"残り#,##0円となります!。"」で良いです。

f:id:waenavi:20191029112918j:plain

 

小数点第2位までの指数表示の記号は「0.00E+00」です(参考:【Excel】なぜ指数の表示形式「E」が表示されるのか、「E」の設定と解除の方法)。「=TEXT(A1,"0.00E+00")」とします。これで「1.23E+04」となります。

f:id:waenavi:20191029113024j:plain

 

5.スラッシュで1文字ずつ区切る

問題

セルA1に12345と入力した。TEXT関数を用いて「1/2/3/4/5」という形式にしなさい。

f:id:waenavi:20191029112427j:plain

 

解説

「=TEXT(A1,"0/0/0/0/0")」と入力します。これはエラーです。

f:id:waenavi:20191029113204j:plain

 

スラッシュは分数の表示形式の記号として使われるものなので、先頭に半角の!をつけるかダブルクォーテーションで囲むことが必要です。「=TEXT(A1,"0!/0!/0!/0!/0")」とします。

f:id:waenavi:20191029113304j:plain

 

別解

REPT関数とLEN関数を使う場合は、「=TEXT(A1,REPT("0!/",LEN(A1)-1)&"0")」となります。これで文字数に関係なくスラッシュで区切ることができます。

f:id:waenavi:20191029113331j:plain

 

一般に「X」で区切る場合は、「=TEXT(文字列,REPT("0!X",LEN(文字列)-1)&"0")」とします。なお、!が不要な場合もあります。

f:id:waenavi:20191029113440j:plain

 

6.パーセントと数値化

問題

セルA1に0.56789と入力した。TEXT関数を用いて、パーセンテージ小数第1位までの表示にしなさい。また、これを数値化するといくつになるか。

f:id:waenavi:20191029113556j:plain

 

解説

「=TEXT(A1,"0.0%")」と入力します。56.8%となります。

f:id:waenavi:20191029113620j:plain

 

1を掛けます。「0.568」となります。元の値は0.56789ですが、TEXT関数によって0.568となります(四捨五入になる)。このようにTEXT関数を用いた後で数値化すると、元の値と異なることがあります。

f:id:waenavi:20191029113723j:plain

 

7.日付・時刻の変換

(1)年・月・日を求める

問題

セルA1に「2023/4/5」と入力した。TEXT関数を用いて年・月・日の部分を取り出しなさい。また、「2023年4月5日」にしなさい。

f:id:waenavi:20191029113819j:plain

 

解説

年・月・日はYEAR、MONTH、DAY関数を使って求めますが、TEXT関数を使って求めることもできます。「=TEXT(A1,"yyyy")」「=TEXT(A1,"m")」「=TEXT(A1,"d")」と入力します。それぞれ2023、4、5となります。

f:id:waenavi:20191029113959j:plain

 

これらをくっつけて「2023年4月5日」とするには「=TEXT(A1,"yyyy年m月d日")」とします。

f:id:waenavi:20191029114039j:plain

 

別解

表示形式の書式記号を別のセルに入力して、それを参照することもできます。

f:id:waenavi:20191029114213j:plain

 

(2)曜日を求める

問題

セルA1に「2023/4/5」と入力した。TEXT関数を用いて曜日を求めなさい。

f:id:waenavi:20191029113819j:plain

 

解説

「=TEXT(A1,"aaa")」と入力します。水曜日となります。TEXT関数を用いた時点でシリアル値ではなくなるので、日付としての計算はできなくなります(参考:【Excel】日付の「シリアル値」を本気で理解するには何を練習するべきか?)。

f:id:waenavi:20191029114527j:plain

 

(3)日付・時刻を数値に変換する

問題

セルA1に「2023/4/5」と入力した。TEXT関数を用いて8桁の数「20230405」にしなさい。また、セルB1に「12:34」と入力した。TEXT関数を用いて4桁の数「1234」にしなさい。

f:id:waenavi:20191029114615j:plain

 

解説

年月日はyyyymmddです。「=TEXT(A1,"yyyymmdd")」と入力します。これで8桁の数になります。

f:id:waenavi:20191029115934j:plain

 

数値化する場合は1を掛けます。

  • =TEXT(A1,"yyyymmdd")*1

f:id:waenavi:20191029120143j:plain

 

*補足*1をかけたときに日付の表示形式が適用されて########となることがあります。

f:id:waenavi:20191029120213j:plain

 

表示形式を「標準」にすれば数値になります。

f:id:waenavi:20191029120342j:plain

 

時間・分はhhmmです。「=TEXT(B1,"hhmm")*1」と入力します。表示形式を「標準」にすれば4桁の数になります。

f:id:waenavi:20191029120434j:plain

 

 

(4)8桁の数値を日付に変換する

問題

セルA1に8桁の数「20230405」と入力した。TEXT関数を用いて文字列「2023年04月05日」にしなさい。また、シリアル値に変換して「2023/4/5」と表示しなさい。

f:id:waenavi:20191029120651j:plain

 

解説

「2023年04月05日」とするには「=TEXT(A1,"#年00月00日")」とします。

f:id:waenavi:20191029120726j:plain

 

スラッシュで区切ります。「=TEXT(A1,"#!/00!/00")」と入力します。スラッシュで区切られましたが、これは文字列であってシリアル値(整数値)ではありません。

f:id:waenavi:20191029120810j:plain

 

そこで1を掛けて数値化します。「45021」となります。スラッシュで区切られた文字列に1を掛けると数値化され、日付のシリアル値になります。

  • =TEXT(A1,"#!/00!/00")*1

f:id:waenavi:20191029120855j:plain

 

表示形式を日付にすると「2023/4/5」となります。

f:id:waenavi:20191029120925j:plain

 

別解

日付のシリアル値を求めるのであればスラッシュではなくハイフンでもよいです。「=TEXT(A1,"#-00-00")」と入力します。

*補足*半角のスラッシュは直前に!が必要ですが、半角のハイフンは不要です。

f:id:waenavi:20191029121738j:plain

 

ハイフンで区切られた文字列に1を掛けて数値化すると、日付のシリアル値になります。

  • =TEXT(A1,"#-00-00")*1

f:id:waenavi:20191029121849j:plain

 

表示形式を日付にします。

f:id:waenavi:20191029121853j:plain

 

(5)4桁の数値を時刻に変換する

問題

セルA1に4桁の数「1234」と入力した。TEXT関数を用いてシリアル値に変換して「12:34」と表示しなさい。

f:id:waenavi:20191029122022j:plain

 

解説

「=TEXT(A1,"#!:00")」と入力します。「12:34」となりますが、これは文字列であってシリアル値(小数値)ではありません。

f:id:waenavi:20191029122059j:plain

 

そこで1を掛けて数値化します。「0.523611」となります。コロンで区切られた文字列に1を掛けると数値化され、時刻のシリアル値になります。

  • =TEXT(A1,"#!:00")*1

f:id:waenavi:20191029122157j:plain

 

表示形式を時刻にすると「12:34:00」となります。

f:id:waenavi:20191029122159j:plain

 

(6)14桁の数値を日付+時刻に変換する

問題

セルA1に14桁の数「20230405123456」と入力した。TEXT関数を用いてシリアル値に変換して「2023/4/5 12:34:56」と表示しなさい。

f:id:waenavi:20191029122322j:plain

 

解説

20230405123456と入力します。13桁以上の数値は指数表示になります。

f:id:waenavi:20191029122340j:plain

 

表示形式を数値にします。

f:id:waenavi:20191029122430j:plain

 

「=TEXT(A23,"#-00-00 00!:00!:00")」と入力します。日付はハイフンでつなぎ、時刻はコロンでつなぎます。日付と時刻の間には半角のスペースが必要です。

f:id:waenavi:20191029122558j:plain

 

1を掛けて数値化します。「45021.52」となります。

f:id:waenavi:20191029122617j:plain

 

表示形式を「yyyy/m/d h:mm:ss」にすると「2023/4/5 12:34:56」となります。

f:id:waenavi:20191029122653j:plain

 

8.和暦

(1)和暦を西暦に変換する

問題

セルA1に「H300303」と入力した。TEXT関数を用いて、2018/3/3(H30.3.3)のシリアル値にしなさい。

f:id:waenavi:20191029122846j:plain

 

解説

「H300303」は文字列なのでこのままではTEXT関数を使うことができません。先頭の文字と数字に分けて考えます。右の数字は「=RIGHT(A1,6)」です(参考:【Excel】LEFT関数、RIGHT関数、MID関数で文字列を抽出する方法の基本例題)。

f:id:waenavi:20191029123224j:plain

 

ハイフンで分割します。「30-03-03」となります。

  • =TEXT(RIGHT(A1,6),"#-00-00")

f:id:waenavi:20191029123307j:plain

 

ところで、TEXT関数の第1引数は数値でなければなりませんが、RIGHT関数の答えは文字列です。しかし、数値化できる文字列は1を掛けなくてもTEXT関数の第1引数として使用することができ、自動的に数値化されます。したがって、1を掛ける必要はありません。

f:id:waenavi:20191029123516j:plain

 

先頭の文字は「LEFT(A1)」です。

  • =LEFT(A1)&TEXT(RIGHT(A1,6),"#-00-00")

f:id:waenavi:20191029123656j:plain

 

これは文字列であり、シリアル値ではありません。全体に1を掛けます。

  • =(LEFT(A1)&TEXT(RIGHT(A1,6),"#-00-00"))*1

f:id:waenavi:20191029123745j:plain

 

日付の表示形式にします。

f:id:waenavi:20191029123834j:plain

 

TEXT関数で「#-00-00」の形式に変換して、1を掛けることでシリアル値にすることができます。この他、「平成30年03月03日」のような日本語も1を掛けることでシリアル値にすることができます。

f:id:waenavi:20191029124316j:plain

 

(2)TEXT関数の入れ子

問題

セルA1に「20180303」と入力した。TEXT関数を用いて「H300303」にしなさい。

f:id:waenavi:20191029124428j:plain

 

解説

いったんハイフンで区切ります。

  • =TEXT(A1,"#-00-00")

f:id:waenavi:20191029124531j:plain

 

7桁の和暦の表示形式は「geemmdd」です。TEXT関数の第1引数に入れることによってシリアル値になり、geemmddによって7桁の和暦の形式になります。

  • =TEXT(TEXT(A1,"#-00-00"),"geemmdd")

f:id:waenavi:20191029124641j:plain

*補足*日付の形式で1をかけるとシリアル値になる文字列は、1をかけなくてもTEXT関数の第1引数にすることができます。

 

(3)元号の判定

問題

A列の日付が平成ならば〇をつけなさい。

f:id:waenavi:20191029124931j:plain

 

解説

「=TEXT(A1,"g")」と入力します。「g」は元号の書式記号です。昭和はS、平成はH、令和はRになります。

f:id:waenavi:20191029125026j:plain

 

これを利用してIF関数で元号の判定をすることができます。平成の日付だけ〇になります。

  • =IF(TEXT(A1,"g")="H","〇","")

f:id:waenavi:20191029125126j:plain

 

(4)元年の判定

問題

A列の日付を「平成e年m月d日」の表示形式にしなさい。ただし1年の場合は元年とする

f:id:waenavi:20191029132649j:plain

 

解説

「=TEXT(A1,"ggge年m月d日")」と入力します。元年が1年になります。表示形式だけで元年の表示にすることはできません。

f:id:waenavi:20191029132718j:plain

 

「=TEXT(A1,"e")」は和暦にした時の年になります。

f:id:waenavi:20191029132849j:plain

 

これが1の時は「元年」とします。1を引いて0になれば元年です。

  • =TEXT(A1,"ggg"&IF(TEXT(A1,"e")-1,"e","元")&"年m月d日")

f:id:waenavi:20191029132823j:plain

 

9.セミコロン

(1)0以上とマイナス

問題

A列のうちプラスまたはゼロの場合は「A」、マイナスの場合は「B」と表示しなさい。

f:id:waenavi:20191029133135j:plain

 

解説

「=TEXT(A1,"!A;!B")」と入力します。プラスまたはゼロの場合は「A」、マイナスの場合は「B」になります。

*補足*AやBのように半角1文字の文字、記号、数字の場合、!を付けておいたほうが良いです。

f:id:waenavi:20191029133243j:plain

 

表示形式書式記号の中にセミコロンが1つある場合、セミコロンの左がプラスまたはゼロ、セミコロンの右がマイナスを表します(参考:【Excel】セルの表示形式「ユーザー定義」書式記号完全総まとめ)。

f:id:waenavi:20191029133524j:plain

 

(2)プラスとマイナスとゼロ

問題

A列のうちプラスの数の場合は先頭に+を付け、マイナスの数の場合はマイナスを▲にし、ゼロの場合は「なし」にしなさい。

f:id:waenavi:20191029133600j:plain
 

解説

「=TEXT(A1,"+0;▲0;なし")」と入力します。プラスの場合は+、マイナスの場合は▲がつき、ゼロの場合は「なし」になります。

f:id:waenavi:20191029133648j:plain

 

表示形式書式記号の中にセミコロンが2つある場合、最初がプラス、2番目がマイナス、3番目がゼロを表します。

f:id:waenavi:20191029133810j:plain

 

10.補足:色や太字の指定は不可

表示形式で[赤]のように色の指定をすることができますが、TEXT関数は文字列を返すだけなので、色の指定はできません。また、表示形式ではない書式(例:太字、フォントサイズなど)も不可です。TEXT関数ではなく、条件付き書式かマクロを使うべきでしょう。

 


解説は以上です。


 

 


Copyright(C)2018-2019 waenavi, All rights reserved. [www.waenavi.com 定礎 平成三十年八月]