China Excel TrainingLesson 5 Pivot Tables1.docx

China Excel TrainingLesson 5 Pivot Tables1.docx

《China Excel TrainingLesson 5 Pivot Tables1.docx》由会员分享，可在线阅读，更多相关《China Excel TrainingLesson 5 Pivot Tables1.docx（27页珍藏版）》请在冰豆网上搜索。

ChinaExcelTrainingLesson5PivotTables1

©2007byCustomGuide,Inc.1502NicolletAvenueSouth,Suite1;Minneapolis,MN55403

ThismaterialiscopyrightedandallrightsarereservedbyCustomGuide,Inc.Nopartofthispublicationmaybereproduced,transmitted,transcribed,storedinaretrievalsystem,ortranslatedintoanylanguageorcomputerlanguage,inanyformorbyanymeans,electronic,mechanical,magnetic,optical,chemical,manual,orotherwise,withoutthepriorwrittenpermissionofCustomGuide,Inc.

Wemakeasincereefforttoensuretheaccuracyofthematerialdescribedherein;however,CustomGuidemakesnowarranty,expressedorimplied,withrespecttothequality,correctness,reliability,accuracy,orfreedomfromerrorofthisdocumentortheproductsitdescribes.Datausedinexamplesandsampledatafilesareintendedtobefictional.Anyresemblancetorealpersonsorcompaniesisentirelycoincidental.

Thenamesofsoftwareproductsreferredtointhismanualareclaimedastrademarksoftheirrespectivecompanies.CustomGuideisaregisteredtrademarkofCustomGuide,Inc.

TableofContents

TheFundamentals11

StartingExcel200712

What’sNewinExcel200713

UnderstandingtheExcelProgramScreen14

UnderstandingtheRibbon15

UsingtheOfficeButtonandQuickAccessToolbar16

UsingKeyboardCommands17

UsingContextualMenusandtheMiniToolbar18

UsingHelp19

ExitingExcel200721

WorksheetBasics22

CreatingaNewWorkbook23

OpeningaWorkbook24

NavigatingaWorksheet25

EnteringLabels26

EnteringValues27

SelectingaCellRange28

OverviewofFormulasandUsingAutoSum29

EnteringFormulas30

UsingAutoFill32

UnderstandingAbsoluteandRelativeCellReferences33

UsingUndoandRedo34

SavingaWorkbook35

PreviewingandPrintingaWorksheet37

ClosingaWorkbook38

EditingaWorksheet39

EditingCellContents40

Cutting,Copying,andPastingCells41

MovingandCopyingCellsUsingtheMouse43

UsingtheOfficeClipboard44

UsingthePasteSpecialCommand45

CheckingYourSpelling46

InsertingCells,Rows,andColumns48

DeletingCells,Rows,andColumns49

UsingFindandReplace50

UsingCellComments52

UsingtheResearchTaskPane54

TrackingChanges56

FormattingaWorksheet58

FormattingLabels59

FormattingValues60

AdjustingRowHeightandColumnWidth61

WorkingwithCellAlignment62

AddingCellBorders,BackgroundColorsandPatterns63

UsingtheFormatPainter65

UsingCellStyles66

UsingDocumentThemes68

ApplyingConditionalFormatting70

CreatingandManagingConditionalFormattingRules72

FindingandReplacingFormatting74

CreatingandWorkingwithCharts76

CreatingaChart77

ResizingandMovingaChart79

ChangingChartType80

ApplyingBuilt-inChartLayoutsandStyles81

WorkingwithChartLabels82

WorkingwithChartAxes84

WorkingwithChartBackgrounds85

WorkingwithChartAnalysisCommands86

FormattingChartElements87

ChangingaChart’sSourceData89

UsingChartTemplates90

ManagingWorkbooks92

ViewingaWorkbook93

WorkingwiththeWorkbookWindow95

SplittingandFreezingaWorkbookWindow97

SelectingWorksheetsinaWorkbook99

InsertingandDeletingWorksheets101

Renaming,MovingandCopyingWorksheets102

WorkingwithMultipleWorkbooks104

HidingRows,Columns,WorksheetsandWindows106

ProtectingaWorkbook108

ProtectingWorksheetsandWorksheetElements110

SharingaWorkbook112

CreatingaTemplate114

WorkingwithPageLayoutandPrinting116

CreatingHeadersandFooters117

UsingPageBreaks119

AdjustingMarginsandOrientation121

AdjustingSizeandScale122

AddingPrintTitles,GridlinesandHeadings123

AdvancedPrintingOptions124

MoreFunctionsandFormulas127

FormulaswithMultipleOperators128

InsertingandEditingaFunction129

AutoCalculateandManualCalculation131

DefiningNames133

UsingandManagingDefinedNames135

DisplayingandTracingFormulas137

UnderstandingFormulaErrors139

WorkingwithDataRanges142

SortingbyOneColumn143

SortingbyColorsorIcons145

SortingbyMultipleColumns147

SortingbyaCustomList148

FilteringData150

CreatingaCustomAutoFilter151

UsinganAdvancedFilter152

WorkingwithTables154

CreatingaTable155

WorkingwithTableSize157

WorkingwiththeTotalRow159

WorkingwithTableData161

SummarizingaTablewithaPivotTable163

UsingtheDataForm164

UsingTableStyles165

UsingTableStyleOptions166

CreatingandDeletingCustomTableStyles167

ConvertorDeleteaTable169

WorkingwithPivotTables170

CreatingaPivotTable171

SpecifyingPivotTableData172

ChangingaPivotTable’sCalculation173

FilteringandSortingaPivotTable174

