Ib feem ntawm cov lus qhia no qhia txog yuav ua li cas los tsim PivotTable hauv Excel. Kab lus no tau sau rau Excel 2007 (nrog rau cov ntawv tom qab). Cov lus qhia rau yav dhau los versions ntawm Excel tuaj yeem pom nyob rau hauv ib tsab xov xwm cais: Yuav ua li cas los tsim PivotTable hauv Excel 2003?
Ua piv txwv, xav txog cov lus hauv qab no, uas muaj cov ntaub ntawv muag khoom rau lub tuam txhab rau thawj peb lub hlis twg ntawm 2016:
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Hnub | Invoice Ref | nyiaj | Muag Rep. | cheeb tsam |
| 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 | ... | ... | ... | ... | ... |
Yuav pib nrog, cia peb tsim ib lub rooj pivot yooj yim heev uas yuav qhia tag nrho kev muag khoom ntawm txhua tus neeg muag khoom raws li cov lus saum toj no. Txhawm rau ua qhov no, ua cov hauv qab no:
- Xaiv ib lub xovtooj ntawm cov ntaub ntawv ntau lossis tag nrho ntau yam los siv rau hauv lub rooj pivot.CEEV FAJ: Yog tias koj xaiv ib lub xov tooj ntawm cov ntaub ntawv ntau, Excel yuav pom thiab xaiv tag nrho cov ntaub ntawv ntau rau PivotTable. Txhawm rau kom Excel xaiv ntau yam kom raug, cov xwm txheej hauv qab no yuav tsum ua kom tau raws li:
- Txhua kab hauv cov ntaub ntawv yuav tsum muaj nws lub npe tshwj xeeb;
- Cov ntaub ntawv yuav tsum tsis muaj cov kab khoob.
- Nyem lub khawm cov lus qhia (Pivot Table) hauv seem ntxhuav (Tables) tab Insert (Ntxig) Excel ntawv qhia zaub mov ribbons.
- Ib lub dialog box yuav tshwm sim ntawm qhov screen. Tsim ib PivotTable (Tsim PivotTable) raws li qhia hauv daim duab hauv qab no.
Xyuas kom tseeb tias qhov kev xaiv ntau yog sib npaug ntawm cov cell uas yuav tsum tau siv los tsim PivotTable. Ntawm no koj tuaj yeem hais qhia qhov twg tsim cov lus pivot yuav tsum tau muab tso rau. Koj tuaj yeem xaiv ib daim ntawv uas twb muaj lawm los ntxig lub rooj pivot ntawm nws, lossis kev xaiv - Rau ib daim ntawv tshiab (Cov ntaub ntawv tshiab). nias OK. - Ib lub rooj pivot khoob yuav tshwm sim, nrog rau lub vaj huam sib luag Pivot rooj teb (Pivot Table Field List) nrog ntau cov ntaub ntawv teb. Nco ntsoov tias cov no yog cov headers los ntawm cov ntaub ntawv qub.
- Hauv panels Pivot rooj teb (Pivot Table Field List):
- luag thiab nco Muag Rep. mus rau thaj chaw Loj (Kaw Labels);
- luag thiab nco nyiaj в Cov nqi (Tus nqi);
- Peb kuaj: in Cov nqi (Values) yuav tsum yog tus nqi Tus nqi teb Tus nqi (Sum of Amount), ib Tus nqi los ntawm daim teb (Ntau tus nqi).
Hauv qhov piv txwv no, kab nyiaj muaj cov lej muaj nuj nqis, yog li thaj chaw Cov nqi (Σ Tus nqi) yuav raug xaiv los ntawm lub neej ntawd Tus nqi teb Tus nqi (Sum ntawm tus nqi). Yog nyob rau hauv ib kab nyiaj yuav muaj cov uas tsis yog tus lej lossis qhov tsis muaj, ces cov lus pivot qub tuaj yeem xaiv tau Tus nqi los ntawm daim teb (Ntau tus nqi). Yog tias qhov no tshwm sim, tom qab ntawd koj tuaj yeem hloov qhov kom muaj nuj nqis raws li hauv qab no:
- Nyob rau hauv lub Cov nqi (Σ Values) nyem rau ntawm Tus nqi los ntawm daim teb (Suav ntawm Tus Nqi) thiab xaiv qhov kev xaiv Tus nqi teb xaiv (Qhov Chaw Nqis Nqis);
- Hauv Advanced tab lag luam (Summarise Values By) xaiv ib qho kev ua haujlwm Sum (Sum);
- Nyem no OK.
Xyuas kom tseeb tias qhov kev xaiv ntau yog sib npaug ntawm cov cell uas yuav tsum tau siv los tsim PivotTable. Ntawm no koj tuaj yeem hais qhia qhov twg tsim cov lus pivot yuav tsum tau muab tso rau. Koj tuaj yeem xaiv ib daim ntawv uas twb muaj lawm los ntxig lub rooj pivot ntawm nws, lossis kev xaiv - Rau ib daim ntawv tshiab (Cov ntaub ntawv tshiab). nias OK.
PivotTable yuav suav nrog cov khoom muag tag nrho rau txhua tus neeg muag khoom, raws li pom hauv daim duab saum toj no.
Yog tias koj xav tso saib cov khoom muag hauv cov nyiaj tau los, koj yuav tsum tsim cov hlwb uas muaj cov txiaj ntsig no. Txoj kev yooj yim tshaj plaws los ua qhov no yog txhawm rau txheeb xyuas cov hlwb uas nws hom koj xav kho thiab xaiv hom ntawv Nyiaj txiag (Currency) ntu Number (Number) tab Tsev (Lub Tsev) Excel ntawv qhia zaub mov ribbons (raws li qhia hauv qab no).
Yog li ntawd, lub rooj pivot yuav zoo li no:
- pivot lub rooj ua ntej tus lej teeb tsa
- pivot rooj tom qab teeb tsa txiaj hom
Thov nco ntsoov tias lub neej ntawd txiaj hom nyob ntawm qhov system nqis.
Pom zoo PivotTables nyob rau hauv Tseeb Versions ntawm Excel
Nyob rau hauv tsis ntev los no versions ntawm Excel (Excel 2013 los yog tom qab), nyob rau hauv lub Insert (Insert) khawm tam sim no Pom zoo pivot rooj (Pivot Tables pom zoo). Raws li cov ntaub ntawv uas tau xaiv, qhov cuab yeej no qhia tau tias muaj peev xwm pivot lub rooj tawm tswv yim. Piv txwv tuaj yeem pom ntawm Microsoft Office lub vev xaib.