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

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

再録にあたって

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

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

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

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

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

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

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

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

図表２　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」を入力してみよう。すると、「セルの書式設定」の画面を呼び出すことができる（図表３）。

図表３　セルの書式設定

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

ここにこんなサンプルシートを用意しよう（図表１）。

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

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

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

図表２　図表１の計算式

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

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

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

図表３　B列の書式設定

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

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

図表４　C列の書式設定

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

図表５　D列の書式設定

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

「aaaa」と入力すれば良い（図表６）。

図表６　E列の書式設定

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

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

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

図表７　セルの書式（日付、シリアル値44432の場合）