Manufaa ya Pivot kwa Muundo wa Data

Wakati wa kuunda jedwali la egemeo katika Excel, katika kisanduku cha kwanza kabisa cha mazungumzo, ambapo tunaombwa kuweka safu ya awali na kuchagua mahali pa kuingiza jedwali la egemeo, kuna kisanduku cha kuteua kisichoonekana lakini muhimu sana hapa chini - Ongeza data hii kwa Modeli ya Data (Ongeza data hii kwa Mfano wa Data) na, juu kidogo, kubadili Tumia muundo wa data wa kitabu hiki (Tumia Muundo wa Data wa kitabu hiki cha kazi):

Kwa bahati mbaya, watumiaji wengi ambao wamezoea meza za egemeo kwa muda mrefu na kuzitumia kwa mafanikio katika kazi zao wakati mwingine hawaelewi maana ya chaguzi hizi na hawatumii kamwe. Na bure. Baada ya yote, kuunda jedwali la egemeo la Modeli ya Data hutupatia faida kadhaa muhimu sana ikilinganishwa na jedwali la egemeo la kawaida la Excel.

Walakini, kabla ya kuzingatia "buns" hizi kwa karibu, hebu kwanza tuelewe ni nini, kwa kweli, Modeli hii ya Data ni nini?

Mfano wa Data ni nini

Mfano wa Data (iliyofupishwa kama MD au DM = Mfano wa Data) ni eneo maalum ndani ya faili ya Excel ambapo unaweza kuhifadhi data ya jedwali - jedwali moja au zaidi zilizounganishwa, ikiwa inataka, kwa kila moja. Kwa kweli, hii ni hifadhidata ndogo (mchemraba wa OLAP) iliyopachikwa ndani ya kitabu cha kazi cha Excel. Ikilinganishwa na uhifadhi wa kawaida wa data katika mfumo wa meza za kawaida (au smart) kwenye karatasi za Excel yenyewe, Modeli ya Data ina faida kadhaa muhimu:

• Majedwali yanaweza kuwa hadi 2 bilioni mistari, na laha ya Excel inaweza kutoshea zaidi ya milioni 1.
• Licha ya saizi kubwa, usindikaji wa meza kama hizo (kuchuja, kupanga, mahesabu juu yao, muhtasari wa jengo, nk) hufanywa. haraka sana Haraka zaidi kuliko Excel yenyewe.
• Ukiwa na data iliyo kwenye Mfano, unaweza kufanya mahesabu ya ziada (ikiwa inataka, magumu sana) kwa kutumia lugha iliyojengwa ndani ya DAX.
  
• Taarifa zote zilizopakiwa kwenye Modeli ya Data ni nyingi sana imebanwa kwa nguvu kwa kutumia kumbukumbu maalum iliyojengwa ndani na badala yake huongeza saizi ya faili asili ya Excel.
  

Mfano unasimamiwa na kuhesabiwa na programu-jalizi maalum iliyojengwa ndani ya Microsoft Excel - pivoti ya nguvuambayo tayari nimeandika. Ili kuiwezesha, kwenye kichupo developer bonyeza COM nyongeza (Msanidi - Viongezi vya COM) na angalia kisanduku kinachofaa:

Ikiwa vichupo developer (Msanidi programu)huwezi kuiona kwenye Ribbon, unaweza kuiwasha kupitia Faili - Chaguzi - Usanidi wa Ribbon (Faili - Chaguzi - Geuza Utepe Upendavyo). Ikiwa katika dirisha lililoonyeshwa hapo juu kwenye orodha ya nyongeza za COM huna Pivot ya Nguvu, basi haijajumuishwa katika toleo lako la Microsoft Office 🙁

Kwenye kichupo cha Power Pivot kinachoonekana, kutakuwa na kitufe kikubwa cha kijani kibichi Utawala (Dhibiti), kubofya ambayo itafungua dirisha la Pivot ya Nguvu juu ya Excel, ambapo tutaona yaliyomo kwenye Mfano wa Data wa kitabu cha sasa:

Ujumbe muhimu njiani: kitabu cha Excel kinaweza kuwa na Mfano mmoja wa Data.

Pakia majedwali kwenye Modeli ya Data

Ili kupakia data kwenye Mfano, kwanza tunageuza meza kuwa njia ya mkato ya kibodi ya "smart" yenye nguvu Ctrl+T na upe jina la kirafiki kwenye kichupo kuujenga (Ubunifu). Hii ni hatua inayohitajika.

