Как пользоваться vlookup: Использование функции ВПР (VLOOKUP) для подстановки значений

Как использовать Vlookup? Классное упражнение в Excel для покупателей

перейти к содержанию

Как использовать VlookupДжефф2021-08-17T10:23:07-04:00

Классное упражнение в Excel для покупателей

PurchTips, выпуск № 64

Знаете ли вы, как использовать ВПР при закупках?

Функция ВПР в Excel используется при работе с большим списком в стиле базы данных. Это позволяет быстро найти значение поля для определенной записи. Лучше всего это проиллюстрировать упражнением …

Вот ситуация: вы покупатель услуг по техническому обслуживанию для компании, имеющей несколько заводов в разных городах США. Одна из ваших обязанностей — обеспечить, чтобы печи на каждом предприятии регулярно проверялись и ремонтировались по мере необходимости. Вы ведете электронную таблицу Excel, в которой перечислены типы печей, которыми вы пользуетесь в каждом городе, даты последнего обслуживания печей, имена поставщиков ремонта в каждом городе и номера телефонов поставщиков. Воспользуйтесь следующей ссылкой, чтобы загрузить электронную таблицу для нашего упражнения: http://www.NextLevelPurchasing.com/vlookup.xls

Когда вам звонят с завода и сообщают о проблеме с печью, вы хотите быстро связаться с поставщиком ремонта. Вы хотите настроить электронную таблицу, чтобы вам просто нужно было ввести название города, и Excel сообщит вам номер телефона поставщика ремонта. Для этого вы можете использовать функцию ВПР в Excel. Формат функции ВПР: = ВПР (lookup_value, table_array, col_index_num, range_lookup)

Вот что означают надписи в скобках … Это может немного сбить с толку, но не торопитесь, читая, и придерживайтесь этого. Все это обретет смысл, когда вы начнете набирать текст чуть позже.

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

table_array: это диапазон ячеек, содержащих ваш список. Формат именования диапазонов в Excel — это левая верхняя ячейка диапазона, за которой следует двоеточие (:), за которым следует нижняя правая ячейка диапазона. В нашем упражнении диапазон A3: E11.

col_index_num: это номер столбца в table_array, из которого должно быть возвращено совпадающее значение. Значение col_index_num, равное 1, возвращает значение в первом столбце table_array; col_index_num, равное 2, возвращает значение второго столбца в table_array и так далее. В нашем упражнении нам нужен номер телефона, который находится в пятом столбце нашей таблицы, поэтому наш col_index_num будет 5.

range_lookup: это значение, которое сообщит Excel, что нужно найти точное или приблизительное совпадение. Если вы хотите найти точное совпадение, значение будет «ложным» (без кавычек). Если вы можете согласиться с обнаружением приблизительного совпадения, значение будет «истинным» или может быть опущено. В нашем случае нам нужно точное совпадение, поэтому мы выберем false для значения.

Теперь мы хотим настроить нашу электронную таблицу так, чтобы мы просто вводили название города в ячейку B1, и Excel заполнял ячейку D1 соответствующим номером телефона. Итак, вот что нужно ввести в ячейку D1 таблицы, которую вы скачали ранее:

= ВПР (B1; A3: E11,5; ложь)

Нажмите клавишу Enter на клавиатуре. «# N / A» появится в ячейке D1. Теперь введите любое название города из списка в ячейку B1 электронной таблицы, затем нажмите клавишу Enter на клавиатуре и посмотрите, как номер телефона появится в ячейке D1. Введите другое название города в ячейку B1, нажмите клавишу Enter и наблюдайте, как Excel находит соответствующий номер телефона. Разве это не крутой способ получить нужную информацию из списка, на который нужно ссылаться снова и снова?

Очевидно, что это не единственный способ использовать ВПР, и он более полезен при работе с большими списками. Но подумайте о больших списках, которые вы часто используете при закупках. Затем поэкспериментируйте со способами использования ВПР, чтобы быстро получить нужную информацию. Повеселись!

Хотите более успешную карьеру в сфере закупок?

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

Получение сертификата SPSM — это действие, которое необходимо предпринять, если вы хотите внедрить самые современные методы закупок в свою организацию и реализовать свой карьерный потенциал. Загрузите Руководство по сертификации SPSM сегодня, чтобы узнать, как начать свой путь к более успешной карьере в сфере закупок!

Скачать

Вам нужна более высокая производительность вашей команды по закупкам?

Вы руководитель отдела закупок, чья команда не достигает ожидаемых результатов? Может быть, экономии недостаточно. Или разочаровывающая производительность базы снабжения. Или недовольные внутренние клиенты.

Вам нужен план повышения производительности, который легко реализовать и быстро дать результаты. NLPA может помочь.

Загрузите нашу техническую документацию «Руководство для руководителя отдела закупок для более успешной команды». Вы узнаете 7 шагов, которые помогут превратить ваших сотрудников в эффективную команду закупок мирового класса.

Скачать

Получаете ли вы максимальную отдачу от членства в NLPA?

Члены NLPA получают больше, чем просто подобные статьи по электронной почте. Став участником, вы также получаете доступ к:

• Стартовый комплект для обучения и сертификации в области закупок
• Последний отчет о зарплатах в сфере управления закупками и поставками
• Журнал Leading-Edge Supply Management ™
• Вебинары только для участников 10 раз в год
• И еще!

Если вы не пользовались этими преимуществами, почему бы не войти в систему и не начать сейчас?

Присоединиться

Ссылка для загрузки страницы

Наверх

Сравнение XLOOKUP и VLOOKUP: какая функция Excel лучше?

Функции XLOOKUP и VLOOKUP в Excel используются для быстрого анализа больших наборов данных. ВПР — это функция, которая используется в Excel уже много лет. Однако есть более новая функция, которую вы должны использовать, которая называется XLOOKUP.

Оба имеют преимущества и ограничения. Мы здесь, чтобы представить различия и помочь вам решить, какой из них больше подходит для вашего аналитического проекта. Проверять Множество полезных приемов Microsoft Excel, которые поразят вашего босса.

Как работает ВПР?

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

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

Давайте посмотрим на пример. Допустим, у вас есть список моделей смартфонов в вашем наборе данных, и вам нужно получить определенную информацию о нем.

Вы можете использовать функцию ВПР, чтобы получить дополнительную информацию об их процессорах и ценах.

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

Как работает XLOOKUP?

XLOOKUP позволяет выполнять горизонтальный и вертикальный поиск, чтобы найти значение в электронной таблице. Функция в основном ищет массив. Если совпадение найдено, возвращается соответствующий элемент из второго диапазона данных (return_array).

Он более эффективен, чем другие параметры поиска, поскольку по умолчанию он содержит точное совпадение. Вы можете использовать необязательные аргументы в функции ([match_mode]), чтобы указать тип необходимых вам значений широкого соответствия — точное совпадение, более низкое или более высокое значение суффикса или совпадение с подстановочным знаком.

Их можно использовать для различных целей, включая финансовый анализ, маркетинг, анализ продаж и т. д. Синтаксис функции XLOOKUP следующий:

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

Следующее руководство познакомит вас с основами и поможет понять, как использовать функцию XLOOKUP в электронной таблице. Эти две функции требуют как минимум трех аргументов.

Ключевые различия между XLOOKUP и VLOOKUP

Хотя между XLOOKUP и VLOOKUP есть много общего, важно понимать различия.

1. Индексный номер столбца

При запуске ВПР вам нужно будет перейти к исходной таблице, чтобы проверить и указать точный номер индекса столбца.

Функция ВПР требует, чтобы параметр номера столбца возвращал правильные результаты. Использование неправильного номера индекса столбца может привести к другому результату на листе или данных либо в виде неправильного значения, либо в виде ошибки «#ССЫЛКА».

Допустим, в приведенном выше примере вам нужно извлечь сведения о процессоре устройства Apple iPhone 12. Мы будем использовать второй набор данных для всех примеров.

Соответственно нам нужно ввести искомое значение, например, Apple iPhone 12 в графу J , который оказался крайним левым столбцом в нашем примере набора данных, соответствующим аргументу table_array в функции (столбцы J и K). Значение должно быть извлечено из второго столбца в table_array, т. е. столбца поиска K, поэтому номер индекса столбца будет равен 2.

XLOOKUP — это динамическая функция, которая инвертирует аргумент номера столбца, что означает, что вам не нужно беспокоиться о подсчете количества столбцов в таблице. Вам нужно будет только указать столбцы поиска, что сэкономит вам много времени и сил.

2. Долговечность и гибкость

После создания модели вы сможете быстро обновить исходную таблицу новыми данными, не беспокоясь о сбое функции. При добавлении или перемещении столбцов с функцией ВПР вам придется переформатировать всю функцию.

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

3. Пользовательские сообщения об ошибках

XLOOKUP удобен в использовании. Это позволяет вам ввести пользовательское текстовое сообщение об ошибке в аргументе [if_not_found], которое появится, если искомое значение не будет найдено.

ВПР не предлагает подобных возможностей. Возможно, вам придется использовать другие функции, такие как ЕСЛИОШИБКА, в качестве обходного пути, если совпадений не найдено. Проверять Основные причины отказаться от Excel и начать использовать Google Sheets.

Почему XLOOKUP лучше, чем VLOOKUP?

Несмотря на использование ВПР, его функциональность более ограничена, чем XLOOKUP. Это делает XLOOKUP предпочтительным выбором для работы с большими наборами данных. Результаты быстрее, а также менее подвержены ошибкам. Давайте разберемся, почему XLOOKUP работает лучше, чем VLOOKUP.

1. XLOOKUP не ограничивается определенными диапазонами данных

XLOOKUP более гибкий, чем VLOOKUP, который может искать значения только в крайнем левом столбце таблицы, и возвращать значения из соответствующих столбцов справа, как мы видели в примере выше. Напротив, модель XLOOKUP требует более простых шагов и может возвращать значения для любого столбца в любом направлении.

2. XLOOKUP по умолчанию использует точное совпадение

По умолчанию функция ВПР Excel предполагает, что найдено приблизительное совпадение. Однако, если для вашего рабочего листа требуется точное совпадение, вы должны ввести «FALSE» в аргументе range_lookup.

Функция XLOOKUP ищет значение поиска в указанном диапазоне, которое по умолчанию является точным совпадением. Если точное совпадение найти не удается, можно изменить аргумент, чтобы найти приблизительное совпадение, которое может быть меньше или больше искомого значения.

Для этого вы можете установить для дополнительного аргумента [match_mode] значение 0 = точное совпадение (по умолчанию), -1 = следующее или наименьшее точное совпадение, 1 = точное или следующее наибольшее совпадение или 2 = совпадение с подстановочным знаком.

3. XLOOKUP может обрабатывать вставки или удаления столбцов.

ВПР — отличная функция для поиска определенных данных из электронной таблицы или другой таблицы. Однако они обеспечивают постоянное масштабирование — если вам нужно обновить источник данных, внести изменения или переставить столбцы, функция вылетит, и вы пропустите ВПР.

С другой стороны, XLOOKUP — более динамичная метрика, которая может обрабатывать изменения столбцов и возвращать точные результаты поиска. Он также поставляется с более конкретными параметрами, такими как массив поиска и массив возврата, вместо аргумента массива таблицы и номера индекса столбца в ВПР.

4. XLOOKUP выполняет вертикальный и горизонтальный поиск

XLOOKUP может находить данные как в горизонтальной, так и в вертикальной матрицах, в отличие от VLOOKUP, который может выполнять только вертикальный поиск данных.

5. XLOOKUP обеспечивает более точное совпадение

Если вы ищете элемент, который ближе, чем вам хотелось бы, XLOOKUP даст вам более точные результаты. Что дает более точные результаты и меньше ложных срабатываний.

Однако с функцией ВПР, если вы хотите найти приблизительное совпадение из электронной таблицы, функция вернет только следующее наименьшее значение ссылки на совпадение. Но XLOOKUP позволяет выполнять более точное приблизительное сопоставление и также возвращать следующее наибольшее или наименьшее значение из таблицы поиска.

6. XLOOKUP ТРЕБУЕТСЯ МЕНЬШЕ ССЫЛОК НА ЯЧЕЙКИ

При использовании XLOOKUP все, что вам нужно, это определить два конкретных диапазона данных — массив или диапазон, из которого вы хотите найти значение (lookup_array), и массив/диапазон, из которого вы хотите получить значение (return_array).

С другой стороны, ВПР требует, чтобы вы указали весь диапазон данных, используя параметр table_array. Это может быть проблемой со сложными наборами данных, потому что, если электронная таблица большая, может быть сложно переупорядочить или выбрать весь диапазон ячеек, из которых вы хотите вернуть значение. Кроме того, чтобы вернуть приблизительное совпадение с помощью функции ВПР, данные в таблице всегда должны быть отсортированы в порядке возрастания.

VLOOKUP или XLOOKUP: какая функция вам подходит?

Итак, XLOOKUP лучше, чем VLOOKUP? При прямом сравнении ответ должен быть положительным. Однако это может быть не так просто для всех пользователей. Например, вы можете предпочесть более старую функцию, если вы особенно хорошо с ней справляетесь, или когда вы найдете применение для конкретной функции, которое подходит именно вам.

Для больших наборов данных XLOOKUP, вероятно, является лучшим выбором для вас из-за его расширенной функциональности. Функция ВПР имеет множество ограничений, которые затрудняют ее использование для сложных данных, и, как следствие, функция КСПР обладает большей гибкостью, что делает ее лучшим выбором для большинства пользователей электронных таблиц. Теперь вы можете просмотреть Навыки Word и Excel, необходимые для успешной работы в офисе.

Источник

Расширенный ВПР в Excel: множественный, двойной, вложенный

Эти примеры научат вас выполнять ВПР по нескольким критериям, возвращать конкретный экземпляр или все совпадения, выполнять динамический ВПР на нескольких листах и ​​т. д.

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

Прежде чем двигаться дальше, позвольте мне кратко напомнить вам синтаксис:

ВПР(значение_просмотра, массив_таблицы, номер_индекса_столбца, [диапазон_просмотра])

Теперь, когда все находятся на одной странице, давайте подробнее рассмотрим примеры расширенной формулы ВПР:

Как выполнить ВПР по нескольким критериям

Функция Excel VLOOKUP очень полезна, когда речь идет о поиске в базе данных определенного значения. Однако ему не хватает важной функции — его синтаксис допускает только одно значение поиска. Но что, если вы хотите найти несколько условий? Есть несколько различных решений на ваш выбор.

Формула 1. ВПР с двумя критериями

Предположим, у вас есть список заказов и вы хотите найти количество на основе 2 критериев: Имя клиента и Продукт . Усложняющим фактором является то, что каждый клиент заказывал несколько продуктов, как показано в таблице ниже:

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

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

Итак, добавьте столбец слева от таблицы и скопируйте приведенную ниже формулу в этот столбец. Это заполнит вспомогательный столбец значениями из столбцов B и C (символ пробела соединен между ними для лучшей читаемости):

=B2&""&C2

Затем используйте стандартную формулу ВПР и поместите оба критерия в аргумент искомое_значение , разделенные пробелом:

=ВПР("Джереми Свитс", A2:D11, 4, ЛОЖЬ)

Или введите критерии в отдельные ячейки (в нашем случае G1 и G2) и соедините эти ячейки:

=ВПР(G1&""&G2, A2:D11, 4, ЛОЖЬ)

Поскольку мы хотим вернуть значение из столбца D, который является четвертым в массиве таблиц, мы используем 4 для col_index_num . Аргумент range_lookup имеет значение FALSE для Vlookup точного совпадения. На скриншоте ниже показан результат:

Если ваша таблица поиска находится на другом листе , включите имя листа в формулу ВПР. Например:

= ВПР(G1&" "&G2, Заказы!A2:D11, 4, ЛОЖЬ)

В качестве альтернативы создайте именованный диапазон для таблицы поиска (скажем, Orders ), чтобы упростить чтение формулы:

=ВПР(G1&""&G2, Заказы, 4, ЛОЖЬ)

Дополнительные сведения см. в разделе Как выполнить визуальный поиск с другого листа в Excel.

Примечание. Чтобы формула работала правильно, значения во вспомогательном столбце должны быть объединены точно так же, как в аргументе lookup_value . Например, мы использовали символ пробела для разделения критериев как во вспомогательном столбце (B2&» «&C2), так и в формуле ВПР (G1&» «&G2).

Формула 2. ВПР Excel с несколькими условиями

Теоретически описанный выше подход к ВПР можно использовать более чем по двум критериям. Однако есть пара предостережений. Во-первых, значение поиска ограничено 255 символами, а во-вторых, дизайн рабочего листа может не позволять добавлять вспомогательный столбец.

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

Например, для поиска по трем различным значениям ( Дата , Имя клиента и Продукт ) используйте одну из следующих формул:

=ИНДЕКС(D2:D11, ПОИСКПОЗ(1, (G1=A2:A11) * (G2=B2:B11) * (G3=C2:C11), 0))

=XLOOKUP(1, (G1=A2:A11) * (G2=B2:B11) * (G3=C2:C11), D2:D11)

Где:

• G1 – критерий 1 (дата)
• G2 — критерий 2 (имя заказчика)
• G3 – критерий 3 (продукт)
• A2:A11 — диапазон поиска 1 (даты)
• B2:B11 — диапазон поиска 2 (имена клиентов)
• C2:C11 — диапазон поиска 3 (продукты)
• D2:D11 — диапазон возврата (количество)

Примечание. Во всех версиях, кроме Excel 365, ПОИСКПОЗ ИНДЕКС следует вводить как формулу массива CSE, нажав Ctrl + Shift + Enter. В Excel 365, который поддерживает динамические массивы, он также работает как обычная формула.

Подробное объяснение формул см.:

• XLOOKUP с несколькими критериями
• Формула INDEX MATCH с несколькими критериями

Как использовать функцию ВПР для получения совпадения 2

^nd , 3 ^rd или n ^th с

Как вы уже знаете, ВПР Excel может выбрать только одно совпадающее значение, точнее, возвращает первое найденное совпадение. Но что, если в вашем массиве поиска есть несколько совпадений, и вы хотите получить экземпляр 2 ^nd или 3 ^rd ? Задача звучит довольно запутанно, но решение существует!

Формула 1. ВПР N-й экземпляр

Предположим, у вас есть имена клиентов в одном столбце, продукты, которые они приобрели, в другом, и вы ищете продукт 2 ^nd или 3 ^rd , купленный данным клиентом.

Самый простой способ — добавить вспомогательный столбец слева от таблицы, как мы сделали в первом примере. Но на этот раз мы заполним его именами клиентов и номерами экземпляров, такими как « John Doe1 », « John Doe2 » и т. д.

Чтобы получить вхождение, используйте функцию СЧЁТЕСЛИ со ссылкой на смешанный диапазон (первая ссылка является абсолютной, а вторая — относительной, например, $B$2:B2). Поскольку относительная ссылка изменяется в зависимости от положения ячейки, в которую копируется формула, в строке 3 она станет $B$2:B3, в строке 4 — $B$2:B4 и так далее.

В сочетании с именем клиента (B2) формула принимает следующий вид:

=B2&СЧЁТЕСЛИ($B$2:B2, B2)

Приведенная выше формула помещается в ячейку A2, а затем вы копируете ее во столько ячеек, сколько необходимо.

После этого введите целевое имя и номер вхождения в отдельные ячейки (F1 и F2) и используйте приведенную ниже формулу для поиска конкретного вхождения:

=ВПР(F1&F2, A2:C11, 3, ЛОЖЬ)

Формула 2.

ВПР 2-е вхождение

Если вы ищете 2-й экземпляр искомого значения, вы можете обойтись без вспомогательного столбца. Вместо этого создайте массив таблиц динамически, используя функцию ДВССЫЛ вместе с ПОИСКПОЗ:

=ВПР(E1, НЕПРЯМАЯ("A"&(СООТВЕТСТВИЕ(E1, A2:A11, 0)+2)&":B11"), 2, ЛОЖЬ)

Где:

• E1 — это значение поиска
• A2:A11 — это диапазон поиска

.

• B11 — последняя (нижняя правая) ячейка справочной таблицы

Обратите внимание, что приведенная выше формула написана для конкретного случая, когда ячейки данных в таблице поиска начинаются со строки 2. Если ваша таблица находится где-то в середине листа, используйте эту универсальную формулу, где A1 — это верхний левый ячейка таблицы поиска, содержащая заголовок столбца:

=ВПР(E1, НЕПРЯМАЯ("A"&(СООТВЕТСТВИЕ(E1, A2:A11, 0)+1+СТРОКА(A1))&":B11"), 2, ЛОЖЬ)

Как работает эта формула

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

.

ДВССЫЛ ("A"&(СООТВЕТСТВИЕ(E1, A2:A11, 0)+2)&":B11")

Функция ПОИСКПОЗ, настроенная на точное совпадение (0 в последнем аргументе), сравнивает целевое имя (E1) со списком имен (A2:A11) и возвращает позицию первого найденного совпадения, в нашем случае это 3. Это число будет использоваться в качестве начальной координаты строки для диапазона vlookup, поэтому мы добавляем к нему 2 (+1, чтобы исключить первый экземпляр, и +1, чтобы исключить строку 1 с заголовками столбцов). Кроме того, вы можете использовать 1+ROW(A1) для автоматического расчета необходимой корректировки на основе положения строки заголовка (в нашем случае A1).

В результате мы получаем следующую текстовую строку, которую ДВССЫЛ преобразует в ссылку на диапазон:

ДВССЫЛ ("A"&5&":B11") -> A5:B11

Этот диапазон переходит к аргументу table_array функции ВПР, заставляя ее начать поиск в строке 5, пропуская первый экземпляр искомого значения:

ВПР(E1, A5:B11, 2, ЛОЖЬ)

Как выполнять ВПР и возвращать несколько значений в Excel

Функция ВПР Excel предназначена для возврата только одного совпадения. Есть ли способ использовать Vlookup для нескольких экземпляров? Да, есть, хоть и не из легких. Это требует комбинированного использования нескольких функций, таких как ИНДЕКС, МАЛЕНЬКИЙ и СТРОКА — формула массива.

Например, приведенный ниже код может найти все вхождения искомого значения F2 в диапазоне поиска B2:B16 и вернуть несколько совпадений из столбца C:

{=ЕСЛИОШИБКА(ИНДЕКС($C$2:$C$11, МАЛЕНЬКИЙ(ЕСЛИ($F$1=$B$2:$B$11, СТРОКА($C$2:$C$11)-1,""), СТРОКА ()-1)),"")}

Есть 2 способа ввести формулу на лист:

1. Введите формулу в первую ячейку, нажмите Ctrl + Shift + Enter, а затем перетащите ее вниз еще на несколько ячеек.
2. Выберите несколько соседних ячеек в одном столбце (F1:F11 на скриншоте ниже), введите формулу и нажмите Ctrl + Shift + Enter, чтобы завершить ее.

В любом случае количество ячеек, в которые вы вводите формулу, должно быть больше или равно максимально возможному количеству совпадений.

Подробное объяснение логики формулы и другие примеры см. в статье Как выполнять ВПР для нескольких значений в Excel.

Как выполнять поиск в строках и столбцах (двусторонний поиск)

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

В этом примере мы возьмем приведенную ниже таблицу с ежемесячными продажами и разработаем формулу ВПР для получения данных о продажах определенного товара в заданном месяце.

С именами элементов в A2:A9, названиями месяцев в B1:F1, целевым элементом в I1 и целевым месяцем в I2 формула выглядит следующим образом:

= ВПР(I1, A2:F9, ПОИСКПОЗ(I2, A1:F1, 0), ЛОЖЬ)

Как работает эта формула

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

.

СОВПАДЕНИЕ(I2, A1:F1, 0)

В переводе на английский формула гласит: найдите значение I2 в A1:F1 и верните его относительное положение в массиве. Указав 0 в третьем аргументе, вы указываете ПОИСКПОЗ найти значение, в точности равное искомому значению (это похоже на использование ЛОЖЬ для аргумента range_lookup функции ВПР).

Поскольку март находится в 4-м столбце массива поиска, функция ПОИСКПОЗ возвращает 4, что соответствует непосредственно 9-му столбцу.0003 col_index_num аргумент функции ВПР:

ВПР(I1, A2:F9, 4, ЛОЖЬ)

Обратите внимание, хотя названия месяцев начинаются в столбце B, мы используем A1:I1 для массива поиска. Это делается для того, чтобы число, возвращаемое функцией ПОИСКПОЗ, соответствовало положению столбца в table_array функции ВПР.

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

Как сделать множественный ВПР в Excel (вложенный ВПР)

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

На рисунке ниже показана ситуация:

Цель состоит в том, чтобы скопировать цены в основную таблицу на основе идентификаторов товаров . Проблема в том, что в таблице с ценами нет идентификаторов товаров , а это означает, что нам придется выполнять два ВПР в одной формуле.

Для удобства сначала создадим пару именованных диапазонов:

• Интерполяционная таблица 1 названа Продукты (D3:E10)
• Таблица поиска 2 названа Цены ( G3:h20 )

Таблицы могут находиться на одном или разных рабочих листах.

А теперь выполним так называемый двойной ВПР , он же вложенный ВПР .

Сначала создайте формулу ВПР, чтобы найти название продукта в таблице поиска 1 (с именем Продукты ) на основе идентификатора элемента (A3):

= ВПР(A3, Произведения, 2, ЛОЖЬ)

Затем поместите приведенную выше формулу в аргумент lookup_value другой функции ВПР, чтобы получить цены из таблицы поиска 2 (с именем Prices ) на основе имени продукта, возвращенного вложенной функцией ВПР:

=ВПР(ВПР(A3, Товары, 2, ЛОЖЬ), Цены, 2, ЛОЖЬ)

На приведенном ниже снимке экрана показана наша вложенная формула Vlookup в действии:

Как динамически выполнять Vlookup на нескольких листах

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

Это может быть легче понять на примере. Допустим, у вас есть несколько региональных отчетов о продажах в одном формате, и вы хотите получить данные о продажах определенного продукта в определенных регионах:

Как и в предыдущем примере, мы начинаем с определения нескольких имен:

• Диапазон A2:B5 на листе CA называется CA_Sales .
• Диапазон A2:B5 на листе FL называется FL_Sales .
• Диапазон A2:B5 на листе KS называется KS_Sales .

Как видите, все именованные диапазоны имеют общую часть ( Sales ) и уникальные части ( CA , FL , KS ). Обязательно назовите свои диапазоны аналогичным образом, так как это важно для формулы, которую мы собираемся построить.

Формула 1. НЕПРЯМАЯ ВПР для динамического извлечения данных из разных листов

Если ваша задача состоит в извлечении данных из нескольких листов, формула ВПР ДВССЫЛ — лучшее решение — компактное и простое для понимания.

Для этого примера мы организуем сводную таблицу следующим образом:

• Введите интересующие продукты в A2 и A3. Это наши значения поиска.
• Введите уникальные части именованных диапазонов в B1, C1 и D1.

Теперь мы объединяем ячейку, содержащую уникальную часть (B1), с общей частью («_Sales») и передаем полученную строку в ДВССЫЛ:

НЕПРЯМОЙ(B$1&"_Продажи")

Функция ДВССЫЛ преобразует строку в имя, понятное Excel, и вы помещаете его в аргумент table_array функции ВПР:

=ВПР($A2, ДВССЫЛ(B$1&"_Продажи"), 2, ЛОЖЬ)

Приведенная выше формула переходит в ячейку B2, а затем вы копируете ее вниз и вправо.

Обратите внимание, что в искомом значении ($A2) мы заблокировали координату столбца с абсолютной ссылкой на ячейку, чтобы столбец оставался фиксированным при копировании формулы вправо. В ссылке B$1 мы заблокировали строку, потому что мы хотим, чтобы координата столбца изменялась, и снабжали соответствующей частью имени ДВССЫЛ в зависимости от столбца, в который копируется формула:

Если ваша основная таблица организована по-другому, значения поиска в строке и уникальные части имен диапазонов в столбце, то вы должны заблокировать координату строки в значении поиска (B$1) и координату столбца в частях имени ( $А2):

=ВПР(B$1, ДВССЫЛ($A2&"_Продажи"), 2, ЛОЖЬ)

Формула 2.

ВПР и вложенные ЕСЛИ для поиска нескольких листов

В ситуации, когда у вас всего два или три листа поиска, вы можете использовать довольно простую формулу ВПР с вложенными функциями ЕСЛИ, чтобы выбрать правильный лист на основе значения ключа в конкретной ячейке:

= ВПР($A2, ЕСЛИ(B$1="CA", CA_Продажи, ЕСЛИ(B$1="FL", FL_Продажи, ЕСЛИ(B$1="KS", KS_Продажи,""))), 2, ЛОЖЬ )

Где $A2 — значение поиска (имя элемента), а B$1 — значение ключа (состояние):

В этом случае вам не обязательно определять имена, и вы можете использовать внешние ссылки для ссылки на другой лист или книгу.

Дополнительные примеры формул см. в статье Как выполнять ВПР на нескольких листах в Excel.

Вот как использовать ВПР в Excel. Я благодарю вас за чтение и надеюсь увидеть вас в нашем блоге на следующей неделе!

Практическая рабочая тетрадь для скачивания

Примеры расширенной формулы ВПР (файл .xlsx)

Вас также может заинтересовать

Как использовать функцию ВПР в Microsoft Excel [+ Видеоруководство]

Координация большого количества данных в Microsoft Excel — это трудоемкая головная боль. Эта головная боль может усугубиться, когда вам нужно сравнить данные из нескольких электронных таблиц. Последнее, что вам нужно сделать, это вручную перенести ячейки с помощью копирования и вставки. К счастью, вам не нужно. Функция ВПР может помочь вам автоматизировать эту задачу и сэкономить массу времени.

Я знаю, «функция ВПР» звучит как самая сложная и сложная вещь. Но к тому времени, когда вы закончите читать эту статью, вы удивитесь, как вы вообще выживали в Excel без него.

Функция ВПР Microsoft Excel проще в использовании, чем вы думаете. Более того, это невероятно мощное средство, и вы определенно хотели бы иметь его в своем арсенале аналитического оружия. Что именно делает ВПР? Вот простое объяснение: функция ВПР ищет определенное значение в ваших данных, и как только она идентифицирует это значение, она может найти и отобразить некоторую другую часть информации, связанную с этим значением.

Как работает функция ВПР?

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

Формула всегда ищет справа.

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

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

Формуле требуется уникальный идентификатор для извлечения данных.

Секрет работы функции ВПР? Уникальные идентификаторы.

Уникальный идентификатор — это фрагмент информации, который используется обоими источниками данных, и, как следует из названия, он уникален (т. е. идентификатор связан только с одной записью в вашей базе данных). Уникальные идентификаторы включают коды продуктов, складские единицы (SKU) и контакты клиентов.

Хорошо, достаточно объяснений: давайте посмотрим еще один пример ВПР в действии!

ВПР Пример

В видео ниже мы покажем пример в действии, используя функцию ВПР для сопоставления адресов электронной почты (из второго источника данных) с соответствующими данными на отдельном листе.

Примечание автора: Существует много разных версий Excel, поэтому то, что вы видите на видео выше, может не всегда точно совпадать с тем, что вы видите в своей версии. Вот почему мы рекомендуем вам следовать приведенным ниже письменным инструкциям.

Как использовать функцию ВПР в Excel

1. Определите столбец ячеек, который вы хотите заполнить новыми данными.
2. Выберите «Функция» (Fx) > ВПР и вставьте эту формулу в выделенную ячейку.
3. Введите значение поиска, для которого вы хотите получить новые данные.
4. Введите массив таблиц электронной таблицы, в которой находятся нужные данные.
5. Введите номер столбца данных, которые должны возвращать Excel.
6. Введите поисковый диапазон, чтобы найти точное или приблизительное совпадение с вашим поисковым значением.
7. Нажмите «Готово» (или «Ввод») и заполните новый столбец.

Для справки, вот как выглядит функция ВПР:

ВПР(искомое_значение, массив_таблиц, номер_индекса_столбцов, диапазон_просмотра)

В следующих шагах мы присвоим правильное значение каждому из этих компонентов, используя имена клиентов как наш уникальный идентификатор для определения MRR каждого клиента.

1. Определите столбец ячеек, который вы хотите заполнить новыми данными.

Помните, что вы хотите извлечь данные из другого листа и поместить их в этот. Имея это в виду, пометьте столбец рядом с ячейками, о которых вы хотите получить дополнительную информацию, с правильным заголовком в верхней ячейке, например «MRR», для ежемесячного регулярного дохода. В этом новом столбце будут храниться данные, которые вы извлекаете.

2. Выберите «Функция» (Fx) > ВПР и вставьте эту формулу в выделенную ячейку.

Слева от текстовой строки над электронной таблицей вы увидите небольшой значок функции, который выглядит как скрипт: Fx . Щелкните первую пустую ячейку под заголовком столбца, а затем щелкните значок этой функции. Поле под названием Построитель формул или Функция вставки появится справа от экрана (в зависимости от того, какая у вас версия Excel).

Найдите и выберите «ВПР» из списка параметров, включенных в построитель формул. Затем выберите OK или Вставьте функцию , чтобы начать создание ВПР. Ячейка, которую вы сейчас выделили в своей электронной таблице, теперь должна выглядеть так: » =VLOOKUP() »

Вы также можете ввести эту формулу в вызов вручную, введя жирный текст выше точно в нужную ячейку.

Когда в первую ячейку введен текст =ВПР, пришло время заполнить формулу четырьмя различными критериями. Эти критерии помогут Excel точно определить, где находятся нужные вам данные и что искать.

3. Введите значение поиска, для которого вы хотите получить новые данные.

Первый критерий — это ваше значение для поиска — это значение вашей электронной таблицы, с которой связаны данные, которые вы хотите, чтобы Excel нашел и вернул вам. Чтобы ввести его, щелкните ячейку, содержащую значение, для которого вы пытаетесь найти совпадение. В нашем примере, показанном выше, он находится в ячейке A2. Вы начнете переносить новые данные в D2, так как эта ячейка представляет MRR имени клиента, указанного в A2.

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

