Excel中鲜为人知的那些坑

小伙伴们好，今天和大家一起聊聊Excel中那些鲜为人知的坑。

经常有小伙伴会遇到这样的问题，我的操作或公式明明是正确的，出来的结果怎么不对呢？重新操作一遍吧，但结果还是不对，真愁人。

只要找到正确的方法，很多问题都可以迎刃而解。

1，高级筛选中的坑

在A1:C13数据区域，我们希望通过高级筛选筛选出苹果的数据。

先点击数据区域任意单元格，然后依次点击【数据】--排序和筛选组--【高级】，在列表区域选择：$A$1:$C$13，在条件区域选择：$E$1:$E$2，点击确定。

我们发现筛选出来的数据中除了苹果还包含了苹果汁的数据，这是什么原因呢，有什么方法可以解决这个问题呢？

其实这是高级筛选模糊匹配的特性导致的，凡是以“苹果”开头的数据都会被视为符合条件的数据。我们只需把条件区域E2单元格内容改为【='=苹果'】或【'=苹果】，再做高级筛选就可以得到正确的结果了。

2，条件格式中也有坑

我们希望对B2:B13设置条件格式判断是否有重复数据。

选中B2:B13，【开始】--【条件格式】--【突出显示单元格规则】--【重复项】--【确定】，我们发现B5单元格明明是唯一值但是也高亮了，这又是什么原因呢？

我们知道，*星号在Excel中有通配符的作用，可以代表任意多个字符，B5单元格中的“B27*90”和B3单元格中的“B27*290”在Excel看来都是以B27开头，以90结尾，所以判断为B5单元格内容为重复项了。

解决方法，可以新建规则，使用公式判断是否有重复项。选中B2:B13设置条件格式，【条件格式】--【新建规则】--使用公式确定要设置格式的单元格。

输入公式：=COUNTIF(B$2:B$13,SUBSTITUTE(B2,'*','~*'))>1，设置格式颜色，点击确定。此时，在数据没有重复项时，数据单元格就不会高亮显示了。

3，函数公式中的坑更多

函数公式中的坑就更多了，我们举两个有代表性的例子。

1）大众情人，VLOOKUP函数中的坑

使用VLOOKUP精确查找F2中的B27*90返回数量，我们发现得到的结果串行到其他位置了。

这是因为VLOOKUP在精确查找时是支持通配符的，把“B27*290”视为“B27*90”了，在从上往下查找时自然是先找到“B27*290”返回C列数据了。

解决方法，嵌套一个SUBSTITUTE函数即可，公式：=VLOOKUP(SUBSTITUTE(E2,'*','~*'),B:C,2,FALSE)

原理，通过SUBSTITUTE将星号指定为文本而不是通配符。

另外，Excel支持通配符的常用函数还有HLOOKUP、SUMIF、SUMIFS、COUNTIF、COUNTIFS、MATCH、SEARCH、SEARCHB等，小伙伴们在使用这些函数时要掌握其特性，以免采坑了。

2）DSUM数据库函数中的坑

我们用公式=DSUM(A1:C13,3,E1:E2)对荔枝的数量进行汇总，发现得到的结果明显大于荔枝的实际数量。

其实这个问题和高级筛选的情况相似，只需把条件设置为【'=荔枝】就可以得到正确结果了。

在使用数据库函数时，有此特性的函数还有DCOUNT、DMAX、DMIN等，我们在应用时需要确定自己的需求，严谨设置条件。

图文制作：心电感应