Teburin Pivot na Excel: motsa jiki na asali

Don ƙarin fahimtar maƙasudi da tasirin amfani da PivotTable a cikin Excel, bari mu ga jagorar mataki-mataki kan yadda ake ƙirƙirar PivotTable a cikin Excel.

Don wannan misali mai sauƙi muna amfani da maƙunsar rubutu, wanda ke jera bayanan tallace-tallace na kamfani.

Rubutun yana nuna kwanan watan siyarwa, sunan mai siyarwa, lardin, sashi da juzu'i.

Misalin da ke gaba yana ƙirƙirar tebur mai mahimmanci wanda ke nuna jimlar tallace-tallace na kowane wata na shekara, wanda lardin tallace-tallace da wakilin tallace-tallace suka rushe. Tsarin ƙirƙirar wannan tebur pivot shine kamar haka:

1. Zaɓi kowane tantanin halitta a cikin kewayon bayanai o zaɓi duk kewayon bayanan don amfani da su a cikin tebur pivot (Lura: Idan kun zaɓi tantanin halitta guda ɗaya a cikin kewayon bayanan, Excel zai gano ta atomatik kuma ya zaɓi kewayon kewayon bayanan don tebur pivot.)
2. Danna maɓallin PivotTable, wanda ke cikin rukunin "Tables", akan shafin "Saka" na ribbon na Excel.

3. Za a gabatar muku da akwatin maganganu "Ƙirƙiri PivotTable".

Tabbatar cewa kewayon da aka zaɓa yana nufin kewayon sel da kuke son amfani da su don tebur pivot (idan kun ƙirƙiri tebur, kamar yadda a cikin misali, komai ya fi sauƙi saboda za ku koma teburin kuma ba za ku ƙara zuwa ga daidaitawa ba).

Hakanan akwai zaɓi wanda zai tambaye ku inda kuke son sanya teburin pivot. Wannan yana ba ku damar sanya tebur pivot a cikin takamaiman takaddar aiki. In ba haka ba, zaɓi zaɓi na farkodefinita sabon takardar aiki .

Danna kan OK .

4. Yanzu za a gabatar muku da daya tebur pivot blank da PivotTable Field List taskar ayyuka, wanda ya ƙunshi filayen bayanai da yawa. Lura cewa waɗannan su ne kanun rubutun bayanan farko.

Muna son tebur pivot yana nuna jimlar bayanan tallace-tallace na kowane wata, wanda yanki da wakilin tallace-tallace suka rushe.

Saboda haka, daga aikin "PivotTable Field List" ayyuka:

• Jawo filin"Date"a cikin yankin da aka yiwa alama"Rows";
• Jawo filin"Sales"a cikin yankin da aka yiwa alama"Values Σ";
• Jawo filin"Province"a cikin yankin da aka yiwa alama"Columns";
• Jawo"Seller“. a unguwar da ake kira"Columns".

5. Teburin pivot da aka samu za a cika shi tare da jimlar tallace-tallace na yau da kullun don kowane yanki na tallace-tallace da kowane wakilin tallace-tallace, kamar yadda aka nuna a ƙasa.

Kamar yadda kake gani, kwanakin an riga an haɗa su ta wata-wata, tare da jimlar dangi na jimlar adadin (wannan rukunin atomatik ya dogara da sigar Excel, tare da sigogin da suka gabata ya zama dole don rukuni ta wata, da hannu).

Kuna iya saita salo ga sel, kamar kuɗi don lambobi saboda adadin kuɗin shiga ne.

Tace Rahoton PivotTable

Tacewar rahoton PivotTable yana ba ku damar duba bayanai don ƙima ɗaya ko zaɓi na ƙimar da aka ƙayyade a cikin filayen bayanai.

Misali, a cikin PivotTable na baya, zaku iya ganin bayanai kawai ta yankin tallace-tallace, kamar lardi.

Don duba kawai bayanai na lardin Turin (TO), komawa zuwa aikin "PivotTable Field List" ayyuka kuma ja maɓallin filin "Lardi" a cikin "Filter Report" (ko "Filters") yankin.

Za ku ga cewa filin "Lardi" yana bayyana a saman teburin pivot. Yi amfani da jerin abubuwan da aka saukar a cikin wannan filin don zaɓar lardin Turin. Sakamakon tebur pivot yana nuna tallace-tallace kawai don lardin Turin.

Hakanan zaka iya duba tallace-tallace da sauri don yankin Piedmont ta zaɓar duk Lardunan da ke cikin yankin Piedmont daga menu mai saukewa.

Ercole Palmeri