ВПР (VLOOKUP) с учетом регистра
Проблема
Все подстановочные функции Excel из категории Ссылки и массивы (Lookup and Reference), такие как ВПР (VLOOKUP), ГПР (HLOOKUP), ПОИСКПОЗ (MATCH) не учитывают регистр символов (т.е. разницу между прописными и строчными) при поиске данных. Таким образом, при использовании, например, функции ВПР для поиска суммы соответствующей клиенту с кодом Smb3 в данной таблице:
...в итоге получим 38, а не 56 - ибо функция не видит разницы между smb3 и Smb3 и выводит первое встретившееся значение из таблицы.
Что же делать, если нужно находить значение точно, с учетом совпадения регистра символов? Ответ - нужна небольшая формула массива вместо ВПР.
Совет: если вы раньше не особо встречались с формулами массива, то очень рекомендую сходить и почитать вот эту статью, чтобы получить о них общее представление. Если тема знакомая, то читаем дальше.
Решение
Формула, которая нам нужна выглядит следующим образом:
=ИНДЕКС(B4:B9;СУММ(СОВПАД(A4:A9;D4)*СТРОКА(B4:B9))-СТРОКА(B3))
=INDEX(B4:B9,SUM(EXACT(A4:A9,D4)*ROW(B4:B9))-ROW(B3))
Напомню, что в конце ввода этой формулы нужно нажать не привычный Enter, а сочетание Ctrl + Shift + Enter, чтобы ввести ее не как обычную формулу, а как формулу массива. Как видно из примера, эта формула находит правильное значение 56, то есть, фактически, различает строчные и прописные символы в написании кода, в отличие от классической функции ВПР (VLOOKUP).
Как это работает
Чтобы проще и нагляднее объяснить, как именно работает эта формула массива, лучше всего воспользоваться одним из инструментов пошаговой отладки сложных формул в Microsoft Excel - кнопкой Вычислить формулу (Evaluate formula). Найти её можно на вкладке Формулы (Formulas) в группе Зависимости формул (Formula auditing).
В открывшемся окне мы увидим формулу из текущей ячейки и кнопку Вычислить (Calculate), последовательно нажимая на которую, мы будем пошагово рассматривать внутренний механизм расчета нашей формулы:
Итак, поехали - первый этап. Функция СОВПАД (EXACT) проверяет точное совпадение двух текстовых строк с учетом регистра и выдает на выходе ИСТИНА или ЛОЖЬ в зависимости от результата. В нашем случае мы подсунули этой функции не две ячейки для сравнения, как обычно, а массив ячеек (A4:A9), каждая из ячеек которого по очереди будет сравниваться с ячейкой D4, т.е. с нашим поисковым значением. После первого нажатия на кнопку Вычислить мы как раз увидим, как эти данные подставляются в формулу:
После второго нажатия на кнопку Вычислить мы увидим результат сравнения - массив {ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ИСТИНА;ЛОЖЬ}. Видно, что точное совпадение (ИСТИНА) произошло только на пятом элементе массива:
Следующий этап. Функция СТРОКА (ROW) делает совсем простую, по-сути, вещь - выдает номер строки для текущей ячейки. Здесь же мы даем ей в качестве аргумента не одну ячейку, а массив (B4:B9), поэтому получаем на выходе набор номеров строк для каждой ячейки массива, т.е. {4;5;6;7;8;9}:
Затем эти два массива попарно умножаются друг на друга, давая нам на выходе массив {0;0;0;0;8;0}, т.к. ЛОЖЬ (FALSE) в понимании Excel равносильно нулю, а ИСТИНА (TRUE) - единице:
Чтобы получить порядковый номер строки с нужными нам данными внутри таблицы - вычитаем из номера строки на листе (8) номер строки начала таблицы, который определяет функция СТРОКА(B3):
Таким образом, мы получаем в итоге число 5 - номер строки в нашей таблице, где находится точное совпадение с искомым значением из D4 с учетом регистра. Осталось извлечь данные из нужной ячейки столбца по вычисленному номеру строки. Это делает функция ИНДЕКС (INDEX), первый аргумент которой - это массив наших значений (B4:B9), а второй - номер строки в этом массиве (5), из которой мы хотим извлечь нужное нам значение:
Ссылки по теме
- Что такое формулы массива, как их вводить и редактировать, зачем они нужны.
- Использование функции ВПР (VLOOKUP) для подстановки значений
- Улучшенная версия функции ВПР (VLOOKUP2)
- Динамическая выборка из списка функциями ИНДЕКС и ПОИСКПОЗ
И как они такое придумывают? ©SouthPark.
Молодец!!!!
Я бы советовал использовать
Не могли бы подсказать, а как найти данной формулой массива сумму по массиву ячеек, к примеру {=ИНДЕКС(B4:B9;СУММ(СОВПАД(A4:A9;D9:D14)))} где D9:D14 это массив А4:A9 из примера выше.
С такими задачами лучше на .
формула массива:
=ИНДЕКС(B4:B9;ПОИСКПОЗ(ИСТИНА;СОВПАД(D4;A4:A9);0))
немассивная формула:
=ПРОСМОТР(2;1/СОВПАД(A4:A9;D4);B4:B9)
Работают немного по разному, первая формула вернет первое совпадение, вторая - последнее
Если значения в столбце A уникальны, то результат совпадет