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

2022年10月12日 18:40

前回は縦1列のシンプルなカレンダーを 1行数式で作成しましたが、今回はもう少し実用的なカレンダー作成に挑戦してみましょう。

前回の記事

Q2. A1セルに年を入れたら、日曜始まり7列の以下のような年間カレンダーを展開させたい

今回も 1行数式（1つのセルにだけ数式を入れる）で、実現していきます。
上のキャプチャだと式を入れるセルは B2ですね。

1/31の隣にすぐ 2/1 がきてるのが気になりますが、とりあえずは月の切り目は繋がっていても問題ないこととしましょう。（その方が簡単なので）

前年や翌年が表示されちゃう箇所は、条件付き書式でグレーにしてもいいんですが、今回は数式側で消すようにしましょう。

どうでしょう？式は作れそうでしょうか？

↓ここから回答です。

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

そんなに難しくないので、今回はいきなり答える 方式です。
まずはシンプルな式を作りましょう。

回答1：まずは年の変わり目を気しない式を作成

=SEQUENCE(53,7,DATE(A1,1,1)-WEEKDAY(DATE(A1,1,1))+1)

とりあえずは、これでOKです。

中身の数値や式については、後で解説していきます。
解説の前に動かしてみましょう。

前回同様、展開されるセル範囲は、事前に表示形式を「日付」としておく必要があります。年がつくと見づらいので、カスタムで "月/日" とするのがよいでしょう。

ちゃんとに 2021年1月1日が金曜日から開始のカレンダーになっています。

でも前年の 12月末の日付が表示されちゃってますね。画像では見えないですが、一番下の最終行には翌年の 2022年1月の日付も表示されちゃっています。

条件付き書式を使って今年以外の部分を表示させない（白文字にする）、もしくはカレンダーっぽく薄いグレー文字にすることも出来ますが、今回は数式側で処理をしたいので、上記の式をもう一工夫する必要があります。

回答2：IFで条件分岐で前後の年の日付を消す【完成版】

=ARRAYFORMULA(IF(YEAR(SEQUENCE(53,7,DATE(A1,1,1)-WEEKDAY(DATE(A1,1,1))+1))=A1,
  SEQUENCE(53,7,DATE(A1,1,1)-WEEKDAY(DATE(A1,1,1))+1),))

うーん、今回も 1行じゃない！って言われそうですが、見やすいように折り返してるだけで 1行で書けてます。

こちらで試してみると。きちんとその年だけが表示されました！
ミッションコンプリートです。

処理としては単純で、生成される個々の日付から YEAR関数で年を取り出して IFで A1（指定した年）と一致した時だけそのまま日付を返し、一致しない場合は空欄とするだけ。

でも、シート関数は（Lambdaを使わないと）変数として宣言ができない のが悩ましいですね。同じ内容の繰り返し部分、今回だと

SEQUENCE(53,7,DATE(A1,1,1)-WEEKDAY(DATE(A1,1,1))+1))

が2回出てきて煩雑な式になっちゃっていますね。
複雑な式を作ると、これが多々発生します。

もちろん今はLAMBDAで短縮化できます。
最後に LAMBD化した（ラムった）場合の式も書いておきます。

今回の式のポイント

今回の式を解説していきましょう。ポイントは2つです。

今回も SEQUENCE関数（行、列に展開）
WEEKDAY関数で曜日を数値で取得し開始日を調整

ポイント1.今回も SEQUENCE関数（行、列に展開）

mir の推しカン （推してる関数）の SEQUENCE は、以下のような4つの引数をとります。

SEQUENCE(行数, 列数, 開始値, 増分量)

Googleヘルプより

第2引数以降は省略可で、省略した場合は 1という扱い。つまりは

SEQUENCE(366)　=>　SEQUENCE(366,1,1,1)
※1から開始する下に 1ずつ増えていく 366行1列の配列を返す

という意味合いです。

今回の式
SEQUENCE( 53 , 7 , DATE(A1,1,1)-WEEKDAY(DATE(A1,1,1))+1 ) は、

53　・・・　1年間の週の数　 = 行数（縦方向への展開）
7　・・・　1週間の日数 = 列数（横方向への展開）
DATE(A1,1,1)-WEEKDAY(DATE(A1,1,1))+1 = 開始値
増分量は省略しているので 1

を意味しています。これによって1つの式の結果を 縦横に展開させているわけです。

ちなみにSEQUENCEの連番は、横方向に増えて下に折り返す動きをします。
だからカレンダー作成にはとても適しているのです。

ポイント2.WEEKDAY関数で曜日を数値で取得（開始日調整）

もう一つのポイントが 開始日（開始値）の調整です。

以下の部分ですね。

DATE(A1,1,1)-WEEKDAY(DATE(A1,1,1))+1

今回は「ひとつなぎ」（月の切れ目を意識しない）カレンダーなので最初の開始日（開始値）、つまりは B2セルに入る日付を 指定した年の 1月1日以前の最初の日曜日 にすればよいのです。

この開始日の調整を

-WEEKDAY(DATE(A1,1,1))+1

↑ この式で処理しています。

WEEKDAY関数は、日付に対して、その曜日に該当する数値を返す関数です。

標準だと

日、月、火、水、木、金、土
1、 2、 3、 4、 5、 6、 7

という扱いになります。

-WEEKDAY(DATE(A1,1,1))+1

今回 A1の年の 1月1日 DATE(A1,1,1) の曜日を WEEKDAY関数で数値化して開始値を調整したい。つまり 1/1 が日曜日なら調整なし = 0 となればOKってことで、最後に +1 をしています。

これで仮に 1/1 が月曜日なら
WEEKDAY(DATE(A1,1,1)) は 2となり、
-WEEKDAY(DATE(A1,1,1))+1 は、-2 +1
つまり　-1 となるので、開始値は

DATE(A1,1,1)-1　→　 1/1の一つ前　→　前年の12/31

これが その年の 1/1以前の直近の日曜日（開始日）となります。
この開始日調整によって、正しい曜日（列）に日付が入るようになるわけです。

イメージできたでしょうか？

とりあえず、1年間通し表示の日曜始まり７列の年間カレンダーは、１行数式で実現出来ました。

1行数式年カレンダー今回の回答の応用例

今回は比較的簡単なお題だったので、回答の応用例も紹介しておきましょう。

応用編：LAMBDA化してみる（ラムってみる）

=ARRAYFORMULA(IF(YEAR(SEQUENCE(53,7,DATE(A1,1,1)-WEEKDAY(DATE(A1,1,1))+1))=A1,
  SEQUENCE(53,7,DATE(A1,1,1)-WEEKDAY(DATE(A1,1,1))+1),))

この式を LAMBDA化して（ラムって）整理しましょう。

上記の式では

SEQUENCE(53,7,DATE(A1,1,1)-WEEKDAY(DATE(A1,1,1))+1

という長い式が2回出てきます。
今回はお試しラムで、ここだけラムります。

この部分を仮に x と置く、という考え方が LAMBDA化の基本です。
つまり

x = SEQUENCE(53,7,DATE(A1,1,1)-WEEKDAY(DATE(A1,1,1))+1 とすると、
=ARRAYFORMULA(IF(YEAR(x)=A1,x,))

こうすればよいってことです。
これを式内で記述出来るのが LAMBDAという関数の特徴です。

↓ LAMBDA化回答

=LAMBDA(x,ARRAYFORMULA(IF(YEAR(x)=A1,x,)))(SEQUENCE(53,7,DATE(A1,1,1)-WEEKDAY(DATE(A1,1,1))+1))

2回登場する DATE(A1,1,1) をさらにラムる（LAMBDAをネストする）こともできますが、本題から逸れるので今回はここまでにしておきましょう。

Googleスプレッドシートに 2022年9月に追加された 新関数 LAMBDAについて詳しく知りたい方は、LAMBDA関数のチャラい解説 を参考に。

応用編：１行数式月カレンダー

年間カレンダーを応用すれば、１行数式で月カレンダー作成も可能です。
こっちの月カレンダーの方がシンプルだし需要が高いかも。

//A1セルに年、C1セルに月 の数字があるとして

=ARRAYFORMULA(IF(MONTH(DATE(A1,C1,SEQUENCE(6,7)-WEEKDAY(DATE(A1,C1,1))+1))=C1,
DATE(A1,C1,SEQUENCE(6,7)-WEEKDAY(DATE(A1,C1,1))+1),))

同様に、常に本日の月カレンダーを表示させたいなら、TODAY()関数を使った以下の式になります。
どのセルも参照しないから、完全にコピペで使えますね。

//当月のみ表示する式
=ARRAYFORMULA(IF(MONTH(EOMONTH(TODAY(),-1)-WEEKDAY(EOMONTH(TODAY(),-1))+SEQUENCE(6,7))=MONTH(TODAY()),
EOMONTH(TODAY(),-1)-WEEKDAY(EOMONTH(TODAY(),-1))+SEQUENCE(6,7),))

//月カレンダーなら前月、翌月は数式側ではそのままで、条件付き書式でグレー文字にするとかで良いかも
=ARRAYFORMULA(EOMONTH(TODAY(),-1)-WEEKDAY(EOMONTH(TODAY(),-1))+SEQUENCE(6,7))

A2に式を入れたら条件付き書式で =MONTH(A2)<>MONTH(TODAY())

応用例の紹介でした。

LAMBDAの練習をしたい人は、月カレンダーの方を自分でラムってみましょう。

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

年間カレンダー、そして応用編の月カレンダーを1行数式で作成しました。

しかし、この年間カレンダーだと月の切れ目がなくて見ずらい！という意見が出るかと思います。

「カレンダーっぽく、月で区切って見やすくしたい！」
当然こういう要望がでますよね。

最終段階として、これを B2セルへの1行数式で実現させましょう。
上の画像のようなイメージです。

が、長くなってしまったので、またまた それは次回で。

ここから難易度が３段階くらい上がります。ギア４（フォース）くらいです。

来週の記事投降まで期間があるので、関数ヲタクの方は是非自力でお試しください。

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

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

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

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

Q2. A1セルに年を入れたら、日曜始まり7列の以下のような 年間カレンダー を展開させたい