【応用編】Googleスプレッドシート 11新輸入関数【カレンダー表示で LET活用】

2023年4月2日 12:48

2023年2月に追加（Excelから輸入）された、Googleスプレッドシートの新関数。

一時、使えなくなる等のトラブルもありましたが、現在は安定して活用できており、mir も早速いろいろな場面で活用しています。

TOROW：配列またはセル範囲を1つの行に変換
TOCOL：配列またはセル範囲を1つの列に変換
CHOOSEROWS：配列から指定した列を選び、新しい配列を生成
CHOOSECOLS：配列から指定した行を選び、新しい配列を生成
WRAPROWS：配列を指定した列数で折り返して行にし、新しい配列を生成
WRAPCOLS：配列を指定した行数で折り返して列にし、新しい配列を生成
VSTACK：配列の指定した複数の範囲を縦に積み、より大きな配列を返す
HSTACK：配列の指定した複数の範囲を横に並べ、より大きな配列を返す

LET：計算結果に名前を付け、スコープ内で利用する

今回は、これらの関数をフル活用する実践例、応用例をとりあげていきましょう。

シリーズ前回の記事

知恵袋回答で使った新関数例

先週の 1行カレンダーでのLET置き換えお題の前に、実際に Yahoo知恵袋で回答した式から新関数の活用例を幾つか紹介しておきましょう。

LET,TOROWの空白除去活用例

=LET(a,A3:A,title,B2:D2,TOCOL(ARRAYFORMULA(IF(((a<>"")*(a<>offset(a,-1,0)))*(offset(a,0,1,,3)="〇"),title,)),3))

LET, CHOOSECOLS の活用例（INDEXの方が短いけど・・・）

=LET(data,'フォームの回答 1'!B:D,arrayformula(MAP(IF(C2:E2<>"",B3:B5&","&C2:E2),LAMBDA(v,xlookup(v,choosecols(data,2)&","&choosecols(data,3),choosecols(data,1),)))))

LETとCHOOSEROWS で importrange を1回で済ませる活用例

=LET(x,importrange("スプレッドシートID","フルーツ!B1:B15"),CHOOSEROWS(x,15)-sum(CHOOSEROWS(x,6,7,8,9,12)))

細かい説明は割愛しますが、知恵袋での質問は実データではなく簡易データで質問してくるケースが多いんですよね。

LETは実データで使う際にシートやセル範囲など書き換える箇所を集約できるのが便利です。

それでは本題、カレンダー表示における LETと配列操作新関数の活用をみていきましょう。

Q1. LET関数と配列操作新関数で 1行カレンダー式をシンプル化したい

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

前回の最後に書いたお題です。こちらの年間カレンダー式を LETに置き換えてみましょう。

LET関数への置き換えだけでなく、配列系新関数を活用して 250文字以下（インデントなし）まで式の文字数を削れるか、チャンレジしてみましょう！

先週の最後にも書いたお題なので、すぐに回答いきます。

↓↓↓

A1. LET関数と配列操作新関数で 1行カレンダー式をシンプル化する

順を追って解説しながら進めます。

LAMBDA を LETに置き換える

まず LAMBDA 3ヵ所でどの変数にどの値（式）を格納しているか整理しましょう。

このようになっています。これを LETにおきかえると、

=ARRAYFORMULA(
  LET(
    m,SEQUENCE(12),
    y,A1,
    z,DATE(y,m,SEQUENCE(1,42)-WEEKDAY(DATE(y,m,1))+1),
    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
      ),
    IF(OR(y<1900,y>2200),,
      {
         TEXT(SEQUENCE(1,7),"ddd");
        IF(x=0,,x)
      }
    )
  )
)

このようになります。

前半の対になっている 赤ゾーンが変数宣言部分、下の黄色部分が最後の処理と短くなった上に非常にわかりやすくなりました。

変数宣言部分も含め配列処理が必要なので Arrayformulaは一番外側から全体に適用させています。

WRAPROWS はカレンダー表示に最適

次に注目すべきは、このLOOKUPの式。この部分はカレンダー表示（7セルで下に折り返し）とする際に、当時は Googleスプレッドシートには WRAPROWSがないので使った代替式です。

というわけで、ここは WRAPROWSに置き換えちゃいましょう。ついでに FLATTENも TOCOLの方が短いので置き換えで。

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

だいぶ短くなってきました。

QUERYをFILTERに置き換える

QUERY式の記述も複雑化と式が長くなる原因の一つです。

"where Col1 is not null OR Col7 is not null"

ここで結構文字数消費してますよね。

