土日の更新は 旬の話題や Googleスプレッドシート、GoogleWorkspace関連でランダムに気になったことを書いていく 雑談記事です。

と言いつつ、すっかりシリーズ連載の記事ばっかりで、単に 平日更新の記事と 世界線が違うってだけになっちゃいました。

昔のサザエさんだと お隣が 浜さんか 伊佐坂さん かの違いみたいなｗ （一応あれは 浜さんが引っ越した後に伊佐坂さんが隣に越してきたという時間軸があるらしい） これ関東ローカルのネタ？

とりあえず同じテーマが続くと飽きるんで、平日更新とは別のテーマで なるべく旬なネタ を 土日に出していこうかなと思います。

前回の記事
【GAS】Googleスプレッドシート自作関数で シート情報を取得する -2

新関数でこれ欲しかったよ～ という、シート名やシートIDを取得する関数を GAS で自作、前回のブラッシュアップで 引数で制御できて ヘルプも表示される 完成形となりました。

自作関数シリーズの最後、今回は シート名、シートIDを取得する自作関数の実際の活用例を紹介していきましょう。

シート名、シートIDの活用数式

自作関数と 既存の関数を組み合わせた、以下の3つのお題にチャンレンジしてみましょう。

1. 複数シートの串刺し計算

2. 複数シートの範囲結合

3. 複数シートの横断検索（リンク付）

いずれも、新関数 LAMBDA + ヘルパー関数が必須です。新関数の練習にもなりますね。

GAS 自作関数がなくても使える

今回トライする お題は、いずれも 前回作成した シート名、シートIDを取得する 自作関数がなくても 使えます。

だから、会社のスプレッドシートのセキュリティ設定上 GASが使えない方や、どうしても GASは苦手でコピペで動かすのも怖いから GASは避けたい って人も参考になるかと思います。

ただし 自作関数無しでやる場合は、シート名の一覧、シートIDの一覧をどこかに手動で 書き出してリスト化しておく必要があります。

シート名に関しては規則性があるのなら 既存関数で生成も可能です。たとえば標準のシート名であれば シート1 の次は シート2 と 「シート」の後ろの数字が増えていくだけなので シート1～シート10 であれば、

こんな式で生成できますね。

残念ながら シートIDに関しては完全にランダムなので、取得するには GASを使うか手動コピペしかありません。

サンプルデータの構成

今回は 部門でシート分けをされた データをサンプルに使いましょう。

別に note用のGmailアカウントも用意してるんで、お題（サンプルデータ）のスプレッドシートを共有してもいいんですが、そこまでガチでやってる人はいないですよね？？

ガチでQAに取り組んでる「俺より強いやつに会いに行く」系の関数求道者みたいな人がいましたら、データ公開しますんでお知らせくださいw

じゃあ 3つのお題にチャレンジしていきましょう。

Q1. 複数シートの串刺し計算

Excelで 串刺し計算とか 3D集計と呼ばれる 複数シートにまたがる集計ですね。

Excelでは出来るけど Googleスプレッドシートでは出来ないのか？って、たまに QAサイトで見かけますが、残念ながら同じようには出来ません。

Googleスプレッドシートは Excel以上に シート毎にデータ分けちゃうと面倒なんですよね。 PowerQuery もないし。

まぁ 月毎とか 部署ごとにシートを分けたくなる気持ちはわかりますが。後々、管理者が 関数もGASもよくわからないって状態だと 泣く羽目になります。

今回の活用例が役に立てば幸いです。

さて、Excelと同じような カチカチとマウスによる選択では 無理ですが、Googleスプレッドシートでも 前回の 自作関数、そして既存関数（新関数）を組み合わせれば、この串刺し計算だって 1つの式で 実現できちゃいます。

串刺し計算の要件

まずは 自力で作れる方は考えてみてください。

ここから回答

↓↓

A1. 複数シートの串刺し計算

1問目は比較的簡単なんで、いきなり答えるじゃなくて 考え方を順に説明していきましょう。読んでる人も、途中で 気づきがあるかもしれませんし。

