Ubwino wa Pivot ndi Data Model

Pomanga tebulo la pivot ku Excel, m'bokosi loyamba la zokambirana, pomwe timafunsidwa kuti tiyike mzere woyambira ndikusankha malo oti muyike tebulo la pivot, pali bokosi losawoneka bwino koma lofunika kwambiri pansipa - Onjezani deta iyi ku Data Model (Onjezani izi ku Data Model) ndipo, m'mwamba pang'ono, chosinthira Gwiritsani ntchito deta ya bukuli (Gwiritsani ntchito Mtundu wa Data wa bukhuli):

Tsoka ilo, ogwiritsa ntchito ambiri omwe akhala akudziwa bwino ma tebulo a pivot kwa nthawi yayitali ndipo amawagwiritsa ntchito bwino pantchito yawo nthawi zina samamvetsetsa tanthauzo la zosankhazi ndipo samazigwiritsa ntchito. Ndipo pachabe. Kupatula apo, kupanga tebulo la pivot la Model Data kumatipatsa zabwino zingapo zofunika kwambiri poyerekeza ndi tebulo lakale la Excel pivot.

Komabe, tisanaganizire za "mabanki" awa pafupi, tiyeni timvetsetse kuti, mtundu wa Data Model ndi chiyani?

Kodi Data Model ndi chiyani

Chitsanzo cha Data (chidule cha MD kapena DM = Data Model) ndi malo apadera mkati mwa fayilo ya Excel momwe mungasungire deta ya tabular - tebulo limodzi kapena angapo olumikizidwa, ngati mukufuna, wina ndi mzake. M'malo mwake, iyi ndi database yaying'ono (OLAP cube) yomwe ili mkati mwa bukhu la Excel. Poyerekeza ndi kusungirako kwachikale kwa data mu mawonekedwe a matebulo okhazikika (kapena anzeru) pamasamba a Excel palokha, Model ya Data ili ndi zabwino zingapo:

• Matebulo akhoza kukhala mpaka 2 biliyoni mizere, ndipo pepala la Excel limatha kukwana pang'ono kuposa 1 miliyoni.
• Ngakhale kukula kwakukulu, kukonza matebulo otere (sefa, kusanja, kuwerengera pa iwo, chidule cha zomangamanga, etc.) mwachangu kwambiri Mofulumira kwambiri kuposa Excel yokha.
• Ndi zomwe zili mu Model, mutha kuwerengera zowonjezera (ngati mukufuna, zovuta kwambiri) pogwiritsa ntchito chilankhulo cha DAX chomangidwa.
  
• Zambiri zomwe zayikidwa mu Data Model ndizambiri mwamphamvu wothinikizidwa pogwiritsa ntchito chosungira chapadera chosungiramo ndikuwonjezera kukula kwa fayilo yoyambirira ya Excel.
  

Chitsanzocho chimayendetsedwa ndikuwerengedwa ndi chowonjezera chapadera chomangidwa mu Microsoft Excel - mphamvu pivotzomwe ndalemba kale. Kuti muyitse, pa tabu Woyambitsa pitani Zowonjezera za COM (Wopanga - Zowonjezera za COM) ndipo onani bokosi loyenera:

Ngati ma tabo Woyambitsa (Wolemba Mapulogalamu)inu simungakhoze kuziwona izo pa riboni, inu mukhoza kuyatsa izo kupyolera Fayilo - Zosankha - Kukhazikitsa Riboni (Fayilo - Zosankha - Sinthani Riboni). Ngati pawindo lomwe likuwonetsedwa pamwambapa pamndandanda wazowonjezera za COM mulibe Power Pivot, ndiye kuti sizikuphatikizidwa mu Microsoft Office yanu 🙁

Pa tabu ya Power Pivot yomwe ikuwoneka, padzakhala batani lalikulu lobiriwira Management (Konzani), kudina komwe kudzatsegula zenera la Power Pivot pamwamba pa Excel, pomwe tiwona zomwe zili mu Data Model ya bukhuli:

Chofunikira kwambiri panjira: buku la Excel litha kukhala ndi Model imodzi yokha ya Data.

Kwezani matebulo mu Data Model

Kuti tiyike deta mu Model, choyamba timatembenuza tebulo kukhala njira yachidule ya kiyibodi ya "smart". Ctrl+T ndipo perekani dzina laubwenzi pa tabu Constructor (Kapangidwe). Ichi ndi sitepe yofunika.

