4 способа сделать ВПР с учетом регистра в Excel

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

Полагаю, каждый пользователь Excel знает, какая функция осуществляет вертикальный поиск. Правильно, это функция ВПР. Однако, мало кто знает, что ВПР не чувствительна к регистру, то есть символы нижнего и ВЕРХНЕГО регистра для нее идентичны.

Вот быстрый пример, демонстрирующий неспособность ВПР распознать регистр. Предположим, в ячейке A1 содержится значение «bill», а в ячейке A2 – «Bill», формула:

=VLOOKUP("Bill",A1:A10,2)
=ВПР("Bill";A1:A10;2)

… остановит свой поиск на «bill», поскольку это значение идёт первым в списке, и извлечёт значение из ячейки B1.

Далее в этой статье я покажу способ сделать ВПР чувствительной к регистру. Кроме этого, мы изучим ещё несколько функций, которые могут выполнить поиск в Excel с учётом регистра.

Мы начнём с простейших – ПРОСМОТР (LOOKUP) и СУММПРОИЗВ (SUMPRODUCT), которые, к сожалению, имеют несколько существенных ограничений. Далее мы пристально рассмотрим чуть более сложную формулу ИНДЕКС+ПОИСКПОЗ (INDEX+MATCH), которая работает безукоризненно в любых ситуациях и с любыми наборами данных.

• Чувствительная к регистру функция ВПР – требует вспомогательный столбец
• Чувствительная к регистру функция ПРОСМОТР – требует сортировку данных
• СУММПРОИЗВ – возвращает только числовые значения
• ИНДЕКС+ПОИСКПОЗ – поиск с учётом регистра для всех типов данных

Функция ВПР чувствительная к регистру

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

Предположим, в столбце B находятся идентификаторы товаров (Item), и Вы хотите извлечь цену товара и соответствующий комментарий из столбцов C и D. Проблема в том, что идентификаторы содержат символы как нижнего, так и верхнего регистров. Например, значения ячеек B4 (001Tvci3u) и B5 (001Tvci3U) отличаются только регистром последнего символа, u и U соответственно.

Как Вы сами догадываетесь, обычная формула поиска

=VLOOKUP("001Tvci3U",$A$2:$C$7,2,FALSE)
=ВПР("001Tvci3U";$A$2:$C$7;2;ЛОЖЬ)

возвратит $90, поскольку значение 001Tvci3u стоит в диапазоне поиска раньше, чем 001Tvci3U. Но это не то, что нам нужно, не так ли?

Чтобы выполнить поиск функцией ВПР в Excel с учётом регистра, Вам придётся добавить вспомогательный столбец и заполнить его ячейки следующей формулой (где B это столбец поиска):

=CODE(MID(B2,1,1)) & CODE(MID(B2,2,1)) & CODE(MID(B2,3,1)) & CODE(MID(B2,4,1)) & CODE(MID(B2,5,1)) & CODE(MID(B2,6,1)) & CODE(MID(B2,7,1)) & CODE(MID(B2,8,1)) & IFERROR(CODE(MID(B2,9,1)),"")

=КОДСИМВ(ПСТР(B2;1;1)) & КОДСИМВ(ПСТР(B2;2;1)) & КОДСИМВ(ПСТР(B2;3;1)) & КОДСИМВ(ПСТР(B2;4;1)) & КОДСИМВ(ПСТР(B2;5;1)) & КОДСИМВ(ПСТР(B2;6;1)) & КОДСИМВ(ПСТР(B2;7;1)) & КОДСИМВ(ПСТР(B2;8;1)) & ЕСЛИОШИБКА(КОДСИМВ(ПСТР(B2;9;1));"")

Эта формула разбивает искомое значение на отдельные символы, заменяет каждый символ его кодом (например, вместо A код 65, вместо a код 97), а затем соединяет эти коды в уникальную строку цифр.

После этого используем простую функцию ВПР для поиска с учётом регистра:

=VLOOKUP($G$3,$A$2:$C$8,3,FALSE)
=ВПР($G$3;$A$2:$C$8;3;ЛОЖЬ)

Правильная работа функции ВПР с учётом регистра зависит от двух факторов:

1. Вспомогательный столбец должен быть крайним левым в просматриваемом диапазоне.
2. Искомое значение должно содержать код символов вместо реального значения.

