解決 Excel 長數字的最後一位歸零或出現科學記號的問題

日期：

當 Excel 的數字太長，最後一位將會四捨五入歸零或出現科學記號。透過原始資料 (CSV) 轉成 Excel 設定文字資料類型解決問題。

Industrial Machine Photo
Photo by Joe deSousa on StockSnap


問題狀況

CSV 轉換成 Excel (XLSX) 檔案時，發現長數字 (有 16 位) 的欄位值的最後一位變成零或出現科學記號 (科學符號)。例如：原本欄位如果數字太長，最後一位都會變成零。

  • 1234567890123454 變成 1234567890123450
  • 1234567890123455 變成 1234567890123460
  • 1234567890123456 變成 1234567890123460
  • 12345678901234567 變成 1.23456789012346E+016
原因是 Excel 數字精準度只到 15 位有效數字 (資料來源：微軟)。微軟文件提到輸入時在 Excel 欄位值前面加上單引號或者是將儲存格格式改成文字。

現在遇到的狀況是 CSV 是正常的，如何轉換到 Excel 後，不會被歸零或出現科學記號。

測試用檔案

CSV 檔案內容： (產品名稱由 ChatGPT 提供)
No.,產品編號,產品名稱
1,1234567890,閃耀星空指甲油套裝
2,12345678901,宇宙探險智能機器人
3,123456789012,瞬間冰涼輕便空調扇
4,1234567890123,靈活運動藍牙運動手環
5,12345678901234,無痕美髮抗熱吹風機
6,123456789012345,神秘花園精油擴香器
7,1234567890123456,無限視野VR眼鏡
8,12345678901234567,遠行者戶外多功能背包

另存成 input.csv 檔案 (UTF-8 編碼)

解決方式

方法 1：使用 Excel 匯入 CSV 檔案

(1) Excel 不能直接點兩下開啟 CSV 檔案，會變成亂碼

Excel 不能直接點兩下開啟 CSV 檔案，會變成亂碼

(2) 先建立空白活頁簿

(3) 切換到「資料」標籤，點選「取得資料 (Power Query)」按鈕

點選「取得資料 (Power Query)」按鈕

(4) 選擇資料來源：「文字/CSV」

選擇資料來源：「文字/CSV」
(5) 選擇電腦上的 CSV 檔案
選擇電腦上的 CSV 檔案
(6) 可以預覽前幾筆的資料內容，雖然這時候看起來資料正常，但還不能按「load」按鈕
預覽前幾筆的資料內容

點選右下的「轉換資料」按鈕
點選右下的「轉換資料」按鈕
(7) 進入 Power Query 編輯器。顯示資料預覽畫面，在每一欄欄位名稱的右邊，標示了資料類型的圖示，例如「123」圖示代表數字、或「ABC」圖示代表示文字類型。

點選欄位名稱「產品編號」右邊的 123 圖示。
點選欄位名稱「產品編號」右邊的 123 圖示。
修改為 text (文字) 類型。
(8) Excel 詢問是否「變更資料行類型」，請選擇「取代所有」的按鈕
點選「取代所有」的按鈕
可以看到欄位名稱「產品編號」右邊，原本是 123 圖示，變成 ABC 圖示。
欄位名稱「產品編號」右邊，原本是 123 圖示，變成 ABC 圖示。

(9) 點選左上角「Close & load」按鈕
點選左上角「Close & load」按鈕
可以看到 Excel 的產品編號不會被歸零或出現科學記號
可以看到 Excel 的產品編號不會被歸零或出現科學記號


方法 2：使用 LibreOffice Calc 匯入 CSV，再轉換成 Excel (XLSX) 檔案

當 CSV 檔案包含換行符號的欄位值時，Excel 轉換常會出現錯誤，所以我常用 LibreOffice Calc 將 CSV 轉換成 Excel 檔案。

(1) 「Text Import」對話視窗，可以設定 CSV 檔案詳細選項，以及預覽資料。這時候還不要按「OK」按鈕。
「Text Import」對話視窗，可以設定 CSV 檔案詳細選項，以及預覽資料
(2) 選擇欄位名稱「產品編號」上方的資料類型「Standard」
選擇欄位名稱「產品編號」上方的資料類型「Standard」
(3) 將欄位的資料類型 (Column type) 從「Standard」改成「Text」
將資料類型 (Column type) 從「Standard」改成「Text」
可以看到欄位名稱「產品編號」上方的資料類型「Standard」，順利變成「Text」
可以看到欄位名稱「產品編號」上方的資料類型「Standard」，順利變成「Text」
(4) 按「OK」按鈕，CSV 檔案順利匯入到 LibreOffice Calc
CSV 檔案順利匯入到 LibreOffice Calc
如果匯入 CSV 檔案時，沒有選擇 Text 資料類型，可以參考這篇文章 How do you convert numbers to text? - English - Ask LibreOffice 修改。

(5) 另存新檔
另存新檔



檔案類型 (File type) 要選「Excel 2007-365 (.xlsx)」，再按「Save」按鈕
檔案類型 (File type) 要選「Excel 2007-365 (.xlsx)」，再按「Save」按鈕

(7) 開啟 Excel 檔案，也可以看到產品編號不會被歸零或出現科學記號

留言

張貼留言