Tábla a roinnt ina leatháin

Tá go leor uirlisí ag Microsoft Excel chun sonraí a bhailiú ó roinnt táblaí (ó bhileoga éagsúla nó ó chomhaid éagsúla): naisc dhíreacha, feidhm INDIRECT (INDIRECT), Power Query agus Power Pivot breiseáin, etc Ón taobh seo den bharacáid, tá gach rud ag breathnú go maith.

Ach má tá fadhb inbhéartach agat - sonraí a scaipeadh ó thábla amháin go bileoga éagsúla - beidh gach rud i bhfad níos brónaí. I láthair na huaire, níl aon uirlisí sibhialta ionsuite le haghaidh scaradh sonraí den sórt sin in Arsenal Excel, ar an drochuair. Mar sin beidh ort macra a úsáid in Visual Basic, nó úsáid a bhaint as an macra-thaifeadán + Power Query le beagán “mionchoigeartú comhaid” ina dhiaidh sin.

Breathnaímis níos géire ar conas is féidir é seo a chur i bhfeidhm.

Foirmiú na faidhbe

Tá tábla den sórt sin againn mar shonraí tosaigh le méid níos mó ná 5000 sraith le haghaidh díolacháin:

Tábla a roinnt ina leatháin

Tasc: na sonraí ón tábla seo a dháileadh de réir cathrach ar bhileoga ar leith den leabhar seo. Iad siúd. ag an aschur, ní mór duit a fháil ar gach leathán ach na sraitheanna sin ón tábla ina raibh an díolachán sa chathair chomhfhreagrach:

Tábla a roinnt ina leatháin

Ullmhaigh

D'fhonn gan an cód macra casta a dhéanamh agus é a dhéanamh chomh héasca le tuiscint agus is féidir, déanaimis cúpla céim ullmhúcháin.

An chéad, an cruthaigh tábla cuardaigh ar leith, áit a liostóidh colún amháin na cathracha go léir a dteastaíonn uait bileoga ar leith a chruthú dóibh. Ar ndóigh, b’fhéidir nach bhfuil san eolaire seo na cathracha go léir atá sna sonraí foinseacha, ach na cathracha sin amháin a bhfuil tuarascálacha de dhíth orainn ina leith. Is é an bealach is éasca tábla den sórt sin a chruthú ná an t-ordú a úsáid Sonraí - Bain Dúblaigh (Sonraí - Bain dúblaigh) le haghaidh cóip colún Cathair nó feidhm UNIK (UNIQUE) - má tá an leagan is déanaí de Excel 365 agat.

Ós rud é go gcruthaítear bileoga nua in Excel de réir réamhshocraithe roimh (ar chlé) an ceann reatha (roimhe), tá sé ciall freisin na cathracha san eolaire seo a shórtáil in ord íslitheach (ó Z go A) - ansin tar éis cruthú, an chathair beidh bileoga eagraithe in ord aibítre.

Dara, an пtiontaigh an dá tábla go dinimiciúil (“cliste”) chun é a dhéanamh níos fusa oibriú leo. Bainimid úsáid as an ordú Baile - Formáid mar tábla (Baile - Formáid mar Tábla) nó aicearra méarchláir Ctrl+T. Ar an táb atá le feiceáil Cruthaitheoir (Dearadh) cuirimis glaoch orthu tábla prodaji и TáblaCity, faoi seach:

Tábla a roinnt ina leatháin

Modh 1. Macra le roinnt ar leatháin

Ar an gcluaisín Advanced forbróir (Forbróir) cliceáil ar an gcnaipe Visual Basic nó úsáid aicearra méarchláir Eile+F11. Sa fhuinneog macra-eagarthóra a osclaíonn, cuir isteach modúl nua folamh tríd an roghchlár Ionsáigh – Modúl agus cóipeáil an cód seo a leanas ansin:

Fo Scoilteoir() Do Gach cill I Raon ("таблГорода") Raon ("таблПродажи").AutoFilter Field:=3, Critéir1:=Raon cille.Luach("таблПродажи[#All]").SpecialCells(VisCells)). Sheets.Add ActiveSheet.Paste ActiveSheet.Name = cell.Value ActiveSheet.UsedRange.Columns.AutoFit Bileoga Oibre Cille Eile("Данные").ShowAllData End Fo

Anseo le lúb I gcás gach ... Ar Aghaidh i bhfeidhm an sliocht trí na cealla an eolaire TáblaCity, áit a ndéantar é a scagadh i ngach cathair (modh Uathscagaire) sa tábla díolacháin bunaidh agus ansin na torthaí a chóipeáil go dtí an leathán nuachruthaithe. Ar an mbealach seo, athainmnítear an leathán cruthaithe go dtí an t-ainm céanna den chathair agus cuirtear leithead na gcolún le haghaidh áilleachta air.

Is féidir leat an macra cruthaithe a rith in Excel ar an táb forbróir cnaipe Macraí (Forbróir - Macraí) nó aicearra méarchláir Eile+F8.

Modh 2. Cruthaigh ceisteanna iolracha i gCeist Cumhachta

Tá míbhuntáiste suntasach ag baint leis an modh roimhe seo, maidir lena dhlúthacht agus lena simplíocht ar fad – ní nuashonraítear na bileoga a chruthaíonn an macra nuair a dhéantar athruithe ar an tábla díolacháin bunaidh. Má tá gá le nuashonrú ar an eitilt, ansin beidh ort an beart Iarratas Cumhachta VBA + a úsáid, nó ina áit sin, cruthaigh ag baint úsáide as macra, ní hamháin bileoga le sonraí statacha, ach fiosrúcháin Power Query nuashonraithe.

Tá an macra sa chás seo cosúil go páirteach leis an gceann roimhe seo (tá timthriall aige freisin I gcás gach ... Ar Aghaidh chun athrá thar chathracha san eolaire), ach taobh istigh den lúb ní bheidh scagadh agus cóipeáil ann a thuilleadh, ach ag cruthú fiosrúchán Cumhachta agus a chuid torthaí a uaslódáil chuig bileog nua:

Fo-Splitter2() Do Gach cill I Raon ("Tábla Cathrach") ActiveWorkbook.Queries.Add Name:=cell.Value, Formula:= _ "lig" & Chr(13) &"" & Chr(10) &" Foinse = Excel.CurrentWorkbook(){[Name=""TáblaSales""]}[Ábhar]," & Chr(13) &"" & Chr(10) &" #""Cineál Athraithe"" = Table.TransformColumnTypes(Foinse , {{""Catagóir"", cineál téacs}, {""Ainm"", cineál téacs}, {""Cathair"", cineál téacs}, {""Bainisteoir"", cineál téacs}, {""Deal date" ", type datetime}, {""Costas"", cineál uimhir}}),, &Chr(13) &"" &Chr(10) &" #" "Ríonna le scagaire curtha i bhfeidhm"" = Table.Se " & _ " lectRows(#" "Cineál athraithe"", gach ceann ([Cathair] = """ & cell.Value &"""))" & Chr(13) &"" & Chr(10) &" in " &Chr(13) &" "&Chr(10) &" #" "Sraitheanna leis an scagaire curtha i bhfeidhm" """ ActiveWorkbook.Worksheets.Add With ActiveSheet.ListObjects.Add(SourceType:=0, Foinse:= _ "OLEDB; Soláthraí =Microsoft.Mashup.OleDb.1;Foinse Sonraí=$Leabhar Oibre$;Suíomh="&cell.Value&";Airíonna Leathnaithe="""" _ , Ceann Scríbe:=Raon("$A$1")). QueryTable .CommandType = xlCmd SQL .CommandText = Eagar("SELECT *Ó ["&cell.Value&"]") .RowNumbers = Bréagach .FillAdjacentFormulas = Bréagach .PreserveFormatting = Fíor .RefreshOnFileOpen = Bréagach .BackgroundQuery = Fíor .RefreshStyle = xlInsertDeleteCells .SavePassword = Bréagach . SaveData = Fíor .AdjustColumnWidth = Fíor .RefreshPeriod = 0 .PreserveColumnInfo = Fíor .ListObject.DisplayName = cell.Value .Refresh BackgroundQuery:=Deireadh Bréagach le ActiveSheet.Name = cell.Value Deireadh cille eile Fo

Tar éis é a sheoladh, feicfimid na bileoga céanna de réir na cathrach, ach foirmeoidh fiosrúcháin Power Query a cruthaíodh cheana iad:

Tábla a roinnt ina leatháin

Má dhéantar aon athruithe ar na sonraí foinse, beidh sé go leor chun an tábla comhfhreagrach a nuashonrú leis an gcnaipe luiche ar dheis - an t-ordú Nuashonraigh & Sábháil (Athnuaigh) nó nuashonraigh na cathracha go léir ag an am céanna ar an mórchóir ag baint úsáide as an gcnaipe Nuashonraigh Uile tab Dáta (Sonraí - Athnuaigh Uile).

  • Cad is macraí ann, conas iad a chruthú agus a úsáid
  • Shábháil bileoga leabhar oibre mar chomhaid ar leith
  • Sonraí a bhailiú ó gach leathán den leabhar i dtábla amháin

Leave a Reply