INDIRECT関数の使い方:文字列を使ってセル参照を求める:Excel関数

INDIRECT関数の使い方:文字列を使ってセル参照を求める:Excel関数

INDIRECT関数は、文字列を使用して他のセルへの参照を求める関数です。

INDIRECT関数は、同じシートのセル範囲、または別シートのセル範囲を文字列で指定できます。INDIRECT関数を使う場合は、他の関数、例えばVLOOKUP関数などと組み合わせて使います。

INDIRECT関数で特定のセルを間接参照する

対応バージョン:Excel365 2019 2016 2013 2010

INDIRECT関数の書式です。

=INDIRECT(参照文字列,参照形式)
※カッコ内の「参照文字列,参照形式」を引数といいます。

  • 引数〔参照文字列〕: 参照したいセルの文字列を指定。
  • 引数〔参照形式〕: 〔参照文字列〕に指定したセル表記が「A1形式」なら「TRUE」を指定し、「R1C1形式」なら「FALSE」を指定。「A1形式」の「TRUE」を指定する場合は省略可能。

「R1C1形式」はVBA(マクロ)を使う場合に用いられる書き方で、「R2C3」のようにROW(行)とCOLUMN(列)を表す「それぞれの頭文字RとC」の後に行数と列数で位置を表します。「R2C3」なら2行目と3列目の交差する位置が求められる。

今回はVBAを使わないので、すべて「A1形式」で指定します。

INDIRECT関数の使い方

まず、実際のサンプルでINDIRECT関数のイメージを掴んでいきます。

INDIRECT関数の使い方

セルB2にINDIRECT関数が入力されています。

=INDIRECT(A2)

関数では、〔参照文字列〕に「A2」が指定されています。「A2」セルには「B8」が入力されていますので、この「B8」セルを参照し商品種類の「椅子B」が返されるという流れです。

A2セルの「B8」を他の文字列に変更すれば、変更後の〔参照文字列〕に応じた値が返されます。

ドロップダウンリスト(プルダウンリスト)で入力を簡単に

なお、今回のサンプルでは、〔参照文字列〕を簡単に変更できるように「A2」セルはドロップダウンリストにしています

「A2」セルを選択(クリック)すると、以下のようなドロップダウンリストが表示されます。

表示されたドロップダウンリストには、椅子とソファーの商品種類の名前が入力されたセル番号を指定しています。

「B7」なら「椅子A」…「B13」なら「ソファーA」という感じです。


ドロップダウンリスト

ドロップダウンリストを作成するには、「データ」タブから画像にある「データの入力規則」を選びます。

データの入力規則

次に、「データの入力規則」ダイアログの「入力値の種類(A)」から「リスト」を選び、「元の値(S)」にドロップダウンリストに表示したい値を入力します。表示する値が複数ある場合は「カンマ(,)」で区切って指定。

データの入力規則ダイアログ

これで、「A2」セルをクリックするとドロップダウンリストが表示されます。

INDIRECT関数とVLOOKUP関数を組み合わせる

INDIRECT関数とVLOOKUP関数を組み合わせて使う例をご紹介します。

VLOOKUP関数の基本的な使い方を再確認したい方は以下の記事を参照してください。

今回の例で行うこと。

商品コードを入力すると、商品名や単価、掛率などを、他のシートに保存されている単価表から取り出して表示する。さらに、取り出した単価や掛率と入力した数量を掛け算し金額を求める。

VLOOKUP関数の引数にINDIRECT関数を指定

以下のサンプルの「A5」セルにVLOOKUP関数を入力し、その引数にINDIRECT関数を指定しています。

=VLOOKUP($B$2,INDIRECT($A$2),2)

画像の上段は、入力エリアで「分類」「商品コード」「数量」を入力します。

画像の下段は、VLOOKUP関数とINDIRECT関数から返された値を表示しています。

VLOOKUP関数とINDIRECT関数を組み合わせる

なお、金額は「数量」「単価」「掛率」からPRODUCT関数で計算しています。

=PRODUCT(C2,B5:C5)

関数と引数の説明

VLOOKUP関数の第一引数

VLOOKUP関数の書式は、=VLOOKUP(検索値,検索範囲,列番号,検索方法)なので、VLOOKUP関数の最初の引数には「商品コード(検索値)」のB2セルを指定します。別シートの単価表を「商品コード」で検索するからです。(イメージは少し下にあります)

VLOOKUP関数の第二引数

第二引数には、INDIRECT関数をネストして指定します。

INDIRECT関数の最初の引数には、〔参照文字列〕を指定しますので、別シートの単価表で「範囲に名前を付けた」ときに指定した「名前」そのものを指定します。

その名前が入力されているのが「A2(分類)」セルなので、INDIRECT関数の第一引数に指定。第二引数は省略(セル参照形式が「A1形式」のため)。

単価表で「範囲に名前を付けた」ときの実際の例が以下の画像です。

範囲に名前を付ける

範囲に名前を付ける方法

選択した範囲に名前を付ける手順。

  1. 名前を付ける範囲を選択(例えば、B5からE7)
  2. 「名前ボックス」に任意の名前を入力(例えば、椅子やソファー)
  3. 最後に「Enter」キーを押せば完了

範囲に名前を付けておくことで、別シートの値を参照するときにシートの切り替えを意識することなく、VLOOKUP関数で目的の値を参照することができます。

VLOOKUP関数の第三引数

VLOOKUP関数の第三引数には、値を取り出したい「列番号」を指定するので、ここでは「商品名」が保存されている、検索範囲の左から2番目の意味の「2」を指定します。

以降、「単価」を取り出すのなら列番号は「3」、掛率を取り出す場合は「4」を指定することになります。

まとめ

INDIRECT関数は、ここで取り上げたVLOOKUP関数の他にも「SUM関数」や「MATCH関数」と組み合わせた便利な使い方もあります。

例えば「SUM関数」と組み合わせるなら、SUM関数の引数〔範囲〕にINDIRECT関数を指定することで、合計範囲を動的に切り替えることができます。

このように、INDIRECT関数は多目的な用途で使うことができますので、覚えて損のない関数です。

INDIRECT関数と同じ分類の関数

INDIRECT関数と同じ「検索/行列」系の関数は以下のページで探せます。

Scroll Up