Tábla pivot thar raonta iolracha sonraí

Foirmiú na faidhbe

Tá táblaí pivot ar cheann de na huirlisí is iontach in Excel. Ach go dtí seo, ar an drochuair, ní féidir le haon cheann de na leaganacha de Excel a leithéid de rud simplí agus riachtanach a dhéanamh ar an eitilt mar achoimre a thógáil ar roinnt raonta sonraí tosaigh atá suite, mar shampla, ar bhileoga éagsúla nó i dtáblaí éagsúla:

Sula dtosaímid, déanaimis cúpla pointe a shoiléiriú. A priori, creidim go gcomhlíontar na coinníollacha seo a leanas inár sonraí:

• Is féidir le táblaí aon líon sraitheanna le haon sonraí, ach ní mór go mbeadh an ceanntásc céanna acu.
• Níor cheart go mbeadh aon sonraí breise ar na bileoga le táblaí foinse. Bileog amháin – tábla amháin. Chun rialú a dhéanamh, molaim duit aicearra méarchláir a úsáid Ctrl+deireadh, a bhogann tú chuig an gcill dheireanach a úsáideadh sa bhileog oibre. Go hidéalach, ba cheart gurb é seo an chill dheireanach sa tábla sonraí. Más rud é nuair a chliceálann tú ar Ctrl+deireadh aibhsítear aon chill fholamh ar dheis nó faoi bhun an tábla - scrios na colúin fholmha seo ar dheis nó na sraitheanna faoin tábla i ndiaidh an tábla agus sábháil an comhad.

Modh 1: Tóg táblaí do mhaighdeog ag baint úsáide as Power Query

Ag tosú ó leagan 2010 le haghaidh Excel, tá breiseán Power Query saor in aisce ar féidir leis aon sonraí a bhailiú agus a athrú agus ansin é a thabhairt mar fhoinse chun tábla pivot a thógáil. Níl sé deacair ar chor ar bith ár bhfadhb a réiteach le cabhair ón mbreiseán seo.

Ar dtús, cruthaimis comhad folamh nua in Excel - beidh an tionól ar siúl ann agus ansin cruthófar tábla pivot ann.

Ansin ar an táb Dáta (má tá Excel 2016 nó níos déanaí agat) nó ar an táb Ceist Chumhachta (má tá Excel 2010-2013 agat) roghnaigh an t-ordú Cruthaigh Iarratas - Ó Chomhad - Excel (Faigh Sonraí - Ó chomhad - Excel) agus sonraigh an comhad foinse leis na táblaí atá le bailiú:

Sa fhuinneog atá le feiceáil, roghnaigh aon bhileog (is cuma cén ceann) agus brúigh an cnaipe thíos Athrú (Edit):

Ba cheart go n-osclódh an fhuinneog Eagarthóir Iarratas Cumhachta ar bharr Excel. Ar thaobh na láimhe deise den fhuinneog ar an bpainéal Iarraidh Paraiméadair scrios gach céim a chruthaítear go huathoibríoch seachas an chéad cheann - Foinse (Foinse):

Anois feicimid liosta ginearálta de na bileoga go léir. Más rud é chomh maith le bileoga sonraí go bhfuil roinnt taobhleagan eile sa chomhad, ansin ag an gcéim seo is é an tasc atá againn ná na bileoga sin amháin a roghnú ónar gá faisnéis a luchtú, gan na cinn eile go léir a úsáideann an scagaire sa cheanntásc tábla a áireamh:

Scrios gach colún seachas colún Dátatrí chliceáil ar dheis ar cheannteideal colún agus roghnaigh Scrios colúin eile (Bain colúin eile):

Is féidir leat a bhfuil sna táblaí bailithe a leathnú ansin trí chliceáil ar an tsaighead dhúbailte ag barr an cholúin (ticbhosca Úsáid ainm bunaidh an cholúin mar réimír is féidir leat é a mhúchadh):

Má rinne tú gach rud i gceart, ansin ag an bpointe seo ba cheart duit ábhar na dtáblaí go léir a bailíodh a fheiceáil ceann amháin faoi bhun an chinn eile:

Tá sé fós chun an chéad ró a ardú go dtí an ceanntásc tábla leis an gcnaipe Úsáid an chéad líne mar cheanntásca (Úsáid an chéad ró mar cheanntásca) tab Baile (Baile) agus bain ceanntásca dúblacha tábla ó na sonraí le scagaire:

Sábháil gach rud a rinneadh leis an ordú Dún agus lódáil - Dún agus lódáil i… (Dún & Luchtaigh - Dún & Luchtaigh go…) tab Baile (Baile), agus sa fhuinneog a osclaíonn, roghnaigh an rogha Ceangal amháin (Ceangal Amháin):

Gach rud. Níl ann ach achoimre a thógáil. Chun seo a dhéanamh, téigh go dtí an cluaisín Ionsáigh – PivotTable (Cuir isteach – Pivot Table), roghnaigh an rogha Úsáid foinse sonraí seachtracha (Úsáid foinse sonraí seachtracha)agus ansin trí chliceáil ar an gcnaipe Roghnaigh nasc, ár n-iarratas. Tarlaíonn cruthú agus cumraíocht bhreise an mhaighdeog ar bhealach iomlán caighdeánach trí na réimsí a theastaíonn uainn a tharraingt isteach sna sraitheanna, sna colúin agus sna luachanna:

Má athraíonn na sonraí foinse sa todhchaí nó má chuirtear cúpla bileog stórais eile leis, is leor an cheist agus ár n-achoimre a nuashonrú leis an ordú Athnuaigh go léir tab Dáta (Sonraí - Athnuaigh Uile).

Modh 2. Aontaimid táblaí leis an ordú UNION SQL i macra

Léirítear réiteach eile ar ár bhfadhb ag an macra seo, a chruthaíonn tacar sonraí (taisce) don tábla pivot ag baint úsáide as an ordú UNITY Teanga cheist SQL. Comhcheanglaíonn an t-ordú seo táblaí ó gach ceann atá sonraithe san eagar Ainmneacha na mBileog leatháin den leabhar ina tábla sonraí amháin. Is é sin, in ionad raonta a chóipeáil agus a ghreamú go fisiciúil ó leatháin éagsúla go ceann amháin, déanaimid an rud céanna i RAM an ríomhaire. Ansin cuireann an macra bileog nua leis an ainm tugtha (athraitheach ResultSheetName) agus cruthaíonn sé achoimre iomlán (!) air bunaithe ar an taisce bailithe.

Chun macra a úsáid, úsáid an cnaipe Visual Basic ar an táb forbróir (Forbróir) nó aicearra méarchláir Eile+F11. Ansin cuirimid modúl folamh nua isteach tríd an roghchlár Ionsáigh – Modúl agus cóipeáil an cód seo a leanas ansin:

Fo New_Multi_Table_Pivot() Dim i Chomh fada Dim arSQL() Mar Teaghrán Dim objPivotCache Mar PivotCache Dim objRS As Object Dim ToradhSheetName Mar Teaghrán Dim Bileoga ainmneacha le táblaí foinse SheetsNames = Eagar ("Alfa", "Beta", "Gamma", "Delta") 'déanaimid taisce le haghaidh táblaí ó bhileoga ó SheetsNames With ActiveWorkbook ReDim arSQL(1 Go (UBound(SheetsNames) + 1) ) Chun i = LBound (SheetsNames) Go UBound(SheetsNames) arSQL(i + 1) = "SELECT *Ó [" & SheetsNames(i) & "$]" Ar Aghaidh i Set objRS = CreateObject("ADODB.Recordset") objRS .Open Join $( arSQL, " UNION ALL "), _ Glac páirt $(Array("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=", _ .FullName,";Airíonna Leathnaithe=""Excel 8.0;" ""), vbNullString ) Críoch Le 'athchruthaigh an leathán chun an tábla mhaighdeog mar thoradh air a thaispeáint Ar Earráid Lean Ar Aghaidh Application.DisplayAlerts = Bileoga Oibre Bréagach(ResultSheetName).Scrios Socraigh wsPivot = Bileoga Oibre.Cuir wsPivo leis t. Name = ResultSheetName 'taispeáin an achoimre taisce ginte ar an mbileog seo Set objPivotCache = ActiveWorkbook.PivotCaches.Add(xlExternal) Socraigh objPivotCache.Recordset = objRS Socraigh objRS = Ní dhéanfaidh aon ní Le wsPivot objPivotCache.CreatePivotCreatePivotA objPivotCache = Raon Ní dhéanfaidh aon ní (“A3”). Roghnaigh Deireadh Leis an bhFo Deiridh    

