スプレッドシートで別ファイルを参照する際の関数

スプレッドシートについて、以前にブログでもご紹介しておりますが、今回はデータ参照する場合に関してEXCELとの違いやその方法についてご紹介をしたいと思います。
あわせて、これと同時に使うと便利な関数もひとつご紹介いたします。

別シートのデータ参照をする場合

EXCELで別シートのデータ参照をする場合には、「シート名!セル位置」を指定する事でセルを参照できます。

スプレッドシートでは、「’シート名’!セル位置」を指定する事でセルを参照できます。

”(シングルコーテーション)で囲む必要があるか無いかと言った差分が発生致します。

また、スプレッドシートで二つのファイルがあり、そのデータを参照したい。と言った場合エクセルであれば同様なやり方ができますが、スプレッドシートではそのままでは出来ません。

ここで、今回ご紹介する便利な関数が用意されております。

スプレッドシートで別ファイルのデータ参照をする場合

IMPORTRANGE関数というものが用意されております。関数のヘルプを確認すると下記定義をされております。

指定したスプレッドシートからセルの範囲を読み込みます。
構文
IMPORTRANGE(スプレッドシートキー, 範囲の文字列)
Googleドキュメントエディタヘルプ, IMPORTRANGEから引用

書かれているとおり、やり方はともかく意味はおおまかに理解できるかと思います。
ここで書かれている、「スプレッドシートキー」はスプレッドシートを開いた際にURLが表示されますが、「d/○○○○/edit」の〇部分の事を指します。
スプレッドシートキー：https://docs.google.com/spreadsheets/d/○○○○/edit

また、本関数を使用しデータ参照をさせる時には初回に認証をする事が必要になりますので、その点も注意が必要になります。

関数で出来る事の利点ってなに？

IMPORTRANGE関数については、色々と利点が考えられますが大きく下記2点についてご紹介します。

複数人での作業をやっている場合には、リアルタイムで反映がされる

EXCELだと、2つのファイルが互いにリンクしたデータの場合には、それぞれのデータが反映されるのは最後に保存した後に、次に開いて外部データの取り込みが完了したタイミングになります。
リアルタイムでデータをやり取りする、状態を確認すると言った場合にはIMPORTRANGE関数を使う事でリアルタイムに更新がされます。

関数1文で参照範囲を可変する事が出来る

EXCELでも別ファイルの読み込みが可能な事はお伝えしましたが、参照範囲を全て各セルに埋め込む必要がありますが、IMPORTRANGE関数を使うと1文で済みます。勿論、その範囲については指定をする事が可能ですので、必要な部分だけを選択し、かつ条件をつけて抽出すると言った事まで可能になります。

IMPORTRANGE関数と組み合わせる関数

IMPORTRANGE関数で範囲を指定した読み込んだ際に、出力するデータをQUERY関数で条件指定をする事が可能になり、抽出したいデータを簡単に抽出する事が出来る事になります。

例えば、現場で日々入力された一覧表があるとします。

管理者は、その一部のデータを用いて管理をしたいと言った場合、この二つの関数を組み合わせるとリアルタイムで更新され、管理をする事が出来ます。

今回の表では、A1セルに現場で入力された一覧表のスプレッドキーを下記に入れて指定するとこの様な表になります。
=QUERY(TRANSPOSE(IMPORTRANGE(“スプレッドキー”,”シート1!a1:ae11″)),”select Col2,Col3,Col4,Col5 where Col2>0 and Col2<6″)

QUERY関数については、以前のブログで簡単には紹介しましたが、その際には列の指定でそのまま「A1、B1」の表記で問題ありませんが、別ファイルを参照する際には「Col1、Col2」の様な指定をする必要があります。

まとめ

今回の関数の様に、遠隔地でのデータのやり取りや、共同作業を行うといった場合にはスプレッドシートは非常に有効な手段であるという事が少しおわかり頂いたと思います。
遠隔地のちょっとした管理や、集計作業などをリアルタイムで行いたいと言った場合には、色々と用意されているものもあります。
データ入力をその人が行うと他データに勝手に集計がされる、管理表に反映されるなど様々な事が可能になりますので、一度やってみる事をお勧めいたします。