Googleスプレッドシート 1行数式でつくる年間カレンダー -3

2022年10月19日 18:39

1行数式でつくるカレンダーシリーズ。曜日の並び7列の月カレンダー、前回までの記事で、月の切り目の無い ぶっ通しの7列年間カレンダーは作成できました。

1行数式シリーズも、今回で本編は最後。

かなりハードルは上がりますが、より実用的な年間カレンダーを1行数式で作成してみましょう。

Q3. A1セルに年を入れたら、日曜始まり7列の以下のような「月の切り替わりで区切り（改行させ）る」年間カレンダーを展開させたい。

■今回の月区切り年間カレンダーの要件
（前回のぶっ通し年間カレンダーと同じ要件）
・A1セルに年 (例 2022）を入れたら、その年の年間カレンダーを出力
・日曜始まりの7列（B~H列）に曜日に沿った形で表示
・数式は1セルにのみ入力（1行数式）とする　上記画像だと B2セル

（ここから今回の追加要件月で区切るイメージ）
・月が替わる際は、繋がって表示ではなく1行下から表示
・月の最終日が土曜日だった場合、次の月が繋がってるように見えてしまうので、その際は 1行空けて翌月を表示
・上記の改行以外に無駄に行を空けない

今回のカレンダー要件

追加要件は、以下のようなイメージです。

今回も 1行数式（1つのセルにだけ数式を入れる）で、実現していきます。
上の画像をベースにすると式を入れるセルは B2のみってことです。

前回は日付が連続していたので SEQUENCEで実現できましたが、この月の変わり目の1段下げ部分をどう処理するか・・・。

実際、こんな細かい要件は仕事じゃなきゃ断ってもいいと思いますがｗ　今回はお遊びということで挑戦してみましょう。

どうでしょう？1行数式で作成できそうでしょうか？

↓ここから回答です。

A3.1行数式で作る日曜始まり7列の月区切り年間カレンダー

今回も「いきなり答える」方式で、先に完成イメージと最終形の数式を先にだします。

↓ いきなり今回の回答、最終形の式です。

【回答】最終形の式

=ARRAYFORMULA(IF(Query(LOOKUP(SEQUENCE(12*42/7,7),SEQUENCE(12*42),FLATTEN(IF(MONTH(DATE(A1,SEQUENCE(12),SEQUENCE(1,42)-WEEKDAY(DATE(A1,SEQUENCE(12),1))+1))=SEQUENCE(12),DATE(A1,SEQUENCE(12),SEQUENCE(1,42)-WEEKDAY(DATE(A1,SEQUENCE(12),1))+1),IF(DATE(A1,SEQUENCE(12),SEQUENCE(1,42)-WEEKDAY(DATE(A1,SEQUENCE(12),1))+1)=DATE(A1,SEQUENCE(12)+1,1),0,)))),"where Col1 is not null OR Col7 is not null",0)=0,,Query(LOOKUP(SEQUENCE(12*42/7,7),SEQUENCE(12*42),FLATTEN(IF(MONTH(DATE(A1,SEQUENCE(12),SEQUENCE(1,42)-WEEKDAY(DATE(A1,SEQUENCE(12),1))+1))=SEQUENCE(12),DATE(A1,SEQUENCE(12),SEQUENCE(1,42)-WEEKDAY(DATE(A1,SEQUENCE(12),1))+1),IF(DATE(A1,SEQUENCE(12),SEQUENCE(1,42)-WEEKDAY(DATE(A1,SEQUENCE(12),1))+1)=DATE(A1,SEQUENCE(12)+1,1),0,)))),"where Col1 is not null OR Col7 is not null",0)))

横長すぎて見ずらいのでインデント付きバージョン（内容は一緒です）

=ARRAYFORMULA(
  IF(
    Query(
      LOOKUP(
        SEQUENCE(12*42/7,7),SEQUENCE(12*42),
        FLATTEN(
          IF(
            MONTH(DATE(A1,SEQUENCE(12),SEQUENCE(1,42)-WEEKDAY(DATE(A1,SEQUENCE(12),1))+1))=SEQUENCE(12),
            DATE(A1,SEQUENCE(12),SEQUENCE(1,42)-WEEKDAY(DATE(A1,SEQUENCE(12),1))+1),
            IF(
              DATE(A1,SEQUENCE(12),SEQUENCE(1,42)-WEEKDAY(DATE(A1,SEQUENCE(12),1))+1)=DATE(A1,SEQUENCE(12)+1,1),
              0,
            )
          )
        )
      ),
      "where Col1 is not null OR Col7 is not null",0
    )=0,
    ,
    Query(
      LOOKUP(
        SEQUENCE(12*42/7,7),SEQUENCE(12*42),
        FLATTEN(
          IF(
            MONTH(DATE(A1,SEQUENCE(12),SEQUENCE(1,42)-WEEKDAY(DATE(A1,SEQUENCE(12),1))+1))=SEQUENCE(12),
            DATE(A1,SEQUENCE(12),SEQUENCE(1,42)-WEEKDAY(DATE(A1,SEQUENCE(12),1))+1),
            IF(
              DATE(A1,SEQUENCE(12),SEQUENCE(1,42)-WEEKDAY(DATE(A1,SEQUENCE(12),1))+1)=DATE(A1,SEQUENCE(12)+1,1),
              0,
            )
          )
        )
      ),
      "where Col1 is not null OR Col7 is not null",0
    )
  )
)

式内の文字数781文字、インデントつけたら 37行。

1行数式と言うにはかなーり無理がある、限界突破、全王様もおったまげ な式になってしまいました。

もう少し精査できるかもしれませんし、同じ記述が2回出てくるので、そこをラムれば（LAMBDA化すれば）、もっとシンプルにはなります。

ただ、要件が結構面倒なんで式が複雑化してしまうのは仕方ないのです。
とりあえずは1行数式？で実現できました～。👏ぱちぱち

今回の式のポイント

今回の式を解説していきましょう。ポイントは6つもあります。

ダブルSEQUENCE （行、列それぞれ展開）
WEEKDAY関数で月ごとの開始日を調整
IFで当月以外は空欄に（翌月1日だけ 0にしておく）
FLATTENで縦１列、さらに SEQUENCE + LOOKUP で指定列数折り返し
QUERYで不要な空白行を削除
最後に IFで 0 を空白化（任務を終えて静かに消える）

早くも心折れそうですね。

ポイント1.ダブルSEQUENCE （行、列それぞれ展開）

ダブルSEQUENCEってなんだ？って話ですが、今までのように1つのSEQUECEでは月毎に区切るのは無理です。だから月ごとに日にちを動かす、つまり月、日をそれぞれ独立したSEQUENCEで動かす方式に変える必要があるのです。

１次元的思考を２次元に変えるという言い方でもいいかもしれません。

=ARRAYFORMULA(DATE(A1,SEQUENCE(12),SEQUENCE(1,31)))

ベースとなるのが ↑ この式。

これは、A1の年に対して、縦方向に 12（月）、横方向に31（日にち）を組み合わせて日付を展開する式です。
実際入れてみると。

この月ごとの横並びのカレンダー行列の、それぞれの行の開始日、終了日をいい感じに調整して7列折り返しにすれば、なんとなくイメージしているカレンダーに近づきそうですよね？

ポイント2.WEEKDAY関数で月ごとの開始日を調整

ポイント1の式を加工した式が以下になります。

=ARRAYFORMULA(
　DATE(A1,
　　SEQUENCE(12),
　　SEQUENCE(1,42)-WEEKDAY(DATE(A1,SEQUENCE(12),1))+1
　)
)

4行目の WEEKDAY関数を使った開始日調整は、前回やったものと一緒です。これによって全ての月の開始日を 1日にもっとも近い 1日以前の日付の 日曜日に揃えています。

また、先ほどは日付をSEQUENCE(1,31)としていましたが、後で連結して7列毎に折り返すので、月ごとに区切ることを考慮すると横への展開数は7の倍数である必要があります。

さらに月の日にちは最大 31なので、それを超える最も近い 7の倍数は

7*5=35

ですが、開始の1日が週の後半（土曜日とか）の場合に 1ヶ月が6週になる月が出てくるので、そのケースに対応させるために 横への展開数を 42としました。

月によっては42だと多すぎますが、これは後で調整すれば良いです。
これを先ほどの式と差し替えると、表示が以下のようになります。

こうやって少しずつ近づいていきます。

ポイント3.IFで当月以外は空欄に（翌月1日だけ 0にしておく）

この段階で、月カレンダーの時と同じように横並びで当月以外の前後の月のセルを空白にしておきましょう。ここは煩雑な式になりますが、前回と同じく IFを使うしかないです。

「翌月1日だけ 0にしておく」はなぜ必要か？

これは後で QUERYで空白行削除をした際に、
要件の

「・月の最終日が土曜日だった場合、次の月が繋がってるように見えてしまうので、その際は 1行空けて翌月を表示」

を満たす為の仕込みです。後ほど解説します。

=ARRAYFORMULA(
  IF(
    MONTH(DATE(A1,SEQUENCE(12),SEQUENCE(1,42)-WEEKDAY(DATE(A1,SEQUENCE(12),1))+1))=SEQUENCE(12),
    DATE(A1,SEQUENCE(12),SEQUENCE(1,42)-WEEKDAY(DATE(A1,SEQUENCE(12),1))+1),
    IF(
      DATE(A1,SEQUENCE(12),SEQUENCE(1,42)-WEEKDAY(DATE(A1,SEQUENCE(12),1))+1)=DATE(A1,SEQUENCE(12)+1,1)
      ,0,
    )
  )
)

さすがに長くなったのでコード表記に変えました。

IF文で今月かどうか？を判定してますが、
ここは　SEQUENCE(12) がそのまま使えますね。

「今月」の日付か？が TRUEならそのまま表示、
FALSEならさらにIFで、

DATE(A1,SEQUENCE(12)+1,1) 　

として、翌月の 1日かどうかを確認し、
TRUEなら 0、FALSE なら空白としています。

うーん、この部分の処理は IFSあたりを使って整理できそうですが、とりあえずこのまま進めましょう。

空白を返すは EXCELだと ""が必要ですが、 Googleスプレッドシートは何もなしでOK。

これを適用すると、こんな感じ。

余計な日付が消えました。

ちなみに 赤で囲った 1899/12/30 は 0がシリアル値として日付表示されたものです。ちょうど翌月の1日（当月末の一つ右）の場所になってますね。

この時点では違和感があるかもしれませんが、
この0は最後に重要な極秘任務を遂行する

「ゼロの執行人」です。

今は日付に紛れ込んで、潜伏捜査中だと思ってくださいｗ

ポイント4.FLATTENで縦１列、さらに SEQUENCE + LOOKUP で指定列数折り返し

FLATTEN関数は複数列のデータを縦1列にまとめる関数です。

ポイント3までで作成した空白セルを含めた 12行 × 42列 の配列を、一旦1列のながーいデータにまとめます。

まとめる順番は、横方向優先で範囲の一番右まできたら１行下にさがります。今回作成した１行目が１月、２行目が２月という表だとちょうどいい感じで１列になります。

FLATTENの使い方については 「いきなり答える備忘録」さんが参考になります。

FLATTEN で1列にしたデータを、さらに目指すカレンダーの形である 7列折り返しにするために、LOOKUPを使います。

ここの処理は EXCELだと最新関数 TOROW からの WRAPROWSが使えそうですが、Googleスプレッドシートには無いので、その代用処理だと思ってください。

=ARRAYFORMULA(LOOKUP(SEQUENCE(12*42/7,7),SEQUENCE(12*42),
FLATTEN( ※ポイント3の式 )))

ポイント3の式はArrayformulaを除く

イメージとしては以下のような感じ。
SEQUENCEで検索キー、検索範囲をバーチャルに生成して、その場所に該当する結果範囲（FLATTENで縦並びにした日付）を引っ張ってくるわけです。

ここを細かく説明すると、それだけで終わってしまうので、簡単にまとめてしまいます。

複数列のデータ　→　1列にまとめる　　　【FLATTEN関数を使う】
1列のデータ　　 →　折り返して複数列に　【LOOKUP + SEQUENCE】
※ただし数が折り返した際にちょうどにならない場合は XLOOKUPを使用

上記の図で理解できない場合は、これだけ覚えておいてください。

今回の場合は元データを 7の倍数で用意しているので、すんなり SEQUENCEとLOOKUP に落とし込めていますが、折り返しの数で割り切れないデータ数の場合は CEILING あたりで行数を切り上げたりXLOOKUPで完全一致にするなどの調整が必要です。

今後、別の機会にこれは解説できればと思います。

LOOKUP後の状態は、ほぼ年間カレンダーになっていますね。
（表示形式を M/d にしています）

赤字の 12/30 はポイント3で仕込んだ 0 なので気にしないでください。
わかりやすくする為に赤くしているだけです。

あとは、この0と余計な空白行をやっつけていくだけです。

ポイント5.QUERYで不要な空白行を削除

今回のようなバーチャル（式内）で生成した配列の処理は、FILTER関数よりも対象列を Col1,Col2 と列番号で指定できる QUERY関数がおススメです。

空白行以外の行、つまりは最低1つは日付が入っている行を条件化する際、

「Col1～Col7 のどれかが空白ではない」
　= 「Col1～Col7 の全てが空白行である」

これをそのまま条件文にすると、かなーり長くなってしまいます。

よく考えてみましょう。今回生成した配列は年間カレンダーの月の間（変わり目）を空白にしているだけなので、両端の Col1、Col7 両方が空白で中の Col2だけ、Col3だけ値がある、なんてことはあり得ません。

つまり

Col1または Col7 が空白ではない = 空白行ではない

という条件づけで十分です。

=ARRAYFORMULA(
  Query(
    LOOKUP(
      SEQUENCE(12*42/7,7),SEQUENCE(12*42),
      FLATTEN(
        IF(
          MONTH(DATE(A1,SEQUENCE(12),SEQUENCE(1,42)-WEEKDAY(DATE(A1,SEQUENCE(12),1))+1))=SEQUENCE(12),
          DATE(A1,SEQUENCE(12),SEQUENCE(1,42)-WEEKDAY(DATE(A1,SEQUENCE(12),1))+1),
          IF(
            DATE(A1,SEQUENCE(12),SEQUENCE(1,42)-WEEKDAY(DATE(A1,SEQUENCE(12),1))+1)=DATE(A1,SEQUENCE(12)+1,1),
            0,
          )
        )
      )
    ),
    "where Col1 is not null OR Col7 is not null",0
  )
)

こんな式になりました。QUERYのところだけフォーカスすると

=ARRAYFORMULA(QUERY(※ポイント4の式,"where Col1 is not null OR Col7 is not null",0))

ポイント4の式のArrayformulaを外にだしてあげる

こんな感じになってます。

QUERYの where句で「空白ではない」 を Col1 <>'' と書く人がいます。ちなみに「ではない」（ノットイコール）の記述は、関数では通常使えない Col1 != '' も使えます。SQLチックですね。

でも、これらの記述は対象列のデータが 文字列の時のみ有効な方法です。

QUERYはデータの型に厳しいんですよね。数値や今回のような日付データの列を絞り込む時は、

Col1 <>'' や Col1 != '' だと機能しません。

数値や日付も含めた全ての型で使える Col1 is not null という表記を使った方が汎用性があります。

また、最後の 0 は「データはタイトル行を含んでいない」ことを意味しています。これを入れないと1行目をタイトル行と判断され表示形式が崩れてしまいます。

このQuery式の結果が ↓ となります。余計な行が削除されました。

あとは、この 12/30( ゼロ ) だけどうにかすればいいんですが・・・。
結局コイツはなんか役に立ったんでしょうか？

ポイント6.最後に IFで 0 を空白化（任務を終えて静かに消える）

最後に 0 の処理なんですが、これは結局何の役に立っていたのか？
↓ ココの処理なんです。

要件の1つ

「・月の最終日が土曜日だった場合、次の月が繋がってるように見えてしまうので、その際は 1行空けて翌月を表示」

これが結構難しくて、一番簡単なのが月と月の間に 1つ余計なデータを残して、QUERYで削除させないって手段だったんですよね。

その為の探り屋として日付のごとく（コードネーム：バーボンとして）、 ゼロがカレンダーに潜入していたわけです。

でも、それなら 0 じゃなくて スペースとか - とか にしておけば、そのまま放置でも目立たずに良かったんじゃね？

って思うかもしれません。

これ、実際に試したのですが、見事に潜入捜査員が見つかってしまったのです。。

実は QUERYには 列ごとのデータタイプをマジョリティーの傾向から勝手に定義する性質があります。

これは、たとえば列内の大半のデータが日付データの場合、文字列（スペース含む）が1つ2つ紛れ込んでいたりすると、勝手に除外（無視）されてしまうという恐ろしい防犯？システムなのです。

半角スペースや - を試したところ、見事に弾かれて（削除されて）しまいました。。

これを突破する為に、日付のように装える数値の 0 が適任だったわけですね。だから、ゼロなんだよ～ （福山風）。

QUERYからしたら、

「なん…だと…」

って感じですね。こちらとしては、

「一体いつから日付以外のデータが残っていないと錯覚していた？」（愛染）

と返してあげましょうｗ

さて、役目を終えたゼロには静かに消えていただくことになるのですが、
この IFの分岐が、結局 ポイント5の長いQuery式を 2回書くので、長くてオサレではない式になります。

これが、回答の1番最初に掲載した 最終形の式 ↓ というわけです。

=ARRAYFORMULA(IF(Query(LOOKUP(SEQUENCE(12*42/7,7),SEQUENCE(12*42),FLATTEN(IF(MONTH(DATE(A1,SEQUENCE(12),SEQUENCE(1,42)-WEEKDAY(DATE(A1,SEQUENCE(12),1))+1))=SEQUENCE(12),DATE(A1,SEQUENCE(12),SEQUENCE(1,42)-WEEKDAY(DATE(A1,SEQUENCE(12),1))+1),IF(DATE(A1,SEQUENCE(12),SEQUENCE(1,42)-WEEKDAY(DATE(A1,SEQUENCE(12),1))+1)=DATE(A1,SEQUENCE(12)+1,1),0,)))),"where Col1 is not null OR Col7 is not null",0)=0,,Query(LOOKUP(SEQUENCE(12*42/7,7),SEQUENCE(12*42),FLATTEN(IF(MONTH(DATE(A1,SEQUENCE(12),SEQUENCE(1,42)-WEEKDAY(DATE(A1,SEQUENCE(12),1))+1))=SEQUENCE(12),DATE(A1,SEQUENCE(12),SEQUENCE(1,42)-WEEKDAY(DATE(A1,SEQUENCE(12),1))+1),IF(DATE(A1,SEQUENCE(12),SEQUENCE(1,42)-WEEKDAY(DATE(A1,SEQUENCE(12),1))+1)=DATE(A1,SEQUENCE(12)+1,1),0,)))),"where Col1 is not null OR Col7 is not null",0)))

簡略化すると以下のようになります。

=ARRAYFORMULA(IF(ポイント5の式 =0, ,ポイント5の式)

Arrayformulaは一番外側でまとめて1回でよい

こう書くとシンプルに見えるんですが・・・。

0なら空白を返し、それ以外ならポイント5の式の結果を返す、
そして 全てを包み込む ARRAYFORMULA って感じですね。

長い過程を経て、ようやく実務で使えそうな年間カレンダー生成が1行数式で出来ました。（式が長すぎて使わなそうだけど・・・）

全3回にわたって、1行数式で作るカレンダーを特集してきました。

最終的には、1行数式と呼ぶにはかなり無理がある式になりましたが、途中計算用の作業セルや式のコピペ、オートフィルなしで、本当に 1つのセルに式をいれるだけで、複雑な条件の年間カレンダーが生成できました。

式の内容は完全に理解できなくても、関数の組み合わせだけでこんなことも出来るんだ！って部分に魅力と可能性を感じていただければ良いかなと。

カレンダー関連のネタは、GASでGoogleカレンダーと連携する方法を紹介したサイトが多いですが、シート内だけでも API関数を使った祝日の自動取得や関数のみで和暦変換、LAMBDA式を使って予定リスト表からカレンダー生成、など、まだまだ面白いネタがあります。

この後の番外編で、もしくは別の機会に書いていきたいと思います。

次回はシリーズ合間の小休止ってことで、今回の1行数式カレンダーの番外編。この長い式をLAMBDAで簡略化とタイトル行の曜日の部分も含める方法、名前付き関数化、条件付き書式で土日祝の色付けなどを補足。

より実用的なカレンダーにしていきます。

■このシリーズの次の記事

いいなと思ったら応援しよう！

チップ大歓迎です。やる気がアップしますｗ