Spliting ib lub rooj rau hauv nplooj ntawv

Microsoft Excel muaj ntau yam cuab yeej los sau cov ntaub ntawv los ntawm ntau lub rooj (los ntawm cov nplooj ntawv sib txawv lossis los ntawm cov ntaub ntawv sib txawv): kev sib txuas ncaj qha, ua haujlwm. TSEEM CEEB (NTSEEG), Power Query thiab Power Pivot add-ons, thiab lwm yam. Los ntawm sab ntawm lub barricade, txhua yam zoo li zoo.

Tab sis yog tias koj khiav mus rau qhov teeb meem rov qab - kis cov ntaub ntawv los ntawm ib lub rooj mus rau cov nplooj ntawv sib txawv - ces txhua yam yuav nyuaj dua. Nyob rau lub sijhawm no, tsis muaj cov cuab yeej tsim ua haujlwm zoo rau cov ntaub ntawv sib cais hauv arsenal ntawm Excel, hmoov tsis. Yog li koj yuav tau siv macro hauv Visual Basic, lossis siv macro recorder + Power Query ua ke nrog me ntsis "cov ntaub ntawv ua kom zoo" tom qab.

Cia peb ua tib zoo saib seb qhov no tuaj yeem siv tau li cas.

Formulation ntawm qhov teeb meem

Peb muaj raws li thawj cov ntaub ntawv xws li lub rooj nrog ib tug loj ntawm ntau tshaj 5000 kab rau kev muag khoom:

Kev Ua Haujlwm: txhawm rau faib cov ntaub ntawv los ntawm lub rooj no los ntawm lub nroog ntawm cov ntawv cais ntawm phau ntawv no. Cov. ntawm qhov tso zis, koj yuav tsum tau txais ntawm txhua daim ntawv tsuas yog cov kab ntawm lub rooj uas qhov muag tau nyob hauv lub nroog sib xws:

Npaj

Txhawm rau kom tsis txhob cuam tshuam cov lej macro thiab ua kom yooj yim nkag siab li sai tau, cia peb ua ob peb kauj ruam npaj.

Ua ntej, tus tsim ib lub rooj nrhiav cais, qhov twg ib kem yuav sau tag nrho cov nroog uas koj xav tsim cov nplooj ntawv cais. Tau kawg, phau ntawv no yuav tsis muaj tag nrho cov nroog tam sim no hauv cov ntaub ntawv, tab sis tsuas yog cov uas peb xav tau cov ntawv ceeb toom. Txoj kev yooj yim tshaj plaws los tsim ib lub rooj zoo li no yog siv cov lus txib Cov ntaub ntawv - Tshem tawm Duplicates (Cov ntaub ntawv - Tshem tawm duplicates) rau kab ntawv luam txoj kev xav los yog muaj nuj nqi UNIK (UNIQUE) - Yog tias koj muaj qhov tseeb version ntawm Excel 365.

Txij li cov nplooj ntawv tshiab hauv Excel yog tsim los ntawm lub neej ua ntej (mus rau sab laug ntawm) tam sim no (yav dhau los) ib qho, nws kuj ua rau kev txiav txim siab los txheeb xyuas cov nroog hauv phau ntawv teev npe hauv nqe lus qis (los ntawm Z mus rau A) - tom qab tsim, lub nroog cov nplooj ntawv yuav tsum tau teem alphabetically.

Thib ob, tus пhloov ob lub rooj rau dynamic ("ntse") kom yooj yim ua hauj lwm nrog lawv. Peb siv cov lus txib Tsev – Format ua ib lub rooj (Lub Tsev — Format as Table) los yog keyboard shortcut Ctrl+T. Ntawm lub tab uas tshwm constructor (Tsim) cia peb hu lawv tablProdaji и TableCity, feem:

Txoj kev 1. Macro rau kev faib los ntawm nplooj ntawv

Hauv Advanced tab tsim tawm (Tus tsim tawm) nyem rau ntawm lub pob Visual Basic los yog siv cov keyboard shortcut Alt+F11. Nyob rau hauv lub qhov rais macro editor uas qhib, ntxig ib qho tshiab khoob module los ntawm cov ntawv qhia zaub mov Ntxig - Module thiab luam cov cai hauv qab no rau hauv:

Sub Splitter() Rau Txhua lub xov tooj Hauv Range("таблГорода") Range("таблПродажи").AutoFilter Field:=3, Criteria1:=cell.Value Range("таблПродажи[#All]").SpecialCells(xovxwm). Sheets.Add ActiveSheet.Paste ActiveSheet.Npe = cell.Value ActiveSheet.UsedRange.Columns.AutoFit Next cell Worksheets("Данные").ShowAllData End Sub	  

Ntawm no nrog lub voj Rau Txhua… Tom ntej tau txais kev tso cai los ntawm cov hlwb ntawm cov ntawv teev npe TableCity, qhov twg rau txhua lub nroog nws raug lim (txoj kev Lub Tshuab AutoFilter) nyob rau hauv thawj lub rooj muag khoom thiab tom qab ntawd luam cov txiaj ntsig mus rau daim ntawv tsim tshiab. Raws li txoj kev, daim ntawv tsim tau hloov npe rau tib lub npe ntawm lub nroog thiab nws pib-fitting qhov dav ntawm txhua kab rau kev zoo nkauj yog qhib rau nws.

Koj tuaj yeem khiav qhov tsim macro hauv Excel ntawm lub tab tsim tawm khawm Ntsig (Tus tsim tawm - Macros) los yog keyboard shortcut Alt+F8.

Txoj Kev 2. Tsim ntau cov lus nug hauv Power Query

Cov txheej txheem dhau los, rau tag nrho nws cov compactness thiab simplicity, muaj ib tug tseem ceeb drawback - cov nplooj ntawv tsim los ntawm cov macro tsis hloov tshiab thaum hloov pauv nyob rau hauv thawj lub rooj muag khoom. Yog tias kev hloov kho ntawm yoov yog qhov tsim nyog, ces koj yuav tsum tau siv VBA + Power Query bundle, lossis theej, tsim siv macro tsis yog cov ntawv txheeb nrog cov ntaub ntawv zoo li qub, tab sis hloov kho Power Query queries.

Lub macro hauv qhov no yog ib feem zoo ib yam li yav dhau los (nws kuj muaj lub voj voog Rau Txhua… Tom ntej mus iterate hla cov nroog hauv cov npe), tab sis hauv lub voj yuav tsis muaj kev lim dej thiab luam tawm lawm, tab sis tsim cov lus nug Power Query thiab upload nws cov txiaj ntsig mus rau ib daim ntawv tshiab:

Sub Splitter2() Rau Txhua lub xov tooj Hauv 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) & " #""Hloov hom"" = Table.TransformColumnTypes(Source , {{""Category"", ntaus ntawv}, {""Npe", ntaus ntawv}, {""City"", ntaus ntawv}, {""Manager"", ntaus ntawv}, {""Deal date "", type datetime}, {""Nqi"", type number}})," & Chr(13) & "" & Chr(10) & " #""Kaw nrog lim siv"" = Table.Se " & _ "lectRows(#""Hloov hom", txhua ([City] = """ & cell.Value & """))" & Chr(13) & "" & Chr(10) & "in " & Chr(13) & "" & Chr(10) & " #""Kaw nrog lim siv""" ActiveWorkbook.Worksheets.Add With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _"OLEDB; Tus muab =Microsoft.Mashup.OleDb.1;Data Source=$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 = Tseeb .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlInsertDeleteCells .FalsePassword . SaveData = True .AdjustColumnWidth = Tseeb .RefreshPeriod = 0 .PreserveColumnInfo = True .ListObject.DisplayName = cell.Value .Refresh BackgroundQuery:=False End With ActiveSheet.Name = cell.Value Next cell End Sub  

Tom qab nws tso tawm, peb yuav pom tib daim ntawv los ntawm lub nroog, tab sis twb tau tsim Power Query queries yuav tsim lawv:

Nrog rau txhua qhov kev hloov pauv hauv cov ntaub ntawv qhov chaw, nws yuav txaus los hloov kho cov lus sib txuas nrog tus nas khawm sab xis - cov lus txib Hloov tshiab & Txuag (Refresh) los yog hloov tshiab txhua lub nroog ib zaug hauv ntau siv lub pob Hloov Tag Nrho tab Cov ntaub ntawv (Cov ntaub ntawv - Refresh All).

• Dab tsi yog macro, tsim thiab siv lawv li cas
• Txuag cov nplooj ntawv ua haujlwm ua cov ntaub ntawv cais
• Sau cov ntaub ntawv los ntawm txhua nplooj ntawv ntawm phau ntawv rau hauv ib lub rooj