数据透视表教程

©2003AmericanExpressTravelRelatedServicesCompany,Inc.Allrightsreserved.Thisisconfidential,proprietaryandtradesecretinformationofAmericanExpressTravelRelatedServicesCompany,Inc.29January20201PivotTable-TrainingGESJ/APA-DataMgt&ValueCreation©2003AmericanExpressTravelRelatedServicesCompany,Inc.Allrightsreserved.Thisisconfidential,proprietaryandtradesecretinformationofAmericanExpressTravelRelatedServicesCompany,Inc.29January20202PivotTable-TrainingPivotTableReports101Let'ssupposeyou'vecompiledalargelistofdata-forexample,salesfiguresforeveryproductyourcompanymakes.Butnowyou'rereadytodistillsomemeaningfulinformationfromthedata.Forexample,youmightwanttoanswerthefollowingquestions:·Whatisthetotalsalesforeachproductbyregion?·Whichproductsaresellingbestovertime?·Whoisyourhighest-performingsalesperson?Fortheseandotherquestions,youcancreateaPivotTable®report.PivotTabledefinition:Aninteractivetablethatautomaticallyextracts,organizes,andsummarizesyourdata.Youcanthenusethereporttoanalyzethedata-forexample,•Makecomparisons•Detectpatternsandrelationships•Analyzetrends©2003AmericanExpressTravelRelatedServicesCompany,Inc.Allrightsreserved.Thisisconfidential,proprietaryandtradesecretinformationofAmericanExpressTravelRelatedServicesCompany,Inc.29January20203PivotTable-Training©2003AmericanExpressTravelRelatedServicesCompany,Inc.Allrightsreserved.Thisisconfidential,proprietaryandtradesecretinformationofAmericanExpressTravelRelatedServicesCompany,Inc.29January20204PivotTable-TrainingAnatomyofaPivotTableReportYoumayhavenoticedthatExcelusesspecifictermstoidentifythepartsofaPivotTablereport.Ifyou'renotfamiliarwiththeseterms,readthissectionforaquickprimer.RowfieldAfieldfromthesourcedatathatyouassigntoaroworientationinaPivotTablereport.Forexample,RegionandSalespersonarerowfields.ColumnfieldAfieldfromthesourcedatathatyouassigntoacolumnorientationinaPivotTablereport.Forexample,Typeisacolumnfield.PagefieldAfieldfromthesourcedatathatyouassigntoapage(orfilter)orientationinaPivotTablereport.Forexample,Yearisapagefield.YoucanusetheYearfieldtodisplaysummarizeddataforonly1997,1998,andsoon.ItemAsubcategoryofarow,column,orpagefield.Forexample,theTypefieldcontainsthefollowingitems:Beverages,Dairy,Meat,andProduce;theSalespersonfieldcontainsthefollowingitems:Buchanan,Davolio,Dodsworth,andSuyama.DatafieldAfieldfromthesourcedatathatcontainsdatatobesummarized.Forexample,SumofSalesisadatafield.Note:Youcanchoosehowtosummarizedata(forexample,bysum,average,orcount).Adatafieldusuallysummarizesnumericdata,butitcanalsosummarizetext.Forexample,youcancountthenumberoftimesaspecifictextentry(suchasYesorNo)appearsinafield.DataareaThecellsinaPivotTablereportthatcontainsummarydata.Forexample,thevalueincellC5summarizesBuchanan'sbeveragesalesfortheEastregionin1997.Inotherwords,it'sasummaryofthesalesfiguresforeveryrowinthesourcedatathatcontainstheitemsBuchanan,Beverage,East,and1997.©2003AmericanExpressTravelRelatedServicesCompany,Inc.Allrightsreserved.Thisisconfidential,proprietaryandtradesecretinformationofAmericanExpressTravelRelatedServicesCompany,Inc.29January20205CreateaPivotTableReport•TocreateaPivotTablereport,youfirstusethePivotTableandPivotChartWizardtospecifythedatayouwanttouseandtocreatethereportframework.•YoucanthenusethePivotTabletoolbartoarrangethedatawithinthatframework.•Forexample,youcanchoosethecategoriesofdatatoincludeandthecorrespondingdatavaluestosummarize©2003AmericanExpressTravelRelatedServicesCompany,Inc.Allrightsreserved.Thisisconfidential,proprietaryandtradesecretinformationofAmericanExpressTravelRelatedServicesCompany,Inc.29January20206PivotTable-TrainingSummarizeandAnalyzeYourData•Toseethebigpictureofyourdata,youcanuseaPivotTablereporttosummarizeandanalyzethedata.•YoucancontrolhowMicrosoft®Excel2000summarizesthedata-forexample,bysum,average,orcount-withoutenteringasingleformula.AddorRemoveData•NotquiteenoughinformationinyourPivotTablereport?•Forexample,maybeyouwanttoincludesalesfiguresbrokendownbysalesperson.Youcaneasilyaddorremovecategoriesofdata.©2003AmericanExpressTravelRelatedServicesCompany,Inc.Allrightsreserved.Thisisconfidential,proprietaryandtradesecretinformationofAmericanExpressTravelRelatedServicesCompany,Inc.29January20207PivotTable-TrainingQuicklyRearrangetheLayout•NotsatisfiedwiththelayoutofyourPivotTablereport?•Forexample,maybeyou'dprefertodisplaythesalespersoninformationinrowsinsteadofincolumns.•TheinteractivenatureofyourPivotTablereportletsyouliterallyturnthetable:youcaneasilymove(orpivot)therowsandcolumnstoviewdifferentsummariesofthedata.©2003AmericanExpressTravelRelatedServicesCompany,Inc.Allrightsreserved.Thisisconfidential,proprietaryandtradesecretinformationofAmericanExpressTravelRelatedServicesCompany,Inc.29January20208PivotTable-TrainingViewaSubsetoftheData•Wanttofilterthedatasoyoucanviewamanageablechunkatatime?•Todothis,youcanuseapagefieldtobreakthePivotTablereportintoseparatepages.•Eachpagecontainsasubsetofthedatathat'ssummarizedinthePivotTablereport.©2003AmericanExpressTravelRelatedServicesCompany,Inc.Allrightsrese