2023年の自作手帳は超ミニサイズ＆スマホカレンダー併用型に決定。

前回はスプレッドシートの数式を使って曜日と日付を設定しました。

超ミニ自作手帳2023紙面作成その1｜スプレッドシートの基本も学べるお得さ

2023年の自作手帳は超ミニサイズ＆スマホカレンダー併用型に決定。 前回は完成図と、併用スマホアプリ「minical」の素晴らしさについて語りま…

今回は月名や西暦の設定と、祝日を自動的に赤くする仕掛け作りです。

関数を使いますが、基本はコピペで済むようにしているので身構えなくてOK。

自作手帳2023作成のザックリ工程

ザックリとこんな流れで進めていきます（ところどころ前後しますが）。今回は工程3「月名リスト作成と年月セット」と工程4「祝日表示セット」していきますよ〜。

月名リスト作成・年月セッティング

まずは「月」と「年」です！

今回は英語で月名を入れていきますが、数字だけでもいいですし、和名でもいいです。

どちらにせよ、月名リストを書き換えるだけで変更できるようにしますぞー。

月名変換リスト（表）を作る

月を「月数」ではなく「月名」で表示させる場合、下準備として月数・月名のリストを作ります。単純に数字とテキストを入れただけで数式は使っていません。

［R3:R14］に数字、［S3:S14］にテキストですね（キャプチャの下が切れてしまった）。

もし今後、月名を和名にしたければS列のテキストを修正すれば、手帳シート全体に適用できる仕様です。

とりあえず下準備は完了！

月数を求める関数チャレンジ

月名表示では「month」という関数を使います。もし月名ではなく「月数」で表示させたい場合はリスト不要、month関数だけで事足ります。

月名表示についてルールを決めました。

キャプチャの場合は［C3］の日付から月数（1月）を判定し、［C2］に表示させるってことですね。別に火曜日でもいいし、日曜日でもいいんですが、ルールを決めます。

month関数の使い方は以下のとおりです。

=month(セル番号)

つまり、C2には以下の関数を入れればいいってこと。

=month(C3)

はい、カンターン！　C3は2023/01/09なので、この関数を実行すると「1」が返ってきます（キャプチャでは小さい吹き出しに青で「1」って出ていますね）。

たとえば開始日［N3］を2023/02/06に変更すると［C3］は06（2023/02/06）になり、［C2］の月数は「2」になります。何ともまあ単純な関数です。

月を数字で表示させたい場合はこれだけでOKです。

でも今回は、月数ではなく月名を表示させるのがゴールなので……。

vlookup関数チャレンジ

先ほど作った月名リストと「vlookup」という関数を使いまーす！

=vlookup(目印,リスト範囲,リストの何列目を使うか,false)

これがvlookup関数の基本形です。

• リストに必ず「目印」の列を作ること
• 目印の列は必ず左端にあること

この2つがvlookup関数を走らせる絶対条件^※1で、私が作った月名変換リストは目印＝月数、左側に配置してあるのでOKですね。

じゃあ手帳に落とし込んでみまーす。

※1：目印列が右にあってもvlookupを走らせることはできますが、基本は左です。

リストを使って月名を表示させる（vlookup関数）

=vlookup(month(C3),$R$3:$S$14,2,false)

• 月数を
• 月名変換リストの左列から探して
• 一致した行の2列目のデータを表示させる

という意味です。

• ［C3］から求めた月数「1」を
• 変換リスト［$R$3:$S$14］の左列から探すと［R3］が該当
• 2列目なので［S3］の「January」がヒット！

ですね。

リスト範囲の「$」マークは忘れずに付けてください。絶対参照と呼ばれるもので、コレを付けておけば関数をどこにコピペしても絶対に「R3:S14」を使ってくれます。

コレを付けない状態が「相対参照」です。文字通り、データの相対的な位置関係を元にして列数行数が変わっていきます。

また後で書きますよ〜。

ということで、vlookupを実行すると「January」が表示されました！

リストの「January」を「睦月」に変えれば「睦月」と表示されます。

月名を表示させたいセルは4つ。手打ちするのはめんどくさいので、［C2］の関数を［D2・G2・H2］にコピペします。

