【応用編】Googleスプレッドシート 11新輸入関数 2【LET活用】

mir

2023年4月9日 15:34

前回のLET関数と VSTACKや WRAPROWSといった新関数の応用編の続きです。このLET他新関数シリーズは、今回でひとまず終了となります。

今回は新関数を応用するお題が3つほどあります。スプレッドシート関数の使い手の方はチャレンジしてみてください。

シリーズ前回の記事

配列操作新関数で 1行（1列）繰り返し配列を生成する

新関数応用お題チャレンジの前に、まずは前回登場した 1行（または1列）の同じセルを繰り返した配列を生成する方法を再度おさらいしましょう。

WRAPROWSを使う繰り返し

=WRAPROWS("★",A2,"★")

前回紹介した方法です。折り返しの数に対しての不足部分を埋めるというWRAPROWSの特性を生かした、A2セルの数字分だけ ★を横方向に展開する配列生成式です。

このようになります。

CHOOSECOLSを使う繰り返し

=CHOOSECOLS("★",SEQUENCE(1,A2,1,0))

通常はセル範囲や配列に対して使う CHOOSECOLSを単文字に使うことで繰り返し配列を生成する式です。

SEQUENCE(1,A2,1,0) で1をA2セルの数値分繰り返す配列を生成し、CHOOSECOLSと組み合わせることで "★"の1列目（つまり "★" ）をA2の数字だけ取得する、つまり横に展開することができます。

VSTACKを使う繰り返し

=CHOOSEROWS(IFERROR(VSTACK(SEQUENCE(1,A2),"★"),"★"),2)

ちょっと無理やりですが、VSTACKでも繰り返し配列の生成が可能です。

まず最も簡単に横方向にA2の数字分（10セル）展開する SEQUENCE(1,A2)を用意します。

この縦1横10の配列と、単体の"★"（横1、縦1）をVSTACKで縦結合すると、上のように不足部分9セルが#N/Aエラーとなります。

ここをIFERRORでエラー部分も　"★"に置き換えることで2行目が ★が A2 の数字分（10セル分）繰り返された配列となります。最後にCHOOSEROWSを使って2行目だけを取り出します。（ここはINDEXでもOK）

ただ、これは A2が 0でも ★が1つ残っちゃうという欠点があります。もう1つIFを入れれば回避できますが、まぁ他の式使った方がいいですね。

TOCOLを使う繰り返し（縦方向組み合わせワザ）

=TOCOL(SPLIT(REPT("★,",A2),","))

さらに無理やり使ってる感ありますがｗ

TOROWやTOCOL自体は元の配列を拡張させることができないので、単体で繰り返し配列の生成は無理です。

そこで新関数登場前は主流だった REPTとSPLITを使った繰り返し配列生成と組み合わせます。

SPLIT(REPT("★,",A2),",")

これは昔からある繰り返し配列の生成式なんですが、横方向への展開しかできないというのがネックでした。

もちろんTRAPSPOSEやFLATTENと組み合わせれば縦方向に転換できるので、かつては

TRAPSPOSE(SPLIT(REPT("★,",A2),","))

としていたものを新関数を絡めたいってことで、

TOCOL(SPLIT(REPT("★,",A2),","))

と、TOCOLに置き換えて少し短くしただけです。

Excelなら配列操作関数 EXPANDで処理するのがベストなんですが、

EXPANDが輸入対象から外れてしまったので、Googleスプレッドシートにおいては、WRAPROWS / WRAPCOLS を使う方法が、1行（1列）の繰り返し配列生成においてはベストかなと思います。

Q1. Googleスプレッドシートで配列を〇行開けに変換したい

では、一つ目のお題いってみましょう。

上の繰り返し配列生成の応用です。こんな感じで対象配列（データ部分 A2:E6）を G1セルで指定した数字分空白行を間に挟んだ形に変換したい、というお題です。

H1にどのような式を入れればよいでしょうか？

