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

当サイトは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

「=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)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

 

8.ROUND関数とTEXT関数の違い

(1)ROUNDは数値、TEXTは文字列

問題

セルA1に0.56789と入力した。TEXT関数を用いて小数第2位までの表示にしなさい。また、ROUND関数を用いた場合との違いを述べなさい。

f:id:waenavi:20191029113556j:plain

 

解説

「=TEXT(A1,"0.00")」と入力します。TEXT関数を用いた場合、表示形式のルールにより端数は四捨五入になります。0.57となり、左揃えになります。

f:id:waenavi:20200318093858j:plain

 

ところで、ROUND関数も四捨五入をする関数です(参考:【Excel】ROUND関数の桁数は「小数点の位置」を0として考えると分かりやすい)。桁数は2です。0.57となり、右揃えになります。

f:id:waenavi:20200318094253j:plain

 

このようにTEXTは文字列を返し、ROUNDは数値を返します。もちろん1をかけることによってTEXTも数値にすることはできますが、単に四捨五入をするだけであれば、ROUNDを使うべきです。

f:id:waenavi:20200318094425j:plain

 

(2)四捨五入をして0も表示する

問題

セルA1に0.5697と入力した。TEXT関数を用いて小数第3位までの表示にしなさい。また、ROUND関数を用いた場合との違いを述べなさい。

f:id:waenavi:20200318100128j:plain

 

解説

「=TEXT(A1,"0.000")」と入力します。0.570となります。文字列なので最後の0も表示されます。

f:id:waenavi:20200318100004j:plain

 

ROUND関数の場合、桁数は3です。数値の場合、最後の0は表示されないので桁数=3を指定しても、3桁表示されないことがあります。

f:id:waenavi:20200318100049j:plain

 

「小数点以下の表示桁数を増やす」ボタンで0を表示します。

f:id:waenavi:20200318100303j:plain

 

9.文字列と連結するときの注意点

(1)パーセンテージの連結

問題

セルA1に56.97%と入力した。これを用いて「この商品の占める割合は56.97%であった。」と表示しなさい。

f:id:waenavi:20200318100555j:plain

 

解説

文字列連結の計算式を入力したとします(参考:【Excel】文字列結合、スペース・改行・ダブルクォーテーションの連結)。文字列連結をすると、56.97%の元の数値である「0.5697」になってしまいます。

  • ="この商品の占める割合は"&A1&"であった。"

f:id:waenavi:20200318100755j:plain

 

パーセンテージの表示形式を保ちながら文字列連結をするには、TEXT関数を使います。

  • ="この商品の占める割合は"&TEXT(A1,"0.00%")&"であった。"

f:id:waenavi:20200318101639j:plain

 

(2)四捨五入をし、文字列を連結して0も表示する

問題

セルA1に56.97%と入力した。小数第2位を四捨五入して、「この商品の占める割合は57.0%であった。」と表示しなさい。

f:id:waenavi:20200318100555j:plain

 

解説

TEXT関数で連結します。

  • ="この商品の占める割合は"&TEXT(A1,"0.0%")&"であった。"

f:id:waenavi:20200318101924j:plain

 

ちなみに、ROUND関数を用いると最後の0は表示されません。

  • ="この商品の占める割合は"&ROUND(A1*100,1)&"%であった。"

f:id:waenavi:20200318102050j:plain

 

10.IF関数で場合分けをする

問題

TEXT関数を用いて、A列の数値が1未満の場合は小数第3位まで、1以上の場合は小数第2位までの表示にしなさい。

f:id:waenavi:20200318094852j:plain

 

解説

元の値の大きさによって表示形式を変える場合はIF関数を使います。

  • =IF(A1<1,TEXT(A1,"0.000"),TEXT(A1,"0.00"))

f:id:waenavi:20200318095154j:plain

 

別解

書式記号を変えるだけであれば、TEXT関数の中にIFを入れてもよいです。

  • =TEXT(A1,IF(A1<1,"0.000","0.00"))

f:id:waenavi:20200318095337j:plain

 

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

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

 


解説は以上です。


 

 


Copyright(C)2018-2021 waenavi, All rights reserved. [www.waenavi.com 定礎 平成三十年八月]
Youtube | 公式サイト | twitter | 著作権 | 運営者情報 | お問い合わせ