在我們的日常生活中,常遇到以一長串數字組成的內容,像是產品序號、商品代碼,又或者是電話號碼。然而,在將這些內容輸入至 Excel 和 Google 試算表時,你可能會發現有些內容沒辦法如你所願被正確地顯示出來,例如將一串較長的數字序號放進 Excel 會變得好像亂數:
使用 Google 試算表時雖然不會出現亂數,但有時你可能也會發現剛貼上的編號開頭的 0 不見了:
這是怎麼回事?怎樣才能調回來?類似這樣的問題,其實通常並不是資料壞掉,只是系統猜錯了你需要的「儲存格格式」,讓你的數字呈現不符預期(甚至有點驚嚇)。只要你對 Excel 和 Google 試算表裡的「格式」更有概念,下次遇到這類問題時,就更能知道解決方向。
因此,接下來我們會先跟你快速解說幾個基礎的「儲存格格式」運作原理,讓你掌握基本邏輯,然後實際示範如何透過調整儲存格格式,解決 3 個最常見的「數字亂跑」困擾:長串數字變亂碼、開頭 0 消失、輸入分數(如 1/3)被系統辨認成日期。(若有需要,可直接點超連結或目錄切換查看對應範例)
要了解「數字亂掉」問題,首先我們要知道,在使用 Excel 和 Google 試算表時,每一格資料,都會有一個對應的「儲存格格式」,這些格式會影響系統怎麼辨識輸入的資料,以及要以什麼樣的方式呈現。
以上面這張圖來說,雖然兩者紀錄的值都是 1,但左方 Excel 儲存格 A1 顯示的 “1.00” 就是指定為「數值」格式;右方 Google 試算表儲存格 A1 顯示的 “100.00%” 就是指定為「百分比」格式。
在你沒有特別設定時,不管是手 key 或貼上資料,Excel 和 Google 試算表都會自動套用系統預設為你選擇的格式。以 Excel 來說,不做任何設定時,系統預設給我們的格式叫做「通用格式」;以 Google 試算表來說,預設給我們的格式則叫做「自動格式」。
Excel「通用格式」顯示資料的基本邏輯是「輸入什麼就顯示什麼」,因此多數時候你的數字並不會有任何變動。但有以下例外:
輸入的資料 | 通用格式顯示的樣子 | 背後的處理邏輯 |
---|---|---|
0922333444 | 922333444 | 系統認為輸入的值是可計算的數字,可刪除開頭無意義的 0 |
1234567890123 (超過 12 位的數值) |
1.23E+11 (以科學記號顯示) |
需要精簡過長資訊 |
1/3 (有 "/" 符號的數值) |
1月3日 (以日期格式顯示) |
系統認為輸入的是日期 |
Google 試算表「自動格式」是類似的邏輯,只是它「自作主張」的幅度小一些,不會將較長的數值或包含「/」的數值轉換形式。但是,在 Google 試算表輸入開頭有 0 的數值時,0 也會被省略。
這樣的處理邏輯有時能讓你很方便(例如 - 輸入 1/3 確實是在記錄日期、也希望顯示成 「1 月 3 日」),但有時正好很不適合(例如 - 輸入 1/3 是要表示「三分之一」),不適合時,就容易出現文章開頭說的「數字亂跑」困擾。
理解原因後,只要另外找到真正適合我們情境的儲存格格式來做設定,就可以避免相關問題。
不過,這裡也要先提另一個需要注意的概念:如果你輸入、貼上資料後,發現數字怪怪的,判斷可能是格式問題,此時「直接手動改動格式」往往沒用。
這是因為儲存格格式有時不只影響儲存值的外觀,特定情況下它還可能直接調整實際上儲存的值,所以如果在「輸入數值」後才「更改格式」,很多時候已經來不及了;最保險的方法就是先「更改格式」,再「輸入數值」,特定情況下先設定好儲存格的格式後,再將值放入儲存格。
至於哪些情況應該調整成什麼格式呢?以下我們就直接從個別範例來一一做解說!
在 Excel 裡,在貼上一長串的數字之後,你可能會發現格內的值竟然變成像是「1.23E+11」這樣看起來非常可怕的亂數:
發生這件事情的原因,其實是因為系統將這串數字辨認為了數值,所以在顯示較大的值時會選擇使用科學記號來精簡地記錄這個值。
也就是說,要避免這樣的情況,只要將指定儲存格的格式設為「文字」(而非套用預設的通用格式),就能夠避免值被以科學記號的方式顯示了。
在 Excel 裡,選擇要更改設定的儲存格範圍後,從原本顯示「通用格式」的位置展開下拉選單就能夠找到「文字」的選項:
經過這樣的設定之後,重新將原本的資料貼到工作表,就可以看到工作表內儲存、顯示的資料都是完整的序號了:
將儲存格格式改為「文字」雖然能夠確保輸入的所有內容都被完整記錄、顯示,不會因為數字過長而被精簡為科學記號,但文字格式本身並不適合公式運算,在一部份的情況下(例如公式欄位曾參照過文字格式,導致欄位本身也自動被轉為文字格式時),會無法觸發公式計算,或是在計算時產生不可預期的錯誤:
假如你只是希望長數字能夠不被簡化、改變呈現方式,但同樣保留數值的計算彈性,可以將儲存格格式改為「數值」後,再對小數位數進行增減(預設的數值格式會固定顯示到小數點後兩位)。
你可以從格式的下拉選單中找到「數值」格式選項:
在將格式更改為數值後,儲存格內的資料會被加上小數點後兩位,儘管小數點後兩位沒有數字也會顯示 “.00”:
這個時候,你可以藉由點擊下圖的「減少小數位數」按鈕,調整需要顯示的小數位數:
最後再輸入公式檢查是否能夠正確計算出結果:
另一個常見的情境,是在將各種編號資料貼上至 Excel 或 Google 試算表後,你會發現有些值最前方的 0 神祕地消失了:
導致 0 消失的原因其實也跟預設格式辨認內容的方式有關。因為輸入的值全部都是數字的關係,因此 Excel 和 Google 試算表的預設格式都會直接將這些值辨識為數值。又因為在數學上,開頭的 0 多半是無意義的,這些值開頭的 0 才會直接被省去。
也就是說,這個問題的解決邏輯其實與前一點類似,只要將儲存格轉為文字格式紀錄,再重新將資料輸入,系統就能夠保留你輸入的所有值,正確儲存編號開頭的 0 了。如果你是使用 Excel 的話,可以點擊這裡查看 Excel 的設定操作步驟。
在 Google 試算表內,你可以點擊 “123” 的圖示,展開格式選單,並點選純文字以轉換格式:
再將原先的資料貼上,就能發現所有的資料都被完整紀錄並顯示了:
我們前面提到的所有情境都是基於你自己操作 Excel 與 Google 試算表時,手動輸入或貼上資料時會發生的狀況。
除了手動輸入以外,另一種可能性是在嘗試打開外部系統匯出的 csv 時,數字首位的 0 因為數值格式的規則而消失:
其實這是因為 csv 本身並沒有包含任何格式定義,導致 Excel 使用「通用格式」來辨識所有的儲存格,所以我們需要讓 Excel 能夠知道我們目前匯入的儲存格是屬於「文字」。
為此,你可以參考這篇說明,使用 Excel 的功能「匯入字串精靈」以在匯入資料前預先指定每一欄的格式。
如果你打算透過 Excel 來記錄食譜,也許會常常需要使用「1/3 茶匙」、「1/4 杯水」這樣的描述方式。但要是直接將 1/3、1/4 這樣的數字輸入到 Excel,通用格式就會自動將這些資料變成日期:
資料變成日期最大的困擾是,Excel 並不只是換個方式顯示原先的內容,而是會直接將輸入的內容轉化為該日期的值,例如輸入的 1/3 變成 2023/01/03 以後,再將儲存格改為通用或數值格式,會發現儲存格的值變成 “44929” 這個與原本的 “1/3” 完全無關的值:
因此,要輸入分數的話也必須先確保儲存格的格式設定正確,除了能夠將儲存格改為「文字」確保值不會被更改之外,你也可以將格式改為「分數」以利於計算:
分數格式在進行計算之後,會以適當的方式分隔整數部分與分數部分,對於常常需要計算多份用量的使用者來說更加方便、易懂。
如果你手上有著分數已經被更改為日期的資料,希望至少能夠讓這些資料以分數的方式呈現,你可以先更改日期的顯示格式,再匯出為 csv 檔案,最後重新匯入並指定為「文字」格式的方式來更改儲存值,如果需要取得原始的分數值以方便計算,也可以再利用字串公式達成。
首先,你需要在格式設定中找到「其他數字格式」,再依序找到「日期」中的「3/14」類型:
接下來,再將檔案儲存為「CSV UTF-8(逗號分隔)」:
最後,你可以再利用匯入字串精靈,將剛剛儲存的 csv 檔匯入,並在最後一個步驟時指定應該儲存分數的欄位為「文字」:
點擊完成後,Excel 就會以「文字」格式儲存這些分數了:
有別於 Excel 和 Google 試算表的預設格式,會發生因為輸入的資料不同而發生變化的狀況,Ragic 的每一個欄位都是在設計表單時就決定好欄位種類(並同步設定欄位格式)的,因此在保存設計之後,不會基於實際使用時登錄的資料,使欄位格式發生變化。
如果沒有特別設定的話,Ragic 會預先套用「自由輸入」這個種類,自由輸入的操作邏輯就像 Excel 和 Google 試算表的「文字」格式,不管輸入什麼都會完整保存,因此儘管沒有指定欄位種類,也不用擔心輸入的資料被更改、調不回來:
每個欄位種類都有各自的特性與適合的資料內容,假如在設計表單時就預先指定這個欄位為「編號/號碼」,未來這個欄位就會完整保存輸入的所有數字,不會因為資料太長而被精簡化,或是省略首位的 0:
例如 EAN-13 這個全球通用、隨國別不同,而可能有些編號以 0 作為開頭的商品編號格式。透過使用我們的編號/號碼欄位加上我們預先設計好的 EAN-13 格式,你就能夠在直接輸入數字的情況下得到包含首位 0、也正確以標準格式編排的 EAN-13 代碼了:
又或者是我們開頭提到的電話號碼,在 Ragic 也有專屬的欄位種類與格式,讓你運用適合的選項來登記不同地區的格式記錄號碼,以適當的分隔方式提升資料的易讀性。另外,Ragic 也設計了直接點擊電話格式內的號碼以撥出的功能:
標籤: Excel