ʻO kēia ʻāpana o ka ʻōlelo aʻoaʻo kikoʻī pehea e hana ai i kahi PivotTable ma Excel. Ua kākau ʻia kēia ʻatikala no Excel 2007 (a me nā mana hope aʻe). Hiki ke loaʻa nā ʻōlelo kuhikuhi no nā mana mua o Excel ma kahi ʻatikala ʻokoʻa: Pehea e hana ai i kahi Pivottable ma Excel 2003?
No ka laʻana, e noʻonoʻo i ka papaʻaina ma lalo nei, aia ka ʻikepili kūʻai no kahi hui no ka hapaha mua o 2016:
A | B | C | D | E | |
---|---|---|---|---|---|
1 | lā | Invoice Ref | huina | Sales Rep. | Māhele |
2 | 01/01/2016 | 2016 - 0001 | $ 819 | Barnes | Ke akau |
3 | 01/01/2016 | 2016 - 0002 | $ 456 | Palaunu | Ka hema |
4 | 01/01/2016 | 2016 - 0003 | $ 538 | Jones | Ka hema |
5 | 01/01/2016 | 2016 - 0004 | $ 1,009 | Barnes | Ke akau |
6 | 01/02/2016 | 2016 - 0005 | $ 486 | Jones | Ka hema |
7 | 01/02/2016 | 2016 - 0006 | $ 948 | Smith | Ke akau |
8 | 01/02/2016 | 2016 - 0007 | $ 740 | Barnes | Ke akau |
9 | 01/03/2016 | 2016 - 0008 | $ 543 | Smith | Ke akau |
10 | 01/03/2016 | 2016 - 0009 | $ 820 | Palaunu | Ka hema |
11 | ... | ... | ... | ... | ... |
No ka hoʻomaka ʻana, e hana mākou i kahi papa pivot maʻalahi loa e hōʻike i ka huina kūʻai o kēlā me kēia mea kūʻai aku e like me ka papa ma luna. No ka hana ʻana i kēia, e hana i kēia:
- E koho i ke kelepona mai ka laula ʻikepili a i ʻole ka laulā holoʻokoʻa e hoʻohana ʻia ma ka papa pivot.e haʻalele aku: Inā koho ʻoe i hoʻokahi kelepona mai kahi pae ʻikepili, e ʻike koke ʻo Excel a koho i ka pae ʻikepili holoʻokoʻa no ka Pivot Table. I mea e koho pono ai ʻo Excel i kahi laulā, pono e hoʻokō ʻia kēia mau kūlana:
- Pono e loaʻa i kēlā me kēia kolamu i ka pae ʻikepili kona inoa ponoʻī;
- ʻAʻole pono ka ʻikepili i nā laina hakahaka.
- Ke kaomi ʻana i ke pihi papa hōʻuluʻulu (Pivot Table) ma ka ʻāpana Papa (Papa) papa hookomo (Hoʻokomo) Nā lipine papa kuhikuhi Excel.
- E hōʻike ʻia kahi pahu kamaʻilio ma ka pale. Hana i kahi Pivot Table (E hana i ka Pivot Table) e like me ka mea i hōʻike ʻia ma ke kiʻi ma lalo nei.E hōʻoia i ka laulā i koho ʻia e like me ka laulā o nā cell e pono e hoʻohana ʻia no ka hana ʻana i ka Pivottable. Maanei hiki iā ʻoe ke kuhikuhi i kahi e hoʻokomo ʻia ai ka papa pivot i hana ʻia. Hiki iā ʻoe ke koho i kahi pepa e hoʻokomo i kahi papa pivot ma luna, a i ʻole koho - I kahi pepa hou (Pepa hana hou). kaomi OK.
- E hōʻike ʻia kahi papa pivot hakahaka, a me kahi papa Nā kahua pākaukau pivot (Pivot Table Field List) me nā kahua ʻikepili lehulehu. E hoʻomaopopo ʻo ia nā poʻomanaʻo mai ka ʻikepili kumu.
- I nā paneli Nā kahua pākaukau pivot (Pivot Table Field List):
- kaualako a hoʻokuʻu Sales Rep. i ka wahi Nā Papa (Lepa Laina);
- kaualako a hoʻokuʻu huina в ʻO nā waiwai (Nā Waiwai);
- Nānā mākou: in ʻO nā waiwai (Nā Waiwai) pono he waiwai Kahua kahua Ka nui (Sum of Amount), а не Ka nui ma ke kahua Ka nui (Helu o ka huina).
Ma kēia laʻana, ke kolamu huina loaʻa nā waiwai helu, no laila ka wahi Σ Waiwai (Σ Values) e koho ʻia e ka paʻamau Kahua kahua Ka nui (Suum of Number). Inā ma kahi kolamu huina e loaʻa nā waiwai helu ʻole a i ʻole nā waiwai, a laila hiki ke koho ʻia ka papa pivot paʻamau Ka nui ma ke kahua Ka nui (Helu o ka huina). Inā hiki kēia, a laila hiki iā ʻoe ke hoʻololi i ka nui i ka nui penei:
- I ka Σ Waiwai (Σ Values) kaomi ma luna Ka nui ma ke kahua Ka nui (Ka helu o ka nui) a koho i ke koho Nā koho kahua waiwai (Ka Waihona Wahi Waiwai);
- Ma ka ʻaoʻao kiʻekiʻe hana (Summarise Values By) koho i kahi hana Mokau (Sum);
- kaomi i 'aneʻi OK.
E hoʻopiha ʻia ka Pivot Table me nā huina kūʻai no kēlā me kēia kanaka kūʻai aku, e like me ka hōʻike ʻana ma ke kiʻi ma luna.
Inā makemake ʻoe e hōʻike i nā puke kūʻai ma nā ʻāpana kālā, pono ʻoe e hōʻano i nā cell i loaʻa kēia mau waiwai. ʻO ke ala maʻalahi e hana ai i kēia, ʻo ia ke kuhikuhi i nā cell nona ka ʻano āu e makemake ai e hoʻopilikino a koho i ke ʻano Keʻāina (Kālā) ʻāpana Number (Helu) pā Home (Home) Nā lipine papa kuhikuhi Excel (e like me ka hōʻike ʻana ma lalo).
ʻO ka hopena, e like me kēia ka papa pivot:
- pivot table ma mua o ka hoʻonohonoho ʻana i ka ʻano helu
- papa kuhikuhi pivot ma hope o ka hoʻonohonoho ʻana i ke ʻano kālā
E ʻoluʻolu e pili ana ke ʻano kālā paʻamau i nā hoʻonohonoho ʻōnaehana.
Paipai ʻia nā PivotPables ma nā mana hou o Excel
Ma nā mana hou o Excel (Excel 2013 a i ʻole ma hope), ma ka hookomo (Insert) pihi aia Paipai ʻia nā papa pivot (Nā Papa Pivot Manaʻo ʻia). Ma muli o ka ʻikepili kumu i koho ʻia, hōʻike kēia hāmeʻa i nā ʻano papaʻaina pivot. Hiki ke ʻike ʻia nā hiʻohiʻona ma ka pūnaewele Microsoft Office.