Clàr pivot thairis air grunn raointean dàta

Cruthachadh na trioblaid

Is e clàran pivot aon de na h-innealan as iongantach ann an Excel. Ach gu ruige seo, gu mì-fhortanach, chan urrainn dha gin de na dreachan de Excel rud cho sìmplidh agus riatanach a dhèanamh air an itealan mar a bhith a 'togail geàrr-chunntas airson grunn raointean dàta tùsail a tha suidhichte, mar eisimpleir, air duilleagan eadar-dhealaichte no ann an clàran eadar-dhealaichte:

Mus tòisich sinn, leig dhuinn puing no dhà a shoilleireachadh. A priori, tha mi a’ creidsinn gu bheil na cumhaichean a leanas air an coinneachadh san dàta againn:

• Faodaidh àireamh de shreathan a bhith aig bùird le dàta sam bith, ach feumaidh an aon cheann-cinn a bhith aca.
• Cha bu chòir dàta a bharrachd a bhith air na duilleagan le clàran stòr. Aon chlàr - aon chlàr. Gus smachd a chumail, tha mi a’ toirt comhairle dhut ath-ghoirid meur-chlàr a chleachdadh Ctrl+End, a ghluaiseas tu chun chill mu dheireadh a chaidh a chleachdadh san duilleag-obrach. Mas fheàrr, bu chòir gur e seo an cealla mu dheireadh sa chlàr dàta. Ma tha nuair a phutas tu air Ctrl+End tha cealla falamh sam bith air an taobh cheart no fon chlàr air a chomharrachadh - sguab às na colbhan falamh sin air an taobh cheart no na sreathan fon chlàr às deidh a’ bhòrd agus sàbhail am faidhle.

Dòigh 1: Tog bùird airson pivot a’ cleachdadh Power Query

A’ tòiseachadh bho dhreach 2010 airson Excel, tha tuilleadan Power Query an-asgaidh as urrainn dàta sam bith a chruinneachadh agus atharrachadh agus an uairsin a thoirt seachad mar stòr airson clàr pivot a thogail. Chan eil e duilich ar duilgheadas fhuasgladh le cuideachadh bhon tuilleadan seo.

An toiseach, cruthaichidh sinn faidhle falamh ùr ann an Excel - thèid co-chruinneachadh a chumail ann agus an uairsin thèid clàr pivot a chruthachadh ann.

An uairsin air an taba Dàta (ma tha Excel 2016 agad no nas fhaide air adhart) no air an taba Ceist cumhachd (ma tha Excel 2010-2013 agad) tagh an àithne Cruthaich Ceist - Bho File - Excel (Faigh dàta - bhon fhaidhle - Excel) agus sònraich am faidhle tùsail leis na clàran a thèid a chruinneachadh:

Anns an uinneag a nochdas, tagh duilleag sam bith (chan eil e gu diofar dè am fear) agus brùth air a’ phutan gu h-ìosal atharrachadh (Edit):

Bu chòir uinneag Deasaiche Ceist Power Query fosgladh air mullach Excel. Air taobh deas na h-uinneige air a 'phannal Paramadairean iarrtas cuir às do gach ceum a chaidh a chruthachadh gu fèin-ghluasadach ach a’ chiad fhear - Stòr (Stòr):

A-nis chì sinn liosta coitcheann de na duilleagan gu lèir. Ma tha cuid de dhuilleagan-taobh eile san fhaidhle a bharrachd air duilleagan dàta, is e an obair againn aig a’ cheum seo na duilleagan sin a thaghadh a-mhàin às am feumar fiosrachadh a luchdachadh, gun a bhith a’ gabhail a-steach an fheadhainn eile a bhios a’ cleachdadh a’ chriathrag ann am bann-cinn a’ bhùird:

Sguab às a h-uile colbh ach a-mhàin colbh Dàtale bhith a’ briogadh deas air ceann colbh agus a’ taghadh Sguab às colbhan eile (Thoir air falbh colbhan eile):

Faodaidh tu an uairsin susbaint nan clàran cruinnichte a leudachadh le bhith a’ briogadh air an t-saighead dhùbailte aig mullach a’ cholbh (bogsa-seic Cleachd ainm colbh tùsail mar ro-leasachan faodaidh tu a chuir dheth):

Ma rinn thu a h-uile càil ceart, an uairsin aig an ìre seo bu chòir dhut na tha anns a h-uile clàr fhaicinn air a chruinneachadh aon gu h-ìosal:

Tha e fhathast gus a’ chiad sreath a thogail gu bann-cinn a’ bhùird leis a’ phutan Cleachd a’ chiad loidhne mar chinn-cinn (Cleachd a’ chiad sreath mar chinn-cinn) tab Dachaigh (Dachaigh) agus thoir air falbh cinn-cinn bùird dùblaichte bhon dàta a’ cleachdadh criathrag:

