2 つの基準 (1 つは数値、もう 1 つは範囲) に基づく検索

Question

リストが 2 つあります。1 つはさまざまな範囲に基づく ID とパーセンテージを含むリストで、もう 1 つは ID と合計を含むリストです。2 番目のスプレッドシートの ID と合計に基づいて、最初のスプレッドシートのパーセンテージを調べる必要があります。

ID  Bottom Top  Percentage
1   1      50       0.3
1   51     75       0.4
1   76              0.5
2   1               0.75
3   1      25       1
3   26     100      2
3   101    1000     3
3   1001   2000     4
3   2001            5

Second List
ID  Total Amount
1   100
2   146
3   1256

Answer 1

最初のテーブルが Sheet1 にあり、2 番目のテーブルが Sheet2 にあり、2 番目のテーブルの Amount 列に値を入力する場合、次の数式で Sheet2 の最初の Amount セル (セル C2) に値を入力します。このためには、最初のテーブルを ID 列で並べ替え、次に Bottom 列で並べ替える必要があります。この数式は、入力後、テーブル 2 の最後までコピーして貼り付けることができます。

=VLOOKUP(B2, OFFSET(Sheet1!B:D, MATCH(A2, Sheet1!A:A, 0) - 1, 0, COUNTIF(Sheet1!A:A, A2)), 3)

これを機能させるために、実際には Top 列は必要ないことに注意してください。

この式を分解すると、VLOOKUP関数は、Bottom列でTotal値以下の最も近い値を検索し、同じ行のPercentage列の値を返します。また、正しいIDに一致する行に検索を限定する必要があり、これはOFFSET機能。OFFSET関数は、最初に見つかった一致するIDの行を使用します。MATCH関数と一致するIDの合計数COUNTIF関数。