Excel Cookbook

Spis treści ebooka

• Preface
  • Conventions Used in This Book
  • Using Code Examples
  • OReilly Online Learning
  • How to Contact Us
  • Acknowledgments
• 1. Workbooks, Worksheets, and Cells
  • 1.1. Using Themes
  • 1.2. Using Cell Styles
  • 1.3. Formatting Cells
  • 1.4. Formatting a Cells Value
  • 1.5. Defining a Custom Number Format
  • 1.6. Merging Cells
  • 1.7. Creating Templates
  • 1.8. Protecting Excel Files, Workbooks, Worksheets, and Cells
  • 1.9. Using Conditional Formatting
  • 1.10. Using the Format Painter
  • 1.11. Using Paste Special
  • 1.12. Using Auto Fill
  • 1.13. Using Custom Lists
  • 1.14. Using Flash Fill
  • 1.15. Customizing AutoCorrect
  • 1.16. Using Notes and Comments
  • 1.17. Finding and Selecting Cells and Navigation
  • 1.18. Creating a Custom View
  • 1.19. Customizing the Ribbon and Ribbon Tabs
  • 1.20. Using the Quick Access Toolbar
  • 1.21. Using the Accessibility Checker
• 2. References and Structured Data
  • 2.1. Using Relative and Absolute References
  • 2.2. Using Relative and Absolute References in Conditional Formatting
  • 2.3. Using R1C1-Style Cell References
  • 2.4. Referencing Another Worksheet or Workbook
  • 2.5. Using 3-D References
  • 2.6. Naming Cells, Ranges, Constants, and Formulas
  • 2.7. Creating Dynamic Named Ranges
  • 2.8. Using Data Validation
  • 2.9. Creating a Custom Data Validation Rule
  • 2.10. Entering Data with a Drop-Down List
  • 2.11. Defining Dependent or Cascading Drop-Down Lists
  • 2.12. Using a Data-Entry Form
  • 2.13. Sorting Data by Value, Format, or Custom List
  • 2.14. Filtering Data
  • 2.15. Freezing Panes
  • 2.16. Using AutoSum
  • 2.17. Using Outlines to Add Subtotals and Groups
  • 2.18. Using Tables
  • 2.19. Using Structured References
• 3. Using Formulas
  • 3.1. Using Operators and Order of Precedence
  • 3.2. Using Excel in Different Regions and Languages
  • 3.3. Using Array Constants
  • 3.4. Using Dynamic and Legacy Array Formulas
  • 3.5. Using Spill Range References
  • 3.6. Preventing Dynamic Array Behavior
  • 3.7. Using the Insert Function or Function Builder Tool
  • 3.8. Adding Notes to Numeric Formulas
  • 3.9. Showing Formulas
  • 3.10. Using the Watch Window
  • 3.11. Showing Cell Interdependencies
  • 3.12. Performing Background Error Checks
  • 3.13. Using Error Checking
  • 3.14. Tracing Errors
  • 3.15. Correcting Error Values
  • 3.16. Evaluating Formulas
  • 3.17. Changing the Calculation Mode
  • 3.18. Setting Rounding Precision
  • 3.19. Resolving Circular References
• 4. Math and Engineering
  • 4.1. Generating Numbers
  • 4.2. Converting Text or a Boolean to a Number
  • 4.3. Getting a Numbers Sign and Absolute Value
  • 4.4. Counting, Summing, and Averaging Cell Values
  • 4.5. Using Criteria to Count, Sum, and Average
  • 4.6. Adding and Subtracting Squares of Values
  • 4.7. Using Multiplication and Multiples
  • 4.8. Finding Quotients, Remainders, and Divisors
  • 4.9. Rounding to Decimal Places and Integers
  • 4.10. Rounding to Significant Figures and Multiples
  • 4.11. Using Powers, Exponents, Square Roots, and Logarithms
  • 4.12. Summing a Power Series
  • 4.13. Using Factorials, Permutations, and Combinations
  • 4.14. Using Trigonometry
  • 4.15. Working with Matrices
  • 4.16. Converting Between Number Systems
  • 4.17. Performing Bitwise Operations
  • 4.18. Working with Complex Numbers
• 5. Text Manipulation
  • 5.1. Concatenating Text
  • 5.2. Using Character Codes
  • 5.3. Generating a Sequence of Characters
  • 5.4. Generating Random Letters
  • 5.5. Finding the Length of a Text String
  • 5.6. Finding Text Position in a Text String
  • 5.7. Getting Fixed-Width Text from a Text String
  • 5.8. Getting Text from a Text String by Delimiter
  • 5.9. Getting Text from a Text String by Digit to Nondigit
  • 5.10. Replacing, Inserting, and Deleting Text
  • 5.11. Removing Extra Characters
  • 5.12. Counting Words or Specific Characters
  • 5.13. Changing Text Case
  • 5.14. Repeating Characters
  • 5.15. Converting an Array to Text
  • 5.16. Formatting Text as Currency
  • 5.17. Including Numeric Values in a Text String
  • 5.18. Including Date/Time Values in a Text String
• 6. Dates and Times
  • 6.1. Returning the Current Date and Time
  • 6.2. Getting Part of a Date/Time Value
  • 6.3. Getting the Day of the Week and Week of the Year
  • 6.4. Getting the Calendar or Fiscal Quarter
  • 6.5. Constructing Dates Using Day, Month, and Year
  • 6.6. Constructing Times Using Hours, Minutes, and Seconds
  • 6.7. Converting a Text Value to a Date/Time Serial Number
  • 6.8. Extracting the Date and Time from a Serial Number
  • 6.9. Adding Days, Months, and Years to a Date
  • 6.10. Adding Hours, Minutes, and Seconds to a Time
  • 6.11. Getting the Last Day of the Month
  • 6.12. Calculating the Year Fraction
  • 6.13. Calculating the Difference Between Dates and Times
  • 6.14. Using Working Days
  • 6.15. Getting a Sequence of Dates
• 7. Array, Logic, and Lookup Functions
  • 7.1. Getting Unique Values
  • 7.2. Sorting an Array
  • 7.3. Filtering an Array
  • 7.4. Manipulating Arrays
  • 7.5. Using Logical True/False Criteria
  • 7.6. Evaluating AND and OR Conditions in Array Formulas
  • 7.7. Working with Types and Error Values
  • 7.8. Choosing Values to Return
  • 7.9. Looking Up Exact and Nearest Values
  • 7.10. Finding a Matching Values Index
  • 7.11. Using an Index to Return a Value
  • 7.12. Creating Indirect References to Cells and Ranges
  • 7.13. Getting a Cells Address
  • 7.14. Using Offset References
• 8. Statistical Analysis
  • 8.1. Creating a Frequency Table
  • 8.2. Showing Cumulative and Percentage Frequencies
  • 8.3. Using a Histogram or Pareto Chart
  • 8.4. Calculating Averages
  • 8.5. Ranking Numeric Data
  • 8.6. Finding the kth Largest or Smallest Value
  • 8.7. Dividing Data into Quartiles and Percentiles
  • 8.8. Calculating Ranges and Variances
  • 8.9. Finding Outliers
  • 8.10. Using a Box and Whisker Chart
  • 8.11. Calculating Skewness
  • 8.12. Calculating Probabilities Using a Probability Table
  • 8.13. Calculating Expectation and Variance
  • 8.14. Using the Binomial Distribution
  • 8.15. Using the Negative Binomial Distribution
  • 8.16. Using the Hypergeometric Distribution
  • 8.17. Using the Poisson Distribution
  • 8.18. Using the Exponential Distribution
  • 8.19. Using the Normal Distribution
  • 8.20. Using Z-Scores
  • 8.21. Calculating a Confidence Interval for the Population Mean
  • 8.22. Performing a Chi-Squared (2) Test for Independence
  • 8.23. Finding the Line of Best Fit
  • 8.24. Getting the Line of Best Fits Equation
• 9. The Analysis ToolPak
  • 9.1. Installing the Analysis ToolPak
  • 9.2. Generating Descriptive Statistics
  • 9.3. Generating Ordinal and Percentage Rank Statistics
  • 9.4. Generating a Frequency Distribution
  • 9.5. Generating Moving Averages
  • 9.6. Using Exponential Smoothing
  • 9.7. Generating a Random Sample
  • 9.8. Generating a Periodic Sample
  • 9.9. Drawing Random Numbers from a Distribution
  • 9.10. Generating a Correlation Matrix
  • 9.11. Generating a Covariance Matrix
  • 9.12. Performing a Linear Regression Analysis
  • 9.13. Performing a Two-Sample t-Test
  • 9.14. Performing a Two-Sample z-Test
  • 9.15. Performing a Paired Two-Sample t-Test
  • 9.16. Performing a Two-Sample F-Test for Variances
  • 9.17. Performing a One-Way ANOVA Test
  • 9.18. Performing a Two-Way ANOVA Test
  • 9.19. Running a Fourier Analysis
• 10. Financial Analysis
  • 10.1. Calculating Fixed-Rate Loan Payments
  • 10.2. Calculating Interest and Principal Loan Payments
  • 10.3. Building a Variable Rate Loan Amortization Schedule
  • 10.4. Calculating the Term for a Fixed-Rate Loan
  • 10.5. Calculating the Principal or Present Value
  • 10.6. Converting Between Nominal and Effective Rates
  • 10.7. Calculating the Future Value of a Fixed-Rate Lump-Sum Investment
  • 10.8. Calculating the Future Value of a Variable-Rate Lump-Sum Investment
  • 10.9. Calculating the Future Value of an Investment with Regular Deposits
  • 10.10. Meeting Investment Goals
  • 10.11. Calculating Net Present Value
  • 10.12. Calculating the Internal Rate of Return
  • 10.13. Calculating Depreciation
  • 10.14. Getting Stock and Currency Data
  • 10.15. Getting Historic Stock and Currency Data
  • 10.16. Using Stock Charts
  • 10.17. Calculating a Stocks Beta
  • 10.18. Forecasting Linear and Exponential Growth
  • 10.19. Forecasting Seasonal Growth
• 11. PivotTables
  • 11.1. Organizing Data for PivotTables
  • 11.2. Inserting a PivotTable
  • 11.3. Adding Rows, Columns, and Values
  • 11.4. Using Secondary Rows
  • 11.5. Refreshing a PivotTables Data
  • 11.6. Moving a PivotTable
  • 11.7. Changing a PivotTables Appearance
  • 11.8. Changing the Default Layout
  • 11.9. Changing Value Aggregations
  • 11.10. Showing Different Value Calculations
  • 11.11. Creating Custom Subtotals
  • 11.12. Sorting Data
  • 11.13. Moving Items Manually
  • 11.14. Filtering Data
  • 11.15. Using a Filter to Create Multiple PivotTables
  • 11.16. Grouping by Date/Time
  • 11.17. Grouping by Number
  • 11.18. Manually Grouping by Text Values
  • 11.19. Including Groups with Missing Data
  • 11.20. Changing the Format of Empty Cells
  • 11.21. Using Calculated Fields
  • 11.22. Using Calculated Fields to Count Items
  • 11.23. Using Calculated Items
  • 11.24. Referring to Position in a Calculated Item Formula
  • 11.25. Changing the Calculated Item Solve Order
  • 11.26. Generating a List of Custom Formulas
  • 11.27. Changing a PivotTables Data Source
  • 11.28. Using the PivotTable Cache
  • 11.29. Filtering Multiple PivotTables That Share a Cache
  • 11.30. Reducing the Workbook File Size
  • 11.31. Reinstating a PivotTables Source Data
  • 11.32. Referring to PivotTable Values
• 12. Charts
  • 12.1. Using Different Chart Types
  • 12.2. Inserting a Chart
  • 12.3. Filtering a Chart
  • 12.4. Tweaking a Charts Appearance
  • 12.5. Adding and Removing Chart Elements
  • 12.6. Formatting Chart Elements
  • 12.7. Creating Dynamic Titles and Labels
  • 12.8. Customizing Data Label Text
  • 12.9. Controlling Chart Axes and Gridlines
  • 12.10. Displaying Negative Values
  • 12.11. Using Pictures in Column Charts
  • 12.12. Formatting Pie of Pie and Bar of Pie Charts
  • 12.13. Formatting a Histogram Chart
  • 12.14. Specifying a Combination Charts Chart Types
  • 12.15. Handling Empty Cells
  • 12.16. Basing a Chart on Noncontiguous Data
  • 12.17. Changing a Data Series Name and Legend Entry
  • 12.18. Adding a Series or Changing the Data Source
  • 12.19. Basing a Chart on a Dynamic Named Range
  • 12.20. Inserting a PivotChart
  • 12.21. Creating a Gantt Chart
  • 12.22. Creating and Using Chart Templates
• 13. Graphics, Sparklines, and 3D Maps
  • 13.1. Inserting Symbols
  • 13.2. Inserting Equations
  • 13.3. Inserting Shapes
  • 13.4. Using the Draw Tool
  • 13.5. Using SmartArt
  • 13.6. Inserting Pictures
  • 13.7. Grouping Objects
  • 13.8. Moving and Sizing Objects with Cells
  • 13.9. Inserting a Linked Picture
  • 13.10. Using Sparklines
  • 13.11. Using Sparkline Groups
  • 13.12. Using 3D Maps
  • 13.13. Creating Videos with 3D Maps
• 14. What-If Analysis
  • 14.1. Creating a One-Variable Data Table
  • 14.2. Creating a Row-Oriented One-Variable Data Table
  • 14.3. Creating a Two-Variable Data Table
  • 14.4. Editing Data Tables
  • 14.5. Using Scenario Manager
  • 14.6. Merging Scenarios
  • 14.7. Generating Scenario Summaries
  • 14.8. Using Goal Seek
  • 14.9. Finding Multiple Solutions with Goal Seek
  • 14.10. Handling Discontinuous Formulas with Goal Seek
  • 14.11. Enabling Solver
  • 14.12. Solving an Optimization Problem with Solver
  • 14.13. Using Integer-Only Constraints with Solver
  • 14.14. Using Binary-Only Constraints with Solver
  • 14.15. Making Changing Cells All Different with Solver
  • 14.16. Handling Discontinuities with Solver
  • 14.17. Finding Multiple Solutions with Solver
  • 14.18. Finding a Formulas Global Minimum or Maximum with Solver
  • 14.19. Adjusting Solvers Options
  • 14.20. Saving and Loading Solver Parameters
  • 14.21. Saving Solver-Generated Scenarios
  • 14.22. Displaying Solver Reports
