Fa'idodin Pivot ta Samfurin Bayanai

Lokacin gina tebur pivot a cikin Excel, a cikin akwatin tattaunawa na farko, inda aka umarce mu da mu saita kewayon farko kuma mu zaɓi wurin da za mu saka teburin pivot, akwai akwati mai mahimmanci amma mai mahimmanci a ƙasa - Ƙara wannan bayanan zuwa Model Data (Ƙara wannan bayanan zuwa Model Data) kuma, kadan mafi girma, mai sauyawa Yi amfani da samfurin bayanan wannan littafin (Yi amfani da Model Data na wannan littafin aikin):

Abin baƙin ciki shine, yawancin masu amfani waɗanda suka saba da allunan pivot na dogon lokaci kuma sun sami nasarar amfani da su a cikin aikinsu wani lokacin ba sa fahimtar ma'anar waɗannan zaɓuɓɓuka kuma ba sa amfani da su. Kuma a banza. Bayan haka, ƙirƙirar tebur pivot don Model Data yana ba mu fa'idodi masu mahimmanci da yawa idan aka kwatanta da tebur pivot na Excel na gargajiya.

Koyaya, kafin yin la'akari da waɗannan “buns” kusa, bari mu fara fahimtar menene, a zahiri, wannan Model ɗin Data?

Menene Samfurin Bayanai

Samfurin Bayanai (wanda aka rage shi azaman MD ko DM = Model Data) yanki ne na musamman a cikin fayil ɗin Excel inda zaku iya adana bayanan tabular - tebur ɗaya ko fiye waɗanda aka haɗa, idan ana so, ga juna. A zahiri, wannan ƙaramin bayanai ne (OLAP cube) wanda aka saka a cikin littafin aikin Excel. Idan aka kwatanta da na gargajiya ajiya na bayanai a cikin nau'i na yau da kullum (ko mai kaifin) tebur a kan zanen gado na Excel kanta, da Data Model yana da yawa gagarumin abũbuwan amfãni:

• Tables na iya zama har zuwa Layukan biliyan 2, kuma takardar Excel na iya dacewa da kadan fiye da miliyan 1.
• Duk da girman girman girman, ana yin aiki da irin waɗannan tebur (tace, rarrabawa, ƙididdiga akan su, taƙaitaccen gini, da sauransu). sosai azumi Mafi sauri fiye da Excel kanta.
• Tare da bayanan da ke cikin Model, zaku iya yin ƙarin lissafin (idan ana so, mai rikitarwa) ta amfani da ginanniyar harshe DAX.
  
• Duk bayanan da aka ɗora a cikin Tsarin Bayanai suna da yawa da karfi matsa ta yin amfani da ginannen kayan tarihi na musamman kuma a maimakon haka yana ƙara girman girman ainihin fayil ɗin Excel.
  

Ana sarrafa samfurin kuma ana ƙididdige shi ta hanyar ƙari na musamman da aka gina a cikin Microsoft Excel - powerpivotgame da wanda na riga na rubuta. Don kunna shi, akan shafin developer click COM add-ins (Mai haɓakawa - COM Add-ins) kuma duba akwatin da ya dace:

Idan tabs developer (Mai haɓakawa)Ba za ku iya ganinsa a kan ribbon ba, kuna iya kunna ta Fayil - Zaɓuɓɓuka - Saitin Ribbon (Fayil - Zaɓuɓɓuka - Keɓance Ribbon). Idan a cikin taga da aka nuna a sama a cikin jerin add-ins na COM ba ku da Power Pivot, to ba a haɗa shi cikin sigar Microsoft Office ɗin ku 🙁

A shafin Power Pivot da ya bayyana, za a sami babban maɓalli koren haske management (Gudanarwa), danna wanda zai buɗe taga Power Pivot a saman Excel, inda zamu ga abubuwan da ke cikin Model Data na littafin yanzu:

Muhimmin bayanin kula akan hanya: littafin aikin Excel zai iya ƙunsar Model Data ɗaya kawai.

Load da allunan cikin Tsarin Bayanai

Don loda bayanai a cikin Model, da farko za mu juya teburin zuwa gajeriyar hanyar maballin “smart” mai ƙarfi Ctrl+T kuma ku ba shi suna na abokantaka akan shafin Constructor (Zane). Wannan mataki ne da ake bukata.

