Загрузите образец книги
В этом руководстве будет показано, как выполнить ВПР с учетом регистра в Excel двумя разными методами, а в Google Таблицах - одним методом.
ВПР с учетом регистра с вспомогательной колонкой
Функция ВПР
Функция ВПР используется для поиска приблизительного или точного совпадения значения в крайнем левом столбце диапазона и возвращает соответствующее значение из другого столбца. По умолчанию ВПР работает только для значений без учета регистра, например:
| 1 | = ВПР ($ E $ 2, $ B $ 2: $ C $ 4,2,0) |
ВПР с учетом регистра
Комбинируя VLOOKUP, EXACT, MAX и ROW, мы можем создать чувствительную к регистру формулу VLOOKUP, которая возвращает соответствующее значение для нашей чувствительной к регистру VLOOKUP. Давайте рассмотрим пример.
У нас есть список товаров и соответствующие цены (обратите внимание, что идентификатор товара уникален с учетом регистра):
Предположим, нас просят получить цену за предмет, используя его идентификатор предмета следующим образом:
Для этого нам сначала нужно создать вспомогательный столбец с помощью ROW:
| 1 | = СТРОКА () |
= СТРОКА () щелкните и перетащите (или дважды щелкните), чтобы предварительно заполнить все строки в диапазоне
Затем объедините VLOOKUP, MAX, EXACT и ROW в следующей формуле:
| 12 | = ВПР (МАКС (ТОЧНЫЙ (,) * (СТРОКА ())),,, 0) |
| 1 | = ВПР (МАКС (ТОЧНО (E2; $ B $ 2: $ B $ 7) * (СТРОКА ($ B $ 2: $ B $ 7)))), $ C $ 2: $ D $ 7,2,0) |
Как работает формула?
- Функция EXACT проверяет ID элемента в E2 (значение поиска) на соответствие значениям в B2: B7 (диапазон поиска) и возвращает массив ИСТИНА, где есть точное совпадение, или FLASE в массиве {FLASE, FLASE, FLASE, FLASE, ФЛАЗА, ИСТИНА}.
- Затем этот массив умножается на массив ROW {2, 3, 4, 5, 6, 7} (обратите внимание, что это соответствует нашему вспомогательному столбцу).
- Функция MAX возвращает максимальное значение из результирующего массива {0,0,0,0,0,7}, которое в нашем примере равно 7.
- Затем мы используем результат в качестве значения поиска в ВПР и выбираем вспомогательный столбец в качестве диапазона поиска. В нашем примере формула возвращает совпадающее значение 16,00 долларов США.
Метод 2 - чувствительный к регистру ВПР с «виртуальным» вспомогательным столбцом
Этот метод использует ту же логику, что и первый метод, но устраняет необходимость в создании вспомогательного столбца и вместо этого использует CHOOSE и ROW для создания «виртуального» вспомогательного столбца следующим образом:
| 12 | = ВПР (МАКС (ТОЧНЫЙ (,) * (СТРОКА ())),ВЫБРАТЬ ({1,2}, СТРОКА (),),, 0) |
| 1 | = ВПР (МАКС (ТОЧНО (D2; $ B $ 2: $ B $ 7) * (СТРОКА ($ B $ 2: $ B $ 7))), ВЫБРАТЬ ({1,2}, СТРОКА ($ B $ 2: $ B $ 7) , $ C $ 2: $ C $ 7), 2,0) |
Как работает формула?
- Первая часть формулы работает так же, как и первый метод.
- Комбинация CHOOSE и ROW возвращает массив с двумя столбцами, один для номера строки, а другой для цены. Массив разделяется точкой с запятой для представления следующей строки и запятой для следующего столбца, например: {2,45; 3,83; 4,23; 5,74; 6,4; 7,16}.
- Затем мы можем использовать результат первой части формулы в ВПР, чтобы найти соответствующее значение из нашего массива CHOOSE и ROW.
ВПР с учетом регистра в Google Таблицах
Чтобы выполнить ВПР с учетом регистра в Google Таблицах, используйте этот метод:
