Difference between revisions of "Microsoft Excel"

From LemonWiki共筆
Jump to navigation Jump to search
(+ php library)
Line 139: Line 139:
 
** 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://onedrive.live.com/about/zh-tw/ Microsoft OneDrive] {{access | date=2020-03-10}}
 +
* 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.google.com/drive/ Google drive]
 +
* 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>
  
 
== Google spreadsheet ==
 
== Google spreadsheet ==
 
利用[[Google spreadsheet]]的函式,分析及統計可複選的問卷題目結果。
 
利用[[Google spreadsheet]]的函式,分析及統計可複選的問卷題目結果。
 
 
  
 
== Excel 「樞紐分析表(Pivot Tables)」==
 
== Excel 「樞紐分析表(Pivot Tables)」==

Revision as of 10:33, 13 March 2020

Export MySQL query to Excel file

Export MySQL query to Excel file

Export CSV from EXCEL

Excel 檔案,匯出 UTF-8 編碼的 CSV 檔案

軟體 操作方式 輸出 CSV 檔案格式
LibreOffice Calc (LibreOffice Portable) 版本 6.2.4.2 檔案 -> 另存新檔 -> 存檔類型:文字 CSV (.csv) -> 字元集:Unicode (UTF-8) Good! UTF-8 編碼 不帶簽名(BOM)
LibreOffice Calc (LibreOffice Portable) 版本 6.2.4.2 檔案 -> 另存新檔 -> 存檔類型:文字 CSV (.csv) -> 字元集:正體中文 (Big5) Big5 編碼
Google 雲端硬碟 檔案 -> 下載 -> 逗號分隔值檔案 (.csv,目前工作表) Good! UTF-8 編碼 不帶簽名(BOM)
Microsoft Excel on Mac Os mac.png 版本 16.33 檔案 -> 另存新檔 -> 檔案格式:CSV UTF-8 逗號分隔(.csv) UTF-8 編碼,帶簽名(BOM)
Microsoft Excel on Mac Os mac.png 版本 16.32 檔案 -> 另存新檔 -> 檔案格式:逗號分隔值 (.csv) Big5 編碼
Microsoft Office 2016 on Win Os windows.png 檔案 -> 另存新檔 -> (選擇存檔位置) -> 存檔類型:CSV 逗號分隔 (.csv) Big5 編碼

Import CSV to EXCEL

讓 CSV 可以方便讓 Excel 直接開啟

CSV 檔案 (測試的 Excel 版本: Excel 2015 on Win Os windows.png 、LibreOffice Calc v. 5.4.3.2 on Win Os windows.png 、Excel for Mac Os mac.png 2011 v. 14.7.7)

  • 欄位間隔符號 , 、UTF-8 編碼、 BOM[1]
    • Good! 使用 Excel on Win Os windows.png 點選兩下檔案直接開啟,不會看到中文變成亂碼,而且不同欄位不會擠到同一個欄位 (儲存格)
    • 使用 LibreOffice Calc on Win Os windows.png 直接開啟檔案,會看到「文字匯入」對話視窗,需要調整字元集「Unicode (UTF-8)」,分隔符號則已經勾選「逗號」,即可順利匯入資料
    • 使用 Excel on Mac Os mac.png 直接開啟,會看到中文變成亂碼,需要改用匯入外部資料方式
  • 欄位間隔符號 , 、UTF-8 編碼、沒有 BOM →
    • 使用 Excel on Win Os windows.png 點選兩下檔案直接開啟,會看到中文變成亂碼,需要改用匯入外部資料方式
    • 使用 LibreOffice Calc on Win Os windows.png 直接開啟檔案,會看到「文字匯入」對話視窗,字元集正確偵測是「Unicode (UTF-8)」,分隔符號則已經勾選「逗號」,即可順利匯入資料
    • 使用 Excel on Mac Os mac.png 直接開啟,會看到中文變成亂碼,需要改用匯入外部資料方式
  • 欄位間隔符號 TAB 、UTF-8 編碼、 BOM →
    • 使用 Excel on Win Os windows.png 點選兩下檔案直接開啟,不會看到中文變成亂碼,但是不同欄位擠到同一個欄位 (儲存格)
    • 使用 LibreOffice Calc on Win Os windows.png 直接開啟檔案,會看到「文字匯入」對話視窗,需要調整字元集「Unicode (UTF-8)」,分隔符號則已經勾選「定位鍵」,即可順利匯入資料
    • 使用 Excel on Mac Os mac.png 直接開啟,會看到中文變成亂碼,需要改用匯入外部資料方式
  • 欄位間隔符號 TAB 、UTF-8 編碼、沒有 BOM (byte-order mark) →
    • 使用 Excel on Win Os windows.png 點選兩下檔案直接開啟,會看到中文變成亂碼,需要改用匯入外部資料方式
    • 使用 LibreOffice Calc on Win Os windows.png 直接開啟檔案,會看到「文字匯入」對話視窗,字元集正確偵測是「Unicode (UTF-8)」,分隔符號則已經勾選「定位鍵」,即可順利匯入資料
    • 使用 Excel on Mac Os mac.png 直接開啟,會看到中文變成亂碼,需要改用匯入外部資料方式

