失效的日期:
数据库导出来的日期通常是字符串格式的!!,超级容易忽略掉的格式问题,成为了统计数目时的一大出错点。
解决办法:用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”)