Googleスプレッドシート IMPORTRANGE関数超応用例 3(QUERY関数、LET関数と組み合わせ活用）

mir

2024年3月31日 15:37

Googleスプレッドシートを代表する便利関数のひとつ IMPORTRANGE関数についてまとめた note 、なんと第３弾です。

他のスプレッドシートのデータを取得し活用する際に必須となる IMPORTRANGE関数。

前回の noteでは、Excelのブックリンク機能との比較、そして Googleスプレッドシートの機能を使った IMPORTRANGE関数の超応用例を紹介しました。

今回は IMPORTRANGE関数と他の関数を組み合わせた超応用例（実際はそうでもない）を紹介していきます。

IMPORTRANGE関数と他の関数を組み合わせた式を作る時におさえるべきポイント

応用例その前に、IMPORTRANGE関数を他の関数と組み合わせて数式を作る際のポイントを押さえておきましょう。

NOWやRAND系関数とは組み合わせて使えない（と思った方がよい）
IMPORTRANGEは出力せず式内で処理してしまった方がよい
他の関数と組み合わせて IMPORTRANGEを使う場合は先に「アクセス許可」を
IMPORTRAGE関数の回数を減らす（なるべく範囲をまとめて取得）

IMPORTRANGE関数を使った式を作る上での注意すべきポイントは、この4つかなと思います。

1. NOWやRAND系関数とは組み合わせて使えない(と思った方が良い）

これは公式にも書いてある仕様ですが、IMPORTRANGE関数は 頻繁に更新される揮発性関数と組み合わせることができません。

確かに

=IMPORTRANGE($B$1,IF(NOW(),"シート1!A1:C10"))

このように、IMPORTRANGE内で NOWやRAND系の関数を使うとエラーになり、

この関数は、NOW()、RAND()、RANDARRAY()、RANDBETWEEN() を含むセルは参照できません

とエラーメッセージが表示されます。

かなり昔はこの方法が使えて、IMPORTRANGEのデータを無理やり更新させる方法として紹介されていた記憶があります。

もちろんサーバーの負荷が高くなるので、Google様によってこの方法は早々にNGになりました。

でも、「含むセルは参照できません」と記載があるわりには

参照したい元データのスプレッドシート側の範囲に RANDやNOWなどの揮発性関数を含むセルがあっても、IMPORTRANGEで参照できちゃいます。

参照できるんやないかーい！（ルネッサーンス）

ただし、取得した結果と元データを見比べていただくとわかりますが、

このように、まったく一致しておらず 元データのシートの計算結果の値を参照しているわけではないことがわかります。

これでは意味がありませんね。

これは Googleスプレッドシートの計算の仕組みが、ローカル（ブラウザ）側とサーバー側の両方で行われており、RAND系関数やNOW関数の場合はローカルとサーバーそれぞれの計算結果が一致しない為と思われます。

この困った仕様は、ビンゴゲームをGoogleスプレッドシートで作る回でも登（苦労）しました。

ちなみにこの RAND系関数が一致しない仕様は Web版Excel でも同様となっています。

余談ですが、インストール版のExcelを 新しいウィンドウを開く機能で、複数開いた場合は、RAND系関数の結果は一致します。

というわけで、IMPORTRANGEでRANDを使った結果を参照させても、違う結果を見ていることになる、つまり使えないと思った方が良いってことです。

ちなみに公式にも記載がありますが、例外的に TODAY関数はIMPORTRANGE関数と組み合わせて使うことが出来ます。（具体的な活用例が思いつきませんが）

2. IMPORTRANGEは出力せず式内で処理してしまった方がよい

※以降は基本的に SSIDというシートのA1セルにデータを参照したいスプレッドシートのIDが入ってることを前提とします

前回登場した、1回のIMPORTRANGEで取得できない巨大サイズのデータの参照をVLOOKUPで検索する式。

=VLOOKUP(A2,{IMPORTRANGE(SSID!A1,"シート1!A1:Z20000");
IMPORTRANGE(SSID!A1,"シート1!A20001:Z33500")},11,false)

これは、一度全データを以下の式でシート2に出力してから

={IMPORTRANGE(SSID!A1,"シート1!A1:Z20000");IMPORTRANGE(SSID!A1,"シート1!A20001:Z33500")}

その出力したシート検索範囲としてVLOOKUPする

=VLOOKUP(A2,'シート2'!A1:Z,11,false)

こんなやり方に置き換えることが出来ます。

体感的には速度にはさほど差はないんで、どっちを使ても良さそうに見えますが・・・

実はIMPORTRANGEを入れているスプレッドシートファイルのデータサイズに差がでます。

上の画像の通り、Chromeのタブのメモリ消費が圧倒的に違います。ただでさえメモリ消費の激しいChromeですから、出来れば節約しときたいところです。

取得した全データを表示させる必要がない場合は、IMPORTRANGEは出力させず、式の中で処理して結果だけ出力させる！

この方法がおススメです。

ただし出力なしで IMPORTRANGEを使うとうまくいかないケースがあります。

それは条件付き書式のカスタム数式にIMPORTRANGEを使う場合です。

IMPORTRANGEを条件付き書式のカスタム数式で使う方法

原則として条件付き書式のカスタム数式では、IMPORT系関数は使えないので、

=COUNTIF(IMPORTRANGE($F$1,"シート1!B:B"),A2)

※F1には参照したいスプレッドシートのIDが入ってる

たとえばこんな式で、別スプレッドシートのシート1のB列と重複する値がA2～A8にあった場合に色を付けると設定しても画像の通り重複に色がつきません。

（※ COUNTIFの結果が 0の場合は FALSE、1以上だとTRUE扱いとなることを利用）

では、IMPORTRANGEで取得した全データを一度別シートにでも出力してから、出力したデータを条件付き書式で参照するしか方法はないのか・・・。

そんなことはありません！

このように条件付き書式で使用する範囲と同じ範囲を取得するIMPORTRANGEを組み込んだ式がスプレッドシート上（別シートでも可）に1つでもあれば、範囲を出力することなく条件付き書式内でIMPORTRANGEが機能します。

画像は ↓ のようにカスタム数式そのままで、1セルの結果を返していますが

=COUNTIF(IMPORTRANGE($F$1,"シート1!B:B"),A2)

↓ このような INDEXで1セル分だけ出力といった式でもOKです。

=INDEX(IMPORTRANGE($F$1,"シート1!B:B"),1,1)

つまりセルで使う式の中に　IMPORTRANGE($F$1,"シート1!B:B")　が入っていれば、連動してカスタム数式内のIMPORTRANGEも機能するってことですね。

条件付き書式で IMPORTRANGEを使いたい場合は、一度全ての範囲を出力させて使うのではなく、この方法が良さそうです。

3. 他の関数と組み合わせて IMPORTRANGEを使う場合は先に「アクセス許可」を

IMPORTRANGEを他の関数と組み合わせて使う際、特に中カッコや VSTACK（HSTACK）で範囲（配列）結合した場合、式は正しいはずなのにエラーが出るといったケースにハマることがあります。

エラー以外にも例えば

=COUNTIF(IMPORTRANGE(SSID!A1,"シート1!B:B"),A2)

IMPORTRANGEで取得したデータをCOUNTIFした時に、なぜかデータは存在するのに 0と表示されてしまう。

これらは IMPORTRANGE関数を初回の「アクセスを許可」を未実施のまま使ってることが原因かもしれません。

通常はカーソルを当てると「アクセスを許可」のボタンが出るんですが、上のような他の範囲と結合したり 他の関数の中で使用した場合、「アクセスを許可」が表示されないことがあります。

解決方法はシンプルで、適当なセルに IMPORTRANGEで1セルだけ取得する式を作り、「アクセスを許可」して参照するスプレッドシートの接続許可をすること。これでエラーが解消されます。

一度アクセスできるようになれば、アクセスを許可用に入れた式は削除してしまってOK。

IMPORTRANGEは「初回のアクセスを許可が必須」という基本知識を知らず（もしくは忘れてて）、IMPORTRANGEと他の関数を組み合わせた式を組んでしまうとハマる罠ですね。

4. IMPORTRAGE関数の回数を減らす（なるべく範囲をまとめて取得）

たまに上のような変な記述をして 「IMPORTRANGEで飛び飛びの範囲を取得したいんですが、どうにも出来ません」 といった質問をしている人を見かけますが、当然これは出来ません。

IMPORTRANGEというかシート関数は、基本的にはまとまった範囲しか取得できない仕様なのです。

どうしても、IMPORTRANGE関数でＡ列の 1,3,5,7,11行目といった飛び飛び範囲を「取得」したい場合は

{ IMPORTRANGE(SSID!$A$1,"シート1!A1");
IMPORTRANGE(SSID!$A$1,"シート1!A3");
IMPORTRANGE(SSID!$A$1,"シート1!A5");
IMPORTRANGE(SSID!$A$1,"シート1!A7");
IMPORTRANGE(SSID!$A$1,"シート1!A9");
IMPORTRANGE(SSID!$A$1,"シート1!A11") }

正攻法というか愚直な式

このように 1つ1つ IMPORTRANGE関数で取得して中カッコと ; で縦に連結するといった方法で１つの式で出力したと言えなくもないですが・・・これはあまりお勧めしません。

式も長くなりますし、結局IMPORTRANGE関数を複数使用していることになるので、シートの動きが重くなってしまいます。

もし、飛び飛び範囲を IMPORTRANGEで取得したい場合は、一旦範囲をまるっと取得して、その後別の関数で必要範囲だけに絞り込む

このような式を組んで、 IMPORTRANGE関数の使用を減らすことを意識すると良いです。

では具体的にどのような式を組めばよいか、どの関数を組みあわせればよいか？

応用例に入っていきましょう！

IMPORTRANGE関数 + QUERY関数

まずは、IMPORTRANGE関数と相性のいい関数の代表格 QUERY関数からいきましょう。。

たまに他のスプレッドシートからデータを取得する際に、QUERY関数も必要なものと誤認している人もいて

=QUERY(IMPORTRANGE(SSID!$A$1,"シート1!A1:C10"),"select *")

QUERY関数を使う意味がないケース

こんな式を記述してたりしますが、これは意味がありません。

上の式はQUERY関数は不要で

=IMPORTRANGE(SSID!$A$1,"シート1!A1:C10")

これで十分です。

では、IMPORTRANGEとQUERY関数の組み合わせはどんな時に効果的なのか？

具体的な例を見ていきましょう。（例が多いので今回はお題形式ではありません）

行の末端（お尻）を決めずにIMPORTRANGE でデータを取得したい

一番多いのがこれですね。データが入っている行（現在は10行目）までを取得したいけど、今後データが増えていくので

IMPORTRANGE(SSID!$A$1,"シート2!A1:C10")

↑ こうではなく、

IMPORTRANGE(SSID!$A$1,"シート2!A:C")

↑ このように記述したい。でも、無駄な空白行は出力したくない。

こんな時はQUERY関数を使って IMPORTRANGEで取得した範囲を絞り込むとよいでしょう。

たとえば、この「最後の行まで」が 「A列（範囲の1列目）が空白ではない」と定義出来る場合

=QUERY(IMPORTRANGE(SSID!$A$1,"シート2!A:C"),"where Col1 is not null")

このように記述することが出来ます。

もちろん、絞り込み条件を指定して IMPORTRANGEで取得したデータの中で特定の条件を満たすものだけを表示したい場合も QUERY関数が使えます。

=QUERY(IMPORTRANGE(SSID!$A$1,"シート2!A:C"),"where Col2='りんご'")

=QUERY(IMPORTRANGE(SSID!$A$1,"シート2!A:C"),"where Col3 >= 400")

飛び飛びの列を IMPORTRANGEで取得したい

これもQUERYの得意とするところです。

飛び飛びの列範囲の取得、列の順番を並び替えなど、列データを Co1,Col2… と列番号で指定出来るQUERY関数なら、無駄にIMPORTRANGEを何度も書く必要なくスッキリ記述できます。

データはコチラから取得して利用 https://testdata.userlocal.jp/

例えば、上のようなL列まで（12列）あるマスターデータのスプレッドシートから、1,3,5,8,12列目を別のスプレッドシートに出力したい場合、

=QUERY(IMPORTRANGE(SSID!$A$1,"シート1!A:L"),"select Col1,Col3,Col5,Col8,Col12 where Col1 is not null")

このように記述することで、欲しい列だけに絞り込んだデータが取得できます。

もちろん列の順番の入れ替えも QUERY関数の SELECT句で対応できます。

select Col1,Col3,Col5,Col8,Col12
　▼
select Col12,Col1,Col8,Col5,Col3

並びを変えるだけ

※列番号は取得した範囲の左から1,2,3…という番号であって、A列が1、B列が2というわけではないので注意。

欲しい列だけを欲しい順番で取得できました。

IMPORTRANGEで取得したデータを並び替えたい

IMPORTRANGEで取得したデータを 並べ替えたい場合も、QUERY関数が有効です。

たとえば元データの3列目の 年齢を降順（年齢が高い方が上にくる）で並び替えしたい場合

=QUERY(IMPORTRANGE(SSID!$A$1,"シート1!A:L"),"select Col1,Col3,Col5,Col8,Col12 where Col1 is not null order by Col3 desc")

このように　order by Col3 desc　の記述を追加することで、3列目（年齢）のデータをキーに降順に並び替えが出来ます。

IMPORTRANGE + QUERY関数の魅力

IMPORTRANGE関数とQUERY関数の組み合わせで出来ることとして、代表的な データの絞り込み（空白除去）、列選択、並べ替え を紹介しました。

これらの処理は、それぞれ単体であれば他の関数でも代替できます。

たとえば条件によるデータの絞り込みであれば　FILTER関数。

列の取得や列の並び替えなら、CHOOSECOLS関数。

並べ替えなら SORT関数

しかし、空白を除去しつつ、列の選択、並び替えをしたうえで、さらにタイトル行は固定してデータを並べ替えといった 複合処理がたった一つの関数で出来るのは QUERY関数以外ありません。チートっすね。

先ほどのIMPORTRANGE関数とQUERY関数を組み合わせた最後の式、

=QUERY(IMPORTRANGE(SSID!$A$1,"シート1!A:L"),"select Col1,Col3,Col5,Col8,Col12 where Col1 is not null order by Col3 desc")

これをQUERY関数無しでやると

=SORT(FILTER(CHOOSECOLS(IMPORTRANGE(SSID!$A$1,"シート1!A:L"),1,3,5,8,12),CHOOSECOLS(IMPORTRANGE(SSID!$A$1,"シート1!A:L"),1)<>""),2,FALSE)

LET関数も使わなかった場合

こんな式になります。なかなか複雑ですね。

他には QUERY関数が真価を発揮するピボット集計もありますが、こちらはラスボス級なんで最後に触れたいと思います。

IMPORTRANGE関数 + LET関数他新関数

IMPORTRAGE関数との組み合わせで最強なのはQUERY関数で間違いないのですが、実は QUERY関数が苦手な処理もあります。

そこで活躍するのが 2023年2月に Excelから輸入された LET関数他新関数の面々です。

その中でも最高に相性の良い LET関数、そして割と使える CHOOSEROWS関数を紹介しましょう。

IMPORTRANGE関数 + CHOOSEROWS関数で飛び飛び行を取得する

先にCHOOSEROWSから。

QUERY関数はセル内のデータで絞り込むには便利ですが、飛び飛びの行番号を指定して取得したい場合や、1行飛ばしで取得といったケースには向いていません。

こんな時に活躍するのが 2023年新関数の一つ CHOOSEROWS です。

たとえば、取得したデータの1,2,5,10番目の行だけ欲しい、といった場合

=CHOOSEROWS(IMPORTRANGE(SSID!$A$1,"シート1!A2:C11"),1,2,5,10)

このように 取得したいデータの行番号を指定することで、1回のIMPORTRANGEから飛び飛びの行データを出力できます。

第2引数以降の列番号指定は配列も使えるので、mirの推し関 SEQUENCE関数を組み合わせれば

奇数データのみ

=CHOOSEROWS(IMPORTRANGE(SSID!$A$1,"シート1!A2:C11"),SEQUENCE(5,1,1,2))

偶数データのみ

=CHOOSEROWS(IMPORTRANGE(SSID!$A$1,"シート1!A2:C11"),SEQUENCE(5,1,2,2))

といった 規則性のある飛び飛び行の取得も可能です。

LET関数で IMPORTRANGEを変数に格納する

続いてLET関数とIMPORTRANGE関数の組み合わせをみていきましょう。

LET関数は、プログラミングの変数の概念をシート関数で使えるすごーい関数なんですが、これがIMPORTRANGE関数となぜ相性が良いのか？

さきほどの CHOOSEROWSの式を例に見てみましょう。

=CHOOSEROWS(IMPORTRANGE(SSID!$A$1,"シート1!A2:C11"),SEQUENCE(5,1,1,2))

IMPORTRANGEで取得した範囲を奇数行データのみとするこの式、このSEQUENCEの第1引数の 5は どっから出てきたんだ？ってなりませんか？

これは IMPORTRANGEで取得した範囲の行数が 10なんで、奇数だけ（または偶数）だけを取得すると、その半分の個数になるんで、10/2（10わる2）で 5としているわけです。

これを範囲に合わせて可変にしたい場合、どうしたらよいでしょうか？

ちなみに CHOOSEROWSは 行数を超える値を入れた場合、全体がエラーとなってしまうので、十分に大きな値をしていしておくという方法は使えません。

考え方としては

IMPORTRANGE(SSID!$A$1,"シート1!A2:C11")
IMPORTRANGEの取得範囲に対して

ROWS(IMPORTRANGE(SSID!$A$1,"シート1!A2:C11"))　・・・10
ROWS関数を使って行数を取得し

CEILING(ROWS(IMPORTRANGE(SSID!$A$1,"シート1!A2:C11")))・・・5
2で割って割り切れない場合は切り上げ。

こんな式を作ります。

※たとえば 11行の場合は 2で割ると5.5だが、奇数行は 1,3,5,7,9,11 行目の6行分のデータとなる。SEQUENCEは少数は切り捨て処理になるんで 5.5だと5と扱われてしまうので、第1引数を 6とする必要がある

小数の切り上げは ROUNDUP関数、もしくは CEILING関数の第2引数省略が使えます。

これをそのまま式にすると

=CHOOSEROWS(
IMPORTRANGE(SSID!$A$1,"シート1!A2:C12"),
SEQUENCE(CEILING(ROWS(
IMPORTRANGE(SSID!$A$1,"シート1!A2:C12"))/2),1,1,2))

こんな感じになります。IMPORTRANGEが2回登場してうげーって感じですね。

式が長くなるだけでなく、IMPORTRANGEが2つあることで無駄があります。

この式を LET関数を使って書き換えると

=LET(x,IMPORTRANGE(SSID!$A$1,"シート1!A2:C12"),
CHOOSEROWS(x,SEQUENCE(CEILING(ROWS(x)/2),1,1,2)))

このようになります。

最初に importrangeで取得した範囲を xと置いて、その後の式では xを使用する。

式も短くなり、IMPORTRANGEは1回で済み、なによりわかりやすいです。

IMPORTRANGEで取得したデータに空白行（列）を挟みたい

LET関数 + IMPORTRANGE関数。この組み合わせの応用例を、もう一つ取り上げましょう。

先ほどのIMPORTRANGEで取得したデータの奇数行だけ出力したいケースですが、上につめるのではなく間（偶数行）を空白にしたいケースもあるかと思います。

では、IMPORTRANGEで取得したデータを右のように 奇数行だけ出力（偶数行は削除するが上に詰めない）としたい場合、どんな式を作ればよいでしょうか？

これはお題を「偶数行の場合は空白を返す」と捉えると答えが見えてきますね。

IMPORTRANGE関数は配列なので、ROW関数が使えません。

ここは、SEQUENCE関数でバーチャルな行番号を振って、それが偶数、奇数で出力を分岐させます。

=LET(x,IMPORTRANGE(SSID!$A$1,"シート1!A2:C12"),
ARRAYFORMULA(IF(ISODD(SEQUENCE(ROWS(x))),x,)))

バーチャルな行番号は SEQUENCE(ROWS(x)) ですね。

これを ISODD関数で奇数の時にTRUEを返すとして IF関数を使って

TRUE 行番号が奇数の時　・・・　x
FALSE 行番号が奇数でない時　・・・　空白

とします。

この時、IMPORTRANGEの結果である配列に対してのIF関数の処理となる為、ARRAYFORMULA関数をつける必要があります。

上に詰めない奇数行だけを出力が出来ました。

では、間に空白（空白列）を入れたい場合はどうでしょうか？

IMPORTRANGEで取得したデータ（3列）の列と列の間に空白を1列ずつ挟みたい、こんな時はどうすればよいか？

=LET(x,IMPORTRANGE(SSID!$A$1,"シート1!A2:C12"),
padd,WRAPCOLS(,ROWS(x),),
CHOOSECOLS({padd,x},{SEQUENCE(3,1,2),SEQUENCE(3,1,1,0)}))

これは少し面倒で、取得データと同じ行数の空白の1列を用意する必要があります。

それが　WRAPCOLS(,ROWS(x),)

これですね。これをLET関数で padd と置きます。

WRAPCOLSで空白配列を生成する方法は以前のnoteで紹介しています。

最後のCHOOSECOLSでは何をやっているか？

それが、↑ こちらです。

まず {padd,x} で空白列と IMPORTRANGEで取得した 3列を横に連結しています。（1列目が空白列）

そして、　{SEQUENCE(3,1,2),SEQUENCE(3,1,1,0)}　では、上の画像の右側のような配列を生成しています。

CHOOSECOLSの第2引数に数値を要素とする 二次元配列を与えた場合、横（左から右）が優先 となる仕様なので、上の配列は CHOOSECOLSで

2,1,3,1,4,1

という順で指定したのと一緒です。

これによって2,3,4の間に1列目の空白列を挟めたわけです。

LET関数で IMPORTRANGE関数で取得したデータをXLOOKUPも簡単に

検索列が取得したいデータ（列）範囲の一番左にある必要がある VLOOKUPに比べ、XLOOKUPは検索の自由度が高く、つい忘れがちなFALSE指定もいらなので人気の検索関数です。

Googleスプレッドシートには 2022年9月に LAMBDA関数と共に追加された新関数です。

しかし IMPORTRANGE関数で取得したデータに利用する場合は、欲しい結果を列の番号（第3引数）を数値指定するVLOOKUPに比べ、検索範囲と結果範囲の両方の範囲指定が必要な XLOOKUP関数は、どうしても記述が長くなってしまうという弱点がありました。

こちらもLET関数を組み合わせることで、IMPORTRANGE関数で取得したデータに対してXLOOKUPをする時の記述をシンプルにすることが出来ます。

もちろん、取得した範囲の1列目を検索して3列目を返すだったらVLOOKUPで十分なんですが、たとえば 3列目（単価）を検索して 1，2列目を結果として表示させたい場合

=xlookup(A2,IMPORTRANGE(SSID!$A$1,"シート1!C2:C12"),
IMPORTRANGE(SSID!$A$1,"シート1!A2:B12"),)

こちらをIMPORTRANGEで A2:C12の範囲をまとめて取得した結果を LET関数で xと置くことで

▼

=LET(x,IMPORTRANGE(SSID!$A$1,"シート1!A2:C12"),
xlookup(A2,index(x,,3),choosecols(x,1,2),))

このような記述にできます。

index(x,,3) ・・・　範囲の3列目　（単体列は indexが便利）
choosecols(x,1,2)　・・・　範囲の1，2列目（複数列は CHOOSECOLSを）

範囲から欲しい列を取り出す記述は、このような書き方がおススメです。

超応用例：複数スプレッドシートを IMPORTRANGEで集計

最後に超応用例ということで、IMPORTRANGE関数を使った複数スプレッドシートの集計を見てみましょう。

たとえば、このように同じ構成の数字報告のスプレッドシートが担当ごとに用意されており、各自が自分のスプレッドシートを入力、上司はIMPORTRANGEで各スプレッドシートのデータを1枚のシートでまとめて見たい。

こんな構成を作るケースを見ていきましょう。

Googleドライブのコピー機能と REGEXEXTRACT関数で IDを取得

まず、最初にやるべきことはスプレッドシートIDの取得とアクセス許可です。なるべく楽にやりたいですよね。

ココで使えるのが、Googleドライブのショートカットによるコピー機能です。

Googleドライブ上で、スプレッドシートIDを取得したい（IMPORTRANGEでデータを取得したい）スプレッドシートを複数選択して Ctrl + C でコピーします。

サマリーのスプレッドシートに SSIDシート（スプレッドシートID管理シート）を用意し、A列に Ctrl + V で貼り付けして（ハイパーリンク状態の）スプレッドシート名を、B列に Ctrl + Shift + V でスプレッドシートのURLを貼り付けとします。

これは、Googleドライブ内の画像のURLを一気に取得したい時に使ったテクニックと一緒ですね。

しかし、この方法で取得できるURLは

https://drive.google.com/open?id=スプレッドシートID&usp=drive_copy

初回でも書きましたが、IMPORTRANGEでは使えないURLです。

その為、ここからスプレッドシートID部分を取得する必要があります。

C列にIDという列を用意して、 REGEXEXTRACT関数を使って正規表現で、id= と &usp の間の文字列を取得しましょう。

=ARRAYFORMULA(IFERROR(REGEXEXTRACT(B2:B6,"id=(.+?)&usp")))

B2:B6部分は少し広めに範囲を取得

※REGEX系関数の説明はそのうち noteでやりたいと思います

スプレッドシートIDが取得できました。

MAP関数で一気にIMPORTRANGEのアクセス許可用の式を生成

さらに、このシートでアクセス許可もしておきましょう。

=MAP(C2:C6,LAMBDA(v,if(v="",,importrange(v,"2024!A1"))))

シート名はどのスプレッドシートも "2024" で同一なので、違いはスプレッドシートIDのみ、ここで使えるのは MAP関数です。

IMPORTRANGEはARRAYFORMULAは効きませんが、LAMBDAヘルパー関数であれば一つの式で一気に処理ができます。

ただし「アクセスを許可」用の式は一括で生成できても、「アクセスを許可」のボタンは一つ一つ手作業で押す必要があります。

ここは良い方法がないので、諦めて従いましょう。最初の1回だけですし。

これで準備はOKです。このシートは後で、スプレッドシート名からVLOOKUPでIDを取得する処理に使います。

サマリシートもIMPORTRANGE関数 + MAPで

準備ができたら、サマリ―シートに1行式を入れるだけで完成です。

外枠のデータ（月と名前）は入っている状態で B4セルに

=MAP(B3:E3,LAMBDA(v,
IMPORTRANGE(VLOOKUP(v&"報告",SSID!A:C,3,false),A1&"!B4:B15")))

こちらも MAP関数を使っています。

3行目の担当者名を対象範囲として、そこから一つ一つ取り出したセルを vとして、v&"報告" でスプレッドシートのファイル名を用意しています。

これを検索キーとして、SSIDシートの表からVLOOKUPでスプレッドシートIDを取得。

シート名は 2024ですが、A1セルは表示形式を 0年としていて、中身は2024なんで、A1セルを参照で利用。範囲は固定ですね。

IMPORTRANGE(VLOOKUP(v&"報告",SSID!A:C,3,false),A1&"!B4:B15")

取得したスプレドシートIDとシート名、固定のセル範囲で、IMPORTRANGEで1列のデータ（B4:B15）を取得する処理を、担当の数（B3:E3）だけ繰り返しています。

Googleスプレッドシートの MAPなど LAMBDAヘルパー関数は、1列データ、または1行データであれば各処理で配列を返すことが出来るので、このような割と短い式で処理できます。

下準備は必要ですが、複数スプレッドシートの場合も 同じスプレッドシート内の複数シートのデータを集計する流れと基本的には一緒ですね。

超応用例：複数スプレッドシートを IMPORTRNG + QUERY関数でピボット集計

Excelで集計をする際に活躍するピボットテーブルの機能ですが、Googleスプレッドシートにも Excelと同じように ピボットテーブルがあります。

ExcelにはPowerPivot （パワーピボット）がある

しかし、Gooleスプレッドシートのピボットテーブルの集計対象は、同一スプレッドシート内の1つの範囲しか指定できません。

他のスプレッドシートを対象としたり、複数範囲や範囲を結合したり、数式で生成した配列を対象としたピボットテーブルは作成できないってことです。

Excelでも通常のピボットテーブルでは複数の範囲（テーブル）を対象としたピボットテーブルはできませんが、PowerPivot（パワーピボット）いうツールがあり、これを使うことで複数ブックにまたがる複数のテーブルを対象としたピボット集計が可能となります。

GoogleスプレッドシートはQUERY関数で解決

Googleスプレッドシートで複数のスプレッドシートのデータからピボット集計をしたい、でも結合した表を出力せずにやりたいって場合は、やはり

IMPORTRANGE関数 + QUERY関数

こちらの組み合わせを使うことになります。

たとえば、先ほどの例の流れで SSIDというシートで

このようにスプレッドシートIDとシート名を用意していた場合、

各スプレッドシートの売上実績というシートの A:C範囲の

このようなリスト表を集約して年、月、商品の売上を担当者ごとにピボット集計したい場合

=ARRAYFORMULA(
  LET(a,SUBSTITUTE(SSID!A2:D5,"報告",),
    b,REDUCE(,SEQUENCE(ROWS(a)),
      LAMBDA(pv,cv,LET(x,
        QUERY(IFERROR(HSTACK(index(a,cv,1),IMPORTRANGE(index(a,cv,3),"売上実績!A2:C")),index(a,cv,1)),
        "where Col2 is not null"),
        IFERROR({pv;x},x))
      )
    ),
    QUERY({TO_DATE(index(b,,2)),CHOOSECOLS(b,1,3,4)},
      "select year(Col1),month(Col1)+1,Col3,sum(Col4)
       group by year(Col1),month(Col1),Col3 
       pivot Col2
       label year(Col1) '年',month(Col1)+1 '月',Col3 '商品'"
    )
  )
)

こんな感じに ARRYAFORMULA、REUDCE、QUERY、IMPORTRANGE、その他色々な関数をフル活用したハードモードな式を作成することで実現できます！

解説はいつの日か QUERY関数超応用例で！

これを解説しようかと迷いましたが、これってIMPORTRANGE関数の超応用例というより、完全に QUERY関数の超応用例だな・・・。

ってことで、今回は長くなりましたし、ここまでとしたいと思います。

いつか執筆予定の QUERY関数超応用例で、こちらを解説したいと思います。

IMPORTRANGE関数の活用はまだまだこれからだ！（完）

というわけで、最後のラスボス戦がダイジェストで一気に後日談みたいな感じの終わり方となりましたがｗ

書いた通り、最後の複数スプレッドシートのピボット集計は、どう考えても主役は QUERY関数とREDUCE関数なんで

「また同じネタを書いた時に恥ずかしいからね」

ってことで、IMPORTRAGE関数の超応用例シリーズはここまでとします。

しかし、IMPORTRANGE関数の人の心を知る旅路は、これからも続きます！（だか～ら～もう一度～、生まれ変わろ～とも～♪）

来週は軽めのGASネタでも書こうかなと。