スプレッドシートの使い方- 別ファイルの参照と条件式の活用法

どうもこんにちは！樋渡です。

ここ最近、Googleのスプレッドシートを触る機会が増えてきました。
簡易的な管理シートを作るくらいであれば、十分に対応できますね。
そこで、今回のブログでは、その時に得た知識について書いていこうと思います。

なお、今回は非エンジニア向けの内容となっているため、GASについては触れていません。
主に、別ファイルの参照や条件式などについて記述しています。
 

別ファイルの参照

まずは別ファイルの参照です。
2つ以上のスプレッドシートを跨いで管理したい時などに便利です。
 

まずは上記のように2つのスプレッドシートを用意します。
sample1(左図)の値を参照してsample2(右図)に表示させたいので、sample2のA1セルに以下の数式を入力します。

=IMPORTRANGE("https://docs.google.com/spreadsheets/d/***********-*************************-******", "サンプル1!A:A")
// =IMPORTRANGE("sample1スプレッドシートのURL", "シート名!表示対象の列")

sample1の値をsample2に表示させることが出来ました。
これで、sample1のA列に追加、編集、削除した時も、sample2に反映されます。
sample1をマスターデータとして運用していく時などに活用できます。
 

別ファイルの参照、条件式

別ファイルを参照するだけだと少し味気ないので、次は応用編です。
例えば、下記のようなスプレッドシートがあったとします。
 

sample2(右図)には先ほどの数式が入力されたままの状態です。
今回は、sample1(左図)のB列で「契約済み」となっている会社名だけsample2に表示してみます。
sample2のA1セルに以下の数式を入力します。

=ARRAYFORMULA(IFERROR(FILTER(IMPORTRANGE("https://docs.google.com/spreadsheets/d/***********-*************************-******", "サンプル1!A:A"), IMPORTRANGE("https://docs.google.com/spreadsheets/d/***********-*************************-******", "サンプル1!B:B") = "契約済み")))
// =ARRAYFORMULA(IFERROR(FILTER(IMPORTRANGE("sample1スプレッドシートのURL", "シート名!表示対象の列"), IMPORTRANGE("sample1スプレッドシートのURL", "シート名!条件分岐の列") = "表示対象の値")))

「契約済み」の会社名だけを表示させることができました。
もちろん、sample1の「契約済み」⇔「未契約」を切り替えることで、sample2の表示もリアルタイムで切り替わります。
上記を応用して、以下のようなこともできます。  
 

sample2にマスターデータというシート(右図)を追加して、A1セルに先ほどと同じ数式を入力しています。
そして、sample2のシート(左図)にプルダウンを設定して、データ範囲に「='マスターデータ'!$A:$A」と入力します。
これにより、sample1のスプレッドシートで「契約済み」となっている会社名だけが、プルダウンで選択できるようになります。

注意点としては、プルダウンのデータ範囲に別ファイルを参照する数式を直接入力することはできません。
そのため、上記のように同ファイルにシートを追加して、そのシートを経由して参照する必要があります。
 

別ファイルの参照、条件式、データ検索

次は、簡易的な代理店管理シートを想定して、複数の数式を使っていきます。
まずは以下のようなスプレッドシートがあったとします。
 

sample1(左図)は今まで使っていたものを少し編集して、親会社という項目を追加しています。
コードリックは自社なので親会社には値が入らず、example①の親にはコードリック、example②の親にはexample①、というようになっています。






まずは⑴の要件を満たすために、代理店の列4行目(B4セル)に下記の数式を入力します。

=IF(A2 = "株式会社コードリック", "", A2)
// =IF(条件式, 条件式の結果が真だった場合の値, 条件式の結果が偽だった場合の値)

同シートのプルダウンで選択された値を表示させるので数式は単純です。
ただし、⑷の要件を満たすために、条件分岐を加えています。

次は⑵の要件を満たすために、代理店の列3行目(B3セル)に下記の数式を入力します。

=IFERROR(IF(VLOOKUP(B4, IMPORTRANGE("https://docs.google.com/spreadsheets/d/***********-*************************-******", "サンプル1!A:C"), 3, FALSE) = "株式会社コードリック", "", VLOOKUP(B4, IMPORTRANGE("https://docs.google.com/spreadsheets/d/***********-*************************-******", "サンプル1!A:C"), 3, FALSE)), "")
// =IFERROR(IF(VLOOKUP(検索値, IMPORTRANGE("sample1スプレッドシートのURL", "シート名!検索対象の列"), 取得する列の番号, 完全一致) = "株式会社コードリック", "", VLOOKUP(B4, IMPORTRANGE("sample1スプレッドシートのURL", "シート名!検索対象の列"), 取得する列の番号, 完全一致)), "")

左図では、プルダウンで選択されたexample②が代理店の列4行目(B4セル)に表示され、その上段に親会社であるexample①が表示されています。
右図では、example①が代理店の列4行目(B4セル)に表示されていますが、親会社がコードリックなので上段には何も表示されていません。

数式が少し複雑なので解説します。

IFERROR関数ついては後述します。
まず、IF関数の中にVLOOKUP関数を2回使用しています。
VLOOKUP関数には4つの引数が必要なので、検索で使用する値、検索範囲、取得したい列番号、検索方法（完全一致 or あいまい検索）を渡しています。
そこで取得した値が株式会社コードリックであれば空文字を返し、株式会社コードリックでなければ取得した値を返します。

それらを囲んだIFERROR関数は、エラー回避用のものです。
B4セルの値を基に検索する関数にも関わらず、プルダウンで株式会社コードリックが選択されるとB4セルが空文字になるため、エラーが返ってきます。
その時にエラーを返すのではなく、空文字を返してもらうためにIFERROR関数を使っています。

最後に、⑶の要件を満たすための数式を代理店の列2行目(B2セル)に入力しますが、これは簡単です。
検索値であるB4をB3にするだけです。

=IFERROR(IF(VLOOKUP(B3, IMPORTRANGE("https://docs.google.com/spreadsheets/d/***********-*************************-******", "サンプル1!A:C"), 3, FALSE) = "株式会社コードリック", "", VLOOKUP(B3, IMPORTRANGE("https://docs.google.com/spreadsheets/d/***********-*************************-******", "サンプル1!A:C"), 3, FALSE)), "")

これで全ての要件を満たすことができました。

本日のブログは以上となります。
今回は、簡易的な代理店管理シートを例として進めてきました。
汎用性は低いかもしれませんが、条件を絞った上で別ファイルを参照したり、その上で特定のデータを取得したりなど、応用は効きやすいと思います。

同じようなケースに出くわした際は、ぜひご参考ください。