數(shù)據(jù)透視表教程

PivotTable-TrainingGESJ/APA-DataMgt&ValueCreation12/6/20231PivotTable-TrainingPivotTableReports101Let'ssupposeyou'vecompiledalargelistofdata-forexample,salesfiguresforeveryproductyourcompanymakes.Butnowyou'rereadytodistillsomemeaningfulinformationfromthedata.Forexample,youmightwanttoanswerthefollowingquestions:·Whatisthetotalsalesforeachproductbyregion?·Whichproductsaresellingbestovertime?·Whoisyourhighest-performingsalesperson?Fortheseandotherquestions,youcancreateaPivotTable?report.PivotTabledefinition:Aninteractivetablethatautomaticallyextracts,organizes,andsummarizesyourdata.Youcanthenusethereporttoanalyzethedata-forexample,MakecomparisonsDetectpatternsandrelationshipsAnalyzetrends12/6/20232PivotTable-Training12/6/20233PivotTable-TrainingAnatomyofaPivotTableReportYoumayhavenoticedthatExcelusesspecifictermstoidentifythepartsofaPivotTablereport.Ifyou'renotfamiliarwiththeseterms,readthissectionforaquickprimer.

Rowfield

AfieldfromthesourcedatathatyouassigntoaroworientationinaPivotTablereport.Forexample,RegionandSalespersonarerowfields.Columnfield

AfieldfromthesourcedatathatyouassigntoacolumnorientationinaPivotTablereport.Forexample,Typeisacolumnfield.Pagefield

Afieldfromthesourcedatathatyouassigntoapage(orfilter)orientationinaPivotTablereport.Forexample,Yearisapagefield.YoucanusetheYearfieldtodisplaysummarizeddataforonly1997,1998,andsoon.

Item

Asubcategoryofarow,column,orpagefield.Forexample,theTypefieldcontainsthefollowingitems:Beverages,Dairy,Meat,andProduce;theSalespersonfieldcontainsthefollowingitems:Buchanan,Davolio,Dodsworth,andSuyama.Datafield

Afieldfromthesourcedatathatcontainsdatatobesummarized.Forexample,SumofSalesisadatafield.Note:

Youcanchoosehowtosummarizedata(forexample,bysum,average,orcount).Adatafieldusuallysummarizesnumericdata,butitcanalsosummarizetext.Forexample,youcancountthenumberoftimesaspecifictextentry(suchasYesorNo)appearsinafield.Dataarea

ThecellsinaPivotTablereportthatcontainsummarydata.Forexample,thevalueincellC5summarizesBuchanan'sbeveragesalesfortheEastregionin1997.Inotherwords,it'sasummaryofthesalesfiguresforeveryrowinthesourcedatathatcontainstheitemsBuchanan,Beverage,East,and1997.

12/6/20234CreateaPivotTableReportTocreateaPivotTablereport,youfirstusethePivotTableandPivotChartWizardtospecifythedatayouwanttouseandtocreatethereportframework.YoucanthenusethePivotTabletoolbartoarrangethedatawithinthatframework.

Forexample,youcanchoosethecategoriesofdatatoincludeandthecorrespondingdatavaluestosummarize12/6/20235PivotTable-TrainingSummarizeandAnalyzeYourDataToseethe"bigpicture"ofyourdata,youcanuseaPivotTablereporttosummarizeandanalyzethedata.YoucancontrolhowMicrosoft?Excel2000summarizesthedata-forexample,bysum,average,orcount-withoutenteringasingleformula.AddorRemoveDataNotquiteenoughinformationinyourPivotTablereport?Forexample,maybeyouwanttoincludesalesfiguresbrokendownbysalesperson.Youcaneasilyaddorremovecategoriesofdata.12/6/20236PivotTable-TrainingQuicklyRearrangetheLayoutNotsatisfiedwiththelayoutofyourPivotTablereport?Forexample,maybeyou'dprefertodisplaythesalespersoninformationinrowsinsteadofincolumns.TheinteractivenatureofyourPivotTablereportletsyouliterallyturnthetable:youcaneasilymove(or"pivot")therowsandcolumnstoviewdifferentsummariesofthedata.12/6/20237PivotTable-TrainingViewaSubsetoftheDataWanttofilterthedatasoyoucanviewamanageablechunkatatime?Todothis,youcanuseapagefieldtobreakthePivotTablereportintoseparate"pages."Eachpagecontainsasubsetofthedatathat'ssummarizedinthePivotTablereport.12/6/20238PivotTable-TrainingArethereotherreasonstousepagefields?IfyourPivotTablereportcontainsmanyfields,youcanusepagefieldstokeepyourreportcompactandreadable.12/6/20239PivotTable-TrainingShowJusttheDetailsYouWantWanttozeroinonspecificdetailsinaPivotTablereport?Youcandisplayorhideitemsinaroworcolumn-forexample,specificproductsorsalespersons.Youcanalsodisplayorhidedetailsaboutitemsinroworcolumn.12/6/202310PivotTable-TrainingShowJusttheDetailsYouWantInaPivotTablereport,youcandrilldownonthedetailsorhideinformationfora"bigpicture"view.

Showorhideitemsinaroworcolumn

Clickthearrowbesideafield,andthenselectorclearthecheckboxesyouwant.

12/6/202311PivotTable-Training12/6/202312PivotTable-TrainingShoworhidespecificdetailsforanitem

Clickafielditem,andthenclicktheShowDetailorHideDetailbuttononthePivotTabletoolbar.Ifprompted,clickthefieldwiththedetailyouwanttoshow.12/6/202313PivotTable-TrainingDisplaytheunderlyingsourced