Excel Formulas and Functions For Dummies
Реклама. ООО «ЛитРес», ИНН: 7719571260.
Оглавление
Bluttman Ken. Excel Formulas and Functions For Dummies
Introduction
About This Book
Foolish Assumptions
How to Use This Book
Icons Used in This Book
Beyond the Book
Where to Go from Here
Part I. Getting Started with Formulas and Functions
Chapter 1. Tapping Into Formula and Function Fundamentals
Working with Excel Fundamentals
Gaining the Upper Hand on Formulas
Using Functions in Formulas
Chapter 2. Saving Time with Function Tools
Getting Familiar with the Insert Function Dialog Box
Finding the Correct Function
Entering Functions Using the Insert Function Dialog Box
Directly Entering Formulas and Functions
Chapter 3. Saying “Array!” for Formulas and Functions
Discovering Arrays
Using Arrays in Formulas
Working with Functions That Return Arrays
Chapter 4. Fixing Formula Boo-Boos
Catching Errors As You Enter Them
Auditing Formulas
Watching the Watch Window
Evaluating and Checking Errors
Making an Error Behave the Way You Want
Part II. Doing the Math
Chapter 5. Calculating Loan Payments and Interest Rates
Understanding How Excel Handles Money
Figuring Loan Calculations
Chapter 6. Appreciating What You’ll Get, Depreciating What You’ve Got
Looking into the Future
Depreciating the Finer Things in Life
Measuring Your Internals
Chapter 7. Using Basic Math Functions
Adding It All Together with the SUM Function
Rounding Out Your Knowledge
Leaving All Decimals Behind with INT
Leaving Some Decimals Behind with TRUNC
Looking for a Sign
Ignoring Signs
Chapter 8. Advancing Your Math
Using PI to Calculate Circumference and Diameter
Generating and Using Random Numbers
Ordering Items
Combining
Raising Numbers to New Heights
Multiplying Multiple Numbers
Using What Remains with the MOD Function
Summing Things Up
Getting an Angle on Trigonometry
Part III. Solving with Statistics
Chapter 9. Throwing Statistics a Curve
Getting Stuck in the Middle with AVERAGE, MEDIAN, and MODE
Deviating from the Middle
Analyzing Data with Percentiles and Bins
Going for the Count
Chapter 10. Using Significance Tests
Testing to the T
Comparing Results with an Estimate
Chapter 11. Rolling the Dice on Predictions and Probability
Modeling
Getting It Straight: Using SLOPE and INTERCEPT to Describe Linear Data
What’s in the Future: Using FORECAST, TREND, and GROWTH to Make Predictions
Using NORM.DIST and POISSON.DIST to Determine Probabilities
Part IV. Dancing with Data
Chapter 12. Dressing Up for Date Functions
Understanding How Excel Handles Dates
Formatting Dates
Making a Date with DATE
Breaking a Date with DAY, MONTH, and YEAR
Converting a Date from Text
Finding Out What TODAY Is
Determining the Day of the Week
Working with Workdays
Calculating Time between Two Dates with the DATEDIF Function
Chapter 13. Keeping Well-Timed Functions
Understanding How Excel Handles Time
Formatting Time
Keeping TIME
Converting Text to Time with TIMEVALUE
Deconstructing Time with HOUR, MINUTE, and SECOND
Finding the Time NOW
Calculating Elapsed Time over Days
Chapter 14. Using Lookup, Logical, and Reference Functions
Testing on One Condition
Choosing the Right Value
Let's Be Logical
Finding Where It Is
Looking It Up
Chapter 15. Digging Up the Facts
Getting Informed with the CELL Function
Getting Information about Excel and Your Computer System
Finding What IS and What IS Not
Getting to Know Your Type
Chapter 16. Writing Home about Text Functions
Breaking Apart Text
Putting Text Together with CONCATENATE
Changing Text
Comparing, Finding, and Measuring Text
Chapter 17. Playing Records with Database Functions
Putting Your Data into a Database Structure
Working with Database Functions
Fine-Tuning Criteria with AND and OR
Adding Only What Matters with DSUM
Going for the Middle with DAVERAGE
Counting Only What Matters with DCOUNT
Finding Highest and Lowest with DMIN and DMAX
Finding Duplicate Values with DGET
Being Productive with DPRODUCT
Part V. The Part of Tens
Chapter 18. Ten T ips for Working with Formulas
Master Operator Precedence
Display Formulas
Fix Formulas
Use Absolute References
Turn Calc On/Turn Calc Off
Use Named Areas
Use Formula Auditing
Use Conditional Formatting
Use Data Validation
Create Your Own Functions
Chapter 19. Ten Functions You Really Should Know
SUM
AVERAGE
COUNT
INT and ROUND
IF
NOW and TODAY
HLOOKUP and VLOOKUP
ISNUMBER
MIN and MAX
SUMIF and COUNTIF
Chapter 20. Some Really Cool Functions
Work with Hexadecimal, Octal, Decimal, and Binary Numbers
Convert Units of Measurement
Find the Greatest Common Divisor and the Least Common Multiple
Easily Generate a Random Number
Convert to Roman Numerals
Factor in a Factorial
Determine Part of a Year with YEARFRAC
Find the Data TYPE
About the Author
Author’s Acknowledgments
Отрывок из книги
Excel worksheets are used in many walks of life: business, education, home finances, and even hobbies (keeping track of your baseball-card collection). In my house, we use Excel for a lot, from our taxes (boring!) to our ever-growing recipe collection (yummy!). Often, I use Excel in place of a calculator. After all, Excel is like a calculator on steroids!
In the workplace, Excel is one of the most commonly used analysis and reporting tools. Financial statements, sales reports, inventory, project scheduling, customer activity – so much of this stuff is kept in Excel. The program’s capability to manipulate and give feedback about the data makes it attractive. Excel’s flexibility in storing and presenting data is like magic.
.....
Try another. This time you create a formula that adds the value of two cells:
Figure 1-14: Entering a formula that references cells.
.....