Google Sheets Forkus

【保存版】スプレッドシートの別シートから、参照・集計する方法まとめ


参照って言葉がまずよくわからないんだよね。
ある場所（セル・シート）から、データを取ってくること（同期機能付き）よ。
※ 同じスプレッドシート内の、別シートからデータ参照しています。


参照元のデータが上書きされたら、参照先のデータも上書きされるの。だから、データ同士が同期するイメージね。


参照のメリットは、データの修正や更新が参照元だけで済み、時間の節約になるところですね。

スプレッドシートの参照には以下の3種類あります。


　Step # 1：　同一スプレッドシート内のデータ参照
　Step # 2：　同じスプレッドシートファイルの別シートからのデータ参照
　Step # 3：　別のスプレッドシートファイルからのデータ参照

1ステップずつやり方をみていきましょう。


　Step # 1：同一スプレッドシート内のデータ参照


　スプレッドシート内で、データ参照するイメージは湧きにくいかもしれません。でも、表の集計や応用など、シート内で計算を可視化させたい時には大活躍です。基本から、みていきましょう。



　セルの参照

これは簡単ね。C2セルに「=A2」を入力するだけよ。


　列の参照

=A1:A5 だけじゃダメなの？
１セルでデータを取得するから、A1:A5 を一塊だと認識させる必要があるわ。{}（波カッコ）は、連なった範囲を1グループにして、入力を1セルに済ませる便利な子よ。



　複数列の参照

{}（波カッコ）は、列が複数ある場合でも、その範囲をグループ化出来るの。
  1. 　 =ArrayFormula(A1:A5)
    2. 　ArrayFormulaは同様に範囲をグループ化します。つまり、関数版波カッコですね。
  2. 　 =Query(A1:A5)
    3. 　Query関数は範囲から情報を抜き出す関数です。


　異なる範囲をまとめて参照

今回は、{}（波カッコ）の応用ね。間にコンマ・セミコロンを置くことで、２つの異なる範囲を左右・上下に引っ付けてまとめるの。
  1. 　コンマの場合:　例　= {a, b}
    2. 　aを入力したセルに、bをその右の列に配置します。
  2. 　 セミコロンの場合:　例　= {a; b}
    3. 　aを入力したセルに、bをその下の行に配置します。


　絶対参照

関係ない気もするけど…絶対参照は、１つのセルのデータを取ってき続けたいときに使えるわ。$（ドルマーク）はF4で入力可能よ。
　
Windows F4　|　Mac F4+ fn

※ もちろん、手打ちで「$」を入力することも可能です。


【スプレッドシート】絶対・相対参照の違いって？｜$マークを使いこなそう

2020.05.01


　Step # 2：同じスプレッドシートの別シートからデータ参照


次に、同じスプレッドシートの別シートからデータ参照する方法をみていきましょう！



　セルの参照


やり方はとっても簡単です！　次の式で参照します。

= ‘参照したいシート名’!参照するセル番

例えば、次のシート名「みかん」のA1セルを参照するなら


以下の式が完成しますね。

= ‘みかん’!A1

これを、シート名「りんご」のA1セルに挿入すると


無事に、同じスプレッドシートの別シートからセルを参照することが出来ました！


次は、より実践的な例をみてみましょう。


　範囲の参照


やり方はセルの参照とほぼ一緒。 以下の部分を範囲に変えましょう。

= ‘参照したいシート名’!参照するセルの範囲

でも、範囲参照では、参照したいシートが沢山あるなんてことも多々あります…

例えば、↓の場合。


ここでは、別シートが「2014年12月期」「2018年12月期」まで、計5つもあります。

参照時に１つずつ、手作業で式のシート名を入力＆変更してもいいのですが、毎回時間がかかってしまいますね。

そこで、ドロップダウンリストを一度作ってしまえば、そのリストがあるセルをシート名の代わりに参照出来て、作業が効率化するわ。


上図だと、ドロップダウンリストはB5セルにあるから、これをシート名にするの？
それだと、シート名がB5になってしまうから、シートが見つかりませんというエラーが出るの。セル上の情報を返す、Indirect関数をつかいましょう！

つまり、本来シート名（201X年12月期）を手作業で入力するはずだった別シート参照の式（以下）を、

= ‘201X年12月期’!C2:C13

次のIndirect関数で表します。（式の解説は以下にあります。）

= Indirect(“‘” &B5 & “‘!C2:C13”)

