TEXT関数の使い方:Excel関数の技

スポンサードリンク

TEXT関数の構文について　　topへ

構文：=TEXT(数値, 表示形式)
- 数値：数値、戻り値が数値となる数式、数値を含むセル参照を指定します。
- 表示形式：数値の書式を、"yyyy/m/d" や "#,##0" など、引用符で囲んだテキスト文字列として指定します。
TEXT関数は数値を表示形式を使用して文字列に変換します。
表示形式に関しては以下のページをご覧ください。
- 数値の表示形式　時刻の表示形式　日付の表示形式　表示形式(ユーザー定義)の設定方法

文字列の中で適切な表示形式で数値を表示することができます。
E1セルに　=A1&"の価格は"&TEXT(B1,"\ #,##0")&" です"　と入力した例です。
数値を桁区切りの表示形式で文字列に変換して、他の文字列と & でつなげた例です。

Excelは表示する桁数で四捨五入しますので、TEXT関数を使って四捨五入が可能です。（ROUND関数を使うのが正道です)
整数値で丸めた(四捨五入した)例です。ただし、結果は文字列ですのでご注意ください。
=TEXT(A1,"0")　で　0 は整数値で表示する表示形式ですので、小数以下1桁目を四捨五入して表示します。
文字列の数字を数値に変換するには、VALUE関数を使います。
=VALUE(TEXT(A1,"0"))
または、*1と乗算をして数値にする方法もあります。
=TEXT(A1,"0")*1

他のシステムからのデータを日付や時刻として扱いたいときに、Excelのシリアル値へ変換します。
- 日付が　20120625 と8桁の数値で表現されていたら、=TEXT(20120625,"0!/00!/00")　で日付の表示形式に変換できます。ただし、これは文字列になります。
  この文字列を日付のシリアル値に変換するには「*1」と乗算をします。
  =TEXT(20120625,"0!/00!/00")*1　とします。
同じセルでシリアル値(日付データ)にするには区切り位置の機能を利用します。
1. データ範囲を選択します。
2. [データ]タブの[区切り位置]を実行します。
3. 区切り位置指定ウィザードで[次へ]ボタンを押し3/3まで進みます。
  「日付」にチェックを入れます。右側のドロップダウンリストが「YMD」になっているのを確認します。
4. [完了]ボタンを押すと、数値データが日付データに変換されました。

表示形式は　正の数の書式;負の数の書式;ゼロの書式;文字列の書式　で表すことができます。
TEXT関数でこれを利用して、計算結果からほかの文字列を表示することもできます。
- B2-A2の結果が、正,負,ゼロ　のいずれかである時にそれに応じた文字を表示する例です。
  =TEXT(B2-A2,”増;減;－") は正の値の時は「増」、負の値の時は「減」、ゼロの時は「ー」と表示するようにしています。
表示形式では[　]角括弧を使って条件を設定することができます。
TEXT関数ではこれを利用することもできます。
- 3段階評価に利用した例です。
  =TEXT(A1,"[>75]優;[>50]良;”不可")　は75より大きい場合は「優」、50より大きい場合は「良」、それ以外は「不可」と表示します。

表示形式でローケールIDを使うことで、どこの国の言語に合わせるかを指定することができます。
下図では2行目と11行目に入力してある日付から各国の言語で曜日を表示しています。
A列に言語名を書いています。B列が使用したロケールIDです。
C3セルの数式は　=TEXT(C$2,"["&$B3&"]aaaa")　として、B列のロケールIDを使っています。
下図のC12セルの数式は　=TEXT(C$11,"["&$B12&"]aaaa")　として、B列のロケールIDを使っています。
ロケールIDの一覧はMSDNにありますので参照してください。
ロケール ID (LCID) の一覧
https://msdn.microsoft.com/ja-jp/library/cc392381.aspx
上記のID一覧表の下3ケタを　$- の後ろにつけて使います。
数値が1桁や数値だけではないケースがあったりします。
ドイツ語(ルクセンブルグ)のロケールIDは　0x1007　ですので、表示形式では　$-7 となります。
中国語(香港)のロケールIDは　0x0C04　ですので、表示形式では　$-C04 となります。