Cantitate/Preț
Produs

Formulas and Functions with Microsoft Office Excel 2007 (Business Solutions)

De (autor)
Notă GoodReads:
en Limba Engleză Carte Paperback – March 2007
“If you’ve never quite grasped formulas and functions, Paul McFedries will radically expand your understanding and use of Excel. And if you’re already an expert and you’re moving up to Excel 2007, this book will quickly show you features you’ve only dreamed of until now...”
—Thomas ‘Duffbert’ Duff, Duffbert’s Random Musings, http://www.twduff.com
 
Develop your Microsoft Excel expertise instantly with proven techniques
  • Master Excel Ranges
  • Create Powerful Arrays
  • Troubleshoot Formula Problems
  • Validate Worksheet Data
  • Perform What-If Analysis
  • Model Your Business
  • Track Trends and Make
  • Forecasts
  • Analyze Data
  • Find Optimal Solutions
  • Build Dynamic Loan
  • Schedules 
Most Microsoft® Excel users learn only a small percentage of the program’s features. They know they could get more out of Excel if they could just get a leg up on building formulas and using functions. Unfortunately, this side of Excel appears complex and intimidating to the uninitiated—shrouded in the mysteries of mathematics, finance, and impenetrable spreadsheet jargon.
Sound familiar? If you’re a businessperson who needs to use Excel as an everyday part of your job, then you’ve come to the right book.  Formulas and Functions with Microsoft® Office Excel 2007 demystifies worksheet formulas and presents the most useful Excel functions in an accessible,  jargon-free way.  This book not only takes you through Excel’s intermediate and advanced formula-building features, it also tells you why these features are useful to you and shows you how to use them in everyday situations. Throughout the book you’ll find no-nonsense, step-by-step tutorials and lots of practical examples aimed directly at business users.
 
     •    Focuses like a laser on the four technologies that you must master to get the most out of Excel: ranges, formulas, functions, and data analysis tools.
     •    Shuns spreadsheet theory in favor of practical know-how that you can put to use right away.
     •    Provides numerous real-world examples and techniques to help you learn and understand the importance of each section.
 



Introduction
1    Getting the Most Out of Ranges
2    Using Range Names
3    Building Basic Formulas
4    Creating Advanced Formulas
5    Troubleshooting Formulas
II    Harnessing the Power of Functions   
6    Understanding Functions
7    Working with Text Functions
8    Working with Logical and Information Functions
9    Working with Lookup Functions
10  Working with Date and Time Functions
11  Working with Math Functions
12  Working with Statistical Functions
III    Building Business Models   
13  Analyzing Data with Tables
14  Analyzing Data with PivotTables
15  Using Excel’s Business-Modeling Tools
16  Using Regression to Track Trends and Make Forecasts
17  Solving Complex Problems with Solver
IV    Building Financial Formulas   
18   Building Loan Formulas
19   Building Investment Formulas
20   Building Discount Formulas

Paul McFedries is well-known as a teacher of Windows and Office, particularly Excel, and is the president of Logophilia Limited, a technical writing company. Paul has been working with spreadsheets for more than 20 years and has been developing Excel solutions since the late 1980s. Now primarily a writer, Paul has written more than 50 books that have sold more than three million copies worldwide. These books include Microsoft Office Access 2007 Forms, Reports, and Queries; Tricks of the Microsoft Office 2007 Gurus (all from Que); and Microsoft Windows Vista Unleashed (Sams).
 
Category  Office Productivity Suite
Covers    Microsoft Office Excel 2007
User Level         Intermediate - Advanced
 
Citește tot Restrânge

Din seria Business Solutions

Preț: 16780 lei

Preț vechi: 20975 lei
-20%

Puncte Express: 252

Preț estimativ în valută:
3429 4034$ 3066£

Carte disponibilă

Livrare economică 10-24 octombrie
Livrare express 05-12 octombrie pentru 2256 lei

