- Microsoft® Excel® 2010 Inside Out
- Dedication
- A Note Regarding Supplemental Files
- Acknowledgments
- Questions and Support
- Conventions and Features Used in This Book
- 1. Examining the Excel Environment
- 1. What’s New in Microsoft Excel 2010
- New and Improved for 2010
- Backstage View
- Ribbon Customization
- Sparklines
- Paste Preview
- Improved Picture Editing
- Office Web Apps
- Slicers
- Improved Conditional Formatting
- New Functions and Functional Consistency
- Improved Math Equation Support
- Improved Charting Capacity
- Additional SmartArt Graphics
- 64-Bit Edition
- Office Mobile 2010
- If You Missed the Last Upgrade
- Retired in 2007
- If You Missed the Last Two Upgrades
- Onward
- New and Improved for 2010
- 2. Exploring Excel Fundamentals
- What Happens After You Install Excel?
- Examining the Excel 2010 Workspace
- Exploring File Management Fundamentals
- Importing and Exporting Files
- Using the Help System
- Recovering from Crashes
- 3. Custom-Tailoring the Excel Workspace
- 4. Security and Privacy
- 1. What’s New in Microsoft Excel 2010
- 2. Building Worksheets
- 5. Planning Your Worksheet Design
- 6. How to Work a Worksheet
- 7. How to Work a Workbook
- 3. Formatting and Editing Worksheets
- 8. Worksheet Editing Techniques
- Copying, Cutting, and Pasting
- Inserting and Deleting
- Undoing Previous Actions
- Editing Cell Contents
- Finding and Replacing Stuff
- Getting the Words Right
- Editing Multiple Worksheets
- Auditing and Documenting Worksheets
- Outlining Worksheets
- Consolidating Worksheets
- 9. Worksheet Formatting Techniques
- Formatting Fundamentals
- Using Themes and Cell Styles
- Formatting Conditionally
- Formatting in Depth
- Using Template Files to Store Formatting
- 8. Worksheet Editing Techniques
- 4. Adding Graphics and Printing
- 10. Creating and Formatting Graphics
- Using the Shapes Tools
- Creating WordArt
- Creating SmartArt
- Inserting Other Graphics
- Formatting Graphics
- Working with Graphic Objects
- More Tricks with Graphic Objects
- 11. Printing and Presenting
- 10. Creating and Formatting Graphics
- 5. Creating Formulas and Performing Data Analysis
- 12. Building Formulas
- Formula Fundamentals
- Using Functions: A Preview
- Working with Formulas
- Naming Cells and Cell Ranges
- Using Names in Formulas
- Defining and Managing Names
- Editing Names
- Workbook-Wide vs. Worksheet-Only Names
- Creating Names Semiautomatically
- Naming Constants and Formulas
- Using Relative References in Named Formulas
- Creating Three-Dimensional Names
- Inserting Names in Formulas
- Creating a List of Names
- Replacing References with Names
- Using Go To with Names
- Getting Explicit About Intersections
- Creating Three-Dimensional Formulas
- Formula-Bar Formatting
- Using Structured References
- Naming Cells and Cell Ranges
- Worksheet Calculation
- Using Arrays
- Linking Workbooks
- Creating Conditional Tests
- 13. Using Functions
- 14. Everyday Functions
- Understanding Mathematical Functions
- Understanding Text Functions
- Understanding Logical Functions
- Understanding Information Functions
- Understanding Lookup and Reference Functions
- 15. Formatting and Calculating Date and Time
- 16. Functions for Financial Analysis
- Calculating Investments
- Calculating Depreciation
- Analyzing Securities
- The DOLLARDE and DOLLARFR Functions
- The ACCRINT and ACCRINTM Functions
- The INTRATE and RECEIVED Functions
- The PRICE, PRICEDISC, and PRICEMAT Functions
- The DISC Function
- The YIELD, YIELDDISC, and YIELDMAT Functions
- The TBILLEQ, TBILLPRICE, and TBILLYIELD Functions
- The COUPDAYBS, COUPDAYS, COUPDAYSNC, COUPNCD, COUPNUM, and COUPPCD Functions
- The DURATION and MDURATION Functions
- Using the Euro Currency Tools Add-In
- 17. Functions for Analyzing Statistics
- Analyzing Distributions of Data
- Understanding Linear and Exponential Regression
- Using the Analysis Toolpak Data Analysis Tools
- Installing the Analysis Toolpak
- Using the Descriptive Statistics Tool
- Creating Histograms
- Using the Rank And Percentile Tool
- Generating Random Numbers
- Distributing Random Numbers Uniformly
- Distributing Random Numbers Normally
- Generating Random Numbers Using Bernoulli Distribution
- Generating Random Numbers Using Binomial Distribution
- Generating Random Numbers Using Poisson Distribution
- Generating Random Numbers Using Discrete Distribution
- Generating Semi-Random Numbers Using Patterned Distribution
- Sampling a Population of Numbers
- Calculating Moving Averages
- 18. Performing What-If Analysis
- Using Data Tables
- Using the Scenario Manager
- Using the Goal Seek Command
- Using the Solver
- Stating the Objective
- Specifying Variable Cells
- Specifying Constraints
- Other Solver Options
- Saving and Reusing the Solver Parameters
- Assigning the Solver Results to Named Scenarios
- Generating Reports
- The Sensitivity Report
- The Answer Report
- The Limits Report
- 6. Creating Charts
- 19. Basic Charting Techniques
- Selecting Data for Your Chart
- Choosing a Chart Type
- Changing the Chart Type
- Switching Rows and Columns
- Choosing a Chart Layout
- Choosing a Chart Style
- Moving the Chart to a Separate Chart Sheet
- Adding, Editing, and Removing a Chart Title
- Adding, Editing, and Removing a Legend
- Adding and Positioning Data Labels
- Adding a Data Table
- Manipulating Axes
- Adding Axis Titles
- Changing the Rotation of Chart Text
- Displaying Gridlines
- Adding Text Annotations
- Changing the Font or Size of Chart Text
- Applying Shape Styles and WordArt Styles
- Adding Glow and Soft Edges to Chart Markers
- Saving Templates to Make Chart Formats Reusable
- 20. Using Sparklines
- 21. Advanced Charting Techniques
- Selecting Chart Elements
- Repositioning Chart Elements with the Mouse
- Formatting Lines and Borders
- Formatting Areas
- Formatting Text
- Working with Axes
- Specifying the Line Style, Color, and Weight
- Specifying the Position of Tick Marks and Axis Labels
- Changing the Numeric Format Used by Axis Labels
- Changing the Scale of a Value Axis
- Changing the Scale of a Text Category Axis
- Changing the Scale of a Date Category Axis
- Formatting a Depth (Series) Axis
- Working with Data Labels
- Formatting Data Series and Markers
- Modifying the Data Source for Your Chart
- Using Multilevel Categories
- Adding Moving Averages and Other Trendlines
- Adding Error Bars
- Adding High-Low Lines and Up and Down Bars
- 19. Basic Charting Techniques
- 7. Managing Databases and Tables
- 22. Managing Information in Tables
- How to Organize a Table
- Creating a Table
- Adding Totals to a Table
- Sorting Tables and Other Ranges
- Filtering a List or Table
- Using Filters
- Determining How Many Rows Pass the Filter
- Removing a Filter
- Using Filter Criteria in More Than One Column
- Using a Filter to Find the Top or Bottom n Items
- Using a Filter to Display Blank Entries
- Using Filters to Select Dates
- Using Filters to Specify More Complex Criteria
- Using Custom Filters to Specify Complex Relationships
- Using the Advanced Filter Command
- An Example Using Three ORs on a Column
- An Example Using Both OR and AND
- Applying Multiple Criteria to the Same Column
- Using Computed Criteria
- Extracting Filtered Rows
- Removing Duplicate Records
- Using Filters
- Using Formulas with Tables
- Formatting Tables
- 23. Analyzing Data with PivotTable Reports
- Introducing PivotTables
- Creating a PivotTable
- Rearranging PivotTable Fields
- Refreshing a PivotTable
- Changing the Numeric Format of PivotTable Data
- Choosing Report Layout Options
- Formatting a PivotTable
- Displaying Totals and Subtotals
- Sorting PivotTable Fields
- Filtering PivotTable Fields
- Changing PivotTable Calculations
- Grouping and Ungrouping Data
- Displaying the Details Behind a Data Value
- Creating PivotCharts
- 24. Working with External Data
- Using and Reusing Data Connections
- Opening an Entire Access Table in Excel
- Working with Data in Text Files
- Working with XML Files
- Using Microsoft Query to Import Data
- Using a Web Query to Return Internet Data
- Using an Existing Web Query
- Creating Your Own Web Query
- Using the From Web Command
- Copying and Pasting from the Web Browser
- Exporting from Internet Explorer to Excel
- 8. Collaborating
- 25. Collaborating on a Network or by E-Mail
- 26. Collaborating Using the Internet
- 9. Automating Excel
- 27. Recording Macros
- 28. Creating Custom Functions
- 29. Debugging Macros and Custom Functions
- Using Design-Time Tools
- Dealing with Run-Time Errors
- 10. Integrating Excel with Other App ication
- 30. Using Hyperlinks
- 31. Linking and Embedding
- 32. Using Excel Data in Word Documents
- 11. Appendixes
- Index
- About the Authors
- Copyright
- 22. Managing Information in Tables
- 12. Building Formulas
Understanding Mathematical Functions
Understanding Logical Functions
Understanding Information Functions
Understanding Lookup and Reference Functions
THIS chapter describes some of the more useful functions Microsoft Excel 2010 has to offer. To keep this book from threatening the structural integrity of your bookshelf, we’ve had to make some hard choices about which functions to highlight. Therefore, this chapter (along with Chapters Chapter 15, Chapter 16, and Chapter 17) by no means represents a comprehensive reference. For complete information about all the built-in functions that Excel 2010 has to offer, you can use a number of on-screen tools, which are covered in Using the Built-In Function Reference in Excel on page 527. Appendix C, lists every function available in Excel, along with the basic information you need to put each one to use, and includes cross-references to any information available in this and other chapters.
Note
For more information, see Chapter 15, Chapter 16 and Chapter 17.
-
No Comment
..................Content has been hidden....................
You can't read the all page of ebook, please click here login for view all page.