エクセル、Googleスプレッドシートで動的に対象範囲を変更できる便利な関数「INDIRECT」
エクセルって本当になんでもできるw
エクセルでデータをまとめるっていうのは今や仕事でできないといけないことです。
おそらく年々扱うデータの数は増えていて数字を一つ一つコピペしてこなせる仕事なんてほぼなくなっているのではないでしょうか。
そういう私もベースは技術職ですが、不良率の分析などエクセルでのデータ分析が仕事で欠かせません。
扱うデータが増えるほど効率化していかないと仕事をこなせないわけですが、そういう時に便利なのが関数です。関数を使いこなせるかどうかでしごとの質は大きく変わると思います。
私も少しずつ覚えていきましたが、仕事の次元が一段上がった！と感じた関数が「INDIRECT」という関数です。
複数のシートを1枚にまとめる時に便利
「INDIRECT」とは一言で言うと「対照範囲を動的に変更できる関数」です。
例えば、毎日エクセルで作成される資料があって、1か月分で一つのエクセルになっているとします。
顧客訪問レポートや生産記録、品質検査レポートなどでしょうか。
シートがたくさんあるから見にくいので1枚のサマリーのシートを作成したいと考えた時に、手作業でやろうとしたらかなり大変ですよね。
足算するにもすべてのシートを開いてセルを指定して、なんてやってられないです。
外部データを取り込んで、日々、関数の対象範囲が変わってしまう時なども
また、こういう使い方をしている方も多いかと思いますが、どこかのサーバーのデータベースとエクセルを接続して、そのデータベースの中身を一定の抽出条件に従ってエクセルにダウンロードするという方法。
日々の売り上げ実績や生産実績など大きなデータはそれなりの大きなシステムで管理していると思います。そこからデータを毎回ダウンロードしてもいいですが、エクセルから接続し、更新すると最新の情報に更新されるのでその後の加工も非常に簡単です。
こういう時に困るのが更新するたびに関数の対象範囲が変わってしまうことです。
月に一回データを更新するとして、更新するたびにデータの個数が同じとは限りません。ちがうことのほうが多いでしょう。
ある月は2行目から100行目までデータあり、それらをSUMしてたとして、次の月はデータが150行目まであったとします。
そうするとSUMの範囲を外れてしまうので足すべき数字が漏れてしまいます。
また、私はプライベートでIFTTTとGoogleスプレッドシートを使って日々の行動を記録しています。
記録するたびにデータが増えていくのでこの場合でも何か関数を組んでいた場合に対象範囲は固定されているので漏れが出てきてうまく計算されなくなってしまいます。
https://out-of-antenna.biz/productivity/google-spread-sheet-...Indirectの使い方
さて、そんなIndirectの使い方です。
Indirectは要は範囲を示す関数です。
例えば、「A1:T5」というセル範囲があったら、それをIndirect関数を使用すると以下のように記述します。
=INDIRECT(“A1:T5”)簡単ですねw
これを応用して「=SUM(A1:T5)」というシンプルなSUM関数があったら、
=SUM(INDIRECT(“A1:T5”))と書いてあげれば同じ意味になります。
「え？これ、無駄に複雑に書いているだけじゃない？」と思われるかもしれませんが、そうじゃないんです。
INDRECT関数のカッコ中身を他のセルの値を使って状況に合わせて変更することができるんです！
例えば、たくさんのシートがあるエクセルで各シートの特定のセルの中身を一つのシートにまとめて表示させたい場合。
「まとめ」シートでは表を作成していますが、そこに月を書いています。
この月名はシート名と合わせているのですが、このようにしたうえで以下のようにIndirect関数を利用するとこんな感じになります。
=INDIRECT("'"&B2&"'!$B$2")INDIRECTのカッコの中には文字列を入力します。そのため、基本的にはすべてダブルクオテーションで囲む必要があります。
INDIRECTは関数ですからセル参照を挿入することができます。その場合は、セル参照の前後の文字列をダブルクオテーションで区切って、＆という記号を使います。
＆は文字列と文字列をつなげるという意味があり、「=”A”&”B”」と書くと「AB」と表示されます。
上に書いて数式は複雑に見えますが、「=INDIRECT(“文字列”&セル参照&”文字列”)」という構造をしており、結局は「=INDIRECT(“‘4月’!B2”)」ということになり、シート「4月」のセルB2を参照する「=‘4月’!B2」という数式になります。
さて、ここからがINDIRECTを使う意味なのですが、この「=INDIRECT(“'"&B2&"'!$B$2")」という色をコピーして横の5月、6月と書かれたセルの下にペーストしていきます。
そうすると「=INDIRECT(“文字列”&セル参照&”文字列”)」のセル参照の部分が自分の真上のセルを参照しているのでどんどん参照がずれていって各シートのB2セルを「まとめ」シートに表示させることができます。
さて、ここまで長かったですが、いかがだったでしょうか。INDIRECTを使えばこのように参照範囲をセルに入力してある文字列を利用して記述することができます。
エクセルの構造が複雑になるほど使用できると便利になります。
さらに複雑で同じような関数にOFFSETというものもありますが、こういうものを使えるようになるとまた一段エクセル、スプレッドシートの利用価値が高まると思います。