WorkingwithPivotTableLayout175

GroupingPivotTableItems177

UpdatingaPivotTable179

FormattingaPivotTable180

CreatingaPivotChart181

AnalyzingandOrganizingData182

CreatingScenarios183

CreatingaScenarioReport185

WorkingwithDataTables186

UsingGoalSeek188

UsingSolver189

UsingDataValidation191

UsingTexttoColumns193

RemovingDuplicates195

GroupingandOutliningData196

UsingSubtotals198

ConsolidatingDatabyPositionorCategory200

ConsolidatingDataUsingFormulas202

WorkingwiththeWebandExternalData203

InsertingaHyperlink204

CreatingaWebPagefromaWorkbook206

ImportingDatafromAccessorTextFiles207

ImportingDatafromtheWebandOtherSources209

WorkingwithExistingDataConnections211

WorkingwithMacros213

RecordingaMacro214

PlayingandDeletingaMacro216

AddingaMacrototheQuickAccessToolbar217

EditingaMacro’sVisualBasicCode218

InsertingCodeinaMacro219

WorkingwithObjects221

InsertingClipArt222

InsertingPicturesandGraphicsFiles223

FormattingPicturesandGraphics224

InsertingShapes226

FormattingShapes228

Resize,Move,CopyandDeleteObjects230

ApplyingSpecialEffectstoObjects231

GroupingObjects232

AligningObjects233

FlippingandRotatingObjects234

LayeringObjects235

InsertingSmartArt236

WorkingwithSmartArtElements237

FormattingSmartArt239

UsingWordArt241

InsertinganEmbeddedObject242

InsertingSymbols243

AdvancedTopics244

CustomizingtheQuickAccessToolbar245

UsingandCustomizingAutoCorrect247

ChangingExcel’sDefaultOptions249

RecoveringYourDocuments250

UsingMicrosoftOfficeDiagnostics252

ViewingDocumentPropertiesandFindingaFile253

SavingaDocumentasPDForXPS254

AddingaDigitalSignaturetoaWorkbook256

PreparingDocumentsforPublishingandDistribution257

PublishingaWorkbooktoaDocumentWorkspace258

AppendixofCommonFunctions259

FinancialFunctions260

Date&TimeFunctions262

Math&TrigFunctions264

StatisticalFunctions266

Lookup&ReferenceFunctions267

DatabaseFunctions268

TextFunctions269

LogicalFunctions270

MicrosoftOfficeExcel2007Review271

WhatPeopleAreSaying

“IhavesavedhundredsofhoursofdesigntimebyjustpickingandchoosingwhatIwantfromthecourseware.”

—StephanieZimmerman

LancasterCountyLibrary

“WehavebeenabletocustomizeourtrainingsessionsonallMicrosoftOfficeproducts,atalllevels.TheROIoftheseguidesisgreat.”

—DawnCalvin

LasVirgenesMunicipalWaterDistrict

“Allinall,thefriendliest,mostopenandeasytounderstandtutorialofitstypethatI’veeverseen.”

—W.Boudville

A

“…curriculumthatisofhighquality,studentfriendly,andadaptabletotheaudience.”

—SherrillWayland

St.CharlesCommunityCollege

“…anicetrainingoptionforalmostanyneed.TheircompleteMicrosoftOfficepackageisbyfarthebestdealonthemarket.”

—TechnicalAssistanceProgram

PurdueUniversity

“AnyinstructorteachingclassesonWindowsorMicrosoftOfficewilldefinitelywanttogiveseriousconsiderationtothisimportantcollectionoftitlesthatwilldefinitelyfitwellintotheirclassroomlearning.”

—DaleFarris

GoldenTrianglePCClub

“Thematerialsareexceptional–Iamsoexcitedaboutusingthem!

Thankstoyouandyourteamfordoingthiswonderfulwork!

”

—ShannonColeman

LearningPostLtd.

IntroducingCustomGuideCourseware

ThankyouforchoosingCustomGuidecoursewareasthesolutiontoyourtrainingneeds.Aprovenleaderinthecomputertrainingindustry,CustomGuidehasbeenthekeytosuccessfultrainingforthousandsofstudentsandinstructorsacrosstheglobe.

Thismanualisdesignedforcomputerusersofallexperiencelevels.Noviceuserscanuseittolearnskillssuchascreatingformulas,whileadvanceduserscanuseittocreateandformatcharts.

Allthisinformationisquicklyaccessible.Lessonsarebrokendownintobasicstep-by-stepinstructionsthatanswer“how-to”questionsinminutes.Youcanprintacomplete300-pagetrainingmanualorasinglepageofinstructions.

Here’showaCustomGuidemanualisorganized:

Chapters

Eachmanualisdividedintoseveralchapters.Aren'tsureifyou'rereadyforachapter?

Lookatthetableofcontentsthatappearsatthebeginningofeachchapter.Itwilltellyouthenameofeachlessonandsubtopicincludedinthechapter.

Lessons

Eachchaptercontainslessonsonrelatedtopics.Eachlessonexplainsanewskillortopicandcontainsanexerciseandexercisefiletogiveyouhands-on-experience.TheseskillscanalsobepracticedusingCustomGuideOnlineLearning.

Review

Areviewisincludedattheendofthemanual.Usethesequizquestionsandanswerstoassesshowmuchyou'velearned.

HowItWorks

Streamlineddesign

Featuringaprofessional-looking,easy-to-readdesign,3rdgenerationcoursewareappealstoinstructors,studentsandindividualusersalike.

ExerciseNotes

AnewExerciseNotessectionappearsatthetopofeachles