同じ数式をコピーして、テーブル配列の行を 15 ずつ増やすことができるソリューションを見つけるのに苦労しています。最初の試みでは、パターンを捉えられることを期待して、適切な数式を 3 つ手動で作成し、その後に完全な手入力を試みたのですが、うまくいきませんでした。
次のような出力を探しています:
=MAX(VLOOKUP($A2,Sheet2!$A2:$B17,2,0),VLOOKUP($A2,Sheet2!$E2:$F17,2,0),VLOOKUP($A2,Sheet2!$I2:$J17,2,0))
=MAX(VLOOKUP($A2,Sheet2!$A17:$B32,2,0),VLOOKUP($A2,Sheet2!$E17:$F32,2,0),VLOOKUP($A2,Sheet2!$I17:$J32,2,0))
=MAX(VLOOKUP($A2,Sheet2!$A32:$B47,2,0),VLOOKUP($A2,Sheet2!$E32:$F47,2,0),VLOOKUP($A2,Sheet2!$I32:$J47,2,0))
私はかなり素人なので、どんなアドバイスでも大歓迎です。よろしくお願いします。
答え1
OFFSETこのオプションでは、ほとんどの人がこの関数に惹かれるでしょう。ただし、これは揮発性関数です。揮発性関数は、世界が終わるというわけではありません。単に、そのセルが揮発性数式に関係がない場合でも、シート上のセルが変更されるたびに再計算されることを意味します。通常の数式は、それらに影響を与える何かが変更された場合にのみ再計算されます。その結果、揮発性関数を使用すると、多くの余分な計算が発生する可能性があります。特に、それがコピーされて多くのセルで再利用される場合はそうです。
と同じ結果は、通常の数式 (非揮発性) である OFFSETを使用しても得られます。は、指定された行 (1D 範囲) と列 (2D 範囲) の値を返すものとよく考えられます。実際に返されるのはセル アドレスで、そのアドレスから値を取得します。 このアドレスの戻り値を使用すると、1 つの関数を使用して範囲の開始点を定義し、別の関数を使用して範囲の終了点を定義できます。 2 つの関数を : で区切ると、範囲が作成されます。INDEXINDEXINDEXINDEXINDEXINDEX
次に、行パターンを決定する計算を見てみましょう。数式 1 は行 2 から始まり、数式 2 は行 17 から始まり、数式 3 は行 32 から始まります。以下同様です。したがって、ここでのパターンは基本的に (数式 #-1)*15+2 です。したがって、数式がコピーされるにつれて 1 ずつ増加するカウンターを開発する必要があります。列に 1、2、3 などを入力するか、代わりに を使用することもできますROW(A1)。最初にどこかで使用されると、1 が返されます。コピーされるにつれて、2、3 が返されます。これは完璧な小さなカウンターです。(注: はColumn(A1)水平方向にカウントするために使用できます)。
前述のとおり、INDEX形式は次のようになります。
INDEX(SELECT RANGE, ROW in SELECT RANGE, COLUMN in SELECT RANGE)
いくつかの注意点:
行番号と列番号は選択範囲を基準としており、選択範囲が 2D 範囲の場合は A1 から、1D 範囲の場合は行 1 または列 A から始まっていない限り、ワークシートと同じにはなりません。
1D の選択範囲では列番号は不要で、行番号のみが必要です。選択範囲が水平の場合、行番号は実際には列番号になります。
行番号または列番号に 0 を入力すると、INDEX はそれを選択した範囲の先頭の行または列を返すものとみなします。
数式の構築に戻りましょう。範囲の開始点を見つけることから始めましょう。この場合、インデックスにA2、A17、A32などを見つけるように指示します。
=INDEX($A:$A,(ROW(A1)-1)*15+2)
範囲 B17、B32、B47 の終点を見つけるための式は次のようになります。
=INDEX($B:$B,ROW(A1)*15+2)
これら 2 つを組み合わせて範囲を定義すると、式は次のようになります。
=INDEX($A:$A,(ROW(A1)-1)*15+2):INDEX($B:$B,ROW(A1)*15+2)
1 つのセルに 1 つのセルの値以上を入力することはできないため、数式自体はそれほど意味がありません。ただし、ルックアップ数式には使用できます。範囲方程式を元の数式に代入すると、次のようになります。
=MAX(VLOOKUP($A2,INDEX(SHEET2!A:A,(ROW(A1)-1)*15+2):INDEX(SHEET2!B:B,ROW(A1)*15+2),2,0),VLOOKUP($A2,INDEX(SHEET2!E:E,(ROW(A1)-1)*15+2):INDEX(SHEET2!F:F,ROW(A1)*15+2),2,0),VLOOKUP($A2,INDEX(SHEET2!I:I,(ROW(A1)-1)*15+2):INDEX(SHEET2!J:J,ROW(A1)*15+2),2,0))