Excel实战技术(5)——函数公式下的数据透视表

接着《Excel实战技术》系列,今天和大家分享一下excel中用函数公式实现的数据透视表。

大家可能会有疑虑:想要生成透视表,在excel中直接插入数据透视表不就好了,干嘛还要使用公式?

我解释的原因有二:1.使用excel中自带的数据透视表中左侧列的名称必须是一致的才可以,比如:“北京市”与“北京”,“新疆维吾尔自治区”与“新疆”,“品牌1”与“品牌一”,在数据透视表中默认的是两个概念,而在实际工作中,我们都知道是因为获取到的数据的不规范性导致的。如果使用excel自带透视表,便无法整合在一起;2.更加自动化,不必在后台数据更改过后人工重新拉取透视表,减去了很多不必要的操作。

由于行业原因,我举例的数据为某三个汽车品牌官网中爬取的全国4S店分布数据,样本数据量为2200+。操作的目的是在31个省份下统计各品牌数量、各品牌设备安装数量、各品牌设备未安装数量以及占比情况(不包含中国香港、中国澳门与中国台湾)。

下面我来讲实现的过程:

下图是,爬取后经过初步整理的样本数据的部分截图(后台数据)

下图是设计呈现的数据表(前端数据)

设计数据表格也是一项技术活,尽可能简洁呈现,并且要有“合计”。

我先将部分单元格的公式进行了编写,方便各品牌数填入时“占比”与“合计”可以自动生成。

求和时,可以不用sum公式,excel中有自动求和功能,点击后可以智能判断你想求和的行与列,自己在进行调整行列始末值便可。

接下来需要求解各品牌数量、安装与未安装数量。

思考过后的朋友可以想到,其实这就是一个多重条件下计数的过程(需要去研究后台数据的结构)。

条件函数是if,计数函数是count,某一条件下计数的函数是countif,多重条件下计数的函数是countifs。本案例中,我们使用的函数是countifs。

下面countifs函数的语句写法:区域1,区域1下的条件1,区域2,区域2下的条件2,....

B3单元格中countifs的条件有两个:1)是品牌1;2)所在省份为A3

对应区域分别为,后台数据中的E列与B列。

B3=COUNTIFS(后台数据!$E$2:$E$3000,"品牌1",后台数据!$B$2:$B$3000,A3&"*")

“$E$2:$E$3000”为绝对引用,可以按F4按键实现。F4按键在excel中是一个很重要的按键,既可以实现绝对引用与相对引用的切换,也可以实现重复上一步操作。

A3&"*",这应该是整合数据表实现过程中最重要的一步了,我并没有在countifs函数中的条件写”安徽“二字,原因是:样本数据中包含”安徽“与”安徽省“两种样式(我在前面已经提到了)。这里的”*“,属于模糊查找的意思,类似mysql中的”%“与”_“。A3&"*"的最终含义是:查找以A3单元格开头的内容。

理解上面两个关键含义后,B3单元格的语句便可以轻松get到了!

C3单元格是在B3的基础上加入第三个条件:”是“,对应列为后台数据中的F列。

C3=COUNTIFS(后台数据!$E$2:$E$3000,"品牌1",后台数据!$B$2:$B$3000,A3&"*",后台数据!$F$2:$F$3000,"是")

同理,E3/G3/H3/J3/L3/M3/O3的函数也可以写出来,再向下拉取,便可以得到一张完整的透视数据表。

大家可以看到N29与P29单元格出现错误,原因是L29是数据为0,为了使数据最终呈现时更加美观,大家可以在与”占比“有关的单元格中使用一下iferror函数,大家自己尝试吧!

数据生成后,还可进行数据可视化生成图表,老板看后,绝对肯定你的能力!

关于数据可视化内容,我们后期会进行!

有需要的可以给我留言~

欢迎关注我的微信公众号,进行评论、转载与分享~

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

扫码关注云+社区

领取腾讯云代金券