Office Hack

ExcelのINDIRECT関数の使い方|セルや他のシートを参照する

  • Release
  • Update

Excelの関数でもよく使われるINDIRECT関数は、セルの参照を文字列で指定する関数です。セルだけではなく、他のシートも参照することができます。

VLOOKUP関数やMATCH関数など他の関数と一緒に組み合わせて使用することが多いです。INDIRECT関数の基本的な使い方から他の関数との組み合わせまで幅広く説明しています。

INDIRECT関数とは?

INDIRECT(インダイレクト)関数は、指定される文字列への参照を返す関数です。

INDIRECT関数とは

上図ではC3セルにINDIRECT関数の数式が入力されています。「=INDIRECT(B3)」と数式が入っており、B3セルの文字列(E4)をセル参照して「バナナ」が表示されています。

INDIRECT関数を使用すると数式を変更しないで参照したいセルを変更することができます。

INDIRECT関数の書式

まずはINDIRECT関数の書式を確認しましょう。

INDIRECT関数の書式

書式は「=INDIRECT(参照文字列,[参照形式])」のように記述します。少なくとも1つの引数を指定します。

※引数(ひきすう)とは、Excelの関数を使用する際に必要な情報です。関数が結果を返すための判断材料とイメージしましょう。関数名の後の括弧「()」内に入力します。

第1引数(参照文字列)

INDIRECT関数の参照文字列

1番目の引数は「参照文字列」です。この引数は必須です。セル参照を表す文字列またはセル参照を指定します。

文字列として指定する場合は「"」(ダブルクォーテーション)で囲んで指定します。

第2引数(参照形式)

INDIRECT関数の参照形式

2番目の引数は「参照形式」です。省略が可能です。参照文字列で指定されたセル参照の種類を、論理値で指定します。

  • TRUEまたは省略:A1形式のセル参照
  • FALSE:R1C1形式のセル参照
A1形式とR1C1形式

Excelには「A1形式」と「R1C1形式」の2つの参照形式があります。

A1形式は「B2」のように英字で列を、数字で行を表します。通常のExcelで使用されています。

一方、R1C1形式は「R2C2」のようにROW(行)とCOLUMN(列)の頭文字にそれぞれ行数と列数で位置を表します。マクロ(VBA)でよく使用されます。

マクロ(VBA)を使わなければ「A1形式」なので第2引数である参照形式は省略できます。

INDIRECT関数を使って別のシート名を参照する

INDIRECT関数がよく使われる別シート内にあるセルを参照する例をご紹介します。実際に手順を1つずつ説明していきます。

