【INDIRECT】別のシートからデータを参照するのに便利！

XLOOKUP関数のところで数式例を挙げたうえで、「ワタシならこうは書かないわ」と何やら匂わせがありました。

この式ですね。
ではワタシならこれをどう書くかを今回紹介します。

INDIRECT関数｜数式に文字列を書かなくても済むスゴいヤツ

えー、まず書き上げた数式から見てみましょう。

なんじゃこれって状態になっていますね。
使用したのはINDIRECT関数です。

これだけだと「何がどうなっているか、さっぱりだわ！」となると思いますので、丁寧に見ていきましょう。

INDIRECT関数を使う前の数式は、こんなんでした。

これの何が気に入らないって、「1月」という文字列ですよ。
このままコピペすると2月や3月のシートを参照したいセルの数式を修正しなくてはいけないので、ワタシ的には「いーっ！」となります。

Excelでミスを起こさないためには、極力表内の数式はすべて同じ記載にすることが肝要です。

この場合、1月のシートから持ってくる列には「1月」って書いて、2月には「2月」って書いて…と、内容が異なる数式が表内にいくつも発生するわけですね。
もし12カ月分のデータを揃えるとなると12種類の数式が必要なわけですよ。
そりゃまあ、置換とか使えばさほど手間なく12種類の数式は作れますが、作った後にメンテナンスしたり表形式を変えたりごちゃごちゃやったときに「あっ、ここは修正が必要なのに漏らした」みたいな事態に陥りがちです。
それを避けるために、使う数式はひとつ！ がワタシの信条です。

で、表をよくよく見ると、「1月のシートからデータを持ってきたい」という列は見出しに「1月」と入っているじゃないですか。
これを使わない手はないです。検索対象の支店名も「東京本社」と書かずに「A3」で指定しているわけですから、これと同じようなカンジで「1月」と入っているB2セルを上手く使えばいいわけです。

…と、意気揚々と数式の「1月」の箇所を「B2」と置換すると、エラーになります。
そりゃそうだ、シート名は文字列を‘ ‘!で括ってやらないといけないのですから。

INDIRECT関数の書き方｜ほかのシートを参照

ややこしいのでちょっと取り出して見てみましょう。
INDIRECT関数はいきなり書くのは難しいので、作りやすいように数式を書いてから修正するのをオススメします。

ここに、1月シートのA2:A6を参照した数式があります。

この「1月」の部分だけ「1月」と入力されているB2セルを参照させたいです。
が、それ以外の箇所はもう固定させちゃうとして、文字列として扱います。

元の数式をINDIRECT関数の（ ）内に入れて、” “で囲みます。” “は文字列の書き方でしたね。

「1月」はこの後B2セル参照で引っ張ってくるので、それ以外の部分を” で囲んで文字列扱いします。

「1月」をB2に置き換え、前後の文字列と繋げるため&を挿入します。
セルとセルをガッシャンコするには&で繋いでいけばOKです。

そう、INDIRECT関数は参照したセルを文字列として扱うという関数でした。
これでめでたく「B2セルに入っている1月という文字列を参照して、その名称のシートのA2:A6を参照する」という数式ができました。
それを行を絶対参照に修正して、XLOOKUP関数の返す範囲にも適用させたのが冒頭の数式です。

INDIRECT関数は便利だけど、万能ではない

これの何が便利なの…？
と思いますよね。

実はワタシもINDIRECT関数って「よそのファイルやシートからデータを引っ張ってくる」というときにしか使わなくて、ほかの上手い使い道がパッと思い浮かばないです。
ですが、大量に！ もンのすごい大量にシートがあって！
あっちこっちからごっちゃごっちゃと引っ張ってこなきゃいけないシーンに遭遇すると「INDIRECT関数がないと死ねる…！」と思う程度には愛用しています。

しかし、INDIRECT関数もいいことばかりではないです。

ひとつは、やたらと長くなって視認性が悪くなること。
XLOOKUPの割とわかりやすい引数の関数ですらあんな状態になるので、もっとややこしい関数とか複数の式を組み合わせたりとかすると、自分で作っても数式バーの中身が「はあ？」みたいな状態になります。

もうひとつは、” “で囲ったところを文字列にしてしまうので、データ範囲に修正があってもExcelが自動で修正してくれないこと。
フツーは元データに1行挿入したら数式も勝手に範囲を変更してくれますが、一旦文字列にしてしまうとそうもいきません。
数式を修正したくなくてINDIRECTを使用するのに、数式を修正しなきゃいけないという、本末転倒な事態になります。

これを防ぐために、INDIRECT関数を使用するのはデータ範囲がガッチガチに確定しているモノ限定にするとか、データ範囲もどこかのセルに文字列として書き込んでおいてそれを参照するとか（そしてまた式の視認性が悪くなる）、ちょいと工夫が必要です。

もうひとつ、INDIRECT関数は、別のファイルからデータを引っ張ってきているとき、元のファイルも開いておかないとエラーになるという点にも注意が必要です。

と、つらつらと注意点を書いているとINDIRECT関数って使いづらい子と思われるかもしれませんが、いろいろあれどそれでもなお、よそのシートからデータを引っ張ってくるにはやっぱり便利なので、使えて損はない関数です。

おわりに

• INDIRECT関数が使えると、別のシートからのデータ参照が劇的に楽に！

いの一番に覚える関数でもないですが、知っていると便利です。