Sannan zaku iya amfani da kowane ɗayan hanyoyin guda uku don zaɓar daga:

• Danna maɓallin Ƙara zuwa Samfura (Ƙara zuwa Model Data) tab powerpivot tab Gida (Gida).
  
• Zabar ƙungiyoyi Saka - PivotTable (Saka - Tebur Pivot) kuma kunna akwati Ƙara wannan bayanan zuwa Model Data (Ƙara wannan bayanan zuwa Model Data). A wannan yanayin, bisa ga bayanan da aka ɗora a cikin Model, ana kuma gina teburin pivot nan da nan.
  
• A kan Babba shafin data (Kwanan wata) danna maballin Daga Tebura/Range (Daga Tebur/Range)don loda teburin mu cikin editan Query Query. Wannan hanya ita ce mafi tsawo, amma, idan ana so, a nan za ku iya yin ƙarin tsaftace bayanan, gyara da kowane nau'i na canje-canje, wanda Query Query ke da ƙarfi sosai.

  Sannan ana shigar da bayanan da aka haɗa su zuwa Model ta umarnin Gida - Rufe kuma Loda - Rufe kuma Load a… (Gida - Rufe & Load - Rufe & Loda zuwa…). A cikin taga da yake buɗewa, zaɓi zaɓi Kawai ƙirƙirar haɗi (ƙirƙiri haɗi kawai) kuma, mafi mahimmanci, sanya kaska Ƙara wannan bayanan zuwa Model Data (Ƙara wannan bayanan zuwa Model Data).
  

Mun gina taƙaitaccen Tsarin Bayanai

Don gina samfurin taƙaitaccen bayani, zaku iya amfani da kowace hanya guda uku:

• Latsa maballin teburin taƙaitaccen bayani (Table Pivot) a cikin Power Pivot taga.
• Zaɓi umarni a cikin Excel Saka - PivotTable kuma canza zuwa yanayin Yi amfani da samfurin bayanan wannan littafin (Saka - Teburin Pivot - Yi amfani da Tsarin Bayanai na wannan littafin).
• Zabar ƙungiyoyi Saka - PivotTable (Saka - Tebur Pivot) kuma kunna akwati Ƙara wannan bayanan zuwa Model Data (Ƙara wannan bayanan zuwa Model Data). Za a ɗora teburin "mai wayo" na yanzu a cikin Model kuma za a gina teburin taƙaitaccen bayani don duka Model.

Yanzu da muka gano yadda ake loda bayanai a cikin Model Data kuma gina taƙaice a kai, bari mu bincika fa'idodi da fa'idodi da wannan ke ba mu.

Amfani 1: Dangantaka tsakanin tebur ba tare da amfani da dabara ba

Za'a iya gina taƙaice na yau da kullun ta amfani da bayanai daga tebur tushe ɗaya kawai. Idan kana da da yawa daga cikinsu, alal misali, tallace-tallace, jerin farashi, kundin adireshi na abokin ciniki, rijistar kwangila, da sauransu, to sai ka fara tattara bayanai daga duk tebur zuwa ɗaya ta amfani da ayyuka kamar VLOOKUP. (VLOOKUP), INDEX (INDEX), MORE BAYYANA (MATSAYI), SUMMESLIMN (SUMIFS) da makamantansu. Wannan yana da tsayi, mai ban sha'awa kuma yana fitar da Excel ɗin ku cikin "tunani" tare da adadi mai yawa na bayanai.

A cikin yanayin taƙaitaccen Tsarin Bayanai, komai ya fi sauƙi. Ya isa don saita alaƙa tsakanin tebur sau ɗaya a cikin taga Power Pivot - kuma an gama. Don yin wannan, a kan tab powerpivot danna maɓallin management (Gudanarwa) sa'an nan kuma a cikin taga wanda ya bayyana - maballin Duban Chart (Duba zane). Ya rage don ja sunayen ginshiƙan gama gari (maɓalli) (filaye) tsakanin teburi don ƙirƙirar hanyoyin haɗi:

Bayan haka, a cikin taƙaitaccen bayanin Model Data, zaku iya jefawa a cikin yanki na taƙaitaccen ( layuka, ginshiƙai, masu tacewa, ƙima) kowane filayen daga kowane tebur masu alaƙa - duk abin da za'a haɗa kuma ana lissafta ta atomatik:

