Поиск текста ВПР по таблице Excel с учетом регистра символов

Функция ВПР не обращает внимания на регистр символов, поэтому если Вы хотите, чтобы Excel учитывал при поиске (Вертикальным ПРосмотром) регистр символов, вам нужно построить для этого правильную формулу.

Как заставить функцию ВПР различать большие и маленькие буквы?

Рассмотрим пример. Сначала ознакомимся с исходными данными:

ВПР.

Наш расчет начнем с функции СОВПАД, которая сравнивает две текстовые строки (с учетом регистра символов) и в зависимости от результата сравнения возвращает логическое значение ИСТИНА или ЛОЖЬ. В нашем примере нам нужно как один из аргументов функции СОВПАД указать текст, который мы ищем, а вторым аргументом - ссылку на столбец, в котором будем искать. Формула следующая:

=СОВПАД(E2;$A$2:$A$11)

При таких данных, функция СОВПАД возвращает массив логических значений ИСТИНА и ЛОЖЬ. ИСТИНА будет в тех ячейках, где текстовые строки совпали, в остальных ячейках - ЛОЖЬ. Схематически данный массив выглядит так:

={ЛОЖЬ:ИСТИНА:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ}

Поэтому функция СОВПАД будет возвращать значение ЛОЖЬ даже если ее выполнить в массиве. Временно добавим функцию ИЛИ, которая возвращает значение ИСТИНА если хотя бы одно значение в массиве является истинным. Временная формула будет выглядеть так:

СОВПАД.

Обратите внимание! Данная формула должна выполняться в массиве. Поэтому для ее ввода нажимаем не просто клавишу Enter, а целую комбинацию клавиш CTRL+SHIFT+Enter.

Теперь, на основе выше указанной таблицы, нам нужно создать элемент, который мы будем искать. Относительно простым решением будет перемножение его в массиве с номерами строк данных. Все что вам нужно сделать, это использовать функцию СТРОКА с заданным диапазоном столбца, в котором будет осуществляться поиск. Его результат должен умножить на массив, возвращаемый функцией СОВПАД.

В результате будет таблица с нулями или номерами строк, там, где Excel нашел идентичные текстовые строки.

Нас интересует максимальное значение в этой таблице, поэтому необходимо наши промежуточные вычисления вставляем в функцию МАКС:

СТРОКА МАКС.

Нам удалось найти значение, которое мы будем искать, используя функцию ВПР.



Функция ВЫБОР и ВПР для вертикального поиска значений таблицы

Теперь Вам нужно построить массив, в которой функция будет осуществлять поиск. Для этого Вам понадобится функция ВЫБОР, которая по умолчанию возвращает выбранный аргумент на основании очередности, то есть если Вы введете 1 в качестве первого аргумента, функция ВЫБОР вернет следующий первый аргумент, если Вы введете 2, она вернет следующий второй аргумент и т.д.

Только нам необходимо чтобы функция ВЫБОР возвращала два аргумента одновременно. Чтобы получить такой эффект, Вы должны ввести первый аргумент в виде массива данных: {1\2}. Для такого аргумента функция ВЫБОР возвращает как первый, так и второй выбранный аргумент.

К счастью, функция ВЫБОР не имеет проблем с возвратом диапазонов данных, поэтому нашим первым аргументом будут номера строк в столбце, в котором мы ищем (СТРОКА($A$2:$A$11)), а вторым аргументом будет столбец, из которого мы хотим извлечь информацию ($B$2:$B$11). Поскольку функция ВПР возвращается значения из одного столбца, даже если Ваши исходные данные содержат больше столбцов, достаточно указать в аргументах функции ВЫБОР только два столбца:

  1. Первый столбец с номерами строк.
  2. Второй столбец, из которого Вы хотите получить значение.

Самое сложное позади. Все, что вам нужно сделать, это еще ввести два последних аргумента функции ВПР:

  1. Значение 2, потому что мы ищем значение из второго столбца.
  2. Значение 0, потому что мы хотим выполнить поиск по принципу точного соответствия.

Таким образом, вся формула будет иметь следующий вид:

ВЫБОР.

Помните, что это формула массива, потому что мы функции Excel в качестве аргумента даем массив данных там, где она ожидает одно значение. Поэтому Вы должны "одобрить" фигурными скобками эту формулу нажатием комбинации клавиш Ctrl + Shift + Enter.


en ru