Excel поиск с учетом регистра

Обновлено: 07.07.2024

[p.s.]Лен, твою формулу можно немного сократить:

[/p.s.] Автор - Serge_007
Дата добавления - 23.03.2014 в 20:23
Всегда стараюсь, чтобы формула была более понятной, а не более короткой
Всегда стараюсь, чтобы формула была более понятной, а не более короткой Pelena
Всегда стараюсь, чтобы формула была более понятной, а не более короткой Автор - Pelena
Дата добавления - 23.03.2014 в 20:31 [offtop]Лен, а что в сокращенной формуле менее понятно чем в исходной? %)[/offtop] [offtop]Лен, а что в сокращенной формуле менее понятно чем в исходной? %)[/offtop] Serge_007 [offtop]Человека, не искушенного в Excel, вот это 0=0 повергнет в ступор :)[/offtop] [offtop]Человека, не искушенного в Excel, вот это 0=0 повергнет в ступор :)[/offtop] Pelena [offtop]ИМХО, если любому человеку, даже далёкому от Excel, задать вопрос "Истинно ли утверждение что ноль равен нулю?", то любой ответит: "Истинно". Не? ;)[/offtop] [offtop]ИМХО, если любому человеку, даже далёкому от Excel, задать вопрос "Истинно ли утверждение что ноль равен нулю?", то любой ответит: "Истинно". Не? ;)[/offtop] Serge_007

[offtop]Серёж, ты не поверишь, но многим людям, очень даже не глупым, даже традиционную функцию ЕСЛИ() в стандартном исполнении, когда сравниваются две ячейки, бывает сложно понять. А уж когда встречается что-либо из логики… Это ДЕЙСТВИТЕЛЬНО трудно! Я это знаю из собственного опыта.

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

А в данном случае справка говорит, что первый аргумент ПОИСКПОЗ() - это искомое значение. То есть ищем мы 0=0. Это ж надо ещё догадаться, что это эквивалентно ИСТИНА [/offtop]

А в данном случае справка говорит, что первый аргумент ПОИСКПОЗ() - это искомое значение. То есть ищем мы 0=0. Это ж надо ещё догадаться, что это эквивалентно ИСТИНА [/offtop] Pelena

А в данном случае справка говорит, что первый аргумент ПОИСКПОЗ() - это искомое значение. То есть ищем мы 0=0. Это ж надо ещё догадаться, что это эквивалентно ИСТИНА [/offtop] Автор - Pelena
Дата добавления - 23.03.2014 в 22:10

Все подстановочные функции Excel из категории Ссылки и массивы (Lookup and Reference) , такие как ВПР (VLOOKUP), ГПР (HLOOKUP) , ПОИСКПОЗ (MATCH) не учитывают регистр символов (т.е. разницу между прописными и строчными) при поиске данных. Таким образом, при использовании, например, функции ВПР для поиска суммы соответствующей клиенту с кодом Smb3 в данной таблице:

. в итоге получим 38, а не 56 - ибо функция не видит разницы между smb3 и Smb3 и выводит первое встретившееся значение из таблицы.

Что же делать, если нужно находить значение точно, с учетом совпадения регистра символов? Ответ - нужна небольшая формула массива вместо ВПР.

Совет: если вы раньше не особо встречались с формулами массива, то очень рекомендую сходить и почитать вот эту статью, чтобы получить о них общее представление. Если тема знакомая, то читаем дальше.

Решение

Формула, которая нам нужна выглядит следующим образом:

Напомню, что в конце ввода этой формулы нужно нажать не привычный Enter , а сочетание Ctrl + Shift + Enter , чтобы ввести ее не как обычную формулу, а как формулу массива. Как видно из примера, эта формула находит правильное значение 56, то есть, фактически, различает строчные и прописные символы в написании кода, в отличие от классической функции ВПР (VLOOKUP).

Как это работает

