Съдържание
Microsoft Excel има много инструменти за събиране на данни от няколко таблици (от различни листове или от различни файлове): директни връзки, функция НЕПРЯК (НЕПРЯК), добавки за Power Query и Power Pivot и т.н. От тази страна на барикадата всичко изглежда добре.
Но ако се сблъскате с обратен проблем - разпространение на данни от една таблица в различни листове - тогава всичко ще бъде много по-тъжно. В момента в арсенала на Excel няма цивилизовани вградени инструменти за такова разделяне на данни, за съжаление. Така че ще трябва да използвате макрос във Visual Basic или да използвате комбинацията за запис на макроси + Power Query с малко „прецизиране на файла“ след това.
Нека да разгледаме по-отблизо как това може да се приложи.
Формулиране на проблема
Имаме като първоначални данни такава таблица с размер над 5000 реда за продажби:
Задача: да разпределите данните от тази таблица по градове на отделни листове на тази книга. Тези. на изхода трябва да получите на всеки лист само онези редове от таблицата, където е била продажбата в съответния град:
Подгответе
За да не усложняваме кода на макроса и да го направим възможно най-лесен за разбиране, нека изпълним няколко подготвителни стъпки.
Първо, създайте отделна справочна таблица, където една колона ще изброява всички градове, за които искате да създадете отделни листове. Разбира се, тази директория може да не съдържа всички градове, присъстващи в изходните данни, а само тези, за които имаме нужда от отчети. Най-лесният начин да създадете такава таблица е да използвате командата Данни – премахване на дубликати (Данни — премахване на дубликати) за копиране на колона град или функция УНИК (ЕДИНСТВЕН ПО РОДА СИ) – ако имате най-новата версия на Excel 365.
Тъй като новите листове в Excel се създават по подразбиране преди (вляво от) текущия (предишния), също има смисъл градовете в тази директория да се сортират в низходящ ред (от Z до A) – тогава след създаването градът листовете ще бъдат подредени по азбучен ред.
На второ място, пконвертирайте и двете таблици в динамични („умни“), за да улесните работата с тях. Използваме командата Начало – Форматиране като таблица (Начало — Форматиране като таблица) или клавишна комбинация Ctrl+T. В раздела, който се показва конструктор (Дизайн) да им се обадим tablProdaji и TableCity, съответно:
Метод 1. Макрос за разделяне по листове
В раздела Разширени предприемач (Разработчик) щракнете върху бутона Visual Basic или използвайте клавишна комбинация Друг+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 в раздела предприемач бутон Макроси (Програмист — Макроси) или клавишна комбинация Друг+F8.
Метод 2. Създайте множество заявки в Power Query
Предишният метод, въпреки цялата си компактност и простота, има значителен недостатък - листовете, създадени от макроса, не се актуализират, когато се правят промени в оригиналната таблица за продажби. Ако е необходимо актуализиране в движение, тогава ще трябва да използвате пакета VBA + Power Query или по-скоро да създадете с помощта на макрос не само листове със статични данни, но актуализирани заявки на Power Query.
Макросът в този случай е частично подобен на предишния (той също има цикъл За всеки … Напред за итериране на градове в директорията), но вътре в цикъла вече няма да има филтриране и копиране, а създаване на заявка за Power Query и качване на нейните резултати в нов лист:
Sub Splitter2() За всяка клетка в Range("City table") ActiveWorkbook.Queries.Add Име:=cell.Value, Formula:= _ "let" & Chr(13) & "" & Chr(10) & " Източник = Excel.CurrentWorkbook(){[Name=""TableSales""]}[Content]," & Chr(13) & "" & Chr(10) & " #""Променен тип"" = 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 заявки ще ги формират:
При всякакви промени в изходните данни ще бъде достатъчно да актуализирате съответната таблица с десния бутон на мишката - командата Актуализиране и запазване (Опресняване) или актуализирайте всички градове наведнъж групово, като използвате бутона Актуализиране All етикет Дата (Данни — Обновяване на всички).
- Какво представляват макросите, как се създават и използват
- Запазване на листове с работна книга като отделни файлове
- Събиране на данни от всички листове на книгата в една таблица