Excel(エクセル)の数式や条件付き書式を使って、自動で表示されるカレンダーを作る手順を紹介します。
祝日も自動で表示し、土日祝日は色付けされるカレンダーです。
作るのが面倒な方は、こちらからどうぞ!
★祝日自動表示
★予定・記念日も追加で表示
★テーマを変更すると色やフォントが変わる
『自動表示カレンダー』ダウンロード
【PR】6ヶ月で全世界5万ユーザー&12億円売り上げAIボイスレコーダー PLAUD NOTE
こちらのボイスレコーダーは、録音した内容を文字に起こし、
即時に要約してくれます。
59カ国語に対応し、発言者を識別する機能や、ChatGPT-4oとも連携しており、毎月300分の文字起しと要約が永年無料!
カレンダー作成手順
カレンダーの作成手順はコチラです。
- カレンダーの基本のレイアウトを考える
- 数式で日付が自動で表示されるようにする
- 書式設定で”日”のみが表示されるようにする
- 祝日データをつくる
- 数式で祝日が自動表示されるようにする
- 条件付き書式で、色を付ける
- 体裁を整える
1. カレンダーの基本レイアウト
まず、A1セルに年(2024)、B2セルに月(10)を入力します。
そして、その下A2に「日」(月曜スタートのカレンダーは「月」)と入力し、オートフィルで曜日を「土」まで入力します。
曜日は「日曜日」と記述しても、「日曜」と記載してもかまいません。
「Sun」と入力すれば、「Sunday」と認識され、「Mon」、「Tue」と入力してくれます。お好みで記述してください!
このままだと、2024と10に年、月が表示されないので、セルの書式設定で自動表示されるようにします。
1. A1セルを選択する
2. 「セルの書式設定」を開く
3. 「ユーザー定義」を押す
4. 種類に「#年」と入力する
年、月を表示しないのもありですし、
英語表記にするものいいと思います!
その場合は「#月」と入力する個所を「mmmm」とすると「January」
「mmm」と入力すると「Jan」と表示してくれます。
枠線や表示位置はこの段階ではいじらなくて大丈夫です!
2. 数式で日付が自動で表示されるようにする
1. まず、日曜日の下のA3セルに、下記の数式を入力します。
=DATE(A1,B1,1)+(1-WEEKDAY(DATE(A1,B1,1)))
数式の説明をすると、
=DATE(A1,B1,1)は「A1年B1月1日」という事。(つまり、ほしいカレンダーの月の最初の日なので、今回は2024/10/1)
WEEKDAY(DATE(A1,B1,1))は「A1年B1月1日」の曜日を表す数字を求めています。日曜日が1、月曜日が2…(2024/10/1の場合は3の火曜日)
=DATE(A1,B1,1)+(1-WEEKDAY(DATE(A1,B1,1)))
=2024/10/1 +(1-3)=2024年10月1日の2日前=2024年9月29日
=A3のセルには2024/9/29を表示するという数式となります!
2. つぎに、B3以降の日付を入力します。前日に日付に1を足すことで、次の日付が表示されます。
3. 翌週も同様に数式で入力します。
できた表がこちら。ごちゃごちゃした表が出来上がりました!(次ですっきりさせますよ!)
3. 書式設定で ”日” のみが表示されるようにする
日付が入力されているセルを全て選択し、セルの書式設定を開きます。
ユーザー定義で「d」と入力すると、「2024/10/10」 が 「10」に表示が変わります。
<セルの書式設定について簡単にまとめた記事はコチラ>
dはdayという意味です。
日付データ(年・月・日)の中で “日” のみ表示する、という書式設定となります。
4. 祝日データをつくる
カレンダーに祝日を表示する為には、祝日が記載されたデータが必要になります。
祝日は、毎年決まっている祝日(元旦など)もありますが、振替休日や海の日などはその年により異なるからです。
祝日データはさまざまなサイトで配布してくれていますが、実際に祝日を決めている内閣府が、配布しているデータが一番正確(だと思う)ので、そちらのデータを利用する方法を紹介します。
国民の祝日は、内閣府が次年の分までを発表しています。
csv形式で1955年から最新の祝日までをまとめたデータを配布してくれているので、そちらをダウンロードすると、古いカレンダー(自分の誕生した月など)を作る事も簡単にできます。
<< 国民の祝日について – 内閣府 (cao.go.jp) >>ホームページはこちら
ホームページに飛んで、下にスライドしていくと、上記の表示が現れるので、クリックしてください。すると、右上にポップアップ窓が出てくるので、ファイル開くをクリックします。
開いたエクセルファイル「syukujitsu」のタブを右クリックし「移動またはコピー」を選択、
移動先のブック名で対象のファイル「カレンダー」を選択し、「末尾へ移動」を選んでOKを押します。
今回はデータをダウンロードしましたが、祝日データは自分で作成することも可能です。A列に日付、B列に祝日名を入力すれば、今回のカレンダーは動作します。
カレンダーシートの隣に祝日一覧のシートを追加できたので、そのデータに名前を付けます。
まず、ABの列を選択し、数式タブの「名前の管理」を押します。
名前を適当に付けて、(今回は「祝日」とします)他はさわらずOKを押します。
これで、祝日という名前のデータが出来ました。
このデータを使って、祝日を検索する数式をA4セルに入力します。
5. 数式で祝日が自動表示されるようにする
次は、実際に数式を入力して祝日を表示します。
そのためにまずは、祝日を表示する行をそれぞれの日付の下に追加しましょう。
行の挿入は、Ctrl + + で可能です。
例えば、5行目と6行目の間に行を挿入したい場合は、
6行目を選択した状態で Ctrl + + を押すと挿入できます。
祝日を表示する為の数式はコチラです。↓↓
=IFERROR(VLOOKUP(A3,祝日,2,FALSE),"")
一番左上の日付(ここでは29日)の下のセル(A4)セルに数式を入力します。
そして、入力した数式を他のセルにコピーする為に、A4セルをコピーし、他の祝日を入力したいセル(日付の下の空白セル)に数式のみ貼り付けします。
数式の説明をすると、
VLOOKUP(A3,祝日,2,FALSE)は
A3セルの値が、「祝日」と名前を付けたデータの中に完全一致であるかを(FALSE)検索し、見つけたらその2列目(隣)の値を返します。
そのままだと、値が見つからない場合に「#N/A」と表示されてしまうので、
IFERROEを付けて、エラーだったらセルに何も入力しない(””)を付けています。
これで、祝日がカレンダーに表示されるはずです。(黒字で!)
6. 条件付き書式で、色を付ける
次は、祝日とその名前を赤色に変更する為に、条件付き書式でルールを作成します。
その前に、日曜日と土曜日の色を変更しましょう。
いのこは、日曜日は赤、土曜日は青にしましたが、お好きな色に設定してください。
この後、祝日は赤、カレンダーの月以外の月はグレーに変更する条件付き書式を追加します。
対象月以外の日付をグレーに変更する条件付き書式
条件付き書式は、数式で指定したルールを適応することで、対象月以外の日付の書式を変更することができます。<条件付き書式でできる事についてまとめた記事はコチラ>
1. まずは、日付が表示されているセルを全選択。
2. 条件付き書式で、「新しいルール」を選択する。
3. 「数式を使用して、書式設定するセルを決定」を選択し、
4. 下記の数式を入力し、書式設定で文字色をグレーに設定する。
=MONTH(A3)<>$B$1
数式の説明をすると、
=MONTH(A3)<>$B$1
は「A3に入力されている日付の”月”が、B1に入力されている”月”と異なる」
という意味の数式です。
「A3」は対象セル。日付が入力されているセルです。
「B1」は絶対参照。対象セルが変更になっても絶対参照のセルは変更しません。
例えば「A3」に入力されているのは、2024/9/29。これの月は9月で、「B1」には10月が入力されているので、ルールが適応され、文字がグレーになります。
祝日を赤色に変更する条件付き書式
カレンダーの全てのセル(A3~G12)を選択し、条件付き書式で「新しいルール」をクリック。
「数式を使用して~」を選び下記の数式を入力します。
=COUNTIF(祝日,A3)>0
=COUNTIF(祝日,A3)>0
COUNTIF関数は範囲の中に検索する条件と一致するセルを探して、その個数を返す関数です。
祝日データの中にA3セルの中身があったら1となる為、0より多い、つまり祝日である場合に、このルールを発動させよ、という意味になります。
これで、祝日が無事赤色表示されたのですが、このままだと、当月以外の日にちも赤色で表示されてしまいます。(例えば、5月のカレンダーにした場合、4/29昭和の日は当月以外なのでグレーにしたいのに赤色表示されてしまいます)
原因は2つ。
・祝日がカレンダーの月以外だったら、祝日名をグレーにする書式設定が追加されていないため
・条件付き書式は、ルール一覧の上の方に表示されているルールが優先されるため
祝日名をグレーに変更する条件付き書式
まずは「祝日名をグレーにする書式設定」です。(分かりやすく、5月のカレンダーでやります)
- 昭和の日(4/29のセルB4)を選択
- 条件付き書式で『新しいルール』を選択する
- 「数式を使用して、書式設定するセルを決定」を選択
- 数式【=MONTH(B3)<>$B$1】を入力する
- 書式設定で文字色をグレーに設定する
- 「OK」を押して、4/29の昭和の日がグレーになるか確認する
【=MONTH(B3)<>$B$1】は、
B3に入力されている月がB1より多いもしくは少ない(つまり、異なる)場合にルールを適用する数式です。
B3の日付は2024/4/29で、月は4。カレンダーは5月(B1)のものなので、ルールが適用されグレーになります。
祝日名がグレーになるのを確認したら、条件付き書式の範囲を広げます。
- 条件付き書式の「ルールの管理」を選択する
- 先ほど作成したグレーになるルールの適用先を確認する
(ルールが見つからない場合は、「書式ルールの表示」のところを「このワークシート」に変更してみてください) - 矢印の個所を押すと、セルの選択ができるようになるので、カレンダー内の祝日名が入力される予定のセルを全て選択して適用させる
(Ctrlを押しながらA4~G4、A6~G6、、、と選択) - 完了!
これで、カレンダーの月以外の日の祝日名がグレーに自動で変更されるようになります。
ルールの管理を調整する
最後に、条件付き書式の適用順序を入れ替えて、文字色を確認します。
条件付き書式の「ルールの管理」を開くと、ルールがいくつかあると思います。
文字を赤色にするルールよりも、文字をグレーにするルールを優先させればOKです。
条件付き書式の「ルールの管理」で、赤色にするルールを ▼ を押して下に下げるだけです。
これで、条件付き書式の設定は完了です!他の月に変えても問題なく表示されるか、確認してみてくださいね!
7. 体裁を整えて完成!
最後に自分好みに表を整えます。
文字のサイズやフォントを変更し、枠の色を変更したりして、整えましょう!
まとめ
いかがでしょう!もし、シートを追加したくない、という方は別の記事で、ネットから自動で祝日を表示する方法を紹介していますので、そちらをご覧ください!