Microsoft Excel: Difference between revisions

From LemonWiki共筆
Jump to navigation Jump to search
No edit summary
 
(32 intermediate revisions by the same user not shown)
Line 1: Line 1:
== Microsoft Excel (XLS or XLSX) & OpenDocument Spreadsheet (ODS) file limits ==
不同試算表方案的工作表或儲存格的大小限制
<table border="1">
<tr>
  <th>Title</th>
  <th>File extension</th>
  <th>Data rows size</th>
  <th>Length of cell</th>
  <th>Available solutions</th>
</tr>
<tr>
  <td>Microsoft Excel Open XML Spreadsheet</td>
  <td>XLSX</td>
  <td>Maximum of worksheet size is 1,048,576 rows by 16,384 columns.<ref>[https://support.office.com/en-us/article/Excel-specifications-and-limits-1672b34d-7043-467e-8e27-269d656771c3?ui=en-US&rs=en-US&ad=US&fromAR=1 Excel specifications and limits]</ref><ref>[http://office.microsoft.com/zh-tw/excel-help/HP010073849.aspx Excel 的規格及限制] 工作表大小</ref><ref>[http://office.microsoft.com/en-us/excel-help/excel-specifications-and-limits-HA103980614.aspx Excel specifications and limits - Excel]</ref> </td>
  <td>(1) Length of formula contents: 8,192 characters <br />(2) Total number of characters that a cell can contain: 32,767 characters<ref>[https://errerrors.blogspot.com/2016/02/navicat.html Err: 解決 Navicat 「存放裝置空間不足，無法完成此操作」問題訊息]</ref></td>
  <td>Microsoft Excel 2007, EXCEL 2013 and [https://fileinfo.com/extension/xlsx more]</td>
</tr>
<tr>
  <td>Microsoft Excel Spreadsheet</td>
  <td>XLS</td>
  <td>Maximum of worksheet size is 65,536 rows by 256 columns. <br />{{exclaim}} Because first row is occupied by column name, actual data size is 65,535 rows. <ref>[http://superuser.com/questions/366468/what-is-the-maximum-allowed-rows-in-a-microsoft-excel-xls-or-xlsx What is the maximum allowed rows in a Microsoft Excel .xls or .xlsx - Super User]</ref><ref>[https://support.office.com/en-us/article/Worksheet-compatibility-issues-f9c80c5b-5afc-40da-a841-b888746abd40 Worksheet compatibility issues - Excel].</ref></td>
  <td></td>
  <td>Microsoft Excel 2003 and [https://fileinfo.com/extension/xls more]</td>
</tr>
<tr>
  <td>OpenDocument Spreadsheet</td>
  <td>ODS</td>
  <td>Maximum of worksheet size is 1,048,576 (2^20) rows by 1024 columns for LibreOffice spreadsheet & OpenOffice.org Calc 3 <ref>[https://wiki.documentfoundation.org/Faq/Calc/022 Frequently asked questions - Calc - The Document Foundation Wiki]</ref><ref>[https://simple.wikipedia.org/wiki/OpenOffice_Calc OpenOffice Calc - Simple English Wikipedia, the free encyclopedia]</ref>. </td>
  <td>Total number of characters that a cell can contain: 32,767 characters for LibreOffice spreadsheet<ref>[https://wiki.documentfoundation.org/Faq/Calc/022 Frequently asked questions - Calc - The Document Foundation Wiki]</ref>.</td>
  <td>[http://www.libreoffice.org/ LibreOffice]([http://portableapps.com/apps/office/libreoffice_portable/ LibreOffice Portable])  spreadsheet 3.3.3+ & Apache [https://www.openoffice.org/ OpenOffice.org] Calc 3+ and [https://fileinfo.com/extension/ods more]</td>
</tr>
<tr>
  <td>[https://www.google.com/sheets/about/ Google spreadsheet]</td>
  <td></td>
  <td>"Up to 2 million cells for spreadsheets that are created in or converted to Google Sheets" <br />2,000,000 cells = 7,812 rows x 256 columns per sheet.<ref>[https://support.google.com/drive/answer/37603?hl=zh-Hant Google 文件、試算表和簡報的檔案大小限制 - 雲端硬碟說明] [https://support.google.com/drive/answer/37603?hl=en Files you can store in Google Drive - Google Drive Help]</ref></td>
  <td></td>
  <td></td>
</tr>
<tr>
  <td>[https://www.zoho.com/docs/sheet.html Zoho Sheet]
  <td></td>
  <td>"Up to 1 million cells for a single spreadsheet with a maximum number of 65536 rows and 256 columns per sheet."<ref>[https://www.zoho.com/docs/faq/online-spreadsheet.html Zoho Docs - FAQ on Online Spreadsheet]</ref></td>
  <td></td>
  <td></td>
</tr>
</table>
* Office 365: [http://office.microsoft.com/zh-tw/excel-help/HA102837464.aspx 資料模型的規格與限制 - Excel - Office.com] 適用: Excel 2013, Power BI for Office 365 Preview, Power Pivot in Excel 2013


== Export MySQL query to Excel file ==
== Export MySQL query to Excel file ==
Line 4: Line 54:


== Export CSV from EXCEL ==
== Export CSV from EXCEL ==
建議使用 [https://zh-tw.libreoffice.org/ LibreOffice] Calc ([http://portableapps.com/apps/office/libreoffice_portable LibreOffice Portable]) 開啟 Excel 檔案，匯出 UTF-8 編碼的 CSV 檔案
Excel 檔案，匯出 UTF-8 編碼的 CSV 檔案
* 雖然 Excel 本身也有提供匯出 「Unicode 文字 (*.txt)」，但是因為無法設定使用雙引號框住欄位值。會造成後續匯入檔案容易出問題。
* 雖然 Excel 本身也有提供匯出 「Unicode 文字 (*.txt)」，但是因為無法設定使用雙引號框住欄位值。會造成後續匯入檔案容易出問題。
* Excel 本身提供匯出「CSV (逗號分隔) (*.csv)」是 Big5/ANSI 編碼，遇到 Unicode 文字會變成問號。
* [https://errerrors.blogspot.com/2018/12/how-to-fix-scientific-notation-on-excel-or-csv.html 解決 Excel 轉 CSV 檔案的數字變為科學符號的問題]
 
<table border="1" class="wikitable sortable">
<tr>
<th>軟體</th>
<th>操作方式</th>
<th>輸出 CSV 檔案格式</th>
</tr>
<tr>
<td>[https://zh-tw.libreoffice.org/ LibreOffice] Calc ([http://portableapps.com/apps/office/libreoffice_portable LibreOffice Portable]) 版本 6.2.4.2</td>
<td>檔案 -> 另存新檔 -> 存檔類型：文字 CSV (.csv) -> 字元集：Unicode (UTF-8)</td>
<td>{{Gd}} UTF-8 編碼 不帶[[Byte order mark |簽名(BOM)]]</td>
</tr>
<tr>
<td>[https://zh-tw.libreoffice.org/ LibreOffice] Calc ([http://portableapps.com/apps/office/libreoffice_portable LibreOffice Portable]) 版本 6.2.4.2</td>
<td>檔案 -> 另存新檔 -> 存檔類型：文字 CSV (.csv) -> 字元集：正體中文 (Big5)</td>
<td>Big5 編碼</td>
</tr>
<td>Google 雲端硬碟</td>
<td>檔案 -> 下載 -> 逗號分隔值檔案 (.csv，目前工作表)</td>
<td>{{Gd}} UTF-8 編碼 不帶[[Byte order mark |簽名(BOM)]]</td>
</tr>
<tr>
<td>Microsoft Excel on {{Mac}} 版本 16.33</td>
<td>檔案 -> 另存新檔 -> 檔案格式：CSV UTF-8 逗號分隔(.csv)</td>
<td>UTF-8 編碼，帶[[Byte order mark |簽名(BOM)]]</td>
</tr>
<tr>
<td>Microsoft Excel on {{Mac}} 版本 16.32</td>
<td>檔案 -> 另存新檔 -> 檔案格式：逗號分隔值 (.csv)</td>
<td>Big5 編碼</td>
</tr>
<tr>
<td>Microsoft Office 2016 on {{Win}}</td>
<td>檔案 -> 另存新檔 -> (選擇存檔位置) -> 存檔類型：CSV 逗號分隔 (.csv)</td>
<td>Big5 編碼</td>
</tr>
<tr>
</table>


== Import CSV to EXCEL ==
== Import CSV to EXCEL ==
* [http://office.microsoft.com/zh-tw/excel-help/HP010099725.aspx 匯入或匯出文字檔案 - Excel] 支援 Unicode 編碼 {{Win}} 圖形教學: [https://www.managertoday.com.tw/articles/view/55615 Excel打開是亂碼？教你換一個「開啟方式」，瞬間找回所有資料｜經理人]
* [http://office.microsoft.com/zh-tw/excel-help/HP010099725.aspx 匯入或匯出文字檔案 - Excel] 支援 Unicode 編碼 {{Win}} 圖形教學: [https://www.managertoday.com.tw/articles/view/55615 Excel打開是亂碼？教你換一個「開啟方式」，瞬間找回所有資料｜經理人]
* 欄位內容分隔符號 " (雙引號); 欄位分隔符號 , 不可夾雜空白。Unicode 檔案需要檔案有 BOM，這樣才能點選兩下 CSV，Excel 開啟時沒有問題。 (Excel 2007) {{Win}}
* 欄位內容分隔符號 " (雙引號); 欄位分隔符號 , 不可夾雜空白。Unicode 檔案需要檔案有 BOM，這樣才能點選兩下 CSV，Excel 開啟時沒有問題。 (Excel 2007) {{Win}}
* [http://errerrors.blogspot.tw/2010/10/csv-microsoft-excel-2007-openofficeorg.html 解決CSV檔亂碼 (Microsoft Excel 2007 或 OpenOffice.org 3.2)] {{Win}}  
* [http://errerrors.blogspot.com/2010/10/csv-microsoft-excel-2007-openofficeorg.html 解決CSV檔亂碼 (Microsoft Excel 2007 或 OpenOffice.org 3.2)] {{Win}}
* [http://errerrors.blogspot.tw/2014/05/mac-office-excel-csv-microsoft-excel.html Mac Office Excel 匯入中文 CSV 檔案 (Microsoft Excel for Mac 2011)] {{Mac}}
* [https://errerrors.blogspot.com/2019/01/fix-garbled-text-after-import-csv-on-mac-excel.html 解決 Mac 系統匯入中文 CSV 檔案遇到亂碼問題 (Excel on Mac 版本 16.20, Office 365)] {{Mac}}
* [http://errerrors.blogspot.com/2014/05/mac-office-excel-csv-microsoft-excel.html Mac Office Excel 匯入中文 CSV 檔案 (Microsoft Excel for Mac 2011)] {{Mac}}
* [https://errerrors.blogspot.com/2018/10/import-csv-file-on-mac-libreoffice.html 解決 MAC 電腦匯入中文 CSV 檔案遇到亂碼問題 (LibreOffice v. 6.1.2.1)] {{Mac}}
* 雖然 CSV 欄位已經用雙引號區隔，但是如果欄位裡面也有包含雙引號，匯入到 Excel，會導致欄位錯誤的問題。解法1: 原始資料的雙引號改成單引號、解法2: 不用 Excel ，改用 [http://www.libreoffice.org/discover/calc/ LibreOffice Calc]
* 雖然 CSV 欄位已經用雙引號區隔，但是如果欄位裡面也有包含雙引號，匯入到 Excel，會導致欄位錯誤的問題。解法1: 原始資料的雙引號改成單引號、解法2: 不用 Excel ，改用 [http://www.libreoffice.org/discover/calc/ LibreOffice Calc]


=== 讓 CSV 可以方便讓 Excel 直接開啟 ===
=== 讓 CSV 可以方便讓 Excel 直接開啟 ===
CSV 檔案 (測試的 Excel 版本: Excel 2015 on {{Win}}、LibreOffice Calc  v. 5.4.3.2 on {{Win}}、Excel for {{Mac}} 2011 v. 14.7.7)
CSV 檔案 (測試的 Excel 版本: Excel 2016 on {{Win}}、LibreOffice Calc  v. 5.4.3.2 on {{Win}}、Excel for {{Mac}} 2011 v. 14.7.7)
* 欄位間隔符號 {{kbd | key = ,}} 、UTF-8 編碼、'''有''' BOM<ref>[https://stackoverflow.com/questions/3127436/adding-bom-to-utf-8-files utf 8 - Adding BOM to UTF-8 files - Stack Overflow]</ref> →  
* 欄位間隔符號 {{kbd | key = ,}} 、UTF-8 編碼、'''有''' BOM<ref>[https://stackoverflow.com/questions/3127436/adding-bom-to-utf-8-files utf 8 - Adding BOM to UTF-8 files - Stack Overflow]</ref> →  
** {{Gd}} 使用 Excel on {{Win}} 點選兩下檔案直接開啟，不會看到中文變成亂碼，而且不同欄位不會擠到同一個欄位 (儲存格)
** {{Gd}} 使用 Excel on {{Win}} 點選兩下檔案直接開啟，不會看到中文變成亂碼，而且不同欄位不會擠到同一個欄位 (儲存格) (測試版本：Excel 2016 on {{Win}})
** 使用 LibreOffice Calc on {{Win}} 直接開啟檔案，會看到「文字匯入」對話視窗，需要調整字元集「Unicode (UTF-8)」，分隔符號則已經勾選「逗號」，即可順利匯入資料
** 使用 LibreOffice Calc on {{Win}} 直接開啟檔案，會看到「文字匯入」對話視窗，需要調整字元集「Unicode (UTF-8)」，分隔符號則已經勾選「逗號」，即可順利匯入資料
** 使用 Excel on {{Mac}} 直接開啟，會看到中文變成亂碼，需要改用匯入外部資料方式
** 使用 Excel on {{Mac}} 直接開啟，會看到中文變成亂碼，需要改用匯入外部資料方式 Excel for (測試版本：{{Mac}} 2011 v. 14.7.7、Mac 版 Microsoft Excel 365 版本 16.49)


* 欄位間隔符號 {{kbd | key = ,}} 、UTF-8 編碼、沒有 BOM →  
* 欄位間隔符號 {{kbd | key = ,}} 、UTF-8 編碼、沒有 BOM →  
** 使用 Excel on {{Win}} 點選兩下檔案直接開啟，會看到中文變成亂碼，需要改用匯入外部資料方式
** 使用 Excel on {{Win}} 點選兩下檔案直接開啟，會看到中文變成亂碼，需要改用匯入外部資料方式 (測試版本：Excel 2016 on {{Win}})
** 使用 LibreOffice Calc on {{Win}} 直接開啟檔案，會看到「文字匯入」對話視窗，字元集正確偵測是「Unicode (UTF-8)」，分隔符號則已經勾選「逗號」，即可順利匯入資料
** 使用 LibreOffice Calc on {{Win}} 直接開啟檔案，會看到「文字匯入」對話視窗，字元集正確偵測是「Unicode (UTF-8)」，分隔符號則已經勾選「逗號」，即可順利匯入資料
** 使用 Excel on {{Mac}} 直接開啟，會看到中文變成亂碼，需要改用匯入外部資料方式
** 使用 Excel on {{Mac}} 直接開啟，會看到中文變成亂碼，需要改用匯入外部資料方式 (測試版本：{{Mac}} 2011 v. 14.7.7、Mac 版 Microsoft Excel 365 版本 16.49)


* 欄位間隔符號 {{kbd | key = TAB}} 、UTF-8 編碼、'''有''' BOM →  
* 欄位間隔符號 {{kbd | key = TAB}} 、UTF-8 編碼、'''有''' BOM →  
** 使用 Excel on {{Win}} 點選兩下檔案直接開啟，不會看到中文變成亂碼，但是不同欄位擠到同一個欄位 (儲存格)
** 使用 Excel on {{Win}} 點選兩下檔案直接開啟，不會看到中文變成亂碼，但是不同欄位擠到同一個欄位 (儲存格) (測試版本：Excel 2016 on {{Win}})
** 使用 LibreOffice Calc on {{Win}} 直接開啟檔案，會看到「文字匯入」對話視窗，需要調整字元集「Unicode (UTF-8)」，分隔符號則已經勾選「定位鍵」，即可順利匯入資料
** 使用 LibreOffice Calc on {{Win}} 直接開啟檔案，會看到「文字匯入」對話視窗，需要調整字元集「Unicode (UTF-8)」，分隔符號則已經勾選「定位鍵」，即可順利匯入資料
** 使用 Excel on {{Mac}} 直接開啟，會看到中文變成亂碼，需要改用匯入外部資料方式
** 使用 Excel on {{Mac}} 直接開啟，會看到中文變成亂碼，需要改用匯入外部資料方式 (測試版本：{{Mac}} 2011 v. 14.7.7)


* 欄位間隔符號 {{kbd | key = TAB}} 、UTF-8 編碼、沒有 [https://zh.wikipedia.org/wiki/%E4%BD%8D%E5%85%83%E7%B5%84%E9%A0%86%E5%BA%8F%E8%A8%98%E8%99%9F BOM] (byte-order mark) →  
* 欄位間隔符號 {{kbd | key = TAB}} 、UTF-8 編碼、沒有 [https://zh.wikipedia.org/wiki/%E4%BD%8D%E5%85%83%E7%B5%84%E9%A0%86%E5%BA%8F%E8%A8%98%E8%99%9F BOM] (byte-order mark) →  
** 使用 Excel on {{Win}} 點選兩下檔案直接開啟，會看到中文變成亂碼，需要改用匯入外部資料方式
** 使用 Excel on {{Win}} 點選兩下檔案直接開啟，會看到中文變成亂碼，需要改用匯入外部資料方式 (測試版本：Excel 2016 on {{Win}})
** 使用 LibreOffice Calc on {{Win}} 直接開啟檔案，會看到「文字匯入」對話視窗，字元集正確偵測是「Unicode (UTF-8)」，分隔符號則已經勾選「定位鍵」，即可順利匯入資料
** 使用 LibreOffice Calc on {{Win}} 直接開啟檔案，會看到「文字匯入」對話視窗，字元集正確偵測是「Unicode (UTF-8)」，分隔符號則已經勾選「定位鍵」，即可順利匯入資料
** 使用 Excel on {{Mac}} 直接開啟，會看到中文變成亂碼，需要改用匯入外部資料方式
** 使用 Excel on {{Mac}} 直接開啟，會看到中文變成亂碼，需要改用匯入外部資料方式 (測試版本：{{Mac}} 2011 v. 14.7.7)
 
相關文章
* [https://errerrors.blogspot.com/2021/06/how-to-fix-garbled-text-in-csv-file-opened-by-excel.html 不要把 CSV 轉成 Big5 編碼，怎樣正確解決 Excel 開 CSV 檔案遇到亂碼]


== Import XLS to MySQL ==
== Import XLS to MySQL ==
Line 99: Line 192:
** Paste to Gmail: the border line of table is exists after posted
** 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
** Paste to MS Word: the border line of table is exists after posted
== Excel online viewer ==
[https://www.google.com/drive/ Google drive] {{access | date=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."<ref>[https://support.google.com/drive/answer/37603?hl=en Files you can store in Google Drive - Google Drive Help]</ref>
[https://onedrive.live.com/about/zh-tw/ Microsoft OneDrive]
* file limit: 5MB<ref>[https://support.office.com/zh-TW/client/results?Shownav=true&lcid=1028&ns=XLWAEndUser&version=15&omkt=zh-TW&ver=15&apps=WDWAENDUSER%2CXLWAENDUSER%2CPPWAENDUSER%2CONWAENDUSER&HelpID=BIGWORKBOOK 支援 - Office.com]</ref>
[https://www.zoho.com/sheet/ Online Spreadsheet Maker | Create Spreadsheets for free - Zoho Sheet]
* file limit: "Max number of cells with data = 1 million/workbook"<ref>[https://help.zoho.com/portal/en/community/topic/limits-of-sheets Limits of Sheets]</ref>


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


== Excel 「樞紐分析表(Pivot Tables)」==
== Excel 「樞紐分析表(Pivot Tables)」==
Line 110: Line 211:
* {{kbd | key =<nowiki>IF</nowiki>}} 回傳的空值，也會被列入計算加 1 {{exclaim}} version: Excel 2013
* {{kbd | key =<nowiki>IF</nowiki>}} 回傳的空值，也會被列入計算加 1 {{exclaim}} version: Excel 2013


== Checking of data type 儲存格資料類型檢查與類型轉換 ==
== Checking of data type ==
儲存格資料類型檢查與類型轉換
 
數字
數字
* 預期: 可以「從最小到最大排序」、「從最大到最小排序」
* 預期: 可以「從最小到最大排序」、「從最大到最小排序」
Line 116: Line 219:
** 法1: [https://support.office.com/zh-tw/article/%E5%B0%87%E5%84%B2%E5%AD%98%E6%88%90%E6%96%87%E5%AD%97%E7%9A%84%E6%95%B8%E5%80%BC%E8%BD%89%E6%8F%9B%E6%88%90%E6%95%B8%E5%80%BC%E6%A0%BC%E5%BC%8F-40105f2a-fe79-4477-a171-c5bad0f0a885 將儲存成文字的數值轉換成數值格式 - Office 支援] {{access | date = 2018-11-05}}  
** 法1: [https://support.office.com/zh-tw/article/%E5%B0%87%E5%84%B2%E5%AD%98%E6%88%90%E6%96%87%E5%AD%97%E7%9A%84%E6%95%B8%E5%80%BC%E8%BD%89%E6%8F%9B%E6%88%90%E6%95%B8%E5%80%BC%E6%A0%BC%E5%BC%8F-40105f2a-fe79-4477-a171-c5bad0f0a885 將儲存成文字的數值轉換成數值格式 - Office 支援] {{access | date = 2018-11-05}}  


年份
時間
* 預期: 可以「從最舊到最新排序」、「從最新到最舊排序」。
* 預期: 可以「從最舊到最新排序」、「從最新到最舊排序」。
* 異常: 如果是「從 A 到 Z 排序」、「從 Z 到 A 排序」，需要將通用格式的年份，改成時間格式的年份:  
* 異常: 如果是「從 A 到 Z 排序」、「從 Z 到 A 排序」，需要將通用格式的年份，改成時間格式的年份:  
** 法1: [https://errerrors.blogspot.com/2016/12/excel-value-text.html?q=excel Excel 如何轉換文字格式的日期時間格式 (使用 VALUE, TEXT 函數) | Err]
** 法1: [https://errerrors.blogspot.com/2016/12/excel-value-text.html?q=excel Excel 如何轉換文字格式的日期時間格式 (使用 VALUE, TEXT 函數) | Err]
** 法2: 將 2010 改成 2010/01/01 ，再利用 YEAR 函數擷取出年份
 
** 法3: 將 2010 改成 2010/01/01 ，再利用 [http://www.tableau.com/ Tableau]''$'' 轉換成年份 {{exclaim}} walk around approach!
時間: 年份
* 預期: 可以「從最舊到最新排序」、「從最新到最舊排序」。
* 異常: 如果是「從 A 到 Z 排序」、「從 Z 到 A 排序」，需要將通用格式的年份，改成時間格式的年份:
** 法1: 將 2010 改成 2010/01/01 ，再利用 YEAR 函數擷取出年份
** 法2: 將 2010 改成 2010/01/01 ，再利用 [http://www.tableau.com/ Tableau]''$'' 轉換成年份 {{exclaim}} walk around approach!


== Troubleshooting of Excel errors ==
== Troubleshooting of Excel errors ==
=== Excel 效能議題 ===
[[Troubleshooting of common Excel errors in Mandarin]]
* 資料筆數大量時的替代方案
** 如果 Excel 資料筆數約百萬筆，操作速度約耗費數小時，可改用資料庫。資料庫資料處理後，再輸出成 Excel 檔，而不要在 Excel 檔上面進行資料處理或換算。
** 如果要刪除符合特定條件約一萬筆的資料列太慢時。改成將篩選出符合另一條件的資料列，再複製貼上到新的工作表，會比較快。如果直接複製貼上也花超過5分鐘時間，可以選擇直接貼上值。
* 降低資料處理複雜度
** 全選工作表儲存格資料，複製後，選擇性貼上值到空白工作表。因為移除公式，所以另一工作表的操作速度會加快。 {{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 檔案，節省電腦效能


== 不同試算表方案的工作表或儲存格的大小限制 ==
== 不同試算表方案的工作表或儲存格的大小限制 ==
Line 145: Line 243:
Merge Excel worksheets (copy data from multiple worksheets into one workbook)
Merge Excel worksheets (copy data from multiple worksheets into one workbook)
* ''$'' [https://www.ablebits.com/consolidate-worksheets-excel/index.php Merge multiple Excel worksheets into 1 - Consolidate Worksheets Wizard add-in]
* ''$'' [https://www.ablebits.com/consolidate-worksheets-excel/index.php Merge multiple Excel worksheets into 1 - Consolidate Worksheets Wizard add-in]
* ''$'' [https://www.extendoffice.com/product/kutools-for-excel.html Kutools - Combines More Than 300 Advanced Functions and Tools for Microsoft Excel]
* [https://www.ptt.cc/bbs/EZsoft/M.1632673165.A.31E.html ExcelMerger 幫你合併 Excel 檔 - 看板 EZsoft - 批踢踢實業坊]
== PHP libraby ==
* [https://github.com/PHPOffice/PhpSpreadsheet PHPOffice/PhpSpreadsheet: A pure PHP library for reading and writing spreadsheet files]
* [https://github.com/rap2hpoutre/fast-excel rap2hpoutre/fast-excel: 🦉 Fast Excel import/export for Laravel]
* [https://github.com/Maatwebsite/Laravel-Excel Maatwebsite/Laravel-Excel: 🚀 Supercharged Excel exports and imports in Laravel]


== 相關新聞聯播 ==
== Further reading ==


{{News feed | title = Excel OR Spreadsheet OR 資料科學 相關新聞聯播 | feed = <rss>https://news.google.com/news/rss/search/section/q/Excel%20OR%20Spreadsheet%20OR%20%E8%B3%87%E6%96%99%E7%A7%91%E5%AD%B8/Excel%20OR%20Spreadsheet%20OR%20%E8%B3%87%E6%96%99%E7%A7%91%E5%AD%B8?hl=zh-TW&gl=TW&ned=tw</rss>
* [https://tools.ietf.org/html/rfc4180 RFC 4180 - Common Format and MIME Type for Comma-Separated Values (CSV) Files]
}}


== references ==
== References ==
<references/>
<references/>


{{Template:Troubleshooting}}
{{Template:Data factory flow}}
 


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

Latest revision as of 11:32, 15 July 2025

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

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

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[edit]

Export MySQL query to Excel file

Export CSV from EXCEL[edit]

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   檔案 -> 另存新檔 -> (選擇存檔位置) -> 存檔類型：CSV 逗號分隔 (.csv) Big5 編碼

Import CSV to EXCEL[edit]

讓 CSV 可以方便讓 Excel 直接開啟[edit]

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

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

相關文章

Import XLS to MySQL[edit]

Batch convert TXT/CSV to EXCEL[edit]

原始檔案: 欄位值用雙引號框起來(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[edit]

from spreadsheet application to another spreadsheet application[edit]

線上編輯上面表格

  • 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[edit]

  • 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[edit]

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[edit]

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

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

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

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

Checking of data type[edit]

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

數字

時間

時間: 年份

  • 預期: 可以「從最舊到最新排序」、「從最新到最舊排序」。
  • 異常: 如果是「從 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[edit]

Troubleshooting of common Excel errors in Mandarin

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

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

Merge data from different files[edit]

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

  • 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[edit]

Further reading[edit]

References[edit]

  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. ↑ 命令提示字元：常見問題集 - Windows 說明

Data factory flow

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