内容
おめでとう！ マラソンの最終日にたどり着きました 30 日間で 30 個の Excel 関数. Excel の関数について多くの有用なことを学んだ、長くて興味深い旅でした。
マラソン30日目は関数の勉強に専念します 間接 (INDIRECT)、テキスト文字列で指定されたリンクを返します。 この機能を使用すると、従属ドロップダウン リストを作成できます。 たとえば、ドロップダウン リストから国を選択すると、都市のドロップダウン リストに表示されるオプションが決まります。
それでは、関数の理論的な部分を詳しく見てみましょう 間接 (間接) とそのアプリケーションの実際の例を探ります。 追加情報や例がある場合は、コメントで共有してください。
機能 30: 間接
演算 間接 (INDIRECT) は、テキスト文字列で指定されたリンクを返します。
INDIRECT 関数はどのように使用できますか?
機能以来 間接 (間接) テキスト文字列で指定されたリンクを返します。次の目的で使用できます。
- 非シフト初期リンクを作成します。
- 静的な名前付き範囲への参照を作成します。
- シート、行、および列の情報を使用してリンクを作成します。
- 数値の非シフト配列を作成します。
構文 間接 (間接)
演算 間接 (間接) の構文は次のとおりです。
INDIRECT(ref_text,a1)
ДВССЫЛ(ссылка_на_ячейку;a1)
- ref_text (link_to_cell) はリンクのテキストです。
- a1 – TRUE (TRUE) に等しいか、指定されていない場合、リンクのスタイルが使用されます A1; FALSE (FALSE) の場合、スタイル R1C1.
トラップ INDIRECT (INDIRECT)
- 演算 間接 (INDIRECT) は、Excel ワークシートの値が変更されるたびに再計算されます。 関数が多くの数式で使用されている場合、これによりワークブックの速度が大幅に低下する可能性があります。
- 関数が 間接 (間接) 別の Excel ワークブックへのリンクを作成します。そのワークブックが開いている必要があります。開いていない場合、式はエラーを報告します。 #REF！ （＃リンク！）。
- 関数が 間接 (INDIRECT) 行と列の制限を超える範囲を参照すると、数式はエラーを報告します #REF！ （＃リンク！）。
- 演算 間接 (INDIRECT) は動的な名前付き範囲を参照できません。
例 1: 非シフト初期リンクを作成する
最初の例では、列 C と E に同じ数値が含まれており、それらの合計は関数を使用して計算されます。 和 (SUM)も同様です。 ただし、式は少し異なります。 セル C8 の式は次のとおりです。
=SUM(C2:C7)
=СУММ(C2:C7)
セル E8 の関数 間接 (INDIRECT) は、開始セル E2 へのリンクを作成します。
=SUM(INDIRECT("E2"):E7)
=СУММ(ДВССЫЛ("E2"):E7)
シートの上部に行を挿入して XNUMX 月 (Jan) の値を追加すると、C 列の金額は変化しません。 行の追加に反応して式が変化します。
=SUM(C3:C8)
=СУММ(C3:C8)
ただし、機能 間接 (INDIRECT) は E2 を開始セルとして固定するため、列 E の合計の計算に XNUMX 月が自動的に含まれます。 終了セルは変更されていますが、開始セルは影響を受けていません。
=SUM(INDIRECT("E2"):E8)
=СУММ(ДВССЫЛ("E2"):E8)
例 2: 静的な名前付き範囲へのリンク
演算 間接 (INDIRECT) は、名前付き範囲への参照を作成できます。 この例では、青色のセルが範囲を構成しています 番号リスト. さらに、列Bの値からダイナミックレンジも作成されます NumListDyn、この列の数字の数に応じて。
両方の範囲の合計は、その名前を引数として関数に与えるだけで計算できます 和 (SUM)、セル E3 と E4 で確認できます。
=SUM(NumList) или =СУММ(NumList)
=SUM(NumListDyn) или =СУММ(NumListDyn)
関数に範囲名を入力する代わりに 和 (SUM)、ワークシートのいずれかのセルに書かれた名前を参照できます。 たとえば、名前が 番号リスト がセル D7 に書き込まれている場合、セル E7 の数式は次のようになります。
=SUM(INDIRECT(D7))
=СУММ(ДВССЫЛ(D7))
残念ながら機能 間接 (間接) はダイナミック レンジ参照を作成できないため、この数式をセル E8 にコピーすると、エラーが発生します。 #REF！ （＃リンク！）。
例 3: シート、行、および列の情報を使用してリンクを作成する
行番号と列番号に基づいてリンクを簡単に作成でき、関数の XNUMX 番目の引数に値 FALSE (FALSE) を使用できます。 間接 (間接)。 これがスタイルリンクの作成方法です R1C1. この例では、シート名をリンクに追加しました – 'MyLinks'!R2C2
=INDIRECT("'"&B3&"'!R"&C3&"C"&D3,FALSE)
=ДВССЫЛ("'"&B3&"'!R"&C3&"C"&D3;ЛОЖЬ)
例 4: 数値の非シフト配列を作成する
Excel の数式で数値の配列を使用する必要がある場合があります。 次の例では、列 B の最大 3 つの数値を平均します。セル D4 で行われるように、数値を数式に入力できます。
=AVERAGE(LARGE(B1:B8,{1,2,3}))
=СРЗНАЧ(НАИБОЛЬШИЙ(B1:B8;{1;2;3}))
より大きな配列が必要な場合、数式にすべての数値を入力する必要はほとんどありません。 XNUMX番目のオプションは、関数を使用することです 行 (ROW)、セル D5 に入力された配列数式で行われるように:
=AVERAGE(LARGE(B1:B8,ROW(1:3)))
=СРЗНАЧ(НАИБОЛЬШИЙ(B1:B8;СТРОКА(1:3)))
XNUMX番目のオプションは、関数を使用することです 行 (STRING) とともに 間接 (間接)、セル D6 の配列数式で行われたように:
=AVERAGE(LARGE(B1:B8,ROW(INDIRECT("1:3"))))
=СРЗНАЧ(НАИБОЛЬШИЙ(B1:B8;СТРОКА(ДВССЫЛ("1:3"))))
3 つの式すべての結果は同じになります。
ただし、行がシートの上部に挿入された場合、行の移動に伴って数式内の参照が変化するため、3 番目の数式は正しくない結果を返します。 これで、最大 4 つの数値の平均ではなく、5 番目、XNUMX 番目、XNUMX 番目に大きい数値の平均が返されます。
関数の使用 間接 (INDIRECT)、XNUMX 番目の数式は正しい行参照を保持し、正しい結果を表示し続けます。