Microsoft Excel: Difference between revisions

From LemonWiki共筆
Jump to navigation Jump to search
No edit summary
Tags: Mobile edit Mobile web edit
 
(40 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}}
* [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 版本: 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> →
** {{Gd}} 使用 Excel on {{Win}} 點選兩下檔案直接開啟,不會看到中文變成亂碼,而且不同欄位不會擠到同一個欄位 (儲存格) (測試版本:Excel 2016 on {{Win}})
** 使用 LibreOffice Calc on {{Win}} 直接開啟檔案,會看到「文字匯入」對話視窗,需要調整字元集「Unicode (UTF-8)」,分隔符號則已經勾選「逗號」,即可順利匯入資料
** 使用 Excel on {{Mac}} 直接開啟,會看到中文變成亂碼,需要改用匯入外部資料方式 Excel for (測試版本:{{Mac}} 2011 v. 14.7.7、Mac 版 Microsoft Excel 365 版本 16.49)
* 欄位間隔符號 {{kbd | key = ,}} 、UTF-8 編碼、沒有 BOM →
** 使用 Excel on {{Win}} 點選兩下檔案直接開啟,會看到中文變成亂碼,需要改用匯入外部資料方式 (測試版本:Excel 2016 on {{Win}})
** 使用 LibreOffice Calc on {{Win}} 直接開啟檔案,會看到「文字匯入」對話視窗,字元集正確偵測是「Unicode (UTF-8)」,分隔符號則已經勾選「逗號」,即可順利匯入資料
** 使用 Excel on {{Mac}} 直接開啟,會看到中文變成亂碼,需要改用匯入外部資料方式 (測試版本:{{Mac}} 2011 v. 14.7.7、Mac 版 Microsoft Excel 365 版本 16.49)
* 欄位間隔符號 {{kbd | key = TAB}} 、UTF-8 編碼、'''有''' BOM →
** 使用 Excel on {{Win}} 點選兩下檔案直接開啟,不會看到中文變成亂碼,但是不同欄位擠到同一個欄位 (儲存格) (測試版本:Excel 2016 on {{Win}})
** 使用 LibreOffice Calc on {{Win}} 直接開啟檔案,會看到「文字匯入」對話視窗,需要調整字元集「Unicode (UTF-8)」,分隔符號則已經勾選「定位鍵」,即可順利匯入資料
** 使用 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) →
** 使用 Excel on {{Win}} 點選兩下檔案直接開啟,會看到中文變成亂碼,需要改用匯入外部資料方式 (測試版本:Excel 2016 on {{Win}})
** 使用 LibreOffice Calc on {{Win}} 直接開啟檔案,會看到「文字匯入」對話視窗,字元集正確偵測是「Unicode (UTF-8)」,分隔符號則已經勾選「定位鍵」,即可順利匯入資料
** 使用 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 77: 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 88: Line 211:
* {{kbd | key =<nowiki>IF</nowiki>}} 回傳的空值,也會被列入計算加 1 {{exclaim}} version: Excel 2013
* {{kbd | key =<nowiki>IF</nowiki>}} 回傳的空值,也會被列入計算加 1 {{exclaim}} version: Excel 2013


== Excel 儲存格格式轉換 ==
== Checking of data type ==
* 通用格式的年份,改成時間格式的年份:  
儲存格資料類型檢查與類型轉換
 
數字
* 預期: 可以「從最小到最大排序」、「從最大到最小排序」
* 異常: 如果是「從 A 到 Z 排序」、「從 Z 到 A 排序」,需要將通用格式,改成數值格式的年份:
** 法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 排序」,需要將通用格式的年份,改成時間格式的年份:
** 法1: [https://errerrors.blogspot.com/2016/12/excel-value-text.html?q=excel Excel 如何轉換文字格式的日期時間格式 (使用 VALUE, TEXT 函數) | Err]
 
時間: 年份
* 預期: 可以「從最舊到最新排序」、「從最新到最舊排序」。
* 異常: 如果是「從 A 到 Z 排序」、「從 Z 到 A 排序」,需要將通用格式的年份,改成時間格式的年份:  
** 法1: 將 2010 改成 2010/01/01 ,再利用 YEAR 函數擷取出年份
** 法1: 將 2010 改成 2010/01/01 ,再利用 YEAR 函數擷取出年份
** 法2: 將 2010 改成 2010/01/01 ,再利用 [http://www.tableau.com/ Tableau]''$'' 轉換成年份 {{exclaim}} walk around approach!
** 法2: 將 2010 改成 2010/01/01 ,再利用 [http://www.tableau.com/ Tableau]''$'' 轉換成年份 {{exclaim}} walk around approach!
Line 95: Line 232:
== Troubleshooting of Excel errors ==
== Troubleshooting of Excel errors ==
=== Excel 效能議題 ===
=== Excel 效能議題 ===
個人經驗
* 資料筆數大量時的替代方案
* 資料筆數大量時的替代方案
** 如果 Excel 資料筆數約百萬筆,操作速度約耗費數小時,可改用資料庫。資料庫資料處理後,再輸出成 Excel 檔,而不要在 Excel 檔上面進行資料處理或換算。
** 如果 Excel 資料筆數約百萬筆,操作速度約耗費數小時,可改用資料庫。資料庫資料處理後,再輸出成 Excel 檔,而不要在 Excel 檔上面進行資料處理或換算。
** 如果要刪除符合特定條件約一萬筆的資料列太慢時。改成將篩選出符合另一條件的資料列,再複製貼上到新的工作表,會比較快。如果直接複製貼上也花超過5分鐘時間,可以選擇直接貼上值。
** 如果要刪除符合特定條件約一萬筆的資料列太慢時。改成將篩選出符合另一條件的資料列,再複製貼上到新的工作表,會比較快。如果直接複製貼上也花超過5分鐘時間,可以選擇直接貼上值。相關文章:[https://errerrors.blogspot.com/2020/03/resolve-too-slow-to-delete-data-rows-in-excel.html 解決 Excel 刪除資料太慢的問題]
* 降低資料處理複雜度
* 降低資料處理複雜度
** 全選工作表儲存格資料,複製後,選擇性貼上值到空白工作表。因為移除公式,所以另一工作表的操作速度會加快。 {{exclaim}} 需要注意時間格式的數值會跑掉,變成一長串數字,需要額外設定儲存格格式成時間格式。
** 全選工作表儲存格資料,複製後,選擇性貼上值到空白工作表。因為移除公式,所以另一工作表的操作速度會加快。 {{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]
* 電腦效能
** 軟體從 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 114: Line 272:
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]


{{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>
== Further reading ==
}}
 
* [https://tools.ietf.org/html/rfc4180 RFC 4180 - Common Format and MIME Type for Comma-Separated Values (CSV) Files]


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



Latest revision as of 14:50, 15 November 2021

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 Mac icon_os_mac.png 版本 16.33 檔案 -> 另存新檔 -> 檔案格式:CSV UTF-8 逗號分隔(.csv) UTF-8 編碼,帶簽名(BOM)
Microsoft Excel on Mac icon_os_mac.png 版本 16.32 檔案 -> 另存新檔 -> 檔案格式:逗號分隔值 (.csv) Big5 編碼
Microsoft Office 2016 on Win Os windows.png 檔案 -> 另存新檔 -> (選擇存檔位置) -> 存檔類型:CSV 逗號分隔 (.csv) Big5 編碼

Import CSV to EXCEL[edit]

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

CSV 檔案 (測試的 Excel 版本: Excel 2016 on Win Os windows.png 、LibreOffice Calc v. 5.4.3.2 on Win Os windows.png 、Excel for Mac 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 Mac icon_os_mac.png 直接開啟,會看到中文變成亂碼,需要改用匯入外部資料方式 Excel for (測試版本:Mac 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 Mac icon_os_mac.png 直接開啟,會看到中文變成亂碼,需要改用匯入外部資料方式 (測試版本:Mac 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 Mac icon_os_mac.png 直接開啟,會看到中文變成亂碼,需要改用匯入外部資料方式 (測試版本:Mac 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 Mac icon_os_mac.png 直接開啟,會看到中文變成亂碼,需要改用匯入外部資料方式 (測試版本:Mac 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。包含

  • 邏輯值 TRUEFALSE、字串、數值、錯誤代碼 #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]

Excel 效能議題[edit]

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

Excel 匯入 CSV 檔案後,遇到欄位錯置[edit]

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

原因:

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

解決方法:

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

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

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

原因:

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

解決方法:

  • 限制筆數

不同試算表方案的工作表或儲存格的大小限制[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 (命令提示字元)[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[edit]

Further reading[edit]

References[edit]


Troubleshooting of ...

Template