Tace Matsalolin PivotTables A lokaci ɗaya

Lokacin ƙirƙirar rahotanni masu rikitarwa kuma, musamman, dashboards a cikin Microsoft Excel, sau da yawa ya zama dole don tace tebur pivot da yawa lokaci guda. Bari mu ga yadda za a iya aiwatar da wannan.

Hanyar 1: Babban Slicer don tace pivots akan tushen bayanai iri ɗaya

Idan an gina pivots akan teburin bayanan tushen guda ɗaya, to hanya mafi sauƙi ita ce amfani da su don tace su lokaci guda. sashe filtaccen maɓalli ne mai hoto wanda aka haɗa zuwa duk tebur pivot lokaci ɗaya.

Don ƙara shi, zaɓi kowane tantanin halitta a ɗaya daga cikin taƙaitawa kuma akan shafin analysis zaɓi ƙungiya Manna Yanki (Bincike - Saka slicer). A cikin taga da ke buɗewa, duba akwatunan ginshiƙan da kuke son tace bayanai kuma danna OK:

Yankan da aka ƙirƙira, ta tsohuwa, za ta tace pivot ɗin da aka ƙirƙira don shi. Duk da haka, amfani da button Rahoton Haɗin kai (Rahoton haɗin kai) tab yanki (Yanki) a sauƙaƙe za mu iya ƙara wasu tebur na taƙaitawa cikin jerin tsararren tebur:

Hanya 2. Gabaɗaya yanki don tace taƙaitaccen bayani akan tushe daban-daban

Idan ba a gina pivots ɗin ku ba bisa ga ɗaya ba, amma bisa ga teburin bayanan tushen daban-daban, to hanyar da ke sama ba za ta yi aiki ba, saboda a cikin taga. Rahoton Haɗin kai kawai waɗancan takaitattun abubuwan da aka gina daga tushe ɗaya kawai ana nunawa.

Koyaya, zaku iya samun sauƙin kusanci wannan iyakance idan kuna amfani da Tsarin Bayanai (mun tattauna shi dalla-dalla a cikin wannan labarin). Idan muka ɗora teburin mu a cikin Model kuma muka haɗa su a can, to, tacewa zai shafi duka tebur biyu a lokaci guda.

Bari mu ce muna da tebur biyu don tallace-tallace da farashin sufuri azaman bayanan shigarwa:

A ce mun fuskanci aikin gina namu taƙaitaccen bayanin kowane ɗayansu sannan mu tace su a lokaci ɗaya ta hanyar birane tare da yanke gama gari.

Muna yin haka:

1. Juya Teburinmu na Asali zuwa Teburan Waya Mai Tsayi tare da Gajerar Allon madannai Ctrl+T ko umarni Gida - Tsarin azaman tebur (Gida - Tsarin azaman Tebur) kuma a ba su sunaye tablProdaji и tabTransport tab Constructor (Zane).

2. Load da biyu tebur bi da bi a cikin Model ta amfani da button Ƙara zuwa Samfuran Bayanai a kan Power Pivot tab.

Ba zai yiwu a haɗa waɗannan tebur ɗin kai tsaye a cikin Model ba, saboda yayin da Power Pivot kawai ke goyan bayan alaƙa ɗaya zuwa da yawa, watau yana buƙatar ɗayan tebur ɗin don samun kwafi a cikin ginshiƙi da muke haɗawa. Muna da iri ɗaya a cikin tebur biyu a cikin filin City akwai maimaitawa. Don haka muna buƙatar ƙirƙira wani tebirin dubawa na tsaka-tsaki tare da jerin sunayen musamman na birni daga tebur biyu. Hanya mafi sauƙi don yin wannan ita ce tare da ayyukan ƙarawa na Ƙarfin Ƙirar, wanda aka gina a cikin Excel tun daga 2016 (kuma don Excel 2010-2013 ana sauke shi kyauta daga gidan yanar gizon Microsoft).

3. Bayan zaɓar kowane tantanin halitta a cikin tebur na “smart”, muna loda su ɗaya bayan ɗaya a cikin Query Query tare da maɓallin Daga tebur / kewayon tab data (Data - Daga tebur / kewayon) sa'an nan a cikin Power Query taga zaži on babban teams Rufewa da lodi - Rufe kuma shigar da ciki (Gida - Rufe & Load - Rufe & Loda zuwa…) da kuma shigo da zaɓi Kawai ƙirƙirar haɗi (ƙirƙiri haɗi kawai):

4. Muna haɗa tebur biyu zuwa ɗaya tare da umarnin Bayanai - Haɗa Tambayoyi - Ƙara (Bayani - Haɗa tambayoyin - Ƙara). Rukunin da ke da sunaye iri ɗaya a cikin taken za su dace a ƙarƙashin juna (kamar ginshiƙi City), kuma waɗanda ba su dace ba za a sanya su a cikin ginshiƙai daban-daban (amma wannan ba shi da mahimmanci a gare mu).

5. Share duk ginshiƙai ban da shafi Cityta danna dama a kan take kuma zaɓi umarnin Share wasu ginshiƙai (Cire wasu ginshiƙai) sannan a cire duk kwafin sunayen birni ta hanyar danna dama a kan shafi kuma zaɓi umarni Cire Kwafin (Cire kwafi):

6. An ɗora lissafin abubuwan da aka ƙirƙira zuwa Tsarin Bayanai ta hanyar Gida - Rufe kuma lodi - Rufe kuma shigar da ciki (Gida - Rufe & Load - Rufe & Loda zuwa…) kuma zaɓi zaɓi Kawai ƙirƙirar haɗi (ƙirƙiri haɗi kawai) kuma mafi mahimmanci! – kunna akwati Ƙara wannan bayanan zuwa samfurin bayanai (Ƙara wannan bayanan zuwa Model Data):

7. Yanzu za mu iya, komawa zuwa Power Pivot taga (tab powerpivot - maballin management), canza zuwa Duban Chart (Duba zane) da kuma haɗa tebur ɗinmu na tallace-tallace da farashin sufuri ta hanyar matsakaicin kundin adireshi na birane (ta jawo filayen tsakanin tebur):

8. Yanzu zaku iya ƙirƙirar allunan pivot da ake buƙata don ƙirar ƙirƙira ta amfani da maɓallin teburin taƙaitaccen bayani (Table Pivot) on babban (Gida) tab a cikin Power Pivot taga kuma, ta zaɓar kowane tantanin halitta a kowane pivot, akan shafin analysis ƙara maɓallin yanki Manna Yanki (Bincike - Saka Slicer) kuma zaɓi yanki a cikin akwatin lissafin City a cikin ƙaramar littafin:

Yanzu, ta danna kan saba button Rahoton Haɗin kai on Yanki tab (Slicer - Rahoton haɗin kai) za mu ga duk taƙaitaccen bayaninmu, saboda yanzu an gina su akan teburan tushe masu alaƙa. Ya rage don kunna akwatunan rajistan da suka ɓace kuma danna kan OK - kuma slicer namu zai fara tace duk zaɓaɓɓun allunan pivot a lokaci guda.