とりあえず使う関数は以下の4つ

前回作成した sheetNames3 と 基本の SUMは特に解説は不要ですね。

INDIRECT を理解する

今までの記事でも何回か登場している INDIRECT関数は、文字列を使ってシート や セル が参照できる関数です。

↓ カレンダー番外編の 他シートを参照する 条件付き書式でも使いましたね

文字列を使って参照？？ となるかもしれませんが、下の例を見てください。

1番（A2）のように 直接 セルに　='第1営業部'!A4　と入力すれば、第1営業部というシートの A4セルの値（一ノ瀬）を参照します。

しかし、これを 文字列の 組み合わせで再現しようとすると上手くいきません。

2番（D6、D7）のようにそのまま 第1営業部!A4 といった  連結した 単なる文字列になってしまいます。セル参照だろうが、直接文字列を連結だろうが 同様です。別にシート名の前後についてる シングルクォートあるなしも関係ありません。

この単なる文字列を セル参照として機能させているのが 3番のケース、INDIRECT関数 との組み合わせです。

単なる 文字列を「本物」（セル参照）に変える魔法のようなINDIRECT関数は、まさにドラえもんの道具なら「本物クレヨン」、ネギま！だとパルのアーティファクト「落書帝国」 で 描いた絵が本物になる イメージ。

プログラミングだと eval みたいな感じと言えるかも。

ちなみに 日本語名のシート を参照すると　='第1営業部'!A4　とシート名の前後に '（シングルクォート）が（自動で）付くので、INDIRECTでも

=INDIRECT("'"&A2&"'!"&B2)　　▲動くけどイマイチ

と わざわざ シングルクォートを付ける人がいますが、これは不要です。

=INDIRECT(A2&"!"&B2)　　　◎ これでOK

上記のようにシート名のシングルクォート無しの シンプルな記述で問題ないです。

新時代だ！ LAMBDA+BYROWで 複数シートをINDIRECT

INDIRECTは 複数範囲をまとめて返すスピル機能を持っています。上記だと１番上の式で A4:A6 を返してますね。

これはこれで便利なんですが、一方で 範囲を返せる関数の宿命といいますか Arrayformulaと組み合わせて使えません。

つまり、Arrayformula + INDIRECTでは 複数シートを連続で参照するこが出来ないのです。

上の画像の通り、Arrayformulaと組み合わせても 一番上のみが返りますし、INDIRECT内の文字列で 範囲結合の記述 （ 中カッコ）を使うとエラーとなります。

INDIRECTを複数シートに使うためには、1シートずつINDIRECTを記述するしかありませんでした。（いままでは）

「いままでは」（過去形）としたのは、もちろん 新関数 LAMBDA＋ヘルパー関数で 今まで出来なかった 1行式で 複数シートを INDIRECTすることが 出来るようになったからです。

まさに、新時代ですね～！ウタですね～！

例えば シート名 の縦並びの配列に対して BYROWを使った場合、上記画像の 上の式 のようにINDIRECTを組み合わせることで、3つの各シートの A4セルの値を 1行数式で返すことができます。

BYROW は、与えた 配列を上から下へ行単位でループ処理するのですが、Arrayformulaが効かない関数にも使えるという点が超絶メリットです。

ただしBYROWは、行毎の処理で返す値は 単体の値である必要があり、配列を返すことが出来ません。画像の下の式のように 各シートから 範囲 A4:C4を 取得しようとするとエラーとなってしまいます。

↑ 上記は2022年12月のアップデートで仕様が変更された為、取り消し線をいれています。

BYROWに関しては、以前の記事 新関数検証-1 で触れているので、そちらも参照ください。（BYROWの記事で 「出来ること」 の中に INDIRECT との組み合わせによる 串刺し計算 って書いてましたね）

BYROWの記事で書いた通り、各行毎の処理では、最終的に配列を返せないってだけなんで 、途中経過の工夫次第で色々回避方法があります。

今回のケースも対象は 各シートの B4:D10 （複数セル）なんですが、 各シート の処理時に 合計（SUM)しちゃえば、処理毎 に返す結果は 1つの値になりますね。この結果配列を 最後に全体 SUMしちゃえば良いわけです。

ユーも SUM しちゃないよ！

というわけで、使うべき 関数が理解出来たら 作成していきましょう。

登場した 関数を組み合わせる

考え方としては上記の手順を 1行数式にまとめればよいってことですね。

1つの式にまとまったら、最後に「シートの開始番号、終了番号、セル範囲」をLAMBDAを使って 引数として外にだせば完成。

以下の式が回答になります。

【回答】 複数シートの串刺し計算 式

注意点として、INDIRECTで利用するので 範囲 "B4:D10" の部分は "ダブルクォートを付けて文字列とする必要があります。

開始シート番号、終了シート番号、範囲 だけを変えれば良いので、汎用性のある 串刺し計算式として使えますし、2か所の SUM の部分を MAX や MIN、AVERAGE に変えることで、合計以外の3D集計も可能となります。

応用： シート毎のサマリ表も作れる

全体をSUMせずに 個別に シート名とシート毎の合計の一覧表（サマリ表）を出力することもできます。

ただし この場合は、BYROWの縛りを回避するために 一度シート名とシート毎の合計を 間に , を入れて 結合し一つの文字列として出力してから、最後に 全行を SPLIT で分ける というテクニックを使います。（他の記述方法もありますが、たぶんこれが一番短くてシンプル）

シート毎に出力した数値 B4:B8の合計と 先ほどの串刺し計算 B1の総合計が一致しているのが分かりますね。

EXCELほど気軽ではないですが、Googleスプレッドシートでも 串刺し計算が出来ました。

Q2. 複数シートの範囲結合

これもありがちなお題ですね。 シート2つの結合なら

こんな形で直接参照した2つのシート範囲 を 範囲結合で 縦連結させた配列を作成、これを Queryで 空白を除去すればよいんですが、対象シートが大量だったり可変だったりすると、対象のシートと範囲を列挙するのは難しいし・・・。

こんな悩みを抱えた方もいるでしょう。

たとえば要件が

こんな感じだと 今まではGASでの処理しか方法がなく、シート関数では無理ゲー でした。

これを 自作関数 と LAMBDAヘルパー関数を活用して 1行数式でやってみよう！ というお題です。（自作関数部分はGAS使ってますが）

結構ハードルが上がるんで、関数腕自慢な人だけ まずは自力で考えてみてください。

ここから回答
↓↓

A2. 複数シートの範囲結合

今回はシート毎に 二次元配列（縦横の範囲）を取得して縦連結していくので、先ほど使ったBYROW 内で 文字列結合 して最後に SPLIT という技が使えません。

このケースは LAMBDAヘルパー界 最強の REDUCEを使いましょう。
もちろん スプレッドシートでの 範囲結合も理解している必要があります。

これはさすがに難問の部類なんで、先に「答え」その後に解説とします。

【回答】自作関数、REDUCE、INDIRECT で作る 複数シート結合式

うーん、さすがに長いんで コード記述にもしておきましょう。
（インデント付けて見やすくしただけで内容は一緒です。）

=LAMBDA(sheets,title,range,
  Arrayformula(
    REDUCE({"シート名",INDIRECT(INDEX(sheets,1,1)&"!"&title)},sheets,
      LAMBDA(pv,cv,
        LAMBDA(data,
          LAMBDA(x,
            IFERROR({pv;x},pv)
          )(Query({IF(SEQUENCE(ROWS(data))>0,cv,),data},"where Col2 is not null"))
        )(INDIRECT(cv&"!"&range))
      )
    )
  )
)(sheetNames3(1,true),"A3:D3","A4:D")

REDUCEの 配列push 的な処理を活用しています。
ステップを踏んで解説していきましょう。

ステップ0: REDUCE のループ処理の準備

まず 最初に設定している 引数を見ていきましょう。

自作関数で sheetNames3(1,true) とすることで、自シート以外の全シートのシート名が取得できます。

これを sheetsと置いています。ここは大丈夫ですね？

