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函数，大家自己尝试吧！

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

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

有需要的可以给我留言~

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