Excelのカレンダーテンプレートの数式をGoogleスプレッドシートで1つの数式にまとめちゃおう（LET,LAMBDA,REDUCE活用）

2024年最後のnoteです。なにを書こうかな～と考えましたが、

2024年を年間ビュー数で振り返ると上位にきているのは

ハイパーリンク活用テクニックや

iPad、iPhoneのアプリ版スプレッドシートの改行方法

プルダウン活用（基本の2段連動プルダウン）

タイムスタンプを自動で入れる方法（GAS）

Googleスプレッドシートの新機能「テーブル機能」

この辺りの「機能」や「テクニック」に関するキャッチーなネタが中心です。

やはり、シート関数をフル活用した複雑な数式を書くようなネタはあまりウケない・・・。

ただぁ（粗品風）

やっぱり mirのベーススキルは「シート関数をフル活用して1つの数式だけで処理する」って部分なんで、自己満足ではありますが 2024年の最後は関数ネタで〆たいと思います。

先週の note ExcelのカレンダーテンプレートをGoogleスプレッドシートで使っちゃおう！

こちらの可変タイプの年間カレンダーを 「あえて」1つの数式、1つの条件付き書式のカスタム数式で 書いてみよう！

今回はこんなnoteをお届けます。

Excelのカレンダーテンプレートの数式をGoogleスプレッドシートで1つの数式にまとめる！のゴール 

今回のnoteは

前回利用した 👆コチラ のカレンダーテンプレートを Googleスプレッドシートで開いて使います。

1つの数式で可変型の年間カレンダーを作るのがゴール

Excelの年間カレンダーのテンプレートには

名前付き関数 や

名前付き範囲、

さらに 条件付き書式

が大量に設定されているんで、これらを全て削除。

名前付き関数や名前付き範囲は便利な機能ですが、多用しすぎると逆にわかりづらくなるんですよね。

また、条件付き書式は多用すると シートが重くなります。

枠だけ利用させてもらって、カレンダー範囲の起点となる左上の B3セルに 数式を1つだけ入れることで

このように年間カレンダーを生成。

月の前後の日程部分の日付をグレーにする条件付き書式も、全体で1つのカスタム数式を設定するだけ

こんな感じで、1つの数式と1つの条件付き書式でカレンダーを完成させよう！というお題です。

もちろん、このカレンダーテンプレートの本来の機能である

B2セル（結合セル）の年を変えることで2025年だけでなく、2026、2027とこの先も使い続けることが出来る万年カレンダーになっていて、さらに K1セルのプルダウンで開始の曜日を選択するとカレンダーの表示が連動する機能もそのまま使えるものとします。

1つの数式で可変型の年間カレンダーを作りなおすメリット

元々のExcelカレンダーのテンプレートの名前付き関数や数式、条件付き書式を削除して、1つの数式、1つの条件付き書式でカレンダーを再構築したからといって、特段なにか良くなるわけではありません。

ぶっちゃけ手間をかけた割にメリットは一切ないですｗ

Excelのカレンダーテンプレートは スピル機能がない Excel2019以前のバージョンでも利用できるように作られていて、これは凄いことなんですが

Googleスプレッドシートだったら バージョン気にせず 最新関数をフル活用して 1つの式で書けるんじゃね？やってみよう！

ってだけ、つまり好奇心と達成欲を満たすだけです。

そこそこの難易度なんで、シート関数好きには学びもあっていいお題だとは思いますが、ただカレンダーとして使いたいだけなら無駄なことをやるだけですｗ

その点はご了承ください。

ちなみに1つの数式でカレンダーを作るネタは過去にも noteを書いていて

LETもLAMBDAも無い時代に作ったカレンダー式や

LAMBDA登場後にアレンジした式

LET関数登場でさらにアレンジした式

などを紹介しています。

Q1. 1つの数式で 開始曜日が可変の 万年カレンダーを作れるか？

それではお題いってみましょう。

Excelのカレンダーテンプレート

こちらを Googleスプレッドシートで開き、すべての名前付き関数、名前付き範囲、条件付き書式、さらにシート上の数式を削除した 枠だけを使って

