Að skipta borði í blöð

Microsoft Excel hefur mörg verkfæri til að safna gögnum úr nokkrum töflum (úr mismunandi blöðum eða úr mismunandi skrám): bein hlekkur, virkni ÓBEIN (Óbein), Power Query og Power Pivot viðbætur o.s.frv. Frá þessari hlið barricade lítur allt vel út.

En ef þú lendir í öfugu vandamáli - að dreifa gögnum frá einni töflu yfir á mismunandi blöð - þá verður allt miklu dapurlegra. Í augnablikinu eru engin siðmenntuð innbyggð verkfæri fyrir slíkan gagnaaðskilnað í vopnabúrinu í Excel, því miður. Þannig að þú verður að nota fjölvi í Visual Basic, eða nota macro upptökutæki + Power Query samsetningu með smá "skráarfíngun" á eftir.

Við skulum skoða nánar hvernig hægt er að útfæra þetta.

Mótun vandans

Við höfum sem upphafsgögn slíka töflu með stærð meira en 5000 raðir til sölu:

Verkefni: að dreifa gögnum úr þessari töflu eftir borgum á sérstökum blöðum þessarar bókar. Þeir. við úttakið þarftu að fá á hverju blaði aðeins þær línur úr töflunni þar sem salan var í samsvarandi borg:

Undirbúa

Til þess að flækja ekki makrókóðann og gera það eins auðvelt að skilja og mögulegt er, skulum við framkvæma nokkur undirbúningsskref.

First, the búa til sérstaka uppflettitöflu, þar sem einn dálkur mun skrá allar borgir sem þú vilt búa til aðskilin blöð fyrir. Auðvitað getur þessi skrá ekki innihaldið allar borgir sem eru til staðar í upprunagögnunum, heldur aðeins þær sem við þurfum skýrslur um. Auðveldasta leiðin til að búa til slíka töflu er að nota skipunina Gögn - Fjarlægðu afrit (Gögn - Fjarlægðu afrit) fyrir dálkaafrit Borg eða virka UNIK (EINSTAK) – ef þú ert með nýjustu útgáfuna af Excel 365.

Þar sem ný blöð í Excel eru sjálfgefið búin til á undan (vinstra megin við) núverandi (fyrri) er líka skynsamlegt að raða borgunum í þessari möppu í lækkandi röð (frá Ö til A) - síðan eftir stofnun, borgin blöðum verður raðað í stafrófsröð.

Second, the пbreyta báðum töflunum í dynamic („snjall“) til að gera það auðveldara að vinna með þeim. Við notum skipunina Heim - Snið sem töflu (Heima - Snið sem töflu) eða flýtilykla Ctrl+T. Á flipanum sem birtist Framkvæmdaaðili (Hönnun) hringjum í þá tablProdaji и TableCity, hver um sig:

Aðferð 1. Fjölvi fyrir skiptingu eftir blöðum

Á Advanced flipanum verktaki (hönnuður) smelltu á hnappinn Visual Basic eða notaðu flýtilykla Alt+F11. Í fjölriti glugganum sem opnast, settu nýja tóma einingu í gegnum valmyndina Settu inn - Eining og afritaðu eftirfarandi kóða þangað:

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

Hér með lykkju Fyrir hvert … Næsta útfærði leiðina í gegnum frumur möppunnar TableCity, þar sem hún er síuð fyrir hverja borg (aðferð Sjálfvirkt sía) í upprunalegu sölutöflunni og afritaðu síðan niðurstöðurnar á nýstofnaða blaðið. Á leiðinni er búið til blaðið endurnefnt í sama nafn borgarinnar og kveikt er á sjálfvirkri aðlögun á breidd dálkanna fyrir fegurð á því.

Þú getur keyrt búið til fjölvi í Excel á flipanum verktaki hnappinn Fjölvi (Hönnuður - Fjölvi) eða flýtilykla Alt+F8.

Aðferð 2. Búðu til margar fyrirspurnir í Power Query

Fyrri aðferðin, fyrir alla þéttleika hennar og einfaldleika, hefur verulegan galla - blöðin sem myndast af fjölvi eru ekki uppfærð þegar breytingar eru gerðar á upprunalegu sölutöflunni. Ef nauðsynlegt er að uppfæra á flugi, þá verður þú að nota VBA + Power Query búntinn, eða öllu heldur, búa til með því að nota fjölvi ekki bara blöð með kyrrstæðum gögnum, heldur uppfærðum Power Query fyrirspurnum.

Fjölvi í þessu tilfelli er að hluta til svipuð þeim fyrri (það hefur líka hringrás Fyrir hvert … Næsta til að endurtaka borgir í skránni), en inni í lykkjunni verður ekki lengur síun og afritun, heldur búið til Power Query fyrirspurn og hlaðið niður niðurstöðum hennar á nýtt blað:

Sub Splitter2() Fyrir hvern reit á bilinu("Borgartafla") ActiveWorkbook.Queries.Add Name:=cell.Value, Formula:= _ "let" & Chr(13) & "" & Chr(10) & " Source = Excel.CurrentWorkbook(){[Name=""TableSales""]}[Content]," & Chr(13) & "" & Chr(10) & " #""Changed Type"" = Table.TransformColumnTypes(Source , {{""Flokkur"", sláðu inn texta}, {""Nafn"", sláðu inn texta}, {""City"", skrifaðu texta}, {""Stjórnandi"", skrifaðu texta}, {""Samningur" dagsetning "", tegund datetime}, {""Kostnaður"", tegund númer}})," & Chr(13) & "" & Chr(10) & " #""Raðir með síu beitt"" = Tafla.Se " & _ "lectRows(#""Breytt gerð"", hver ([City] = """ & cell.Value & """))" & Chr(13) & "" & Chr(10) & "in " & Chr(13) & "" & Chr(10) & " #""Raðir með síu beitt""" ActiveWorkbook.Worksheets.Add With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _ "OLEDB; Provider =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 = True .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlInsertDeleteCells = FalsePass. SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .PreserveColumnInfo = True .ListObject.DisplayName = cell.Value .Refresh BackgroundQuery:=False End With ActiveSheet.Name = cell.Value Next cell End Sub Sub  

Eftir opnun þess munum við sjá sömu blöð eftir borg, en þegar búið til Power Query fyrirspurnir munu mynda þau:

Með öllum breytingum á upprunagögnum mun það vera nóg að uppfæra samsvarandi töflu með hægri músarhnappi - skipuninni Uppfærðu og vistaðu (Endurnýja) eða uppfærðu allar borgir í einu í einu með því að nota hnappinn Uppfæra allt flipi Gögn (Gögn — endurnýja allt).

• Hvað eru fjölvi, hvernig á að búa til og nota þau
• Vistar vinnubókarblöð sem aðskildar skrár
• Að safna gögnum úr öllum blöðum bókarinnar í eina töflu