1、Excel 講義宋惠亮注意事項(xiàng)成績(jī)計(jì)算：平時(shí)*30%期中*30% 期末*40%缺席乙次扣總分2分(病假1分)點(diǎn)名缺席超過3次.講義、成績(jī)：.tw/hlsongE-mail：hlsong.tw連絡(luò)電話：25009374、校內(nèi)分機(jī)83742頁(yè)預(yù)定進(jìn)度Excel軟體之功能畫面說明工作表之建立、編輯、公式之應(yīng)用(配合實(shí)例)圖表之製作資料庫(kù)之管理資料庫(kù)之進(jìn)階管理3頁(yè)畫面說明(一)工作表 視窗標(biāo)題、功能表列、工具列、資料編輯列、儲(chǔ)存格、 填滿控點(diǎn)、工作表標(biāo)籤、水平捲軸、垂直捲軸環(huán)境的改變 工具選項(xiàng)一般、檢視、編輯 儲(chǔ)存格中的文字靠左,而數(shù)字靠右排列4頁(yè)畫面說明(二)游標(biāo)

2、之應(yīng)用 ctrl+ 將游標(biāo)移至當(dāng)行之A欄(如X30A30) ctrl+ 將游標(biāo)移至當(dāng)行之IV欄(如DA30IV30) ctrl+ 將游標(biāo)移至當(dāng)欄之第一列(如X30 x1) ctrl+ 將游標(biāo)移至當(dāng)欄之最後一列(如X30 x16384) ctrl+end 將游標(biāo)移至本工作表最後之行與列之交集處 ctrl+home 將游標(biāo)移至A1 page down/page up 將游標(biāo)往後/前移一頁(yè) 5頁(yè)工作表之管理儲(chǔ)存格內(nèi)容之複製、搬移、貼上、選擇性貼上、 刪除欄寬、列高之定義格式欄格式列檔案另存新檔檔案存檔編輯尋找、取代、到6頁(yè)儲(chǔ)存格內(nèi)容文字?jǐn)?shù)字分?jǐn)?shù) : 如 0 與日期區(qū)別日期型態(tài)資料公式函數(shù)圖片註解超連

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

4、)Date(yyyy,mm,dd)date(Year(位址),month (位址),day (位址)weekday (位址)位址2 - 位址1位址1 + 日數(shù)10頁(yè)其他功能之應(yīng)用間距級(jí)數(shù)自行鍵入編輯填滿數(shù)列 選擇類型清單選擇清單：工具選項(xiàng)自訂清單確定自訂清單：工具選項(xiàng)自訂清單新清單， 一一鍵入， 確定先鍵入清單內(nèi)容：工具選項(xiàng)自訂清單匯入， 選擇範(fàn)圍確定11頁(yè)常用函數(shù)彙整表數(shù)值函數(shù)int,mod,round,rand(),randbetween,fact,sin,cos.文字函數(shù)Left,right,mid,find,rept,repl,replace,len,mid,right比較兩字串exa

5、ct,trim,比較兩日期dateif(日期1,日期2,“Y”) or YM or MD12頁(yè)美化報(bào)表格式儲(chǔ)存格文字 or 工具列格式儲(chǔ)存格 外框、圖樣格式儲(chǔ)存格保護(hù)格式欄格式列格式設(shè)定格式化條件格式自動(dòng)格式設(shè)定回儲(chǔ)存格內(nèi)容13頁(yè)列印插入分頁(yè)檢視頁(yè)首/頁(yè)尾檔案版面設(shè)定頁(yè)面、邊界、頁(yè)首/頁(yè)尾、 工作表檔案列印14頁(yè)工作表之保護(hù)防止工作表中之公式或重要內(nèi)容被篡改步驟：選定整個(gè)工作表(欄列交叉處)，格式儲(chǔ)存格保護(hù)取消鎖定之勾勾確定選定要保護(hù)之儲(chǔ)存格，儲(chǔ)存格保護(hù)勾鎖定確定工具保護(hù)保護(hù)工作表確定解除保護(hù)工具保護(hù)解除保護(hù)工作表確定15頁(yè)圖表之應(yīng)用(一)插入圖表 or 圖表精靈在http:/www.ntpu

6、.edu.tw/hlsong 檔案區(qū)/excel/budget.xls選取圖表 or 按右鍵(以budget.xls為例)圖表圖表類型、選項(xiàng)資料數(shù)列格式圖樣、座標(biāo)軸、資料標(biāo)籤、 選項(xiàng) 16頁(yè)圖表之應(yīng)用(二)立體曲面圖分別在b3:c3,a4:a4打入-3.14,-2.51 ，向右、下填滿在b4打入 =sin(b$32+$a42)/ (b$32+$a42+0.01) ， 向右、 下填滿.tw/hlsong檔案區(qū)/excel/股票資料檔.xls試做股票、折線、xy散佈圖17頁(yè)資料庫(kù)之應(yīng)用排序篩選表單驗(yàn)證小計(jì)運(yùn)算列表資料剖析樞紐分析表18頁(yè)排序的應(yīng)用三要素：檔案、key(關(guān)鍵值)、升/降冪排序資料排序

7、 以score.xls為例，試做以學(xué)號(hào)為key，升冪排序先將2至3人成績(jī)複製為一致， 1.以總成績(jī)?yōu)閗ey，降冪排序，總成績(jī)相同時(shí)， 學(xué)號(hào)升冪排序 2.增加名次及排名二欄 rank(某欄位址,所有資料範(fàn)圍) 3.再以學(xué)號(hào)為key，升冪排序19頁(yè)篩選的應(yīng)用資料篩選自動(dòng)篩選進(jìn)階篩選資料區(qū)準(zhǔn)則區(qū)輸出區(qū)試做各種型態(tài)資料And, or 條件句資料庫(kù)函數(shù)Dcount(資料清單範(fàn)圍,“所欲計(jì)算的欄位(可用欄位名稱指定)”,篩選準(zhǔn)則範(fàn)圍)Dmax,dmin,daverage,dsum.20頁(yè)小計(jì)的應(yīng)用資料小計(jì)此為一維變數(shù)之統(tǒng)計(jì)應(yīng)用，需依關(guān)鍵值(key)sort依照班別做max,min,sum,count之練習(xí)

