впр без учета регистра
4 способа сделать ВПР с учетом регистра в Excel
Этот небольшой урок объясняет, как сделать функцию ВПР (VLOOKUP) чувствительной к регистру, показывает несколько других формул, которые могут искать в Excel с учётом регистра, а также указывает на сильные и слабые стороны каждой функции.
Полагаю, каждый пользователь Excel знает, какая функция осуществляет вертикальный поиск. Правильно, это функция ВПР. Однако, мало кто знает, что ВПР не чувствительна к регистру, то есть символы нижнего и ВЕРХНЕГО регистра для нее идентичны.
Вот быстрый пример, демонстрирующий неспособность ВПР распознать регистр. Предположим, в ячейке A1 содержится значение «bill», а в ячейке A2 – «Bill», формула:
… остановит свой поиск на «bill», поскольку это значение идёт первым в списке, и извлечёт значение из ячейки B1.
Далее в этой статье я покажу способ сделать ВПР чувствительной к регистру. Кроме этого, мы изучим ещё несколько функций, которые могут выполнить поиск в Excel с учётом регистра.
Мы начнём с простейших – ПРОСМОТР (LOOKUP) и СУММПРОИЗВ (SUMPRODUCT), которые, к сожалению, имеют несколько существенных ограничений. Далее мы пристально рассмотрим чуть более сложную формулу ИНДЕКС+ПОИСКПОЗ (INDEX+MATCH), которая работает безукоризненно в любых ситуациях и с любыми наборами данных.
Функция ВПР чувствительная к регистру
Как Вам уже известно, обычная функция ВПР не учитывает регистр. Тем не менее, есть способ сделать её чувствительной к регистру. Для этого необходимо добавить вспомогательный столбец в таблицу, как показано в следующем примере.
Предположим, в столбце B находятся идентификаторы товаров (Item), и Вы хотите извлечь цену товара и соответствующий комментарий из столбцов C и D. Проблема в том, что идентификаторы содержат символы как нижнего, так и верхнего регистров. Например, значения ячеек B4 (001Tvci3u) и B5 (001Tvci3U) отличаются только регистром последнего символа, u и U соответственно.
Как Вы сами догадываетесь, обычная формула поиска
возвратит $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)),»»)
Эта формула разбивает искомое значение на отдельные символы, заменяет каждый символ его кодом (например, вместо A код 65, вместо a код 97), а затем соединяет эти коды в уникальную строку цифр.
После этого используем простую функцию ВПР для поиска с учётом регистра:
Правильная работа функции ВПР с учётом регистра зависит от двух факторов:
Как правильно пользоваться функцией КОДСИМВ
Формула, вставленная в ячейки вспомогательного столбца, предполагает, что все Ваши искомые значения имеют одинаковое количество символов. Если нет, то нужно знать наименьшее и наибольшее количества и добавить столько функций ЕСЛИОШИБКА (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)),»»)
Для функции ПСТР (MID) Вы задаёте следующие аргументы:
ОГРАНИЧЕНИЯ: Функция ВПР – это не лучшее решение для поиска в Excel с учётом регистра. Во-первых, требуется добавление вспомогательного столбца. Во-вторых, формула неплохо справляется, только если данные однородны, или известно точное количество символов в искомых значениях. Если это не Ваш случай, лучше используйте одно из решений, которые мы покажем далее.
Функция ПРОСМОТР для поиска с учётом регистра
Функция ПРОСМОТР (LOOKUP) сродни ВПР, однако её синтаксис позволяет искать с учётом регистра без добавления вспомогательного столбца. Для этого используйте ПРОСМОТР в сочетании с функцией СОВПАД (EXACT).
Если мы возьмём данные из предыдущего примера (без вспомогательного столбца), то с задачей справится следующая формула:
Формула ищет в диапазоне A2:A7 точное совпадение со значением ячейки F2 с учётом регистра и возвращает значение из столбца B той же строки.
Как и ВПР, функция ПРОСМОТР одинаково работает с текстовыми и числовыми значениями, это хорошо видно на снимке экрана внизу:
Важно! Для того, чтобы функция ПРОСМОТР работала правильно, значения в столбце поиска должны быть упорядочены по возрастанию, то есть от меньшего к большему.
Позвольте кратко объяснить, как действует функция СОВПАД в показанной выше формуле, поскольку это ключевой момент.
Функция СОВПАД сравнивает два текстовых значения в 1-ом и 2-ом аргументе и возвращает ИСТИНА (TRUE), если они в точности одинаковы, или ЛОЖЬ (FALSE), если нет. Для нас важным является то, что функция СОВПАД чувствительна к регистру.
Давайте разберёмся, как работает наша формула ПРОСМОТР+СОВПАД:
Надеюсь, это объяснение было понятным и теперь Вам понятна основная идея. Если да, то у Вас не возникнет каких-либо трудностей и с другими функциями, которые мы будем разбирать далее, т.к. все они работают по одинаковому принципу.
ОГРАНИЧЕНИЯ: Данные в столбце поиска должны быть упорядочены по возрастанию.
СУММПРОИЗВ – находит текстовые значения с учётом регистра, но возвращает только числа
Как Вы уже поняли из заголовка, СУММПРОИЗВ (SUMPRODUCT) это ещё одна функция Excel, которая поможет выполнить поиск с учётом регистра, но возвратит только числовые значения. Если этот вариант Вам не подходит, то можете сразу переходить к связке ИНДЕКС+ПОИСКПОЗ, которая даёт решение на любой случай и для любых типов данных.
Для начала, позвольте кратко объяснить синтаксис данной функции, это поможет лучше понять чувствительную к регистру формулу, которая следует далее.
Функция СУММПРОИЗВ перемножает элементы заданных массивов и возвращает сумму результатов. Синтаксис имеет такой вид:
Раз нам необходим поиск с учётом регистра, используем функцию СОВПАД (EXACT) из предыдущего примера в качестве одного из множителей:
Как Вы помните, СОВПАД сравнивает значение ячейки F2 со всеми элементами в столбце A. В случае, если найдено точное совпадение, возвращает ИСТИНА (TRUE), иначе – ЛОЖЬ (FALSE). В математических операциях Excel принимает ИСТИНА (TRUE) за 1, а ЛОЖЬ (FALSE) за 0, далее СУММПРОИЗВ перемножает эти цифры и суммирует полученные результаты.
Нули не считаются, поскольку при умножении они всегда дают 0. Давайте посмотрим подробнее, что происходит, когда точное совпадение в столбце A найдено и возвращена 1. Функция СУММПРОИЗВ умножает число в столбце B на 1 и возвращает результат – точно такое же число! Так происходит потому, что результаты других произведений – нули, и они не влияют на получившуюся в итоге сумму.
К сожалению, функция СУММПРОИЗВ не может работать с текстовыми значениями и датами, так как их нельзя перемножить. В этом случае Вы получите сообщение об ошибке #ЗНАЧ! (#VALUE!), как в ячейке F4 на рисунке ниже:
ОГРАНИЧЕНИЯ: Возвращает только числовые значения.
ИНДЕКС+ПОИСКПОЗ – поиск с учётом регистра для любых типов данных
Наконец мы приблизились к неограниченной по возможностям и чувствительной к регистру формуле поиска, которая работает с любыми наборами данных.
Этот пример идёт последним не потому, что лучшее оставлено на десерт, а потому, что знания, полученные из предыдущих примеров, помогут лучше и быстрее понять чувствительную к регистру формулу ИНДЕКС+ПОИСКПОЗ (INDEX+MATCH).
Как Вы, наверное, догадались, комбинация функций ПОИСКПОЗ и ИНДЕКС используется в Excel как более гибкая и мощная альтернатива для ВПР. Статья Использование ИНДЕКС и ПОИСКПОЗ вместо ВПР прекрасно объяснит Вам, как эти функции работают в паре.
Я лишь напомню ключевые моменты:
Чтобы формула ИНДЕКС+ПОИСКПОЗ могла искать с учётом регистра, к ней нужно добавить лишь одну функцию. Не трудно догадаться, что это снова СОВПАД (EXACT):
В этой формуле СОВПАД работает так же, как и в связке с функцией ПРОСМОТР, и даёт такой же результат:
Заметьте, что формула ИНДЕКС+ПОИСКПОЗ заключена в фигурные скобки – это формула массива, и Вы должны завершить её ввод нажатием Ctrl+Shift+Enter.
Почему ИНДЕКС+ПОИСКПОЗ – это лучшее решение для поиска с учётом регистра?
Главные преимущества связки ИНДЕКС и ПОИСКПОЗ:
Эта формула кажется идеальной, не правда ли? На самом деле, это не так. И вот почему.
Предположим, что ячейка в столбце возвращаемых значений, связанных с искомым значением, пуста. Какой результат возвратит формула? Никакой? Давайте посмотрим, что возвратит формула на самом деле:
Упс, формула возвращает ноль! Это может быть не велика беда, если Вы работаете с чисто текстовыми значениями. Однако, если таблица содержит числа, в том числе «настоящие» нули – это становится проблемой.
На самом деле, все остальные формулы поиска (ВПР, ПРОСМОТР и СУММПРОИЗВ), которые мы обсуждали ранее, ведут себя так же. Но Вы же хотите безупречную формулу, так ведь?
Чтобы сделать чувствительную к регистру формулу ИНДЕКС+ПОИСКПОЗ идеальной, поместите её в функцию ЕСЛИ (IF), которая будет проверять ячейку с возвращаемым значением и возвращать пустой результат, если она пуста:
Рисунки ниже демонстрируют исправленную чувствительную к регистру формулу ИНДЕКС+ПОИСКПОЗ в действии. Она возвращает пустой результат, если возвращаемая ячейка пуста.
Я переписал формулу в столбцы B:D, чтобы строка формул поместилась на скриншоте.
Формула возвращает 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.»)
Поиск позиции ТЕКСТа с учетом РЕгиСТра в EXCEL и выводом значения из соседнего столбца
history 14 апреля 2013 г.
Пусть в диапазоне А4:В15 имеется таблица с перечнем неких артикулов (артикулы ABCD 100 и abcd 100 считаются разными) и соответствующими им наименованиям товаров.
Задача
Решение
Алгоритм решения задачи следующий:
Решение задачи основано на использовании функций чувствительных к РЕгиСТру. См. в файле примера лист Текст.
Решение
Если несколько значений удовл. критерию
берется последнее сверху
берется последнее сверху
Задача подразумевает, что диапазон поиска содержит неповторяющиеся значения (с учетом регистра). В самом деле, если критерию удовлетворяет сразу несколько значений, то из какой строки выводить соответствующее ему значение из соседнего столбца? Если все же диапазон поиска содержит повторяющиеся значения, то второй столбец из таблицы выше поясняет какое значение будет выведено (обычно возвращается первое значение, удовлетворяющее критерию).
Вывод числовых значений
Если в соседнем с артикулом столбце находятся не текстовые значения, а числа (например, цена товара), то решения будут немного отличаться.
Решение
Если несколько значений удовл. критерию
соответствующие значения суммируется
берется последнее сверху
берется последнее сверху
Совет : Для визуальной проверки наличия дубликатов можно использовать Условное форматирование (см. статью Выделение повторяющихся значений (дубликатов) с учетом РЕГИСТРА ).
Поиск текста ВПР по таблице Excel с учетом регистра символов
Функция ВПР не обращает внимания на регистр символов, поэтому если Вы хотите, чтобы Excel учитывал при поиске (Вертикальным ПРосмотром) регистр символов, вам нужно построить для этого правильную формулу.
Как заставить функцию ВПР различать большие и маленькие буквы?
Рассмотрим пример. Сначала ознакомимся с исходными данными:
Поэтому функция СОВПАД будет возвращать значение ЛОЖЬ даже если ее выполнить в массиве. Временно добавим функцию ИЛИ, которая возвращает значение ИСТИНА если хотя бы одно значение в массиве является истинным. Временная формула будет выглядеть так:
Обратите внимание! Данная формула должна выполняться в массиве. Поэтому для ее ввода нажимаем не просто клавишу Enter, а целую комбинацию клавиш CTRL+SHIFT+Enter.
Теперь, на основе выше указанной таблицы, нам нужно создать элемент, который мы будем искать. Относительно простым решением будет перемножение его в массиве с номерами строк данных. Все что вам нужно сделать, это использовать функцию СТРОКА с заданным диапазоном столбца, в котором будет осуществляться поиск. Его результат должен умножить на массив, возвращаемый функцией СОВПАД.
В результате будет таблица с нулями или номерами строк, там, где Excel нашел идентичные текстовые строки.
Нас интересует максимальное значение в этой таблице, поэтому необходимо наши промежуточные вычисления вставляем в функцию МАКС:
Нам удалось найти значение, которое мы будем искать, используя функцию ВПР.
Функция ВЫБОР и ВПР для вертикального поиска значений таблицы
Теперь Вам нужно построить массив, в которой функция будет осуществлять поиск. Для этого Вам понадобится функция ВЫБОР, которая по умолчанию возвращает выбранный аргумент на основании очередности, то есть если Вы введете 1 в качестве первого аргумента, функция ВЫБОР вернет следующий первый аргумент, если Вы введете 2, она вернет следующий второй аргумент и т.д.
Только нам необходимо чтобы функция ВЫБОР возвращала два аргумента одновременно. Чтобы получить такой эффект, Вы должны ввести первый аргумент в виде массива данных: <1\2>. Для такого аргумента функция ВЫБОР возвращает как первый, так и второй выбранный аргумент.
К счастью, функция ВЫБОР не имеет проблем с возвратом диапазонов данных, поэтому нашим первым аргументом будут номера строк в столбце, в котором мы ищем (СТРОКА($A$2:$A$11)), а вторым аргументом будет столбец, из которого мы хотим извлечь информацию ($B$2:$B$11). Поскольку функция ВПР возвращается значения из одного столбца, даже если Ваши исходные данные содержат больше столбцов, достаточно указать в аргументах функции ВЫБОР только два столбца:
Самое сложное позади. Все, что вам нужно сделать, это еще ввести два последних аргумента функции ВПР:
Таким образом, вся формула будет иметь следующий вид:
Помните, что это формула массива, потому что мы функции Excel в качестве аргумента даем массив данных там, где она ожидает одно значение. Поэтому Вы должны «одобрить» фигурными скобками эту формулу нажатием комбинации клавиш Ctrl + Shift + Enter.
Учет верхнего и нижнего регистра в Excel для формулы поиска
Функция ВПР и другие подобные ей функции поиска имеют один недостаток – они не могут различать верхний и нижний регистр символов (большие и маленькие буквы). Данный недостаток может оказаться весьма раздражающим, а иногда существенно усложняющим для определенного рода задач. Если поставленная перед вами задача в Excel требует учитывать регистр символов в тексте значений, тогда функцию ВПР (и подобные ей) следует заменить формулой.
Как заставить формулу Excel различать большие и маленькие буквы
Допустим, что содержимое исходного значения для поиска находится в ячейке D1, а таблица, по которой будет выполнен поиск, находится в диапазоне A1:B10.
Чтобы найти необходимые значения:
Пример таблицы и работы формулы показано на рисунке:
Как видно теперь в критериях поиска учитывается верхний регистр символов.
Внимание! Если таблица не содержит исходное значение для поиска, тогда формула возвращает пустую ячейку. Если же таблица содержит несколько дубликатов исходного значения, тогда формула возвращает последний дубликат. Это противоположный результат функции ВПР, которая при наличии дубликатов возвращает первый из них.
Принцип действия формулы поиска с учетом регистра
Для поиска значения формула использует функцию =СОВПАД(), которая сравнивает два текста. При этому учитывает верхний регистр символов и возвращает логическое значение ИСТИНА, если тексты значений совпали. Иначе будет возвращено логическое значение ЛОЖЬ. Так как мы используем эту функцию в массиве формул, сравнение значения D1 происходит с каждым значением всех ячеек таблицы в диапазоне A1:A10.
Задача функции =ЕСЛИ() – возвращать постой текст, в случаи когда логическое выражение ИЛИ(СОВПАД(A1:A10;D1)) возвращает значение ЛОЖЬ. Пустой текст формула вернет если функция СОВПАД не найдет ни одного совпадения при сравнении с исходным текстом. Если вместо этого значение будет найдено, то в фрагменте формулы: СОВПАД(A1:A10;D1)*СТРОКА(A1:B10) будет выполнен повторный поиск и в результате в память будет возвращен номер строки, которая содержит найденное значение. Здесь используется тот факт, что во врем выполнения арифметических действий логические значения ИСТИНА и ЛОЖЬ заменяются на числа 1 и 0 – соответственно. Поэтому в случаи, когда в процессе поиска текст найден, будет получено значение соответствующие номеру строки (иначе будет равно 0). Из всех полученных номеров строк функция =МАКС() выбирает наибольший и передает его в качестве аргумента для функции =ИНДЕКС(). Эта функция уже возвращает окончательный результат отображения значения ячейки из столбца B соответственной номеру выбранной строки.
Впр без учета регистра
Синтаксис функции ВПР:
Аргументы:
Lookup_value: Значение, которое вы хотите найти. Он должен быть в первом столбце диапазона table_array.
Table_array: Диапазон данных или таблица, в которой находятся столбец значения поиска и столбец значения результата.
Col_index_num: Номер столбца, из которого будет возвращено совпадающее значение. Он начинается с 1 в крайнем левом столбце массива таблиц.
Range_lookup: Логическое значение, определяющее, будет ли функция ВПР возвращать точное или приблизительное совпадение.
Ноты:
Основные примеры ВПР
1. Выполните точное совпадение Vlookup и приблизительное совпадение Vlookup
Обычно, если вы ищете точное совпадение с функцией Vlookup, вам просто нужно использовать FALSE в последнем аргументе.
Например, чтобы получить соответствующие баллы по математике на основе определенных идентификационных номеров, выполните следующие действия:
1. Примените приведенную ниже формулу в пустую ячейку, в которой вы хотите получить результат:
2. Затем перетащите маркер заполнения вниз к ячейкам, которые вы хотите заполнить этой формулой, и вы получите нужные результаты. Смотрите скриншот:
Приблизительное совпадение полезно для поиска значений между диапазонами данных. Если точное совпадение не найдено, приблизительный Vlookup вернет наибольшее значение, которое меньше искомого значения.
Например, если у вас есть данные следующего диапазона, указанных заказов нет в столбце «Заказы», как получить ближайшую скидку в столбце B?
1. Введите следующую формулу в ячейку, в которую вы хотите поместить результат:
2. Затем перетащите маркер заполнения вниз к ячейкам, чтобы применить эту формулу, и вы получите приблизительные совпадения на основе заданных значений, см. Снимок экрана:
Ноты:
2. Выполните Vlookup с учетом регистра в Excel
По умолчанию функция Vlookup выполняет поиск без учета регистра, что означает, что она обрабатывает символы нижнего и верхнего регистра как идентичные. Иногда вам может потребоваться выполнить поиск с учетом регистра в Excel, функции Index, Match и Exact или функции Lookup и Exact могут оказать вам услугу.
Например, у меня есть следующий диапазон данных, в котором столбец идентификатора содержит текстовую строку с верхним или нижним регистром, теперь я хочу вернуть соответствующую математическую оценку данного идентификатора.
Формула 1: использование функций ТОЧНЫЙ, ИНДЕКС, ПОИСКПОЗ
1. Введите или скопируйте приведенную ниже формулу массива в пустую ячейку, в которой вы хотите получить результат:
2, Затем нажмите Shift + Ctrl + Enter одновременно, чтобы получить первый результат, а затем выберите ячейку формулы, перетащите дескриптор заполнения вниз к ячейкам, которые вы хотите заполнить этой формулой, тогда вы получите правильные результаты, которые вам нужны. Смотрите скриншот:
Ноты:
Формула 2: использование функций поиска и точных данных
1. Примените приведенную ниже формулу в пустую ячейку, в которой вы хотите получить результат:
2. Затем перетащите дескриптор заполнения вниз к ячейкам, в которые вы хотите скопировать эту формулу, и вы получите совпадающие значения с учетом регистра, как показано ниже:
Ноты:
3. Значения Vlookup справа налево в Excel
Функция Vlookup всегда ищет значение в крайнем левом столбце диапазона данных и возвращает соответствующее значение из столбца справа. Если вы хотите выполнить обратный Vlookup, что означает поиск определенного значения справа и возврат его соответствующего значения в левом столбце, как показано ниже:
4. Vlookup второе, n-е или последнее совпадающее значение в Excel
Обычно, если при использовании функции Vlookup найдено несколько совпадающих значений, возвращается только первая совпадающая запись. В этом разделе я расскажу о том, как получить второе, n-е или последнее совпадающее значение с помощью функции Vlookup.
Предположим, у вас есть список имен в столбце A, учебный курс, который они приобрели, в столбце B, и теперь вы ищете 2-й или n-й учебный курс, купленный данным клиентом. Смотрите скриншот:
1. Чтобы получить второе или n-е совпадающее значение на основе заданных критериев, примените следующую формулу массива в пустую ячейку:
2, Затем нажмите Shift + Ctrl + Enter вместе, чтобы получить первый результат, а затем выберите ячейку формулы, перетащите дескриптор заполнения вниз к ячейкам, которые вы хотите заполнить этой формулой, и все вторые совпадающие значения на основе заданных имен будут отображены одновременно, см. снимок экрана:
Примечание:
Если вы хотите выполнить vlookup и вернуть последнее совпадающее значение, как показано на скриншоте ниже, это Vlookup и возврат последнего совпадающего значения Учебник может помочь вам получить подробную информацию о последнем совпадающем значении.
5. Vlookup сопоставление значений между двумя заданными значениями или датами
Иногда вам может потребоваться найти значения между двумя значениями или датами и вернуть соответствующие результаты, как показано на скриншоте ниже, в этом случае вы можете использовать функцию LOOKUP и отсортированную таблицу.
1. Во-первых, ваша исходная таблица должна быть отсортированным диапазоном данных. Затем скопируйте или введите следующую формулу в пустую ячейку:
2. Затем перетащите дескриптор заполнения, чтобы заполнить эту формулу в другие нужные вам ячейки, и теперь вы получите все сопоставленные записи на основе заданного значения, см. Снимок экрана:
Ноты:
6. Использование подстановочных знаков для частичных совпадений в функции Vlookup
В Excel подстановочные знаки могут использоваться в функции Vlookup, которая позволяет выполнить частичное совпадение для значения поиска. Например, вы можете использовать Vlookup для возврата совпадающего значения из таблицы на основе части значения поиска.
Предположим, у меня есть ряд данных, как показано на скриншоте ниже, теперь я хочу извлечь оценку на основе имени (а не полного имени). Как можно было решить эту задачу в Excel?
1. Обычная функция Vlookup работает некорректно, вам необходимо объединить текст или ссылку на ячейку с помощью подстановочного знака, скопируйте или введите следующую формулу в пустую ячейку:
2. Затем перетащите дескриптор заполнения, чтобы заполнить эту формулу в другие нужные вам ячейки, и все соответствующие баллы будут возвращены, как показано ниже:
Ноты:
Советы:
7. Значения Vlookup из другого листа
Обычно вам, возможно, придется работать с более чем одним листом, функцию Vlookup можно использовать для поиска данных с другого листа, как и на одном листе.
Например, у вас есть два рабочих листа, как показано на скриншоте ниже, для поиска и возврата соответствующих данных из указанного вами рабочего листа выполните следующие действия:
1. Введите или скопируйте приведенную ниже формулу в пустую ячейку, в которой вы хотите получить совпадающие элементы:
2. Затем перетащите дескриптор заполнения вниз к ячейкам, к которым вы хотите применить эту формулу, и вы получите соответствующие результаты по мере необходимости, см. Снимок экрана:
 |  |  |
