l财务管理系统

Excel講義,宋惠亮,第2頁,注意事項,成績計算：平時*30%期中*30%期末*40%缺席乙次扣總分2分(病假1分)點名缺席超過3次.講義、成績：.tw/hlsongE-mail：.tw連絡電話：25009374、校內分機8374,第3頁,預定進度,Excel軟體之功能畫面說明工作表之建立、編輯、公式之應用(配合實例)圖表之製作資料庫之管理資料庫之進階管理,第4頁,畫面說明(一),工作表視窗標題、功能表列、工具列、資料編輯列、儲存格、填滿控點、工作表標籤、水平捲軸、垂直捲軸環境的改變工具選項一般、檢視、編輯儲存格中的文字靠左,而數字靠右排列,第5頁,畫面說明(二),游標之應用ctrl+將游標移至當行之A欄(如X30A30)ctrl+將游標移至當行之IV欄(如DA30IV30)ctrl+將游標移至當欄之第一列(如X30x1)ctrl+將游標移至當欄之最後一列(如X30x16384)ctrl+end將游標移至本工作表最後之行與列之交集處ctrl+home將游標移至A1pagedown/pageup將游標往後/前移一頁,第6頁,工作表之管理,儲存格內容之複製、搬移、貼上、選擇性貼上、刪除欄寬、列高之定義格式欄格式列檔案另存新檔檔案存檔編輯尋找、取代、到,第7頁,儲存格內容,文字數字分數:如0與日期區別日期型態資料公式函數圖片註解超連結,第8頁,文字之應用,格式儲存格對齊or工具列靠左(自定值)靠右置中跨欄置中向下填滿水平、垂直對齊自動換列、縮小字型以適合欄寬、合併儲存格,第9頁,數字之應用,格式儲存格數字or工具列G(通用)、數值、貨幣、會計專用、百分比簡單算術運算sum,average,count,counta,sqrt,if,countif,sumif,frequency*,rank插入函數，選取函數類別、函數名稱如sum(起始位置:終止位置)先選取組界範圍,輸入公式,按ctrl+shift+enter完成陣列內容,第10頁,日期之應用,格式儲存格數字日期Today()Now()Year(位址)month(位址)day(位址)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,replace,len,mid,right比較兩字串exact,trim,比較兩日期dateif(日期1,日期2,“Y”)orYMorMD,第13頁,美化報表,格式儲存格文字or工具列格式儲存格外框、圖樣格式儲存格保護格式欄格式列格式設定格式化條件格式自動格式設定,回儲存格內容,第14頁,列印,插入分頁檢視頁首/頁尾檔案版面設定頁面、邊界、頁首/頁尾、工作表檔案列印,第15頁,工作表之保護,防止工作表中之公式或重要內容被篡改步驟：選定整個工作表(欄列交叉處)，格式儲存格保護取消鎖定之勾勾確定選定要保護之儲存格，儲存格保護勾鎖定確定工具保護保護工作表確定解除保護工具保護解除保護工作表確定,第16頁,圖表之應用(一),插入圖表or圖表精靈在.tw/hlsong檔案區/excel/budget.xls選取圖表or按右鍵(以budget.xls為例)圖表圖表類型、選項資料數列格式圖樣、座標軸、資料標籤、選項,第17頁,圖表之應用(二),立體曲面圖分別在b3:c3,a4:a4打入-3.14,-2.51，向右、下填滿在b4打入=sin(b$32+$a42)/(b$32+$a42+0.01)，向右、下填滿.tw/hlsong檔案區/excel/股票資料檔.xls試做股票、折線、xy散佈圖,第18頁,資料庫之應用,排序篩選表單驗證小計運算列表資料剖析樞紐分析表,第19頁,排序的應用,三要素：檔案、key(關鍵值)、升/降冪排序資料排序以score.xls為例，試做以學號為key，升冪排序先將2至3人成績複製為一致，1.以總成績為key，降冪排序，總成績相同時，學號升冪排序2.增加名次及排名二欄rank(某欄位址,所有資料範圍)3.再以學號為key，升冪排序,第20頁,篩選的應用,資料篩選自動篩選進階篩選資料區準則區輸出區試做各種型態資料And,or條件句資料庫函數Dcount(資料清單範圍,“所欲計算的欄位(可用欄位名稱指定)”,篩選準則範圍)Dmax,dmin,daverage,dsum.,第21頁,小計的應用,資料小計此為一維變數之統計應用，需依關鍵值(key)sort依照班別做max,min,sum,count之練習(數個函數在一起),第22頁,驗證的應用,資料驗證下拉資料項目之應用，減少按鍵之錯誤適用於少量固定性之資料試練習品名等,第23頁,垂直查表的應用(一),資料驗證步驟：1.於a1鍵入被查表值101院長室102秘書室103教務分處104學務分處105總務分處2.於d1鍵入以下資料，如報價單日期項目別單位代號單位名稱數量金額承辦人備註08/01傢俱101120,000甲08/03電腦101150,000甲,第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),第25頁,運算列表的應用,資料運算列表此為依據公式，產生一或二維表格之結果試做10 x+x，x2，x3，sqrt(x)九九乘法表貸款100,000元，年利率分別為8.5、9、9.5、10，分5年、10年、15年攤還，各期應攤還多少。公式：PMT（年利率/12，年期數，1本金）,第26頁,其他應用,轉置矩陣如a1:a3轉至c1:e1選取c1:e1,在c1打入=transpose(a1:a3)ctrl+shift+enter合併一篇文章中每三列為三欄1.選取該欄，格式欄欄寬如502.選取該欄，編輯填滿段落重排遞增排序a-z3.選取儲存格範圍，資料資料剖析固定欄寬下一步調整各欄位置下一步完成,第27頁,資料剖析的應用,資料資料剖析文字檔之分割依分界符號，如,;“.依固定長度,第28頁,樞紐分析表的應用,資料樞紐分析表此為二維變數之統計應用依照班別與性別做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頁,巨集指令的應用,新建立工具巨集