l财务管理系统

Excel講義宋惠亮Excel財務管理系統第2頁注意事項成績計算：平時*30%期中*30%期末*40%缺席乙次扣總分2分(病假1分)點名缺席超過3次….講義、成績：~hlsongE-mail：[e-mail]連絡電話：25009374、校內分機8374Excel財務管理系統第3頁預定進度Excel軟體之功能畫面說明工作表之建立、編輯、公式之應用(配合實例)圖表之製作資料庫之管理資料庫之進階管理Excel財務管理系統第4頁畫面說明(一)工作表視窗標題、功能表列、工具列、資料編輯列、儲存格、填滿控點、工作表標籤、水平捲軸、垂直捲軸環境的改變工具選項一般、檢視、編輯儲存格中的文字靠左,而數字靠右排列Excel財務管理系統第5頁畫面說明(二)游標之應用ctrl+←將游標移至當行之A欄(如X30→A30)ctrl+→將游標移至當行之IV欄(如DA30→IV30)ctrl+↑將游標移至當欄之第一列(如X30→x1)ctrl+↓將游標移至當欄之最後一列(如X30→x16384)ctrl+end將游標移至本工作表最後之行與列之交集處ctrl+home將游標移至A1pagedown/pageup將游標往後/前移一頁Excel財務管理系統第6頁工作表之管理儲存格內容之複製、搬移、貼上、選擇性貼上、刪除欄寬、列高之定義格式欄格式列檔案另存新檔檔案存檔編輯尋找、取代、到Excel財務管理系統第7頁儲存格內容文字數字分數:如0½與日期區別日期型態資料公式函數圖片註解超連結Excel財務管理系統第8頁文字之應用格式儲存格對齊or工具列靠左(自定值)靠右置中跨欄置中向下填滿水平、垂直對齊自動換列、縮小字型以適合欄寬、合併儲存格Excel財務管理系統第9頁數字之應用格式儲存格數字or工具列G(通用)、數值、貨幣、會計專用、百分比簡單算術運算sum,average,count,counta,sqrt,if,countif,sumif,frequency*,rank插入函數，選取函數類別、函數名稱如sum(起始位置:終止位置)先選取組界範圍,輸入公式,按ctrl+shift+enter完成陣列內容Excel財務管理系統第10頁日期之應用格式儲存格數字日期Today()Now()Year(位址)month(位址)day(位址))Date(yyyy,mm,dd)date(Year(位址),month(位址),day(位址))weekday(位址)位址2-位址1位址1+日數Excel財務管理系統第11頁其他功能之應用間距級數自行鍵入編輯填滿數列選擇類型清單選擇清單：工具選項自訂清單…確定自訂清單：工具選項自訂清單新清單，一一鍵入，…確定先鍵入清單內容：工具選項自訂清單匯入，選擇範圍…確定Excel財務管理系統第12頁常用函數彙整表數值函數int,mod,round,rand(),randbetween,fact,sin,cos..文字函數Left,right,mid,find,rept,repl,replace,len,mid,right比較兩字串exact,trim,比較兩日期dateif(日期1,日期2,“Y”)orYMorMDExcel財務管理系統第13頁美化報表格式儲存格文字or工具列格式儲存格外框、圖樣格式儲存格保護格式欄格式列格式設定格式化條件格式自動格式設定回儲存格內容Excel財務管理系統第14頁列印插入分頁檢視頁首/頁尾檔案版面設定頁面、邊界、頁首/頁尾、工作表檔案列印Excel財務管理系統第15頁工作表之保護防止工作表中之公式或重要內容被篡改步驟：選定整個工作表(欄列交叉處)，格式儲存格保護取消鎖定之勾勾確定選定要保護之儲存格，儲存格保護勾鎖定確定工具保護保護工作表確定解除保護工具保護解除保護工作表確定Excel財務管理系統第16頁圖表之應用(一)插入圖表or圖表精靈在~hlsong檔案區/excel/budget.xls選取圖表or按右鍵(以budget.xls為例)圖表圖表類型、選項資料數列格式圖樣、座標軸、資料標籤、選項Excel財務管理系統第17頁圖表之應用(二)立體曲面圖分別在b3:c3,a4:a4打入-3.14,-2.51，向右、下填滿在b4打入=sin(b$3^2+$a4^2)/(b$3^2+$a4^2+0.01)，向右、下填滿~hlsong檔案區/excel/股票資料檔.xls試做股票、折線、xy散佈圖Excel財務管理系統第18頁資料庫之應用排序篩選表單驗證小計運算列表資料剖析樞紐分析表Excel財務管理系統第19頁排序的應用三要素：檔案、key(關鍵值)、升/降冪排序資料排序以score.xls為例，試做以學號為key，升冪排序先將2至3人成績複製為一致，1.以總成績為key，降冪排序，總成績相同時，學號升冪排序2.增加名次及排名二欄rank(某欄位址,所有資料範圍)3.再以學號為key，升冪排序Excel財務管理系統第20頁篩選的應用資料篩選自動篩選進階篩選資料區準則區輸出區試做各種型態資料And,or條件句資料庫函數Dcount(資料清單範圍,“所欲計算的欄位(可用欄位名稱指定)”,篩選準則範圍)Dmax,dmin,daverage,dsum….Excel財務管理系統第21頁小計的應用資料小計此為一維變數之統計應用，需依關鍵值(key)sort依照班別做max,min,sum,count…之練習(數個函數在一起)Excel財務管理系統第22頁驗證的應用資料驗證下拉資料項目之應用，減少按鍵之錯誤適用於少量固定性之資料試練習品名等Excel財務管理系統第23頁垂直查表的應用(一)資料驗證步驟：1.於a1鍵入被查表值101院長室102秘書室103教務分處104學務分處105總務分處2.於d1鍵入以下資料，如報價單日期項目別單位代號單位名稱數量金額承辦人備註08/01傢俱101120,000甲08/03電腦101150,000甲Excel財務管理系統第24頁垂直查表的應用(二)08/03電腦1052100,000乙08/05車資10213,000丙08/08傢俱104260,000丁08/10車資10111,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_index_num,0))Excel財務管理系統第25頁運算列表的應用資料運算列表此為依據公式，產生一或二維表格之結果試做10x+x，x^2，x^3，sqrt(x)九九乘法表貸款100,000元，年利率分別為8.5、9、9.5、10，分5年、10年、15年攤還，各期應攤還多少。公式：PMT（年利率/12，年＊期數，－1＊本金）Excel財務管理系統第26頁其他應用轉置矩陣如a1:a3轉至c1:e1選取c1:e1,在c1打入=transpose(a1:a3)ctrl+shift+enter合併一篇文章中每三列為三欄1.選取該欄，格式欄欄寬如502.選取該欄，編輯填滿段落重排遞增排序a-z3.選取儲存格範圍，資料資料剖析固定欄寬下一步調整各欄位置下一步完成Excel財務管理系統第27頁資料剖析的應用資料資料剖析文字檔之分割依分界符號，如,;“….依固定長度Excel財務管理系統第28頁樞紐分析表的應用資料樞紐分析表此為二維變數之統計應用依照班別與性別做max,min,sum,count…之練習(數個函數在一起)樞紐分析表精靈自行建立群組~hlsong檔案區excel程式統計.xlsExcel財務管理系統第29頁多檔連結的應用欲整合數個不同的檔案內容，達同步更新之效果先建立北、中、南、東區一至三月(第一季)各產品之銷售檔，各檔案之欄、列內容須一致再以產品與地區別產生第一季銷售量之彙總表Excel財務管理系統第30頁特殊函數的應用找出指定範圍內位於第幾列第幾欄儲存格之內容index(array,row_num,column_num)根據所設定之比對方式找出欲搜尋之值在搜尋範圍的位置match(lookup_value,lookup_array,match_type)match_type:1或省略找出小於或等於搜尋值(先遞增排序)的最大值-1找出大於或等於搜尋值(先遞減排序)的最小值0找出與搜尋值(不須排序)相等的值Excel財務管理系統第31頁增益集的應用工具增益集，勾選分析工具箱確定工具資料分析以問卷調查為例，產生直方圖等統計表工具規劃求解以score.xls為例，求算final擬pass時，期末應考幾分Excel財務管理系統第32頁巨集指令的應用新建立工具巨集錄製新巨集使用1.工具巨集巨集，選取巨集名稱2.工具列表單按紐，連結巨集名稱試做二巨集以score.xls為例，製作成績降冪排列，改變字型再以學號升冪排列，改變字型Excel財務管理系統第33頁作業自訂一至二主題以一主畫面連結各標籤(label)，其功能須有運算式排序進階篩選，至少兩例資料庫統計函數垂直查表，資料驗證運算列表小計，樞紐分析表其他圖表不能獨自一個標籤Excel財務管理系統第34頁學習愉快