Excel распределяет данные по листам

Обновлено: 23.11.2024

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

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

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

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

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

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

Искомые_значения находятся в столбце A на листе Summary, и мы ссылаемся на первую ячейку данных, которая называется A2.
Table_array — это диапазон A2:B6 на листе за январь. Чтобы сослаться на него, добавьте к ссылке на диапазон имя листа, за которым следует восклицательный знак: Jan!$A$2:$B$6.

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

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

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

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

=ВПР(A2, февраль!$A$2:$B$6, 2, ЛОЖЬ)

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

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

Вид из другой книги

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

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

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

Виртуальный поиск по нескольким листам с помощью IFERROR

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

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

Сначала мы вытащим элементы. Для этого мы указываем формуле ВПР искать номер заказа в A2 на листе East и возвращать значение из столбца B (2-й столбец в table_array A2:C6 ). Если точное совпадение не найдено, выполните поиск на листе Запад. Если оба ВПР не пройдены, возвращается "Не найдено".

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

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

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

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

Виртуальный поиск в нескольких книгах

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

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

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

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

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

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

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

При вводе в столбец B значение COLUMNS($A$1:B$1) оценивается как 2, что указывает функции ВПР вернуть значение из второго столбца в массиве таблиц.

При копировании в столбец C (т. е. при перетаскивании формулы из B2 в C2) B$1 меняется на C$1, поскольку ссылка на столбец является относительной. Следовательно, COLUMNS($A$1:C$1) оценивается как 3, заставляя VLOOKUP возвращать значение из 3-го столбца.

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

Виртуальный поиск нескольких листов с помощью НЕПРЯМОГО

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

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

ВПР(искомое_значение, ДВССЫЛ("'"&ИНДЕКС(Листы_просмотра, ПОИСКПОЗ(1, --(СЧЁТЕСЛИ(ДВССЫЛ("'" & Список_листов< /em> & "'!искомый_диапазон"), искомое_значение)>0), 0)) & "'!табличный_массив"), < em>col_index_num, FALSE)

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

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

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

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

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

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

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

=ВПР($A2, ДВССЫЛ("'"&ИНДЕКС(Просмотр_листов, ПОИСКПОЗ(1, --(СЧЁТЕСЛИ(ДВССЫЛ("'"& Просмотр_листов&"'!$A$2:$A$6"), $A2) >0), 0)) &"'!$A$2:$C$6"), 2, ЛОЖЬ)

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

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

=ВПР($A2, ДВССЫЛ("'"&ИНДЕКС(Просмотр_листов, ПОИСКПОЗ(1, --(СЧЁТЕСЛИ(ДВССЫЛ("'" & Просмотр_листов & "'!$A$2:$A$6")), $A2 )>0), 0)) & "'!$A$2:$C$6"), 3, ЛОЖЬ)

=IFNA(ВПР($A2, ДВССЫЛ("'"&ИНДЕКС(Лист_просмотра, ПОИСКПОЗ(1, --(СЧЁТЕСЛИ(ДВССЫЛ("'" & Листы_просмотра & "'!$A$2:$A$6")), $A2)>0), 0)) & "'!$A$2:$C$6"), 3, FALSE), "Не найдено")

Виртуальный поиск нескольких листов между книгами

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

=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), "Не найдено")

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

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

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

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

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

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

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

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

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

СЧЁТЕСЛИ и ДВССЫЛ

В двух словах, ДВССЫЛ создает ссылки для всех листов поиска, а СЧЁТЕСЛИ подсчитывает количество вхождений значения поиска (A2) в каждом листе:

--(СЧЁТЕСЛИ( ДВССЫЛ("'"&Lookup_sheets&"'!$A$2:$A$6"), $A2)>0)

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

СЧЁТЕСЛИ сравнивает каждую ячейку в диапазоне A2:A6 на каждом листе подстановки со значением в A2 на основном листе и возвращает количество совпадений для каждого листа. В нашем наборе данных номер заказа в A2 (101) находится на листе Запад, который является 4-м в именованном диапазоне, поэтому СЧЁТЕСЛИ возвращает этот массив:

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

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

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

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

ВПР($A2, ДВССЫЛ("'"&ИНДЕКС(Лист_просмотра, ПОИСКПОЗ(1, , 0)) &"'!$A$2:$C$6"), 2, ЛОЖЬ)

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

На этом этапе вступает в действие классическая комбинация INDEX MATCH:

ИНДЕКС(Листы поиска, ПОИСКПОЗ(1, , 0))

Функция ПОИСКПОЗ, настроенная на точное совпадение (0 в последнем аргументе), ищет значение 1 в массиве и возвращает его позицию, которая равна 4:

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

Итак, формула сводится к следующему:

ВПР($A2, ДВССЫЛ("'"&" Запад "&"'!$A$2:$C$6"), 2, ЛОЖЬ)

ВПР и НЕПРЯМАЯ

Функция ДВССЫЛ обрабатывает текстовую строку внутри нее:

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

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

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

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

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

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

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

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

East_Sales — A2:B6 на листе "Восток"
North_Sales — A2:B6 на северном листе
Продажи на юге — A2:B6 на листе "Юг"
West_Sales – A2:B6 на листе West

ВПР и вложенные ЕСЛИ

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

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

=ВПР($A2, ЕСЛИ(B$1="восток", Восток_Продажи, ЕСЛИ(B$1="север", Север_Продажи, ЕСЛИ(B$1="юг", Юг_Продажи, ЕСЛИ(B$1="запад" , West_Sales)))), 2, FALSE)

В переводе на английский часть ЕСЛИ гласит:

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

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

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

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

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

При работе с большим количеством листов несколько уровней вложенности могут сделать формулу слишком длинной и трудной для чтения. Гораздо лучший способ — создать динамический диапазон vlookup с помощью INDIRECT:

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

Здесь мы объединяем ссылку на ячейку, содержащую уникальную часть именованного диапазона (B1) и общую часть (_Sales). Это создает текстовую строку, например "East_Sales", которая ДВССЫЛЕННО преобразуется в имя диапазона, понятное Excel.

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

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

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

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

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

Существует два способа консолидации данных: по позиции или по категории.

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

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

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

Примечание. Примеры в этой статье были созданы в Excel 2016. Хотя ваше представление может отличаться, если вы используете другую версию Excel, шаги те же.

Выполните следующие действия, чтобы объединить несколько листов в один главный лист:

Если вы еще этого не сделали, настройте данные на каждом составном листе, выполнив следующие действия:

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

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

Убедитесь, что все диапазоны имеют одинаковый макет.

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

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

Нажмите «Данные»> «Консолидировать» (в группе «Инструменты данных»).

В поле «Функция» выберите итоговую функцию, которую вы хотите использовать для консолидации данных в Excel. Функция по умолчанию — СУММ.

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

Выберите свои данные.

Затем в поле "Ссылка" нажмите кнопку "Свернуть", чтобы уменьшить панель и выбрать данные на листе.

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

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

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

Во всплывающем окне «Консолидация» нажмите «Добавить». Повторите это, чтобы добавить все диапазоны, которые вы объединяете.

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

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

Если вам нужно изменить размер диапазона или заменить диапазон, щелкните диапазон во всплывающем окне «Консолидация» и обновите его, выполнив описанные выше действия. Это создаст новую ссылку на диапазон, поэтому вам нужно будет удалить предыдущую перед повторной консолидацией. Просто выберите старую ссылку и нажмите клавишу Delete.

Нажмите «ОК», и Excel создаст для вас консолидацию. По желанию можно применить форматирование. Форматирование необходимо только один раз, если только вы не запустите консолидацию повторно.

Любые ярлыки, которые не совпадают с ярлыками в других исходных областях, приводят к отдельным строкам или столбцам в консолидации.

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

Если данные для консолидации находятся в разных ячейках на разных листах:

Введите формулу со ссылками на ячейки других рабочих листов, по одной на каждый отдельный рабочий лист. Например, чтобы консолидировать данные из листов с названиями "Продажи" (в ячейке B4), "Кадры" (в ячейке F5) и "Маркетинг" (в ячейке B9), в ячейке A2 основного листа введите следующее:

Совет. Чтобы ввести ссылку на ячейку, например Sales!B4, в формулу без ввода текста, введите формулу до нужного места, затем щелкните вкладку листа и щелкните ячейку. Excel дополнит имя листа и адрес ячейки за вас. ПРИМЕЧАНИЕ: формулы в таких случаях могут быть подвержены ошибкам, так как очень легко случайно выбрать не ту ячейку. Также может быть сложно обнаружить ошибку после ввода сложной формулы.

Если данные для консолидации находятся в одних и тех же ячейках на разных листах:

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

Нужна дополнительная помощь?

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

Сертифицированное предприятие штата Нью-Йорк, принадлежащее женщине

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

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

Теперь мне нужны три отдельных листа, по одному для каждого представителя службы поддержки в столбце «Кому назначено», которые будут содержать запросы в службу поддержки только этого человека. Но помимо этого мне нужно, чтобы было возможно следующее:

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

Звучит сложно? Это не так!

Приведенные ниже шаги объясняют, как настроить это с помощью Excel 2007 или более поздней версии.

(Эта функция также доступна в предыдущих версиях Excel.)

Создайте все нужные листы в книге. Здесь я создал лист для каждого представителя службы поддержки.
Создайте именованный диапазон для главной таблицы. Выделите данные в таблице (включая заголовки), введите имя диапазона в поле имени слева от строки формул, затем нажмите Enter. Именованный диапазон необходим Microsoft Query для выбора данных для анализа.
Создайте таблицу на основе главной таблицы. Выделите свои данные в соответствии с шагами, описанными выше. Щелкните вкладку «Вставка». Щелкните Таблица. Этот шаг немного избыточен, но преобразование ваших данных в таблицу имеет массу преимуществ, основным из которых является то, что когда вы добавляете в нее новые данные, она автоматически расширяется для захвата новых данных. В свою очередь, это также автоматически расширит ваш именованный диапазон.
Перейдите к листу, на который вы хотите поместить результаты запроса. Это может быть другой рабочий лист или даже другая рабочая книга.
Перейдите на вкладку "Данные". Щелкните Из других источников. Выберите Из Microsoft Query. Появится окно выбора источника данных.
Выберите файлы Excel. Нажмите «ОК». Выберите окно рабочей книги.
Найдите и выберите книгу для запроса. Обратите внимание, что это может быть та же рабочая книга, что и та, которая у вас открыта в данный момент. Нажмите «ОК». Откроется мастер запросов.
Именованные диапазоны должны появиться в левой части окна в виде таблицы. Я выберу все столбцы слева для отображения на листах подмножества. Нажмите "Далее".
На шаге мастера «Фильтровать данные» выберите фильтр для данных, которые вы хотите отобразить на этом листе. Здесь я создаю лист Джерри, поэтому мне нужны только данные, где «Назначено» равно «Джерри». Нажмите кнопку "Далее.
Выполняйте оставшиеся шаги мастера, пока не будете готовы выбрать место назначения для своих данных. В диалоговом окне «Импорт данных» убедитесь, что в качестве способа вставки данных выбрана таблица. Я решил вставить данные в формате A1 на лист Джерри.
Поскольку вы вставили данные в виде таблицы, у вас есть специальная возможность Обновить, если вы вносите какие-либо изменения в основную таблицу. Вы можете найти «Обновить» на вкладке «Данные» или на вкладке «Дизайн инструментов для работы с таблицами».

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

16 ответов на вопрос «Как связать данные Excel на нескольких листах»

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

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

Если по какой-либо причине диалоговое окно не отображается при открытии файла, вы можете получить к нему доступ с помощью ленты. Перейдите на вкладку «Данные» на ленте в Excel. Нажмите кнопку «Редактировать ссылки» в группе «Подключения». Откроется диалоговое окно «Редактировать ссылки».

С наилучшими пожеланиями,
Морин (отвечает за Райана)

Это было именно то, что мне было нужно. Все заполнено на рабочих листах, как описано. Однако, когда я обновляю мастер, он не обновляется на соответствующем листе (используя Excel 2007).

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

Я смог следовать вашим инструкциям, и все работало, пока я не изменил имя файла Excel, все ссылки не работали.

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

Есть какие-либо предложения, кроме перехода к каждому соединению (у меня их 10) и изменения указателей?

Этот метод описывает распределение данных "один ко многим". Как насчет отношения «многие к одному»? Я собираю данные из 4 запросов Access (в 4 базах данных) в 4 таблицы в одной книге Excel. Я хочу суммировать определенные столбцы из каждой из четырех таблиц в одной вкладке/таблице «SummaryData» в той же книге. Как я могу создать одну связанную таблицу, которая автоматически обновляется новыми записями в таблицах входных данных?
Мне пришлось выбрать вариант отображения «Системных таблиц» при просмотре подключений.
Я попытался создать таблицу SummaryData, к которой я бы подключил четыре питающие таблицы, но на ленте значки подключения к данным отображаются серым цветом, поэтому мне приходится начинать с нуля, что работает для подключения к одной таблице, но я хочу четыре из них обновляют одну главную таблицу. Возможно?
Спасибо, я немного тороплюсь. Я хочу сделать это приложение удобным для других пользователей, поэтому я задокументирую, как обновлять ссылки, но сначала оно должно работать!

Это именно то, что я хочу сделать. Однако, когда я пытаюсь выполнить шаги, на шаге 5 я обнаруживаю, что когда я нажимаю вкладку «Данные», я не вижу «Из других источников». Ближайший вариант, который у меня есть, — «Получить внешние данные», и ни один из вариантов оттуда не кажется тем, что мне нужно. Я использую Excel для Mac 2011. Есть ли простое решение, которое я не вижу?

Я искал и искал - я ошибаюсь в том, что это не переносится в Google Apps Excel с формой?

Я проверил, и я согласен, что он не передается. В исследовании указано, что приложение позволяет передавать основные файлы Excel. Это ограниченная версия Excel.

Этот совет очень помог мне настроить лист Excel. Он работает хорошо, пока другой пользователь не откроет лист Excel на другом компьютере. У меня есть лист, сохраненный на моем сервере, и я надеялся, что другие смогут получить к нему доступ с моего общего диска и внести изменения.Каждый раз, когда другой пользователь пытался обновить данные, на листе появляется сообщение об ошибке «Нераспознанный формат базы данных». Есть ли способ исправить это? Спасибо!

Привет… Это именно то, что я искал. И это работает! Но возможно ли извлечь данные из других листов без превращения одной из строк в заголовок в виде раскрывающегося списка? Все мои листы уже имеют одинаковую строку заголовка. Все, что я хочу, это чтобы данные, которые существуют под строкой заголовка на трех других листах, выводились под строку заголовка на моем мастере всякий раз, когда я его открываю. Спасибо! –Шон

Кажется, не работает, если включены автоматические фильтры? Я пытаюсь вести основной список контактов на листе 1 и подкатегории на листе 2,3 и т. д., чтобы их можно было распечатать по отдельности. Есть мысли?

У меня есть 2 электронные таблицы в рабочей тетради (МАСТЕРСКАЯ И АКЦИЯ), которые требуют одинаковых списков продуктов и поставщиков в первых 2 столбцах. У них обоих есть несколько других столбцов, которые очень важны для акций, но уникальны для каждой электронной таблицы.

Я использую Microsoft Query в электронной таблице АКЦИИ, чтобы убедиться, что столбцы моих продуктов и поставщиков точно такие же, как в таблице ГЛАВНАЯ.

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

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

Надеюсь, это имеет смысл? Вы можете помочь?

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

Спасибо за ваш комментарий – это был отличный вопрос!

Вы совершенно правы: как только ссылка перемещается с «главного» листа на «подлист», ссылка ведет себя как текст, а не как ссылка. На сегодняшний день мне не удалось найти обходной путь для этой проблемы.

Вот некоторые веб-сайты, на которых решается проблема с неработающими ссылками Excel:

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

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

Краткий обзор связывания данных

Давайте кратко рассмотрим основы связывания данных листа. Рабочие листы, предоставляющие данные, являются исходными рабочими листами. Лист, содержащий формулу для суммирования данных из исходных листов, называется рабочим листом назначения. А ссылка на ячейку на другом листе имеет следующий формат: SheetName ! Ссылка на ячейку .

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

Суммирование по всем листам в книге

У нас есть лист назначения, итоги и 3 листа городов. Как показано ниже, все 3 листа городов имеют одинаковый формат. Формула в ячейке D4 рабочего листа «Итоги» суммирует «Единицы» в ячейке B3 (элемент синего цвета) по всем рабочим листам города (источника).

Чтобы просуммировать ячейку B3 по всем листам книги, введите: =СУММ('*' ! b3 )

Синтаксис =СУММ('*' ! CellReference ) . Используется функция СУММ, а звездочка, заключенная в одинарные кавычки, сообщает Excel о суммировании по ВСЕМ листам в книге.

После нажатия клавиши ввода в строке формул отображается =СУММ (Милан:Торонто ! B3 ). Это формула, которую Excel действительно хранит. Excel указывает диапазон ячеек: первое и последнее имена рабочих листов, разделенные двоеточием, а буква "b" заглавная.

Формула также может быть написана длинным образом, перечисляя каждый рабочий лист и ячейку:

=СУММ( Милан!B3 , Лондон!B3 , Торонто!B3 )

=СУММ( Милан!B3 , Лондон!B3 ,
Торонто!B3 )

Суммирование по выбранным листам в книге

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

Чтобы суммировать значения Тампы и Торонто и исключить Милан, введите: =СУММ (Тампа:Торонто ! B3 )

Вставка рабочего листа с существующими формулами SUM Link

Будьте осторожны при вставке новых рабочих листов. Если вы использовали простую формулу, например. =СУММ('*' ! B3 ), чтобы просуммировать по всем листам книги, Excel включит новые данные листа в формулу суммирования. Это может быть или не быть тем, что вам нужно.

Аналогично, если вы указали диапазон в формуле, например =СУММ (Милан:Торонто ! B3 ) и вставьте лист внутри диапазона, Excel включит лист в формулу.

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

Положение целевого листа в книге

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

Рабочий лист "Итоги" находится посередине шести других листов. Когда мы ввели =СУММ ('*' ! b3 ), Excel создал формулу, показанную в строке формул и показанную ниже, указав два диапазона рабочих листов: от Милана до Лондона и от Парижа до Торонто:

=СУММ( Милан:Лондон!B3 , Париж:Торонто!B3 )

=СУММ( Милан:Лондон!B3 ,
Париж:Торонто!B3 )

Обзор

Если это ваша первая попытка связать данные листа в Microsoft Excel, мы рекомендуем вам прочитать наше вводное руководство «Как связать данные электронной таблицы Excel».

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

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

Читайте также: