Griffiths Dawn / Гриффитс Дон - Excel Cookbook: Recipes for Mastering Microsoft Excel / Книга рецептов Excel: рецепты для освоения Microsoft Excel [2024, PDF/EPUB, ENG]

Примеры страниц (скриншоты)

Оглавление

Preface xiii
1. Workbooks, Worksheets, and Cells 1
1.1 Using Themes 1
1.2 Using Cell Styles 2
1.3 Formatting Cells 3
1.4 Formatting a Cell’s Value 4
1.5 Defining a Custom Number Format 6
1.6 Merging Cells 10
1.7 Creating Templates 11
1.8 Protecting Excel Files, Workbooks, Worksheets, and Cells 12
1.9 Using Conditional Formatting 13
1.10 Using the Format Painter 16
1.11 Using Paste Special 16
1.12 Using Auto Fill 18
1.13 Using Custom Lists 20
1.14 Using Flash Fill 21
1.15 Customizing AutoCorrect 23
1.16 Using Notes and Comments 24
1.17 Finding and Selecting Cells and Navigation 26
1.18 Creating a Custom View 28
1.19 Customizing the Ribbon and Ribbon Tabs 29
1.20 Using the Quick Access Toolbar 31
1.21 Using the Accessibility Checker 32
2. References and Structured Data 33
2.1 Using Relative and Absolute References 33
2.2 Using Relative and Absolute References in Conditional Formatting 34
2.3 Using R1C1-Style Cell References 36
2.4 Referencing Another Worksheet or Workbook 38
2.5 Using 3-D References 39
2.6 Naming Cells, Ranges, Constants, and Formulas 39
2.7 Creating Dynamic Named Ranges 42
2.8 Using Data Validation 44
2.9 Creating a Custom Data Validation Rule 46
2.10 Entering Data with a Drop-Down List 47
2.11 Defining Dependent or Cascading Drop-Down Lists 48
2.12 Using a Data-Entry Form 49
2.13 Sorting Data by Value, Format, or Custom List 51
2.14 Filtering Data 52
2.15 Freezing Panes 54
2.16 Using AutoSum 55
2.17 Using Outlines to Add Subtotals and Groups 56
2.18 Using Tables 57
2.19 Using Structured References 60
3. Using Formulas 63
3.1 Using Operators and Order of Precedence 63
3.2 Using Excel in Different Regions and Languages 66
3.3 Using Array Constants 67
3.4 Using Dynamic and Legacy Array Formulas 68
3.5 Using Spill Range References 70
3.6 Preventing Dynamic Array Behavior 71
3.7 Using the Insert Function or Function Builder Tool 72
3.8 Adding Notes to Numeric Formulas 73
3.9 Showing Formulas 74
3.10 Using the Watch Window 75
3.11 Showing Cell Interdependencies 76
3.12 Performing Background Error Checks 77
3.13 Using Error Checking 78
3.14 Tracing Errors 79
3.15 Correcting Error Values 80
3.16 Evaluating Formulas 82
3.17 Changing the Calculation Mode 84
3.18 Setting Rounding Precision 86
3.19 Resolving Circular References 87
4. Math and Engineering 89
4.1 Generating Numbers 89
4.2 Converting Text or a Boolean to a Number 90
4.3 Getting a Number’s Sign and Absolute Value 91
4.4 Counting, Summing, and Averaging Cell Values 92
4.5 Using Criteria to Count, Sum, and Average 93
4.6 Adding and Subtracting Squares of Values 95
4.7 Using Multiplication and Multiples 96
4.8 Finding Quotients, Remainders, and Divisors 97
4.9 Rounding to Decimal Places and Integers 98
4.10 Rounding to Significant Figures and Multiples 100
4.11 Using Powers, Exponents, Square Roots, and Logarithms 101
4.12 Summing a Power Series 103
4.13 Using Factorials, Permutations, and Combinations 103
4.14 Using Trigonometry 104
4.15 Working with Matrices 105
4.16 Converting Between Number Systems 107
4.17 Performing Bitwise Operations 108
4.18 Working with Complex Numbers 109
5. Text Manipulation 111
5.1 Concatenating Text 111
5.2 Using Character Codes 112
5.3 Generating a Sequence of Characters 113
5.4 Generating Random Letters 114
5.5 Finding the Length of a Text String 114
5.6 Finding Text Position in a Text String 115
5.7 Getting Fixed-Width Text from a Text String 116
5.8 Getting Text from a Text String by Delimiter 117
5.9 Getting Text from a Text String by Digit to Nondigit 119
5.10 Replacing, Inserting, and Deleting Text 120
5.11 Removing Extra Characters 121
5.12 Counting Words or Specific Characters 122
5.13 Changing Text Case 123
5.14 Repeating Characters 124
5.15 Converting an Array to Text 124
5.16 Formatting Text as Currency 125
5.17 Including Numeric Values in a Text String 126
5.18 Including Date/Time Values in a Text String 127
6. Dates and Times 129
6.1 Returning the Current Date and Time 129
6.2 Getting Part of a Date/Time Value 130
6.3 Getting the Day of the Week and Week of the Year 130
6.4 Getting the Calendar or Fiscal Quarter 131
6.5 Constructing Dates Using Day, Month, and Year 133
6.6 Constructing Times Using Hours, Minutes, and Seconds 134
6.7 Converting a Text Value to a Date/Time Serial Number 135
6.8 Extracting the Date and Time from a Serial Number 136
6.9 Adding Days, Months, and Years to a Date 137
6.10 Adding Hours, Minutes, and Seconds to a Time 138
6.11 Getting the Last Day of the Month 139
6.12 Calculating the Year Fraction 140
6.13 Calculating the Difference Between Dates and Times 141
6.14 Using Working Days 142
6.15 Getting a Sequence of Dates 143
7. Array, Logic, and Lookup Functions 145
7.1 Getting Unique Values 145
7.2 Sorting an Array 146
7.3 Filtering an Array 148
7.4 Manipulating Arrays 149
7.5 Using Logical True/False Criteria 151
7.6 Evaluating AND and OR Conditions in Array Formulas 152
7.7 Working with Types and Error Values 153
7.8 Choosing Values to Return 154
7.9 Looking Up Exact and Nearest Values 156
7.10 Finding a Matching Value’s Index 158
7.11 Using an Index to Return a Value 159
7.12 Creating Indirect References to Cells and Ranges 161
7.13 Getting a Cell’s Address 162
7.14 Using Offset References 163
8. Statistical Analysis 165
8.1 Creating a Frequency Table 165
8.2 Showing Cumulative and Percentage Frequencies 168
8.3 Using a Histogram or Pareto Chart 169
8.4 Calculating Averages 171
8.5 Ranking Numeric Data 173
8.6 Finding the kth Largest or Smallest Value 174
8.7 Dividing Data into Quartiles and Percentiles 175
8.8 Calculating Ranges and Variances 176
8.9 Finding Outliers 178
8.10 Using a Box and Whisker Chart 178
8.11 Calculating Skewness 180
8.12 Calculating Probabilities Using a Probability Table 181
8.13 Calculating Expectation and Variance 182
8.14 Using the Binomial Distribution 183
8.15 Using the Negative Binomial Distribution 184
8.16 Using the Hypergeometric Distribution 185
8.17 Using the Poisson Distribution 186
8.18 Using the Exponential Distribution 187
8.19 Using the Normal Distribution 187
8.20 Using Z-Scores 189
8.21 Calculating a Confidence Interval for the Population Mean 190
8.22 Performing a Chi-Squared (χ2) Test for Independence 192
8.23 Finding the Line of Best Fit 193
8.24 Getting the Line of Best Fit’s Equation 195
9. The Analysis ToolPak 197
9.1 Installing the Analysis ToolPak 197
9.2 Generating Descriptive Statistics 198
9.3 Generating Ordinal and Percentage Rank Statistics 200
9.4 Generating a Frequency Distribution 202
9.5 Generating Moving Averages 205
9.6 Using Exponential Smoothing 207
9.7 Generating a Random Sample 209
9.8 Generating a Periodic Sample 211
9.9 Drawing Random Numbers from a Distribution 211
9.10 Generating a Correlation Matrix 213
9.11 Generating a Covariance Matrix 215
9.12 Performing a Linear Regression Analysis 216
9.13 Performing a Two-Sample t-Test 219
9.14 Performing a Two-Sample z-Test 222
9.15 Performing a Paired Two-Sample t-Test 224
9.16 Performing a Two-Sample F-Test for Variances 226
9.17 Performing a One-Way ANOVA Test 227
9.18 Performing a Two-Way ANOVA Test 229
9.19 Running a Fourier Analysis 232
10. Financial Analysis 235
10.1 Calculating Fixed-Rate Loan Payments 235
10.2 Calculating Interest and Principal Loan Payments 236
10.3 Building a Variable Rate Loan Amortization Schedule 238
10.4 Calculating the Term for a Fixed-Rate Loan 240
10.5 Calculating the Principal or Present Value 241
10.6 Converting Between Nominal and Effective Rates 242
10.7 Calculating the Future Value of a Fixed-Rate Lump-Sum Investment 243
10.8 Calculating the Future Value of a Variable-Rate Lump-Sum Investment 244
10.9 Calculating the Future Value of an Investment with Regular Deposits 245
10.10 Meeting Investment Goals 246
10.11 Calculating Net Present Value 248
10.12 Calculating the Internal Rate of Return 249
10.13 Calculating Depreciation 251
10.14 Getting Stock and Currency Data 252
10.15 Getting Historic Stock and Currency Data 254
10.16 Using Stock Charts 254
10.17 Calculating a Stock’s Beta 256
10.18 Forecasting Linear and Exponential Growth 257
10.19 Forecasting Seasonal Growth 259
11. PivotTables 263
11.1 Organizing Data for PivotTables 263
11.2 Inserting a PivotTable 264
11.3 Adding Rows, Columns, and Values 266
11.4 Using Secondary Rows 268
11.5 Refreshing a PivotTable’s Data 270
11.6 Moving a PivotTable 270
11.7 Changing a PivotTable’s Appearance 271
11.8 Changing the Default Layout 272
11.9 Changing Value Aggregations 273
11.10 Showing Different Value Calculations 274
11.11 Creating Custom Subtotals 276
11.12 Sorting Data 277
11.13 Moving Items Manually 278
11.14 Filtering Data 279
11.15 Using a Filter to Create Multiple PivotTables 281
11.16 Grouping by Date/Time 282
11.17 Grouping by Number 283
11.18 Manually Grouping by Text Values 284
11.19 Including Groups with Missing Data 285
11.20 Changing the Format of Empty Cells 286
11.21 Using Calculated Fields 287
11.22 Using Calculated Fields to Count Items 290
11.23 Using Calculated Items 291
11.24 Referring to Position in a Calculated Item Formula 294
11.25 Changing the Calculated Item Solve Order 295
11.26 Generating a List of Custom Formulas 297
11.27 Changing a PivotTable’s Data Source 298
11.28 Using the PivotTable Cache 298
11.29 Filtering Multiple PivotTables That Share a Cache 300
11.30 Reducing the Workbook File Size 301
11.31 Reinstating a PivotTable’s Source Data 302
11.32 Referring to PivotTable Values 303
12. Charts 305
12.1 Using Different Chart Types 305
12.2 Inserting a Chart 311
12.3 Filtering a Chart 312
12.4 Tweaking a Chart’s Appearance 313
12.5 Adding and Removing Chart Elements 314
12.6 Formatting Chart Elements 314
12.7 Creating Dynamic Titles and Labels 320
12.8 Customizing Data Label Text 320
12.9 Controlling Chart Axes and Gridlines 321
12.10 Displaying Negative Values 323
12.11 Using Pictures in Column Charts 324
12.12 Formatting Pie of Pie and Bar of Pie Charts 325
12.13 Formatting a Histogram Chart 326
12.14 Specifying a Combination Chart’s Chart Types 327
12.15 Handling Empty Cells 329
12.16 Basing a Chart on Noncontiguous Data 329
12.17 Changing a Data Series Name and Legend Entry 330
12.18 Adding a Series or Changing the Data Source 331
12.19 Basing a Chart on a Dynamic Named Range 332
12.20 Inserting a PivotChart 334
12.21 Creating a Gantt Chart 335
12.22 Creating and Using Chart Templates 336
13. Graphics, Sparklines, and 3D Maps 339
13.1 Inserting Symbols 339
13.2 Inserting Equations 341
13.3 Inserting Shapes 341
13.4 Using the Draw Tool 342
13.5 Using SmartArt 343
13.6 Inserting Pictures 344
13.7 Grouping Objects 346
13.8 Moving and Sizing Objects with Cells 346
13.9 Inserting a Linked Picture 347
13.10 Using Sparklines 348
13.11 Using Sparkline Groups 351
13.12 Using 3D Maps 352
13.13 Creating Videos with 3D Maps 355
14. What-If Analysis 357
14.1 Creating a One-Variable Data Table 357
14.2 Creating a Row-Oriented One-Variable Data Table 359
14.3 Creating a Two-Variable Data Table 360
14.4 Editing Data Tables 362
14.5 Using Scenario Manager 363
14.6 Merging Scenarios 365
14.7 Generating Scenario Summaries 366
14.8 Using Goal Seek 368
14.9 Finding Multiple Solutions with Goal Seek 370
14.10 Handling Discontinuous Formulas with Goal Seek 371
14.11 Enabling Solver 372
14.12 Solving an Optimization Problem with Solver 373
14.13 Using Integer-Only Constraints with Solver 378
14.14 Using Binary-Only Constraints with Solver 381
14.15 Making Changing Cells All Different with Solver 384
14.16 Handling Discontinuities with Solver 387
14.17 Finding Multiple Solutions with Solver 389
14.18 Finding a Formula’s Global Minimum or Maximum with Solver 391
14.19 Adjusting Solver’s Options 392
14.20 Saving and Loading Solver Parameters 395
14.21 Saving Solver-Generated Scenarios 396
14.22 Displaying Solver Reports 396
15. Power Query 397
15.1 Getting and Loading Data 397
15.2 Getting and Loading Data from Files in a Folder 399
15.3 Specifying Where to Load Data To 400
15.4 Editing Data Source Settings and Security 402
15.5 Refreshing a Query’s Data 404
15.6 Managing Queries 405
15.7 Editing a Query 407
15.8 Managing a Query’s Steps 408
15.9 Managing Columns 409
15.10 Using Data Types 410
15.11 Sorting and Filtering Data 411
15.12 Filtering Files When Loading Data from a Folder 412
15.13 Removing Duplicates, Blank Rows, and Errors 413
15.14 Transforming Data in Columns 414
15.15 Splitting and Merging Columns 418
15.16 Pivoting Columns 419
15.17 Unpivoting Columns 420
15.18 Transforming Structured Columns 421
15.19 Returning a Value or List 423
15.20 Adding New Columns 424
15.21 Adding a Column Based on Examples 425
15.22 Adding a Conditional Column 427
15.23 Adding a Custom Column 428
15.24 Using Parameters 431
15.25 Creating a Custom Function 433
15.26 Adding a Column by Invoking a Custom Function 435
15.27 Duplicating a Query 436
15.28 Referencing a Query 437
15.29 Appending Data from Multiple Queries 438
15.30 Merging Data from Multiple Queries 440
15.31 Editing a Query’s M Code 442
16. Power Pivot and the Data Model 443
16.1 Installing Power Pivot 443
16.2 Adding Data to the Data Model 444
16.3 Managing Power Pivot Data Connections 446
16.4 Viewing and Managing the Data Model’s Tables 447
16.5 Refreshing the Data Model’s Data 449
16.6 Working with Table Columns 450
16.7 Creating and Editing Relationships 452
16.8 Adding a Calculated Column 454
16.9 Basing a PivotTable or PivotChart on Data Model Tables 457
16.10 Inserting Measures 459
16.11 Using KPIs 462
16.12 Creating Hierarchies 465
16.13 Creating a Date Table 468
16.14 Using Named Sets 470
16.15 Converting a PivotTable to Formulas 473
16.16 Using Cube Formulas 475
16.17 Filtering Cube Formulas with Slicers and Timelines 478
17. LET, LAMBDA, and LAMBDA Helper Functions 481
17.1 Improving Formula Efficiency 481
17.2 Writing and Testing a LAMBDA Formula 482
17.3 Making LAMBDA Arguments Optional 484
17.4 Defining a Custom LAMBDA Function 485
17.5 Writing Recursive LAMBDA Formulas 487
17.6 Copying a Custom LAMBDA Function to Another Workbook 489
17.7 Applying a LAMBDA Formula to Each Column 490
17.8 Applying a LAMBDA Formula to Each Row 492
17.9 Creating an Array of Calculated Values 493
17.10 Transforming the Values in Arrays 495
17.11 Calculating Cumulative Values 497
17.12 Returning the Final Value of a Cumulative Calculation 499
18. Developer Tools: Macros, VBA, Controls, and XML 501
18.1 Showing the Developer Tab 501
18.2 Recording a Macro 502
18.3 Using a Personal Macro Workbook 504
18.4 Editing a Macro’s Options 505
18.5 Running a Macro 506
18.6 Viewing or Editing a Macro’s VBA Code 507
18.7 Using Absolute and Relative References 510
18.8 Creating a Macro by Writing VBA 511
18.9 Creating a Custom VBA Function 512
18.10 Using Worksheet and Workbook Events 515
18.11 Overriding Keystrokes with OnKey 517
18.12 Scheduling Code with OnTime 519
18.13 Deleting a Macro or Function 520
18.14 Copying Code to Another VBA Project 521
18.15 Debugging VBA Code 523
18.16 Using Built-in Dialog Boxes 528
18.17 Using Form Controls 529
18.18 Using ActiveX Controls 532
18.19 Creating a UserForm 537
18.20 Creating a Custom Excel Add-in 540
18.21 Setting Security and Privacy Options 542
18.22 Importing and Exporting XML 543
Index 547