Wannan bangare na koyawa yayi cikakken bayanin yadda ake ƙirƙirar PivotTable a cikin Excel. An rubuta wannan labarin don Excel 2007 (da kuma sigar baya). Ana iya samun umarni don sigogin Excel na farko a cikin wani labarin dabam: Yadda ake ƙirƙirar PivotTable a cikin Excel 2003?
A matsayin misali, la'akari da tebur mai zuwa, wanda ya ƙunshi bayanan tallace-tallace na kamfani don kwata na farko na 2016:
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Rana | Invoice Ref | Adadin | Wakilin tallace-tallace | Region |
| 2 | 01/01/2016 | 2016 - 0001 | $ 819 | Barnes | North |
| 3 | 01/01/2016 | 2016 - 0002 | $ 456 | Brown | South |
| 4 | 01/01/2016 | 2016 - 0003 | $ 538 | Jones | South |
| 5 | 01/01/2016 | 2016 - 0004 | $ 1,009 | Barnes | North |
| 6 | 01/02/2016 | 2016 - 0005 | $ 486 | Jones | South |
| 7 | 01/02/2016 | 2016 - 0006 | $ 948 | Smith | North |
| 8 | 01/02/2016 | 2016 - 0007 | $ 740 | Barnes | North |
| 9 | 01/03/2016 | 2016 - 0008 | $ 543 | Smith | North |
| 10 | 01/03/2016 | 2016 - 0009 | $ 820 | Brown | South |
| 11 | ... | ... | ... | ... | ... |
Da farko, bari mu ƙirƙiri tebur mai sauƙi mai sauƙi wanda zai nuna jimillar tallace-tallace na kowane mai siyarwa bisa ga teburin da ke sama. Don yin wannan, yi haka:
- Zaɓi kowane tantanin halitta daga kewayon bayanai ko gabaɗayan kewayon da za a yi amfani da su a cikin tebur pivot.Tsanaki: Idan ka zaɓi tantanin halitta ɗaya daga kewayon bayanai, Excel zai gano ta atomatik kuma ya zaɓi kewayon kewayon bayanai na PivotTable. Domin Excel ya zaɓi kewayon daidai, dole ne a cika waɗannan sharuɗɗan:
- Kowane shafi a cikin kewayon bayanai dole ne ya sami sunansa na musamman;
- Dole ne bayanan su ƙunshi layukan wofi.
- Danna maɓallin teburin taƙaitaccen bayani (Pivot Table) a cikin sashe Tables (Tables) tab Saka (Saka) Ribbons menu na Excel.
- Akwatin maganganu zai bayyana akan allon. Ƙirƙiri PivotTable (Ƙirƙiri PivotTable) kamar yadda aka nuna a hoton da ke ƙasa.
Tabbatar cewa kewayon da aka zaɓa yayi daidai da kewayon sel waɗanda yakamata a yi amfani dasu don ƙirƙirar PivotTable. Anan kuma zaku iya tantance inda yakamata a saka teburin pivot da aka ƙirƙira. Kuna iya zaɓar takardar da ke akwai don saka tebur pivot akansa, ko zaɓi - Zuwa sabon takarda (Sabon takardar aiki). danna OK. - Teburin pivot na fanko zai bayyana, da kuma panel Filayen teburi (Jerin Filin Fayil na Pivot) tare da filayen bayanai da yawa. Yi la'akari da cewa waɗannan su ne masu kai daga ainihin bayanan bayanan.
- A cikin bangarori Filayen teburi (Jerin Filin Teburin Pivot):
- ja da sauke Wakilin tallace-tallace zuwa yankin Rows (Lambobin Layi);
- ja da sauke Adadin в Da dabi'u (Dabi'u);
- Muna duba: in Da dabi'u (Dabi'u) dole ne ya zama ƙima Adadin filin adadin (Jimillar Adadi), а не Adadi ta filin Adadin (Kidaya adadin).
A cikin wannan misali, shafi Adadin ya ƙunshi ƙimar lambobi, don haka yankin Σ Darajoji (Σ Ƙimar) za a zaɓi ta tsohuwa Adadin filin adadin (Jimillar Adadi). Idan a cikin shafi Adadin zai ƙunshi ƙima marasa ƙima ko fanko, sannan za'a iya zaɓar tebirin pivot na asali Adadi ta filin Adadin (Kidaya adadin). Idan hakan ta faru, to zaku iya canza adadin zuwa adadin kamar haka:
- a cikin Σ Darajoji (Σ Values) danna kan Adadi ta filin Adadin (Kidaya Adadin) kuma zaɓi zaɓi Zaɓuɓɓukan filin darajar (Saitunan Filin Ƙimar);
- A kan Babba shafin Operation (Taƙaita Ƙimar Ta) zaɓi aiki Sum (Sum);
- Latsa nan OK.
Tabbatar cewa kewayon da aka zaɓa yayi daidai da kewayon sel waɗanda yakamata a yi amfani dasu don ƙirƙirar PivotTable. Anan kuma zaku iya tantance inda yakamata a saka teburin pivot da aka ƙirƙira. Kuna iya zaɓar takardar da ke akwai don saka tebur pivot akansa, ko zaɓi - Zuwa sabon takarda (Sabon takardar aiki). danna OK.
Za a cika PivotTable tare da jimlar tallace-tallace na kowane mai siyarwa, kamar yadda aka nuna a hoton da ke sama.
Idan kuna son nuna adadin tallace-tallace a cikin raka'a na kuɗi, dole ne ku tsara sel ɗin da ke ɗauke da waɗannan ƙimar. Hanya mafi sauƙi don yin wannan ita ce ta haskaka sel waɗanda kuke son tsara tsarin su kuma zaɓi tsarin Monetary (Kudi) sashe Number (Lambar) tab Gida (Gida) Ribbons menu na Excel (kamar yadda aka nuna a ƙasa).
A sakamakon haka, tebur pivot zai yi kama da haka:
- tebur pivot kafin saitin tsarin lamba
- tebur pivot bayan saita tsarin kuɗi
Lura cewa tsarin kuɗi na asali ya dogara da saitunan tsarin.
Shawarwari PivotTables a cikin Sabbin Sabbin Na'urorin Excel
A cikin 'yan kwanan nan na Excel (Excel 2013 ko kuma daga baya), akan Saka (Saka) maɓallin yanzu Shawarar tebur pivot (Shawarwari na Pivot Tables). Dangane da bayanan tushen da aka zaɓa, wannan kayan aikin yana nuna yiwuwar tsarin tebur pivot. Ana iya duba misalai akan gidan yanar gizon Microsoft Office.