Canja lissafin a cikin PivotTable tare da masu yankan

Ana iya amfani da slicers a cikin allunan pivot ba kawai ta hanyar gargajiya ba - don tace bayanan tushen, amma har ma don canzawa tsakanin nau'ikan ƙididdiga daban-daban a cikin yankin ƙimar:

Aiwatar da wannan abu ne mai sauƙi - duk abin da kuke buƙata shine nau'i-nau'i biyu da tebur na taimako. Da kyau, za mu yi duk wannan ba a cikin taƙaitaccen bayanin da aka saba ba, amma a cikin taƙaitaccen bayanin da aka gina bisa ga Tsarin Bayanai na Power Pivot.

Mataki 1. Haɗa ƙarawar Pivot Power

Idan ba'a iya ganin abubuwan ƙarawar Power Pivot a cikin Excel ɗinku, da farko kuna buƙatar kunna shi. Akwai zaɓuɓɓuka guda biyu don wannan:

• tab developer - maballin COM add-ins (Mai haɓakawa - COM Add-ins)
• Fayil - Zaɓuɓɓuka - Add-ins - COM Add-ins - Tafi (Fayil - Zabuka - Add-ins - COM-Add-ins - Je zuwa)

Idan wannan bai taimaka ba, to gwada sake kunna Microsoft Excel.

Mataki 2: Load Data a cikin Power Pivot Model

Za mu sami tebur biyu a matsayin bayanan farko:

Na farko shine tebur tare da tallace-tallace, bisa ga abin da za mu gina taƙaitaccen bayani daga baya. Na biyu tebur ne mai taimako, inda aka shigar da sunayen maɓallan yanki na gaba.

Duk waɗannan allunan biyu suna buƙatar canzawa zuwa “smart” (tsari) tare da gajeriyar hanyar madannai Ctrl+T ko tawaga Gida - Tsarin azaman tebur (Gida - Tsarin azaman Tebur) kuma yana da kyawawa a ba su sunaye masu hankali akan shafin Constructor (Zane). Bari shi, misali, Tallace-tallace и sabis.

Bayan haka, kowane tebur bi da bi yana buƙatar a loda shi cikin Tsarin Bayanai - don wannan muna amfani da shafin powerpivot button Ƙara zuwa Samfuran Bayanai (Ƙara zuwa Model Data).

Mataki 3. Ƙirƙiri ma'auni don ƙayyade maɓallin da aka danna akan yanki

Ana kiran filayen da aka lissafa a cikin PivotTable ta Model Data matakan. Bari mu ƙirƙiri ma'auni wanda zai nuna sunan maɓallin da aka danna akan yanki na gaba. Don yin wannan, a cikin kowane tebur ɗinmu, zaɓi kowane tantanin halitta mara komai a cikin ƙananan kwamiti na lissafi kuma shigar da ginin mai zuwa cikin ma'aunin dabara:

Anan, sunan ma'aunin ya fara zuwa (Maɓallin latsawa), sa'an nan kuma bayan colon da alamar daidai, da dabara don lissafta ta ta amfani da aikin SAURARA DAX an gina shi cikin Power Pivot.

Idan kun maimaita wannan ba a cikin Power Pivot ba, amma a cikin Power BI, to ba a buƙatar colon kuma a maimakon haka SAURARA zaka iya amfani da takwaransa na zamani - aikin SELECTEDVALUE.

Ba mu kula da kurakurai a cikin ƙananan ɓangaren taga wanda ya bayyana bayan shigar da dabarar - sun taso, saboda har yanzu ba mu da taƙaitaccen bayani da yanki wanda aka danna wani abu.

Mataki 4. Ƙirƙiri ma'auni don lissafin akan maɓallin danna

Mataki na gaba shine ƙirƙirar ma'auni don zaɓuɓɓukan lissafin daban-daban dangane da ƙimar ma'aunin baya Maɓallin latsawa. Anan tsarin ya ɗan fi rikitarwa:

Mu raba shi guntu-guntu:

1. aiki SAUYA - analogue na gida IF - yana duba cikar ƙayyadaddun sharuɗɗan kuma ya dawo da ƙima daban-daban dangane da cikar wasu daga cikinsu.
2. aiki GASKIYA() - yana ba da "gaskiya" mai ma'ana ta yadda yanayin da aka bincika daga baya ta aikin SWITCH yayi aiki kawai idan an cika su, watau gaskiya.
3. Sa'an nan kuma muna duba ƙimar ma'auni da aka danna da kuma ƙididdige sakamakon ƙarshe don zaɓuɓɓuka daban-daban guda uku - a matsayin jimlar farashi, matsakaicin rajistan da adadin masu amfani na musamman. Don ƙidaya ƙididdiga na musamman, yi amfani da aikin DISTINCTCOUNT, kuma don zagaye - zagaye.
4. Idan babu ɗayan sharuɗɗan uku na sama da aka cika, to ana nuna hujja ta ƙarshe na aikin SWITCH - mun saita shi azaman dummy ta amfani da aikin. BLANK ().

Mataki 5. Gina taƙaitaccen bayani da ƙara yanki

Ya rage don dawowa daga Power Pivot zuwa Excel kuma gina tebur pivot a can don duk bayananmu da matakan mu. Don yin wannan, a cikin Power Pivot taga kunna babban shafin zaɓi umarni teburin taƙaitaccen bayani (Gida - Tebur Pivot).

Sa'an nan:

1. Muna jefa filin Samfur daga tebur Tallace-tallace zuwa yankin Rows (Layuka).
2. Jifa da filin can Sakamako daga tebur sabis.
3. Danna dama akan filin Sakamako kuma zaɓi ƙungiya Ƙara azaman yanki (Ƙara azaman Slicer).
4. Jifar ma'auni na biyu Ƙaddamarwa daga tebur sabis zuwa yankin dabi'u (Dabi'u).

Anan, a gaskiya, duk dabaru ne. Yanzu zaku iya danna maɓallan slicer - kuma jimlar a cikin tebur pivot zai canza zuwa aikin da kuke buƙata.

Beauty 🙂

• Fa'idodin Pivot ta Samfurin Bayanai
• Binciken gaskiya-tsari a cikin tebur pivot akan Power Pivot
• Ƙirƙiri bayanan bayanai a cikin Excel ta amfani da ƙarawar Power Pivot