【応用編】Googleスプレッドシート 11新輸入関数 【カレンダー表示で LET活用】
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,)))
式内の
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関数職人の方々は是非チャレンジしてみてください。
回答は次週で！
いいなと思ったら応援しよう！
