ルックアップテーブルで列を選択し、最も近い値を検索します

Question

VLOOKUP値を初め範囲の列を検索し、一致した行の範囲の指定された列から値を取得します。したがって、最初の数式は

=VLOOKUP(C46,'Battery Lookup Table'!B4:O36,MATCH(C39,'Battery Lookup Table'!B2:O2,0),TRUE)

C46範囲内の値と一致しています (例では 500.8 だと思います) 。'Battery Lookup Table'!B4:B36これは一致させたい範囲ではありませんが、間違った結果が得られる理由を説明している可能性があります。

実際、関数と照合する正しい列はすでに特定されていますMATCH。INDEX関数を使用して範囲の一部を選択できます。式

INDEX('Battery Lookup Table'!B4:O36,,n)

提供するん'番目の列な'Battery Lookup Table'!B4:O36ので、MATCH関数をん比較できる正しい値の列を提供しますC46。これにより、次の式が生成されます。

INDEX('Battery Lookup Table'!B4:O36,,MATCH(C39,'Battery Lookup Table'!B2:O2,0))

わかりやすくするために、この表現を次のように略します。リスト。

ここで、次の 2 つのことを行います。

最小値を求めるリストより大きいか等しいC46
この値に対応するバッテリーを見つけます。

ここでの秘訣は、VLOOKUP()より柔軟な「いとこ」を使用することですINDEX(MATCH())。はVLOOKUP範囲の最初の列の値と一致し、右側の列から値を提供しますが、より柔軟なバージョンでは、同じ列または左側の列から値を取得できます。

さらに、MATCH3番目の引数の値が-1,0か1かによって、を使用したマッチングには3つの方法があります。最も単純なのは、とマッチングするときにすでに行ったように、0の値を使用する完全一致ですBattery Lookup Table'!B2:O2。-1の値は、検索値以上の検索範囲内の最小値の位置を見つけます。これは要件ですが、検索範囲を下降順序。(値 1 は、検索値以下の検索範囲内の最大値の位置を検索します。検索範囲は昇順である必要がありますが、これは要件ではありません。)

すべての値はBattery Lookup Table昇順になっているように見えるので、行が逆順になるようにデータを再編成すると、降順の条件が満たされます。100G99 がデータの最初の行になり、50G05 が最後の行になります。また、空白行は削除する必要があります。これらの行の値はゼロとして扱われるため、降順の要件に違反して問題が発生する可能性があります。

表現

MATCH(C46,list,-1))

の位置を提供するリストあなたが探している値とその実際の値（緑の背景のセルに表示される）は、

=INDEX(list,MATCH(C46,list,-1))

バッテリーモデルは以下から提供されています

=INDEX('Battery Lookup Table'!A4:A46,MATCH(C46,list,-1))

の式を代入するとリスト(上記) をこれら 2 つの式に代入すると、最初の式ではある程度の繰り返しがあり、MATCH(C46,list,-1)両方の式で繰り返し計算される煩雑な式になります。このような繰り返しは良い方法ではなく、可能であれば避けるべきです。

これを行う 1 つの方法は、ワークシートのセルに、対応する列番号と行番号の値を保存することです。それぞれ、次のようになります。

=MATCH(C39,'Battery Lookup Table'!B2:O2,0)- で示すん、そして

=MATCH(C46, INDEX('Battery Lookup Table'!B4:B36,,n),-1)- で示すメートル

そして検索結果は

INDEX('Battery Lookup Table'!B4:O36,m,n)（必要な負荷期間における一定定格）および

INDEX('Battery Lookup Table'!A4:A36,m) （対応バッテリー型番）

どこんそしてメートル計算された列番号と行番号を含む 2 つのセル参照です。

Answer 1