第4章　让Excel变聪明

学习Excel的目标不是掌握所有的Excel函数，而是在掌握了一些最基本的最常用的函数之后，应该进一步学习那些能让Excel变得更聪明、更智能的逻辑函数，如IF，OR，AND，NOT，IFERROR函数和那些能够减少我们大量手工操作的查找引用函数，如VLOOKUP，INDEX，MATCH，CHOOSE，INDIRECT函数等。在此基础上，结合自己的专业知识，我们就可以借用Excel建立自己的管理模型和效率改善工具。

下面有选择地介绍几个对于Excel建模重要且常用的函数，使读者通过简单的案例了解其应用方法和使用场合。在理解和掌握了这些函数后，相信我们的Excel应用水平一定会跨上一个新的高度。

4.1　IFERROR函数

从Excel 2007开始，Excel新增加几个非常有用的函数，如新增的COUNTIFS()，SUMIFS，AVERAGEIFS函数让我们的多条件汇总变得更加容易。而新增的IFERROR函数能让我们的公式变得更简洁。

在这里我们重点介绍一下从Excel 2007开始新增的函数：IFERROR。

在实践中，我们可能经常遇到这样的情况：因为各种原因，某个公式的运算结果显示错误信息，如图4-1所示，关于人均营业额的计算，由于单元格C3中的营业额数据缺失，造成D3单元格中公式返回“#VALUE”错误信息，这个错误信息既不友好，又不美观。

图4-1

在Excel 2003中，为了抑制错误信息，我们通常借助IF和ISERROR函数来完成这个任务，例如公式“=IF(ISERROR(C2/B2),"",C2/B2)”，该公式的含义是：当C2/B2计算出现错误时，函数返回空字符串，如果没有错误返则回正常的计算结果，如图4-2所示。在这个公式中，计算逻辑“C2/B2”在公式中出现了两次，这很啰嗦，特别是当计算逻辑比较复杂时，公式会相当长。

图4-2

从Excel 2007开始，Excel提供了一个新的函数IFERROR，该函数有两个参数，第一个参数是我们的计算公式；第二个参数是当我们的计算公式出错时，用以替代出错信息的自定义内容。

有了IFERROR函数，前面的公式可以简化为“=IFERROR(C2/B2,"")”，它的含义是，如果公式计算出现错误，则返回空字符。这样，我们的计算逻辑在整个公式中只出现了一次，为将来的公式维护带来了很大的方便，如图4-3所示。

图4-3

4.2　VLOOKUP函数

有一个笑话：对于经常和Excel数据打交道的人员，只要你学会了VLOOKUP函数，哪怕你只是幼儿园毕业，也能在这个部门潜伏半年而不会被发现。

这个笑话虽然夸张，但似乎也有道理，这足以见得VLOOKUP函数之重要！

VLOOKUP函数通俗地说就是查字典函数，使用方法为“＝VLOOKUP（要查的词，作为字典的表格的位置，要查找内容所在的列，精确查找还是近似查找）”。

在1.4节的“函数的误用”中已经初步介绍了VLOOKUP函数通常的用法，即VLOOKUP函数最后一个参数为0（精确查找）的用法，下面我们再来介绍一下VLOOKUP函数另外一种用法，即VLOOKUP函数最后一个参数为1（近似查找时）的用法。

当VLOOKUP函数第四个参数为1时，可以用来处理按时间分段（Time-Phased）的数据，这样讲比较抽象，下面结合具体案例来讲解这个函数。

假设我们在市场部工作，现在有一个产品A的价格预测表，我们的任务是用Excel建一个模型，给定任意一个日期，立即得出该日期“适用的”产品价格，如图4-4所示。

图4-4

在这里，A1:A18是产品价格预测表，预测价格在每月的第1日进行一次调整，产品价格可能上升也可能下降。为了方便理解公式，我们使用了Excel定义名称功能把E1单元格定义名称为“任意日期”，产品价格预测表A1:B18定义名称为“预计价格”，并且“预计价格”表按照“产品A价格生效日期”中的日期升序排列。