Как правильно пользоваться функцией КОДСИМВ

Формула, вставленная в ячейки вспомогательного столбца, предполагает, что все Ваши искомые значения имеют одинаковое количество символов. Если нет, то нужно знать наименьшее и наибольшее количества и добавить столько функций ЕСЛИОШИБКА (IFERROR), сколько символов составляет разница между самым коротким и самым длинным искомым значением.

Например, если самое коротко искомое значение состоит из 3 символов, а самое длинное – из 5 символов, используйте такую формулу:

=CODE(MID(B2,1,1)) & CODE(MID(B2,2,1)) & CODE(MID(B2,3,1)) & IFERROR(CODE(MID(B2,3,1)),"") & IFERROR(CODE(MID(B2,4,1)),"")

=КОДСИМВ(ПСТР(B2;1;1)) & КОДСИМВ(ПСТР(B2;2;1)) & КОДСИМВ(ПСТР(B2;3;1)) & ЕСЛИОШИБКА(КОДСИМВ(ПСТР(B2;3;1));"") & ЕСЛИОШИБКА(КОДСИМВ(ПСТР(B2;4;1));"")

Для функции ПСТР (MID) Вы задаёте следующие аргументы:

• 1-й аргумент – text (текст) – это текст или ссылка на ячейку, содержащую символы, которые нужно извлечь (в нашем случае это B2)
• 2-й аргумент – start_num (начальная_позиция) – позиция первого из тех символов, которые нужно извлечь. Вы вводите 1 в первой функции ПСТР, 2 – во второй функции ПСТР и т. д.
• 3-й аргумент – num_chars (количество_знаков) – определяет количество знаков, которые нужно извлечь из текста. Так как нам всё время нужен только 1 символ, то во всех функциях пишем 1.

ОГРАНИЧЕНИЯ: Функция ВПР – это не лучшее решение для поиска в Excel с учётом регистра. Во-первых, требуется добавление вспомогательного столбца. Во-вторых, формула неплохо справляется, только если данные однородны, или известно точное количество символов в искомых значениях. Если это не Ваш случай, лучше используйте одно из решений, которые мы покажем далее.

Функция ПРОСМОТР для поиска с учётом регистра

Функция ПРОСМОТР (LOOKUP) сродни ВПР, однако её синтаксис позволяет искать с учётом регистра без добавления вспомогательного столбца. Для этого используйте ПРОСМОТР в сочетании с функцией СОВПАД (EXACT).

Если мы возьмём данные из предыдущего примера (без вспомогательного столбца), то с задачей справится следующая формула:

=LOOKUP(TRUE,EXACT($A$2:$A$7,$F$2),$B$2:$B$7)
=ПРОСМОТР(ИСТИНА;СОВПАД($A$2:$A$7;$F$2);$B$2:$B$7)

Формула ищет в диапазоне A2:A7 точное совпадение со значением ячейки F2 с учётом регистра и возвращает значение из столбца B той же строки.

Как и ВПР, функция ПРОСМОТР одинаково работает с текстовыми и числовыми значениями, это хорошо видно на снимке экрана внизу:

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

Позвольте кратко объяснить, как действует функция СОВПАД в показанной выше формуле, поскольку это ключевой момент.

Функция СОВПАД сравнивает два текстовых значения в 1-ом и 2-ом аргументе и возвращает ИСТИНА (TRUE), если они в точности одинаковы, или ЛОЖЬ (FALSE), если нет. Для нас важным является то, что функция СОВПАД чувствительна к регистру.

Давайте разберёмся, как работает наша формула ПРОСМОТР+СОВПАД:

=LOOKUP(TRUE,EXACT($A$2:$A$7,$F$2),$B$2:$B$7)
=ПРОСМОТР(ИСТИНА;СОВПАД($A$2:$A$7;$F$2);$B$2:$B$7)

• Функция СОВПАД сравнивает значение ячейки F2 со всеми элементами в столбце A (A2:A7). Если точное совпадение найдено, возвращает ИСТИНА (TRUE), а если нет – ЛОЖЬ (FALSE).
• Так как Вы задаёте для первого аргумента функции ПРОСМОТР значение ИСТИНА (TRUE), то она извлекает соответствующее значение из указанного столбца (в нашем случае это столбец B), только если найдено точное совпадение с учётом регистра.