Basi unaweza kutumia yoyote ya njia tatu kuchagua kutoka:

• Bonyeza kitufe Ongeza kwa Model (Ongeza kwa Muundo wa Data) tab pivoti ya nguvu tab Nyumbani (Nyumbani).
  
• Kuchagua timu Ingiza - Jedwali la Pivot (Ingiza - Jedwali la Egemeo) na uwashe kisanduku cha kuteua Ongeza data hii kwa Modeli ya Data (Ongeza data hii kwa Muundo wa Data). Katika kesi hii, kwa mujibu wa data iliyopakiwa kwenye Mfano, meza ya pivot pia hujengwa mara moja.
  
• Kwenye kichupo cha hali ya juu Data (Tarehe) bonyeza kitufe Kutoka kwa Jedwali/Safu (Kutoka kwa Jedwali/Safu)kupakia jedwali letu kwenye kihariri cha Hoja ya Nguvu. Njia hii ndiyo ndefu zaidi, lakini, ikiwa inataka, hapa unaweza kufanya kusafisha data ya ziada, kuhariri na kila aina ya mabadiliko, ambayo Swala la Nguvu ni kali sana.

  Kisha data iliyochanganywa inapakiwa kwa Mfano kwa amri Nyumbani - Funga na Pakia - Funga na Pakia ndani... (Nyumbani — Funga&Pakia — Funga&Pakia kwa…). Katika dirisha linalofungua, chagua chaguo Unda tu muunganisho (Unda muunganisho pekee) na, muhimu zaidi, weka tiki Ongeza data hii kwa Modeli ya Data (Ongeza data hii kwa Muundo wa Data).
  

Tunaunda muhtasari wa Muundo wa Data

Ili kuunda muhtasari wa Mfano wa Data, unaweza kutumia mojawapo ya mbinu tatu:

• Bonyeza kitufe jedwali la muhtasari (Jedwali la Egemeo) kwenye dirisha la Pivot ya Nguvu.
• Chagua amri katika Excel Ingiza - Jedwali la Pivot na ubadilishe kuwa modi Tumia muundo wa data wa kitabu hiki (Ingiza — Jedwali la Egemeo — Tumia Muundo wa Data wa kitabu hiki cha mazoezi).
• Kuchagua timu Ingiza - Jedwali la Pivot (Ingiza - Jedwali la Egemeo) na uwashe kisanduku cha kuteua Ongeza data hii kwa Modeli ya Data (Ongeza data hii kwa Muundo wa Data). Jedwali la sasa la "smart" litapakiwa kwenye Mfano na jedwali la muhtasari litajengwa kwa Mfano mzima.

Sasa kwa kuwa tumegundua jinsi ya kupakia data kwenye Muundo wa Data na kujenga muhtasari juu yake, hebu tuchunguze manufaa na manufaa ambayo hii inatupa.

Faida ya 1: Uhusiano kati ya majedwali bila kutumia fomula

Muhtasari wa kawaida unaweza tu kutengenezwa kwa kutumia data kutoka kwa jedwali moja la chanzo. Ikiwa unayo kadhaa kati yao, kwa mfano, mauzo, orodha ya bei, saraka ya wateja, rejista ya mikataba, n.k., basi itabidi kwanza kukusanya data kutoka kwa jedwali zote hadi moja kwa kutumia vitendaji kama vile VLOOKUP. (VLOOKUP), INDEX (INDEX), WAZI ZAIDI (MECHI), SUMMESLIMN (SUMIFS) na kadhalika. Hii ni ndefu, ya kuchosha na inaendesha Excel yako kwenye "mawazo" yenye kiasi kikubwa cha data.

Katika kesi ya muhtasari wa Mfano wa Data, kila kitu ni rahisi zaidi. Inatosha kusanidi uhusiano kati ya jedwali mara moja kwenye dirisha la Power Pivot - na imekamilika. Ili kufanya hivyo, kwenye kichupo pivoti ya nguvu bonyeza kitufe Utawala (Dhibiti) na kisha kwenye dirisha inayoonekana - kifungo Mwonekano wa Chati (Mwonekano wa mchoro). Inabakia kuburuta majina ya safu wima (ufunguo) wa kawaida (sehemu) kati ya jedwali ili kuunda viungo:

Baada ya hayo, katika muhtasari wa Mfano wa Data, unaweza kutupa katika eneo la muhtasari (safu, safu, vichungi, maadili) sehemu zozote kutoka kwa jedwali zozote zinazohusiana - kila kitu kitaunganishwa na kuhesabiwa kiotomatiki:

Faida ya 2: Hesabu maadili ya kipekee

Jedwali la egemeo la kawaida linatupa fursa ya kuchagua mojawapo ya vitendaji kadhaa vya kukokotoa vilivyojengewa ndani: jumla, wastani, hesabu, kiwango cha chini, cha juu zaidi, n.k. Katika muhtasari wa Muundo wa Data, kipengele muhimu sana cha kukokotoa kinaongezwa kwenye orodha hii ya kawaida ili kuhesabu idadi ya kipekee (thamani zisizorudiwa). Kwa msaada wake, kwa mfano, unaweza kuhesabu kwa urahisi idadi ya vitu vya kipekee vya bidhaa (mbalimbali) ambazo tunauza katika kila jiji.

Bonyeza kulia kwenye uwanja - amri Chaguzi za uga wa thamani na kwenye kichupo operesheni Kuchagua Idadi ya vipengele tofauti (Idadi tofauti):

Faida ya 3: Fomula Maalum za DAX

Wakati mwingine unapaswa kufanya mahesabu mbalimbali ya ziada katika jedwali za egemeo. Katika muhtasari wa kawaida, hii inafanywa kwa kutumia nyuga na vitu vilivyokokotwa, huku muhtasari wa modeli ya data ukitumia hatua katika lugha maalum ya DAX (DAX = Maneno ya Uchambuzi wa Data).

Ili kuunda kipimo, chagua kwenye kichupo pivoti ya nguvu Amri Hatua - Tengeneza Kipimo (Hatua - Kipimo kipya) au bonyeza tu kulia kwenye jedwali kwenye orodha ya Sehemu za Pivot na uchague Ongeza kipimo (Ongeza kipimo) kwenye menyu ya muktadha:

Katika dirisha linalofungua, weka:

• Jina la jedwaliambapo kipimo kilichoundwa kitahifadhiwa.
• Pima jina - jina lolote unaloelewa kwa uga mpya.
• Maelezo - hiari.
• Mfumo - jambo muhimu zaidi, kwa sababu hapa tunaingia kwa mikono, au bonyeza kitufe fx na uchague kazi ya DAX kutoka kwenye orodha, ambayo inapaswa kuhesabu matokeo wakati tunatupa kipimo chetu kwenye eneo la Maadili.
• Katika sehemu ya chini ya dirisha, unaweza kuweka mara moja muundo wa nambari kwa kipimo kwenye orodha Kategoria.
  

Lugha ya DAX sio rahisi kueleweka kila wakati kwa sababu haifanyi kazi na maadili ya mtu binafsi, lakini kwa safu wima na majedwali yote, yaani, inahitaji urekebishaji wa fikra baada ya fomula za kawaida za Excel. Hata hivyo, ni thamani yake, kwa sababu nguvu ya uwezo wake katika usindikaji kiasi kikubwa cha data ni vigumu overestimate.

Faida ya 4: Daraja maalum za uga

Mara nyingi, wakati wa kuunda ripoti za kawaida, lazima utupe michanganyiko sawa ya uwanja kwenye jedwali la egemeo katika mlolongo fulani, kwa mfano. Mwaka-Robo-Mwezi-Siku, Au Kitengo-Bidhaa, Au Nchi-Jiji-Mteja nk Katika muhtasari wa Mfano wa Data, tatizo hili linatatuliwa kwa urahisi kwa kuunda yako mwenyewe safu za juu - seti za uga maalum.

Katika dirisha la Egemeo la Nguvu, badilisha hadi modi ya chati kwa kitufe Mwonekano wa Chati tab Nyumbani (Nyumbani - Mwonekano wa Mchoro), chagua na Ctrl mashamba taka na haki-click juu yao. Menyu ya muktadha itakuwa na amri Unda Hierarkia (Unda uongozi):

Uongozi ulioundwa unaweza kubadilishwa jina na kuvutwa ndani yake na panya sehemu zinazohitajika, ili baadaye katika harakati moja zinaweza kutupwa kwa muhtasari:

Faida ya 5: Stencil maalum

Kuendeleza wazo la aya iliyotangulia, katika muhtasari wa Mfano wa Data, unaweza pia kuunda seti zako za vipengele kwa kila sehemu. Kwa mfano, kutoka kwa orodha nzima ya miji, unaweza kutengeneza kwa urahisi seti ya wale tu walio katika eneo lako la uwajibikaji. Au kukusanya wateja wako pekee, bidhaa zako, n.k. katika seti maalum.