注意：“预计价格”表（A1:B18）按照VLOOKUP函数要查找的参数（“产品A价格生效日期”）升序排列，这是使用VLOOKUP函数进行近似查找之前的必需操作！

在E2单元格中输入公式“=VLOOKUP(任意日期,预计价格,2,1)”，即可完成该任务！从计算结果中可以看到，2012年5月15日适用的价格为“36”。现在人工检查一下该计算结果是否正确：由于每个月月初调整价格，因此2012年5月15日的产品A的适用价格应该是“预计价格”表中的2012年5月1日的价格，而其对应的结果的确是36，说明公式运算结果正确。

下面总结一下VLOOKUP函数

VLOOKUP函数最后一个参数（第四个参数）为1或省略时（省略时默认为1），返回近似匹配值，即如果找不到要查找的内容（VLOOKUP函数的第一个参数）的精确匹配，则返回小于所查找的内容的最大数值所对应的信息，需要特别注意的是：VLOOKUP函数的第二个参数所对应的表格必须按照要查找的内容所对应的列升序排列，否则可能会返回不正确的结果。

如果觉得上面的解释太难懂，干脆就记住前面的例子，知道VLOOKUP函数的近似查找功能可以在类似前面这样的场合中派上用场就可以了！

用VLOOKUP函数的近似查找功能有时可以代替多重IF()函数嵌套，让多重判断的公式变得容易制作和管理。例如在图4-5所示的工作表中，根据考试成绩给学生评级，如果使用多重IF函数嵌套将是一个很长的公式，并且难以维护，使用了VLOOKUP函数的近似查找功能后，只需一个“级别列表”加一个简短的公式就完成任务了，而且公式修改和维护相当方便！

注意：在这里我们给单元格区域F1:G6定义名称为“级别列表”。C2中的公式为“=VLOOKUP(B2,级别列表,2,1)”。然后把公式向下复制到C3:C12单元格区域，如图4-5所示。

图4-5

4.3　INDEX和MATCH函数

如果想知道在一个单元格区域中，某行某列交叉点所在的单元格中是什么内容，可以用INDEX函数取出这个单元格中的内容。INDEX函数的语法是：

=INDEX(单元格区域的引用地址，第几行，第几列)

例如，在图4-6中，有一个单元格区域“B3:C4”，如果想得到该区域第1行第1列的数字，可以使用公式=INDEX(B3:C4,1,1)，因为我们前面已经学习了单元格区域的命名方法，在这里给单元格区域“B3:C4”取一个好记的名称为“转换时间”（之所以取这个名称，是因为我们后面还要使用它），这样我们的公式就可以写成“=INDEX(转换时间,1,1)”；同理，如果想得到该区域第2行第1列单元格中的数字，可以使用公式“=INDEX(转换时间,2,1)”，如图4-6所示。

图4-6

MATCH函数：如果在Excel中有一行或一列数值，那么给定一个数值，就可以使用MATCH函数得到该数值在这行或这列中的位置。如图4-7所示，如果把单元格区域“B2:F2”命名为“To列表”，那么，“红漆”在“To列表”中的位置就可以用公式“=MATCH("红漆",To列表,0)”得到，其中，MTACH函数的最后一个参数为0，表示精确查找，这个函数和VLOOKUP函数有些类似。

图4-7

INDEX函数和MATCH函数单独看起来很简单，但却很难想象出它们在实际工作中有什么特别的价值。确实是这样，这两个函数在单独使用时确实价值不大，但是如果把这两个函数结合起来，就会发挥出巨大的威力！

假设我们是某涂料制造工厂的管理人员，为了简化问题假设该涂料厂只有一台设备，该机器生产两种不同颜色的油漆（白色和黑色），工作经验告诉我们：

如果这台机器上一个班次生产的是白漆，下一个班次生产的也是白漆，因为两个班次生产的油漆颜色相同，在不同产品转换时，清理机器只需花1分钟的时间；如果这台机器上一个班次生产的是黑漆，而下一个班次生产的是白漆，需要彻底清理机器，因此清理该机器要花10分钟的时间；其他两种情况的清理时间请参见图4-8中单元格区域B2:E5所示的产品转换清理时间的列表。

图4-8

现在我们要解决的问题是，给定任意一种产品转换组合，立即得出转换所需的时间。这个问题可以用INDEX函数结合MATCH函数来解决。

解决这个问题的过程其实就是建立一个简单的Excel生产管理模型的过程。模型所需的转换清理时间参数在B2:E5单元格区域，为了使模型易于理解和维护，我们给参数表的不同部分分别命名了易于理解和记忆的名称。所有名称和它们所代表的单元格区域列表放在G2:H6单元格区域。解决这个问题的公式在D10单元格中。

D10单元格中的公式为：

=INDEX(转换时间,MATCH(From,From列表,0),MATCH(To,To列表,0))

为了便于分析，我们把公式写成如下格式：

如果名称为From的单元格（C8）中的内容为“白漆”时，公式“MATCH(From,From列表,0)”的计算结果为1，表示“白漆”位于“From列表”的第一个位置，名称“From列表”代表的单元格区域为C4:C5。

名称为To的单元格（E8）中的内容为“黑漆”时，公式“MATCH(To,To列表,0)”的计算结果为2，表示“黑漆”位于“To列表”的第二个位置，名称“To列表”代表的单元格区域为D3:E3。

原始公式“=INDEX(转换时间,MATCH(From,From列表,0),MATCH(To,To列表,0))”计算到这里简化为“=INDEX(转换时间,1,2)”，表示我们要在矩形单元格区域“转换时间”里取第1行第2列的数值，计算结果为2，名称“转换时间”代表的单元格区域为D4:E5。

完成这个模型的制作后，我们就可以给定名称为“From”和“To”所代表的产品，立即得到相应的产品转换时间。

如果想详细地了解D10单元格公式“=INDEX(转换时间,MATCH(From,From列表,0), MATCH(To,To列表,0))”的计算过程，还可以把鼠标放置于D10单元格，然后选择“公式→公式审核→公式求值”命令，使用Excel的“公式求值”工具一步一步地查看整个公式的计算过程。

到这里，我们已经具备了建立Excel管理模型的初步知识，好了，继续我们的Excel神奇之旅吧。

4.4　CHOOSE函数

先看一个公式（Excel 2007版本以上适用）：=CHOOSE(RANDBETWEEN(1,6),"做饭","洗衣","擦地","学习","锻炼","休息")。

当你要为做哪件事情犹豫不定时，可以打开Excel，在工作表中输入类似这样的一个公式让它帮你做一个决定。

下面解释一下这个公式的含义。RANDBETWEEN函数用来得到介于指定的两个数值之间的一个随机整数。而CHOOSE()函数用于得到一系列数值中的某一个值，这个值由CHOOSE()函数的第一个参数指定，比如公式“=CHOOSE(2,"A","B","C")”得到的结果是数值序列("A","B","C")中的第2个值，即字母“B”。

因此，公式：=CHOOSE(RANDBETWEEN(1,6),"做饭","洗衣","擦地","学习","锻炼","休息")的意思是从6个值组成的序列（"做饭","洗衣","擦地","学习","锻炼","休息"）中随机地取出一个数值，其效果相当于投骰子，我们可以把这个公式输入到Excel中，连续按快捷键F9（Excel重新计算的快捷键）查看效果。

在图4-9中，D9单元格中的公式为：

图4-9

可以得到在B9单元格中指定的三个组之一的总成绩，可见，CHOOSE函数的应用非常灵活。

4.5　OFFSET函数

OFFSET函数可谓是Excel函数中的“机灵鬼”，它能够让Excel模型非常灵活。OFFSET函数的语法是：