Sàbhail a h-uile càil a chaidh a dhèanamh leis an àithne Dùin is luchdaich - Dùin is luchdaich a-steach… (Dùin & Luchdaich - Dùin & Luchdaich gu ...) tab Dachaigh (Dachaigh), agus anns an uinneag a tha a’ fosgladh, tagh an roghainn Ceangal a-mhàin (Ceangal a-mhàin):

A h-uile càil. Chan eil air fhàgail ach geàrr-chunntas a thogail. Gus seo a dhèanamh, rachaibh gu tab Cuir a-steach - PivotTable (Cuir a-steach - Clàr Pivot), tagh an roghainn Cleachd stòr dàta bhon taobh a-muigh (Cleachd stòr dàta bhon taobh a-muigh)agus an uairsin le bhith a’ briogadh air a’ phutan Tagh ceangal, ar n-iarrtas. Bidh tuilleadh cruthachadh agus rèiteachadh pivot a’ tachairt ann an dòigh gu tur àbhaisteach le bhith a’ slaodadh nan raointean a dh’ fheumas sinn a-steach do na sreathan, colbhan agus raon luachan:

Ma dh'atharraicheas an dàta tùsail san àm ri teachd no ma thèid beagan dhuilleagan stòr a chur ris, bidh e gu leòr an ceist agus an geàrr-chunntas againn ùrachadh leis an àithne Ùraich a h-uile tab Dàta (Dàta - Ùraich na h-uile).

Dòigh 2. Bidh sinn ag aonachadh bùird leis an àithne UNION SQL ann am macro

Tha fuasgladh eile don duilgheadas againn air a riochdachadh leis a’ mhacro seo, a chruthaicheas seata dàta (tasgadan) airson a’ chlàr pivot a’ cleachdadh an àithne UNITY Cànain ceist SQL. Bidh an àithne seo a’ cothlamadh chlàran bho na tha air a shònrachadh san raon Ainmean-duilleig duilleagan an leabhair ann an aon chlàr dàta. Is e sin, an àite a bhith a’ dèanamh lethbhreac agus pasgadh gu corporra air raointean bho dhiofar dhuilleagan gu aon, bidh sinn a’ dèanamh an aon rud ann an RAM a’ choimpiutair. An uairsin cuiridh am macro duilleag ùr leis an ainm a chaidh a thoirt seachad (caochlaideach Ainm Duilleag Thoraidh) agus a’ cruthachadh geàrr-chunntas làn-chuimseach (!) air stèidhichte air an tasgadan a chaidh a chruinneachadh.

Gus macro a chleachdadh, cleachd am putan Visual Basic air an taba -leasachaidh (Leasaiche) no ath-ghoirid meur-chlàr Alt+F11. An uairsin cuiridh sinn modal falamh ùr a-steach tron ​​​​chlàr Cuir a-steach - modal agus dèan lethbhreac den chòd a leanas an sin:

