应用场景
公司的数据都会汇总至财务部,然而大部份时候,我们拿到的各部门数据都是没有按照一个统一的标准进行登记。这个时候,我们想对这些数据进行统计分析就显得有点麻烦了
——来自一位朋友的提问
举个简单的实例
对于这么一张表,要按客户汇总销售额,很简单吧,数据透视表或sumif函数都可以。
方法如下
但是如果各部门提交上来的数据是这样子的呢?
客户名称,有的加了"市",有的少了“有限”
这时候,再用透视表,完全没用,起不到汇总的效果。
解决方案
一、数据透视表
继续用数据透视表。这个时候,我们没办法再用现有的“客户”字段来进行汇总。
思路:
1、做一张客户简称对照表(如果有档案,最好了)
2、添加一辅助列规范一下“客户”字段
3、数据透视表中,用新添加的列进行汇总统计
图中公式:
=VLOOKUP(MATCH(1=1,ISNUMBER(FIND(公司!$C$2:$C$4,A2)),0),公司!$A$2:$C$4,2,0)
注:输入完公式后,需要按Ctrl+Shift+回车
公式其实也不难,用的是一些常用的函数(VLookup、Match、IsNumber、Find)的数组运用。
如果公式理解起来有难度的话,也没关系,可以直接使用。注意我标注颜色的部份,需要修改,与自己的表格相适应。
二、Sumif()函数
利用sumif() 第二参数的模糊匹配,进行汇总
图中公式:
=SUMIF(统计表!A:A,"*"&公司!C2&"*",统计表!C:C)
注意 * 通配符的运用。
优缺点对比
添加辅助列,用数据透视表。这种方法,由于完善了数据源,方便继续利用数据透视表做其他的统计,只是公式理解起来可能有点困难(其实也没事,就当作套路收藏一下。)
Sumif() 函数,很容易理解,做起来也方便,但相对于透视表而言,做后续其他的统计就还得写其他函数,比较麻烦。
本文素材:进入公众号,回复“数据规范1”,即可获取。
小风说:不推荐死扣Excel函数、数组公式,然后表演那些Excel杂技。而是更应该注重同事之间的沟通与协调,让数据更规范,注重学习Excel的制表思路和掌握数据库思维。在数据规范的基础上,几乎大部份的数据处理与分析,都能运用数据透视表自动完成。
END
咱们的微信群
互助学习更精彩
欢迎加我微信
注明“订阅号”
进群一起学习
领取专属 10元无门槛券
私享最新 技术干货