Fa'ida ta 2: Ƙididdige ƙididdiga na musamman

Teburin pivot na yau da kullun yana ba mu damar zaɓar ɗaya daga cikin ayyukan ƙididdigewa da yawa: jimla, matsakaita, ƙidaya, mafi ƙarancin, matsakaici, da sauransu. adadi na musamman (marasa maimaituwa). Tare da taimakonsa, alal misali, zaku iya ƙidaya adadin abubuwan musamman na kayayyaki (kewayon) waɗanda muke siyarwa a kowane birni.

Danna-dama akan filin - umarni Zaɓuɓɓukan filin darajar kuma a kan tab Operation zabi Adadin abubuwa daban-daban (ƙididdigar ƙididdiga):

Amfani 3: Tsarin DAX na Musamman

Wani lokaci dole ne ka aiwatar da ƙarin ƙididdiga daban-daban a cikin tebur pivot. A cikin taƙaitaccen bayani na yau da kullun, ana yin wannan ta amfani da filaye da abubuwa masu ƙididdigewa, yayin da taƙaitaccen ƙirar bayanai ke amfani da ma'auni a cikin wani harshe na musamman na DAX (DAX = Bayanan Binciken Bayanai).

Don ƙirƙirar ma'auni, zaɓi kan shafin powerpivot umurnin Matakan - Ƙirƙiri Ma'auni (Ma'auni - Sabon ma'auni) ko kawai danna dama akan tebur a cikin Filayen Pivot kuma zaɓi Ƙara ma'auni (Ƙara ma'auni) a cikin menu na mahallin:

A cikin taga da yake buɗewa, saita:

• Sunan teburinda za a adana ma'aunin da aka ƙirƙira.
• Auna suna - kowane suna da kuka fahimta don sabon filin.
• description – na zaɓi.
• formula - abu mafi mahimmanci, saboda a nan muna ko dai shigar da hannu, ko danna maɓallin fx kuma zaɓi aikin DAX daga lissafin, wanda yakamata ya ƙididdige sakamakon lokacin da muka jefa ma'aunin mu a cikin Wuraren Ƙimar.
• A cikin ƙananan ɓangaren taga, zaku iya saita tsarin lamba nan da nan don ma'auni a cikin jerin category.
  

Harshen DAX ba koyaushe yana da sauƙin fahimta ba saboda yana aiki ba tare da ƙimar mutum ɗaya ba, amma tare da duka ginshiƙai da teburi, watau yana buƙatar sake fasalin tunani bayan ƙa'idodin Excel na yau da kullun. Duk da haka, yana da daraja, saboda ƙarfin ikonsa a cikin sarrafa bayanai masu yawa yana da wuyar ƙima.

Fa'ida 4: Matsayin filin na musamman

Sau da yawa, lokacin ƙirƙirar rahotanni na yau da kullun, dole ne ku jefa mahaɗin filaye iri ɗaya cikin tebur pivot a jerin da aka bayar, misali. Shekara-kwata-wata-rana, ko Category- Samfura, ko Ƙasa-Birnin- Abokin ciniki da sauransu. A cikin taƙaitaccen samfurin Data, ana samun sauƙin warware wannan matsala ta hanyar ƙirƙirar naku matsayi - saitunan filin al'ada.

A cikin Power Pivot taga, canza zuwa yanayin ginshiƙi tare da maɓallin Duban Chart tab Gida (Gida - Duban zane), zaɓi tare da Ctrl filayen da ake so kuma danna-dama akan su. Menu na mahallin zai ƙunshi umarnin Ƙirƙiri Matsayi (ƙirƙiri matsayi):

Za a iya canza tsarin da aka ƙirƙira kuma a jawo shi tare da linzamin kwamfuta filayen da ake buƙata, ta yadda daga baya a cikin motsi ɗaya za a iya jefa su cikin taƙaitaccen bayani:

Amfani 5: Custom stencil

Ci gaba da ra'ayin sakin layi na baya, a cikin taƙaitaccen Tsarin Bayanai, zaku iya ƙirƙirar abubuwan abubuwan ku don kowane filin. Misali, daga duk jerin biranen, zaku iya sauƙaƙe saitin waɗanda kawai ke yankin ku na alhakin. Ko tara abokan cinikin ku kawai, kayanku, da sauransu cikin saiti na musamman.

