精通Excel 2010公式：财务计算与数组公式的实战指南

需积分: 10 139 浏览量更新于2024-07-24 收藏 10.58MB PDF 举报

"Excel 2010 Formulas 公式" Excel 2010 Formulas 是一本专门针对Microsoft Excel 2010版本的指南，由Excel专家John Walkenbach编写。这本书全面介绍了如何利用Excel 2010的所有最新功能来创建和运用公式，帮助用户提升在财务计算、数组公式、查找公式以及条件格式化等方面的技能。同时，书中还涉及了自定义函数的开发，提供了解决各种常见及不那么常见的Excel公式挑战的实用方案。在书中，作者演示了如何利用Excel 2010的新特性来创建财务公式，这些公式对于商业分析和决策至关重要。例如，如何运用财务函数如PV（现值）、FV（未来值）和NPV（净现值）来评估投资回报，以及如何使用IF函数进行条件判断，实现更复杂的逻辑运算。此外，书中还详细讲解了数组公式的运用，这是Excel中一种强大的工具，能够一次性处理多行或多列的数据，大大提高了数据处理效率。对于查找和引用公式，如VLOOKUP、HLOOKUP和INDEX/MATCH，书中提供了详细的步骤和实例，教会读者如何在大量数据中快速准确地找到所需信息。条件格式化是Excel中的另一大亮点，通过设置规则，可以自动改变单元格的样式，以便于对数据进行可视化分析。书中将介绍如何设置和管理这些规则，以提升数据的可读性。除了内置的函数和公式，书中还涵盖了创建和使用自定义函数的方法，让读者能够根据特定需求扩展Excel的功能。这包括VBA（Visual Basic for Applications）的基础知识，以及如何编写简单的宏来自动化重复性的公式操作。书中的资源还包括了一张附赠的CD-ROM，其中包含了书中所有使用的Excel工作簿文件，便于读者跟随练习。同时，还可以访问“Mr. Spreadsheet”网站，获取更多的模板、工作表，以及John Walkenbach获奖的Power Utility Pak工具包，进一步增强Excel的工作效能。 Excel 2010 Formulas 是一本深入浅出的Excel公式使用手册，适合于从初学者到高级用户的各个层次，旨在帮助读者充分利用Excel 2010的功能，提升数据分析和决策能力。无论是处理日常工作任务，还是应对复杂的计算挑战，这本书都提供了丰富的知识和实践经验。

xiv

Working with Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 239

Creating a table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 240

Changing the look of a table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 240

Navigating and selecting in a table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .241

Adding new rows or columns . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 242

Deleting rows or columns . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 242

Moving a table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 243

Setting table style options . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 243

Removing duplicate rows from a table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 244

Sorting and filtering a table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 245

Working with the Total row . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 249

Using formulas within a table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 252

Referencing data in a table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 254

Converting a table to a worksheet database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 257

Using Advanced Filtering . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 258

Setting up a criteria range . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 259

Applying an advanced filter . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 260

Clearing an advanced filter . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 262

Specifying Advanced Filter Criteria . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 262

Specifying a single criterion . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 262

Specifying multiple criteria . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 264

Specifying computed criteria . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 267

Using Database Functions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 268

Inserting Subtotals . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 270

Chapter 10: Miscellaneous Calculations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 275

Unit Conversions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 275

Solving Right Triangles . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 277

Area, Surface, Circumference, and Volume Calculations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 280

Calculating the area and perimeter of a square . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 280

Calculating the area and perimeter of a rectangle . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 280

Calculating the area and perimeter of a circle . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 280

Calculating the area of a trapezoid . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .281

Calculating the area of a triangle . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .281

Calculating the surface and volume of a sphere . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .281

Calculating the surface and volume of a cube . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 282

Calculating the surface and volume of a cone . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 282

Calculating the volume of a cylinder . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 282

Calculating the volume of a pyramid . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 283

Solving Simultaneous Equations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 283

Rounding Numbers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 284

Basic rounding formulas . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 285

Rounding to the nearest multiple . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 286

Rounding currency values . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 286

Working with fractional dollars . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 288

Using the INT and TRUNC functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 288

Rounding to an even or odd integer . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 289

Rounding to n significant digits . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 290

02_475362-ftoc.indd xiv02_475362-ftoc.indd xiv 4/14/10 9:14 PM4/14/10 9:14 PM

Part III: Financial Formulas

Chapter 11: Borrowing and Investing Formulas . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 293

Financial Concepts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 293

Time value of money . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 294

Cash in and cash out . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 294

Matching time periods . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 295

Timing of the first payment . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 295

The Basic Excel Financial Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 295

Calculating present value . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 295

Calculating future value . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 299

Calculating payments . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 302

Calculating rates . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 303

Calculating periods . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 306

Calculating the Interest and Principal Components . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 307

Using the IPMT and PPMT functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 308

Using the CUMIPMT and CUMPRINC functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 309

Converting Interest Rates . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 310

Methods of quoting interest rates . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 310

Conversion formulas . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 311

Limitations of Excel’s Financial Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .312

Deferred start to a series of regular payments . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .312

Valuing a series of variable payments . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .313

Bond Calculations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .314

Pricing bonds . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .314

Calculating yield . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .316

Chapter 12: Discounting and Depreciation Formulas . . . . . . . . . . . . . . . . . . . . . . . . . . . . .317

Using the NPV Function . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .317

Definition of NPV . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .318

NPV function examples . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .319

Using the NPV function to calculate accumulated amounts . . . . . . . . . . . . . . . . . . . . . . . . . 325

Using the IRR Function . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 327

Rate of return . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 328

Geometric growth rates . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 329

Checking results. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 330

Multiple Rates of IRR and the MIRR Function . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .331

Multiple IRRs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .331

Separating flows . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 332

Using balances instead of flows . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 333

Irregular Cash Flows . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 334

Net present value . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 334

Internal rate of return . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 335

Using the FVSCHEDULE Function . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 336

Calculating an annual return . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 336

Depreciation Calculations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 337

02_475362-ftoc.indd xv02_475362-ftoc.indd xv 4/14/10 9:14 PM4/14/10 9:14 PM

xvi

Chapter 13: Financial Schedules . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .341

Creating Financial Schedules . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .341

Creating Amortization Schedules . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 342

A simple amortization schedule . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 342

A dynamic amortization schedule . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 345

Using payment and interest tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 348

Credit card calculations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 350

Summarizing Loan Options Using a Data Table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .351

Creating a one-way data table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .351

Creating a two-way data table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 353

Financial Statements and Ratios . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 355

Basic financial statements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 355

Ratio analysis . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 359

Creating Indices . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 362

Part IV: Array Formulas

Chapter 14: Introducing Arrays . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 367

Introducing Array Formulas . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 367

A multicell array formula . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 368

A single-cell array formula . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 369

Creating an array constant . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 370

Array constant elements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .371

Understanding the Dimensions of an Array . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 372

One-dimensional horizontal arrays . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 372

One-dimensional vertical arrays . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 372

Two-dimensional arrays . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 373

Naming Array Constants . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 374

Working with Array Formulas . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 376

Entering an array formula . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 376

Selecting an array formula range . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 376

Editing an array formula . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 376

Expanding or contracting a multicell array formula . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 377

Using Multicell Array Formulas . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 378

Creating an array from values in a range . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 378

Creating an array constant from values in a range . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 379

Performing operations on an array . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 379

Using functions with an array . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .381

Transposing an array . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .381

Generating an array of consecutive integers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 382

Using Single-Cell Array Formulas . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 383

Counting characters in a range . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 383

Summing the three smallest values in a range . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 384

Counting text cells in a range . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 385

Eliminating intermediate formulas . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 387

Using an array in lieu of a range reference . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 389

02_475362-ftoc.indd xvi02_475362-ftoc.indd xvi 4/14/10 9:14 PM4/14/10 9:14 PM

xvii

Chapter 15: Performing Magic with Array Formulas . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .391

Working with Single-Cell Array Formulas . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .391

Summing a range that contains errors . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .391

Counting the number of error values in a range . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 393

Summing the n largest values in a range . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 394

Computing an average that excludes zeros . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 394

Determining whether a particular value appears in a range . . . . . . . . . . . . . . . . . . . . . . . . . 395

Counting the number of differences in two ranges . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 396

Returning the location of the maximum value in a range . . . . . . . . . . . . . . . . . . . . . . . . . . . 397

Finding the row of a value’s nth occurrence in a range . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 397

Returning the longest text in a range . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 398

Determining whether a range contains valid values . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 398

Summing the digits of an integer . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 399

Summing rounded values . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .400

Summing every nth value in a range . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 401

Removing nonnumeric characters from a string . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 402

Determining the closest value in a range . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 402

Returning the last value in a column . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .404

Returning the last value in a row . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 404

Ranking data with an array formula . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .404

Working with Multicell Array Formulas . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .406

Returning only positive values from a range . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 406

Returning nonblank cells from a range . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 407

Reversing the order of cells in a range . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 407

Sorting a range of values dynamically . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .408

Returning a list of unique items in a range . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 408

Displaying a calendar in a range . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 410

Part V: Miscellaneous Formula Techniques

Chapter 16: Intentional Circular References . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .415

What Are Circular References? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .415

Correcting an accidental circular reference . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 416

Understanding indirect circular references . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .417

Intentional Circular References . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .417

How Excel Determines Calculation and Iteration Settings . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 420

Circular Reference Examples . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .421

Generating unique random integers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .421

Solving a recursive equation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 423

Solving simultaneous equations using a circular reference . . . . . . . . . . . . . . . . . . . . . . . . . . 424

Animating a chart using iteration . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 426

Potential Problems with Intentional Circular References . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 427

Chapter 17: Charting Techniques . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 429

Understanding the SERIES Formula . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 429

Using names in a SERIES formula . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .431

Unlinking a chart series from its data range . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 432

02_475362-ftoc.indd xvii02_475362-ftoc.indd xvii 4/14/10 9:14 PM4/14/10 9:14 PM

xviii

Creating Links to Cells . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 434

Adding a chart title link . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 434

Adding axis title links . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 435

Adding links to data labels . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 435

Adding text links . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 435

Adding a linked picture to a chart . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 436

Chart Examples . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 436

Charting progress toward a goal . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 436

Creating a gauge chart . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 438

Displaying conditional colors in a column chart . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 439

Creating a comparative histogram . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .440

Creating a Gantt chart . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 441

Creating a box plot . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 443

Plotting every nth data point . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 446

Plotting the last n data points . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 447

Selecting a series from a combo box . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 448

Plotting mathematical functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 450

Plotting a circle . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 455

Creating a clock chart . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 457

Creating awesome designs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 460

Working with Trendlines . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 461

Linear trendlines . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 462

Working with nonlinear trendlines . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 466

Chapter 18: Pivot Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 473

About Pivot Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 473

A Pivot Table Example . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 474

Data Appropriate for a Pivot Table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 476

Creating a Pivot Table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 479

Specifying the Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .480

Specifying the location for the pivot table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 480

Laying out the pivot table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 481

Formatting the pivot table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 484

Modifying the pivot table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 485

More Pivot Table Examples . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 487

Question 1 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 487

Question 2 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 488

Question 3 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 489

Question 4 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 489

Question 5 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .490

Question 6 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 491

Question 7 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 492

Grouping Pivot Table Items . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 493

A manual grouping example . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 493

Viewing grouped data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 495

Automatic grouping examples . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 496

Creating a Frequency Distribution. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .500

02_475362-ftoc.indd xviii02_475362-ftoc.indd xviii 4/14/10 9:14 PM4/14/10 9:14 PM

剩余817页未读，继续阅读

scut_yituoshi

粉丝: 1
资源: 10

精通Excel 2010公式：财务计算与数组公式的实战指南

Excel 2010 Formulas_John Walkenbach

Excel 2016 formulas

Formulas and Functions Microsoft Excel 2010s

Excel 2010 Formulas

Microsoft Excel 2019 Formulas and Functions

formulas:Python中的Excel公式解释器

Practical PowerPivot & DAX Formulas for Excel 2010

Excel Formulas and Functions For Dummies, 2nd Edition

101 Ready-to-Use Excel Formulas.pdf

QUE.Formulas.and.Functions.Microsoft.Excel.2010.May.2010.rar

最新资源