中国数据新闻工作坊培训手册第三章 – 实操:Excel进阶技能

分享给朋友:

此练习基于Investigative Reporter and Editors – IRE 的Jaimi Dowdell所编写的材料更改而成

我们来使用世行数据WorldBank.xlsx进行练习,提高分析速度和准确度。

使用筛选功能来迅速找到信息

看一下在“Ineligibility period to”(被禁时段到)一列中的日期。你注意到了什么?

是的。不是所有的条目都是日期。现在我们来练习用filters(筛选)来看多少条目没有日期。

请记着,要打开筛选功能,你只需在你的数据任何一处点击,点击Data (数据)标签,选择 Filter(筛选)图标,见下图:

Excel进阶技能1

你应当注意到下拉箭头出现在你所有列标题的旁边。点击ineligibility period to (被禁时段到)旁边的下拉箭头,你就应该看到一个日期或年的清单(单个日期被年遮住了)和 permanent (永久) (见下图)

Excel进阶技能2

点击Select  All(选择全部) 旁边的对号来取消所有选择。然后点击permanent(永久)旁边的方格。

Excel进阶技能3

点击OK,你现在应该只看见Ineligibility period to(被禁时段到)permanent (永久)的条目。向屏幕左下角看, Excel应该显示按你筛选标准出来的条目总数:607个中有94个,见下图。

Excel进阶技能4

注意左边的行数现在是蓝色。这意味着Excel处于筛选状态。其它条目不过是隐藏着的,你很容易就可以再看到它们- 只要把那列的筛选标准变回到Select All (选择全部), 或者关掉筛选功能就行。要关掉筛选,再次点击Data标签下的Filter(筛选)图标。

Excel进阶技能5

每列旁边的下拉箭头应该都消失,现在你应该能看到所有条目。

 

使用透视图表(pivot table)对数据进行总结

现在我们对数据熟悉一点了,我们可以回答Bergman 2012年报道(“孟加拉位居世行采购黑名单之首”)中的那个问题:

哪个国家被禁的公司和个人最多?

你可以分国数,然后比较各国的数字。你也可以就每个国家进行筛选计数。最快捷的方法是让Excel用一个透视表来给我们做这些事。

透视表让我们把一大批单个记录进行分组总结。在本例里,我们要计数每个国家的条目。计数出的清单应当只有一个国家名和旁边的一个数字。你也可以用透视表和不同数据来回答其它类似问题。

要开始做透视表,首先选择你用的数据。如果你用的是PC,在数据的任何一处点击,同时按下shift、ctrl和8 。在Mac上用Command+A. 不同软件的快捷键可能有所不同,这里我们要选择所有数据。注意到现在你的数据一直到右边第一个空白列和底部第一空白行都被加亮。

 

现在,在Insert(插入)标签下点击,往最左边看,看能否看到如下图10中所示的Pivot table(透视表)图标。图标所在根据你的Excel版本的不同而不同。如果你用的是2003年或之前的Excel,你会发现透视表是在菜单上Data(数据)下。

 

Excel进阶技能6

一个窗口应当出现:

Excel进阶技能7

 

在该窗口里,Excel想知道用于透视表的数据在哪里、在透视表里应当放到何处。因为我们加亮了要用的数据, Table/Range (图表/范围)信息应当是准确的,所以你在此不必做任何改变。 一定要保证选择了New Worksheet (新工作单)来放置透视表,然后点击确定,一个带有透视表工具的新工作单就应当出现。这就是我们创建数据总结结果的地方。

Excel进阶技能8

你将使用右边框中的工具来创建一个透视图。注意你看见了一个包含你原始数据中所有列名(或域)的清单。我们的问题是哪个国家被禁公司/个人数目最多。我们要计数的是不同的国家。把此域从清单上拿出,放到 透视表域名单/在区域间拖动字段 中 行标签 那一部份下面。

你放在行标签部分的任何内容都会沿你的透视表左边出现。每个国家应当只出现一次。你放在透视表中的东西应当只出现一次,而且按字母顺序排列。

 

Excel进阶技能9

现在我们有了一个国家的清单,但我们想知道每个国家在数据库中出现多少次。要做到这点,我们需要计数每个国家的条目数量。每次在透视表中做数学计算,你都会使用PivotTable Field List (透视表域名单)窗口中的Values(值)框。因为我们在计数每个国家出现的次数,需要再把国家列名拿出,放到Values(值)框之下。Excel进阶技能10

 

