數據表難處理?幾個清理指令有妙用

Print More

Featured image說到數據處理,最常用的就是Excel,但你知道如何用它快速清理數據嗎?有什麼實用訣竅便於掌握?深度君節選網絡新聞學教授Paul Bradshaw新書《用數據表尋找好故事》的內容,分享經典指令,助你處理信息。

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的發起人。

 

編譯/周煒樂
編輯/王一葦

Print Friendly, PDF & Email

發表回復

您的電子郵箱地址不會被公開。 必填項已用 * 標註