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

2022年10月5日 16:25

GASが絡んでくるとややハードル上がって重めのネタになっちゃうので、シート関数のみで実現できる小ネタも挟んでいきましょう。今回は関数でカレンダー生成です。結局は書いてたら 1回じゃ収まらなくて全3回なんですが・・・。

ちなみに 1行数式 という言い方ですが、1つのセルにだけ式を入れるという意味合いです。

Q1. A1セルに年を入れたら、A2以降にその年の日付（カレンダー）を展開させたい

とりあえずは簡単なお題からいきましょう。

縦並びに表示させる年間カレンダーです。もちろん1行数式なので、A2セルにのみ関数を入れることが前提です。あとは、なるべくシンプルな記述の方がカッチョイイですよね。

まずは自分でお題に対する答えを考えてみましょう。

Googleスプレッドシートは、いつでもどこでも、パソコンなくても使えるんで、特に関数系ならサクッと検証できるのも魅力です。

↓ここから回答です。

A1.1行数式で作る縦1列の年間カレンダー

複数のアプローチがありますが、以下の式が一番シンプルじゃないでしょうか？

式を入れて動かしてみよう

=SEQUENCE(337+DAY(DATE(A1,3,0)),1,DATE(A1,1,1))

解説の前に動かしてみましょう。展開されるA2以降は、事前に表示形式を「日付」としておく必要があります。

画像だと「3行じゃん！」ってツッコまれそうですが・・・。

安心してください。1行ですよ！
画像で見やすくするために数式内で改行を入れてるだけです。

年の部分が変わるだけなので動きが地味ですが、「うるう年」の2020年の場合は、しっかり1行増えて 366日となっているのがわかります。

今回の式のポイント

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

日付データはシリアル値という数値であることを理解する
連番を配列で返す SEQUENCE関数
DATE関数の引数を制する

ポイント1.日付データはシリアル値という数値であることを理解する

まず、ここが理解できていないとスプレッドシート上で日付を扱えません。
恐らくはきちんと理解していなくても、

A1 に 2022/09/01 って日付が入っていたら、
下のセル A2 に = A1+1 で 2022/09/02 (翌日) となる。

こんな処理は、なんか使ったことがあるって人も多いんじゃないでしょうか？

Googleスプレッドシートの場合は、数値 1を 1899/12/31 としたシリアル値を利用しています。わざわざ 「Googleスプレッドシートの場合は」と言ってるのは、Excelだと 1を 1900/01/01 と定義しているからです。

1日経過を+1としており、1日より小さい単位、
たとえば 1時間だと　1/24 = 0.04166….　← （1÷24）
1 を24時間で割った値、つまり1以下の少数になってきます。

でも、開始の 1に対応する日付が ExcelとGoogleスプレッドシートで違うなら、本日（記事作成の日） 2022/09/21 のシリアル値は ExcelとGoogleスプレッドシートでズレてるの？

と疑問に思うかもしれませんが、

これは同じ 2022/09/21　=　44825　なんですねー。
※ ちなみに上記は　DATEVALUE関数を使ってますが、表示形式を「数値」にかえれば日付はシリアル値になります。

スタートが違うのに1ずつ増えていったら、なぜか同じに？？

「時を飛ばした、だと！？」

キングクリムゾンを発動させたわけではありません。
これは、Excel側には 疑惑の1900年うるう年が存在するからです。

その辺りは、かなりこぼれ話になってくるのでここでは割愛します。

興味がある人は以下のようなまとめてくれてる方のサイトをお読みください。

