【番外編】Googleスプレッドシート年間カレンダー (LAMBDAってみよう)

mir

2022年10月26日 18:33

前回までの記事では、Googleスプレッドシートで 1行数式でカレンダーを作成する方法を全3回にわたって書いてきました。

最終的に理想的なカレンダーが1行数式で実現できたのですが、式は 1行数式と呼ぶにはあまりに長い・長すぎる式になってしまったかなと。

これを今回、最新関数 LAMBDA を使って簡略化したい（ラムってみたい）という主旨の「番外編」になります。

後半は曜日のタイトル行部分を含める方法や、名前付き関数の作成にもチャレンジしています。

LAMBDA について基本を知りたい方は、新関数の動向記事で「チャラい解説」をしてますので、そちらをご確認ください。

Q1.年間カレンダーを作成する長い数式を簡略化させたい

簡略化させたい式（インデントつきバージョン）は、前回作成した以下になります。

=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
    )
  )
)

この長い式で、A1セルに入っている 4桁数字の年の日曜始まり7日間で折り返す年間カレンダーが生成されます。

便利だけど、あまりに式が長すぎるのでこれを簡略化させたい！

というのが今回の番外編のお題です。

LAMBDAをフル活用するので、LAMBDAの練習にもなりますね！

どうでしょう？ラムれそうでしょうか？

↓ここから回答です。

A1.LAMBDAで式を簡略化する

LAMBDAで式を簡略化するポイントはチャラい解説でも登場した以下の通り。

1. 変数（都度変わる範囲や値）は全て引数にする
2. 2回以上登場する数式は引数化する
3. 引数はなるべく絞り込んで少なくする
4. 複雑な式は LAMBDAをネスト（入れ子）して使う
5. エラーが解消できない時はエディタにコピペでインデントつける

今回のカレンダー数式の場合は、「変数（都度変わる範囲や値）」は、年の数字4桁を入力する想定の A1のみですね。

「2回以上登場する数式」 は、まさに今回の場合は 式を長くしている元凶なので、先にこれから手を付けていきましょう。

というわけで、処理の手順としては以下で進めます。

重複して登場する式を LAMBDAでまとめる
LAMBDAを入れ子にする（ポイントは内から外へ）
変動する引数を一番外側（後ろ）に出す

特殊な関数を組み合わせて数式を作るような閃きはいりません。
コツコツ地道に処理していくだけです。

1. 重複して登場する式を LAMBDAでまとめる

まずは重複して登場する式をまとめていきましょう。

実はこの長い式、前回の記事を読んでる方ならわかりますが、まったく同じやたら長いQuery式が2回登場しています。

簡潔に書くと

