スケテン for Excelの実例で解説、Excelの日付計算に関するTips！

ブログ（http://siland.jp/blog/）でのエントリですが、Webサイトリニューアルでブログも根本的に作り変える予定のため、アクセス数の多かった日付計算についてのエントリを移行しました。

基本的にスケテン for Excel を作るための日付計算という体で書いております。

1. EXCELでの日付の扱いについて
2. セルの表示形式による日付処理について
3. セルの表示形式の使い分け
4. DATE関数による日付計算について
5. MONTH関数を使って月を比較する
6. 曜日の表示について
7. 曜日の計算とTEXT関数について

カレンダーを作るにあたり一番必要な日付計算について書いてみようと思います。

実際セルに入力されているのは=sum(A1:A10)という数式でも、表示されるのはその結果。書式設定によって計算結果の見た目も変える事ができる。というようにEXCELはセルの実際の値と目に見える値が異なります。

スケテンでも、同じ「1日」と表示されていても、実際はシリアル値のものと、１という数値のものとがあり、両者とも異なる書式設定を行い目に見える結果を同じにしています。

Excel2003のヘルプの引用です。

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

との事ですが、言葉だけでなく実際のExcelの挙動とあわせてもう少し詳しい解説をしてみたいと思います。

まずは、B2のセルに2008/1/1と入力します。

数式バーにも2008/1/1と表示されるため、あたかも「2008/1/1」というままExcelで扱われているような錯覚を起こしてしまいますが、ヘルプに記載されているとおり、あくまで日付はシリアル値として処理されています。

それでは、シリアル値で処理されている事を検証するために、B2のセルの書式を桁区切りのスタイルに変更してみます。

すると下図のように、1900年1月1日から39,448日後に当たるというシリアル値での表示になります。数式バーに表示される値も同様にシリアル値になっています。

まとめると

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

という感じでしょうか。

Excelでは2008/1/1と入力しても、39448というシリアル値に変換してしまいます。なので見方を変えると、Excelでは2008/1/1と入力した場合と、39448と入力した場合ではセルの値に違いは無く、日付は単に書式の違いだけという事になります。

従って、Excelで日付を扱うには、書式設定を使いこなすという点と、シリアル値をどう扱うかというのが重要になってきます。

スケテンでは、2008/2/6（シリアル値で39,485）という値が入力されているセルを、日付だけ（6日）を表示するというような使い方をしています。これはセルの書式設定で行うのですが、ユーザー定義で書式設定の種類を作成してあげる必要があります。

という事で、今回はユーザー定義の書式設定を行う方法と、具体的な設定例（スケテンで使っているもの）などを解説していきたいと思います。

分かりやすいように、B2のセルに2008/2/6と入力し、B2をアクティブセルにします。

念のため前回のおさらいをしておきますが、ここでは2008/2/6と見えますが、実際のセルの値はシリアル値に変換されるので39,485になります。セルの書式が標準の場合、自動で日付形式の書式が設定されるので、2008/2/6と見えています。

続いてセルの書式設定のダイアログを開きます。

Excel2002/2003の場合

書式→セルを実行します。

Excel2007の場合

ホーム→数値グループ→ダイアログボックスランチャーをクリックします。

Excel2007は他にも書式設定のダイアログを表示する方法がありますが、ここをクリックすると「表示形式」のタブが選択された状態で開く事ができます。

ショートカットキーで行う場合

どのバージョンのExcelでも使えるので個人的には一番オススメです。

CTRLを押しながら1を押します。

セルの書式設定のダイアログが開きます。

分類を「ユーザー定義」にし、種類に表示形式を入力します。

細かい説明は抜きにして、種類にyyyy/mm/ddという表示形式を入力しOKをクリックします。（サンプルで表示結果を確認する事ができますが、アクティブセルに値が無い場合はサンプルには何も表示されません。）

2008/2/6が2008/02/06と言うように、月と日が二桁で表示される形式になりました。

表示形式をyyyy/mm/ddとした場合の解説

一つ一つ分解して解説します。

yyyyは日付を表すシリアル値より、西暦年を4桁で表示する形式です。yyと2桁にすると、西暦の下2桁で表示されます。

mmは日付を表すシリアル値より、月を2桁で表示する形式になります。2なら02となります。mとすると0で桁揃えをしません。（2→2のまま）

ddは日付を表すシリアル値より、日付を2桁で表示する形式になります。6なら06となります。dとすると0で桁揃えをしません。（6→6のまま）

yはyear、mはmonth、dはdayの頭文字なので覚えやすいですね。少し形を変えてyyyy年mm月dd日という表示形式にすると、2008年02月06日と表示されます。

