l财务管理系统.ppt

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