=ARRAYFORMULA( IF( くそ長いQueryの式 = 0, ,くそ長いQueryの式 )

こうなってるわけです。

だから、LAMBDA化する場合は、

=ARRAYFORMULA( LAMBDA( x, IF( x = 0, , x ) )( くそ長いQueryの式 ) )

まずはこんな風にしてあげればよいです。
適当にくそ長いQuery式を xと置きました。

ここで、ARRAYFORMULAの位置には注意です。

ARRAYFORMULAのスピル効果を「くそ長いQuery式」にも効かせる為に、LAMBDA式全体をARRAYFOMRULAの中に入れてあげる必要があります。

実際の式で記述すると

=ARRAYFORMULA(
  LAMBDA(x,
    IF(x=0,,x)
  )
  (
    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文字 → 437文字

でもまだ 437文字、24行あります。
ここからさらに重複している式を拾っていきます。

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

という結構長い式が 3ヵ所に登場しています。3つも一気にまとめられるので、お得感がありますね。

お笑い漫画道場で富永一郎先生が、「寿司をネタにして面白ければ、その寿司が食える」というコーナーで「トロとエビ」みたいに2つのネタを使って漫画を描き両方を食べるという偉業を成し遂げていましたが、それくらいお得感がありますｗ

ただ、この式って先ほど外に出した（LAMBDA でｘとした）QUERY式内にありますが、こんな時はどうすれば良いのでしょうか？

2. LAMBDAを入れ子にする（ポイントは内から外へ）

ここで LAMBDA化の際のポイント、内から外へが重要になります。

3回登場のターゲットの式に着目して、式の状態を簡略化すると、

=ARRAYFORMULA(
LAMBDA( x, IF( x = 0, , x ) )
( Query(…
IF(MONTH(ターゲットの式) = SEQUENCEZ(12),ターゲットの式,
IF( ターゲットの式 = DATE(A1,SEQUENCE(12)+1,1), 0) )
) )

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

う・・・、今更ながら IFの入れ子が煩雑ですね。文字数はあまり変わりませんが、ここを先に IFSにしちゃいましょう。

=ARRAYFORMULA(
LAMBDA( x, IF( x = 0, , x ) )
( Query(…
IFS(MONTH(ターゲットの式) = SEQUENCEZ(12),ターゲットの式,
　ターゲットの式 = DATE(A1,SEQUENCE(12)+1,1), 0,
true,)
) )

IFS式でどれにも該当しない場合 = その他を設定するには、一番最後に

IFS(条件A, 結果A, 条件B, 結果B … true, その他の時の結果）

こんな感じにしてあげれば良いです。

これをラムる（LAMBDA化する）には、xの LAMBDA式をまるっと包み込むように一つ外側にLAMBDA式を作ってあげればOK。

料理でいうと、パイ生地でスズキの旨味を閉じ込めるみたいな感じです。

=ARRAYFORMULA(
　LAMBDA( z,
　　LAMBDA( x, IF( x = 0, , x ) )
　　( Query(…
　　　IFS(MONTH(z) = SEQUENCEZ(12), z,
　　　　　z = DATE(A1,SEQUENCE(12)+1,1), 0,
　　　　　true,))
　　)
　)(ターゲットの式)
)

だんだん、カッコ / カッコ閉じの整合性がわからなくなってくるので注意です。（エディタで作ると便利）

xの次に なんで zを使ってるかというと、あとで年として y を使う為です。
これによって

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

インデントがあるので行数はあまり減ってませんが、ボリュームはぐっと減りました。

781文字 → 437文字 → 311文字。いい感じですね。

ここから最後のLAMBDAで、変動する A1を外だしします。

3. 変動する引数を一番外側（後ろ）に出す

最後に変動する A1を外に出すのに合わせて、何度か登場する SEQUENCE(12)も外に出しちゃいましょう。それぞれ意味するものから

A1 　　　　　　・・・年を表す4桁の数値なので　y　とする
SEQUENCE(12)　・・・　12ヵ月（それぞれの月）なので　m とする

この2つは Arrayformulaを効かせる必要がないので、Arrayformulaの外側でラムれます（LAMBDA出来ます）。

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

これくらいならインデント無しでもいいかもしれません。

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

「なんということでしょう～」

っていうくらいのビフォー・アフターっぷりですね～。
ラムってますね～。（スピってるに対抗して使ってるけど流行らなそう）

LAMBDA前の式 781文字
↓
最終 LAMBDA後 275文字。

65%削減に成功です。省エネ大賞受賞できそう。

iPhone が登場した時によくネットで見かけた

これを思い出しましたｗ LAMBDAイノベーションですね。

LAMBDAによる式の簡略化クリアです。

Q2. 曜日の行も式に入れ込みたい

人間の欲は満たされないもので、LAMBDAで式が短くなるとこんな要望も出てくるんですね。

カレンダーの一番上の 曜日部分 ↓

これも式に入れ込めないか？ってことです。

わざわざ式に入れなくてもと思いますが・・・。
まぁ式にするのは割と簡単ですね。

↓ここから回答です。

A2. 曜日の行を1行カレンダーの式に入れ込む

式を作る前に、まず大前提として

曜日はオートフィルできる

これを覚えておきましょう。

曜日の漢字 1文字、もしくは 〇曜日という3文字であれば、1つ入力してオートフィルで曜日が埋まります。

日でなく、火や金といった他の曜日をオートフィルしてもOK。
ただし、日曜や火曜ではダメです。日曜日、火曜日ならOK。

これでいけるんだから、手入力でもよさそうなもんですが。。

曜日はTEXT関数で生成できる

関数で作る場合はどうするか？

これはご存じの方も多いと思いますが、日付から曜日を抽出したい場合は、TEXT関数を使います。

=TEXT(A1,"ddd")

日付に対して書式フォーマットを "ddd"と指定してあげればOK。
これで 曜日を表す漢字1文字（上の場合は「土」）が返ります。

ここで "dddd" としてあげれば、漢字3文字になります。（上の場合は「土曜日」）

Excelとちょっと違うというのが間違いやすいポイント で、Excelだと曜日の漢字1文字は "aaa"なんですよね。

この感覚で Googleスプレッドシートで =TEXT(A1,"aaa")　
としても、そのまま aaa が返るだけです。

ちなみにExcelで =TEXT(A1,"ddd") と入れると 英語での曜日表記の簡略系となります。(土曜日の場合は Sat ）

ddd の方が世界標準で、aaa はExcelがローカル言語用に用意したってことなのかも。

で、面白いのが、

=TEXT(1,"ddd")

こうすると 日（日曜日） が返るんです。

シリアル値の 1 は Googleスプレドシートだと 1899/12/31 である。

これは、カレンダーシリーズの初回で触れましたが、狙って設定したのかこの日がちょうど日曜日 になってるんですね。

というわけで日～土を関数で表示させるには、数値の 1~7 をTEXT関数で曜日にすればいいわけです。

1～7の連番とうことは、また SEQUENCE関数の出番ですね！

=ARRAYFORMULA(TEXT(SEQUENCE(1,7),"ddd"))

横方向に展開するので、SEQUENCE(1,7)としています。
また、配列に対しての処理なので ARRAYFORMULAをつける必要があります。

あとは、これを LAMBDA化した年間カレンダー式と合体させるだけです。

数式の結果も配列結合できる

とは言え 数式同士の合体って・・・、しかも複雑なLAMBDA式とどうやって合体するの？？

※どっちがタチでどっちがネコ？という話ではありませんｗ

あまり難しく考える必要はありません。今回の場合は、単純にそれぞれの式の結果を縦に重ねるだけなので、セル範囲や配列の結合を応用できます。

シンプルに言えば、

={曜日を出力する式 ; LAMBDAの年間カレンダー式 }

これでOKなのです。数式から出力される結果でも結合できるんですねー。

注意点としては結合面のサイズが揃っていないとエラーになること。

今回の場合はタテに結合するので、列数が同じである必要がありますが、どちらの式も 7列で固定なので問題ないです。

ただ、そのまま結合すると両方に Arrayformulaがあって 美しくないんで、一工夫しましょう。

ついでに y (年の部分）が空だったり、通常の年を表す4桁の数値でない場合は空白を返すように if文も追加してエラー処理をしておきましょう。

というわけで、前回の完成系を超えた アルティメット年間カレンダー数式です。

=LAMBDA(m,y,
  ARRAYFORMULA(
    IF(OR(y<1900,y>2200),,
      {
        TEXT(SEQUENCE(1,7),"ddd");
        LAMBDA(z,
          LAMBDA(x,
            IF(x=0,,x)
          )(
            Query(
              LOOKUP(
                SEQUENCE(12*42/7,7),SEQUENCE(12*42),
                FLATTEN(IFS(MONTH(z)=m,z, z=DATE(y,m+1,1),0, true,))
              ),"where Col1 is not null OR Col7 is not null",0
            )
          )
        )(DATE(y,m,SEQUENCE(1,42)-WEEKDAY(DATE(y,m,1))+1))
      }
    )
  )
)(SEQUENCE(12),A1)

IF(OR(y<1900,y>2200),, の記述で、1900年から2200年までをカレンダー出力の対象としています。それ以外の数値や文字列、空白の場合は空白を返します。

また、曜日出力の式は Arrayformula内のこのIF文の中に入れることで、1つのArrayformulaで曜日タイトル行の式、カレンダー数式両方をスピらせています。

ただし、個人的にはこの式は 2つの数式を連結させた式なんで、ちょっと1行数式と呼ぶには抵抗があります。

これを1行数式と言っちゃうと、なんでもアリになっちゃうなと。
それが前回までが本編、今回を番外編としている理由です。

このまま試してもいいですが、このレベルの数式はせっかくなんで「名前付き関数」にしてみましょう。

転スラでも ネームドになったらレベルが上がりますしｗ

カレンダー数式を名前付き関数にしてみる

名前付き関数の作成手順

新機能、名前付き関数はもう試したでしょうか？
シート内で使えるオリジナル自作関数が、GASに頼らず比較的簡単に作れます。

今回のカレンダー関数を例に試してみましょう。

■名前付き関数作成の流れ

メニューの データから 名前付き関数 を選択
サイドバーが表示されるので、下部の 新しい関数を追加 を選択
関数名を設定（アルファベット大文字とアンダーバー）
引数のプレースホルダーを設定（ ye とする）
数式の定義にカレンダー式をコピペで貼り付け。
今回は式の A1のところだけ 設定した ye に修正
次へ → 作成 で完成。
サイドバーに作成した関数が表示される

関数名は、

ULTIMATE_ETERNALL_FULLYEAR_CALENDAR
（アルティメットエターナルフルイヤーカレンダー）

にしようかと思いましたが、あまりにイタすぎるのと、せっかく式を短くしたのに関数名が長くちゃ意味ないだろ！ってことで、

FULLYEAR_CAL

としました。

引数の「プレースホルダー」というのは、変動する箇所って意味合いです。要は引数としてセル指定したり、数値入れたりする箇所をなにかしらでおいてあげるってことですね。

今回の場合は A1となっている箇所が対象となります。y はLAMBDA式内で使っちゃったので、 ye と置きました。

ここで、どうせ名前付き関数にするなら LAMBDAで短縮しなくても良かったんじゃ？って思うかもしれませんが、LAMBDAで整理しておいた方が後々メンテナンスがしやすいというメリットがありますし、記載ミスも防げます。

個人的には、変動する引数を外（一番後ろ）に出して整理してからネームドファンクションにすることをお勧めします。

上記画像以外の残りの項目は、他の人に使ってもらう際の説明を記載する箇所なので今回は飛ばします。

とりあえず、これだけ設定すれば使える状態になります。

これで完成。使ってみましょう。

名前付き関数として使ってみる

事前に表示形式を設定したり、セル幅調整をしておく必要はありますが、 = FULLまで入れれば 関数候補に FULLYEAR_CALが登場するのでそれを選択すればOK。

あとは引数の年だけ直接数字4桁を入力するか、セル参照すれば完成です。

=FULLYEAR_CAL(2022)

この式だけで 2022年の年間カレンダーが表示されました。

年を変えれば延々使えるし、汎用性のある自作関数になったんじゃないでしょうか？

番外編とはいえ、1行数式で作るカレンダーは今回で 真の完成形になったといえるかもしれません。

このクラスの数式なら、名前付き関数を使う価値もあるでしょう。

式や解説はよくわからんけど・・・って人もコピペで使える式なんで、まずは自分で試して、動かして Googleスプレッドシートの可能性を体感してもらえればと思います。

次回はもう1回カレンダーシリーズの番外編です。

今回のカレンダーの見栄えをよくするために、数式ではカバーできない土日祝の自動色付けを通じて、 条件付き書式 の使い方や APIを活用するシート関数について書いていきます。

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

■LET関数、配列新関数でさらに1行数式カレンダーをシンプルにしようの記事

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