Googleスプレッドシートで別シートから条件に合うデータを取得する方法

概要

Googleスプレッドシートでデータをまとめる際に何度も同じことするの面倒だったので、
別シートにマスターデータを作ってそのデータを参照するようにしたら楽になったというお話。

ちなみにこちらの記事を参考にしました。
Googleスプレッドシート 他のシートから特定のセルを条件付きで取ってきて表示させたい

やり方

1. IMPORTRANGE関数を使って他のスプレッドシートからデータを取得
2. そのデータに対してQUERY関数を使って条件に合うデータを絞る
3. 条件のセルが空でなければ条件に合ったデータを入力する

1.IMPORTRANGE関数を使って他のスプレッドシートからデータを取得

まずはIMPORTRANGE関数を使って別のスプレッドシートからデータを取得します。
これで別のスプレッドシートの指定範囲をまるっと取得できます。

IMPORTRANGE関数

=IMPORTRANGE("spreadsheets_URL","sheet_name!A1:B70")

2.そのデータに対してQUERY関数を使って条件に合うデータを絞る

QUERY関数を使って先ほどIMPORTRANGE関数を使って取得したデータから条件に当てはまるデータを検索します。
今回はA列の値が一致したところのB列の値が欲しいので、こんな感じになります。

QUERY関数

=query(IMPORTRANGE("spreadsheets_URL","sheet_name!A1:B70"),"select Col2 where Col1 ="&B2)

注意すべきポイントは

• A列はCol1、B列はCol2のようにCol表記する
• B2セルを指定する時に&でつなげる(文字列の連結扱いなので&を使う)

3.条件のセルが空でなければ条件に合ったデータを入力する

先ほど条件に当てはまるデータを検索しましたが、条件のセルが空だった場合はエラーになってしまいます。
そこで、ISBLANK関数を使ってセルが空の場合は何もせず、何かしら入力されている場合だけQUERY関数が実行されるようにします。

ISBLANK関数

=if(ISBLANK(B2),B2が空の場合の処理,B2に値が入ってる場合の処理)

最終結果

今までのを全て組み合わせるとこんな感じになります。

完成形

=if(ISBLANK(B2),"",query(IMPORTRANGE("spreadsheets_URL","sheet_name!A1:B70"),"select Col2 where Col1 ="&B2))

これでB2セルに値が入っている場合だけ、sheet_nameのA1〜B70の範囲でselect B列 where A列 = B2した結果が入るようになります。

補足

IMPORTRANGE関数は本家のExcelには無い機能なので、Googleスプレッドシート便利だなと思いました。