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

说到数据处理，最常用的就是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）的发起人。

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