Microsoft Excel: Difference between revisions

From LemonWiki共筆
Jump to navigation Jump to search
mNo edit summary
Line 231: Line 231:


== Troubleshooting of Excel errors ==
== Troubleshooting of Excel errors ==
[[Troubleshooting of common Excel errors in Mandarin]]
=== Excel 效能議題 ===
* 資料筆數大量時的替代方案
** 如果 Excel 資料筆數約百萬筆，操作速度約耗費數小時，可改用資料庫。資料庫資料處理後，再輸出成 Excel 檔，而不要在 Excel 檔上面進行資料處理或換算。
** 如果要刪除符合特定條件約一萬筆的資料列太慢時。改成將篩選出符合另一條件的資料列，再複製貼上到新的工作表，會比較快。如果直接複製貼上也花超過5分鐘時間，可以選擇直接貼上值。相關文章：[https://errerrors.blogspot.com/2020/03/resolve-too-slow-to-delete-data-rows-in-excel.html 解決 Excel 刪除資料太慢的問題]
* 降低資料處理複雜度
** 全選工作表儲存格資料，複製後，選擇性貼上值到空白工作表。因為移除公式，所以另一工作表的操作速度會加快。 {{exclaim}} 需要注意時間格式的數值會跑掉，變成一長串數字，需要額外設定儲存格格式成時間格式。
** 多重篩選條件會使用比較多的系統資源，在不使用篩選條件下，改成使用函數處理，會比較快。
* 電腦效能
** 軟體從 32位元 升級為 64位元版本，能較有效率使用記憶體。 e.g. [https://support.office.com/en-us/article/Choose-between-the-64-bit-or-32-bit-version-of-Office-2dee7807-8f95-4d0c-b5fe-6c6f49b8d261 Choose between the 64-bit or 32-bit version of Office - Office Support]
** 關閉沒有在使用的 Excel 檔案，節省電腦效能
* 切換使用不同軟體
** 先使用 [https://zh-tw.libreoffice.org/discover/calc/ LibreOffice Calc] 操作 ODS ([https://en.wikipedia.org/wiki/OpenDocument OpenDocument] Spreadsheet) 或 Excel XML 檔案格式，處理速度比較快。再輸出成 Excel 檔案格式。
 
=== Excel 匯入 CSV 檔案後，遇到欄位錯置 ===
Excel 開啟 CSV 檔案，遇到欄位錯置
 
原因：
* 如果 CSV 檔案內的欄位值包含換行符號 (Return symbol)，Excel 開啟時會出現錯誤：原本應該同一欄位，卻換行變成第二筆資料。
 
解決方法：
* 使用 [https://zh-tw.libreoffice.org/ LibreOffice] 開啟 CSV 檔案，再轉換成 Excel 檔案。建議再用 Office Excel 開啟檔案確認有沒有問題
 
=== 此文字檔案含有多餘單一工作表所能容納的資料。 ===
Excel 匯入 CSV 檔案，遇到「此文字檔案含有多餘單一工作表所能容納的資料。」
 
原因：
* CSV 檔案資料筆數超出單一工作表所能容納的資料筆數上限：1,048,576 (rows)。<ref>[https://support.microsoft.com/en-us/office/excel-specifications-and-limits-1672b34d-7043-467e-8e27-269d656771c3 Excel specifications and limits - Excel]</ref>
 
解決方法：
* 限制筆數


== 不同試算表方案的工作表或儲存格的大小限制 ==
== 不同試算表方案的工作表或儲存格的大小限制 ==
Line 261: Line 290:




[[Category:Software]]
[[Category: Software]]
[[Category:spreadsheet]] [[Category:Excel]]  [[Category:MySQL]]
[[Category: Spreadsheet]]  
[[Category:Data Science]]
[[Category: Excel]]   
[[Category: MySQL]]
[[Category: Data Science]]

Revision as of 13:56, 14 July 2025

Microsoft Excel (XLS or XLSX) & OpenDocument Spreadsheet (ODS) file limits

不同試算表方案的工作表或儲存格的大小限制

Title File extension Data rows size Length of cell Available solutions
Microsoft Excel Open XML Spreadsheet XLSX Maximum of worksheet size is 1,048,576 rows by 16,384 columns.[1][2][3] (1) Length of formula contents: 8,192 characters
(2) Total number of characters that a cell can contain: 32,767 characters[4]		 Microsoft Excel 2007, EXCEL 2013 and more
Microsoft Excel Spreadsheet XLS Maximum of worksheet size is 65,536 rows by 256 columns.
Icon_exclaim.gif Because first row is occupied by column name, actual data size is 65,535 rows. [5][6]		 Microsoft Excel 2003 and more
OpenDocument Spreadsheet ODS Maximum of worksheet size is 1,048,576 (2^20) rows by 1024 columns for LibreOffice spreadsheet & OpenOffice.org Calc 3 [7][8]. Total number of characters that a cell can contain: 32,767 characters for LibreOffice spreadsheet[9]. LibreOffice(LibreOffice Portable) spreadsheet 3.3.3+ & Apache OpenOffice.org Calc 3+ and more
Google spreadsheet "Up to 2 million cells for spreadsheets that are created in or converted to Google Sheets"
2,000,000 cells = 7,812 rows x 256 columns per sheet.[10]
Zoho Sheet "Up to 1 million cells for a single spreadsheet with a maximum number of 65536 rows and 256 columns per sheet."[11]



Export MySQL query to Excel file

Export MySQL query to Excel file

Export CSV from EXCEL

Excel 檔案，匯出 UTF-8 編碼的 CSV 檔案

軟體 操作方式 輸出 CSV 檔案格式
LibreOffice Calc (LibreOffice Portable) 版本 6.2.4.2 檔案 -> 另存新檔 -> 存檔類型：文字 CSV (.csv) -> 字元集：Unicode (UTF-8) Good.gif UTF-8 編碼 不帶簽名(BOM)
LibreOffice Calc (LibreOffice Portable) 版本 6.2.4.2 檔案 -> 另存新檔 -> 存檔類型：文字 CSV (.csv) -> 字元集：正體中文 (Big5) Big5 編碼
Google 雲端硬碟 檔案 -> 下載 -> 逗號分隔值檔案 (.csv，目前工作表) Good.gif UTF-8 編碼 不帶簽名(BOM)
Microsoft Excel on macOS icon_os_mac.png 版本 16.33 檔案 -> 另存新檔 -> 檔案格式：CSV UTF-8 逗號分隔(.csv) UTF-8 編碼，帶簽名(BOM)
Microsoft Excel on macOS icon_os_mac.png 版本 16.32 檔案 -> 另存新檔 -> 檔案格式：逗號分隔值 (.csv) Big5 編碼
Microsoft Office 2016 on Win Os windows.png 檔案 -> 另存新檔 -> (選擇存檔位置) -> 存檔類型：CSV 逗號分隔 (.csv) Big5 編碼

Import CSV to EXCEL

讓 CSV 可以方便讓 Excel 直接開啟

CSV 檔案 (測試的 Excel 版本: Excel 2016 on Win Os windows.png 、LibreOffice Calc v. 5.4.3.2 on Win Os windows.png 、Excel for macOS icon_os_mac.png 2011 v. 14.7.7)

  • 欄位間隔符號 , 、UTF-8 編碼、有 BOM[12] →
    • Good.gif 使用 Excel on Win Os windows.png 點選兩下檔案直接開啟，不會看到中文變成亂碼，而且不同欄位不會擠到同一個欄位 (儲存格) (測試版本：Excel 2016 on Win Os windows.png )
    • 使用 LibreOffice Calc on Win Os windows.png 直接開啟檔案，會看到「文字匯入」對話視窗，需要調整字元集「Unicode (UTF-8)」，分隔符號則已經勾選「逗號」，即可順利匯入資料
    • 使用 Excel on macOS icon_os_mac.png 直接開啟，會看到中文變成亂碼，需要改用匯入外部資料方式 Excel for (測試版本：macOS icon_os_mac.png 2011 v. 14.7.7、Mac 版 Microsoft Excel 365 版本 16.49)
  • 欄位間隔符號 , 、UTF-8 編碼、沒有 BOM →
    • 使用 Excel on Win Os windows.png 點選兩下檔案直接開啟，會看到中文變成亂碼，需要改用匯入外部資料方式 (測試版本：Excel 2016 on Win Os windows.png )
    • 使用 LibreOffice Calc on Win Os windows.png 直接開啟檔案，會看到「文字匯入」對話視窗，字元集正確偵測是「Unicode (UTF-8)」，分隔符號則已經勾選「逗號」，即可順利匯入資料
    • 使用 Excel on macOS icon_os_mac.png 直接開啟，會看到中文變成亂碼，需要改用匯入外部資料方式 (測試版本：macOS icon_os_mac.png 2011 v. 14.7.7、Mac 版 Microsoft Excel 365 版本 16.49)
  • 欄位間隔符號 TAB 、UTF-8 編碼、有 BOM →
    • 使用 Excel on Win Os windows.png 點選兩下檔案直接開啟，不會看到中文變成亂碼，但是不同欄位擠到同一個欄位 (儲存格) (測試版本：Excel 2016 on Win Os windows.png )
    • 使用 LibreOffice Calc on Win Os windows.png 直接開啟檔案，會看到「文字匯入」對話視窗，需要調整字元集「Unicode (UTF-8)」，分隔符號則已經勾選「定位鍵」，即可順利匯入資料
    • 使用 Excel on macOS icon_os_mac.png 直接開啟，會看到中文變成亂碼，需要改用匯入外部資料方式 (測試版本：macOS icon_os_mac.png 2011 v. 14.7.7)
  • 欄位間隔符號 TAB 、UTF-8 編碼、沒有 BOM (byte-order mark) →
    • 使用 Excel on Win Os windows.png 點選兩下檔案直接開啟，會看到中文變成亂碼，需要改用匯入外部資料方式 (測試版本：Excel 2016 on Win Os windows.png )
    • 使用 LibreOffice Calc on Win Os windows.png 直接開啟檔案，會看到「文字匯入」對話視窗，字元集正確偵測是「Unicode (UTF-8)」，分隔符號則已經勾選「定位鍵」，即可順利匯入資料
    • 使用 Excel on macOS icon_os_mac.png 直接開啟，會看到中文變成亂碼，需要改用匯入外部資料方式 (測試版本：macOS icon_os_mac.png 2011 v. 14.7.7)

相關文章

Import XLS to MySQL

Batch convert TXT/CSV to EXCEL

原始檔案: 欄位值用雙引號框起來(enclosure)、不同欄位資料用定位鍵 Tab 間隔、檔案編碼 UTF8 沒有 BOM 

"column_1" \t "column_2" \t "column_3"

批次轉檔的方案比較

  1. Good.gif $ Advanced CSV Converter v 5.55 ok
    • 轉檔時可以選擇 UTF-8 編碼，輸出的中文 Excel 不會亂碼。
    • 但是不能辨識欄位值前後的雙引號 符號，所以原始檔案需要先去除欄位值前後的雙引號 符號。
    • 試用版只能匯出 50 筆資料。
  2. ConvertXLS v. 8.54 中文亂碼 Icon_exclaim.gif
  3. Bytescout Spreadsheet Tools v. 1.10.0.21 中文亂碼 Icon_exclaim.gif
  4. Google 雲端硬碟 上傳 CSV 檔前，要勾選「轉換上傳檔案 -- 將已上傳的檔案轉換成 Google 文件編輯器格式」。上傳後，再下載會變成 Excel 格式。 [Last visited: 2016-12-30]

Copy & Paste

from spreadsheet application to another spreadsheet application

線上編輯上面表格

  • Microsoft Excel --> Google Spreadsheet:
    • columns & rows: Copy & Paste is ok
  • Plain text --> Google Spreadsheet: ok
    • columns: Tab separated columns --> Google Spreadsheet: ok
    • rows: Enter separated rows --> Google Spreadsheet: ok
  • Plain text --> Microsoft Excel: ok
    • columns: Tab separated columns --> Microsoft Excel: ok
    • rows: Enter separated rows --> Microsoft Excel: ok
  • Microsoft Excel --> Table of Google Document:
    • columns & rows: Copy & Paste is ok
  • Plain text --> Table of Google Document: fail Icon_exclaim.gif Suggest you copy to Microsoft Excel first and then copy paste to Google Document from Microsoft Excel.
    • columns: Tab separated columns --> Table of Google Document: fail
    • rows: Enter separated rows --> Table of Google Document: fail
  • Plain text --> Microsoft Excel: ok
    • columns: Tab separated columns --> Microsoft Excel: ok
    • rows: Enter separated rows --> Microsoft Excel: ok
  • How-to Create and Copy a Table in Google Mail (Gmail) from Excel - YouTube


further reading

from spreadsheet application to editor application

  • Copy the table from Gmail/HTML ...
    • Paste to MS Excel: the border line of table is missing Icon_exclaim.gif
    • Paste to MS Word: the border line of table is exists after posted
  • Copy the table from MS Excel ...
    • Paste to Gmail: the border line of table is missing Icon_exclaim.gif
    • Paste to MS Word: the border line of table is exists after posted
  • Copy the table from MS Word ...
    • Paste to Gmail: the border line of table is exists after posted
    • Paste to MS Word: the border line of table is exists after posted

Excel online viewer

Google drive [Last visited: 2020-03-10]

  • file limit: "Up to 5 million cells or 18,278 columns (column ZZZ) for spreadsheets that are created in or converted to Google Sheets."[13]

Microsoft OneDrive

Online Spreadsheet Maker | Create Spreadsheets for free - Zoho Sheet

  • file limit: "Max number of cells with data = 1 million/workbook"[15]

Google spreadsheet

利用Google spreadsheet的函式，分析及統計可複選的問卷題目結果。

Excel 「樞紐分析表(Pivot Tables)」

區塊「Σ 值」的「計數 - 欄位名稱」(該欄位的項目個數)，除非是沒有填入任何資料的「空值」，否則都會被計算加 1。包含

  • 邏輯值 TRUE、FALSE、字串、數值、錯誤代碼 #NAME? 等資料，會被列入計算加 1
  • IF 回傳的空值，也會被列入計算加 1 Icon_exclaim.gif version: Excel 2013

Checking of data type

儲存格資料類型檢查與類型轉換

數字

時間

時間: 年份

  • 預期: 可以「從最舊到最新排序」、「從最新到最舊排序」。
  • 異常: 如果是「從 A 到 Z 排序」、「從 Z 到 A 排序」，需要將通用格式的年份，改成時間格式的年份:
    • 法1: 將 2010 改成 2010/01/01 ，再利用 YEAR 函數擷取出年份
    • 法2: 將 2010 改成 2010/01/01 ，再利用 Tableau$ 轉換成年份 Icon_exclaim.gif walk around approach!

Troubleshooting of Excel errors

Excel 效能議題

  • 資料筆數大量時的替代方案
    • 如果 Excel 資料筆數約百萬筆，操作速度約耗費數小時，可改用資料庫。資料庫資料處理後，再輸出成 Excel 檔，而不要在 Excel 檔上面進行資料處理或換算。
    • 如果要刪除符合特定條件約一萬筆的資料列太慢時。改成將篩選出符合另一條件的資料列，再複製貼上到新的工作表，會比較快。如果直接複製貼上也花超過5分鐘時間，可以選擇直接貼上值。相關文章：解決 Excel 刪除資料太慢的問題
  • 降低資料處理複雜度
    • 全選工作表儲存格資料，複製後，選擇性貼上值到空白工作表。因為移除公式，所以另一工作表的操作速度會加快。 Icon_exclaim.gif 需要注意時間格式的數值會跑掉，變成一長串數字，需要額外設定儲存格格式成時間格式。
    • 多重篩選條件會使用比較多的系統資源，在不使用篩選條件下，改成使用函數處理，會比較快。
  • 電腦效能
  • 切換使用不同軟體
    • 先使用 LibreOffice Calc 操作 ODS (OpenDocument Spreadsheet) 或 Excel XML 檔案格式，處理速度比較快。再輸出成 Excel 檔案格式。

Excel 匯入 CSV 檔案後，遇到欄位錯置

Excel 開啟 CSV 檔案，遇到欄位錯置

原因：

  • 如果 CSV 檔案內的欄位值包含換行符號 (Return symbol)，Excel 開啟時會出現錯誤：原本應該同一欄位，卻換行變成第二筆資料。

解決方法：

  • 使用 LibreOffice 開啟 CSV 檔案，再轉換成 Excel 檔案。建議再用 Office Excel 開啟檔案確認有沒有問題

此文字檔案含有多餘單一工作表所能容納的資料。

Excel 匯入 CSV 檔案，遇到「此文字檔案含有多餘單一工作表所能容納的資料。」

原因：

  • CSV 檔案資料筆數超出單一工作表所能容納的資料筆數上限：1,048,576 (rows)。[16]

解決方法：

  • 限制筆數

不同試算表方案的工作表或儲存格的大小限制

Microsoft Excel (XLS or XLSX) & OpenDocument Spreadsheet (ODS) file limits

Merge data from different files

Merge multiple CSV / Text files by using Windows command (命令提示字元)[17]

  • copy *.csv bundle.csv for different CSV files
  • copy *.txt bundle.txt for different Text files

Merge Excel worksheets (copy data from multiple worksheets into one workbook)

PHP libraby

Further reading

References

  1. ↑ Excel specifications and limits
  2. ↑ Excel 的規格及限制 工作表大小
  3. ↑ Excel specifications and limits - Excel
  4. ↑ Err: 解決 Navicat 「存放裝置空間不足，無法完成此操作」問題訊息
  5. ↑ What is the maximum allowed rows in a Microsoft Excel .xls or .xlsx - Super User
  6. ↑ Worksheet compatibility issues - Excel.
  7. ↑ Frequently asked questions - Calc - The Document Foundation Wiki
  8. ↑ OpenOffice Calc - Simple English Wikipedia, the free encyclopedia
  9. ↑ Frequently asked questions - Calc - The Document Foundation Wiki
  10. ↑ Google 文件、試算表和簡報的檔案大小限制 - 雲端硬碟說明 Files you can store in Google Drive - Google Drive Help
  11. ↑ Zoho Docs - FAQ on Online Spreadsheet
  12. ↑ utf 8 - Adding BOM to UTF-8 files - Stack Overflow
  13. ↑ Files you can store in Google Drive - Google Drive Help
  14. ↑ 支援 - Office.com
  15. ↑ Limits of Sheets
  16. ↑ Excel specifications and limits - Excel
  17. ↑ 命令提示字元：常見問題集 - Windows 說明


Troubleshooting of ...

Template

Retrieved from "https://wiki.planetoid.info/index.php?title=Microsoft_Excel&oldid=25822"