Google Docs and Spreadsheets: Difference between revisions

Jump to navigation Jump to search

Google Docs and Spreadsheets (edit)

Revision as of 12:22, 20 July 2015

5,981 bytes removed ,  20 July 2015
no edit summary
No edit summary
Line 1: Line 1:
[https://drive.google.com/ Google drive]
[https://drive.google.com/ Google drive]


 
== Microsoft Excel ==
== 不同試算表方案的工作表或儲存格的大小限制 ==
[[Microsoft Excel]]
* {{Gd}} EXCEL 2007, EXCEL 2013:
** (約100萬列) 1,048,576 列 * 16,384 欄 = 17,179,869,184 個儲存格<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>
* [http://www.libreoffice.org/ LibreOffice]([http://portableapps.com/apps/office/libreoffice_portable/ LibreOffice Portable]) Calc 3.3.3版以後 (目前版本 4.3):
** (約100萬列) 1,048,576 列 * 1,024 欄 = 1,073,741,824 個儲存格<ref>[http://ask.libreoffice.org/en/question/8631/upper-limit-to-number-of-rows-in-calc/ upper limit to number of rows in Calc - Ask LibreOffice]</ref><ref>[http://www.libreoffice.org/get-help/documentation/ Documentation » LibreOffice] - [https://wiki.documentfoundation.org/images/b/b7/CG34-CalcGuideLO.pdf Calc Guide 3.4 (spreadsheets)]</ref> {{exclaim}} When I save the CSV as XLS format, I met the 65,536 rows limit & 256 column limit on linux LibreOffice 3.4.5 {{Linux}} /v. 4.2.3.3 {{Win}}
* [http://www.openoffice.org/ Apache OpenOffice] 3.x Calc:
** (約60萬列) 65,536 列 * 1,024 欄 = 67,108,864 個儲存格<ref>[http://wiki.openoffice.org/wiki/Documentation/FAQ/Calc/Miscellaneous/What's_the_maximum_number_of_rows_and_cells_for_a_spreadsheet_file%3F What's the maximum number of rows and cells for a spreadsheet file? - Apache OpenOffice Wiki]</ref>
* EXCEL 2003: 工作表大小
** (約60萬列) 65,536 列 * 256 欄 = 16,777,216 個儲存格<ref>[http://office.microsoft.com/zh-tw/excel-help/HP005199291.aspx Excel 的規格及限制 - Excel - Office.com]</ref>
* Google spreadsheet:
** 約 1000列。 所有工作表共 40 萬個儲存格。如果只使用一個工作表，使用256 欄，則列數限制為 1,562 列<ref>[https://support.google.com/drive/answer/37603?hl=zh-Hant Google 文件、試算表和簡報的檔案大小限制 - 雲端硬碟說明]</ref>
* 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 ==
EXCEL limit:
* (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
 
== Import CSV to EXCEL ==
* [http://office.microsoft.com/zh-tw/excel-help/HP010099725.aspx 匯入或匯出文字檔案 - Excel] 支援 Unicode 編碼 {{Win}}
* 欄位內容分隔符號 " (雙引號); 欄位分隔符號 , 不可夾雜空白。Unicode 檔案需要檔案有 BOM，這樣才能點選兩下 CSV，Excel 開啟時沒有問題。 (Excel 2007) {{Win}}
* [http://errerrors.blogspot.tw/2014/05/mac-office-excel-csv-microsoft-excel.html Mac Office Excel 匯入中文 CSV 檔案 (Microsoft Excel for Mac 2011)] {{Mac}}
* 雖然 CSV 欄位已經用雙引號區隔，但是如果欄位裡面也有包含雙引號，匯入到 Excel，會導致欄位錯誤的問題。解法1: 原始資料的雙引號改成單引號、解法2: 不用 Excel ，改用 [http://www.libreoffice.org/discover/calc/ LibreOffice Calc]
 
== Copy & Paste ==
{{#widget:Iframe
|url=https://docs.google.com/spreadsheet/pub?key=0AuFlnVY9TNrsdDJodlRYRG1zWUY4dFd4SktiZzVyYlE&single=true&gid=0&range=A1%3AH8&output=html
|width=100%
|height=AUTO
|border=1
}}
[https://docs.google.com/spreadsheet/ccc?key=0AuFlnVY9TNrsdDJodlRYRG1zWUY4dFd4SktiZzVyYlE&usp=sharing 線上編輯]
 
* Microsoft Excel --> Google Spreadsheet:
** columns & rows: Copy & Paste is ok
* Plain text --> Google Spreadsheet: ok
** columns: {{kbd | key = Tab}} separated columns --> Google Spreadsheet: ok
** rows: {{kbd | key = Enter}} separated rows --> Google Spreadsheet: ok
* Plain text --> Microsoft Excel: ok
** columns: {{kbd | key = Tab}} separated columns --> Microsoft Excel: ok
** rows: {{kbd | key = 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 {{exclaim}} Suggest you copy to Microsoft Excel first and then copy paste to Google Document from Microsoft Excel.
** columns: {{kbd | key = Tab}} separated columns --> Table of Google Document: fail
** rows: {{kbd | key = Enter}} separated rows --> Table of Google Document: fail
* Plain text --> Microsoft Excel: ok
** columns: {{kbd | key = Tab}} separated columns --> Microsoft Excel: ok
** rows: {{kbd | key = Enter}} separated rows --> Microsoft Excel: ok
* [http://www.youtube.com/watch?v=Q43U-7r5AMk&desktop_uri=%2Fwatch%3Fv%3DQ43U-7r5AMk&nomobile=1 How-to Create and Copy a Table in Google Mail (Gmail) from Excel - YouTube]
 
== Google spreadsheet ==
利用[[Google spreadsheet]]的函式，分析及統計可複選的問卷題目結果。
 
 
 
== Excel 「樞紐分析表(Pivot Tables)」==
區塊「Σ 值」的「計數 - 欄位名稱」(該欄位的項目個數)，除非是沒有填入任何資料的「空值」，否則都會被計算加 1。包含
* 邏輯值 {{kbd | key =TRUE}}、{{kbd | key =FALSE}}、字串、數值、錯誤代碼 {{kbd | key =<nowiki>#NAME?</nowiki>}} 等資料，會被列入計算加 1
* {{kbd | key =<nowiki>IF</nowiki>}} 回傳的空值，也會被列入計算加 1 {{exclaim}} version: Excel 2013


== Post to MovableType from Google Docs ==
== Post to MovableType from Google Docs ==
Retrieved from "https://wiki.planetoid.info/index.php/Special:MobileDiff/15263"

Navigation menu