打破Ｅｘｃｅｌ的枷锁.docVIP

打破Ｅｘｃｅｌ的枷锁 　　大家知道，在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。