この式を、データを参照したいF5セルに入力すると、


無事にドロップダウンリスト（B5セル）を経由して、別シートからデータ参照することが出来ました！



　式の解説【番外編】


= Indirect(“‘” &B5 & “‘!C2:C13”)
Indirect関数中で、「” “」をつけると、ただの文字としてそれを読み込ませる一方で、「” “」がないと、その文字を参照させるの。B5に「” “」がないのは、B5（＝201X年12月期）を参照させるためね。＆は文字同士をくっつける役割よ。



Indirect関数とドロップダウンリストのコンボは、動的参照をするときにとても便利ですよ！

【スプレッドシート】INDIRECT｜セル上の情報（セル番・シート名）を介して、別のセル・シートを参照（#動的参照）

2020.01.23

【スプレッドシート】ドロップ（プル）ダウンリストのつくりかた｜動的参照, INDIRECT

2020.01.28


　Step # 3：別のスプレッドシートファイルからのデータ参照


いよいよ最後です！別のスプレッドシートファイルからのデータ参照、もう少し踏ん張ってみていきましょう。


別のスプレッドシートファイルというのは、別のタブで開くことのできる、完全に分離したシートのことよ。
上のくだもの・ぶどう・いちごのこと？
そう。別々のファイルでダウンロードしたけど、１つにまとめたい時に、この参照方法は便利なの。


　セル＆範囲の参照


別ファイルのシート参照では、IMPORTRANGEというスプレッドシート独特の関数を使います。使い方はとっても簡単です。

= IMPORTRANGE(“参照するシートのURL”, “範囲orセル番”)

実際に、「ぶどう」の範囲A1:D7を、「くだもの」へ参照してみましょう。


「ぶどう」タブのURLをコピペして、後ろに取得したい範囲（A1:D7）をつけるだけです！もし、A1セルの値のみを取得したい場合は、範囲をA1に変えればOKです。


　合計値の参照（SUM関数とのコンボ）


IMPORTRANGE関数は、別の関数と合わせることによってその真価を発揮します。
もし、次のいちごの収穫量（C3:C7）を、合計値で参照したいとすると..


SUM関数を先頭につけるだけで、合計値参照することが出来ます！

= SUM(IMPORTRANGE(“いちごシートのURL”, “C3:C7”))

この式を「くだもの」タブのA1セルに入力すると…


無事に収穫量の合計値を参照できましたね！


もちろん、SUM関数の代わりに、SumifやCounta、Averageを先頭につけると、条件付き足し算やデータの数・平均値も求められます！


　条件付き合計値の参照（SUMIF関数）

= Sumif(IMPORTRANGE(“いちごシートのURL”, “C3:C7″),”>=15000″)

　収穫量が15,000(t)以上のものだけを合計しています。



　データ個数の参照（Counta関数）

= Counta(IMPORTRANGE(“いちごシートのURL”, “C3:C7”))

　収穫量のランキングデータは５つでしたね。その個数を求めています。（count関数の方が適切ですね。数・文字どちらもいけるのでcountaに頼りがちです…）


【スプレッドシート】COUNT と COUNTA｜数字のあるセル数 vs 入力値のあるセル数 を数える

2020.04.06


　平均値の参照（Average関数）

= Average(IMPORTRANGE(“いちごシートのURL”, “C3:C7”))


　別シート中の別シートの値の参照


いよいよ、長かった本章もこれにて最後です！少し日本語がおかしいような気もしますが笑　以下のケースを想定してみてください。

別シート（なし）中に別シート（日本梨と西洋梨）がありますね。



前章でやった、別シートの参照方法と考え方は一緒です。今回は「なし」シート中の西洋梨のデータを「くだもの」へ参照します。

= IMPORTRANGE(“なしシートのURL”, “西洋梨!A1:D7”))

この式を「くだもの」タブのA1セルに入力すると…


なしシート中の西洋梨シートからデータ参照ができましたね！


　Step # 4：まとめ


これにてスプレッドシートの参照のまとめは終了です。最初は量に圧倒されると思いますが、何度も使っているうちに覚えていくと思います！

不安な部分は以下から再度おさらいしてみてくださいね！

　Step # 1：　同一スプレッドシート内のデータ参照
　Step # 2：　同じスプレッドシートファイルの別シートからのデータ参照
　Step # 3：　別のスプレッドシートファイルからのデータ参照

おつかれさまでした！

RECOMMENDこちらの記事も人気です。