1.今天接到业务团队的一个需求,写一个Excel的KPI完成进度指示表,要求能够依据指示今天完成每月、每季度、每年的KPI进度情况。同时还要对比去年的KPI完成进度。

其实逻辑很简单:

1.KPI手动填写;
2.Excel公式依据日期判断月份、季度,多条件求和;
3.将求和的值除以对应的月、季度、年KPI即可;

刚刚开始的时候很顺利,但是一会就卡在一个函数上:sumifs()。这个函数跟sumif()的区别是sumif只能对一个条件进行判断,而sumifs可以对多个条件进行判断。考虑到要做同比数据计算,而去年的数据是全部导入在表格中的,所以要控制计算的条件:求和日期不能大于当前日期,在计算月度KPI完成情况时月份为当前的月份,而计算季度时为当前季度。
因为我的当前日期计算是用函数A1=today(),所以在sumifs中表示为sumifs(range,小结:单元格作为公式的中间值有时候能起大作用。
2.从右向左取字符串:
网页的CMS路径一般为abc/123/xxx/aaa.html。现在希望能够把aaa.html提取出来。最开始考虑用Python, 解决也比较快。今天突然懒得打开代码了,想直接excel处理。最开始的思路是依据’/'进行分列,可是路径的深度不一致,2级路径和4级路径切出来后还需要重新进行判断。后来在网上搜了下,一个神一般的解决公式:
=TRIM(RIGHT(SUBSTITUTE(H1,”/”,REPT(” “,100)),50))
这个函数,先将’/'替换为100个空格,接着从右向左取出50个字符串。最后使用trim将空格去掉。考虑到一般文章的title应该不会超过50个字,所以很轻松的就解决了这个问题。
附参考资料: