【Excel】函数之易踩坑系列（常用函数干货）

失效的日期：

数据库导出来的日期通常是字符串格式的！！，超级容易忽略掉的格式问题，成为了统计数目时的一大出错点。

解决办法：用DATE 或 DATEVALUE+MID转化为日期格式

季度和周期的计算公式

• 【如：第1季度】 =“第”&TEXT((MONTH(A2)+2)/3,“0”)&“季度”
• 【如：2023/1/2-2023/1/8】=TEXT(A2-WEEKDAY(A2,2)+1,“YYYY/M/D”)&“-”&TEXT(A2-WEEKDAY(A2,2)+7,“YYYY/M/D”)

劝退之老是#N/A的VLOOKUP：

场景：由于数据库权限问题，重要数据只能导出脱敏文件，需要用到vlookup函数查找另一张源表补全信息。
  然而问题来了：为什么同样的一列，有大部分的单元格能查找到，却有小部分是#N/A（用Ctrl+F是能在两个表都找到的value）呢？
  带着疑惑，我先检查了格式是否相等，再用IF函数验证两个单元格是否相等，答案是TRUE的！！那为什么用VLOOKUP还是找不到呢，我想是出现了薛定谔的格式吧。
  所以我改用了Index函数，结果是理想的，全部都能找到，而且index函数可灵活多了，完全可以代替VLOOKUP。

=INDEX(查询结果!A:B,MATCH(TEXT(F2,“0”),查询结果!A:A,0),2)

强大的COUNTIFS函数：

场景：统计二级、三级指标完成数量任务，需要用到辅助列+条件列+search函数提取目标列中包含任意关键词的行。
于是乎又臭又长的公式就诞生了（B:B为目标列，F2:F6为条件列）：

单条件查询： =IF(ISNUMBER(SEARCH(F2,B2)),1,0)
多条件查询： =IF(SUMPRODUCT(N(NOT(ISERROR(SEARCH(F2:F6,B2))))),1,0)

既然条件列能用通配符，那优化：

单条件查询： =IF(COUNTIF(F2,B2),1,0)
多条件查询： =IF(SUMPRODUCT((COUNTIF(B2,F2:F6))),1,0)

此外，COUNTIFS的其他应用场景：

• 去重：=IF(COUNTIF(B:B,B2)>1,0,1)
• 判断该元素是否在另一列出现过：=IF(COUNTIF(B:B,A2),1,0)
• 分组统计（条件顺序：有重大区分的放在前面）

SEARCH函数的用武之地：

场景：从字符串中截取关键句，如：把字符串里的日期提取出来、截取包含某个关键字的完整地名。

计算公式： =MID(G2,SEARCH(“保利*”,G2),10)

想用数组就找OFFSET函数：

场景：想返回一组满足多个条件的数组，比如：求出2022年的某个地区的某项指标的中位数。
思路：先利用INDEX+MATCH找到符合条件的单元格组的首格，再用OFFSET返回整个数组，最后用MEDIAN求出中位数。（记得按三键：Ctrl+Shift+Enter）

=MEDIAN(OFFSET(INDEX(数据源!B:H,MATCH(1,(数据源!B:B=B1)*(数据源!D:D=4),0),7),0,0,12,1))

Excel版的case when语句

场景：根据销售业绩分配不同的提成比例或者根据特定的人给提成。
思路：先用MATCH找到目标值在数组中的位置，再配合CHOOSE返回该位置的值。

=CHOOSE(MATCH(B2,{0,10001,20001,30001}),0.05,0.1,0.15,0.2) # 根据销售业绩分配拟定的提成比例，如：销售额为[0,10000]的提成比例只有0.0.5，大于30000的提成比例有0.2；
=IFERROR(CHOOSE(MATCH(A2,{“小明3”,“小明4”,“小明5”},0),0.1,0.12,0.15),0) # 给特定的员工设置提成比例；

长文本类型数字下拉递增问题

场景：商品的编号通常由字母+10位以上的数字组成，想要在Excel中下拉数字递增是做不到的，因此要借用函数ROW() + 字符串拼接：

=J898615001923000&TEXT(ROW($A1)-1,“00”)