Is féidir an macra críochnaithe a rith ansin le haicearra méarchláir Eile+F8 nó an cnaipe Macraí ar an táb forbróir (Forbróir - Macraí).

Míbhuntáistí an chur chuige seo:

• Ní dhéantar na sonraí a nuashonrú toisc nach bhfuil aon bhaint ag an taisce leis na táblaí foinse. Má athraíonn tú na sonraí foinse, ní mór duit an macra a rith arís agus an achoimre a thógáil arís.
• Nuair a bhíonn líon na mbileog á athrú, is gá an cód macra (eagar Ainmneacha na mBileog).
  

Ach sa deireadh faigheann muid tábla maighdeog iomlán fíor, tógtha ar raonta éagsúla ó bhileoga éagsúla:

Seo!

Nóta teicniúil: má fhaigheann tú earráid cosúil le “Soláthraí nach bhfuil cláraithe” agus an macra á rith agat, is dóichí go bhfuil leagan 64-giotán de Excel agat nó go bhfuil leagan neamhiomlán de Office suiteáilte (gan Rochtain). Chun an cás a réiteach, cuir an blúire sa mhacrachód in ionad:

	 Soláthraí=Microsoft.Jet.OLEDB.4.0;  

chuig:

	Soláthraí=Microsoft.ACE.OLEDB.12.0;  

Agus íoslódáil agus suiteáil an t-inneall próiseála sonraí saor in aisce ó Access ó shuíomh Gréasáin Microsoft - Microsoft Access Database Engine 2010 Redistributable

Modh 3: Treoraí PivotTable a chomhdhlúthú ó Sheanleaganacha de Excel

Tá an modh seo beagán as dáta, ach fós is fiú a lua. Ag labhairt go foirmiúil, i ngach leagan suas go dtí 2003 agus an bhliain sin san áireamh, bhí rogha sa PivotTable Wizard “maighdeog a thógáil le haghaidh roinnt raonta comhdhlúthaithe”. Mar sin féin, ar an drochuair, ní bheidh i dtuarascáil a dhéanfar ar an mbealach seo ach léargas trua ar fhíor-choimre iomlán agus ní thacaíonn go leor de na “sliseanna” de ghnáth-tháblaí mhaighdeogacha:

I maighdeog den sórt sin, níl aon cheannteidil colún sa liosta réimse, níl aon socrú struchtúr solúbtha ann, tá an sraith feidhmeanna a úsáidtear teoranta, agus, go ginearálta, níl sé seo go léir an-chosúil le tábla pivot. B'fhéidir gurb é sin an fáth, ag tosú i 2007, bhain Microsoft an fheidhm seo as an dialóg chaighdeánach nuair a chruthaigh sé tuarascálacha tábla pivot. Anois níl an ghné seo ar fáil ach trí chnaipe saincheaptha Treoraí PivotTable(Treoraí Pivot Table), ar féidir, más mian leat, a chur leis an mBarra Uirlisí Mearrochtana via Comhad - Roghanna - Saincheap an Barra Uirlisí Rochtana Tapa - Gach Ordú (Comhad - Roghanna - Saincheap an Barra Uirlisí Rochtana Mear - Gach Ordú):

Tar éis duit cliceáil ar an gcnaipe breise, ní mór duit an rogha chuí a roghnú ag an gcéad chéim den draoi:

Agus ansin sa chéad fhuinneog eile, roghnaigh gach raon ar a seal agus cuir leis an liosta ginearálta é:

Ach, arís, ní achoimre iomlán é seo, mar sin ná bí ag súil leis an iomarca uaidh. Ní féidir liom an rogha seo a mholadh ach amháin i gcásanna an-simplí.

• Tuarascálacha a Chruthú le PivotTables
• Socraigh ríomhaireachtaí i PivotTables
• Cad iad macraí, conas iad a úsáid, cá háit le cód VBA a chóipeáil, etc.
• Bailiú sonraí ó leatháin iolracha go ceann amháin (breiseán PLEX)