Примечание: В приведенной выше формуле:
8. Значения Vlookup из другой книги
В этом разделе мы поговорим о поиске и возврате совпадающих значений из другой книги с помощью функции Vlookup.
Например, первая книга содержит списки продуктов и затрат, теперь вы хотите извлечь соответствующие затраты во второй книге на основе элемента продукта, как показано ниже.
1. Чтобы получить относительную стоимость из другой книги, сначала откройте обе книги, которые вы хотите использовать, а затем примените следующую формулу к ячейке, в которую вы хотите поместить результат:
2. Затем перетащите и скопируйте эту формулу в другие ячейки, которые вам нужны, см. Снимок экрана:
Ноты:
9. Vlookup и вернуть пустой или конкретный текст вместо значения ошибки 0 или # Н / Д
Обычно, когда вы применяете функцию vlookup для возврата соответствующего значения, если соответствующая ячейка пуста, она вернет 0, а если соответствующее значение не найдено, вы получите значение ошибки # N / A, как показано ниже. Вместо отображения значения 0 или # N / A с пустой ячейкой или другим значением, которое вам нравится, это Vlookup возвращает пустое или определенное значение вместо 0 или н / д пошаговое руководство может оказать вам услугу.
Расширенные примеры ВПР
1. Двусторонний поиск с функцией Vlookup (Vlookup в строке и столбце)
Иногда вам может потребоваться двумерный поиск, что означает одновременное выполнение Vlookup как в строке, так и в столбце. Скажем, если у вас есть следующий диапазон данных, и теперь вам может потребоваться получить стоимость для определенного продукта в указанном квартале. В этом разделе будет представлена некоторая формула для работы с этой работой в Excel.
Формула 1: использование функций ВПР и ПОИСКПОЗ
В Excel вы можете использовать комбинацию функций ВПР и ПОИСКПОЗ для двустороннего поиска, примените следующую формулу в пустую ячейку и нажмите Enter ключ для получения результата.
Примечание: В приведенной выше формуле:
Формула 2: использование функций ИНДЕКС и ПОИСКПОЗ
Вот еще одна формула, которая также может помочь вам выполнить двумерный поиск, примените формулу ниже, а затем нажмите Enter ключ для получения нужного вам результата.
Примечание: В приведенной выше формуле:
2. Значение соответствия Vlookup на основе двух или более критериев
Вам легко найти соответствующее значение на основе одного критерия, но если у вас есть два или более критериев, что вы можете сделать? Функции ПРОСМОТР или ПОИСКПОЗ и ИНДЕКС в Excel могут помочь вам быстро и легко решить эту задачу.
Например, у меня есть приведенная ниже таблица данных, чтобы вернуть согласованную цену на основе конкретного продукта и размера, вам могут помочь следующие формулы.
Формула 1: использование функции ПРОСМОТР
Примените приведенную ниже формулу в ячейку, в которой вы хотите получить результат, а затем нажмите клавишу Enter, см. Снимок экрана:
Ноты:
Формула 2: использование функций ИНДЕКСТ и ПОИСКПОЗ
Комбинация функций Index и Match также может использоваться для возврата совпадающего значения на основе нескольких критериев. Скопируйте или введите следующую формулу:
Затем нажмите клавиши Ctrl + Shift + Enter вместе, чтобы получить нужное относительное значение. Смотрите скриншот:
Ноты:
3. Vlookup возвращает несколько совпадающих значений с одним или несколькими условиями
В Excel функция Vlookup ищет значение и возвращает только первое совпадающее значение, если найдено несколько соответствующих значений. Иногда может потребоваться вернуть все соответствующие значения в строке, столбце или отдельной ячейке. В этом разделе будет рассказано о том, как вернуть несколько совпадающих значений с одним или несколькими условиями в книге.
Vlookup все совпадающие значения на основе одного условия по горизонтали:
Для Vlookup и возврата всех совпадающих значений на основе одного конкретного значения по горизонтали общая формула:
1. Примените приведенную ниже формулу в пустую ячейку и нажмите Shift + Ctrl + Enter вместе, чтобы получить первое совпадающее значение, см. снимок экрана:
2. Затем выберите первую ячейку формулы и перетащите маркер заполнения в правые ячейки, пока не отобразится пустая ячейка и не будут извлечены все соответствующие элементы, см. Снимок экрана:
Советы:
Если в возвращенном списке есть повторяющиеся совпадающие значения, чтобы игнорировать дубликаты, используйте эти формулы, затем нажмите Enter чтобы получить первый результат: =IFERROR(INDEX($C$2:$C$20,MATCH($F1,$A$2:$A$20,0)),»») 
Продолжайте вводить эту формулу: =IFERROR(INDEX($C$2:$C$20,MATCH(1,($F1=$A$2:$A$20)*ISNA(MATCH($C$2:$C$20,$F2:F2,0)),0)),»») в ячейку рядом с первым результатом, а затем нажмите Shift + Ctrl + Enter вместе, чтобы получить второй результат, затем перетащите эту формулу в правые ячейки, чтобы получить все другие совпадающие значения, пока не отобразится пустая ячейка, см. снимок экрана:
Vlookup все совпадающие значения на основе двух или более условий по горизонтали:
Для Vlookup и возврата всех совпадающих значений на основе более конкретных значений по горизонтали общая формула:
1. Примените следующую формулу в пустую ячейку, в которую вы хотите вывести результат:
2. Затем выберите ячейку формулы и перетащите маркер заполнения в правые ячейки, пока не отобразится пустая ячейка и не будут возвращены все совпадающие значения на основе определенных критериев, см. Снимок экрана:
Vlookup все совпадающие значения на основе одного условия по вертикали:
Для Vlookup и возврата всех совпадающих значений на основе одного конкретного значения по вертикали общая формула:
1. Скопируйте или введите следующую формулу в ячейку, в которой вы хотите получить результат, и нажмите Shift + Ctrl + Enter вместе, чтобы получить первое совпадающее значение, см. снимок экрана:
2. Затем выберите первую ячейку формулы и перетащите дескриптор заполнения вниз в другие ячейки, пока не отобразится пустая ячейка, а все соответствующие элементы не будут перечислены в столбце, см. Снимок экрана:
Советы:
Чтобы игнорировать дубликаты в возвращаемых совпадающих значениях, используйте следующие формулы: =IFERROR(INDEX($C$2:$C$20,MATCH(0,COUNTIF($F$1:F1,$C$2:$C$20)+($A$2:$A$20<>$E$2),0)),»»)
Затем нажмите Shift + Ctrl + Enter вместе, чтобы получить первое совпадающее значение, а затем перетащите эту ячейку формулы вниз в другие ячейки, пока не отобразится пустая ячейка, и вы получите нужный результат: 
Vlookup все совпадающие значения на основе двух или более условий по вертикали:
Для Vlookup и возврата всех совпадающих значений на основе более конкретных значений по вертикали общая формула:
1. Скопируйте приведенную ниже формулу в пустую ячейку и нажмите Shift + Ctrl + Enter вместе, чтобы получить первый совпавший элемент.
2. Затем перетащите ячейку формулы вниз в другие ячейки, пока не отобразится пустая ячейка, см. Снимок экрана:
Если вы хотите, чтобы Vlookup возвращал несколько совпадающих значений в одну ячейку с указанным разделителем, новая функция TEXTJOIN может помочь вам быстро и легко решить эту задачу.
Vlookup все совпадающие значения на основе одного условия в одну ячейку:
Примените приведенную ниже простую формулу в пустую ячейку и нажмите Shift + Ctrl + Enter ключи вместе, чтобы получить результат:
Советы:
Vlookup все совпадающие значения на основе двух или более условий в одну ячейку:
Чтобы иметь дело с несколькими условиями при возврате всех совпадающих значений в одну ячейку, примените формулу ниже, а затем нажмите Shift + Ctrl + Enter ключи вместе, чтобы получить результат:
Ноты:
1. Функция ОБЪЕДИНЕНИЕ доступна только в Excel 2019 и Office 365.
2. Если вы используете Excel 2016 и более ранние версии, воспользуйтесь функцией, определяемой пользователем, из следующих статей:
4. Vlookup для возврата всей или целой строки сопоставленной ячейки
В этом разделе я расскажу о том, как получить всю строку совпадающего значения с помощью функции Vlookup.
1. Скопируйте или введите приведенную ниже формулу в пустую ячейку, в которую вы хотите вывести результат, и нажмите Enter ключ, чтобы получить первое значение.
2. Затем перетащите ячейку формулы вправо, пока не отобразятся данные всей строки, см. Снимок экрана:
Внимание: В приведенной выше формуле F2 это значение поиска, на основе которого вы хотите вернуть всю строку, A1: D12 диапазон данных, который вы хотите использовать, A1 указывает номер первого столбца в диапазоне данных.
Советы:
Затем перетащите дескриптор заполнения вниз по ячейкам, чтобы получить все совпадающие строки, как показано ниже:
5. Выполните несколько функций Vlookup (вложенный Vlookup) в Excel
Иногда вам может потребоваться поиск значений в нескольких таблицах, если какая-либо из таблиц содержит заданное значение поиска, как показано на скриншоте ниже, в этом случае вы можете объединить одну или несколько функций Vlookup вместе с функцией ЕСЛИОШИБКА для выполнения множественного поиска.
Общая формула для вложенной функции Vlookup:
Примечание:
1. Примените следующую формулу в пустую ячейку, куда вы хотите поместить результат:
2. Затем перетащите дескриптор заполнения вниз к ячейкам, к которым вы хотите применить эту формулу, и все совпавшие значения были возвращены, как показано ниже:
Ноты:
6. Vlookup, чтобы проверить, существует ли значение на основе данных списка в другом столбце
Функция Vlookup также может помочь вам проверить, существуют ли значения на основе другого списка, например, если вы хотите найти имена в столбце C и просто вернуть Да или Нет, если имя найдено или нет в столбце A, как показано ниже. показано.
1. Вставьте следующую формулу в пустую ячейку:
2. Затем перетащите дескриптор заполнения вниз к ячейкам, которые вы хотите заполнить этой формулой, и вы получите нужный результат, см. Снимок экрана:
7. Vlookup и суммирование всех совпавших значений в строках или столбцах
Если вы работаете с числовыми данными, иногда при извлечении совпадающих значений из таблицы вам также может потребоваться суммировать числа в нескольких столбцах или строках. В этом разделе будут представлены некоторые формулы для завершения этой работы в Excel.
Предположим, у вас есть список продуктов с продажами за несколько месяцев, как показано на скриншоте ниже, теперь вам нужно просуммировать все заказы за все месяцы на основе данных продуктов.
Vlookup и просуммируйте первые совпавшие значения в строке:
1. Скопируйте или введите следующую формулу в пустую ячейку и нажмите Shift + Ctrl + Enter ключи вместе, чтобы получить первый результат.
2. Затем перетащите дескриптор заполнения вниз, чтобы скопировать эту формулу в другие нужные ячейки, и все значения в строке первого совпадающего значения были суммированы вместе, см. Снимок экрана:
Vlookup и просуммируйте все совпавшие значения в нескольких строках:
Приведенная выше формула может суммировать значения в строке только для первого совпадающего значения. Если вы хотите просуммировать все совпадения в нескольких строках, используйте следующую формулу, а затем перетащите дескриптор заполнения вниз к ячейкам, которые вы хотите применить к этой формуле, и вы получите желаемый результат, который вам нужен, см. Снимок экрана:
Внимание: В приведенной выше формуле: H2 искомое значение поиска; A2: A9 заголовки строк, содержащие искомое значение; B2: F9 диапазон данных числовых значений, которые вы хотите суммировать.
Vlookup и суммируйте первые совпавшие значения в столбце:
Если вы хотите суммировать общую стоимость за определенные месяцы, как показано на скриншоте ниже.
Примените приведенную ниже формулу в пустую ячейку, а затем перетащите дескриптор заполнения вниз, чтобы скопировать эту формулу в другие ячейки, теперь первые совпавшие значения на основе определенного месяца в столбце были суммированы вместе, см. Снимок экрана:
Внимание: В приведенной выше формуле: H2 искомое значение поиска; B1: F1 заголовки столбцов, содержащие искомое значение; B2: F9 диапазон данных числовых значений, которые вы хотите суммировать.
Vlookup и просуммируйте все совпавшие значения в нескольких столбцах:
Чтобы Vlookup суммировать все совпавшие значения в нескольких столбцах, вы должны использовать следующую формулу:
Внимание: В приведенной выше формуле: H2 искомое значение поиска; B1: F1 заголовки столбцов, содержащие искомое значение; B2: F9 диапазон данных числовых значений, которые вы хотите суммировать.
Если вы хотите суммировать значения, когда вам нужно сопоставить столбец и строку, например, чтобы получить общую стоимость продукта Sweater в месяц Mar, как показано на скриншоте ниже.
Пожалуйста, примените следующую формулу к ячейке, а затем нажмите клавишу Enter, чтобы получить результат, см. Снимок экрана:
8. Vlookup для объединения двух таблиц на основе одного или нескольких ключевых столбцов
В повседневной работе при анализе данных вам может потребоваться собрать всю необходимую информацию в единую таблицу на основе одного или нескольких ключевых столбцов. Чтобы решить эту задачу, функция Vlookup также может оказать вам услугу.
Например, у вас есть две таблицы, первая таблица содержит данные о продуктах и названиях, а вторая таблица содержит продукты и заказы. Теперь вы хотите объединить эти две таблицы, сопоставив общий столбец продуктов в одну таблицу.
Формула 1: Использование функции ВПР
Чтобы объединить две таблицы в одну на основе ключевого столбца, примените следующую формулу в пустую ячейку, в которой вы хотите получить результат, а затем перетащите дескриптор заполнения вниз к ячейкам, которые вы хотите применить к этой формуле, вы будете получить объединенную таблицу со столбцом заказа, присоединяющимся к данным первой таблицы на основе данных ключевого столбца.
Формула 2: использование функций ИНДЕКС и ПОИСКПОЗ
Если ваши общие данные в правой части и возвращенные данные в левом столбце во второй таблице объединить столбец порядка, функция Vlookup не сможет выполнить эту работу. Чтобы искать справа налево, вы можете использовать функции ИНДЕКС и ПОИСКПОЗ, чтобы заменить функцию Vlookup.
Скопируйте или введите приведенную ниже формулу в пустую ячейку, затем скопируйте формулу в столбец, и столбец заказа был присоединен к первой таблице, см. Снимок экрана:
Внимание: В приведенной выше формуле A2 это искомое значение, которое вы ищете, E2: E8 диапазон данных, которые вы хотите вернуть, F2: F8 диапазон поиска, который содержит значение поиска.
Если две таблицы, которые вы хотите объединить, имеют несколько ключевых столбцов, для объединения таблиц на основе этих общих столбцов вам могут помочь функции ИНДЕКС и ПОИСКПОЗ.
Общая формула для объединения двух таблиц на основе нескольких ключевых столбцов:
1. Примените приведенную ниже формулу в пустую ячейку, в которую вы хотите поместить результат, а затем нажмите Shift + Ctrl + Enter вместе, чтобы получить первое совпадающее значение, см. снимок экрана:
Внимание: В приведенной выше формуле ссылки на ячейки представлены на скриншоте ниже:
2Затем выберите первую ячейку формулы и перетащите маркер заполнения, чтобы скопировать эту формулу в другие ячейки по мере необходимости:
9. Соответствующие значения Vlookup на нескольких листах
Вы когда-нибудь пробовали использовать значения Vlookup на нескольких листах? Предположим, у меня есть следующие три листа с диапазоном данных, и теперь я хочу получить часть соответствующих значений на основе критериев из этих трех листов, чтобы получить результат, как показано на скриншоте ниже. В этом случае Значения Vlookup на нескольких листах пошаговое руководство может оказать вам услугу.
Соответствующие значения VLOOKUP сохраняют форматирование ячеек
1. Vlookup для получения форматирования ячеек (цвет ячейки, цвет шрифта) вместе со значением поиска
Как мы все знаем, обычная функция Vlookup может помочь нам вернуть совпадающее значение из другого диапазона данных, но иногда вы можете захотеть вернуть соответствующее значение вместе с форматированием ячейки, таким как цвет заливки, цвет шрифта, стиль шрифта. как показано ниже. В этом разделе будет рассказано о том, как получить форматирование ячейки с возвращенным значением в Excel.
Выполните следующие действия, чтобы найти и вернуть соответствующее значение вместе с форматированием ячейки:
1. На листе, содержащем данные, которые вы хотите использовать Vlookup, щелкните правой кнопкой мыши вкладку листа и выберите Просмотреть код из контекстного меню. Смотрите скриншот:
2. В открытом Microsoft Visual Basic для приложений окно, скопируйте ниже код VBA в окно кода.
Код VBA 1: Vlookup для получения форматирования ячеек вместе со значением поиска
3. Все еще в Microsoft Visual Basic для приложений окна, нажмите Вставить > Модуль, а затем скопируйте приведенный ниже код VBA 2 в окно модуля.
Код VBA 2: Vlookup для получения форматирования ячеек вместе со значением поиска
 | |  |
