Microsoft Excel: Difference between revisions
Jump to navigation
Jump to search
(Created page with " == 不同試算表方案的工作表或儲存格的大小限制 == * {{Gd}} EXCEL 2007, EXCEL 2013: ** (約100萬列) 1,048,576 列 * 16,384 欄 = 17,179,869,184 個儲存...") |
No edit summary |
||
| Line 17: | Line 17: | ||
* (1) "Total number of characters that a cell can contain: 32,767 characters" Using [http://www.w3resource.com/mysql/string-functions/mysql-substring-function.php MySQL SUBSTRING() function] ex: {{kbd | key = <nowiki>SUBSTRING(string_column, 1, 32767)</nowiki>}} for EXCEL 97<ref>[https://support.microsoft.com/en-us/kb/296053 Summary of capability limitations in Excel 97]</ref>-2007<ref>[https://support.office.com/en-us/article/Excel-specifications-and-limits-16c69c74-3d6a-4aaf-ba35-e6eb276e8eaa?CorrelationId=1de7e8d2-91e2-4eec-8868-4dc8cbd23463&ui=en-US&rs=en-US&ad=US Excel specifications and limits]</ref> ''unverified'' | * (1) "Total number of characters that a cell can contain: 32,767 characters" Using [http://www.w3resource.com/mysql/string-functions/mysql-substring-function.php MySQL SUBSTRING() function] ex: {{kbd | key = <nowiki>SUBSTRING(string_column, 1, 32767)</nowiki>}} for EXCEL 97<ref>[https://support.microsoft.com/en-us/kb/296053 Summary of capability limitations in Excel 97]</ref>-2007<ref>[https://support.office.com/en-us/article/Excel-specifications-and-limits-16c69c74-3d6a-4aaf-ba35-e6eb276e8eaa?CorrelationId=1de7e8d2-91e2-4eec-8868-4dc8cbd23463&ui=en-US&rs=en-US&ad=US Excel specifications and limits]</ref> ''unverified'' | ||
* (2) "Worksheet size: 1,048,576 rows": Using {{kbd | key =LIMIT}} for EXCEL 2007 | * (2) "Worksheet size: 1,048,576 rows": Using {{kbd | key =LIMIT}} for EXCEL 2007 | ||
相關文章 | |||
* [http://errerrors.blogspot.tw/2015/07/excel.html Err: 解決從資料庫輸出 Excel 檔案，遇到非常大數字的科學符號和有效位數的問題] | |||
== Import CSV to EXCEL == | == Import CSV to EXCEL == | ||
| Line 61: | Line 65: | ||
* {{kbd | key =<nowiki>IF</nowiki>}} 回傳的空值，也會被列入計算加 1 {{exclaim}} version: Excel 2013 | * {{kbd | key =<nowiki>IF</nowiki>}} 回傳的空值，也會被列入計算加 1 {{exclaim}} version: Excel 2013 | ||
== references == | |||
<references/> | |||
[[Category:spreadsheet]] [[Category:Excel]] | [[Category:spreadsheet]] [[Category:Excel]] | ||
[[Category:Data Science]] | [[Category:Data Science]] | ||
Revision as of 12:23, 20 July 2015
不同試算表方案的工作表或儲存格的大小限制
EXCEL 2007, EXCEL 2013:
- LibreOffice(LibreOffice Portable) Calc 3.3.3版以後 (目前版本 4.3):
- Apache OpenOffice 3.x Calc:
- (約60萬列) 65,536 列 * 1,024 欄 = 67,108,864 個儲存格[5]
- EXCEL 2003: 工作表大小
- (約60萬列) 65,536 列 * 256 欄 = 16,777,216 個儲存格[6]
- Google spreadsheet:
- 約 1000列。 所有工作表共 40 萬個儲存格。如果只使用一個工作表，使用256 欄，則列數限制為 1,562 列[7]
- Office 365: 資料模型的規格與限制 - Excel - Office.com 適用: Excel 2013, Power BI for Office 365 Preview, Power Pivot in Excel 2013
Export MySQL query to Excel file
EXCEL limit:
- (1) "Total number of characters that a cell can contain: 32,767 characters" Using MySQL SUBSTRING() function ex: SUBSTRING(string_column, 1, 32767) for EXCEL 97[8]-2007[9] unverified
- (2) "Worksheet size: 1,048,576 rows": Using LIMIT for EXCEL 2007
相關文章
Import CSV to EXCEL
- 匯入或匯出文字檔案 - Excel 支援 Unicode 編碼 Win
- 欄位內容分隔符號 " (雙引號); 欄位分隔符號 , 不可夾雜空白。Unicode 檔案需要檔案有 BOM，這樣才能點選兩下 CSV，Excel 開啟時沒有問題。 (Excel 2007) Win
- Mac Office Excel 匯入中文 CSV 檔案 (Microsoft Excel for Mac 2011) macOS
- 雖然 CSV 欄位已經用雙引號區隔，但是如果欄位裡面也有包含雙引號，匯入到 Excel，會導致欄位錯誤的問題。解法1: 原始資料的雙引號改成單引號、解法2: 不用 Excel ，改用 LibreOffice Calc
Copy & Paste
- 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
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
Google spreadsheet
利用Google spreadsheet的函式，分析及統計可複選的問卷題目結果。
Excel 「樞紐分析表(Pivot Tables)」
區塊「Σ 值」的「計數 - 欄位名稱」(該欄位的項目個數)，除非是沒有填入任何資料的「空值」，否則都會被計算加 1。包含
- 邏輯值 TRUE、FALSE、字串、數值、錯誤代碼 #NAME? 等資料，會被列入計算加 1
- IF 回傳的空值，也會被列入計算加 1 version: Excel 2013
references
- ↑ Excel 的規格及限制 工作表大小
- ↑ Excel specifications and limits - Excel
- ↑ upper limit to number of rows in Calc - Ask LibreOffice
- ↑ Documentation » LibreOffice - Calc Guide 3.4 (spreadsheets)
- ↑ What's the maximum number of rows and cells for a spreadsheet file? - Apache OpenOffice Wiki
- ↑ Excel 的規格及限制 - Excel - Office.com
- ↑ Google 文件、試算表和簡報的檔案大小限制 - 雲端硬碟說明
- ↑ Summary of capability limitations in Excel 97
- ↑ Excel specifications and limits