Содержание:
В этом небольшом уроке объясняется, как создать функцию ВПР (VLOOKUP) с учетом регистра, показывает несколько других формул, по которым Excel может выполнять поиск с учетом регистра, и указывает на сильные и слабые стороны каждой функции.
Думаю, каждый пользователь Excel знает, какая функция выполняет вертикальный поиск. Всё верно, это функция ВПР. Однако мало кто знает, что ВПР не чувствителен к регистру, т.е. символы нижнего и верхнего регистра для него идентичны.
Вот краткий пример, демонстрирующий неспособность ВПР признать регистр. Предположим, в ячейке A1 содержит значение «счет» и ячейку A2 – «Вексель», формула:
=VLOOKUP("Bill",A1:A10,2)
=ВПР("Bill";A1:A10;2)
… прекратит поиск слова «счет», поскольку это значение стоит первым в списке, и извлечет значение из ячейки B1.
Далее в этой статье я покажу вам, как это сделать. ВПР с учетом регистра. Кроме того, мы изучим еще несколько функций, которые могут выполнять поиск с учетом регистра в Excel.
Начнем с самого простого – Перейти к товару (ПРОСМОТР) и SUMPRODUCT (СУММПРОИЗВ), которые, к сожалению, имеют несколько существенных ограничений. Далее мы подробнее рассмотрим немного более сложную формулу ИНДЕКС+ПОИСКПОЗ (ИНДЕКС+ПОИСКПОЗ), который безупречно работает в любой ситуации и с любым набором данных.
Функция ВПР чувствительна к регистру
Как вы уже знаете, обычная функция ВПР нечувствителен к регистру. Однако есть способ сделать его чувствительным к регистру. Для этого в таблицу необходимо добавить вспомогательный столбец, как показано в следующем примере.
Предположим, в столбце B есть идентификаторы продуктов (Товар), и вы хотите извлечь цену продукта и соответствующий комментарий из столбцов 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;ЛОЖЬ)
Правильная работа функции ВПР чувствительность к регистру зависит от двух факторов:
- Вспомогательный столбец должен быть крайним левым столбцом в видимом диапазоне.
- Искомое значение должно содержать код символа вместо реального значения.
Как правильно использовать функцию КОД
Формула, вставленная в ячейки вспомогательного столбца, предполагает, что все ваши искомые значения имеют одинаковое количество символов. Если нет, то вам нужно знать наименьшее и наибольшее числа и добавить как можно больше признаков. 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-й аргумент – текст (текст) — это текст или ссылка на ячейку, содержащую символы, которые необходимо извлечь (в нашем случае это B2)
- 2-й аргумент – начальный_номер (start_position) — это позиция первого из извлекаемых символов. ты входишь 1 в первой функции ПСТР, 2 – во второй функции ПСТР и так далее
- 3-й аргумент – число_символов (number_of_characters) – указывает количество символов, которые нужно извлечь из текста. Так как нам все время нужен только 1 символ, во всех функциях пишем 1.
ОГРАНИЧЕНИЯ: Функция ВПР не лучшее решение для поиска с учетом регистра в Excel. Во-первых, требуется добавление вспомогательного столбца. Во-вторых, формула хорошо справляется со своей задачей только в том случае, если данные однородны или известно точное количество символов в искомых значениях. Если это не ваш случай, лучше использовать одно из решений, которые мы покажем ниже.
Функция ПРОСМОТР для поиска с учетом регистра
Функция Перейти к товару (ПРОСМОТР) связанные ВПР, однако его синтаксис позволяет выполнять поиск с учетом регистра без добавления вспомогательного столбца. Для этого используйте Перейти к товару в сочетании с функцией 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 той же строки.
Подобно ВПРфункция Перейти к товару одинаково работает с текстовыми и числовыми значениями, как вы можете видеть на скриншоте ниже:
Важно! Для того, чтобы функция Перейти к товару сработало правильно, значения в справочной колонке должны быть отсортированы по возрастанию, т.е. от меньшего к большему.
Кратко поясню, как работает эта функция. EXACT в формуле, показанной выше, поскольку это ключевой момент.
Функция EXACT сравнивает два текстовых значения в 1-м и 2-м аргументах и возвращает TRUE, если они абсолютно одинаковы, или FALSE, если это не так. Для нас важно, чтобы функция EXACT деликатный случай.
Давайте посмотрим, как работает наша формула ПРОСМОТР+ТОЧНО:
=LOOKUP(TRUE,EXACT($A$2:$A$7,$F$2),$B$2:$B$7)
=ПРОСМОТР(ИСТИНА;СОВПАД($A$2:$A$7;$F$2);$B$2:$B$7)
- Функция EXACT сравнивает значение ячейки F2 со всеми элементами в столбце A (А2:А7). Возвращает TRUE, если найдено точное совпадение, в противном случае — FALSE.
- Поскольку вы даете первый аргумент функции Перейти к товару значение TRUE, оно извлекает соответствующее значение из указанного столбца (в нашем случае столбца B) только в том случае, если найдено точное совпадение, с учетом регистра.
Надеюсь, это объяснение было понятным, и теперь вы поняли основную идею. Если да, то с другими функциями, которые мы разберем далее, у вас не возникнет никаких сложностей, т.к. все они работают по одному и тому же принципу.
ОГРАНИЧЕНИЯ: Данные в столбце поиска должны быть отсортированы по возрастанию.
СУММПРОИЗВ – находит текстовые значения с учетом регистра, но возвращает только числа.
Как вы уже поняли из названия, SUMPRODUCT (СУММПРОИЗВ) — еще одна функция Excel, которая поможет вам выполнять поиск с учетом регистра, но возвращает только числовые значения. Если этот вариант вас не устраивает, то можете сразу приступить к комплектации. ИНДЕКС+ПОИСКПОЗ, что дает решение для любого случая и для любых типов данных.
Во-первых, позвольте мне кратко объяснить синтаксис этой функции, это поможет вам лучше понять следующую формулу с учетом регистра.
Функция SUMPRODUCT умножает элементы данных массивов и возвращает сумму результатов. Синтаксис выглядит следующим образом:
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)))
Как вы помните, EXACT сравнивает значение ячейки F2 со всеми элементами в столбце A. Возвращает TRUE, если найдено точное совпадение, в противном случае — FALSE. В математических операциях Excel принимает ИСТИНА как 1, и ЛОЖЬ для 0В дальнейшем SUMPRODUCT умножает эти числа и суммирует результаты.
Нули не учитываются, потому что при умножении они всегда дают 0. Давайте подробнее рассмотрим, что происходит, когда точное совпадение в столбце A нашел и вернул 1… Функция SUMPRODUCT умножает число в столбце B on 1 и возвращает результат – точно такое же число! Это связано с тем, что результаты остальных произведений равны нулю и не влияют на итоговую сумму.
К сожалению, функция SUMPRODUCT не может работать с текстовыми значениями и датами, так как их нельзя умножать. В этом случае вы получите сообщение об ошибке #СТОИМОСТЬ! (#ЗНАЧЕНИЕ!) как в ячейке F4 на картинке ниже:
ОГРАНИЧЕНИЯ: Возвращает только числовые значения.
ИНДЕКС + ПОИСКПОЗ – поиск с учетом регистра для любого типа данных
Наконец, мы близки к неограниченной формуле поиска с учетом регистра, которая работает с любым набором данных.
Этот пример идет последним не потому, что самое лучшее остается на десерт, а потому, что знания, полученные из предыдущих примеров, помогут вам лучше и быстрее понять формулу с учетом регистра. ИНДЕКС+ПОИСКПОЗ (ИНДЕКС+МАТЧ).
Как вы, наверное, догадались, комбинация функций БОЛЬШЕ ОТКРЫТОГО и ИНДЕКС используется в Excel как более гибкая и мощная альтернатива ВПР. Статья «Использование ИНДЕКС и ПОИСКПОЗ вместо ВПР» прекрасно объяснит, как эти функции работают вместе.
Я просто резюмирую ключевые моменты:
- Функция БОЛЬШЕ ОТКРЫТОГО (MATCH) ищет значение в заданном диапазоне и возвращает его относительную позицию, то есть номер строки и/или столбца;
- Далее, функция ИНДЕКС (ИНДЕКС) возвращает значение из указанного столбца и/или строки.
Чтобы сформулировать ИНДЕКС+ПОИСКПОЗ может выполнять поиск с учетом регистра, вам нужно добавить к нему только одну функцию. Нетрудно догадаться, что это снова 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))
В этой формуле EXACT работает так же, как и в связке с функцией Перейти к товаруи дает тот же результат:
Обратите внимание, что формула ИНДЕКС+ПОИСКПОЗ в фигурных скобках заключена формула массива, которую необходимо завершить, нажав Shift + Ctrl + Enter.
Почему ИНДЕКС+ПОИСКПОЗ — лучшее решение для поиска с учетом регистра?
Основные преимущества пакета ИНДЕКС и БОЛЬШЕ ОТКРЫТОГО:
- Не требует добавления вспомогательного столбца, в отличие от ВПР.
- Не требует сортировки столбца поиска, в отличие от Перейти к товару.
- Работает со всеми типами данных – числами, текстом и датами.
Эта формула кажется идеальной, не так ли? На самом деле, это не. И вот почему.
Предположим, что ячейка в столбце возвращаемого значения, связанная с искомым значением, пуста. Какой результат вернет формула? Нет? Давайте посмотрим, что на самом деле возвращает формула:
Упс, формула возвращает ноль! Это не может быть большой проблемой, если вы работаете с чистыми текстовыми значениями. Однако если таблица содержит числа, в том числе «настоящие» нули, это становится проблемой.
Фактически, все остальные формулы поиска (ВПР, ПРОСМОТР и СУММПРОИЗВ), которые мы обсуждали ранее, ведут себя одинаково. Но вам нужна идеальная формула, верно?
Чтобы сделать формулу чувствительной к регистру ИНДЕКС+ПОИСКПОЗ отлично, поместите это в функцию IF (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чтобы компенсировать разницу и иметь функцию КОСВЕННЫЕ (ДВССЫЛ) извлекает значение из нужной ячейки.
На рисунках ниже показана исправленная формула с учетом регистра. ИНДЕКС+ПОИСКПОЗ В бою. Он возвращает пустой результат, если возвращаемая ячейка пуста.
Я переписал формулу на столбцы Б:Дчтобы строка формул поместилась на скриншоте.
Формула возврата 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.")
