我们上一章聊了如何快速、正确的编写Excel函数公式的11个小技巧；从单篇数据可以看得出来，大家都是实干家，所以并不在意那些小技巧——我不是吓唬你们嘿，函数新人所犯的错误，通常就是由于不知道那些小技巧，所以再次强烈推荐大家看一看：如何快速编写正确的函数公式？

作为《从入门到精深，Excel函数轻松学》的第17篇推文，我们这一章来聊……坑。

常在河边走，哪能不湿脚？人在江湖飘，哪能不挨刀？所以掉坑不可怕，可怕的是没掉过坑——连坑都没掉过，也敢说会函数？切~单纯~

         

本章概要：

1，单元格引用地址变形记

2，你今天绝对引用了吗

3，不要搞混参数的默认值

4，参数的省略和留白

5，如何屏蔽公式返回的零值？

6，真空和假空，坑你没商量

7，函数无法跨工作簿引用怎么办

8，有哪些函数可以跨工作簿自动读取数据？

1，单元格引用地址变形记

一觉醒来，忽然发现Excel变得连脸都不认识了。输入公式=A1，莫名其妙变成了=R[XX]C[XX]，让人一脸懵懂。

我们在函数教程的第2章讲过，单元格引用有两种样式。

一种是A1引用样式，这是系统默认的，也是最为常用的。其中A是指列标，1是指行号，例如C5单元格，也就是指C列和第5行交叉处的单元格。

另一种是R1C1引用样式，比较少见，其中R是英文ROW的首字母，代表行。C是英文COLUMN的首字母，代表列。R5C3单元格也就是第5行和第3列交叉处的单元格，等同于A1引用样式中的C5单元格。

有时候您可能会收到列标变为数字的表格，这可能是因为制表者在无意间设置了R1C1的单元格引用样式，取消的方法很简单，依次单击【文件】→【选项】→【公式】，然后撤销R1C1引用样式即可。

         

2，你今天绝对引用了吗？

单元格引用有两种形式，相对引用和绝对引用。相对引用的地址会随着公式所在位置的改变而改变，绝对引用的地址永恒不变——但很多函数新人会忘记绝对引用的存在，比如说……

         

如上图所示，需要在E列查询D列人员的考试成绩，有些朋友会在E2输入以下公式：

=VLOOKUP(D2,A2:B10,2,0)

公式有错吗？有！哪里错了？查询范围没有绝对引用！可是E2返回正确结果了啊？——但是E4没返回正确结果啊？当E2的公式向下复制填充到E4，就变成了：

=VLOOKUP(D4,A4:B12,2,0)

查询范围已经不包含A2和A3了。因此正确的查询函数应该是：

=VLOOKUP(D4,A$4:B$12,2,0)

所以，我们说，做人不要太抠门，该给钱时就给钱——听不懂这句话说明您——没有——看——我们的——函数——教程~

3，不要搞混参数的默认值

         

同样以上图所示的数据为例，有时候我们会把公式写成：

=VLOOKUP(D2,A:B,2)

公式可以返回结果，但结果并不正确。

公式省略了第4参数，也就是VLOOKUP的查询匹配机制。有朋友会说，VLOOKUP省略第4参数不是默认采用精确匹配吗？——真的吗？

当然不是。

我们很明确的讲过，当VLOOKUP第4参数为0时，才代表零失误精确匹配。如果省略第4参数，则默认为近似匹配。

具有同样问题的还有MATCH函数等，所以千万不要搞错函数参数的默认值。

4，参数的省略和留白

         

还是以上图所示的数据表为例，有时候我们又会把VLOOKUP公式写成这样：

=VLOOKUP(D2,A:B,2,)

有朋友会说，咦，你这公式和上面那条公式，不是一样吗？

=VLOOKUP(D2,A:B,2,)

=VLOOKUP(D2,A:B,2)

放大一下看，一样吗？不一样吧~

         

第一条公式多了个逗号，VLOOKUP函数第4参数，作了留白处理。而第二条公式没有逗号，直接省略了第4参数。

省略和留白是不一样的。省略状态下，并不存在该参数，留白状态下，参数是存在的，只是没有输入数据，但会被Excel默认为0。而在VLOOKUP函数中，第4参数为0，即为零失误精确匹配查询。

尽管对参数留白会显得很酷，但我们还是建议大家使用完整的参数描述，这既显得你公式编写规范，又可以避免无意间犯下错误。

5，如何屏蔽公式返回的零值？

         

还是以上图所示的数据表为例，E2公式如下：

=VLOOKUP(D2,A:B,2,0)

有一个问题，当数据源的B列ID号为空白时，公式返回为0，比如E2单元格。这是由于在公式计算中，空白单元格默认为0。

不过，我们往往并不希望空白单元格显示为0，而是希望它显示为空白。

如果函数的计算结果为文本，如此例，我们可以将公式修改为：

=VLOOKUP(D2,A:B,2,0)&""

         

但是，如果公式的计算结果为数值，我就不推荐使用这种方法来屏蔽公式返回的零值了，尽管它也可以做到。这是由于它会将数值类型的数据，转换为文本型数值，并不利于数据的再次运算，比如求和、求平均、条件求和等。

同样的缘故不推荐使用TEXT函数屏蔽零值，TEXT是文本函数，一切文本函数返回的结果必然为文本。

此时推荐使用自定义单元格格式：

G/通用格式;G/通用格式;

         

6，真空和假空，坑你没商量

我们上一节讲，为了屏蔽公式返回的零值，我们很可能采用&""的方式，这是很常见的。

尽管这种方式会将公式返回的零值屏蔽为空白单元格，但单元格并非真正的空白，实际上它存在了一对半角双引号：""。这一类单元格我们称之为假空，假空的本质是一个文本值。

而真空单元格，顾名思义单元格是空白的，看起来是空白的，实际上也是空白的。

从肉眼上我们很难判断一个单元格是真空还是假空。

而假空的本质是文本，文本是不能参与数学运算的，否则会返回错误值#VALUE!

         

此外，在函数教程里我们介绍了几个和假空有关的函数。

比如COUNTBLANK，号称计算空白单元格的个数，实际上并不区分真空和假空，这两者在它眼中都是空白单元格。

同时对于COUNTIF和SUMIF来说，=COUNTIF(A:A,"")，同样并不区分真空和假空，这两者在该公式中，也都认为是空白单元格。

计算真空单元格的个数，我们通常使用=COUNTIF(A:A,"=")

7，函数无法跨工作簿引用数据怎么办

这个问题最常出现在07版Excel，07版Excel是一个过渡版本，有很多BUG，所以我们并不推荐大家使用，推荐大家使用骨灰级的03版都别用07版，不然有一天你怎么被Excel坑死的都不晓得。

10和13版偶尔也会遇到这种情况，16和19版就不会遇到这种情况。

如果有遇到这种情况，解决方法有三种。

1）重新设置文件默认的打开方式。

2）修改注册表。

3）卸载Office软件重新安装。

4）一劳永逸，升级Office到16版。

优先推荐第一种方案。右键单击某个Excel文件，在弹出的菜单中选择【打开方式】→【选择其他应用】，勾选【始终使用此应用打开.xlsx】。

         

8，有哪些函数可以跨工作簿自动读取数据？

大部分函数和公式，在工作簿未打开的情况下，是不能跨工作簿读取数据的。

我们说的是大部分，意思是还有一小部分会例外。

最常用的有两个：等号和VLOOKUP。

即便工作簿未打开，只要输入完整的工作簿地址，等号一样可以将数据引用到当前工作表。

比如：='D:\函数教程\[半岛湾项目组.xlsx]Sheet1'!B2

这可以解决很多问题。比如，使用等号将某个工作簿指定工作表的数据全部引用到当前工作簿的一张工作表中，也就无需打开目标工作簿，即可直接处理相关数据。

单纯的VLOOKUP函数也不需要打开目标工作簿，一样可以读取数据，并且它可以将数据放入自身的缓存之中，即便你将目标工作簿删除了，也不妨碍它运算。

         

比如公式：=VLOOKUP(A2,'D:\函数教程\[半岛湾项目组.xlsx]Sheet1'!A:B,2,0)

不用打开半岛湾项目组.xlsx，一样可以对其Sheet1工作表进行查询运算。

这可以解决很多问题。一次性设置公式，即可批量处理多个工作簿，以后只需要收集、更新其它工作簿的数据，总表数据自可自动更新。

……