如何在Excel中创建数据透视表:一步一步的教程
在我之前的博客文章中,我说:
- 多么好的数据透视表啊
- 在处理大量新数据时，它如何成为我Excel库中的第一武器
- 如何创建简单的数据透视表，包括使用推荐的数据透视表
在这篇博客文章中，我将描述:
- 如何创建更高级的数据透视表，
- 您的初始数据需要在什么条件下才能使用
- 如何使用数据透视图扩展数据以创建Excel指示板。
数据应该采用什么格式?
首先，您需要一些数据。以下是一些你可以使用的数据:
- 它可以是销售数据——例如，公司销售人员的统计数据以及他们每月的总销售额。
- 它可以是带有详细交易信息的金融数据。
- 可能是网站访客数据。
对于本文，我使用的是我拥有的音频文件的数据，其列标签为文件名、专辑、大小和购买日期。
您必须将使用的数据分为行和列。以下是创建正确格式的一些提示：
- 每一行都需要涉及一个类似的项目。例如，在我的数据中，每一行代表一个不同的音频文件。
- 每一列都需要表示项的一个属性。在我的数据中，我有:
- ID号的数字列，
- 文本列的文件名，和
- 购买日期的日期/时间栏。
- 您的数据中的第一行需要是项目属性的标签。在我的示例中，我有文件名，专辑和日期的行标题。我有不同颜色和字体的行标题，以区分标题。它看起来不错，但这不是要求。
- 应该没有合并的细胞。如果列C和D共享单元格，则Excel可能很难知道创建数据透视表时的列数据。
- 理想情况下，行或列中不应该有任何空白。如果我想在列中显示空白，我仍然会添加一个简短的列标题，但是使用相同的前景色和背景色，这样您就看不到文本了。
- 如果行中有空白，可能会导致以下问题之一:
- 你的数据透视表可能只使用到间隙的数据，这意味着它不会使用你的所有数据。
- 这个空行在数据透视表中就像它实际上是数据一样。例如，文件名将在数据透视表中显示为“(blank)”。
让我们创建一个数据透视表
在创建数据透视表之前，首先突出显示源数据。如果您不这样做，Excel将猜测源数据范围的实际位置，并可能得出错误的结果。如果数据存在缺口，情况尤其如此。所以让我们创建一个数据透视表，高亮显示范围，然后选择插入->数据透视表，然后点击确定。
通过这样做，我们在Excel工作簿中创建了一个新的电子表格。左边是数据透视表的大纲，右边是一个新窗格，顶部是数据透视表字段列表，下面是四个区域。
让我们创造我们的第一个分析
从这里，我们可以向下拖动一个字段。让我们将相册字段拖放到Rows区域。如果您想要分析跨越而不是向下，您可以将它拖到列区域。
从一个简单的拖动字段，您可以看到一个分析，我们有大约20个不同的专辑。在这个阶段，我们不知道每个专辑在数据中有多少行，所以让我们再次将专辑字段向下拖动，但这次是到Values区域。
因此，现在您还可以看到每个相册在我的数据集中出现的次数。
现在让我们找出每个专辑的字节大小。要发现这一点，我们也可以将“Size”字段拖到值区域。
看起来不错，但让我们从另一个角度来看数据。我们不计算相册文件大小的总和，而是找出最小的文件大小。要做到这一点，我可以:
- 点击“大小总和”旁边的下拉箭头。
- 选择“值域设置”。
- 在“新建”对话框中，将“Sum”汇总值更改为“Min”，然后单击“确定”。
成功!我还可以加上Size的最大值。为此，我再次拖动“大小”字段到值区域，然后以同样的方式将它从“大小总和”更改为“最大大小”。
这看起来像一个有用的分析。
扩大你的数据
假设您扩展了源数据，添加了额外的1,000行。不幸的是，你的数据透视表不会知道你这么做了，它会引用和你创建数据透视表时相同的行数。
要更改数据透视表应该使用的数据范围:
- 单击数据透视表内部。
- 转到数据透视表分析->更改数据源->更改数据源。(在Excel 2010和更早的版本中，你需要打开数据透视表工具->选项->更改数据源->更改数据源。)
- 更新对话框中的数据源范围，该屏幕突出显示当前范围。
如果您需要定期扩展数据，那么您应该考虑将现有的范围转换为Excel表。为此，突出显示数据并转到Insert选项卡- >表并单击OK。
新的数据透视表可以使用这个表对象，你也可以改变现有数据透视表的数据源。数据透视表现在链接到一个当你添加额外行时可以自动展开的表，而不是链接到一个通常不会自动展开的范围。你的数据透视表总是会链接到正确的行数。当你刷新数据透视表时，你就会得到基于扩展数据集的更新分析。
计算百分比
现在，找出每一个“专辑数量”数字在所有数据中所占的百分比将会很有用。你可以用公式做到这一点，但你也可以在数据透视表本身中做到这一点。
- 首先，让我们通过将它们拖出值区域(或单击它们并选择“删除字段”)来删除最小和最大大小分析。
- 现在，让我们在Values区域再次向下拖动Album字段，它创建第二个“Count of Album”列。
- 我们可以在“值”区域中单击这个新字段，然后再次进入“值”字段设置。
- 在“值字段设置”对话框中有两个选项卡。通过单击第二个选项卡，我们可以改变它的计算方式。
- 在第二个选项卡中，通过点击“% of Grand Total”改变计算方法。
- 您可能还想在“自定义名称”部分更改此字段的名称。
通过这样做，我们可以看到每个专辑的条目占总数的百分比。
在第二个选项卡中可以执行许多其他计算。创建高级数据透视表时值得探索此选项卡。
添加一个PivotChart
我们现在可以基于这个数据透视表创建一个图形或图表。要做到这一点，我们所要做的就是点击数据透视表内部，进入插入- >图表，并选择我们想要使用的图表类型。
数据透视表和数据透视表是相互链接的，因此对数据透视表所做的任何更改都会反映在数据透视表中，反之亦然。在这个例子中，我将“Count of Album”从数据透视表中拖出数值区域。一旦我这样做了，这个字段也会从数据透视图中删除。
过滤值
您还可以过滤数据。例如，我们的专辑列表中有一个排版错误，“a musical offers”拼错了。我可以用这些数据做以下几件事:
- 我可以在源数据中更正它。
- 我可以在数据透视表中把类似的项目分组在一起我的前一篇文章。
- 我可以从分析中删除它。
为了删除它，我点击数据透视表中“行标签”旁边的下拉箭头，取消选中该项目。
选择数据类别
在本系列的前一篇文章中时，我将专辑按类型分类，比如音乐、歌剧和音频。如果我只想显示其中一种类型呢?
- 首先，我必须重新评估这些专辑。看一下我的前一篇数据透视表文章看看如何做到这一点。
- 我还将这个新字段重命名为“Album Type”。一种方法是点击一个字段，进入数据透视表分析(或者Excel 2010中的数据透视表选项)->活动字段->活动字段。
- 将这些专辑分组后，我可以将“Album Type”字段拖到数据透视表的过滤器区域(早期版本的Excel称之为Page部分)。这个区域允许我使用一个下拉列表来限制出现什么专辑类型。
尽管如此，Excel 2010还引入了另一个更友好的界面——切片器(Slicer)。要添加切片器，单击数据透视表中的任何位置，进入数据透视表分析- >过滤器- >插入切片器，并选择你想要切片的字段。
这将显示一个切片器，它包含该字段中的值。然后你可以点击其中任何一个(或者按住Ctrl键点击多个)来筛选数据透视表。对我来说，这是一种比去过滤器区域更简单的方法，当你创建一个仪表板时更有用。
在Excel中创建一个仪表板
一个仪表板通常包含不止一个分析。让我们用创建第一个数据透视表的方法创建第二个数据透视表，并把它放到同一个电子表格中。这一次:
- 将“购买日期的年份”字段拖到Rows区域(参见我的上一篇文章)如何向数据透视表添加日期)
- 将“专辑计数”字段拖到“值”区域。
- 基于第二个数据透视表创建饼图。
我们的分析现在看起来就像一个仪表盘。不幸的是，切片器只影响一个数据透视表，而不是两个数据透视表。因此，如果我在切片器中选择了“Opera”作为我的相册类型，这将在第一个数据透视表及其数据透视图中创建一个过滤器，而不是在第二个数据透视表及其数据透视图中。
要允许切片器同时影响多个档位，请右侧单击切片机并转到报表连接。
目前，只有一个选中的数据透视表。当我们检查两个数据透视表并点击确定时，切片器现在连接到两个数据透视表。当我们改变切片器中的选择时，数据透视表和数据透视表都改变了。现在我们已经在Excel中创建了一个可以工作的仪表板。
添加一个时间表
让我们通过插入时间轴来进一步开发仪表板。Excel 2013引入了这个特性。要插入时间轴，在数据透视表中单击，然后转到数据透视表分析- >插入时间轴。然后，Excel将显示所有可用的日期字段。
点击“购买日期”字段，然后点击“确定”。“我们现在有了一个新的时间表。与切片器一样，我们可以右键单击时间轴，并选择“Report Connections”，将其连接到数据透视表及其数据透视图表。
现在我们可以用图形来限制数据透视表中日期的范围。我们可以查看单个年，或一组年，或者我们可以使用右上角的指示器，以季度、月或日来查看时间轴。
办公室生产力的顶尖课程betwayiosapp
感谢您阅读这篇文章
我希望您喜欢这篇文章。在数据透视表中还有更多的功能可以探索。
我的课程,”分析和可视化数据与微软Excel”,包含:
- 六个小时的档位和Pivotcharts视频
- 大量的练习活动，这样你就可以确保你在学习
- 几个小时的视频“获取和转换”和PowerPoint，这将把您的Excel使用到一个新的水平
如果你对此感兴趣，可以看一看我的课程去学习更多。非常感谢你阅读这篇文章，并继续学习!
