Добрый день уважаемый читатель!
В этой статьи я хочу показать и рассказать вам, как производить поиск данных, когда регистр имеет значение. Ранее, на сайте я уже описывал много интересных возможностей функции ВПР, это и интервальный просмотр данных, и поиск по нескольким листам и просто описывал, как улучшить возможности работы этой функции. Отдельно я описывал работу, синтаксис и принципы построения функции ВПР и ее горизонтальной сестры функции ГПР. Но давайте рассмотрим нашу тему поподробнее.
Все используемые функции для подстановки из категории «Ссылки и массивы», это и ВПР, и ГПР, и ПОИСКПОЗ не умеют учитывать разницу между строчными и прописными символами, то есть произвести поиск согласно регистру символов – невозможно. Таким образом, если поиск в таблице необходимо производить с учётом регистра, эта статья вам очень поможет в этом.
Рассмотрим пример, где нам нужно найти суму по коду товара «Orjsmv45», и простой формулой: =ВПР(D2;A2:B6;2;0), найдем значение равным 245, то есть первое значение которое встретила функция, а не 930 как того требует задачка. Возникает закономерный вопрос, как это сделать? Ответ очень прост, с помощью формулы массива вместо простой формулы ВПР. И для получения необходимого результата нужна формула:
={ИНДЕКС (B2:B6; СУММ (СОВПАД (A2:A6;D2)* СТРОКА (B2:B6))— СТРОКА (B1))},
Как видите, формула заключена в фигурные скобки «{» и «}», что означает, что она введена как формула массива с помощью горячей комбинации клавиш «Ctrl+Shift+Enter», а не простим кликом по кнопке Enter. Теперь формула находит правильное значение суммы, которой соответствует искомому коду товара. То есть формула различает, с каких именно символов начинается значение, с прописных или строчных, в отличие от стандартно написанной функции ВПР.
А теперь давайте пошагово посмотрим, как производить поиск данных, когда регистр имеет значение:
Первый этап начинается с работы функции СОВПАД, в ее части СОВПАД(A2:A6;D2), формула проверяет в указанном диапазоне точное совпадение прописанного в условии текстового значения с учётом регистра и как результат получаем значение ЛОЖЬ или ИСТИНА по итогу проверки.
Вторым этапом включается формула СТРОКА(B2:B6), где производится проверка в массиве значений для определения, какой именно из элементов произошло совпадение условий. Схематично массив выглядит так— {ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ИСТИНА}, то есть совпадение появилось на пятом элементе таблицы или на строке (6).
Третьим этапом, мы, по сути, попарно начинаем преумножать значения друг на друга, и когда происходит несовпадение результатов, Excel, указывает значение ЛОЖЬ или 0, а если значения совпадают – ИСТИНА или возврат номера строки (6). То есть массив на выходе будет иметь значения по совпадениям {0;0;0;0;6}.
Четвёртый этап, это получение порядкового номера под которым скрывается нужное нам значение внутри таблицы. То есть из полученного значения номера строки (6), вычитаем тот номер строки, с которого начинается таблица. За эти вычисления отвечает функция СТРОКА(B1), которая определяет начало таблицы как 1 и в итоге 6-1=5, наше значение с точным совпадением при учёте регистра символов размещено на 5 месте в таблице.
Пятый этап, это извлечение значения из определенной нами уже ячейки с помощью функции ИНДЕКС, где из первого аргумента – массива значений «B2:B6» будет извлечено совпадающее значение под номером (5), которое было указано в условии «D2».
Если же вы хотите самостоятельно изучить все этапы работы формулы и увидеть всё это более наглядно, то рекомендую вам воспользоваться инструментом для пошаговой отладки непростых и больших формул в MS Excel. Установив курсор на формулу на рабочем листе, в панели управление выберите вкладку «Формулы», на блоке «Зависимости формул» нажимаете кнопку «Вычислить формулу» и в открывшемся диалоговом окне вы с лёгкостью сможете отследить все манипуляции. Я очень хочу, чтобы о возможностях поиска данных, когда регистр имеет значение в Excel, вы знали еще больше и могли применить их в своей работе. Если у вас есть чем дополнить меня пишите комментарии, я буду их ждать с нетерпением, ставьте лайки и делитесь полезной статьей в соц.сетях!
Не забудьте подкинуть автору на кофе…