Preluare comenzi: 021 569.72.76

Specificații

ISBN-13: 9780789736680
ISBN-10: 0789736683
Pagini: 523
Dimensiuni: 171 x 215 x 31 mm
Greutate: 0.86 kg
Ediția: 1
Editura: Que
Seria Business Solutions

Locul publicării: Indianapolis, United States

Cuprins

Introduction
    What’s in the Book
    This Book’s Special Features
    I    Mastering Excel Ranges and Formulas
1    Getting the Most Out of Ranges
    Advanced Range-Selection Techniques
        Mouse Range-Selection Tricks
        Keyboard Range-Selection Tricks
        Working with 3D Ranges
        Selecting a Range Using Go To
        Using the Go To Special Dialog Box
    Data Entry in a Range
    Filling a Range
    Using the Fill Handle
        Using AutoFill to Create Text and Numeric Series
        Creating a Custom AutoFill List
        Filling a Range
    Creating a Series
    Advanced Range Copying
        Copying Selected Cell Attributes
        Combining the Source and Destination Arithmetically
        Transposing Rows and Columns
    Clearing a Range
    Applying Conditional Formatting to a Range
        Creating Highlight Cells Rules
        Creating Top/Bottom Rules
        Adding Data Bars
        Adding Color Scales
        Adding Icon Sets
        From Here
2    Using Range Names
    Defining 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 Names
        Naming Constants
    Working with Range Names
        Referring to a Range Name
        Working with Name AutoComplete
        Navigating Using Range Names
        Pasting a List of Range Names in a Worksheet
        Displaying the Name Manager
        Filtering Names
        Editing a Range Name’s Coordinates
        Adjusting Range Name Coordinates Automatically
        Changing a Range Name
        Deleting a Range Name
        Using Names with the Intersection Operator
        From Here
3    Building Basic Formulas
    Understanding Formula Basics
        Formula Limits in Excel 2007
        Entering and Editing Formulas
        Using Arithmetic Formulas
        Using Comparison Formulas
        Using Text Formulas
        Using Reference Formulas
    Understanding Operator Precedence
        The Order of Precedence
        Controlling the Order of Precedence
    Controlling Worksheet Calculation
    Copying and Moving Formulas
        Understanding Relative Reference Format
        Understanding Absolute Reference Format
        Copying a Formula Without Adjusting Relative References
    Displaying Worksheet Formulas
    Converting a Formula to a Value
    Working with Range Names in Formulas
        Pasting a Name into a Formula
        Applying Names to Formulas
        Naming Formulas
    Working with Links in Formulas
        Understanding External References
        Updating Links
        Changing the Link Source
    Formatting Numbers, Dates, and Times
        Numeric Display Formats
        Date and Time Display Formats
        Deleting Custom Formats
        From Here
4    Creating Advanced Formulas
    Working with Arrays
        Using Array Formulas
        Using Array Constants
        Functions That Use or Return Arrays
    Using Iteration and Circular References
    Consolidating Multisheet Data
        Consolidating by Position
        Consolidating by Category
    Applying Data-Validation Rules to Cells
    Using Dialog Box Controls on a Worksheet
        Using the Form Controls
        Adding a Control to a Worksheet
        Linking a Control to a Cell Value
        Understanding the Worksheet Controls
        From Here
5    Troubleshooting Formulas
    Understanding Excel’s Error Values
        #DIV/0!
        #N/A
        #NAME?
        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 Checker
        Choosing an Error Action
        Setting Error Checker Options
    Auditing a Worksheet
        Understanding Auditing
        Tracing Cell Precedents
        Tracing Cell Dependents
        Tracing Cell Errors
        Removing Tracer Arrows
        Evaluating Formulas
        Watching Cell Values
        From Here
    II    Harnessing the Power of Functions   
6    Understanding Functions
    About Excel’s Functions
    The Structure of a Function
    Typing a Function into a Formula
    Using the Insert Function Feature
    Loading the Analysis ToolPak
        From Here
7    Working with Text Functions
    Excel’s Text Functions
    Working with Characters and Codes
        The CHAR() Function
        The CODE() Function
    Converting Text
        The LOWER() Function
        The UPPER() Function
        The PROPER() 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() Function
        The REPT() Function: Repeating a Character
        Padding a Cell
        Building Text Charts
    Extracting a Substring
        The LEFT() Function
        The RIGHT() Function
        The MID() Function
        Converting Text to Sentence Case
        A Date-Conversion Formula
    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 Another
        The REPLACE() Function
        The SUBSTITUTE() Function
        Removing a Character from a String
        Removing Two Different Characters from a String
        Removing Line Feeds
    Generating Account Numbers, Part 2
        From Here
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
    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 IS Functions
        From Here
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() Function
        Returning a Customer Discount Rate with a Range Lookup
        Returning a Tax Rate with a Range Lookup
        Finding Exact Matches
        Advanced Lookup Operations
        From Here
10    Working with Date and Time Functions
    How Excel Deals with Dates and Times
        Entering Dates and Times
        Excel and Two-Digit Years
    Using 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
    Building an Employee Time Sheet
        From Here
11    Working with Math Functions
    Understanding Excel’s Rounding Functions
        The ROUND() Function
        The MROUND() Function
        The ROUNDDOWN() and ROUNDUP() Functions
        The CEILING() and FLOOR() Functions
        Determining the Fiscal Quarter in Which a Date Falls
        Calculating Easter Dates
        The EVEN() and ODD() Functions
        The INT() and TRUNC() Functions
        Using Rounding to Prevent Calculation Errors
        Setting Price Points
    Rounding Billable Time
    Summing Values
        The SUM() Function
        Calculating 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 Numbers
        The RAND() Function
        The RANDBETWEEN() Function
        From Here
12    Working with Statistical Functions
    Understanding Descriptive Statistics
    Counting Items with the COUNT() Function
    Calculating Averages
        The 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 with the VAR() Function
        Calculating the Standard Deviation with the STDEVP() and STDEV() Functions
    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() Function
    Using the Analysis ToolPak Statistical Tools
        Using the Descriptive Statistics Tool
        Determining the Correlation Between Data
        Working with Histograms
        Using the Random Number Generation Tool
        Working with Rank and Percentile
        From Here
    III    Building Business Models   
13    Analyzing Data with Tables
    Converting a Range to a Table
    Basic Table Operations
    Sorting a Table
        Sorting a Table in Natural Order
        Sorting on Part of a Field
        Sorting Without Articles
    Filtering 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
        Applying Statistical Table Functions to a Defects Database
        From Here
14    Analyzing Data with PivotTables
    What Are PivotTables?
        How PivotTables Work
        Some PivotTable Terms
    Building PivotTables
        Building 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
    Budgeting with Calculated Items
    Using PivotTable Results in a Worksheet Formula
        From Here
15    Using Excel’s Business-Modeling Tools
    Using What-If Analysis
        Setting Up a One-Input Data Table
        Adding More Formulas to the Input Table
        Setting Up a Two-Input Table
        Editing a Data Table
    Working 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
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
    Trend Analysis and Forecasting for a Seasonal Sales Model
    Using Simple Regression on Nonlinear Data
        Working with an Exponential Trend
        Working with a Logarithmic Trend
            Working with a Power Trend
        Using Polynomial Regression Analysis
    Using Multiple Regression Analysis
        From Here
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
        Controlling Solver
        Selecting the Method Solver Uses
        Working with Solver Models
    Making Sense of Solver’s Messages
    Solving the Transportation Problem
    Displaying Solver’s Reports
        The Answer Report
        The Sensitivity Report
        The Limits Report
        From Here
    IV    Building Financial Formulas   
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
    Working with Mortgages
        Building a Variable-Rate Mortgage Amortization Schedule
        Allowing for Mortgage Principal Paydowns
        From Here
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 Deposit
        Calculating the Future Value with Varying Interest Rates
    Building an Investment Schedule
        From Here
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
    Publishing a Book
        From Here
