拿到一份資料庫查詢匯出的Excel檔,想加工卻碰了釘子。如下圖,明明是數字,用SUM(A1:A4)加總卻得到0。

全是幻覺,嚇不倒我滴!一切皆因儲存格格式被設成文字。

已知原因,但經驗不足,後續處理不怎麼順利。先試著將儲存格格式改為數值,加上千位號並調小數位數,但因欄位已被認定成文字,不動如山。換一招,複製整欄再用選擇性貼上,試了不同選項,文字就是文字,怎麼都無法變回數字。

【小筆記】一般手動輸入時若想將數字強迫存為文字(最常見情境是電話號碼,遇到通用格式前方的零會被移除),做法是在數字前方加上單引號(')。如果文字內容來自複製,要避免被解讀成數字走味,則可先將儲存格格式指定成文字再貼上即可。

所幸,Excel夠貼心!當數字被視為文字儲存,儲存格左上角會出現綠色三角,同時有選單提供處理選項,其中有項「轉換成數字」,執行後Excel就會將欄位解讀成數字。另外,「轉換成數字」也支援批次處理,可以選取範圍後一次轉換。

同事分享了另一種做法,使用「資料剖析」也能快速將整批文字轉成數字,雖然步驟較多,但能對不同欄位指定不同轉換原則,還有很多花式應用,威力強大,特別適合複雜情境。

轉換為數字後,就能順利加總了。

爬文還找到其他解法,但都不如「轉換成數字」及「資料剖析」簡便,補充於後:

  1. 使用選擇性貼上配合乘法運算(跟JavaScript String轉Number有異曲同工之妙 XD)
    EXCEL 移除 ' 符號 並讓「格式為文字」的數字,一次轉換成數字格式的方法 @ Ted's Blog -- 痞客邦 PIXNET --
  2. 另外建一欄位用=VALUE(A1)做轉換

Comments

# by Eric Chen

如果是資料庫轉成的Excel有時它會自動加上雙引號"與空白,而資料剖析一次只能處理一欄 1.先將要變為數值的欄位屬性,變更為數值 2.打開附屬程式的記事本 3.將欄位複製起來,可多欄 4.貼到記事本,將雙引號"取代成空白 5.再將貼到記事本的資料複製貼回Excel就大功告成

# by Mike L

我的資料是從sharepoint匯出的excel檔,一直想要以裡面的數字資料計算公式都無法(EX.加總顯示0)。 原文的方法試了都不行...感謝Eric Chen大大 真是救了我

# by Ellis

感謝分享~

Post a comment