Чтобы проще и нагляднее объяснить, как именно работает эта формула массива, лучше всего воспользоваться одним из инструментов пошаговой отладки сложных формул в Microsoft Excel - кнопкой Вычислить формулу (Evaluate formula) . Найти её можно на вкладке Формулы (Formulas) в группе Зависимости формул (Formula auditing) .

В открывшемся окне мы увидим формулу из текущей ячейки и кнопку Вычислить (Calculate) , последовательно нажимая на которую, мы будем пошагово рассматривать внутренний механизм расчета нашей формулы:

Итак, поехали - первый этап. Функция СОВПАД (EXACT) проверяет точное совпадение двух текстовых строк с учетом регистра и выдает на выходе ИСТИНА или ЛОЖЬ в зависимости от результата. В нашем случае мы подсунули этой функции не две ячейки для сравнения, как обычно, а массив ячеек (A4:A9), каждая из ячеек которого по очереди будет сравниваться с ячейкой D4, т.е. с нашим поисковым значением. После первого нажатия на кнопку Вычислить мы как раз увидим, как эти данные подставляются в формулу:

После второго нажатия на кнопку Вычислить мы увидим результат сравнения - массив . Видно, что точное совпадение (ИСТИНА) произошло только на пятом элементе массива:

Следующий этап. Функция СТРОКА (ROW) делает совсем простую, по-сути, вещь - выдает номер строки для текущей ячейки. Здесь же мы даем ей в качестве аргумента не одну ячейку, а массив (B4:B9), поэтому получаем на выходе набор номеров строк для каждой ячейки массива, т.е. :

Затем эти два массива попарно умножаются друг на друга, давая нам на выходе массив , т.к. ЛОЖЬ (FALSE) в понимании Excel равносильно нулю, а ИСТИНА (TRUE) - единице:

Чтобы получить порядковый номер строки с нужными нам данными внутри таблицы - вычитаем из номера строки на листе (8) номер строки начала таблицы, который определяет функция СТРОКА(B3):

Таким образом, мы получаем в итоге число 5 - номер строки в нашей таблице, где находится точное совпадение с искомым значением из D4 с учетом регистра. Осталось извлечь данные из нужной ячейки столбца по вычисленному номеру строки. Это делает функция ИНДЕКС (INDEX) , первый аргумент которой - это массив наших значений (B4:B9), а второй - номер строки в этом массиве (5), из которой мы хотим извлечь нужное нам значение:

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

Смотрите на примере. Здесь есть 3 личности с похожими именами. С помощью ВПР мы ищем строку «Сергей» и должны получить его возраст (50 лет). Но из-за того, что ВПР не различает регистры символов, мы получили другой результат. И это проблема. В этой статье я расскажу, как найти значение с учетом строчных и заглавных букв. Будем использовать функции ИНДЕКС, ПОИСКПОЗ и СОВПАД.

Забегая вперед, скажу, что готовая формула выглядит так:

<=ИНДЕКС( данные ; ПОИСКПОЗ(ИСТИНА ; СОВПАД( значение ; диапазон ) ; 0) ; колонка_для_вывода )>

Здесь используется формула массива. После её ввода нужно нажать не Enter , а Ctrl+Shift+Enter . В противном случае, работать не будет. Фигурные скобки вначале и в конце появятся автоматически, печатать их не нужно!

Формула выглядит немного запутанно, поэтому давайте пошагово разберемся с последовательностью её работы:

значение

диапазон

В массиве будет столько элементов, сколько их в диапазоне с именами. В тех элементах массива, где имена точно совпадают, будет записана ИСТИНА, в остальных – ЛОЖЬ;

В результате такой операции мы получим число 3, т.е. в третьем элементе массива содержится ИСТИНА

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

Возможно, вы знаете, что можете комбинировать что собой представляет ИНДЕКС и МАТЧ функции или используйте ВПР функция для поиска значений в Excel. Однако при поиске регистр не учитывается. Итак, чтобы выполнить сопоставление с учетом регистра, вы должны воспользоваться EXACT a nd ВЫБЕРИТЕ f соборов.

Выполните поиск с учетом регистра с помощью ИНДЕКС и ПОИСКПОЗ