配列操作系関数をある程度理解できた方なら自力で解けると思います。
チャレンジしてみてください。

↓↓↓
回答は以下
↓↓↓

A1. Googleスプレッドシートで配列を〇行開けに変換する

ちなみにこのお題、元ネタは「いきなり答える備忘録」さんの記事がベースになります。

=WRAPROWS(TOCOL(EXPAND(B3:D6,,6,"")),3)

EXCELの範囲 1行開け式（いきなり答える備忘録様）

これはExcelがベースなので EXPAND関数を使っていますが、今回のお題は Googleスプレッドシートなので EXPANDが使えません。

というわけで回答のポイントとしては、

EXPANDの部分をどう処理するか？
〇行ずつの部分をどう可変にするか？

の2点となります。

【回答】〇行開け変換式

これらを踏まえて回答はコチラになります。

=LET(array,A2:E6,br,G1,WRAPROWS(TOROW(IFERROR(HSTACK(array,WRAPROWS(,COLUMNS(array)*br,)))),COLUMNS(array),))

LETを使うことで

対象の配列 array ・・・ A2:E6
〇行開け　br　・・・　G1　(Blank Row を略した感じ）

の部分だけ変えれば使える汎用的な式にしました。

解説していきましょう。

HSTACKと WRAPROWSで EXPANDを代替する

EXPANDを代替している部分が

IFERROR(HSTACK(array,WRAPROWS(,COLUMNS(array)*br,)))

ここです。可変にしているので COLUMNS関数を使ってたりするのもありますが、結構長いですね。

WRAPROWS(,COLUMNS(array)*br,)

まずこの部分は、冒頭の繰り返し配列の活用ですね。対象の配列の長さ（横幅）× 開ける行数 サイズの横に1行の空白配列を生成します。

対象となる配列と、この生成された横1行の配列を HSTACKで横に結合すると

このように、不足部分が NAエラーとなります。これをIFERRORで空白に置き換えてあげれば、

このように元の配列の横に元の配列の横の長さの〇倍（br倍）の空白行列が連結された状態となります。

TOROWとWRAPROWSで配列を再構築

あとは一度 TOROWまたはTOCOLで 1行（1列）に一度伸ばしてから、再度WRAPROWSでもとの横幅 COLUMNS(array) で折り返すことで〇行分空白行を挟む配列が生成できます。

最後の繋げて一度伸ばして再度折り返し、って処理は前回の 2行カレンダー作成と同じ手順ですね。

当然ですが、この空白は数式によって生成された空白なので、ここに何か手入力したいと言っても無理です。

入力が必要って場合は、一度範囲を丸ごとコピーしてそのまま同じ場所に値を貼り付けとして値化してください。

PDFにする、印刷して使うといった場合は問題ありません。

本題の前に、もう1問新関数の応用お題をやってみましょう。

Q2. 行数が変動するデータで、常に最終行の一つ下に合計を表示したい

シンプルなこうのような集計表があったとします。

単価と数量があって E列に各行毎の金額を算出した上で、最後に合計を算出しているわけですが、このE列の 行毎の金額と最後の行の合計を E2セルに式を入れるだけで実現したい！というお題です。

なんでこんな要望があるかというと、途中に行挿入した際に E列に式をいちいち入れるのが面倒ってことみたいです。

うーん、そもそも合計を下じゃなくて上に表示すれば簡単じゃない？と思いますが、まぁ色々な事情があって最終行の下に合計を出したいんでしょう。

条件として 途中に空白行はないものとします。

どうしょうか？出来そうでしょうか？
まずはチャレンジしてみてください。

↓↓↓
回答は以下
↓↓↓

A2. 行数が変動するデータで、常に最終行の一つ下に合計を表示したい

このお題は大きく2つのアプローチが考えられます。

Arrayformula と IFSでその行に応じた計算をさせる方法
単価 x 数量計算とその合計を生成し上下に連結させる

それぞれ回答と簡単に解説をしていきましょう。

A2-1. Arrayformula と IFSでその行に応じた計算をさせる方法

=ARRAYFORMULA(IFS(D2:D<>"",C2:C*D2:D,OFFSET(D2:D,-1,0,ROWS(D2:D))<>"",SUMPRODUCT(C2:C,D2:D),true,))

IFS関数で順に条件判別して、それぞれに応じた計算を返しています。
どんな処理をしているかというと

こんな感じ。

ポイントとしては最終行指定のないセル範囲をOFFSETを使って一つズラして上の行を取得する際に

OFFSET(D2:D,-1,0,ROWS(D2:D))

と、単に行ズレを -1 するだけではなく、ROWS(D2:D)で高さを指定する点。これをやらないと行数が1つ増えるバグが発生します。

古のレジェンド配列関数 SUMPRODUCTは、なんだかんだで優秀ですね。
ケースによっては SUMIFやCOUTIFよりもシンプルに記述できます。

A2-2. 単価 x 数量計算とその合計を生成し上下に連結させる

=LET(data,FILTER(C2:C*D2:D,D2:D<>""),VSTACK(data,SUM(data)))

もう一つというか、本題の LETや配列操作関数を使う方法がこっち。途中に空白行を含まないって条件なんで、こんな感じの記述が可能です。

上の Arrayformula + IFS よりもシンプルで処理が分かりやすいですね。

SUM(data)　この式で FILTER式が返す計算結果配列を合計しているのですが、LETを使うことで、FILTER式を2回書かずに記述がすっきりしています。

またFILTER式を使うことでARRAYFORMULAいらずなのもGood。最後のVSTACKはここは中カッコ結合でも問題ないです。

こうしておくことで、途中に行挿入しても E列が自動で全て計算されます。なかなか良い LETの活用でした。

で、ようやく本題です。

Q3. 1行数式で横並び 3か月カレンダーを生成したい

年と開始月を指定するだけで画像のように表示される、1行数式で作る 3か月カレンダーにチャレンジしてみましょう。

カレンダーの表示条件は以下です。

・行や列位置の影響を受けない、どこに式をいれてもOKであること
・各月のカレンダーに〇年〇月というタイトルが付くこと
・タイトルから1行開けて日曜始まりで曜日を表示させること
・各月のカレンダーには前の月、後ろの月の日付は表示させない
・各月のカレンダーどうしの間は 1列あけること
・年をまたぐ表示にも対応すること

画像拡大すれば、ほぼ式の中身も見えちゃうんで答えネタバレしちゃった人もいるかもですが、まずは関数得意な人は自力でお試しください。

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

A3. 1行数式で横並び 3か月カレンダーを生成する

カレンダーネタは先週2つやってるんで、その応用です。いきなり答えといきましょう。

=LET(yyyy,A1,MM,B1,REDUCE(,{0,1,2},LAMBDA(pv,cv,LET(d,DATE(yyyy,MM+cv,1),y,YEAR(d),M,MONTH(d),buffa,WEEKDAY(d-1),days,SEQUENCE(CEILING((DAY(EOMONTH(d,0))+buffa)/7),7,d-buffa),cal,Arrayformula(VSTACK(y&"年"&M&"月",,TEXT(SEQUENCE(1,7),"ddd"),IF(MONTH(days)=M,days,))),IFERROR(IF(pv="",cal,HSTACK(pv,,cal)),)))))

ちょっとわかりづらいですかね。インデントつけときましょう。

=LET(
  yyyy,A1,
  MM,B1,
  REDUCE(,{0,1,2},
    LAMBDA(pv,cv,
      LET(
        d,DATE(yyyy,MM+cv,1),
        y,YEAR(d),
        M,MONTH(d),
        buffa,WEEKDAY(d-1),
        days,SEQUENCE(CEILING((DAY(EOMONTH(d,0))+buffa)/7),7,d-buffa),
        cal,
          Arrayformula(
            VSTACK(
              y&"年"&M&"月",
              ,
              TEXT(SEQUENCE(1,7),"ddd"),
              IF(MONTH(days)=M,days,)
            )
          ),
        IFERROR(
          IF(pv="",cal,HSTACK(pv,,cal)),
        )
      )
    )
  )
)

ボリューム感の挟み撃ちな式ですね。解説していきましょう。

REDUCEで繰り返し処理 + LET入れ子処理

冒頭の部分は

=LET(
　yyyy,A1,　・・・開始年を yyyyとおく
　MM,B1,　・・・開始月をMMとおく

という意味です。

そして3か月カレンダーを作成する為に、1ヶ月カレンダーを作成して、それを横連結するという処理を繰り返したいので、ここで最強関数の一つ REDUCEを使っています。

REDUCE(,{0,1,2},　・・・初期値空白、配列 0,1,2 の3回を繰り返す
　LAMBDA(pv,cv,　・・・一つ前の結果を pv、今処理している値 cvと置く

さらに REDUCE内でもう一つLETを使って、 LETを入れ子（ネスト）にしています。これは変数化の際に cvを使いたいので、REDUCE内でも LETする必要がある為です。

LET(
　d,DATE(yyyy,MM+cv,1),　・・・現在のループの月の開始日（1日）
　y,YEAR(d),　・・・　現在のループの年（数値）
　M,MONTH(d),　・・・　現在のループの月（数値）
　buffa,WEEKDAY(d-1),　・・・　現在のループの開始日の一つ前（前の月の月末）の曜日を数値化したもの

この REDUCE 内の LETで生成される変数はループの度に変わっていきます。

yyyyが2023、MMが 4 だった場合、
ループ1回目　cv・・・0
　d 2023/04/01
　y 2023
　M 4
　baffa 6（2023/3/31 は金なので）

ループ2回目　cv・・・1
　d 2023/05/01
　y 2023
　M 5
　baffa 1（2023/4/30 は日なので）

ループ3回目　cv・・・2
　d 2023/06/01
　y 2023
　M 6
　baffa 4（2023/5/31 は水なので）

REDUCE内のループごとに LETで生成する変数が置き換わっていくのは、まさにプログラミングのループ処理的ですね。

日曜開始のカレンダー日付を生成し縦連結

days,SEQUENCE(CEILING((DAY(EOMONTH(d,0))+buffa)/7),7,d-buffa),

カレンダーの日曜から開始する1ヶ月分を生成する式がここです。

SEQUENCEを使って7セル折り返しデータを生成しているので、WRAPROWSの出番はありません。

先ほど変数化した buffaを使って丁寧に式を作っていますが、ここは雑に

SEQUENCE(6,7,d-buffa),

としてしまっても良いです。1ヶ月は最大でも6週なので最大値をとれば問題ないってやり方です。一番下に余計な空白行が発生することがありますが、まあ気にしないならアリでしょう。

cal,
　　Arrayformula(
　　　VSTACK(
　　　　y&"年"&M&"月",　・・・○年〇月タイトル行
　　　　,　・・・空白
　　　　TEXT(SEQUENCE(1,7),"ddd"),　・・・　曜日
　　　　IF(MONTH(days)=M,days,) ) ),　・・・　カレンダー配列（当月以外を空白処理）

そしてこのように VSTACKで縦に連結します。

サイズ（横幅）が合ってないのでエラーでまくりですが、この時点では放置で問題ないです。

この今回のループで生成される1ヶ月カレンダーを calと置きます。

最後に空白を挟んで横連結、エラーを空白化

IFERROR(
　IF(pv="",cal,　・・・　pvが空白（ループ初回）ならそのまま calを返す
　　HSTACK(pv,,cal)　・・・それ以外は pv 前のループのカレンダー + 空白 + 今生成したカレンダーを横連結
　),　・・・IFERRORで最後にエラーを空白化
)

最後にループの初回判定をしたうえで、初回以外は HSTACKで一つ前までに生成されたカレンダー（pv）の右側に今回のループで生成したカレンダー（cal）を空白を挟んで連結します。

仕上げに IFERRORでエラーを空白化すれば完成。

=LET(
  yyyy,A1,
  MM,B1,
  REDUCE(,{0,1,2},
    LAMBDA(pv,cv,
      LET(
        d,DATE(yyyy,MM+cv,1),
        y,YEAR(d),
        M,MONTH(d),
        buffa,WEEKDAY(d-1),
        days,SEQUENCE(CEILING((DAY(EOMONTH(d,0))+buffa)/7),7,d-buffa),
        cal,
          Arrayformula(
            VSTACK(
              y&"年"&M&"月",
              ,
              TEXT(SEQUENCE(1,7),"ddd"),
              IF(MONTH(days)=M,days,)
            )
          ),
        IFERROR(
          IF(pv="",cal,HSTACK(pv,,cal)),
        )
      )
    )
  )
)

流れを追って解説しましたが、なんとなく理解できたでしょうか？

ちょっとアレンジすれば今月を開始とした 3か月カレンダー、もしくは今月が真ん中にきて、前月と翌月が表示される3か月カレンダーも作れますね！

Excelでも出来る 1行数式で3か月カレンダー

オマケです。Excelでも少しアレンジすれば同じように1行数式で 3か月カレンダーを生成できます。

=LET(yyyy,A1,MM,B1,x,REDUCE("",SEQUENCE(3,1,MM),LAMBDA(pv,cv,LET(y,IF(cv>12,yyyy+1,yyyy),M,IF(cv>12,cv-12,cv),buffa,WEEKDAY(DATE(y,M,)),days,SEQUENCE(CEILING((DAY(DATE(y,M+1,))+buffa)/7,1),7,DATE(y,M,1)-buffa),cal,VSTACK(HSTACK(y&"年",M&"月"),"",TEXT(SEQUENCE(1,7),"aaa"),IF(MONTH(days)=M,days,"")),IFERROR(HSTACK(pv,"",cal),"")))),DROP(x,,2))

EXCELで作る場合は空白をきちんと ""と空文字にする必要があるのと、pvが空かどうかの判定方法が少し面倒なので、初回ループの判定しないで最後に無駄な空白となる左2列をDROPで削ぎ落とすって流れになります。

曜日の表示も "aaa" と指定する必要があります。

あと、Excelのスピルだとセルをはみ出した文字が表示できないんですよね～。セル幅を均等にしたかったんで、タイトル部分の年と月のセルを分けました。

新関数の活用で Googleスプレッドシートの配列操作がさらに便利に！

Googleスプレッドシートに追加（輸入）されたLET他、新関数を取り上げたシリーズもこれで終了です。

基本を押さえる「最新動向シリーズ」4回、お題を解く実践メインの「応用シリーズ」2回の合計6回にわたって、新関数のメリットや応用例を取り上げてみました。

LET使いたくなったでしょうか？

再度言いますが、LETはこれが無いと出来ないって処理はないです。ただし、使うことで圧倒的に式が読みやすく、メンテンスしやすく、そして軽くなります。

まずは式内で何度も同じ範囲や式が出てくるケースを LET化してみてください。この便利さは結構ハマりますよ！

関数ネタが続いたので、次回はGASネタか機能ネタを挟みたいと思います。

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

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

【応用編】Googleスプレッドシート 11新輸入関数 2【LET活用】

配列操作新関数 で 1行（1列）繰り返し配列を生成する

WRAPROWSを使う繰り返し

CHOOSECOLSを使う繰り返し

VSTACKを使う繰り返し

TOCOLを使う繰り返し（縦方向 組み合わせワザ）

Q1. Googleスプレッドシートで 配列を〇行開けに変換したい

A1. Googleスプレッドシートで 配列を〇行開けに変換する

【回答】〇行開け 変換式