K1セルの開始曜日のプルダウン、B2セルの年 に連動する 年間カレンダーを B3 セルだけに1つ数式を入れることで実現したい！

年間カレンダーは 以下のように 月、曜日の下に 6行（6週）表示し、4か月分が横並びで3行構成のものが B3:AC26のセル範囲に展開されるものとする。

なお、日付の部分は 表示形式で "d" としているので、見た目は数値ですが中身は日付データです。

まずは、これに挑戦してみましょう！

好きな人、やってみたい人だけチャレンジしてみてください！！

↓↓
回答はここから。
↓↓

A1. 1つの数式で 開始曜日が可変の 万年カレンダーを作る

いきなり回答ではなく、考えた方を含めて解説していきます。

複雑な配列を生成する時は、まずはパーツに分割

この手の複雑な配列構成は、数式で作りやすいパーツに分解して考えるのが良いです。

まず、4か月分のカレンダーを1セット（calSet）と考え、これが縦に積みあがっている（下に足されていく）と考えます。

さらに

calSetは1ヶ月カレンダー calMonth が4つ横に連結したものと考えます。

さらに1ヶ月のカレンダーは

このように3つに分割できます。

ここで

1～12月までや 日付部分の生成は SEQUENCE関数

縦や横の連結は VSTACK関数 、HSTACK関数 または { a ; b }、{ a , b } といった中カッコによる結合

開始日をズラす部分は WEEKDAY関数

曜日部分の生成は TEXT関数で 表示形式 "ddd"指定

繰り返し処理を REDUCE関数

複雑な式になるので変数化できる LET関数

と、ざっくりと使うべき関数のイメージが出来るかどうか？がポイントです。

この段階で「このあたりの関数を使うんだろうな」とイメージが出来る人は、かなりのレベルと言えるでしょう。

曜日部分の可変を考える

最小構成の3つのパートを組み立てる式を考える上でネックになるのが、開始の曜日がプルダウンで可変となる点です。

プルダウンで選択した曜日は文字列になっているので、まずはこれを数値化して 幾つずらすのか？ を考えましょう。

選択した曜日を 何番目という数値にするのは

こんな感じの曜日配列を用意して、プルダウンで選択した曜日（K1セル）がMATCH関数、またはXMATCH関数で何番目にヒットしたかを取得すればよいです。

曜日配列はSEQUENCE関数で 1～7の連番を生成し、それをTEXT関数で 表示形式を "dddd"と指定することで用意できます。

SEQUENCEは自動でスピる関数ですが、TEXT関数を配列に対して適用させる為に ARRAYFORULA関数が必要となります。

以降も配列処理が必要な個所が登場するので、ARRAYFORMULAは常に一番外側にしておくと良いでしょう。

ARRAYFORMULAは一番外側に一つあれば、全体に配列効果（スピル効果）を付与できます。

このSEQUENCE関数で生成した 1～7 がいい感じに曜日に変換されるテクニックは、 1～7をシリアル値として見た時、日付の曜日が ちょうど 日曜日～土曜日となっていることを利用しています。

ちなみに Excelの場合 1をシリアル値として 日付とみなすと 1900年1月1日なんですが、なぜか曜日は 日曜日となっており Googleスプレッドシートと同じ並びになっています。

この辺りはExcel側のうるう年問題やらなんやらに関連してくるんですが、とりあえず Googleスプレッドシートの曜日の方が正しいです。

ただ、実は上のような日曜日始まりではなく、今回はこの曜日配列を 月曜日始まりとしておくことが 重要なポイントです。

なぜ月曜日始まりにする必要があるか？は後で触れます。

この配列を使ってXMATCH関数で

こんな式を作ると、K1セルのプルダウンで選択した曜日が 月曜日始まりとして何番目か？という数値を得ることができます。

この曜日の数値は後で使うことになるので、曜日数値（Weekday Number） WDN としておきましょう。

計算結果を変数化する（○○と置く）、LET関数の出番ですね。