• 15. Power Query
  • 15.1. Getting and Loading Data
  • 15.2. Getting and Loading Data from Files in a Folder
  • 15.3. Specifying Where to Load Data To
  • 15.4. Editing Data Source Settings and Security
  • 15.5. Refreshing a Querys Data
  • 15.6. Managing Queries
  • 15.7. Editing a Query
  • 15.8. Managing a Querys Steps
  • 15.9. Managing Columns
  • 15.10. Using Data Types
  • 15.11. Sorting and Filtering Data
  • 15.12. Filtering Files When Loading Data from a Folder
  • 15.13. Removing Duplicates, Blank Rows, and Errors
  • 15.14. Transforming Data in Columns
  • 15.15. Splitting and Merging Columns
  • 15.16. Pivoting Columns
  • 15.17. Unpivoting Columns
  • 15.18. Transforming Structured Columns
  • 15.19. Returning a Value or List
  • 15.20. Adding New Columns
  • 15.21. Adding a Column Based on Examples
  • 15.22. Adding a Conditional Column
  • 15.23. Adding a Custom Column
  • 15.24. Using Parameters
  • 15.25. Creating a Custom Function
  • 15.26. Adding a Column by Invoking a Custom Function
  • 15.27. Duplicating a Query
  • 15.28. Referencing a Query
  • 15.29. Appending Data from Multiple Queries
  • 15.30. Merging Data from Multiple Queries
  • 15.31. Editing a Querys M Code
• 16. Power Pivot and the Data Model
  • 16.1. Installing Power Pivot
  • 16.2. Adding Data to the Data Model
  • 16.3. Managing Power Pivot Data Connections
  • 16.4. Viewing and Managing the Data Models Tables
  • 16.5. Refreshing the Data Models Data
  • 16.6. Working with Table Columns
  • 16.7. Creating and Editing Relationships
  • 16.8. Adding a Calculated Column
  • 16.9. Basing a PivotTable or PivotChart on Data Model Tables
  • 16.10. Inserting Measures
  • 16.11. Using KPIs
  • 16.12. Creating Hierarchies
  • 16.13. Creating a Date Table
  • 16.14. Using Named Sets
  • 16.15. Converting a PivotTable to Formulas
  • 16.16. Using Cube Formulas
  • 16.17. Filtering Cube Formulas with Slicers and Timelines
• 17. LET, LAMBDA, and LAMBDA Helper Functions
  • 17.1. Improving Formula Efficiency
  • 17.2. Writing and Testing a LAMBDA Formula
  • 17.3. Making LAMBDA Arguments Optional
  • 17.4. Defining a Custom LAMBDA Function
  • 17.5. Writing Recursive LAMBDA Formulas
  • 17.6. Copying a Custom LAMBDA Function to Another Workbook
  • 17.7. Applying a LAMBDA Formula to Each Column
  • 17.8. Applying a LAMBDA Formula to Each Row
  • 17.9. Creating an Array of Calculated Values
  • 17.10. Transforming the Values in Arrays
  • 17.11. Calculating Cumulative Values
  • 17.12. Returning the Final Value of a Cumulative Calculation
• 18. Developer Tools: Macros, VBA, Controls, and XML
  • 18.1. Showing the Developer Tab
  • 18.2. Recording a Macro
  • 18.3. Using a Personal Macro Workbook
  • 18.4. Editing a Macros Options
  • 18.5. Running a Macro
  • 18.6. Viewing or Editing a Macros VBA Code
  • 18.7. Using Absolute and Relative References
  • 18.8. Creating a Macro by Writing VBA
  • 18.9. Creating a Custom VBA Function
  • 18.10. Using Worksheet and Workbook Events
  • 18.11. Overriding Keystrokes with OnKey
  • 18.12. Scheduling Code with OnTime
  • 18.13. Deleting a Macro or Function
  • 18.14. Copying Code to Another VBA Project
  • 18.15. Debugging VBA Code
  • 18.16. Using Built-in Dialog Boxes
  • 18.17. Using Form Controls
  • 18.18. Using ActiveX Controls
  • 18.19. Creating a UserForm
  • 18.20. Creating a Custom Excel Add-in
  • 18.21. Setting Security and Privacy Options
  • 18.22. Importing and Exporting XML
• Index

pokaż cały spis treści

ukryj recenzje