【笨問題】Excel「數值儲存為文字」小筆記

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

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

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

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

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

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

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

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

  1. 使用選擇性貼上配合乘法運算(跟JavaScript String轉Number有異曲同工之妙 XD)
    EXCEL 移除 ' 符號 並讓「格式為文字」的數字,一次轉換成數字格式的方法 @ Ted's Blog -- 痞客邦 PIXNET --
  2. 另外建一欄位用=VALUE(A1)做轉換
歡迎推文分享:
Published 12 August 2014 09:39 PM 由 Jeffrey
Filed under:
Views: 72,911



意見

# Eric Chen said on 21 September, 2017 05:41 AM

如果是資料庫轉成的Excel有時它會自動加上雙引號"與空白,而資料剖析一次只能處理一欄

1.先將要變為數值的欄位屬性,變更為數值

2.打開附屬程式的記事本

3.將欄位複製起來,可多欄

4.貼到記事本,將雙引號"取代成空白

5.再將貼到記事本的資料複製貼回Excel就大功告成

你的看法呢?

(必要的) 
(必要的) 
(選擇性的)
(必要的) 
(提醒: 因快取機制,您的留言幾分鐘後才會顯示在網站,請耐心稍候)

5 + 3 =

搜尋

Go

<August 2014>
SunMonTueWedThuFriSat
272829303112
3456789
10111213141516
17181920212223
24252627282930
31123456
 
RSS
創用 CC 授權條款
【廣告】
twMVC

Tags 分類檢視
關於作者

一個醉心技術又酷愛分享的Coding魔人,十年的IT職場生涯,寫過系統、管過專案, 也帶過團隊,最後還是無怨無悔地選擇了技術鑽研這條路,近年來則以做一個"有為的中年人"自許。

文章典藏
其他功能

這個部落格


Syndication