どうもY-nek0(@Ynek0)です。
Googleスプレッドシートでサクッと資料をまとめて半自動的に作ってしまい、
明日は休日……折角だし人事部のあの子を飲みに誘っちゃいたい方やそうでない方に向けて
お話ししていこうと思います。
今回はGoogleスプレッドシートとはよく使うであろう関数、IMPORTRANGE関数の話です。
IMPORTRANGE関数とは？
では先に、IMPORTRANGEとはインポートレンジと呼びます。
この関数が何をする関数かというと
別スプレッドシートの特定のシート、特定のセル範囲をIMPORTRANGE関数を使用したスプレッドシートに写してくれるというものです。
ちょっと何言っているか分からない……から近未来世界で例えろって？
とりあえず最近Facebookがメタバースを提唱したのでそれが近未来世界感あるのでそれで例えますね。
IMPORTRANGE関数は主人公がVRダイブマシンを使用して現実世界からメタバースへとログインし、
メタバースに主人公の分身が顕現した。
つまり大本（現実世界）と分身（メタバース）に分かれた事になります。
これをIMPORTRANGEに置き換えるとデジタル世界がIMPORTRANGEを使った側になります。
つまりは分身です。
このIMPORTRANGE関数は １つで数万行列のセル使用する事も可能です。
以前に紹介したFILTER関数と同じように、元あるデータから必要なデータを参照し
全て持ってくる関数です。
ただ、その参照するデータの先が同じスプレッドシート内にあるか、
別のスプレッドシート内にあるかの違いです。
このIMPORTRANGE関数は非常に汎用性に優れていますので
Googleスプレッドシートの 利用の幅が一気に増大して
プチイノベーションみたいな感じに なった方もいるほどです。
では基本的な構文を見て見ましょう
IMPORTRANGE関数の基本構文
構文は以下の通りです。
IMPORTRANGE(スプレッドシートキー, 範囲の文字列)
んんんぅ？？？何じゃこりゃ？
ってなりますよね。
順に解説していきます。
スプレッドシートキー
初めて聞くような名称ですが、これはメタバースにログインするためにはどこにログインするかの場所を示すキーになります。
このスプレッドシートキーは全てのスプレッドシートで一意で付与されているので特定のキーを入力する事で
どのスプレッドシートを指定しているのが関数の裏側で判別できる様になっているのです。
ではそんなスプレッドシートキーはどこにあるのかというと……
スプレッドシートを開いているブラウザのURLの中に存在します。
Googleに登録していらっしゃるのであればスプレッドシートを開いて確認してみて下さい。
https://docs.google.com/spreadsheets/d/[xxxxxxxxxxxxxxxxxxxxxxxxxxxxx]/edit#gid=0
とあるURLが確認出来ると思います。
この[xxxxxxxxxxxxxxxxxxx]の部分がスプレッドシートのIDとなります。
えっ？
いちいちこんな長ったらしいＩＤをＵＲＬから選択範囲指定して貼り付けろって？面倒くさい!
そんな声が聞こえてくるような気がします。
実はこのスプレッドシートキーなのですがぶっちゃけURLコピペしても動きます。
まるっとコピーしてダブルクォーテーション(")でそのURLを囲えば問題なく動作します。
上記の用に括れば問題無いです。
なのでIDで括らずURLまるごとコピーしちゃいましょう。
ちなみにスプレッドシートIDは参照する事も可能ですので、
関数の文字数も多いのにさらにIDの文字数も多いからちょっと……って方は
別のセルにスプレッドシートキーをコピペしておいて
IMPORTRANGE関数には参照セル値を入力すればスリムにできます。
この特性を利用して別のシートでIMPORTRANGE用URLシートを作成して、
そこにURLをコピペするだけでよい形であればメンテナンスも楽になりますよ。
セル参照？？ ……？？？となっている方はこちらの記事で説明していますのでご参照下さい。
範囲のセル
これは、参照元のスプレッドのシート名とクエッションマーク（!）を
挟んで、 その範囲(A1:B3とかの事)を入れてねって意味です。
この範囲は必ずダブルクォーテーションでくくってあげてください。
そうしないとエラーになってしまいます。
それでどのように記述するかというと以下の様な形になります。
”シート名!A1:B3”
シート名は省略可能で、省略した場合は 参照元のスプレッドシートの一番最初のシートが
呼び出されます。
そして、合体させるとこんな感じになります。
例文
IMPORTRANGE("https://docs.google.com/spreadsheets/d/abcd123abcd123", ”シート名!A1:B3”)
これで他のスプレッドシートから参照できるようになります。
IMPORTRANGE関数の各種エラーについて
IMPORTRANGE関数を使っているけれど、よく分からないエラーが発生してしまった
との声もよく聞きますので各種エラーについてどうして起きるのか、その対処法を説明していきます。
初回#REF エラー アクセス権の許可を行う
最初に「＃REF！」と表示されたセルをクリックすると
アクセス許可を 促すポップアップが出てきますので許可をクリックしてください。
すると値が出てくるはずです。
しかしこれは関数を入れ子構造にしていると現れないので
1度必ずIMPORTRANGE関数単体でアクセス許可を行った後に入れ子構造にして下さい。
このシートにアクセスする権限がありません。
エラーの記述の通り、
IMPORTRANGE関数で参照しようとしているスプレッドシートの閲覧権限が存在しないため、この様なエラーが出てきます。
対処方法は参照しようとしているスプレッドシートの作成者に共有を依頼し承認されることで解消されます。
元のシートを見せたくないあまり、スプレッドシートのアクセス権限を外していると
元のスプレッドシートを参照しているIMPORTRANGE関数が全て参照不可で
エラーで表示される悲惨な事態に陥ります。
閲覧可のみの権限は残しておくようにしましょう。
複数人で利用する際は注意してくださいね。
結果が大きすぎます
IMPORTRANGE関数での取り込みするデータの総量には限界があります。
その総数を超えた場合エラーが発生する事になります。
Googleの公式ドキュメントではその総数というのは明記されていなかったのですが、
テストされた方によると175000セルが限界といわれています。
それ以上の出力をしたい場合はIMPORTRANGE関数を分割する必要があります。
配列を使用してIMPORTRANGE関数を分割できるので是非お試し下さい。
={(IMPORTRANGE("sheetkey1","sheetname!A1:A100000")),(Importrange("sheetkey1","sheetname!A100001:A200000"))}
また配列に関しましてはARRAY FORMULA関数の記事にてご紹介させて頂いておりますので
もしよろしければこちらも合わせてご参照下さい。
また、同じように配列を使用して複数シートから1つのシートへの累積方法を
FILTER関数を利用してできますのでこちらもご参考にどうぞ。
範囲のインポートの内部エラーです。
こちらでよく起きるパターンはIMPORTRANGEで参照しようとしているスプレッドシートの範囲で
関数や計算ミスによるエラーが発生している事でIMPORTRANGE関数が『参照元でエラーが発生しているよ？直そ？』って言っています。
参照元のファイルのエラーを直せば基本解消されます。
循環依存が検出されました。
こちらのエラーは様々な計算式を組み込んだシートで起こりやすいものになっています。
例えばB1にIMPORTRANGE関数を記述していると仮定します。
参照先はA1:A10としましょう。
しかし記述ミスで以下の様な記述になりました。
=IMPORTRANGE("sheetkey","'sheet1!A1:B10'")
記述ミスがあり、B10と指定されていますね。B1にIMPORTRANGE関数を記述しているので
参照元が参照先になっているという循環状態となり、よく分からない状態になってしまったが為に起こるエラーになります。
解消法としては参照先が参照元に干渉しないように範囲を指定し直す必要があります。
このシートに接続するにはパソコンのウェブブラウザを使用して下さい。
このエラーについてはかなり珍しいですが、調べて見ると
エクセルファイルをGoogleDriveにアップロードして、
そのエクセルファイルをIMPORTRANGE関数で参照しようとした際に起こるみたいです。
この場合はアップロードしたエクセルファイルをスプレッドシートに変換するか、
新たにスプレッドシートを作成してそこに貼り付けるかで対処する必要があります。
インポートした範囲の範囲またはシートが見つかりません。
IMPORTRANGE関数のスプレッドシートIDはしっかりと記述できているのですが、参照するシート名または範囲が間違っている可能性があります。
また、シングルクォーテーション(')やダブルクォーテーション(")を全角で囲ってしまってもこのエラーが発生します。
これらを修正すればエラーは解消されると思われます。
スプレッドシートが見つかりません。
これはスプレッドシートIDが間違っている可能性があります。
参照元スプレッドシートのスプレッドシートIDを確認し修正してください。
数式の解析エラー
こちらのエラーは単純にIMPORTRANGE関数の記述エラーです。
スプレッドシートIDを半角のダブルクォーテーション(")で囲って下さい。
シングルクォーテーション(')で囲ってしまうとこのエラーが出てきてしまいます。
またシート名!A:Bも半角ダブルクォーテーション(")で囲って下さい。
シート名をシングルクォーテーション(')で囲っておくと安心です。
例：" 'シート名'!A1:B1"
以上でIMPORTRANGE関数の基本的な使い方と注意事項でした。
では次にIMPORTRANGE関数と連携できる関数を使った応用術をご紹介します。
IMPORTRANGE関数とその他関数の応用
FILTER関数を用いたIMPORTRANGE関数
では、このIMPORTRANGE関数は単体でも利用できますが、
前回ご紹介したFILTER関数とも相性がいいです。
FILTER関数については以下の記事を是非ご参考にして下さい。
参照元から条件を指定することで特定の値のみを出すことも可能なのです！
関数で表すとこんな感じです。
例文
FILTER(
IMPORTRANGE(参照元スプレッドシートURL,シート名!範囲),
IMPORTRANGE(参照元スプレッドシートURL,シート名!範囲) 条件式,[条件式２]
)
こちらのタイプは先述しました#REFエラーが出てこないタイプであります。
1度必ずIMPORTRANGE関数単体でアクセス許可を行った後に入れ子構造にして下さい。
解説
このFILTER関数で表示させたい文字列は別スプレッドシートにあります。
その別スプレッドシートから値をとるために最初のIMPORTRANGEを使っています。
次に、別に全てを表示させたいわけじゃなくて特定の値のみを表示させたい。
IMPORTRANGEでとってくる値の中からこの条件に合致したものだけ表示してと
２番目のIMPORTRANGEと条件式で指定しています。
この条件式ってところに『 = “文字列”』とか『 >= 数字』とか入れれば大丈夫です。
複数条件ある場合はカンマ(,)で区切って別の条件式を与えてあげると
『これと、これ両方に合致している値をとってくるよ！』
って意味になります。
そのことを『AND』と読んだり、『論理積』と呼ぶこともあるので覚えておくと得ですよ！
いずれかの条件に一致した場合の値を取得する(OR)方法
ではもう１つ、この条件式で1度は悩む方はいると思います。
このFILTER関数とIMPORTRANGEでどうしても
『AとBどちらかもしくは両方に合致している値をとってくるよ！』
っていう式を作りたい場合の話しになります。
そのばあいは場合はどうするかといいますと
FILTER関数の記事で文字だけ残していたけれど、
『+』で連結させます。
また、『+』で連結させる際は各条件式をカッコ()でくくって下さい。
でないとエラーになります。
上記を用いた構文が以下になります。
例文２
FILTER(
IMPORTRANGE(参照元スプレッドシートURL,シート名!範囲),
(IMPORTRANGE(参照元スプレッドシートURL,シート名!範囲) 条件式1)+
(IMPORTRANGE(参照元スプレッドシートURL,シート名!範囲) 条件式2)
)
こうすることにより『どちらか片方もしくは両方の値に合致した値をとってくる』ことができます。
ちなみに、この『どちらか片方もしくは両方に合致』することを『OR』もしくは『論理和』と言います。
『OR』と『AND』どちらも利用しますので覚えておくと後々役に立ちますよ！
条件に一致した場合の値を取得し一意のデータのみを表示する方法
条件に一致したデータを取得したのは良いのですが、それに同じデータが複数混じっていた場合、
それも取り除きたい場合がありますよね。
そういった場合はUNIQUE関数を使用してデータを一意に表示させる事が出来ます。
FILTER関数は条件を与えますが重複していても条件を与えてしまえば表示してしまいます。
そこでUNIQUE関数を用意する事で一緒のデータは排除してくれるようになります。
ではどうするか簡単ですFILTER関数の上にかぶせるだけです。
例文２
UNIQUE(
FILTER(
IMPORTRANGE(参照元スプレッドシートURL,シート名!範囲),
(IMPORTRANGE(参照元スプレッドシートURL,シート名!範囲) 条件式1)+
(IMPORTRANGE(参照元スプレッドシートURL,シート名!範囲) 条件式2)
)
)
ただしUNIQUE関数は単一行と複数行で動作が違うので動作に注意する必要があります。
詳細は以下の記事で解説しておりますのでご参考下さい。
INDIRECT関数を使用したセルの値でシート名を指定する方法
また、シート名をセルの値で指定したい時は以下の記事も便利です。
例文
IMPORTRANGE("参照元スプレッドシートURL",INDIRECT(A1&"!A1:B20"))
この様にしてINDIRECTを使用すれば特定のセルの値にシート名を入力しておくだけで自動的に参照するシートを切り替えられたりできます。
先ほどのFILTER関数と一緒にも使えたりします。
IMPORTRANGE関数をメンテナンスしやすくするには？
IMPORTRANGE関数を使い続けていると入力する値が長いので
メンテナンスが少し面倒と感じるときがあります。
そういったときにどのように構成すれば良いかと言う部分をお伝え出来ればと思います。
別にIMPORTRANGE関数の管理シートを用意して貰います。
今回は仮に『シート1』と『シート2』を用意します。
IMPORTRANGE関数の管理シートは『シート1』としますね。
そこに項目名としてセルA1『URL』とセルA2『シート名』セルA3に『範囲』と入力します。
あとお好みで『#REF』対策の判定項目を追加しても良いかも知れません。
別のスプレッドシートから承認が取れているかどうかのリンクチェックを兼ねて置くと管理が楽になります。
判定の方には下記の様なIMPORTRANGE関数を書いて下さい。
=IF(ERROR.TYPE(IMPORTRANGE(URL,"シート名"&"!"&A1)) = 4,IMPORTRANGE(URL,"シート名"&"!"&A1),"OK")
ERROR.TYPE関数はエラータイプを判別して数字で返してくれる関数です。『#REF』のエラー番号は4となっていますのでイコールで4と指定しています。
これは『#REF』が出てきたら#REFを表示して、アクセス権の承認ができて、アクセス権の承認が取れたら『OK』と表示するIF文になります。
さて、実際のIMPORTRANGE関数はどのように使うかというと下記の通りです。
=IMPORTRANGE('シート1'!B1,'シート1'!B2&"!"&'シート1'!B3)
大分IMPORTRANGE関数がスッキリしましたね。
あとは管理シートの値だけを編集すれば大元のIMPORTRANGE関数を触らずにメンテナンスができる感じになります。
ネストなどされているIMPORRANGE関数とかに便利ですので是非参考にして貰えると幸いです。
また、大勢でひとつのスプレッドシートを利用する事になると誰かが
管理シートの値を勝手にいじったりとかそう言った事象が多々あるので管理シートを保護しておくと良いかも知れないですね。
最後に
では、今回のIMPORTRANGE関数については以上となります。
これを覚えることによってプチイノベーションが起こることを
楽しみにしています。
一応、今後わかりやすく噛み砕いて説明を掲載していこうと
考えている 関数のリファレンスを以下に掲載しておきます。
多分覚えが早い人ですとこのリファレンスを読むと
すぐに理解できるのではないでしょうか
https://support.google.com/docs/answer/3093340?hl=ja
また、スプレッドシート標準として搭載されている機能一覧もご紹介していますので
是非ご参考ください。
今回結構頑張って考えた近未来世界の例え話がほぼほぼ使えなかったのが残念です。
では、次回もまたご贔屓に。
