月ごとのシートから数字を集めたいとき、「4月」「5月」「6月」…と、シートごとに参照を書き直していませんか。
- ・セルに入力したシート名に合わせて、参照先を切り替えたい
- ・1つ目のプルダウンで選んだ内容によって、2つ目のプルダウンの選択肢を変えたい
- ・INDIRECT関数が便利と聞くけど、何をする関数なのかよく分からない
そんなときに役立つのがINDIRECT（インダイレクト）関数です。この記事では、INDIRECT関数のしくみと、仕事でよく使う3つの使い方、エラーになるときの注意点を紹介します。記事の最後には、手を動かして確かめたい方向けのおまけの練習問題もご用意しました。
INDIRECT関数とは
INDIRECT関数は、文字で書いた「セルの場所」を、本物のセル参照に変えて、そのセルの値を取り出す関数です。
=INDIRECT(参照文字列, [参照形式])| 引数 | 意味 |
|---|---|
| 参照文字列 | セルの場所を表す文字（例：”B2″）。セル参照や、名前の定義で付けた名前も指定できる |
| 参照形式（省略可） | 省略するかTRUEなら「A1」の形式、FALSEなら「R1C1」の形式として読み取る。ふだんは省略でOK |
たとえば、次の数式はどちらもB2セルの値を返します。
=B2
=INDIRECT("B2")これだけだと普通の参照と変わりませんが、INDIRECT関数の本領は、「場所を表す文字」をほかのセルの値や文字の組み合わせで作れるところにあります。A2セルに「B2」という文字が入っていれば、=INDIRECT(A2)でB2の値を取り出せます。A2の中身を「B3」に変えれば、取り出す先もB3に切り替わります。
使い方1：文字をつなげて参照先を切り替える
「&」で文字をつなげると、参照先の一部だけをセルの値で変えられます。A5セルに「3」と入力されているとき、次の数式はB3セルの値を返します。
=INDIRECT("B"&A5)A5の数字を変えるだけで、B列の何行目を取り出すかを切り替えられます。
使い方2：シート名をセルで指定して、別シートの値を集める
仕事で特に役立つのがこの使い方です。「4月」「5月」「6月」の3枚のシートがあり、それぞれのB5セルに月の合計が入っているとします。集計用のシートのA列にシート名を並べておけば、B列に次の数式を入れるだけで、各シートの合計を集められます。
=INDIRECT(A2&"!B5")
A2が「4月」なら、数式の中で「4月!B5」という文字ができあがり、「4月」シートのB5の値が返ります。数式を下にコピーすれば、「5月」「6月」も同じように取り出せます。7月のシートを追加したときも、A列に「7月」と書き足すだけで済みます。
シート名は最初から「’」で囲んでおくと安心
で囲む
シート名に「4月 実績」のようにスペースが入っていると、上の数式では#REF!エラーになります。シート名を半角の「’」（シングルクォーテーション）で囲む形にしておくと、このエラーを防げます。
=INDIRECT("'"&A2&"'!B5")「’」で囲む書き方は、スペースが入っていないシート名でもそのまま使えます（「4月」のようなシート名でも、同じように値を取り出せます）。あとから誰かがシート名にスペースを入れても困らないように、シート名をつなげるときは、最初から「’」で囲む書き方にしておくのがおすすめです。
使い方3：連動するプルダウンを作る
1つ目のプルダウンで「果物」を選んだら、2つ目のプルダウンには果物の名前だけが出る。このような連動するプルダウンも、INDIRECT関数と「名前の定義」の組み合わせで作れます。
手順1：選択肢の範囲に名前を付ける
まず、2つ目のプルダウンの選択肢を分類ごとに並べ、それぞれの範囲に1つ目のプルダウンの項目と同じ名前を付けます。
| 範囲 | 中身 | 付ける名前 |
|---|---|---|
| E2:E4 | りんご・みかん・ぶどう | 果物 |
| F2:F4 | にんじん・たまねぎ・キャベツ | 野菜 |
範囲を選択して、左上の名前ボックスに「果物」と入力してEnterを押すと、名前を付けられます。名前の付け方は名前の定義の使い方で詳しく解説しています。
手順2：1つ目のプルダウンを作る
A2セルを選択し、「データ」タブの「データの入力規則」を開きます。「入力値の種類」で「リスト」を選び、「元の値」に「果物,野菜」と入力して「OK」を押します。
手順3：2つ目のプルダウンにINDIRECT関数を入れる
B2セルを選択し、同じように「データの入力規則」を開いて「リスト」を選びます。「元の値」には次の数式を入力します。
=INDIRECT(A2)
これで、A2で「果物」を選ぶと、B2のプルダウンには「果物」と名前を付けた範囲（りんご・みかん・ぶどう）が表示されます。A2を「野菜」に変えれば、B2の選択肢も野菜に切り替わります。
なお、名前には使えない文字があり、スペースを含む名前は付けられません。1つ目のプルダウンの項目は、スペースを入れずに作っておきましょう。プルダウンの基本の作り方はプルダウン（ドロップダウンリスト）の作り方で紹介しています。
INDIRECT関数を使うときの注意点
| こんなとき | 何が起きる？ | 対処 |
|---|---|---|
| 参照先の文字が正しいセルの場所になっていない（シート名の打ち間違い・存在しないシートなど） | #REF!エラー | 参照先を作っているセルの中身や、つないだ文字を確認する |
| ほかのブックを参照していて、そのブックが閉じている | #REF!エラー | 参照先のブックを開いておく |
| シート名をあとから変更した | 数式は自動で書き換わらない | シート名を入れたセル（A列など）も合わせて直す |
| INDIRECT関数をたくさん使っている | ブックの再計算に時間がかかることがある | 必要な場所だけに使う |
INDIRECT関数は、参照先を「文字」で持っているので、シート名を変えたり、行や列を挿入したりしても、普通の参照のように自動では追いかけてくれません。シート名を変えたら、シート名を入れたセルも一緒に直しましょう。
また、Microsoftの開発者向けの資料では、INDIRECT関数は「揮発性関数」の1つとされています。揮発性関数は、ほかのセルを変更して再計算が行われるたびに計算し直されるため、使いすぎると再計算に時間がかかることがあります。
おまけ：練習問題で手を動かしてみよう
INDIRECT関数は、読むだけではイメージしにくい関数です。実際に数式を入れて試せる練習問題ファイルを用意しました。記事の例とは少し条件を変えて、店舗ごとのシートから値を取り出す6問に挑戦できます。
- 「問題」シート：問1〜問6が並んでいます。黄色のセルが答えを入力するところです。問1・問2は基本、問3〜問5は別シートの参照、問6は連動プルダウン作りです。
- 店舗のシート（東京・大阪・名古屋・福岡 天神）：問3〜問5で値を取り出す先のシートです。
- 「解答」シート：一番右の灰色のタブです。正解の数式と、問題ごとの解説が載っています。
書き方が違っても、結果が同じなら正解です。ファイルにマクロは含まれていません。
まとめ
- INDIRECT関数は、文字で書いたセルの場所を本物の参照に変えて、値を取り出す関数
- 「&」で文字をつなげると、参照先をセルの値で切り替えられる
- =INDIRECT(A2&”!B5″)で、シート名をセルで指定して別シートの値を集められる（シート名は最初から「’」で囲んでおくと安心）
- 名前の定義と組み合わせると、連動するプルダウンが作れる
- シート名の変更には自動で追いかけないので、シート名を入れたセルも一緒に直す
腕試しをしたい方は、おまけの練習問題にも挑戦してみてください。
