数据透视表是Microsoft Excel中最强大——也是最令人生畏的——功能之一。它的强大之处在于它可以帮助您总结和理解大型数据集。
9个Excel营销模板
告诉我们一些关于你自己的情况,以便今天就能进入:
什么是透视表?
数据透视表是数据的摘要,打包在图表中,让您可以根据您的信息报告和研究趋势。如果您的行或列很长,需要跟踪这些行或列的值,并且很容易相互比较,那么Pivot表就特别有用。
换句话说,枢轴表从屏幕上的那种看似无穷无尽的混合号码中提取意义。更具体地说,它允许您以不同方式将数据组合在一起,因此您可以更轻松地绘制有用的结论。
透视表的“透视”部分源于这样一个事实:您可以旋转(或透视)表中的数据,以便从不同的角度查看它。需要明确的是,当你做一个主元时,你并没有增加、减少或改变你的数据。相反,您只是重新组织数据,以便从中显示有用的信息。
如何使用数据透视表
如果你仍然对数据透视表的功能感到困惑,不用担心。一旦您看到它的实际应用,这是那些更容易理解的技术之一。以下是希望使用数据透视表的七个假设场景。
1.比较不同产品的销售总额。
假设你有一个包含三种不同产品(产品1、产品2和产品3)的月度销售数据的工作表,你想知道这三种产品中哪一种收入最高。当然,您可以查看工作表,并在每次出现产品1时手动将相应的销售数字添加到运行总数中。然后可以对产品2和产品3做同样的处理,直到得到它们的总和。小菜一碟,对吧?
现在,假设您的月度销售工作表有成千上万行。手动整理它们可能要花一辈子的时间。使用透视表,您可以在不到一分钟的时间内自动汇总产品1、产品2和产品3的所有销售数字——并计算它们各自的总和。
2.显示产品销售额占总销售额的百分比。
当您创建数据透视表时,数据透视表自然会显示每一行或每一列的总数。但这并不是你能自动生成的唯一数字。
假设您将三种不同产品的季度销售数字输入到一个Excel表格中,并将该数据转换为数据透视表。表格会在每一栏的底部自动给你三个总数——包括每种产品的季度销售额。但如果你想找到百分比这些产品的销售贡献了公司的全部销售,而不仅仅是这些产品的销售总额?raybet电子竞技
使用数据透视表,您可以配置每个列,以显示该列占所有三个列总数的百分比,而不仅仅是列总数。例如,如果三个产品的销售额为20万美元,而第一个产品的销售额为4.5万美元,则可以编辑一个透视表,以表示该产品贡献了公司销售额的22.5%。raybet电子竞技
要在透视表中以总销售额的百分比显示产品销售额,只需右键单击包含总销售额的单元格并选择“显示值”>“占总数的百分比。”
3.结合重复数据。
在这个场景中,您刚刚完成了一个博客重新设计,并且必须更新一系列url。不幸的是,您的博客报告软件并没有很好地处理它,最终将单个帖子的“视图”雷竞技苹果下载官方版指标划分为两个不同的url。因此,在电子表格中,每个博客文章都有两个单独的实例。为了获得准确的数据,您需要组合每个重复的视图总数。
这就是pivot table发挥作用的地方。无需手动搜索并组合重复文章中的所有指标,您可以根据博客文章标题(通过数据透视表)总结数据,并且voilà:这些重复文章中的视图指标将自动聚合。
4.为单独的部门计算员工人数。
数据透视表有助于自动计算基本Excel表格中无法轻易找到的内容。其中之一就是计算有共同点的行数。
如果你有一个员工在一个Excel表的列表,例如,和旁边的员工的名字他们属于各自的部门,你可以从这些数据创建数据透视表,向你展示了每个部门名称和数量的员工是那些部门。透视表有效地消除了按部门名称对Excel表排序和手工计算每行的任务。
5.向空单元格添加默认值。
并不是输入到Excel中的每个数据集都会填充每个单元格。如果你在等待新数据进入Excel之前,你可能会有很多看起来令人困惑的空单元格,或者在向经理显示这些数据时需要进一步解释。这就是pivot表的用途。
您可以轻松地自定义透视表,以使用默认值(例如$0或TBD)填充空单元格。对于大型数据表,能够快速标记这些单元格是一个有用的特性,当许多人都在查看同一工作表时。
若要自动格式化透视表的空单元格,请右键单击表格并单击“数据透视表选项”。在出现的窗口中,勾选标签框“空细胞”当单元格没有其他值时,输入您希望显示的内容。
如何创建透视表
- 将数据输入一系列行和列。
- 按特定属性对数据进行排序。
- 突出显示单元格以创建透视表。
- 将一个字段拖放到“行标签”区域。
- 将一个字段拖放到“值”区域。
- 调整你的计算。
现在您已经对数据透视表可以用于什么有了更好的理解,让我们进入如何实际创建数据透视表的基本细节。
1.将数据输入一系列行和列。
Excel中的每个数据透视表都从一个基本的Excel表开始,所有数据都存放在这里。要创建这个表,只需将值输入到特定的行和列集合中。使用最上面的行或最上面的列根据值所代表的内容对值进行分类。
例如,要创建博客文章性能数据的Excel表,可以使用列列出每个“URL”,列列出每个URL的“文章标题”,列列出每个文章的“Views to Date”,等等。(我们将在接下来的步骤中使用这个例子。)
2.按特定属性对数据进行排序。
当你将所有数据输入到Excel表格中时,你会想要以某种方式对这些数据进行排序,以便将其转换为数据透视表后更容易管理。
要对数据进行排序,请单击“数据”选项卡,并选择“排序”下面的图标。在出现的窗口中,您可以选择按您想要的任何列和以任何顺序对数据进行排序。例如,要按“Views To Date”对Excel表排序,请选择下面的此列标题“列”然后选择您想要从最小到最大的排序,还是从最大到最小的排序。
选择“OK”在Sort窗口的右下角,您将根据每篇博客文章收到的浏览次数,成功地重新排序Excel表的每一行。
3.突出显示单元格以创建透视表。
在Excel工作表中输入数据并按自己的喜好排序后,突出显示想要汇总到数据透视表中的单元格。点击“插入”沿顶部导航,并选择“数据透视表”图标。你也可以点击工作表中的任何位置,选择“数据透视表”,然后手动输入你想要包含在数据透视表中的单元格范围。
这将打开一个选项框,除了设置单元格范围外,您还可以在其中选择是否在新工作表中启动该透视表,还是将其保留在现有工作表中。如果您打开一个新工作表,您可以在Excel工作簿的底部导航到它或离开它。一旦你选择了,点击“好吧。”
或者,您可以高亮单元格,选择数据透视表图标右侧的“推荐数据透视表”,然后打开一个数据透视表,其中包含对如何组织每一行和每一列的预先设置的建议。
注意:如果你使用的是早于2016年的Excel版本,“数据透视表”可能在下面“表”或“数据”而不是“插入”。在谷歌Sheets中,可以创建数据透视表“数据”下拉沿顶部导航。
4.将一个字段拖放到“行标签”区域。
完成第1步后,Excel将为您创建一个空白的透视表。下一步是将一个字段(根据电子表格中列的名称进行标记)拖放到“行标签”区域。这将确定透视表将根据什么惟一标识符(博客文章标题、产品名称等)来组织数据。
例如,假设您想要按帖子标题组织一组博客数据。要做到这一点,你只需点击并拖动“Title”字段到“Row Labels”区域。
注意:根据使用的Excel版本不同,数据透视表看起来可能会有所不同。但是,一般原则是不变的。
5.将一个字段拖放到“值”区域。
确定了要组织数据的对象后,下一步是通过将字段拖到“价值观”区域。
让我们继续以博客数据为例,假设您希望按标题总结博客文章视图。要做到这一点,只需将“Views”字段拖到Values区域。
6.调整你的计算。
默认情况下将计算特定值的和,但您可以很容易地将其更改为平均值、最大值或最小值,这取决于您想要计算的值。
在Mac电脑上,你可以通过点击小图标来做到这一点"我”在“值”区域的值旁边,选择你想要的选项,然后点击“确定”。一旦您做出选择,您的数据透视表将相应地更新。
如果你用的是PC,你需要点击数值旁边的小的倒置三角形,然后选择“值字段设置”才能进入菜单。
深入挖掘数据透视表
您现在已经学习了在Excel中创建数据透视表的基础知识。但这取决于你需要pivot表做什么,你可能还没做完。
例如,您可能会注意到数据透视表中的数据没有按照您希望的方式排序。如果是这样,Excel的Sort函数可以帮助您。或者,您可能需要将来自其他数据源的数据合并到您的报告中,在这种情况下,VLOOKUP函数可能会派上用场。
要更深入地了解Excel的世界,了解它的各种功能,请下载我们的全面指南,如何使用Excel.
想要更多的Excel技巧?看看这些创建图表和图形的设计技巧.
编者按:本文最初发表于2018年12月,现已全面更新。
最初发布于2020年10月19日上午7:00:00,更新于2020年10月26日
主题:
Excel