Надеюсь, это объяснение было понятным и теперь Вам понятна основная идея. Если да, то у Вас не возникнет каких-либо трудностей и с другими функциями, которые мы будем разбирать далее, т.к. все они работают по одинаковому принципу.

ОГРАНИЧЕНИЯ: Данные в столбце поиска должны быть упорядочены по возрастанию.

СУММПРОИЗВ – находит текстовые значения с учётом регистра, но возвращает только числа

Как Вы уже поняли из заголовка, СУММПРОИЗВ (SUMPRODUCT) это ещё одна функция Excel, которая поможет выполнить поиск с учётом регистра, но возвратит только числовые значения. Если этот вариант Вам не подходит, то можете сразу переходить к связке ИНДЕКС+ПОИСКПОЗ, которая даёт решение на любой случай и для любых типов данных.

Для начала, позвольте кратко объяснить синтаксис данной функции, это поможет лучше понять чувствительную к регистру формулу, которая следует далее.

Функция СУММПРОИЗВ перемножает элементы заданных массивов и возвращает сумму результатов. Синтаксис имеет такой вид:

SUMPRODUCT(array1,[array2],[array3],...)
СУММПРОИЗВ(массив1;[массив2];[массив3];…)

Раз нам необходим поиск с учётом регистра, используем функцию СОВПАД (EXACT) из предыдущего примера в качестве одного из множителей:

=SUMPRODUCT((EXACT($A$2:$A$7,$F$2)*($B$2:$B$7)))
=СУММПРОИЗВ((СОВПАД($A$2:$A$7;$F$2)*($B$2:$B$7)))

Как Вы помните, СОВПАД сравнивает значение ячейки F2 со всеми элементами в столбце A. В случае, если найдено точное совпадение, возвращает ИСТИНА (TRUE), иначе – ЛОЖЬ (FALSE). В математических операциях Excel принимает ИСТИНА (TRUE) за 1, а ЛОЖЬ (FALSE) за 0, далее СУММПРОИЗВ перемножает эти цифры и суммирует полученные результаты.

Нули не считаются, поскольку при умножении они всегда дают 0. Давайте посмотрим подробнее, что происходит, когда точное совпадение в столбце A найдено и возвращена 1. Функция СУММПРОИЗВ умножает число в столбце B на 1 и возвращает результат – точно такое же число! Так происходит потому, что результаты других произведений – нули, и они не влияют на получившуюся в итоге сумму.

К сожалению, функция СУММПРОИЗВ не может работать с текстовыми значениями и датами, так как их нельзя перемножить. В этом случае Вы получите сообщение об ошибке #ЗНАЧ! (#VALUE!), как в ячейке F4 на рисунке ниже:

ОГРАНИЧЕНИЯ: Возвращает только числовые значения.

ИНДЕКС+ПОИСКПОЗ – поиск с учётом регистра для любых типов данных

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

Этот пример идёт последним не потому, что лучшее оставлено на десерт, а потому, что знания, полученные из предыдущих примеров, помогут лучше и быстрее понять чувствительную к регистру формулу ИНДЕКС+ПОИСКПОЗ (INDEX+MATCH).

Как Вы, наверное, догадались, комбинация функций ПОИСКПОЗ и ИНДЕКС используется в Excel как более гибкая и мощная альтернатива для ВПР. Статья Использование ИНДЕКС и ПОИСКПОЗ вместо ВПР прекрасно объяснит Вам, как эти функции работают в паре.

Я лишь напомню ключевые моменты:

• Функция ПОИСКПОЗ (MATCH) ищет значение в заданном диапазоне и возвращает его относительную позицию, то есть номер строки и/или столбца;
• Далее, функция ИНДЕКС (INDEX) возвращает значение из определённого столбца и/или строки.

Чтобы формула ИНДЕКС+ПОИСКПОЗ могла искать с учётом регистра, к ней нужно добавить лишь одну функцию. Не трудно догадаться, что это снова СОВПАД (EXACT):

=INDEX($B$2:$B$7,MATCH(TRUE,EXACT($A$2:$A$7,$F$2),0))
=ИНДЕКС($B$2:$B$7;ПОИСКПОЗ(ИСТИНА;СОВПАД($A$2:$A$7;$F$2);0))

В этой формуле СОВПАД работает так же, как и в связке с функцией ПРОСМОТР, и даёт такой же результат:

Заметьте, что формула ИНДЕКС+ПОИСКПОЗ заключена в фигурные скобки – это формула массива, и Вы должны завершить её ввод нажатием Ctrl+Shift+Enter.

Почему ИНДЕКС+ПОИСКПОЗ – это лучшее решение для поиска с учётом регистра?

Главные преимущества связки ИНДЕКС и ПОИСКПОЗ:

1. Не требует добавления вспомогательного столбца, в отличие от ВПР.
2. Не требует сортировки столбца поиска, в отличие от ПРОСМОТР.
3. Работает со всеми типами данных – с числами, текстом и датами.

Эта формула кажется идеальной, не правда ли? На самом деле, это не так. И вот почему.

Предположим, что ячейка в столбце возвращаемых значений, связанных с искомым значением, пуста. Какой результат возвратит формула? Никакой? Давайте посмотрим, что возвратит формула на самом деле:

Упс, формула возвращает ноль! Это может быть не велика беда, если Вы работаете с чисто текстовыми значениями. Однако, если таблица содержит числа, в том числе «настоящие» нули – это становится проблемой.

На самом деле, все остальные формулы поиска (ВПР, ПРОСМОТР и СУММПРОИЗВ), которые мы обсуждали ранее, ведут себя так же. Но Вы же хотите безупречную формулу, так ведь?

Чтобы сделать чувствительную к регистру формулу ИНДЕКС+ПОИСКПОЗ идеальной, поместите её в функцию ЕСЛИ (IF), которая будет проверять ячейку с возвращаемым значением и возвращать пустой результат, если она пуста:

=IF(INDIRECT("B"&(1+MATCH(TRUE,EXACT($A$2:$A$7,$G$2),0)))<>"",INDEX($B$2:$B$7, MATCH(TRUE,EXACT($A$2:$A$7,$G$2),0)),"")

=ЕСЛИ(ДВССЫЛ("B"&(1+ПОИСКПОЗ(ИСТИНА;СОВПАД($A$2:$A$7;$G$2);0)))<>"";ИНДЕКС($B$2:$B$7; ПОИСКПОЗ(ИСТИНА;СОВПАД($A$2:$A$7;$G$2);0));"")

В этой формуле:

• B – это столбец с возвращаемыми значениями
• 1+ – это число, которое превращает относительную позицию ячейки, возвращаемую функцией ПОИСКПОЗ, в реальный адрес ячейки. Например, в нашей функции ПОИСКПОЗ задан массив поиска A2:A7, то есть относительная позиция ячейки A2 будет 1, потому что она первая в массиве. Но реальная позиция ячейки A2 в столбце – это 2, поэтому мы добавляем 1, чтобы компенсировать разницу и чтобы функция ДВССЫЛ (INDIRECT) извлекла значение из нужной ячейки.

Рисунки ниже демонстрируют исправленную чувствительную к регистру формулу ИНДЕКС+ПОИСКПОЗ в действии. Она возвращает пустой результат, если возвращаемая ячейка пуста.

Я переписал формулу в столбцы B:D, чтобы строка формул поместилась на скриншоте.

Формула возвращает 0, если возвращаемая ячейка содержит ноль.

Если Вы хотите, чтобы связка ИНДЕКС и ПОИСКПОЗ отображала какое-то сообщение, когда возвращаемое значение пусто, можете написать его в последних кавычках («») формулы, например, так:

=IF(INDIRECT("D"&(1+MATCH(TRUE,EXACT($B$2:$B$7,$G$2),0)))<>"",INDEX($D$2:$D$7, MATCH(TRUE,EXACT($B$2:$B$7,$G$2),0)),"There is nothing to return, sorry.")

=ЕСЛИ(ДВССЫЛ("D"&(1+ПОИСКПОЗ(ИСТИНА;СОВПАД($B$2:$B$7;$G$2);0)))<>"";ИНДЕКС($D$2:$D$7; ПОИСКПОЗ(ИСТИНА;СОВПАД($B$2:$B$7;$G$2);0));"There is nothing to return, sorry.")