Excel業務カイゼンブログ

セルの参照を文字で指定できるINDIRECT関数

OFFSET関数と今回紹介するINDIRECT関数は、セルの範囲を指定する特殊な関数です。 OFFSET関数は、セルの範囲や参照を、列番号と行番号で指定します。対してINDIRECT関数は、「A1」や「B2:C3」のような文字で指定します。

実際に操作するファイルはこちらからダウンロードできます。

ダウンロードファイルは、このような形になっています。

INDIRECT関数の使い方

INDIRCT関数の書式は次の通りです。

=INDIRECT(参照先を示す文字列)

例えば、セルA1を指定するには、

=INDIRECT("A1")

です。直接セルを入力する場合は、参照先を示す文字列は" "で囲みます。

この計算式をセルB11に入力してみましょう。

セルA1の値「1」が表示されました。

今度は、A2からC3の範囲を指定するため、

=INDIRECT("A2:C3")

をセルB11に入力してみましょう。

今度は#VALUEエラーになりました。

INDIRECT関数で範囲を指定すると、答えは1つの値ではなく、範囲になるので、どの値求めればよいかわからなくなるため、エラーになります。

範囲として指定は、他の関数と組み合わせで範囲を指定するときに使います。

INDIRECT関数の範囲指定のしくみ

ダウンロードファイルのセルB8には、INDIRCT関数を使ってセルB7の文字列で指定した範囲の最大値をMAX関数で求める計算式が入力されています。

=MAX(INDIRECT(B7))

セルB9には、同様にMIN関数で最小値を求める計算式が入力されています。

=MIN(INDIRECT(B7))

セルB7にセルA1を示す「A1」と入力すると、最大値も最小値もセルA1しか見ていないので、「1」が求まります。

セルB7にA1からB5の範囲を示す「A1:B5」を入力すると、最大値はA1からB5までの範囲の最大値「15」、最小値は「1」が表示されます。

セルB7にB3からE5の範囲を示す「b3:e5」をわざと小文字で入力しても、最大値はB3からE5までの範囲の最大値「45」、最小値は「13」が表示されます。

全角半角小文字は関係なくなります。また、絶対参照で使用する$も無視されます。

セルや範囲を示す形であれば、指定できます。また名前機能で名前が設定されている場合は、その名前を指定すればその範囲で指定できます。

セル範囲を求める計算式と組み合わせたINDIRECT関数

INDIRECT関数の引数にセルの値を指定することができることがご理解いただけたと思います。

セル範囲を計算式で求めたものを引数に指定すると、セル範囲を変更することが容易になります。

例えば、VLOOKUP関数の第２引数である一覧表範囲の行数を他のセルの値で変えることができます。

VLOOKUP関数、

=VLOOKUP(C13,INDIRECT("A"&D13&":C"&D13+E13-1),3,0)

の式をセルB13に入力してみましょう。

計算式で参照しているセルD13やセルE13が未入力のため、エラーになっています。

セルD13は一覧表範囲の始まる行、セルE13には一覧表範囲の行数を指定しています。

一覧表範囲の終わりの行は、始まりの行に行数を足し、1を引くことで求まります。

A列を表す"A"の文字、C列を表す"C"の文字、セル範囲を表す":"の文字と、行数を表す数値を「&」記号で結合し、

"A"&D13&":C"&D13+E13-1

をINDIRECT関数の引数とすることで、一覧表範囲を他のセルの値で決定しています。

最終的に、セルC13の値を一覧表範囲から指定し、その3列目を求めるVLOOKUP関数になっています。

では、セルD13は一覧表範囲の始まる行に「1」、セルE13には一覧表範囲の行数に「3」、セルC13のVLOOKUP関数の検索値に「2」と入力してみましょう。

A1からC3の範囲を一覧表として「2」を検索し、その3列目の「22」を求めました。

そのまま、セルC13の検索値に「4」と入力してみましょう。

A1からC3の範囲を一覧表として「4」を検索しても「4」は一覧表の範囲外なので、検索できずエラーになります。

今度は、セルE13には一覧表範囲の行数に「4」と入力してみましょう。

一覧表の範囲がA1からC4に広がるので、「4」が検索できるので、その3列目の「24」を求めることができました。

このように、あるセルの値でINDIRECT関数のセル範囲を指定することができます。

列を数字で指定する

上記の例では、列は固定で文字として指定していますが、数値で指定することもできます。

MID関数を組み合わせた例では、

=VLOOKUP(検索値,INDIRECT(MID("ABCDEFGHIJKLMNOPQRSTUVWXYZ",一覧表の始まる列,1)&一覧表範囲の始まる行&":"&MID("ABCDEFGHIJKLMNOPQRSTUVWXYZ",一覧表の始まる列+一覧表範囲の列数-1,1)&一覧表範囲の始まる行+一覧表範囲の行数-1),3,0)

となります。

ただし、この場合は、AA列以降に対応できないことと、何より計算式が長くなるデメリットがあります。OFFSET関数を使用して

=VLOOKUP(検索値,OFFSET(A1,一覧表範囲の始まる行-1,一覧表の始まる列-1,一覧表範囲の行数,一覧表範囲の列数),3,0)

とした方がすっきりします。