|
日常统计汇总本是一件简单的事情，但是总有人会让你体会到什么叫“化简为繁”！
日常加上按小时有饭补，于是就做了一张统计表，方便每个月底统计一下每个人需要补多少？
本文涉及函数如下： TEXTJOIN函数 -WPS可用 SUBSTITUTE函数 - 主流版本 REPT函数 - 主流版本 MID函数 - 主流版本 TRIM函数 - 主流版本 UNIQUE函数 -O365新增 SUMIFS函数 - 主流版本
你觉得应该是这样的
但是最后给你的其实是这样的 
而你要统计的是这样的 
你们可能最想学的是如何快速处理杂乱日期，但是这不是我们本次的重点，我们先撇开日期乱的问题（文末补充处理方法），简单说一下如何把上面多个姓名转成下面这种标准的二维表！ 我们结束两种方法，一种是纯技巧操作，一种是函数方法！ 首先我们还是介绍一种最简单的，技巧操作法
▼超详细动画演示-0代码纯操作！ 
1、鼠标点击数据区域的任意位置，点击【数据】-【自工作表】-确定
（推荐2016及以上版本，2010以下版本及WPS等没有Power Query！）
2、选择加班人字段，点击【转换】-【拆分列】-【高级选项】-【行】
确定，这样我们就可以把内容按照固定的分隔符拆分到每一行，如果自动推测的分隔符不对，也可以自己填写！3、选择餐补列，点击【转换】-【透视列】，值 列 选择加班时长，确定即可！这种我们需要利用到一些新版函数，也正好让大家感受一下新函数好用的地方！
为了大家更直观的看到我们公式是如何一步一步书写的，我们也录制了动画！=SUBSTITUTE(TEXTJOIN('，',,$A$2:$A$14),'，',REPT(' ',99))
▲ 这一步，我们主要干了两个事情，把所有的姓名使用TEXTJOIN拼接到一起，然后把中文逗号，替换成99个空格，方便我们下一步分别提取姓名！
=TRIM(MID(SUBSTITUTE(TEXTJOIN('，',,$A$2:$A$14),'，',REPT(' ',99)),ROW($1:$99)*99-98,99)) ▲ 我们在第一次处理的基础上，从1，100，199……提取99个长度，因为我们上一步替换成了99个空格，这样截取的内容就是空格+对应分隔符的内容，这也是我们没有类似的TextSplit函数前，分隔文本的常用套路！提取出来的结果是内存数组，我是O365版本所以自动扩展显示了，不要再问我的为什么不行，可能你的版本不支持动态数组！
=UNIQUE(TRIM(MID(SUBSTITUTE(TEXTJOIN('，',,$A$2:$A$14),'，',REPT(' ',99)),ROW($1:$99)*99-98,99))) 你学到了什么，明白一个多层嵌套的公式是怎么写出来的了吧！我们1、先有处理思路，根据思路去搜索方式，最好有一定的函数储备知识2、知道或者通过测试知道每步的运行结果，根据结果决定下一步使用哪一个函数（一般都在处理思路时考虑的差不多了）3、想要嵌套，需要知道对应函数的参数是否支持上一步的结果，比如你上一步是文本，下一步你嵌套一个SUM，显然是没用对！一句话：有思路、有一定的函数储备、掌握函数参数类型和结果类型、适当的练习！
姓名处理好后，金额比较简单，我们可以使用SUMIF直接处理=SUMIFS($B:$B,$A:$A,'*'&$E2&'*',$C:$C,F$1)
不过这里还是不太严谨的做法，比如王文和王文文就会出现问题！想要更加严谨，我们需要把数据源稍微加工一下！把两遍都加上逗号，这样我们的姓名就全部在两个中文逗号之间了！在求和的公式中，同样加上两个逗号，这样就不会出现王文和王文文类似的问题了！=SUMIFS($C:$C,$A:$A,'*，'&$F2&'，*',$D:$D,G$1) 到这里，我们的核心要分享的知识就结束了，你可能觉得第一种方法更简单，但是部分同学更喜欢使用函数处理！选择适合自己的方法吧！
|
