|
说数据透视表是Excel数据分析中最高频使用的功能，一点都不为过，甚至可以说Excel数据透视表是数据分析师日常工作中最常用的功能（vs Python）。有人可能会说，Python中pandas.pivot_table也可以做透视表，甚至还有很多优势，比如能够处理的数据量更大（超100万行）、统计函数类型更丰富（可自定义聚合函数），那为什么还要用Excel呢？ 原因就两个字：灵活。 灵活体现在以下几个方面： 1、拖拽布局：值、行、列、筛选四个区域调整方便。 2、汇总计算：“值汇总方式”有多种汇总函数类型可选、“值显示方式”可以切换多种自定义计算方式，还有计算字段和计算项可编辑使用。 3、多项组合：当一个字段有多个取值（Excel中叫做“项”）可以合并为一项时，可以手动组合，而且可以在新组合上继续组合。 4、即时交互：以上所有操作结果都是即时可见的，这在数据分析中非常好用，因为分析不是一件能提前确定所有分析操作的事，常常需要边做边调整。 5、直接输出：Excel数据透视表的结果就是“表”，可作为一个普通的表直接输出，基于此可视化或者再透视都是可以的。 当然Excel数据透视表也有一些小问题： 1、文本类型字段只能计数，不能取文本的min、max。这个问题Excel无解，只能通过其他工具处理。 2、汇总函数中没有非重复计数。这个Excel有解，通过将数据源转换为数据模型就可以使用，后文会讲。 3、字段名改名之后可以通过“字段设置”找到源字段名，但字段的项改名之后，无法知道修改之前是什么。 好了，接下来，我们就以“A商城销售数据”为案例，把常用功能串起来，并且保证常用的处理方法没有遗漏。这里没讲到的大多数用不上，所以有些知识点没学，也不必觉得有什么遗憾。与多数讲透视表的文章不同的是，我不仅会讲具体怎么操作，还会讲清楚为什么（在什么场景下）这么操作，以及我更多会讲应该怎么做，而不是可以怎么做，把数据透视表的最佳玩法，展现给大家，肯定会涉及到深水区（我目前还没看到有其他地方有讲过），因为这都是我每天反复磨炼的泣血总结，其中不乏诸多巧思在里面。话不多说，我们开始吧。 选择数据源插入数据透视表快速了解将要使用的数据在Excel底部“自定义状态栏”右键单击，把统计的那六项全部勾选，以后选中的区域的基本六项统计指标就都会在自定义状态栏快速统计好了，不需要任何函数或其他操作。 通过选中明确知道不为空的A列（行id），数值计数为9959，我们就知道这份数据总记录数是9959。选中“销售额”列，求和为16068954.13，选中“销量”列，求和为37534。   建议：在“新工作表”放置新建的数据透视表选择位置时建议选“新工作表”，这个在Excel官方的学习文档中也有提到。透视表与原始数据分开放置在不同Sheet的，以免相互影响。  可选：数据源转换为表格以动态更新数据透视表若数据源经常需要增加行记录或者列字段，那么应该将数据源设置成可动态更新的。有两种方法，表格法和名称法，但这里推荐你用表格法。 表格法就是把普通区域转成“表”，表的一个特性就是会自动扩展连续区域。 名称法就是通过名称管理器使用函数返回动态区域来定义名称。但这个方法有个问题是，必须使用完全没有空值的行和列，这在数据经常更新状态下是无法彻底保证的。 函数=OFFSET(订单!$A$1,,,COUNTA(订单!$A:$A),COUNTA(订单!$1:$1)) OFFSET 是一个引用函数，第2和第3个参数表示行、列偏移量，这里是0意味着不发生偏移，第4个参数和第5个参数表示引用的高度和宽度。公式中分别统计A列和第1行的非空单元格的数量作为数据源的高度和宽度。当“销售明细表”工作表中新增了数据记录时，这个高度和宽度的值会自动地发生变化,从实现对数据源区域的动态引用。 可选：需要统计不同维度下的“非重复计数”则勾选添加到数据模型勾选“将此数据添加到数据模型”后，统计函数中就可以使用“非重复计数”了。 后文会有详细操作。 数据透视表的值值的放置及显示为什么先放值？通常我们对数据是了解总体情况，而不知细节，所以先放值可以校验数据结果是否与预期相符。若大数都有差异，那就需要排查取数逻辑是否正确。 tips：当我们往透视表的值区域放了2个以上的汇总字段时，列区域会出来“数值”，这个是可以拖动放置到行区域里的。  两种不同摆放位置的结果如下：  调整数字格式 在数据透视表的统计字段上右键点击，出现下图，可以看到有“设置单元格格式”和“数字格式”两个指令。 单元格格式与数字格式的区别是，“单元格格式”仅设置选中单元格区域的格式，而“数字格式”设置活动值字段的格式。我们希望数字格式是应用在字段上而不是选中的单元格上，这样在数据透视的布局发生变化时，该字段的所有值格式都是设置好的。这样看起来貌似设置“数字格式”是更好的选择，但其实不然。 若一次性选中整列（含总计行）应用“单元格格式”，布局变动也不会导致设置好的格式在新行上失效。另外使用“单元格格式”还有其他两个好处： 设置方式较多：这里的单元格格式，与“开始-数字”功能菜单中的设置按钮是一致的，也可以用Ctrl+1快捷调出设置窗口，或者应用快捷键，或者右键快捷菜单上方的浮动工具栏上的常用格式工具 设置格式更多：除了数字格式外，还可以设置字体、对齐等其他单元格格式。 所以推荐使用“单元格格式”设置数据透视表的字段格式，并且建议在选完指标之后就设置好数字格式，后续看数会更轻松。  数据透视表的数值区域只能是数值格式，所以源数据的字段以及数据透视表中的公式或计算字段的返回结果，都只能是数值或可直接转换为数值的日期或文本字符。 值的汇总方式是计数、求和，还是求平均、最大、最小。同一个字段可以放多种不同汇总方式。  这里的非重复计数选项是置灰的，需要先建数据模型，在数据透视表字段选择区域中的最下方，点击“更多表格”。  字段筛选区域、值的名称会变得略有不同外，其他功能没变。可以看到非重复计数城市数是573个。  计算字段本案例中我们要计算两个字段： 1、分项及整体的利润率是多少？ 2、分项及整体的折扣率是多少？   看C5单元格的公式我们知道，刚刚创建的计算字段公式（=利润/销售额），其实是sum(利润)/sum(销售额)，是整体利润率，即利润率的加权平均。 这是因为计算公式是应用在公式中的列总和上，而不是项（单个记录）上，无论这个列字段在数据透视表中的统计方式是求和还是平均。这就会导致在求整体的平均折扣率时，会出现问题。 首先直接对折扣字段求平均肯定是错误的，因为这没有考虑到不同金额的权重差异。 正确的整体折扣率公式应该是sum(销售额*折扣)/sum(销售额)。但如果计算公式写成销售额*折扣/消费额，在计算公式的实际应用其实是=sum(销售额)*sum(折扣)/sum(消费额)=sum(折扣)，最终的折扣率结果是折扣字段的加总=1059.7，这显然不对。 推荐的解决方法是，在源数据当中新增一列“折扣额”，计算公式=销售额*折扣，再在计算字段中新增“折扣率”，计算公式=折扣额/销售额，最终得到总的折扣率是9.15%。 出个题考考大家，当总计计算公式为每个子项结果的加权平均的场景下，以下哪类计算公式应该先提前在源数据中增加计算字段，而不能在数据透视表中的计算字段中使用呢？ 1、(A+B)/C 2、A/(B-C) 3、(A*B)/C 4、A/(B*C) 注：以上四种类型的加减号相互替换、乘除号相互替换的答案不变。 答案是只有3和4需要。 当总计计算公式为每个子项结果的加总时，则透视表的计算公式中只能有加减法，不能有乘除，乘除计算需要提前在源数据中处理好。 好的，到这里，我们数据透视表的上篇就讲完了。在下篇中，我会讲哪些内容呢？
|
|
|
