Учет верхнего и нижнего регистра в Excel для формулы поиска

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

Как заставить формулу Excel различать большие и маленькие буквы

Допустим, что содержимое исходного значения для поиска находится в ячейке D1, а таблица, по которой будет выполнен поиск, находится в диапазоне A1:B10.

Чтобы найти необходимые значения:

  1. В ячейку E1 введите следующую формулу:
  2. После ввода формулы, для подтверждения нажмите комбинацию горячих клавиш CTRL+SHIFT+Enter, так как формула должна быть выполнена в массиве. Если все сделано правильно в строке формул появятся фигурные скобки { }.

Пример таблицы и работы формулы показано на рисунке:

Поиск с учетом регистра символов.

Как видно теперь в критериях поиска учитывается верхний регистр символов.

Внимание! Если таблица не содержит исходное значение для поиска, тогда формула возвращает пустую ячейку. Если же таблица содержит несколько дубликатов исходного значения, тогда формула возвращает последний дубликат. Это противоположный результат функции ВПР, которая при наличии дубликатов возвращает первый из них.



Принцип действия формулы поиска с учетом регистра

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

Задача функции =ЕСЛИ() – возвращать постой текст, в случаи когда логическое выражение ИЛИ(СОВПАД(A1:A10;D1)) возвращает значение ЛОЖЬ. Пустой текст формула вернет если функция СОВПАД не найдет ни одного совпадения при сравнении с исходным текстом. Если вместо этого значение будет найдено, то в фрагменте формулы: СОВПАД(A1:A10;D1)*СТРОКА(A1:B10) будет выполнен повторный поиск и в результате в память будет возвращен номер строки, которая содержит найденное значение. Здесь используется тот факт, что во врем выполнения арифметических действий логические значения ИСТИНА и ЛОЖЬ заменяются на числа 1 и 0 – соответственно. Поэтому в случаи, когда в процессе поиска текст найден, будет получено значение соответствующие номеру строки (иначе будет равно 0). Из всех полученных номеров строк функция =МАКС() выбирает наибольший и передает его в качестве аргумента для функции =ИНДЕКС(). Эта функция уже возвращает окончательный результат отображения значения ячейки из столбца B соответственной номеру выбранной строки.


en ru