На чтение 10 мин Просмотров 15.8к. Опубликовано 31.07.2020
Содержание
- 5 thoughts on “ «ВПР» по частичному совпадению ”
- Проверяем условие для полного совпадения текста.
- ЕСЛИ + СОВПАД
- Использование функции ЕСЛИ с частичным совпадением текста.
- ЕСЛИ + ПОИСК
- ЕСЛИ + НАЙТИ
- Примеры использования функции ПОИСКПОЗ в Excel
- Формула для поиска неточного совпадения текста в Excel
- Сравнение двух таблиц в Excel на наличие несовпадений значений
- Поиск ближайшего большего знания в диапазоне чисел Excel
- Особенности использования функции ПОИСКПОЗ в Excel
Спустя катастрофически большой промежуток времени с момента публикации моего последнего поста, решил поделиться супер крутой, на мой взгляд, Excel-формулой, узнав о которой, начинаешь удивляться, как же раньше-то я жил без нее. Но, должен сказать, авторство ее создания не мое, а вероятнее всего принадлежит англоязычному ресурсу, о котором я скажу ниже.
Кто более-менее часто работает с массивами данных в Excel почти наверняка знает про функцию ВПР (см. мою статью) или ИНДЕКС+ПОИСКПОЗ, которые решают достаточно частую задачу по объединению двух наборов данных по каким-либо совпадающим значениям. И действительно, использование этих функций решает задачи по сопоставлению и объединению данных в 90% случаев. Если бы не одно но — данные, по которым производится объединение, действительно должны именно совпадать. Но бывают случаи, когда требуется сопоставление по частичному совпадению. Да, в ВПР есть поиск по приблизительному совпадению, но работает он не совсем прозрачно, а потому предугадать, почему было подобрано одно похожее слово, а не другое, может быть невозможно не просто. Как вы поняли, эту прелюдию я затеял не просто так, а для того, чтобы рассказать, как же решить такую задачу при помощи Excel.
Предположим, у нас есть список товаров, которые надо как-то сгруппировать:
5 thoughts on “ «ВПР» по частичному совпадению ”
На форуме SQL.ru мне подсказали еще одно очень изящное решение этой задачи, посмотреть его можно здесь:
http://www.sql.ru/forum/actualutils.aspx?action=gotomsg&t > Спасибо большое, Казанский (автор совета)!
Игорь, спасибо Вам огромное за эту «бронебойную» формулу. Весь интернет «перелопатила» в поиске решения своей задачи и только Вы мне помогли на 100%. Всё работает как часики. Удачи Вам, успешной работы и ещё больше таких гениальных решений.
Ольга, спасибо большое за Ваш комментарий! Справедливости ради надо сказать, что идея этой формулы не моя, а обнаружил я ее на сайте Exceljet
Игорь, добрый день!
Формула прекрасная, но есть ли какая-нибудь ее вариация, которая может находить и подставлять несколько значений сразу?
Например, в строке указаны два производителя холодильников, LG и Samsung
Можно ли вывести их в ячейку через запятую?
Добрый день, Артём!
Спасибо за ваш комментарий и прошу прощения за медленный ответ. Вопрос интересный, но с ходу у меня на него ответа, увы, нет, а по времени довольно сильно ограничен. Если будет свободное время, попробую поломать голову на эту тему
Рассмотрим использование функции ЕСЛИ в Excel в том случае, если в ячейке находится текст.
Будьте особо внимательны в том случае, если для вас важен регистр, в котором записаны ваши текстовые значения. Функция ЕСЛИ не проверяет регистр – это делают функции, которые вы в ней используете. Поясним на примере.
Проверяем условие для полного совпадения текста.
Проверку выполнения доставки организуем при помощи обычного оператора сравнения «=».
=ЕСЛИ(G2=»выполнено»,ИСТИНА,ЛОЖЬ)
При этом будет не важно, в каком регистре записаны значения в вашей таблице.
Если же вас интересует именно точное совпадение текстовых значений с учетом регистра, то можно рекомендовать вместо оператора «=» использовать функцию СОВПАД(). Она проверяет идентичность двух текстовых значений с учетом регистра отдельных букв.
Вот как это может выглядеть на примере.
Обратите внимание, что если в качестве аргумента мы используем текст, то он обязательно должен быть заключён в кавычки.
ЕСЛИ + СОВПАД
В случае, если нас интересует полное совпадение текста с заданным условием, включая и регистр его символов, то оператор «=» нам не сможет помочь.
Но мы можем использовать функцию СОВПАД (английский аналог — EXACT).
Функция СОВПАД сравнивает два текста и возвращает ИСТИНА в случае их полного совпадения, и ЛОЖЬ — если есть хотя бы одно отличие, включая регистр букв. Поясним возможность ее использования на примере.
Формула проверки выполнения заказа в столбце Н может выглядеть следующим образом:
Как видите, варианты «ВЫПОЛНЕНО» и «выполнено» не засчитываются как правильные. Засчитываются только полные совпадения. Будет полезно, если важно точное написание текста — например, в артикулах товаров.
Использование функции ЕСЛИ с частичным совпадением текста.
Выше мы с вами рассмотрели, как использовать текстовые значения в функции ЕСЛИ. Но часто случается, что необходимо определить не полное, а частичное совпадение текста с каким-то эталоном. К примеру, нас интересует город, но при этом совершенно не важно его название.
Первое, что приходит на ум – использовать подстановочные знаки «?» и «*» (вопросительный знак и звездочку). Однако, к сожалению, этот простой способ здесь не проходит.
ЕСЛИ + ПОИСК
Нам поможет функция ПОИСК (в английском варианте – SEARCH). Она позволяет определить позицию, начиная с которой искомые символы встречаются в тексте. Синтаксис ее таков:
=ПОИСК(что_ищем, где_ищем, начиная_с_какого_символа_ищем)
Если третий аргумент не указан, то поиск начинаем с самого начала – с первого символа.
Функция ПОИСК возвращает либо номер позиции, начиная с которой искомые символы встречаются в тексте, либо ошибку.
Но нам для использования в функции ЕСЛИ нужны логические значения.
Здесь нам на помощь приходит еще одна функция EXCEL – ЕЧИСЛО. Если ее аргументом является число, она возвратит логическое значение ИСТИНА. Во всех остальных случаях, в том числе и в случае, если ее аргумент возвращает ошибку, ЕЧИСЛО возвратит ЛОЖЬ.
В итоге наше выражение в ячейке G2 будет выглядеть следующим образом:
Еще одно важное уточнение. Функция ПОИСК не различает регистр символов.
ЕСЛИ + НАЙТИ
В том случае, если для нас важны строчные и прописные буквы, то придется использовать вместо нее функцию НАЙТИ (в английском варианте – FIND).
Синтаксис ее совершенно аналогичен функции ПОИСК: что ищем, где ищем, начиная с какой позиции.
Изменим нашу формулу в ячейке G2
То есть, если регистр символов для вас важен, просто замените ПОИСК на НАЙТИ.
Итак, мы с вами убедились, что простая на первый взгляд функция ЕСЛИ дает нам на самом деле много возможностей для операций с текстом.
Функция ПОИСКПОЗ в Excel используется для поиска точного совпадения или ближайшего (меньшего или большего заданному в зависимости от типа сопоставления, указанного в качестве аргумента) значения заданному в массиве или диапазоне ячеек и возвращает номер позиции найденного элемента.
Примеры использования функции ПОИСКПОЗ в Excel
Например, имеем последовательный ряд чисел от 1 до 10, записанных в ячейках B1:B10. Функция =ПОИСКПОЗ(3;B1:B10;0) вернет число 3, поскольку искомое значение находится в ячейке B3, которая является третьей от точки отсчета (ячейки B1).
Данная функция удобна для использования в случаях, когда требуется вернуть не само значение, содержащееся в искомой ячейке, а ее координату относительно рассматриваемого диапазона. В случае использования для констант массивов, которые могут быть представлены как массивы элементов «ключ» — «значение», функция ПОИСКПОЗ возвращает значение ключа, который явно не указан.
Например, массив <«виноград»;»яблоко»;»груша»;»слива»>содержит элементы, которые можно представить как: 1 – «виноград», 2 – «яблоко», 3 – «груша», 4 – «слива», где 1, 2, 3, 4 – ключи, а названия фруктов – значения. Тогда функция =ПОИСКПОЗ(«яблоко»;<«виноград»;»яблоко»;»груша»;»слива»>;0) вернет значение 2, являющееся ключом второго элемента. Отсчет выполняется не с 0 (нуля), как это реализовано во многих языках программирования при работе с массивами, а с 1.
Функция ПОИСКПОЗ редко используется самостоятельно. Ее целесообразно применять в связке с другими функциями, например, ИНДЕКС.
Формула для поиска неточного совпадения текста в Excel
Пример 1. Найти позицию первого частичного совпадения строки в диапазоне ячеек, хранящих текстовые значения.
Вид исходной таблицы данных:
Для нахождения позиции текстовой строки в таблице используем следующую формулу:
Из полученного значения вычитается единица для совпадения результата с id записи в таблице.
Сравнение двух таблиц в Excel на наличие несовпадений значений
Пример 2. В Excel хранятся две таблицы, которые на первый взгляд кажутся одинаковыми. Было решено сравнить по одному однотипному столбцу этих таблиц на наличие несовпадений. Реализовать способ сравнения двух диапазонов ячеек.
Вид таблицы данных:
Для сравнения значений, находящихся в столбце B:B со значениями из столбца A:A используем следующую формулу массива (CTRL+SHIFT+ENTER):
Функция ПОИСКПОЗ выполняет поиск логического значения ИСТИНА в массиве логических значений, возвращаемых функцией СОВПАД (сравнивает каждый элемент диапазона A2:A12 со значением, хранящимся в ячейке B2, и возвращает массив результатов сравнения). Если функция ПОИСКПОЗ нашла значение ИСТИНА, будет возвращена позиция его первого вхождения в массив. Функция ЕНД возвратит значение ЛОЖЬ, если она не принимает значение ошибки #Н/Д в качестве аргумента. В этом случае функция ЕСЛИ вернет текстовую строку «есть», иначе – «нет».
Чтобы вычислить остальные значения «протянем» формулу из ячейки C2 вниз для использования функции автозаполнения. В результате получим:
Как видно, третьи элементы списков не совпадают.
Поиск ближайшего большего знания в диапазоне чисел Excel
Пример 3. Найти ближайшее меньшее числу 22 в диапазоне чисел, хранящихся в столбце таблицы Excel.
Вид исходной таблицы данных:
Для поиска ближайшего большего значения заданному во всем столбце A:A (числовой ряд может пополняться новыми значениями) используем формулу массива (CTRL+SHIFT+ENTER):
Функция ПОИСКПОЗ возвращает позицию элемента в столбце A:A, имеющего максимальное значение среди чисел, которые больше числа, указанного в ячейке B2. Функция ИНДЕКС возвращает значение, хранящееся в найденной ячейке.
Для поиска ближайшего меньшего значения достаточно лишь немного изменить данную формулу и ее следует также ввести как массив (CTRL+SHIFT+ENTER):
Особенности использования функции ПОИСКПОЗ в Excel
Функция имеет следующую синтаксическую запись:
=ПОИСКПОЗ( искомое_значение;просматриваемый_массив; [тип_сопоставления])
- искомое_значение – обязательный аргумент, принимающий текстовые, числовые значения, а также данные логического и ссылочного типов, который используется в качестве критерия поиска (для сопоставления величин или нахождения точного совпадения);
- просматриваемый_массив – обязательный аргумент, принимающий данные ссылочного типа (ссылки на диапазон ячеек) или константу массива, в которых выполняется поиск позиции элемента согласно критерию, заданному первым аргументом функции;
- [тип_сопоставления] – необязательный для заполнения аргумент в виде числового значения, определяющего способ поиска в диапазоне ячеек или массиве. Может принимать следующие значения:
- -1 – поиск наименьшего ближайшего значения заданному аргументом искомое_значение в упорядоченном по убыванию массиве или диапазоне ячеек.
- 0 – (по умолчанию) поиск первого значения в массиве или диапазоне ячеек (не обязательно упорядоченном), которое полностью совпадает со значением, переданным в качестве первого аргумента.
- 1 – Поиск наибольшего ближайшего значения заданному первым аргументом в упорядоченном по возрастанию массиве или диапазоне ячеек.
- Если в качестве аргумента искомое_значение была передана текстовая строка, функция ПОИСКПОЗ вернет позицию элемента в массиве (если такой существует) без учета регистра символов. Например, строки «МоСкВа» и «москва» являются равнозначными. Для различения регистров можно дополнительно использовать функцию СОВПАД.
- Если поиск с использованием рассматриваемой функции не дал результатов, будет возвращен код ошибки #Н/Д.
- Если аргумент [тип_сопоставления] явно не указан или принимает число 0, для поиска частичного совпадения текстовых значений могут быть использованы подстановочные знаки («?» — замена одного любого символа, «*» — замена любого количества символов).
- Если в объекте данных, переданном в качестве аргумента просматриваемый_массив, содержится два и больше элементов, соответствующих искомому значению, будет возвращена позиция первого вхождения такого элемента.
Частичное совпадение с ВПР
Бывают случаи, когда вам нужен Excel для извлечения данных на основе частичной информации. Для решения проблемы можно использовать ВПР формула вместе с подстановочными знаками — звездочка (*) и вопросительный знак (?).
Как выполнить частичное совпадение с ВПР?
Чтобы получить информация о товарах и продажах, осуществленных продавцом, в названии которого есть слово «эль», из таблицы, как показано выше, вы должны знать, как использовать подстановочный знак — звездочка (*), который соответствует любому количеству символов. В приведенном выше случае вы можете поместить «эль» между двумя звездочками («* эль *«) для соответствия всем именам, содержащим из. После того, как мы разберемся с использованием подстановочных знаков, мы можем использовать функцию ВПР, чтобы применить частичное совпадение.
Советы для других случаев: сопоставьте имена, начните с эля, используйте «эль *»; Сопоставьте имена, которые закончить элем, используйте «* ale»; Сопоставьте имена, которые в них 2–4 буквы — эль, используйте «? ale *». Обратите внимание, что один вопросительный знак соответствует одному символу.
Общий синтаксис
=VLOOKUP(lookup_value,table_array,column_num,FALSE)
- искомое_значение: Значение VLOOKUP, используемое для частичного совпадения в таблица_массив. Здесь имеется в виду название содержит «эль». В искомое_значение должен быть в крайнем левом столбце таблица_массив.
- таблица_массив: Диапазон ячеек, из которых нужно смотреть.
- номер_столбца: Номер столбца в таблица_массив содержащий возвращаемое значение.
Чтобы получить информация о товарах и продажах, осуществленных продавцом, в названии которого есть слово «эль», скопируйте или введите приведенные ниже формулы в ячейки G7 и G8 и нажмите Enter чтобы получить результат:
Ячейка G7 = ВПР («* эль *»,B5: D11,2,ЛОЖНЫЙ)
Ячейка G8 = ВПР («* эль *»,B5: D11,3,ЛОЖНЫЙ)
Или используйте ссылку на ячейку, чтобы сделать формулы динамическими:
Ячейка G7 = ВПР («*» & G5 & «*»,B5: D11,2,ЛОЖНЫЙ)
Ячейка G8 = ВПР («*» & G5 & «*»,B5: D11,3,ЛОЖНЫЙ)
√ Примечание: чтобы склеить ссылку на ячейку и текст, вы должны использовать амперсанд (&), чтобы соединить их. И текст должен быть заключен в двойные кавычки.
Пояснение формулы
Здесь мы используем формулу ниже в качестве примера:
=VLOOKUP(«*»&G5&»*»,B5:D11,2,FALSE)
- The lookup value «*»&G5&»*» («*ale*») can match any names that contain the string “ale”, no matter what position the string is in the names.
- The range_lookup FALSE asks the VLOOKUP function to find the exact match.
- The VLOOKUP function will then return the value that matches «*»&G5&»*» and is in the 2nd column of the range B5:D11, which is Ruler, Glue, Notebook.
Связанные функции
Функция ВПР в Excel
Функция ВПР в Excel выполняет поиск значения по первому столбцу таблицы и возвращает соответствующее значение из определенного столбца в той же строке.
Связанные формулы
Найдите первое частичное совпадение
Есть случаи, когда вам нужно получить позицию первого частичного совпадения, которое содержит определенное число в диапазоне числовых значений в Excel. В этом случае формула ПОИСКПОЗ и ТЕКСТ, содержащая звездочку (*), подстановочный знак, который соответствует любому количеству символов, окажет вам услугу. И если вам также нужно знать точное значение в этой позиции, вы можете добавить в формулу функцию ИНДЕКС.
Поиск с учетом регистра
Возможно, вы знаете, что можете комбинировать функции ИНДЕКС и ПОИСКПОЗ или использовать функцию ВПР для поиска значений в Excel. Однако при поиске регистр не учитывается. Итак, чтобы выполнить сопоставление с учетом регистра, вы должны воспользоваться функциями EXACT и CHOOSE.
Лучшие инструменты для работы в офисе
Kutools for Excel — Помогает вам выделиться из толпы
Хотите быстро и качественно выполнять свою повседневную работу? Kutools for Excel предлагает 300 мощных расширенных функций (объединение книг, суммирование по цвету, разделение содержимого ячеек, преобразование даты и т. д.) и экономит для вас 80 % времени.
- Разработан для 1500 рабочих сценариев, помогает решить 80% проблем с Excel.
- Уменьшите количество нажатий на клавиатуру и мышь каждый день, избавьтесь от усталости глаз и рук.
- Станьте экспертом по Excel за 3 минуты. Больше не нужно запоминать какие-либо болезненные формулы и коды VBA.
- 30-дневная неограниченная бесплатная пробная версия. 60-дневная гарантия возврата денег. Бесплатное обновление и поддержка 2 года.
Вкладка Office — включение чтения и редактирования с вкладками в Microsoft Office (включая Excel)
- Одна секунда для переключения между десятками открытых документов!
- Уменьшите количество щелчков мышью на сотни каждый день, попрощайтесь с рукой мыши.
- Повышает вашу продуктивность на 50% при просмотре и редактировании нескольких документов.
- Добавляет эффективные вкладки в Office (включая Excel), точно так же, как Chrome, Firefox и новый Internet Explorer.
Mernyy
Пользователь
Сообщений: 7
Регистрация: 10.01.2014
Приветствую пользователей данного ресурса.
Я не являюсь профессиональным пользователем exel, но пользуюсь им ежедневно для ведения определенных баз. Как правило мне достаточно простейших формул, фильтров и выделения повторяющихся значений, но сейчас я столкнулся с определенной задачей и хотел бы у Вас попросить помощи.
Имеется база определенных клиентов, состоящая из нескольких столбцов: почта, ФИО, телефон, и тп. Меня интересует столбец Фамилия, можно ли как-то выявить ячейки в которых есть частичное совпадение. Например: Иванов и Иванова; Сидоров и Сидорова; Petrov и Petrova. Столбец содержит около 1000 строк и каждый день это количество возрастает, поэтому процедуру хотелось бы научиться выполнять самостоятельно. Это нужно для выявления мультирегистраций мошенников.
Спасибо за внимание.
КУРС
EXCEL ACADEMY
Научитесь использовать все прикладные инструменты из функционала MS Excel.
В этой статье мы поговорим, наверное, о самой популярной и удобной функции Excel – ВПР().
Если Вы хоть раз пользовались Excel, то 100% создавали формулу с этой функцией. Сегодня мы еще раз напомним, как эффективно использовать функцию ВПР, и покажем необычные ситуации, где она тоже может пригодиться. О других полезных функциях Excel мы рассказываем в нашем бесплатном гайде, который можно скачать по ссылке.
1. Автономный ВПР
Основная задача функции ВПР – поиск элементов в таблице или диапазоне по строкам. Также есть «симметричная» функция ГПР, которая позволяет искать элементы по столбцам. Все довольно просто: например, можно найти имя клиента по его id или стоимость продукта по его артикулу и так далее.
Тем, кто знаком с SQL, функция ВПР напоминает запрос SELECT c оператором WHERE, где указывается условие поиска.
Самое простое использование функции ВПР:
= ВПР (искомое значение; таблица для поиска; номер столбца, содержащего возвращаемое значение; интервальный просмотр (1/истина или 0/ложь)).
Три первых аргумента являются обязательными. Главное требование к организации данных – искомое значение должно находится в первом столбце таблицы для поиска. (Если речь идет о ГПР, то в первой строке диапазона).
Интервальный просмотр – это необязательный аргумент, который может принимать значения 0 или 1. Единичка означает, что функция будет искать приближенное совпадение, а ноль – точное. Если нет других указаний, то по умолчанию применяется приближенный поиск.
На рассмотренном простом примере мы видим, как работает функция: в выбранном диапазоне в первом столбце она ищет значение, совпадающее со значением из ячейки F2 (ID = 3). Затем, выбирает значение из второго столбца найденной строки. В результате мы получаем цвет, соответствующий заданному ID.
Это все, что касается простого использования ВПР. Давайте посмотрим, в каких еще ситуациях можно использовать данную функцию.
2. Как найти несколько совпадений, используя ВПР
ВПР – крайне удобная функция. Но есть одно «но»: в случае, если в выбранном диапазоне присутствуют несколько совпадений, функция возвращает данные только из первого сопоставленного экземпляра.
И что же делать, если нужно найти значения первого, второго или n-ого соответствия?
Для этой задачи нужно использовать сочетание функций СЧЕТЕСЛИ (COUNTIF) и ВПР (VLOOKUP).
Предположим, у нас есть таблица с именами покупателей, внесенной суммой в кассу и сдачей. Нам необходимо найти все покупки, совершенные одним человеком. Если мы воспользуемся функцией ВПР, то она нам выдаст только первое совпадение. Нужно сделать так, чтобы первый столбец таблицы имел уникальные значения. Создадим его.
Для этого воспользуемся формулой:
А3 = B3&СЧЁТЕСЛИ($B$3:B3;B3)
Благодаря этой формуле мы создали вспомогательный столбец #Имя, который содержит имя клиента и число, которое показывает, сколько раз до этого в таблице появлялось имя клиента – это новое уникальное значение поиска.
Теперь воспользуемся созданным столбцом, чтобы найти все данные о сумме покупок для каждого клиента.
1. Воспользуемся расширенным фильтром, чтобы не переписывать вручную уникальные имена клиентов.
2. Создаем формулу для поиска значений в таблице:
В качестве условия поиска указываем склеенное значение двух ячеек (Имя и номер покупки), чтобы получить значение, как в первом столбце таблицы (#Имя).
Функция ЕСНД необходима для того, чтобы у покупателей, совершивших немного покупок в ячейках стоял 0, а не сообщение об ошибке (#Н/Д).
Замечание! Не забывайте закреплять ячейки и диапазон. Иначе при растягивании формулы все значения съедут и дадут неправильные результаты. К сожалению, не всегда это можно быстро заметить.
Теперь, протянув формулу на все ячейки, мы получили таблицу с данными о суммах покупки каждого клиента и количестве покупок. Теперь можно их с легкостью просуммировать, найти среднее и так далее.
Для решения данной задачи также можно было воспользоваться функциями ИНДЕКС (INDEX) и ПОИСКПОЗ (MATCH):
= ИНДЕКС($A$3:$E$12; ПОИСКПОЗ($G$3&H2;$A$3:$A$12;0);5)
3. Как смотреть налево с VLOOKUP
VLOOKUP – одна из самых мощных функций в Excel. Тем не менее, он имеет несколько недостатков. Одним из них является то, что эта функция возвращает значения, находящиеся справа от столбца поиска и не ищет слева. Но для решения этой проблемы есть обходной путь: комбинация функций VLOOKUP и ВЫБОР (CHOOSE).
Обратимся к таблице из первого примера. Допустим, мы знаем название цвета (Синий), но нам нужно найти его код.
Хитрость здесь состоит в том, чтобы виртуально изменить порядок столбцов в таблице, используя функцию CHOOSE. Многие думают, что эта функция работает только с массивами и одиночными индексами. Но это не так. В нашем случае в качестве диапазона для поиска мы укажем следующее выражение: ВЫБОР({1;2};C3:C7;B3:B7) или ВЫБОР({2;1};B3:B7;C3:C7).
Это выражение выдаст ссылку на два объединенных столбца в порядке Colour; ID. А это именно то, что нам нужно.
В результате мы получим следующую формулу:
Обратите внимание, что в функции ВПР в качестве номера столбца, из которого берется результат мы указываем 2, несмотря на то, в таблице, которая у нас перед глазами, столбец ID является первым. Это все потому, что функция ВЫБОР меняет местами столбцы.
В итоге получаем нужный нам результат:
Также для решения этой задачи можно использовать комбинация функций ИНДЕКС и ПОИСКПОЗ:
=ИНДЕКС(B3:B7; ПОИСКПОЗ(F3;C3:C7;0))
4. Как сделать двусторонний поиск с ВПР
Когда нужно найти значение из таблицы по строке и столбцу, стандартный ВПР сталкивается с проблемой. Однако можно использовать комбинацию функций ВПР и ПОИСКПОЗ, чтобы обойти это узкое место.
Как вы знаете, ВПР возвращает значение, просматривая один столбец, потому что третий аргумент – номер столбца – является статическим значением.
Но с помощью функции ПОИСКПОЗ можно сделать номер столбца также изменяемым параметром.
Например, у нас есть таблица с данными о количествах продаж товара каждого производителя за первые полгода.
Предположим, Вам нужно найти какое количество товара от Производителя5 было проданного в апреле.
В этом примере мы используем формулу для динамического поиска столбцов и необходимой строки с помощью ВПР.
Синтаксис формулы:
=ВПР (искомое значение1; таблица; ПОИСКПОЗ (искомое значение2, столбец, 0), 0)
Функция ПОИСКПОЗ сопоставляет требуемое значение из указанного диапазона и возвращает ссылку на нужный столбец.
В нашем случае формула примет вид:
Эта комбинация делает формулу ВПР более устойчивой. Эта функция может изменяться, когда столбцы вставляются или удаляются из таблицы, но все же сочетание ВПР + ПОИСКПОЗ обеспечит плавную работу даже при внесении изменений в столбцы.
В результате получим:
В качестве альтернативы можно использовать функции ИНДЕКС и ПОИСКПОЗ:
=ИНДЕКС(B3:H7; ПОИСКПОЗ(K2;B3:B7;0); ПОИСКПОЗ(K3;B2:H2;0))
5. Как найти частичное совпадение в Excel с VLOOKUP
Иногда мы сталкиваемся с ситуациями, когда нужно найти информацию из таблицы на основе частичного совпадения. Не всегда в данном случае изменение параметра (интервальный просмотр) выдает правильный результат.
В таком случае можно сделать это с помощью подстановочных знаков в VLOOKUP.
Важное примечание: при частичном сопоставлении (4-тый аргумент – интервальный просмотр) всегда должен быть равен 0, чтобы совпадение с подстановочными знаками работало должным образом.
Рассмотрим пример:
Мы хотим узнать подробности о сотруднике, чье имя начинается с «Ил».
Для решения этой задачи мы воспользуемся классической функцией ВПР, но преобразуем первый аргумент – искомое значение. В этом случае мы предоставляем значение поиска как part_name&”*”. Part_name – это тот кусочек, по которому нужно найти соответствие, а «*» – подстановочный знак. Машина переведет выражение part_name&”*” как «начинается с part_name». В нашем случае part_name = Ил.
В SQL такую задачу можно решить с помощью оператора LIKE “Ил%”
Вот, какая формула у нас получилась:
Для того, чтобы достать всю информацию о сотруднике, нам пришлось написать три формулы, в которых мы изменили третий параметр – номер столбца.
Как видите, мы получили все нужные нам значения, соответствующие нашему частичному поисковому значению, без указания полного имени.
Вы также можете найти значение, которое заканчивается определенным символом или имеет их в середине.
Если присоединить подстановочный знак (“*”), то функция будет искать ячейку со значением, заканчивающимся на part_name.
Если Вам нужно совпадение «содержит part_name», то нужно использовать два подстановочных знака, например, ”*” & part_name & ”*”.
6. Как использовать ВПР с несколькими критериями
VLOOKUP – мощная функция, без сомнения, но в случае поиска на основе двух или более критериев ВПР не справится.
Однако, если у Вас есть доступ и права на изменение данных, то можно воспользоваться вспомогательным столбцом, как в примере 2. Мы объединим несколько полей с помощью амперсанда и используем их в функции. Проще говоря, мы добавим новый столбец, в котором объединятся все критерии в один родительский критерий. Теперь мы сможем применить к данным функцию ВПР.
Давайте рассмотрим пример:
Несмотря на то, что все проектировщики баз данных как Отче наш должны знать три основные нормальные формы баз данных, частенько мы сталкиваемся с таблицами, как представлена в примере.
Она не подчиняется никаким правилам, поэтому с ней трудно проводить различные манипуляции.
Попробуем привести таблицу в более понятный вид.
Предположим, нам нужно найти количество баллов ученицы Ольги по физике. Сначала добавим новый столбец, склеив первый и второй:
Чтобы сделать Вспомогательный столбец более читабельным, мы добавили пробел после имени, используя “ ”.
Теперь запишем функцию ВПР. Так как мы должны искать два условия имя и предмет, то первый аргумент нужно также записать так, как во вспомогательном столбце. Похожую процедуру мы делали во втором примере, когда склеивали имя покупателя с порядковым номером покупки.
Посмотрим, что у нас получилось:
Важно, чтобы функция была настроена на точное совпадение.
Новости
Для подписчиков Microsoft 365, начиная с 2020 июля, доступна новая функция ПРОСМОТРХ (XLOOKUP), которая призвана заменить сразу функции ВПР и ГПР. XLOOKUP отличается от функции ВПР тем, что использует отдельные массивы уточняющих запросов и возвращаемых значений там, где функция ВПР использует один массив таблиц, за которым следует порядковый номер столбца.
Например, старая запись ВПР (В3; B3:D7; 3; ложь) будет соответствовать новой: ПРОСМОТРХ (В3; B2:B7; D2:D7) для следующей таблицы:
Также функция ПРОСМОТРХ даст возможность выводить по значению для искомого значения всю строку. Так, например, для таблицы ниже по ID можно будет вывести и имя сотрудника, и его должность одной формулой.
Заключение
Вот мы и вспомнили 6 случаев, в которых может пригодиться функция ВПР. Эти примеры достаточно простые, но очень важны для понимания работы функции. Если Вы часто используете Эксель в работе и хотите создавать сложные, автоматизированные отчеты, предлагаем Вам пройти обучение в нашей Excel Академии, где Вы найдете еще больше интересных задач и получите дополнительные навыки по работе с функциями Excel.
КУРС
EXCEL ACADEMY FULL PACK
Научитесь использовать все прикладные инструменты из функционала MS Excel.
Автор: Андрон Алексанян, эксперт SF Education
Ссылка на это место страницы:
#title
- Получить первое не пустое значение в списке
- Получить первое текстовое значение в списке
- Получить первое текстовое значение с ГПР
- Получить позицию последнего совпадения
- Получить последнее совпадение содержимого ячейки
- Получить n-е совпадение
- Получить n-ое совпадение с ИНДЕКС/ПОИСКПОЗ
- Получить n-ое совпадение с ВПР
- Если ячейка содержит одну из многих вещей
- Поиск первой ошибки
- Поиск следующего наибольшего значения
- Несколько совпадений в списке, разделенных запятой
- Частичное совпадение чисел с шаблоном
- Частичное совпадение с ВПР
- Положение первого частичного совпадения
- Скачать файл
Ссылка на это место страницы:
#punk01
{ = ИНДЕКС( диапазон ; ПОИСКПОЗ( ЛОЖЬ; ЕПУСТО ( диапазон ); 0 )) }
{ = INDEX( диапазон ; MATCH( FALSE; ISBLANK ( диапазон ); 0 )) }
Если вам нужно получить первое не пустое значение (текст или число) в диапазоне в одной колонке вы можете использовать формулу массива на основе функций ИНДЕКС, ПОИСКПОЗ и ЕПУСТО.
В данном примере мы используем эту формулу:
{ = ИНДЕКС( B3: B11; ПОИСКПОЗ( ЛОЖЬ; ЕПУСТО ( B3: B11 ); 0 )) }
{ = INDEX( B3:B11; MATCH( FALSE; ISBLANK ( B3:B11 ); 0 )) }
Таким образом, суть проблемы заключается в следующем: мы хотим получить первую не пустую ячейку, но для этого нет конкретной формулы в Excel. Мы могли бы использовать ВПР с шаблоном *, но это будет работать только для текста, а не для чисел.
Таким образом, нам нужно строить функциональные возможности для нужных нам формул. Способ сделать это состоит в использовании функции массива, которая «тестирует» ячейки и возвращает массив истина/ложь значения, которые мы можем сопрягать с ПОИСКПОЗ.
Работая изнутри, ЕПУСТО оценивает ячейки в диапазоне В3: В11 и возвращает результат и массив, который выглядит следующим образом:
{ИСТИНА; ЛОЖЬ; ИСТИНА; ЛОЖЬ; ЛОЖЬ; ИСТИНА; ИСТИНА; ИСТИНА; ИСТИНА}
Каждая ЛОЖЬ представляет собой ячейку в диапазоне, который не является пустой.
Далее, ПОИСКПОЗ ищет ЛОЖЬ внутри массива и возвращает позицию первого наденного совпадения, в этом случае 2. На данный момент, формула в примере теперь выглядит следующим образом:
{ = ИНДЕКС( B3: B11; 2; 0 )) }
{ = INDEX( B3:B11; 2; 0 )) }
И, наконец, функция ИНДЕКС выводит значение в положении 2 в массиве, в этом случае число 10.
Ссылка на это место страницы:
#punk02
= ВПР ( «*»; диапазон; 1; ЛОЖЬ)
= VLOOKUP ( «*»; диапазон; 1; FALSE)
Если вам нужно получить первое текстовое значение в списке (диапазон один столбец), вы можете использовать функцию ВПР, чтобы установить точное соответствие, с шаблонным символом для поиска.
В данном примере формула в D7 является:
= ВПР ( «*» ; B5: B11 ; 1 ; ЛОЖЬ)
= VLOOKUP ( «*» ; B5:B11 ; 1 ; FALSE)
Групповой символ звездочка (*) соответствует любому текстовому значению.
Ссылка на это место страницы:
#punk03
= ГПР ( «*»; диапазон; 1; ЛОЖЬ)
= HLOOKUP ( «*»; диапазон; 1; FALSE)
Для поиска и получения первого текстового значения во всем диапазоне столбцов, вы можете использовать функцию ГПР с групповым символом. В примере формула в F5 является:
= ГПР ( «*»; диапазон; 1; ЛОЖЬ)
= HLOOKUP ( «*»; диапазон; 1; FALSE)
Значение поиска является «*», групповым символом, который соответствует одному или более текстовому значению.
Ссылка на это место страницы:
#punk04
= ГПР ( «*»; диапазон; 1; ЛОЖЬ)
= HLOOKUP ( «*»; диапазон; 1; FALSE)
Для того, чтобы получить позицию последнего совпадения (т.е. последнего вхождения) от значения поиска, вы можете использовать формулу, основанную на ЕСЛИ, СТРОКА, ИНДЕКС, ПОИСКПОЗ и MAКС функций.
=МАКС(ЕСЛИ(B4:B11=G5;СТРОКА(B4:B11)-СТРОКА(ИНДЕКС(B4:B11;1;1))+1))
=MAX(IF(B4:B11=G5;ROW(B4:B11)-ROW(INDEX(B4:B11;1;1))+1))
Суть этой формулы состоит в том, что мы строим список номеров строк для данного диапазона, соответствующие по значению, а затем используем функцию MAКС, чтобы получить наибольшее количество строк, что соответствует последнему значению соответствия.
Ссылка на это место страницы:
#punk05
=МАКС(ЕСЛИ(B4:B11=G5;СТРОКА(B4:B11)-СТРОКА(ИНДЕКС(B4:B11;1;1))+1))
=MAX(IF(B4:B11=G5;ROW(B4:B11)-ROW(INDEX(B4:B11;1;1))+1))
Чтобы проверить ячейку для одной из нескольких вещей, и вернуть последнее совпадение, найденное в списке, вы можете использовать формулу, основанную на ПРОСМОТР и ПОИСК функций. В случае нескольких найденных совпадений, формула вернет последнее совпадение из списка «вещей».
=ПРОСМОТР(2;1/ПОИСК($E$4:$E$7;B4);$E$4:$E$7)
=LOOKUP(2;1/SEARCH($E$4:$E$7;B4);$E$4:$E$7)
Ссылка на это место страницы:
#punk06
= НАИМЕНЬШИЙ( ЕСЛИ( логический тест; СТРОКА( список ) — МИН( СТРОКА( список )) + 1 ); n )
= SMALL( IF( логический тест; СТРОКА( список ) — MIN( ROW( список )) + 1 ); n )
Для того, чтобы получить позицию n-го совпадения (например, второе значение соответствия заданному, третье значение соответствия и т.д.), вы можете использовать формулу, основанную на функции НАИМЕНЬШИЙ.
= НАИМЕНЬШИЙ( ЕСЛИ( список = E5 ; СТРОКА( список ) — МИН( СТРОКА( список )) + 1 ); F5 )
= SMALL( IF( список = E5 ; ROW( список ) — MIN( ROW( список )) + 1 ); F5 )
Эта формула возвращает позицию второго появления «красных» в списке.
Сутью этой формулы является функция НАИМЕНЬШИЙ, которая просто возвращает n-е наименьшее значение в списке значений, которое соответствует номеру строки. Номера строк были «отфильтрованы» функцией ЕСЛИ, которая применяет логику для совпадения.
Ссылка на это место страницы:
#punk07
{ = ИНДЕКС( массив; НАИМЕНЬШИЙ( ЕСЛИ( величины = знач ; СТРОКА ( величины ) — СТРОКА ( ИНДЕКС( величины; 1 ; 1 )) + 1 ); n-й )) }
{ = INDEX( массив; SMALL( IF( величины = знач ; ROW ( величины ) — ROW ( INDEX( величины; 1 ; 1 )) + 1 ); n-й )) }
Эта формула возвращает позицию второго появления «красных» в списке.
Сутью этой формулы является функция НАИМЕНЬШИЙ, которая просто возвращает n-е наименьшее значение в списке значений, которое соответствует номеру строки. Номера строк были «отфильтрованы» функцией ЕСЛИ, которая применяет логику для совпадения.
Ссылка на это место страницы:
#punk08
= ВПР( id_формулы; стол; 4; 0 )
= VLOOKUP( id_формулы; стол; 4; 0 )
Чтобы получить n-ое совпадение с ВПР, вам необходимо добавить вспомогательный столбец в таблицу , которая строит уникальный идентификатор , который включает счетчик.
Эта формула зависит от вспомогательного столбца, который добавляется в качестве первого столбца таблицы исходных данных.
Вспомогательный столбец содержит формулу, которая строит уникальное значение взгляда вверх от существующего идентификатора и счетчика. Счетчик подсчитывает сколько раз уникальный идентификатор появился в таблице данных.
В примере, формула ячейки J6 вспомогательного столбца выглядит следующим образом:
=ВПР(J3&»-«&I6;B4:G11;4;0)
=VLOOKUP(J3&»-«&I6;B4:G11;4;0)
Ссылка на это место страницы:
#punk09
{ = ИНДЕКС( результаты ;ПОИСКПОЗ( ИСТИНА ; ЕЧИСЛО( ПОИСК( вещи ; A1 )); 0 )) }
{ = INDEX( результаты ;MATCH( TRUE ; ISNUMBER( SEARCH( вещи ; A1 )); 0 )) }
Чтобы проверить ячейку для одной из нескольких вещей, и вернуть пользовательский результат для первого найденного совпадения, вы можете использовать формулу ИНДЕКС/ПОИСКПОЗ, основанную на функции поиска.
{ = ИНДЕКС( результаты ; ПОИСКПОЗ( ИСТИНА ; ЕЧИСЛО( ПОИСК ( вещи ; B5 )); 0 )) }
= INDEX( результаты ; MATCH( TRUE ; ISNUMBER( SEARCH ( вещи ; B5 )); 0 ))
Эта формула использует два названных диапазона: E5: E8 называется «вещи» и F5: F8 называется «Результаты». Убедитесь, что вы используете диапазоны имен с одинаковыми именами (на основе ваших данных). Если вы не хотите использовать именованные диапазоны, используйте абсолютные ссылки вместо этого.
Ссылка на это место страницы:
#punk10
{ = ПОИСКПОЗ( ИСТИНА ; ЕОШИБКА(диап ); 0 ) }
{ = MATCH( TRUE ; ISERROR(диап ); 0 ) }
Если вам нужно найти первую ошибку в диапазоне ячеек, вы можете использовать формулу массива, основанную на ПОИСКПОЗ и ЕОШИБКА функциях.
В приведенном примере формула:
{ = ПОИСКПОЗ( ИСТИНА ; ЕОШИБКА( B4: B11 ); 0 ) }
{ = MATCH( TRUE ; ISERROR( B4:B11 ); 0 ) }
Работая изнутри, функция ЕОШИБКА возвращает значение ИСТИНА, если значение является признанной ошибкой, и ЛОЖЬ, если нет.
Когда дается диапазон ячеек (массив ячеек) функция ЕОШИБКА будет возвращать массив истина/ложь значений. В примере, это результирующий массив выглядит следующим образом:
{ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ИСТИНА; ЛОЖЬ; ЛОЖЬ}
Обратите внимание, что 6-е значение (что соответствует 6-й ячейке в диапазоне) истинно, так как ячейка В9 содержит #Н/A.
Ссылка на это место страницы:
#punk11
=ИНДЕКС ( данные; ПОИСКПОЗ( поиск ; значения ) + 1 )
=INDEX ( данные; MATCH( поиск ; значения ) + 1 )
Для того, чтобы найти «следующее наибольшее» значение в справочной таблице, можно использовать формулу, основанную на ИНДЕКС и ПОИСКПОЗ. В примере формула в F6 является:
=ИНДЕКС ( данные; ПОИСКПОЗ( поиск ; значения ) + 1 )
=INDEX ( данные; MATCH( поиск ; значения ) + 1 )
Ссылка на это место страницы:
#punk12
{ = ОБЪЕДИНИТЬ ( «;» ; ИСТИНА ; ЕСЛИ( диапазон1 = E5 ; диапазон2 ; «» )) }
{ = ОБЪЕДИНИТЬ ( «;» ; TRUE ; IF( диапазон1 = E5 ; диапазон2 ; «» )) }
Для поиска и извлечения нескольких совпадений, разделенных запятыми (в одной ячейке), вы можете использовать функцию ЕСЛИ с функцией ОБЪЕДИНИТЬ.
{ = ОБЪЕДИНИТЬ( «;» ; ИСТИНА ; ЕСЛИ( группа = E5 ; имя ; «» )) }
Эта формула использует «имя» — именованный диапазон (B5: B11) и «группа» — (C5: C11).
Ссылка на это место страницы:
#punk13
{ = ПОИСКПОЗ( «*» & номер & «*» ; ТЕКСТ( диапазон ; «0» ); 0 ) }
{ = MATCH( «*» & номер & «*» ; TEXT( диапазон ; «0» ); 0 ) }
Для того, чтобы выполнить частичное совпадение (подстроки) против чисел, вы можете использовать формулу массива, основанную на ПОИСКПОЗ и ТЕКСТ.
Excel поддерживает символы подстановки «*» и «?». Тем не менее, если вы используете специальные символы с номером, вы будете преобразовывать числовое значение в текстовое значение. Другими словами, «*» & 99 & «*» = «* 99 *» (текстовая строка).
Если попытаться найти текстовое значение в диапазоне чисел, совпадение завершится неудачно.
Одно из решений заключается в преобразовании чисел в диапазоне поиска для текстовых значений, а затем сделать нормальный поиск с ПОИСКПОЗ, ВПР и т.д.
Другой способ, чтобы преобразовать числа в текст, чтобы сцепить пустую строку. Эта формула работает так же, как выше формуле:
= ПОИСКПОЗ ( «*» & Е5 & «*» ; В5: В10 & «» ; 0 )
= MATCH ( «*» & Е5 & «*» ; В5: В10 & «» ; 0 )
Ссылка на это место страницы:
#punk14
Если вы хотите получить информацию из таблицы на основе частичного совпадения, вы можете сделать это с помощью ВПР в режиме точного соответствия, и групповые символы.
В примере формула ВПР выглядит следующим образом:
=ВПР($H$2&»*»;$B$3:$E$12;2;0)
=VLOOKUP($H$2&»*»;$B$3:$E$12;2;0)
В этой формуле, значение представляет собой именованный диапазон, который относится к Н2, а также данные , представляет собой именованный диапазон , который относится к B3: E102. Без названных диапазонов, формула может быть записана следующим образом:
Ссылка на это место страницы:
#punk15
= ПОИСКПОЗ ( «* текст *» ; диапазон; 0 )
= MATCH ( «* текст *» ; диапазон; 0 )
Для того, чтобы получить позицию первого частичного совпадения (то есть ячейку, которая содержит текст, который вы ищете), вы можете использовать функцию ПОИСКПОЗ со специальными символами.
=ПОИСКПОЗ(«*»&E6&»*»;B5:B10;0)
=MATCH(«*»&E6&»*»;B5:B10;0)
Функция ПОИСКПОЗ возвращает позицию или «индекс» в первом совпадении на основании значения поиска в диапазоне.
ПОИСКПОЗ поддерживает подстановочное согласование со звездочкой «*» (один или несколько символов) или знаком вопроса «?» (один символ), но только тогда, когда третий аргумент, тип_сопоставления, установлен в ЛОЖЬ или ноль.
Ссылка на это место страницы:
#punk16
Файлы статей доступны только зарегистрированным пользователям.
1. Введите свою почту
2. Нажмите Зарегистрироваться
3. Обновите страницу
Вместо этого блока появится ссылка для скачивания материалов.
Привет! Меня зовут Дмитрий. С 2014 года Microsoft Cretified Trainer. Вместе с командой управляем этим сайтом. Наша цель — помочь вам эффективнее работать в Excel.
Изучайте наши статьи с примерами формул, сводных таблиц, условного форматирования, диаграмм и макросов. Записывайтесь на наши курсы или заказывайте обучение в корпоративном формате.
Подписывайтесь на нас в соц.сетях: