【スプレッドシート】INDIRECT|取得したセル上のセル番号を介して、別のセルを参照(#動的参照)

INDIRECT関数って構文みてもよくわからないんだよね。

 構文


文字列で指定したセル参照を、入力セルに返します。

INDIRECT(セル参照の文字列, [A1 形式にする])

 セル参照の文字列:セル参照を、二重引用符(“_”)で囲んだ文字列で指定します。
 [A1 形式にする]:セルの参照形式を A1 形式と R1C1 形式のどちらにするかを指定します。(デフォルトは TRUE)



例題もみれば、何かわかるんじゃない?

 例題


黄色セル上の文字列(セルの番号)を介して、そのセル番の元データ(B2=1等)を引き出す

サンプルファイル

 回答

 手順1: C8〜9を選択状態にし、=Indirect()を入力する。
 手順2: B2〜D6がある黄色セルを、()に指定すると、実際のB2〜D6セル上の値が取得される。


つまり、B8セル上にある「B2」を介して、本当のB2セルにある「1」を取ってきてる…ってこと?
その通りよ。「indirect」は「間接的」、つまり言葉通り、何かを間接的に取得する関数ね。
 セル参照の文字列:セル参照を、二重引用符(“_”)で囲んだ文字列で指定します。

ってあったけど、二重引用符は使わないの? 
二重引用符のあり・なしを比べてみましょうか。

Step # 1:二重引用符のつかいかた



 例1

  1.  二重引用符あり:indirect(“A1”)

  2.  A1の中身(=あら便利)を、単なる文字列として取得します。よって「あら便利」はただの文字としてそのまま表示されます。

  3.   二重引用符なし:indirect(B4)

  4.  B4の中身(=A1)を、文字列ではなく、真の意味で取得します。A1の真の意味は、「A1セルの情報の参照」、つまり「あら便利」を参照、取得します。


    じゃあ「文字列」は文字通り「文字」として「A1」の中身(=あら便利)をとってくるけど、もし「真の意味」で取得すると、「あら便利」の「真の意味」を発揮させるってこと?
    そうね。次の例で、スッキリするんじゃないかしら。

     例2

  5.  二重引用符なし:indirect(A1)

  6.  A1の中身(=あら便利)を、真の意味で取得します。しかし、「あら便利」にはシートや範囲・セル参照など、真の意味はないため、「見つけられない」という#REF!(エラー)を返します。

  7.   二重引用符あり:indirect(“B4”)

  8.  B4の中身(=A1)を、文字列として参照します。つまり、A1の真の意味(=あら便利)は発揮されず、そのままA1という文字で返されます。


    二重引用符なしは、真の意味での取得だけど、真の意味がない場合はエラーなのか…
    「” “」があると文字列! 「” “」なしは真の意味でセル取得よ!
    「” “」の有無で取得方法を選択できるの。これって実は便利なのよ。
    例えばどんな時?

    Step # 2:indirect関数をつかうと便利なケースって?


     


    こんな風に、プルダウンのあるセルを参照すれば、別のシートのデータを簡単に参照できるわ。
    おお…。便利!
    以下ではindirect関数ありとなしの方法をそれぞれ見てみましょう。

     つくりかた

    1.  Indirect関数なし

    2. シート名「2014年12月期」のセル範囲「C2:C13」を、Indirect関数なしで参照する時、

       F4 = ‘2014年12月期’!C2:C13

      を使うと思います。でも、シート名が変わるとすごく困ります。

      式のシート名も変更することになり、もしこの式を沢山使っていたら、全式を手作業で直すことに…


      = ‘参照したいシート名’!(そのシート中で)ほしいデータの範囲
       によって、別シートからデータを取得することができます。この方法でも勿論可能ですが、毎回手作業でシート名を入力するのは大変ですね。
      ここがIndirect関数の出番よ。あるセルにシート名を入力して、そのセルを参照させれば、名前変更後もそのセル名を変えるだけで済むの。

    3.   Indirect関数あり 〜 をIndirect関数で表すと? 〜

    4. Indirect関数なし  F4 = ‘2014年12月期’!C2:C13

      Indirect関数あり F4 = Indirect(“‘” &B5 & “‘!C2:C13”)
      「” “」があると文字列。 「” “」なしは真の意味で参照よ!
      上のB5は「” “」なしでしょう?これはB5セルの中身(=2014年12月期)を真の意味(=このシートの範囲を参照)で取得してほしいからなの。一方、「“‘!C2:C13”」なのは、これらに文字でいてほしいからなの。
      え?C2:C13は参照しないの?
      それは後。もしC2:C13を「” “」なしで、真の意味として参照してしまうと、このシートのC2:C13を参照するでしょう?それはダメ。今は文字列として扱うの。B5セルの中身(=2014年12月期)が別シートへ連れていってくれた後に、自動的に参照されるわ。
      &って?
      スプレッドシートで、文字列の結合は「&」によって行われるわ。例えば、「=Indirect(“B2”)」は「=Indirect(“B”&”2”)」でも同じことよ。
      うーん、なんかやっぱり難しいなぁ。
      でも、今回みたいにドロップダウンをつければ、セルの情報を簡単に変更出来るから、Indirect関数とは相性が抜群よ。少しづつ慣れていくしかないわね。