0789736683, TOC, 2/19/2007

Notă biografică

Paul McFedries is the president of Logophilia Limited, a technical writing company. Now primarily a writer, Paul has worked as a programmer, consultant, spreadsheet developer, and website developer. He has written more than 50 books that have sold more than three million copies worldwide. These books include Access 2007 Forms, Reports, and Queries (Que, 2007), Tricks of the Microsoft Office 2007 Gurus (Que, 2007), VBA for the 2007 Microsoft Office System (Que, 2007), and Windows Vista Unleashed (Sams, 2006).

Textul de pe ultima copertă

"If you've never quite grasped formulas and functions, Paul McFedries will radically expand your understanding and use of Excel. And if you're already an expert and you're moving up to Excel 2007, this book will quickly show you features you've only dreamed of until now..." -Thomas 'Duffbert' Duff, Duffbert's Random Musings, http: //www.twduff.com Develop your Microsoft Excel expertise instantly with proven techniques Master Excel Ranges Create Powerful Arrays Troubleshoot Formula Problems Validate Worksheet Data Perform What-If Analysis Model Your Business Track Trends and Make Forecasts Analyze Data Find Optimal Solutions Build Dynamic Loan Schedules Most Microsoft(R) Excel users learn only a small percentage of the program's features. They know they could get more out of Excel if they could just get a leg up on building formulas and using functions. Unfortunately, this side of Excel appears complex and intimidating to the uninitiated-shrouded in the mysteries of mathematics, finance, and impenetrable spreadsheet jargon. Sound familiar? If you're a businessperson who needs to use Excel as an everyday part of your job, then you've come to the right book. Formulas and Functions with Microsoft(R) Office Excel 2007 demystifies worksheet formulas and presents the most useful Excel functions in an accessible, jargon-free way. This book not only takes you through Excel's intermediate and advanced formula-building features, it also tells you why these features are useful to you and shows you how to use them in everyday situations. Throughout the book you'll find no-nonsense, step-by-step tutorials and lots of practical examples aimed directly at business users. - Focuses like a laser on the four technologies that you must master to get the most out of Excel: ranges, formulas, functions, and data analysis tools. - Shuns spreadsheet theory in favor of practical know-how that you can put to use right away. - Provides numerous real-world examples and techniques to help you learn and understand the importance of each section.
Introduction 1 Getting the Most Out of Ranges2 Using Range Names 3 Building Basic Formulas 4 Creating Advanced Formulas 5 Troubleshooting Formulas
II Harnessing the Power of Functions 6 Understanding Functions 7 Working with Text Functions 8 Working with Logical and Information Functions 9 Working with Lookup Functions 10 Working with Date and Time Functions
11 Working with Math Functions 12 Working with Statistical Functions III Building Business Models 13 Analyzing Data with Tables14 Analyzing Data with PivotTables15 Using Excel's Business-Modeling Tools 16 Using Regression to Track Trends and Make Forecasts 17 Solving Complex Problems with SolverIV Building Financial Formulas 18 Building Loan Formulas19 Building Investment Formulas 20 Building Discount Formulas
Paul McFedries is well-known as a teacher of Windows and Office, particularly Excel, and is the president of Logophilia Limited, a technical writing company. Paul has been working with spreadsheets for more than 20 years and has been developing Excel solutions since the late 1980s. Now primarily a writer, Paul has written more than 50 books that have sold more than three million copies worldwide. These books include Microsoft Office Access 2007 Forms, Reports, and Queries; Tricks of the Microsoft Office 2007 Gurus (all from Que); and Microsoft Windows Vista Unleashed (Sams). Category Office Productivity Suite Covers Microsoft Office Excel 2007 User Level Intermediate - Advanced