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

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

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

シリーズ前回の記事

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

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

WRAPROWSを使う繰り返し

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

このようになります。

CHOOSECOLSを使う繰り返し

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

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

VSTACKを使う繰り返し

ちょっと無理やりですが、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を使う繰り返し（縦方向 組み合わせワザ）

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

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スプレッドシートで 配列を〇行開けに変換する

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

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

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

• EXPANDの部分をどう処理するか？

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

の2点となります。

【回答】〇行開け 変換式

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

LETを使うことで

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

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

解説していきましょう。

HSTACKと WRAPROWSで EXPANDを代替する

EXPANDを代替している部分が

ここです。可変にしているので 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つのアプローチが考えられます。

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

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

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

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

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

こんな感じ。

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

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

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

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

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

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

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

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

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

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

で、ようやく本題です。

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

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

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

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

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

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

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

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

冒頭の部分は

という意味です。

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

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

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

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

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

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

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

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

SEQUENCE(6,7,d-buffa),

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

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

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

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

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

最後にループの初回判定をしたうえで、初回以外は 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か月カレンダーを生成できます。

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

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

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

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

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

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

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

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

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

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