EXCEL 簡介 一 EXCEL ( 電子試算表軟體 ) 的功能 表格式的環境 具備有公式 函數, 計算功能強大 可建立圖表 可做資料彙整及統計 應用於成績單 統計表 會計報表等 二 EXCEL 結構預設 :.xls.xlsx 活頁簿 Book( 檔案 ) 範本 :.xlt.xlts 工作表 Sheet( 表格 ) == 欄 列組成 儲存格 Cell ( 資料 ) { 位址 = 欄名列名 } EXCEL 編輯 列 { 範例 = 左上 : 右下 } 欄 工作表標籤列 儲存格 一 資料輸入 資料分為二種 文字資料 : 預設靠左對齊, 數字前加單引數 (`) 會成為文字, 例 `010100 數值資料 : 包含數字 日期 時間, 預設靠右對齊 點一下儲存格, 即可輸入, 完成按 ENTER 或方向鍵或 TAB 鍵會移至下一個 要修改文字時, 要點二下或按 F2 鍵 在儲存格中要分行, 要按 Alt+Enter 鍵 製作成績單 1. 先輸入 姓名 數學 國文 英文 總分 平均 六個欄位名稱 2. 再自行輸入十筆資料 ( 總分 平均先不輸入 ) 二 儲存格的選取 單一儲存格 點一下 多個儲存格 拖曳或 Shife 鍵 ( 點左上 右下的儲存格 ) 多重範圍 ( 不連續 ) Ctrl 鍵 + 拖曳 整列 按列名 整欄 按欄名 整個工作表 按左上角的矩形 三 填滿控點 重複性資料 : 直接拖曳填滿控點 Ctrl+ 拖曳填滿控點, 會以遞增 ( 往下或右 ) 或遞減( 往上或左 ) 產生連續資料 第 1 頁
順序性資料 ( 等差 ): 在兩個儲存格輸入數值 選取兩格 再拖曳填滿控點 四 儲存格的增加 / 刪除 ( 先選取範圍 ) 方法 1. 右鍵 插入 或 刪除 設定增 / 刪儲存格的移動方式 方法 2. 刪除儲存格 : 功能表 編輯 / 刪除 增加儲存格 : 功能表 插入 / 儲存格 注意 :Del 鍵只能刪除資料 選取資料後拖曳填滿控點, 會刪除資料五 儲存格的搬移 複製 ( 先選取範圍 ) 方法 1. 工具列或功能表 : 複製或剪下, 再做貼上方法 2. 滑鼠的拖曳 : 選取 游標放在框線上 拖曳 即可 搬移 六 儲存格插入註解 : 右上角會有紅色三角形 (Ctrl + 拖曳則為 複製 功能 ) 1. 利用儲存格增加的方法, 在最左邊增加一個 座位 欄位, 並以填滿控點方式填上座號 2. 利用儲存格搬移的方法, 將 數學 英文 兩個欄位的位置對調 七 欄 列的操作 ( 兩者操作相似, 以欄的操作來說明 ) 欄寬調整 若欄寬不足時, 數值資料會以 # 表示, 文字資料則無法完整顯示方法 1. 單欄的欄寬 : 拖曳欄名右側的欄線, 即可調整單一欄寬 方法 2. 多欄的欄寬 : 選取欲調整之多欄 功能表 格式 / 欄 / 欄寬 輸入欄寬大小, 即可設定多欄有相同的欄寬 方法 3. 自動調整 : 在欄線上點二下, 會調整成最適欄寬 欄位的增 / 刪 ( 先選取範圍 ) 方法 1. 右鍵 插入 或 刪除 方法 2. 刪除 : 功能表 編輯 / 刪除 增加 : 功能表 插入 / 欄 增加的欄位會出現在游標或選取範圍的左邊 欄位的搬移 / 複製 直接剪下或複製 再貼上會覆蓋目的地的資料 若要調整位置, 利用滑鼠右鍵的 插入剪下的儲存格 欄位的隱藏 工作表超過一頁寬時, 可暫時將不需看到的欄位設定隱藏, 避免捲軸要經常左右移動 隱藏的方法 : 選取欄 功能表 格式 / 欄 / 隱藏 ( 或右鍵 / 隱藏 ) 第 2 頁
取消隱藏 : 選取隱藏欄 ( 包含左右兩欄 ) 功能表 格式 / 欄 / 取消隱藏 ( 或右鍵 / 取消隱藏 ) 1. 利用 插入剪下的儲存格 的方法, 將 英文 國文 的位置對調 2. 在 數學 右側增加兩個科目 ( 例 : 專一 專二 ) 成績, 並輸入資料 3. 適當調整欄寬, 其中五個科目的欄寬要設定相同 4. 表格上增加一列, 輸入 年 班第 次成績單 格式化資料 一 格式文字 字型設定 : 方法 1. 格式工具列方法 2. 功能表 格式 / 儲存格 的 字型 設定頁 文字對齊及方向方法 1. 格式工具列 : 只有設定水平對齊方法 2. 功能表 格式 / 儲存格 的 對齊方式 設定頁 跨欄置中 跨欄置中是將多個儲存格合併成一格, 並將文字置中 跨欄置中只會保留第一個儲存格內的文字, 其餘會刪除 設定 : 方法 1 選取 格式工具列的 跨欄置中 按鈕 方法 2 選取 功能表 格式 / 儲存格 的 對齊方式 設定頁, 勾選 合併儲存格 二 格式化工作表 框線設定方法 1. 格式工具列的框線按鈕方法 2. 功能表 格式 / 儲存格 的 外框 設定頁 網底設定方法 1. 格式工具列的填滿顏色按鈕方法 2. 功能表 格式 / 儲存格 的 圖樣 設定頁 1. 表格標題設定跨欄置中 2. 依個人設計, 將成績單加上文字格式 框線及網底 三 資料格式設定 : 見 12-5 頁 第 3 頁
練習二 : 成績單 1. 輸入資料如表 2. 平均分數 最高分數 最低分數 成績數之儲存格設定為粗體字, 跨欄置中 3. 文字對齊方式如結果, 如結果圖 4. 適當調整欄寬及加入框線, 如結果圖 5. 請自行設定文字及填滿顏色 6. 資料計算 : (1) 平均 : 統計學 微績分 資訊概論 三科之平均, 顯示至小數第一位 (2) 及格科目數 : 以 COUNTIF 函數算出每位學生及格的科目數 (3) 及格否 : 以 IF 函數判斷每位學生的平均是否達到及格標準, 若及格者該欄顯示 V, 否則顯示 X (4) 平均分數 : 以 AVERAGE 函數算出, 四捨五入 (ROUND 函數 ) 至小數第二位 (5) 最高分數 : 以 MAX 函數算出 (6) 最低分數 : 以 MIN 函數算出 (7) 成績數 : 以 COUNT 函數算出 7. 結果圖如下 : 第 4 頁
條件化格式 功能表 格式 / 設定格式化條件 : 依設定的條件結果, 顯示出特定的格式 1. 將各科成績不及格分數的儲存格, 以紅色 粗體字來標示 公式及函數 一 公式 公式 函數之前必須先輸入等號 = 公式使用方式 : 方法 1. 人工輸入 運算式方法 2. 點選帶入 儲存格位址 公式有錯誤時, 會顯示 #VALUE! 的訊息 複製公式 : 拖曳填滿控點, 可選擇是否要改變格式 1. 分別以 人工輸入 及 點選帶入 的方式, 練習計算總分 平均 二 函數 函數的格式 : 函數名稱 ( 參數 1, 參數 2 ) 函數使用方式 : ( 開始用等號 = ) 方法 1. 工具列的提供有總和 平均 最大值 最小值 計數 5 種方法 2. 使用插入數按鈕或 Shift + F3 鍵 常用函數有 : 總和 SUM() 平均 AVERAGE() 最大值 MAX() 最小值 MIN() 計數 COUNT() 四捨五入 ROUND() 排序 RANK() 查表 VLOOK() HVLOOK() 判斷 IF() 1. 以函數方式, 練習計算總分 平均 ( 小數第二位四捨五入 ) 三 位址表示法 相對參照位址 : 複製公式時, 公式內的位址會相對改變 絕對參照位址 : 在欄名和列名前加上 $, 公式內的位址不會改變 混合參照位址 : 在欄名或列名前加上 $, $ 後的位址即固定 按 F4 鍵切換參照方式 第 5 頁
立體參照位址 : 完整標示位址, 包含 [ 活頁簿 ] 工作表! 儲存格格式 [Book1.xls] Sheet1! A1 插入定義名稱 : 將某個資料範圍用一個名稱來替代 採絕對參照位址 1. 在成績單外的右側, 建立一個科目節數表 ( 例如 : 國文 *4, 英文 *3, 數學 *3, 地理 *2, 歷史 *2) 接著, 在 總分 欄位的左邊增加 乘積總分 欄位, 以絕對位址法輸入公式 2. 在成績單最右邊增加一個欄位 及格, 若總分不及格則顯示 * 工作表操作 工作表的命名 改名 : 名稱處點二下 : 工作表的複製 : 拖曳標籤 工作表的搬移 :Ctrl + 拖曳標籤 工作表標籤列 排序 單欄排序 : 直接使用工具列的遞增 遞增的按鈕 多欄排序 : 功能表 資料 / 排序, 留意標題列有無的差別 1 名次以總分排序, 若總分同分者以國文分數決定高低 成績單最後再以座號排序 第 6 頁
練習三 : 成績單 1. 計算請假資料的 小計 (H 欄 )= 事假 0.5 + 病假 0.5 + 曠課 1 2. 計算 操行成績 (I 欄 )= 基本分 + 導師評分 - 小計, 調整小數位至整數 3. 利用 導師評分 的分數至 導師評分對照表 以查表方式找出每位學生的 導師評語 ( 提示 :VLOOKUP 或 HLOOKUP) 4. 計算班級的 平均 (B17) 最高分 (B18) 最低分 (B19): 以全班同學 操行成績 (I 欄 ) 分別計算出總平均 最高分 最低分 5. 計算班級的請假統計 : 總時數 (E17 F17 G17) 分別為事假 病假 曠課的時數總和, 人次 (E18 F18 G18) 分別為事假 病假 曠課的請假人數 ( 即具有數字的儲存格數目 ) 6. 調整標題列 (A1:J2) 格式 : 設定跨欄置中 ( 結果如下圖 ), 文字設定為粗體字, 框線除下框為雙線 其餘外框為粗線 內框為細線, 網底為海藻綠色 7. 調整表格內容 (A3:J12) 格式 : 框線除上框為雙線 其餘外框為粗線 內框為細線, 網底為淺綠色 8. 調整表格 (A1:J12) 格式 : 文字皆為置中對齊 9. 結果如下 : 第 7 頁
練習四 : 成績單 1. 輸入資料如下的資料 2. 資料計算 : (1) 考績獎金 = 本薪 乘數比例 ( 乘數比例為依據每位員工的考績, 參考上表中 考績等級對照乘比例, 使用 HLOOKUP 函數 ) (2) 薪資合計 = 本薪 + 考績獎金 3. 標題 華穎資訊公司 設定跨欄置中,20pt 字型大小, 粗體字 4. 標題 年終考績統計表 設定跨欄置中,16pt 字型大小, 斜體及底線字 5. 表格標題 A4:A5,A7:F7 設定為粗體字, 水平置中 6. 數字格式 : (1) 乘數比例 之顯示格式設定為分數形式, 如 1.5 1 1/2 (2) 本薪 考績獎金 薪資合計 之顯示格式加入千分位 7. 文字對齊方式如結果 8. 適當調整欄寬及加入框線, 如結果 9. 請自行設定文字及填滿顏色 10. 結果 : 圖 2 第 8 頁
一 資料的篩選 格式功能表 / 自動篩選 格式功能表 / 進階篩選 1. 原工作表名稱命名為 成績單 2. 複製 成績單 工作表並命名為 奨學金, 以篩選方式顯示出前五名且 總分在 85 分以上的學生 3. 複製 成績單 工作表並命名為 全科及格, 以篩選方式找五科成績皆 在 60 分以上的學生 4. 複製 成績單 工作表並命名為 單科優等, 以篩選方式找單科成績有 在 90 分以上的學生 目前操作中的工作表 第 9 頁
圖表 Excel 講義 一 建立圖表 執行工具列的 圖表精靈按鈕, 依四個步驟逐步設計 1. 圖表類型 : 選擇圖表及副表的類型 副表又分一般 堆疊及百分比 2. 資料來源 : 可按資料範圍右側的按鈕縮小設定視窗來選取 數列資料取自列或欄, 會改變 X 軸和數列的位置 3. 圖表選項 : 可設定標題 圖例 資料標籤 4. 圖表位置 : 放在與資料同一個工作表或新工作表 1. 繪製 1~5 號同學, 國 英 數三科的成績直條圖 圖表放在同一工作表中 二 圖表修改 在圖表上方按右鍵, 可在快顯功能表中上述建立圖表的四個選項 三 圖表格式 圖表位置 : 拖曳圖表 圖表大小 : 拖曳圖表外框 圖表區格式 : 在圖表區空白處按右鍵, 點選 圖表區格式 可設定整個圖表的字型 外框 背景色或圖 各部份格式 :EXCEL 圖表中每個部份皆可自行修改, 先選取要設定的部份, 再按右鍵點選 XX 區格式 ( 或點二下 ) 1. 請依個人喜好修改圖表格式 四 不連續區域的圖表 在選取圖表範圍時, 若圖表區域不連續, 則以 Ctrl 分開選取 請繪製前三名同學, 國 英 數三科的成績折線圖 圖表放在新工作表中, 工作表名稱改為 前三名圖表 第 10 頁
版面設定 執行功能表 檔案 / 版面設定 來調整列印的相關格式 頁面標籤 : 可設定紙張大小及方向 在縮放比例中, 可指定縮放的百分比, 或自動調整成 1 頁的大小 邊界標籤 : 可指定上 下 左 右的邊界, 來調整資料和紙張邊緣之間的距離 在置中方式, 可指定資料在紙張的水平或垂直的置中設定 頁首 / 頁尾標籤 : 在每一頁的上方 ( 頁首 ) 和下方 ( 頁首 ) 區域加上列印的資料, 如標題 頁碼 製作日期等 可由內建的清單選擇或按自訂按鈕來建立 使用自訂方式建立頁首 / 頁尾, 宜善用上方的按鈕列, 來調整文字格式 A 加入頁碼 # 日期等 2002 版之後可加入圖片, 但限制每個檔案只能使用一個圖檔 工作表標籤 : 只指定列印範圍來列印部份的資料 若列印的資料太寬或太長而會跨頁, 可在每頁加上欄或列的標題, 方便閱讀列印出的報表 1. 將 成績單 工作表, 調整為一頁的輸出大小, 水平置中對齊, 頁首的中間區域輸入 國立溪湖高中 及校徽圖案, 右側區域輸入 製表人 :OOO, 頁尾的中間區域輸入 第 X 頁 2. 將 前三名圖表 工作表, 頁者的左側區域插入工作表名稱, 右側區域輸入 製作日期 :XXXX/XX/XX 預覽列印 列印之前, 應先執行預覽列印功能, 來確保列印的正確性 預覽列印後, 會顯示出分頁線 ( 虛線 ), 可直接拖曳分頁線來調整頁面的列印資料, 系統會自動調整列印的比例 第 11 頁