现在每个国家旁边应当出现一个数字。该数字代表该国家的条目数,在本例中即被禁的个人/公司数目。把游标滚动到底部,注意Excel 已经在你的透视表里计算了一个总数,该数字应当和你原始数据中的条目总数相符(即607)。如果不符,就说明有出错的地方。

因为我们是记者,一般喜欢看到数据按大小顺序排列。我们想知道谁在顶上,谁在底下。要做到这点,我们需要排序。透视表的排序和我们在电子表格中的排序方法不同。

如果你用的是最新版本的Excel,右击国家名旁边的任何一个数字,一个窗口就会出现:

Excel进阶技能11

点击sort(排序),然后选择Sort Largest to Smallest(从大到小排序)Excel进阶技能12

该结果表明,加拿大在清单的顶部,然后是美国、印度尼西亚和英国。

要是你用的是旧版的Excel, 只要点击任何数字,并使用排序的便捷图标(A-Z和Z-A按钮)就行。

在透视表里做筛选

还记得我们看原始数据中Ineligibility period to(被禁时段到)那一列时,看到了94条被永久禁止的公司/个人记录吗?哪个国家被永久禁止的公司/个人数目最多?

要解答这个问题,我们可以利用已经建立的透视表(当然你也可以再重建一个)。

注意在域名单/生成器下面有4个窗口。到目前为止我们已经使用过行标签和值。这次我们要在 报表筛选 下添加信息。

我们知道自己只想看到永久被禁的记录,而且这些记录是在Ineligibility period to (被禁时段到)下面。将此列拖拉到 报表筛选 框里。见下图:

Excel进阶技能13

注意到现在域名可以在透视表上边的A1格里找到。这是那列的筛选依据,和正常电子表格里的筛选依据使用情况相似。根据系统内定,Ineligibility Period To (被禁时段到)的值都显示了出来。你只所以知道这一点,因为B1格里说 全部。

点击ALL旁边的箭头,选择permanent (永久)。请注意你可以同时选择一个以上的值,但在此练习中,我们不会使用此选择。点击 OK。

Excel进阶技能14

透视表现在就开始筛选。请看总数- 应该是94。这和在电子表格里进行同样筛选所得出的结果相符,再最后进行一次排列,你现在就可以看出哪个国家被永久禁止的公司/个人最多。

挑战题

被禁时段开始于2010~2012年之间最多的国家是哪一个?

提示:你需要对原表格中的一列数据进行处理后,再使用数据透视表得出答案。

答案:Indonesia

常见问题

  • 我把列出列名的框弄丢了,怎么弄回来?

要把数据透视表生成器窗口弄回来,只要在透视表上任何地方点击就行。

  • 我加了一个新的列,但域名单(field list)中没有显示。

在Options(选择)标签下,点击 Refresh(刷新)图标。

要是上一步不行,删除有你透视表的页面,保存工作册,然后做一个新的透视表。

  • 我无法开动透视表。系统不断给我域名不正确的奇怪提示。

每一列都必须有标识。如果有一列没有标题,透视表就会拒绝开动。

  • 我知道在透视表里应当是独特的值,但我看到有重复值。问题在哪儿?

虽然我们肉眼看到的两个字词完全一样,它们前后有没有空格对Excel很重要。假如发生这种情况,在你的数据中添加一列,并使用trim(削减)功能来给它值。举例来讲,如果在透视表里显示重复值的原始数据是在B列,我会添加一个新列,给它一个名字,然后在第二行里写下这个公式: =trim(B2)。得出的结果看上去和原来一样,但所有多余的空格都没有了。

请记着透视表占用很多空间。

通过透视表里得到你要的东西后,我推荐你加亮该表,复制,到新的一页,右击,选择Paste special (特别黏贴), 然后选择 values (值)。这样所有数据会被复制下来,但不包括透视表工具等。你然后就可以把原来透视表所在的页面删除掉。


点击进入下一章节:第四章:数据可靠性和数据清理
想回到目录请点击这里

作者简介

数据新闻工作坊

数据新闻网以引介全球范围内最顶尖的数据新闻实践为初衷,以推动数据开放及媒体革新为宗旨,面向中国的新闻从业者、媒体管理者、新传教育者以及对传媒感兴趣的设计师、程序员,提供线上信息平台与线下交流机会。