دانلود کتاب Excel ® Formulas & Functions

فهرست مطالب :

Title Page Copyright Page Table of Contents Introduction About This Book Foolish Assumptions Icons Used in This Book Beyond the Book Where to Go from Here Part 1 Getting Started with Excel Formulas and Functions Chapter 1 Tapping Into Formula and Function Fundamentals Working with Excel Fundamentals Understanding workbooks and worksheets Introducing the Formulas tab Working with rows, columns, cells, ranges, and tables Formatting your data Getting help Gaining the Upper Hand on Formulas Entering your first formula Understanding references Copying formulas with the fill handle Assembling formulas the right way Using Functions in Formulas Looking at what goes into a function Arguing with a function Nesting functions 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 Selecting a function that takes no arguments Selecting a function that uses arguments Entering cells, ranges, named areas, and tables as function arguments Getting help in the Insert Function dialog box Using the Function Arguments dialog box to edit functions Directly Entering Formulas and Functions Entering formulas and functions in the Formula Bar Entering formulas and functions directly in worksheet cells 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 Getting parentheses to match Avoiding circular references Mending broken links Using the Formula Error Checker Auditing Formulas Watching the Watch Window Evaluating and Checking Errors Making an Error Behave the Way You Want Part 2 Doing the Math Chapter 5 Calculating Loan Payments and Interest Rates Understanding How Excel Handles Money Going with the cash flow Formatting for currency Choosing separators Figuring Loan Calculations Calculating the payment amount Calculating interest payments Calculating payments toward principal Calculating the number of payments Calculating the number of payments with PDURATION Calculating the interest rate Calculating the principal Chapter 6 Appreciating What You’ll Get, Depreciating What You’ve Got Looking into the Future Depreciating the Finer Things in Life Calculating straight-line depreciation Creating an accelerated depreciation schedule Creating an even faster accelerated depreciation schedule Calculating a midyear depreciation schedule Measuring Your Internals Chapter 7 Using Basic Math Functions Adding It All Together with the SUM Function Rounding Out Your Knowledge Just plain old rounding Rounding in one direction 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 The all-purpose RAND function Precise randomness with RANDBETWEEN Ordering Items Combining Raising Numbers to New Heights Multiplying Multiple Numbers Using What Remains with the MOD Function Summing Things Up Using SUBTOTAL Using SUMPRODUCT Using SUMIF and SUMIFS Getting an Angle on Trigonometry Three basic trigonometry functions Degrees and radians Part 3 Solving with Statistics Chapter 9 Throwing Statistics a Curve Getting Stuck in the Middle with AVERAGE, MEDIAN, and MODE Deviating from the Middle Measuring variance Analyzing deviations Looking for normal distribution Skewing from the norm Comparing data sets Analyzing Data with Percentiles and Bins QUARTILE.INC and QUARTILE.EXC PERCENTILE.INC and PERCENTILE.EXC RANK PERCENTRANK FREQUENCY MIN and MAX LARGE and SMALL Going for the Count COUNT and COUNTA COUNTIF Chapter 10 Using Significance Tests Testing to the T Comparing Results with an Estimate Chapter 11 Rolling the Dice on Predictions and Probability Modeling Linear model Exponential model Getting It Straight: Using SLOPE and INTERCEPT to Describe Linear Data What’s Ahead: Using FORECAST, TREND, and GROWTH to Make Predictions FORECAST TREND GROWTH Using NORM.DIST and POISSON.DIST to Determine Probabilities NORM.DIST POISSON.DIST Part 4 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 Isolating the day Isolating the month Isolating the year Converting a Date from Text Finding Out What TODAY Is Counting the days until your birthday Counting your age in days Determining the Day of the Week Working with Workdays Determining workdays in a range of dates Workdays in the future 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 Isolating the hour Isolating the minute Isolating the 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 NOT AND and OR XOR Finding Where the Data Is ADDRESS INDIRECT ROW, ROWS, COLUMN, and COLUMNS OFFSET Looking It Up HLOOKUP and VLOOKUP XLOOKUP MATCH and INDEX FORMULATEXT NUMBERVALUE 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 ISERR, ISNA, and ISERROR ISBLANK, ISNONTEXT, ISTEXT, and ISNUMBER Getting to Know Your Type Chapter 16 Writing Home about Text Functions Breaking Apart Text Bearing to the LEFT Swinging to the RIGHT Staying in the MIDdle Finding the long of it with LEN Putting Text Together with CONCATENATE Changing Text Making money Turning numbers into text Repeating text Swapping text Giving text a trim Making a case Comparing, Finding, and Measuring Text Going for perfection with EXACT Finding and searching Chapter 17 Playing Records with Database Functions Putting Your Data into a Database Structure Working with Database Functions Establishing your database Establishing the criteria area 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 5 The Part of Tens Chapter 18 Ten Tips 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 Ways to Get Fancy with Excel Calculating Data from Multiple Sheets Getting Data from the Internet Determining the Needed Number Removing Duplicates Getting to the Last Row of Your Data Freezing Panes Splitting a Worksheet Filling Cells Adding Notes to Cells Getting More Information about a Workbook or Worksheet Chapter 20 Ten 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 Find the LENgth of Your Text Just in CASE Index EULA