【3ステップ】スプレッドシートの別シートを参照・集計する方法まとめ


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


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


参照のメリットは、データの修正や更新が参照元だけで済み、時間の節約になるところですね。一方、一口に参照といっても、スプレッドシートの参照には以下の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をその下の行に配置します。



 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つでしたね。その個数を求めています。



 平均値の参照(Average関数)

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


 別シート中の別シートの値の参照(SUM関数とのコンボ)


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

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



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

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

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


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


 Step # 4:まとめ


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

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


おつかれさまでした!