8、 (數(shù)個(gè)函數(shù)在一起)21頁(yè)驗(yàn)證的應(yīng)用資料驗(yàn)證下拉資料項(xiàng)目之應(yīng)用，減少按鍵之錯(cuò)誤適用於少量固定性之資料試練習(xí)品名等22頁(yè)垂直查表的應(yīng)用(一)資料驗(yàn)證步驟：1.於a1鍵入被查表值101院長(zhǎng)室102秘書室103教務(wù)分處104學(xué)務(wù)分處105總務(wù)分處2.於d1鍵入以下資料，如報(bào)價(jià)單日期 項(xiàng)目別 單位代號(hào) 單位名稱 數(shù)量 金額 承辦人 備註08/01 傢俱 101 1 20,000 甲08/03 電腦 101 1 50,000 甲23頁(yè)垂直查表的應(yīng)用(二)08/03 電腦 105 2 100,000 乙08/05 車資 102 1 3,000 丙08/08 傢俱 104 2 60,000 丁08/10 車

9、資 101 1 1,000 丙3.於單位名稱下鍵入vlookup(lookup_value,table_array,col_index_num,range_lookup) range_lookup: 0 表示完全符合資料hlookup(lookup_value,table_array,offset) ps:vlookup表垂直查表，hlookup表水平查表若查詢值找不到時(shí),顯示err之訊息 if(isna(vlookup(lookup_value,table_array,col_index_num,0),err, vlookup(lookup_value,table_array,col_ind

10、ex_num,0)24頁(yè)運(yùn)算列表的應(yīng)用資料運(yùn)算列表此為依據(jù)公式，產(chǎn)生一或二維表格之結(jié)果試做10 x+x ， x2 ，x3 ，sqrt(x)九九乘法表貸款100,000元，年利率分別為8.5、9、9.5、10，分5年、10年、15年攤還，各期應(yīng)攤還多少。公式：PMT（年利率/12，年期數(shù)，1本金）25頁(yè)其他應(yīng)用轉(zhuǎn)置矩陣 如a1:a3轉(zhuǎn)至c1:e1 選取c1:e1,在c1打入=transpose(a1:a3) ctrl +shift+ enter合併一篇文章中每三列為三欄 1.選取該欄，格式欄欄寬如50 2.選取該欄，編輯填滿段落重排 遞增排序a-z 3.選取儲(chǔ)存格範(fàn)圍，資料資料剖析固定欄寬下一步

11、調(diào)整各欄位置下一步完成26頁(yè)資料剖析的應(yīng)用資料資料剖析文字檔之分割依分界符號(hào)，如, ; “.依固定長(zhǎng)度27頁(yè)樞紐分析表的應(yīng)用資料樞紐分析表此為二維變數(shù)之統(tǒng)計(jì)應(yīng)用依照班別與性別做max,min,sum,count之練習(xí) (數(shù)個(gè)函數(shù)在一起)樞紐分析表精靈自行建立群組 .tw/hlsong檔案區(qū) excel程式統(tǒng)計(jì).xls28頁(yè)多檔連結(jié)的應(yīng)用欲整合數(shù)個(gè)不同的檔案內(nèi)容，達(dá)同步更新之效果先建立北、中、南、東區(qū)一至三月(第一季)各產(chǎn)品之銷售檔，各檔案之欄、列內(nèi)容須一致再以產(chǎn)品與地區(qū)別產(chǎn)生第一季銷售量之彙總表29頁(yè)特殊函數(shù)的應(yīng)用找出指定範(fàn)圍內(nèi)位於第幾列第幾欄儲(chǔ)存格之內(nèi)容index(array,row_num,column_num)根據(jù)所設(shè)定之比對(duì)方式找出欲搜尋之值在搜尋範(fàn)圍的位置match(lookup_value,lookup_array,match_type)match_type: 1或省略 找出小於或等於搜尋值(先遞增排序)的最大值 -1 找出大於或等於搜尋值(先遞減排序)的最小值 0 找出與搜尋值(不須排序)相等的值30頁(yè)增益集的應(yīng)用工具增益集，勾選分析工具箱確定工具資料分析 以問卷調(diào)查為例，產(chǎn)生直方圖等統(tǒng)計(jì)表工具規(guī)劃求解 以score.xls為例，求算final擬pass時(shí)，期末應(yīng)考幾分31頁(yè)巨集指令的應(yīng)用新建