Fo New_Multi_Table_Pivot() Dim i Cho fada Dim arSQL() Mar String Dim objPivotCache Mar PivotCache Dim objRS As Object Dim ToradhSheetName Mar String Dim SheetsNames As Variant 'ainm duilleig far an tèid am pivot a thàinig às a shealltainn ResultSheetName = "Pivot" 'sreath de dhuilleagan ainmean le clàran stòr SheetsNames = Array ("Alpha", "Beta", "Gamma", "Delta") 'bidh sinn a' cruthachadh tasgadan airson clàran bho dhuilleagan bho SheetsNames With ActiveWorkbook ReDim arSQL(1 Gu (UBound(SheetsNames) + 1) ) Airson i = LBound (SheetsNames) Gu UBound(SheetsNames) arSQL(i+1) = "SELECT * FROM [" & SheetsNames(i) & "$]" Air adhart i Set objRS = CreateObject ("ADODB.Recordset") objRS .Open Join $( arSQL, " UNION ALL "), _ Thig còmhla ri $(Array("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=", _ .FullName, ";Extended Properties=""Excel 8.0;" ""), vbNullString ) Crìochnaich Le 'ath-chruthaich an duilleag gus an clàr pivot a thàinig às a-rithist a thaisbeanadh On Error Resume Next Application.DisplayAlerts = Duilleagan-obrach meallta (ResultSheetName).Delete Set wsPivot = Duilleagan-obrach.Cuir ris wsPivo t. Name = ResultSheetName 'taisbeanadh geàrr-chunntas an tasgadan a chaidh a chruthachadh air an duilleag seo Set objPivotCache = ActiveWorkbook.PivotCaches.Add(xlExternal) Suidhich objPivotCache.Recordset = objRS Set objRS = Chan eil dad le wsPivot objPivotCache.CreatePivotCache(ReatePivotA objPivotCache = Raon gun dad (“A3”).Tagh Deireadh le Fo-dheireadh    

Faodar am macro crìochnaichte an uairsin a ruith le ath-ghoirid meur-chlàr Alt+F8 no am putan Macros air an taba -leasachaidh (Leasaiche - Macros).

Eas-bhuannachdan an dòigh-obrach seo:

• Chan eil an dàta air ùrachadh a chionn 's nach eil ceangal aig an tasgadan ris na clàran tùsail. Ma dh'atharraicheas tu an dàta tùsail, feumaidh tu am macro a ruith a-rithist agus an geàrr-chunntas a thogail a-rithist.
• Nuair a dh'atharraicheas tu an àireamh de dhuilleagan, feumar an còd macro (sreath Ainmean-duilleig).
  

Ach aig a’ cheann thall gheibh sinn fìor bhòrd pivot làn-chuimseach, air a thogail air grunn raointean bho dhiofar dhuilleagan:

Voilà!

Nòta teicnigeach: ma gheibh thu mearachd mar “Solaraiche nach eil clàraichte” nuair a bhios tu a’ ruith am macro, is coltaiche gu bheil dreach 64-bit agad de Excel no dreach neo-choileanta de Office air a chuir a-steach (gun ruigsinneachd). Gus an suidheachadh a cheartachadh, cuir a-steach a’ chriomag sa chòd macro:

	 Solaraiche=Microsoft.Jet.OLEDB.4.0;  

gu:

	Solaraiche=Microsoft.ACE.OLEDB.12.0;  

Agus luchdaich sìos agus stàlaich an einnsean giullachd dàta an-asgaidh bho Access bho làrach-lìn Microsoft - Microsoft Access Database Engine 2010 Redistributable

Dòigh 3: Daingnich Draoidh PivotTable bho Seann Tionndaidhean de Excel

Tha an dòigh seo beagan seann-fhasanta, ach is fhiach iomradh a thoirt air fhathast. Gu foirmeil, anns a h-uile dreach suas gu agus a’ toirt a-steach 2003, bha roghainn anns an PivotTable Wizard “pivot a thogail airson grunn raointean daingneachaidh”. Ach, gu mì-fhortanach, bidh aithisg a chaidh a thogail san dòigh seo, gu mì-fhortanach, dìreach na fhìor shealladh de gheàrr-chunntas làn-chuimseach agus chan eil i a’ toirt taic do mhòran de na “chips” de chlàran pivot àbhaisteach:

Ann an leithid de pivot, chan eil cinn colbh anns an liosta achaidh, chan eil suidheachadh structar sùbailte ann, tha an seata de ghnìomhan a thathar a ’cleachdadh cuingealaichte, agus, san fharsaingeachd, chan eil seo uile glè choltach ri clàr pivot. Is dòcha gur e sin as coireach, a’ tòiseachadh ann an 2007, gun tug Microsoft air falbh a’ ghnìomh seo bhon chòmhradh àbhaisteach nuair a bha iad a’ cruthachadh aithisgean clàr pivot. A-nis chan eil am feart seo ri fhaighinn ach tro phutan àbhaisteach Draoidh PivotTable(Pivot Table Wizard), a ghabhas, ma thogras tu, a chur ris a’ Chàr Inneal Ruigsinneachd Luath tro Faidhle - Roghainnean - Gnàthaich am bàr-inneal ruigsinneachd luath - a h-uile àithne (Faidhle - Roghainnean - Gnàthaich Bàr Inneal Ruigsinneachd Luath - A h-uile Òrdugh):

Às deidh dhut briogadh air a ’phutan a bharrachd, feumaidh tu an roghainn iomchaidh a thaghadh aig a’ chiad cheum den draoidh:

Agus an uairsin san ath uinneag, tagh gach raon mu seach agus cuir ris an liosta choitcheann:

Ach, a-rithist, chan e geàrr-chunntas làn a tha seo, agus mar sin na bi a’ dùileachadh cus bhuaithe. Chan urrainn dhomh an roghainn seo a mholadh ach ann an cùisean gu math sìmplidh.

• A’ cruthachadh aithisgean le PivotTables
• Stèidhich àireamhachadh ann am PivotTables
• Dè a th’ ann am macros, mar a chleachdas tu iad, càite an dèan thu lethbhreac de chòd VBA, msaa.
• Cruinneachadh dàta bho iomadh duilleag gu aon (tuilleadan PLEX)