[Excel.2013：The.Missing.Manual(2013.4)].Matthew.MacDonald.文字版.pdf

[Excel.2013：The.Missing.Manual(2013.4)].Matthew.MacDonald.文字版.pdfExce 2013the missing manualThe book that should have been in the boxMatthew macDonald○ REILLYBeijing I Cambridge FarnhamI Koln Sebastopol I TokyoExcel 2013: The Missing Manualby Matthew MacDonaldCopyright o 2013 Matthew MacDonald. All rights reservedPrinted in the United states of americaPublished by o' reilly Media, Inc1005 Gravenstein Highway North, Sebastopol, CA 95472O'Reilly books may be purchased for educational, business, or sales promotional useOnlineeditionsarealsoavailableformosttitles(http://my.safaribooksonline.comFor more information, contact our corporate/institutional sales department(800)998-9938orcorporate@oreilly.comApril 2013First editionRevision History for the Nth Edition:2013-04-10 First releaseSeehttporeilly.com/catalog/errata.csp?isbn=9781449357276forreleasedetailsThe Missing manual is a registered trademark of o'Reilly Media, Inc. The missingManual logo, and the book that should have been in the box "are trademarks ofO'Reilly Media, Inc. Many of the designations used by manufacturers and sellers todistinguish their products are claimed as trademarks. Where those designationsappear in this book, and o Reilly media is aware of a trademark claim, thedesignations are capitalizedhile every precaution has been taken in the preparation of this book, the publisherassumes no responsibility for errors or omissions, or for damages resulting from theuse of the information contained in itSBN-13:978-1-449-35727-6[LSIContentsThe missing creditsIntroductionXIPart One: Worksheet BasicsCHAPTER 1: Creating Your First SpreadsheetStarting a WorkbookAdding Information to a WorksheetUsing the ribbon12Using the status bar19Going backstage.........,...23Saving FilesOpening filesCHAPTER 2: Adding Information to WorksheetsAdding different Types of data47Handy Timesavers56Dealing with Change: Undo, Redo, and AutoRecover69Spell-CheckAdding Hyperlinks80CHAPTER 3: Moving Data85Selecting CellsMoving Cells around92Adding and moving columns or rows103CHAPTER 4: Managing Worksheets.107Worksheets and workbooks108Find and replaceCHAPTER 5: Formatting Cells127Formatting Cell values128Formatting Cell Appearance.149CHAPTER 6: Smart Formatting Tricks165The format painter.165Styles and themes166Conditional Formatting179CHAPTER 7: Viewing and Printing Worksheets189Controlling your view89Printing,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,202Controlling Pagination214Part two: Formulas and functionsCHAPTER 8: Building Basic Formulas.221Creating a basic formula221Functions227Formula errors234Logical Operators238Formula shortcuts240Copying Formulas248chaPter 9: Math and statistical functions261Rounding NumbersGroups of Numbers267General math FunctionsTrigonometry and Advanced Math................. 288Advanced statistics292CHAPTER 10: Financial Functions297The world of finance.297Financial functions.298Depreciation...312other Financial functions315CHAPTER 11: Manipulating Dates, Times, and Text319Manipulating Text319Manipulating Dates and Times328CHAPTER 12: Lookup, Reference, and Information Functions345The Basic lookup.346Advanced Lookups353Information functions365Tutorial: Generating Invoices from a Product catalog369CONTENTSCHAPTER 13: Advanced Formula riting and Troubleshooting....375Conditions in formulas375Descriptive Names for cell references.....384Variable data tables395Controlling recalculation400Solving formula errors401Part Three: Organizing Your InformationCHAPTER 14: Tables: List Management Made Easy413The basics of tables414Sorting a Table424Filtering a Table430Dealing with duplicate rows436Performing Table Calculations.....,438CHAPTER 15: Grouping and outlining Data451Basic Data grouping452Grouping Tim464CHAPTER 16: Templates469Using the office Online Templates.470Rolling your own templates479Part Four: Charts and GraphicsCHAPTER 17: Creating Basic Charts487Charting 101488Basic tasks with charts495Practical Charting502Chart Types512CHAPTER 18: Formatting and Perfecting Charts527Chart styles and layouts.......527Adding chart elements53Formatting Chart elements543Improving Your Charts556Advanced Charting568CHAPTER 19: Inserting Graphics577Adding pictures to a Worksheet578Excel's Clip art Library......593Drawing Shapes596SmartArt607CONTENTSCHAPTER 20: Visualizing Your Data.611Data bars612Color scale622Icon sets623Sparklines627Part Five: Sharing Data with the rest of the worldCHAPTER 21: Protecting Your Workbooks639Understanding excel's safeguards.................639Data∨ alidation640Locked and hidden cells652CHAPTER 22. Worksheet Collaboration663Your Excel ldentity664Preparing your Workbook665Distributing Your Workbook671Adding Comments.674Tracking Changes679Sharing Your Workbook.690Reviewing Workbooks with Inquire696CHAPTER 23: Using Excel on the Web.....705Putting Your Files online705Using the Excel Web app.....................714Sharing your files724CHAPTER 24: Exchanging Data with other Programs739Sharing Information in Windows740Embedding and linking objects74Transferring data751Part Six: Advanced Data AnalysisCHAPTER 25: Scenarios and Goal Seeking763Using scenarios.Using Goal Seek771Solver776CONTENTSCHAPTER 26: Pivot Tables791Summary Tables Revisited792Building pivot Tables.795Multi-Layered Pivot Ta806Fine-Tuning Pivot Table Calculations811Filtering a pivot table.815Pivot charts826CHAPTER 27: Analyzing Databases831Excel and databases831Creating a data Connection833The data Model: Boosting pivot Tables.....846CHAPTER 28: Analyzing XMl and Web data861Understanding XML862Retrieving Information from XML867Creating Web Queries879Connecting to Online Data Services with OData883Part Seven: Programming ExcelCHAPTER 29: Automating Tasks with Macros893Macros 101......893The macro recorder896Macro security903Creating Practical macrosCHAPTER 30: Programming Spreadsheets with VBA9T9The visual basic editor920Understanding macro code926Exploring the VBa Language.930Part Eight: AppendixAPPENDIX A: Customizing the Ribbon945Adding your Favorites to the Qat946Personalizing the ribbon951Index957CONTENTS