そして タイトル行の範囲 を文字列で指定した "A3:D3" を title、同じく ターゲットとなる セル範囲の文字列  "A4:E" を range と置いています。

この シート名配列 sheets を 使って REDUCE でループ処理をさせます。

REDUCEの処理は、以下のような流れを シート名の数だけ繰り返すイメージ。

1. シート名(cv) と範囲文字列の INDIRECT で データ（二次元配列）を取得

2. シート名 配列を生成し、データの左に連結

3. データを Queryで空白行削除

4. pv の下に 連結（配列プッシュ）

5. 次のcv に移り また1から繰り返す 

こんな感じ。

REDUCEの 初期値 部分、

{"シート名",INDIRECT(INDEX(sheets,1,1)&"!"&title)}

これなんですが、INDEX(sheets,1,1) つまり 対象のシートの 1枚目から タイトル行部分を取得し、左端に "シート名"って文字を 追加（横連結）しています。 これが 配列を入れていく 入れ物（の先頭） と思ってください。

ここからループ内の処理を見ていきましょう。

ステップ１. シート名(cv) と範囲文字列の INDIRECT で データ（二次元配列）を取得

cvは sheets から 1つずつ取り出される シート名です。これを使って

 INDIRECT(cv&"!"&range)　

とすることで、でシートの対象範囲のデータを取得します。何度か登場するので、これを data と置きます。

ステップ2. シート名 配列を生成し、データの左に連結

data の 左側にシート名を連結させたいので、

Arrayformula(IF(SEQUENCE(ROWS(data))>0,cv,))

として dataの行数分 シート名が繰り返される 縦1列の 配列を用意します。

※ 1から最終行番号 ROWS(data) までの連番をSEQUENCEで生成、全て0より大きいので IFで シート名 cv に変換される。

ステップ3. データを Queryで空白行削除

ステップ2で作成した シート名を連結した範囲が、 Queryの式の範囲 

この太字部分です。 Col1はひたすら最終行まで シート名が詰まってますので、本来の シート A列である Col2 が空白でないものに絞り込んでいます。

このQuery式の結果が 一番左にシート名が追加され A列が空白の行が削除された配列データ 、つまり以下のような状態です。

再度登場すると長いんで、Query式の結果を x と置いています。

シート毎の xを どんどんプッシュして（重ねて）いけば最終的に全シート範囲結合となるわけです。

ステップ4. pv の下に 連結 （次のcv に移り また1から繰り返す） 

プッシュ処理 （縦連結）部分が {pv;x} です。pvが 1つ前のシートまでの xの縦積み配列、このpvの下に xを 結合していく処理を 繰り返して、下に下に追加され行が伸びていきます。

