【番外編】Googleスプレッドシート 年間カレンダー (土日祝を色付けしよう)
Googleスプレッドシートで 1行数式で カレンダーを作成する方法を 全3回と前回の番外編で書いてきました。
↓前回の記事
本編の最後に完成した カレンダー関数は、前回の番外編で LAMBDA、数式結合、名前付き関数を使うことで、庵野氏もビックリの シン・カレンダー関数 に進化しました。
今回は カレンダーの見栄えをよくする為の、 色付けをやってみましょう。
色付け処理は 関数・数式ではカバーできないなので、「番外編」の扱いとしました。
Q1.年間カレンダーの 土・日 に自動で色をつけたい
祝日は少しハードルが上がるので、先に土日の色付けを考えましょう。
要件としては以下になります。
■年間カレンダー 土日色付け要件
・日曜日は 薄い赤で色付けしたい
・カレンダー1行目（曜日行）の「日」も同じく 薄い赤にしたい
・土曜日は 薄い青で色付けしたい
・カレンダー1行目（曜日行）の「土」の同じく 薄い青にしたい
・カレンダーのセル位置が変わっても対応させたい
「条件付き書式」を使うのはわかりますね？
カレンダーの位置が固定なら、範囲の一番左が 日曜日で、一番右が 土曜日だから、列を指定して「空白でないセル」をそれぞれ色付け設定してあげるだけで超簡単なんですが・・・。
今回は 列位置が変わる可能性があるってことなんで、ちゃんとに そのセルの中身を条件としたカスタム数式で色付けする必要があります。
前回までに比べればだいぶ簡単ですが、土日色付けできそうでしょうか？
↓ここから回答です。
A1.年間カレンダーの 土・日 に自動で色をつける
セルの色 や 書式 は 関数・数式ではどうにもなりません。
「条件付き書式」という機能を使います。
メニュー から 表示形式 > 条件付き書式 を開きましょう。
「条件付き書式」 EXCELとの比較
mirは Googleスプレッドシート職人で 推進派なんですが、それでも Excelに比べて Googleスプレッドシートが不便だなと感じる点が幾つかあります。
グラフ回りやビジュアル系 に加えて、この「条件付き書式」もGoogleスプレッドシートが Excelに比べ 弱い部分の一つです。
比較してみると、EXCEL側から「圧倒的じゃないか、我が軍は」と聞こえそうなくらい差がありますね。
Googleスプレッドシートの 条件付き書式は、EXCELに比べ 出来ることが少ない！ かなり限られているのです。
EXCELでシートカレンダーを作ったときは、 1ヶ月が6週あるときは 枠線を拡張させたり、 1日だけ 月/日という表示 にして他は日付のみにしたり、文字サイズを条件に応じて変えたり、色々できましたが・・・。
他にも 条件付き書式関連で不満があるのですが、それは後述します。
とりあえず、表示形式、文字サイズ・フォント、罫線回りなどの 出来ないことは諦めて、Googleスプレッドシートで出来ることをやりましょうｗ
条件付き書式を使ってみよう
土、日 は それぞれ 別の色をつけるので、それぞれ書式を設定する必要があります。
範囲はシート全体という指定が出来ないので、とりあえず 今回は A:Zとします。今回は お題回答 なので広い範囲を指定してますが、条件付き書式を広い範囲に対して多用するとシートが重くなります。
実際に使う際は、本当に必要な範囲にだけ設定しましょう。
条件は 「空白でない」や「次の文字を含む」、「次の数値以上」といった単純なもので、条件の対象とするセルと書式を設定するセルが同じ場合は 用意された条件の型を使えます。
一方、複雑な条件や 色付け範囲が条件セルとイコールではない場合（ その行全体・列全体といった時 ）は カスタム数式を使う必要があります。
条件付き書式のカスタム数式に関しては、一部動かない関数もありますが Googleスプレッドシートの豊富な関数が活用できるので、この部分はあまり不満はありません。
条件設定に関してはカスタム数式を理解すれば、かなり柔軟に対応できます。
■条件付き書式で カスタム数式を作成するポイント
・その範囲の開始セル（左上）で動く式を作る （範囲内で自動でスピる）
・式は TRUE,FALSEを返す形にする （TRUEの時に書式適用）
・条件セルではなく 書式設定するセル（色付けセル）の視点で式を作る
・条件セルと色付けセルがイコールでない場合は 絶対参照を利用
・一部の関数は 動かないので注意
・エラーが見つけにくので複雑な式はセルで作成・確認してからコピペ
こんな感じでしょうか。
一番大事なのは、事前に シート上で式をテストして正しく TRUE,FALSE が返ることを確認することです。
日、土の色付け 実践
まずは日（薄い赤）の条件から設定していきましょう。
セルを薄い赤で塗りつぶす条件は、「そのセルの内容が 日 という文字である、または 日付でその曜日が 日曜日である」ですね。
日付の曜日確認は、1行数式カレンダー作成の際に開始日調整で活用した WEEKDAY関数が使えます。日曜日は 1です。
というわけで A1セルの条件式を書くと以下のようになります。
=OR(A1="日",WEEKDAY(A1)=1)
「または」なので OR関数を使います。今回の場合は 条件セル = 書式設定セル なので、絶対参照は考慮不要ですね。
ただ、これを試すと 曜日行の「日」に色がつきません。
なんでだー！
早々に QAサイトに頼るのではなく、もう少し自力で検証しましょう。うまくいかない時は、その条件式が TRUEになっていないってことです。
こんな時は ポイントに書いたように、一度シート上で式を動かしてみるのがよいです。
なるほど、WEEKDAYに 日付以外を入れたことでエラーとなってました。
A1="日” が TRUE でも、もう一方の条件が エラーだと OR関数はエラーを返すってことですね。
それならエラー回避(IFERROR)を入れれば良いとわかります。
=OR(A1="日",IFERROR(WEEKDAY(A1)=1))
うまく動いたのでこのまま土曜日も条件設定しましょう。
ここで「完了」ではなく、「＋条件を追加」 を押すと、この設定した 内容が保存された上で、内容をそのまま引き継いで新しい条件設定の画面になります。
必要な個所だけ修正すればよいので便利。
日曜日の条件を 土曜日に置き換えてみましょう。土曜日は 7です。
=OR(A1="土",IFERROR(WEEKDAY(A1)=7))
空白セルが全て青になってしまいました・・・。
どうやら WEEKDAY で空白セルを 0 （シリアル値で 日付にした場合 1899/12/30）と判断し、その曜日（土曜日）の数値 7を返してしまったみたいです。
これを回避するのは、条件に「空白ではない」を追加すればよいです。
ここは AND条件となります。
=AND(A1<>"",OR(A1="土",IFERROR(WEEKDAY(A1)=7)))
ANDとOR両方登場してちょっと複雑ですが、これで完成です。
無事、土日の色付けができました～。
Q1.年間カレンダーの 土・日 に自動で色をつけたい
お題 クリアです。
Q2.年間カレンダーの祝日に 自動で色をつけたい （なるべく楽に）
土日に続いて、祝日も色付けをしてみましょう。
要件としては
祝日（振替休日も）を 日曜と同じ、薄い赤で自動色付けしたい
これだけです。
祝日リストを手動で用意すれば出来ますが、なるべく楽をしたい！ってスタンスでいきたいですよね？
昭和世代は「若いうちは苦労した方がいい」「隠れた努力」みたいなのが好きですが、今の令和 Z世代には響きませんｗ
祝日色付けは、どうやって実現すればよいでしょうか？
↓ここから回答です。
A2.年間カレンダーの祝日に 自動で色をつける（なるべく楽に）
使うべきは 他力本願API です。
（他力本願APIって、椎名林檎のアルバムっぽくない？）
API は Application Programming Interface の略でプログラミング用語なんですが、関数で使う場合は「よく使いそうなデータを使わせてくれるサービス」くらいの捉え方でよいでしょ。
たとえば、郵便番号から 住所を出力したい とか、ひらがなをカタカナに変換したい、といった際に自分でリストを用意するの面倒なんで、誰かが用意してくれてたら便利ですよね？
そんなときは、こんな感じで検索してみましょう。
存在すれば 、ありがたい API がサクッと見つかることも。
※APIによっては有料だったり、使用に制限事項があったり、事前に利用登録が必要なものもあります。
↑ こちらを使わせていただきましょう。
APIと関数で 祝日一覧を書き出す
このAPIを Googleスプレッドシート上で活用するには、IMPORTDATA関数と組み合わせれば良いです。
サイトの中の 年別API (date) を ↓ こんな感じで使います。
=IMPORTDATA("https://holidays-jp.github.io/api/v1/2022/date.json")
祝日 データが出力されました。
このAPIを使うメリットは、振替休日も考慮されているという点です。
数式処理で 振替休日を考慮しようとすると 結構複雑なんですが、このAPIを使えば 振替休日の考慮が不要ってことで、だいぶ楽になります。
ただ祝日の名称とセットで文字列になっているので、このままだと日付として祝日の条件に使えません。
この式を
年をセル参照(とりあえずA1）
日付だけのデータ
という形に加工しましょう。
=INDEX(SPLIT(IMPORTDATA("https://holidays-jp.github.io/api/v1/" & A1 &"/date.json"),":"),,1)
":"で SPLITで 分割して、1列目だけ取得すれば 祝日の日付データとなります。SPLITも Googleスプレッドシートの最強関数の一つですね。
EXCELにも TEXTSPLITって関数が追加されましたが、あちらは 横展開と同時に 縦方向への展開 が出来る利点はあるものの、複数セルに対してスピル利用できないという大きいデメリットがあります。
各行をSPLITさせる配列処理に関しては、スピル効果があるINDEX関数を組み合わせたことで ARRAYFORMULAいらずで実現できています。
{ と } もさらに関数を組み合わせれば 消せますが、カレンダーに単体で登場する記号ではないので、このまま放置で良いでしょう。
これを 条件付き書式で「祝日一覧に含まれるなら」という条件として使うには、 COUNTIF と組み合わせれば OKです。
祝日一覧を返す 名前付き関数 HOLIDAY を作ってみた
ちなみに裏で判定に使うだけではなく、祝日一覧として公開するんで 見栄えをよくしたいって場合は、以下の式を登録して 名前付き関数にしちゃいましょう。
=LAMBDA(y,Query(INDEX(SPLIT(REGEXREPLACE(IMPORTDATA("https://holidays-jp.github.io/api/v1/" & y &"/date.json"),"""",),": ",false)),"label Col1 '日付',Col2 '祝日名'"))(ye)
関数名 HOLIDAY
プレースホルダーとして ye を登録 ※yeは 年を表す4桁の数値 例 2022
こんな感じにすると良いです。
1行カレンダー作成時に 頭を悩ませた Query関数のマジョリティー除外システムが、今回は { } 部分を消すのに使えますねｗ
その他の細かい説明は割愛します。
Googleスプレッドシート 条件付き書式は IMPORT系は 直接使えない
条件付き書式のカスタム数式には制限があると書きましたが、作業しているスプレッドシートの外部から情報を取得・参照する関数（IMPORT系）は残念ながら直接 カスタム数式に使っても動きません。
「いや、動いてるよ！」という場合は、シート内で同じ式を使っているんじゃないでしょうか？ シート内で同じ式を使うと キャッシュの問題なのか、なぜか動きます。
というわけで、今回のIMPORTDATAや、IMPORTRANGE、翻訳関数の GOOGLETRANSLATE などの 外部を参照する関数は 条件付き書式で使いたい場合は、直接カスタム数式に入れるのではなく 一度 シート上に書き出したデータを参照させる必要があります。
とりあえず、シンカレンダー関数（FULLYEAR_CAL）を入れて、カレンダーを表示するシート名を カレンダー。祝日を出力するシート名を 祝日一覧 としましょう。
カレンダーシートが、カレンダーだけであれば 年 の参照（取得）には MAX関数が使えますね。（年の部分をセル参照としているなら、そこから参照させた方がよいです）
祝日一覧シート のA1に以下を入れて カレンダーシートの年と連動して 祝日を出力させましょう。
=INDEX(SPLIT(IMPORTDATA("https://holidays-jp.github.io/api/v1/" & YEAR(MAX('カレンダー'!A:Z)) &"/date.json"),":"),,1)
↓ カレンダーシートの 年を取得して、祝日が出力されました。
日本の祝日は 20以下なので、少し多めに考慮しても
'祝日一覧'!A1:A30
の範囲 にカレンダーの日付が一致したら、という条件にすればよいでしょう。
ちなみに 2014年以前の 祝日データは用意されていないようです。注意。
Googleスプレッドシート 条件付き書式は 他のシート 参照に INDIRECT が必要
祝日一覧に出力した 祝日リスト と カレンダーの日付が一致したら、という条件式ですが、
カスタム数式のポイントにも書いた通り、「書式設定するセル（色付けセル）の視点」で式をつくる必要があります。
探索範囲が 祝日一覧で、条件 が 左端のスタート地点 A1 とします。
=COUNTIF(祝日一覧!A1:A30,A1)
でもこれだと 残念ながらエラーになります。
エラーの原因は、「TRUE、FALSEを返す式になっていない」からではありません。
1以上の数値は TRUE という扱いになるので、COUNTIFはこのまま条件に使えるので、ここは合ってます。
では、どこが間違っているのか？
実は Googleスプレッドシートの 条件付き書式のカスタム数式は、別シートを直接参照することが出来ません。
でも INDIRECT関数を組み合わせることで 別シートが参照できます。
これは結構、知らないとハマるトラップです。
なぜか、こんな制限があるんですよね。
というわけで、ちょっと面倒ですが
=COUNTIF(INDIRECT("祝日一覧!A:A"),A1)
こんな式にしてあげる必要があります。
これで完成・・・ではないのです。
Googleスプレッドシート 条件付き書式は「一致したらそこで終了だよ」
「祝日」を薄い赤にする条件付き書式を設定して 祝日が色付けされましたが、 元旦 である 1/1 が青（土曜の表示）のままです。
これは、Googleスプレッドシートの 条件付き書式が 「上から条件を参照して、一致したらそこで終了（下の条件はスルー）」という仕様だからです。
ここもEXCELと違って不満な点。
EXCELだと「そこで終了するかどうか」を選択できるのにー。
「安西先生、祝日を優先したいです！」
今回の場合は並びを変えればよいだけですね。
条件は上が優先されます。
祝日の条件を上にしたら、ちゃんとに 1/1が祝日扱いで 薄い赤になりました。完成です！
今回の祝日のケースは並び順を変えるだけで解決できましたが、例えば土日祝の色付けに加えて、「本日の日付を太字にしたい」という条件も入れたい場合は、
Googleスプレッドシートだと
日付が 本日の日付かつ 祝日である → セルを薄い赤、文字太字
日付が 本日の日付かつ 日曜日である → セルを薄い赤、文字太字
日付が 本日の日付かつ 土曜日である → セルを薄い青、文字太字
日付が 本日の日付である → 文字太字
日付が 祝日である → セルを薄い赤
日付が 日曜日である → セルを薄い赤
日付が 土曜日である → セルを薄い青
と、全 ７パターンの全てのケースの条件を設定する必要があります。
すげー面倒ですね。
これは、さすがに EXCELが恋しくなるかもｗ
今回の 土日祝色付け 条件付き書式まとめ
今回の回答を まとめると、設定は以下のようになります。
■全て共通
適用範囲： A1:Z1000
書式ルール： カスタム数式
以下は、この順番で設定
■祝日用のカスタム数式 セルの色設定：#f4cccc
=COUNTIF(INDIRECT("祝日一覧!A:A"),A1)
■日曜日のカスタム数式 セルの色設定：#f4cccc
=OR(A1="日",IFERROR(WEEKDAY(A1)=1))
■土曜日のカスタム数式 セルの色設定：#c9daf8
=AND(A1<>"",OR(A1="土",IFERROR(WEEKDAY(A1)=7)))
こんな感じで、1行数式カレンダーの色付け が完成しました。
GASで 書式回りをパッケージも可能
ここまでは解説しませんが、今回の祝日一覧シートの自動作成を含めた 書式設定回りを GASでパッケージ化することも可能です。
条件付き書式、表示形式の "M/d"設定に加えて タイトル行の 太字、さらに 条件付き書式ではコントロールできない 中央揃え、日付行の高さ変更、罫線設定 といた処理を GASに入れてみました。
処理としては全てこのスプレッドシートファイル内なので、 onEdit で "=FULL_YEAR" が入力されたことを検知して 動かせるので承認いらずってのがよいですね。
式をいれれば、全自動で 見栄えを設定した 年間カレンダーが 作成されます。
GAS使える人は挑戦してみてください。
1行数式 年間カレンダーシリーズは、本編３回、番外編２回の 計５回の長編シリーズとなりましが 今回で終了です。
１行数式カレンダーを通じて、シート上での日付データの扱い、SEQUENCE、QUERYなど 最新のLAMBDA といった関数の組み合わせ・使い方 ・特徴、配列結合テクニック、そして条件付き書式のポイント、さらにGASの可能性と たくさんの学びがあったんじゃないでしょうか？
やりたいネタは色々ありますが、次はなにを書こうかなー。
小ネタが溜まってるので、その辺りを先に出しとくか。