Ndiye mutha kugwiritsa ntchito iliyonse mwa njira zitatu zomwe mungasankhe:

• Dinani batani Onjezani ku Model (Onjezani ku Data Model) tsamba mphamvu pivot tsamba Kunyumba (Kunyumba).
  
• Kusankha magulu Ikani - PivotTable (Lowetsani - Pivot Table) ndi kuyatsa cholembera Onjezani deta iyi ku Data Model (Onjezani deta iyi ku Data Model). Pankhaniyi, malinga ndi deta yomwe yayikidwa mu Model, tebulo la pivot limapangidwanso nthawi yomweyo.
  
• Pa Advanced tabu Deta (Tsiku) dinani batani Kuchokera pa Table/Range (Kuchokera pa Table/Range)kutsegula tebulo lathu mu Power Query editor. Njirayi ndi yayitali kwambiri, koma ngati mungafune, apa mutha kuchita zina zowonjezera kuyeretsa, kusintha ndi mitundu yonse ya masinthidwe, momwe Power Query ndi yamphamvu kwambiri.

  Kenako deta yophatikizika imakwezedwa ku Model ndi lamulo Kunyumba - Tsekani ndikunyamula - Tsekani ndikulowetsani… (Kunyumba - Tsekani & Kwezani - Tsekani & Kwezani ku…). Pazenera lomwe limatsegulidwa, sankhani njira Ingopangani kulumikizana (Pangani kulumikizana kokha) ndipo, chofunika kwambiri, ikani chizindikiro Onjezani deta iyi ku Data Model (Onjezani deta iyi ku Data Model).
  

Timamanga chidule cha Model Data

Kuti mupange chidule cha Data Model, mutha kugwiritsa ntchito iliyonse mwa njira zitatu:

• Dinani batani mwachidule tebulo (Pivot Table) pawindo la Power Pivot.
• Sankhani malamulo mu Excel Ikani - PivotTable ndi kusintha mode Gwiritsani ntchito deta ya bukuli (Lowetsani - Pivot Table - Gwiritsani ntchito Chitsanzo cha Data cha bukhuli).
• Kusankha magulu Ikani - PivotTable (Lowetsani - Pivot Table) ndi kuyatsa cholembera Onjezani deta iyi ku Data Model (Onjezani deta iyi ku Data Model). Tebulo lamakono la "smart" lidzalowetsedwa mu Model ndipo tebulo lachidule lidzamangidwa kwa Chitsanzo chonse.

Tsopano popeza tapeza momwe tingakhazikitsire deta mu Data Model ndikumanga chidule chake, tiyeni tifufuze maubwino ndi maubwino omwe izi zimatipatsa.

Phindu 1: Mgwirizano pakati pa matebulo osagwiritsa ntchito mafomu

Chidule cha nthawi zonse chikhoza kupangidwa pogwiritsa ntchito deta kuchokera pa tebulo limodzi. Ngati muli ndi angapo, mwachitsanzo, malonda, mndandanda wamitengo, chikwatu chamakasitomala, kaundula wamakontrakitala, ndi zina zotero, ndiye kuti muyenera kusonkhanitsa deta kuchokera pamatebulo onse kukhala amodzi pogwiritsa ntchito ntchito monga VLOOKUP. (VLOOKUP), INDEX (INDEX), ZAMBIRI ZOVUTIKA (MATCH), SUMMESLIMN (SUMIFS) ndi zina zotero. Izi ndizotalika, zotopetsa ndipo zimayendetsa Excel yanu kukhala "lingaliro" lokhala ndi deta yambiri.

Pankhani ya chidule cha Data Model, chirichonse chiri chophweka kwambiri. Ndikokwanira kukhazikitsa maubwenzi pakati pa matebulo kamodzi pawindo la Power Pivot - ndipo zatha. Kuti muchite izi, dinani pa tabu mphamvu pivot kanikizani batani Management (Konzani) ndiyeno pawindo lomwe likuwoneka - batani Mawonedwe a Ma chart (Mawonedwe azithunzi). Zimatsalira kukoka wamba (makiyi) mayina (magawo) pakati pa matebulo kuti apange maulalo:

