Ho arola tafole ka maqephe

Microsoft Excel e na le lisebelisoa tse ngata tsa ho bokella lintlha ho tsoa litafoleng tse 'maloa (ho tloha maqepheng a fapaneng kapa lifaeleng tse fapaneng): lihokelo tse tobileng, ts'ebetso. TS'ELISO (INDIRECT), Potso ea Matla le li-add-on tsa Power Pivot, joalo-joalo Ho tloha ka lehlakoreng lena la barricade, ntho e 'ngoe le e' ngoe e shebahala e le ntle.

Empa haeba u thulana le bothata bo fapaneng - ho jala data ho tloha tafoleng e le 'ngoe ho ea maqepheng a fapaneng - joale ntho e' ngoe le e 'ngoe e tla ba e bohloko haholo. Hajoale, ha ho na lisebelisoa tse tsoetseng pele tse hahelletsoeng ka har'a karohano e joalo ea data pokellong ea lihlomo tsa Excel, ka bomalimabe. Kahoo o tla tlameha ho sebelisa macro ho Visual Basic, kapa sebelisa motsoako oa macro rekota + Power Query ka "ntlafatso ea faele" hanyane kamora.

A re hlahlobeng ka hloko hore na sena se ka etsoa joang.

Tlhahiso ea bothata

Re na le data ea pele ea tafole e joalo ka boholo ba mela e fetang 5000 bakeng sa thekiso:

Mosebetsi: ho aba lintlha tse tsoang tafoleng ena ho ea ka motse maqepheng a arohaneng a buka ena. Tseo. ka tlhahiso, o hloka ho fumana leqepheng le leng le le leng feela mela e tsoang tafoleng moo thekiso e neng e le teng motseng o lumellanang:

Lokisetsa

E le hore re se ke ra thatafatsa khoutu ea macro mme re e utloisise habonolo kamoo ho ka khonehang, a re etseng mehato e 'maloa ea ho itokisa.

Pele, e theha tafole e arohaneng ea ho sheba, moo kholomo e le 'ngoe e tla thathamisa litoropo tsohle tseo u batlang ho li etsa maqephe a arohaneng. Ehlile, bukana ena e kanna ea se be le litoropo tsohle tse teng mohloling oa data, empa ke tseo re li hlokang litlaleho. Tsela e bonolo ka ho fetisisa ea ho etsa tafole e joalo ke ho sebelisa taelo Lintlha - Tlosa Likopi (Boitsebiso - Tlosa tse kopitsoang) bakeng sa kopi ea kholomo maikutlo a kapa tshebetso UNIK (E KHETHEHILENG) - haeba u na le mofuta oa morao-rao oa Excel 365.

Kaha maqephe a macha ho Excel a bōpiloe ka ho sa feleng pele (ka ho le letšehali ho) ea hona joale (e fetileng), hoa utloahala ho hlophisa metse e bukeng ena ka tatellano e theoha (ho tloha ho Z ho ea ho A) - ka mor'a ho bōptjoa, motse. maqephe a tla hlophisoa ka alfabeta.

Ea bobeli, the пfetola litafole ka bobeli ho matla (“bohlale”) ho etsa hore ho be bonolo ho sebetsa le bona. Re sebelisa taelo Lehae – Fometa joalo ka tafole (Lehae - Fometa joalo ka Tafole) kapa khaoletso ea keyboard Ctrl+T. Ho tab e hlahang Constructor (Moralo) a re ba bitseng tablProdaji и TableCityka ho latellana:

Mokhoa oa 1. Macro bakeng sa karohano ka maqephe

Ho Advanced tab Moqapi (Moetsi) tobetsa konopo Visual Basic kapa sebelisa khaoletso ea keyboard Alt+F11. Fensetereng ea macro editor e bulang, kenya module e ncha e se nang letho ka har'a menu Kenya – Mojule ebe u kopitsa khoutu e latelang moo:

Sub Splitter() Bakeng sa Seleng ka 'ngoe Ka Range("таблГорода") Range("таблПродажи").AutoFilter Field:=3, Criteria1:=cell.Boleng ba Range("таблПродажи[#All]").SpecialCell(xlCellTypeVisible).Kopi Sheets.Eketsa ActiveSheet.Paste ActiveSheet.Name = cell.Value ActiveSheet.UsedRange.Columns.AutoFit Next cell Worksheets("Данные").ShowAllData End Sub	  

