Googleスプレッドシート IMPORTRANGE関数 超応用例 2
Googleスプレッドシートを代表する便利関数のひとつ IMPORTRANGE関数についてまとめた note 第２弾です。
他のスプレッドシートの情報を取得し活用する際に必須となる IMPORTRANGE関数。
前回の noteでは、IMPORTRANGE関数の基本やおススメの式の書き方、「アクセスを許可」の挙動、そして使用する上での注意点をお伝えしました。
世の中の多くの IMPORTRANGEの解説記事、サイトは、第1弾の基本程度の内容で終わっています。（第1弾の半分以下の内容も多い）
mirの noteは情報量が違います。ここからが本番です。
IMPORTRANGE関数の最高到達点（どちらかというとマニアックな深み）へと入っていきましょう！
Excelのブックリンク（外部参照）との比較
超応用例の前に、少し Excelとの比較も入れておきましょう。
たまに、GoogleスプレッドシートのIMPORTRANGE関数は Excelには存在しない便利関数とか、ExcelにもIMPORTRANGE関数欲しいなんて声を見かけます。
これが、IMPORTRANGE関数が便利で使い勝手がいいという意味なのか、Excelが別ブックを参照できないと思ってるのか、発言の真意はわかりませんが、Excelには別ブックを参照する機能が昔からあります。
この機能は現在は ブックリンク という名称になっています。（前は外部参照だった）
「Excelが機能で対応しているものを、Googleスプレッドシートはなんでも関数で解決しがち」とよく言われますが、まさにこの別ブック参照もそれですねｗ
Excelのブックリンクは Web版でも使える
Excelの場合の他のブックの参照（ブックリンク）は、ファイルパス にシート名、セル位置を合わせた形で参照します。
ファイルパスは、そのファイルのシステム内の保存場所を表したもの、つまり住所（アドレス）みたいなものです。
例えば、上の場合は
「Cドライブ」内の「検証用」というフォルダ内の「リンク2.xlsx」というExcelブックの 「Sheet5」というシートの A2セルを参照しているわけです。
このブックリンクはOneDriveに保存されている Excelを Web版Excelで開いた場合も同様に使えて
='https://d.docs.live.net/ユニークなOneDriveのID/sansyo/[参照テスト用.xlsx]Sheet3'!$A$1:$C$10
このような記述で、OneDrive内の 「sansyo」というフォルダにある「参照用.xlsx」というブックの Sheet3の A1:C10 を参照することが出来ます。
ただし、Web版とローカル保存のExcelでは上記のようにファイルパスの記述方法が違いますし、当然ですがローカルファイルをWeb側から見れちゃったらセキュリティ上問題ががあるので、
Web版 OneDrive保存のExcelブック ⇔ ローカル保存のExcelブック
このような Webとローカルをまたいだブックリンクは出来ません。
ローカル保存同士、またはOneDrive内のExcel同士でのみ、ブックリンク（別ブックの参照）が可能となります。
参照したい箇所を選択するか、リンク貼り付けだけ で参照できる
フォルダパスとか 記述するの難しくね？
と思うかもしれませんが、ユーザー側ではあまり記述を意識する必要はなく、以下の2つの方法で簡単に 別ブックのデータ参照が出来ます。
※ 紹介する方法はEdgeブラウザでは使えますが、Chromeだと正しく動かないことがあります。
1つはいわゆる、リンク貼り付け を使う方法です。
参照したいデータをコピー して、出力させたいセルで
右クリック ＞ 形式を選択して貼り付け ＞ ソースへのリンク
同じシート内や ブック内の別シートの場合、参照したいセル範囲をコピーして別のセルにリンク貼り付けすると、自動的に参照する式が入るんですが、これが他のブックを対象とした場合やWeb版Excelでも使えます。
2つ目は セルの先頭に イコールを入れて「セル範囲の選択」で選択で式内の参照を作成する方法。
出力したいセルに = と打ってから、参照したい ブックの範囲を選択
こちらも 別セルや別シートに使う時と同じ操作感で、Web版でも別ブックを選択できるので簡単です。
どちらの方法もGoogleスプレッドシートでは出来ない、Excelならではの 別ブックの参照方法です。便利ですね。
ちなみに、個人的にはスピル対応しているWeb版Excelを使うなら 2番目の =を打って参照範囲を選択 の方法がおススメです。
理由は リンク貼り付け で外部ブックの複数セル範囲を参照した場合は
このように、1セル１式 という状態になってしまうから。
せっかくスピルが使えるので、一つの式で範囲をまるっと取得した方が良いですよね。
Excelの他ブック参照も初回のお作法がある
Googleスプレッドシートは、IMPORTRANGEで別スプレッドシートを参照する場合、初回に「アクセスを許可」をする必要がありました。
実はこれに近い初回のお作法が Web版Excelでもあります。それが、「ブックのリンクを信頼する」です。
Googleスプレッドシートの「アクセスを許可」は元データのある参照される側のファイルが、アクセス（参照）していいよ～と許可をするもので、参照される側の視点です。
一方、Excelの「ブックのリンクを信頼する」は式が入っている方、つまり参照データを出力する「このファイル」からの視点といえます。
似たような仕組みですが、考え方が違うのは面白いですね。
Excelのブックリンクの細かい挙動を ガチで取り上げると、かなりのボリュームになっちゃうんで紹介はこれくらいにしておきましょう。
Excel ブックリンクと Googleスプレッドシート IMPORTRANGE関数の比較
本題のGoogleスプレッドシートのIMPORTRANGE関数との比較に入っていきます。
上の表は、Web版のExcelのブックリンクとGoogleスプレッドシートのIMPORTRANGE関数のそれぞれの検証項目ごとの対応（利便性）を比較したものです。
結構 IMPORTRANGE関数も〇が多いですね。
もちろん、1番目の 参照する時の式を作成する手間は、範囲の選択やリンク貼り付けといった機能でちゃちゃっと作成できるExcelの方が圧倒的に簡単です。
IMPORTRANGE関数は スプレッドシートID確認して、手入力でシート名、セル位置を記述するんでミスしやすいし面倒！
しかし、2番目の同期に関しては Web版 Excelはタイムラグを感じるケースが多いです。
元データのブックの変更が 数分反映されなかったり、F5更新を2回くらいやってようやく反映といったこともあります。（録画してみたんですが、流すぎるんで割愛）
比べて GoogleスプレッドシートのIMPORTRANGEは同期のリアルタイム（即時性）は優秀という印象。以前は表示が更新さないことも多かったんですが、技術が進歩しているのか 反映は安定していて早くなっていますすね。
3番目の「関数によるデータの切り替え」ですが、前回書いた通り、IMPORTRANGE関数は引数が文字列なので、数式や別セルからの参照、ドロップダウンリストなどを使って、簡単に参照を切り替えることができました。
一方、Web版Excelの場合、INDIRECT関数による 他ブック参照は出来そうだけど出来ません。
厳密に言うと、別で 直接 別ブックを参照している箇所と重複する部分だけは、INDIRECTでも別ブックのデータが取得が出来ます。（これは取得できてるとは言わない？）
Web版Excelだと 文字列を使った別ブック参照は、ちょっと難しそうですね。（もしかすると方法があるのかも）
ちなみに インストール版の Excelだと 参照したいデータの入った Excelブックを開いた状態であれば INDIRECTによる参照が可能です。
※もしやり方がある場合はお知らせください。
そして4番目のリンク切れリスク。
これがExcelの別ブック参照だと結構発生します。
参照しているExcelブックのファイル名を変えたり、保存先を変更したり、保存しているフォルダ名を変えたり、いずれの場合もリンク切れとなります。
リンク切れ後はセルにエラー表示が出るわけではありませんが、その後の更新は反映されなくなります。
一方 Googleスプレッドシートは ユニークな スプレッドシートIDによる接続なので、保存場所変更やファイル名変更ではリンク切れは発生せず、一度接続するとリンク切れしづらいというメリットがあります。
最後に 5番目の共有していないブックの参照ですが、Googleスプレッドシートと違って、ブック参照を使って一部のシートの情報だけ共有メンバーに見せるといった方法が Excelでは出来ないようです。
上の場合Bさんがブックリンクを作ってあげれば、Aさんはそれを見ることは出来るんですが、Aさんがブックその1を 開いても データの更新がされないってことです。
これは 共有利用の多いWeb版では 残念な点かもしれません。
元々個人の利用から進歩していった Excel とチームコラボレーションツールという設計思想のGoogleスプレッドシートの違いと言えるかもしれませんね。
他にも Excelのブックリンクは
別ブックの空白セルを参照した時の 0 表示を考慮する必要あり
スピル非対応のExcelだと1セル単位で式が必要（CSE 数式利用可能）
といったデメリットもあります。
式の作成はちょっと面倒なIMPORTRANGE関数ですが、Excelのブックリンクと比べて意外とメリットが多いことが、おわかりいただけたでしょうか。
※ Excelのブックリンクは 無料Web版で検証し挙動をまとめていますが、内容の間違いや 365版では違うといったご指摘があればお知らせください
※ インストール版のExcelの場合は データの取得 など他の機能を使った方が良いケースもあります
IMPORTRANGE関数の超応用例（機能で突破する）
それでは IMOPORTRANGE関数の超応用例いってみましょう。
まずは、Googleスプレッドシートの機能を理解して「出来ない」を突破する超応用例から。
IMPORTRANGEは「シート名やセル範囲 の変更に対応できない」を「名前付き範囲」で突破する
Excelとの比較で Googleスプレッドシートの IMPORTRANGE関数は、リンク切れを起こすリスクが低いと書きましたが、元データが入っているシートの シート名変更 には弱いです。
たとえば、上の画像のように「シート3」というシート名を「シート3a」に変更した場合、IMPORTRANGEで参照していたデータは #REF!エラーとなり、
インポートした範囲の範囲またはシートが見つかりません。
とエラーメッセージが出ます。
IMPORTRANGE側のシートの指定が文字列なんで、仕方ないっちゃ仕方ないんですが。。Excelと違って 即エラー表示（悪・即・斬）です。
ちなみに、このメッセージが出るのは、ほぼ第2引数の記述ミスで、スプレッドシートIDが間違っている（存在しない）場合は
このようなメッセージになります。
また、同じく第2引数で文字列で指定している セル位置の方も
このように 元データのスプレッドシートB側で 行や列、セルの追加があった場合、参照セル範囲が連動するといったことも出来ません。
では、この問題をどう突破するか？
IMPORTRANGE関数で シート名の変更や、セル範囲の変更が連動されない問題は、「名前付き範囲」を使うことで突破できます！
このように 参照したい元データの入ったスプレッドシートで、参照したい範囲を データ ＞ 名前付き範囲で 、適当な名前をつけます。
=IMPORTRANGE("スプレッドシートID","範囲の名前")
すると、このように IMPORTRANGEの 第2引数を 「範囲の名前」だけで指定出来るようになります。
これを使うと・・・
そうです！
シート名の変更や行挿入、削除によるデータのセル位置の変更も、元データの「名前付き範囲」で吸収してくれるので、IMPORTRANGE関数側はリンク切れや 範囲ズレを起こすことなく、しっかりデータが連動されます。
途中に行を追加して表を拡張した場合も、IMPORTRANGE +「名前付き範囲」であればこのように自動的に連動して拡張してくれます。
「名前付き範囲」をIMPORTRANGEすると式も簡略化されますし、相手にシート名を知られたくない場合にも使えます。
非常に便利な 「名前付き範囲」 ＋ IMPORTRANGE関数
是非活用ください。
IMPORTRANGEで「常に現在の月のシートの情報を取得したい」を シート指定なしで突破する
報告や集計のスプレッドシートは 、月ごとにシートを分けて運用している人も多いかと思います。（集計しづらいからあまりよくないんですが）
このようなスプレッドシートを IMPORTRANGEで常に現在の月のシートを参照したい時、どうすればよいでしょうか？
もちろん IMPORTRANGEを入れたシート側で、シート名をプルダウンで切り替えたり、
MONTH(TODAY())&"月!A1:C10")
こんな式で自動で本日の月のシート名を生成する方法もありますが、さらに簡単なちょっとした運用ルールで、これを実現することが出来ます。
それは、常に「ユーザーは現在の月の（現在使ってる）シートを 1番左に移動させる」というルールで運用すること。
あとは IMPORTRANGEの式をシート名無しで
=IMPORTRANGE($B$1,"A1:C10")
このようにするだけ。
ユーザー側でシートの並びを変えると、IMPORTRANGE関数が入った管理者シート側は連動して参照するシートが変化しているのが分かりますね。
前回の noteでチラッと触れた
もしシート名を忘れて（省略して）、第2引数を "A1:C10" とした場合は、エラーにはならず 先頭のシート（一番左のシート）のセルを参照するという動きになります。
IMPORTRANGE関数の この特徴を使って
今使ってる（参照させたい）シートを一番左にするだけ
これだけで運用できる、最新シートのIMPORTRANGEの情報取得テクニックでした。是非活用ください！
編集権限を与えたスプレッドシートからIMPORTRANGEすると、元データの入ったスプレッドシートを探索される恐れがあるを チェーン（連鎖）で突破する
IMPORTRANGE関数は、一部のシートだけを相手に見せたいときに便利です。
ただし、そのIMPORTRANGE関数を入れているスプレッドシートの共有相手を編集権限としている場合は注意が必要です。
ケーススタディ形式で説明しましょう。
ある組織のリーダーが1つのスプレッドシートで シートを人毎に分けて 3名のメンバー 佐藤、田中、鈴木の 評価を入れていたとします。
メンバーには編集権限で共有した 各々の名前の評価のスプレッドシートを用意し、 IMPORTRANGEで参照させて その人の評価だけを見せる、こんな仕組みで運用しているケースを考えていましょう。
今回評価の低かった 田中さんは、他の奴の評価気になるなー、ちょっと覗き見したいなーと思っています。
でも当然 元スプレッドシートは共有されていないので、スプレッドシートのIDやURLがわかっても 開くことはできません。
でも、自分の評価を出力している式 を見ると・・・
もしや、この式の 田中の部分を他のメンバーの名前に変えたら、他の奴の評価見れるんじゃね？
こう思いつきます。そして、つい魔が差して・・・
このようにシート名をいじって他のシート（他の人の評価）を 覗き見してしまったわけです。
この後、田中さんが 覗きにハマって、盗撮犯罪に堕ちていくんですが、それはまた別のお話。と、たとたえ話はこれくらにして、
このように「アクセスを許可」したスプレッドシートは、元データの入ったスプレッドシートの全情報を取得できるって問題があります。
もちろん、相手が編集権限があることが前提で、少しはIMPORTRANGE関数の心得がないと思いつかないですし、シート名を推察する必要はあります。
しかし、先ほど紹介した通り シート名無しで1枚目（1番左）のシートが参照できることを知っていれば、1枚目シートは確実に見られちゃいます。
まぁ、これをやると編集履歴にログが残るんで、本当に詳しい人はやらないとは思いますが。。。
とりあえずは、他の部分を見せたくない共有していないスプレッドシートでも、IMPORTRANGEで連携（参照）しているスプレッドシートの編集者からは見られるリスクがあるってことは覚えておきましょう。
これの対策は2つあります。
IMPORTRANGEで連携したスプレッドシートは閲覧権限のみとする
中間スプレッドシートを使って2段階連鎖とする
閲覧権限のみで良ければそれがベストですが、どうしても編集権限を与える必要がある時、2つ目の方法 「2段階連鎖」を使うという手があります。
「2段階連鎖」とは、このように まず 田中さんの評価（中間）から、全員の評価の田中さんのシートを IMPORTRANGEで参照して、さらに 田中さんの評価 から 田中さんの評価（中間）をもう1回 IMPORTRANGEで参照するという 2段構えの構成にすることです。
作成は面倒ですが、こうすることで 田中さん他メンバーは 全員の評価のスプレッドシートには一切アクセスできず、セキュアな部分共有が実現できます。
また一度構築してしまえば、リーダーさんは全員の評価スプレッドシートで、シートを切り替えて評価を入れるだけで、自動で同期されるので手間はありません。
万が一にも見られたくないスプレッドシートだけど、どうしても一部分だけ公開して同期させたい！ こんな時は IMPORTRANGEの2段階連鎖 おススメです。
巨大データの IMPORTRANGE を分割 や 相互参照 で突破する
スプレッドシートをデータベース的に利用している場合、例えば商品一覧の入ったスプレッドシートを別スプレッドシートから VLOOKUP + IMPORTRANGEで参照して検索、なんて使い方はあるある じゃないでしょうか？
しかし、参照したいデータの表が 巨大で、たとえば 20列、3万行越えくらいのサイズだと、上のように「結果が大きすぎます。」とエラーが出ることも。
公式だと
読み込むデータの上限はリクエストごとに 10 MB
このように記載されてますが、感覚的には 50万セルくらいになってくると厳しいという印象。
では、このような巨大なサイズのデータをIMPORTRANGEで扱いたい時は、どうすればよいか？
これを解消する為の方法として、分割IMPORTRANGEという方法があります。
これだとエラー
=VLOOKUP(A2,IMPORTRANGE(SSID!A1,"シート1!A1:Z33500"),11,false)
▼
これで解決
=VLOOKUP(A2,
{IMPORTRANGE(SSID!A1,"シート1!A1:Z20000");
IMPORTRANGE(SSID!A1,"シート1!A20001:Z33500")},
11,false)
1つのIMPORTRANGEの限界を超えたデータサイズの場合、このように 上下（または左右）でデータを分割して、それぞれIMPORTRANGEで取得してから、中カッコで配列連結する
{IMPORTRANGE(SSID!A1,"シート1!A1:Z20000");
IMPORTRANGE(SSID!A1,"シート1!A20001:Z33500")}
こんな方法で 結果を取得できたりします。（もちろん 配列の連結は VSTACKでもOK）
車に乗らないような大型家具を 一度2つに分解して、車2台で運んで、現地で再度組み立てる。こんなイメージですね。
しかしさらに巨大なデータだと3分割、4分割と 式が複雑になっていく上に、分割方法でも解決できないケースが出てくることも。
そんな時は 相互IMPORTRANGE という方法が使えます。
上の画像のように
① 検索キーを データベースのあるスプレッドシートの別シートで IMPORTRANGEで参照（参照検索 ⇒ 製品データベース）
②VLOOKUPの検索処理は データベースがあるスプレッドシート側で実行
③その結果だけを IMPORTRANGEで取得する
（製品データベース ⇒ 参照検索）
このように 相互に IMPORTRANGEをすることで、IMPORTRANGEでの重いデータのやりとりを無くし
リクエストを投げて、サーバーサイドで処理した結果を戻してもらうという、GASのような使い方で解決できるわけです。
どちらも巨大なデータを IMPORTRANGEで扱う際に必要となるテクニックです。面白いですね。
スマホやiPadで IMPORTRANGEの「アクセス許可」が出来ない問題を突破する
最後に軽めのネタを紹介して今回は終わりとしましょう。
Googleスプレッドシートは モバイルから利用している人も多いかと思いますが、アプリ版は結構出来ないことが多いんですよね。
IMPORTRANGE関数の初回の「アクセスを許可」も 残念ながらアプリ版では出来ず
このシートを接続するには、パソコンのウェブブラウザを使用してください
とメッセージが出て、データの参照が出来ません。
残念ながら、こちらは裏技的な回避方法はなく、PC版ブラウザでボタンを押すしかありません。
どうしてもPCが無くてスマホしか使えない場合は、スマホのブラウザのPC版表示でスプレッドシートを開くことで、スマホだけでもIMPORTRANGEの「アクセスを許可」を対応することが可能です。
アプリ版スプレッドシートを開き、右上の・・・（3点リーダー）から、
共有とエクスポート ＞ リンクをコピー
で、スプレッドシートのURLをコピーします。
次にブラウザを開き、コピーしたリンクを貼り付け スプレッドシートを開きます。
ここではスマホ版の簡易表示となります。
※ iPhone、iPadでSafariで開く場合は、先にGoogleアカウントにログインをしておく必要があります。
次にブラウザを PC版表示に切り替えます。
※iPhoneでSafariを利用の場合は、アドレスバー左の 「ぁあ」という箇所をタップし「デスクトップ用Ｗｅｂサイトを表示」を選択
表示が小さいですがPCブラウザ版でスプレッドシートが開かれた状態となり、IMPORTRANGEを入れたセルをタップすると「アクセスを許可」のボタンが表示されるので、こちらをタップ。
IMPORTRANGEでデータが取得できました。
アプリ版に戻ってIMPORTRANGEでデータが取得できたことを確認しましょう。
一度許可してしまえば、あとはアプリ版から閲覧、操作できます。
こんな手順で、スマホのみでも IMPORTRANGE関数は使うことができます。
IMPORTRANGE以外のアプリ版では出来ない処理も同様に、スマホのブラウザをPC表示モードでスプレッドシートを開くことで対応できるので、どうしても出先でスマホのみで対応する必要がある時、この方法を知っていると便利です。
次回、IMPORTRANGE関数シリーズ最後。関数を組み合わせた超応用例を
今回は Excelのブックリンクと GoogleスプレッドシートのIMPORTRANGE関数の比較、そして 様々な機能を使った IMPORTRANGEの超応用例を紹介しました。
IMPORTRANGE関数を使う人にとっては、結構使えるネタが多かったんじゃないでしょうか？
次回はIMPORTRANGE関数の最後、他の関数と組み合わせた超応用例を紹介します！
我ながらIMPORTRANGE関数で3回も引っ張ることになるとは思わなかった・・・
いいなと思ったら応援しよう！
