各部门上报的数据不规范,怎么办?Excel

应用场景

公司的数据都会汇总至财务部,然而大部份时候,我们拿到的各部门数据都是没有按照一个统一的标准进行登记。这个时候,我们想对这些数据进行统计分析就显得有点麻烦了

——来自一位朋友的提问

举个简单的实例

对于这么一张表,要按客户汇总销售额,很简单吧,数据透视表或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

咱们的微信群

互助学习更精彩

欢迎加我微信

注明“订阅号”

进群一起学习

  • 发表于:
  • 原文链接https://kuaibao.qq.com/s/20180905G14H2U00?refer=cp_1026
  • 腾讯「云+社区」是腾讯内容开放平台帐号(企鹅号)传播渠道之一,根据《腾讯内容开放平台服务协议》转载发布内容。
  • 如有侵权,请联系 yunjia_community@tencent.com 删除。

扫码关注云+社区

领取腾讯云代金券