Divize yon tab an dra

Microsoft Excel gen anpil zouti pou kolekte done ki soti nan plizyè tab (ki soti nan diferan fèy oswa nan diferan dosye): lyen dirèk, fonksyon ENDRÈK (Endirèk), Power Query ak Power Pivot add-ons, elatriye Soti nan bò sa a nan barikad la, tout bagay sanble bon.

Men, si ou kouri nan yon pwoblèm envès - gaye done ki sòti nan yon tab nan fèy diferan - Lè sa a, tout bagay pral pi tris. Nan moman sa a, pa gen okenn zouti sivilize entegre pou separasyon done sa yo nan asenal Excel la, malerezman. Se konsa, w ap oblije sèvi ak yon makro nan Visual Basic, oswa itilize makro achiv + Power Query konbinezon ak yon ti kras "fichye rafineman" apre.

Ann pran yon gade pi pre nan ki jan sa a ka aplike.

Fòmasyon pwoblèm nan

Nou gen kòm premye done tankou yon tablo ki gen yon gwosè ki gen plis pase 5000 ranje pou lavant:

Divize yon tab an dra

Objektif Travay la: distribye done ki nan tablo sa a pa vil sou fèy separe liv sa a. Moun sa yo. nan pwodiksyon an, ou bezwen jwenn sou chak fèy sèlman ranje sa yo ki soti nan tab la kote vant la te nan vil korespondan an:

Divize yon tab an dra

Prepare

Nan lòd pa konplike kòd la macro epi fè li fasil pou konprann ke posib, se pou yo fè yon koup nan etap preparasyon.

Premyèman, nan kreye yon tab rechèch separe, kote yon kolòn sèl pral lis tout vil yo pou ki ou vle kreye fèy separe. Natirèlman, anyè sa a gendwa pa genyen tout vil ki prezan nan done sous yo, men se sèlman sa yo pou nou bezwen rapò. Fason ki pi fasil pou kreye yon tab konsa se sèvi ak lòd la Done - Retire kopi (Done — Retire doublons) pou kopi kolòn City oswa fonksyon UNIK (INIK) – si ou gen vèsyon an dènye nan Excel 365.

Depi nouvo dra nan Excel yo kreye pa default anvan (a goch la nan) aktyèl la (anvan), li fè sans tou pou klase vil yo nan anyè sa a nan lòd desandan (soti nan Z rive nan A) - Lè sa a, apre kreyasyon an, vil la. fèy yo pral ranje alfabè.

Dezyèmman, nan пkonvèti tou de tab nan dinamik (“entelijan”) pou fè li pi fasil pou travay avèk yo. Nou itilize kòmandman an Kay - Fòma kòm yon tab (Kay — Fòma kòm Tablo) oswa rakoursi klavye Ctrl+T. Sou tab la ki parèt Constructor (Design) ann rele yo tablProdaji и TableCity, respektivman:

Divize yon tab an dra

Metòd 1. Macro pou divizyon pa dra

Sou tab la Avanse pwomotè (Devlopè) klike sou bouton an Vizyèl Debaz oswa itilize rakoursi klavye Alt+F11. Nan fennèt editè macro ki ouvè, mete yon nouvo modil vid nan meni an Mete – Modil epi kopye kòd sa a la:

Sub Splitter() Pou chak selil nan Range("таблГорода") Range("таблПродажи").AutoFilter Field:=3, Criteria1:=cell.Value Range("таблПродажи[#All]").SpecialCells(xlVisibleellCopyy). Sheets.Add ActiveSheet.Paste ActiveSheet.Name = cell.Value ActiveSheet.UsedRange.Columns.AutoFit Next selil Worksheets("Данные").ShowAllData End Sub

Isit la ak yon bouk Pou Chak… Next aplike pasaj la nan selil yo nan anyè a TableCity, kote pou chak vil yo filtre (metòd AutoFilter) nan tablo lavant orijinal la epi kopye rezilta yo sou fèy ki fèk kreye a. Sou tout wout la, fèy la kreye yo chanje non an menm non an nan vil la ak oto-fitting lajè a nan kolòn yo pou bote vire sou li.

Ou ka kouri makro a kreye nan Excel sou tab la pwomotè bouton Makro (Devlopè — Macro) oswa rakoursi klavye Alt+F8.

Metòd 2. Kreye plizyè requêtes nan Power Query

Metòd anvan an, pou tout konpak li yo ak senplisite, gen yon dezavantaj enpòtan - fèy papye yo kreye pa macro a pa mete ajou lè chanjman yo fèt nan tablo lavant orijinal la. Si mete ajou sou vole a nesesè, Lè sa a, w ap oblije sèvi ak pake VBA + Power Query, oswa pito, kreye lè l sèvi avèk yon makro pa sèlman fèy papye ak done estatik, men mete ajou demann Power Query.

Macro a nan ka sa a se pasyèlman menm jan ak youn anvan an (li tou gen yon sik Pou Chak… Next iterasyon sou vil yo nan anyè a), men andedan bouk la pa pral filtraj ak kopye ankò, men yo pral kreye yon demann Power Query ak telechaje rezilta li yo nan yon nouvo fèy:

Sub Splitter2() Pou chak selil nan seri ("tablo vil") ActiveWorkbook.Queries.Add Non:=cell.Value, Fòmil:= _ "kite" & Chr(13) & "" & Chr (10) & "Sous = Excel.CurrentWorkbook(){[Name=""TableSales""]}[Content]," & Chr(13) & "" & Chr(10) & " #""Chanje Kalite"" = Table.TransformColumnTypes(Sous , {{""Kategori"", tape tèks}, {""Non"", tape tèks}, {""Vil"", tape tèks}, {""Manadjè"", tape tèks}, {""Deal dat "", tape datetime}, {""Pri"", tape nimewo}})," & Chr(13) & "" & Chr(10) & " #""Ranj ak filtè aplike"" = Table.Se " & _ "lectRows(#""Chanje tip"", chak ([Vil] = """ & selil.Valè & ""))" & Chr(13) & "" & Chr(10) & "nan " & Chr(13) & "" & Chr(10) & " #""Ranj ak filtè aplike""" ActiveWorkbook.Worksheets.Add With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _ "OLEDB; Founisè =Microsoft.Mashup.OleDb.1;Sous Done=$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 = Vrè .RefreshOnFileOpen = Fo .BackgroundQuery = Vrè .RefreshStyle = xlInsertDeleteCells =SavePasword .SavePasword SaveData = Vrè .AdjustColumnWidth = Vrè .RefreshPeriod = 0 .PreserveColumnInfo = Vrè .ListObject.DisplayName = cell.Value .Refresh BackgroundQuery:=False Fen Ak ActiveSheet.Name = cell.Value Pwochen selil Fen Sub

Apre lansman li, nou pral wè menm fèy papye yo pa vil, men demann Power Query ki deja kreye yo pral fòme yo:

Divize yon tab an dra

Avèk nenpòt chanjman ki fèt nan done sous yo, li pral ase mete ajou tablo ki koresponn lan ak bouton an sourit dwat - lòd la Mete ajou & Sove (Rafrechi) oswa mete ajou tout vil yo nan yon fwa an gwo lè l sèvi avèk bouton an Mete ajou tout tab Done (Done — rafrechi tout).

  • Ki sa ki makro, ki jan yo kreye epi sèvi ak yo
  • Ekonomize fèy liv travay kòm dosye separe
  • Kolekte done ki sòti nan tout fèy liv la nan yon sèl tab

Kite yon Reply