数据透视表教程

PivotTable-TrainingGESJ/APA-DataMgt&ValueCreation1/17/20261PivotTable-TrainingPivotTableReports101Let'ssupposeyou'vecompiledalargelistofdata-forexample,salesfiguresforeveryproductyourcompanymakes.Butnowyou'rereadytodistillsomemeaningfulinformationfromthedata.Forexample,youmightwanttoanswerthefollowingquestions:·Whatisthetotalsalesforeachproductbyregion?·Whichproductsaresellingbestovertime?·Whoisyourhighest-performingsalesperson?Fortheseandotherquestions,youcancreateaPivotTable®report.PivotTabledefinition:Aninteractivetablethatautomaticallyextracts,organizes,andsummarizesyourdata.Youcanthenusethereporttoanalyzethedata-forexample,MakecomparisonsDetectpatternsandrelationshipsAnalyzetrends1/17/20262PivotTable-Training1/17/20263PivotTable-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.

1/17/20264CreateaPivotTableReportTocreateaPivotTablereport,youfirstusethePivotTableandPivotChartWizardtospecifythedatayouwanttouseandtocreatethereportframework.YoucanthenusethePivotTabletoolbartoarrangethedatawithinthatframework.

Forexample,youcanchoosethecategoriesofdatatoincludeandthecorrespondingdatavaluestosummarize1/17/20265PivotTable-TrainingSummarizeandAnalyzeYourDataToseethe"bigpicture"ofyourdata,youcanuseaPivotTablereporttosummarizeandanalyzethedata.YoucancontrolhowMicrosoft®Excel2000summarizesthedata-forexample,bysum,average,orcount-withoutenteringasingleformula.AddorRemoveDataNotquiteenoughinformationinyourPivotTablereport?Forexample,maybeyouwanttoincludesalesfiguresbrokendownbysalesperson.Youcaneasilyaddorremovecategoriesofdata.1/17/20266PivotTable-TrainingQuicklyRearrangetheLayoutNotsatisfiedwiththelayoutofyourPivotTablereport?Forexample,maybeyou'dprefertodisplaythesalespersoninformationinrowsinsteadofincolumns.TheinteractivenatureofyourPivotTablereportletsyouliterallyturnthetable:youcaneasilymove(or"pivot")therowsandcolumnstoviewdifferentsummariesofthedata.1/17/20267PivotTable-TrainingViewaSubsetoftheDataWanttofilterthedatasoyoucanviewamanageablechunkatatime?Todothis,youcanuseapagefieldtobreakthePivotTablereportintoseparate"pages."Eachpagecontainsasubsetofthedatathat'ssummarizedinthePivotTablereport.1/17/20268PivotTable-TrainingArethereotherreasonstousepagefields?IfyourPivotTablereportcontainsmanyfields,youcanusepagefieldstokeepyourreportcompactandreadable.1/17/20269PivotTable-TrainingShowJusttheDetailsYouWantWanttozeroinonspecificdetailsinaPivotTablereport?Youcandisplayorhideitemsinaroworcolumn-forexample,specificproductsorsalespersons.Youcanalsodisplayorhidedetailsaboutitemsinroworcolumn.1/17/202610PivotTable-TrainingShowJusttheDetailsYouWantInaPivotTablereport,youcandrilldownonthedetailsorhideinformationfora"bigpicture"view.

Showorhideitemsinaroworcolumn

Clickthearrowbesideafield,andthenselectorclearthecheckboxesyouwant.

1/17/202611PivotTable-Training1/17/202612PivotTable-TrainingShoworhidespecificdetailsforanitem

Clickafielditem,andthenclicktheShowDetailorHideDetailbuttononthePivotTabletoolbar.Ifprompted,clickthefieldwiththedetailyouwanttoshow.1/17/202613PivotTable-TrainingDisplaytheunderlyingso