そもそも QUERYを使ったのは、変数化が出来ない状態で　FILTER関数を使うと記述が煩雑になるからです。LETで変数化できる今なら、今回のケースは FILTER関数を使った方が良いです。

=ARRAYFORMULA(
  LET(
    m,SEQUENCE(12),
    y,A1,
    z,DATE(y,m,SEQUENCE(1,42)-WEEKDAY(DATE(y,m,1))+1),
    x,WRAPROWS(TOCOL(IFS(MONTH(z)=m,z,z=DATE(y,m+1,1),0, true,)),7),
    IF(OR(y<1900,y>2200),,
      {
        TEXT(SEQUENCE(1,7),"ddd");
        FILTER(IF(x=0,,x),INDEX(x,,1)&INDEX(x,,7)<>"")
      }
    )
  )
)

FILTERで使う為に WRAPROWS( … ) の部分を xと置きます。また、"where Col1 is not null OR Col7 is not null" の部分は、

INDEX(x,,1)&INDEX(x,,7)<>""

として、1列目と7列目の両方が空白である行を削除しています。

ここで新関数の CHOOSECOLS を使ってあげたいんですが、文字数を考えるとINDEXの方が短く書けるので出番ならず。。

さらに、そもそも最後に IF(x=0,,x) としているのは、Query関数の異物除外機能対策の為なので、FILTERに置き換えたら必要ありません。

IFS(MONTH(z)=m,z,z=DATE(y,m+1,1),0, true,)
↓
IFS(MONTH(z)=m,z,z=DATE(y,m+1,1)," ", true,)

このように翌月1日を 0じゃなくて " "(半角スペース）に変換した方が、あとで0を空白に置き換える処理が不要になりシンプルですね。

【回答 LET利用最新版】1行年間カレンダー式

=ARRAYFORMULA(
  LET(
    m,SEQUENCE(12),
    y,A1,
    z,DATE(y,m,SEQUENCE(1,42)-WEEKDAY(DATE(y,m,1))+1),
    x,WRAPROWS(TOCOL(IFS(MONTH(z)=m,z,z=DATE(y,m+1,1)," ", true,)),7),
    IF(OR(y<1900,y>2200),,
      {
        TEXT(SEQUENCE(1,7),"ddd");
        FILTER(x,INDEX(x,,1)&INDEX(x,,7)<>"")
      }
    )
  )
)

すごく短くなりました。インデントなしで 244文字です。

こまかく削っていけば、もう10くらいは文字数減らせますが、こちらで十分でしょう。

【余談】 Excelで年間カレンダーを実現する 1行式

Googleスプレッドシートのみで使える Query関数から、EXCELにも存在するFILTER関数に切り替えたので、1行年間カレンダー数式は少し修正すれば Excelでも使えます。（スピルおよび LET等の関数が使えるバージョン限定）

↓ Excel版年間カレンダー 1行数式

=LET(
  m,SEQUENCE(12),
  y,A1,
  z,DATE(y,m,SEQUENCE(1,42)-WEEKDAY(DATE(y,m,1))+1),
  x,WRAPROWS(TOCOL(IFS(MONTH(z)=m,z,DAY(z)=1," ", TRUE,"")),7),
  IF(
    OR(y<1900,y>2200),"",
    VSTACK(
      TEXT(SEQUENCE(1,7),"aaa"),
      FILTER(x,INDEX(x,,1)&INDEX(x,,7)<>"")
    )
  )
)

Excelの場合は自動で配列処理されるので Arrayformula が不要ですが、 中カッコによる配列結合が出来ないのでそこを VSTACKに変える必要があります。また空白ではなく 空文字 ""にする必要があるので式の長さはそこまで変わりません。

IFS(MONTH(z)=m,z,z=DATE(y,m+1,1)," "
↓
IFS(MONTH(z)=m,z,DAY(z)=1," "

上のIFSの 太字部分ですが、Googleスプレッドシート側でも削れる箇所でしたが、後から気づいたので Excel式で修正しました。

MONTH(z)=m が falseである時点で日付が1であれば翌月一日であると判断できるので z=DATE(y,m+1,1) を DAY(z)=1 という条件に簡略化しています。

Excelでも 1行数式で年間カレンダー生成が実現できました～。

ちなみに、Excelだと スピル式とテーブルは相性が悪い（併用できない）ので、テーブルの書式だけコピペしたものをアレンジして装飾しています。

もう一つ別のカレンダーお題もいってみましょう。

Q2. 2列のデータをカレンダー表示変換したい

新関数フル活用のカレンダーお題 2問目。

左のような日付と担当（または予定）といった 2列データを右の7列のカレンダー表記に変換したいってケースです。

もちろん式を入れるのは左上（D2セル）のみ、A列は表示形式で曜日も表示しています。ここは 日付データという前提です。

カレンダー表示でおなじみの 7セル折り返しなので、WRAPROWSを使いそうですが 2列データをどう折り返すか？がポイントでしょうか。

一番上の行の曜日部分の生成も必要ですが、これは上の 1年カレンダーと同じですね。

まずは、自信のある方は考えてみてください。

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

の前に、ヒントというか他の方法で解決しちゃう人もいるんで、使う関数の条件縛りも。

LET
TOCOL
CHOOSECOLS
VSTACK / HSTACK、
WRAPCOLS / WRAPROWS

自力で出来た人は、今度はこの新関数 7つを全部使って作成してみてください。

TOROW、CHOOSEROWS以外の配列操作新関数フル登場、オールスター天丼感ある問題ですね～ｗｗ

当然これ以外の関数も使います。

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

A2. 2列のデータをカレンダー表示変換する

いきなり答えです。

【回答式】2列データカレンダー表示変換式

=LET(array,A2:B32,padd,WRAPCOLS(,WEEKDAY(INDEX(array,1,1))-1,),VSTACK(Arrayformula(TEXT(SEQUENCE(1,7),"ddd")),WRAPROWS(TOCOL(HSTACK(WRAPROWS({padd;CHOOSECOLS(array,1)},7,),WRAPROWS({padd;CHOOSECOLS(array,2)},7,))),7,)))

わかりづらいのでインデントつけると ↓ こんな感じ。

=LET(
  array,A2:B32,
  padd,WRAPCOLS(,WEEKDAY(INDEX(array,1,1))-1,),
  VSTACK(
    Arrayformula(TEXT(SEQUENCE(1,7),"ddd")),
    WRAPROWS(
      TOCOL(
        HSTACK(
          WRAPROWS({padd;CHOOSECOLS(array,1)},7,),
          WRAPROWS({padd;CHOOSECOLS(array,2)},7,)
        )
      ),7,
    )
  )
)

解説していきましょう。

WRAPROWS / WRAPCOLS で1行（1列）の空白セル範囲を生成

まずカレンダー表示に変換するにあたり、開始位置調整をする必要があります。

ここで A2セルの 1日の曜日を取得して、それに合わせて空白セル範囲を用意したいのですが、それを

WRAPCOLS(,WEEKDAY(INDEX(array,1,1))-1,)

この式で処理しています。これは何をしている式か？

A2:B32・・・array と置いているので、

INDEX(array,1,1))　・・・ A2 セルつまり 2023/3/1
WEEKDAY(INDEX(array,1,1)) ・・・ A2セルの曜日を数値にしたもの
1 (日曜) ～ 7 (土曜) を返す

ここまでは大丈夫ですね。

1日が日曜日ならズレなし、つまり 0なので

WEEKDAY(INDEX(array,1,1)) -1
→ 今回の 3/1は 4(水曜日)なので 4-1 で 3つの空白セル

を開始位置をズラすのに用意したいわけです。

Excelなら EXPAND関数で空白配列を生成したいところですが、残念ながら EXPAND関数は今回の Googleスプレッドシートへの輸入候補から落選してしまったので使えません。

Googleスプレッドシートの場合、上のEXPAND回の noteでも書いている通り、SEQUENCEとIFで生成する、MAKEARRAYを使う、REPTしてSPLIT、といった方法が思いつきますが、実は 1行（1列）の配列生成であれば新関数の WRAPROWS / WRAPCOLS が使えます！

このように WRAPROWS、WRAPCOLSの 折り返し位置まで第3引数の埋め文字で、余った箇所を埋めようとする特性を利用することで、1行または1列の繰り返し配列を生成できます。

というわけで、違和感があるかもしれませんが、

=WRAPCOLS(,5,)

第1引数と第3引数を空白としたこの式で縦5セルの空白配列を生成できるのです。たぶんこれが一番短い記述かと！

この 5の部分を開始日の曜日に応じて可変にしたものが

WRAPCOLS(,WEEKDAY(INDEX(array,1,1))-1,)
これを padd と置く

というわけです。

CHOOSECOLS で1列目、2列目をそれぞれ抜き出し空白配列と連結

{padd;CHOOSECOLS(array,1)}
{padd;CHOOSECOLS(array,2)}

このようにすることで、画像のように 3セル分空白セルの下から日付、担当者のデータが始まる配列を生成します。

2つに分けて処理しているのは、今後それぞれ別々に処理をする必要があるからです。

ちなみにここは本当は INDEX(array,,1) と INDEXを使った方が短い記述になりますが、新関数オールスター天丼 を実現する為に CHOOSECOLSを無理に使ってますｗ

逆に配列の連結部分も VSTACK(padd,CHOOSECOLS(array,1)) としてもいいんですが、VSTACKは後でも登場するんで、ここはシンプルな中カッコ連結で記述しています。

WRAPROWS でカレンダー形式に折り返したデータを2つ用意 HSTACKで横連結

空白セル範囲を先頭に連結した1列目、2列目をそれぞれ WRAPROWSで 7セルで折り返した上で、さらに HSTACK で横方向に連結します。

つまり、日付と担当者をそれぞれカレンダー形式に並べたものを横につなげた横14セルの配列を生成したわけです。

一度 TOCOLで1列にしてから、再度 WRAPROWSで 7セル折り返しに

=LET(array,A2:B32,padd,WRAPCOLS(,WEEKDAY(INDEX(array,1,1))-1,),WRAPROWS(TOCOL(HSTACK(WRAPROWS({padd;CHOOSECOLS(array,1)},7,),HSTACK(WRAPROWS({padd;CHOOSECOLS(array,2)},7,)))),7))

この横連結した配列を TOCOLで一度 1列にしてから、再度 WRAPROWSで7セル折り返しにすることで、2行毎のカレンダー表示配列が生成できます。

この部分は2つの配列の各行にそれぞれ奇数、偶数の番号を振って SORTで並び替えることで、交互に重なる一つの配列を生成 といった方法も思いつきますが、

同じ構成の配列を2つ作成
それが交互になるように配列を組み替える

という部分は一緒です。

WRAPROWSという折り返し配列を生成できる関数の登場で、こういった方法が簡単に出来るようになりました。

最後に VSTACK で曜日行と縦連結

最後に

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

で生成した曜日配列と連結と VSTACK で連結で完成。

ここもVSTACK使わず中カッコでも出来るんですが、合えて新関数を使ってますｗ

曜日生成の部分がよくわからないって方は過去 noteを参照。

以上、丁寧めに解説を書きましたが最新関数の組み合わせ、活用による 2列データのカレンダー表示変換、理解できましたでしょうか？

=LET(array,A2:B32,padd,WRAPCOLS(,WEEKDAY(INDEX(array,1,1))-1,),VSTACK(Arrayformula(TEXT(SEQUENCE(1,7),"ddd")),WRAPROWS(TOCOL(HSTACK(WRAPROWS({padd;CHOOSECOLS(array,1)},7,),WRAPROWS({padd;CHOOSECOLS(array,2)},7,))),7,)))

2列データカレンダー表示変換式

式内の

WRAPROWS({padd;CHOOSECOLS(array,1)},7,)
WRAPROWS({padd;CHOOSECOLS(array,2)},7,)

の部分が煩雑に感じますが 2回ならこのままの方がよいです。これが3列データ、4列データとなった場合は REDUCEでループ処理させる式に改良すると良いでしょう。

Q3. 1行数式で横並び 3か月カレンダーを生成する（回答は次回）

LET + 配列操作新関数のカレンダー応用例の最後です。年と月を指定することで、1つの式で横並び3か月カレンダーを生成しています。

ポイントは各月のカレンダーにはタイトル（〇年〇月）をつけ、かつ各カレンダーは 1列開けて表示している点でしょうか。

これもExcel版が作れます。

Googleスプレッドシート職人、Excel関数職人の方々は是非チャレンジしてみてください。

回答は次週で！

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

【応用編】Googleスプレッドシート 11新輸入関数 【カレンダー表示で LET活用】

知恵袋回答で使った 新関数例

LET,TOROWの空白除去 活用例

LET, CHOOSECOLS の活用例（INDEXの方が短いけど・・・）

LETとCHOOSEROWS で importrange を1回で済ませる活用例

Q1. LET関数と 配列操作新関数で 1行カレンダー式を シンプル化したい

A1. LET関数と 配列操作新関数で 1行カレンダー式を シンプル化する

LAMBDA を LETに置き換える

WRAPROWS はカレンダー 表示に最適

QUERYをFILTERに置き換える

【回答 LET利用 最新版】1行 年間カレンダー式

【余談】 Excelで 年間カレンダーを実現する 1行式

Q2. 2列のデータを カレンダー表示変換したい

A2. 2列のデータを カレンダー表示変換する

【回答式】2列データ カレンダー表示変換式