Pivot cov ntxhuav hauv Excel yog ntau hom kev qhia uas ua rau nws yooj yim mus extract cov ntaub ntawv los ntawm cov ntxhuav loj ntawm cov ntaub ntawv tsis siv cov qauv.
Pivot tables yog cov neeg tsis tshua muaj neeg siv nyob rau hauv qhov uas tau tsiv, los yog piv, cov lus teb ntawm cov ntaub ntawv los ntawm ib qho chaw mus rau lwm tus siv cab thiab tee peb saib cov ntaub ntawv sib txawv ntawm ntau txoj kev.
Txoj kev qhia no npog tsim thiab siv lub table piv rau cov ntaub ntawv sib txawv ntawm cov ntaub ntawv (siv cov ntaub ntawv no rau kev nyeem ntawv).
01 ntawm 06
Nkag mus rau hauv Pivot Table Data
Thawj kauj ruam hauv kev tsim cov pivot table yog sau cov ntaub ntawv rau hauv daim ntawv ua haujlwm .
Thaum ua li ntawd, khaws cov lus hauv qab no:
- Yam tsawg peb kab ntawm cov ntaub ntawv xav tau los tsim cov table pivot.
- Nws yog ib qho tseem ceeb kom nkag mus rau cov ntaub ntawv kom raug. Tsis raug teeb meem, vim yog cov ntaub ntawv nkag tsis raug, yog lub hauv paus ntawm ntau cov teeb meem ntsig txog kev tswj xyuas cov ntaub ntawv.
- Tsis txhob sau ntawv tsis muaj dab tsi los yog txhua kab ntawv thaum nkag mus rau hauv cov ntaub ntawv. Qhov no suav nrog TSIS tawm hauv kab sab nraud ntawm kem npe thiab thawj kab ntawm cov ntaub ntawv.
Sau cov ntaub ntawv rau hauv hlwb A1 rau D12 raws li pom hauv daim duab saum toj no.
02 ntawm 06
Tsim cov Pivot Table
- Highlight hlwb A2 rau D12.
- Nias rau ntawm daim ntxig ntawm daim ntawv sau.
Nias rau hauv qab xub nyob hauv qab ntawm Pivot Table khawm qhib qhov chaw sau npe. - Nias ntawm Pivot Table nyob rau hauv daim ntawv qhib qhib Pivot Table dialog box.
Los ntawm kev xaiv ua ntej cov ntaub ntawv A2 rau F12, cov lus / kab kab hauv cov lus sib tham yuav tsum tau sau rau peb. - Xaiv Cov Ntawv Xaus Haum Rau Qhov Chaw ntawm lub rooj sib piv.
Nias ntawm qhov chaw nyob hauv lub thawv ntawv. - Nias ntawm tes D16 nyob rau hauv daim ntawv ua haujlwm kom nkag mus rau tus xovtooj ntawm kev xa mus rau hauv kab qhov chaw.
Nyem OK.
Cov lus piv txwv yuav tsum tshwm sim nyob rau hauv daim ntawv ua hauj lwm nrog rau sab laug sab saum toj ntawm lub rooj pivot rau hauv D16.
Daim Pivot Table Field Field yuav tsum qhib rau sab xis ntawm lub qhov rais Excel.
Nyob rau sab saum toj ntawm Pivot Table Field Field panel yog lub npe cov npe (kem npe) ntawm peb cov ntaub ntawv lub rooj. Cov ntaub ntawv nyob hauv qab ntawm lub vaj huam sib luag tau txuas rau cov lus pivot.
03 ntawm 06
Muab cov ntaub ntawv ntxiv rau Pivot Table
Lus Cim: Rau kev pabcuam ntawm cov lus qhia no saib cov duab saum toj no.
Koj muaj ob txoj kev xaiv thaum nws los mus muab cov ntaub ntawv ntxiv rau Pivot Table:
- Tsav lub npe ntawm pawg kab lus ntawm Pivot Table Field Field thiab muab tso rau ntawm Pivot Table hauv daim ntawv ua haujlwm.
- Tsav lub npe rau hauv qab ntawm Pivot Table Field Field vaj huam sib luag thiab tso tseg rau hauv cov chaw qhia.
Cov chaw nyob hauv Pivot Table Field Field cov vaj huam sib luag tau txuas mus rau cov chaw sib piv ntawm lub rooj sib piv. Thaum uas koj ntxiv cov npe hauv cheeb tsam rau cov chaw, koj cov ntaub ntawv ntxiv rau lub table pivot.
Nyob ntawm seb cov teb twg tau muab tso rau hauv cov cheeb tsam cov ntaub ntawv, sib tw tau sib txawv tau.
Luag lub npe rau cov npe ntawm cov chaw no:
- Tag nrho Cov Muag Khoom mus rau Daim Ntawv Ceeb Toom Txog Kev Hais Qhia
- Thaj av rau cheeb tsam sab sau
- Kev muag khoom muag xa mus rau cov cheeb tsam Liab Lab
- Kev txiav txim rau qhov tseem ceeb cheeb tsam
- Thaum ua tiav lawm, Cov Pivot Table yuav tsum muaj cov ntaub ntawv muab tso ua ke nrog tib yam li tus qauv hauv kauj ruam 1 ntawm phau ntawv no.
04 ntawm 06
Filtering Pivot Table Data
Cov Pivot Table muaj cov cuab yeej siv hluav taws xob uas siv tau rau kev nplua cov ntsiab lus ntawm Pivot Table.
Cov ntaub ntawv teev xeeb yog siv cov kev txwv kom txwv cov ntaub ntawv teev tawm los ntawm Pivot Table.
- Nias ntawm qhov xub hauv qab ntawm cheeb tsam uas hais hauv Pivot Table qhib lub lim daim ntawv teev npe.
- Nias ntawm lub checkbox ntawm qhov Kev Xaiv Tag Nrho xaiv kom tshem tus cim ntawm tag nrho cov thawv ntawm daim ntawv no.
- Nias rau ntawm lub checkboxes ntawm sab hnub tuaj thiab North kev xaiv ntxiv rau cov cim rau cov npov no.
- Nyem OK.
- Cov Pivot Table yuav tsum qhia tam sim no tsuas yog qhov kev txiav txim tag nrho rau cov muag reps uas ua haujlwm nyob rau sab hnub tuaj thiab North nruab nrab.
05 ntawm 06
Hloov cov Ntaub Ntawv Pivot Table
Txhawm rau hloov cov ntsiab lus tau qhia los ntawm Pivot Table:
- Rearrange lub rooj pivot los ntawm kev rub cov ntaub ntawv cov lus teb los ntawm ib cheeb tsam ntawm cov ntaub ntawv mus rau lwm qhov chaw hauv Pivot Table Field List vaj huam sib luag.
- Ua raws li cov ntaub ntawv xav tau.
Luag lub npe rau cov npe ntawm cov chaw no:
- Txiav txim rau qhov Chaw Fai Sau
- Kev muag khoom Rep mus rau thaj tsam sab sau
- Thaj av rau cheeb tsam Cov Kab Tsiaj
- Tag nrho Cov Muag Khoom rau Qhov Chaw Tshawb Fawm
- Nias ntawm qhov xub hauv qab ntawm cheeb tsam uas hais hauv Pivot Table qhib lub lim daim ntawv teev npe.
- Nias ob zaug hauv lub thawv tso ntawm ib sab ntawm Xaiv Txhua qhov xaiv rau thawj zaug thiab tshem tawm cov cim ntawm txhua lub thawv rau ntawm daim ntawv teev npe no.
- Nias rau ntawm lub checkbox ntawm sab hnub poob mus ntxiv rau lub cim kos rau lub thawv no.
- Nyem OK.
- Cov Pivot Table yuav tsum tam sim no qhia tag nrho cov kev muag khoom tsuas yog cov muag reps uas ua haujlwm nyob rau thaj tsam West.
06 ntawm 06
Pivot Table Piv txwv
Ntawm no yog ib qho piv txwv ntawm qhov koj cov lus piv yuav zoo li cas.