Pambuyo pake, mu chidule cha Data Model, mukhoza kuponyera m'dera lachidule (mizere, mizati, zosefera, zikhalidwe) minda iliyonse kuchokera ku matebulo aliwonse okhudzana - chirichonse chidzalumikizidwa ndikuwerengedweratu:

Phindu lachiwiri: Werengani zinthu zomwe mumakonda

Tebulo la pivot lanthawi zonse limatipatsa mwayi wosankha chimodzi mwazinthu zingapo zowerengera zomangidwa: kuchuluka, pafupifupi, kuwerengera, kuchepera, kupitilira, ndi zina. chiwerengero chapadera (makhalidwe osabwerezabwereza). Ndi chithandizo chake, mwachitsanzo, mutha kuwerengera mosavuta kuchuluka kwa zinthu zapadera (zosiyanasiyana) zomwe timagulitsa mumzinda uliwonse.

Dinani kumanja pamunda - kulamula Zosankha zamtengo wapatali ndi pa tabu Ntchito Sankhani Chiwerengero cha zinthu zosiyanasiyana (Kuwerengera kosiyana):

Phindu 3: Mafomu a DAX Amakonda

Nthawi zina mumayenera kuwerengera mosiyanasiyana pamatebulo a pivot. Muchidule chanthawi zonse, izi zimachitika pogwiritsa ntchito minda ndi zinthu zowerengeka, pomwe chidule chachidule cha data chimagwiritsa ntchito miyeso muchilankhulo chapadera cha DAX (DAX = Mawu Analysis Data).

Kuti mupange muyeso, sankhani pa tabu mphamvu pivot lamulo Miyeso - Pangani Muyeso (Miyeso - Muyezo watsopano) kapena ingodinani kumanja patebulo mumndandanda wa Pivot Fields ndikusankha Onjezani muyeso (Onjezani muyeso) mu menyu yankhani:

Pawindo lomwe limatsegula, tsegulani:

• Dzina latebulokumene muyeso wopangidwa udzasungidwa.
• Yezerani dzina - dzina lililonse lomwe mumamvetsetsa pagawo latsopanoli.
• Kufotokozera - mwakufuna.
• chilinganizo - chinthu chofunika kwambiri, chifukwa apa ife mwina pamanja kulowa, kapena dinani batani fx ndikusankha ntchito ya DAX pamndandanda, yomwe iyenera kuwerengera zotsatira zake tikamaponya muyeso wathu kudera la Values.
• M'munsi mwa zenera, mukhoza yomweyo anaika chiwerengero mtundu kwa muyeso mu mndandanda Category.
  

Chilankhulo cha DAX sichapafupi kumva chifukwa sichigwira ntchito ndi zikhalidwe zapayekha, koma ndi mizati ndi matebulo, mwachitsanzo, pamafunika kukonzanso kaganizidwe pambuyo pa ma fomula akale a Excel. Komabe, m'pofunika, chifukwa mphamvu ya mphamvu zake pokonza kuchuluka kwa deta n'kovuta overestimate.

Phindu la 4: Makhalidwe amitundu yosiyanasiyana

Nthawi zambiri, popanga malipoti okhazikika, muyenera kutaya magawo omwewo m'matebulo a pivot motsatizana, mwachitsanzo. Chaka-Kotala-Mwezi-Tsikukapena Gulu-Katundukapena Country-Client-Client etc. Muchidule cha Data Model, vutoli limathetsedwa mosavuta popanga lanu zikondwerero - makonda amitundu yosiyanasiyana.

Pazenera la Power Pivot, sinthani ku ma chart mode ndi batani Mawonedwe a Ma chart tsamba Kunyumba (Kunyumba - Mawonedwe a Zithunzi), sankhani ndi Ctrl minda yomwe mukufuna ndikudina kumanja paiwo. Mndandanda wazinthu udzakhala ndi lamulo Pangani Hierarchy (Pangani maudindo):

Ulamuliro wopangidwa ukhoza kusinthidwanso ndikukokeramo ndi mbewa magawo ofunikira, kotero kuti pambuyo pake mumayendedwe amodzi akhoza kuponyedwa mwachidule:

Phindu lachisanu: Zolemba mwamakonda

Kupitiliza lingaliro la ndime yapitayi, muchidule cha Model Data, mutha kupanganso magawo anu azinthu pagawo lililonse. Mwachitsanzo, kuchokera pamndandanda wonse wamizinda, mutha kupanga magulu okhawo omwe ali mdera lanu laudindo. Kapena sonkhanitsani makasitomala anu okha, katundu wanu, ndi zina zambiri mumagulu apadera.

Kuti muchite izi, dinani pa tabu Kusanthula kwa tebulo la pivot pamndandanda wotsitsa Minda, Zinthu, ndi Seti pali malamulo olingana (Analysis - Fields, Items & Sets - Pangani seti kutengera zinthu za mzere / mzere):

Pazenera lomwe limatsegulidwa, mutha kuchotsa mwasankha, kuwonjezera kapena kusintha malo azinthu zilizonse ndikusunga zomwe zakhazikitsidwa pansi pa dzina latsopano:

Ma seti onse opangidwa adzawonetsedwa mu gulu la PivotTable Fields mufoda yosiyana, kuchokera pomwe atha kukokedwa momasuka ku mizere ndi magawo a PivotTable yatsopano iliyonse:

Phindu Lachisanu: Bisani Matebulo ndi Zigawo Mosankha

Ngakhale izi ndi zazing'ono, koma zokondweretsa mwayi nthawi zina. Mwa kuwonekera kumanja pa dzina lamunda kapena pa tebulo pawindo la Power Pivot, mutha kusankha lamulo Bisani Client Toolkit (Bisani kwa Client Tools):

Gawo lobisika kapena tebulo lizimiririka pagawo la PivotTable Field List. Ndizothandiza kwambiri ngati mukufuna kubisira wogwiritsa mizati yothandizira (mwachitsanzo, owerengeka kapena mizati yokhala ndi zofunikira zopanga maubwenzi) kapena matebulo onse.

Phindu 7. Kubowola-pansi kwakuya

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”).

Muchidule cha Data Model, chida chothandizachi chimagwira ntchito mochenjera kwambiri. Poyimirira pa selo iliyonse yomwe ili ndi zotsatira zomwe zimatisangalatsa, mukhoza kudina chizindikirocho ndi galasi lokulitsa lomwe limatuluka pafupi ndi ilo (lotchedwa Express Trends) kenako sankhani gawo lililonse lomwe mukufuna patebulo lililonse logwirizana:

Pambuyo pake, mtengo wamakono (Model = Explorer) udzalowa m'dera la fyuluta, ndipo chidulecho chidzamangidwa ndi maofesi:

Zachidziwikire, njirayi imatha kubwerezedwa kangapo, ndikuwunika pafupipafupi momwe mukufunira.

Phindu 8: Sinthani Pivot kukhala Cube Ntchito

Ngati mungasankhe selo iliyonse mu chidule cha Data Model ndiyeno sankhani pa tabu Kusanthula kwa tebulo la pivot lamulo Zida za OLAP - Sinthani kukhala Mafomula (Unikani - Zida za OLAP - Sinthani kukhala mafomu), ndiye chidule chonse chidzasinthidwa kukhala ma fomu. Tsopano minda yomwe ili mumzere-mizere ndipo zotsatira zomwe zili mugawo lamtengo wapatali zidzatengedwa kuchokera ku Data Model pogwiritsa ntchito ma cube apadera: CUBEVALUE ndi CUBEMEMBER:

Mwaukadaulo, izi zikutanthauza kuti tsopano sitikuchita ndi chidule, koma ndi maselo angapo okhala ndi ma formula, mwachitsanzo, titha kusintha mosavuta ndi lipoti lathu lomwe silikupezeka mwachidule, mwachitsanzo, ikani mizere yatsopano kapena mizati pakati. za lipotilo, chitani mawerengedwe owonjezera mkati mwachidulecho, akonzeni mwanjira iliyonse yomwe mukufuna, ndi zina zotero.

Panthawi imodzimodziyo, kugwirizana ndi deta yochokera, ndithudi, kumakhalabe ndipo m'tsogolomu mafomuwa adzasinthidwa pamene magwero asintha. Kukongola!

• Kusanthula zenizeni mu tebulo la pivot ndi Power Pivot ndi Power Query
• Pivot tebulo yokhala ndi mitu yambiri
• Pangani nkhokwe mu Excel pogwiritsa ntchito Power Pivot