ВПР в нескольких таблицах Excel: простое руководство

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

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

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

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

Содержание

Задача: Данные, которые нужно найти и извлечь при помощи функции ВПР, находятся в нескольких таблицах. Эти таблицы имеют одинаковую структуру (то есть, одни и те же столбцы, расположенные в одном и том же порядке). Необходимо помочь функции ВПР определить, из какой именно таблицы ей извлечь результаты поиска?

Выбор таблицы для ВПР при помощи функции ЕСЛИ.

Начнем с самого простого – когда несколько таблиц находятся на одном листе и в зависимости от какого-то условия нам нужно произвести поиск ВПР в одной из них.

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

Для того чтобы легче было работать с таблицами ставок, используем именованные диапазоны и обозначим их tabl1, tabl2 и tabl3.

В качестве условия для использования каждой из таблиц служит стаж работника. Таким образом, внутри функции ВПР (VLOOKUP) используем обработку условий при помощи функции ИЛИ (IF). В ячейке D2 запишем:

=ВПР(C2;ЕСЛИ(B2>3;tabl3;ЕСЛИ(B2<1;tabl1;tabl2));2;1)

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

Читайте подробнее: Функция ЕСЛИ: примеры с несколькими условиями

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

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

Сумма комиссионных находится простым произведением ставки комиссионных на объем продаж. Далее копируем эти формулы вниз по столбцу для всех менеджеров.

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

Формула ВПР для нескольких таблиц или именованных диапазонов

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

Использование функции ДВССЫЛ и именованных диапазонов.

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

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

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

Как создать выпадающий список в Excel - читайте пошаговую инструкцию.

Также создадим 3 именованных диапазона с именами такими же, как названия месяцев - январь, февраль, март.

Теперь вспомним, что формула

=ДВССЫЛ("март")

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

= ДВССЫЛ($B$15)

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

Таким образом, формулу в ячейке С14 запишем:

=ВПР($B$14;ДВССЫЛ($B$15);2;ЛОЖЬ)

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

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

ВПР между двумя таблицами на разных листах

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

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

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

В качестве примера возьмем данные о продажах. Опишем исходные данные:

Искомые_значения находятся в столбце А, и мы ссылаемся на первую ячейку данных, то есть A2. Здесь ссылка на лист не обязательна, так как мы и так на нем находимся.
Лист!диапазон — это диапазон поиска A2:B6 во второй таблице на листе “Янв”. Чтобы создать правильную ссылку, добавьте к координатам диапазона имя листа, за которым следует восклицательный знак: Jan!$A$2:$B$6. Более подробно об этом читайте в этом материале: Ссылка на другой лист или другую книгу.
Обратите внимание, что мы фиксируем диапазон с помощью абсолютных ссылок на ячейки чтобы предотвратить его изменение при копировании формулы.
Номер_столбца равен 2, потому что мы хотим получить значение из столбца B, который является вторым в диапазоне поиска.
Интервальный_просмотр установлен как ЛОЖЬ для поиска точного совпадения.

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

=ВПР(A2; Янв!$A$2:$B$6; 2; ЛОЖЬ)

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

Аналогичным образом вы можете выполнить поиск данных из таблиц на листах Фев и Март:

=ВПР(A2; Фев!$A$2:$B$6; 2; ЛОЖЬ)

=ВПР(A2; Март!$A$2:$B$6; 2; ЛОЖЬ)

Советы и примечания:

Если имя листа содержит пробелы или символы, не являющиеся цифрами или буквами, то оно должно быть заключено в одинарные кавычки, например: ВПР(A2; 'Январь продажи'!$A$2:$B$6; 2; ЛОЖЬ).
Вместо того, чтобы вводить имя листа непосредственно в формуле, вы можете при вводе второго аргумента формулы ВПР просто перейти на другой лист на таблицу поиска и выбрать там диапазон. Excel автоматически вставит ссылку с правильным синтаксисом, избавляя вас от необходимости проверять имя листа и устранять ошибки.

ВПР в нескольких таблицах с помощью ЕСЛИОШИБКА

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

В общем виде формула выглядит так:

ЕСЛИОШИБКА(ВПР(…); ЕСЛИОШИБКА(ВПР(…); …; "Не найдено"))

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

ВПР поиск нужных данных в нескольких таблицах

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

Если заказ в этой таблице не найден, то выполните поиск в в следующей таблице на листе Зап.

Если же и там заказ не обнаружен, ищите в третьей таблице на листе Юг.

Если все ВПР завершатся неудачно, верните «Не найдено».

=ЕСЛИОШИБКА(ВПР($A2; Вост!$A$2:$C$6; 2; ЛОЖЬ); ЕСЛИОШИБКА(ВПР($A2; Зап!$A$2:$C$6; 2; ЛОЖЬ); ЕСЛИОШИБКА(ВПР($A2; Юг!$A$2:$C$6; 2; ЛОЖЬ); "Не найдено")))

Номер столбца, из которого нужно получить значение, можно указать непосредственно, а можно сделать его динамическим и вычислить его при помощи функции ЧИСЛСТОЛБ. Такое вычисление может быть полезным, если ваша таблица находится где-то далеко от начала листа (например, начинается в столбце BA) или в ней очень много столбцов и высчитывать номер столбца весьма хлопотно.

Также этот метод может сэкономить вам время, если ваши таблицы имеют одинаковый порядок столбцов и вам нужно извлечь данные из нескольких столбцов. Формулу, которую вы видите ниже, вы можете просто копировать вправо по строке в соседние столбцы, и номер столбца будет вычисляться автоматически. При копировании в столбец С выражение ЧИСЛСТОЛБ($A$1:B$1) изменится на ЧИСЛСТОЛБ($A$1:С$1) и вернет вам номер столбца 3.

=ЕСЛИОШИБКА(ВПР($A2; Вост!$A$2:$C$6; ЧИСЛСТОЛБ($A$1:B$1); ЛОЖЬ); ЕСЛИОШИБКА(ВПР($A2; Зап!$A$2:$C$6; ЧИСЛСТОЛБ($A$1:B$1); ЛОЖЬ); ЕСЛИОШИБКА(ВПР($A2; Юг!$A$2:$C$6; ЧИСЛСТОЛБ($A$1:B$1); ЛОЖЬ); "Не найдено")))

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

ВПР последовательный поиск значения в нескольких таблицах

Обратите внимание, что некоторые заказы не были найдены, так как они находятся в четвертой таблице Север, которую мы не включили в поиск. Но если необходимо, думаю, вы сможете это исправить при необходимости.

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

=ЕСЛИОШИБКА(ВПР($A2; Вост!$A$2:$C$6; 3; ЛОЖЬ); ЕСЛИОШИБКА(ВПР($A2; Зап!$A$2:$C$6; 3; ЛОЖЬ); ЕСЛИОШИБКА(ВПР($A2; Юг!$A$2:$C$6; 3; ЛОЖЬ); "Не найдено")))

Или используйте рекомендованную выше формулу с динамическим номером столбца и просто скопируйте ее из В2 в С2.

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

Чтобы выполнить ВПР в нескольких таблицах, которые находятся в разных рабочих книгах, заключите имя книги в квадратные скобки и поместите его перед именем листа. Например, вот как вы можете использовать ВПР с тремя таблицами в трех разных файлах (Отчет1, Отчет2 и Отчет3) в одной формуле:

=ЕСЛИОШИБКА(ВПР($A2; [Отчет1.xlsx]Вост!$A$2:$C$6; 2; ЛОЖЬ); ЕСЛИОШИБКА(ВПР($A2; [Отчет2.xlsx]Зап!$A$2:$C$6; 2; ЛОЖЬ); ЕСЛИОШИБКА(ВПР($A2; [Отчет3.xlsx]Юг!$A$2:$C$6; 2; ЛОЖЬ); "Не найдено")))

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

Следующий пример демонстрирует немного более сложный, но гораздо более элегантный подход.

ВПР в нескольких таблицах через ДВССЫЛ

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

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

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

Где:

Таблицы_для_поиска – именованный диапазон, состоящий из названий листов с таблицами поиска.
Искомое_значение – значение для поиска.
Диапазон поиска – диапазон столбцов в таблицах поиска, в которых следует искать искомое значение.
Таблица_диапазон – весь диапазон данных в таблицах поиска.
Номер_столбца — номер столбца в таблице, из которого нужно вернуть значение.

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

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

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

Запишите все имена рабочих листов с вашими таблицами где-нибудь в своей книге и дайте имя этому диапазону (Таблицы_для_поиска в нашем случае).

Именованный диапазон с именами нескольких таблиц для поиска

