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