зміст
Microsoft Excel має багато інструментів для збору даних з кількох таблиць (з різних аркушів або з різних файлів): прямі посилання, функц. НЕПРАВИЛЬНО (НЕПРЯМИЙ), надбудови Power Query та Power Pivot тощо. З цього боку барикади все виглядає добре.
Але якщо ви зіткнетеся зі зворотною проблемою – рознести дані з однієї таблиці на різні аркуші – то все буде набагато сумніше. На даний момент цивілізованих вбудованих засобів для такого розділення даних в арсеналі Excel, на жаль, немає. Тож вам доведеться використовувати макрос у Visual Basic або використовувати комбінацію запису макросів + Power Query з невеликим «покращенням файлу».
Давайте детальніше розглянемо, як це можна реалізувати.
Постановка проблеми
Маємо в якості вихідних даних таку таблицю розміром більше 5000 рядків для продажів:
Завдання: розподілити дані цієї таблиці по містах на окремих аркушах цієї книги. Ті. на виході потрібно отримати на кожному аркуші тільки ті рядки з таблиці, де була розпродаж у відповідному місті:
Готувати
Щоб не ускладнювати код макросу і зробити його максимально зрозумілим, виконаємо пару підготовчих кроків.
По-перше, створити окрему таблицю пошуку, де в одній колонці буде перелічено всі міста, для яких ви хочете створити окремі аркуші. Звичайно, цей каталог може містити не всі міста, присутні у вихідних даних, а лише ті, для яких нам потрібні звіти. Створити таку таблицю найпростіше за допомогою команди Дані – видалити дублікати (Дані — видалити дублікати) для копії колонки Місто або функція UNIK (УНІКАЛЬНИЙ) – якщо у вас остання версія Excel 365.
Оскільки нові аркуші в Excel створюються за замовчуванням перед (ліворуч) від поточного (попереднього), також має сенс сортувати міста в цьому каталозі за спаданням (від Я до А) – тоді після створення місто аркуші будуть розташовані в алфавітному порядку.
По-друге, пперетворити обидві таблиці на динамічні («розумні»), щоб з ними було легше працювати. Використовуємо команду Домашня сторінка – Формат у вигляді таблиці (Домашня сторінка — форматувати як таблицю) або комбінацію клавіш Ctrl+T. На вкладці, що з’явиться Конструктор (Дизайн) давайте їх покличемо tablProdaji и TableCityвідповідно:
Спосіб 1. Макрос поділу на аркуші
На вкладці Додатково розробник (розробник) натисніть на кнопку Візуальний Бейсік або скористайтеся комбінацією клавіш інший+F11. У вікні редактора макросів, що відкриється, вставте новий порожній модуль через меню Вставка – модуль і скопіюйте туди наступний код:
Sub Splitter() Для кожної комірки в Range("таблГорода") Range("таблПродажи").AutoFilter Field:=3, Criteria1:=cell.Value Range("таблПродажи[#All]").SpecialCells(xlCellTypeVisible).Copy Sheets.Add ActiveSheet.Paste ActiveSheet.Name = cell.Value ActiveSheet.UsedRange.Columns.AutoFit Next cell Worksheets("Данные").ShowAllData End Sub Ось з петлею Для кожного … Далі реалізовано перехід по комірках довідника TableCity, де для кожного міста фільтрується (метод Автофільтр) у вихідній таблиці продажів, а потім скопіювати результати на щойно створений аркуш. Попутно створений аркуш перейменовується на однойменне місто і на ньому для краси включається автопідбір ширини стовпців.
Створений макрос можна запустити в Excel на вкладці розробник button Макрос (Розробник — Макроси) або комбінацію клавіш інший+F8.
Спосіб 2. Створіть кілька запитів у Power Query
У попереднього способу, при всій його компактності та простоті, є істотний недолік – аркуші, створені макросом, не оновлюються при внесенні змін у вихідну таблицю продажів. Якщо необхідно оновлення на льоту, то доведеться використовувати пакет VBA + Power Query, а точніше, створювати за допомогою макросу не просто листи зі статичними даними, а оновлені запити Power Query.
Макрос в цьому випадку частково схожий на попередній (в ньому також є цикл Для кожного … Далі для повторення міст у каталозі), але всередині циклу більше не буде фільтрації та копіювання, а буде створюватися запит Power Query і завантажувати його результати на новий аркуш:
Sub Splitter2() Для кожної клітинки в Range("City table") ActiveWorkbook.Queries.Add Name:=cell.Value, Formula:= _ "let" & Chr(13) & "" & Chr(10) & " Source = Excel.CurrentWorkbook(){[Name=""TableSales""]}[Content]," & Chr(13) & "" & Chr(10) & " #""Changed Type"" = Table.TransformColumnTypes(Source , {{""Категорія"", введіть текст}, {""Назва"", введіть текст}, {""Місто"", введіть текст}, {""Менеджер"", введіть текст}, {""Угода" date "", type datetime}, {""Cost"", type number}})," & Chr(13) & "" & Chr(10) & " #""Рядки із застосованим фільтром"" = Table.Se " & _ "lectRows(#""Змінений тип"", кожен ([Місто] = """ & 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;Джерело даних=$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 = xlInsertDeleteCells .SavePassword = False . SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .PreserveColumnInfo = True .ListObject.DisplayName = cell.Value .Refresh BackgroundQuery:=False End With ActiveSheet.Name = cell.Value Next cell End Sub Після його запуску ми побачимо ті самі аркуші по містах, але формувати їх будуть вже створені запити Power Query:
При будь-яких змінах у вихідних даних достатньо буде оновити відповідну таблицю правою кнопкою миші – команда Update & Save (Оновити) або оновити всі міста одночасно масово за допомогою кнопки Оновити всі таб дані (Дані — оновити все).
- Що таке макроси, як їх створювати та використовувати
- Збереження аркушів робочої книги в окремих файлах
- Збір даних з усіх аркушів книги в одну таблицю