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

本文主要是介绍【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) # 给特定的员工设置提成比例；