【超便利】スプレッドシートで別シートから参照したり集計したりする方法まとめ

refer-anothersheet

SPONSORED LINK

こんにちは、いろんな効率化が大好きなたぬ(@KAivlys)です!

値を参照するメリットは、参照元のデータを変更したとき自動で参照した値も変わることですよね。

つまり、積極的に値を参照すると、データの修正や更新に時間を奪われることが激減します!

同じシートの中から値を参照するのは簡単ですが、別のスプレッドシートから値だったり、同じスプレッドシートの別シートの値だったりを引っ張ってきて、使いたいことってありませんか?

  1. 別のスプレッドシートから参照する
  2. 同じスプレッドシートの別シートから参照する

今回は2つに分けて別シートから値を参照する方法を紹介します。

同じシート内からだけじゃなく、別シートからも値を参照して、仕事の効率化を加速させましょう!

別のスプレッドシートから参照する

refer-anothersheet1

別のシートから値を参照するためには、『IMPORTRANGE』というスプレッドシートにしかない関数を使用します。

知っていたらドヤれる!スプレッドシート独自の関数を10個まとめてみた

2018.03.22
構文
IMPORTRANGE("スプレッドシートキー", "範囲の文字列")

『スプレッドシートキー』というところに値を参照したいスプレッドシートのURLを入力します。

※スプレッドシートキーとは、スプレッドシートのURLを指した名称で、以下の例でいう「××××」の部分のことです。

(例:https://docs.google.com/spreadsheets/d/××××/edit#gid=0)

値(単一セル)を参照したい場合

構文
IMPORTRANGE("スプレッドシートキー", "範囲の文字列")

この『範囲の文字列』のところに、"持ってきたいシートの名前!持ってきたいセル"を入力します。

例えば、データベースという名前のシートのA1セルを参照したい場合は、"データベース!A1"と入力すれば大丈夫です。

表(範囲セル)を参照する

refer-anothersheet2

表を参照したい場合は、『範囲の文字列』のところに、"持ってきたいシートの名前!持ってきたい範囲セル"を入力します。

例えば、データベースという名前のシートのA1からG9を参照したい場合は、"データベース!A1:G9"と入力すれば大丈夫です。

集計したデータや表引きを参照する方法

ただ、値を引っ張ってをくるだけじゃ面白くないですよね!

IMPORTRANGE関数は、他の関数と組み合わせることでその真価を発揮します。

例1:合計金額を参照する(SUM関数)

合計金額だけを参照したいときは、SUM関数とIMPORTRANGE関数を組み合わせます。

例えば、 別のシートのA列の合計金額を参照したい場合は、次のようにSUM関数と組み合わせます。

数式
=SUM(IMPORTRANGE("1gNduUGoxaYd30MyDovseKxkojmiAWzWdGpGDglK9d-c","A:A"))
refer-anothersheet5

他の集計データを参照したい場合は、SUM以外に、SUMIFやSUMIFS、COUNTA、COUNTIF、COUNTIFSなどと組み合わせてみてはいかがでしょうか。色々捗りますよ(^o^)

例2:表引きを実現させる(VLOOKUP関数)

次に、他のスプレッドシートから表引きしたいときは、VLOOKUP関数とIMPORTRANGE関数を組み合わせます。

例えば、次のような表があったとします。

refer-anothersheet3

ここで『1002』をキーとして、商品名である『ぶどう』を参照したい場合は、次のように記述します。

数式
=VLOOKUP(1002,IMPORTRANGE("1gNduUGoxaYd30MyDovseKxkojmiAWzWdGpGDglK9d-c","A1:C6"),2,FALSE)

※VLOOKUPの使い方は割愛します。

refer-anothersheet4

ほら、こんな感じで参照することができました。

IMPORTRANGE関数とVLOOKUP関数を組み合わせれば、このように他のスプレッドシートであっても表引きを実現できるんです。

スプレッドシートすごすぎません?Excelには出来ない芸当ですよね。

例3:スプレッドシートを簡易的なデータベースとして使う

集計したデータや表引きしたデータだけをIMPORTRANGEで持ってくるということは、スプレッドシートをデータベースのように扱うことができます。

スプレッドシートは200万個のセルにしか値を入力できませんが、IMPORTRANGEを使えば実質無限のセルを使うことができるんですよ。

詳しくは割愛しますが、気になる方はこの記事をどうぞ!

スプレッドシートの『IMPORTRANGE』関数で200万の制約が無くなるって本当?

2017.12.26

同じスプレッドシートの別シートから参照する

refer-anothersheet7

次に、同じスプレッドシートの別シートから参照する方法を紹介します。

値(単一セル)を参照する

やり方はとっても簡単です。

シングルクォーテーションで参照したい値を持つシート名を挟んで、その値のセル番号を入力するだけです。

同じスプレッドシートの別シートから値を取得する方法
='参照したい値を持っているシート名'!セル
refer-anothersheet6

[実践テクニック]参照したい値を持つシート名がセルに入力されていて、その値を参照して、B1セルに目的の値を参照させたい場合

refer-anothersheet11

日本語って難しい、笑

まず、A1セルに『参照元のシート』が入力されているので、B1セルに『='A1'!A1』みたいな感じで目的の値を参照したくなりませんか?

Before
='参照元のシート'!A1
解決策1
='A1'!A1

ぱっと見いけそうな感じですが、「『A1』という名前のシートが無いよ」というエラーを返されます。

refer-anothersheet8

であれば、次の方法として『&』を使って、文字列を分解して参照したくなりますよね。

Before
='A1'!A1
解決策2
="'"&A1&"'!A1"

ですが、このままだと単に文字列を結合させてるだけに過ぎないので、『'参照元のソート'!A1』という文字列のセル参照が返されます。

refer-anothersheet9

文字列でセル参照が返されるということは、文字列で指定したセル参照を返す、あの関数を使えば解決しますね。

そう、INDIRECT関数です!

Before
="'"&A1&"'!A1"
答え
=INDIRECT("'"&A1&"'!A1")
refer-anothersheet10

これで、A1セルの値を参照して別のシートから値を参照することができるようになりましたね。

INDIRECTは、他にも動的に関数を作り込むときに役立つので、覚えておくと本当に便利ですよ!

【超便利】スプレッドシートの『INDIRECT』関数で動的にセルを指定する方法

2018.01.06

表(範囲セル)を参照する

refer-anothersheet13

同じスプレッドシート内で表(範囲セル)を引っ張って場合は、さっきの『別のスプレッドシートから値を取得する』のところ登場したIMPORTRANGE関数を使います。

構文
IMPORTRANGE("スプレッドシートキー", "範囲の文字列")

ちょっと変な感じですが、スプレッドシートキーは今開いているスプレッドシートのURLを入力します。

refer-anothersheet12

これで範囲指定してあげれば、同じスプレッドシート内でも表を参照することができるという訳です。

まとめ

  1. 別のスプレッドシートから値を参照する
  2. 同じスプレッドシートの別シートから値を参照する

今回は、別シートから値を参照する方法を紹介しました。

冒頭でも述べましたが、同じシート内からだけじゃなく、別シートからも値を参照して、仕事の効率化を加速させましょう!

それでは!

SPONSORED LINK

refer-anothersheet

Time Ticketはじめました
実務で即戦力になるためのスプレッドシート講座開講!

記事を読んでも、よく分からなかった人やもっと深く知りたい人はこちらからご連絡ください。

ブログ特典!!

『クーポンコード:9876』とメッセージいただければ、どのレベルのコースでも初級コースと同じ価格で教えます!!

スプレッドシートをより深く学んで、実務で即戦力になりませんか?

ABOUTこの記事を書いてる人

たぬ(谷口健太)

25歳社会人。本職は福岡の通販会社でWebマーケッターやっとります。 プログラミング✕マーケティング=最強説を唱えており、非効率的な世の中に革命を起こそうと本ブログを設立。 ストレングスファインダー:戦略性・最上志向・目標志向・未来志向・個別化