Excel TEXT関数で日付や数値の表示形式を指定する方法

ExcelのTEXT関数で日付や数値の表示形式を指定する方法を紹介します。

Excel TEXT関数で日付や数値の表示形式を指定する方法

Excel の TEXT 関数とは、数値に書式設定を適用し、表示方法を変更することができます。該当数値に「%」を付けたり、「,(カンマ)」区切りにすることもできます。実は日付も 1900/1/1 から何日後かという数値になりますので、TEXT 関数の表示形式を変更して曜日を表示したり、AM/PMを表示することもできます。

ただし、TEXT 関数の戻り値は「文字列」です。そのため、後から計算で使うようなセルへの利用はを避けるようにしましょう。

ここでは ExcelのTEXT関数で数値や日付の表示形式を指定する方法 を紹介します。


TEXT関数の使い方

TEXT 関数の使い方は下記のようになります。

=TEXT(書式設定する値, "適用する表示形式のコード")

第一引数に対象の値もしくは対象のセルを指定し、第二引数に表示形式のコードを指定します。第二引数は必ず引用符「"(ダブルクォーテーション)」で囲みます。

戻り値は文字列です。数値じゃないことに注意して使いましょう。

金額の表示形式

数値を、金額形式の表示・・・、つまり「,(カンマ)」区切り表示するには下記のように設定します。

=TEXT(1234.56,"$#,##0.00")
=TEXT(1234.56,"金#,##0円也")

Excel TEXT関数 カンマ区切り金額表示

「#」は、1桁の数字を表示します。最後に「0」を付けることで、「数値=0」だった場合に、「0」と表示されます。円貨で利用するほとんどの場合、「"#,##0"」で十分のはずです。小数点以下がある場合にこの書式を設定すると、Excel の自動機能で四捨五入されますのでご注意ください。

日付の表示形式

「2018/7/1」を、「2018年07月01日」と表示するには下記のように設定します。

=TEXT("2018/7/1", "YYYY年MM月DD日")

Excel TEXT関数 日付の表示形式

表示形式を「"MM/DD/YY"」とすれば、海外の日付表示形式にも対応できます。

時刻の表示形式

「17:00」を、「5:00 PM」と表示するには下記のように設定します。

=TEXT("17:00","H:MM AM/PM")

Excel TEXT関数 時刻の表示形式

最近は AM/PM の表記ってあんまりしないかもだけど・・・。

曜日の表示形式


「2018/7/1」を、「日曜日」と表示するには下記のように設定します。

=TEXT("2018/7/1", "AAAA")
=TEXT("2018/7/1", "AAA")

Excel TEXT関数 曜日の表示形式

表示形式を「"DDDD"」とすれば、「Sunday」と表示されます。


その他の表示形式

使用可能な表示形式コードは、[セルの書式設定] ダイアログで確認できます。

「Ctrl + 1」を押下してセルの書式設定] ダイアログを表示し、「表示形式」タブの「ユーザー定義」から種類をコピーして TEXT 関数の第二引数に利用することもできます。「;(セミコロン)」 「@」の記号があった場合は除いてコピーしましょう。

Excel TEXT関数 その他の表示形式

TEXT関数で日付と曜日を表示する

「2018/7/1」という日付と、「17:00」という時刻から、「2018年7月1日(日) 17:00」という表示にしてみたいと思います。

TEXT 関数を使う場合って、ほとんどが数値と文字列の連結や他の関数を組み合わせての場合だと思います。

例えば、TEXT 関数を使っていない日付と時刻を「&(アンパサンド)」で結合すると下図のようになります。

Excel TEXT関数 日付と時刻をアンパサンドで結合

日付も数値なので、「2018/7/1」を表す「43282」という数値が文字列として連結されただけです。目的である「2018年7月1日(日) 17:00」という表示にはなりませんね。

そこで TEXT 関数を利用し、計算した後で連結させます。

=TEXT(A2,"YYYY年M月D日(AAA)")&" "&TEXT(B2,"H:MM")

Excel TEXT関数で日付と時刻を結合

おおお、うまくいきましたねー^^

参考サイト

まとめ

ExcelのTEXT関数で日付や数値の表示形式を指定する方法を紹介しました。

TEXT 関数は様々な用途で利用できますが、私自身が多く使うのが日付と時刻の連結や計算関連です。

勤務表などに代表される日付と時刻の入力。そこに入力しやすさから曜日を表示しているなんてのはよくある書式で、これらを連結して表示させたいニーズが結構あったりします。ある日時から50時間以内を求めてブックを開いたときにアラートを出すとか、そんなことをやって表示しようとすると TEXT 関数が必要になってきます。

ただ、TEXT 関数を使った後は文字列になります。入力や計算が必要なセルには「セルの書式設定」ダイアログから設定しておき、インプットしないセルや計算に使わないセルのみ TEXT 関数を使うようにしましょう。

おつかれさまでした。

この記事がお役に立ちましたら シェア をお願いいたします。