5, Затем нажмите OK чтобы закрыть диалоговое окно, а затем сохранить и закрыть окно кода, теперь вернитесь на рабочий лист и примените эту формулу: =LookupKeepFormat(E2,$A$1:$C$10,3) в пустую ячейку, в которую вы хотите вывести результат, а затем нажмите клавишу Enter. Смотрите скриншот:
6. Затем выберите первую ячейку результата и перетащите маркер заполнения вниз, чтобы получить все результаты вместе с их форматированием. Смотрите скриншот.
2. Сохранять формат даты из возвращаемого значения Vlookup
Обычно при использовании функции Vloook для поиска и возврата значения соответствующего формата даты, некоторый числовой формат будет отображаться, как показано ниже. Чтобы сохранить формат даты из возвращаемого результата, вы должны заключить функцию ТЕКСТ в функцию Vlookup.
Примените приведенную ниже формулу в пустую ячейку, а затем перетащите дескриптор заполнения, чтобы скопировать эту формулу в другие ячейки, и все совпавшие даты были возвращены, как показано ниже:
3. Vlookup и возврат соответствующего значения с комментарием ячейки
Вы когда-нибудь пробовали, чтобы Vlookup возвращал не только совпадающие данные ячеек, но и комментарий ячейки, а также в Excel, как показано на следующем снимке экрана? Для решения этой задачи вам может помочь следующая функция, определяемая пользователем.
2. Нажмите Вставить > Модуль, затем скопируйте и вставьте следующий код в окно модуля.
Код VBA: Vlookup и возврат соответствующего значения с комментарием ячейки:
3. Затем сохраните и закройте окно кода, введите эту формулу: =vlookupcomment(D2,$A$2:$B$9,2,FALSE) в пустую ячейку, чтобы найти результат, а затем перетащите маркер заполнения, чтобы скопировать эту формулу в другие ячейки, теперь совпадающие значения и комментарии возвращаются сразу, см. снимок экрана:
4. Работайте с текстом и действительными числами в Vlookup
Для работы с текстом и действительными числами в функции Vlookup примените следующую формулу в пустую ячейку, а затем перетащите дескриптор заполнения вниз, чтобы скопировать эту формулу, и вы получите правильные результаты, как показано ниже:
Ноты: