Как создать ВПР между двумя листами (с примером)

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

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

Причины использования ВПР между двумя листами Excel

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

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

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

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

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

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

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

1. Определите компоненты

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

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

Вы можете определить каждый из этих компонентов как:

• lookup_value: это значение, которое вы ищете.

• Sheet!range: включает в себя как массив таблиц, так и лист, на котором вы хотите собрать информацию. Например, это может выглядеть как «лист 2!A2:D8».

• col_index_num: это номер столбца, в котором вы собираетесь собирать данные. Это соответствует последовательности столбцов в выбранном массиве таблиц.

• [range_lookup]: Это необязательно, но вы можете указать TRUE, чтобы собрать приблизительные совпадения, или FALSE, чтобы создать точное совпадение.

2. Определите свои критерии между листами

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

=ВПР(A2, Продажи!A2:E6, 2, ЛОЖЬ)

Это означает, что вы будете собирать данные для ячейки A2 из рабочего листа «Продажи» во втором столбце массива таблиц от A2 до E6.

3. Выполните ВПР между книгами

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

ВПР(искомое_значение, [workbook_name]Sheet_name!диапазон, col_index_num, [range_lookup])

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

4. Дублирование формул ВПР

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

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

Пример ВПР между двумя листами

Вот пример ВПР между двумя листами:

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

Название листа: Январь

A B 1 Название предмета Заработанные доллары 2 Горячий кофе 1200 долларов США 3 Кофе со льдом 900 долларов США 4 Выпечка 700 долларов США 5 Специальные напитки 800 долларов США

* Название листа: Резюме

A B C 1 Наименование товара Заработанные доллары (январь) Заработанные доллары (февраль) 2 Горячий кофе 3 Кофе со льдом 4 Выпечка 5 Специальные напитки

=ВПР(A2, январь!A2:B5, 2, ЛОЖЬ)

Это заполняет число $ 1,200 на сводной вкладке. Теперь они щелкают ячейку и перетаскивают ее через следующие три строки, чтобы заполнить данные для этих полей. После заполнения сводная таблица выглядит следующим образом:

A B 1 Название предмета Заработанные доллары (январь) 2 Горячий кофе 1200 долларов США 3 Кофе со льдом 900 долларов США 4 Выпечка 700 долларов США 5 Специальные напитки 800 долларов США

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

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

Вот несколько советов, которые вы можете использовать при выполнении ВПР между двумя листами:

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

• Используйте кавычки для специальных символов: если в имени вашего листа есть специальные символы или пробелы, включение одинарных кавычек вокруг его имени в формуле гарантирует, что вы сможете найти правильные данные. Это может выглядеть так: “=ВПР(A2, ‘Покупки клиентов’!A2:E8, ЛОЖЬ)”.

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

• Просмотрите точные и приблизительные совпадения: если вы хотите искать точные совпадения для данных, убедитесь, что вы указали «FALSE» или «0» в качестве значения поиска диапазона. ВПР по умолчанию использует приблизительные совпадения, что означает, что если вы отсортировали данные в исходной таблице иначе, чем в целевой таблице, ваши значения будут заполняться на основе номера строки, а не значения, указанного в вашей таблице.

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