Excel入门:如何简化函数公式
为什么老师们一个很简洁的公式写出来的效果 , 我要写一长串?以下是一些典型化简案例 , 希望对大家有所帮助 。
在函数圈有一句话:”思路决定出路” , 很多时候你写的公式长度取决于你知道的函数数量和参数性质 。
1 , 填0占位
文章插图
把数字控制成固定位数的字符串 , 新手通常使用TEXT的第二参数添加添加对应个数的0解决 , 所以总是要在心里默数0的个数 。
大佬从来不数0 , 而是而是用REPT的第二参数来控制0的个数 。
萌新默默路过 , 借助BASE函数的第三参数来指定0的个数 。
(BASE2参数为指定的进制数 , 这个用法下数据10进制不变 , 故2参数固定为10(进制)) 。
因为能对数字位数处理的函数不是只有文本函数的(注意BASE是2013新增的) 。
文章插图
2 , 定位最后1个”-”的位置
文章插图
新手一般不了解数组公式 , 因此要先用LEN+SUBSTITUTE的思路确认”-”的数量后作为SUBSTITUTE的4参数(此时只替换这个”-”) ,
把最后这个“-”替换为一个字符串内不存在的特殊字符(这里用@) , 利用FIND查找这个特殊字符的位置 , 即为结果 。
大佬都是熟悉数组的 , 利用二分法以大欺小原则下的1 , 0/结构来比较每个字符是否为”-” , 结果即最后1个”-”的位置 。
论坛的萌新熟悉各种函数的各参数 , 利用FIND的3参数性质配合能忽略错误值的COUNT , 计数结果就是所求 。
3 , 日期类计算
求本月最后1个周六的对应日期:
文章插图
日期类问题是函数问题里一类相对比较常见的题目 , 新手通常处理成下个月第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 , 判断胜负
文章插图
新手公式
=IF(--LEFT(B2,FIND("-",B2)-1)>--MID(B2,FIND("-",B2)+1,9),"胜",IF(--LEFT(B2,FIND("-",B2)-1)=--MID(B2,FIND("-",B2)+1,9),"平","负"))
大佬公式
=TEXT(LEFT(B2,FIND("-",B2)-1)-MID(B2,FIND("-",B2)+1,9),"胜;负;平")
萌新公式
=TEXT(IMREAL(IMDIV(B2&"I","1+I")),"胜;负;平")
这类问题新手只会先把两部分比分 , 分别提取出来比较或计算差值 , 用IF分别判断差值分别为正数 , 负数或者0来确认结果是胜负还是平局 。
大佬会使用TEXT来把2次IF简化为TEXT的正数位;负数位;0位来获取对应结果 。
至于”萌新” , 能只引用1次的绝不引用2次数据源 , 利用复数结构和IMDIV的特殊算法来简化计算A+B(I)的复数结构的A+B的结果(这里的数据结构B是负值) 。
文章插图
(利用对应参数C+DI实现AC+BD 为A-B算法 , 用IMREAL提取复数结构中的实部系数 , 即为需要的2个数的差值的正负情况 。
文章插图
6 , 最大值之和
求各学科的最大值之和
文章插图
新手有多少列 , 就用多少个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的多维引用效果进行求和
【Excel入门:如何简化函数公式】=SUM(SUBTOTAL(4,OFFSET(A2:A6,,COLUMN(A:H))))
(注意数组三键)
而对于”萌新”而言 , 能用于多维引用的函数不仅仅是SUBTOTAL和SUMIF这些 , 数据库函数也是可以的
=SUM(DMAX(A1:I6,COLUMN(B:I),Z1:Z2))
(注意数组三键 , Z1:Z2是任意空白区域 , 利用数据库函数性质默认条件为全部)
7 , 不规范时间格式处理
文章插图
很多时候数据源是不规范的 , 譬如我们要用这种小数处理成正确的对应时间格式 。
新手通常分别分别提取小时和分钟部分后用TIME函数构成对应的正确时间(这里INT和MOD*100已经是简洁思路了 , 如果用LEFT+FIND会更繁琐) 。
大佬级会直接*100处理成整数后用TEXT进行格式处理 , 变成正确的时间格式 , 最后用减负运算变成真数值(时间) 。
“萌新”利用财务函数DOLLARDE对小数部分进行进制换算后直接用时间计算方式(1天是24小时)处理成正确时间 。
这个函数适合处理这种整数和小数位的进制换算不同的情况 。
推荐阅读
- ppt入门:如何选择幻灯片背景提升视觉效果
- 2020年,「微观个体」该如何努力?
- 疫情期间培训机构招生方案,如何做好保育员工作培训心得
- 教育机构靠谱吗?如何选择教育机构?
- 疫情期间出门不带口罩,如何处理?口罩重复利用的方法
- 疫情期间吃饭怎么解决?疫情期间上下班途中如何做?
- 如何做黑鱼好吃又简单_黑鱼的做法大全家常菜
- 姚贝娜乳腺癌复发住进ICU病房 术后如何正确预防?
- 如何正确看待初服避孕药的不良反应
- 如何治疗经前综合症 三种方法有效缓解经前综合症