说到数据处理,最常用的就是Excel,但你知道如何用它快速清理数据吗?有什么实用诀窍便于掌握?深度君节选网络新闻学教授Paul Bradshaw新书《用数据表寻找好故事》的内容,分享经典指令,助你处理信息。
Bradshaw教授介绍的三个简单又实用的指令是:TRIM
, CLEAN
和SUBSTITUTE
。
清除空格用Trim,数据匹配少差错
我们从最简单的TRIM讲起。它的作用就是删去每个单元格末尾的多余空格。为什么要删空格呢?处理数据时,空格可能是个大麻烦:如果电脑想匹配两个数据,比方说要将某区的犯罪率和人口数配对,哪怕在末尾多加了一个空格,都不能完成指令。所以操作前一定记得删除!
TRIM
作用大,操作也简便,在指令后的括号里填单元格名称就ok了,例如你想删去单元格A2的空格,就在任一空白单元格里键入:
=TRIM(A2)
A2里的数据会出现在这个单元格,但这时前后的空格都已经一扫光啦!想处理A列里的所有单元格,只要在键入第一个指令的同一列里下拉复制该指令,使指令应用到A3、A4等单元格中,就能建立一个“已清理”的列。
巧用查找和替换,隐形字符无处藏
这个指令想必大家都轻车熟路了:点击“编辑”——“查找”——“替换”。
简单指令有其妙用:有时候看似空格的地方其实是另一种字符,用TRIM
又去不掉,在此情况下,用替换指令更合适。
点击“替换”之前,双击单元格,选中你想去掉的奇怪字符,复制到查找框,在“替换为”一栏留白,点击“替换所有”,查看结果。完成后会显示替换总次数,这个数字应该等于包含该字符的单元格数。如果数量超了,则说明系统更换了无关的字符,需排查。
套用TRIM加CLEAN,强力清除一键灵
接下来要介绍的CLEAN
指令用于清除无法显示的字符。你可能好奇,什么样的字符会有此效果呢?登入Ascii-code.com,你就会发现它们大多是回车、退出、退格键、水平制表符等功能字符。
CLEAN
和TRIM
的指令书写同理,把单元格代号填入括号即可:
=CLEAN(A2)
你也可以套用两个指令,这样就可先消空格,再清掉这些恼人的小字符啦。
=CLEAN(TRIM(A2))
替代指令一上手,同类字符速统一
实际上,空格和无法打印的字符是一回事儿,但如果想换其他类型字符,就得用到指令SUBSTITUTE
了——它就像是为每个单元格定制的“查找替换”功能,操作灵活。
SUBSTITUTE
指令由三个基本元素组成:被替代字符所在单元格,被替代字符,替代字符。你也可以选择性填入更换的次数。
如果你手上的多个数据集的数据规范各不相同,此时就该用SUBSTITUTE
统一。
例如,表格可能经常出现如下并用情况:
- “and”和“&”并用
- “percent”和“%”并用
- 指代上千的数字时,小数位和“,”并用
- Dr、Doctor和无学位称呼的情况并存
若想清洗数据,就用如下指令:
=SUBSTITUTE(A2,"&","and")
这代表:
=SUBSTITUTE(单元格A2里的内容,替换'&',用'AND')
如果单元格里原来没有‘&’字符,就不会有内容被替换。
在有些情况下,我们只想替换第一个“&”,那就改成:
=SUBSTITUTE(A2,"&","and",1)
我们可以如法炮制到其他单元格。
要是把此法用于替换国家名称,需注意,有些国家名有没有“&”无大碍,但是这种处理方法会影响安提瓜和巴布达(“Antigua & Barbuda”)等国家名的准确度。
活用字符码,空白代字符
你可以借用SUBSTITUTE指令实现这一功能。例如,你有一长串名单,但想去除所有称呼,只保留简单的姓名,用下面的指令即可:
=SUBSTITUTE(A2,"Mr","")
或:
=SUBSTITUTE(A2,"Mrs","")
就能去掉“Mr”或者“Mrs”这样的称呼了。
如果想替换引号, 该怎么办?
Excel常用引号指代一串字符的开头和结尾,想直接用其他字符替换引号就没那么简单了,例如用=SUBSTITUTE(A6,""","")
把引号替换成空白就会出错。
另一神奇指令CHAR恰好能解决难题。CHAR可以把电脑使用的代码转换成文字。这些代码叫做ASCII,即American Standard Code for Information Interchange,美国信息互换标准代码,主要用于显示现代英语和其他西欧语言,是现今最通用的单字节编码系统,共有255个码。例如,字母“A”对应的码数是65。
问号对应的码是34,所以我们把指令改成:SUBSTITUTE(A6,CHAR(34),"")
需注意,因为CHAR(34)
不是字符,所以别在它前后加上引号。这叫做嵌套指令,具体操作请大家关注《用数据表寻找好故事》。
案例分析:生成网址加速姓名搜索
Bradshaw教授曾参与《镜报》的调查,当时处理的一个数据表包含上百个公司名。为了将纷繁的数字变成有料的故事,他们需要确认每个公司的董事是否曾与某些新闻事件有涉。例如曾是被判刑的罪犯、政治资金捐助者,又或曾逃税、频繁被投诉等等。
传统方法是:到Duedil或Companies House等公司信息大全网站上查阅公司董事的名字和其他信息。
这种重复性的工作,其实非常适合电脑来做。
所以,是时候抛弃这些老法式啦!你可以直接用=SUBSTITUTE指令,为每一间要查询的公司生成URL网址,可直接搜到结果。
操作如下:
在Duedil上查公司Homezone Housing Ltd的名称,搜索结果页面的网址是这样的:
https://www.duedil.com/beta/search/companies?name=Homezone%20Housing%20Ltd
搜索EBM房地产公司,网址如下:
https://www.duedil.com/beta/search/companies?name=EBM%20PROPERTIES%20LTD
注意:每个网址的末尾都是公司名。
如果你有一系列包含公司名称的单元格,直接用单元格替代公司名即可,指令为:
="https://www.duedil.com/beta/search/companies?name="&A2
单元格A2
就会指代一个公司的名字,如果想用多个单元格,如法炮制即可。
需注意:网址中那些%20都是用于代替空格,因此我们可以用=SUBSTITUTE
功能代替任何%20
,指令如下:
=SUBSTITUTE(A2," ", "%20")
换言之:抓取A2
的内容,但用%20替代内容中的空格。
如果公式是在B2单元格里,我们可以重写SUBSTITUTE公式,选中结果:
="https://www.duedil.com/beta/search/companies?name="&B2
当然你可以跳过这些替换的步骤,直接写一个指令把两个功能合二为一:
="https://www.duedil.com/beta/search/companies?name="&SUBSTITUTE(B2," ","%20")
以上指令的目的是将B2
的内容替换成公式:=SUBSTITUTE(B2," ","%20")
。注意忽略最开头的等号,用一次就可(我们之前用过,这回就不加了)。
写网址,细节决定成败,一定记得多检查。例如,如果你确定公司名称拼写正确,不用生成搜索公司信息的网址,直接生成公司页的网址即可。公司页面的网址构成如下(注意需先输入公司编号,公司名称则直接用破折号代替%20
):
https://www.duedil.com/company/IP28306R/homezone-housing-limited
而谷歌搜索用的是+
符号,套用SUBSTITUTE就变成:
=SUBSTITUTE(A2," ","+")
在此之前,记得用TRIM指令清除前后空格,先键入指令:
=TRIM(A2)
再把所得结果套用到公式。
You could also combine both SUBSTITUTE and TRIM like so:
或者直接把SUBSTITUTE和TRIM合二为一:
=SUBSTITUTE(TRIM(A2)," ","+")
你甚至可以将三步合起来:
="https://www.google.co.uk/search?q="&substitute(trim(A2)," ","+")
要点归纳:
TRIM
会移除单元格的多余空格,意义在于保证数据格式一致,便于匹配。- 有些“特殊”的空格实际上是像回车这样的字符,
TRIM
无法去除。我们应换用“编辑——查找——替换”,方法是:将字符黏贴到的“查找”一栏,空着“替换为”一栏,这样就能替换成空白了。注意查看应替换数、实际替换数是否一致。 SUBSTITUTE
会用你输入的内容(包括空白)替换特定的字母(例如“&”),或者字符串(例如“和”)。这和“查找——替换”指令类似,但只影响你指定的单元格。SUBSTITUTE
需要三个元素:被替代字符所在单元格,被替代字符和替代字符。- 如果你只想替换第一个或者头两三个字符,直接在指令后注明即可。
- 如果程序没有查到字符,替换就没法完成。
- 因为引号通常用于指示字符串,如果想替换引号,就得用另一个功能
CHAR
。它可用ASCII码来指代字符。要替换引号,直接用=CHAR(34)
就行了,要是套用SUBSTITUTE指令,就换成=SUBSTITUTE(A6,CHAR(34),"")
。 TRIM
和SUBSTITUTE
一次只能作用于一个单元格,所以要操作整栏的话,就把这些指令在一列中下拉复制,把结果单列到新的“已清理”栏。
Paul Bradshaw是伯明翰城市大学网络新闻学硕士项目负责人,副教授,常在“网络新闻博客”(the Online Journalism Blog)刊文,是调查新闻网站“帮我调查”(HelpMeInvestigate)的发起人。
编译/周炜乐
编辑/王一苇