說到數據處理，最常用的就是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）的發起人。
編譯/周煒樂
編輯/王一葦
