Содержание:
Microsoft Excel имеет множество инструментов для сбора данных из нескольких таблиц (с разных листов или из разных файлов): прямые ссылки, функции КОСВЕННЫЕ (КОСВЕННО), надстройки Power Query и Power Pivot и т. д. С этой стороны баррикады все выглядит хорошо.
А вот если вы столкнетесь с обратной задачей — разнесением данных из одной таблицы на разные листы, — то все будет гораздо печальнее. На данный момент в арсенале Excel, к сожалению, нет цивилизованных встроенных инструментов для такого разделения данных. Поэтому вам придется использовать макрос в Visual Basic или использовать комбинацию средства записи макросов + Power Query с небольшой «уточнением файла» после этого.
Давайте подробнее рассмотрим, как это можно реализовать.
Постановка задачи
Имеем в качестве исходных данных такую таблицу размером более 5000 строк для продаж:
Задача: распределить данные из этой таблицы по городам на отдельных листах этой книги. Те. на выходе нужно получить на каждом листе только те строки из таблицы, где продажа была в соответствующем городе:
Подготовить
Чтобы не усложнять код макроса и сделать его максимально простым для понимания, выполним пару подготовительных действий.
Во-первых, создать отдельную таблицу поиска, где в одном столбце будут перечислены все города, для которых вы хотите создать отдельные листы. Конечно, в этом каталоге могут быть не все города, присутствующие в исходных данных, а только те, по которым нам нужны отчеты. Самый простой способ создать такую таблицу — использовать команду Данные – удаление дубликатов (Данные — Удалить дубликаты) для копирования столбца Город или функция UNIK (УНИКАЛЬНЫЙ) – если у вас установлена последняя версия Excel 365.
Поскольку новые листы в Excel по умолчанию создаются перед (слева от) текущего (предыдущего), то имеет смысл также отсортировать города в этом каталоге по убыванию (от Я до А) – тогда после создания город листы будут расположены в алфавитном порядке.
Во-вторых, ппреобразовать обе таблицы в динамические («умные»), чтобы с ними было проще работать. Мы используем команду Главная – Форматировать в виде таблицы (Главная страница — Форматировать как таблицу) или сочетание клавиш Ctrl+T. На появившейся вкладке Конструктор (Дизайн) давай позвоним им таблПродажи и СтолГородсоответственно:
Способ 1. Макрос для разделения по листам
На вкладке Дополнительно разработчик (Разработчик) нажмите на кнопку Визуальный Бейсик или используйте сочетание клавиш другой+F11. В открывшемся окне редактора макросов вставьте новый пустой модуль через меню Вставка – Модуль и скопируйте туда следующий код:
Sub Splitter() Для каждой ячейки в диапазоне("таблицаГорода") Range("таблицаПродажи").Поле AutoFilter:=3, Criteria1:=cell.Value Range("таблицаПродажи[#All]").SpecialCells(xlCellTypeVisible).Copy Листы.Добавить ActiveSheet.Вставить ActiveSheet.Name = cell.Value ActiveSheet.UsedRange.Columns.AutoFit Следующая ячейка Worksheets("Данные").ShowAllData End Sub Здесь с петлей Для каждого… Далее реализован проход по ячейкам каталога СтолГород, где для каждого города фильтруется (метод Автофильтр) в исходной таблице продаж, а затем скопировать результаты на вновь созданный лист. Попутно созданный лист переименовывается в одноимённое название города и на нём включается автоподбор ширины колонок для красоты.
Созданный макрос можно запустить в Excel на вкладке разработчик кнопка Макрос (Разработчик — Макросы) или сочетание клавиш другой+F8.
Способ 2. Создайте несколько запросов в Power Query.
Предыдущий метод при всей своей компактности и простоте имеет существенный недостаток – листы, созданные макросом, не обновляются при внесении изменений в исходную таблицу продаж. Если необходимо обновление на лету, то придется использовать связку VBA + Power Query, а точнее создавать с помощью макроса не просто листы со статическими данными, а обновляемые запросы Power Query.
Макрос в этом случае частично аналогичен предыдущему (в нем также есть цикл Для каждого… Далее для перебора городов в каталоге), но внутри цикла уже будет не фильтрация и копирование, а создание запроса Power Query и выгрузка его результатов на новый лист:
Sub Splitter2() для каждой ячейки в диапазоне («Таблица городов») ActiveWorkbook.Queries.Add Name:=cell.Value, Formula:= _ "let" & Chr(13) & "" & Chr(10) & " Источник = Excel.CurrentWorkbook(){[Name=""TableSales""]}[Content]," & Chr(13) & "" & Chr(10) & " #""Измененный тип"" = Table.TransformColumnTypes(Source , {{""Категория"", введите текст}, {""Имя"", введите текст}, {""Город"", введите текст}, {""Менеджер"", введите текст}, {""Сделка date "", type datetime}, {""Cost"", введите номер}})," & Chr(13) & "" & Chr(10) & " #""Строки с примененным фильтром"" = Table.Se " & _ "lectRows(#""Измененный тип"", каждый ([City] = """ & cell.Value & """))" & Chr(13) & "" & Chr(10) & "in " & Chr(13) & "" & Chr(10) & " #""Строки с примененным фильтром""" ActiveWorkbook.Worksheets.Add With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _ "OLEDB; Поставщик =Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=" & cell.Value & ";Extended Properties=""""" _ , Destination:=Range("$A$1")). QueryTable .CommandType = xlCmd Sql .CommandText = Array("SELECT * FROM [" & cell.Value & "]") .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlInsertDeleteC элли .SavePassword = Ложь. SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .PreserveColumnInfo = True .ListObject.DisplayName = cell.Value .Refresh BackgroundQuery:=False End With ActiveSheet.Name = cell.Value Следующая ячейка End Sub После его запуска мы увидим одни и те же листы по городам, но формировать их будут уже созданные запросы Power Query:
При любых изменениях исходных данных достаточно будет обновить соответствующую таблицу правой кнопкой мыши – командой Обновить и сохранить (Обновить) или обновить все города сразу массово с помощью кнопки Обновить все таб Данные (Данные — Обновить все).
- Что такое макросы, как их создавать и использовать
- Сохранение листов рабочей книги в отдельные файлы
- Сбор данных со всех листов книги в одну таблицу