=OFFSET(参照点,返回区域相对于参照点偏移的行数,返回区域相对于参照点偏移的列数,返回区域行高数,返回区域列宽数)

为了便于更清晰地理解该函数，我们把上面的函数语法写成如下形式：

=OFFSET

(

参照点,

返回区域相对于参照点偏移的行数,

返回区域相对于参照点偏移的列数,

返回区域行高数,

返回区域列宽数

)

在这里，函数的“返回区域”意思是指“函数计算的结果是一个单元格区域”；“偏移”是“移动”的意思。

如图4-10所示，公式“=OFFSET($E$2,3,5,6,8)”的含义是：以单元格$E$2为参照点，向下移动3行，向右移动5列，得到一个新的单元格位置，然后以新的单元格作为起点，最后得到这样一个新的区域：新的区域的行高为6行，列宽为8列，即图中带阴影的区域。

由于OFFSET函数返回的结果为单元格区域，因此可以把这个单元格区域作为参数进行相关操作，例如把该函数的计算结果作为各种汇总函数的参数。在图4-10中，B8单元格中的公式为“=SUM(OFFSET($E$2,3,5,6,8))”，计算结果为48。

图4-10

如果OFFSET函数省略最后两个参数，即省略“返回区域行高数”和“返回区域列宽数”，则这两个参数默认值为1，函数运算结果只返回一个单元格。

正是由于OFFSET函数的超级灵活性，它在稍微复杂一点的Excel模型和Excel动态图表中经常用到。

4.6　INDIRECT函数

INDIRECT函数是一个非常有趣的函数，INDIRECT的意思是“间接的”，到底是怎么一个间接法，下面还是举例说明。

如图4-11中A1∶D4单元格中所示的数据，现在要从该数据区域中指定的单元格提取数据，为了增加提取数据的灵活性，我们设想自己能够分别指定目标单元格地址的“列标”和“行号”，例如，要提取B3单元格中的数据：

图4-11

（1）我们把目标单元格地址的“列标”放在H1单元格(B)；

（2）我们把目标单元格地址的“行号”放在H2单元格(3)。

H3单元格中的公式为：=INDIRECT(H1&H2)，该函数的含义是：将H1单元格中的内容所代表的列标（字母“B”）与H2单元格中的内容所代表的行号（数字“3”）连接起来。

注意：这时连接起来形成的“B3”仅仅是一个字符串而已，并不是真正的单元格地址引用，INDIRECT()函数作用就是把这个“看起来像单元格地址”的字符串转化成真正的单元格地址引用，从而取出对应的单元格B3中的数值。

通过这个例子我们可以了解到：INDIRECT函数的作用就是把“字符串形式的”单元格地址引用转化为真正的单元格地址引用！

再来观察H4单元格中的公式：=INDIRECT("B"&"3")，其运算结果和前一个公式一样，只是我们把目标单元格地址的“列标”和“行号”直接写在了INDIRECT函数里，不如前一个公式灵活。

这里介绍INDIRECT函数可不是用来玩数字游戏的，前面的例子是让我们理解INDIRECT函数的含义和用法，下面介绍一个非常有用的功能：利用INDIRECT函数制作联动下拉列表。

联动下拉列表又称“级联下拉列表”，就是一个下拉列表的选项与另外一个下拉列表的选项动态关联。如图4-12所示，当在“省份”一列选中某个省份名称时，其右侧的“城市”单元格中只显示对应省份的城市列表。

图4-12

联动下拉列表的制作过程如下。

首先给下拉列表选项所引用的单元格区域定义名称。先选中数据所在的区域，然后选择“公式→定义的名称→根据所选内容创建”命令，此时弹出“以选定区域创建名称”对话框。在该对话框中选择“首行”复选框。这表示，我们将使用每列数据的第一行的单元格中的内容作为该列数据的名称。单击“确定”按钮关闭对话框，如图4-13所示。

