Microsoft Excelにはすばらしいトリックがあり、SUMと間接的なダイナミックレンジの式を使用すると、データを簡単に操作できる2つの方法に過ぎません。
SUM - 間接式の概要
Excelの数式でINDIRECT関数を使用すると、数式自体を編集することなく、数式で使用されるセル参照の範囲を簡単に変更できます。
INDIRECTは、 OFFSET関数やSUM関数などの引数としてセル参照を受け入れる多数の関数で使用できます。
後者の場合、SUM関数の引数としてINDIRECTを使用すると、SUM関数が合計するセル参照のダイナミックレンジを作成できます。
間接的に中間の場所を介してセル内のデータを参照することによりこれを行います。
例：動的範囲の値を合計するために使用されるSUM - INDIRECT式
この例は、上記の画像に示されているデータに基づいています。
以下のチュートリアル手順を使用して作成されたSUM - INDIRECT 式は、次のとおりです。
= SUM（間接（ "D"＆E1＆ "：D"＆E2））
この式では、入れ子になったINDIRECT関数の引数には、セルE1およびE2への参照が含まれています。 これらのセルの番号1と4は、残りのINDIRECTの引数と組み合わされて、セル参照D1とD4を形成します。
その結果、 SUM関数によって合計される数値の範囲は、セルD1〜D4の範囲に含まれるデータ（50）になります。
セルE1とE2にある数字を変更することにより、 しかし、合計する範囲を容易に変更することができる。
この例では、最初に上記の式を使用してセルD1：D4のデータを合計し、セルF1の式を編集せずに合計範囲をD3：D6に変更します。
01/03
数式の入力 - オプション
数式を入力するオプションは次のとおりです。
- 上記の式をセルF1に直接入力し、キーボードのEnterキーを押します
- SUM関数のダイアログボックスを使用してINDIRECT関数を引数として入力する
Excelのほとんどの関数にはダイアログボックスがあり、 構文の心配なしに関数の引数を別々の行に入力することができます 。
この場合、SUM関数のダイアログボックスを使用して、数式をある程度単純化することができます。 INDIRECT関数がSUM内でネストされているため、INDIRECT関数とその引数は引き続き手動で入力する必要があります。
以下の手順では、[SUM]ダイアログボックスを使用して数式を入力します。
チュートリアルデータの入力
セル データ D1 - 5 D2 - 10 D3 - 15 D4 - 20 D5 - 25 D6 - 30 E1 - 1 E2 - 4- セルD1〜E2に以下のデータを入力します。
SUM - 間接式の開始 - SUM関数ダイアログボックスを開く
- セルF1をクリックします - ここにこの例の結果が表示されます
- リボンメニューの[ 数式 ]タブをクリックします
- リボンからMath＆Trigを選択して関数ドロップダウンリストを開きます
- リスト内のSUMをクリックして、関数のダイアログボックスを開きます
02/03
INDIRECT機能への入力 - クリックすると大きな画像が表示されます
INDIRECT式は、SUM関数の引数として入力する必要があります。
ネストされた関数の場合、Excelは引数を入力するために2番目の関数のダイアログボックスを開くことを許可していません。
したがって、 SUMIR関数のダイアログボックスのNumber1行にINDIRECT関数を手動で入力する必要があります。
- ダイアログボックスで、 Number1行をクリックします
- 間接（ "D"＆E1＆ "：D"＆E2）次の間接機能を入力します。
- OKをクリックして機能を完了し、ダイアログボックスを閉じます。
- 番号D1はセルD1に表示されます。これはセルD1〜D4にあるデータの合計であるためです
- セルF1をクリックすると、 ワークシート上の数式バーに完全な数式= SUM（INDIRECT（ "D"＆E1＆ "：D"＆E2））が表示されます
間接的機能を破る
INDIRECTを使用して列Dにダイナミックレンジを作成するには、INDIRECT関数の引数にある文字DとセルE1およびE2に含まれる数値を組み合わせる必要があります。
これは、以下によって達成されます。
- アンパサンド（ ＆ ）は、テキストデータ（この場合は文字D）をセル参照（E1およびE2）と連結または結合するために使用されます。
- さらに、セル参照と連結されているテキストデータは、二重引用符（ "" ）で囲む必要があります。
- 最後に、範囲の終点はコロン（:)で区切られます。
したがって、範囲の開始点は文字"D"＆E1によって定義されます。
2番目の文字セット： "：D"＆E2はコロンと終点を結合します。 これは、コロンがテキスト文字であるため、引用符で囲む必要があるために行われます。
中央の3番目のアンパサンドは、2つの部分を1つの引数に連結するために使用されます 。
"D"＆E1 ＆ "：D"＆E203/03
SUM関数の範囲を動的に変更する
この式の全体のポイントは、関数の引数を編集することなく、SUM関数によって合計された範囲を簡単に変更できるようにすることです。
数式にINDIRECT関数を含めると、セルE1とE2の数値を変更すると、SUM関数によって読み取られるセルの範囲が変更されます。
上の図に見られるように、これはまた、新しい範囲のデータを集計するときに、セルF1にある式の答えが変化する結果となります。
- セルE1をクリック
- 番号3をタイプしてください
- キーボードのEnterキーを押します。
- セルE2をクリックします
- 番号6を入力してください
- キーボードのEnterキーを押します。
- セルF1の答えは、セルD3〜D6に含まれる数値の合計である90に変更する必要があります
- セルB1とB2の内容を1と6の間の任意の数に変更して、式をさらにテストします
間接と#REF！ エラー値
#REF！ INDIRECT関数の引数の場合、エラー値がセルF1に表示されます。
- 有効なセル参照ではありません
- 別のブックへの外部参照が含まれていて、そのブックが開いていない
- ワークシートの限界（行1,048,576または列XFD）の外側のセル範囲を指します。