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

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

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

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

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

Здесь вы можете узнать о: Как улучшить работу с Excel — 13 полезных советов

Общая формула для Функция ВПР в Excel с другого листа следующее:

• ВПР (искомое_значение, Лист! Диапазон, col_index_num, [range_lookup])

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

• Значения поиска Они находятся в столбце А листа. Резюме и мы обращаемся к первой ячейке данных, то есть A2.
• Таблица_массивимеет ранг А2: B6 на январском листе. Чтобы сослаться на него, вы должны поставить перед ссылкой на диапазон имя листа, за которым следует восклицательный знак: Ян! $A$2:$B$6.

ПРИМЕЧАНИЕ: Здесь нужно обратить внимание, что вы блокируете диапазон абсолютными ссылками на ячейки, чтобы предотвратить его изменение при копировании формулы в другие ячейки.

• Col_index_numравно 2, поскольку вы хотите скопировать значение из столбца B, который является вторым столбцом в матрице таблицы.
• Диапазон_поискаустановлено значение FALSE, чтобы найти точное совпадение.

Объединив аргументы, вы получите следующую формулу:

• =ВПР(A2, Январь!$A$2:$B$6, 2, ЛОЖЬ)

Перетащите формулу вниз по столбцу, и вы получите такой результат:

Аналогичным образом вы можете выполнить функцию Vlookup, используя данные из листов за февраль и март:

• =ВПР(A2, фев!$A$2:$B$6, 2, ЛОЖЬ)
• =ВПР(A2, Вт!$A$2:$B$6, 2, ЛОЖЬ)

Советы и примечания по функции ВПР в Excel:

• Если имя листа содержит пространстваили персонажи неалфавитный, должен быть заключен в одинарные кавычки, например «Январские распродажи»! $A$2:$B$6.
• Вместо того, чтобы вводить имя листа непосредственно в формулу, вы можете переключиться на таблицу поиска и выбрать там диапазон. Excel автоматически вставит ссылку с правильным синтаксисом, избавляя вас от необходимости проверять имя и устранять неполадки.

Методы использования функции ВПР в Excel

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

Способ 1. Поиск другой книги.

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

• Например: чтобы найти значение A2 в диапазоне A2:B6 на январском листе книги. XLSX, используйте эту формулу:
• =ВПР(A2, [Sales_reports.xlsx]Ян!$A$2:$B$6, 2, ЛОЖЬ)

Метод 2: Впросмотр на нескольких листах с помощью ЕСЛИОШИБКА

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

• ОШИБКА (ВПР(…), ЕСЛИОШИБКА (ВПР(…),…, » Нет encontrado«))

Чтобы увидеть, как этот подход работает на реальных данных, давайте рассмотрим следующий пример:

Ниже представлена ​​таблица Resumen которые вы должны заполнить, указав наименования позиций и количества при поиске номера заказа на листах «Запад» и «Восток»:

• Шаг 1: Сначала вы вытащите элементы. Для этого укажем на формулу ВПР Найдите номер заказа в формате А2 на листе. Este и верните значение столбца B (2-й столбец в таблица_массив A2:C6).
• Шаг 2: Если точное совпадение не найдено, выполните поиск по листу. Запад.
• Шаг 3: Да оба Сбой ВПР, вернет сообщение: "Не найден".
  • =ЕСЛИОШИБКА(ВПР(A2, Восток!$A$2:$C$6, 2, ЛОЖЬ), ЕСЛИОШИБКА(ВПР(A2, Запад!$A$2:$C$6, 2, ЛОЖЬ), «Не найдено»))

• Шаг 4: Чтобы вернуть сумму, просто измените индекс столбца на 3:
  • =ЕСЛИОШИБКА(ВПР(A2, Восток!$A$2:$C$6, 3, ЛОЖЬ), ЕСЛИОШИБКА(ВПР(A2, Запад!$A$2:$C$6, 3, ЛОЖЬ), «Не найдено»))

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

Способ 3: Vlookup в нескольких книгах

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

• Например- Вот как вы можете использовать Vlookup вдва разных файла (Книга1 и Книга2) с помощью одной формулы:
  • =ЕСЛИОШИБКА(ВПР(A2, [Book1.xlsx]Восток!$A$2:$C$6, 2, ЛОЖЬ), ЕСЛИОШИБКА(ВПР(A2, [Book2.xlsx]Восток!$A$2:$C$6, 2, ЛОЖЬ),»Не найдено»))

Способ 4: сделать номер индекса столбца динамическим для нескольких столбцов Vlookup

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

• Для аргумента col_index_num, используйте функцию КОЛОННЫ который возвращает количество столбцов в указанном массиве: КОЛОННЫ ($A$1:B$1). (Координата строки не имеет особого значения, это может быть любая строка.)
• В аргументе искомое_значение, блокирует ссылку на столбец с помощью Знак $ ($A2), поэтому он остается фиксированным при копировании формулы в другие столбцы.

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

• =ЕСЛИОШИБКА(ВПР($A2, Восток!$A$2:$C$6, СТОЛБЦЫ($A$1:B$1), ЛОЖЬ), ЕСЛИОШИБКА(ВПР($A2, Запад!$A$2:$C$6, СТОЛБЦЫ( $A$1:B$1), FALSE), «Не найдено»))

При вводе в столбец B, КОЛОННЫ ($A$1:B$1) оценивается как 2 и сообщает VLOOKUP вернуть значение 2 второй столбец в матрице таблицы.

При копировании в столбец C (то есть при перетаскивании формулы из B2 в C2) значение B$1 меняется на C$1, поскольку ссылка на столбец является относительной. Следовательно, КОЛОННЫ ($A$1:C$1) оценивается как 3, что заставляет функцию ВПР в Excel возвращать значение из третьего столбца.

Эта формула отлично работает для 2 или 3 листов поиска. Если их больше, повторяющиеся ЕСЛИОШИБКИ станут слишком громоздкими. Следующий пример демонстрирует немного более сложный, но гораздо более элегантный подход.

Метод 5: поиск нескольких листов Vlook с помощью INDIRECT

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

Общая формула для поиска на листах выглядит следующим образом:

• ВПР ( искомое_значение, КОСВЕННЫЙ («'» И ИНДЕКС ( Справочные_листы , СООТВЕТСТВИЕ (1, – (СЧЁТЕСЛИ (ДВССЫЛ («'» & Лист_список & «'! Диапазон поиска_диапазона "), искомое_значение )> 0), 0)) & «'! таблица_массив "), номер_столбца , ЛОЖЬ)

Dónde:

• Справочные_листы- Именованный диапазон, состоящий из названий листов поиска.
• Искомое_значение: значение для поиска.
• Диапазон поиска_диапазона— Диапазон столбцов в таблицах поиска, в которых можно искать искомое значение.
• Массив таблиц- Диапазон данных в справочных таблицах.
• Col_index_num— Номер столбца в массиве таблицы, из которого возвращается значение.

Для корректной работы формулы необходимо учитывать следующие предупреждения:

1. Это формула массива, которую необходимо заполнить, нажав Ctrl + Shift + Ввод ключи вместе.
2. Все листы должны иметь тот же порядок столбцов.
3. Поскольку вы используете массив таблиц для всех справочных листов, укажите самый большой диапазон если ваши листы имеют разное количество строк.

Как использовать формулу Vlookup на листах

Чтобы использовать функцию Vlookup на нескольких листах одновременно, необходимо выполнить следующие действия:

• Шаг 1: запишите все имена справочных листов где-нибудь в своей книге и назовите этот диапазон (Справочные_листы в этом случае).

• Шаг 2: настройте общую формулу для ваших данных. В этом примере это будет:
  • ищем значение A2 (искомое_значение)
  • в диапазоне А2: А6 (искомый_диапазон) на четырех листах (Восток, Север, Юг и Запад) и
  • извлечь совпадающие значения из столбца B, который является столбцом 2 (сol_index_num) в диапазоне данных A2: C6 (таблица_массив).

С учетом предыдущих аргументов формула принимает следующий вид:

• =VLOOKUP ($A2, INDIRECT («'»&INDEX (Lookup_sheets, MATCH (1, –(COUNTIF (INDIRECT («'»& Lookup_sheets&»'!$A$2:$A$6″), $A2)>0), 0)) &»'!$A$2:$C$6»), 2, ЛОЖЬ)

ПРИМЕЧАНИЕ: Обратите внимание, что мы блокируем оба диапазона. ($A$2:$A$6 и $A$2:$C$6) с абсолютными ссылками на ячейки.

• Шаг 3: введите формулу в верхнюю ячейку (в этом примере B2) и нажмите Ctrl + Shift + Ввод чтобы завершить его.
• Шаг 4: дважды щелкните или перетащите маркер заполнения, чтобы скопировать формулу вниз по столбцу.

В результате у вас будет формула для поиска номера заказа на 4 листах и ​​получения соответствующего товара. Если конкретный номер заказа не найден, отображается ошибка. # Н/Д как в строке 14:

Чтобы вернуть сумму, просто замените 2 на 3 в аргументе. номер_столбца так как количества находятся в 3-м столбце матрицы таблицы:

• =VLOOKUP($A2, INDIRECT(«'»&INDEX(Таблицы_поиска, MATCH(1, –(СЧЁТЕСЛИ(ДВССЫЛ(«'» & Таблицы_поиска & «'!$A$2:$A$6»), $A2)>0) , 0)) & «'!$A$2:$C$6»), 3, ЛОЖЬ)

Если вы хотите заменить стандартное обозначение ошибки # Н/Д со своим текстом, оберните формулу в функцию IFNA:

• =IFNA (VLOOKUP($A2, INDIRECT («'»&INDEX (Lookup_sheets, MATCH (1, –(COUNTIF(INDIRECT («'» & Lookup_sheets & «'!$A$2:$A$6»), $A2)> 0), 0)) & «'!$A$2:$C$6»), 3, FALSE), «Не найдено»)

Vlookup нескольких листов между книгами

Эту общую формулу (или любой ее вариант) также можно использовать для отображения нескольких листов на одном. другая рабочая тетрадь. Для этого объедините имя книги в ДВССЫЛ, как показано в следующей формуле:

• =IFNA(VLOOKUP ($A2, INDIRECT («'[Book1.xlsx]» & INDEX (Lookup_sheets, MATCH (1, –(COUNTIF(INDIRECT («'[Book1.xlsx]» & Lookup_sheets & «'!$A$2) :$A$6"), $A2)>0), 0)) & «'!$A$2:$C$6"), 2, FALSE), «Не найдено»)

Vlookup между листами и возвращает несколько столбцов

Если вы хотите извлечь данные из нескольких столбцов, один формула массива из нескольких ячеек вы можете сделать это за один раз. Чтобы создать такую ​​формулу, укажите константу массива для аргумента. номер_столбца.

В этом примере мы возвращаем имена элементов (столбец B) и количества (столбец C), которые являются вторым и третьим столбцами в матрице таблицы соответственно. Итак, искомая матрица {2,3}.

• =VLOOKUP($A2, INDIRECT(«'»&INDEX(Таблицы поиска, MATCH(1, –(СЧЁТЕСЛИ(ДВССЫЛ(«'»& Таблицы поиска &»'!$A$2:$C$6″), $A2)>0) , 0)) &»'!$A$2:$C$6»), {2,3}, ЛОЖЬ)

Чтобы успешно ввести формулу в несколько ячеек, вот что вам нужно сделать:

• Шаг 1: в первой строке выберите все ячейки, которые необходимо заполнить (в данном примере B2:C2).
• Шаг 2: Напишите формулу и нажмите клавиши Ctrl + Shift + Ввод. При этом в выбранные ячейки будет введена одна и та же формула, которая вернет разные значения в каждом столбце.
• Шаг 3: перетащите формулу в оставшиеся строки.

Как работает формула функции ВПР в Excel

Чтобы лучше понять логику, давайте разобьем эту базовую формулу на отдельные функции:

• =VLOOKUP ($A2, INDIRECT («'»&INDEX(Lookup_sheets, MATCH (1, –(COUNTIF (INDIRECT («'»& Lookup_sheets&»'!$A$2:$A$6″), $A2)>0), 0)) &»'!$A$2:$C$6»), 2, ЛОЖЬ)

Работая изнутри наружу, формула делает следующее:

СЧЁТЕСЛИ и КОСВЕННЫЕ

Короче говоря, яНДИРЕКТ создает ссылки для всех листов поиска, а функция СЧЕТЕСЛИ подсчитывает вхождения искомого значения (A2) на каждом листе:

• –(COUNTIF( ДВССЫЛ(«'»&Lookup_sheets&»'!$A$2:$A$6»), $A2)>0)

Более подробно:

Сначала объедините имя диапазона (Справочные_таблицы) и ссылка на диапазон ($A$2:$A$6), добавив апострофы и восклицательный знак в нужных местах, чтобы создать внешнюю ссылку, и передать полученную текстовую строку в функцию ДВССЫЛ для динамической ссылки на таблицы поиска:

• INDIRECT ({«'Восток'!$A$2:$A$6»; «'Юг'!$A$2:$A$6»; «'Север»!$A$2:$A$6»; «'Запад'! $A$2:$A$6»})

КОНТАР.SI проверяет каждую ячейку в диапазоне A2: A6 на каждом листе поиска со значением в A2 на родительском листе и возвращает количество совпадений для каждого листа.

В этом наборе данных номер заказа в A2 (101) находится в лист Запад, вопрос 4 ^º в указанном диапазоне, поэтому СЧИТАТЬ ДА возвращает этот массив:

• {0; 0; 0; 1}

Затем сравните каждый элемент приведенного выше массива с 0:

• –({0; 0; 0; 1}>0)

Это создает массив значений ИСТИНА (больше 0) и ЛОЖЬ (равна 0), который приводит 1 и 0 с помощью унарного двойной (-), и в результате вы получите следующий массив:

• {0; 0; 0; 1}

Эта операция является дополнительной мерой предосторожности в ситуации, когда таблица поиска содержит несколько вхождений искомого значения, в которых Случай СЧЕТЕСЛИ вернет счет больше 1, тогда как вы просто хотите 1 и 0 в финальной матрице (сейчас вы поймете, почему).

После всех этих преобразований формула выглядит так:

• VLOOKUP($A2, INDIRECT(«'»&INDEX(Lookup_sheets, MATCH(1, {0;0;0;1}, 0)) &»'!$A$2:$C$6»), 2, FALSE)

ИНДЕКС и ПОИСКПОЗ

На данный момент классическое сочетание ИНДЕКСНОЕ СОВПАДЕНИЕ идет к:

• ИНДЕКС(Таблицы_поиска, ПОИСКПОЗ(1, {0;0;0;1}, 0))

Функция СООТВЕТСТВИЕ установлено точное совпадение (0 в последнем аргументе), ищет значение 1 в массиве {0; 0; 0; 1} и возвращает свою позицию, равную 4:

ИНДЕКС(Справочные_листы, 4)

Функция INDEX используйте число, возвращенное СООТВЕТСТВИЕ как аргумент номера строки (номер_строки) и возвращает четвертое значение в указанном диапазоне Справочные_листы, Который является Запад.

Таким образом, формула далее сводится к:

• ВПР($A2, INDIRECT(«'»&»West»&»'!$A$2:$C$6»), 2, FALSE)

ВПР и КОСВЕННЫЙ

Функция КОСВЕННЫЕ обработайте текстовую строку внутри нее:

• КОСВЕННЫЙ («'»&»Запад»&»'!$A$2:$C$6»)

И превращает его в ссылку, которая переходит к аргументу таблица_массив из ВПР:

• ВПР ($A2, «Запад»!$A$2:$C$6, 2, ЛОЖЬ)

Наконец, эта стандартная формула ВПР ищет значение A2 в первом столбце диапазона A2:C6 на западном листе и возвращает совпадение из второго столбца.

Динамический ВПР для возврата данных из нескольких листов в разные ячейки

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

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

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

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

• East_Sales- A2: B6 на восточном листе
• North_Sales- A2: B6 на северном листе
• South_Sales- A2: B6 на южном листе
• West_Sales- A2: B6 на листе «Запад»

ВПР и вложенные IF

Если у вас есть достаточное количество листов для поиска, вы можете использовать вложенные функции ЕСЛИ чтобы выбрать лист на основе ключевых слов в предопределенных ячейках (ячейки от B1 до D1 в этом случае).

Если искомое значение находится в A2, формула выглядит следующим образом:

• =VLOOKUP ($A2, ЕСЛИ (B$1=»восток», East_Sales, IF (B$1=»север», North_Sales, IF (B$1=»юг», South_Sales, IF (B$1=»запад», West_Sales) ))), 2, НЕВЕРНО)

Переведенная на английский часть IF говорит:

Если B1 восток, ищет диапазон с именем Восток_Продажи; если B1 север, ищет диапазон под названием Север_Продажи; если B1 Южная, ищет диапазон под названием Юг_Продажи; и если B1 запад, ищет диапазон под названием West_Sales.

Диапазон, возвращаемый IF, будет равен таблица_массив de ВПР, который извлекает соответствующее значение из второго столбца соответствующего листа.

Разумное использование смешанных ссылок для искомого значения ($A2 – абсолютный столбец и относительная строка) и логического теста ЕСЛИ (B$1 – относительный столбец и абсолютная строка) позволяет копировать формулу в другие ячейки без каких-либо изменений – Excel автоматически корректирует ссылки в зависимости от относительного положения строки и столбца..

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

КОСВЕННЫЙ ВПР

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

• =ВПР ($A2, ДВССЫЛ (B$1&»_Sales»), 2, ЛОЖЬ)

Здесь мы объединяем ссылку на ячейку, содержащую уникальную часть именованного диапазона (B1) и общую часть (_Продажи). Это создает текстовую строку типа «Восток_Продажи», которое INDIRECT преобразуется в имя диапазона, читаемое в Excel.

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

Вам может быть интересно узнать о: Как сгруппировать сводную таблицу по месяцам в Excel

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