图4-13

自动创建名称完成后，单击名称栏，可以看到：取好的名称已经在那里了，如果选择“山东”，会发现工作表中所有属于山东省的城市被选中了，如图4-14所示。

图4-14

下面，我们利用数据有效性制作级联下拉列表。

如图4-15所示，在单元格区域E1:F10中制作一个表格，这里希望利用Excel数据有效性技术，在E 列选择省份时，对应的F列单元格中显示该省份的城市列表。

图4-15

下面先利用数据有效性制作“省份”这一列的下拉列表。

选择“数据→数据工具→数据有效性”命令，打开“数据有效性”对话框，选择“设置”标签，在“有效性条件”中的“允许”下拉框中选择“序列”选项。在“来源”列表中用单元格区域选取按钮选择单元格区域引用地址“=$A$1:$B$1”，表示我们要用工作表中A1:B1单元格中的内容作为数据有效性序列表的下拉选项（即这里的“河北”和“山东”）。最后单击“确定”按钮，如图4-15所示。

这时，我们看到数据有效性下拉列表设置成功，如图4-16所示。

图4-16

下面的问题是：如何设置数据有效性，让F列中的每一个单元格中的下拉选项根据E列单元格中的内容动态地调整呢？比如，当E2单元格中为“河北”时，F2单元格中只能选择属于河北省的城市。

由于我们已经在Excel中对每个城市列表定义了以其所在省份定义的名称，因此可以利用已经定义的省份名称来代表对应的城市列表。

选中单元格区域F2:F10（见图4-17），然后选择“数据→数据工具→数据有效性”命令，打开“数据有效性”对话框。在该对话框中的“允许”下拉框中选择“序列”选项。在“来源”列表框，我们针对“选中区域中的活动单元格（F2）”输入公式“=INDIRECT(E2)”。

图4-17

我们已经知道：INDIRECT函数的作用就是把字符串形式的单元格地址引用转化为真正的单元格地址引用，并且Excel中名称技术的本质也是一种单元格地址引用。

因此，这里的公式=INDIRECT(E2)的作用是：当前活动单元格（F2）中的数据有效性序列是由其左侧的单元格中的内容所决定的，如果当前活动单元格左侧的单元格（E2）中的内容是“河北”时，那么当前单元格中的数据有效性序列应该是=INDIRECT("河北")，在这里，“河北”作为INDIRECT函数的参数只是一个普通的字符串，但是经过INDIRECT函数加工后，INDIRECT函数把“河北”这个普通字符串变成了一个真正的单元格地址引用，也就是把“河北”这个字符串变成了真正的Excel中的名称。

制作级联下拉列表的最终效果如图4-18所示，当我们在单元格E3中选择“山东”时，其右侧的F3单元格中只有属于山东省的城市显示在下拉列表中。

图4-18

4.7　SUMPRODUCT函数

SUMPRODUCT函数可谓是Excel中的“超级函数”。顾名思义，SUMPRODUCT函数的含义就是先“PRODUCT”（相乘）再“SUM”（求和），这看起来似乎并不复杂，不过用起来可没有这那简单。

在图4-19中，B9单元格中的公式为“=SUMPRODUCT(A2:A8,B2:B8)”，表示把两个单元格区域A2:A8和B2:B8中行对应的单元格的数值相乘，然后再把所有得到的乘积累加。

图4-19

SUMPRODUCT函数可以接受一组或多组参数，如果是多组参数，要求每组参数有相同的行和列。

如此看来SUMPRODUCT函数的功能也没有什么大不了的！如果SUMPRODUCT函数只能做这些，确实也没什么，不过现在要告诉大家，SUMPRODUCT函数可以做多条件汇总计算，这是很厉害的一个功能！

先由简单的例子说起，在如图4-20所示的工作表中，如果我们只想计算“工程部”的员工工资总额，公式可以写成：=SUMPRODUCT(("B2:B13"="工程部")*(D12:D13))。