Ili kufanya hivyo, kwenye kichupo Uchambuzi wa jedwali la egemeo katika orodha kunjuzi Sehemu, Vipengee na Seti kuna amri zinazolingana (Changanua - Mashamba, Items & Seti - Unda seti kulingana na vitu vya safu / safu):

Katika dirisha linalofungua, unaweza kuchagua kuondoa, kuongeza au kubadilisha nafasi ya vitu vyovyote na kuhifadhi matokeo yaliyowekwa chini ya jina jipya:

Seti zote zilizoundwa zitaonyeshwa kwenye kidirisha cha Sehemu za PivotTable katika folda tofauti, kutoka ambapo zinaweza kuburutwa kwa uhuru hadi kwenye safu mlalo na safu wima maeneo ya PivotTable yoyote mpya:

Manufaa ya 6: Ficha Majedwali na Safu kwa Chaguo

Ingawa hii ni faida ndogo, lakini ya kupendeza sana katika hali zingine. Kwa kubofya kulia kwenye jina la shamba au kwenye kichupo cha jedwali kwenye dirisha la Power Pivot, unaweza kuchagua amri Ficha kutoka kwa Zana ya Mteja (Ficha kutoka kwa Zana za Mteja):

Safu wima au jedwali lililofichwa litatoweka kwenye kidirisha cha Orodha ya Sehemu ya PivotTable. Ni rahisi sana ikiwa unahitaji kuficha kutoka kwa mtumiaji safu zingine za msaidizi (kwa mfano, zilizohesabiwa au safu wima zilizo na maadili muhimu ya kuunda uhusiano) au hata meza nzima.

Faida 7. Uchimbaji wa hali ya juu

Ukibofya mara mbili kisanduku chochote katika eneo la thamani katika jedwali la egemeo la kawaida, basi Excel itaonyesha kwenye laha tofauti nakala ya kipande cha data cha chanzo ambacho kilihusika katika kukokotoa kisanduku hiki. Hili ni jambo linalofaa sana, linaloitwa rasmi Drill-down (kwa kawaida wanasema "kushindwa").

Katika muhtasari wa Muundo wa Data, zana hii inayofaa inafanya kazi kwa hila zaidi. Kwa kusimama kwenye seli yoyote na matokeo ambayo yanatupendeza, unaweza kubofya ikoni na glasi ya kukuza ambayo inajitokeza karibu nayo (inaitwa. Mwelekeo wa Express) kisha uchague uwanja wowote unaovutiwa nao kwenye jedwali lolote linalohusiana:

Baada ya hapo, thamani ya sasa (Model = Explorer) itaingia kwenye eneo la chujio, na muhtasari utajengwa na ofisi:

Kwa kweli, utaratibu kama huo unaweza kurudiwa mara nyingi, ukizingatia data yako kwa mwelekeo unaopenda.

Manufaa ya 8: Badilisha Egemeo kuwa Shughuli za Mchemraba

Ukichagua kisanduku chochote katika muhtasari wa Muundo wa Data kisha uchague kwenye kichupo Uchambuzi wa jedwali la egemeo Amri Zana za OLAP - Badilisha hadi Mifumo (Changanua - Zana za OLAP - Badilisha kuwa fomula), basi muhtasari wote utabadilishwa kiotomatiki kuwa fomula. Sasa maadili ya uwanja katika eneo la safu-safu na matokeo katika eneo la thamani yatapatikana kutoka kwa Mfano wa Data kwa kutumia kazi maalum za mchemraba: CUBEVALUE na CUBEMEMBER:

Kitaalam, hii inamaanisha kuwa sasa hatushughulikii muhtasari, lakini kwa seli kadhaa zilizo na fomula, yaani, tunaweza kufanya mabadiliko yoyote kwa urahisi na ripoti yetu ambayo haipatikani katika muhtasari, kwa mfano, ingiza safu mlalo au safu wima mpya katikati. ya ripoti, fanya mahesabu yoyote ya ziada ndani ya muhtasari, yapange kwa njia yoyote unayotaka, nk.

Wakati huo huo, uunganisho na data ya chanzo, bila shaka, unabaki na katika siku zijazo fomula hizi zitasasishwa wakati vyanzo vinabadilika. Uzuri!

• Uchanganuzi wa ukweli wa mpango katika jedwali la egemeo lenye Pivot ya Nishati na Hoji ya Nishati
• Jedwali la egemeo lenye kichwa cha mistari mingi
• Unda hifadhidata katika Excel ukitumia Power Pivot