Чтобы знать класс of YUKI как показано на скриншоте выше с ИНДЕКС и ПОИСКПОЗ, вы можете использовать функцию ТОЧНО для сравнения текстовых строк в диапазоне имен учащихся с YUKI, значением в ячейке G5, включая регистр каждого символа. Затем мы можем использовать функции ИНДЕКС и ПОИСКПОЗ вместе, чтобы найти нужное значение.

Общий синтаксис

=INDEX( return_range ,MATCH(TRUE,EXACT( lookup_value , lookup_range ),0))

√ Примечание. Это формула массива, требующая ввода с помощью Ctrl + Shift + Enter.

return_range: Диапазон, из которого вы хотите, чтобы комбинационная формула возвращала значение. Здесь имеется в виду модельный ряд.
lookup_value: Значение EXACT, используемое для выполнения чувствительного к регистру сравнения с текстовыми строками в lookup_range. Здесь имеется в виду данное имя, ЮКИ.
lookup_range: Диапазон ячеек для сравнения с искомое_значение. Здесь имеется в виду диапазон имен.
match_type 0: ПОИСКПОЗ найдет первое значение, которое точно равно искомое_значение.

Чтобы знать класс of YUKI, скопируйте или введите формулу ниже в ячейку G6, и нажмите Ctrl + Shift + Enter чтобы получить результат:

= ИНДЕКС ( D5: D14 , ПОИСКПОЗ (ИСТИНА, ТОЧНО ( "ЮКИ" , B5: B14 ), 0))

Или используйте ссылку на ячейку, чтобы сделать формулу динамической:

= ИНДЕКС ( D5: D14 , ПОИСКПОЗ (ИСТИНА, ТОЧНО ( G5 , B5: B14 ), 0))

Пояснение формулы

=INDEX( D5:D14 , MATCH(TRUE, EXACT("YUKI",B5:B14) ,0) )

ТОЧНЫЙ (G5; B5: B14) : Функция EXACT сравнивает текстовые строки в диапазоне имен учащихся. B5: B14 против "YUKI", значение в ячейке G5, возвращает ИСТИНА, если значение в ячейках от B5 до B14 точно такое же, как YUKI, в противном случае - ЛОЖЬ. Итак, мы получим массив ИСТИНЫ и ЛОЖИ вроде этого:
МАТЧ (ИСТИНА; ТОЧНЫЙ (G5; B5: B14) , 0) = МАТЧ (ИСТИНА; , 0) : Match_type 0 просит функцию ПОИСКПОЗ найти точное совпадение. Затем функция вернет позицию своего точного lookup_value “TURE”В массиве, который 10, так как единственное ИСТИНА находится на 10-я позиция в массиве.
ПОКАЗАТЕЛЬ( D5: D14 , МАТЧ (ИСТИНА; ТОЧНЫЙ (G5; B5: B14) , 0) ) = ИНДЕКС ( D5: D14 , 10 ): Функция ИНДЕКС возвращает 10th значение в диапазоне оценок D5: D14, Которая является A.

Выполните поиск с учетом регистра с помощью ВПР

Чтобы знать страна что ДЖИММИ исходит из функции VLOOKUP, вам следует воспользоваться функциями CHOOSE и EXACT для выполнения поиска с учетом регистра. Функция EXACT будет проводить сравнение ДЖИММИ и каждой текстовой строки в диапазоне имен учащихся с учетом регистра. Затем мы можем использовать ВЫБОР, чтобы объединить ТОЧНЫЙ результат и значения в диапазоне стран. Пришло время VLOOKUP показать свои возможности.

Общий синтаксис

=VLOOKUP(TRUE,CHOOSE(,EXACT( lookup_value , lookup_range ), return_range ),2,FALSE)

√ Примечание. Это формула массива, требующая ввода с помощью Ctrl + Shift + Enter.