作業時間:5分

  1. シートを準備する

    シートを準備する

    例では、4つのシートを用意しました。「りんご」「バナナ」「みかん」シートにはそれぞれC2セルに価格が入力されています。INDIRECT関数を使って「集計」シートのD列に各商品の価格を表示させてみましょう。

  2. 参照文字列を指定する

    INDIRECT関数の参照文字列

    INDIRECT関数の引数「参照文字列」として今回は各シートのC2セルを指定します。「集計」シートの【D3セル】を選択し、『=INDIRECT(C3&』と入力します。他のシートを参照する場合、「りんご!C2」のようにシート名の後に「!」を付けてセルを指定します。今回、「集計」シートのC列と他のシート名を同じにしているのでシート名を指定する際に参照します。

  3. 文字列結合によるシートの指定

    文字列結合によるシートの指定

    「=INDIRECT(C3&」に続いて『"!C2")』と入力し、【Enter】キーを押して数式を確定します。シート名とC2セルを「&」で文字列結合しています。

  4. オートフィルでコピーする

    オートフィル

    INDIRECT関数を使って「りんご」シートの価格が「集計」シートのD3セルに表示されました。他の行にも数式を反映させます。D3セルの右下をD5セルまでドラッグします。

  5. INDIRECT関数の結果

    INDIRECT関数の結果

    「りんご」「バナナ」「みかん」シートに入力されている価格が「集計」シートに反映することができました。INDIRECT関数を使って1つずつ参照せずオートフィルで一括でコピーできるので便利です。

INDIRECT関数と他の関数の組み合わせ

INDIRECT関数とVLOOKUP関数、MATCH関数、ADDRESS関数、SUM関数を組み合わせた例をご紹介します。

VLOOKUP関数との組み合わせ

VLOOKUP関数の中にINDIRECT関数を入れ子(ネスト)にする例をご紹介します。VLOOKUP関数の引数「検索範囲」にINDIRECT関数を使用することで動的に切り替える例です。

VLOOKUP関数の引数や基本的な使い方が心配な方は以下の記事をご確認ください。

VLOOKUPとの組み合わせ

上図のように「りんご」と「みかん」という2つのテーブルがあります。一番上の検索テーブル内の種類と品種を入力すると「りんご」と「みかん」の2つのテーブルから検索して価格を表示させるようにしましょう。

検索範囲が1つの場合はVLOOKUP関数だけできますが、今回は複数の範囲が切り替わります。

名前を定義

それぞれの範囲に名前を定義します。①B9セル~C11セルをドラッグして選択し、②名前に『りんご』と入力します。「みかん」の方にも同じように名前を付けます。

VLOOKUP関数の検索値

【D4セル】を選択し、『=VLOOKUP(C4,』と入力します。VLOOKUP関数の検索値は「品種」を指定します。

VLOOKUP関数の検索範囲

「=VLOOKUP(C4,」に続いて『INDIRECT(B4),』と入力します。VLOOKUP関数の検索範囲に「種類」を指定します。

範囲に名前を定義することでINDIRECT関数で範囲を指定することができます。

VLOOKUP関数の列番号

VLOOKUP関数の列番号と検索の型を指定します。「=VLOOKUP(C4,INDIRECT(B4),」に続いて『2,FALSE)』と入力し、Enterを押します。

検索結果

B4、C4セルに指定した項目を満たす検索結果をD4セルに表示させることができました。

切り替えた結果

検索値と検索範囲を変更するとそれに合わせて別のテーブルを参照して検索します。

MATCH関数との組み合わせ

INDIRECT関数にMATCH関数を入れ子(ネスト)にする例をご紹介します。INDIRECT関数の引数「参照文字列」にMATCH関数を使用することで参照セルを動的に切り替える例です。

MATCH関数の引数や基本的な使い方が心配な方は以下の記事をご確認ください。

表の準備

上図は下段に商品コードごとに商品名、単価が入力されています。上段のB3セルの商品コードに入力すると隣の検索結果に商品名を表示させてみましょう。

INDIRECT関数

INDIRECT関数の引数「参照文字列」を指定します。価格テーブルのC7、C8、C9セルのいずれかを指定したいので文字列として「C」を指定します。『=INDIRECT("C"&』と入力します。

MATCH関数

B3セルに入力された商品コードの位置をMATCH関数を使って求めます。「=INDIRECT("C"&」に続いて『MATCH(B3,B7:B9,0)+6)』と入力します。MATCH関数の検索値はB3セル、範囲は価格テーブルの商品コード列を指定しています。

MATCH関数の検索範囲は7行目から始まっていますので「6」を足してセルの位置を調整しています。

検索結果

C3セルにINDIRECT関数とMATCH関数を組み合わせて検索した結果が表示されました。では商品コードを変更すると検索結果がどうなるか確認してみましょう。

切り替わった結果

B3セルの商品コードを「1003」に変更すると検索結果が自動的に「みかん」に切り替わりました。

ADDRESS関数との組み合わせ

ADDRESS関数はシート内のセルの位置を文字列で返します。文字列であればINDIRECT関数の引数「参照文字列」に指定すればセルの値を求めることができます。

ADDRESS(アドレス)関数は、指定したセルの参照を文字列の形式で返します。書式は「=ADDRESS(行番号,列番号,[参照の種類],[参照形式],[シート名])」のように記述します。

行と列の位置を求める

上図は1から50までの数字が表になっています。行と列をI列に入力することで交差するセルの値を表示させてみましょう。

INDIRECT関数

H5セルを選択し、『=INDIRECT(』と入力します。

ADDRESS関数

「=INDIRECT(」に続いて『ADDRESS(I2,I3,1))』と入力します。INDIRECT関数の引数「参照文字列」にADDRESS関数を指定しています。

ADDRESS関数内では、行と列にI2、I3セルを指定することで文字列として「$B$8」が返ってきます。

求めた結果

H5セルにI2、I3セルで指定した行と列が交差するセル「$B$8」に入力されている「32」が表示されました。

SUM関数との組み合わせ

SUM関数の中にINDIRECT関数を入れ子(ネスト)にする例をご紹介します。SUM関数の引数「範囲」にINDIRECT関数を使用することで動的に切り替える例です。

表の準備

上図は日付ごとに商品と価格が入力されています。右側には行数を入力すると左側のテーブルの価格を指定の行数までの合計を求めるとします。

SUM関数の入力

G3セルを選択し、『=SUM(D3:』と入力します。SUM関数の合計したい範囲の最初はD3セルです。合計範囲の最後はG2セルに入力されている数字によって動的に切り替わるようにINDIRECT関数を使用します。

INDIRECT関数の入力

「=SUM(D3:」に続いて『INDIRECT("D" & G2))』と入力します。D列は固定なので文字列として「D」と指定し、G2セルの行数を文字列結合しています。

関数の入力結果

G3セルに8行目までの合計結果が表示されました。動的に切り替わるかどうか確認してみましょう。

切り替えた結果

G2セルを「5」にするとG3セルが自動的に切り替わりました。左側のテーブルの5行目までの価格が合計されています。

INDIRECT関数と入力規則を使った絞り込みリスト

決まった形式の値を入力したいとき、ドロップダウンリスト(またはプルダウンリスト)は便利です。

INDIRECT関数と組み合わせることで複数のリストを連動させる方法をご紹介しています。

おすすめの商品をご紹介

他の関数も合わせてチェック!

Office Hackでは、ここでご紹介できなかった関数の一覧ページもご用意しております。ぜひ、参考にしてください。

よろしければ参考にならなかった点をお聞かせください

CAPTCHA


Page Top