どうもY-nek0(@Ynek0)です。
Googleスプレッドシートでサクッと資料をまとめて半自動的に作ってしまい、
明日は休日……折角だし人事部のあの子を飲みに誘っちゃいたい方やそうでない方に向けて
お話ししていこうと思います。
今回はGoogleスプレッドシートとはよく使うであろう関数、IMPORTRANGE関数の話です。
目次
IMPORTRANGE関数とは？
では先に、IMPORTRANGEとはインポートレンジと呼びます。
おっと、男の自信のことを想像した方はいらっしゃらないでしょうが、
一応無関係であると伝えておきますね！
この関数が何をする関数かというと
別スプレッドシートの特定のシート、特定のセル範囲をIMPORTRANGE関数を使用したスプレッドシートに写してくれるというものです。
ちょっと何言っているか分からない……から近未来世界で例えろって？
こっちがちょっと何言ってるかわかんない……
とりあえず近未来世界的に例えるのであったら……
近未来、人類は2つの世界を行き来することができる様になった。
デジタル世界と現実世界。
主人公はおじいちゃんが大好きだ。
そのおじいちゃんは80歳でデジタル世界に移住した。
お金さえあれば死後、デジタル世界でも生きながらえる事が出来る。
肉体のある人間であれば専用VR機器を使い、会いに行くこともできる。
そんな近未来世界の物語で例えましょう。
IMPORTRANGE関数は主人公が現実世界からデジタル世界へとログインし、デジタル世界に主人公の分身が顕現しました。つまり大本（現実世界）と分身（デジタル世界）に分かれた事になります。
これをIMPORTRANGEに置き換えるとデジタル世界がIMPORTRANGEを使った側になります。
つまりは分身です。
このIMPORTRANGE関数は １つで複数セルを使用します。
以前に紹介したFILTER関数と同じように、元あるデータから必要なデータを参照し
全て持ってくる関数です。
物語ではVR世界へ行くのに専用VR機器と言いました。
某近未来VRMMO系のような頭をかぶせるような機械では無く、
本体は医療機器のMRIの様な寝そべって全身スキャンをしてインします。
ですのでデジタル世界にログイン後の世界でも同じ様な容姿になります。
そう言うわけで同じ内容の人間が現実世界からデジタル世界に分身として
姿を現したと言う事になります。
このIMPORTRANGE関数は非常に汎用性に優れていますので
Googleスプレッドシートの 利用の幅が一気に増大して
プチイノベーションみたいな感じに なった方もいるほどです。
では基本的な構文を見て見ましょう
基本構文
構文は以下の通りです。
IMPORTRANGE(スプレッドシートキー, 範囲の文字列)
んんんぅ？？？何じゃこりゃ？ってなりますよね……
順に解説していきますね。
■スプレッドシートキー
初めて聞くような名称ですが、これは近未来世界で言う、デジタル世界にログインするための認証と識別を担う部分になります。
つまりは自分のIDを指定して、サーバーに保存している自分のアバターと照合をとり、デジタル世界にそのアバターでログインするイメージですね。
ではそんなスプレッドシートIDはどこにあるのかというと……
スプレッドシートを開いているURLの中に存在します。
https://docs.google.com/spreadsheets/d/[xxxxxxxxxxxxxxxxxxxxxxxxxxxxx]/edit#gid=0
この[xxxxxxxxxxxxxxxxxxx]の部分がスプレッドシートのIDとなります。
・・・
わかります。すごく分かります。
いちいちこんな長ったらしいＩＤをＵＲＬから選択範囲指定して貼り付けろって？面倒くさい!
そんな声が聞こえてくるような気がします。
実はこのスプレッドシートID……
ぶっちゃけURLコピペしても動きます。
まるっとコピーしてダブルクォーテーション(")でそのURLを囲えば問題なく動作します。
"https://docs.google.com/spreadsheets/d/[xxxxxxxxxxxxxxxxxxxxxxxxxxxxx]/edit#gid=0"
上記の用に括れば問題無いです。
なのでIDで括らずURLまるごとコピーしちゃいましょう。
ちなみにスプレッドシートIDは参照する事も可能ですので、関数の文字数も多いのにさらにIDの文字数も多いからちょっと……って方は
セルにスプレッドシートIDをコピペしておいてIMPORTRANGE関数には参照セル番地を入力すれば良いと思います。
セル番地？ ……？？？となっている方はこちらの記事で説明しています。
■範囲のセル
これは、参照元のスプレッドのシート名とクエッションマーク（!）を
挟んで、 その範囲(A1:B3とかの事)を入れてねって意味です。
近未来世界の話で例えるならば、ワールド選択して、表示するフィールドの範囲を指定するみたいなイメージですかね。
これもダブルクォーテーションでくくってあげてください。
そうするとこんな風になります。
”シート名!A1:B3”
シート名は省略可能で、省略した場合は 参照元のスプレッドシートの一番最初のシートが
呼び出されます。
またシートや範囲をセルで参照させる事も出来るのですが、一工夫が必要になります。
その方法については別関数も関わってくるので後半の方で記述しています。
そして、合体させるとこんな感じになります。
例文
IMPORTRANGE("https://docs.google.com/spreadsheets/d/abcd123abcd123", ”シート名!A1:B3”)
これで他のスプレッドシートから参照できるようになります。
無事主人公がデジタル世界にログイン出来た事になります。
しかし初回の接続のみ、アクセス許可を求められる事があります。
アクセス権
最初に「＃REF！」と表示されたセルをクリックすると
アクセス許可を 促すポップアップが出てきますので許可をクリックしてください。
すると値が出てくるはずです。
近未来世界で言うと端末認証ですかね。
初回アクセスにおいて不正アクセス防止のためにアクセス端末を登録するって感じです。
共有権限について
また、共有アクセス権について 参照する元のスプレッドシートのアクセス権が
編集もしくは閲覧まで許可がされていないと参照することができません。
つまりは元のシートを見せたくないあまり、スプレッドシートのアクセス権限を外していると
元のスプレッドシートを参照しているIMPORTRANGE関数が全て参照不可で
エラーで表示される悲惨な事態に陥ります。
近未来世界でいきなりログインできなくなったら『あれ？』ってなりますよね。
物語としては何か始まりを感じさせざる得ないですがね。
閲覧可のみの権限は残しておくようにしましょう。
複数人で利用する際は注意してくださいね。
以上でIMPORTRANGE関数の基本的な使い方と注意事項でした。
では次にIMPORTRANGE関数と連携できる関数を使った応用術をご紹介します。
今回そんな例え話つかわんなぁ……
応用
FILTER関数を用いたIMPORTRANGE関数
では、このIMPORTRANGE関数は単体でも利用できますが、
前回ご紹介したFILTER関数とも相性がいいです。
FILTER関数については以下の記事を是非ご参考にして下さい。
参照元から条件を指定することで特定の値のみを出すことも可能なのです！
関数で表すとこんな感じです。
例文
FILTER(
IMPORTRANGE(参照元スプレッドシートURL,シート名!範囲),
IMPORTRANGE(参照元スプレッドシートURL,シート名!範囲) 条件式,[条件式２]
)
解説
このFILTER関数で表示させたい文字列は別スプレッドシートにあります
その別スプレッドシートから値をとるために最初のIMPORTRANGEを使っています。
次に、別に全てを表示させたいわけじゃなくて特定の値のみを表示させたい。
IMPORTRANGEでとってくる値の中からこの条件に合致したものだけ表示してと
２番目のIMPORTRANGEと条件式で指定しています。
この条件式ってところに『 = “文字列”』とか『 >= 数字』とか入れれば大丈夫です。
複数条件ある場合はカンマ(,)で区切って別の条件式を与えてあげると
『これと、これ両方に合致している値をとってくるよ！』
って意味になります。
そのことを『AND』と読んだり、『論理積』と呼ぶこともあるので覚えておくと得ですよ！
近未来世界で例えるならばいつもどおりおじいちゃんの家でのんびりしていた主人公が
ある日珍しく外出しているおじいちゃんの後をつけていった先に色々とあって、おじいちゃんを見失ってしまったが
なにやらＩＤを入手してしまいました。
後日それを使ってログインしてみると自分が知っている世界とはまた別の世界へとインしたのでした。
つまり、特定の条件を満たした者のみが入れる世界があったというわけです。
いずれかの条件に一致した場合の値を取得する(OR)方法
ではもう１つ、誰もがハマる条件式をご紹介。
このFILTER関数とIMPORTRANGEでどうしても
『AとBどちらかもしくは両方に合致している値をとってくるよ！』
っていう式を作りたい場合はどうするか。
FILTER関数の記事で文字だけ残していたけれど、
『+』で連結させます。
また、『+』で連結させる際は各条件式をカッコ()でくくって下さい。
じゃないとエラーになります。
下記がその構文になります。
例文２
FILTER(
IMPORTRANGE(参照元スプレッドシートURL,シート名!範囲),
(IMPORTRANGE(参照元スプレッドシートURL,シート名!範囲) 条件式1)+
(IMPORTRANGE(参照元スプレッドシートURL,シート名!範囲) 条件式2)
)
こうすることにより『どちらか片方もしくは両方の値に合致した値をとってくる』ことができます。
ちなみに、この『どちらか片方もしくは両方に合致』することを『OR』もしくは『論理和』と言います。
ＯＲを近未来世界で例えるならば主人公がいつもインしているデジタル世界です。
こちらは一般人でも管理者でも、アクセスするＩＤさえ特定できれば入れる世界、つまりは幾つか条件があるけれども
そのうちの1つがマッチしていたら入れる世界です。
『OR』と『AND』どちらも利用しますので覚えておくと後々役に立ちますよ！
ネストでの注意点
１つ注意してほしいことがあり、現在、IMPORTRANGE関数は単体使用でのみ
「＃REF！」と表示され、アクセス許可を促す画面が出てきます。
それをFILTER関数での入れ子構造（ネストと言います）にしてしまうとエラーのみが表示され、アクセス許可を促す画面が出てきません。
このアクセス許可は初回１回のみ許可すれば次聞かれることは暫くないので
最初はIMPORTRANGE関数単体でアクセス許可をとるようにしてください。
INDIRECT関数を使用したセルの値でシート名を指定する方法
また、シート名をセルの値で指定したい時は以下の記事も便利です。
例文
IMPORTRANGE("参照元スプレッドシートURL",INDIRECT(A1&"!A1:B20"))
この様にしてINDIRECTを使用すれば特定のセルの値にシート名を入力しておくだけで自動的に参照するシートを切り替えられたりできます。
先ほどのFILTER関数と一緒にも使えたりします。
メンテナンスしやすくするには？
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
そして今回結構頑張って考えた近未来世界の例え話がほぼほぼ使えなかったのが残念です。
ネタバレしておくと実は現実世界もデジタル世界であり、
そのシステムが動いているのは宇宙船内部であった。
元々人類が宇宙を移動するには肉の身体が移動年数に耐えきれない事から
意識のみをデジタルに移行し、観測された新たに住める星へと宇宙航海をしている最中だった
というオチです。
では、次回もまたご贔屓に。
