次のような数式を入力したい行があります
=SUMPRODUCT(B93:B96,B46:B49)/SUM(B46:B49)
=SUMPRODUCT(C92:C95,C45:C48)/SUM(C45:C48)
=SUMPRODUCT(D91:D94,D44:D47)/SUM(D44:D47)
しかし、数式をドラッグすると、インデックスのアルファベット部分のみが変更されます。Excel で配列インデックスを正しく変更する方法はありますか?
答え1
私の理解では、数式が入力されたセルがあり=SUMPRODUCT(B93:B96,B46:B49)/SUM(B46:B49)、このセルに数式を入力して、同じ行の次の列のセルに数式が入力されるようにしたいという=SUMPRODUCT(C92:C95,C45:C48)/SUM(C45:C48)ことです。つまり、通常どおり列を 1 増やし、行を 1 減らしたいということです。
これは、OFFSET関数を使用して実現できます。これにより、既知の範囲から指定された距離の範囲を構築できます。COLUMN関数を使用して、必要なオフセットの量を指定できます。
必要な範囲はB93:B96と でB46:B49、最初の列以降の各列に対して -1 行と +1 列ずつオフセットされます。したがって、OFFSET(B93:B96, -1, 1)は への参照を返しますC92:C95。ただし、参照によって列が自然に更新されるため、列のオフセットは省略できます。
元の数式にある範囲 の場合B93:B96、最初のセルではOFFSET(B93:B96, 0, 0)、2 番目のセルではOFFSET(C93:C96, -1, 0)、3 番目のセルではOFFSET(D93:D96, -2, 0)などが必要です。 を使用して、現在の列の番号である結果を取得できます(参照が 2 番目の列で B1 に更新されるため)。これらを組み合わせると、 参照を変更することで簡略化できる を使用できます。最初=COLUMN(A1)の セルでは、これはになります。2 番目のセルでは、数式は になります。
OFFSET(B93:B96, 1 - COLUMN(A1), 0)
OFFSET(B94:B97, -COLUMN(A1), 0)OFFSET(B94:B97, -1, 0)B93:B96OFFSET(C94:C97, -COLUMN(B1), 0)C92:C95
必要な2つの参照は と ですOFFSET(B94:B97, -COLUMN(A1), 0)。OFFSET(B47:B50, -COLUMN(A1), 0)したがって、式は次のようになります。
=SUMPRODUCT(OFFSET(B94:B97, -COLUMN(A1), 0), OFFSET(B47:B50, -COLUMN(A1), 0)) / SUM(OFFSET(B47:B50, -COLUMN(A1), 0))
最後にもう一つ複雑な点があります。Excel 2007でこれをテストしていたところ、数式の中にSUMPRODUCT、COLUMN(A1)配列単なる値 1 ではなく、値 1 が含まれます。(これは、[数式の評価] ダイアログ ボックスで確認できます。評価すると、COLUMN(A1)結果は{1}単なる ではなく になり1、SUMPRODUCT式は 2 つの#VALUE!引数を取得し、最終的に 0 になります。) バージョンによっては、これが発生しない場合があります。発生する場合は、次のように を で囲みCOLUMN(A1)ますSUM()。
=SUMPRODUCT(OFFSET(B94:B97, -SUM(COLUMN(A1)), 0), OFFSET(B47:B50, -SUM(COLUMN(A1)), 0)) / SUM(OFFSET(B47:B50, -COLUMN(A1), 0))
COLUMN(A1)最終部分を ; で囲んでいないことにお気づきでしょう。SUM()これは、この関数が通常どおり数値を返し、配列参照を返さないためです。この関数はSUM配列参照を受け入れないため、Excel はこの場合 から配列を取得する必要がないことを認識したと考えられますCOLUMN。