Настройте формулу ВПР для своих данных. В этом примере мы делаем следующее:
- ищем значение из ячейки A2 (искомое_значение)
- в диапазоне A2:A6 (диапазон_поиска) на четырех листах (Вост,Сев,Юг и Зап), и
- извлекаем соответствующие значения из столбца B, который в каждой из таблиц на этих листах является столбцом 2 (номер_столбца) в диапазоне данных A2:C6 (таблица_диапазон).

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

=ВПР($A2; ДВССЫЛ("'"&ИНДЕКС(Таблицы_для_поиска; ПОИСКПОЗ(1; --(СЧЁТЕСЛИ(ДВССЫЛ("'" & Таблицы_для_поиска & "'!$A$2:$A$6"); $A2)>0); 0)) & "'!$A$2:$C$6"); 2; ЛОЖЬ)

Обратите внимание, что мы фиксируем оба диапазона ($A$2:$A$6 и $A$2:$C$6) с помощью абсолютных ссылок на ячейки.

Запишите эту формулу в самую верхнюю ячейку (в этом примере B2) и нажмите Ctrl + Shift + Enter.
Дважды щелкните или перетащите маркер заполнения, чтобы скопировать формулу вниз по столбцу.

Функция СЧËТЕСЛИ помогает определить наличие совпадений с искомым значением, а функция ПОИСКПОЗ - позицию этого найденного совпадения.

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

поиск номера заказа в четырех таблицах по списку

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

=ВПР($A2; ДВССЫЛ("'"&ИНДЕКС(Таблицы_для_поиска; ПОИСКПОЗ(1; --(СЧЁТЕСЛИ(ДВССЫЛ("'" & Таблицы_для_поиска & "'!$A$2:$A$6"); $A2)>0); 0)) & "'!$A$2:$C$6"); 3; ЛОЖЬ)

Если вы хотите заменить стандартное обозначение ошибки #Н/Д собственным текстом, используйте функцию ЕСЛИОШИБКА:

=ЕСЛИОШИБКА(ВПР($A2; ДВССЫЛ("'"&ИНДЕКС(Таблицы_для_поиска; ПОИСКПОЗ(1; --(СЧЁТЕСЛИ(ДВССЫЛ("'" & Таблицы_для_поиска & "'!$A$2:$A$6"); $A2)>0); 0)) & "'!$A$2:$C$6"); 2; ЛОЖЬ);"Не найдено")

Результат вы видите на скриншоте ниже:

ВПР ЕСЛИОШИБКА и ДВССЫЛ для поиска в нескольких таблицах

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

ВПР в таблицах в нескольких файлах

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

Например, для поиска значения A2 в диапазоне A2:B6 на листе Янв в книге Sales_report.xlsx, используйте эту формулу:

=ВПР(A2; [Sales_report.xlsx]Янв!$A$2:$B$6; 2; ЛОЖЬ)

Все остальное совершенно аналогично предыдущему примеру.

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

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

Для этого добавьте имя книги в функцию ДВССЫЛ, как показано для примера в формуле ниже:

=ВПР($A2; ДВССЫЛ("'[Отчет1.xlsx]'"&ИНДЕКС(Таблицы_для_поиска; ПОИСКПОЗ(1; --(СЧЁТЕСЛИ(ДВССЫЛ("'[Отчет1.xlsx]'" & Таблицы_для_поиска & "'!$A$2:$A$6"); $A2)>0); 0)) & "'!$A$2:$C$6"); 2; ЛОЖЬ)

Как получить сразу несколько столбцов из нескольких таблиц

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

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

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

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

получить сразу несколько столбцов из нескольких таблиц

Динамический диапазон таблиц для поиска

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

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

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

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

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

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

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

Восток_прод - A2:B6 на листе "Восток"
Север_прод - A2:B6 на листе "Север"
Юг_прод - A2:B6 на листе "Юг"
Запад_прод - A2:B6 на листе "Запад"

Вы можете сделать ВПР из нескольких таблиц более наглядным, если запишете их названия в определенном диапазоне ячеек, например, в шапке таблицы (В1:Е1 в нашем примере).

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

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

Для каждого из регионов у нас есть отдельная таблица с отчетом о продажах. Создадим в каждой из этих таблиц именованный диапазон вида [Название региона]_прод, например Восток_прод, как на скриншоте ниже.

Создать именованный диапазон по данным таблицы

Создадим динамический диапазон ВПР с помощью функции ДВССЫЛ:

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

ВПР из нескольких именованных диапазонов на основе таблиц

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

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