Excel讲义.ppt

1、Excel 講義,宋惠亮,第2頁,注意事項,成績計算： 講義、成績：.tw/hlsong E-mail：.tw,第3頁,預定進度,Excel軟體之功能 畫面說明 工作表之建立、編輯、公式之應用(配合實例) 圖表之製作 資料庫之管理 資料庫之進階管理,第4頁,畫面說明(一),工作表 視窗標題、功能表列、工具列、資料編輯列、儲存格、 填滿控點、工作表標籤、水平捲軸、垂直捲軸 儲存格中的文字靠左,而數字靠右排列,第5頁,畫面說明(二),游標之應用 ctrl+ 將游標移至當行之A欄(如X30A30) ctrl+ 將游標移至當行之IV

2、欄(如DA30IV30) ctrl+ 將游標移至當欄之第一列(如X30 x1) ctrl+ 將游標移至當欄之最後一列(如X30 x16384) ctrl+end 將游標移至本工作表最後之行與列之交集處 ctrl+home 將游標移至A1 page down/page up 將游標往後/前移一頁,第6頁,工作表之管理,儲存格內容之複製、搬移、貼上、選擇性貼上、 刪除 欄寬、列高之定義 格式欄 格式列 檔案另存新檔 檔案存檔 編輯尋找、取代、到,第7頁,儲存格內容,文字 數字 分數 : 如 0 與日期區別 日期型態資料 公式 函數 圖片 註解 超連結,第8頁,文字之應用,格式儲存格對齊 or 工具

3、列 靠左(自定值) 靠右 置中 跨欄置中 向下填滿 水平、垂直對齊 自動換列、縮小字型以適合欄寬、合併儲存格,第9頁,數字之應用,格式儲存格數字 or 工具列 G(通用)、數值、貨幣、會計專用、百分比 簡單算術運算 sum,average,count,counta,sqrt,if,countif,sumif, frequency*,rank 插入函數，選取函數類別、函數名稱 如sum(起始位置:終止位置) 先選取組界範圍,輸入公式,按ctrl+shift+enter完成 陣列內容,第10頁,日期之應用,格式儲存格數字日期 Today() Now() Year(位址) month (位址) da

4、y (位址) Date(yyyy,mm,dd) date(Year(位址),month (位址),day (位址) weekday (位址) 位址2 - 位址1 位址1 + 日數,第11頁,其他功能之應用,間距級數 自行鍵入 編輯填滿數列 選擇類型 清單 選擇清單：工具選項自訂清單確定 自訂清單：工具選項自訂清單新清單， 一一鍵入， 確定 先鍵入清單內容：工具選項自訂清單匯入， 選擇範圍確定,第12頁,常用函數彙整表,數值函數 int,mod,round,rand(),randbetween,fact,sin,cos. 文字函數 Left,right,mid,find,rept,repl,re

5、place,len,mid,right 比較兩字串exact,trim, 比較兩日期dateif(日期1,日期2,“Y”) or YM or MD,第13頁,美化報表,格式儲存格文字 or 工具列 格式儲存格 外框、圖樣 格式儲存格保護 格式欄 格式列 格式設定格式化條件 格式自動格式設定,回儲存格內容,第14頁,列印,插入分頁 檢視頁首/頁尾 檔案版面設定頁面、邊界、頁首/頁尾、 工作表 檔案列印,第15頁,工作表之保護,防止工作表中之公式或重要內容被篡改 步驟： 選定整個工作表(欄列交叉處)，格式儲存格格式保護取消鎖定之勾勾確定 選定要保護之儲存格，儲存格格式保護勾鎖定確定 格式儲存格格式

6、保護保護工作表確定 解除保護 格式儲存格格式保護解除保護工作表確定,第16頁,圖表之應用(一),插入圖表 or 圖表精靈 在.tw/hlsong 檔案區/excel/budget.xls 選取圖表 or 按右鍵(以budget.xls為例) 圖表圖表類型、選項 資料數列格式圖樣、座標軸、資料標籤、 選項,第17頁,圖表之應用(二),立體曲面圖 分別在b3:c3,a4:a45打入-3.14,-2.51 ，向右、下填滿 在b4打入 =sin(b$32+$a42)/ (b$32+$a42+0.01) ， 向右、 下填滿 .tw/

7、hlsong 檔案區/excel/股票資料檔.xls 試做股票、折線、xy散佈圖,第18頁,資料庫之應用,排序 篩選 表單 驗證 小計 運算列表 資料剖析 樞紐分析表,第19頁,排序的應用,三要素：檔案、key(關鍵值)、升/降冪排序 資料排序 以score.xls為例，試做 以學號為key，升冪排序 先將2至3人成績複製為一致， 1.以總成績為key，降冪排序，總成績相同時， 學號升冪排序 2.增加名次及排名二欄 rank(某欄位址,所有資料範圍) 3.再以學號為key，升冪排序,第20頁,篩選的應用,資料篩選 自動篩選 進階篩選 資料區 準則區 輸出區 試做 各種型態資料 And, or

8、條件句 資料庫函數 Dcount(資料清單範圍,“所欲計算的欄位(可用欄位名稱指定)”,篩選準則範圍) Dmax,dmin,daverage,dsum.,第21頁,小計的應用,資料小計 此為一維變數之統計應用，需依關鍵值(key)sort 依照班別做max,min,sum,count之練習 (數個函數在一起),第22頁,驗證的應用,資料驗證 下拉資料項目之應用，減少按鍵之錯誤 適用於少量固定性之資料 試練習品名等,第23頁,垂直查表的應用(一),資料驗證 步驟： 1.於a1鍵入被查表值 101院長室 102秘書室 103教務分處 104學務分處 105總務分處 2.於d1鍵入以下資料，如報價單

9、 日期 項目別 單位代號 單位名稱 數量 金額 承辦人 備註 08/01 傢俱 101 1 20,000 甲 08/03 電腦 101 1 50,000 甲,第24頁,垂直查表的應用(二),08/03 電腦 105 2 100,000 乙 08/05 車資 102 1 3,000 丙 08/08 傢俱 104 2 60,000 丁 08/10 車資 101 1 1,000 丙 3.於單位名稱下鍵入 vlookup(lookup_value,table_array,col_index_num,range_lookup) range_lookup: 0 表示完全符合資料 hlookup(looku

10、p_value,table_array,offset) ps:vlookup表垂直查表，hlookup表水平查表 若查詢值找不到時,顯示err之訊息 if(isna(vlookup(lookup_value,table_array,col_index_num,0),err, vlookup(lookup_value,table_array,col_index_num,0),第25頁,運算列表的應用,資料運算列表 此為依據公式，產生一或二維表格之結果 試做 10 x+x ， x2 ，x3 ，sqrt(x) 九九乘法表 貸款100,000元，年利率分別為8.5、9、9.5、10，分5年、10年、1

11、5年攤還，各期應攤還多少。 公式：PMT（年利率/12，年期數，1本金）,第26頁,其他應用,轉置矩陣 如a1:a3轉至c1:e1 選取c1:e1,在c1打入=transpose(a1:a3) ctrl +shift+ enter 合併一篇文章中每三列為三欄(須有空白欄) 1.選取該欄，常用格式欄寬如50 2.選取該欄，常用填滿(符號)左右對齊 遞增排序a-z 3.選取儲存格範圍，資料資料剖析固定欄寬下一步調整各欄位置下一步完成,第27頁,資料剖析的應用,資料資料剖析 文字檔之分割 依分界符號，如, ; “. 依固定長度,第28頁,樞紐分析表的應用,資料樞紐分析表 此為二維變數之統計應用 依照

12、班別與性別做max,min,sum,count之練習 (數個函數在一起) 樞紐分析表精靈 自行建立群組 .tw/hlsong檔案區 excel程式統計.xls,第29頁,多檔連結的應用,欲整合數個不同的檔案內容，達同步更新之效果 先建立北、中、南、東區一至三月(第一季)各產品之銷售檔，各檔案之欄、列內容須一致 再以產品與地區別產生第一季銷售量之彙總表,第30頁,特殊函數的應用,找出指定範圍內位於第幾列第幾欄儲存格之內容 index(array,row_num,column_num) 根據所設定之比對方式找出欲搜尋之值在搜尋範圍的位置 match(lookup_value,lookup_array,match_type) match_type: 1或省略 找出小於或等於搜尋值(先遞增排序)的最大值 -1 找出大於或等於搜尋值(先遞減排序)的最小值 0 找出與搜尋值(不須排序)相等的值,第31頁,增益集的應用,增益集，勾選分析工具箱確定 資料分析 以問卷調查為例，產生直方圖等統計表 規劃求解 以score.xls為例，求算final擬pass時，期末應考幾分,第32頁,巨集指令的應用,新建立 工具巨集錄製新巨集