Excel初心者卒業の証！千年カレンダー！
■ はじめに
はじめに言っておきます。（注意書き！）
Excelの初心者の判定は、人によって異なります。
だから、あくまでも個人的な考えです。
「こんな考えもあるかも？」って思っていただければ、嬉しいです。
■ 個人的に思うExcel初心者卒業のスキル
変化に強いワークシートが作れる
日付（シリアル値）を正しく扱える
ワークシート関数を２０個以上扱える
「データ」「処理」「結果」を分離してワークシートを設計できる
数式は手入力で作成している
以上の５個が個人的に思うExcel初心者卒業のスキルと考えています。
今回の課題とも言える、「千年カレンダー」という課題は、これらすべてを含める事例ではありません。
しかしながら、「千年カレンダー」を自力で作れれば、立派なExcel使いと自信をもってもいいと思います。
今回は、考え方と作り方を珍しく詳しく説明したいと思います。
（時々、がんばるよ。）
■ なぜ「千年カレンダー」が課題なのか？
理由は簡単です。
とてもExcel初心者では作れない代物だからです。
Excelに自信がある人であれば、３０分もあれば、余裕で作れるでしょう。
ただ、Excelに自信が無くても、色々なワークシートを作った人であれば、４時間あれば、完成すると思います。（Ｗｅｂで調べながらもＯＫ）
■ 課題「千年カレンダー」の作成ルール。
ある程度、制限を加えることで、難易度を調整します。
下記１３個がルール（制限）になります。
「年」と「月」を指定すると、指定された年月の１月分のカレンダーを表示させる
指定されていない年月の日付は、表示させない
土曜日は文字色を青に、日曜日は赤にする
週の初めは「月曜日」とする
カレンダーは、日付のみ表示させる
カレンダーのヘッダーに指定した月を表示させる
カレンダーの一番左に「年と週」の項目を付けて「年と週」を表示させる
「月」の指定は、ドロップダウンリストで選択できるようにする
「年」の指定は、ドロップダウンリストで選択できるようにする
「年」は、過去２年～未来２までの５年間を選択できようにする
使用する関数は２０１３のバージョンまで（スピル関数使用禁止）
ＥＳＣ（配列数式）使用禁止
ＶＢＡ禁止
使用できるワークシート関数は、2013までのバージョンとします。
使用できる関数は、公式サイトをペタリンコしますので、詳しくない方は参照してください。
因みに、スピル関数（配列関数）を使うと、難易度がちょっと下がります。
■ まずは、考え方を整理します。
● 日付は「シリアル値」
Excelの日付は、「シリアル値」といわれる数値の連番で管理されています。「1」は、1900年1月1日を意味しています。
「2」は、1900年1月2日になります。「3」は、1900年1月3日です。
つまり、Excelの日付は、数値の連番であることがポイントになります。
● 月の初日を利用する
日付が数値の連番である「シリアル値」と理解できれば、月の初日から、＋１、＋２、＋３・・・と１ごと増やせば、日付を表現できます。
● 曜日を利用する
ただ、連番を作れたとしても、毎月の月初は、何曜日から始まるのでしょうか？ なかなか規則性を語れる人は多くないと思います。（俺も無理）
ただ、シリアル値は、曜日を取得できます。
曜日さえ取得できれば、月初の開始曜日が設定できそうです。
● 条件書式付き書式設定
指定されていない年月の日付を表示させない方法は、条件付き書式設定の機能を活用します。数式で条件を作ることによって、複雑な条件を設定できます。是非、覚えてほしい機能です。
● その他、色々
課題の内容をクリアするためには、さらに肉付けが必要になります。
機能と関数を組み合わせて実現してきます。
■ フォーマットを考える
全体のデザインを考えます。
今回は、下図のような感じのシンプルにしたいと思います。
（俺🍶が面倒くちゃいと思ったのは内緒です）
B1 に「年」を指定して、B2 に「月」を指定するようにして、「千年カレンダー」を実現させていきます。
なお、ひと月のみの表示ですので、カレンダー本体は、６行（データ部分のみ）あれば十分です。
■ 名前の定義を使う
● 数式に名前を付ける
数式に対して、「名前の定義」を設定する事が可能です。意外に知られていない方法です。
しかし、個人的にはお勧めしていません。
理由は、ワークシートが一気にブラックボックス化するからです。
しかし、今回は、あえて使って、数式に対して、「名前の定義」を使ってみます。
「こんな使い方があるんだぁ」って感じるだけでいいです。
さて、さっそく、数式に対して、名前の定義を使っていきましょう！
■ 数式に名前の定義を使うには？
下図のように、数式のタブから「名前の管理」を選びます。
以下、ショートカットキーの「Ctrl+F3」で表現します。
すると、「名前の管理」のウィンドウが表示されます。そこで「新規作成(N)」のボタンをクリックします。
「新しい名前」のウィンドウが表示されます。下図の「参照範囲(R)」に手入力で数式を作っていきます。一応、マウスでもセルを選択して数式を作れますので、セル参照くらいは、手入力しなくても大丈夫ですが、結果的に手修正することが多くなるので、個人的感想として、全部手入力で数式を作る事をお勧めします。
● 名前の定義における俺的ルール
完全に俺ルールになってしまいますが、名前の定義は、数式やＶＢＡで使いまわしするケースが多いです。
理由は、手入力で指定することが多くなるからです。
ある程度のルールを設定するだけで、使いやすく（入力しやすく）なります。
名前の先頭は、半角の「＿」（アンダースコア）にする
分かりやすい名前にする（基本、日本語）
使用するワークシートが別のブックにコピーする場合は極力使用しない
■ 「千年カレンダー」を作る
● 作る手順
カレンダーの基準点（一番左上）を決める
指定された年月から初日をどうにかして取得する
位置を意味する連番データをどうにかして計算させる
月の初日の位置を曜日を利用して計算させる
当月意外を見せないように条件付き書式設定でどうにかする
あとは、表示形式で、どうにかする
この順番で説明していきます。
１．カレンダーの基準点（一番左上）を決める
ま、ここのセルを基準点とします。あとで、数式で利用することになります。名前の定義に設定してしまいましょう。
B5セルを選択して、名前ボックスに直接入力します。
２．指定された年月から初日をどうにかして取得する
ま、これは、実にシンプルでして。
DATE関数を使うだけです。
せっかくですので、指定する「年」と「月」のセルにも名前を定義しちゃいます。
ここでは、B1のセル（年を指定する）を「_月」、B2のセル（月を指定する）を「_日」とします。
そして、数式に対して「名前の定義」をしてみましょう。
ショートカットキーで「Ctrl + F3」で、名前の管理のウィンドウを出します。そして、「新規作成」します。
名前は「_月初の日付」しました。（ちょっと長いかな・・・）
数式に対して名前の定義をすると、一気に読みにくいワークシートが出来てしまいます。よって、コメントは出来るなら入力しておくことをお勧めします。
参照範囲(R)は、手入力で、数式を作り込みます。
=DATE(_年, _月, 1)名前の定義の名前を忘れても大丈夫。
下図の方法で貼り付けする事ができます！
これで、指定された年月の初日の計算は大丈夫です。
３．位置を意味する連番データをどうにかして計算させる
言葉では説明が難しいですね。つまり、下図のようなイメージです。
６行×７列のセルに対して連番を付けることです。
これができれば、初日のシリアル値に対して、足し算するだけで、日付ができます。
例えば、３月１日に５を足せば、３月６日のシリアル値を出すことができます。
図ではルール違反のスピル関数を利用していますので、スピル関数を使わない例を説明します。
ここで利用する関数は、ROW関数とCOLUMN関数です。セルの位置を返してくれる関数です。
=COLUMN()-COLUMN(_基準点)+(ROW()-ROW(_基準点))*7この数式を作るとこんな結果になります。
セルをコピーして貼り付けします。
なんとなく、カレンダーっぽくなりましたね。でも表示されている値は単なる数値で、更に工夫が必要ですね。
４．月の初日の位置を曜日を利用して計算させる
「千年カレンダー」を作る上で、一番重要なポイントは、実は初日の位置を計算させることです。逆に言うと、これさえ出来れば、簡単なのです。
仕組みは単純です。
求められた初日の曜日を取得するだけです。
Excelには、日付に関するワークシート関数を数多く用意されています。
今回は、WEEKDAY関数を利用します。
WEEKDAY関数の第１引数に、月の初日のシリアル値を指定します。そして、第２引数は、開始曜日によって、値が異なります。
今回は、開始が月曜日なので「３」を指定します。
もし、カレンダーの一番左を日曜日にしたのなら、１を指定して、－１してあげれば良いでしょう。
最終的に、月初の日付にセルの座標値を足し算して、最後にWEEKDAY関数の結果を引き算してあげれば、うまく日付を出力してくれます。
ここまでくれば、ほぼ完成したようなものです。
では、「Ctrl+F3」で名前の定義のウィンドウを表示させて、新しい名前の定義を作りましょう。こんな数式を作ります。
=_月初の日付+COLUMN()-COLUMN(_基準点)+(ROW()-ROW(_基準点))*7-WEEKDAY(_月初の日付,3)名前は「_日付」としました。
コメントもしっかり書きましょう。
早速、セルに数式を入れて、結果を見てみましょう。
上手く出力されているか、表示形式を変更して確認してみましょう。
正しく表示されています。
後は、見た目だけです。指定していない月の日付をなんとかしましょう。
５．当月意外を見せないように条件付き書式設定でどうにかする
ここは、条件付き書式設定の機能を活用します。
日付部分を選択して、「ホーム」タブから、「条件付き書式」→「ルールの管理」を選択します。（新しいルールでも良いです）
下図の「新しいルール(N)」をクリックします。
ルールの種類を一番下の「数式を利用して、書式設定するセルを決定」（なんか日本語変ですよね💦）を選択し、下の「次の数式を満たす場合に値を書式設定」に数式を入力します。
● 数式で書式設定する場合の重要なポイント
数式の結果 が TRUE か FALSE で判断している
数式の結果が、論理値で返すように作ることがとても重要なポイントになります。この点が条件付き書式で数式を扱う時の鬼門になっていると思います。
で、式の内容としては、指定された月と日付の月が等しいか等しくないかを比べるだけです。ただ、空白文字には変更できませんから、文字色を白にしてごまかします。
下図の様に、比較演算子を使って、結果を論理値で返すようにしています。
MONTH関数の引数は、かならずアクティブセルを指定して相対参照にします。
書式を設定します。フォントの色を白くするだけ・・・
下図の様になり、カレンダーと言っても良いレベルになりました。
６．あとは、表示形式で、どうにかする
後は、表示形式で、日付だけを表示するようにします。
日付のセル範囲を選択した状態で、「Ctrl+1」を押します。
表示形式から「ユーザー定義」を選択し、種類に「Ｄ」と入力してＯＫボタンをクリックします。
下図の様に日付だけが表示されるようになりました。
■ 入力規則を設定する
最後に、年と月の入力規則を設定して、マウス操作だけでも操作できるようにします。
● 月の入力規則を作る
いくつかの方法がありますが、一番、単純な方法を紹介します。
入力値の種類から「リスト」を選択して、元の値に、「1,2,3,4,5,・・・」と入力して、ＯＫします。月は、減ったり増えたりしないので、この直接入力する方法が成立します。
● 年の入力規則を設定する
年の指定は、過去２年、未来２年と当年の５種類を選べるようにします。
これは、別のセル範囲に、そのデータを準備することで実現できます。
適当なセルに下図の様な数式を作ります。
YEAR関数を指定されたシリアル値（日付）の年を返す関数です。
TODAY関数は、今日の日付をのシリアル値を返す関数です。
そして、その下のセルに＋１した数式を４個つくります。
今回は、「_年指定」という名前を定義します。
作成した年の連番を選択して（５個のセル）、名前の定義をします。
今回は、「_年指定」とします。
次に、年を指定セルを選択します。
リストを選択して、元の値に「=_年指定」と入力します。必ず最初に「＝」と入力しましょう。
=_年指定
これで、マウス操作だけで、年月が指定できるようになりました。
あとは、土曜日を青にして、日曜日を赤にして、ヘッダ部分に月を表示させて、週を表示させて終わりになります。（これで、課題クリア）
一気に進めていきます。
■ 残りの課題を作る
土曜日と日曜日の色に関しては、単純にフォントの色を、指定の色に指定しましょう。
表示形式に関しては、条件付き書式設定が優先させますので、土曜日の列は、青文字にして、日曜日には、赤文字にするだけです。
次に、ヘッダ部分に月を表現してみましょう。
色々な手段がありますが、今回は、表示形式の機能を利用します。
B3のセルに、数式を作ります。
値は、シリアル値が表示されていますが、これを「月」だけの表現にします。Ctrl + 1 で、書式設定のウィンドウを開きます。
ちょっと、トリッキーな表示形式です。
[DBNum3]は、半角数値を全角数値で表示してくれる表示形式の記号です。
[デブ・ナム・サン]と覚えるといいですよ（笑）
表示は全角なのに、ちゃんと計算してくるようになります。
日付の表現で、全角の方が見栄えが良い場合多いですよね。覚えておくと「どや顔」できるかもですw
最後に、週の番号を表示させておしまいになります。
週の表現は、ISOWEEKNUM関数が正確です。
正しい週の数値を出力するポイントがあります。
ISOWEEKNUM関数の引数は、木曜日を指定する事。
これだけです。
以上で、完成といたします。
色付けたり、罫線引いたり、フォント変えたりしたい所ですが、いいでしょう。（面倒と思っている事なのは内緒です）
■ 「千年カレンダー」を課題にしたもう一つの理由
俺🍶は、日付に関する関数は重要と考えています。
一般事務において、数値は、はっきり言って、四則演算レベルで十分すぎるほどです。SUM関数だけでもいいくらいです。
しかし、日付は複雑です。
例えば、「２０日締め」っていうけれど、カレンダーによって、前倒しになったりします。（先送りもあるのかな？）
毎月の稼働日も毎月ことなるし、曜日も休日も変わってきます。
他にも、日付を扱う機会は多く、しかも複雑です。
更に、日付関連の関数は、単体ではほぼ役に立たなく、関数に関数を入れ子（ネスト）にしないと使いものにならない場合が多く、必然的にExcelの数式は、複雑になります。
そんな複雑な日付計算をExcelは簡単に操作できる関数や表示形式を準備しています。これらを組み合わせる技術が重要だと思っています。
■ 数式に「名前を定義」をするメリット
俺🍶は、最初の方で、「数式に名前を定義を使うことは良くない」と書きましたが、小さなメリットがあります。
数式を簡単に変更できないため、「Excelこわれたー！トラブル」を防ぐ効果があり。
長い数式を日本語で表現できるので、可読性が上がる。
数式を作るのが簡単になる。
この３点くらいですかね。特に１の数式を消されても、簡単に復旧できる可能性が高いです（作り方によりますが・・・）。
しかし、結果的に、ブラックボックス化することは、否定できないため、乱用は避けるべきかと思います。
■ おわりに
カレンダーの作り方を書いた俺🍶が言うのもおかしな話ですが、Excelは表計算ソフトです。どんなツールを作ってもいいと思いますが、これは忘れてはいけないポイントだと思っています。
また、今回の課題を実際にやってみると実感できますが、「関数の挿入ダイアログボックス」が、名前を定義や条件付き書式設定ではまったく使えないことを伝えます。
私が、冒頭で書いた「個人的に思うExcel初心者卒業のスキル」の５個目に「数式は手入力で作成している」と書きました。
個人的に、とっても重要なポイントだと思っています。
Excelをもっと使い倒したいと思っているなら、今日から「関数の挿入ダイアログボックス」は絶対に使用しないでください。あの機能は、Excelの成長を止めてしまう悪魔の機能です。よっぱおじさんのお願いです。
最後に、「千年カレンダー」が作れれば、Excel初心者卒業とか、書いてしまいましたが、正直、ちょっと難しいかもしれませんね・・・
でも、スキルチェックには良い題材だと思います。
是非、スキルチェックで試してみては？
こんなくだらないExcel記事を最後まで読んでくれた素敵なあなた。本当にありがとうございます。
いつものように、今回の資料を添付します。
今日も、明日も素敵な日であります事、祈っています。
この記事が気に入ったらサポートをしてみませんか？