数据表难处理?几个清理指令有妙用

Print More

Bradshaw教授介绍的三个简单又实用的指令是:TRIM, CLEANSUBSTITUTE


清除空格用Trim,数据匹配少差错

我们从最简单的TRIM讲起。它的作用就是删去每个单元格末尾的多余空格。为什么要删空格呢?处理数据时,空格可能是个大麻烦:如果电脑想匹配两个数据,比方说要将某区的犯罪率和人口数配对,哪怕在末尾多加了一个空格,都不能完成指令。所以操作前一定记得删除!

TRIM作用大,操作也简便,在指令后的括号里填单元格名称就ok了,例如你想删去单元格A2的空格,就在任一空白单元格里键入:

=TRIM(A2)

A2里的数据会出现在这个单元格,但这时前后的空格都已经一扫光啦!想处理A列里的所有单元格,只要在键入第一个指令的同一列里下拉复制该指令,使指令应用到A3、A4等单元格中,就能建立一个“已清理”的列。

巧用查找和替换,隐形字符无处藏

这个指令想必大家都轻车熟路了:点击“编辑”——“查找”——“替换”。

简单指令有其妙用:有时候看似空格的地方其实是另一种字符,用TRIM又去不掉,在此情况下,用替换指令更合适。

点击“替换”之前,双击单元格,选中你想去掉的奇怪字符,复制到查找框,在“替换为”一栏留白,点击“替换所有”,查看结果。完成后会显示替换总次数,这个数字应该等于包含该字符的单元格数。如果数量超了,则说明系统更换了无关的字符,需排查。

套用TRIM加CLEAN,强力清除一键灵

接下来要介绍的CLEAN指令用于清除无法显示的字符。你可能好奇,什么样的字符会有此效果呢?登入Ascii-code.com,你就会发现它们大多是回车、退出、退格键、水平制表符等功能字符。

CLEANTRIM的指令书写同理,把单元格代号填入括号即可:

=CLEAN(A2)

你也可以套用两个指令,这样就可先消空格,再清掉这些恼人的小字符啦。

=CLEAN(TRIM(A2))

1

替代指令一上手,同类字符速统一

实际上,空格和无法打印的字符是一回事儿,但如果想换其他类型字符,就得用到指令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,""","")把引号替换成空白就会出错。

2

另一神奇指令CHAR恰好能解决难题。CHAR可以把电脑使用的代码转换成文字。这些代码叫做ASCII,即American Standard Code for Information Interchange,美国信息互换标准代码,主要用于显示现代英语和其他西欧语言,是现今最通用的单字节编码系统,共有255个码。例如,字母“A”对应的码数是65。

问号对应的码是34,所以我们把指令改成:SUBSTITUTE(A6,CHAR(34),"")

需注意,因为CHAR(34)不是字符,所以别在它前后加上引号。这叫做嵌套指令,具体操作请大家关注《用数据表寻找好故事》。

案例分析:生成网址加速姓名搜索

Bradshaw教授曾参与《镜报》的调查,当时处理的一个数据表包含上百个公司名。为了将纷繁的数字变成有料的故事,他们需要确认每个公司的董事是否曾与某些新闻事件有涉。例如曾是被判刑的罪犯、政治资金捐助者,又或曾逃税、频繁被投诉等等。

case study

传统方法是:到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),"")
  • TRIMSUBSTITUTE一次只能作用于一个单元格,所以要操作整栏的话,就把这些指令在一列中下拉复制,把结果单列到新的“已清理”栏。

Paul BradshawPaul Bradshaw是伯明翰城市大学网络新闻学硕士项目负责人,副教授,常在网络新闻博客”the Online Journalism Blog刊文,是调查新闻网站“帮我调查”HelpMeInvestigate的发起人。

 

编译/周炜乐
编辑/王一苇

Leave a Reply

Your email address will not be published. Required fields are marked *