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


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


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


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

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



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


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


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



 セルの参照

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


 列の参照

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



 複数列の参照

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


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

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


 絶対参照

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

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


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

2020.05.01


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


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



 セルの参照


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

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

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


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

= ‘みかん’!A1

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


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


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


 範囲の参照


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

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

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

例えば、↓の場合。


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

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

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


上図だと、ドロップダウンリストは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:別のスプレッドシートファイルからのデータ参照


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


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


 セル&範囲の参照


別ファイルのシート参照では、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”))

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


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

2020.04.06


 平均値の参照(Average関数)

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


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


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

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



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

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

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


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


 Step # 4:まとめ


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

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


おつかれさまでした!