スプレッドシートのimportrange関数で別シートを参照・集計する方法

importrange関数の使い方アイキャッチ

importrange関数の使い方アイキャッチ

こんにちは、シラカバをご覧いただきありがとうございます。WEBマーケ@ガーシラです。

今回は、Googleスプレッドシートで使える関数の紹介シリーズ、今回は、「importrange関数」についてご紹介します。

この関数はExcelにはない関数で、別シートのデータを参照や加工したい時に便利なスプレッドシートのみにある独自関数です!

Excelの利用の方はよろしければGoogleスプレッドシートの利用をオススメする理由の記事もご覧ください。

それでは、気を取り直してどうぞ!

1.スプレッドシートのimportrange関数の使い方【基本】

1.importrange関数とは?

How-to-importrange

 

上記のように「参照元シート:苗字ランキング」から別シートの「情報取得用シート」へ情報を取得するために利用しています。

データを取り扱う上で集計・加工用のシートと入力や修正をしない元データを参照したい時ってありますよね?

そんな時に活躍してくれる関数です。

1-1.基本構文の確認

importrange(“スプレッドシートキー”,”範囲の文字列”) で表現されます。 上記のgif画像の記述を例として書くと,importrange(“https://docs.google.com/spreadsheets/d/13eCbbRlc8ROnuNYR13RN6PLQ3s2NxUSQ1_Y5ZEC4Yos/edit#gid=0″,”シート!A:C”)と書いて、参照元のシートを読み込み、別のスプレッドシート:情報取得用で扱える状態にしています。

1-2.元データが更新されたらちゃんと自動で更新される

how-to-importrange-02スプレッドシートをデータベースのように利用している方にとっては、元データ数人で記入してもらう。など編集を可能にしておき、情報取得用シートでimportrange関数を利用してデータを常に加工したいように自分で作成をしておくことも可能です。便利ですねー。

1-3.よくあるエラーの対処法

エラー(共有許可)初心者が記述ミス以外に少し慌てるエラーで共有のアクセス許可というimportrange特有のものがあるので紹介しておきます。上のGIF画像の通り初めて別のシートを参照する時は許可を行う必要があります。

2.応用編別の関数との合わせ技

how-to-use-importrange-1-3

2-1.別シートのデータから最大値を抽出したい場合:MAX関数との合わせ技

構文例:MAX(importrange(“参照したスプレッドシートのURL”,”シート名!範囲文字列”))

例:MAX(importrange(“https://docs.google.com/spreadsheets/d/13eCbbRlc8ROnuNYR13RN6PLQ3s2NxUSQ1_Y5ZEC4Yos/edit#gid=0″,”シート!E2:E21”))

これで売上高の最大のアイテムを上記では抽出しています。

2-1.別シートのデータから最小値を抽出したい場合:MIN関数との合わせ技

構文例:MIN(importrange(“参照したスプレッドシートのURL”,”シート名!範囲文字列”))

例:MIN(importrange(“https://docs.google.com/spreadsheets/d/13eCbbRlc8ROnuNYR13RN6PLQ3s2NxUSQ1_Y5ZEC4Yos/edit#gid=0″,”シート!E2:E21”))

2-3.別シートのデータを集計して合計を抽出したい場合:AVERAGE関数との合わせ技

構文例:AVERAGE(importrange(“参照したスプレッドシートのURL”,”シート名!範囲文字列”))

例:AVERAGE(importrange(“https://docs.google.com/spreadsheets/d/13eCbbRlc8ROnuNYR13RN6PLQ3s2NxUSQ1_Y5ZEC4Yos/edit#gid=0″,”シート!E2:E21”))

2-4.別シートのデータを集計して合計を抽出したい場合:SUM関数との合わせ技

構文例:SUM(importrange(“参照したスプレッドシートのURL”,”シート名!範囲文字列”))

例:SUM(importrange(“https://docs.google.com/spreadsheets/d/13eCbbRlc8ROnuNYR13RN6PLQ3s2NxUSQ1_Y5ZEC4Yos/edit#gid=0″,”シート!E2:E21”))

2-5.別シートのデータから指定データの情報を抽出したいVLOOK関数との合わせ技

 

構文例:Vlookup(検索値,importrange(“参照したスプレッドシートのURL”,”シート名!範囲文字列”),欲しい情報が記載された列数,FALSE)

例:Vlookup(1001,importrange(“https://docs.google.com/spreadsheets/d/13eCbbRlc8ROnuNYR13RN6PLQ3s2NxUSQ1_Y5ZEC4Yos/edit#gid=0″,”シート!A:E”),5,0)

2-6.別シートのデータから指定条件にあった関数のデータのみ集計したい場合:QUERY関数との合わせ技

構文例:QUERY(importrange(“参照したスプレッドシートのURL”,”シート名!範囲文字列”), “where Col列番号 = ‘検索値’ “)

例:QUERY(importrange(“https://docs.google.com/spreadsheets/d/13eCbbRlc8ROnuNYR13RN6PLQ3s2NxUSQ1_Y5ZEC4Yos/edit#gid=401678467″,”シート2!A:G”), “where Col7 = ‘受注’ “)

通常Query関数は、QUERY( IMPORTRANGE( “シートID” ,”参照範囲”), “条件”)という書き方をしますが、この条件の箇所がimportrange関数を使用した場合は異なります。

条件文の書き方に関しては詳しくここでは述べませんが、where A = “検索値”と通常Query関数だけでは書きますが、importrange関数との併用の場合は、

Col1/col2・・・・といったようにCol、つまりColumn(=列)の数を指定します。 左から数えて何番目に指定したい検索値があるかによって設定をしてみてください。

まとめ

いかがでしたでしょうか?

importrange関数は今回紹介した以外にも使い方は様々あると思います。

スプレッドシートを利用していると頻度も非常に多い関数になると思いますので、ぜひどんどん活用してみてください。

それでは、また今日も1%と業務効率と1%の業績アップに向けて勉強ありがとうございました。

コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です