Import XLS to MySQL

Batch convert TXT/CSV to EXCEL

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

from spreadsheet application to another spreadsheet application

線上編輯上面表格

  • 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

  • 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

Microsoft OneDrive [Last visited: 2020-03-10]

  • file limit: 5MB[2]

Google drive

  • file limit: "Up to 5 million cells or 18,278 columns (column ZZZ) for spreadsheets that are created in or converted to Google Sheets."[3]

Google spreadsheet

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

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

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

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

Checking of data type

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

數字

時間

時間: 年份

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

Excel 效能議題

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

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

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

Merge data from different files

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

  • 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

相關新聞聯播

Excel OR Spreadsheet OR 資料科學 相關新聞聯播
強到開畫展!EXCEL技術不拿來做報表 日本爺爺竟然用來創作「浮世繪」 - Yahoo奇摩新聞
FevaWorks 免費 Excel 工作坊 獲微軟官方認可培訓伙伴 - UNWIRE.HK
Excel乘法公式:計算整排表格的乘積,有文字或空格也不影響 - 蘋果仁(HK)
Excel如何加總「篩選過」的數值,不計算隱藏的儲存格? - 蘋果仁
從千萬首歌單百萬用戶推薦預測,如何練出跨產業AI預測力 - iThome Online
Excel 一次選取/刪除所有空格:「特殊目標」搜尋所有空白儲存格 - 蘋果仁(HK)
Excel 錯誤值代碼說明:###、#NULL、#DIV/0!、#VALUE! 是什麼意思? - 蘋果仁(HK)
excel光綻時裳四色眼采,限定新色登場 春夏迎接鮮嫩果汁色!幻紫甜柚演繹日系溫柔 | 品牌新聞 - 妞新聞 niusnews
EXCEL表格欄列要怎麼鎖定?常用的Google試算表功能藏在這 - UDN 聯合新聞網
【Excel 越改越難用?】科學家改不了Excel 格式,只好重新命名27 種人類基因| TechOrange - TechOrange 科技報橘
微軟Excel 改寫了人類基因的... - XFastest News
數字裡的魔鬼:探索 Airbnb 的資料科學與機器學習 - INSIDE 硬塞的網路趨勢觀察
想學機器學習、深度學習、資料科學、人工智慧的人照過來! 22 個線上社群網站助你一臂之力 - PanSci 泛科學
方便好用的Excel反而增加遺傳學家的挑戰 - 明日科學
【Excel教學】試算表複雜難閱讀? 即學3步驟設定「格式化條件」 - 香港01
【Excel函數】5 個辦公室常用Excel公式整理(上) - Yahoo奇摩新聞
Excel輸入手機號碼0狂被吃掉?日期永遠變小數?選對格式免加班加到爽 - UDN 聯合新聞網
【Excel】菜鳥必學!超實用4個Excel基礎功能:排序、篩選、格式化、樞紐分析 - Yahoo奇摩新聞
【Excel教學】10大常用試算表函數公式熟用秒速計算及整理文件 - 香港01
EXCEL核對重複資料,浪費時間又容易出錯?簡單3步驟「抓漏」更效率 - 風傳媒
苦惱將 google 表單回覆輸出 Excel?透過 SurveyCake 輕鬆匯出多種表單回覆格式! - The News Lens 關鍵評論網
排不好還讓整個表格亂掉?EXCEL排序功能這樣做 - UDN 聯合新聞網
活用 Excel 合併彙算!把不同工作表的資料合而為一、還能自動更新 - 經濟日報
【Excel教學】善用SUM函數、貼上功能即學8大試算表計加數方法 - 香港01
不會Excel只配領22K?網友笑:他啥都不會卻年薪500萬 - UDN 聯合新聞網
Excel推出Money功能助用家管理財務- 香港經濟日報- 即時新聞頻道- 科技 - 香港經濟日報 - 即時新聞
【Excel教學】善用一個Shift鍵8個小技巧即提升工作效率 - 香港01
WFH必學32個Excel快捷鍵及美化秘技輕鬆做出專業報表提升效率|科技玩物 - 香港01
果然萬能,Amazon 工程師開源出 Excel 電腦視覺課程 - INSIDE 硬塞的網路趨勢觀察
Excel快速鍵懶人包》鍵盤上F1到F12用法大揭秘,一個按鍵處理所有雜事 - 風傳媒
【Excel函數】5 個辦公室常用Excel公式整理(下) - Yahoo奇摩新聞
怎么把PDF转换成Excel?这样轻松完成转换 - 站长网

Powered by Google News

references


Troubleshooting of ...

Template