Как я могу сделать двумерный поиск, где заголовок столбца и строки являются диапазонами?

Question

У меня есть таблица с таблицей, которая похожа по структуре на эту:

      | 1-2 | 3-4 | 5-6 | 7+
-------------------------------
18-20 |  7  |  2  |  9  |  4  |
21-25 |  1  |  0  |  6  |  3  |
26-30 |  1  |  4  |  6  |  9  |
31+   |  3  |  8  |  5  |  0  |

Скажем, например, что я получил от пользователя 2, 19 , поиск должен вернуть 7 . Или, если входы были 9, 29 , поиск должен вернуть 9 .

Я знаю, что вы можете выполнять двумерный поиск, когда у вас есть только отдельные значения в заголовках строк и столбцов, комбинируя VLOOKUP и MATCH . Но как мне выполнить поиск, когда заголовки представляют собой диапазон значений?

Мне нужно просто написать пользовательскую функцию VBA или в Excel есть какой-то встроенный способ сделать это?

Answer 1

INDEX() может выполнять двумерный поиск. Синтаксис: INDEX(array,row_num,[col_num],[area_num]) . Он принимает 2D-массив и возвращает пересечение row_num и column_num.

Ваша проблема немного сложна из-за диапазонов в заголовках, но некоторая быстрая работа с TRUNC() и IF() делает свою работу. Эта формула, заполненная с D8 на скриншоте ниже, дает показанные результаты:

=INDEX(B$2:E$5,IF(C8>31,4,TRUNC((C8-11)/5)),IF(B8>7,4,TRUNC((B8+1)/2)))

Если вы хотите увидеть, как работает часть TRUNC() , поместите =TRUNC((ROW()-11)/5) в строке 18 или TRUNC((ROW()+1)/2) в строке 1 и заполните.

Надеюсь, это поможет, и удачи.

Answer 2

Учитывая, что вы ищете диапазоны, которые являются взаимоисключающими и полными (по крайней мере, для значений> = 18), ваш диапазон может быть определен по первому значению в каждой ячейке. Например, предоставленная вами таблица может быть описана эквивалентно:

Затем вы можете найти свою ценность, комбинируя vlookup и match. Например, если ваши значения таблицы и поиска были расположены следующим образом:

Ваше желаемое значение будет в выделенной ячейке, которая будет содержать следующее: = VLOOKUP(B $ 8, $ A $ 1: $ E $ 5, MATCH($ A8, $ A $ 1: $ E $ 1,1), TRUE)