A 'roinn bòrd gu duilleagan

Tha mòran innealan aig Microsoft Excel airson dàta a chruinneachadh bho ghrunn chlàran (bho dhiofar dhuilleagan no bho dhiofar fhaidhlichean): ceanglaichean dìreach, gnìomh URNUIGH (INDIRECT), Power Query agus Power Pivot add-ons, msaa Bhon taobh seo den bharricade, tha a h-uile dad a’ coimhead math.

Ach ma ruitheas tu a-steach do dhuilgheadas cas - a’ sgaoileadh dàta bho aon bhòrd gu diofar dhuilleagan - bidh a h-uile dad tòrr nas brònach. Aig an àm seo, chan eil innealan aonaichte sìobhalta ann airson a leithid de sgaradh dàta ann an arsenal Excel, gu mì-fhortanach. Mar sin feumaidh tu macro a chleachdadh ann an Visual Basic, no cleachd am measgachadh macro recorder + Power Query le beagan “ùrachadh faidhle” às deidh sin.

Bheir sinn sùil nas mionaidiche air mar a ghabhas seo a chuir an gnìomh.

Cruthachadh na trioblaid

Tha an dàta tùsail againn mar chlàr le meud nas motha na 5000 sreath airson reic:

Gnìomh: an dàta bhon chlàr seo a sgaoileadh a rèir baile-mòr air duilleagan fa leth den leabhar seo. An fheadhainn sin. aig an toradh, feumaidh tu faighinn air gach duilleag a-mhàin na sreathan sin bhon chlàr far an robh an reic anns a 'bhaile fhreagarrach:

Ullaich

Gus nach dèan sinn iom-fhillte air a 'chòd macro agus a dhèanamh cho furasta a thuigsinn' sa ghabhas, dèanamaid ceum ullachaidh no dhà.

An toiseach, an cruthaich clàr sgrùdaidh fa leth, far am bi aon cholbh a’ liostadh a h-uile baile-mòr air a bheil thu airson duilleagan fa leth a chruthachadh. Gu dearbh, is dòcha nach bi a h-uile baile-mòr a tha an làthair anns an stòr-dàta anns an eòlaire seo, ach dìreach an fheadhainn air a bheil feum againn air aithisgean. Is e an dòigh as fhasa a leithid de bhòrd a chruthachadh an àithne a chleachdadh Dàta - Thoir air falbh dùblaidhean (Dàta - Thoir air falbh dùblaidhean) airson lethbhreac colbh faireachdainnean no gnìomh UNIK (UNIQUE) - ma tha an dreach as ùire de Excel 365 agad.

Leis gu bheil duilleagan ùra ann an Excel air an cruthachadh gu bunaiteach ro (air taobh clì) an tè a th’ ann an-dràsta (roimhe), tha e cuideachd ciallach na bailtean-mòra san eòlaire seo a sheòrsachadh ann an òrdugh teàrnaidh (bho Z gu A) - an uairsin às deidh an cruthachadh, am baile-mòr. bidh duilleagan air an rèiteachadh ann an òrdugh na h-aibideil.

San dàrna àite, an пtionndaidh an dà chlàr gu fiùghantach (“smart”) gus a dhèanamh nas fhasa obrachadh leotha. Cleachdaidh sinn an àithne Dachaigh - Cruth mar chlàr (Dachaigh - Cruth mar Clàr) no ath-ghoirid meur-chlàr Ctrl+T. Air an taba a nochdas Neach-togail (Dealbhadh) canamaid iad clàr prodaji и Cathair Clàr, fa leth:

Dòigh 1. Macro airson roinneadh le duilleagan

Air an taba Adhartach -leasachaidh (Leasaiche) cliog air a ’phutan Visual Basic no cleachd ath-ghoirid meur-chlàr Alt+F11. Anns an uinneag deasaiche macro a tha a’ fosgladh, cuir a-steach modal falamh ùr tron ​​​​chlàr Cuir a-steach - modal agus dèan lethbhreac den chòd a leanas an sin:

Fo-splitter() Airson gach cealla san raon ("таблГорода") Raon ("таблПродажи").AutoFilter Field:=3, Slatan-tomhais 1:=Raon cealla.Value("таблПродажи[#All]").SpecialCells(xlCells). Sheets.Add ActiveSheet.Paste ActiveSheet.Name = cell.Value ActiveSheet.UsedRange.Columns.AutoFit An ath chealla Duilleagan-obrach (“Данные”).ShowAllData End Fo	  

An seo le lùb Airson Each … Air adhart chuir e an gnìomh an trannsa tro cheallan an eòlaire Cathair Clàr, far a bheil e air a shìoladh airson gach baile (modh AutoFilter) anns a’ chlàr reic tùsail agus an uairsin a’ dèanamh lethbhreac de na toraidhean chun na duilleige a chaidh a chruthachadh às ùr. Air an t-slighe, tha an duilleag cruthaichte air ath-ainmeachadh chun an aon ainm a 'bhaile agus tha e fèin-fhreagarrach air leud nan colbhan airson bòidhchead air a thionndadh air.

Faodaidh tu am macro cruthaichte a ruith ann an Excel air an taba -leasachaidh 'phutan macros (Leasaiche - Macros) no ath-ghoirid meur-chlàr Alt+F8.

Dòigh 2. Cruthaich ioma-cheistean ann an Power Query

Tha ana-cothrom mòr aig an dòigh a bh’ ann roimhe, airson cho teann agus cho sìmplidh ‘s a tha e - chan eil na duilleagan a chruthaich am macro air an ùrachadh nuair a thèid atharrachaidhean a dhèanamh sa chlàr reic tùsail. Ma tha feum air ùrachadh air an itealan, feumaidh tu am pasgan VBA + Power Query a chleachdadh, no an àite sin, cruthaich le bhith a’ cleachdadh macro chan e a-mhàin duilleagan le dàta statach, ach ceistean Power Query ùraichte.

Tha am macro sa chùis seo gu ìre coltach ris an fhear roimhe (tha cearcall aige cuideachd Airson Each … Air adhart gus ath-aithris thairis air bailtean-mòra san eòlaire), ach taobh a-staigh na lùb cha bhith sìoladh agus copaidh ann tuilleadh, ach a’ cruthachadh ceist Ceist Cumhachd agus a’ luchdachadh suas a thoraidhean gu duilleag ùr:

Fo-Splitter2() Airson gach cealla ann an Raon ("Clàr a' Bhaile") ActiveWorkbook.Queries.Add Name:=cell.Value, Formula:= _ "let" & Chr(13) &"" & Chr(10) &" Source = Excel.CurrentWorkbook(){[Name=""Clàr-reic""]}[Susbaint]," & Chr(13) & "" & Chr(10) &" #" "Seòrsa air atharrachadh"" = Table.TransformColumnTypes(Tobar , {{""Category"", seòrsa text}, {""Ainm"", seòrsa text}, {""City"", seòrsa text}, {""Manaidsear"", seòrsa text}, {""Deal date "", seòrsa datetime}, {""Cosgais"", seòrsa an àireamh }}), " &Chr(13) &"" &Chr(10) &" #" "Rows le criathar air a chur an sàs"" = Table.Se " & _ "lectRows(#" "Seòrsa air atharrachadh"", gach ([City] = """ & cell.Value & """))" & Chr(13) &"" &Chr(10) &" a-steach " &Chr(13) &" "&Chr(10) &" #" "Rothan le criathrag air an cur an sàs"" "ActiveWorkbook.Worksheets.Add With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _ "OLEDB; Solaraiche =Microsoft.Mashup.OleDb.1;Data Source=$Leabhar-obrach$;Location="&cell.Value&";Extended Properties=""""" _ , Ceann-uidhe:=Raon ("$A$1")). QueryTable .CommandType = xlCmd Sql .CommandText = Eagrachadh ("SELECT *BHO ["&cell.Value&"]") .RowNumbers = Meallta .FillAdjacentFormulas = Meallta .PreserveFormatting = Fìor .RefreshOnFileOpen = Meallta .BackgroundQuery = Fìor .RefreshStyle = xlInsertDeleteCells .SavePassword = False . SaveData = Fìor .AdjustColumnWidth = Fìor .RefreshPeriod = 0 .PreserveColumnInfo = Fìor .ListObject.DisplayName = cell.Value .Refresh BackgroundQuery:=Deireadh meallta le ActiveSheet.Name = cell.Value An ath chealla Deireadh Fo  

Às deidh a chuir air bhog, chì sinn na h-aon duilleagan a rèir baile-mòr, ach cruthaichidh ceistean Power Query mar-thà iad:

Le atharrachaidhean sam bith anns an dàta tùsail, bidh e gu leòr an clàr co-fhreagarrach ùrachadh leis a’ phutan deas lucha - an àithne Ùraich & Sàbhail (Ùraich) no ùraich a h-uile baile-mòr aig an aon àm le bhith a’ cleachdadh a’ phutan Ùraich a h-uile tab Dàta (Dàta - Ùraich na h-uile).

• Dè a th’ ann am macros, mar a chruthaicheas agus a chleachdas iad iad
• A’ sàbhaladh duilleagan leabhar-obrach mar fhaidhlichean air leth
• A’ cruinneachadh dàta bho gach duilleag den leabhar ann an aon chlàr