lookup_value: Значение EXACT, используемое для выполнения чувствительного к регистру сравнения с текстовыми строками в lookup_range. Здесь имеется в виду данное имя, ДЖИММИ.
lookup_range: Диапазон ячеек для сравнения с искомое_значение. Здесь имеется в виду диапазон имен.
return_range: Диапазон, из которого вы хотите, чтобы комбинационная формула возвращала значение. Здесь имеется в виду диапазон страны.
range_lookup ЛОЖЬ: Функция ВПР будет искать точное совпадение.

Чтобы знать страна ДЖИММИ происходит из, скопируйте или введите формулу ниже в ячейку G9, и нажмите Ctrl + Shift + Enter чтобы получить результат:

= ВПР (ИСТИНА; ВЫБРАТЬ (; ТОЧНО ( "ДЖИММИ" , B5: B14 ), C5: C14 ), 2, ЛОЖЬ)

Или используйте ссылку на ячейку, чтобы сделать формулу динамической:

= ВПР (ИСТИНА; ВЫБРАТЬ (; ТОЧНО ( G8 , B5: B14 ), C5: C14 ), 2, ЛОЖЬ)

Пояснение формулы

=VLOOKUP( TRUE , CHOOSE(, EXACT(G8,B5:B14) ,C5:C14) ,2,FALSE)

ТОЧНЫЙ (G8; B5: B14) : Функция EXACT сравнивает текстовые строки в диапазоне имен учащихся. B5: B14 против значения в ячейке G8, ДЖИММИ, возвращает ИСТИНА, если значение в диапазоне имен учащихся точно такое же, как ДЖИММИ, в противном случае - ЛОЖЬ. Итак, мы получим массив ИСТИНЫ и ЛОЖИ вроде этого:
ВЫБРАТЬ (, ТОЧНЫЙ (G8; B5: B14) , C5: C14) = ВЫБРАТЬ (, , C5: C14) : Аргумент index_num команды CHOOSE 1,2 <> объединяет ТОЧНЫЙ массив и значения из C5: C14 в двумерный массив вроде этого:
ВПР ( ИСТИНА , ВЫБРАТЬ (, ТОЧНЫЙ (G8; B5: B14) , C5: C14) ,2, ЛОЖЬ) = ВПР ( ИСТИНА , , 2, ЛОЖЬ): Range_lookup НЕПРАВДА запрашивает функцию ВПР для поиска точного значения «ИСТИНА”В 1-м столбце двумерного массива и возвращает его точное совпадение в 2ый столбец, который Америка.

Связанные функции

Функция ИНДЕКС Excel возвращает отображаемое значение на основе заданной позиции из диапазона или массива.

Функция ПОИСКПОЗ в Excel ищет определенное значение в диапазоне ячеек и возвращает относительное положение значения.

Функция ВПР в Excel выполняет поиск значения по первому столбцу таблицы и возвращает соответствующее значение из определенного столбца в той же строке.

Функция EXACT сравнивает две строки и возвращает TRUE, если они точно такие же (с учетом чувствительности к регистру), или возвращает FALSE.

Функция ВЫБОР возвращает значение из списка аргументов значений на основе заданного номера индекса. Например, ВЫБОР (3, «Яблоко», «Персик», «Оранжевый») возвращает оранжевый, номер индекса - 3, а оранжевый - третье значение после номера индекса в функции.

Связанные формулы

Если вам нужно найти информацию, указанную в Excel, о конкретном продукте, фильме или человеке и т. Д., Вы должны хорошо использовать комбинацию функций ИНДЕКС и ПОИСКПОЗ.

В этом руководстве вы узнаете, как использовать формулу с функциями СУММПРОИЗВ, ЕЧИСЛО и НАЙТИ для подсчета ячеек, содержащих определенный текст, с учетом верхнего и нижнего регистра.

Лучшие инструменты для работы в офисе

Kutools for Excel - поможет вам выделиться из толпы

Хотите быстро и безупречно выполнять свою повседневную работу? Kutools for Excel предлагает мощные расширенные функции 300 (объединение книг, сумма по цвету, разделение содержимого ячеек, дата преобразования и так далее . ) и экономия 80% времени для вас.

Читайте также: