Excel財務管理系統(tǒng)

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

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

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

4、)Date(yyyy,mm,dd)date(Year(位址),month (位址),day (位址)weekday (位址)位址2 - 位址1位址1 + 日數(shù)10頁其他功能之應用間距級數(shù)自行鍵入編輯填滿數(shù)列 選擇類型清單選擇清單：工具選項自訂清單確定自訂清單：工具選項自訂清單新清單， 一一鍵入， 確定先鍵入清單內(nèi)容：工具選項自訂清單匯入， 選擇範圍確定11頁常用函數(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頁美化報表格式儲存格文字 or 工具列格式儲存格 外框、圖樣格式儲存格保護格式欄格式列格式設定格式化條件格式自動格式設定回儲存格內(nèi)容13頁列印插入分頁檢視頁首/頁尾檔案版面設定頁面、邊界、頁首/頁尾、 工作表檔案列印14頁工作表之保護防止工作表中之公式或重要內(nèi)容被篡改步驟：選定整個工作表(欄列交叉處)，格式儲存格保護取消鎖定之勾勾確定選定要保護之儲存格，儲存格保護勾鎖定確定工具保護保護工作表確定解除保護工具保護解除保護工作表確定15頁圖表之應用(一)插入圖表 or 圖表精靈在http:/www.ntpu

6、.edu.tw/hlsong 檔案區(qū)/excel/budget.xls選取圖表 or 按右鍵(以budget.xls為例)圖表圖表類型、選項資料數(shù)列格式圖樣、座標軸、資料標籤、 選項 16頁圖表之應用(二)立體曲面圖分別在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頁資料庫之應用排序篩選表單驗證小計運算列表資料剖析樞紐分析表18頁排序的應用三要素：檔案、key(關(guān)鍵值)、升/降冪排序資料排序

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

8、 (數(shù)個函數(shù)在一起)21頁驗證的應用資料驗證下拉資料項目之應用，減少按鍵之錯誤適用於少量固定性之資料試練習品名等22頁垂直查表的應用(一)資料驗證步驟：1.於a1鍵入被查表值101院長室102秘書室103教務分處104學務分處105總務分處2.於d1鍵入以下資料，如報價單日期 項目別 單位代號 單位名稱 數(shù)量 金額 承辦人 備註08/01 傢俱 101 1 20,000 甲08/03 電腦 101 1 50,000 甲23頁垂直查表的應用(二)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表水平查表若查詢值找不到時,顯示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頁運算列表的應用資料運算列表此為依據(jù)公式，產(chǎn)生一或二維表格之結(jié)果試做10 x+x ， x2 ，x3 ，sqrt(x)九九乘法表貸款100,000元，年利率分別為8.5、9、9.5、10，分5年、10年、15年攤還，各期應攤還多少。公式：PMT（年利率/12，年期數(shù)，1本金）25頁其他應用轉(zhuǎn)置矩陣 如a1:a3轉(zhuǎn)至c1:e1 選取c1:e1,在c1打入=transpose(a1:a3) ctrl +shift+ enter合併一篇文章中每三列為三欄 1.選取該欄，格式欄欄寬如50 2.選取該欄，編輯填滿段落重排 遞增排序a-z 3.選取儲存格範圍，資料資料剖析固定欄寬下一步

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