Excel 2007 輕鬆學 Chun-Nan Lin PhD
前言 辦公室自動化軟體對許多人而言, 已不是陌生的任務, 但如何適當應用在日常生活與工作中, 就不是件容易的事 本課程將介紹辦公室自動化軟體中, 相當常見的 MS Excel, 包含其基礎操作 函數的應用 圖表製作等 Excel 是 Office 家族裡面的試算表軟體, 它除了可以快速整合資料明細, 還能進一步整理出相關的數據 圖表, 對於許多人來說是不可或缺的工具程式 Excel 目前的最新版本是 2013
Excel 2007 基本操作
Excel 2007 基本介面 功能區 儲存格 內容輸入區 行 (A~XFD) 列 (1~1,048,576) 活頁簿切換 ( 預設為三頁 ) 縮放比例
功能區分三個部份 : 索引標籤 群組和指令 索引標籤 :Excel 上方共有七個索引標籤, 每個索引標籤各代表您在 Excel 中進行的主要工作 群組 : 每個索引標籤都有一些群組, 顯示相關項目 命令 : 命令可能是按鈕 可供您輸入資訊的方塊, 或是功能表
Excel 2007 並不會一直顯示所有的命令, 而是根據您採取的動作, 顯示一些您可能會需要的命令
對話方塊啟動器 在群組的右下角看到箭號符號時 ( 對話方塊啟動器 ), 表示這個群組有更多選項可以使用
檢視 - 整頁模式 在整頁模式中, 可以看見工作表上 下 左 右的頁面邊界, 工作表之間則以藍色的空間隔開 活用不同檢視模式的好處 例如 : 整頁模式可以容易的新增頁首與頁尾等
輸入公式 ( 加總 ) 按鈕位在常用索引標籤的編輯群組中 請將游標放置要加總的最後一個儲存格, 然後按一下 按紐, 確認範圍後再按 Enter
新增頁首及頁尾 先切換為整頁模式 直接在下圖紅色區域按一下滑鼠左鍵, 以鍵入文字
列印 請用版面配置索引標籤來微調您的列印選項 方向 : 選擇直向或橫向 大小 : 選擇紙張大小
新增活頁簿 依序按下 Microsoft Office 按鈕和新增時, 新增活頁簿的視窗就會開啟
開始使用 Excel - 以學生通訊錄為例 學生通訊錄的表格在建置時, 只要規劃好每一欄的資料類別, 學生依照欄位的規定進行填寫即可完成 但在預設的儲存格中輸入文字資料後, 會發生如下文字無法完全顯示的情況
此範例將幫助我們熟悉資料的編輯動作, 並了解不同的資料欄位在輸入時的注意事項
輸入學生通訊錄的資料 在儲存格中輸入資料 首先要輸入學生通訊錄的標題, 再輸入欄位的名稱, 最後輸入學生的資料 在選取要輸入資料的儲存格後, 即可進行資料輸入的動作 完成輸入的動作後, 可以按 Enter 鍵完成資料的輸入 接著要輸入學生通訊錄的欄位標題, 請依序輸入學號 姓名 性別 生日 電話 住址 Email 接著要開始輸入學生的資料, 請先輸入第一筆資料
調整欄位寬度 在輸入資料的過程中會發現有些資料的長度會大於儲存格的寬度, 所以需進行調整 使用手動的方式來調整欄位的寬度 自動依內容來調整寬度 直接在要調整寬度的欄名間連點滑鼠左鍵兩下
自動填滿的使用 需要在連續的儲存格中填入連續的數字 編號, 或是有相關的文字, 此時即可使用自動填滿的功能 選取已填入座號的 A4 儲存格, 將滑鼠移到儲存格右下角的填滿控點上, 此時游標會顯示為 + 按著 Ctrl 鍵不放往下拖曳到 A13 儲存格 放開滑鼠左鍵後 Excel 即會在這個區域內填入連續的數列 PS: 如果沒按著 Ctrl 鍵, 則會自動填滿 相同 的資料
設定儲存格的資料格式 儲存格中所輸入的內容會因為不同的資料型態而有不同的顯示方法 例如 : 在目前的學生通訊錄中, 電話號碼即被視為數字, 這裡即要進行資料格式的調整 請依序將所有同學的資料輸入完畢, 接著請選取第一個學生的電話資料儲存格 : E4, 先檢視它的資料內容
設定儲存格的資料格式 在儲存格選取的狀態下, 開啟儲存格格式對話方塊 在儲存格格式對話方中選按數值標籤, 接著設定儲存格格式為特殊類別的行動電話 呼叫器號碼 如此即完成學生通訊錄資料輸入的動作, 請於檔案索引標籤選按儲存檔案, 設定檔名為 學生通訊錄.xlsx 後按儲存鈕完成存檔
設定儲存格文字格式 剛輸入資料的儲存格看起來是很清爽的, 這裡要設定儲存格中的文字格式, 以增加資料的閱讀性 : 選取標題文字的儲存後進行文字格式的設定 選擇設定標題文字的範圍後, 選按跨欄置中鈕
插入表格 在 Excel 中可以設定某個區域的儲存格資料為表格, Excel 即會將之視為資料群組, 除了可以設定整體格式之外, 還能對其中的資料進行處理 選取含有資料的儲存格範圍 於插入索引標籤選按表格 在對話框中按確定
自動設定表格名稱 自動套用表格樣式 篩選鈕 若不再需要表格所帶來的功能, 要如何將表格轉換為一般範圍呢? 選取表格中任一儲存格後於資料表工具 \ 設計索引標籤選按轉換為範圍, 接著在對話方塊中按是鈕
列印工作表 於檔案索引標籤選按列印即可進行列印設定的畫面
進階的版面設定 請先修改紙張方向後, 進入版面設定的對話方塊 修改紙張方向為橫向方向 選按版面設定進入對話方塊
版面設定對方塊中, 可設定頁面 邊界 頁首 / 頁尾 工作表等選項
Excel 2007 公式與函數
公式與函數 公式與函數是 Excel 中十分重要功能, 它能在儲存格中顯示儲存格之間或是指定範圍 區域中即時運算的結果 在成績單裡常要計算的內容有各科的總分 平均分數 分數排名, 並依所呈現的分數來顯示等級, 因此我們將利用此範例將應用不同的公式或是函數進行計算
Excel 共有四種不同的運算子 算術運算子 比較運算子 + - * / % ^ 加號減號乘號除號百分比號次方符號 ( 乘羃 ) = > < >= <= <> 等於大於小於大於或等於小於或等於不等於 參照運算子 文字運算子 :, ( 空白 ) 範圍運算子, 可將參照位置擴大到兩個參照位置之間聯合運算子, 可將多個參照位置結合成一個參照位置交集運算子, 產生由兩個參照交集部分的儲存格參照 & 連結兩個值, 產生一個連續文字值
Excel 中常用的函數 函數 說明 應用方法 COUNT 數量 =COUNT( 計算範圍 ) SUM 加總 =SUM( 計算範圍 ) AVERAGE 平均數 =AVERAGE( 計算範圍 ) INT 整數 =INT( 數值 ) ROUND 四捨五入 =ROUND( 數值, 小數點的指定位數 ) MAX 最大值 =MAX( 計算範圍 ) MIN 最小值 =MIN( 計算範圍 ) RANK 排序值 =RANK( 尋找值, 參照範圍 ) PMT 借款利息 =PMT( 本金, 利率, 期數 ) IF 真假值 =IF( 條件式, 真值, 假植值 ) VLOOKUP 垂直查詢 =VLOOKUP( 尋找值, 查詢表範圍, 顯示出的欄數 ) HLOOKUP 水平查詢 =HLOOKUP( 尋找值, 查詢表範圍, 顯示出的列數 )
加入 SUM 函數計算加總 語法 :SUM( 計算範圍 ) 請將作用儲存格移動到 F3, 接著按資料編輯列的插入函數鈕進入對話方塊 選取 SUM 函數並設定使用的引數, 最後按確定鈕 也可以直接於儲存格中輸入函數
加入 AVERAGE 函數計算平均 語法 :AVERAGE( 計算範圍 ) 請將作用儲存格移動到 G3, 接著按資料編輯列的插入函數鈕進入對話方塊 選取 AVERAGE 函數並設定引數後, 請按確定鈕完成設定 也可以直接於儲存格中輸入函數
加入 ROUND 函數設定四捨五入 語法 :ROUND( 數值, 小數點的指定位數 ) 請選取 G3 儲存格, 請在原來的 AVERAGE 函數外, 再加入 ROUND 函數, 並指定顯示在小數第 2 位
加入 RANK 函數設定名次 語法 :RANK( 尋找值, 參照範圍 ) 請將作用儲存格移動到 H3, 接著按資料編輯列的插入函數鈕進入對話方塊 選取 RANK 函數後利用選取儲存格範圍鈕來設定引數
加入 VLOOKUP 函數設定等級 語法 :VLOOKUP( 尋找值, 查詢表範圍, 值的間隔數 ) 請將作用儲存格移動到 I3, 接著按資料編輯列的插入函數鈕進入對話方塊 選取 VLOOKUP 函數後利用選取儲存格範圍鈕來設定引數
設定格式化條件 若希望在成績單上用紅字標示出不及格的成績, 可以使用格式化條件的功能 格式化條件就是為選取的範圍加上條件, 符合的以設定的格式來顯示, 如此即能將有差異性的資料標示出來 選取填寫成績的儲存格範圍後開始設定格式化的條件 常用 \ 樣式 \ 設定格式化的條件 \ 醒目提示儲存格規則 \ 小於
設定條件值, 以及顯示出的效果後, 按確定即可
資料排序與篩選 如果想以國文成績由最低分排到最高分, 或篩選出前 3 名的學生名單, 可以利用資料排序與篩選的功能 資料排序 請選取表格範圍, 於資料索引標籤選按篩選表頭欄位會顯示自動篩選鈕
資料篩選 如果要篩選出前 3 名的同學名單, 請選按名次欄位的自動篩選鈕後選取數字篩選 \ 小於或等於, 設定名次小於或等於 3 的條件後按確定
Excel 2007 圖表製作
一圖勝萬言 圖表主要的功能是將數值資料轉換為圖形, 而 Excel 圖表包含了代表整個圖表的圖表區與代表圖表主體的繪圖區 圖表區是由圖表標題 座標軸標題 繪圖區以及圖例等組成, 而繪圖區則是由座標軸 資料數列等組成
常見圖表類型 類型直線圖折線圖圓形圖橫條圖區域圖 XY 散佈圖其他圖表 說明 可比較資料間的相差情形, 類別置於水平軸 值於垂直軸 用描點方式繪製資料再連結, 表示某時間的趨勢變化 只能顯示一組資料數列, 表現出各項目相較於全體資料的比例 與直條圖相似, 主要強調每個項目間的比較情況 強調一段時間內的變化幅度, 用面積大小表示其與整體間的關係 顯示多組資料數列彼此間的關聯性, 以 X 與 Y 座標值繪製, 可快速比較兩組資料間的差距 股票圖 曲面圖 環圈圖 泡泡圖 雷達圖
圖表的新增
圖表的搬移 圖表工具 \ 設計 \ 移動圖表在工作表中的物件, 選擇工作表 2 後按確定
調整圖表位置並設定圖表長寬 在工作表 2 中選取圖表, 移至工作表 2 的左上角對齊 依照資料量多寡或欲列印的紙張規格, 設定圖表最佳瀏覽的長寬比例 ( 圖表工具 \ 格式 \ 高度 :9 公分 寬度 :18.5 公分 )
套用預設圖表樣式 選取圖表後, 於圖表工具 \ 設計索引標籤選按其他清單鈕 \ 樣式 26
在圖表中新增一筆資料 新增圖表來源資料
1. 選擇工作表 1 的 A5 儲存格 2. 選擇工作表 1 的 B5:H5 儲存格範圍
變更單筆資料的圖表類型 已插入工作表中的圖表並非就此定型, 在此將為指定數列套用不同的圖表類型, 以突顯其數值, 請選取成長率 % 資料後, 變更其為 折線圖 圖表類型
選擇折線圖 \ 含有資料標記的折線圖
設定單筆資料的資料數列格式 因為此組數列與其他數列的數值相距甚大, 所以無法清楚顯示出來, 這時就需以副座標軸的方式來解決此問題 副座標軸就是在圖表右側增加第二個數值 Y 軸 ( 僅平面類型圖表可適用 ), 副座標軸即會依相關資料數列的數值調整其刻度
於標記選項中選擇內建, 並設定類型 : 圓形 大小 :10 於線條樣式中設定寬度 :3pt
圖表的配色 1. 先選擇任一欲變更的數列 2. 於圖表工具 \ 格式選按其他, 選擇您喜好的樣式套用即可
設定單筆資料的資料標籤格式 資料標籤主要是標示於資料數列上的相關數據值, 讓圖表資料更容易理解
新增圖表與座標軸標題文字 文字標題能概括內容, 協助瀏覽者了解圖表所要表達的意念
鍵入 留學人數統計表
1. 於圖表工具 \ 版面配置選按座標軸標題 \ 主垂直軸標題 \ 垂直標題 2. 鍵入 人數
美化圖表標題文字 若欲調整圖表內特定的文字物件樣式, 可於選取該文字物件後再進行設定, 也可搭配常用字型標籤 \ 字型區中的格式設定功能, 讓圖表文字更有特色
圖表工具 \ 格式 \ 快速樣式也可以美化文字
新增運算列表 在統計圖表下方加入原數據表格, 其多重的顯示方式讓使用者更容易檢視結果
右側與下方的圖例重複!! 因此我們在此取消右側的圖例於圖表工具 \ 版面配置 \ 圖例 \ 無
參考資料 1. 亞洲大學蒲公英計劃簡報 2. 2010 Office 高效實用範例必修 16 課