はじめに
データ範囲を複数のスプレッドシートから結合する
これまでシリーズで「クエリ関数」について紹介して来ました。これまでは同じスプレッドシートのファイルの中にクエリ関数を書いてデータ抽出を行って来ましたが、前回こちたの記事で、データ元の範囲指定をIMPORTRANGE関数で指定することで、スプレッドシートをもっと便利に使うことができることを紹介しました。
今回は更に発展させて、IMPORTRANGEで指定する範囲を複数に増やし、結合したデータを範囲指定する方法を紹介します！
これまでのクエリ関数シリーズの記事ははこちら
IMPORTRANGE関数
この関数は、複数のスプレッドシートがあった場合、あるスプレッドシートから別のスプレッドシートのデータを参照する時に使う関数です。
※EXCELでは、別ブックの参照は、「＝」を使ってマウスで選択するだけでできますが、スプレッドシートではそのような操作ができません。
IMPORTRANGE関数についての詳しい記事はこちら
結合方法
クエリ関数で複数のスプレッドシートから範囲を結合するためには、
- IMPORTRANGE関数で複数のスプレッドシートの範囲を結合する
- 結合した範囲を、クエリ関数の範囲に指定する
といった具合に進めます。
IMPORTRANGEで複数の範囲を結合する
まずは、IMPORTRANGE関数で複数のスプレッドシートを結合する方法から始めます。
次の2つのスプレッドシートを、３つ目の表示用のスプレッドシートに結合して表示させます。
「IMPORTRANGE１」「IMPORTRANGE２」の２つの同じ項目（店舗名・商品・売上）で構成されたスプレッドシートです。
結合の方法には以下の２つの種類があります。
- 横結合
- 縦結合
横結合の構文
｛IMPORTRANGE関数,IMPORTRANGE関数}
横結合の場合は、IMPORTRANGE関数を「,」カンマで繋げて、全体を「{}」でくくります。
表示用スプレッドシートのA1セルに、以下の数式を入れます。
={IMPORTRANGE(“スプレッドシートキー1″,”‘シート1’!A1:C4”), IMPORTRANGE(“スプレッドシートキー2″,”‘シート1’!A1:C4”)}
※IMPORTRANGE関数の使い方が分からない方は先に以下をご覧下さい。
注意店として、初めてIMPORTRANGE関数を使う時は、数式を入力した後、次のようなメッセージが流れてアクセス許可をクリックしないといけないのですが、結合するスプレッドシート全てでアクセス許可が行われている状態じゃないとエラーになってしまいます。
数式の入力結果は下記です。
2つのスプレッドシートがよこにつながって表示されました。これが横結合です。
実務ではあまり使わないかもしれないですね。使いやすいの次の紹介する「縦結合」です。
縦結合の構文
｛IMPORTRANGE関数;IMPORTRANGE関数}
基本的に横結合と同じですが、IMPORTRANGERANGE関数をつなげる記号が、「;」セミコロンになります。
={IMPORTRANGE(“スプレッドシートキー1″,”‘シート1’!A1:C4”); IMPORTRANGE(“スプレッドシートキー2″,”‘シート1’!A1:C4”)}
実際に、先程の数式の「,」カンマを「;」セミコロンに変えてみましょう。
図のように、縦につながります。
見出しが2回出てきちゃうのはブサイクなので、範囲の指定に工夫をしましょう。2つ目のIMPORTRANGE関数の読み込み範囲を「A2:C4」に変更します。
2つ目のIMPORTRANGE関数の指定範囲をA2からにすることによって、見出しを除いた範囲を結合しているため、使いやすい形で表示することができました。
クエリ関数に指定の指定する
クエリ関数で複数のスプレッドシートの範囲を結合して指定するには、上記のIMPORTRANGE関数で結合した数式を、そのままクエリ関数のデータ範囲に指定します。
クエリア関数の構文
= QUERY( 範囲, “式”)
式は、簡単に1列目と2列目を表示させるクエリ式を入れます。
※クエリ関数が分からない方は先にこちらをどうぞ。
表示用のスプレッドシートのA1セルに次の数式を入力します。
=QUERY({IMPORTRANGE(“スプレッドシートキー1″,”‘シート1’!A1:C4”); IMPORTRANGE(“スプレッドシートキー2″,”‘シート1’!A2:C4″)},”select Col1,Col2”)
長いですが、先程のIMPORTRANGE関数の結合とQUERY関数を組み合わせているだけです。
※クエリ関数のデータ範囲を、IMPORTRANGEで指定した場合、AやBといった列名での指定はできません。「Col1」「Col2」などの列番号での指定をして下さい。
結合した範囲の1列目、2列目だけがクエリ関数で抽出されました。
まとめ
以上、今回はクエリ関数に複数のスプレッドシートを結合したデータを指定する方法を紹介しました！
同じ形のスプレッドシートを複数人に配布して、集計用のスプレッドシートに結合してリアルタイムに結果処理するなど、スプレッドシート独特の使い方ができそうですね！
コメント