Buntáistí Pivot de réir Múnla Sonraí

Agus tábla pivot á thógáil in Excel, sa chéad bhosca dialóige, áit a n-iarrtar orainn an raon tosaigh a shocrú agus áit a roghnú chun an tábla pivot a chur isteach, tá ticbhosca neamhfheiceálach ach an-tábhachtach thíos - Cuir na sonraí seo leis an tSamhail Sonraí (Cuir na sonraí seo leis go Samhail Sonraí) agus, beagán níos airde, an lasc Úsáid samhail sonraí an leabhair seo (Úsáid Samhail Sonraí an leabhair oibre seo):

Ar an drochuair, uaireanta ní thuigeann go leor úsáideoirí a bhí eolach ar tháblaí pivot le fada an lá agus iad a úsáid go rathúil ina gcuid oibre brí na roghanna seo agus ní úsáideann siad riamh iad. Agus i vain. Tar éis an tsaoil, tugann cruthú tábla maighdeog don tSamhail Sonraí roinnt buntáistí an-tábhachtach dúinn i gcomparáid leis an tábla pivot Excel clasaiceach.

Sula ndéantar machnamh ar na “bunóga” seo, áfach, tuigimis ar dtús cad é, i ndáiríre, an Múnla Sonraí seo?

Cad is Múnla Sonraí ann

Múnla Sonraí (giorraithe mar MD nó DM = Samhail Sonraí) is réimse speisialta é laistigh de chomhad Excel inar féidir leat sonraí tábla a stóráil - tábla amháin nó níos mó atá nasctha, más mian leat, lena chéile. Go deimhin, is bunachar sonraí beag é seo (ciúb OLAP) atá leabaithe taobh istigh de leabhar oibre Excel. I gcomparáid le stóráil clasaiceach sonraí i bhfoirm táblaí rialta (nó cliste) ar bhileoga Excel féin, tá roinnt buntáistí suntasacha ag an Múnla Sonraí:

• Is féidir le táblaí a bheith suas go dtí 2 billiún líne, agus is féidir le bileog Excel beagán níos mó ná 1 milliún a fheistiú.
• In ainneoin an méid ollmhór, déantar próiseáil táblaí den sórt sin (scagadh, sórtáil, ríomh orthu, achoimre tógála, etc.). an-tapa I bhfad níos tapúla ná Excel féin.
• Leis na sonraí sa tSamhail, is féidir leat ríomhaireachtaí breise (más inmhianaithe, an-chasta) a dhéanamh ag baint úsáide as teanga DAX ionsuite.
  
• Tá an fhaisnéis go léir a lódáiltear isteach sa tSamhail Sonraí an-mhór comhbhrúite go láidir baintear úsáid as cartlannóir speisialta ionsuite agus méadaítear méid an chomhaid Excel bhunaidh go measartha.
  

Déantar an Múnla a bhainistiú agus a ríomh trí bhreiseán speisialta atá ionsuite i Microsoft Excel - pivotatá scríofa agam cheana féin faoi. Chun é a chumasú, ar an táb forbróir cliceáil Breiseáin COM (Forbróir - Breiseáin COM) agus seiceáil an bosca cuí:

Má tabs forbróir (Forbróir)ní féidir leat é a fheiceáil ar an ribín, is féidir leat é a chasadh air Comhad - Roghanna - Socrú Ribín (Comhad - Roghanna - Saincheap Ribbon). Más rud é sa fhuinneog a thaispeántar thuas sa liosta breiseán COM nach bhfuil Power Pivot agat, níl sé san áireamh i do leagan de Microsoft Office 🙁

Ar an táb Power Pivot atá le feiceáil, beidh cnaipe mór glas éadrom Bainistíocht (Bainistigh), ag cliceáil ar a osclóidh an fhuinneog Power Pivot ar bharr Excel, áit a bhfeicfimid ábhar an tSamhail Sonraí den leabhar reatha:

Nóta tábhachtach ar an mbealach: ní féidir ach Múnla Sonraí amháin a bheith i leabhar oibre Excel.

Íoslódáil na táblaí sa mhúnla sonraí

Chun sonraí a luchtú isteach sa Mhúnla, ar dtús déanaimid aicearra méarchláir dinimiciúil “cliste” den tábla Ctrl+T agus tabhair ainm cairdiúil air ar an táb Cruthaitheoir (Dearadh). Is céim riachtanach í seo.

Ansin is féidir leat aon cheann de na trí mhodhanna a úsáid le roghnú as:

