同じフォーマットの別々のシートからデータを一撃でデータを集めてくる方法〜INDIRECT関数とCNCATENATE関数のあわせ技〜

こんにちは、よさそう®です。
タイトルが長すぎでごめんなさい(笑)

お仕事していると、いくつかのシートに同じようなデータがあって、それを1枚のシートにまとめたいといったことありませんか？

たとえば「年度ごとに別れている売上データの合計値を1枚にまとめて年度推
移をみたい」とか。

そうしたときに1つずつセルに参照先を「=’2021年度’!A3」とかって書いていくの面倒ですよね。
しかも年度が増えたらまた一つずつ書かないといけなくなる。

そうしたときに使えるのがINDIRECT関数です。そしてCONCATENATE関数と組み合わせると驚異的に手間が省けます。

ということで今回はこの2つの関数をご紹介いたします。ExcelでもGoogle Spread Sheetでも使えますので、ご安心ください。

INDIRECT関数とは

INDIRECT関数は文字列で指定したセルの参照を返す、関数です。
たとえば、「=INDIRECT("A3")」と書いたら「A3セルに書いてある情報に基づいて、データを取ってこい」という意味になります。

B5セルでINDIRECT関数を使って、A5セルを参照して、A2セルの情報をとってきている様子

上の画像でB5セルに「=INDIRECT(A5)」と書いています。これにより「A5セルに書いてある情報に基づいてデータを取ってくる」となります。
A5セルには「A2」と書いてあります。A2セルには「150」と書いてあります。その結果B5セルには「150」がセットされます。

つまりセルに書かれたセルの場所に応じて、表示する情報を変えられます。そして同じシートだけでなく、別のシートの情報もとってこれます。
たとえば「=INDIRECT("'2021年度'!A3")」と書いたら「2021年度シートのA3セルに書いてある情報を取得する」となります。

CONCATENATE関数を組合わせる

INDIRECT関数だけでも、十分に便利なのですが、複数年に渡ってデータを収拾するときにはちょっと面倒です。毎回セルのなかのINDIRECT関数をコピペして参照先のシート名を変えてという作業が必要になります。
できればこうした作業はミスを誘発するので避けたいものです。

そこでCONCATENATE関数の出番です。CONCATENATE関数は関数の中に書かれた文字列を組み合わせます。
たとえば「=CONCATENATE("A","3")」と書くと、「A3」と表示されます。

このCONCATENATE関数をINDIRECT関数と組みあわせると、INDIRECT関数のなかで判定する文字列をCONCATENATE関数で生成することができるのです。
これができると、複数シートに渡った同じフォーマットのデータを取ってくるときに楽ちんになります。
楽ちんになる様子を次の動画でご紹介します。

使い方を動画でご紹介

動画ではここまでお話したきたように、各年の情報が別シートにあり、それを1つにまとめる手順をご紹介しています。
大事なのは、まとめるシートの行頭にシート名を、そして列に参照先セルの名前を書いておきます。
それをCONCATENATE関数とINDIRECT関数を組み合わせて参照先を特定しているところです。
これにより、シートが増えても、関数をコピペして行頭のシート名だけ変えれば作業は完了となります。

関数は一つずつでも強力です。そして組み合わせによりさらにパワーアップして、みなさんのお仕事の手間を激減できます。ぜひこうした状況がやってきたらご活用なさってみてください。

最後までお読みいただきありがとうございました。少しでもあなたのお役立ちになれば嬉しいです。
よろしければいいね！やフォローをいただけますと書き続ける励みになりますので、応援よろしくお願いします。

それでは、また。