Excel常用函数的应用

1,Office應用之Excel篇,第9章製作統計分析圖表,2,第9章製作統計分析圖表,9-1介紹常用函數9-2應用範圍名稱9-3提示訊息功能9-4製作圖表功能9-5製作樞紐分析圖表,9-6編修樞紐分析表9-7綜合範例-學生成績單9-8綜合範例-銷售業績圖表,3,9-1介紹常用函數,Excel提供共九大類別，將近三百個函數財務函數：NPV、PMT、RATE、IRR日期與時間函數：DATE、TODAY數學與三角函數：SIN、COS、ABS統計函數：AVERAGE、TTEST、VAR檢視與參照函數：ADDRESS、VLOOKUP資料庫和清單管理函數：DMAX、DMIN文字函數：ASC、LEN、VALUE、TEXT邏輯函數：IF、FALSE、AND、OR、NOT資訊函數函數：INFO、CELL、TYPE,4,9-1介紹常用函數,5,9-1介紹常用函數,6,9-1介紹常用函數,7,第9章製作統計分析圖表,9-1介紹常用函數9-2應用範圍名稱9-3提示訊息功能9-4製作圖表功能9-5製作樞紐分析圖表,9-6編修樞紐分析表9-7綜合範例-學生成績單9-8綜合範例-銷售業績圖表,8,9-2應用範圍名稱,EXCEL儲存格位置可分為相對位置及絕對位置兩種，當使用填滿控點填滿公式時，公式參照的儲存格位置會隨著相對改變，如果需要使用絕對位置時，一般可用鍵盤上F4鍵將儲存格位置鎖定。不過將這些固定參照位置定義範圍名稱，就不用擔心相對位置的問題。,9,9-2應用範圍名稱,9-2-1建立範圍名稱9-2-2定義範圍名稱9-2-3貼上範圍名稱,10,9-2-1建立範圍名稱,通常用於有標題列或標題欄的表格式儲存格範圍，一次可以同時建立多個範圍名稱。,選取B2:I11儲存格,1,2,11,9-2-2定義範圍名稱,使用定義範圍名稱指令也可以建立範圍名稱，通常是用於沒有標題列或欄的儲存格範圍。同時也可以看到已經建立過的範圍名稱及其儲存格範圍，並可以在此進行新增或編修範圍名稱。若要刪除範圍名稱，必須先選取要變更的名稱，按刪除鈕即可。若要變更名稱或修改參照位置，也是先選取要變更的名稱，再直接修改名稱或參照儲存格位置。,1,12,9-2-3貼上範圍名稱,使用建立和定義的範圍名稱，如果沒有配合貼上指令，似乎也是無用武之地只要是需要參照儲存格位置的EXCEL功能，都可以搭配使用範圍名稱例如：函數公式、資料驗證等。,13,第9章製作統計分析圖表,9-1介紹常用函數9-2應用範圍名稱9-3提示訊息功能9-4製作圖表功能9-5製作樞紐分析圖表,9-6編修樞紐分析表9-7綜合範例-學生成績單9-8綜合範例-銷售業績圖表,14,9-3提示訊息功能,9-3-1設定格式化條件9-3-2資料驗證功能9-3-3插入註解功能,15,9-3-1設定格式化條件,當編輯活頁簿檔案資料時，可以設定一些提示訊息，告知使用者使用檔案時需要注意的事項，與使用者產生互動關係格式化條件主要當指定儲存格被輸入特定條件的資料時，透過儲存格格式的變化，提醒使用者該儲存格符合特定條件例如A1儲存格被輸入數值7時，儲存格格式就會自動變成紅色的粗體字。,1,16,9-3-2資料驗證功能,資料驗證功能最主要是用來設定儲存格可以輸入的資料內容，若不符合可輸入條件，便出現錯誤提示訊息，錯誤提醒樣式分成三種。,9-3-2資料驗證功能,設定儲存格可以輸入的資料內容，若不符合條件，便出現錯誤提示訊息，錯誤提醒樣式分成三種。,18,9-3-3插入註解功能,註解也是一種不錯的提示訊息，不需要給予特別條件，隨時隨地都能提供重要訊息。使用方法先選取要顯示註解的儲存格，再執行插入註解指令，在註解方塊中輸入相關提示訊息。可移動、隱藏、編輯註解格式,1,19,補充、欄位隱藏、凍結與列印公式,欄位隱藏：按右鍵，直接往左拖曳隱藏同時選取兩欄(列)按右鍵取消隱藏凍結欄位：使重要的欄位不被隱藏起來工具選項檢視勾選顯示列印公式設定密碼：檔案另存新檔右上角之工具一般選項(安全選項)設定密碼,20,第9章製作統計分析圖表,9-1介紹常用函數9-2應用範圍名稱9-3提示訊息功能9-4製作圖表功能9-5製作樞紐分析圖表,9-6編修樞紐分析表9-7綜合範例-學生成績單9-8綜合範例-銷售業績圖表,21,9-4製作圖表功能,Excel圖表功能可以把工作表上的數據資料，轉換製作成專業圖表，以便使用者比較差異，分析數據資料。EXCEL預設的圖表類型有直條圖、橫條圖、折線圖、圓形圖、XY散佈圖、區域圖、環圈圖、雷達圖、曲面圖、泡泡圖、股票圖、圓柱圖、圓錐圖及金字塔圖共14種每一種類下面還有27個不等的副圖表類型，可說是相當豐富且專業。,22,9-4製作圖表功能,9-4-1插入標準類型圖表9-4-2圖表格式設定9-4-3修改圖表區格式9-4-4變更繪圖區底色9-4-5修改座標軸標題樣式9-4-6開啟及關閉資料表格和圖例9-4-7變更及檢視立體圖表9-4-8自訂圖表類型,23,9-4-2圖表格式設定,直條圖,以堆疊方式顯示,25,橫條圖,26,折線圖,27,圓形圖,28,散佈圖,29,區域圖,30,環圈圖,31,雷達圖,32,曲面圖,33,股票圖,34,泡泡圖,35,圓柱圖,36,折線圖加直條圖,37,含多個軸的圖表(雙軸折線圖加直條圖),直線圖是參考左邊的內外銷軸，折線圖是參考右邊的總計數量軸(此軸又稱覆疊軸)。,38,第9章製作統計分析圖表,9-1介紹常用函數9-2應用範圍名稱9-3提示訊息功能9-4製作圖表功能9-5製作樞紐分析圖表,9-6編修樞紐分析表9-7綜合範例-學生成績單9-8綜合範例-銷售業績圖表,39,9-5製作樞紐分析圖表,樞紐分析表是一種互動式的資料表，依照使用者的需求製作而成，具有強大的統計及分析能力。樞紐分析表是由四種元件組合而成，分別為欄、列、資料及分頁。,40,9-5製作樞紐分析圖表,41,補充、交叉資料表查詢,將數值資料經過整理分析後，建立具有欄、列標題且於中間交錯的儲存格中，顯示與對應欄、列有關的資料。單純的交叉資料表查詢，含有三個欄位欄和列標題可以再擁有子標題，而得到多層次的交叉資料表(最多三個層次)。又稱為交叉分析或樞紐分析,42,實際案例2004民意調查,43,實際案例2004民意調查,44,第9章製作統計分析圖表,9-1介紹常用函數9-2應用範圍名稱9-3提示訊息功能9-4製作圖表功能9-5製作樞紐分析圖表,9-6編修樞紐分析表9-7綜合範例-學生成績單9-8綜合範例-銷售業績圖表,45,9-6編修樞紐分析表,9-6-1設定資料區域數值格式9-6-2變更資料區域位置9-6-3增加計算欄位9-6-4套用報表格式9-6-5更新資料9-6-6建立樞紐分析圖,46,第9章製作統計分析圖表,9-1介紹常用函數9-2應用範圍名稱9-3提示訊息功能9-4製作圖表功能9-5製作樞紐分析圖表,9-6編修樞紐分析表9-7綜合範例-學生成績單9-8綜合範例-銷售業績圖表,47,9-7綜合範例學生成績單,學生成績單主要著重在分數的加總、平均等函數公式的運用，配合不及格分數提示訊息的資訊，最後再加上各科分數比較圖。請開啟範例檔成績表1.xls：,48,49,50,第9章製作統計分析圖表,9-1介紹常用函數9-2應用範圍名稱9-3提示訊息功能9-4製作圖表功能9-5製作樞紐分析圖表,9-6編修樞紐分析表9-7綜合範例-學生成績單9-8綜合範例-銷售業績圖表,51,9-8綜合範例銷售業績圖表,銷售業績圖表最主要是以樞紐分析表及圖報表為主，統計一季的銷售金額及數量，並分析比較各分店之間的差異，還能以月作為比較的基準。,53,54,假設本季咖啡五折特價促銷，實際銷售金額為原來一半，請開啟銷費業績圖表2.xls更新資料,55,本章學習評量,1.請開啟練習檔體重紀錄.xls利用工作表數據製作一張蜜蜂寶寶體重變化圖。,56,57,2.請開啟練習檔銷售統計.xls，利用工作表數據製作一張銷售統計表及圖報表,58,59,60,3.請開啟錄取人數.xls範例檔，並依照底下提示製作一份樞紐分析圖表,利用SUM函數，計算表單中的所有總計項目設定C3：G7範圍內的儲存