Baixe Excel 2010 Formulas e outras Notas de estudo em PDF para Microsoft Excel, somente na Docsity!
Microsoft
®
John Walkenbach
Excel 2010
Formulas
Microsoft
®
BONUS CD-ROM!
Includes all Excel workbook files used in the book, plus the complete book in a searchable PDF file
Excel® 2010 Formulas
Published by Wiley Publishing, Inc. 111 River Street Hoboken, NJ 07030- www.wiley.com
Copyright © 2010 by Wiley Publishing, Inc., Indianapolis, Indiana
Published by Wiley Publishing, Inc., Indianapolis, Indiana
Published simultaneously in Canada
No part of this publication may be reproduced, stored in a retrieval system or transmitted in any form or by any means, electronic, mechanical, photocopying, recording, scanning or otherwise, except as permitted under Sections 107 or 108 of the 1976 United States Copyright Act, without either the prior written permission of the Publisher, or authorization through payment of the appropriate per-copy fee to the Copyright Clearance Center, 222 Rosewood Drive, Danvers, MA 01923, (978) 750-8400, fax (978) 646-8600. Requests to the Publisher for permission should be addressed to the Permissions Department, John Wiley & Sons, Inc., 111 River Street, Hoboken, NJ 07030, (201) 748- 6011, fax (201) 748-6008, or online at http://www.wiley.com/go/permissions.
Trademarks: Wiley, the Wiley Publishing logo, For Dummies, the Dummies Man logo, A Reference for the Rest of Us!, The Dummies Way, Dummies Daily, The Fun and Easy Way, Dummies.com, Making Everything Easier, and related trade dress are trademarks or registered trademarks of John Wiley & Sons, Inc. and/or its affiliates in the United States and other countries, and may not be used without written permission. Excel is a registered trademark of Microsoft Corporation in the United States and/or other countries. All other trademarks are the property of their respective owners. Wiley Publishing, Inc., is not associated with any product or vendor mentioned in this book.
LIMIT OF LIABILITY/DISCLAIMER OF WARRANTY: THE PUBLISHER AND THE AUTHOR MAKE NO REPRESENTATIONS OR WARRANTIES WITH RESPECT TO THE ACCURACY OR COMPLETENESS OF THE CONTENTS OF THIS WORK AND SPECIFICALLY DISCLAIM ALL WARRANTIES, INCLUDING WITHOUT LIMITATION WARRANTIES OF FITNESS FOR A PARTICULAR PURPOSE. NO WARRANTY MAY BE CREATED OR EXTENDED BY SALES OR PROMOTIONAL MATERI- ALS. THE ADVICE AND STRATEGIES CONTAINED HEREIN MAY NOT BE SUITABLE FOR EVERY SITUATION. THIS WORK IS SOLD WITH THE UNDERSTANDING THAT THE PUBLISHER IS NOT ENGAGED IN RENDERING LEGAL, ACCOUNTING, OR OTHER PROFESSIONAL SERVICES. IF PROFESSIONAL ASSISTANCE IS REQUIRED, THE SERVICES OF A COMPETENT PROFESSIONAL PERSON SHOULD BE SOUGHT. NEITHER THE PUBLISHER NOR THE AUTHOR SHALL BE LIABLE FOR DAMAGES ARISING HEREFROM. THE FACT THAT AN ORGANIZATION OR WEBSITE IS REFERRED TO IN THIS WORK AS A CITATION AND/OR A POTENTIAL SOURCE OF FURTHER INFORMATION DOES NOT MEAN THAT THE AUTHOR OR THE PUBLISHER ENDORSES THE INFORMATION THE ORGANIZATION OR WEBSITE MAY PROVIDE OR RECOMMENDATIONS IT MAY MAKE. FURTHER, READERS SHOULD BE AWARE THAT INTERNET WEBSITES LISTED IN THIS WORK MAY HAVE CHANGED OR DISAPPEARED BETWEEN WHEN THIS WORK WAS WRIT- TEN AND WHEN IT IS READ. FULFILLMENT OF EACH COUPON OFFER IS THE SOLE RESPONSIBILITY OF THE OFFEROR.
For general information on our other products and services, please contact our Customer Care Department within the U.S. at 877-762-2974, outside the U.S. at 317-572-3993, or fax 317-572-4002.
For technical support, please visit www.wiley.com/techsupport.
Wiley also publishes its books in a variety of electronic formats. Some content that appears in print may not be available in electronic books.
Library of Congress Control Number: 2010925706
ISBN: 978-0-470-47536-
Manufactured in the United States of America
10 9 8 7 6 5 4 3 2 1
About the Author
John Walkenbach is a leading authority on spreadsheet software, and principal of J-Walk and
Associates Inc., a one-person consulting firm based in southern Arizona. John has received a
Microsoft MVP award every year since 2000. He’s the author of more than 50 spreadsheet books,
and has written more than 300 articles and reviews for a variety of publications, including PC
World , InfoWorld , PC Magazine , Windows , and PC/Computing. John also maintains a popular Web
site ( The Spreadsheet Page , http://spreadsheetpage.com), and is the developer of several
Excel utilities, including the Power Utility Pak, an award-winning add-in for Excel. John graduated
from the University of Missouri, and earned a Masters and PhD from the University of Montana.
Contents at a Glance
vi
Part VII: Appendixes Appendix A: Excel Function Reference.................................................... Appendix B: Using Custom Number Formats.............................................. 733 Appendix C: Additional Excel Resources.................................................. 753 Appendix D: What’s on the CD-ROM?.................................................... 759
Index................................................................................. 769
xv
- Introduction
- Chapter 1: Excel in a Nutshell Part I: Basic Information
- Chapter 2: Basic Facts about Formulas
- Chapter 3: Working with Names
- Chapter 4: Introducing Worksheet Functions Part II: Using Functions in Your Formulas
- Chapter 5: Manipulating Text
- Chapter 6: Working with Dates and Times.
- Chapter 7: Counting and Summing Techniques
- Chapter 8: Using Lookup Functions.
- Chapter 9: Tables and Worksheet Databases
- Chapter 10: Miscellaneous Calculations
- Chapter 11: Borrowing and Investing Formulas Part III: Financial Formulas
- Chapter 12: Discounting and Depreciation Formulas.
- Chapter 13: Financial Schedules..
- Chapter 14: Introducing Arrays Part IV: Array Formulas
- Chapter 15: Performing Magic with Array Formulas.
- Chapter 16: Intentional Circular References.. Part V: Miscellaneous Formula Techniques
- Chapter 17: Charting Techniques
- Chapter 18: Pivot Tables
- Chapter 19: Conditional Formatting and Data Validation.
- Chapter 20: Creating Megaformulas
- Chapter 21: Tools and Methods for Debugging Formulas
- Chapter 22: Introducing VBA Part VI: Developing Custom Worksheet Functions
- Chapter 23: Function Procedure Basics.
- Chapter 24: VBA Programming Concepts
- Chapter 25: VBA Custom Function Examples
- Introduction
- What You Need to Know
- What You Need to Have
- Conventions in This Book
- Keyboard conventions
- Mouse conventions
- What the icons mean
- How This Book Is Organized.
- Part I: Basic Information
- Part II: Using Functions in Your Formulas
- Part III: Financial Formulas
- Part IV: Array Formulas
- Part V: Miscellaneous Formula Techniques
- Part VI: Developing Custom Worksheet Functions
- Part VII: Appendixes
- How to Use This Book
- About the Companion CD-ROM
- About the Power Utility Pak Offer
- Reach Out
- Chapter 1: Excel in a Nutshell. Part I: Basic Information
- The History of Excel.
- It started with VisiCalc.
- Then came Lotus.
- Microsoft enters the picture.
- Excel versions..
- The Object Model Concept.
- The Workings of Workbooks
- Worksheets.
- Chart sheets.
- Macro sheets and dialog sheets.
- The Excel User Interface.
- A new UI.
- The Ribbon.
- Backstage View.
- Shortcut menus and the Mini Toolbar
- Customizing the UI
- Smart Tags
- Task pane
- Drag and drop
- Keyboard shortcuts viii
- Customized on-screen display
- Data entry
- Object and cell selecting
- The Excel Help System
- Cell Formatting
- Numeric formatting
- Stylistic formatting
- Tables
- Worksheet Formulas and Functions
- Objects on the Drawing Layer.
- Shapes.
- Illustrations.
- Linked picture objects.
- Controls
- Charts
- Sparkline graphics
- Customizing Excel
- Macros
- Add-in programs
- Internet Features
- Analysis Tools
- Database access
- Outlines
- Scenario management
- Pivot tables
- Auditing capabilities
- Solver add-in
- Protection Options
- Protecting formulas from being overwritten
- Protecting a workbook’s structure
- Password-protecting a workbook
- Chapter 2: Basic Facts about Formulas
- Entering and Editing Formulas.
- Formula elements
- Entering a formula.
- Pasting names.
- Spaces and line breaks
- Formula limits
- Sample formulas
- Editing formulas.
- Using Operators in Formulas
- Reference operators
- Sample formulas that use operators
- Operator precedence
- Nested parentheses
- Calculating Formulas ix
- Cell and Range References.
- Creating an absolute or a mixed reference
- Referencing other sheets or workbooks
- Making an Exact Copy of a Formula
- Converting Formulas to Values
- Hiding Formulas
- Errors in Formulas
- Dealing with Circular References
- Goal Seeking.
- A goal seeking example
- More about goal seeking
- Chapter 3: Working with Names
- What’s in a Name?
- A Name’s Scope
- Referencing names
- Referencing names from another workbook
- Conflicting names
- The Name Manager
- Creating names
- Editing names
- Deleting names
- Shortcuts for Creating Cell and Range Names
- The New Name dialog box.
- Creating names using the Name box
- Creating names automatically
- Naming entire rows and columns
- Names created by Excel
- Creating Multisheet Names
- Working with Range and Cell Names
- Creating a list of names
- Using names in formulas
- Using the intersection operators with names
- Using the range operator with names.
- Referencing a single cell in a multicell named range.
- Applying names to existing formulas
- Applying names automatically when creating a formula
- Unapplying names
- Names with errors
- Viewing named ranges
- Using names in charts
- How Excel Maintains Cell and Range Names
- Inserting a row or column
- Deleting a row or column.
- Cutting and pasting.
- Potential Problems with Names x
- Name problems when copying sheets.
- Name problems when deleting sheets
- The Secret to Understanding Names
- Naming constants
- Naming text constants
- Using worksheet functions in named formulas
- Using cell and range references in named formulas.
- Using named formulas with relative references
- Advanced Techniques That Use Names
- Using the INDIRECT function with a named range
- Using the INDIRECT function to create a named range with a fixed address.
- Using arrays in named formulas
- Creating a dynamic named formula
- Chapter 4: Introducing Worksheet Functions. Part II: Using Functions in Your Formulas
- What Is a Function?
- Simplify your formulas
- Perform otherwise impossible calculations
- Speed up editing tasks
- Provide decision-making capability
- More about functions
- Function Argument Types
- Names as arguments
- Full-column or full-row as arguments
- Literal values as arguments
- Expressions as arguments
- Other functions as arguments
- Arrays as arguments
- Ways to Enter a Function into a Formula.
- Entering a function manually.
- Using the Function Library commands
- Using the Insert Function dialog box
- More tips for entering functions
- Function Categories
- Financial functions.
- Date and time functions
- Math and trig functions
- Statistical functions
- Lookup and reference functions
- Database functions
- Text functions
- Logical functions
- Information functions
- User-defined functions
- Engineering functions xi
- Cube functions
- Compatibility functions
- Other function categories
- Chapter 5: Manipulating Text
- A Few Words about Text
- How many characters in a cell?.
- Numbers as text
- Text Functions.
- Determining whether a cell contains text
- Working with character codes.
- Determining whether two strings are identical.
- Joining two or more cells.
- Displaying formatted values as text..
- Displaying formatted currency values as text.
- Removing excess spaces and nonprinting characters.
- Counting characters in a string.
- Repeating a character or string.
- Creating a text histogram.
- Padding a number
- Changing the case of text
- Extracting characters from a string.
- Replacing text with other text.
- Finding and searching within a string.
- Searching and replacing within a string.
- Advanced Text Formulas.
- Counting specific characters in a cell.
- Counting the occurrences of a substring in a cell.
- Removing trailing minus signs.
- Expressing a number as an ordinal.
- Determining a column letter for a column number.
- Extracting a filename from a path specification.
- Extracting the first word of a string.
- Extracting the last word of a string.
- Extracting all but the first word of a string.
- Extracting first names, middle names, and last names.
- Removing titles from names
- Counting the number of words in a cell.
- Chapter 6: Working with Dates and Times.
- How Excel Handles Dates and Times.
- Understanding date serial numbers
- Entering dates.
- Understanding time serial numbers
- Entering times.
- Formatting dates and times
- Problems with dates
- Date-Related Functions. xii
- Displaying the current date.
- Displaying any date..
- Generating a series of dates.
- Converting a non-date string to a date.
- Calculating the number of days between two dates..
- Calculating the number of work days between two dates.
- Offsetting a date using only work days.
- Calculating the number of years between two dates.
- Calculating a person’s age.
- Determining the day of the year.
- Determining the day of the week
- Determining the date of the most recent Sunday
- Determining the first day of the week after a date.
- Determining the n th occurrence of a day of the week in a month.
- Counting the occurrences of a day of the week.
- Expressing a date as an ordinal number
- Calculating dates of holidays
- Determining the last day of a month.
- Determining whether a year is a leap year.
- Determining a date’s quarter.
- Converting a year to roman numerals
- Time-Related Functions
- Displaying the current time
- Displaying any time.
- Summing times that exceed 24 hours
- Calculating the difference between two times.
- Converting from military time.
- Converting decimal hours, minutes, or seconds to a time.
- Adding hours, minutes, or seconds to a time.
- Converting between time zones.
- Rounding time values.
- Working with non–time-of-day values.
- Chapter 7: Counting and Summing Techniques
- Counting and Summing Worksheet Cells
- Counting or Summing Records in Databases and Pivot Tables.
- Basic Counting Formulas.
- Counting the total number of cells.
- Counting blank cells.
- Counting nonblank cells.
- Counting numeric cells.
- Counting nontext cells.
- Counting text cells..
- Counting logical values.
- Counting error values in a range.
- Advanced Counting Formulas. xiii
- Counting cells with the COUNTIF function.
- Counting cells that meet multiple criteria.
- Counting the most frequently occurring entry..
- Counting the occurrences of specific text.
- Counting the number of unique values.
- Creating a frequency distribution
- Summing Formulas
- Summing all cells in a range
- Computing a cumulative sum
- Summing the “top n ” values
- Conditional Sums Using a Single Criterion
- Summing only negative values
- Summing values based on a different range
- Summing values based on a text comparison
- Summing values based on a date comparison
- Conditional Sums Using Multiple Criteria
- Using And criteria
- Using Or criteria
- Using And and Or criteria
- Chapter 8: Using Lookup Functions.
- What Is a Lookup Formula?.
- Functions Relevant to Lookups.
- Basic Lookup Formulas.
- The VLOOKUP function.
- The HLOOKUP function.
- The LOOKUP function..
- Combining the MATCH and INDEX functions.
- Specialized Lookup Formulas.
- Looking up an exact value
- Looking up a value to the left
- Performing a case-sensitive lookup
- Choosing among multiple lookup tables
- Determining letter grades for test scores
- Calculating a grade point average
- Performing a two-way lookup
- Performing a two-column lookup.
- Determining the address of a value within a range
- Looking up a value by using the closest match
- Looking up a value using linear interpolation
- Chapter 9: Tables and Worksheet Databases
- Tables and Terminology
- A worksheet database example
- A table example
- Uses for worksheet databases and tables
- Working with Tables xiv
- Creating a table
- Changing the look of a table
- Navigating and selecting in a table.
- Adding new rows or columns
- Deleting rows or columns
- Moving a table
- Setting table style options
- Removing duplicate rows from a table
- Sorting and filtering a table
- Working with the Total row
- Using formulas within a table
- Referencing data in a table
- Converting a table to a worksheet database
- Using Advanced Filtering
- Setting up a criteria range
- Applying an advanced filter
- Clearing an advanced filter
- Specifying Advanced Filter Criteria.
- Specifying a single criterion
- Specifying multiple criteria
- Specifying computed criteria
- Using Database Functions.
- Inserting Subtotals
- Chapter 10: Miscellaneous Calculations
- Unit Conversions
- Solving Right Triangles
- Area, Surface, Circumference, and Volume Calculations
- Calculating the area and perimeter of a square
- Calculating the area and perimeter of a rectangle
- Calculating the area and perimeter of a circle
- Calculating the area of a trapezoid.
- Calculating the area of a triangle.
- Calculating the surface and volume of a sphere.
- Calculating the surface and volume of a cube
- Calculating the surface and volume of a cone
- Calculating the volume of a cylinder
- Calculating the volume of a pyramid
- Solving Simultaneous Equations
- Rounding Numbers
- Basic rounding formulas.
- Rounding to the nearest multiple
- Rounding currency values
- Working with fractional dollars
- Using the INT and TRUNC functions
- Rounding to an even or odd integer
- Rounding to n significant digits
- Chapter 11: Borrowing and Investing Formulas Part III: Financial Formulas
- Financial Concepts
- Time value of money
- Cash in and cash out
- Matching time periods
- Timing of the first payment
- The Basic Excel Financial Functions
- Calculating present value
- Calculating future value
- Calculating payments
- Calculating rates
- Calculating periods
- Calculating the Interest and Principal Components
- Using the IPMT and PPMT functions
- Using the CUMIPMT and CUMPRINC functions
- Converting Interest Rates
- Methods of quoting interest rates
- Conversion formulas
- Limitations of Excel’s Financial Functions.
- Deferred start to a series of regular payments.
- Valuing a series of variable payments.
- Bond Calculations.
- Pricing bonds.
- Calculating yield.
- Chapter 12: Discounting and Depreciation Formulas
- Using the NPV Function.
- Definition of NPV.
- NPV function examples.
- Using the NPV function to calculate accumulated amounts
- Using the IRR Function
- Rate of return
- Geometric growth rates
- Checking results.
- Multiple Rates of IRR and the MIRR Function.
- Multiple IRRs..
- Separating flows
- Using balances instead of flows
- Irregular Cash Flows
- Net present value
- Internal rate of return
- Using the FVSCHEDULE Function
- Calculating an annual return
- Depreciation Calculations
- Chapter 13: Financial Schedules. xvi
- Creating Financial Schedules.
- Creating Amortization Schedules
- A simple amortization schedule
- A dynamic amortization schedule
- Using payment and interest tables.
- Credit card calculations
- Summarizing Loan Options Using a Data Table.
- Creating a one-way data table.
- Creating a two-way data table
- Financial Statements and Ratios
- Basic financial statements
- Ratio analysis
- Creating Indices
- Chapter 14: Introducing Arrays Part IV: Array Formulas
- Introducing Array Formulas
- A multicell array formula
- A single-cell array formula
- Creating an array constant
- Array constant elements.
- Understanding the Dimensions of an Array
- One-dimensional horizontal arrays
- One-dimensional vertical arrays
- Two-dimensional arrays
- Naming Array Constants
- Working with Array Formulas
- Entering an array formula
- Selecting an array formula range
- Editing an array formula.
- Expanding or contracting a multicell array formula
- Using Multicell Array Formulas
- Creating an array from values in a range
- Creating an array constant from values in a range
- Performing operations on an array
- Using functions with an array.
- Transposing an array.
- Generating an array of consecutive integers
- Using Single-Cell Array Formulas
- Counting characters in a range
- Summing the three smallest values in a range
- Counting text cells in a range
- Eliminating intermediate formulas
- Using an array in lieu of a range reference
- Chapter 15: Performing Magic with Array Formulas. xvii
- Working with Single-Cell Array Formulas.
- Summing a range that contains errors.
- Counting the number of error values in a range
- Summing the n largest values in a range
- Computing an average that excludes zeros
- Determining whether a particular value appears in a range
- Counting the number of differences in two ranges
- Returning the location of the maximum value in a range
- Finding the row of a value’s n th occurrence in a range
- Returning the longest text in a range
- Determining whether a range contains valid values
- Summing the digits of an integer
- Summing rounded values.
- Summing every n th value in a range
- Removing nonnumeric characters from a string
- Determining the closest value in a range
- Returning the last value in a column
- Returning the last value in a row
- Ranking data with an array formula
- Working with Multicell Array Formulas
- Returning only positive values from a range
- Returning nonblank cells from a range
- Reversing the order of cells in a range
- Sorting a range of values dynamically
- Returning a list of unique items in a range
- Displaying a calendar in a range
- Chapter 16: Intentional Circular References. Part V: Miscellaneous Formula Techniques
- What Are Circular References?.
- Correcting an accidental circular reference
- Understanding indirect circular references.
- Intentional Circular References.
- How Excel Determines Calculation and Iteration Settings
- Circular Reference Examples.
- Generating unique random integers.
- Solving a recursive equation
- Solving simultaneous equations using a circular reference
- Animating a chart using iteration
- Potential Problems with Intentional Circular References
- Chapter 17: Charting Techniques.
- Understanding the SERIES Formula
- Using names in a SERIES formula.
- Unlinking a chart series from its data range
- Creating Links to Cells xviii
- Adding a chart title link
- Adding axis title links
- Adding links to data labels
- Adding text links
- Adding a linked picture to a chart
- Chart Examples
- Charting progress toward a goal
- Creating a gauge chart
- Displaying conditional colors in a column chart
- Creating a comparative histogram
- Creating a Gantt chart
- Creating a box plot
- Plotting every n th data point
- Plotting the last n data points
- Selecting a series from a combo box
- Plotting mathematical functions
- Plotting a circle
- Creating a clock chart
- Creating awesome designs
- Working with Trendlines
- Linear trendlines
- Working with nonlinear trendlines
- Chapter 18: Pivot Tables
- About Pivot Tables
- A Pivot Table Example
- Data Appropriate for a Pivot Table
- Creating a Pivot Table
- Specifying the Data
- Specifying the location for the pivot table
- Laying out the pivot table
- Formatting the pivot table.
- Modifying the pivot table
- More Pivot Table Examples
- Question
- Question 2.
- Question 3.
- Question
- Question 5.
- Question
- Question 7.
- Grouping Pivot Table Items
- A manual grouping example
- Viewing grouped data
- Automatic grouping examples
- Creating a Frequency Distribution.
