Cargando…
Excel 2016 formulas /
Annotation
| Clasificación: | Libro Electrónico |
|---|---|
| Autores principales: | , |
| Formato: | Electrónico eBook |
| Idioma: | Inglés |
| Publicado: |
Indianapolis, IN :
Wiley,
[2016]
|
| Colección: | Mr. Spreadsheet's bookshelf.
|
| Temas: | |
| Acceso en línea: | Texto completo Texto completo |
Tabla de Contenidos:
- Excel® 2016 Formulas
- About the Authors
- About the Technical Editor
- Credits
- Contents at a Glance
- Table of Contents
- Introduction
- Part I: Understanding Formula Basics
- Chapter 1: The Excel User Interface in a Nutshell
- The Workings of Workbooks
- Worksheets
- Chart sheets
- Macro sheets and dialog sheets
- The Excel User Interface
- The Ribbon
- Backstage View
- Shortcut menus and the mini toolbar
- Dialog boxes
- Customizing the UI
- Task panes
- Customizing onscreen display
- Numeric formatting
- Stylistic formatting
- Protection Options
- Securing access to the entire workbook
- Limiting access to specific worksheet ranges
- Protecting the workbook structure
- Chapter 2: Basic Facts About Formulas
- Entering and Editing Formulas
- Formula elements
- Entering a formula
- Pasting names
- Spaces and line breaks
- Formula limits
- Sample formulas
- Editing formulas
- Using Operators in Formulas
- Reference operators
- Sample formulas that use operators
- Operator precedence
- Nested parentheses
- Calculating Formulas
- Cell and Range References
- Creating an absolute or a mixed reference
- Referencing other sheets or workbooks
- Copying or Moving Formulas
- Making an Exact Copy of a Formula
- Converting Formulas to Values
- Hiding Formulas
- Errors in Formulas
- Dealing with Circular References
- Goal Seeking
- A goal seeking example
- More about goal seeking
- Chapter 3: Working with Names
- What's in a Name?
- A Name's Scope
- Referencing names
- Referencing names from another workbook
- Conflicting names
- The Name Manager
- Creating names
- Editing names
- Deleting names
- Shortcuts for Creating Cell and Range Names
- The New Name dialog box
- Creating names using the Name box
- Creating names from text in cells
- Naming entire rows and columns.
- Names created by Excel
- Creating Multisheet Names
- Working with Range and Cell Names
- Creating a list of names
- Using names in formulas
- Using the intersection operators with names
- Using the range operator with names
- Referencing a single cell in a multicell named range
- Applying names to existing formulas
- Applying names automatically when creating a formula
- Unapplying names
- Names with errors
- Viewing named ranges
- Using names in charts
- How Excel Maintains Cell and Range Names
- Inserting a row or column
- Deleting a row or a column
- Cutting and pasting
- Potential Problems with Names
- Name problems when copying sheets
- Name problems when deleting sheets
- The Secret to Understanding Names
- Naming constants
- Naming text constants
- Using worksheet functions in named formulas
- Using cell and range references in named formulas
- Using named formulas with relative references
- Advanced Techniques That Use Names
- Using the INDIRECT function with a named range
- Using arrays in named formulas
- Creating a dynamic named formula
- Using an XLM macro in a named formula
- Part II: Leveraging Excel Functions
- Chapter 4: Introducing Worksheet Functions
- What Is a Function?
- Simplify your formulas
- Perform otherwise impossible calculations
- Speed up editing tasks
- Provide decision-making capability
- More about functions
- Function Argument Types
- Names as arguments
- Full-column or full-row as arguments
- Literal values as arguments
- Expressions as arguments
- Other functions as arguments
- Arrays as arguments
- Ways to Enter a Function into a Formula
- Entering a function manually
- Using the Function Library commands
- Using the Insert Function dialog box
- More tips for entering functions
- Chapter 5: Manipulating Text
- A Few Words About Text.
- How many characters in a cell?
- Numbers as text
- Text Functions
- Determining whether a cell contains text
- Working with character codes
- Determining whether two strings are identical
- Joining two or more cells
- Displaying formatted values as text
- Displaying formatted currency values as text
- Removing excess spaces and nonprinting characters
- Counting characters in a string
- Repeating a character or string
- Creating a text histogram
- Padding a number
- Changing the case of text
- Extracting characters from a string
- Replacing text with other text
- Finding and searching within a string
- Searching and replacing within a string
- Advanced Text Formulas
- Counting specific characters in a cell
- Counting the occurrences of a substring in a cell
- Removing trailing minus signs
- Expressing a number as an ordinal
- Determining a column letter for a column number
- Extracting a filename from a path specification
- Extracting the first word of a string
- Extracting the last word of a string
- Extracting all but the first word of a string
- Extracting first names, middle names, and last names
- Removing titles from names
- Counting the number of words in a cell
- Chapter 6: Working with Dates and Times
- How Excel Handles Dates and Times
- Understanding date serial numbers
- Entering dates
- Understanding time serial numbers
- Entering times
- Formatting dates and times
- Problems with dates
- Date-Related Functions
- Displaying the current date
- Displaying any date with a function
- Generating a series of dates
- Converting a nondate string to a date
- Calculating the number of days between two dates
- Calculating the number of work days between two dates
- Offsetting a date using only work days
- Calculating the number of years between two dates
- Calculating a person's age.
- Determining the day of the year
- Determining the day of the week
- Determining the week of the year
- Determining the date of the most recent Sunday
- Determining the first day of the week after a date
- Determining the nth occurrence of a day of the week in a month
- Counting the occurrences of a day of the week
- Expressing a date as an ordinal number
- Calculating dates of holidays
- Determining the last day of a month
- Determining whether a year is a leap year
- Determining a date's quarter
- Converting a year to roman numerals
- Time-Related Functions
- Displaying the current time
- Displaying any time using a function
- Calculating the difference between two times
- Summing times that exceed 24 hours
- Converting from military time
- Converting decimal hours, minutes, or seconds to a time
- Adding hours, minutes, or seconds to a time
- Converting between time zones
- Rounding time values
- Calculating Durations
- Chapter 7: Counting and Summing Techniques
- Counting and Summing Worksheet Cells
- Other Counting Methods
- Basic Counting Formulas
- Counting the total number of cells
- Counting blank cells
- Counting nonblank cells
- Counting numeric cells
- Counting text cells
- Counting nontext cells
- Counting logical values
- Counting error values in a range
- Advanced Counting Formulas
- Counting cells with the COUNTIF function
- Counting cells that meet multiple criteria
- Counting the most frequently occurring entry
- Counting the occurrences of specific text
- Counting the number of unique values
- Creating a frequency distribution
- Summing Formulas
- Summing all cells in a range
- Summing a range that contains errors
- Computing a cumulative sum
- Summing the "top n" values
- Conditional Sums Using a Single Criterion
- Summing only negative values.
- Summing values based on a different range
- Summing values based on a text comparison
- Summing values based on a date comparison
- Conditional Sums Using Multiple Criteria
- Using And criteria
- Using Or criteria
- Using And and Or criteria
- Chapter 8: Using Lookup Functions
- What Is a Lookup Formula?
- Functions Relevant to Lookups
- Basic Lookup Formulas
- The VLOOKUP function
- The HLOOKUP function
- The LOOKUP function
- Combining the MATCH and INDEX functions
- Specialized Lookup Formulas
- Looking up an exact value
- Looking up a value to the left
- Performing a case-sensitive lookup
- Choosing among multiple lookup tables
- Determining letter grades for test scores
- Calculating a grade point average
- Performing a two-way lookup
- Performing a two-column lookup
- Determining the address of a value within a range
- Looking up a value by using the closest match
- Looking up a value using linear interpolation
- Chapter 9: Working with Tables and Lists
- Tables and Terminology
- A list example
- A table example
- Working with Tables
- Creating a table
- Changing the look of a table
- Navigating and selecting in a table
- Adding new rows or columns
- Deleting rows or columns
- Moving a table
- Removing duplicate rows from a table
- Sorting and filtering a table
- Working with the Total row
- Using formulas within a table
- Referencing data in a table
- Converting a table to a list
- Using Advanced Filtering
- Setting up a criteria range
- Applying an advanced filter
- Clearing an advanced filter
- Specifying Advanced Filter Criteria
- Specifying a single criterion
- Specifying multiple criteria
- Specifying computed criteria
- Using Database Functions
- Inserting Subtotals
- Chapter 10: Miscellaneous Calculations
- Unit Conversions
- Rounding Numbers
- Basic rounding formulas.