- ホーム
- > 洋書
- > 英文書
- > Computer / Spreadsheets
Full Description
Master core Excel 2013 tools for building powerful, reliable spreadsheets! Excel expert Paul McFedries shows how to use Excel 2013's core features to solve problems and get the answers you need. Using real-world examples, McFedries helps you get the absolute most out of features and improvements ranging from FlashFill to Excel's newest functions. Along the way, you discover the fastest, best ways to handle essential day-to-day tasks ranging from generating account numbers to projecting the impact of inflation.Becoming an Excel expert has never been easier! You'll find crystal-clear instructions; insider insights; even complete step-by-step projects for building timesheets, projecting cash flow, aging receivables, analyzing defects, and more..* Quickly create powerful spreadsheets with FlashFill* Use conditional formatting to instantly reveal anomalies, problems, or opportunities* Analyze your data with standard tables and PivotTables* Use complex criteria to filter data in lists* Understand correlations between data* Perform sophisticated what-if analyses* Use regression to track trends and make forecasts* Build loan, investment, and discount formulas* Validate data, troubleshoot problems, and build more accurate, trustworthy spreadsheetsAbout MrExcel Library: Every book in the MrExcel Library pinpoints a specific set of crucial Excel tasks and presents focused skills and examples for performing them rapidly and effectively. Selected by Bill Jelen, Microsoft Excel MVP and mastermind behind the leading Excel solutions website MrExcel.com, these books will* Dramatically increase your productivity-saving you 50 hours a year or more* Present proven, creative strategies for solving real-world problems * Show you how to get great results, no matter how much data you have* Help you avoid critical mistakes that even experienced users makeCATEGORY: SpreadsheetsCOVERS: Microsoft Office Excel 2013
Contents
IntroductionPart I: Mastering Excel Ranges and FormulasChapter 1 Getting the Most Out of RangesAdvanced Range-Selection Techniques Mouse Range-Selection Tricks Keyboard Range-Selection Tricks Working with 3D Ranges Selecting a Range Using Go ToUsing the Go To Special Dialog Box Data Entry in a Range Filling a RangeUsing the Fill Handle Flash-Filling a RangeCreating a SeriesAdvanced Range Copying Copying Selected Cell Attributes Combining Two Ranges Arithmetically Transposing Rows and Columns Clearing a RangeApplying Conditional Formatting to a Range Creating Highlight Cells Rules Creating Top/Bottom Rules Adding Data Bars Adding Color Scales Adding Icon Sets From Here Chapter 2 Using Range NamesDefining a Range Name Working with the Name Box Using the New Name Dialog Box Changing the Scope to Define Sheet-Level Names Using Worksheet Text to Define NamesNaming ConstantsWorking with Range Names Referring to a Range NameWorking with Name AutoCompleteNavigating Using Range NamesPasting a List of Range Names in a WorksheetDisplaying the Name Manager Filtering NamesEditing a Range Name's Coordinates Adjusting Range Name Coordinates Automatically Changing a Range NameDeleting a Range Name Using Names with the Intersection OperatorFrom HereChapter 3 Building Basic FormulasUnderstanding Formula Basics Formula Limits in Excel 2013Entering and Editing FormulasUsing Arithmetic FormulasUsing Comparison FormulasUsing Text FormulasUsing Reference FormulasUnderstanding Operator Precedence The Order of Precedence Controlling the Order of Precedence Controlling Worksheet CalculationCopying and Moving Formulas Understanding Relative Reference Format Understanding Absolute Reference FormatCopying a Formula Without Adjusting Relative References Displaying Worksheet FormulasDisplaying All Worksheet Formulas Displaying a Cell's Formula Using FORMULATEXT()Converting a Formula to a ValueWorking with Range Names in Formulas Pasting a Name into a FormulaApplying Names to FormulasNaming FormulasWorking with Links in Formulas Understanding External References Updating Links Changing the Link Source Formatting Numbers, Dates, and Times Numeric Display FormatsDate and Time Display Formats Deleting Custom FormatsFrom HereChapter 4 Creating Advanced Formulas Working with Arrays Using Array FormulasUsing Array ConstantsFunctions That Use or Return Arrays Using Iteration and Circular ReferencesConsolidating Multisheet DataConsolidating by PositionConsolidating by CategoryApplying Data-Validation Rules to Cells Using Dialog Box Controls on a WorksheetDisplaying the Developer TabUsing the Form ControlsAdding a Control to a Worksheet Linking a Control to a Cell ValueUnderstanding the Worksheet Controls From HereChapter 5 Troubleshooting Formulas Understanding Excel's Error Values #DIV/0!#N/A #NAME?Case Study: Avoiding #NAME? Errors When Deleting Range Names#NULL! #NUM! #REF! #VALUE!Fixing Other Formula Errors Missing or Mismatched Parentheses Erroneous Formula Results Fixing Circular References Handling Formula Errors with IFERROR()Using the Formula Error CheckerChoosing an Error ActionSetting Error Checker Options Auditing a WorksheetUnderstanding Auditing Tracing Cell PrecedentsTracing Cell DependentsTracing Cell ErrorsRemoving Tracer Arrows Evaluating Formulas Watching Cell Values From Here Part II: Harnessing the Power of FunctionsChapter 6 Understanding Functions About Excel's Functions The Structure of a Function Typing a Function into a Formula Using the Insert Function FeatureLoading the Analysis ToolPakFrom HereChapter 7 Working with Text Functions Excel's Text Functions Working with Characters and Codes The CHAR() FunctionThe CODE() FunctionConverting TextThe LOWER() Function The UPPER() Function The PROPER() Function The DECIMAL() Function The NUMBERVALUE() Function Formatting Text The DOLLAR() Function The FIXED() Function The TEXT() Function Displaying When a Workbook Was Last Updated Manipulating Text Removing Unwanted Characters from a String The TRIM() Function The CLEAN() FunctionThe REPT() Function: Repeating a Character Padding a CellBuilding Text Charts Extracting a SubstringThe LEFT() FunctionThe RIGHT() Function The MID() FunctionConverting Text to Sentence Case A Date-Conversion FormulaCase Study: Generating Account Numbers Searching for Substrings The FIND() and SEARCH() Functions Extracting a First Name or Last Name Extracting First Name, Last Name, and Middle Initial Determining the Column Letter Substituting One Substring for AnotherThe REPLACE() Function The SUBSTITUTE() Function Removing a Character from a String Removing Two Different Characters from a String Case Study: Generating Account Numbers, Part IIRemoving Line FeedsFrom Here Chapter 8 Working with Logical and Information Functions Adding Intelligence with Logical Functions Using the IF() Function Performing Multiple Logical Tests Combining Logical Functions with Arrays Case Study: Building an Accounts Receivable Aging Worksheet Calculating a Smarter Due Date Aging Overdue Invoices Getting Data with Information Functions The CELL() Function The ERROR.TYPE() Function The INFO() Function The SHEET() and SHEETS() Functions The IS Functions From Here Chapter 9 Working with Lookup Functions Understanding Lookup Tables The CHOOSE() Function Determining the Name of the Day of the Week Determining the Month of the Fiscal Year Calculating Weighted Questionnaire Results Integrating CHOOSE() and Worksheet Option Buttons Looking Up Values in Tables The VLOOKUP() Function The HLOOKUP() FunctionReturning a Customer Discount Rate with a Range Lookup Returning a Tax Rate with a Range Lookup Finding Exact MatchesAdvanced Lookup Operations From HereChapter 10 Working with Date and Time FunctionsHow Excel Deals with Dates and Times Entering Dates and Times Excel and Two-Digit YearsUsing Excel's Date Functions Returning a Date Returning Parts of a Date Calculating the Difference Between Two Dates Using Excel's Time Functions Returning a Time Returning Parts of a Time Calculating the Difference Between Two Times Case Study: Building an Employee Time SheetEntering the Time Sheet Data Calculating the Daily Hours Worked Calculating the Weekly Hours WorkedCalculating the Weekly Pay From Here Chapter 11 Working with Math Functions Understanding Excel's Rounding Functions The ROUND() Function The MROUND() Function The ROUNDDOWN() and ROUNDUP() Functions The CEILING.MATH() and FLOOR.MATH() Functions Determining the Fiscal Quarter in Which a Date Falls Calculating Easter Dates The EVEN() and ODD() Functions The INT() and TRUNC() FunctionsUsing Rounding to Prevent Calculation Errors Setting Price PointsCase Study: Rounding Billable Time Summing ValuesThe SUM() FunctionCalculating Cumulative Totals Summing Only the Positive or Negative Values in a Range The MOD() Function A Better Formula for Time Differences Summing Every nth Row Determining Whether a Year Is a Leap Year Creating Ledger Shading Generating Random NumbersThe RAND() Function The RANDBETWEEN() Function From Here Chapter 12 Working with Statistical Functions Understanding Descriptive Statistics Counting Items with the COUNT() Function Calculating AveragesThe AVERAGE() Function The MEDIAN() Function The MODE() Function Calculating the Weighted Mean Calculating Extreme Values The MAX() and MIN() Functions The LARGE() and SMALL() Functions Performing Calculations on the Top k Values Performing Calculations on the Bottom k Values Calculating Measures of Variation Calculating the Range Calculating the Variance Calculating the Standard Deviation Working with Frequency Distributions The FREQUENCY() Function Understanding the Normal Distribution and the NORMDIST() Function The Shape of the Curve I: The SKEW() Function The Shape of the Curve II: The KURT() FunctionUsing the Analysis ToolPak Statistical ToolsUsing the Descriptive Statistics Tool Determining the Correlation Between Data Working with Histograms Using the Random Number Generation Tool Working with Rank and PercentileFrom Here Part III: Building Business ModelsChapter 13 Analyzing Data with Tables Converting a Range to a Table Basic Table Operations Sorting a Table Performing a More Complex Sort Sorting a Table in Natural Order Sorting on Part of a Field Sorting Without ArticlesFiltering Table Data Using Filter Lists to Filter a Table Using Complex Criteria to Filter a Table Entering Computed Criteria Copying Filtered Data to a Different Range Referencing Tables in Formulas Using Table Specifiers Entering Table Formulas Excel's Table Functions About Table Functions Table Functions That Don't Require a Criteria Range Table Functions That Accept Multiple Criteria Table Functions That Require a Criteria Range Case Study: Applying Statistical Table Functions to a Defects Database From Here Chapter 14 Analyzing Data with PivotTablesWhat Are PivotTables? How PivotTables Work Some PivotTable Terms Building PivotTablesBuilding a PivotTable from a Table or Range Building a PivotTable from an External Database Working with and Customizing a PivotTable Working with PivotTable Subtotals Hiding PivotTable Grand Totals Hiding PivotTable Subtotals Customizing the Subtotal Calculation Changing the Data Field Summary Calculation Using a Difference Summary Calculation Using a Percentage Summary Calculation Using a Running Total Summary Calculation Using an Index Summary Calculation Creating Custom PivotTable Calculations Creating a Calculated Field Creating a Calculated Item Case Study: Using PivotTable Results in a Worksheet Formula From Here Chapter 15 Using Excel's Business-Modeling Tools Using What-If Analysis Setting Up a One-Input Data Table Adding More Formulas to the Input TableSetting Up a Two-Input Table Editing a Data TableWorking with Goal Seek How Does Goal Seek Work? Running Goal Seek Optimizing Product Margin A Note About Goal Seek's Approximations Performing a Break-Even Analysis Solving Algebraic Equations Working with Scenarios Understanding Scenarios Setting Up Your Worksheet for Scenarios Adding a Scenario Displaying a Scenario Editing a Scenario Merging Scenarios Generating a Summary Report Deleting a Scenario From Here Chapter 16 Using Regression to Track Trends and Make Forecasts Choosing a Regression Method Using Simple Regression on Linear Data Analyzing Trends Using Best-Fit Lines Making Forecasts Case Study: Trend Analysis and Forecasting for a Seasonal Sales Model About the Forecast Workbook Calculating a Normal Trend Calculating the Forecast TrendCalculating the Seasonal Trend Computing the Monthly Seasonal Indexes Calculating the Deseasoned Monthly Values Calculating the Deseasoned Trend Calculating the Reseasoned Trend Calculating the Seasonal ForecastWorking with Quarterly Data Using Simple Regression on Nonlinear Data Working with an Exponential TrendWorking with a Logarithmic Trend Working with a Power Trend Using Polynomial Regression Analysis Using Multiple Regression Analysis From Here Chapter 17 Solving Complex Problems with Solver Some Background on Solver The Advantages of Solver When Do You Use Solver? Loading Solver Using Solver Adding Constraints Saving a Solution as a Scenario Setting Other Solver Options Selecting the Method Solver UsesControlling How Solver Works Working with Solver Models Making Sense of Solver's Messages Case Study: Solving the Transportation Problem Displaying Solver's Reports The Answer Report The Sensitivity Report The Limits Report From Here Part IV: Building Financial FormulasChapter 18 Building Loan Formulas Understanding the Time Value of Money Calculating the Loan Payment Loan Payment Analysis Working with a Balloon Loan Calculating Interest Costs, Part I Calculating the Principal and Interest Calculating Interest Costs, Part 2 Calculating Cumulative Principal and Interest Building a Loan Amortization Schedule Building a Fixed-Rate Amortization Schedule Building a Dynamic Amortization Schedule Calculating the Term of the Loan Calculating the Interest Rate Required for a Loan Calculating How Much You Can Borrow Case Study: Working with Mortgages Building a Variable-Rate Mortgage Amortization Schedule Allowing for Mortgage Principal Paydowns From Here Chapter 19 Building Investment Formulas Working with Interest Rates Understanding Compound Interest Nominal Versus Effective Interest Converting Between the Nominal Rate and the Effective Rate Calculating the Future Value The Future Value of a Lump Sum The Future Value of a Series of Deposits The Future Value of a Lump Sum Plus Deposits Working Toward an Investment Goal Calculating the Required Interest Rate Calculating the Required Number of Periods Calculating the Required Regular Deposit Calculating the Required Initial DepositCalculating the Future Value with Varying Interest Rates Case Study: Building an Investment Schedule From Here Chapter 20 Building Discount Formulas Calculating the Present Value Taking Inflation into Account Calculating Present Value Using PV() Income Investing Versus Purchasing a Rental Property Buying Versus Leasing Discounting Cash Flows Calculating the Net Present Value Calculating Net Present Value Using NPV() Net Present Value with Varying Cash Flows Net Present Value with Nonperiodic Cash Flows Calculating the Payback Period Simple Undiscounted Payback Period Exact Undiscounted Payback Point Discounted Payback Period Calculating the Internal Rate of Return Using the IRR() Function Calculating the Internal Rate of Return for Nonperiodic Cash Flows Calculating Multiple Internal Rates of Return Case Study: Publishing a Book Per-Unit Constants Operating Costs and Sales Cash Flow Cash-Flow Analysis From Here 9780789748676 TOC 1/25/2013
![Ueber Die Sprache Der Altenglischen Story of Genesis and Exodus [By Cædmon].](../images/goods/ar/work/imgdatag/11410/1141015250.JPG)
