Koj tau txais cov ntaub ntawv thiab ntau ntawm nws. Thaum koj xav txheeb xyuas tag nrho cov ntaub ntawv ntawd, kawm paub siv Power Pivot ntxiv-hauv nrog Excel los import cov ntaub ntawv, txheeb xyuas kev sib raug zoo, tsim PivotTables, thiab tsim PivotCharts.
Cov lus qhia hauv kab lus no siv rau Excel 2019, 2016, 2013, thiab Excel rau Microsoft 365.
Yuav ua li cas kom tau txais Excel Power Pivot Ntxiv-hauv
Power Pivot muab rau koj lub zog ntawm kev paub txog kev lag luam thiab kev tshuaj xyuas app. Koj tsis tas yuav muaj kev cob qhia tshwj xeeb los tsim cov qauv ntaub ntawv thiab ua kev suav. Koj tsuas yog yuav tsum tau qhib nws ua ntej koj tuaj yeem siv nws.
- Open Excel.
-
Xaiv File> Options.
Image -
Xaiv Add-Ins.
Image -
Xaiv lub Manage cov ntawv qhia zaub mov, ces xaiv COM Ntxiv-ins.
Image -
Xaiv Go.
Image -
Xaiv Microsoft Power Pivot rau Excel.
Image - Xaiv OK. Lub Power Pivot tab ntxiv rau Excel.
Ua Raws Nrog Kev Qhia
Thaum koj xav sawv thiab khiav nrog Power Pivot sai, kawm ua piv txwv. Microsoft muaj ntau yam piv txwv datasets muaj raws li ib tug dawb download, uas muaj cov ntaub ntawv raw, cov ntaub ntawv qauv, thiab cov piv txwv ntawm cov ntaub ntawv tsom xam. Cov no yog cov cuab yeej kawm zoo uas muab kev nkag siab txog yuav ua li cas cov kws tshaj lij tsom xam cov ntaub ntawv loj.
Qhov kev qhia no siv Microsoft Cov Tub Ntxhais Kawm Cov Ntaub Ntawv Tus Qauv qauv ua haujlwm. Koj yuav pom qhov txuas rub tawm mus rau cov qauv ua haujlwm thiab rau cov qauv ntaub ntawv ua tiav hauv thawj daim ntawv ntawm nplooj ntawv.
Cov ntaub ntawv hauv phau ntawv ua haujlwm Excel no muaj cov hauv qab no:
- Phau ntawv ua haujlwm muaj plaub daim ntawv ua haujlwm.
- Txhua daim ntawv ua haujlwm muaj cov ntaub ntawv cuam tshuam, txhais tau tias muaj tsawg kawg ib kab lus ntawm ib daim ntawv ua haujlwm sib piv rau kab lus hauv lwm daim ntawv.
- Cov ntaub ntawv hauv txhua daim ntawv ua haujlwm yog formatted ua ib lub rooj.
- Txhua lub xovtooj ntawm lub rooj muaj cov ntaub ntawv. Tsis muaj cov hlwb, kab, lossis kab hauv cov ntxhuav.
Muaj lwm cov piv txwv datasets ntawm Microsoft lub vev xaib. Tshawb nrhiav cov kev kawm no:
- Download tau cov ntaub ntawv los ntawm Microsoft Access database uas piav qhia txog Olympic Medals.
- Download peb Cov Qauv Ua Lag Luam Kev Txawj Ntse uas qhia tias yuav siv Power Pivot li cas los import cov ntaub ntawv, tsim kev sib raug zoo, tsim PivotTables, thiab tsim PivotCharts.
Ua ntej siv cov ntaub ntawv, ntxuav nws. Siv Excel's CLEAN muaj nuj nqi txhawm rau tshem tawm cov cim tsis tuaj yeem luam tawm, khiav cov ntawv txheeb xyuas kev sau ntawv, tshem tawm cov kab sib npaug ntawm cov ntaub ntawv, hloov cov lej thiab hnub mus rau hom kom raug, thiab rov kho cov ntaub ntawv.
Yuav ua li cas ntxiv cov ntaub ntawv rau koj cov ntaub ntawv Excel thiab tsim cov qauv ntaub ntawv
Koj tau sau cov ntaub ntawv koj xav tau. Tam sim no nws yog lub sij hawm los import koj cov ntaub ntawv teev rau hauv Excel thiab cia li tsim cov ntaub ntawv qauv. Tus Qauv Cov Ntaub Ntawv zoo ib yam li cov ntaub ntawv sib txheeb thiab muab cov ntaub ntawv tabular siv hauv PivotTables thiab PivotCharts.
Yog tias koj xav tau cov ntaub ntawv rau lub tsev kawm ntawv txoj haujlwm, haujlwm ua haujlwm, lossis ua raws li cov lus qhia no, koj yuav pom cov ntaub ntawv zoo rau pej xeem ntawm GitHub.
Txhim kho cov ntaub ntawv Excel rau hauv Power Pivot Data Model:
- Qhib ib daim ntawv ua haujlwm dawb huv thiab khaws cov ntaub ntawv nrog lub npe tshwj xeeb.
-
Xaiv Data, ces xaiv Get Data > From File > Los ntawm Workbook qhib lub Ntshuam cov ntaub ntawv dialog box.
Image In Excel 2013, xaiv Power Query > Tau Cov Ntaub Ntawv Sab Nraud thiab xaiv koj cov ntaub ntawv qhov chaw.
-
Nav mus rau lub nplaub tshev uas muaj cov ntaub ntawv Excel, xaiv cov ntaub ntawv, tom qab ntawd xaiv Import txhawm rau qhib Navigator.
Image -
Xaiv lub npov rau Xaiv ntau yam khoom.
Image -
Xaiv cov ntxhuav koj xav import.
Thaum koj import ob lossis ntau lub rooj, Excel cia li tsim Cov Qauv Qauv.
Image -
Xaiv Load los import cov ntaub ntawv rau hauv cov ntaub ntawv qauv.
Image -
Txhawm rau kom paub tseeb tias kev ntshuam tau ua tiav thiab Cov Qauv Cov Ntaub Ntawv tau tsim, mus rau Data thiab, hauv pawg Data Tools, xaiv mus rau lub qhov rai fais fab Pivot.
Image -
Lub Hwj Chim Pivot Qhov rai qhia koj cov ntaub ntawv hauv daim ntawv ua haujlwm thiab muaj peb qhov tseem ceeb: Cov Ntaub Ntawv, Xam Xaj Thaj Tsam, thiab Cov Ntaub Ntawv Tabs.
Image - Cov tab nyob hauv qab ntawm Lub Hwj Chim Pivot Qhov rai sib haum rau txhua lub rooj uas tau raug xa tawm.
- Kaw lub Qhov rai Fais Fab Pivot.
Thaum koj xav ntxiv cov ntaub ntawv tshiab rau cov qauv ntaub ntawv, hauv Excel qhov rai, mus rau Power Pivot thiab xaiv Ntxiv rau Cov Qauv. Cov ntaub ntawv zoo li ib lub tab tshiab hauv Power Pivot Window.
Tsim Kev Sib Raug Zoo Ntawm Cov Rooj Nrog Lub Zog Pivot Excel
Tam sim no koj muaj Cov Qauv Qauv, nws yog lub sijhawm los tsim kev sib raug zoo ntawm txhua lub rooj ntaub ntawv.
-
Xaiv Power Pivot, tom qab ntawd xaiv Manage Data Model txhawm rau qhib lub Fais Fab Pivot Window.
Image -
Select Home, ces xaiv Daim duab saib.
Image -
Cov rooj tuaj txawv teb chaws tshwm li cov thawv cais hauv Daim duab saib. Luag kom txav cov ntxhuav mus rau lwm qhov chaw. Luag ib ces kaum ntawm ib lub thawv los hloov nws.
Image -
Txoj kab nqes ntawm ib lub rooj mus rau lwm lub rooj lossis cov rooj uas muaj tib kab lus.
Image -
Txuas ntxiv kom phim cov kab ntawv kab lus.
Image - Select Home, ces xaiv Data View.
Yuav Ua Li Cas Tsim PivotTables
Thaum koj siv Power Pivot los tsim Cov Qauv Cov Ntaub Ntawv, feem ntau ntawm kev ua haujlwm nyuaj nrog PivotTables thiab PivotCharts tau ua tiav rau koj. Cov kev sib raug zoo uas koj tsim ntawm cov ntxhuav hauv koj cov ntaub ntawv yog siv los ntxiv cov teb koj yuav siv los tsim PivotTables thiab PivotCharts.
-
Nyob rau hauv lub Fais Fab Pivot Qhov rai, xaiv Home, ces xaiv PivotTable.
Image -
In the Create PivotTable dialog box, xaiv New Worksheet, ces xaiv OK.
Image -
In the PivotTable Fields pane, xaiv cov teb ntxiv rau PivotTable. Hauv qhov piv txwv no, PivotTable yog tsim uas muaj cov tub ntxhais kawm lub npe thiab lawv qib nruab nrab.
Image -
Xaiv cov ntaub ntawv PivotTable, luag ib daim teb mus rau qhov Filters cheeb tsam. Hauv qhov piv txwv no, Chav Kawm Lub Npe yog ntxiv rau thaj tsam Limsyog li cov npe tuaj yeem lim tau los qhia cov tub ntxhais kawm qhov qhab nia nruab nrab rau ib chav kawm.
Image Txhawm rau hloov txoj kev suav siv los ntawm thaj chaw hauv thaj chaw muaj txiaj ntsig, xaiv lub thawv xa khoom ntawm ib sab ntawm lub npe teb thiab xaiv Tus nqi teb chaw. Hauv qhov piv txwv no, Cov Qib ntawm Qib tau hloov mus rau Qhov Nruab Nrab ntawm Qib.
- Tshaj koj cov ntaub ntawv. Kev sim nrog cov lim dej thiab txheeb cov ntaub ntawv siv cov kab ntawv hauv kab ntawv sau npe.
Hloov ib PivotTable rau hauv PivotChart
Yog tias koj xav pom koj cov ntaub ntawv PivotTable, tig PivotTable rau hauv PivotChart.
- Xaiv PivotTable, tom qab ntawd mus rau PivotTable Tools > Tshuaj ntsuam.
- Xaiv Pivot Chart qhib lub Insert Chart dialog box.
- Xaiv daim duab, ces xaiv OK.
Create PivotCharts
Yog tias koj xav tshuaj xyuas koj cov ntaub ntawv hauv hom kev pom, tsim PivotChart.
- Hauv lub hwj chim Pivot qhov rais, xaiv Home, tom qab ntawd xaiv tus PivotTable dropdown xub. Ib daim ntawv teev cov kev xaiv tshwm.
-
Select PivotChart.
Image -
Xaiv Daim Ntawv Qhia Tshiab thiab xaiv OK. PivotChart placeholder tshwm nyob rau ntawm daim ntawv tshiab.
Image -
Go to PivotChart cuab yeej > Ntsuam xyuas thiab xaiv Daim Ntawv Qhia los tso saib PivotChart Fields pane.
Image -
Drag teb ntxiv rau PivotChart. Hauv qhov piv txwv no, PivotChart yog tsim los qhia qhov nruab nrab qib rau cov chav kawm lim los ntawm semester.
Image - Tshaj koj cov ntaub ntawv. Kev sim nrog Filters thiab txheeb cov ntaub ntawv nrog cov kab hauv kab hauv kab hauv qab nqes xub.