ただ、もしA4:Aの範囲が 全て空というシートがあった場合、x (Queryの結果）は、#N/Aを 返します。これを pvと縦結合しようとした場合、結合面（列数）が合ってないのでエラーになります。

途中で 1つでもエラーがあると、せっかくの長い式の 最終結果が エラーで終わってしまいます。

それを回避する為、

IFERRORで {pv;x} がエラーとなった場合は、そのまま前回までの配列 pvを返すようにしています。

式の中身はなんとなく理解できたでしょうか？

複数シートの範囲結合 式の動きを確認

まぁgif動画見ても「反映されたねー」くらいの感想とは思いますがｗ

sheetName3 の関数で 対象とするシート範囲を絞り込んだり、セル範囲を変更することで、色々なケースに対応できそうです。

また、アレンジして 部門（シート）毎の月ごとの合計の サマリ表を出力したり、全部門から 月ごとのMVP（もっとも実績が大きい人）を出力したりも出来そうですね。

Q3. 複数シートの横断検索（リンク付）

最後はこのお題、「検索と置換」でなら簡単に出来る 複数シート、かつ 行や列を指定せず、シート全体を対象とした横断検索を わざわざ関数でやろうってことです。完全に自己満式ですねｗ

Q1,Q2は シート名のみ利用だったんで、シートIDを使う例も取り上げておきたいなって狙いもあります。

条件は以下の通り

最終イメージは以下のような動作。

どうでしょう？結構 数式でやるには エグい処理ですが、作れそうでしょうか？

ここから回答
↓↓

A3. 複数シートの横断検索（リンク付）

まずは、いきなり 複数シートはハードルが高いので、1シートを 検索して リンク付きの アドレスを結果として返す式を作ってみましょう。

まずは 1シートの場合の 全範囲検索の式を作ってみる

検索対象のシート情報を 以下とします。

といっても、検索対象の 列や 行が指定されていれば XLOOKUP や XMATCHが 使えそうだけど、シート全体を検索ってどうやるんだ？？

ってなりますよね。ここは発想を転換しましょう。

検索ワードに 一致したセル を シート名!アドレス という表示かつ そのセルにジャンプする ハイパーリンク に変換して それ以外は 空白とする処理 だったら 出来そうでしょうか？

さらに、それを FLATTENで1列にしてから 空白以外で絞り込んだらどうでしょう？

いけそうな気がしてきたら、なかなかのスプ値です。

※スプ値 ・・・ スプレッドシートを使いこなせる能力を数値化したもの。
わたしのスプ値は 53万です。 ですが、もちろんフルパワーで記事を書く気はありませんからご心配なくｗ

これをLAMBDAを使った式にすると以下のようになります。

今回もインデント付き式にしておきましょう。中身は上と一緒です。

=Arrayformula(
  LAMBDA(target,
    LAMBDA(add,
      LAMBDA(x,
        FILTER(x,x<>"")
      )(
        FLATTEN(
          IF(target=A2,
            HYPERLINK("#gid=552005631&range="&add,"第1営業部!"&add),
          )
        )
      )
    )(ADDRESS(ROW(target),COLUMN(target),4))
  )('第1営業部'!A:Z)
)

まず、 Arrayformula +IF で 第1営業部シート のA:Z範囲の全てセルを 対象として A2（検索ワード）と一致したら ハイパーリンク付きアドレス、一致しなかったら空白を返すという処理にしています。

セルの A1表記を 取得する部分は 

ここですね。 ADDRESS関数に 行番号、列番号を 渡して、第3引数（絶対相対モード）を 4として、 $が付かない形で A1 表記のアドレスを返しています。

このADDRESS関数にシート名も入れ込めるんですが、結果として返される A1表記のアドレス（add）は、ハイパーリンクの URL部分

"#gid=552005631&range="&add

ここにも 使いたいんですよね。ここで addに シート名が含まれていると邪魔なんで、あえてシート名は ADDRESS関数内に含めていません。

この後の処理は以下のような流れ。

FLATTENした後の空白除去は、通常ならQ2と同じ流れで Query で where Col1 is not null とやりたいところですが、ここで Queryを使っちゃうと ハイパーリンクが死んで 単なる文字列 になっちゃいます。

ここが Query の悩ましいところなんですが、とりあえず 今回のようなハイパーリンクのまま絞り込みたいケース は FILTERを使うことで 対応できます。

LAMBDAを使うことで 重複部分をすっきり記述できるようになったのが良いですね。

この式を 複数セルに対して動く（繰り返し処理する）ようにするには、REDUCEと組み合わせれば OKです。（とはいえ結構これが難しい）

【回答】全シート、全範囲 検索の式

=LAMBDA(sheets,searchword,
  IF(searchword="",,
    ARRAYFORMULA(
      REDUCE(,SEQUENCE(ROWS(sheets)),
        LAMBDA(pv,cv,
          LAMBDA(x,
            LAMBDA(add,
              LAMBDA(y,
                filter({pv;y},{pv;y}<>"")
              )
              (
                FLATTEN(
                  if(x=searchword,
                    HYPERLINK("#gid="&INDEX(sheets,cv,2)&"&range="&add,index(sheets,cv,1)&"!"&add),
                  )
                )
              )
            )
            (ADDRESS(ROW(x),COLUMN(x),4))
          )(INDIRECT(index(sheets,cv,1)&"!A:Z"))
        )
      )
    )
  )
)(sheetNames3(0,true),A2)

うーん、LAMBD ５回は なかなかエグい。かなり ラムってますねー。

REDUCE部分を中心に少し解説をしておきましょう。

ポイント1. REDUCE で行単位の処理をする

今回は ハイパーリンク化するので、

sheetNames3(0,true) ・・・ sheets と置く

として、シート名とシートID の両方（２列）を取得しています。

これをそのまま REDUCEに使うと

と 各々 に対して 処理がループされちゃいます。

やりたい事は、

なので 、まず 行番号の配列を 生成して それを REDUCEに渡します。

それが この部分。 

SEQUENCE(ROWS(sheets)) 　・・・　配列の個々の値を cv と置く

で  1～sheets の行数 までの連番配列を生成しています。

各シートの シート名、シートIDは

これで取得しています。これで シート毎の処理としています。

再度 リンク載せますが、REDUCEに関してはGoogleスプレッドシートに登場時に検証記事を書いております。

もの凄い関数なんですが、もの凄く複雑なので そちらも参照ください。

ポイント2. 前回までの結果と結合してから FILTER処理

もう一つのポイントは、FILTERのタイミングでしょうか。

個々のシート毎の処理の中で  FILTERで空白除去をするのではなく、一つ前のシートまでの処理の結果 pv と縦結合してから FILTER処理をしています。

↑ この部分ですね。

理由は シート毎に FILTER処理をしちゃうと そのシートで検索が1件もヒットしないと #N/Aが返るんで、これを pvと結合すると・・・

このように pv 初期値の 空欄が一番上に残る上に、各シートの #N/A を含めた結果が全て返ってしまいます。

この対策で　filter({pv;y},{pv;y}<>"")　とpvと縦結合後に空白除去をしています。

これで完成。

複数シートの横断検索（リンク付き）を数式で 実現出来ました～。

検索を 一致ではなく 「含む」にしたい場合は、 if内の 一致の式を REGEXMATCH を使った式にすればよいです。 もちろん処理はさらに重くなりますが・・・。

他にも 検索にヒットしたセルの  行をまるっとを返すとか、ワード検索ではなく、特定の数値以上の実績を どこかの月で1回でも達成した  担当者名を返すといった アレンジも出来そうですね。

自作関数 活用のまとめ

今回は GASではなく、がっつりシート関数フル活用の数式回でした。

今回の記事を読んで 難しすぎるー、こんなんわけワカメ と思った方は気落ちしないでください。

これ、ゲームだと最終ステージ でよくある、今までのステージのボスが 3連発で登場したようなもんです。 難しいと感じるのは当然です。

いずれも 記事1回分くらいのレベルの内容ですが、面倒なんで 3つまとめて 1つの記事にしています。解説も駆け足ですし、食傷気味にもなりますねｗ

重めの活用例を3つも紹介したのは、「Googleスプレッドシート自作関数で シート情報を取得する」シリーズの記事の初回で書いた

「シート情報が取得できさえすれば、LAMBDAとかの 新関数と組み合わせて 一気に関数で出来ることが広がるのにー。」

これを実感して欲しかったからです。

GAS使いからしたら、

「LAMBDAでごちゃごちゃやるより、最初から 串刺し計算や 範囲結合の 自作関数作ればいいじゃない。 人間は愚かだね。」

て言われそうですがｗ

今回冒頭に書いた通り、GASによる シート名、シートIDを取得する 自作関数がなくても 手動でリスト化すれば 活用できます。

是非、ご自身の実データで活用してみてください。

そして同じく GASによる自作関数、こちらも理解し使えるようになれば一気にシート上での可能性が広がることがわかったかと思います。

検索すると色々と面白い自作関数を作ってる方が見つかるので、是非他のサイトも参考にしてみてください。

機会があれば、今回の3問をそのまま GAS関数として作成する場合や 他の自作関数についても記事を書きたいと思います。

で、次回の記事どうしようかなと・・・

旬な話題ってことで GoogleWorkspaceユーザー限定ですが「タイムラインビュー」について書こうかなと。 気が付いたら会社アカウントで 最近使えるようになってたし。

■次の記事