China Excel TrainingLesson 5 Pivot Tables1.docx

1、China Excel TrainingLesson 5 Pivot Tables1 2007 by CustomGuide, Inc. 1502 Nicollet Avenue South, Suite 1; Minneapolis, MN 55403This material is copyrighted and all rights are reserved by CustomGuide, Inc. No part of this publication may be reproduced, transmitted, transcribed, stored in a retrieval

2、system, or translated into any language or computer language, in any form or by any means, electronic, mechanical, magnetic, optical, chemical, manual, or otherwise, without the prior written permission of CustomGuide, Inc.We make a sincere effort to ensure the accuracy of the material described her

3、ein; however, CustomGuide makes no warranty, expressed or implied, with respect to the quality, correctness, reliability, accuracy, or freedom from error of this document or the products it describes. Data used in examples and sample data files are intended to be fictional. Any resemblance to real p

4、ersons or companies is entirely coincidental.The names of software products referred to in this manual are claimed as trademarks of their respective companies. CustomGuide is a registered trademark of CustomGuide, Inc.Table of Contents The Fundamentals 11Starting Excel 2007 12Whats New in Excel 2007

5、 13Understanding the Excel Program Screen 14Understanding the Ribbon 15Using the Office Button and Quick Access Toolbar 16Using Keyboard Commands 17Using Contextual Menus and the Mini Toolbar 18Using Help 19Exiting Excel 2007 21 Worksheet Basics 22Creating a New Workbook 23Opening a Workbook 24Navig

6、ating a Worksheet 25Entering Labels 26Entering Values 27Selecting a Cell Range 28Overview of Formulas and Using AutoSum 29Entering Formulas 30Using AutoFill 32Understanding Absolute and Relative Cell References 33Using Undo and Redo 34Saving a Workbook 35Previewing and Printing a Worksheet 37Closing

7、 a Workbook 38 Editing a Worksheet 39Editing Cell Contents 40Cutting, Copying, and Pasting Cells 41Moving and Copying Cells Using the Mouse 43Using the Office Clipboard 44Using the Paste Special Command 45Checking Your Spelling 46Inserting Cells, Rows, and Columns 48Deleting Cells, Rows, and Columns

8、 49Using Find and Replace 50Using Cell Comments 52Using the Research Task Pane 54Tracking Changes 56 Formatting a Worksheet 58Formatting Labels 59Formatting Values 60Adjusting Row Height and Column Width 61Working with Cell Alignment 62Adding Cell Borders, Background Colors and Patterns 63Using the

9、Format Painter 65Using Cell Styles 66Using Document Themes 68Applying Conditional Formatting 70Creating and Managing Conditional Formatting Rules 72Finding and Replacing Formatting 74 Creating and Working with Charts 76Creating a Chart 77Resizing and Moving a Chart 79Changing Chart Type 80Applying B

10、uilt-in Chart Layouts and Styles 81Working with Chart Labels 82Working with Chart Axes 84Working with Chart Backgrounds 85Working with Chart Analysis Commands 86Formatting Chart Elements 87Changing a Charts Source Data 89Using Chart Templates 90 Managing Workbooks 92Viewing a Workbook 93Working with

11、 the Workbook Window 95Splitting and Freezing a Workbook Window 97Selecting Worksheets in a Workbook 99Inserting and Deleting Worksheets 101Renaming, Moving and Copying Worksheets 102Working with Multiple Workbooks 104Hiding Rows, Columns, Worksheets and Windows 106Protecting a Workbook 108Protectin

12、g Worksheets and Worksheet Elements 110Sharing a Workbook 112Creating a Template 114 Working with Page Layout and Printing 116Creating Headers and Footers 117Using Page Breaks 119Adjusting Margins and Orientation 121Adjusting Size and Scale 122Adding Print Titles, Gridlines and Headings 123Advanced

13、Printing Options 124 More Functions and Formulas 127Formulas with Multiple Operators 128Inserting and Editing a Function 129AutoCalculate and Manual Calculation 131Defining Names 133Using and Managing Defined Names 135Displaying and Tracing Formulas 137Understanding Formula Errors 139 Working with D

14、ata Ranges 142Sorting by One Column 143Sorting by Colors or Icons 145Sorting by Multiple Columns 147Sorting by a Custom List 148Filtering Data 150Creating a Custom AutoFilter 151Using an Advanced Filter 152 Working with Tables 154Creating a Table 155Working with Table Size 157Working with the Total

15、Row 159Working with Table Data 161Summarizing a Table with a PivotTable 163Using the Data Form 164Using Table Styles 165Using Table Style Options 166Creating and Deleting Custom Table Styles 167Convert or Delete a Table 169 Working with PivotTables 170Creating a PivotTable 171Specifying PivotTable D

16、ata 172Changing a PivotTables Calculation 173Filtering and Sorting a PivotTable 174Working with PivotTable Layout 175Grouping PivotTable Items 177Updating a PivotTable 179Formatting a PivotTable 180Creating a PivotChart 181 Analyzing and Organizing Data 182Creating Scenarios 183Creating a Scenario R

17、eport 185Working with Data Tables 186Using Goal Seek 188Using Solver 189Using Data Validation 191Using Text to Columns 193Removing Duplicates 195Grouping and Outlining Data 196Using Subtotals 198Consolidating Data by Position or Category 200Consolidating Data Using Formulas 202 Working with the Web

18、and External Data 203Inserting a Hyperlink 204Creating a Web Page from a Workbook 206Importing Data from Access or Text Files 207Importing Data from the Web and Other Sources 209Working with Existing Data Connections 211 Working with Macros 213Recording a Macro 214Playing and Deleting a Macro 216Add

19、ing a Macro to the Quick Access Toolbar 217Editing a Macros Visual Basic Code 218Inserting Code in a Macro 219 Working with Objects 221Inserting Clip Art 222Inserting Pictures and Graphics Files 223Formatting Pictures and Graphics 224Inserting Shapes 226Formatting Shapes 228Resize, Move, Copy and De

20、lete Objects 230Applying Special Effects to Objects 231Grouping Objects 232Aligning Objects 233Flipping and Rotating Objects 234Layering Objects 235Inserting SmartArt 236Working with SmartArt Elements 237Formatting SmartArt 239Using WordArt 241Inserting an Embedded Object 242Inserting Symbols 243 Ad

21、vanced Topics 244Customizing the Quick Access Toolbar 245Using and Customizing AutoCorrect 247Changing Excels Default Options 249Recovering Your Documents 250Using Microsoft Office Diagnostics 252Viewing Document Properties and Finding a File 253Saving a Document as PDF or XPS 254Adding a Digital Si

22、gnature to a Workbook 256Preparing Documents for Publishing and Distribution 257Publishing a Workbook to a Document Workspace 258 Appendix of Common Functions 259Financial Functions 260Date & Time Functions 262Math & Trig Functions 264Statistical Functions 266Lookup & Reference Functions 267Database

23、 Functions 268Text Functions 269Logical Functions 270 Microsoft Office Excel 2007 Review 271What People Are Saying“I have saved hundreds of hours of design time by just picking and choosing what I want from the courseware.” Stephanie ZimmermanLancaster County Library“We have been able to customize o

24、ur training sessions on all Microsoft Office products, at all levels. The ROI of these guides is great.” Dawn CalvinLas Virgenes Municipal Water District“All in all, the friendliest, most open and easy to understand tutorial of its type that Ive ever seen.” W. BoudvilleA“curriculum that is of high q

25、uality, student friendly, and adaptable to the audience.” Sherrill WaylandSt. Charles Community College“a nice training option for almost any need. Their complete Microsoft Office package is by far the best deal on the market.” Technical Assistance ProgramPurdue University“Any instructor teaching cl

26、asses on Windows or Microsoft Office will definitely want to give serious consideration to this important collection of titles that will definitely fit well into their classroom learning.” Dale FarrisGolden Triangle PC Club “The materials are exceptional I am so excited about using them! Thanks to y

27、ou and your team for doing this wonderful work!” Shannon ColemanLearning Post Ltd.Introducing CustomGuide CoursewareThank you for choosing CustomGuide courseware as the solution to your training needs. A proven leader in the computer training industry, CustomGuide has been the key to successful trai

28、ning for thousands of students and instructors across the globe.This manual is designed for computer users of all experience levels. Novice users can use it to learn skills such as creating formulas, while advanced users can use it to create and format charts.All this information is quickly accessib

29、le. Lessons are broken down into basic step-by-step instructions that answer “how-to” questions in minutes. You can print a complete 300-page training manual or a single page of instructions.Heres how a CustomGuide manual is organized:ChaptersEach manual is divided into several chapters. Arent sure

30、if youre ready for a chapter? Look at the table of contents that appears at the beginning of each chapter. It will tell you the name of each lesson and subtopic included in the chapter.LessonsEach chapter contains lessons on related topics. Each lesson explains a new skill or topic and contains an e

31、xercise and exercise file to give you hands-on-experience. These skills can also be practiced using CustomGuide Online Learning.ReviewA review is included at the end of the manual. Use these quiz questions and answers to assess how much youve learned. How It Works Streamlined designFeaturing a professional-looking, easy-to-read design, 3rd generation courseware appeals to instructors, students and individual users alike. Exercise NotesA new Exercise Notes section appears at the top of each les