☰

Поиск по двум критериям: один — число, другой — диапазон

tag-icon • tag-icon microsoft-excel
Поиск по двум критериям: один — число, другой — диапазон

У меня есть два списка, один с идентификаторами и процентами на основе различных диапазонов, а другой с идентификаторами и суммами. Мне нужно найти процент в первой таблице на основе идентификатора и суммы во второй таблице.

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

решение1

Предположим, что ваша первая таблица находится на Листе 1, а вторая — на Листе 2, и что вы хотите заполнить столбец Сумма вашей второй таблицы, следующая формула выполнит это для первой ячейки Сумма (ячейка C2) Листа 2. Для этого требуется, чтобы первая таблица была отсортирована по столбцу ID, а затем по столбцу Bottom. Эту формулу можно скопировать и вставить в конец таблицы 2 после ее ввода.

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

Обратите внимание, что верхний столбец на самом деле не является обязательным для работы этого метода.

Разбивая эту формулу, можно сказать, что она используетVLOOKUPфункция для поиска в столбце Bottom ближайшего значения, меньшего или равного значению Total, а затем возвращает значение в столбце Percentage для той же строки. Она также должна ограничить поиск строками, соответствующими правильному идентификатору, и она делает это с помощьюOFFSETФункция.OFFSETФункция использует строку первого найденного совпадающего идентификатораMATCHфункция и общее количество совпадающих идентификаторов, найденных с помощьюCOUNTIFфункция.

Связанный контент