Don yin wannan, a kan tab Binciken tebur pivot a cikin drop down list Filaye, Abubuwa, da Saiti akwai umarni masu dacewa (Bincike - Filaye, Items & Sets - Ƙirƙiri saiti dangane da abubuwan layi/shafi):

A cikin taga da ke buɗewa, zaku iya zaɓin cirewa, ƙara ko canza matsayin kowane abubuwa kuma adana saitin da aka samo a ƙarƙashin sabon suna:

Duk saitin da aka ƙirƙira za a nuna su a cikin PivotTable Fields panel a cikin wani babban fayil daban, daga inda za a iya jan su kyauta zuwa layuka da ginshiƙan kowane sabon PivotTable:

Fa'ida ta 6: Zaɓin Ɓoye Tebura da ginshiƙai

Ko da yake wannan ƙarami ne, amma fa'ida mai daɗi sosai a wasu lokuta. Ta danna dama akan sunan filin ko akan shafin tebur a cikin taga Power Pivot, zaku iya zaɓar umarnin Boye daga Kayan aikin Abokin ciniki (Boye daga Kayan aikin Abokin Ciniki):

Rukunin da ke ɓoye ko tebur zai ɓace daga faren Lissafin Filin PivotTable. Yana da matukar dacewa idan kuna buƙatar ɓoye daga mai amfani da wasu ginshiƙan taimako (misali, ƙididdigewa ko ginshiƙai tare da maɓalli masu mahimmanci don ƙirƙirar alaƙa) ko ma duka tebur.

Amfani 7. Advanced drill-down

Idan ka danna kowane tantanin halitta sau biyu a cikin ƙimar ƙimar a cikin tebur pivot na yau da kullun, to Excel yana nuna kwafin guntun bayanan tushen da ke cikin lissafin wannan tantanin halitta. Wannan abu ne mai matukar amfani, wanda ake kira Drill-down a hukumance (a galibi ana cewa “kasa”).

A cikin taƙaitaccen Samfurin Bayanai, wannan kayan aiki mai amfani yana aiki da wayo. Ta hanyar tsayawa akan kowane tantanin halitta tare da sakamakon da ke sha'awar mu, zaku iya danna gunkin tare da gilashin ƙara girma wanda ke tashi kusa da shi (ana kiran shi. Bayyana Trends) sannan ka zaɓi kowane filin da kake sha'awar kowane tebur mai alaƙa:

Bayan haka, ƙimar na yanzu (Model = Explorer) za ta shiga cikin wurin tacewa, kuma ofisoshi za su gina taƙaitaccen bayanin:

Tabbas, ana iya maimaita irin wannan hanya sau da yawa, akai-akai zurfafa cikin bayanan ku ta hanyar da kuke sha'awar.

Amfani 8: Maida Pivot zuwa Ayyukan Cube

Idan ka zaɓi kowane tantanin halitta a cikin taƙaitaccen bayanin Model ɗin bayanai sannan zaɓi kan shafin Binciken tebur pivot umurnin Kayan aikin OLAP - Canza zuwa Formulas (Bincike - Kayan aikin OLAP - Canza zuwa dabaru), sa'an nan gaba dayan summary za a canza ta atomatik zuwa dabara. Yanzu ƙimar filin a cikin layin layi-layi da sakamakon da ke cikin yankin ƙimar za a dawo da su daga Model Data ta amfani da ayyukan cube na musamman: CUBEVALUE da CUBEMEMBER:

A fasaha, wannan yana nufin cewa yanzu ba mu magana game da taƙaitaccen bayani ba, amma tare da sel da yawa tare da tsari, watau za mu iya yin kowane canji cikin sauƙi tare da rahotonmu wanda babu shi a cikin taƙaitaccen bayani, misali, saka sababbin layuka ko ginshiƙai a tsakiya. na rahoton, yi kowane ƙarin ƙididdiga a cikin taƙaitaccen bayani, shirya su ta kowace hanya da ake so, da sauransu.

A lokaci guda, haɗin kai tare da bayanan tushen, ba shakka, ya kasance kuma a nan gaba za a sabunta waɗannan dabarun lokacin da tushen ya canza. A kyau!

• Binciken gaskiya-tsari a cikin tebur pivot tare da Power Pivot da Query Query
• Teburin Pivot tare da taken multiline
• Ƙirƙirar bayanai a cikin Excel ta amfani da Power Pivot