当サイトはWordとExcelの実力を鍛える最高水準の特訓問題集です。リンクを張るのは自由です。【Excel】TEXT関数で数値を文字列に変換するときの注意点と練習問題2019-10-29【Excel】TEXT関数で数値を文字列に変換するときの注意点と練習問題ExcelExcelのTEXT関数は、数値に表示形式を適用して文字列に変換する関数です。表示形式は書式記号で表します。ところで、セルの書式設定で表示形式を変えることができるのに、わざわざTEXT関数を使って表示形式を変えるだけであれば全く意味がありません。TEXT関数は、数式の一部として利用したり、別の関数の中に入れて入れ子にしたりします。目次0.表示形式の書式記号まとめ(復習)1.TEXT関数の基本(1)表示形式を適用する(2)セルの書式設定とTEXT関数の違い2.換算や文字列削除はできない3.TEXT関数の文字列を数値に変換する4.カンマ桁区切り、指数表示5.スラッシュで1文字ずつ区切る6.パーセントと数値化7.日付・時刻の変換(1)年・月・日を求める(2)曜日を求める(3)日付・時刻を数値に変換する(4)8桁の数値を日付に変換する(5)4桁の数値を時刻に変換する(6)14桁の数値を日付+時刻に変換する8.和暦(1)和暦を西暦に変換する(2)TEXT関数の入れ子(3)元号の判定(4)元年の判定9.セミコロン(1)0以上とマイナス(2)プラスとマイナスとゼロ10.補足:色や太字の指定は不可0.表示形式の書式記号まとめ(復習)書式記号についてはこちらの記事をご覧ください。まとめ【Excel】ユーザー定義表示形式のサンプルの意味を全部解説します1.TEXT関数の基本(1)表示形式を適用する問題A列に7桁以内の整数を入力した。B列に、TEXT関数を用いて「000-0000」の表示形式の文字列に変換しなさい。解説TEXT関数は、数値に表示形式を適用して、その結果を文字列として返します。表示形式を指定するにはユーザー定義表示形式の書式記号を使います(上記記事参照)。ダブルクォーテーションで囲みます。=TEXT(数値,"表示形式の書式記号")「=text(a1,"000-0000")」と入力します。「0」は数字1桁を表し、桁数が足りない場合は0で埋めるという意味です。3桁目と4桁目の間にハイフンを入れます。TEXT関数挿入の画面を用いてもかまいません。7桁の郵便番号の形式になります。(2)セルの書式設定とTEXT関数の違い問題A列の表示形式を「000-0000」にしなさい。セルの書式設定によって表示形式を設定するのと、TEXT関数を表示形式を適用するのでは何が違うか述べなさい。解説A列を選択します。ユーザー定義で「000-0000」にします。これで、A列の表示形式が「000-0000」となり、7桁の郵便番号の形式になります。A列とB列は全く同じに見えますが、まったく違うものです。A列はセルの値を変えることなく、表示形式を変えています。数式バーには元の数値が表示されています。表示形式を「標準」にすると元の数値に戻ります。B列は「標準」でこの状態なので、他の表示形式に変えても表示は変わりません。TEXT関数を使うとその計算結果は「文字列」なので表示形式は適用されません。A列には数値が保存されています。10倍すると、10倍した値が出ます。B列を10倍するとエラーです。TEXT関数は文字列なので掛け算することができません。後ろに「円」を連結します(参考:【Excel】文字列結合、スペース・改行・ダブルクォーテーションの連結)。A列のほうは元の数値に「円」が付きます。掛け算にしても、文字列連結にしてもその計算で利用される値は元の数値であり、表示形式は無関係です。B列に「円」を連結すると、郵便番号の後に円がつきます。TEXT関数は元の数値の情報を持たないので、表示形式で変換された後の文字列が演算に使われます。2.換算や文字列削除はできない問題TEXT関数を用いてmをcmに変換することは可能か。TEXT関数を用いて「円」を削除することは可能か。表示形式は、セルの値を変えずにセルの表示を変えるものであって、原則として元のデータの大きさを変えるものではありません。したがって、TEXT関数のみで元の値を変えることはできません。mをcmに換算するには100倍すればよく、cmをつけたければ「&"cm"」とすればよいです。このように、桁数を増やす、減らす、換算するといった掛け算・割り算でできる計算をTEXT関数でやろうとしてはいけません。換算したものに対して、TEXT関数を用いることは可能です。100倍してから「0m00cm」の表示形式にしたい場合は、「=TEXT(A1*100,"0!m00c!m")」とすればよいです(!については後述)。TEXT関数で指定できるセルは「数値」であり、文字列を指定することはできません。したがって、「円」を削除することはできません。この場合はLEFT関数で「=LEFT(C1,LEN(C1)-1)」とするべきです(参考:Excelで特定の文字を検索して文字列の一部を抽出する【LEN、FIND、LEFT、RIGHT、MID】)。この他、全角半角の変換(JIS関数、ASC関数)、大文字小文字の変換(UPPER関数、LOWER関数)、セル内改行の追加(CHAR(10)の連結)などの文字列操作は、それぞれ文字列操作関数や演算子を用いるべきであり、TEXT関数を使ってはいけません。3.TEXT関数の文字列を数値に変換する問題(1)数式「=TEXT(123,"000")」で得られる123と、数値の123は同じか。(2)「=TEXT(123,"000")+TEXT(123,"000")」を計算することは可能か。解説123と入力します。「=TEXT(A1,"000")」と入力します。123を3桁の表示「000」にするだけなので123のままです。見た目は同じですが左揃えになります。イコールで比較するとFALSEになります。TEXT関数で得られる答えは文字列であり、数値の123とは異なるものです。1を掛けると通常はエラーになりますが、数値に変換できるものは数値化されます。=TEXT(A1,"000")*1TEXT関数の文字列を数値化するには次のような方法があります。VALUE(TEXT)「=TEXT(123,"000")+TEXT(123,"000")」と入力します。通常は、文字列同士の演算をすることはできませんが、数値化できる文字列は、その直前か直後に四則演算(足し算引き算掛け算割り算)があれば数値とみなされるため、計算をすることができます。*補足*数値と文字列の違い、変換方法の基本についてはこちらの記事をご覧ください。問題セルA1に12345と入力した。TEXT関数を用いて「残り12,345円となります。」「1.23E+04」という形式にしなさい。解説カンマ桁区切りにします。これに「円」を連結しても「12,345円」にはなりません。参照元のセルに表示形式を設定しても、それを参照した数式にその表示形式は反映されません。カンマ区切りの表示形式の記号は「#,##0」です。「="残り"&TEXT(A1,"#,##0")&"円となります。これで「残り12,345円となります。日本語の部分をTEXT関数の中に入れて「=TEXT(A1,"残り#,##0円となります!。")」としてもかまいません。この場合は&は不要です。本来なら「"""残り""#,##0""円となります。"""」とダブルクォーテーション2つで囲むべきですが、「"残り#,##0円となります!。小数点第2位までの指数表示の記号は「0.00E+00」です(参考:【Excel】なぜ指数の表示形式「E」が表示されるのか、「E」の設定と解除の方法)。「=TEXT(A1,"0.00E+00")」とします。これで「1.23E+04」となります。5.スラッシュで1文字ずつ区切る問題セルA1に12345と入力した。TEXT関数を用いて「1/2/3/4/5」という形式にしなさい。「=TEXT(A1,"0/0/0/0/0")」と入力します。スラッシュは分数の表示形式の記号として使われるものなので、先頭に半角の!をつけるかダブルクォーテーションで囲むことが必要です。「=TEXT(A1,"0!/0!/0!/0!/0")」とします。別解REPT関数とLEN関数を使う場合は、「=TEXT(A1,REPT("0!/",LEN(A1)-1)&"0")」となります。これで文字数に関係なくスラッシュで区切ることができます。一般に「X」で区切る場合は、「=TEXT(文字列,REPT("0!X",LEN(文字列)-1)&"0")」とします。!が不要な場合もあります。問題セルA1に0.56789と入力した。TEXT関数を用いて、パーセンテージ小数第1位までの表示にしなさい。これを数値化するといくつになるか。「=TEXT(A1,"0.0%")」と入力します。56.8%となります。「0.568」となります。元の値は0.56789ですが、TEXT関数によって0.568となります(四捨五入になる)。このようにTEXT関数を用いた後で数値化すると、元の値と異なることがあります。7.日付・時刻の変換(1)年・月・日を求める問題セルA1に「2023/4/5」と入力した。TEXT関数を用いて年・月・日の部分を取り出しなさい。YEAR、MONTH、DAY関数を使って求めますが、TEXT関数を使って求めることもできます。「=TEXT(A1,"yyyy")」「=TEXT(A1,"m")」「=TEXT(A1,"d")」と入力します。それぞれ2023、4、5となります。これらをくっつけて「2023年4月5日」とするには「=TEXT(A1,"yyyy年m月d日")」とします。表示形式の書式記号を別のセルに入力して、それを参照することもできます。(2)曜日を求める問題セルA1に「2023/4/5」と入力した。TEXT関数を用いて曜日を求めなさい。「=TEXT(A1,"aaa")」と入力します。TEXT関数を用いた時点でシリアル値ではなくなるので、日付としての計算はできなくなります(参考:【Excel】日付の「シリアル値」を本気で理解するには何を練習するべきか?)。(3)日付・時刻を数値に変換する問題セルA1に「2023/4/5」と入力した。TEXT関数を用いて8桁の数「20230405」にしなさい。セルB1に「12:34」と入力した。TEXT関数を用いて4桁の数「1234」にしなさい。年月日はyyyymmddです。「=TEXT(A1,"yyyymmdd")」と入力します。これで8桁の数になります。数値化する場合は1を掛けます。=TEXT(A1,"yyyymmdd")*1*補足*1をかけたときに日付の表示形式が適用されて########となることがあります。表示形式を「標準」にすれば数値になります。時間・分はhhmmです。「=TEXT(B1,"hhmm")*1」と入力します。表示形式を「標準」にすれば4桁の数になります。(4)8桁の数値を日付に変換する問題セルA1に8桁の数「20230405」と入力した。TEXT関数を用いて文字列「2023年04月05日」にしなさい。シリアル値に変換して「2023/4/5」と表示しなさい。「2023年04月05日」とするには「=TEXT(A1,"#年00月00日")」とします。スラッシュで区切ります。「=TEXT(A1,"#!/00!/00")」と入力します。スラッシュで区切られましたが、これは文字列であってシリアル値(整数値)ではありません。そこで1を掛けて数値化します。「45021」となります。スラッシュで区切られた文字列に1を掛けると数値化され、日付のシリアル値になります。=TEXT(A1,"#!/00!/00")*1表示形式を日付にすると「2023/4/5」となります。日付のシリアル値を求めるのであればスラッシュではなくハイフンでもよいです。「=TEXT(A1,"#-00-00")」と入力します。*補足*半角のスラッシュは直前に!が必要ですが、半角のハイフンは不要です。ハイフンで区切られた文字列に1を掛けて数値化すると、日付のシリアル値になります。=TEXT(A1,"#-00-00")*1表示形式を日付にします。(5)4桁の数値を時刻に変換する問題セルA1に4桁の数「1234」と入力した。TEXT関数を用いてシリアル値に変換して「12:34」と表示しなさい。「=TEXT(A1,"#!:00")」と入力します。「12:34」となりますが、これは文字列であってシリアル値(小数値)ではありません。そこで1を掛けて数値化します。「0.523611」となります。コロンで区切られた文字列に1を掛けると数値化され、時刻のシリアル値になります。=TEXT(A1,"#!:00")*1表示形式を時刻にすると「12:34:00」となります。(6)14桁の数値を日付+時刻に変換する問題セルA1に14桁の数「20230405123456」と入力した。12:34:56」と表示しなさい。解説20230405123456と入力します。13桁以上の数値は指数表示になります。表示形式を数値にします。00!:00!:00")」と入力します。日付はハイフンでつなぎ、時刻はコロンでつなぎます。日付と時刻の間には半角のスペースが必要です。1を掛けて数値化します。「45021.52」となります。12:34:56」となります。8.和暦(1)和暦を西暦に変換する問題セルA1に「H300303」と入力した。TEXT関数を用いて、2018/3/3(H30.3.3)のシリアル値にしなさい。「H300303」は文字列なのでこのままではTEXT関数を使うことができません。先頭の文字と数字に分けて考えます。右の数字は「=RIGHT(A1,6)」です(参考:【Excel】LEFT関数、RIGHT関数、MID関数で文字列を抽出する方法の基本例題)。ハイフンで分割します。「30-03-03」となります。=TEXT(RIGHT(A1,6),"#-00-00")ところで、TEXT関数の第1引数は数値でなければなりませんが、RIGHT関数の答えは文字列です。数値化できる文字列は1を掛けなくてもTEXT関数の第1引数として使用することができ、自動的に数値化されます。したがって、1を掛ける必要はありません。先頭の文字は「LEFT(A1)」です。=LEFT(A1)&TEXT(RIGHT(A1,6),"#-00-00")これは文字列であり、シリアル値ではありません。=(LEFT(A1)&TEXT(RIGHT(A1,6),"#-00-00"))*1日付の表示形式にします。TEXT関数で「#-00-00」の形式に変換して、1を掛けることでシリアル値にすることができます。この他、「平成30年03月03日」のような日本語も1を掛けることでシリアル値にすることができます。問題セルA1に「20180303」と入力した。TEXT関数を用いて「H300303」にしなさい。解説いったんハイフンで区切ります。=TEXT(A1,"#-00-00")7桁の和暦の表示形式は「geemmdd」です。TEXT関数の第1引数に入れることによってシリアル値になり、geemmddによって7桁の和暦の形式になります。=TEXT(TEXT(A1,"#-00-00"),"geemmdd")*補足*日付の形式で1をかけるとシリアル値になる文字列は、1をかけなくてもTEXT関数の第1引数にすることができます。問題A列の日付が平成ならば〇をつけなさい。「=TEXT(A1,"g")」と入力します。「g」は元号の書式記号です。昭和はS、平成はH、令和はRになります。これを利用してIF関数で元号の判定をすることができます。平成の日付だけ〇になります。問題A列の日付を「平成e年m月d日」の表示形式にしなさい。1年の場合は元年とする。「=TEXT(A1,"ggge年m月d日")」と入力します。元年が1年になります。表示形式だけで元年の表示にすることはできません。「=TEXT(A1,"e")」は和暦にした時の年になります。これが1の時は「元年」とします。1を引いて0になれば元年です。【令和れいわ】Excelを業務で使用している場合の新元号対応と注意点まとめ(Office2010以降)https://www.waenavi.com/entry/20190401/15540876379.セミコロン(1)0以上とマイナス問題A列のうちプラスまたはゼロの場合は「A」、マイナスの場合は「B」と表示しなさい。「=TEXT(A1,"!A;!B")」と入力します。プラスまたはゼロの場合は「A」、マイナスの場合は「B」になります。(2)プラスとマイナスとゼロ問題A列のうちプラスの数の場合は先頭に+を付け、マイナスの数の場合はマイナスを▲にし、ゼロの場合は「なし」にしなさい。「=TEXT(A1,"+0;▲0;なし")」と入力します。中にセミコロンが2つある場合、最初がプラス、2番目がマイナス、3番目がゼロを表します。表示形式で[赤]のように色の指定をすることができますが、TEXT関数は文字列を返すだけなので、色の指定はできません。表示形式ではない書式(例:太字、フォントサイズなど)も不可です。TEXT関数ではなく、条件付き書式かマクロを使うべきでしょう。わえなびチャンネルhttp://www.youtube.com/c/waenavijpチャンネル登録をすると最新の動画をチェックできます。初心者向けの動画も大量にあります。平成三十年八月]スターをつけました引用をストックしましたストック一覧を見る閉じる引用するにはまずログインしてくださいログイン閉じる引用をストックできませんでした。再度お試しください閉じる限定公開記事のため引用できません。