キャプチャは［H2］を選択した状態です。関数を見てみると……。

=vlookup(month(H3),$R$3:$S$14,2,false)

月数判定に使うセルが［H3］に変わっていますが、リストは変わらず［$R$3:$S$14］です。

関数をお隣にコピペすれば月数判定セルもお隣に変わる＝相対参照、リストはコピペしても変わらない＝絶対参照って話です。

1行目のテキストを削除して、ページごとに背景色を付けてみました。

おお、何かそれっぽくなってきたぞ……。

年数を求める関数

曜日列の一番上に「西暦」を表示させることにしました。キャプチャでは［B2・I2］ですね。

月数の関数が「=month()」でした。年数を求める関数はというと……。

=year(セル番号)

たんじゅーん！

年数については1週目・4週目月曜の日付から求めることにしました。つまり［I2］に入れる関数は……。

=year(H3)

ですね。

こんな感じで、関数っつってもvlookupみたいに複雑なものもあれば、monthやyearのように直感的で覚えやすいものもあります。

祝日表示を設定

フリーランスやっていると祝日に疎くなるんですよね……。ということで、祝日は赤字で表示します。

祝日は未来永劫同じではありません。制度変更があったり、オリンピックの年はかなりイレギュラーでした。

このあたりを自動で更新できて、更新結果によって日付に自動で色が付くようにしまっせ。

何せ永久不滅手帳シートだからね！

祝日のリストを作り、手帳の日付とリストの日付が一致したら文字を赤にする、という仕様にしていきましょう。

内閣府の祝日データを引き込む

手帳の紙面を作っているタブとは別に、新しく祝日用のタブを作ります。

スプレッドシート左端の「＋」を押すと、真っ新なタブ（シート）が立ち上がりますよ。名前は「祝日」にしました。

新しく作ったタブの［A1］に、四の五の言わず関数を入れます。

=IMPORTDATA("https://www8.cao.go.jp/chosei/shukujitsu/syukujitsu.csv")

内閣府は最新の祝日情報をCSVファイルで公開しています。

これをダウンロードして使ってもいいんですが、そうすると祝日が変更された場合に手動で修正しないといけません。

IMPORTDATAを使うと、内閣府のサイトにあるCSVファイルが更新された場合にシート側のデータも更新されます（もちろんオンラインの場合）。

ただしリンクが変わった場合は再設定が必要！

コレが結構便利で、仕事でもよく使っています。さて、関数を実行してみましょう。

まあ、文字化けがすげーっす。でも気にしない気にしない。使いたいのはA列の値。

A列の数字は、前回の記事でご紹介した「シリアル値」です。表示形式を変更してみますね。

西暦からの日付データでした。2023年1月時点では、1955年〜2023年分までのデータが公開されています。

B列は祝日名だと思いますが、私は使わないのでこのままにしておきます。

リストに名前を付ける

A列・B列を「内閣府祝日」という名前のデータベースとして使います。

さあ、名前を付けますぞ〜。

［A:B］をまるっと選択した状態でサブメニューを開きます。

［列での他の操作項目を表示］→［名前付き範囲を定義］をクリック。

キャプチャの青枠部分に名前を入力します。何でもいいですよ、私は「内閣府祝日」にしました。

入力したら［完了］をクリック。

こんな感じで、範囲に「名前」が付きました。

これで下準備は完了。このリストを使って、祝日を「赤」表示にする仕様にしていきまっせ！

条件付き書式設定チャレンジ

条件付き書式は、指定したデータが、指定した条件と一致（または不一致）した場合、指定した書式を適用する機能です。

どこでもいいのでセルを選択した状態でサブメニューを開き、［列での他の操作項目を表示］→［条件付き書式］をクリックします。

キャプチャは条件付き書式のデフォルトで、セルが空白ではない場合にビミョーな緑に塗りつぶします。

現在［C3］には値が入っているので、空白ではない＝ビミョーな緑になっていますね。

［セルの書式設定の条件］には他にもいろいろな選択肢があります。空白だったらとか、データが一致したら、この数値より大きかったら、○〜○の間だったら、とかね。