で、このWDNを使うとカレンダーの曜日部分（短い曜日表示の配列）は

このように書くことができます。

MOD関数を使って 8や9 を1や2に変換したくなるところですが、曜日の場合は勝手に7ずつ循環するので、MODを使う必要はありません。

まとめると 曜日部分は

=ARRAYFORMULA(LET(
  WDN,XMATCH(K1,TEXT(SEQUENCE(7,1,2),"dddd")),
  TEXT(SEQUENCE(1,7,WDN+1),"ddd")
))

こんな式になります。

開始（左端）がプルダウンと連動して変化する 曜日配列の見出し部分が出来ました。

月の日付部分を考える

では、いよいよメインの日付部分生成に入っていきましょう。

とりあえず 6行7列でサイズは固定なんで、基本となるのは

SEQUENCE関数で生成する6行7列の配列です。

問の部分にも書きましたが、日付の数値に見える部分は中身は 日付データなので B1セルの年をYEAR、とりあえず1月の 1を mと置いて

=ARRAYFORMULA(LET(YEAR,B2,m,1,DATE(YEAR,m,SEQUENCE(6,7))))

こんな感じにしてみます。

表示形式を カスタム数値形式で  d とすれば

このように日付となりました。

3つの塊を縦に連結する

まだ開始日がズレてますが、とりあえず曜日も表示させた方がわかりやすいんで、先に連結しちゃいましょう。

月の数値を mとしたんで、 月のヘッダー部分は m&"月" ですね。

曜日のヘッダー部分 TEXT(SEQUENCE(1,7,WDN+1),"ddd") は、WD（WeekDayの略）と置きましょう。

ただ、 m&"月" は単体の文字で 横幅が1 であるのに対して、 曜日部分や 日付部分は横幅 7の配列で、縦に連結する際に接続面のサイズが合っていません。

というわけで、ここでは中カッコ { a ; b } での縦連結は使えないので、VSTACK関数を使ってエラーとなる部分を IFERRORで空白としましょう。

式をまとめると

=ARRAYFORMULA(LET(
  YEAR,B2,
  m,1,
  WDN,XMATCH(K1,TEXT(SEQUENCE(7,1,2),"dddd")),
  WD,TEXT(SEQUENCE(1,7,WDN+1),"ddd"),
  IFERROR(VSTACK(
    m&"月",
    WD,
    DATE(YEAR,m,SEQUENCE(6,7))
  ))
))

このようになります。

あとは日付部分の開始日を曜日に合わせてズラすことが出来ればOKですね。

WEEKDAY関数の隠された仕様

カレンダーの開始日を曜日に合わせてズラす際に使うのが、曜日を数値で返す WEEKDAY関数なんですが、

GoogleスプレッドシートのWEEKDAY関数の解説ページを見ると

このように第2引数には 1,2,3 のいずれかが入るような解説が書かれています。

もちろん通常は 週のスタートは 日曜日か月曜日なんで、これだけでも問題ないんですが、今回は プルダウン連動で水曜日始まりや金曜日始まりとなるケースも出てくるので、これだけだと少し困ります。

実は、GoogleスプレッドシートのWEEKDAY関数の解説には書かれていませんが、Excel側のWEEKDAY関数の解説ページには

第2引数の解説にこのような記述があり、11～17を指定することで自由に 好きな曜日を1として 曜日を数値化出来るとなっています。

で、これは実は GoogleスプレッドシートのWEEKDAY関数でも使えるんです。

このように 2025年1月1日は 水曜日なので、WEEKDAY関数を第2引数指定なしだと、日曜日を1として水曜日は 日、月、日、水 で4番目なので 4となりますが、

第2引数が 11なら 月曜日を1とするので 水は 3
第2引数が 13なら 水曜日を1とするので 水は 1
第2引数が 14なら 木曜日を1とするので 水は 7
・・・

このようにコントロールすることが出来ます。

この11～17のWEEKDAYの第2引数が 11が 月曜日であることが、曜日ヘッダー部分の可変式を考える際に、月曜日始まりの配列を用意した理由です。

この11～17の並びと揃えておく必要があったってわけです。

月の開始日を考える

では 月の開始日付をどのようにズラせばよいか？

たとえば 2025/01/01 の場合は、水曜日なんでカレンダーの曜日が水曜始まりだったら ズレは 0で 2025/1/1から始まれば良いわけです。

同じように

こんな感じになるわけです。

つまり 月の1日の曜日をWEEKDAY関数で取得する際に

このように 第2引数を 開始曜日の数値 WDN に +10 した 曜日（開始曜日と一致した場合は1）をさらに -1 したもの、これを buffer とおいて、

日付部分を

とすれば、開始日が その月の1日の前の指定した曜日の日付 となります。

=ARRAYFORMULA(LET(
  YEAR,B2,
  m,1,
  WDN,XMATCH(K1,TEXT(SEQUENCE(7,1,2),"dddd")),
  WD,TEXT(SEQUENCE(1,7,WDN+1),"ddd"),
  buffer,WEEKDAY(DATE(YEAR,m,1),WDN+10)-1,
  IFERROR(VSTACK(
    m&"月",
    WD,
    DATE(YEAR,m,SEQUENCE(6,7)-buffer)
  ))
))

ちなみにこの時点で曜日だけでなく、年も連動して可変する1月カレンダーとなっています。

1ヶ月カレンダーを4つ横連結する

1ヶ月分のカレンダー calMonth が完成したので、これを4つ連結した colSetを作っていきましょう。

ここは二次元配列を生成して 連結してく処理を繰り返すことになるので、LAMBDAヘルパー関数 最強クラスの REDUCE関数が必要となります。

イメージとしては

SEQUENCE(4) で 1,2,3,4 という配列を用意して、1月のカレンダーを作って 横連結、2月のカレンダ―を作って さらに横連結、3月のカレンダーを作ってもういっちょ横に連結、4月のカレンダーを作って 最後の横連結。

という繰り返し処理です。

REDUCE関数に苦手意識を持っている人も多いかもしれませんが、毎回言ってますがこれは慣れれば腹落ちしてきます。

避けずに積極的に使って失敗してを繰り返しましょう！

先ほどの式で

この部分は、月によって変動するので REDUCE内に入れます。

REDUCE関数の冒頭は

こんな感じで用意しましょう。

REDUCE関数の 第1引数 初期値は 空白（とりあえず空白でというケースが多い）、第2引数 配列は SEQUENCE(4) となります。

横方向に連結するから SEQUENCE(1,4) としたくなるかもしれませんが、ここは処理に使うだけの配列なので 横にする必要はありません。

LAMBDA内の 引数となる
_pv2 ・・・ 累積値
_cv2 ・・・ 現在の値

は、どう置いてもいいんですが、mirは

previous value と current value の略で、こう付けることが多いです。

今回は後でもう1回REDUCE出てくるんで、2をつけてます。

ここで pv2やcv2としちゃうと セル指定と 見なされてエラーになるので、頭に _をつけています。

さらに REDUCE内でもう1回 LETを使って

=ARRAYFORMULA(LET(
  YEAR,B2,
  WDN,XMATCH(K1,TEXT(SEQUENCE(7,1,2),"dddd")),
  WD,TEXT(SEQUENCE(1,7,WDN+1),"ddd"),
  REDUCE(,SEQUENCE(4),LAMBDA(_pv2,_cv2,
    LET(
      buffer,WEEKDAY(DATE(YEAR,_cv2,1),WDN+10)-1,
      calMonth,IFERROR(VSTACK(_cv2&"月",WD,DATE(YEAR,_cv2,SEQUENCE(6,7)-buffer))),
      HSTACK(_pv2,calMonth)
    )
  ))
))

こんな式を作ってみましょう。

1月だけの式の時に m だった箇所を _cv2に置き換えて、1ヶ月分のカレンダーの塊を  calMonth と置く。

最後に  HSTACK(_pv2,calMonth) として 横に連結していく式です。

ただ、これをそのまま B3セルに入れると

こんな感じでエラーになります。

