第1页 / 共818页
第2页 / 共818页
第3页 / 共818页
第4页 / 共818页
第5页 / 共818页
第6页 / 共818页
第7页 / 共818页
第8页 / 共818页
excel_formulas.pdf
Microsoft® Excel® 2010 Formulas
About the Author
Contents at a Glance
Table of Contents
INTRODUCTION
What You Need to Know
What You Need to Have
Conventions in This Book
How This Book Is Organized
How to Use This Book
About the Companion CD-ROM
About the Power Utility Pak Offer
Reach Out
Part I: Basic Information
Chapter 1: Excel in a Nutshell
The History of Excel
The Object Model Concept
The Workings of Workbooks
The Excel User Interface
The Excel Help System
Cell Formatting
Tables
Worksheet Formulas and Functions
Objects on the Drawing Layer
Customizing Excel
Internet Features
Analysis Tools
Protection Options
Chapter 2: Basic Facts about Formulas
Entering and Editing Formulas
Using Operators in Formulas
Calculating Formulas
Cell and Range References
Making an Exact Copy of a Formula
Converting Formulas to Values
Hiding Formulas
Errors in Formulas
Dealing with Circular References
Goal Seeking
Chapter 3: Working with Names
What’s in a Name?
A Name’s Scope
The Name Manager
Shortcuts for Creating Cell and Range Names
Creating Multisheet Names
Working with Range and Cell Names
How Excel Maintains Cell and Range Names
Potential Problems with Names
The Secret to Understanding Names
Advanced Techniques That Use Names
Part II: Using Functions in Your Formulas
Chapter 4: Introducing Worksheet Functions
What Is a Function?
Function Argument Types
Ways to Enter a Function into a Formula
Function Categories
Chapter 5: Manipulating Text
A Few Words about Text
Text Functions
Advanced Text Formulas
Chapter 6: Working with Dates and Times
How Excel Handles Dates and Times
Date-Related Functions
Time-Related Functions
Chapter 7: Counting and Summing Techniques
Counting and Summing Worksheet Cells
Counting or Summing Records in Databases and Pivot Tables
Basic Counting Formulas
Advanced Counting Formulas
Summing Formulas
Conditional Sums Using a Single Criterion
Conditional Sums Using Multiple Criteria
Chapter 8: Using Lookup Functions
What Is a Lookup Formula?
Functions Relevant to Lookups
Basic Lookup Formulas
Specialized Lookup Formulas
Chapter 9: Tables and Worksheet Databases
Tables and Terminology
Working with Tables
Using Advanced Filtering
Specifying Advanced Filter Criteria
Using Database Functions
Inserting Subtotals
Chapter 10: Miscellaneous Calculations
Unit Conversions
Solving Right Triangles
Area, Surface, Circumference, and Volume Calculations
Solving Simultaneous Equations
Rounding Numbers
Part III: Financial Formulas
Chapter 11: Borrowing and Investing Formulas
Financial Concepts
The Basic Excel Financial Functions
Calculating the Interest and Principal Components
Converting Interest Rates
Limitations of Excel’s Financial Functions
Bond Calculations
Chapter 12: Discounting and Depreciation Formulas
Using the NPV Function
Using the IRR Function
Multiple Rates of IRR and the MIRR Function
Irregular Cash Flows
Using the FVSCHEDULE Function
Depreciation Calculations
Chapter 13: Financial Schedules
Creating Financial Schedules
Creating Amortization Schedules
Summarizing Loan Options Using a Data Table
Financial Statements and Ratios
Creating Indices
Part IV: Array Formulas
Chapter 14: Introducing Arrays
Introducing Array Formulas
Understanding the Dimensions of an Array
Naming Array Constants
Working with Array Formulas
Using Multicell Array Formulas
Using Single-Cell Array Formulas
Chapter 15: Performing Magic with Array Formulas
Working with Single-Cell Array Formulas
Working with Multicell Array Formulas
Part V: Miscellaneous Formula Techniques
Chapter 16: Intentional Circular References
What Are Circular References?
Intentional Circular References
How Excel Determines Calculation and Iteration Settings
Circular Reference Examples
Potential Problems with Intentional Circular References
Chapter 17: Charting Techniques
Understanding the SERIES Formula
Creating Links to Cells
Chart Examples
Working with Trendlines
Chapter 18: Pivot Tables
About Pivot Tables
A Pivot Table Example
Data Appropriate for a Pivot Table
Creating a Pivot Table
More Pivot Table Examples
Grouping Pivot Table Items
Creating a Frequency Distribution
Creating a Calculated Field or Calculated Item
Filtering Pivot Tables with Slicers
Referencing Cells within a Pivot Table
Another Pivot Table Example
Producing a Report with a Pivot Table
Chapter 19: Conditional Formatting and Data Validation
Conditional Formatting
Data Validation
Chapter 20: Creating Megaformulas
What Is a Megaformula?
Creating a Megaformula: A Simple Example
Megaformula Examples
The Pros and Cons of Megaformulas
Chapter 21: Tools and Methods for Debugging Formulas
Formula Debugging?
Formula Problems and Solutions
Excel’s Auditing Tools
Part VI: Developing Custom Worksheet Functions
Chapter 22: Introducing VBA
About VBA
Displaying the Developer Tab
About Macro Security
Saving Workbooks That Contain Macros
Introducing the Visual Basic Editor
Chapter 23: Function Procedure Basics
Why Create Custom Functions?
An Introductory VBA Function Example
About Function Procedures
Using the Insert Function Dialog Box
Testing and Debugging Your Functions
Creating Add-Ins
Chapter 24: VBA Programming Concepts
An Introductory Example Function Procedure
Using Comments in Your Code
Using Variables, Data Types, and Constants
Using Assignment Expressions
Using Arrays
Using Built-In VBA Functions
Controlling Execution
Using Ranges
Chapter 25: VBA Custom Function Examples
Simple Functions
Determining a Cell’s Data Type
A Multifunctional Function
Generating Random Numbers
Calculating Sales Commissions
Text Manipulation Functions
Counting Functions
Date Functions
Returning the Last Nonempty Cell in a Column or Row
Multisheet Functions
Advanced Function Techniques
Part VII: Appendixes
Appendix A: Excel Function Reference
Appendix B: Using Custom Number Formats
About Number Formatting
Creating a Custom Number Format
Custom Number Format Examples
Appendix C: Additional Excel Resources
The Excel Help System
Microsoft Technical Support
Internet Newsgroups
Internet Web sites
Appendix D: What’s on the CD-ROM?
System Requirements
Using the CD
Files and Software on the CD
Troubleshooting
Index
COMPUTERS/Spreadsheets
$49.99 US $59.99 CAN
Follow Mr. Spreadsheet’s formula for Excel success
Experts estimate that barely 10 percent of Excel
users understand how to make the most of work-
sheet formulas. If you already know your way around
Excel basics, “Mr. Spreadsheet” John Walkenbach
can help you master formulas to gain greater Excel
functionality.
First, he shows you exactly what a formula is, how
to create one, and what formulas can do. Then
you’ll learn about using functions in your formulas.
Finally you’ll explore specific types of formulas that
can help you on multiple levels, such as financial
and array formulas.
You’ll learn how to apply formulas to charts and
pivot tables, troubleshoot your formulas, develop
custom functions, and much more.
John Walkenbach, arguably the
foremost authority on Excel, has
written hundreds of articles and
created
award-winning
Power Utility Pak. His 50-plus
include Excel 2010 Power
Programming with VBA, John Walkenbach's Favorite
Excel 2010 Tips & Tricks, and the Excel 2010
Bible, all published by Wiley. Visit his popular
Spreadsheet Page at spreadsheetpage.com.
books
the
Let Mr. Spreadsheet
show you how to:
CD-ROM INCLUDES:
Workbook files for all examples
used in the book
The entire book in a search-
able PDF file
See Appendix D for complete system
requirements.
Master the new functionality in Excel 2010
Understand and use various lookup formulas
Create financial formulas for borrowing or investing
Work with formulas for conditional formatting
Develop custom worksheet functions using VBA
CD-ROM
CD-ROM
INCLUDED
INCLUDED
E
M
i
c
x
r
o
c
s
o
e
f
t
®
l
®
2
0
1
0
F
o
r
m
u
l
a
s
Walkenbach
®
Microsoft®
Microsoft®
Excel 2010
Formulas
John Walkenbach
Visit Mr. Spreadsheet’s Web site at spreadsheetpage.com
BONUS CD-ROM!
Includes all Excel workbook files used in the book,
plus the complete book in a searchable PDF file
01_475362-ffirs.indd iii
01_475362-ffirs.indd iii
4/14/10 9:13 PM
4/14/10 9:13 PM
Excel® 2010 Formulas
by John Walkenbach
01_475362-ffirs.indd i
01_475362-ffirs.indd i
4/14/10 9:13 PM
4/14/10 9:13 PM
Excel® 2010 Formulas
Published by
Wiley Publishing, Inc.
111 River Street
Hoboken, NJ 07030-5774
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-2
Manufactured in the United States of America
10 9 8 7 6 5 4 3 2 1
01_475362-ffirs.indd ii
01_475362-ffirs.indd ii
4/14/10 9:13 PM
4/14/10 9:13 PM
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.
01_475362-ffirs.indd iii
01_475362-ffirs.indd iii
4/14/10 9:13 PM
4/14/10 9:13 PM
Publisher’s Acknowledgments
We’re proud of this book; please send us your comments at http://dummies.custhelp.
com. For other comments, 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.
Some of the people who helped bring this book to market include the following:
Composition Services
Project Coordinator: Katherine Crocker
Layout and Graphics: Beth Brooks,
Jennifer Mayberry, Ronald G. Terry,
Erin Zeltner
Proofreaders: Laura Albert, Laura L. Bowman
Indexer: Christine Karpeles
Acquisitions, Editorial, and
Media Development
Project Editor: Susan B. Cohen
Acquisitions Editor: Katie Mohr
Copy Editor: Susan B. Cohen
Technical Editor: Niek Otten
Editorial Manager: Jodi Jensen
Media Development Assistant Project Manager:
Jenny Swisher
Media Development Associate Producer:
Marilyn Hummel
Editorial Assistant: Amanda Graham
Sr. Editorial Assistant: Cherie Case
Publishing and Editorial for Technology Dummies
Richard Swadley, Vice President and Executive Group Publisher
Andy Cummings, Vice President and Publisher
Mary Bednarek, Executive Acquisitions Director
Mary C. Corder, Editorial Director
Publishing for Consumer Dummies
Diane Graves Steele, Vice President and Publisher
Composition Services
Debbie Stailey, Director of Composition Services
01_475362-ffirs.indd iv
01_475362-ffirs.indd iv
4/14/10 9:13 PM
4/14/10 9:13 PM
Contents at a Glance
Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1
Part I: Basic Information
Chapter 1: Excel in a Nutshell . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11
Chapter 2: Basic Facts about Formulas . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 39
Chapter 3: Working with Names . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 65
Part II: Using Functions in Your Formulas
Chapter 4: Introducing Worksheet Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 103
Chapter 5: Manipulating Text . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 119
Chapter 6: Working with Dates and Times . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .143
Chapter 7: Counting and Summing Techniques . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 181
Chapter 8: Using Lookup Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .213
Chapter 9: Tables and Worksheet Databases . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 235
Chapter 10: Miscellaneous Calculations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 275
Part III: Financial Formulas
Chapter 11: Borrowing and Investing Formulas . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 293
Chapter 12: Discounting and Depreciation Formulas . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .317
Chapter 13: Financial Schedules . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .341
Part IV: Array Formulas
Chapter 14: Introducing Arrays . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 367
Chapter 15: Performing Magic with Array Formulas . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .391
Part V: Miscellaneous Formula Techniques
Chapter 16: Intentional Circular References . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .415
Chapter 17: Charting Techniques . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 429
Chapter 18: Pivot Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 473
Chapter 19: Conditional Formatting and Data Validation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .519
Chapter 20: Creating Megaformulas . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 555
Chapter 21: Tools and Methods for Debugging Formulas . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 573
Part VI: Developing Custom Worksheet Functions
Chapter 22: Introducing VBA . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 599
Chapter 23: Function Procedure Basics . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .613
Chapter 24: VBA Programming Concepts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 635
Chapter 25: VBA Custom Function Examples . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 669
02_475362-ftoc.indd v
02_475362-ftoc.indd v
4/14/10 9:14 PM
4/14/10 9:14 PM
vi
Part VII: Appendixes
Appendix A: Excel Function Reference . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .717
Appendix B: Using Custom Number Formats . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 733
Appendix C: Additional Excel Resources . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 753
Appendix D: What’s on the CD-ROM? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 759
Index . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 769
02_475362-ftoc.indd vi
02_475362-ftoc.indd vi
4/14/10 9:14 PM
4/14/10 9:14 PM
相关推荐
2023年江西萍乡中考道德与法治真题及答案.doc
2012年重庆南川中考生物真题及答案.doc
2013年江西师范大学地理学综合及文艺理论基础考研真题.doc
2020年四川甘孜小升初语文真题及答案I卷.doc
2020年注册岩土工程师专业基础考试真题及答案.doc
2023-2024学年福建省厦门市九年级上学期数学月考试题及答案.doc
2021-2022学年辽宁省沈阳市大东区九年级上学期语文期末试题及答案.doc
2022-2023学年北京东城区初三第一学期物理期末试卷及答案.doc
2018上半年江西教师资格初中地理学科知识与教学能力真题及答案.doc
2012年河北国家公务员申论考试真题及答案-省级.doc
2020-2021学年江苏省扬州市江都区邵樊片九年级上学期数学第一次质量检测试题及答案.doc
2022下半年黑龙江教师资格证中学综合素质真题及答案.doc
