Benefis Pivot pa modèl done

Lè w ap bati yon tablo pivot nan Excel, nan premye bwat dyalòg la, kote yo mande nou pou mete seri inisyal la epi chwazi yon kote pou mete tab pivot la, gen yon kaz ki pa klè men trè enpòtan anba a - Ajoute done sa yo nan Modèl Done yo (Ajoute done sa yo nan modèl done) epi, yon ti kras pi wo, switch la Sèvi ak modèl done liv sa a (Sèvi ak modèl done liv travay sa a):

Malerezman, anpil itilizatè ki te abitye ak tab pivot pou yon tan long epi yo sèvi ak yo avèk siksè nan travay yo pafwa pa reyèlman konprann siyifikasyon an nan opsyon sa yo epi pa janm sèvi ak yo. E pou gremesi. Apre yo tout, kreye yon tablo pivot pou modèl done a ban nou plizyè avantaj trè enpòtan konpare ak klasik Excel pivot tab la.

Sepandan, anvan nou konsidere "pen" sa yo byen, se pou nou konprann premye kisa, an reyalite, Modèl Done sa a ye?

Ki sa ki se yon modèl done

Done ki gen konpòtman egzanplè (Abreje kòm MD oswa DM = Modèl Done) se yon zòn espesyal andedan yon fichye Excel kote ou ka estoke done tabul - youn oswa plis tab ki lye, si ou vle, youn ak lòt. An reyalite, sa a se yon ti baz done (kib OLAP) entegre andedan yon liv travay Excel. Konpare ak depo done klasik nan fòm tab regilye (oswa entelijan) sou fèy Excel li menm, Modèl Done a gen plizyè avantaj enpòtan:

• Tablo yo ka jiska 2 milya liy, ak yon fèy Excel ka anfòm yon ti kras plis pase 1 milyon dola.
• Malgre gwosè a gwo, pwosesis la nan tab sa yo (filtraj, klasman, kalkil sou yo, rezime bilding, elatriye) fèt. trè vit Pi vit pase Excel tèt li.
• Avèk done ki nan Modèl la, ou ka fè kalkil adisyonèl (si ou vle, trè konplèks) lè l sèvi avèk bati nan lang DAX.
  
• Tout enfòmasyon ki chaje nan Modèl Done yo trè fòtman konprese lè l sèvi avèk yon espesyal bati-an achiv ak olye modere ogmante gwosè a nan dosye orijinal Excel la.
  

Modèl la jere ak kalkile pa yon espesyal ajoute nan Microsoft Excel - powerpivotsou sa mwen te deja ekri. Pou pèmèt li, sou tab la pwomotè klike sou COM ajoute-ins (Devlopè - COM Add-ins) epi tcheke kaz ki apwopriye a:

Si onglè pwomotè (Devlopè)ou pa ka wè li sou riban an, ou ka vire li sou nan File – Opsyon – Enstalasyon riban (Fichiye — Opsyon — Customize Riban). Si nan fennèt yo montre pi wo a nan lis COM ajoute ou pa gen Power Pivot, lè sa a li pa enkli nan vèsyon ou nan Microsoft Office 🙁

Sou tab Power Pivot ki parèt, pral gen yon gwo bouton vèt limyè jesyon (Jere), klike sou ki pral louvri fenèt la Power Pivot sou tèt Excel, kote nou pral wè sa ki nan modèl la Done nan liv aktyèl la:

Yon nòt enpòtan sou wout la: yon liv Excel ka genyen sèlman yon modèl done.

Chaje tab yo nan modèl done yo

Pou chaje done nan Modèl la, premye nou vire tab la nan yon rakoursi klavye dinamik "entelijan". Ctrl+T epi bay li yon non zanmitay sou tab la Constructor (Design). Sa a se yon etap obligatwa.

Lè sa a, ou ka itilize nenpòt nan twa metòd yo chwazi nan:

• Peze bouton an Ajoute nan Modèl (Ajoute nan modèl done) tab powerpivot tab Akèy (Kay).
  
• Chwazi ekip yo Mete - PivotTable (Antre — Tablo Pivote) epi vire sou kaz la Ajoute done sa yo nan Modèl Done yo (Ajoute done sa yo nan modèl done). Nan ka sa a, dapre done yo chaje nan Modèl la, yon tab pivote tou imedyatman bati.
  
• Sou tab la Avanse Done (Dat) klike sou bouton an Soti nan Tablo / Ranje (Soti nan tab/ranje)pou chaje tab nou an nan editè Power Query. Chemen sa a se pi long la, men, si ou vle, isit la ou ka fè plis netwayaj done, koreksyon ak tout kalite transfòmasyon, nan ki Power Query trè fò.

  Lè sa a, done yo penyen yo Uploaded nan Modèl la pa lòd la Kay — Fèmen epi chaje — Fèmen epi chaje nan… (Kay — Fèmen&Chaje — Fèmen&Chaje sou…). Nan fenèt la ki ouvè, chwazi opsyon an Jis kreye yon koneksyon (Sèlman kreye koneksyon) epi, sa ki pi enpòtan, mete yon tik Ajoute done sa yo nan Modèl Done yo (Ajoute done sa yo nan modèl done).
  

Nou bati yon rezime modèl done a

Pou konstwi yon modèl done rezime, ou ka itilize nenpòt nan twa apwòch:

• Peze bouton tablo rezime (Tablo Pivote) nan fenèt Power Pivot la.
• Chwazi kòmandman nan Excel Mete - PivotTable epi chanje nan mòd Sèvi ak modèl done liv sa a (Antre — Tablo pivote — Sèvi ak modèl done liv travay sa a).
• Chwazi ekip yo Mete - PivotTable (Antre — Tablo Pivote) epi vire sou kaz la Ajoute done sa yo nan Modèl Done yo (Ajoute done sa yo nan modèl done). Tablo "entelijan" aktyèl la pral chaje nan Modèl la epi yo pral bati yon tablo rezime pou tout Modèl la.

Kounye a ke nou te kalkile ki jan yo chaje done nan Modèl Done a epi bati yon rezime sou li, ann eksplore benefis ak avantaj ke sa a ban nou.

Benefis 1: Relasyon ant tab san yo pa itilize fòmil

Yon rezime regilye kapab fèt sèlman lè l sèvi avèk done ki sòti nan yon tab sous. Si ou gen plizyè nan yo, pou egzanp, lavant, lis pri, anyè kliyan, enskri nan kontra, elatriye, Lè sa a, ou pral premye gen kolekte done ki soti nan tout tab nan yon sèl lè l sèvi avèk fonksyon tankou VLOOKUP. (VCHACHUP), ENDEX (ENDEX), PLIS Ekspoze (MATCH), SUMMESLIMN (SUMIFS) ak renmen an. Sa a se long, fatigan ak kondui Excel ou nan yon "panse" ak yon gwo kantite done.

Nan ka a nan yon rezime nan Modèl la Done, tout bagay se pi senp. Li se ase yo mete kanpe relasyon ant tab yon fwa nan fenèt la Power Pivot - epi li fè. Pou fè sa, sou tab la powerpivot peze bouton an jesyon (Jere) ak Lè sa a, nan fenèt la ki parèt - bouton an View Tablo (Vè dyagram). Li rete pou trennen komen (kle) kolòn non (jaden) ant tab yo pou kreye lyen:

Apre sa, nan rezime pou Modèl Done a, ou ka jete nan zòn rezime a (ranje, kolòn, filtè, valè) nenpòt jaden ki soti nan nenpòt tab ki gen rapò - tout bagay pral lye ak kalkile otomatikman:

Benefis 2: Konte valè inik

Yon tablo pivot regilye ba nou opòtinite pou nou chwazi youn nan plizyè fonksyon kalkil entegre: sòm, mwayèn, konte, minimòm, maksimòm, elatriye. Nan rezime Modèl Done a, yo ajoute yon fonksyon trè itil nan lis estanda sa a pou konte a. kantite inik (valè ki pa repete). Avèk èd li, pou egzanp, ou ka fasilman konte kantite atik inik nan machandiz (ranje) ke nou vann nan chak vil.

Dwa-klike sou jaden an - kòmand Opsyon jaden valè ak sou tab la Operasyon Chwazi Kantite eleman diferan (Konte diferan):

Benefis 3: Fòmil DAX Custom

Pafwa ou oblije fè plizyè kalkil adisyonèl nan tab pivot. Nan rezime regilye yo, sa fèt lè l sèvi avèk jaden kalkile ak objè, pandan y ap rezime modèl done a sèvi ak mezi nan yon lang espesyal DAX (DAX = Ekspresyon analiz done).

Pou kreye yon mezi, chwazi sou tab la powerpivot Kòmandman Mezi - Kreye Mezi (Mezi - Nouvo mezi) oswa jis klike sou tab la nan lis Pivot Fields epi chwazi Ajoute mezi (Ajoute mezi) nan meni kontèks la:

Nan fenèt la ki louvri, mete:

• Non tab lakote mezi kreye a pral estoke.
• Mezire non – nenpòt non ou konprann pou nouvo jaden an.
• Deskripsyon - opsyonèl.
• Fòmil – bagay ki pi enpòtan, paske isit la nou swa manyèlman antre, oswa klike sou bouton an fx epi chwazi yon fonksyon DAX nan lis la, ki ta dwe kalkile rezilta a lè nou jete mezi nou an nan zòn Valè yo.
• Nan pati ki pi ba nan fenèt la, ou ka imedyatman mete fòma nimewo pou mezi a nan lis la kategori.
  

Lang DAX a pa toujou fasil pou konprann paske li pa opere ak valè endividyèl, men ak kolòn antye ak tab, sa vle di mande pou kèk restriktirasyon nan panse apre fòmil yo klasik Excel. Sepandan, li vo li, paske pouvwa a nan kapasite li yo nan trete gwo kantite done se difisil a ègzajere.

Benefis 4: Hiérarchies jaden personnalisé

Souvan, lè w ap kreye rapò estanda, ou oblije jete menm konbinezon jaden yo nan tablo pivot nan yon sekans bay, pou egzanp. Ane-Trimès-Mwa-Jou, Oswa Kategori-Pwodwi, Oswa Peyi-Vil-Kliyan elatriye Nan rezime Modèl Done a, pwoblèm sa a fasil rezoud lè w kreye pwòp ou a yerachi - seri jaden koutim.

Nan fenèt Power Pivot, chanje nan mòd tablo ak bouton an View Tablo tab Akèy (Kay — View Dyagram), chwazi avèk Ctrl jaden ou vle ak dwa-klike sou yo. Meni kontèks la ap genyen kòmandman an Kreye yerachi (Kreye yerachi):

Yerachi a kreye ka chanje non ak trennen nan li ak sourit la jaden ki nesesè yo, pou pita nan yon mouvman yo ka jete nan rezime a:

Benefis 5: stencils personnalisé

Kontinye lide paragraf anvan an, nan rezime Modèl Done a, ou kapab tou kreye pwòp seri eleman ou pou chak jaden. Pou egzanp, nan tout lis vil yo, ou ka fasilman fè yon seri sèlman sa yo ki nan zòn responsablite w la. Oswa kolekte sèlman kliyan ou yo, machandiz ou yo, elatriye nan yon seri espesyal.

Pou fè sa, sou tab la Pivot tablo analiz nan lis deroule a Jaden, Atik, ak Ansanm gen kòmandman korespondan (Analize - Fields, Items & Sets — Kreye seri ki baze sou atik ranje/kolòn):

Nan fenèt la ki ouvè, ou ka oaza retire, ajoute oswa chanje pozisyon nenpòt eleman epi sove seri a ki kapab lakòz anba yon nouvo non:

Tout ansanm kreye yo pral parèt nan panèl PivotTable Fields nan yon katab separe, kote yo ka trennen yo lib nan zòn ranje ak kolòn nan nenpòt nouvo PivotTable:

Benefis 6: Selektif kache Tablo ak Kolòn

Malgre ke sa a se yon ti, men trè bèl avantaj nan kèk ka. Lè w klike sou non jaden an oswa sou tab tab la nan fenèt Power Pivot, ou ka chwazi lòd la Kache nan Toolkit Kliyan (Kache nan Zouti Kliyan):

Kolòn oswa tab kache a pral disparèt nan fenèt PivotTable Field List la. Li trè pratik si ou bezwen kache pou itilizatè a kèk kolòn oksilyè (pa egzanp, kalkile oswa kolòn ak valè kle pou kreye relasyon) oswa menm tab antye.

Benefis 7. Avanse drill-down

If you double-click on any cell in the value area in a regular pivot table, then Excel displays on a separate sheet a copy of the source data fragment that was involved in the calculation of this cell. This is a very handy thing, officially called Drill-down (in they usually say “fail”).

Nan rezime Modèl Done a, zouti sa a sou la men travay pi sibtil. Lè w kanpe sou nenpòt selil ki gen rezilta ki enterese nou an, ou ka klike sou icon nan ak yon loup ki parèt akote li (li rele Eksprime tandans) epi chwazi nenpòt jaden ou enterese nan nenpòt tablo ki gen rapò:

Apre sa, valè aktyèl la (Modèl = Explorer) pral antre nan zòn nan filtre, epi rezime a pral bati pa biwo:

Natirèlman, yon pwosedi konsa ka repete anpil fwa, toujou ap fouye nan done ou yo nan direksyon ou enterese nan.

Benefis 8: Konvèti Pivot nan Fonksyon Kib

Si w chwazi nenpòt selil nan rezime a pou Modèl Done a epi chwazi sou tab la Pivot tablo analiz Kòmandman Zouti OLAP - Konvèti nan fòmil (Analize — Zouti OLAP — Konvèti an fòmil), Lè sa a, tout rezime a pral otomatikman konvèti nan fòmil. Koulye a, valè jaden yo nan zòn ranje-kolòn ak rezilta yo nan zòn valè yo pral rekipere nan Modèl Done yo lè l sèvi avèk fonksyon espesyal kib yo: CUBEVALUE ak CUBEMEMBER:

Teknikman, sa vle di ke kounye a nou pa fè fas ak yon rezime, men ak plizyè selil ki gen fòmil, sa vle di nou ka fasilman fè nenpòt transfòmasyon ak rapò nou an ki pa disponib nan rezime a, pou egzanp, mete nouvo ranje oswa kolòn nan mitan an. nan rapò a, fè nenpòt kalkil adisyonèl andedan rezime a, fè aranjman pou yo nan nenpòt fason ou vle, elatriye.

An menm tan an, koneksyon an ak done sous yo, nan kou, rete ak nan lavni an fòmil sa yo pral mete ajou lè sous yo chanje. Bote a!

• Analiz plan-reyalite nan yon tablo pivot ak Power Pivot ak Power Query
• Tablo pivote ak header multiliy
• Kreye yon baz done nan Excel lè l sèvi avèk Power Pivot