時系列で自在にデータ集計するならに日付計算を正しく知ろう

売上明細や仕訳明細などデータを活用し、毎月の月次資料や仕訳処理、また四半期や年間での集計業務を行う際に必要な、エクセルでの日付の扱いについて深掘り解説します。

データ集計に便利なピボットテーブルには、グループ化という機能もありますが、正直実務で使えない事が多いです。

ピボットテーブルのグループ化を使い四半期での集計を行うと、下図のように1月始まりになってしまい、12月決算の企業以外は使う事ができません。

したがって、時系列での集計を自在に行うには、エクセルの日付計算を正確に理解する事が重要になります。

エクセルセミナー

シリアル値が意味不明?

Excelにおける日付は『シリアル値』で処理されているのですが、日付計算が苦手な方は、このシリアル値が何なのかを理解できていません。

では、まずエクセルでの日付計算について、ヘルプの引用してみます。

Excel では、日付を連続したシリアル値として処理することで、日付の計算が行われています。
既定では、1900 年 1 月 1 日がシリアル値 1 として保存されます。
2008 年 1 月 1 日は 1900 年 1 月 1 日から 39,448 日後に当たるので、シリアル値は 39,448 になります。
Macintosh 版 Excel では、標準として異なる日付システムが使用されます。

これは、シリアル値はただの連番で、数値の1が1900年1月1日になると言っているのですが、この文章を読むだけではなかなか理解しづらいですし、じゃあ実際にどう使うの?という事もわかりませんね。

シリアル値ってただの連番です

それでは、実際にシリアル値と日付がどのような関係になっているのか、説明していきたいと思います。

1900年1月1日に対応するシリアル値が1で、1900年1月2日が2というように、1日増える毎に、シリアル値が1増えていきます。

シリアル値というと小難しく感じますが、実際はただの連番なんですよ。

1899/12/31以前は日付として扱われません

ヘルプに記載されている1900/1/1より前の日付はどうなるのでしょうか?

日付として扱う事ができませんのでご注意下さい。
1899/12/31と入力しても文字として扱われますし、数式で求めようとすると下図のようにエラーになってしまいます。

約300万日後まで日付計算できる

逆にいつまで日付として扱えるのかも検証してみました。シリアル値が100万でも日付として計算しますね。

Excel2013ですと、9999/12/31まで日付として扱えます。
シリアル値にすると2,958,465です。
約300万日後まで計算できるので、地球滅亡の日を計算するような事をしない限り、未来の日付については心配は無さそうですね!

日付は書式設定の違いだけです

では、シリアル値の1と、数値の1の違いって何かあるのでしょうか・・・。
結論を言うとセルの値に違いはなく、日付と数値の違いは、単純にセルの書式設定の違いだけなのです。

下図を御覧ください。

B14、B16、B18のセルには、1という数値を入力していますが、表示結果が異なっています。
これは書式設定(表示形式)を変えているからです。
同じ値を入力しても、セルの書式設定で、日付で表示されたり、入力値の通り表示されたりする事が理解できるでしょうか。

2008/1/1のように日付形式でセルに値を入力すると

  1. 2008/1/1という日付形式の値を入力する。
  2. 裏でExcelが39448というシリアル値に変換する。
  3. Excel上では39448というシリアル値で処理される。
  4. 書式が標準の場合、Excelが気を利かせて日付形式の書式にしている。

という事をエクセルが行ってくれています。
Excelが自動で行ってしまうので、楽な反面わかりづらくなっている部分もありますね。

Excelでは2008/1/1と入力しても、39448というシリアル値に変換されてしてしまいます。
なので見方を変えると、Excelでは2008/1/1と入力した場合と、39448と入力した場合ではセルの値に違いは無く、日付は単に書式の違いだけという事になります。
従って、Excelで日付を扱うには、書式設定を使いこなすという点と、シリアル値をどう扱うかというのが重要になってきます。

今日の日付を扱うには

ショートカットキーで簡単入力

Excelでデータ管理を行う場合、今日の日付を入力する機会が多くなりますので、ショートカットキーを利用すると効率的です。
ショートカットキーは、CTRL + ;(セミコロン)になります。

TODAY関数で常に今日の日付を表示する

また、常に今日の日付を表示したい場合ははTODAY関数を使います。引数が無いので誰でも簡単に使えます。
TODAY関数を使うと、期限までの残日数を表示させるような使い方もできます。

シリアル値が何なのかをしっかり理解していると、期限から今日の日付を引くと、残日数が求められると言うことも、すぐに理解できるようになります。

DATE関数を使って日付(シリアル値)を求める

DATE関数は引数で年月日を指定すればシリアル値に変換してくれます。

上図18行目の2014年13月32日というのは、現実にはあり得ない日付なのですが、
DATE関数を使うと、13月を翌年1月に、32日を翌月1日(1月なので)と、超えた分を加味して正しい結果を返してくれます。

エクセルでカレンダーを作る時などに便利ですので活用してみて下さい。

和暦より西暦を使いましょう

DATE関数では、年の指定は必ず西暦にする必要があります。(和暦では計算できません。)
和暦の場合、元号が変わる事もあり、また海外とのやり取りも考えると、エクセルでは西暦での利用をお勧めします。

月末日を求めるには?

DATE関数を使い、月末日を求める小技も紹介しておきます。

2017年4月の月末日を求めたいなら、
=DATE(2017,5,0)というように、翌月の0日と指定すると、月末日を求める事ができます。

うるう年なども気にする必要がなく、月末日を求める事ができます。

月間の集計には日付から月を数値で抜きだす

例えば、6月分のデータ集計を行いたい場合、売上日の6月1日から6月30日までの期間を集計する・・・という事はせずに、MONTH関数を使い、売上日(シリアル値)から月を抜きだし、月が6のものだけ集計する、というように使います。

曜日を表示させてみよう

日付から曜日を表示させるには2つの方法があります。

日付が入力されているセルに曜日を表示させるには?

日付が入力セルと同じセルに曜日を表示させるには、セルの書式設定(表示形式)を使います。
D10:D16まで範囲指定しCTRL+1(セルの書式設定のショートカットキーです)を押します。

表示形式のタブからユーザー定義を選択し、種類に書式を入力します。

今回は6/9(月)のような表示形式にするので、種類に、m/d(aaa) と入力します。
サンプルで表示結果を確認しながら設定して下さい。

ちなみに、aaaが日本語で曜日を表示させる設定になります。見やすくするために括弧を入れ、(aaa)としています。

集計にも使える!曜日を別セルに表示させるには?

TEXT関数は、数値に指定した書式を設定し、文字列に変換した結果を返す関数になります。

C29のセルに、=TEXT(B29,"aaa")という数式を入力していますが、これは、B29の日付(シリアル値)に、aaaと曜日の書式設定をし、文字列に変換した値を求める、という意味になります。

シリアル値ではなく、文字列に変換する事で、集計がしやすくなります。

書式設定とTEXT関数どちらで曜日を表示させる?

どちらを使っても構わない場合が多いですが、一つのセルに日付と曜日を表示させたいのであればセルの書式設定(表示形式)で対応します。
曜日毎に集計したい場合などは、シリアル値では集計できないので、TEXT関数を使って文字列に変換しましょう。
どちらでも構わない場合、私はどちらかと言うと文字列に変更してしまう派です。

書式設定で、和暦、西暦の表示を変えてみよう。

日付は書式設定を変更する事によって、和暦、西暦の表記や、桁揃えなどの見た目を整える事ができます。

書式設定の方法は、
CTR+1を押し、表示形式のタブから、ユーザー定義を選択し、種類に直接入力する形になります。

西暦年はy、和暦年はeになる点に注意して下さい。
月日を2桁で揃えるなら、mm/ddと記述します。(06/09のように月日が2桁で揃います)
/や年月日の漢字など、表示形式定に関係の無い文字は、そのまま表示されます。

和暦表示なら、gee/mm/ddやggge年m月d日は良く使うので暗記してしまうと良いでしょう。

TEXT関数の引数としても使う事が多いので、ユーザー定義書式は覚えておきましょう。

年月での集計はTEXT関数で集計キーを作ろう!

ピボットテーブルのフループ化では、1月始まりになるため12月決算の企業以外は使えません。

思い通り集計ができない場合は、集計キーを自力で作れるようになると、色々応用がききます。
例えば、年月で集計するなら、TEXT関数を使い日付から必要な部分を(年月日から年月だけを)文字に変換し、集計するための文字データ(集計キー)を作ります。

下図の26行目では、2013/2/1という日付(シリアル値)から、TEXT関数を使って2013/02という集計キーを作っています。

作成した集計キーを使うと、ピボットテーブルでも4月始まりに対応できるようになります。

※ SUMIF関数やSUMIFS関数を使った集計にも便利です。

最後までお読みいただきありがとうございました。
エクセルでの日付の扱いについてご理解いただけたでしょうか。
この日付計算については、当社のエクセル研修でも時間を割いて行っている重要な部分になりますので、参考になれば幸いです。

業務改善サポート