これはREDUCEのループ1巡目で 空白と 1月のカレンダーを横に連結する影響でサイズ違いのエラーと さらに空白の初期値分、一つ右にズレてしまう為です。

Excelだったら、最後にDROP関数で左端をそぎ落とすんですが、Googleスプレッドシートの場合は残念ながらDROPがありません。

無いものは仕方ないので、内部処理で分岐させましょう。

簡単な方法は、IFERRORと 中カッコ連結を使うテクニックです。

=ARRAYFORMULA(LET(
  YEAR,B2,
  WDN,XMATCH(K1,TEXT(SEQUENCE(7,1,2),"dddd")),
  WD,TEXT(SEQUENCE(1,7,WDN+1),"ddd"),
  REDUCE(,SEQUENCE(4),LAMBDA(_pv2,_cv2,
    LET(
      buffer,WEEKDAY(DATE(YEAR,_cv2,1),WDN+10)-1,
      calMonth,IFERROR(VSTACK(_cv2&"月",WD,DATE(YEAR,_cv2,SEQUENCE(6,7)-buffer))),
      IFERROR({_pv2,calMonth},calMonth)
    )
  ))
))

最後の部分をHSTACKではなく あえて中カッコによる 配列の横連結を使って

こう記述することで_pv2 が1巡目で初期値（空白）だった時だけエラーを起こさせ IFERRORで calMonth を返す、2巡目では _pv2が 1巡目の calMonth（1月のカレンダー）なのでエラーにならず 横に連結、以降も同様に横に連結 とできます。

中カッコによる配列の結合面のサイズ違いエラーを使って、1巡目だけ分岐させてるわけですね。

これで 4か月分のカレンダー calSet が出来ました。

REDUCEをネスト（入れ子に）して 2重ループを作る

この4か月分のカレンダーセット calSetをさらに縦に連結する必要があるので、もう1回 REDUCEをします。

つまり REDUCEをネスト（入れ子に）して使うってことですね。

GAS（JavaScript）やVBAを書ける人なら、for ループを2重にする記述には馴染みがありますよね？ これと一緒です。

外側のREDUCEで使う配列は {0;4;8} という配列がよいでしょう。これを 取り出したものを _cv1とすれば、

SEQUENCE(4) + _cv2 で

と3回のループで 12月までが生成出来ます。

{0;4;8} は SEQUENCE(3,1,4,0) としてもOK。
※ 3行1列の配列で 0から始まり4ずつ増える

初期値空白の対処方法は 先ほど紹介したIFEEROR ＋ 中カッコでいいですね。

式を書くと

=ARRAYFORMULA(LET(
  YEAR,B2,WS,K1,
  WDN,XMATCH(K1,TEXT(SEQUENCE(7,1,2),"dddd")),
  WD,TEXT(SEQUENCE(1,7,WDN+1),"ddd"),
  REDUCE(,SEQUENCE(3,1,0,4),LAMBDA(_pv1,_cv1,
    LET(
      calSet,REDUCE(,SEQUENCE(4)+_cv1,LAMBDA(_pv2,_cv2,
      LET(
        buffer,WEEKDAY(DATE(YEAR,_cv2,1),WDN+10)-1,
        calMonth,IFERROR(VSTACK(_cv2&"月",WD,DATE(YEAR,_cv2,SEQUENCE(6,7))-buffer)),
        IFERROR({_pv2,calMonth},calMonth)
      )
      )),
      IFERROR({_pv1;calSet},calSet)
    )
  ))
))

プルダウンで開始曜日を選ぶ K1セルを WS（WeekStartの略）としました。

REDUCEは2重ネスト、LETは3重ネストになってますが、そこまで複雑ではないかと思います。

この式を テンプレートの中身を削除した シートのB3セル（結合セル）にコピペすれば

このように 1つの式で Excelカレンダーテンプレートの開始曜日が可変の万年カレンダーが実現できました。

Q2. 1つの条件付き書式で 年間カレンダーの 前の月、後の月の日付をグレーにできるか？

カレンダーは生成できたので、残りは 条件付き書式で

各月のカレンダー内の 前月の日付と翌月の日付部分をグレー文字とするカスタム数式の作成にチャレンジしてみましょう。

もとのテンプレートの条件付き書式は

こんな感じで、各月ごとに全部で12の条件付き書式が設定してあって

各月で 3週目の先頭（1月だったら B7セル）を絶対参照として 月を取得、日付のセル範囲（1月だったら B5:H10）の月と不一致だったらTRUEとするカスタム数式 を設定しています。

ま、これは基本ですね。

ただ、これだと12個つくる必要があるんで非常に面倒です。

これを月ヘッダーも含めたカレンダー範囲 B3:AC26を対象として1つの条件付き書式で実現したい！ってお題です。

考えてみましょう。

↓↓
回答はここから。
↓↓

A2. 1つの条件付き書式で 年間カレンダーの 前の月、後の月の日付をグレーにする

こちらもいきなり回答ではなく、考えた方を含めて解説していきます。

月が一致しているか？を判別する方法

まず 当月かどうかの判定で、各月で 3週目の先頭を取得するのはちょっと厳しい・・・

というわけで、月のヘッダー行のこの部分を使います。

月が一致しているかどうか？を判別する方法として、MONTH関数を使う方法以外に TEXT関数で "m月"として一致判定する方法があります。

今回は月のヘッダーの文字列 と 各セルの日付をTEXT関数で 〇月に変換した文字列

これが一致していない時にTRUEとする式を作れば良さそうです。（一致している時は その月の日付なので 黒い文字のままでOK）

では、この月のヘッダー部分を各月で取得するにはどうすれば良いか？

これは 月のヘッダーの位置（行、列）のルールに着目する必要があります。

月ヘッダー行は結合セルになっているので、ちょっと列位置はわかりにくいですが、結合されたセルは実際に値が入っているのは左上の起点となるセルなので、1月～4月だったら B3、I3、P3、W3 で、列番号だと 2,9,16,23 と 7ずつ増えているのがわかります。

行に関しては 3,11,19と 8ずつ増加してますね。

これを式にしたいわけですが、ここで基準値の倍数のうち、最も近い整数の倍数に数値を切り捨てる FLOOR関数 が使えます。

FLOOR関数を使って 行の場合は 第2引数を 8として

このようにすることで、その日付のセルの カレンダー範囲 B3:AC26内の月ヘッダーの行位置を取得することが出来ます。

同じく列方向も 第2引数を7として

この式で、その日付のセルの カレンダー範囲 B3:AC26内の月ヘッダー列位置を取得することが出来ます。

あとはこれを INDEX関数と組み合わせた式

これをフィルコピーすると

このように そのセルの月のカレンダーのヘッダー行（〇月）が取得できます。

これを使って以下のようにカスタム数式を作成し

条件付き書式に設定するといけそうですが・・・

残念ながら 曜日部分でも TRUEを返し グレーになってしまいます。

これを回避する為に「そのセルが日付である」という条件を加えます。

ここで使う関数は ISDATE関数です。

これを先ほどの条件式と AND関数で組み合わせて

このようにすればOK。こちらが回答のカスタム数式となります。

条件付き書式は、絶対参照と相対参照、そして複合参照の理解が必須です。

各月の前月、翌月の日付部分だけをグレー表示にする条件付き書式が出来ました～。

他の方法もありますが、これが一番？シンプルな式かなと思います。

1つの式、1つの条件付き書式で開始曜日が可変の万年カレンダーが完成

Excelテンプレートの開始曜日が可変の万年カレンダーを、Googleスプレッドシートで 1つの数式、1つの条件付き書式で 実現することができました。

いかがだったでしょうか？

これを面白い！と思える人はシート関数沼にハマってますねｗ

来年 2025年も、この手のディープな関数ネタをメインで書きつつ、定期的に数字のとれるキャッチーな GoogleスプレッドシートやGoogleWorkspaceネタ、GASネタなどを 書いていきたいと思います。

2025年も 引き続き mirの noteをよろしくお願いいたします！