INDIRECT関数の使い方|文字列のセル番号やシート名で値を参照する

INDIRECT関数の使い方
Excel関数

Excel(エクセル)のINDIRECT関数の使い方|文字列のセル番号やシート名で値を参照する

使用関数:INDIRECT、VLOOKUP、ADDRESS、SUM

INDIRECT関数とは?

INDIRECTは、直接的という意味のdirect(ダイレクト)に否定の接頭辞in(イン)が付いて、「間接的な」とか「遠回しな」を意味します。インダイレクトと読みます。セル番号を文字列として指定し、間接的にその入力値を取得する関数です。

INDIRECT関数の引数と書式

「数式」→「関数ライブラリ」→「検索/行列」からINDIRECTを選択します。
数式ライブラリの検索/行列からINDIRECTを選択
書式の構成はこうなります。
INDIRECT関数の書式の構成

第一引数「参照文字列」

INDIRECT関数の引数は通常、第一引数の「参照文字列」だけです。
「参照文字列」には入力値を抽出したいセルのセル番地を文字列で指定します。または、セル番地を入力したセル参照で指定します。

B4」と入力されたセルを「参照文字列」に指定すると、
文字列として入力されたセル番号が入ったセルをINDIRECTに指定
B4セルの入力値「木星」が取得できます。直接、=INDIRECT(“B4”)としてもOKです。
数式を右へオートフィルコピーすると「A2」「a1」と入力されたセルが指定されて「太陽」「地球」が抽出できました。小文字でも全角が混じっていても、Excelがセル番地と判定できれば正解が返ります。
小文字・全角のセル番号が入ったセルをINDIRECTに指定

後述しますが、セル番号の他、別ブック、別シートに定義した名前と範囲を指定することもできます。

第二引数「参照形式」(R1C1形式のみ)

もう一つの引数「参照形式」はシートの参照形式がA1形式(列アルファベット・行数字)でなく、R1C1形式(行数字・列数字)になっている場合でのみ論理値の「FALSE」または「0」を指定します。
INDIRECTの「参照形式」に参照形式がR1C1形式の場合FALSEか0を指定
A1形式では「TRUE」か「1」を指定してもいいですが、普通は省略します。

参照形式(A1、R1C1)の詳しい解説記事です。

R1C1参照形式の表示方法と役立て方・A1との相違点
Excelで規定のA1参照形式をR1C1参照形式に切替える方法と、この2つの形式の相違点を詳しく解説します。R1C1形式では、数式の修正箇所が見える利点があります。

INDIRECT関数で別シートのデータを反映させる

別シートの表データをINDIRECT関数で抽出する場合の「参照文字列」の指定方法です。

INDIRECTの引数に&を使って別シート名とセルを指定する

別シートに個々のメニューの売上データがあり、それぞれシート名が付いています。四半期売上合計をまとめたシートに、別シートの各メニュー売上データを自動で抽出して表示させます。
INDIRECTの「参照文字列」に別シート名と範囲を指定して各メニュー売上合計を自動で抽出
INDIRECTで抽出したいデータがあるのは他の各シートの「E2」セルです。
別シートでINDIRECTを使って参照するのはE2セル

別シート名と特定のセルをINDIRECTの「参照文字列」に指定するときは「&」で各シートを連結させて同じ位置のセルを抽出する方法を採ります。

「四半期売上」シートの最初のメニューの合計を抽出するセルを選択して、INDIRECTのダイアログを開き、各シート名に対応するメニュー名のセルをクリックで入力し、続けて「&」を入力します。
INDIRECTの「参照文字列」に各シート名に対応するメニュー名のセル参照と「&」を入力
続けて文字列入力のための「」を入れ、「別シートを参照する」意味の「!」を入力。参照したいセル番地「E2」を入力し、「」で閉じます。「参照文字列」に「A3&”!E2″」と入り、これで「A3セルの「軽食」に対応する別シートのE2セルを参照する」という指定ができました。
INDIRECTの「参照文字列」に別シートのE2セルを参照するという指定

INDIRECTの引数に別シート名と範囲を指定する(スピル)

Excel2016まではINDIRECT関数の「参照文字列」にセル範囲を指定して取得できませんでした。
Excel2019~及び365でスピルが使える環境では、別シートから複数の連続したセルのデータを一気に反映させられるようになっています。
INDIRECT(スピル)で別シートから複数の連続したセルのデータを一気に反映
基本的には上記のセル参照と同じです。単独のセル番地をセル範囲(B2:E2)にするだけです。最初のメニューの行データが取得できたら、数式を下へオートフィルコピーします。
INDIRECTで別シート名から複数の連続したセル範囲のデータを反映させる

INDIRECT関数の使い方(他の関数と組み合わせる)

INDIRECTは他の関数と組み合わせることが多い関数です。以下に幾つかの使用例を挙げてみます。

INDIRECT関数とADDRESS関数との組み合わせでデータを抽出

ADDRESS関数は指定された行番号と列番号から文字列としてのセル番地を作ります。例えば、「行番号」に2、「列番号」に1を指定すると「$A$2」と絶対参照のセル番地が文字列で返ります。

下図の例では1~5までの数字を2つ選んで、そこからINDIRECT関数とADDRESS関数で恋の行く末を表示させます。
入力され2つの数値からINDIRECT関数とADDRESS関数で恋の行く末を表示
まず、ADDRESS関数の「行番号」に1つ目の選択数字のセル番号、「列番号」に2つ目の選択数字のセル番号を指定します。
ADDRESS関数の「行番号」と「列番号」に2つの選択数字のセル番号を指定

このADDRESS関数の数式「ADDRESS(B8,B9)」をINDIRECT関数の引数に入れ子します。
ADDRESS関数をINDIRECT関数の引数に入れ子する
INDIRECT関数とADDRESS関数と組み合わせて、恋の行く末が表示されました。数字を入れ替えれば答えも変わります。
INDIRECT関数とADDRESS関数との組み合わせた結果

INDIRECT関数とSUM関数との組み合わせで別シートのデータを合計

SUM関数にINDIRECTを入れ子して、別シートの列データの合計を表示させます。
別シートのそれぞれの列データ範囲ごとに列見出しを名前として定義しておき、これを利用して数式をコンパクトにまとめます。
別シートのそれぞれの列データ範囲ごとに列見出しを名前として定義
合計を表示させるセルを選択して、SUM関数の引数にINDIRECT関数を入れ子します。
INDIRECT関数の引数に別シートの合計したい列に定義した名前と同じ文字列が入ったセルをクリックで指定します。
INDIRECT関数の参照文字列に別シートの合計したい列に定義した名前と同じ文字列が入ったセルをクリックで指定
SUM関数の引数はこうなります。
SUM関数の引数に入れ子したINDIRECT関数
別シートの項目の売上合計が取得できました。他の項目までオートフィルコピーすれば完了です。
別シートの項目の売上合計がSUM関数とINDIRECT関数で取得された

INDIRECT関数とVLOOKUP関数との組み合わせで複数の表からデータを抽出

VLOOKUP関数にINDIRECTを入れ子して、複数の表からデータを取り出します。
各表のデータ範囲には左上セルの「A組」「B組」「C組」の名前がそれぞれ定義されています。
各表のデータ範囲に「A組」「B組」「C組」の名前がそれぞれ定義
A組の「第5競技者」の氏名を表示させるセルを選択して、VLOOKUP関数を組み立てます。
最初の引数「検索値」には「第5競技者」と入ったセルをクリックで指定し、F4キーで絶対参照にしておきます。
VLOOKUPの検索値に「第5競技者」と入ったセルを指定、絶対参照にしておく
第二引数の「範囲」にはINDIRECT関数を入れ子します。
INDIRECTの「参照文字列」に「A組」と入ったセルをクリックで指定します。この例では「A10」セルです。
INDIRECTの「参照文字列」に「A組」と入ったセルをクリックで指定
第三引数「列番号」には表の氏名の列が左から2番目なので「2」、第四引数「検索方法」には完全一致の「FALSE」を指定します。
VLOOKUPの第三引数「列番号」に2、第四引数「検索方法」にFALSEと指定
これでVLOOKUPにINDIRECTをネストした数式を確定させればA組の「第5競技者」の氏名が表示されます。他の組にもオートフィルコピーをして完了です。
VLOOKUPにINDIRECTをネストした数式をオートフィルコピー
「第5競技者」を「第3競技者」に入れ換えれば自動で氏名が切り替わります。
「第5競技者」を「第3競技者」に入れ換えて氏名を自動切り替え

VLOOKUP関数の使い方をもっと分かりやすく、詳細にまとめています。

VLOOKUP(ブイルックアップ)関数の使い方
VLOOKUP関数の基本的な使い方を初心者にも分かりやすく解説。VLOOKUP関数で使う表の作成、別シートでの管理、VLOOKUP関数の引数「検索値・範囲・列番号・検索方法」の指定、エラーへの対処法、複数の条件の指定方法、VLOOKUP関数の応用例など

INDIRECT関数と入力規則の組み合わせで連動するリストを作成する

入力規則で手軽に実装できるプルダウンリスト(ドロップダウンリスト)の拡張版です。

連動する複数のプルダウンリスト(ドロップダウンリスト)の作成方法を初心者にも分かりやすい使用例で解説しています。

Excel(エクセル)で複数のプルダウン(ドロップダウン)リストを連動させる
3階層のプルダウン(ドロップダウン)リストを連動させる方法です。データの入力規則を応用して、最初のプルダウンリストで選択された値に連動して次のプルダウンリストを表示させ、そこで選ばれた値に連動して3段階目のプルダウンリストを表示させます

INDIRECT関数の動作が重い?

INDIRECT関数は便利な関数ですが、使い方によっては動作が重くなります。
INDIRECTは揮発性関数という少し特殊な性質を持っており、シート内で何か書き換えたり、ファイルを開く度に再計算が行われます。INDIRECTが組み込まれたシートのデータ量が多いと重いと感じることがあるのは、そのためです。
便利だからといってINDIRECT関数を無闇に多用せず、適度に使用することをお勧めします。

INDIRECT関数のまとめ
  • INDIRECT関数は文字列のセル番号やシート名で値を間接的に参照する関数です
  • INDIRECT関数の引数は通常のA1参照形式の元で使うなら「参照文字列」一つだけです
  • 「参照文字列」には入力値を抽出したいセルのセル番地を文字列で指定します。または、セル番地を入力したセル参照で指定します
  • INDIRECTで別シートのデータを反映させる場合は、「参照文字列」に&を使って別シート名とセルを指定します。別ブック、別シート、列、範囲に定義した名前を指定することもできます
  • 第二引数「参照形式」にはシートの参照形式がR1C1形式(行数字・列数字)になっている場合でのみ論理値のFALSEを指定します
  • INDIRECTは他の関数と組み合わせて使うことの多い関数です。VLOOKUP関数やADDRESS関数と組み合わせてデータを抽出したり、SUM関数と組み合わせて別シートのデータを合計したりするのに使われます
  • INDIRECT関数と入力規則の組み合わせで連動するプルダウン(ドロップダウン)リストを作成できます
  • INDIRECT関数は便利な関数ですが、使い方によっては動作が重くなります。データ量が多いファイルでは多用は避けましょう
タイトルとURLをコピーしました