• Brúigh an cnaipe Cuir le Múnla (Cuir leis an Múnla Sonraí) tab pivot tab Baile (Baile).
  
• Foirne a roghnú Ionsáigh – PivotTable (Cuir isteach – Pivot Table) agus cas ar an ticbhosca Cuir na sonraí seo leis an tSamhail Sonraí (Cuir na sonraí seo leis an Múnla Sonraí). Sa chás seo, de réir na sonraí a luchtaítear isteach sa Mhúnla, tógtar tábla pivot láithreach freisin.
  
• Ar an gcluaisín Advanced Dáta (Dáta) cliceáil ar an gcnaipe Ó Tábla/Raon (Ó Tábla/Raon)chun ár tábla a luchtú isteach san eagarthóir Power Query. Is é an cosán seo an ceann is faide, ach, más mian leat, anseo is féidir leat sonraí breise a ghlanadh, eagarthóireacht agus gach cineál claochluithe a dhéanamh, ina bhfuil Power Query an-láidir.

  Ansin déantar na sonraí cíortha a uaslódáil chuig an Múnla de réir an ordaithe Baile - Dún agus Luchtaigh - Dún agus Luchtaigh i… (Baile - Dún&Luchtaigh - Dún&Luchtaigh go…). Sa fhuinneog a osclaíonn, roghnaigh an rogha Just a chruthú nasc (Cruthaigh ceangal amháin) agus, níos tábhachtaí fós, cuir tic Cuir na sonraí seo leis an tSamhail Sonraí (Cuir na sonraí seo leis an Múnla Sonraí).
  

Déanaimid achoimre ar an tSamhail Sonraí

Chun Samhail Sonraí achoimre a thógáil, is féidir leat aon cheann de thrí chur chuige a úsáid:

• Brúigh an cnaipe tábla achoimre (Tábla Pivot) sa fhuinneog Power Pivot.
• Roghnaigh orduithe in Excel Ionsáigh – PivotTable agus aistrigh go mód Úsáid samhail sonraí an leabhair seo (Ionsáigh — Pivot Table — Úsáid Samhail Sonraí an leabhair oibre seo).
• Foirne a roghnú Ionsáigh – PivotTable (Cuir isteach – Pivot Table) agus cas ar an ticbhosca Cuir na sonraí seo leis an tSamhail Sonraí (Cuir na sonraí seo leis an Múnla Sonraí). Lódálfar an tábla “cliste” reatha isteach sa tSamhail agus tógfar tábla achoimre don tSamhail iomlán.

Anois agus muid tar éis a dhéanamh amach conas sonraí a luchtú isteach sa tSamhail Sonraí agus achoimre a thógáil air, déanaimis iniúchadh ar na buntáistí agus na buntáistí a thugann sé seo dúinn.

Sochar 1: Caidrimh idir táblaí gan úsáid a bhaint as foirmlí

Ní féidir achoimre rialta a dhéanamh ach le sonraí ó tábla foinse amháin. Má tá roinnt acu agat, mar shampla, díolacháin, liosta praghsanna, eolaire custaiméirí, clár conarthaí, etc., ansin beidh ort ar dtús sonraí a bhailiú ó na táblaí go léir i gceann amháin ag baint úsáide as feidhmeanna cosúil le VLOOKUP (VLOOKUP), INDEX (INDEX), NÍOS MÓ NÍOSAITHE (MATCH), SUMMESLIMN (SUMIFS) agus a leithéid. Tá sé seo fada, tedious agus a thiomáineann do Excel isteach i "smaoineamh" le go leor sonraí.

I gcás achoimre ar an tSamhail Sonraí, tá gach rud i bhfad níos simplí. Is leor caidrimh a bhunú idir táblaí uair amháin sa bhfuinneog Power Pivot - agus déantar é. Chun seo a dhéanamh, ar an táb pivot brúigh an cnaipe Bainistíocht (Bainistigh) agus ansin sa fhuinneog atá le feiceáil - an cnaipe Amharc Cairte (Amharc Léaráide). Tá sé fós le hainmneacha coitianta (eochair) colúin (réimsí) a tharraingt idir táblaí chun naisc a chruthú:

Ina dhiaidh sin, san achoimre don tSamhail Sonraí, is féidir leat aon réimsí ó aon táblaí gaolmhara a chaitheamh san achar achoimre (sraitheanna, colúin, scagairí, luachanna) - déanfar gach rud a nascadh agus a ríomh go huathoibríoch:

Sochar 2: Déan luachanna uathúla a chomhaireamh

Tugann tábla maighdeog rialta deis dúinn ceann amháin de roinnt feidhmeanna ríomha ionsuite a roghnú: suim, meán, comhaireamh, íosmhéid, uasmhéid, etc. San achoimre ar an tSamhail Sonraí, cuirtear feidhm an-úsáideach leis an liosta caighdeánach seo chun an líon na luachanna uathúla (neamh-athrá). Le cabhair uaidh, mar shampla, is féidir leat líon na n-earraí uathúla (raon) a dhíolfaimid i ngach cathair a chomhaireamh go héasca.

Deaschliceáil ar an réimse - ordú Roghanna réimse luach agus ar an táb Oibríocht Roghnaigh Líon na n-eilimintí éagsúla (Comhaireamh ar leith):

Sochar 3: Foirmlí DAX Saincheaptha

Uaireanta caithfidh tú ríomhaireachtaí breise éagsúla a dhéanamh i dtáblaí mhaighdeogacha. In achoimrí rialta, déantar é seo ag baint úsáide as réimsí agus réad ríofa, agus úsáideann achoimre na samhla sonraí bearta i dteanga speisialta DAX (DAX = Léirithe Anailíse Sonraí).

Chun tomhas a chruthú, roghnaigh ar an táb pivot Ceannasaíocht Bearta – Cruthaigh Tomhas (Bearta - Beart nua) nó cliceáil ar dheis ar an tábla sa liosta Réimsí Pivot agus roghnaigh Cuir tomhas leis (Cuir tomhas leis) sa roghchlár comhthéacs:

Sa fhuinneog a osclaíonn, socraigh:

• Ainm an táblaáit a stórálfar an beart cruthaithe.
• Ainm tomhais – aon ainm a thuigeann tú don réimse nua.
• Tuairisc - roghnach.
• Foirmle - an rud is tábhachtaí, mar gheall anseo táimid ag dul isteach de láimh, nó cliceáil ar an gcnaipe fx agus roghnaigh feidhm DAX ón liosta, ar cheart di an toradh a ríomh nuair a chaithfimid ár mbeart ansin isteach sa réimse Luachanna.
• Sa chuid íochtair den fhuinneog, is féidir leat an fhormáid uimhir a shocrú láithreach don bheart sa liosta Catagóir.
  

Ní bhíonn an teanga DAX éasca le tuiscint i gcónaí toisc go n-oibríonn sé ní le luachanna aonair, ach le colúin agus táblaí iomlána, .i. éilíonn sé roinnt athstruchtúraithe smaointeoireachta tar éis foirmlí clasaiceacha Excel. Mar sin féin, is fiú é, toisc go bhfuil sé deacair rómheastachán a dhéanamh ar chumhacht a chumais maidir le méideanna móra sonraí a phróiseáil.

Sochar 4: Ordlathas réimse saincheaptha

Go minic, agus tuairiscí caighdeánacha á gcruthú agat, caithfidh tú na teaglamaí céanna réimsí a chaitheamh isteach i dtáblaí maighdeogacha i seicheamh ar leith, mar shampla Bliain-Ceathrú-Mí-Lá, Nó Catagóir-Táirge, Nó Tír-Cathair-Cliant srl. San achoimre ar an tSamhail Sonraí, is furasta an fhadhb seo a réiteach trí do chuid féin a chruthú ordlathais — tacair réimse saincheaptha.

Sa fhuinneog Power Pivot, aistrigh go mód cairte leis an gcnaipe Amharc Cairte tab Baile (Baile - Léaráid Amharc), roghnaigh le Ctrl réimsí atá ag teastáil agus deaschliceáil orthu. Beidh an t-ordú sa roghchlár comhthéacs Cruthaigh Ordlathas (Cruthaigh ordlathas):

Is féidir an t-ordlathas cruthaithe a athainmniú agus a tharraingt isteach inti leis an luch na réimsí riachtanacha, ionas gur féidir iad a chaitheamh isteach san achoimre níos déanaí i ngluaiseacht amháin:

Sochar 5: Stionsail saincheaptha

Ag leanúint leis an smaoineamh ar an mír roimhe seo, san achoimre ar an Múnla Sonraí, is féidir leat do thacair eilimintí féin a chruthú do gach réimse freisin. Mar shampla, ó liosta iomlán na gcathracha, is féidir leat a dhéanamh go héasca sraith de amháin iad siúd atá i do réimse freagrachta. Nó ná bailigh ach do chustaiméirí, do chuid earraí, etc. i sraith speisialta.

Chun seo a dhéanamh, ar an táb Anailís tábla pivot sa liosta anuas Réimsí, Míreanna, agus Tacair tá orduithe comhfhreagracha ann (Anailís — Réimsí, Items & Sets - Cruthaigh tacar bunaithe ar mhíreanna ró/colún):

Sa fhuinneog a osclaíonn, is féidir leat suíomh aon eilimintí a bhaint, a chur leis nó a athrú go roghnach agus an tacar mar thoradh air a shábháil faoi ainm nua:

Taispeánfar na tacair chruthaithe go léir sa phainéal PivotTable Fields i bhfillteán ar leith, ónar féidir iad a tharraingt go saor go dtí na sraitheanna agus na colúin in aon PivotTable nua:

Sochar 6: Folaigh go Roghnach Táblaí agus Colúin

Cé gur buntáiste beag, ach an-taitneamhach é seo i gcásanna áirithe. Trí chliceáil ar dheis ar an ainm réimse nó ar an táb tábla i bhfuinneog Power Pivot, is féidir leat an t-ordú a roghnú Folaigh ó Cliaint Toolkit (Folaigh ó Client Tools):

Imeoidh an colún nó an tábla i bhfolach ón bpána Liosta Réimse PivotTable. Tá sé an-áisiúil más gá duit roinnt colúin chúnta a cheilt ón úsáideoir (mar shampla, ríomh nó colúin le príomhluachanna chun caidreamh a chruthú) nó fiú táblaí iomlána.

Sochar 7. Druileáil síos chun cinn

Má chliceálann tú faoi dhó ar aon chill sa limistéar luacha i dtábla mhaighdeog rialta, ansin taispeánann Excel ar bhileog ar leith cóip den blúire sonraí foinse a bhí bainteach le ríomh na cille seo. Is rud an-áisiúil é seo, ar a dtugtar Druileáil síos go hoifigiúil (mar de ghnáth deir siad “teip”).

San achoimre ar an tSamhail Sonraí, oibríonn an uirlis áisiúil seo ar bhealach níos simplí. Agus tú ag seasamh ar aon chill a bhfuil an toradh spéisiúil ann dúinn, is féidir leat cliceáil ar an deilbhín le gloine formhéadúcháin a thagann aníos in aice leis (tugtar Treochtaí Express) agus ansin roghnaigh aon réimse a bhfuil suim agat ann in aon tábla gaolmhar:

Tar éis sin, rachaidh an luach reatha (Model = Explorer) isteach sa limistéar scagaire, agus tógfaidh oifigí an achoimre:

Ar ndóigh, is féidir nós imeachta den sórt sin a dhéanamh arís agus arís eile, ag dul isteach i do shonraí go seasta sa treo a bhfuil suim agat ann.

Sochar 8: Pivot a Thiontú go Feidhmeanna Ciúb

Má roghnaíonn tú aon chill san achoimre don tSamhail Sonraí agus ansin roghnaigh ar an táb Anailís tábla pivot Ceannasaíocht Uirlisí OLAP - Tiontaigh go Foirmlí (Anailís - Uirlisí OLAP - Tiontaigh go foirmlí), ansin déanfar an achoimre iomlán a thiontú go foirmlí go huathoibríoch. Anois déanfar na luachanna réimse sa limistéar ró-cholún agus na torthaí sa limistéar luacha a aisghabháil ón tSamhail Sonraí ag baint úsáide as na feidhmeanna ciúb speisialta: CUBEVALUE agus CUBEMEMBER:

Go teicniúil, ciallaíonn sé seo nach bhfuilimid ag déileáil le hachoimre anois, ach le roinnt cealla le foirmlí, ie is féidir linn aon chlaochlú a dhéanamh go héasca lenár dtuarascáil nach bhfuil ar fáil san achoimre, mar shampla, cuir isteach sraitheanna nó colúin nua sa lár. den tuarascáil, déan aon ríomhanna breise laistigh den achoimre, socraigh iad ar aon bhealach atá ag teastáil, etc.

Ag an am céanna, tá an nasc leis na sonraí foinse, ar ndóigh, fós agus sa todhchaí déanfar na foirmlí seo a nuashonrú nuair a athraíonn na foinsí. An áilleacht!

• Anailís plean-fhíricí i dtábla mhaighdeog le Power Pivot agus Power Query
• Tábla pivot le ceanntásc illíne
• Cruthaigh bunachar sonraí in Excel ag baint úsáide as Power Pivot