这一个月来,卢子都在分享函数的运用。可是万万没想到,有读者居然把前面教的透视表忘得一干二净。函数还没学会,透视表就忘记,这怎么行呢? 这里给你一个建议,不要只是停留在看文章的层面上,而是要每一篇文章操作3-5遍。抽空再把过去学过的文章,翻出来复习一下。只要你能坚持一年,相信你的Excel水平,会有一个质的飞跃。 明细表记录着每天的金额,现在要统计每个月的总金额。 练习素材下载: https://pan.baidu.com/s/1hh98txHIk2k1jg-RFqJUzA Step 01 选择A1,单击插入数据透视表,在弹出来的对话框保持默认不变,单击确定。 Step 02 将日期拉到行,金额拉到值。Excel2016默认情况下就按年、季度、月组合。 其他版本,都是日期没有组合的效果,这里以其他版本进行说明。 Step 03 选择日期任意单元格,右键组合(创建组),因为这里的日期是跨年的,所以必须选择年跟月组合。 问题来了,读者的公司采用月份统计方法跟常规方法不一样,将上个月的26日到这个月的25日划分成一个月。如2018-5-26到2018-6-25划分成6月,这种情况有如何处理? 解决方法:利用一个辅助列,获取新的日期,再用透视表进行统计。 Step 01 判断日是否大于25,如果大于就显示1,否则显示0。
Step 02 原来的日期+判断的结果就组成新的日期。输入公式后,将单元格设置为短日期格式。
EDATE函数语法:
之前就是负数,这个月就是0,之后就是正数。 Step 03 重新创建数据透视表,将新日期拉到行,金额拉到值,再对新日期进行组合,参考前面的案例,这样就完成了统计。 在获取新的月份,可能有读者想到用这个公式:
理论上是可行,实际上是错误的。这个公式没办法处理跨年的情况,比如现在是12月,加上一个月就变成了13月。 在写公式的时候,一定要考虑得非常全面,否则就会闹出笑话。在Excel中1%的错误等于100%的错误。 上面的透视表教程,有3个读者有疑问,这里逐一进行解释。 1.行标签出现相同字段,如何解决? 一眼看过去,会发现有两个卢子,居然没有统计在一起,怎么回事呢? 眼见不一定为实,像空格或者隐藏字符,我们眼睛是看不到的。数据如果是手工输入,偶尔会多输入一两个空格。数据如果是从网页等地方复制出来,可能会含有隐藏字符。 针对空格,直接查找替换掉就可以,针对隐藏字符,需要复制到记事本中才可以替换。现在演示一下,替换空格的方法。 选择A列,按Ctrl+H,输入一个空格,单击全部替换,刷新数据透视表就恢复正常。 2.日期这一列,含有不相关的内容,组合出错,如何解决? 不同内容混合在一起,是一个超级不好的习惯。一列一属性,日期列就只能存在日期,金额列就只能存在金额。不规范的,手工处理成规范的,没有其他办法解决。 3.如何按周统计数据? 一周7天,正常的想法就是直接将天数改成7,可惜这里是灰色的,没法设置。 这个功能用的人非常少,不懂使用也是很正常。选择日期,右键组合,取消选择月,再选择日,这样就可以设置天数为7。 好,到此三个疑问都有了答案,如有透视表疑问留言区见。 |
|