再度、セルの書式設定のダイアログを開いて書式を確認してみると、年月日の漢字部分はダブルクォーテーションで括られていますが、これはExcelが勝手にやってくれるので、yyyymmddさえしっかり定義すれば後は意識する必要はありません。

それでは、最後に色々なパターンのセルの表示形式設定例を記載します。いずれも、2008/2/6と入力した場合の例です。

ざっと、こんな感じでしょうか。和暦の場合、年がeになるのが違いです。2桁で揃えたい場合はeeとなるのは同じです。

下図は、週間タイプ（month04.xls）での表示形式をまとめたものです。前述のユーザー定義の表示形式で設定します。

カレンダーの日付部はセルの値がシリアル値なので、表示形式をdとしています。

4行目に、表示したいカレンダーの開始年月日を入力できるようにしているのですが、ここの日付部分（C4）の表示形式は#日～となっています。これは入力した数字をそのまま表示したいので、#または0を指定します。#と0の違いは、ゼロを表示するかしないかになります（#記号の数より少ない場合に0は表示されません）。また、日付を2桁で表示したい場合は00とします。

同様に年と月を、それぞれ#年、#月としています。

まあ、日付部分はd日～という表示形式にしても、#日～という指定にしても同様の表示結果になるのですが、年月の部分はそうは行きません。仮に年のところをyyyy年という表示形式にした場合、2008と入力すると、「1905年」と表示されてしまいます。これは2008を1900年1月1日から2008日後（1905年6月30日になります）というシリアル値だと解釈し、その日付の年を表示しているからです。

以降にその辺の設定とその表示例を記載しますので参考にして下さい。

スケテンでは、年月日を指定する部分（入力する部分）は、#年、#月、#日という表示形式にする事で、数値だけを入力するだけで見た目ははあたかも年月日として扱われているようにしています。

入力した値をそのまま表示する。シリアル値を日付形式で表示する。実際のセルの値をきっちり把握し、適切な表示形式を設定するためには、この辺の書式設定を使いこなせるようになりましょう。

もう少しEXCELの基本的なコトの解説になります。

スケテンでは、上図のように指定した年月（日）からカレンダーを作成しています。まずは指定した年月日から対応する日付を返すDATE関数について解説します。

DATE関数について、Excel2007のヘルプを引用します。

指定された日付に対応するシリアル値を返します。関数が入力される前に、セルの表示形式が [標準] であった場合、計算結果は日付形式で表示されます。

=DATE(年,月,日)

上図の場合、B9のセルに　=DATE(A4,C4,E4)という計算式が入っています。

A4に年、C4に月、E4に日付を数値だけ入力し、DATE関数で日付に対応するシリアル値を作成している訳ですが、なぜこのような回りくどい方法をとっているのか説明していきます。

カレンダーを作る時、30日までしか無い月、31日まである月、月や年をまたぐ場合の処理、うるう年の処理、その辺をどううまくやるか・・・というのがポイントになります。

その際に便利なのがDATE関数です。以下にDATE関数で、実際にはあり得ない月日を引数にした場合の計算結果を記載します。

1番目は、2008/1/32という実際はあり得ない日付ですが、DATE関数で求めると2008/2/1という値が返ってきます。１/33日なら2/2となります。日付を加算するだけで、年月を意識することなく正しい年月日を求める事ができている訳です。

2番目と3番目は、月を加算したり減算しています。12月の次は1月ですが算数上だと12+1で13月になってしまいますよね。1月の前の月は12月ですが、算数上では1-1で0月になってしまいます。これもDATE関数を使うを月を増減させるだけで、正しい年月日を求める事ができます。

このようにDATE関数を使うと、面倒臭い日付の計算を全部EXCELがやってくれるのでとても楽ですね。スケテンもほとんどDATE関数でカレンダーの日付を管理しており、私は月や日付を足し算するだけで済んでしまってます。（使っていないテンプレートもありますが、今になって考えれば失敗したなぁと思っています。）

DATE関数は日付に対応するシリアル値を返すので、年を表示したい時はyyyy年、月の場合はm月、日付の場合はdやd日と、前回までに解説してきたセルの表示形式と組み合わせ使用しています。

今回は逆に日付に対応するシリアル値から、年、月、日、それぞれの数値を抜き出す関数について、一番使う機会が多いMONTH関数を例にとって解説します。

スケテンでは、月末日以降の日付を表示しないようにしていますが、シリアル値のままでは「月が変わった・・・という判定がしづらい」ため、MONTH関数でシリアル値から月を抜き出し、比較するようにしています。

=MONTH(DATE(2008,1,31))

上の計算式では、DATE(2008,1,31)で、2008/1/31に対応するシリアル値を求め、MONTH関数で2008/1/31の月数を求めています。戻り値は、当然ですが1になります。

=MONTH(DATE(2008,2,31))

続いて、上の計算式では、DATE(2008,2,31)という指定をしていますが、2月31日というのはあり得ない日付です。前回解説した通り、DATE関数ではその辺もうまく計算して2008/3/2に対応するシリアル値を求めてくれます。結果、MONTH関数で2008/3/2の月数を求める事になるので、戻り値は3になります。

YEAR関数もDAY関数も使い方は同じなので、ここでの説明は省略します。

複数月にまたがるカレンダーを作成する場合、翌月は何年何月になるか？という計算が必要で、スケテンでは主にその部分でYEAR関数やMONTH関数を使っています。

例えば、下図の6行目の2月と表示されているセルには以下のような計算式が設定されています。A6には2008、C6には1が入力されています。MONTH関数でC6の1に1を加算した月の1日が何月にあたるか？という計算をしています。

=MONTH(DATE(A6,C6+1,1))

同様にYEAR関数で2008年１月の翌月の年数を求めたり、2月30日が2月かどうか比較するために条件付き書式でMONTH関数を使っていたりします。

日付の計算は、DATE関数でシリアル値を求めたり、シリアル値からYEAR関数、MONTH関数、DAY関数で対応する年月日の数値を求めたりしている訳です。

スケテンで使用している日付計算でおさえておきたい最後、曜日の計算（表示）について書きたいと思います。

スケテンでも、そこかしこで曜日を表示していますが、今まで解説してきたシリアル値を書式設定を変更して曜日を表示しているパターンと、計算式でシリアル値から曜日を文字列に変換し表示しているパターンがあります。ほとんどの場合、前者の書式設定で用件が足りてしまうので、どちらの方法を取るかは好みによりますが、私は再利用のしやすさから、後者の文字列に変換する方法を好んで使います。

まずは、日付に対応するシリアル値を、セルの表示形式を変更する事によって曜日を表示する方法を解説します。（後述する「計算式でシリアル値から曜日を文字列に変換する方法」も結局はこの辺の事を理解する必要があります。）

分かりやすいように、B2のセルに2008/3/6と入力し、B2をアクティブセルにした状態で、セルの表示形式を変更します。

続いてセルの書式設定のダイアログを開きます。

Excel2002/2003の場合

書式→セルを実行します。

Excel2007の場合

ホーム→数値グループ→ダイアログボックスランチャーをクリックします。

Excel2007は他にも書式設定のダイアログを表示する方法がありますが、ここをクリックすると「表示形式」のタブが選択された状態で開く事ができます。

セルの表示形式を入力

セルの書式設定のダイアログが開くので、分類を「ユーザー定義」にし、種類に表示形式にaaaと入力します。

2008/3/6に対応する曜日が表示されました。

全て入力値を2008/3/6とした場合の例です。

セルの表示形式で曜日を表示している場合、セルの値はあくまでシリアル値です。従って、他のワークシートへ値のみコピー＆貼り付けすると、シリアル値になってしまったり、ピボットテーブルで曜日毎の集計をしようとしてもできなかったりと、再利用するとうまく出来なくなってしまいます。

人生でもそうですが、見た目だけで物事を判断せず、中身のほうも良く理解したいものですね。

次回は、もうひとつの方法、計算式でシリアル値から曜日を文字列に変換し表示しているパターンを書きたいと思います。

日付計算のラスト。TEXT関数を使って、数値を書式設定した文字列に変換する方法を書きたいと思います。スケテンでもあまり使用している訳ではないので説明は軽めにしておきます。

=TEXT(値,"表示形式")

引数である表示形式は、前回までに記載したセルの表示形式を設定します。（表示形式をダブルクォーテーションでくくって書きます）

下図は設定例になります。B2～B4にTEXT関数が設定されています。

TEXT関数で求めた値はシリアル値では無く単なる文字となります。なので書式設定を変える必要がありません。ただしMONTH関数など計算式で再利用する事ができません。

B2は2008/3/16に対応する曜日を計算し文字列に変換しています。目に見える値がそのままセルの値なのでわかりやすいですね。スケテンでも下図のような場面で使っています。

見た目だけ変えるか、セルの値そのものを変えるか、それぞれメリット、デメリットがあるので状況に応じて使い分けできるようになりたいですね。

• スケテン for Excelの紹介
• ダウンロードページヘ
• 操作方法の説明
• ガントチャートの作り方を解説します
• 休日設定の共有など、簡単な改造方法を解説します
• スケテン for Excelの実例で解説、Excelの日付計算に関するTips！