ハマログ

株式会社イーツー・インフォの社員ブログ

スプレッドシートテクニック – カレンダーの作り方

つーじーです。そろそろワクチン予約したい今日この頃。

大抵のIT系企業なら「エクセル」や「スプレッドシート」、それに類する表計算系ソフトを使って多くのことをこなしている事でしょう。特に表の形をしているものを作る場合はワードよりも作りやすいです。

今回はその中の一つ、『カレンダー』をスプレッドシートで作る場合のテクニックをお教え致します。

特にスプレッドシートでカレンダーを作る用途として、「スケジュール管理」が挙げられます。どの日までに終えるべきかを示す期日管理。何人日の業務かを識別するための工数管理。あらゆる意図において、スプレッドシートとスケジュール管理は相性が良いです。ここではスプレッドシートでカレンダーを作る際に効率的に作れるテクニックをご紹介します。

最終的には1つのセルに日付を入力するだけで、以下のようなカレンダーが作れることを目指します。

カレンダーの作り方

■初日を入力するだけで全ての日付を表示させる方法

カレンダーを作るたびに1か月分全ての日付を毎回設定し直すのは面倒です。そこは関数を使えば効率化できます。

まずは初日を設定するセルを作ります。今後はここの日付を変えるだけで、カレンダーのすべてが変わることを目指します。なお、初日セルは必ず各月の初日(1日)を設定してください

※本稿では『A1』に初日を設定し、その一つ下の行に日付を設定する方向で解説を進めます。実際にカレンダーを作成する際は状況に応じて読み替えてください。

その後、日付を表示したい行の一番左に

=A1

を入力します。

次にその1つ右のセルに

=A2+1

を入力します。

そのセルを右いっぱいにオートフィルします(セルの右下の■を右方向にドラッグ)

あとはその行全てを選択し、[表示形式の詳細設定→カスタム数値形式から

dd
(一桁で良い場合は『d』)

を設定すれば、その行全てが日付表示になります

■日付の行の上に、1日の上部のみに月を表示させる方法

日付が連番で表示させられたのは良いですが、それが何月の日付なのかが見た目だけではわかりません。そこで新たに関数を構築することで、何月のカレンダーなのかがわかるようにします。

初日セルの一つ右のセルに

=IF(DAY(B2)=1,B2,)

を入力します。一つ下のセルが「1」だった場合はそのセルを表示させ、違う場合は空白にする関数です。

そのセルを右いっぱいにオートフィルします。そうすると下の画像の通り、1日の上のセルにだけ数値が表示されます。

ただしこのままでは日付が表示されてしまいます。(関数で「一つ下のセルと同じ内容を表示」させているため)

ここで重要なのは、日付セルに入力されているのは日付のみではなく、年月日が入力されている上で日付のみを表示させている点です。なので同じセルの情報を表示させていても、表示形式さえ変えれば「日表示」「月表示」を切り替えることができるのです。

つまり、あとは月の行全てを選択し、[表示形式の詳細設定→カスタム数値形式から

mm
(一桁で良い場合は『m』)

に設定すれば、その行全てが月表示になります。これで最低限のカレンダーの完成です。

■関数で曜日を表示させる方法

カレンダーを作るなら曜日が欲しいですよね。ちゃんと曜日を表示させる方法はあります。

日付の行の一つ下の行の一番左のセルに

=TEXT(A2,”ddd”)

を入力しましょう。なんとこれだけで、セルに入力された日付を曜日に変換してくれます。

補足すると、 『=TEXT(A2,”dddd”)』と入力した場合は「〇曜日」ともっと詳しく表示されます。

あとはこのセルを横いっぱいにオートフィルすれば曜日表示の完成です。

■土曜日と日曜日の列に色を付ける方法

平日と休日は見分けがつくようにしたいですよね。ここでは条件付き書式を使って、特定の列に色が付くようにします。

少し見やすくするために枠線を作りました。まずは色を付けたい範囲全てを選択します

その状態で[表示形式]→ 条件付き書式 を選択して『条件付き書式設定ルール』のメニューを表示させ、『+条件を追加』を押します。

書式ルールを『カスタム書式』にしたあと、

=A$3=”日”
(「A$3」の部分には曜日の行の一番左のセルを指定)
(必ず見本と同じ位置に「$」マークを挿入すること)

と入力し、書式設定のスタイルで任意の色を指定すれば、日曜日の列だけ色が付くようになります。なぜこれで特定の列に色が付くようになるかについては、説明が長くなるので割愛します。

上記と同じ方法で、同じ範囲を選択後に

=A$3=”土”
(「A$3」の部分には曜日の行の一番左のセルを指定)

を指定した条件を追加すれば、土曜日の列にも色を付けることができます

■祝日の列に色を塗る方法

さすがに単純な関数だけでは祝日の判定はできません。どうしても祝日も自動で色を変えたい場合は、別途祝日リストを作る必要があります。せっかくなので今回は五輪で祝日が移動した2021年の祝日仕様でやってみようと思います。

祝日のリストが作成できたら、土日の色塗りと同じように 範囲を選択 → [表示形式]→ 条件付き書式 →『条件付き書式設定ルール』→『+条件を追加』 → 書式ルールを『カスタム書式』にしたあと、

=COUNTIF(INDIRECT(“‘祝日’!$B$1:$B$16”),A$2)=1
(「 $B$1:$B$16 」の部分には別シートの祝日一覧の範囲を指定)
(「祝日」の部分にはシート名を指定)
(「A$2」の部分には日付の行の一番左のセルを指定)
(必ず見本と同じ位置に「$」マークを挿入すること)

と入力します。別シートに指定するだけでこれだけ面倒な記述になります。

もし同一シートに祝日リストを記載する場合は

=COUNTIF($B$1:$B$16,A$2)=1
(「 $B$1:$B$16 」の部分には同一シートの祝日一覧の範囲を指定)

のみでOKです。

頑張れば振替休日(日曜日が祝日だった場合、月曜日がお休みになる)の判定もできるのですが、さらに複雑な書式になってしまうため、本稿では割愛いたします。

■補足

これで作りたい要素はすべて実装することができました。以降補足説明となります。

上記のカレンダー画像を見てほしいのですが、こちら、8月8日が祝日指定されているのに緑色になっておりません
これは条件付き書式の順番が原因となっております。

実は条件付き書式は上の方にある条件のほうが優先されます。だから赤色のほうが優先されてしまうわけです。

これを変更したい場合は、条件付き書式にマウスを載せている間に表示される「:」のような縦並びの点線をドラッグアンドドロップすれば、条件の並べ替えができます。求めている条件に応じて並べ替えしてみましょう

ここまで出来たら完成です。試しに一番最初に作成した「初日セル」の日付を別の月にしてみましょう。

◆2021/1/1に指定した場合

これまで作ってきたすべてが自動で置き換わるのでなかなか爽快です。

この手法がスケジュール作成などに役立てば幸いです。今回はこれにて。

Excelカレンダースプレッドシート関数

  つーじー   2021年8月17日


関連記事

中華街の思い出

こんにちは、かねこです。 僕のおとうと(あるいは犬)のウェブディレクターJOEが…

花残月 2022

今年の染井吉野は、見頃前後が雨に降られてしまってハラハラとしました。 いつもの大…

Amazon Dash を試しに買ってみた。

どうも! 最近、近所の定食屋で唐揚げ定食頼んだら殆ど生肉だったWingmanです…


← 前の投稿

次の投稿 →