どうもY-nek0(@Ynek0)です。
今回はスプレッドシートの関数についてお話ししますね。
目次
IMPORTRANGE関数について
この関数が何をする関数かというと
別のスプレッドシートの特定のシート、特定のセル範囲を別のスプレッドシートに写してくれるというものです。
もちろんFILTER関数と同じように 関数１つで複数行もの処理が
可能なので非常に便利な関数です。
こちらの関数を覚えたことにより、Googleスプレッドシートの 利用の幅が
一気に増大してプチイノベーションみたいな感じに なった方もいるほどです。
では基本的な構文を見て見ましょう
基本構文
IMPORTRANGE(スプレッドシートキー, 範囲の文字列)
■スプレッドシートキー
初めて聞くような名称ですが、これは
参照する元スプレッドシートのURLのことを指します。
ダブルクォーテーション(")でそのURLを囲います。
ダブルクォーテーションはshift押しながら２のキーを押せば出てきます。
半角で入力してくださいね！全角ではないですよ。
気をつけてくださいね。
するとこんな風になるかと思います。
"https://docs.google.com/spreadsheets/d/abcd123abcd123"
■範囲の文字列
これは、参照する元のスプレッドのシート名とクエッションマーク（!）を
挟んで、 その範囲(A1:B3とかの事)を入れてねって意味です。
これもダブルクォーテーションでくくってあげてください。
そうするとこんな風になります。
”シート名!A1:B3”
シート名は省略可能で、省略した場合は 参照元のスプレッドシートの一番最初のシートが
呼び出されるから注意が必要です。
そして、合体させるとこんな感じになります。
例文
IMPORTRANGE("https://docs.google.com/spreadsheets/d/abcd123abcd123", ”シート名!A1:B3”)
これで他のスプレッドシートから参照できるようになります。
しかし、初回の接続のみアクセス許可を求められる場合があります。
アクセス権
最初に「＃REF！」と表示されたセルをクリックすると
アクセス許可を 促す画面が出てきますので許可をクリックしてください。
すると値が出てくるはずです。
共有権限について
また、共有アクセス権について 参照する元のスプレッドシートのアクセス権が
編集もしくは閲覧まで許可がされていないと参照することができませんので
複数人で利用する際は注意してください。
応用
FILTER関数を用いたIMPORTRANGE関数
では、このIMPORTRANGE関数は単体でも利用できますが、
前回ご紹介したFILTER関数とも相性がいいです。
FILTER関数については以下の記事を是非ご参考にして下さい。
参照元から条件を指定することで特定の値のみを出すことも可能なのです！
関数で表すとこんな感じです。
例文
FILTER(
IMPORTRANGE(参照元スプレッドシートURL,シート名!範囲),
IMPORTRANGE(参照元スプレッドシートURL,シート名!範囲) 条件式,[条件式２]
)
解説
このFILTER関数で表示させたい文字列は別スプレッドシートにあります
その別スプレッドシートから値をとるために最初のIMPORTRANGEを使っています。
次に、別に全てを表示させたいわけじゃなくて特定の値のみを表示させたい。
IMPORTRANGEでとってくる値の中からこの条件に合致したものだけ表示してと
２番目のIMPORTRANGEと条件式で指定しています。
この条件式ってところに『 = “文字列”』とか『 >= 数字』とか入れれば大丈夫です。
複数条件ある場合はカンマ(,)で区切って別の条件式を与えてあげると
『これと、これ両方に合致している値をとってくるよ！』
って意味になります。
そのことを『AND』と読んだり、『論理積』と呼ぶこともあるので覚えておくと得ですよ！
とっておきの条件式
ではもう１つ、誰もがハマる条件式をご紹介。
このFILTER関数とIMPORTRANGEでどうしても
『これと、これのどちらかもしくは両方に合致している値をとってくるよ！』
っていう式を作りたい場合はどうするか。
FILTER関数の記事で文字だけ残していたけれど、
『+』で連結させます。
また、『+』で連結させる際は各条件式をカッコ()でくくって下さい。
じゃないとエラーになります。
下記がその構文になります。
例文２
FILTER(
IMPORTRANGE(参照元スプレッドシートURL,シート名!範囲),
(IMPORTRANGE(参照元スプレッドシートURL,シート名!範囲) 条件式1)+
(IMPORTRANGE(参照元スプレッドシートURL,シート名!範囲) 条件式2)
)
こうすることにより『どちらか片方もしくは両方の値に合致した値をとってくる』ことができます。
ちなみに、この『どちらか片方もしくは両方に合致』することを『OR』もしくは『論理和』と言います。
『OR』と『AND』どちらも利用しますので覚えておくと後々役に立ちますよ！
ネストでの注意点
１つ注意してほしいことがあり、現在、IMPORTRANGE関数は単体使用でのみ
「＃REF！」と表示され、アクセス許可を促す画面が出てきます。
それをFILTER関数での入れ子構造（ネストと言います）にしてしまうとエラーのみが表示され、アクセス許可を促す画面が出てきません。
このアクセス許可は初回１回のみ許可すれば次聞かれることは暫くないので
最初はIMPORTRANGE関数単体でアクセス許可をとるようにしてください。
セルの値でシート名を指定する方法
また、シート名をセルの値で指定したい時は以下の記事も便利です。
例文
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
では、次回もまたご贔屓に。
