【GAS】Googleスプレッドシート自作関数でシート情報を取得する -3 活用編

mir

2022年11月19日 11:27

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

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

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

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

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

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

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

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

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

複数シートの串刺し計算
複数シートの範囲結合
複数シートの横断検索（リンク付）

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

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

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

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

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

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

=ARRAYFORMULA("シート"&SEQUENCE(10))

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

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

サンプルデータの構成

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

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

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

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

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

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

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

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

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

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

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

串刺し計算の要件

・〇枚目～〇枚目 という形で対象シートを指定
　　※第1営業部から第5営業部（2枚目～6枚目）とする
・指定した複数シートの同じ範囲（複数セル）を全て合計する
　　※売上数値のセル B4:D10 とする
・シートの開始番号、終了番号、セル範囲を引数とする一つの式にする

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

ここから回答

↓↓

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

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

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

sheetNames3 ・・・　範囲を指定してシート名を取得する自作関数
SUM　　　　・・・　合計を出すので当然必要
INDIRECT　　・・・　シート名の文字列からシート参照するのに必要
LAMBDA + BYROW　・・・　繰り返し処理をするのに必要　

前回作成した 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(A3:A5,LAMBDA(row,INDIRECT(row&"!"&B3)))

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

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

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

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

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

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

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

ユーも SUM しちゃないよ！

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

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

1. 対象のシート 2枚目から6枚目のシート名を取得
　=sheetNames3(1,,,2,6)

2. シート名配列に対してBYROW + INDIRECT + SUMで
　各シートの B4:D10 範囲の合計を取得
　=BYROW(A2:A6,LAMBDA(row,SUM(INDIRECT(row&"!B4:D10"))))

3. シート毎の合計値をSUMで合計 → 指定したシートの範囲の総合計
　=SUM(C2:C6)

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

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

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

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

=LAMBDA(start,end,range,SUM(BYROW(sheetNames3(1,,true,start,end),LAMBDA(row,SUM(INDIRECT(row&"!"&range))))))(2,6,"B4:D10")

※ 2が開始シート番号、6が終了シート番号、"B4:D10" が対象範囲

このくらいの長さならインデント不要かな

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

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

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

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

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

=LAMBDA(start,end,range,ARRAYFORMULA(SPLIT(BYROW(sheetNames3(1,,true,start,end),LAMBDA(row,row&","&SUM(INDIRECT(row&"!"&range)))),",")))(2,6,"B4:D10")

↓ A4:B8のような各シート毎の合計サマリ表を作れる

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

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

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

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

=Query({'第1営業部'!A4:D;'第2営業部'!A4:D},"where Col1 is not null")

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

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

たとえば要件が

・式の入っているサマリーシート以外の全シートのデータを結合させたい
・いずれのシートも対象範囲は A4:D これを縦に重ねたい
・A列が空白の場合はデータなしとして除外したい
・一番左の列にシート名（部署名）も入れたい

こんな感じだと今までは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")

※ sheetNames3(1,true) ・・・自シート以外のシート名配列
　"A3:E3" ・・・タイトル行範囲
　"A4:E"　・・・　結合するデータ範囲

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

=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 のループ処理の準備

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

冒頭部分　=LAMBDA( sheets, title, range,
対比する引数部分　( sheetNames3( 1, true ), "A3:D3", "A4:D" )

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

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

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

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

=REDUCE( {"シート名",INDIRECT(INDEX(sheets,1,1)&"!"&title)},sheets,
　LAMBDA(pv,cv, ・・・
pv ・・・ひとつ前のシートまでの範囲が結合された配列
cv ・・・ sheets から一つずつ取り出したシート名

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

シート名(cv) と範囲文字列の INDIRECT でデータ（二次元配列）を取得
シート名配列を生成し、データの左に連結
データを Queryで空白行削除
pv の下に連結（配列プッシュ）
次の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の式の範囲

Query({IF(SEQUENCE(ROWS(data))>0,cv,),data},"where Col2 is not null")

この太字部分です。 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)

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

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

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

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

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

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

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

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

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

条件は以下の通り

・A2セルに入れた 1つのキーワードの完全一致セルを検索
・検索対象のシートは式を入れるサマリシート以外の全シート
・検索範囲は各シートの A:Z
・一致したセルの 「シート名!A1表記のアドレス」を返す
・結果はハイパーリンクになっており、クリックでその場所に飛べる
・複数ヒットした場合は、全ての結果を返す （縦方向に展開させる）

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

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

ここから回答
↓↓

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

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

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

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

シート名：　第1営業部
シートID：　552005631

といっても、検索対象の 列や行が指定されていれば 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(
  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(ROW(target),COLUMN(target),4)
※ target の中身は '第1営業部'!A:Z

ここですね。 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)

↓同じ式のインデントつけた記述

=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に使うと

1回目シート名1
2回目シートID1
3回目シート名2
4回目シートID2
…

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

やりたい事は、

1回目 [ シート名1, シートID1 ]
2回目 [ シート名2, シートID2 ]
…

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

REDUCE( , SEQUENCE(ROWS(sheets))

それがこの部分。

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

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

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

シート名　・・・　index(sheets,cv,1)
シートID　・・・　index(sheets,cv,2)

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

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

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

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

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

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

filter({pv;y},{pv;y}<>"")

※ yは個々のシートの FLATTEN による 1列化までしたデータ FLATTEN(if(x=searchword,HYPERLINK("#gid="&INDEX(sheets,cv,2)&"&range="&add,index(sheets,cv,1)&"!"&add),))

↑ この部分ですね。

理由はシート毎に 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ユーザー限定ですが「タイムラインビュー」について書こうかなと。気が付いたら会社アカウントで最近使えるようになってたし。

【GAS】Googleスプレッドシート自作関数でシート情報を取得する -3 活用編

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

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

サンプルデータの構成

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

串刺し計算の要件

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

INDIRECT を理解する

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

自作関数活用のまとめ

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

■次の記事

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

【GAS】Googleスプレッドシート自作関数で シート情報を取得する -3 活用編

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

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

サンプルデータの構成

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

串刺し計算の要件

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

INDIRECT を理解する

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

自作関数 活用のまとめ

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

■次の記事

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

【GAS】Googleスプレッドシート自作関数でシート情報を取得する -3 活用編

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

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

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

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

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

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

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

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

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

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

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

自作関数活用のまとめ