日付をさまざまな値に変換する「TEXT関数」の威力

本稿は、著者自身がかつて運営していた『エク達』というウェブサイトに掲載した、「TEXT関数」を使った文字列操作の基本形について触れた記事を転載したものです。昨日の『日付データからさまざまな情報を抽出するTEXT関数』ともあわせてぜひともご参照ください。

エク達は、「エクセル評論家」でもある新宿会計士が、エクセルなどの「オフィス系の汎用ソフト」を使い、誰でも気軽に、かつ安価に、とても迅速かつ正確に仕事をこなす、ビジネスマンとしての基本テクニックを研究するサイトである。

エクセルを使いこなすには、基本的な関数をいくつか組み合わせることが重要である。そして、実務的に重要な関数というものは、意外とガイドブックには載っていないものだ。こうした「実務的に大変重要な関数」のひとつが、「TEXT関数」である。

日付のシリアル値をさまざまに変換

日付を「曜日」、「元号(和暦)」、「西暦」などに表示変更するテクニックをまとめておこう。2021年8月24日、すなわち「シリアル値44432」を例にとって、さまざまな書式変換を行った結果は、次のとおりだ(図表1)。

図表1 セルの書式(日付、シリアル値44432の場合)
フォーマット表示備考
y21西暦下2桁
yy21西暦下2桁
yyy2021西暦4桁
yyyy2021西暦4桁
gR元号のアルファベット
gg元号の漢字1字
ggg令和元号の漢字2字表示
e3元号の数値
ee03元号の数値2桁表示
m8月の数値
mm08月の数値2桁表示
mmmAug月の英語名称3桁表示
mmmmAugust月の英語名称
mmmmmA月の英語名称1桁表示
d24日の数値
dd24日の数値2桁表示
dddTue曜日の英語名称3桁
ddddTuesday曜日の英語名称
aaa曜日の漢字1字
aaaa火曜日曜日の漢字表示

こうしたシリアル値を、「セルの書式設定」などを使ってエクセルに表示させてみたものが、図表2である。

図表2 シリアル値→さまざまなフォーマットに変換

ちなみに、これらについて表示させるためには、「セルの書式設定」で次のように設定すれば良い。

  • フォーマット①→yyyy/m/d
  • フォーマット②→yyyy年m月d日
  • フォーマット③→ge/m/d
  • フォーマット④→aaaa

TEXT関数の活用

ただし、昨日の『日付データからさまざまな情報を抽出するTEXT関数』で示したやり方は、「セルの書式設定」で変更しているに過ぎず、「セルの値」自体を変更したわけではない。やはり作業をしていると、何らかのニーズに基づき、セルの値自体を変更したいという需要が生じて来るものである。

そこで、今回紹介するのは、「text関数」である。

書式は、次のとおりだ。

=TEXT(値,表示形式)

表示形式の部分にはセルを指定してやっても良いし、図表1に示したフォーマットを打ち込んでやっても良い。ただし、直接、文字列を打ち込む場合には、「””」(ダブルクオーテーション)で囲む必要がある。

さきほどの図表2のシートのF2セルに、こんな数式を入れてみよう。

=TEXT(A2,”yyyy/m/d”)

A2は日付型に変換すれば「2021/8/24」と表示される、エクセル上は「シリアル値」として認識することが可能な値を入力している(図表3)。

図表3 計算式の入力

その結果は、図表4のとおりだ。

図表4 計算結果

この「=TEXT(A2,”yyyy/m/d”)」の「yyyy/m/d」を変更してみると、さまざまな値を作ることができる。たとえば “aaa” を入力(図表5)した結果が図表6のとおりだ。

図表5 計算式の入力

図表6 計算結果

参考:いろいろとやってみた

ちなみに、本稿のオリジナル記事の執筆日、すなわち「2021/08/25」を入力したセルに、図表1で示したフォーマットを掛け合わせて計算した結果が、図表7だ。

図表7 計算結果

是非、いろいろと試してみてほしい。

エク達は、「エクセル評論家」でもある新宿会計士が、エクセルなどの「オフィス系の汎用ソフト」を使い、誰でも気軽に、かつ安価に、とても迅速かつ正確に仕事をこなす、ビジネスマンとしての基本テクニックを研究するサイトである。

読者コメント一覧

  1. Sky より:

    ノウハウ開示ありがとうございます。TEXT 関数以前に、フォーマットでgとかeとかaとかがあるのは知らなかったです。特にgは驚きです。こんなローカライズもしていたんですね。凄いぞマイクロソフト。昼休みに遊んでみよう。

※【重要】ご注意:他サイトの文章の転載は可能な限りお控えください。

やむを得ず他サイトの文章を引用する場合、引用率(引用する文字数の元サイトの文字数に対する比率)は10%以下にしてください。著作権侵害コメントにつきましては、発見次第、削除します。

※【重要】ご注意:人格攻撃等に関するコメントは禁止です。

当ウェブサイトのポリシーのページなどに再三示していますが、基本的に第三者の人格等を攻撃するようなコメントについては書き込まないでください。今後は警告なしに削除します。なお、コメントにつきましては、これらの注意点を踏まえたうえで、ご自由になさってください。また、コメントにあたって、メールアドレス、URLの入力は必要ありません(メールアドレスは開示されません)。ブログ、ツイッターアカウントなどをお持ちの方は、該当するURLを記載するなど、宣伝にもご活用ください。なお、原則として頂いたコメントには個別に返信いたしませんが、必ず目を通しておりますし、本文で取り上げることもございます。是非、お気軽なコメントを賜りますと幸いです。

コメントを残す

メールアドレスが公開されることはありません。