Kaundan
Ang Microsoft Excel adunay daghang mga himan alang sa pagkolekta sa datos gikan sa daghang mga lamesa (gikan sa lainlaing mga sheet o gikan sa lainlaing mga file): direkta nga mga link, function KINAHANGLAN (INDIREKTO), Power Query ug Power Pivot add-on, ug uban pa. Gikan niining kilid sa barikada, nindot tan-awon ang tanan.
Apan kung modagan ka sa usa ka balikbalik nga problema - ang pagkaylap sa datos gikan sa usa ka lamesa ngadto sa lainlaing mga sheet - nan ang tanan mahimong labi ka makapasubo. Sa pagkakaron, wala'y sibilisado nga built-in nga mga himan alang sa ingon nga pagbulag sa datos sa arsenal sa Excel, sa walay palad. Mao nga kinahanglan nimo nga mogamit usa ka macro sa Visual Basic, o gamiton ang macro recorder + Power Query nga kombinasyon nga adunay gamay nga "pagpino sa file" pagkahuman.
Atong tan-awon pag-ayo kung giunsa kini ipatuman.
Pagporma sa problema
Kami adunay usa ka inisyal nga datos sa ingon nga usa ka lamesa nga adunay gidak-on nga labaw pa sa 5000 nga mga laray alang sa pagbaligya:
Buluhaton: sa pag-apod-apod sa mga datos gikan niini nga lamesa sa siyudad sa lain nga mga panid niini nga libro. Mga. sa output, kinahanglan nimo nga makuha sa matag sheet lamang ang mga laray gikan sa lamesa diin ang pagbaligya didto sa katugbang nga lungsod:
Pag-andam
Aron dili makomplikado ang macro code ug himoon kini nga sayon sabton kutob sa mahimo, himoon nato ang pipila ka mga lakang sa pagpangandam.
Una, ang paghimo og bulag nga lookup table, diin ang usa ka kolum maglista sa tanang siyudad nga gusto nimong himoong separadong mga panid. Siyempre, kini nga direktoryo mahimong dili maglangkob sa tanan nga mga lungsod nga naa sa gigikanan nga datos, apan kadtong kinahanglan namon nga mga taho. Ang labing kadali nga paagi sa paghimo sa ingon nga lamesa mao ang paggamit sa mando Data - Kuhaa ang mga Duplicate (Data - Kuhaa ang mga duplicate) para sa kopya sa kolum City o gimbuhaton UNIK (UNIQUE) – kung ikaw adunay pinakabag-o nga bersyon sa Excel 365.
Tungod kay ang mga bag-ong sheet sa Excel gihimo pinaagi sa default sa wala pa (sa wala sa) ang karon (kaniadto) usa, makatarunganon usab nga ihan-ay ang mga lungsod sa kini nga direktoryo sa pagkasunud-sunod (gikan sa Z hangtod A) - pagkahuman pagkahuman sa paghimo, ang lungsod Ang mga palid gihan-ay sa alpabeto.
Ikaduha, ang пi-convert ang duha ka mga lamesa ngadto sa dinamikong (“smart”) aron mas sayon ang pagtrabaho uban nila. Gigamit namon ang mando Panimalay - Pag-format ingon usa ka lamesa (Balay — Format isip Talaan) o keyboard shortcut Ctrl+T. Sa tab nga makita Magtutukod (Laraw) tawgon nato sila tablProdaji и TableCity, matag usa:
Pamaagi 1. Macro para sa pagbahin sa mga sheet
Sa Advanced tab developer (Developer) i-klik ang buton visual Basic o gamita ang keyboard shortcut alt+F11. Sa bintana sa macro editor nga nagbukas, pagsulud usa ka bag-ong wala’y sulod nga module sa menu Isulod – Module ug kopyaha ang mosunod nga code didto:
Sub Splitter() Para sa Matag cell Sa Range("таблГорода") Range("таблПродажи").AutoFilter Field:=3, Criteria1:=cell.Value Range("таблПродажи[#All]").SpecialCells(xlCellTypeVisible). Sheets.Add ActiveSheet.Paste ActiveSheet.Name = cell.Value ActiveSheet.UsedRange.Columns.AutoFit Sunod nga cell Worksheets("Данные").ShowAllData End Sub Dinhi sa usa ka loop Alang sa matag… Sunod gipatuman ang agianan pinaagi sa mga selula sa direktoryo TableCity, diin alang sa matag siyudad kini gisala (pamaagi AutoFilter) sa orihinal nga lamesa sa pagpamaligya ug dayon kopyaha ang mga resulta sa bag-ong nahimo nga sheet. Sa kadugayan, ang gibuhat nga sheet gibag-o sa parehas nga ngalan sa lungsod ug ang awtomatik nga pagpahiangay sa gilapdon sa mga haligi alang sa katahum gi-on niini.
Mahimo nimong ipadagan ang gihimo nga macro sa Excel sa tab developer button Macros (Developer - Macros) o keyboard shortcut alt+F8.
Pamaagi 2. Paghimo ug daghang pangutana sa Power Query
Ang miaging pamaagi, alang sa tanan nga pagkakomplikado ug kayano niini, adunay usa ka hinungdanon nga disbentaha - ang mga sheet nga gihimo sa macro wala ma-update kung ang mga pagbag-o gihimo sa orihinal nga lamesa sa pagpamaligya. Kung gikinahanglan ang pag-update sa langaw, nan kinahanglan nimo nga gamiton ang VBA + Power Query bundle, o hinoon, paghimo gamit ang usa ka macro dili lang mga sheet nga adunay static nga datos, apan gi-update ang Power Query nga mga pangutana.
Ang macro sa niini nga kaso mao ang partially susama sa miaging usa (kini usab adunay usa ka cycle Alang sa matag… Sunod sa pag-uli sa mga siyudad sa direktoryo), apan sa sulod sa loop wala nay pagsala ug pagkopya, kondili paghimo ug Power Query nga pangutana ug pag-upload sa mga resulta niini ngadto sa bag-ong sheet:
Sub Splitter2() Para sa Matag cell Sa 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 , {{""Category"", type text}, {""Ngalan"", type text}, {""City"", type text}, {""Manager"", type text}, {""Deal petsa "", type datetime}, {""Cost"", type number}})," & Chr(13) & "" & Chr(10) & " #""Rows with filter applied"" = Table.Se " & _ "lectRows(#""Giusab nga tipo"", matag usa ([City] = """ & cell.Value & """))" & Chr(13) & "" & Chr(10) & "sa " & Chr(13) & "" & Chr(10) & " #""Mga laray nga adunay filter nga gigamit""" ActiveWorkbook.Worksheets.Add With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _ "OLEDB; Provider =Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=" & cell.Value & ";Extended Properties=""""" _ , Destination:=Range("$A$1")). QueryTable .CommandType = xlCmd Sql .CommandText = Array("PILI *GIKAN sa [" & cell.Value & "]") .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery = Tinuod .RefreshStyle = xlInsertDeleteCellsword .Savelsessword SaveData = Tinuod .AdjustColumnWidth = Tinuod .RefreshPeriod = 0 .PreserveColumnInfo = Tinuod .ListObject.DisplayName = cell.Value .Refresh BackgroundQuery:=False End With ActiveSheet.Name = cell.Value Next cell End Sub Human sa paglusad niini, atong makita ang parehas nga mga sheet sa siyudad, apan nahimo na nga Power Query nga mga pangutana ang magporma kanila:
Sa bisan unsang mga pagbag-o sa gigikanan nga datos, igo na ang pag-update sa katugbang nga lamesa gamit ang tuo nga buton sa mouse - ang mando I-update ug I-save (I-refresh) o i-update ang tanan nga mga lungsod sa makausa sa kadaghanan gamit ang buton Update ang Tanan tab Data (Data — I-refresh Tanan).
- Unsa ang mga macro, unsaon paghimo ug paggamit niini
- Pag-save sa mga workbook sheets isip bulag nga mga file
- Pagkolekta sa datos gikan sa tanang panid sa libro ngadto sa usa ka lamesa