Microsoft Excel

From LemonWiki共筆
Jump to: navigation, search

Export MySQL query to Excel file[edit]

Export MySQL query to Excel file

Export CSV from EXCEL[edit]

建議使用 LibreOffice Calc (LibreOffice Portable) 開啟 Excel 檔案,匯出 UTF-8 編碼的 CSV 檔案

  • 雖然 Excel 本身也有提供匯出 「Unicode 文字 (*.txt)」,但是因為無法設定使用雙引號框住欄位值。會造成後續匯入檔案容易出問題。
  • Excel 本身提供匯出「CSV (逗號分隔) (*.csv)」是 Big5/ANSI 編碼,遇到 Unicode 文字會變成問號。

Import CSV to EXCEL[edit]

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! $ 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

Google spreadsheet[edit]

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


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

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

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

Excel 儲存格格式轉換[edit]

  • 通用格式的年份,改成時間格式的年份:
    • 法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分鐘時間,可以選擇直接貼上值。
  • 降低資料處理複雜度
    • 全選工作表儲存格資料,複製後,選擇性貼上值到空白工作表。因為移除公式,所以另一工作表的操作速度會加快。 Icon exclaim.gif 需要注意時間格式的數值會跑掉,變成一長串數字,需要額外設定儲存格格式成時間格式。
    • 多重篩選條件會使用比較多的系統資源,在不使用篩選條件下,改成使用函數處理,會比較快。
  • 電腦效能

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

  • 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)

相關新聞聯播[edit]

Excel OR Spreadsheet OR 資料科學 相關新聞聯播
How to Excel at Both Strategy and Execution

Meet The Spreadsheet That Can Solve NYC Transit (and the Man Who Made It)
Raptors' Fred VanVleet ready to excel in expanded role
Legacy Dream, Classical Dancer, Saladin, Masada, Malawi and Kangra excel
West Bengal, Assam teams excel in postal table tennis tourney
Cascade Locks 'Excel' program receives funding boost from area businesses
Sushil 'walks over' to a gold; Sakshi, Geeta excel at National Wrestling championship
Young is old news as Riders' quarterbacks excel
This course will teach you everything you don't know about Microsoft Excel
Family's Financial Worries Motivate Student to Excel
Dolphins' defense wants offense to give them a chance to excel
Family's move to city sees daughter excel
Shipley brothers excel again, help 10-3 Weddington handle Huss in second round
Football Manager 2018 Review – Spreadsheet Simulator
It's 2017 – and your Windows PC can be forced to run malware-stuffed Excel macros
Boxers who can excel in MMA
ATHLETICS: Wellington School students excel in pentathlon and modern pentathlon events
African-American students excel through OCCC mentorship program
Stonebridge karatekas excel in Black Belt examination

Spreadsheet Phil must become Storyteller Phil – if his Budget is to succeed where the Tory conference failed

Powered by Google News

references[edit]

  1. 命令提示字元:常見問題集 - Windows 說明


Troubleshooting

Template

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