Qsim ta 'tabella f'folji

Microsoft Excel għandu ħafna għodod għall-ġbir tad-dejta minn diversi tabelli (minn folji differenti jew minn fajls differenti): links diretti, funzjoni INDIRETTA (INDIRETT), Power Query u Power Pivot add-ons, eċċ Minn din in-naħa tal-barricade, kollox jidher tajjeb.

Imma jekk tiltaqa 'ma' problema inversa - it-tixrid tad-dejta minn tabella waħda għal folji differenti - allura kollox ikun ferm aktar imdejjaq. Fil-mument, m'hemm l-ebda għodda integrata ċivilizzata għal tali separazzjoni tad-dejta fl-armament ta 'Excel, sfortunatament. Allura inti jkollok tuża makro fil-Visual Basic, jew tuża l-makro recorder + kombinazzjoni Power Query bi ftit "raffinament tal-fajl" wara.

Ejja nagħtu ħarsa aktar mill-qrib lejn kif dan jista' jiġi implimentat.

Formulazzjoni tal-problema

Bħala data inizjali għandna tabella bħal din b'daqs ta 'aktar minn 5000 ringiela għall-bejgħ:

Qsim ta 'tabella f'folji

Kompitu: li tqassam id-dejta minn din it-tabella skond il-belt fuq folji separati ta 'dan il-ktieb. Dawk. fl-output, għandek bżonn tikseb fuq kull folja dawk ir-ringieli biss mit-tabella fejn il-bejgħ kien fil-belt korrispondenti:

Qsim ta 'tabella f'folji

Ipprepara

Sabiex ma tikkomplikax il-kodiċi makro u tagħmilha faċli biex tinftiehem kemm jista 'jkun, ejja nwettqu ftit passi preparatorji.

L-ewwel, il- oħloq tabella ta' tfittxija separata, fejn kolonna waħda se telenka l-ibliet kollha li għalihom trid toħloq folji separati. Naturalment, dan id-direttorju jista' ma jkunx fih l-ibliet kollha preżenti fid-dejta tas-sors, iżda dawk biss li għalihom għandna bżonn ir-rapporti. L-eħfef mod biex tinħoloq tabella bħal din huwa li tuża l-kmand Dejta – Neħħi d-duplikati (Data — Neħħi d-duplikati) għall-kopja tal-kolonna belt jew funzjoni UNIK (UNIKA) – jekk għandek l-aħħar verżjoni ta’ Excel 365.

Peress li folji ġodda f'Excel huma maħluqa awtomatikament qabel (fuq ix-xellug ta') dik kurrenti (preċedenti), jagħmel sens ukoll li l-ibliet f'dan id-direttorju jiġu ssortjati f'ordni dixxendenti (minn Z sa A) - imbagħad wara l-ħolqien, il-belt il-folji jiġu rranġati alfabetikament.

It-tieni, il- пjikkonvertu ż-żewġ tabelli għal dinamiċi (“intelliġenti”) biex tagħmilha aktar faċli li taħdem magħhom. Aħna nużaw il-kmand Home – Format bħala tabella (Id-dar — Format bħala Tabella) jew shortcut tat-tastiera Ctrl+T. Fuq it-tab li tidher kostruttur (Disinn) ejja nsejħulhom tablProdaji и TableCity, rispettivament:

Qsim ta 'tabella f'folji

Metodu 1. Makro għad-diviżjoni b'folji

Fuq it-tab Avvanzat iżviluppatur (Żviluppatur) ikklikkja fuq il-buttuna Visual Basic jew uża shortcut tat-tastiera Alt+F11. Fit-tieqa tal-editur makro li tiftaħ, daħħal modulu vojt ġdid permezz tal-menu Daħħal – Modulu u kkopja l-kodiċi li ġej hemmhekk:

Sub Splitter() Għal kull ċellula Fil-Medda ("таблГорода") Medda("таблПродажи"). Qasam tal-Filtru Awtomatiku:=3, Kriterji1:=cell.Medda tal-Valur("таблПродажи[#All]").SpecialCells(xlCellCopyy) . Sheets.Add ActiveSheet.Paste ActiveSheet.Name = cell.Value ActiveSheet.UsedRange.Columns.AutoFit Worksheets taċ-ċellula li jmiss ("Данные").ShowAllData End Sub

