Как собрать данные с разных листов excel в одну таблицу
Обновлено: 30.06.2024
Очень часто при формировании отчётов (аналитики) основанных на больших объемах данных приходиться сводить в одну таблицу информацию с нескольких листов книги «Excel» .
В идеале для сбора и аналитики информации с разных листов «Эксель» желательно сделать сводную таблицу, которая будет брать данные из нескольких источников (листов книги).
Такую таблицу можно выполнить двумя разными способами.
- Создание сводной таблицы с разных листов при помощи стандартных возможностей и инструментов.
- Создание таблицы берущей данные с нескольких листов при помощи запроса сформированного в надстройке Power Query .
Рассмотрим первый способ.
Создание сводной таблицы с разных листов при помощи стандартных возможностей и инструментов.
Шаг первый.
Необходимо добавить в ленту инструментов кнопку мастера создания сводных таблиц и диаграмм.
Для этого следует кликнуть правой кнопкой мыши по ленте (панели инструментов) и выбрать из выпадающего меню пункт «Настройка ленты»
Настройка ленты
либо войти во вкладку
«Файл» => «Параметры» => «Настройка ленты».
Далее в настройках ленты из выпадающего списка под надписью «Выбрать команды» выбрать пункт «Все команды». В окне ниже отобразятся все возможные команды, которые можно разместить на панели, в алфавитном порядке.
Из списка выбираем «Мастер сводных таблиц и диаграмм»
Добавление мастера сводных таблиц
В правом окне при помощи кнопки «Создать группу» создаем новую группу инструментов. Для группы можно выбрать удобное для Вас наименование. Например, «Своя группа». Можно выбрать на какой вкладке будет создана группа. В своем примере я выбрал вкладку «Главная».
Когда группа создана, выделите ее курсором, выделите курсором «Мастер сводных таблиц и диаграмм» в левом окне и нажмите кнопку «Добавить >>».
После нажмите «Ок».
Теперь на главной вкладке панели инструментов находится инструмент «Мастер сводных таблиц и диаграмм».
Мастер сводных на панели
Шаг второй. Построение сводной таблицы из нескольких источников данных.
- Кликнуть по кнопке мастера построения сводных таблиц.
- На первом окне поставить флажок, напротив «в нескольких диапазонах консолидации» и флажок напротив «сводная таблица»
Второй способ.
Создание таблицы берущей данные с нескольких листов при помощи запроса сформированного в надстройке Power Query .
Данный способ заключается в использовании запроса надстройки Power Query.
О данной надстройке рассказывалось в статье: «Power Query» в «Excel» — что это?
Создание запроса Power Query для сведения нескольких страниц книги в одну таблицу.
Шаг первый.
Нужно создать два запроса, каждый из которых будет брать информацию из отдельной таблицы.
Шаг два.
Для этого во вкладке Power Query нужно нажать кнопку «Из таблицы» и указать в появившемся окне диапазон – источник данных. После чего нажать «Ок».
Power Query из таблицы
Шаг три.
Когда создан второй запрос, нужно во вкладке Power Query кликнуть по кнопке «Слияние запросов» и настроит в появившемся окне вид получившейся общей таблицы.
Слияние запросов
Шаг четыре.
Когда вид настроен, нужно нажать кнопку «Закрыть и загрузить.»
Надстройка Power Query соберет данные с двух листов и соединит их в одной таблице.
Дано: 22 таблицы унифицированного формата с перечнем помещений от 22 дочерних предприятий.
Задача: сделать сводную таблицу с данными всех 22 таблиц
Итак, решение. Есть три варианта решения данной задачи. Первый - использовать встроенную (начиная с excel 2013) надстройку PowerPivot (об этом методе мы поговорим позднее). Второй - посредством сводной таблицы через несколько диапазонов консолидации. Не люблю этот метод, т.к. такая сводная таблица имеет существенно более ограниченный функционал по сравнению с простой сводной таблицей. Наконец, третий вариант - это создать общую таблицу с использованием функции INDIRECT (ДВССЫЛ). Этому методу и посвятим сегодня наш пост.
Что мы имеем? 22 одинаковых по формату таблицы на отдельных листах и с разным количеством строк в каждой. Соответственно, для построения общей таблицы нам надо решить следующие "проблемы":
1) как заставить общую таблицу "переключаться" с одного листа данных на другой?;
2) как заставить ее делать это в нужный момент (когда закончились строки на одном листе)?
Начнем со второго вопроса, т.к. ответ на него мы уже знаем. Это циклы, о которых мы подробно говорили в выпуске 2 "Циклы в Excel без VBA". Для решения проблемы достаточно в наш файл добавить лист с перечнем всех обществ, соответствующими названиями листов и количеством подсчетом количества строк на каждом таком листе.
Обратите внимание на формулу в столбце Кол-во записей. Мы могли бы использовать просто COUNTA (СЧЁТА) со ссылкой на каждый лист. Но это потребует времени для "линковки" каждого листа. Плюс мы с вами готовим универсальные решения, которые будут работать в независимости от количества листов.
Именно для этого в функцию COUNTA (СЧЁТА) и внедряется эта чуднАя функция INDIRECT (ДВССЫЛ). Остановимся на ней подробнее.
Функция имеет, по сути, единственный параметр - ref_text (ссылка_на_текст). Что делает эта функция? Всего-навсего преобразует текст в ссылку на ячейку. То есть она преобразует текст, например, "А1" в ссылку и возвращает значение ячейки А1.
Соответственно, зная несложные правила работы с текстом в Excel, мы можем легко сделать текст внутри изменяемым, а значит можем сделать и изменяемыми ссылки, которые будет возвращать функция INDIRECT (ДВССЫЛ).
Разберем пример выше. Функция INDIRECT (ДВССЫЛ) имеет следующий вид:
Чтобы понять, что означает текст внутри нее, давайте вспомним, как выглядит в Excel ссылка на ячейку на другом листе. Вот так:
'Название листа'!A1
Соответственно, в нашем случае у нас неизменен столбец B:B, а вот название листа меняется. Для "автоматизации" формулы заменяем Название листа на ссылку на название листа с данными по соответствующему обществу (столбец Название листа). Получаем следующее:
" ' " & A1 & " '!B:B "
где все, что подчеркнуто - текст, а выделено жирным - ссылки.
ВАЖНО! Обратите внимание на кавычки и конкатенацию ("склеивание") при помощи амперсента. Не забудьте, что текст вносится в кавычках и соединяется со ссылками при помощи символа &.
Получается, что INDIRECT (ДВССЫЛ) получает название листа из ячеек в столбце А:А (Название листа), а диапазон у нас прописан фиксированно текстом (В:В). COUNTA (СЧЁТА) же просто считает количество строк в столбце В:В на соответствующем листа за вычетом заголовка.
Перейдем к формированию общей таблицы. Как вы уже наверное прикинули, у нас будет два вложенных цикла: один - количество обществ, второй - количество записей на листе данных по соответствующему обществу. Соответственно, нам потребуются два вспомогательных столбца (определим их в столбцы А и В). Ровно так же, как мы делали на примере в выпуске 2 "Циклы в Excel без VBA", пишем формулу первого цикла:
=IF(A2="";"";IF(COUNTIF($A$2:A2;A2)=OFFSET('Список обществ'!$E$2;A2-1;0);IF(A2+1>COUNTA('Список обществ'!A:A)-1;"";A2+1);A2))
Затем пишем формулу второго цикла:
Далее при помощи уже знакомой нам функции OFFSET (СМЕЩ) заполняем столбцы из таблицы на листе Список обществ.
Осталось подтянуть данные с 22 листов по обществам. Тут нам опять поможет OFFSET (СМЕЩ), но уже в паре с INDIRECT (ДВССЫЛ).
OFFSET (СМЕЩ) передает в формулу название листа, а конструкция B2+1 - номер строки в столбце Е на том листе.
ВАЖНО! Обратите внимание, что OFFSET (СМЕЩ) вкладывается внутрь INDIRECT (ДВССЫЛ), а не наоборот.
Через некоторое время вам может потребоваться собрать данные с нескольких листов в один мастер-лист для объединения или выполнения других операций, как показано на скриншоте ниже, но копировать и вставлять содержимое одно за другим проблематично, есть ли какие-нибудь уловки, которые могут быстро решить эту проблему в Excel?
Собирайте данные из нескольких листов в один с помощью мощной функции объединения
Собирайте данные с нескольких листов в один с помощью функции консолидации
Если вы хотите собрать данные из нескольких листов на один лист в одной книге, вы можете применить функцию консолидации в Excel.
1. На новом листе книги, с которого вы хотите собирать данные, щелкните Данные > Консолидировать.
2. в Consolidate диалог, выполните следующие действия:
(1 Выберите одну операцию, которую вы хотите выполнить после объединения данных в функция раскрывающийся список;
(2 Нажмите для выбора диапазона каждого листа, который вы хотите собрать;
(3 Нажмите Add кнопку, чтобы добавить диапазон данных в All references список;
(4 Проверьте метки, которые вы используете после объединения данных, и проверьте Create links to source data вариант, если вы хотите связать данные в объединенном листе с исходными данными.
3. Нажмите OK. Теперь данные собраны и суммированы на одном листе.
Легко объединяйте несколько листов / книгу в один лист или рабочую книгу
Собирайте данные из нескольких листов в один с кодом VBA
Вот код VBA, который может собирать данные со всех листов на новом листе в текущей книге.
1. Нажмите Alt + F11 ключ для включения Microsoft Visual Basic for Applications окно.
2. Нажмите Insert > Module , скопируйте и вставьте приведенный ниже код в Module скрипты.
VBA: соберите все данные из таблиц в один.
3. Нажмите F5 ключ для запуска кода, все данные всей книги были собраны в новом листе Combined.
Собирайте данные с нескольких листов в один с помощью мощных Combine функция
Если вам обычно нужно объединить листы в один, я предлагаю вам одну мощную функцию объединения, это Kutools for ExcelАвтора Сочетать Утилита, которая имеет следующие четыре основные функции:
Объедините несколько листов из книги в один лист
Объедините все листы с одинаковыми именами в один лист
Объедините несколько листов из книг в одну книгу
Объединяйте и вычисляйте значения из нескольких книг на одном листе
После бесплатная установка Kutools for Excel, сделайте следующее:
Если вы просто хотите объединять листы на один лист без каких-либо других операций, вы можете сделать следующее:
1. Включите Excel, щелкните Kutools Plus > Combine , появится один или два диалоговых окна, чтобы напомнить вам некоторые уведомления, просто нажмите OK и Да продолжать. Смотрите скриншот:
2. Нажмите OK , чтобы перейти к Combine окно, проверка Combine multiple worksheets from workbook into worksheet опцию.
3. Нажмите Next , затем нажмите Add > File / Folder чтобы добавить книги, которые вы будете использовать для объединения в Workbook list , Затем перейдите к Worksheet list чтобы проверить листы, которые вы хотите объединить, из каждой книги.
4. Нажмите Next чтобы перейти к последнему шагу, вы можете указать некоторые настройки результата комбинирования по своему усмотрению. Если вам не нужен этот шаг.
5. Нажмите Finish и выберите одну папку для размещения новой книги, объединяющей данные из листов.
6. Нажмите Сохраните. Теперь все данные собраны из листов книги в один мастер-лист.
Объединить все листы из рабочих тетрадей в один лист
Если ты хочешь консолидировать рабочие листы из разных книг в одну, выполните следующие действия:
1. в Combine окно, проверка Consolidate and calculate values across multiple worksheets into one worksheet опцию.
2. Нажмите Next , и добавить файлы в Workbook list , затем проверьте листы, которые вы используете для объединения и расчета.
3. Нажмите Next , затем выберите одно вычисление из Function раскрывающийся список и при необходимости отметьте другие параметры.
4. Нажмите Finish чтобы сохранить новую книгу в папке.
Теперь листы в книгах объединены и рассчитаны в один лист.
Иногда может возникнуть необходимость в объединении данных из разных файлов в одну таблицу. Если их много, а информация размещена на разных листах, то обычное копирование займет много времени.
Решить данную задачу поможет Power Query – встроенный в MS Excel инструмент обработки данных.
Рассмотрим пример объединения файлов с общей «шапкой» и данными на разных листах:
2. Power Query покажет все файлы, размещенные по указанному адресу:
3. Нажимаем «изменить», переходим в редактор запросов и фильтруем нужные файлы:
4. Удаляем все столбцы кроме «Content»:
5. Добавляем новый столбец и вводим формулу с учетом регистра:
Если есть предупреждение о незавершенном списке, то жмем «загрузить ещё»:
7. Удаляем столбцы кроме «Data», раскрываем его. Если есть предупреждение, то жмем «загрузить ещё»:
8. Power Query отразит сводные данные:
10. Скрываем фильтром лишние заголовки и выгружаем данные на новый/существующий лист:
Готово! Информация со всех листов всех файлов собрана в общую таблицу при существенно меньших трудозатратах по сравнению с ручным копированием.
Таким образом, Power Query позволяет решать широкий спектр задач без использования сложных запросов и формул, что делает его универсальным инструментом для обработки данных и повышения эффективности работы.
Читайте также: