試算表軟體 醫務管理暨醫療資訊學系 陳以德 副教授: 濟世CS202-3 07-3121101 轉 2648-25 itchen@kmu.edu.tw
學習目標 試算表 Calc v.s. Excel Cell 儲存格格式 四則、時間運算, 指數, max, min, 和, 平均, 標準差 註解 條件判斷,排序,篩選 合併列印
Why Excel? 日常生活中我們經常需要製作一些圖表,而 且這一些圖表中包含著簡單或複雜的數學計 算、 統計工作,例如:家計簿、成績單、業績 報表、獲利預估…等。 這些工作都可以利用 Excel 完成。 微軟官方解釋 Microsoft Office Excel 可用來執行計算分析資訊, 並且管理多個工作表。
認識環境 檔案名稱 功能表 一般工具列 格式工具列 名稱方塊 作用儲存格 資料編輯列 工作表 狀態列
Create a new worksheet Approach 1
Create a new worksheet Approach 2 Step 1 : 滑鼠移到工作表上方 > 滑鼠右鍵 > 插入
Create a new worksheet Approach 2 (cont’d) Step 2 : 選"工作表" > 確定
Re-name a worksheet 方法一 : 格式 > 工作表 > 重新命名 方法二
Delete a worksheet 滑鼠移到工作表上方 > 滑鼠右鍵 > 刪除
Change worksheet color Menu Bar > 格式 > 工作表 > 索引標籤色彩
更改預設的工作表數目 Menu bar > 工具 > 選項 > 一般
隱藏工作表 格式 > 工作表 > 隱藏
顯示工作表 Step 1 : 格式 > 工作表 > 取消隱藏
顯示工作表 (cont’d) Step 2 : 選取工作表
同時選取多個工作表 方法 1 (一次選取一個) Step 1 : 左手按住 Ctrl 鍵 Step 2 : 右手移動滑鼠點選工作表 方法 2 (一次選取多個) Step 1 : 左手按住 Shift 鍵 Step 2 : 右手移動滑鼠點選工作表
隱藏欄 – Step 1 選取要隱藏的欄 > Menu Bar > 格式 > 欄 > 隱藏
隱藏欄 – Step 2 完成
顯示欄 – Step 1 選取欄位
顯示欄 – Step 2 Menu Bar > 格式 > 欄 > 取消隱藏
顯示欄 – Step 3 完成
隱藏列 – Step 1 選取要隱藏的列 > Menu Bar > 格式 > 列 > 隱藏
隱藏列 – Step 2 完成
顯示列 – Step 1 選取列 > Menu Bar > 格式 > 列 > 取消隱藏
顯示列 – Step 2 完成
永遠顯示完整功能表 Step 1 : Menu bar 工具 自訂
永遠顯示完整功能表 (cont’d) Step 2 : 選項 > 永遠顯示完整功能表
工作表之間的超連結 – Step 1 建立 5 個工作表 2004年度報表,春季報表,夏季報表,秋季報表,冬季報表
工作表之間的超連結 – Step 2 滑鼠點選橘色箭頭 按滑鼠右鍵 選擇"超連結"
工作表之間的超連結 – Step 3 選擇"超連結"的目的地
如何加入箭頭符號 - Step 1 Menu Bar > 插入 > 圖片 > 快取圖樣
如何加入箭頭符號 - Step 2 選取箭號符號
如何加入箭頭符號 - Step 3 滑鼠選取適當的位置 > 由左上角往右下角拉 完成
如何加入箭頭符號 - Step 4 調整美工 完成
Excel 2003 - Cell
Data Type 常數 數字、日期、時間、貨幣、百分比、分數、科學 符號 公式 Sum(B2:B10) Average(C3:C10)
如何輸入分數? 重點在於必須有一個空格 目前所在位置 公式 常數
輸入分數(fraction) 2/5 會被視為2月5日? Solution 1 0 2/5 重點在於必須有一個 "0" 和"空格“ Solution 2 先將儲存格的格式設為分數 選取儲存格 > 滑鼠右鍵 > 儲存格格式 > 數值 > 分數
輸入分數(fraction) 2/5 會被視為2月5日? (cont’d)
數值:加法 (1)
數值:加法 (2) 範圍 Sum(A2:E2)=Sum(A2,B2,C2,D2,E2)
數值:加法 (3)
數值:加法 (4)
數值:乘法 (1)
數值:乘法 (2)
數值:除法 (1)
數值:除法 (2) 問題:如何設定1位小數?
數值:除法 (2) (cont’d) 方法一: 滑鼠點選儲存格 滑鼠右鍵 儲存格格式
數值:除法 (2) (cont’d) 方法二: 滑鼠點選儲存格 > Menu Bar > 格 式 > 儲存格
數值:除法 (2) (cont’d) 1.選數值 2.設定小數位數
數值:除法 (2) (cont’d)
數值:除法 (3)
數值:指數 A^3=A*A*A
數值:百分比
數值:四捨五入 ROUND(number,num_digits) Number 是個您想執行四捨五入的數字
數值:ROUNDDOWN
數值:ROUNDUP
數值:MIN
數值:MAX
數值:AVERAGE
數值:標準差 - (1) 標準差的計算是採用不偏估計或 n-1 法。 STDEV 函數的計算公式是:
數值:標準差 - (2) 當樣本個數愈大時,STDEV 與 STDEVP 函數所算出 的標準差估計值會愈趨於相等。 標準差的計算是採用偏誤估計或 n 法。 STDEVP 函數的計算公式是:
貨幣
貨幣 (cont’d)
貨幣 (cont’d)
會計專用
會計專用 (cont’d)
數值:自動填滿
數值:自動填滿(cont’d) 滑鼠左鍵點著儲存格右下角不放 > 往下拉 (i.e., 用滑鼠拖曳)
數值:自動填滿 > 等差級數 Step 1 : 填寫A1的內容 Step 2 : 選取範圍
數值:自動填滿 > 等差級數 (cont’d) Step 3 : Menu Bar > 編輯 > 填滿 > 數列
數值:自動填滿 > 等差級數 (cont’d) Step 4 : 類型=等差級數 間距值=3
數值:自動填滿 > 等差級數 (cont’d) Step 5 : 完成
文字:自動填滿
文字:自動填滿 (cont’d) Step 1 : 填寫A1的內容 Step 2 : 滑鼠點選A1右下角往右拉 Step 3 : 完成
文字:自動填滿 > 自訂清單 Step 1 : Menu bar > 工具 > 自訂清單
文字:自動填滿 > 自訂清單 (cont’d) Step 2 : 在 A1 輸入資工系 > 滑鼠點選A1右下角往右 拉 Step 3 : 完成
文字+數字:自動填滿 Step 1 : 填寫A1的內容 Step 2 : 滑鼠點選A1右下角往右拉
日期儲存格:自動推算年齡 請將C2的格式設為數值
日期儲存格:加法
日期儲存格:減法 與預期結果不符
日期儲存格:減法 (cont’d) 解決方法 : 將儲存格 C2 的格式設為數值
日期儲存格:減法 (cont’d) 將儲存格 C2 的格式設為數值後
日期儲存格:設定允許的日期範圍 Step 1 : 選取欄位 (或範圍)
日期儲存格:設定允許的日期範圍 (cont’d) Step 2 : Menu bar > 資料 > 驗證
日期儲存格:設定允許的日期範圍 (cont’d) Step 3 : 設定
日期儲存格:設定允許的日期範圍 (cont’d) Step 4 : 提示訊息
日期儲存格:設定允許的日期範圍 (cont’d) Step 5 : 錯誤提醒
日期儲存格:設定允許的日期範圍 (cont’d) Step 6 : 輸入法模式
日期儲存格:設定允許的日期範圍 (cont’d) Step 7 : 出現提示訊息
日期儲存格:設定允許的日期範圍 (cont’d) Step 8 : 出現錯誤提醒
時間儲存格:減法
時間儲存格:加法 (1)
時間儲存格:加法 (2) 如何換行? Alt+Enter
相對位址 vs. 絕對位址 相對位址 A1 絕對位址 $A$1
相對位址 D2=B2*C2 自動填滿D3-D5
相對位址 (cont’d) D3自動變成B3*C3
絕對位址 D2=B2*C2*$F$2 自動填滿 D3-D5
絕對位址 (cont’d) D3自動變成B3*C3*$F$2而不是 B3*C3*F3
註解 – 插入註解
註解 – 編寫註解內容
註解 – 完成
註解 – 編輯 滑鼠移到儲存格上方 按右鍵 編輯註解
註解 –刪除 滑鼠移到儲存格上方 按右鍵 刪除註解
條件判斷 – 語法 (Syntax) =IF (logical_test,value_if_true,value_if_false)
條件判斷 – 範例一
條件判斷 – 範例二 =IF(F2>=90,"優", IF(F2>=80,"甲", IF(F2>=70,"乙", )
設定格式化的條件 - Step 1
設定格式化的條件 - Step 2 1 2 3 4
設定格式化的條件 - Step 3
設定格式化的條件 - Step 4 2 3 4 5 1
設定格式化的條件 - Step 5 完成
Excel 2007
Excel 2007 設定格式化的條件
Banner (文字藝術師) – Step 1 工作表資料先 Keyin 完成
Banner (文字藝術師)– Step 2 Menu Bar > 插入 > 圖片 > 文字藝術師
Banner (文字藝術師)– Step 3 挑選樣式 > 確定
Banner (文字藝術師)– Step 4 設定字型與大小
Banner (文字藝術師)– Step 5 移動至適當的位置並進行微調 > 完成
Banner (圖片) – Step 1 工作表資料先 Keyin 完成
Banner (圖片) – Step 2 Menu Bar > 插入 > 圖片 > 從檔案
Banner (圖片) – Step 3 挑選檔案 > 插入
Banner (圖片) – Step 4 點選圖片 > 進行微調 > 完成
參考其他工作表的儲存格 - 範例一 參考同一個活頁簿檔案的其他工作表的某 個儲存格 語法 工作表名稱!參考位址
參考其他工作表的儲存格 - 範例二 參考另一個活頁簿檔案的工作表 語法 注意 [活頁簿檔案]工作表名稱!參考位址 輸入相對檔案位置,自動轉換成絕對檔案位置
設定分頁點 – Step 1 選取位置 > 插入 > 分頁
設定分頁點 – Step 2 出現分頁線
設定分頁點 – Step 3 檔案 > 版面設定 > 頁首/頁尾
設定分頁點 – Step 4 檔案 > 版面設定 > 工作表 > 列印標題
排序 – 範例一 – Step 1 原始資料
排序 – 範例一 – Step 2 選取範圍 > Menu Bar > 資料 > 排序
排序 – 範例一 – Step 3 設定
排序 – 範例一 – Step 4 完成
排序 – 範例二 – Step 1 原始資料
排序 – 範例二 – Step 2 選取範圍 > Menu Bar > 資料 > 排序
排序 – 範例二 – Step 3 設定
排序 – 範例二 – Step 4 完成
篩選 – 範例一 – Step 1 原始資料
篩選 – 範例一 – Step 2 加上條件
篩選 – 範例一 – Step 3 選取範圍 > Menu Bar > 資料 > 篩選
篩選 – 範例一 – Step 4 設定
篩選 – 範例一 – Step 5 完成
篩選 – 範例二 – Step 1 原始資料
篩選 – 範例二 – Step 2 加上條件 - A and B
篩選 – 範例二 – Step 3 選取範圍 > Menu Bar > 資料 > 篩選
篩選 – 範例二 – Step 4 設定
篩選 – 範例二 – Step 5 完成
篩選 – 範例三 – Step 1 原始資料
篩選 – 範例三 – Step 2 加上條件 A or B
篩選 – 範例三 – Step 3 選取範圍 > Menu Bar > 資料 > 篩選
篩選 – 範例三 – Step 4 設定
篩選 – 範例三 – Step 5 完成
凍結窗格 – 動機 當一個表格有很多資料時(e.g., 一個班級的 成績單,有數百名學生),我們會希望捲動工 作表的捲軸往下拉時,各格儲存格的欄標題 能固定不動
凍結窗格 – Step 1 原始資料
因為沒有欄標題所以不知道每個數值代表什麼意義! 凍結窗格 – Step 2 捲動工作表的捲軸往下拉時 因為沒有欄標題所以不知道每個數值代表什麼意義!
凍結窗格 – Step 3 選擇儲存格 (A5 is selected!)
凍結窗格 – Step 4 Menu Bar > 視窗 > 凍結窗格
凍結窗格 – Step 5 出現凍結窗格的分隔線
凍結窗格 – Step 6 往下拉 > 欄標題固定不動
凍結窗格 – Step 7 如果要取消凍結窗格 => Menu Bar > 視窗 > 取消凍結窗格
儲存格格式 – Step 1 觀看範例 換行 大小不一
儲存格格式 – Step 2 滑鼠點選欄位 A > 滑鼠右鍵 > 儲存格格式
儲存格格式 – Step 3 欄位 A 的儲存格格式 > 勾選"縮小字型以配合欄寬"
儲存格格式 – Step 4 欄位 B 的儲存格格式 > 勾選“自動換列"
旋轉 – Step 1 & 2 Step 1 : 原始資料 Step 2 : 選取範圍
旋轉 – Step 3 Ctrl + C (複製) > 選擇新的位置 (D1) > Tool Bar > 複 製 > 轉置
旋轉 – Step 4 完成
Excel 2007 右鍵->選擇性貼上->轉置 Ctrl + C (複製) > 選擇新的位置 (D1) 右鍵->選擇性貼上->轉置
Excel 2003 - Advanced
Word + Excel 合併列印 – Step 1 準備一個EXCEL檔案 member.xls
Word + Excel 合併列印 – Step 2 開啟一個WORD檔案 happy_new_year.doc
Word + Excel 合併列印 – Step 3 WORD 的工具列出現合併列印功能 工具 > 自訂 > 工具列
Word + Excel 合併列印 – Step 4 WORD 的工具列 > 合併列印功能 > 開啟資料來源
Word + Excel 合併列印 – Step 5 選擇步驟一準備好的 EXCEL 檔案
Word + Excel 合併列印 – Step 6 確認
Word + Excel 合併列印 – Step 7 在適當的位置插入合併欄位
Word + Excel 合併列印 – Step 8 選擇要插入的欄位
Word + Excel 合併列印 – Step 9 已經插入好欄位
Word + Excel 合併列印 – Step 10 檢視合併資料
Word + Excel 合併列印 – Step 11 合併至新文件
Word + Excel 合併列印 – Step 12 選取範圍
Word + Excel 合併列印 – Step 13 完成
Word + Excel 合併列印 – Step 14 選擇合併到電子郵件
Word + Excel 合併列印 – Step 15 填寫資料 > 按下確定
Word + Excel 合併列印 – Step 16 E-mail 確認
Excel 2007 合併列印 先準備一個 Excel 檔 再進Word 郵件->啟動合併列印
分六個步驟 開始文件 選擇收信者 寫信 此時選瀏覽選 Excel檔案
選Excel檔
第4步選其他項目 可插入<<姓名>>變數
匯入外部資料
匯入 ACCESS 資料表 – Step 1 Create an ACCESS Database with 1 data table named student.
匯入 ACCESS 資料表 – Step 2 啟動EXCEL Menu bar 資料 匯入外部資料 匯入資料
匯入 ACCESS 資料表 – Step 3 選取檔案 > 開啟
匯入 ACCESS 資料表 – Step 4 匯入資料
匯入 ACCESS 資料表 – Step 5 完成
匯入文字檔 – Step 1 下載文字檔 資料來源 http://career.cju.edu.tw/2004/form/sipa.htm
匯入文字檔 – Step 2 啟動 Excel Menu bar 資料 匯入外部資料 匯入資料
匯入文字檔 – Step 3 選取檔案
匯入文字檔 – Step 4 匯入資料 > 步驟3之1
匯入文字檔 – Step 5 匯入資料 > 步驟3之2
匯入文字檔 – Step 6 匯入資料 > 步驟3之3
匯入文字檔 – Step 7 選擇匯入點
匯入文字檔 – Step 8 完成
Excel 2003 - Application
停車場 收費標準 每小時10元,不足一小時以一小時計算。
範例一:同一天 Step 1 : 停留時間 Step 2 :費用
範例二:允許跨日 Step 1 : 分鐘
範例二:允許跨日 (cont’d) Step 2 : 小時
範例二:允許跨日 (cont’d) Step 3 : 收費
學生成績 – Step 1 資料 Keyin + 計算
學生成績 – Step 2 Menu Bar 工具 增益集
學生成績 – Step 3 分析工具箱
學生成績 – Step 4 Menu Bar 工具 資料分析
Excel 2010 分析工具箱 Office 圖示 -> 右鍵 -> 自訂快速存取工具列 選 增益集 -> 分析工具箱-VBA 按「執行」
Excel 2010 分析工具箱 選取 勾選分析工具箱 分析工具箱 – VBA 再按 「是」
分析工具箱安裝中…
資料分析 -> 敍述統計
學生成績 – Step 5 資料分析 > 直方圖
學生成績 – Step 6 設定
學生成績 – Step 7 – 完成
學生成績 – Step 8 -放大
學生成績 – Step 9 – 圖表選項
學生成績 – Step 10 – 圖表選項 格線 > 數值 Y 軸 > 主要格線
學生成績 – Step 11 – 加了格線
試算表軟體 Calc的 畫面範例
Summery Cell 儲存格格式 四則、時間運算, 指數, max, min, 和, 平均, 標 準差 相對位址 vs. 絕對位址 註解 條件判斷,排序,篩選 合併列印
Reference http://www.vexp.idv.tw 自由軟體的天空:LibreOffice小技巧彙集 http://itchen.class.kmu.edu.tw