Сборка данных со всех листов книги в одну таблицу
Постановка задачи
Допустим, что у нас есть книга с большим количеством листов, где на каждом листе находится таблица с данными по сделкам в этом городе:
Давайте будем исходить из следующих соображений:
- Структура и столбцов на всех листах одинаковая.
- Количество строк на всех листах разное.
- Листы могут в будущем добавляться или удаляться.
Наша задача - собрать все данные со всех листов в одну таблицу, чтобы потом с ней работать (фильтровать, сортировать, построить сводную и т.д.) Сделать это можно разными способами, но самыми удобными будут, пожалуй, Power Query и макросы.
Способ 1. Сборка данных с листов с помощью Power Query
Если вы ещё не сталкивались в своей работе с Power Query, то очень советую копнуть в этом направлении. Использование этой бесплатной и уже встроенной по умолчанию в Excel надстройки, способно полностью перевернуть весь ваш процесс сбора и анализа данных, упростив всё в разы. С задачей сбора данных с листов Power Query справляется весьма успешно.
Шаг 1. Подключаемся к файлу
Для начала, создадим новый пустой файл в Excel, куда и будут собираться данные.
Если у вас Excel 2010-2013 и вы установили Power Query как отдельную надстройку, то откройте вкладку Power Query, если у вас Excel 2016 или новее, то вкладку Данные (Data). Нажмите кнопку Получить данные / Создать запрос - Из файла - Книга Excel (Get Data / New Query - From file - From Excel) и укажите наш файл с исходными листами:
В появившемся окне Навигатора (Navigator) выберите слева любой лист и нажмите в правом нижнем углу кнопку Преобразовать данные (Transform Data) или Изменить (Edit):
Должно появиться окно редактора запросов Power Query, где отобразятся данные с выбранного листа. Поскольку нам нужен, на самом деле, не один лист, а все, то удалим в правой панели все шаги, кроме первого шага Источник (Source) используя крестик слева от названия шага:
То, что останется после удаления шагов - это список всех объектов, которые Power Query "видит" во внешних файлах, а это:
- листы (Sheet)
- "умные таблицы" (Table)
- именованные диапазоны (Defined Name)
- области печати (Print Area), которые, по сути, являются одним из видов именованного диапазона
Шаг 2. Отбираем нужные листы
В исходном файле может быть много всего лишнего, что нам не требуется собирать: случайные ненужные листы, служебные именованные диапазоны, побочные умные таблицы и т.п. Очень важно отфильтровать этот "информационный мусор", т.к. в будущем из-за любого подобного объекта наш запорс будет, скорее всего, вылетать с ошибкой или некорректно собирать данные. Для решения этой задачи можно использовать несколько подходов.Во-первых, легко можно отфильтровать нужные объекты по типу по столбцу Kind. Например, если вам нужны только листы:
Во-вторых, если нам нужны только видимые листы, то дополнительно можно отфильтровать ещё по столбцу Hidden.
В-третьих, если вы точно знаете размер таблиц, которые вам нужны, то можно легко добавить к нашему списку вычисляемый столбец с формулой, выводящей количество столбцов или строк и использовать потом эти числа для отбора. Для этого выберем на вкладке Добавление столбца - Настраиваемый столбец (Add Column - Custom Column) и введём в открывшееся окно следующую формулу (с учётом регистра):
Для подсчёта количества строк можно использовать аналогичную функцию Table.RowCount. Получившийся столбец затем можно использовать для фильтрации "мусорных" таблиц.
В-четвёртых, можно извлечь с каждого листа содержимое любой ячейки (например, А1) и использовать его для отбора. Например, если там нет слова "Товар", то это не наш лист. Для извлечения нужно будет также добавить вычисляемый столбец с такой конструкцией:
=[Data][Column1]{0}
Здесь:
- [Data] - имя столбца, где в каждой ячейке лежат таблицы с содержимым каждого листа (убийственная формулировка для рядового пользователя Excel, да, я знаю)
- [Column1] - имя столбца на листе, из которого мы хотим извлечь данные
- {0} - номер строки (считая с нуля), откуда мы хотим взять данные
После фильтрации "мусора" все добавленные вспомогательные столбцы можно, конечно же, спокойно удалить, оставив только колонки Name и Data.
Шаг 3. Разворачиваем таблицы
Теперь развернём содержимое таблиц в одно целое, используя кнопку с двойными стрелками в заголовке столбца Data, отключив флажок Использовать исходное имя столбца как префикс (Use original column name as prefix):
После нажатия на ОК Power Query соберёт для нас все данные в одну мегатаблицу со всех отобранных листов нашего файла:
Останется лишь "навести блеск", а именно:
- Поднять первую строку в шапку таблицы кнопкой Использовать первую строку в качестве заголовков (Use first row as headers) на вкладке Главная (Home).
- Переименовать первый столбец в Город двойным щелчком на заголовку.
- Удалить повторяющиеся шапки таблиц, попавшие в одну кучу вместе с данными, используя фильтр по столбцу Товар.
Всё. Осталось только дать нашему запросу подходящее имя (например, Сборка) в панели справа и выгрузить затем собранные данные обратно в Excel кнопкой Закрыть и загрузить на вкладке Главная (Home - Close & Load):
В будущем, при любых изменениях в исходном файле достаточно будет лишь обновить наш запрос, щелкнув по собранной таблице правой кнопкой мыши и выбрав команду Обновить (Refresh) или такой же кнопкой на вкладке Данные (Data) или сочетанием клавиш Ctrl+Alt+F5.
Плюсы такого подхода:
- Не нужно уметь программировать, всё делается быстро и почти без использования клавиатуры.
- Последовательность столбцов на разных листах может быть различной - это не играет роли, столбцы правильно встанут друг под друга в итоговой сборке.
- Можно быстро обновлять запрос при изменении исходных данных.
Минусы этого способа:
- Собираются только значения, т.е. формулы с исходных листов не сохраняются.
- Названия столбцов должны на всех листах совпадать с точностью до регистра.
- Нельзя выбрать какой именно диапазон берётся с каждого листа - это определяется автоматически (берётся всё, что есть).
- Для обновления нужен Excel 2016 или новее или установленная надстройка Power Query.
Способ 2. Сборка данных с листов макросом на VBA
Похожего результата можно добиться и с помощью более "классического" подохода - макросом на VBA. Для этого на вкладке Разработчик (Developer) нажмите кнпоку Visual Basic или воспользуйтесь сочетанием клавиш Alt+F11. В открывшемся окне добавьте новый модуль через меню Insert - Module и скопируйте туда текст вот такого макроса:
Sub CollectDataFromAllSheets()
Dim ws As Worksheet
Set wbCurrent = ActiveWorkbook
Workbooks.Add
Set wbReport = ActiveWorkbook
'копируем на итоговый лист шапку таблицы из первого листа
wbCurrent.Worksheets(1).Range("A1:D1").Copy Destination:=wbReport.Worksheets(1).Range("A1")
'проходим в цикле по всем листам исходного файла
For Each ws In wbCurrent.Worksheets
'определяем номер последней строки на текущем листе и на листе сборки
n = wbReport.Worksheets(1).Range("A1").CurrentRegion.Rows.Count
'задаем исходный диапазон, который надо скопировать с каждого листа - любой вариант на выбор:
Set rngData = ws.Range("A1:D5") 'фиксированный диапазон или
Set rngData = ws.UsedRange 'всё, что есть на листе или
Set rngData = ws.Range("F5").CurrentRegion 'область, начиная от ячейки F5 или
Set rngData = ws.Range("A2", ws.Range("A2").SpecialCells(xlCellTypeLastCell)) 'от А2 и до конца листа
'копируем исходный диапазон и вставляем в итоговую книгу со следующей строки
rngData.Copy Destination:=wbReport.Worksheets(1).Cells(n + 1, 1)
Next ws
End Sub
Запустить созданный макрос можно на вкладке Разработчик кнопкой Макросы (Developer - Macros) или с помощью сочетания клавиш Alt+F8. Макрос автоматически создаст новую книгу и скопирует туда нужные вам данные.
Плюсы такого подхода:
- Формулы с исходных листов сохраняются в сборке.
- Имена столбцов не играют роли.
- Макрос будет работать в любой версии Excel.
- Можно выбирать, что именно брать с каждого листа (конкретный фиксированный диапазон или весь лист).
Минусы этого способа:
- Последовательность столбцов на всех листах должна быть одинаковой, т.к. происходит, по сути, тупое копирование таблиц друг-под-друга.
- Защита от макросов должна быть отключена.
- Быстрого обновления, как это было с Power Query, здесь, к сожалению, не будет. При изменении исходных данных придётся запустить макрос повторно.
Способ 3. Готовый макрос из надстройки PLEX
Если лень возиться с макросами или Power Query, то можно пойти по пути наименьшего сопротивления - использовать готовый макрос (кнопка Собрать) из моей надстройки PLEX для Excel. Это, может, и не спортивно, но зато эффективно:
В общем, выбирайте любой удобный вам вариант и действуйте. Выбор - это всегда хорошо.
Ссылки по теме
- Что такое Power Query и с чем его едят.
- Как создавать свои макросы, использовать чужие и запускать их в своих файлах.
- Сборка данных из нескольких файлов с помощью Power Query.
Строки:
Set rngData = ws.Range("A1:D5") 'фиксированный диапазон или Set rngData = ws.UsedRange 'всё, что есть на листе или Set rngData = ws.Range("F5").CurrentRegion 'область, начиная от ячейки F5 илизабыли удалить?
Ошибка возникает при выполнении строчки:
#"Переименованные столбцы" = Table.RenameColumns(#"Измененный тип",{{"Абакан", "Город"}}),
Такая ошибка возникает, в том числе, при работе с файлами из папки.
Спасибо!
P.S.
Когда мы увидим Вашу книгу по PQ? Очень ждём!
С такой проблемой я часто встречаюсь при обработке файлов из папки. И технически с помощью условных столбцов решение этой проблемы мне известно. Но возможно, существует более изящное решение.
В случае файла Николая условный столбец может быть такой:
#"step_N" = Table.AddColumn(#"step_N-1", "NEW_COLUMN", each if [Column1] = "Товар" then "SheetName" else [Name],
Т.е. если в столбце "Column1" содержится слово "Товар", то в новый столбец вносим "SheetName", в противном случае наименование листа из столбца "Name".
Теперь необходимо удалить первый столбец с наименованиями листов, применить фильтр по слову не "Товар" и повысить первую строку.
Готово!
У меня в таком варианте PQ не корректно обрабатывает в случаях если порядок столбцов отличается, а названия столбцов полностью совпадают. Т.е. в итоге данные копируются именно в порядке столбцов на исходных листах.
Подскажите, пожалуйста, что я могу делать не так?
Скажите пожалуйста, а можно ли в этом приеме распространить шаги обработки ранее загруженных листов (например, отмена свертывания) на листы, добавляемые позднее? Если да, можете ли подсказать, как? Спасибо.
Подскажите как с помощью Power Query загрузить (сделать подключение ) к листу на этой же книге ?
Если я делаю "Получить данные- Из файла- Из книги Excel, то я посылаюсь на эту же книгу, но пишет ее адрес (как при внешнем источнике) : C:\Users\okurinna\Desktop\Flash report (short version) v3.xlsx а не лист Actual
Ниже приведено 2 запроса, которые я потом объеденяю в один целый:
Запрос 1:
let
Источник = Excel.Workbook(File.Contents("C:\Users\okurinna\Desktop\Flash report (short version) v3.xlsx" ), null, true),
Actual_Sheet = Источник{[Item="Actual",Kind="Sheet"]}[Data],
#"Удаленные верхние строки" = Table.Skip(Actual_Sheet,4),
#"Удаленные столбцы" = Table.RemoveColumns(#"Удаленные верхние строки",{"Column17", "Column18", "Column19"}),
#"Повышенные заголовки" = Table.PromoteHeaders(#"Удаленные столбцы", [PromoteAllScalars=true ] )
in
#"Повышенные заголовки"
Запрос 2:
let
Источник = Excel.Workbook(File.Contents("C:\Users\okurinna\Desktop\Flash report (short version) v3.xlsx" ) , null, true),
Actual_Sheet = Источник{[Item="Actual",Kind="Sheet"]}[Data],
#"Удаленные верхние строки" = Table.Skip(Actual_Sheet,4),
#"Повышенные заголовки" = Table.PromoteHeaders(#"Удаленные верхние строки", [PromoteAllScalars=true ] ),
#"Удаленные столбцы" = Table.RemoveColumns(#"Повышенные заголовки",{"Sum in USD", "Sum in USD with adjustments", "Reported month"}),
#"Переименованные столбцы" = Table.RenameColumns(#"Удаленные столбцы",{{"Adjustments in reported period", "Reported month"}}),
#"Переупорядоченные столбцы" = Table.ReorderColumns(#"Переименованные столбцы",{"Plan/Fact", "Reported month", "Project", "SOW ID ", "Cost/Revenue", "Category", "Article", "Date of operation", "raw data", "Working hours", "Surname Name", "Description", "Original sum", "Original currency", "EUR/USD", "Sum of adjustments in previous period"}),
#"Переименованные столбцы1" = Table.RenameColumns(#"Переупорядоченные столбцы",{{"Sum of adjustments in previous period", "Sum in USD"}}),
#"Строки с примененным фильтром" = Table.SelectRows(#"Переименованные столбцы1", each ([Sum in USD] <> null)),
#"Удаленные столбцы1" = Table.RemoveColumns(#"Строки с примененным фильтром",{"Article"}),
#"Добавлен пользовательский объект" = Table.AddColumn(#"Удаленные столбцы1", "Article", each Text.From([#"Cost/Revenue"]&" adjustments" ] ),
#"Переупорядоченные столбцы1" = Table.ReorderColumns(#"Добавлен пользовательский объект",{"Plan/Fact", "Reported month", "Project", "SOW ID ", "Cost/Revenue", "Category", "Article", "Date of operation", "raw data", "Working hours", "Surname Name", "Description", "Original sum", "Original currency", "EUR/USD", "Sum in USD"}),
#"Измененный тип" = Table.TransformColumnTypes(#"Переупорядоченные столбцы1",{{"Reported month", type date}})
in
#"Измененный тип"
У меня несколько таблиц на одной и той же книге, но они в разном формате. Мне нужно в Power Query потом писать разные запросы чтобы их потом привести к общей форме и соединить.
Мне крайне важно сделать этот файл автономным и не зависищем от внешних источников.
Как посоветуете ?
Заранее спасибо.
По способу 2 макрос создает отдельную книгу.
А как сделать чтобы он создавал дополнительный лист в той же книге, в которой он берет данные?
Вернее - как добавить лист это понятно, он добавляет, но потом код макроса ведь нужно тоже как-то скорректировать для копирования в новый лист, а не в новую книгу?
Возможно ли собирать данные из выделенных диапазонов со всех листов книги в 1 таблицу с копированием по столбцам.
Не строкам ниже как делает Макрос и PLEX, а столбцами справа.
Иными словами настроить макрос / PLEX чтобы собирал таблицу перетаскивая столбцы с разных листов в 1 таблицу.
Подскажите, пожалуйста, есть ли макрос обратный функионалу "Сборке данных со всех листов книги в одну таблицу" ? Т.е. необходимо из одной таблицы вывести определенные значения в отдельный файл и отправить на электронную почту ?
Помогите разобраться.
У меня есть книга, в которой 140 листов. на каждом таблица из 6-7 столбцов. 5 столбцов одинаковые, а 1-2 столбца могут присутствовать или отсутствовать на отдельных листах (в середине таблицы).
Мне нужно объединить данные из этих листов в один массив, но когда я делаю это с помощью PowerQuery - то столбцы с одинаковым названием не выстраиваются друг под другом. Хотя выше в примере прямо написано, что последовательность столбцов не имеет значения и они должны встать друг под другом. Названия столбцов идентичные.
Заранее благодарен!!!
Но я бы предположил:
1. Имена столбцов все-таки разные (проверяйте пробелы, непечатаемые знаки, русская "с" вместо английской "с" и т.п.
2. Вы как-то неправильно производите сборку (но тут надо видеть ваш файл).
Если пришлете ваш файл мне на почту - гляну, как будет минутка, ага?
тоесть у меня есть книга в которой 5 столбцов 1: Наименование товара 2: остаток на начало 3:приход 4: расход 5: остаток на конец. во второй книге стоит ведомость в которой есть такие же названия товаров и такие же стобцы с остатком, приходом, расходом и остатком на конец. ТАк вот мне надо значения из ведомости, тоесть из столбца расход внести в столбец в 1ой книге в расход, чтобы эти значения автоматически перешли во 1ую книгу в нужные места под нужным наименованием товара и без формул в1 книге. Пример : 1 книга( в столбце есть расход =5+5+10 под наименованием товара шарики), мне нужно приплюсовать из 2ой книги под таким же названием шарики, значения допусти 7 в 1ую книгу так, чтобы формулы в 1 книге остались свои, но теперь было в 1 книге =5+5+10+7
Подскажите пожалуйста как решить проблему с доступом.
Excel 2016. Формирую файл на сервере (локальная сеть фирмы). Проверяю, обновляю, все работает.
Если запустить файл с другого компьютера, подключенного к сети, и попытаться обновить данные, возникает ошибка:
[DataSource.Error] Отказано в доступе по пути \\сетевой путь на файл
В итоге работать с файлом (обновлять данные) может только тот, кто его создавал.
Как правильно настроить запросы, чтобы обновлять данные могли любые пользователи сети?
Заранее спасибо за ответ.
нужна помощь.
Есть книга , в ней 12 листов с разными месяцами, и есть лист СКЛАД, в котором есть расходники, как сделать чтобы из 12 листов на лист склад вносились данные? По наименованию и количеству
Т.е. есть таблица с данными, надо по каждой строке сформировать документ по шаблону, в котором данные попадут в конкретные места нового документа.
Заранее спасибо.
А у меня не высвечиваются столбцы с наименованиями "kind" и "hidden".
Есть только столбцы name и data.
Как тогда объединить выборочные таблицы??
Подскажите, пожалуйста, можно ли воспользовавшись сборкой в Power Query, сохранить формулы и ссылки в отобранных ячейках?
Спасибо за второй способ! Очень помог.
Но немного не понятно, как функцией ищется конец листа!
Под таблицей с данными есть подписи. Так вот подписи в сводном листе получаются только после всего свода!
Это мне нравится, как раз так и надо.
Правда в конце выскакивает ошибка, что не может скопировать, т.к. ячейки объединены. Но ничего не пропало. Со всех листов данные скопировались. В форме и правда есть объединённые яцейки. Но всё собралось верно. Не понимаю ошибку. Или может есть функция отмены объединения, что бы ей отменить перед обработкой листа?
Спасибо.
Собирал этим скриптом 4 формы из одной программы, но видимо делали разные люди.И шапка у разных форм формировалась по разному. То со столбца A во многих случаях, то с B, то вообще с E! Хотя было объединение по центру. Не сразу заметишь. пока разобрался...
И ещё, после таблицы с данными, перед подписями, которые всё правильно в колонке A начинались, была пустая строка.
Но я так понимаю, что команда:
Set rngData = ws.Range("A2", ws.Range("A2").SpecialCells(xlCellTypeLastCell)) 'от А2 и до конца листа'определяем номер последней строки на текущем листе и на листе сборки n = wbReport.Worksheets(1).Range("A1").CurrentRegion.Rows.CountПоэтому с каждых листов вставлялась таблица с подписями, но со следующего листа подписи перезаписывались новой таблицей.
В принципе получилось классно! Спасибо.
Интересно, как бы сделать определение для n также без учёта пустых строк.
Немного доработал скрипт, что бы перед таблицей проставлялось название чья она. Это указывается в двух строках из шапки.
Получилось так:
Sub CollectDataFromAllSheets() Dim ws As Worksheet Set wbCurrent = ActiveWorkbook Workbooks.Add Set wbReport = ActiveWorkbook 'копируем на итоговый лист шапку таблицы из первого листа wbCurrent.Worksheets(1).Range("A1:H13").Copy Destination:=wbReport.Worksheets(1).Range("A1") 'проходим в цикле по всем листам исходного файла For Each ws In wbCurrent.Worksheets 'определяем номер последней строки на текущем листе и на листе сборки n = wbReport.Worksheets(1).Range("A1").CurrentRegion.Rows.Count 'задаем исходный диапазон, который надо скопировать с каждого листа - любой вариант на выбор: 'Set rngData = ws.Range("A1:D5") 'фиксированный диапазон или Set rngData1 = ws.Range("A8") 'фиксированный диапазон или Set rngData2 = ws.Range("A5") 'фиксированный диапазон или 'Set rngData = ws.UsedRange 'всё, что есть на листе или 'Set rngData = ws.Range("F5").CurrentRegion 'область, начиная от ячейки F5 или Set rngData = ws.Range("A14", ws.Range("A14").SpecialCells(xlCellTypeLastCell)) 'от А2 и до конца листа ''Range("A1:D8").MergeCells = False 'копируем исходный диапазон и вставляем в итоговую книгу со следующей строки rngData1.Copy Destination:=wbReport.Worksheets(1).Cells(n + 1, 1) rngData2.Copy Destination:=wbReport.Worksheets(1).Cells(n + 2, 1) rngData.Copy Destination:=wbReport.Worksheets(1).Cells(n + 3, 1) Next ws End Subпо умолчанию мы проходим все листы книги:
'проходим в цикле по всем листам исходного файла
For Each ws In wbCurrent.Worksheets
Подскажите пожалуйста, как в макросе проходить все листы, кроме скрытых? что и где добавить в коде?
Скажите, пожалуйста, а как консолидировать листы из файла, находящегося на ЯндексДиске, с помощью Power Query?
У меня никак не получается...Может в ссылке ошибка...
А это вообще возможно?
У вас фильтруются получаемые листы по названию первого столбца.
Мне нужно отфильтровать получаемые именно таблицы - как опросить название первого столбца таблицы?
формулой =[Data][Column1]{0} выдаёт ошибку в "настраиваемом столбце"
Заранее спасибо!
нужно было собрать данные с каждого листа в свой столбец, немного изменил код, работает:
Sub CollectDataFromAllSheets()
Dim ws As Worksheet
Set wbCurrent = ActiveWorkbook
Workbooks.Add
Set wbReport = ActiveWorkbook
'проходим в цикле по всем листам исходного файла n=4 так как мне нужно начинать вставлять данные в итоговый файл с пятого столбца
n = 4
For Each ws In wbCurrent.Worksheets
n = n + 1
'определяем номер последнего столбца на текущем листе и на листе сборки
' n = wbReport.Worksheets(1).Range("A3";).CurrentRegion.Rows.Column
'задаем исходный диапазон, который надо скопировать с каждого листа - любой вариант на выбор:
Set rngData = ws.Range("e3:e501";) 'фиксированный диапазон в каждом листе где лежат мои данные или
'Set rngData = ws.UsedRange 'всё, что есть на листе или
'Set rngData = ws.Range("F5";).CurrentRegion 'область, начиная от ячейки F5 или
'Set rngData = ws.Range("A2", ws.Range("A2";).SpecialCells(xlCellTypeLastCell)) 'от А2 и до конца листа
'копируем исходный диапазон и вставляем в итоговую книгу со следующего столбца в третью строку
rngData.Copy Destination:=wbReport.Worksheets(1).Cells(3, n)
'добавляем название листа откуда брали данные в ячейку выше
wbReport.Worksheets(1).Cells(2, n) = ws.Name
Next ws
End Sub