日付データからさまざまな情報を抽出するTEXT関数

エクセルを使っていると、日付を入力したつもりがいきなり数字になってしまう、といった「イライラ」もよく発生します。本稿は、『エク達』というウェブサイトにかつて掲載した『入力した日付が「数字の値」になってしまう場合の対処』、『日付から曜日を抽出する:「地味に役立つ」テクニック』という2つの記事を1つにまとめたものです。

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

再録にあたって

本稿では『エク達』というウェブサイトにかつて掲載した『入力した日付が「数字の値」になってしまう場合の対処』、『日付から曜日を抽出する:「地味に役立つ」テクニック』という2つの記事を1つにまとめています。

図表番号が重複して出てきてしまいますが、原則として修正をしていませんのでご了承ください。

入力した日付が「数字の値」になってしまう場合の対処

「あれ?日付を入力したらいきなりわけのわからない数列に変換された…!」そんな経験をされた方は多いのではないだろうか。いい加減、エクセルの使い勝手については改善していただきたいものだが、本稿以降は不定期ではあるが、「日付操作の基本」について説明していきたいと思う。

「エクセルを使いこなす」うえで重要なのは、「日付をどう使いなすか」、だ。

エクセルを立ち上げ、「日付」のつもりで、たとえば本日、「2021/8/23」と入力してみよう(図表1)。

図表1 A2セルに「2021/8/23」と入力

すると、たいていの場合は、「44431」と表示されてしまうだろう(図表2)。

図表2 A2セルに「44431」の表示

たいていの人は、これで大変に苛立つはずだ。

では、この「44431」とは、いったい何を意味しているのか。

じつは、エクセルでは日付のすべてに「シリアル値」が定められている。1901年1月1日が「1」、翌日の1902年1月2日が「2」、という具合だ。最大値は9999年12月31日で、シリアル値は「2958465」である。

ということは、エクセルでは1900年1月1日から9999年12月31日までの範囲しか、日付を正確に認識することができない、という意味でもある。

したがって、1899年12月31日(日本の場合だと明治32年12月31日)以前をエクセル上、日付として扱うことはできない(当ウェブサイトとしては、マイクロソフトに対し、たとえば「マイナスの値」を入力すればそれ以前の数値が正確に表示されるようにしていただきたいと思う次第である)。

それはともかくとして、日付を認識させるために一番手っ取り早いのは、範囲を指定して「Ctrl+1」キーを入力し、「セルの書式設定」画面を呼び出すことだ。

ここで、A2セルに「44431」、A3セルに「1」、A4セルに「2958465」と入力し、A列を選択したうえで「Ctrl+1」を入力してみよう。すると、「セルの書式設定」の画面を呼び出すことができる(図表3)。

図表3 セルの書式設定

そのうえで、セルの値を「日付」に変更してしまおう(図表4)。

図表4 セルの書式を「日付」に変更

その結果、先ほどの「44431」だの、「1」だの、「2958465」だのといった値が、日付に変更されたことが確認できるだろう(図表5)。

図表5 セルの値が日付に変わった

日付から曜日を抽出する:「地味に役立つ」テクニック

本稿では、実務上大変頻繁に使用する「日付の曜日」などのフォーマットを紹介したい。エクセルは日付を全てシリアル値で管理しているのだが、これを「セルの書式設定」だけを使って簡単に変換することができてしまうのだ。是非、身につけてほしい。

日付が数列になってしまう問題とシリアル値

『入力した日付が「数字の値」になってしまう場合の対処』では、エクセルで作業をしている際、日付を入力したら「わけのわからない数列になってしまう」という問題を紹介した。

このような問題が生じる理由は、エクセルが日付に「シリアル値」を与えている点にある。

「シリアル値」とは、「1900年1月1日」を「1」、「1900年1月2日」を「2」という具合に、日付を固有の番号で管理するという、オフィス系のソフトウェアに共通する考え方で、ほかにも Microsoft Access でもシリアル値が用いられている。

ちなみに本稿執筆日、すなわち2021年8月24日のシリアル値は「44432」であるが、これは「2021年8月24日が1990年1月1日から起算して44431日後である」という意味でもある。

そして、ユーザーの側がこの具体的な「シリアル値」を覚えておく必要は全くないが、エクセルの「癖」を知っておく上では、「エクセルが日付をシリアル値で管理している」という事実を抑えておく必要がある。

欠点としては、1899年12月31日以前の日付をエクセルに入力することができないという点が挙げられるが、長所としては、日数の計算などが容易にできる、という点が挙げられる。これについてはいずれ機会を見て、「ある日からX日後」を自動計算する、などのテクニックに落とし込んでいきたい。

サンプルシートと書式設定

さて、本稿で次に確認したいのは、実務上の優先順位が非常に高い「必須テクニック」のひとつである「曜日の抽出」だ。とくに、意外と知られていないが、「セルの書式設定」でできることはたくさんある。

ここにこんなサンプルシートを用意しよう(図表1)。

図表1 サンプルシート:シリアル値と書式の関係

2行目で言えば、左端のA2セルには日付のシリアル値「44432」、B2~E2セルにはA2セルを列絶対参照する数式「=$A2」を入れている。

また、A3セル以下、A列には「直上のセルの値を1つ減らす」という計算式を入れており(たとえばA3の場合は「=A2-1」)、A2セルを動かすだけで、すべての値を変更することができる、という仕組みだ(図表2)。

図表2 図表1の計算式

ではなぜ、B列は2021/8/24、C列は「2021年8月24日」、D列は「R3/8/24」、E列は「火曜日」、という具合に、まったく別々の表示ができているのか。

これが、書式設定である。

たとえば、B列を選んで「Ctrl+1」を押すと、「日付」の「#2012/3/14」が選択されていることがわかる(図表3)。

図表3 B列の書式設定

ただ、書式を日付に設定する場合、この「セルの書式設定」ウィンドウに準備されているものを必ずしも使う必要はない。

たとえば、C列に関しては「ユーザー設定」を選び、「種類」ウィンドウで「yyyy”年”m”月”d”日”」という書式を自身で入力している(図表4)。

図表4 C列の書式設定

「y」が西暦下2桁、「yyyy」が西暦4桁、「m」が月、「mm」だと月2桁表示(たとえば1月の場合は「01」と表示される)、「d」が日、「dd」だと日2桁表示(たとえば3日の場合は「03」と表示される)、そして「ge」が「和暦表示」を意味している(図表5

図表5 D列の書式設定

では、E列はどうなっているのか。

「aaaa」と入力すれば良い(図表6)。

図表6 E列の書式設定

セルの書式設定だけで、ここまでできる、ということだ。

日付に関連するセル書式一覧

ちなみに、日付に関連するセルの書式を一覧にしておきたい(図表7)。

図表7 セルの書式(日付、シリアル値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火曜日曜日の漢字表示

 

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

 

読者コメント一覧

  1. チキンサラダ より:

    この記事は続きがありますか?
    タイトルにある TEXT function の説明がないようなので、続編で説明されるのかな?

    いずれにせよ、日付フォーマットは意外と重要ですね。
    Google Sheets では、日付フォーマットを自由に編集して
    「発生日○月○日午後○時頃以降」などと、日付の前後や中に自由に文字列を設定することができます。それこそ、TEXT function で出来ることですが。)
    恐らく Excel でも同様の機能があると思われます。便利なのでおすすめです。

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

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

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

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

コメントを残す

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