ВПР между двумя книгами: простое пошаговое руководство

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

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

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

Вы также узнаете о некоторых улучшениях функции ВПР и нескольких альтернативах этой функции.

Давайте начнем!

Оглавление

• Что такое ВПР?
• Как использовать функцию ВПР между двумя книгами
  • Образец данных
  • Шаг 1. Держите обе рабочие книги открытыми
  • Шаг 2: начните вводить формулу ВПР
  • Шаг 3: Как сослаться на вторую книгу
• 3 распространенные ошибки ВПР и способы их исправления
  • 1. #Н/Д Ошибка
  • 2. #Ссылка! Ошибка номера столбца
  • 3. Ошибка кавычек и квадратных скобок
• 4 совета по управлению разными книгами
• Как обновлять данные автоматически
• Расширенные возможности использования функции ВПР
• 2 основные альтернативы ВПР
  • 1. СОВПАДЕНИЕ ПО ИНДЕКСУ
  • 2. ПРОСМОТР
• Как использовать функцию ВПР на нескольких листах
  • Шаг 1: Определите компоненты
  • Шаг 2. Настройте свои данные
  • Шаг 3: Используйте функцию ВПР
  • (Необязательно) Шаг 4. Устранение ошибок #Н/Д
• Последние мысли

Что такое ВПР?

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

VLOOKUP — одно из решений этой задачи. Этот термин означает «вертикальный поиск» . “

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

Его можно использовать с:

• одна таблица

• несколько листов в одной книге

• две книги Excel

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

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

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

Три основных шага для этого:

1. Откройте обе книги.

2. Введите формулу ВПР в первую книгу Excel.

3. Предоставьте полную ссылку на вторую книгу.

Образец данных

Этот простой учебник работает с примерами, основанными на демонстрационных данных. Чтобы продолжить, создайте новую книгу под названием «Продажи» и введите эти данные в столбцы A и B:

• Яблоки, 40

• Апельсины, 30

• Груши, 70

• Бананы, 20

Добавьте третий столбец для хранения информации о цене:

Создайте вторую книгу Excel под названием «Продукт» и переименуйте Лист1 в «Цены». Введите эти данные в столбец A и столбец B:

• Яблоки, 1,50

• Апельсины, 2.30

• Груши, 3,60

• Бананы, 2.10

Данные выглядят так:

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

Шаг 1. Держите обе рабочие книги открытыми

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

Вы можете либо открыть их по отдельности, либо использовать команду «Открыть» в Excel, чтобы открыть их сразу. Любую книгу можно открыть двумя способами:

• Перейдите на вкладку «Файл» и выберите «Открыть».

• Используйте сочетание клавиш «Ctrl + O» .

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

Шаг 2: начните вводить формулу ВПР

Вот основной синтаксис функции ВПР:

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

• lookup_value : значение, которое вы хотите найти в другой книге.

• table_array : диапазон ячеек в другой книге, содержащий данные, которые вы хотите получить.

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

• range_lookup : этот последний параметр является необязательным. Если вы установите значение TRUE или опустите его, функция ВПР будет искать приблизительное совпадение. Если вы установите значение FALSE, функция ВПР будет искать только точное совпадение.

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

• Искомое_значение находится в ячейке A2 первой книги.

• Массив таблиц находится в диапазоне ячеек A1:B5 второй книги.

• col_index_num — это второй столбец, содержащий цену.

Самая сложная часть формулы ВПР — правильная ссылка на вторую книгу.

Шаг 3: Как сослаться на вторую книгу

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

• имя книги

• имя листа

• диапазон ячеек

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

Это значения для выборочных данных:

• имя книги: Product.xlsx

• Название листа: Цены

• диапазон ячеек: $A1:$B5

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

=ВПР(A2, [Product.xlsx]Цены!$A$1:$B$5, 2, ЛОЖЬ)

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

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

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

В формуле используются абсолютные значения диапазона ячеек (префикс $). Это позволяет скопировать формулу в остальные строки, щелкнув маркер заполнения.

Вот результаты ВПР:

Вот и все! Теперь вы знаете, как эффективно использовать функцию ВПР. Чтобы помочь вам, если вы столкнетесь с какими-либо ошибками, мы собрали три наиболее распространенных ошибки ВПР в следующем разделе.

3 распространенные ошибки ВПР и способы их исправления

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

Вот три наиболее частые ошибки и способы их исправления.

1. #Н/Д Ошибка

Ошибка #Н/Д возникает, когда искомое значение не найдено в массиве таблиц. Чтобы исправить эту ошибку, вы можете попробовать следующее:

• Проверьте правильность написания искомого значения.

• Убедитесь, что искомое значение находится в первом столбце массива таблиц.

• Убедитесь, что значение поиска не отформатировано как текст на двух листах.

• Убедитесь, что аргумент range_lookup имеет значение FALSE, чтобы найти точное совпадение.

В приведенном выше примере не удалось найти соответствующее значение для Cherries во второй книге.

2. #Ссылка! Ошибка номера столбца

# Реф! Ошибка возникает, когда аргумент col_index_num превышает количество столбцов в массиве таблиц.

Чтобы исправить эту ошибку, убедитесь, что аргумент col_index_num меньше или равен количеству столбцов в исходной таблице.

На рисунке ниже для col_index_num установлено значение 3, но в опорном диапазоне только два столбца.

3. Ошибка кавычек и квадратных скобок

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

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

• Используйте двойные кавычки вокруг искомого значения.

• Используйте функцию CHAR(34) для вставки двойных кавычек.

• Используйте функцию ЕСЛИОШИБКА, чтобы вернуть пустую ячейку, если искомое значение содержит кавычки или квадратные скобки.

Как только вы ознакомитесь с этими ошибками, исправить их будет просто! Чтобы еще больше помочь вам стать профессионалом ВПР, мы предоставили четыре совета по управлению различными книгами в следующем разделе.

4 совета по управлению разными книгами

При работе с несколькими книгами важно отслеживать, какая книга содержит какие данные.

Вот наши четыре лучших совета, чтобы сохранить здравомыслие:

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

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

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

4. Рассмотрите возможность блокировки ячеек в справочнике , чтобы их нельзя было перезаписать.

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

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

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

Вот как:

• В формуле ВПР замените аргумент массива таблицы функцией ДВССЫЛ.

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

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

1. Используйте одну ячейку в столбце D, чтобы ввести ссылку на лист «Цены» рабочей книги «Продукт» и диапазон ячеек A1: B5 следующим образом: [Product.xlsx]Prices!A:B

2. В ячейке C2 книги «Продажи» используйте следующую формулу: =ВПР(A2, ДВССЫЛ($D$1), 2, ЛОЖЬ)

3. Скопируйте формулу вниз (абсолютные ссылки обеспечивают сохранение ячейки с электронной таблицей).

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

На этом рисунке показана функция ДВССЫЛ в сочетании с ВПР:

Обратите внимание, что в этом примере для ссылки на два столбца используется диапазон «A:B». Это означает, что когда вы добавляете больше продуктов и цен, формула будет продолжать работать в текущей книге.

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

Расширенные возможности использования функции ВПР

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

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

Комплексный анализ включает в себя такие методы, как критерий хи-квадрат и Т-критерий для парных выборок. Вот пример использования теста хи-квадрат в Excel:

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

Но что, если вы по какой-то причине не хотите использовать функцию ВПР? Вам повезло! Есть несколько альтернатив, которые мы рассмотрим в следующем разделе.

2 основные альтернативы ВПР

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

Два наиболее распространенных:

• ИНДЕКС СОВПАДЕНИЕ

• XLOOKUP

1. СОВПАДЕНИЕ ПО ИНДЕКСУ

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

Вот как это работает:

1. Функция ИНДЕКС возвращает значение из указанного диапазона на основе номера строки и столбца.

2. Функция ПОИСКПОЗ возвращает позицию искомого значения в указанном диапазоне.

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

Вот пример формулы:

=ИНДЕКС(Диапазон таблицы,ПОИСКПОЗ(Значение поиска,Диапазон поиска,0),Индекс столбца)

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

=ИНДЕКС([Product.xlsx]Цены!$B$1:$B$5, MATCH(A2, [Product.xlsx]Цены!$A$1:$A$5, 0))

Это разбивается на:

• Функция ПОИСКПОЗ ищет значение в ячейке A2 (название продукта) в диапазоне A1:A5 на листе «Цены» рабочей книги «Продукт».

• Функция ИНДЕКС возвращает значение из диапазона B1:B5 на листе «Цены» рабочей книги «Продукт» на основе номера строки, предоставленного функцией ПОИСКПОЗ.

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

2. ПРОСМОТР

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

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

Вот как это работает:

1. Функция XLOOKUP возвращает значение из указанного диапазона на основе значения поиска.

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

3. Вы также можете указать, что возвращать, если совпадений не найдено.

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

Вот пример формулы:

=XLOOKUP(LookupValue,LookupRange,ReturnRange,"Not Found",0)

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

=XLOOKUP(A2, [Product.xlsx]Цены!$A$1:$A$5, [Product.xlsx]Цены!$B$1:$B$5)

Это разбивается на:

• A2: значение, которое вы хотите найти (название продукта).

• [Product.xlsx]Цены!$A$1:$A$5: диапазон, в котором вы хотите найти значение поиска (названия продуктов на листе «Цены» рабочей книги «Продукт»).

• [Product.xlsx]Prices!$B$1:$B$5: диапазон, содержащий возвращаемые значения (цены на листе «Цены» рабочей книги «Продукт»).

Это формула в действии:

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

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

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

Есть четыре шага.

Шаг 1: Определите компоненты

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

Например, если вы хотите найти значение в диапазоне A2:B6 на листе января в книге Sales_reports.xlsx, ваша формула будет выглядеть так:

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

Шаг 2. Настройте свои данные

Убедитесь, что ваши данные организованы таким образом, чтобы их можно было легко использовать на нескольких листах.

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

Шаг 3: Используйте функцию ВПР

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

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

(Необязательно) Шаг 4. Устранение ошибок #Н/Д

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

Вы также можете проверить, выполняете ли вы точное совпадение, а не поиск диапазона.

Последние мысли

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

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

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

Включив такие функции, как INDIRECT, INDEX-MATCH или XLOOKUP, вы можете еще больше повысить гибкость и эффективность поиска данных.

Продолжайте практиковаться и изучать обширные возможности Excel; это только вершина айсберга. Удачной охоты за данными!