祝日を赤にする条件付き書式を設定

• 内閣府祝日リストにある日付
• 手帳の日付

この2つの条件を満たした場合に、手帳の日付を赤くする、という条件付き書式を作っていきます。

［C3:C］（C3から下のセルすべて）に適用させるので、範囲は［C3:C］です（本当はあまりよろしくないんですが^※2）。

［セルの書式設定の条件］は［カスタム数式］を選択しました。今回は2つの条件を突き合わせるという割とややこしい条件なので、既存の条件は使えません。

［書式設定のスタイル］（見せ方）は［B（太字）］と文字色を赤にしました。

※2：条件付き書式は、スプレッドシートがいちいち計算して判定している「数式」なので、数が増えれば当然負荷がかかります。条件付き書式無双みたいなシートを作ると動きがどちゃくそ重くなるんです。なので本来は［C3:C250］みたいにある程度範囲を狭めておくのがベター。今回はそれほどデータが多くないので気にしません。

カスタム数式っていうぐらいなので、自分でカスタムした数式を入れる必要があります。

ここでひとつお断りを……。キャプチャの適用範囲が［D3:D］になっておりますので、ここからはD列で説明しまーす！

以下の式を入力しました。

=countif(indirect("内閣府祝日"),$D3)

範囲の始まりが［D3］の場合、カスタム数式も［D3］にします。

• 内閣府祝日リストの中に［D3］の日付が1つでもあったら［D3］を赤太字にする
• 次は［D4］の日付でやる、その次は［D5］……で最後まで調べて書式を適用する

［$D3］としているので行番号は固定されていません。なので指定した範囲内［D3:D］で「3の次は4、4の次は5」と順番に適用してくれるんです。

まあ難しいことは考えず数式をコピペして［完了］をクリックしまーす。

結果がこちら。2023/01/09は成人の日、祝日でした。きちんと赤くなっている！

同じようにC列・G列・H列にも条件付き書式を設定しました。

内閣府の祝日CSVが生きている限り、日付を日曜始まりにしても過去の日付にしても、祝日は常に赤太字で表示されます。

【参考】絶対参照と相対参照は知っておくと便利

セル番号を指定した数式をコピペする場合、絶対参照・相対参照を理解しておくと便利です。

下のキャプチャは今回使ったvlookupです。リストは絶対参照でした。

vlookupの数式を1つ下にコピペすると、month()の中身は3→4にズレます。でもリストはズレません。

なぜならリストの行にも列にも「$」を付けて絶対参照にしてあるからですね！

じゃあ「$」を付けなかったらどうなるのか。

vlookupの数式を1つ下にコピペすると、month()の中身は3→4にズレます。同時に、リストの指定範囲も3→4、14→15にズレます。

そうすると、1月の月名がリストから外れてしまい、1月の月名が表示できなくなってしまうんです。そりゃ困る。

相対参照は「数式を1つ下げたら全部1つずつ下げる」「数式を1つ右に動かしたら全部1つずつ右に動かす」、数式が入ったセルからのソーシャルディスタンスを保ち続けるんです。

これ絶対参照って書いちゃいましたが、そうでもないな。リストの行だけを固定して、列は固定しないケースです。

数式セルを右に1つ移動させると、リストの指定範囲も右に1つズレてしまいます。これも困る。

右ではなく下に動かす分には問題ないですよ。なぜなら行は固定してあるから。

まあこんな感じで、数式をコピペしたときにセルの番号が「動いてほしい」「動いてほしくない」を考えて「$」を付けていく必要があります。

次回はハイパーコピペ回を予定しているので、もう少し理解が深まるかなと！

次回は見た目を整えてページを増やしていくよ！

数式と条件付き書式をうまい具合に設定すれば、手帳の書き換え・作り替えがラクになるんですよね。

手帳だけじゃなく、レイアウトを変えればカレンダーとしても使えるし、もちろん印刷せずに使ったっていい。

無料のスプレッドシートでこんなに遊べるなんて、Googleさんの太っ腹加減に涙出ますよ。

次回はいよいよ手帳っぽくページを増やしますよ！