为什么VLOOKUP出错的频率比人们想象的更高
VLOOKUP是通过"位置编号"在查找区域内引用某一列的,所以只要在这个区域内的任何位置插入或删除一列,所有VLOOKUP公式的结果都会跟着偏移,而且不会有任何错误提示——公式依然会正常运行,只是悄悄地返回了错误的答案。这个特性正是"我的VLOOKUP昨天还好好的"这类问题最常见的原因,也是在规模更大、编辑更频繁的表格中,人们经常推荐使用INDEX+MATCH的主要原因。
真正的威力体现在函数组合使用的时候
在实际工作中,这些函数很少被完全孤立地使用——把COUNTIF嵌套在IF里面,或者把SUMIF的结果和VLOOKUP结合起来,是熟悉每个函数之后很常见的用法。学会每个函数的语法只是第一步,学会把两三个函数组合起来回答一个具体问题,才是真正能在日常工作中节省时间的关键。
学习顺序和练习方法
新手可以先从SUM和AVERAGE入手熟悉区域选取,再学习IF的条件判断,然后用COUNTIF、SUMIF做简单统计,最后再进入VLOOKUP(有余力的话继续学INDEX+MATCH或XLOOKUP)。练习时建议用自己熟悉的数据,比如自己的记账表或排班表,因为你清楚结果应该是什么,更容易发现公式写错了。每个函数先在十行左右的小表里试一遍,结果符合预期后再套用到大表,可以减少很多返工。
把表格交给别人之前的检查清单
在提交或转交表格前,建议检查四件事:求和区域是否包含了最新添加的行,该锁定的引用是否加了$,VLOOKUP的最后一个参数是否为FALSE,以及有没有以文本形式保存的数字混在里面。另外,用计算器或另一种方法对合计值做一次验算,能防住大部分"区域错位"这类悄无声息的错误。还要注意,部分函数在较旧版本中不能使用,如果不清楚对方的环境,尽量少用IFS、XLOOKUP等较新的函数。
常见问题
VLOOKUP和INDEX+MATCH在实际使用中有什么区别?
VLOOKUP写起来更简单,对于规模小、结构稳定的表格已经够用;而INDEX+MATCH通常更稳健,适合规模更大或经常被编辑的文件,因为它不会因为插入列而出错,而且可以向左右任意方向查找,而不只是从查找列往右查找。
为什么我的公式会显示#N/A或#VALUE!这样的错误?
#N/A通常意味着查找类函数没能找到匹配的值(常见原因包括多余的空格、数据类型不一致,或者单纯的拼写错误);#VALUE!通常意味着公式试图对一个实际上不是数字的内容做运算,比如被格式化成看起来像数字的文本。
绝对引用应该在什么时候用?
当你把公式复制到别处,仍希望它指向同一个单元格时就要用。例如多行计算都要引用同一个税率单元格,或者要固定VLOOKUP的查找区域。如果忘了加$,复制后引用会错位,造成不容易发现的计算错误。
我应该把VLOOKUP换成XLOOKUP吗?
如果大家的软件版本都支持,XLOOKUP写起来更简单、也更不容易出错,值得使用。但与使用Excel 2019及更早版本的人交换文件时,它可能无法正常运行。比较现实的做法是先确认自己和对方的版本,再决定用哪一个。
为什么我的合计和计算器算出来的不一样?
常见原因有三种:求和区域没有包含新增的行;有的数字其实是文本格式,没有被计入;还有被隐藏或被筛选掉的行被忽略了。可以先检查是否有靠左对齐的数字,这通常是文本格式的信号。