4. Введите массив таблиц электронной таблицы, где находятся нужные данные.

Рядом с полем «массив таблиц» введите диапазон ячеек, которые вы хотите найти, и лист, на котором эти ячейки расположены, используя формат, показанный на снимке экрана выше. Запись выше означает, что данные, которые мы ищем, находятся в электронной таблице под названием «Страницы» и могут быть найдены в любом месте между столбцами B и столбцами K.

Лист, на котором находятся ваши данные, должен находиться в вашем текущем файле Excel. Это означает, что ваши данные могут находиться в другой таблице ячеек где-то в вашей текущей электронной таблице, или в другой электронной таблице, связанной внизу вашей книги, как показано ниже.

Например, если ваши данные расположены на «Листе2» между ячейками C7 и L18, запись массива вашей таблицы будет «Лист2!C7:L18».

5. Введите номер столбца данных, которые вы хотите вернуть в Excel.

Под полем массива таблиц введите «номер индекса столбца» массива таблиц, в котором выполняется поиск. Например, если вы фокусируетесь на столбцах от B до K (обозначается как «B:K» при вводе в поле «табличный массив»), но конкретные значения, которые вы хотите, находятся в столбце K, вы вводите «10» в поле поле «номер индекса столбца», так как столбец K является 10-м столбцом слева.

6. Введите поиск диапазона, чтобы найти точное или приблизительное совпадение с вашим значением поиска.

В таких ситуациях, как наша, когда речь идет о ежемесячном доходе, вы хотите найти точных совпадений из таблицы, которую вы ищете. Для этого введите «FALSE» в поле «просмотр диапазона». Это говорит Excel, что вы хотите найти только точный доход, связанный с каждым торговым контактом.

Чтобы ответить на ваш животрепещущий вопрос: Да, вы можете разрешить Excel искать приблизительное соответствие вместо точного совпадения. Для этого просто введите TRUE вместо FALSE в четвертом поле, показанном выше.

Если функция ВПР настроена на приблизительное совпадение, она ищет данные, которые больше всего похожи на искомое значение, а не данные, идентичные этому значению. Если вы просматриваете данные, связанные, например, со списком ссылок на веб-сайты, и некоторые из ваших ссылок имеют в начале «https://», возможно, вам следует найти приблизительное совпадение на тот случай, если есть ссылки, которые не имеют этого тега «https://». Таким образом, остальная часть ссылки может совпадать без этого начального текстового тега, из-за чего ваша формула ВПР будет возвращать ошибку, если Excel не сможет ее найти.

7. Нажмите «Готово» (или «Ввод») и заполните новый столбец.

Чтобы официально внести нужные значения в новый столбец из шага 1, нажмите «Готово» (или «Ввод», в зависимости от вашей версии Excel) после заполнения поля «Поиск диапазона». Это заполнит вашу первую ячейку. Вы можете воспользоваться этой возможностью, чтобы посмотреть в другой таблице, чтобы убедиться, что это правильное значение.

Если это так, заполните остальную часть нового столбца каждым последующим значением, щелкнув первую заполненную ячейку, а затем щелкнув крошечный квадрат, который появляется в правом нижнем углу этой ячейки. Сделанный! Все ваши значения должны появиться.

ВПР не работает?

Если вы выполнили описанные выше действия, но функция ВПР по-прежнему не работает, проблема может заключаться в:

• Синтаксисе (т. е. в том, как вы структурировали формулу)
• Значения (т. е. являются ли данные, которые он ищет, точными и правильно отформатированы)

Устранение неполадок Синтаксис ВПР

Начните с просмотра формулы ВПР, которую вы написали в указанной ячейке.

• Имеется ли в виду правильное значение поиска для идентификатора ключа?
• Указывает ли он правильный диапазон массива таблиц для значений, которые необходимо получить
• Указывает ли он правильный лист для диапазона?
• Правильно ли написан этот лист?
• Используется ли правильный синтаксис для ссылки на лист? (например, Страницы!B:K или «Лист 1»!B:K)
• Был ли указан правильный номер столбца? (например, A равно 1, B равно 2 и т. д.)
• Верно или неверно правильно настроена таблица?

Устранение неполадок со значениями ВПР

Если проблема не в синтаксисе, то как у вас могут возникнуть проблемы со значениями, которые вы пытаетесь получить сами. Это часто проявляется как ошибка #Н/Д, когда функция ВПР не может найти указанное значение.

• Значения отформатированы вертикально и справа налево?
• Соответствуют ли значения тому, как вы к ним относитесь?

Например, если вы просматриваете данные URL-адреса, каждый URL-адрес должен быть строкой с соответствующими данными слева от нее в той же строке. Если у вас есть URL-адреса в качестве заголовков столбцов с данными, перемещающимися по вертикали, ВПР не будет работать.

В соответствии с этим примером URL-адреса должны совпадать по формату на обоих листах. Если у вас есть один лист, включающий «https://» в значении, а на другом листе «https://» отсутствует, функция ВПР не сможет сопоставить значения.