とりあえずは、日付(カレンダー）が連番である という理解があれば、
ポイント2で SEQUECEという関数を使う理由がわかるはずです。

ポイント2.連番を作る SEQUENCE関数

SEQUENCEは連番を生成する関数で、いわゆる自動で他のセルに展開される（スピる）系の関数です。

初心者向けの部分、SEQUENCEとは？ DATE関数とは？といった単体の関数の基本的な理解については割愛してます。ご了承ください。その部分は公式ページなり、他に解説してるサイトなりで学べますので。

=SEQUENCE(337+DAY(DATE(A1,3,0)),1,DATE(A1,1,1))

ポイントの2つ目が、よりシンプルな式にする為に ARRAYFORMULA や ROWを使わずにSEQUENCE のみで展開部分を処理している点です。

個人的に SEQUECE は好きな関数のベスト10に入るレベルの推しカン（推しの関数）で、これだけで全3回くらい記事書けちゃうくらいネタがありますｗ

SEQUENCEの設定を

行数　　・・・　その年の日数
列数　　・・・　1
開始値　・・・　その年の 1月1日

とすれば良いだけ。

極論を言ってしまえば、年間カレンダー作成は、
=SEQUENCE(366,1,DATE(A1,1,1))
でも良いんです。（うるう年を考慮して 366としてます）

これでA1の年の 1月1日から自動で 366日分下に展開されます。
（Excel風に言うと スピります）

ただし上記だと、うるう年ではない（365日）の普通の年は、翌年の 1月1日まで表示されちゃいます。これをIFで消し込んでもいんですが、そうすると結局 ARRAYFORMULAも使うことになるし、ちょっとカッコ悪いですよね。

ポイント3.DATE関数の引数を制する

この日数変動を制御するのがポイントの3つ目、「うるう年」対応の部分です。

337 + DAY( DATE( A1 , 3 , 0 ) )

この式の 337ってなんだ？？って感じですが、
この部分は元々

365 + ( DAY( DATE( A1 , 3 , 0 ) ) - 28 )

という式の、計算できる数字部分を 365-28 = 337 とまとめたものです。
※今回はいかに短くするかを追求してますが、この手の計算は実際はまとめない方が良いです。

では、　365 + ( DAY( DATE( A1 , 3 , 0 ) ) - 28 )　は何を現わしているのか？

365は基本となる 1年の日数 365日なので、それ以外の部分を見ていきましょう。

式が入れ子になっている場合は内側から見ていくのが基本です。

まず、DATE( A1 , 3 , 0 )　は、A1の年の3月0日、つまりは 3月1日の1日前、うるう年に影響を与える 2月の最終日を返します。

月末を取得する関数は EOMONTH があるので、これを使っても良いのですが、年の数値から 2月末を取得しようとすると、

EOMONTH( DATE( A1, 2, 1), 0 )　とか
EOMONTH( DATE( A1, 1, 1), 1 )　って式になるんで、

結局DATE使うを絡ませる必要があるんで、長くなっちゃいます。

DATE関数の 3番目の引数 は日付を表す数値を入れる箇所なんですが、実はここは意外と自由で、1～31 に縛られることなく今回のように 0だったり、マイナスの数値だったり、100だったりを入れてもいいんです。

ちゃんと年、月が連動した結果になります。日付のシリアル値を返すんで当然ちゃ当然なんですが便利ですね。

うるう年は、2月の日数が1日増えるので、

A1が 2022（普通の年）なら　DATE( A1 , 3 , 0 )　→　2022/02/28
A1が 2020（うるう年）なら　DATE( A1 , 3 , 0 )　→　2020/02/29

となります。

ここから、 DAY関数で日にちの部分の数値、上のケースであれば 28 と29 だけ取り出します。

この数値をうるう年ではない普通の年の 2月の最終日の数値 28 でマイナスすることで、

A1が 2022（普通の年）なら　( DAY( DATE( A1 , 3 , 0 ) ) - 28 )　→　0
A1が 2020（うるう年）なら　( DAY( DATE( A1 , 3 , 0 ) ) - 28 )　→　1

となります。この結果を加算することで、うるう年の際の 365日 → 366日 を切り替えているのです。

別解もあるよ

今回の縦1列の年間カレンダーは、ポイント2で解説した DATE関数の特性をいかして、DATE の日の部分の第3引数をSEQUENCEにするという方法でも実現できます。

=ARRAYFORMULA(DATE(A1,1,SEQUENCE(337+DAY(DATE(A1,3,0)))))

この場合、DATE関数は引数に配列をとれない為、ARRAYFORMULAを組み合わせる必要があります。

式は少し長くなりますが、こちらだと セルの書式設定を日付とする準備が不要で、自動で日付表記で結果を返してくれるという利点があります。

Excel（スピル対応）の場合は Arrayformulaなしで展開されるので、
=DATE(A1,1,SEQUENCE(337+DAY(DATE(A1,3,0))))
こっちの方がシンプルで良いかもしれません。

応用編：タテ1列の1ヵ月カレンダーの生成

では、年と月を指定した 縦1列の月カレンダーの場合はどうでしょうか？

年間カレンダーと考え方は一緒です。
SEQUENCEで、

行数　　・・・　その月の日数
列数　　・・・　1
開始値　・・・　その月の1日

とするだけです。

A1セルに年、B1セルに月、の数値が入ってるとしたら、月末の日付を DATE(A1,B1+1,0) で取得、そこからDAYで日数を取得すればよいです。

=SEQUENCE(DAY(DATE(A1,B1+1,0)),1,DATE(A1,B1,1))

これは難しくないですよね？

1行数式カレンダーシリーズの初回だったので、我ながら親切めな解説ｗそのせいでシンプルな縦1列のカレンダーなのに長くなってしまった。。どこまで解説したらいいか難しいですね。

1行数式による縦1列の年間カレンダー作成は、理解できたでしょうか？

ただ、このシンプルな縦並びカレンダーなら、

A2 に =DATE(A1,1,1)
A3 に =A2+1

と入れて、
下にオートフィルでばばーっとやって、バーン（関西おっちゃん風）
でも良いわけで、あまり式を組む価値がありません。

Q2.A1セルに年を入れたら、日曜始まり土曜終わりの 7日間ずつ表示させる年間カレンダーを生成したい。

せっかくなら、横方向に日曜始まり土曜終わりの 7日間ずつ折り返して表示させるカレンダーを 1行数式で実現したいと思います。

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

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

この記事が気に入ったらサポートをしてみませんか？