很多小伙伴提出过这样的疑问:为什么高手们一个很简洁的公式写出来的效果,我要写一长串? 在函数圈有一句话:」思路决定出路 」,很多时候你写的公式长度取决于你知道的函数数量和参数性质。
以下是一些典型化简案例,希望对大家有所帮助。
1,填0占位
新手公式:
=TEXT(A2,"000000000000")
大佬公式:
=TEXT(B2,REPT(0,12))
萌新公式:
=BASE(A2,10,12)
把数字控制成固定位数的字符串,新手通常使用TEXT的第二参数添加添加对应个数的0解决,所以总是要在心里默数0的个数。
大佬从来不数0,而是而是用REPT的 第二 参数来控制0的个数。
萌新默默路过,借助BASE函数的 第三 参数来指定0的个数。
BASE2参数为指定的进制数,这个用法下数据10进制不变,故2参数固定为10(进制)。
2,定位最后1个」-」的位置
新手公式:
=FIND("@",SUBSTITUTE(A2,"-","@",LEN(A2)-LEN(SUBSTITUTE(A2,"-",))))
大佬公式: 数组三键
=MATCH(1,0/(MID(A2,ROW($1:$99),1)="-"))
萌新公式: 数组三键
=COUNT(FIND("-",A2,ROW($1:$99)))
新手一般不了解数组公式,因此要先用LEN+SUBSTITUTE的思路确认」-」的数量后作为SUBSTITUTE的4参数(此时只替换这个」-」),
把最后这个「-」替换为一个字符串内不存在的特殊字符(这里用@),利用FIND查找这个特殊字符的位置,即为结果。
大佬都是熟悉数组的,利用二分法以大欺小原则下的1,0/结构来比较每个字符是否为」-」,结果即最后1个」-」的位置。
论坛的萌新熟悉各种函数的各参数,利用FIND的3参数性质配合能忽略错误值的COUNT,计数结果就是所求。
3,日期类计算
求本月最后1个周六的对应日期:
新手公式:
=EDATE(DATE(A2,B2,1),1)-WEEKDAY(EDATE(DATE(A2,B2,1),1),1)
大佬公式:
=DATE(A2,B2+1,)-MOD(DATE(A2,B2+1,)-7,7)
萌新公式:
=FLOOR(DATE(A2,B2+1,),7)
日期类问题是函数问题里一类相对比较常见的题目, 新手通常处理成下个月第1天后用WEEKDAY的差值(注意此时这函数2参数)返回上一个周六。
大佬在日期函数基础上可以用MOD来简化公式。
而萌新在解决日期类问题时,经常直接把函数题变成数学题来大幅简化公式。
4,日期类计算Ⅱ
计算两个日期之间,周一/周三/周五的总天数:
新手公式:
=SUMPRODUCT(N(WEEKDAY(ROW(INDIRECT(A2&":"&B2)),2)=1))
+SUMPRODUCT(N(WEEKDAY(ROW(INDIRECT(A2&":"&B2)),2)=3))
+SUMPRODUCT(N(WEEKDAY(ROW(INDIRECT(A2&":"&B2)),2)=5))
这类题纯新手很难在不使用辅助列的前提下实现,会百度的小伙伴可能会用第1个公式的思路分别求2个日期间周一/周三/周五的分别天数再求和。
大佬公式:
=SUMPRODUCT(N(WEEKDAY(ROW(INDIRECT(A2&":"&B2)),2)={1,3,5}))
大佬的数组公式都是很熟练的,可以把3段简化为1段,利用二维数组简化公式。
萌新公式:
=NETWORKDAYS.INTL(A2,B2,"0101011")
萌新总是能找到正确的函数做正确的事情,利用NETWORKDAYS.INTL的3参数利用1和0的7位数序列来指定控制计算周几。
(周末字符串值的长度为七个字符,并且字符串中的每个字符表示一周中的一天(从星期一开始)。 1 表示非工作日,0 表示工作日。 在字符串中仅允许使用字符 1 和 0。 使用 1111111 将始终返回0。
例如,0000011 结果为星期六和星期日是周末。因此某些不以周六和周日为指定休息日的计算,用NETWORKDAYS.INTL和WORKDAY.INTL这两个函数会简化很多。
5, 最大值之和
求各学科的最大值之和
新手有多少列,就用多少个MAX。
=MAX(B2:B6)+MAX(C2:C6)+MAX(D2:D6)+MAX(E2:E6)+MAX(F2:F6)+MAX(G2:G6)+MAX(H2:H6)+MAX(I2:I6)
大佬都是熟悉多维引用的,利用SUBTOTAL和OFFSET的多维引用效果进行求和
=SUM(SUBTOTAL(4,OFFSET(A2:A6,,COLUMN(A:H))))
(注意数组三键)
而对于萌新而言,能用于多维引用的函数不仅仅是SUBTOTAL和SUMIF这些,数据库函数也是可以的 (注意数组三键) :
=SUM(DMAX(A1:I6,COLUMN(B:I),Z1:Z2))
当然,如果你使用的是Excel 365或者最新的WPS表格,还可以使用以下公式:
=SUM(BYCOL(B2:I6,LAMBDA(x,MAX(x))))
BYCOL与LAMBDA结合,提取出每一列的最大值,最后使用SUM函数求和。
6,不规范时间格式处理
很多时候数据源是不规范的,譬如我们要用这种小数处理成正确的对应时间格式。
新手通常分别分别提取小时和分钟部分后用TIME函数构成对应的正确时间:
=TIME(INT(A2),MOD(A2,1)*100,0)
大佬级会直接*100处理成整数后用TEXT进行格式处理,变成正确的时间格式,最后用减负运算变成时间序列值:
=--TEXT(A2*100,"0!:00")
萌新利用财务函数DOLLARDE对小数部分进行进制换算后直接用时间计算方式(1天是24小时)处理成正确时间:
=DOLLARDE(A2,6)/24
这个函数适合处理这种整数和小数位的进制换算不同的情况。
人外有人天外有天,函数这条路是没有终点的,简化公式不仅是为了锻炼提升,也是为了把正确的函数拿来做正确的事情。
好了,今天的内容就是这些,祝大家一天好心情。
作者:流浪铁匠