打破Excel的枷锁

大家知道，在Excel中的某些函数存在着一些限制。但是在实际工作中，各种各样的需求经常会要求我们突破Excel的限制。那么，如何打破Excel的枷锁呢？

一、妙用数组公式进行统计

Excel提供了多种单元格计数的方法，可以根据不同条件选用，例如DCOUNT、COUNT、COUNTA、COUNTBLANK、COUNTIF等。

在多个条件下进行总计和计数是Excel应用中经常遇到的问题。如果数据位于数据清单中，用户可以使用数据库函数(如DCOUNT或DSUM等)。这些函数要求工作表中必须指定一个独立的条件区域。但是在实际应用环境中，用户可能受到限制，例如工作表处于保护状态，无法设定输入条件区域，或者用户不希望使用额外单元格，希望工作表更加简洁明快。

解决这样的问题有多种方法。下面给出一些示例，利用这些示例可以解决绝大多数的计数和总计问题。与数据库函数的不同之处在于这些公式不需要条件区域。示例公式使用了图 1的数据清单，用户可以根据实际数据灵活选用，适当修改。

利用SUMIF、COUNTIF函数可以实现一些简单的统计。例如统计IT部门的薪金总计可以使用公式：=SUMIF(B2:B10,"IT",D2:D10)，统计IT和销售部门的薪金总计，可以使用公式： =SUMIF(B2:B10,"IT",D2:D10)+SUMIF(B2:B10,"销售",D2:D10)。不过当不同字段列中存在多个条件时，SUMIF和COUNTIF函数就没有用武之地了。

要实现不同列离散条件的统计计算，可以使用数组公式。使用数组公式时请注意：输入数组公式的时候要在确认的时候按下Ctrl+Shift+Enter组合键，否则会出现#VALUE!错误。确认输入数组公式之后，公式栏中公式两端会出现花括号“{ }”。

下面示例公式要求以数组公式的方式输入：部门="IT" 并且性别="男"的薪金总计：=SUM((B2:B10="IT")*(C2:C10="男")*D2:D10)

二、打造多才多艺的自定义函数

大多数情况下，一个Excel工作表函数只能执行单一操作。但是在实际工作中，用户可能希望能够在一个公式中根据实际需求灵活选取某个函数功能进行计算。要打破这种限制，用户可以使用VBA代码自己设计一个“多才多艺”的多功能函数，在这个自定义函数中根据用户的计算要求灵活选用计算功能。

要实现这样的要求，可以使用VBA编写一个名为Stat的用户自定义函数，它具有两个参数：命名区域(rng)和操作(op)。根据 op参数的单元格的实际内容，函数可以返回针对命名区域的求和、平均、计数、最大、最小、方差、标准偏差、中值、众数等计算结果。

在Excel工作表中，选择菜单“工具-宏-Visual Basic编辑器”命令，打开Visual Basic窗口，然后选择菜单“插入-模块”命令，插入一个名为“模块1”的模块，然后输入如下代码：

Public Function stat(rng, op)

Select Case op

Case "求和"

stat = Application.Sum(rng)

Case "平均"

stat = Application.Average(rng)

Case "计数"

stat = Application.Count(rng)

Case "最大"

stat = Application.Max(rng)

Case "最小"

stat = Application.Min(rng)

Case "方差"

stat = Application.Var(rng)

Case "标准偏差"

stat = Application.StDev(rng)

Case "中值"

stat = Application.Median(rng)

Case "众数"

stat = Application.Mode(rng)

Case Else

stat = "操作参数错误，在'" & op & "'单元格中输入'求和'、'平均'等"

End Select

End Function

在工作表中，用户可以使用如下方法调用函数：

=STAT(d2:d10,A30)

公式计算的结果取决于单元格A30，在A30单元格中，用户输入一个操作字符串，例如求和、平均、计数、最大、最小、方差、标准偏差、中值、众数等，stat函数会进行对应的计算。如果A28单元格中的文本不能识别，则会显示错误提示。以图1为例，假设A30单元格输入的是“平均”，上述公式的计算结果为：3066。

使用这种技巧还可以创建功能更加复杂的多功能函数。需要注意的是，Excel默认的宏安全性为“高”，用户应选择安全性为“中”以下才能运行宏。在安全性要求较高的场合，可以选择“工具”菜单下的“数字签名”选项，为VBA代码添加数字签名。

三、让单元格自己“变身”

Excel单元格中的数据输入完毕后，用户一般只能在其他单元格针对数据区域进行计算，而不能让计算结果显示在原来的单元格上。但是在很多情况下，用户希望能够直接计算之后让计算结果替换掉原来的单元格，例如计算加薪的时候，用户会希望将原来的薪金经过计算之后替换掉自身单元格，以免除手工复制粘贴的麻烦。

Excel工作表函数无法直接完成单元格自身的替换工作。要解决这个问题，可以使用VBA编写程序。我们可以使用Excel的Visual Basic 编辑器编制一个宏，利用For Each… Next循环对区域中的数值进行计算，并将结果保存回原单元格。代码如下：

Sub Macro1()

For Each c In range("salary")'d2:d10区域已经被命名为salary

c.Value = c.Value + 50 '每个单元格加50，并保存回原单元格Next c

End Sub

当用户需要进行单元格替换的时候，只需选择“工具”菜单中的“宏”选项，执行宏Macro1即可(如图2)。