エクセルの間接関数の使い方は|セルやその他のシートを参照します
間接関数とは何ですか?
INDIRECT 関数は、指定された文字列への参照を返します。
上の図では、INDIRECT 関数の数式が C3 セルに入力されています。"=INDIRECT(B3)" という数式が含まれており、B3 セルのセル文字列 (E4) を参照する “Banana" が表示されます。
INDIRECT 関数を使用すると、数式を変更せずに参照するセルを変更できます。
間接関数形式
まずはINDIRECT 関数のフォーマットを確認してみましょう。
形式は “=INDIRECT(参照文字列、[参照形式]) 引数を少なくとも 1 つ指定します。
※引数(ひきす)は、Excelの関数を利用する際に必要な情報です。結果を返すための意思決定材料としての関数を想像してみましょう。関数名の後に括弧 “() を付けて入力します。
最初の引数 (参照文字列)
最初の引数は “参照文字列" です。この引数は必須です。セル参照を表す文字列またはセル参照。
文字列として指定した場合は、"" (二重引用符) で囲まれます。
第 2 引数 (参照形式)
2 番目の引数は “参照形式" です。省略できます。参照文字列で指定されたセル参照型の論理値。
- TRUE または省略: A1 形式のセル参照
- FALSE: R1C1 形式のセル参照
Excel には、"A1 形式" と “R1C1 形式" の 2 つの参照形式があります。
A1 形式は、"B2″ などの英字で列を表し、行を数値で表します。通常のエクセルで使用されます。
一方、R1C1形式は、「R2C2」のように、それぞれROW(行)とCOOLPN(列)のイニシャルにおける行数と列数で位置を表します。マクロ (VBA) でよく使用されます。
マクロ (VBA) を使用しない場合、2 番目の引数である参照形式は “A1 形式" であるため省略できます。
シートを準備する
例では、4枚用意している。「アップル」、「バナナ」、「マンダリンオレンジ」のシートには、それぞれC2セルで価格が記載されています。INDIRECT 関数を使用して、各製品の価格を “集計" シートの列 D に表示してみましょう。
参照文字列の指定
今回は、各シートのC2セルをINDIRECT 関数の引数「参照文字列」として指定します。選ぶ[D3 cell]「集計」シートに「=INDIRECT(C3+)」と入力します。他のシートを参考にする場合は、「りんご」をクリックしてください。シート名の後に C2″ と “!" を付けてセルを指定します。今回は、「集計」シートの列Cと他のシート名が同じであるため、シート名を指定する際に参照されます。
文字列の結合によるシートの指定
“=INDIRECT(C3&)" の後に “"!C2″) を押し、[Enter]をクリックして数式を確認します。シート名と C2 セルは、"&" の文字列として結合されます。
オートフィル付きのコピー
INDIRECT 関数を使用して、"リンゴ" シートの価格が “集計" シートの D3 セルに表示されました。数式を他の行にも反映します。D3 セルの右下隅を D5 セルにドラッグします。
間接関数の結果
「アップル」、「バナナ」、「マンダリンオレンジ」シートに入力された価格は、「集計」シートに反映される可能性があります。INDIRECT 関数を使用して 1 つずつ参照せずに、オートフィルで一括でコピーできると便利です。
VLOOKUP 関数との組み合わせ
Vlookup 関数に INDIRECT 関数をネストする例を紹介します。これは、Vlookup 関数の引数 “検索範囲" として INDIRECT 関数を使用して動的に切り替える例です。
Vlookup関数の引数や基本的な使い方が気になる方は、以下の記事をご覧ください。
上の図に示すように、「リンゴ」と「みかん」の2つのテーブルがあります。一番上の検索表に種類や品種を入力すると、「りんご」と「みかん」の2つの表から検索して価格を表示しましょう。
検索範囲が1つの場合、VLOOKUP機能しか作成できませんが、今回は複数の範囲が切り替わります。
ea の名前を定義するch 範囲。(1)B9セルをC11セルにドラッグして選択し、(2)名前に「Apple」と入力します。同じ方法で「みかん」に名前を付けます。
選ぶ[D4 cell]と入力すると、=Vlookup (C4,") と入力します。Vlookup 関数の検索値は “breed" を指定します。
=Vlookup(C4,"]と入力してください。
その後に間接(B4)が続きます。VLOOKUP関数の検索範囲に「タイプ」を指定します。
INDIRECT 関数で範囲を指定するには、範囲内に名前を定義します。
Vlookup 関数の列番号と検索タイプを指定します。"=Vlookup (C4, INDIRECT(B4)" と入力し、その後に “2,FALSE" と入力します。入る押す。
D4セルのB4セルとC4セルで指定した項目を満たす検索結果を表示することができました。
検索値と範囲を変更すると、それに応じて別のテーブルを参照して検索します。
マッチ機能との組み合わせ
間接関数に MATCH 関数をネストする例を見てみましょう。これは、INDIRECT 関数の引数 “参照文字列" として MATCH 関数を使用して、参照セルを動的に切り替える例です。
match関数の引数や基本的な使い方が気になる方は、以下の記事をご覧ください。
上の図では、下段の製品コードごとに製品名と単価が入力されています。上段にB3セルの商品コードを入力し、隣の検索結果に商品名を表示させましょう。
間接関数の引数 “参照文字列" を指定します。価格表の C7、C8、または C9 セルを指定したいので、文字列として “C" を指定します。=INDIRECT(“C"&" と入力します。
MATCH 関数を使用して、B3 セルに入力された統計品目コードの位置を検索します。"=INDIRECT(C"&" と入力し、その後に MATCH (B3, B7:B9,0)+6 と入力します。一致関数の検索値は B3 セルで、範囲は価格テーブルの統計品目コード列を指定します。
MATCH関数の検索範囲は7行目から始まるため、セルの位置は「6」を追加して調整します。
C3セルの間接関数とMATCH関数を組み合わせて検索した結果が表示されました。製品コードを変更すると検索結果がどうなるかを見てみましょう。
B3セルの商品コードを「1003」に変更すると、検索結果は自動的に「みかん」に切り替わりました。
アドレス関数との組み合わせ
ADDRESS 関数は、シート内のセルの位置を文字列として返します。文字列の場合は、INDIRECT 関数の引数 “参照文字列" としてセルを指定することで、セルの値を指定できます。
ADDRESS 関数は、指定されたセルへの参照を文字列の形式で返します。形式は “=ADDRESS (行番号、列番号、[参照の種類],[参照形式],[シート名])
上の図は、1 から 50 までの数値を示しています。列Iに行と列を入力して、交差するセルの値を表示しましょう。
H5 セルを選択し、=INDIRECT() と入力します。
“=INDIRECT() の後に ADDRESS(I2,I3,1) と入力し、ADDRESS 関数を INDIRECT 関数の “参照文字列" 引数として指定します。
ADDRESS 関数内では、行と列に I2 セルと I3 セルを指定することで、"$B$8″ が文字列として返されます。
H5 セルは、I3 セルで指定された行と列が交差するセル “$B$8" に “32" と入力されました。
SUM機能との組み合わせ
SUM 関数に INDIRECT 関数を入れ子にする例を見てみましょう。これは、SUM 関数の引数 “range" として INDIRECT 関数を使用して動的に切り替える例です。
上の図では、日付ごとに製品と価格が入力されています。右側に行数を入力し、左側のテーブルの価格は指定された行数までの合計です。
G3 セルを選択し、「=SUM(D3:」と入力します。sum 関数の合計範囲の最初のセルは D3 セルです。DIRECT 関数を使用して、合計範囲の終わりが G2 セルに入力された数値によって動的に切り替わるようにします。
タイプ “=SUM(D3:)"その後に間接(“D"&G2)"が続きます。nd列Dは固定されているので、文字列として “D"を指定し、G2セルの行数を文字列として結合します。
G3セルは、ライン8までの合計結果を示した。動的に切り替わるかどうか見てみましょう。
G2セルが「5」に設定されている場合、G3セルは自動的に切り替わります。左側の表の 5 行目までの価格が合計されます。