содржина
Microsoft Excel има многу алатки за собирање податоци од неколку табели (од различни листови или од различни датотеки): директни врски, функција ИНДИРЕКТЕН (Индиректно), Power Query и Power Pivot додатоци, итн. Од оваа страна на барикадата, сè изгледа добро.
Но, ако наидете на обратен проблем – ширење податоци од една табела на различни листови – тогаш сè ќе биде многу потажно. Во моментов, во арсеналот на Excel, за жал, нема цивилизирани вградени алатки за такво раздвојување на податоците. Така, ќе мора да користите макро во Visual Basic или да ја користите комбинацијата на макро рекордер + Power Query со малку „рафинирање на датотеката“ потоа.
Ајде да погледнеме подетално како ова може да се спроведе.
Формулирање на проблемот
Како првични податоци имаме ваква табела со големина од повеќе од 5000 редови за продажба:
Задача: да се дистрибуираат податоците од оваа табела по град на посебни листови од оваа книга. Оние. на излезот, на секој лист треба да ги добиете само оние редови од табелата каде што продажбата беше во соодветниот град:
Подгответе
За да не го комплицираме макро кодот и да го направиме што е можно поедноставен за разбирање, ајде да извршиме неколку подготвителни чекори.
Прво, креирајте посебна табела за пребарување, каде што една колона ќе ги наведе сите градови за кои сакате да креирате посебни листови. Се разбира, овој директориум не може да ги содржи сите градови присутни во изворните податоци, туку само оние за кои ни се потребни извештаи. Најлесен начин да се создаде таква табела е да се користи командата Податоци - Отстранете ги дупликатите (Податоци - Отстранете ги дупликатите) за копија од колона Град или функција УНИК (ЕДИНСТВЕНИ) – ако ја имате најновата верзија на Excel 365.
Бидејќи новите листови во Excel стандардно се креираат пред (лево од) тековниот (претходниот), исто така има смисла градовите во овој директориум да се подредуваат по опаѓачки редослед (од Ш до А) - потоа по креирањето, градот листовите ќе бидат наредени по азбучен ред.
Второ, пконвертирај ги двете табели во динамични („паметни“) за полесно да се работи со нив. Ја користиме командата Дома – Форматирајте како табела (Дома - Формат како табела) или кратенка на тастатура Ctrl+T. На јазичето што се појавува Изведувач (Дизајн) ајде да ги повикаме tablProdaji и TableCity, соодветно:
Метод 1. Макро за делење по листови
На јазичето Напредно инвеститорот (програмер) кликнете на копчето Visual Basic или користете кратенка на тастатурата Alt+F11. Во прозорецот за макро уредувач што се отвора, вметнете нов празен модул низ менито Вметни – Модул и копирајте го следниов код таму:
Под разделувач() За секоја ќелија во опсегот ("таблГорода") Опсег ("таблПродажи").Поле за автоматско филтрирање:=3, Критериуми1:=ќелија.Опсег на вредност ("таблПродажи[#Сите]").SpecialCells(xlCellTypeVisible).Копирај Лист. Овде со јамка За секој… Следно го имплементирал преминот низ ќелиите на директориумот TableCity, каде што за секој град се филтрира (метод Автофилтер) во оригиналната табела за продажба и потоа копирање на резултатите на новосоздадениот лист. Попатно, креираниот лист се преименува во истото име на градот и на него е вклучено автоматско поставување на ширината на колоните за убавина.
Можете да го извршите креираното макро во Excel на јазичето инвеститорот копчето. Макроа (Програмер - макроа) или кратенка на тастатура Alt+F8.
Метод 2. Креирајте повеќе прашања во Power Query
Претходниот метод, и покрај сета своја компактност и едноставност, има значителен недостаток - листовите создадени од макрото не се ажурираат кога се прават промени во оригиналната табела за продажба. Ако е неопходно ажурирање веднаш, тогаш ќе мора да го користите пакетот VBA + Power Query, или подобро кажано, да креирате користејќи макро не само листови со статични податоци, туку ажурирани барања за Power Query.
Макрото во овој случај е делумно слично на претходното (исто така има циклус За секој… Следно да се повторува преку градовите во директориумот), но внатре во јамката веќе нема да има филтрирање и копирање, туку создавање на барање за Power Query и поставување на неговите резултати на нов лист:
Под Splitter2() За секоја ќелија во опсег ("Градска табела") ActiveWorkbook.Queries.Add Name:=cell.Value, Formula:= _ "let" & Chr(13) & "" & Chr(10) & " Извор = Excel.CurrentWorkbook(){[Name=""TableSales""]}[Содржина]," & Chr(13) & "" & Chr(10) & " #""Променет тип"" = Table.TransformColumnTypes(Извор , {{""Категорија"", напишете текст}, {""Име"", напишете текст}, {""Град"", напишете текст}, {""Менаџер"", напишете текст}, {""Зделка" датум "", напишете datetime}, {""Cost"", тип број}})," & Chr(13) & "" & Chr(10) & " #""Редови со применет филтер"" = Табела. Види " & _ "lectRows(#""Променет тип"", секој ([Град] = """ и ќелија.Вредност & """))" & Chr(13) & "" & Chr(10) & "во " & Chr(13) & "" & Chr(10) & " #""Редови со применет филтер""" ActiveWorkbook.Worksheets.Add With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _ "OLEDB; Провајдер =Microsoft.Mashup.OleDb.1;Извор на податоци=$Работна книга$;Локација=" & cell.Value & ";Extended Properties=""""" _ , Дестинација:=Range("$A$1")). QueryTable .CommandType = xlCmd Sql .CommandText = Array("SELECT *FROM [" & cell.Value & "]") .RowNumbers = False .FillAdjacentFormulas = False. SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .PreserveColumnInfo = True .ListObject.DisplayName = cell.Value .Refresh BackgroundQuery:=False End With ActiveSheet.Name = ќелија. По неговото лансирање, ќе ги видиме истите листови по град, но веќе креираните прашања за Power Query ќе ги формираат:
Со какви било промени во изворните податоци, доволно е да се ажурира соодветната табела со десното копче на глувчето - командата Ажурирајте и зачувајте (Освежи) или ажурирајте ги сите градови одеднаш на големо користејќи го копчето Ажурирај ги сите табот податоци (Податоци - Освежи ги сите).
- Што се макроа, како да се креираат и користат
- Зачувување на листовите од работните книги како посебни датотеки
- Собирање податоци од сите листови од книгата во една табела