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

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

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

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

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

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

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

Мы начнём с простейших — ПРОСМОТР (LOOKUP) и СУММПРОИЗВ (СУММПРОИЗВ), которые, к сожалению, имеют несколько существенных ограничений. Далее мы пристально рассмотрим чуть более сложную формулу ИНДЕКС + ПОИСКПОЗ (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)) & КОДСИМВ (ПСТР (B 2; 6; 1)) & КОДСИМВ (ПСТР (B2; 7; 1)) & КОДСИМВ (ПСТР (B2; 8; 1)) & ЕСЛИОШИБКА (КОДСИМВ (ПСТР (B2; 9; 1)); "")

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

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

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

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

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

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

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

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

= CODE ( MID (B2,1,1)) & CODE (MID (B2,2,1)) & CODE (MID (B2,3,1)) & IFERROR (CODE (MID (B2,3,1)), "" ) & ЕСЛИОШИБКА (КОД (MID (B2,4,1)), "")

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

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

  • 1-й аргумент — текст (текст) — это текст или ссылка на ячейку, содержащую символы, которые нужно извлечь (в нашем случае это B2)
  • 2-й аргумент — start_num (начальная_позиция) — позиция первого из тех символов, которые нужно извлечь. Вы вводите 1 в первую функцию ПСТР , 2 — во второй функции ПСТР и т. д.
  • 3-й аргумент — num_chars (количество_знаков) — определить количество знаков, которые нужно извлечь из текста. Так как нам всё время нужен только 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-ом аргументе и возвращает ИСТИНА (ИСТИНА), если они в точности одинаковы, или ЛОЖЬ (ЛОЖЬ), если нет. Для нас важна функция, что функция СОВПАД чувствительна к регистру.

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

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

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

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

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

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

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

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

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

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

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

= СУММПРОИЗВ ((ТОЧНЫЙ ($ A $ 2: $ A $ 7, $ F $ 2) * ($ B $ 2: $ B $ 7) ))
= СУММПРОИЗВ ((СОВПАД ($ A $ 2: $ A $ 7; $ F $ 2) * ($ B $ 2: $ B $ 7)))

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

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

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

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

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

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

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

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

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

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

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

= INDEX ($ B $ 2: $ B $ 7, MATCH (TRUE, ТОЧНЫЙ ($ 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 (ИСТИНА, ТОЧНО ($ A $ 2: $ A $ 7, $ G $ 2), 0))) "", ИНДЕКС ($ B $ 2: $ B $ 7, ПОИСКПОЗ (ИСТИНА, ТОЧНО ($ 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 , чтобы компенсировать разницу и чтобы функция ДВССЫЛ (КОСВЕННЫЙ) извлекла значение из нужной ячейки.

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

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

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

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

= IF (INDIRECT ("D" & (1 + MATCH (TRUE, EXACT ($ B $ 2: $ B $ 7, $ G $ 2), 0))) "", INDEX ($ D $ 2: $ D $ 7, MATCH (ИСТИНА, ТОЧНО ($ B $ 2: $ B $ 7, $ G $ 2), 0)), «Нечего возвращать, извините.»)

= ЕСЛИ (ДВССЫЛ ("D" & (1 + ПОИСКПОЗ (ИСТИНА; СОВПАД ($ B $ 2: $ B $ 7; $ G $ 2); 0))) ""; ИНДЕКС ($ D $ 2: $ D $ 7 ; ПОИСКПОЗ (ИСТИНА; СОВПАД ($ B $ 2: $ B $ 7; $ G $ 2); 0)); «Нечего возвращать, извините.»)

Урок подготовлен для Вас командой сайт а office-guru.ru
Источник: https://www.ablebits.com/office-addins-blog/2014/08/19/vlookup-case-sensitive-excel/
Перевел: Антон Андронов
Правила перепечатки
Еще больше уроков по Microsoft Excel

Оцените качество статьи. Нам важно ваше мнение:
Оцените статью
Botgadget.ru
Добавить комментарий