图4-20

公式“=SUMPRODUCT(("B2:B13"="工程部")*(D12:D13))”的含义介绍如下。

公式中的（"B2:B13"="工程部"）用于判断哪些部门属于“工程部”，如果某个单元格内容等于“工程部”，则对应的计算结果为“TRUE”，否则为“FALSE”，因此，公式中的（"B2:B13"="工程部"）计算结果为一列由TRUE和FALSE组成的数值（计算结果竖向排列，与原始数据排列方向保持一致）。

TRUE和FALSE是Excel中的一种特殊类型的数据，如果公式中的判断条件成立，运算结果为TRUE，否则为FALSE；TRUE和FALSE还有一个特性，如果用TRUE和FALSE参与数学运算，那么TRUE和FALSE分别可以当做1和0使用（TRUE代表1，FALSE代表0）。

SUMPRODUCT公式前半部分（"B2:B13"="工程部"）运算得到的一列由TRUE和FALSE组成的数值与后半部分（D12:D13）的数值相乘时，由TRUE和FALSE组成的数值分别当做1和0与（D12:D13）中对应的数值相乘，其运算结果是：属于“工程部”员工对应的月薪保留了下来，而其他部门对应的员工的月薪变成了0，最后SUMPRODUCT()函数对(“B2:B13”=“工程部”)*(D12:D13)的计算结果做了一下汇总，得到我们最终期望的结果。

Excel中有一个非常有用的工具叫做“公式求值”，它可以帮助我们进一步理解SUMPRODUCT函数。首先选中要分析的公式所在的单元格，然后选择“公式→公式审核→公式求值”命令，此时弹出“公式求值”对话框，如图4-21所示。“公式求值”工具可以对复杂公式执行分步计算，这样，我们可以通过观察公式的计算过程来分析公式的逻辑，如图4-21所示。

图4-21

单击“公式求值”对话框下部的“求值”按钮，公式的（"B2:B13"="工程部”）下部出现了一条下画线，表示开始要对整个公式的这一部分进行求值，如图4-22所示。

图4-22

继续单击“求值”按钮，公式（"B2:B13"="工程部"）部分的计算结果如图4-23所示，TRUE和FALSE之间用分号隔开，表示这是一列数据（如果是一行数据，会用逗号分隔）。

图4-23

继续单击“求值”按钮，直至出现如图4-24所示的内容，现在马上就要进行SUMPRODUCT函数的对应元素相乘（PRODUCT）的操作了。

图4-24

前面讲过，TRUE和FALSE参与数学运算时，TRUE作为1，FALSE作为0，因此图4-24的求值结果如图4-25所示。

图4-25

最后一步，对应元素相乘的结果进行汇总（SUM），完成整个公式的计算，如图4-26所示。

图4-26

了解SUMPRODUCT函数条件汇总原理后，下面用SUMPRODUCT函数处理更复杂一点的汇总计算，如图4-27所示。

图4-27

现在要计算“工程部”青年员工（小于40岁的员工）的工资总额。这相当于在前面那个公式的基础上又增加了一个条件（C2:C13<40），基本计算逻辑和前面的公式相同，读者可以用“公式求解”工具分步分析一下。

微言微语

●　企业周期性的裁员已经成了惯例，要想在职场中立于不败之地，必须要有一技之长。Excel作为除了专业技能之外的另一个重要的技能，能够熟练地使用Excel改善效率，为企业创造价值的员工一定会脱颖而出。很难想象一个20分钟就能完成的报告，却需要两个小时完成而且错误百出的员工能够得到重视。

●　简单地说，Microsoft Office无代码开发技术是充分利用Microsoft Office（Excel，Access等）软件的内置功能，在不写代码的情况下，设计出高效实用的工作效率改善工具的一种技术。由于不需要编写程序代码，该技术消除了大多数人对编写程序的恐惧感，更容易为人所接受，让普通Office用户也能获得设计程序的成就感。