Hawnhekk b'linja Għal Kull ... Li jmiss implimentat il-passaġġ miċ-ċelloli tad-direttorju TableCity, fejn għal kull belt hija ffiltrata (metodu AutoFilter) fit-tabella tal-bejgħ oriġinali u mbagħad tikkopja r-riżultati fil-folja maħluqa ġdida. Tul it-triq, il-folja maħluqa tingħata isem mill-ġdid għall-istess isem tal-belt u t-twaħħil awtomatiku tal-wisa 'tal-kolonni għas-sbuħija jinxtegħel fuqha.

Tista 'taħdem il-makro maħluqa f'Excel fuq it-tab iżviluppatur buttuna Macros (Żviluppatur — Makros) jew shortcut tat-tastiera Alt+F8.

Metodu 2. Oħloq mistoqsijiet multipli f'Power Query

Il-metodu preċedenti, għall-kumpattezza u s-sempliċità kollha tiegħu, għandu żvantaġġ sinifikanti - il-folji maħluqa mill-makro ma jiġux aġġornati meta jsiru bidliet fit-tabella tal-bejgħ oriġinali. Jekk l-aġġornament fuq il-fly huwa meħtieġ, allura jkollok tuża l-VBA + Power Query bundle, jew aħjar, toħloq bl-użu ta 'makro mhux biss folji b'dejta statika, iżda mistoqsijiet aġġornati Power Query.

Il-makro f'dan il-każ huwa parzjalment simili għal dak preċedenti (għandu wkoll ċiklu Għal Kull ... Li jmiss biex itenni fuq il-bliet fid-direttorju), iżda ġewwa l-linja mhux se jkun hemm aktar iffiltrar u kkupjar, iżda toħloq mistoqsija dwar Power Query u ttella' r-riżultati tagħha f'folja ġdida:

Sub Splitter2() Għal kull ċellula Fil-Medda("Tabella tal-Belt") ActiveWorkbook.Queries.Add Name:=cell.Value, Formula:= _ "let" & Chr(13) & "" & Chr(10) & " Sors = Excel.CurrentWorkbook(){[Name=""TableSales""]}[Kontenut]," & Chr(13) & "" & Chr(10) & " #""Tip Mibdul"" = Tabella.TransformColumnTypes(Sors , {{""Kategorija"", ittajpja test}, {""Isem"", ittajpja test}, {""Belt"", ittajpja test}, {""Manager"", ittajpja test}, {""Deal data "", tip datetime}, {""Cost"", tip numru}})," & Chr(13) & "" & Chr(10) & " #""Ringieli b'filtru applikat"" = Tabella.Se " & _ "lectRows(#""Tip mibdul"", kull wieħed ([Belt] = """ & cell.Value & """))" & Chr(13) & "" & Chr(10) & "fl " & Chr(13) & "" & Chr(10) & " #""Ringieli b'filtru applikat""" ActiveWorkbook.Worksheets.Add With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _ "OLEDB; Fornitur =Microsoft.Mashup.OleDb.1;Sors tad-Data=$Workbook$;Location=" & cell.Value & ";Proprjetajiet Estiżi=""""" _ , Destinazzjoni:=Firxa ("$A$1")). QueryTable .CommandType = xlCmd Sql .CommandText = Array("SELECT *FROM [" & cell.Value & "]") .RowNumbers = Falza .FillAdjacentFormulas = Falza .PreserveFormatting = Veru .RefreshOnFileOpen = Falz .BackgroundQuery = Veru .RefreshStyle = xlInsertDeleteCells = SavePasword . SaveData = Veru .AdjustColumnWidth = Veru .RefreshPeriod = 0 .PreserveColumnInfo = Veru .ListObject.DisplayName = cell.Value .Refresh BackgroundQuery:=False End With ActiveSheet.Name = cell.Value Ċellula li jmiss End Sub

Wara t-tnedija tiegħu, se naraw l-istess folji skont il-belt, iżda mistoqsijiet tal-Power Query diġà maħluqa se jiffurmawhom:

Qsim ta 'tabella f'folji

Bi kwalunkwe tibdil fid-dejta tas-sors, ikun biżżejjed li taġġorna t-tabella korrispondenti bil-buttuna tal-lemin tal-maws - il-kmand Aġġorna u Issejvja (Aġġorna) jew taġġorna l-ibliet kollha f'daqqa bl-ingrossa billi tuża l-buttuna Aġġorna kollox tab Data (Data — Aġġorna Kollha).

  • X'inhuma macros, kif toħloqhom u tużahom
  • Iffrankar tal-folji tal-ktieb tax-xogħol bħala fajls separati
  • Il-ġbir tad-dejta mill-folji kollha tal-ktieb f'tabella waħda

Ħalli Irrispondi