Mona ka loop Bakeng sa E 'ngoe le e 'ngoe ... E latelang e kentse ts'ebetso ea ho feta ka lisele tsa directory TableCity, moo bakeng sa toropo e 'ngoe le e 'ngoe e tlhotliloeng (mokhoa AutoFilter) tafoleng ea pele ea thekiso ebe o kopitsa liphetho ho lakane e sa tsoa etsoa. Tseleng, letlapa le bōpiloeng le rehoa lebitso le le leng la motse, 'me bophara ba litšiea bakeng sa botle bo buletsoe ho eona.

U ka tsamaisa macro e entsoeng ho Excel ho tab Moqapi konopo makhro (Moetsi - Macros) kapa khaoletso ea keyboard Alt+F8.

Mokhoa oa 2. Etsa lipotso tse ngata ho Potso ea Matla

Mokhoa o fetileng, bakeng sa compactness eohle le bonolo, o na le drawback ea bohlokoa - maqephe a entsoeng ke macro ha a ntlafatsoe ha liphetoho li etsoa tafoleng ea pele ea thekiso. Haeba ho ntlafatsa ho fofa hoa hlokahala, joale u tla tlameha ho sebelisa VBA + Power Query bundle, kapa ho e-na le hoo, u bōpe u sebelisa macro eseng feela maqephe a nang le data e tsitsitseng, empa lipotso tse nchafalitsoeng tsa Power Query.

Macro tabeng ena e batla e tšoana le e fetileng (e boetse e na le potoloho Bakeng sa E 'ngoe le e 'ngoe ... E latelang ho pheta-pheta litoropo tse bukeng), empa ka har'a loop ha ho sa tla hlola ho sefa le ho kopitsa, empa ho theha potso ea Power Query le ho kenya liphetho tsa eona leqepheng le lecha:

Sub Splitter2() Bakeng sa sele ka 'ngoe e In Range("Tafole ea Motse") ActiveWorkbook.Queries.Eketsa Lebitso:=sele.Boleng, Foromo:= _ "tlohela" & Chr(13) & "" & Chr(10) & "Mohloli = Excel.CurrentWorkbook(){[Name=""TableSales""][Content]," & Chr(13) & "" & Chr(10) & " #""Changed Type"" = Table.TransformColumnTypes(Mohloli , {{""Category", type text}, {""Name"", type text}, {""Motse", type text}, {""Motsamaisi"", type text}, {""Deal letsatsi "", thaepa datetime}, {""Cost"", mofuta oa nomoro}})," & Chr(13) & "" & Chr(10) & " #""Mela e nang le sefe e sebelisitsoeng"" = Table.Se " & _ "lectRows(#""Mofuta o fetotsoeng"", e 'ngoe le e 'ngoe ([Motse] = """ & sele.Boleng & """))" & Chr(13) & "" & Chr(10) & "ho " & Chr(13) & "" & Chr(10) & " #""Mela e nang le sefe e kentsoeng""" ActiveWorkbook.Worksheets.Eketsa Ka ActiveSheet.ListObjects.Eketsa(Mohloli:=0, Mohloli:= _ "OLEDB; Mofani =Microsoft.Mashup.OleDb.1;Mohloli oa Boitsebiso=$Bookbook$;Sebaka=" & cell.Boleng & ";Thepa e Atolositsoeng=""""" _ , Sebaka:=Range("$A$1")). QueryTable .CommandType = xlCmd Sql .CommandText = Array("KHETHA *HO TSWA HO [" & cell.Value & "]") .RowNumbers = Bohata .FillAdjacentFormulas = Bohata .PreserveFormatting = True .RefreshOnFileOpen = Bohata .BackgroundQuery = True .RefreshStyle = xlInsertDeleteCells .SavePasword. SaveData = 'Nete .AdjustColumnWidth = True  

Kamora ho qala, re tla bona maqephe a tšoanang ka toropo, empa lipotso tse seng li entsoe tsa Power Query li tla li theha:

Ka liphetoho leha e le life tsa mohloli oa mohloli, ho tla lekana ho ntlafatsa tafole e lumellanang le konopo e nepahetseng ea mouse - taelo Ntlafatsa & Boloka (Nchafatsa) kapa ntjhafatsa litoropo tsohle ka nako e le 'ngoe ka bongata u sebelisa konopo Ntlafatsa tsohle tab ya Lintlha (Boitsebiso - Hlahisa Tsohle).

• Li-macros ke eng, mokhoa oa ho li theha le ho li sebelisa
• Ho boloka maqephe a libuka tsa mosebetsi e le lifaele tse arohaneng
• Ho bokella lintlha ho tsoa maqepheng ohle a buka ka tafole e le 'ngoe