Excel會計與財務管理-理論的方案暨模型第11章Excel財務管理系統(tǒng)的建立教學課件

1、第11章 Excel財務管理系統(tǒng)的建立本章主要內(nèi)容： 11.1 宏和VBA 11.2 財務管理模型用戶界面的設計 11.3 財務管理系統(tǒng)菜單的設計 11.4 控制應用系統(tǒng)自動啟動的方法 第1頁，共61頁。本章目標：第2頁，共61頁。VBAVBA是基于Visual Basic for Windows發(fā)展而來的新一代標準宏語言，提供了面向對象的程序設計方法和相當完整的程序設計語言。對于在工作中需要經(jīng)常使用Office套裝軟件的用戶，學習VBA有助于使工作自動化，提高工作效率。另外，由于VBA可直接應用Office套裝軟件的各項強大功能，所以對于程序設計人員的程序設計和開發(fā)也更加方便快捷。11.1

2、宏和VBA第3頁，共61頁。宏宏程序是指用VBA提供的各種函數(shù)、語句、對象、方法和屬性等編寫的程序。宏程序可以被多次重復使用，自動執(zhí)行復雜的任務，減少完成任務所需的步驟。11.1 宏和VBA第4頁，共61頁。宏的創(chuàng)建和運行 使用宏錄制創(chuàng)建新宏 使用Visual Basic 編輯器創(chuàng)建新宏11.1 宏和VBA第5頁，共61頁。11.1.2 使用宏錄制器錄制宏 宏錄制器是Excel提供的一種軟件工具，它能夠將用戶的操作過程記錄下來，并自動將所記錄的操作轉換成VBA程序代碼。對于經(jīng)常重復進行的操作過程，可以通過宏錄制器將它記錄下來，當需要再次進行這些操作時，只需運行錄制的宏，Excel就能自動完成這

3、些重復的操作。需要注意的是，當錄制宏的工作開始后，所有的操作步驟都將被記錄在宏中，所以應盡量減少不必要的或錯誤的操作，如果在錄制宏時出現(xiàn)失誤，更正失誤的操作也會記錄在宏中。 第6頁，共61頁。11.1.2 使用宏錄制器錄制宏_記錄宏程序的方法和步驟選擇【工具】菜單上的【宏】命令，然后從子菜單中選擇【錄制新宏】命令。在“宏名”框中輸入宏程序的名稱。在“保存在”框中，指定存放宏的位置。在“說明”框中，輸入要錄制宏的有關說明信息。選擇【確定】按鈕，此時【停止錄制】按鈕出現(xiàn)在屏幕上。用戶可以進行各種操作，如選定單元格、輸入標題、設置表格等。單擊【停止錄制】按鈕或者選擇【工具】菜單上【宏】命令下的【停止

4、錄制】命令，結束宏錄制。第7頁，共61頁。11.1.2 使用宏錄制器錄制宏_記錄宏實例【例11-1】打開工作簿cwgl11，用錄制宏的方式記錄以下工作：插入一張工作表，在新插入的工作表上制作一張簡單的分部銷售情況表，表結構如圖11-4所示。錄制一個能夠建立這種表結構的宏Sales。圖11-4：銷售情況表第8頁，共61頁。11.1.2 使用宏錄制器錄制宏_錄制宏的過程：（1）選擇【工具】菜單上【宏】命令下的【錄制新宏】命令；（2）在“宏名”框中輸入宏程序的名稱“Sales”；（3）在“保存在”框中指定存放宏的位置“當前工作簿”；（4）在“說明”框中，輸入要錄制宏的有關說明信息；（5）選擇【確定】

5、按鈕，此時【停止錄制】按鈕出現(xiàn)在屏幕上；（6）選擇【插入】菜單上的【工作表】命令；（7）選擇A1單元格，輸入“銷售情況表”；（8）依次在其他單元格輸入文字，并調整字體字號；（9）選擇范圍A1:AF，合并單元格；（10）選擇標題，居中顯示，單擊【字體】工具，選擇14號，加粗字體；（11）單擊【停止錄制】按鈕，結束宏錄制。 第9頁，共61頁。11.1.2 使用宏錄制器錄制宏_查看所錄制的宏宏程序錄制完畢后，選擇【工具】菜單上【宏】命令下的【宏】菜單項，彈出“宏”對話框。在“宏名”框中選擇宏程序“Sales”，單擊【編輯】按鈕，則可顯示剛才所錄制的宏程序“Sales”。第10頁，共61頁。11.1.

6、3 利用VBA編寫宏程序宏錄制器存在問題：錄制的宏往往語句冗長（記錄了許多不需要的步驟，不需要的語句），會降低執(zhí)行速度當錄制宏的工作開始后，所有的操作步驟都將被記錄在宏中，如果在錄制宏時出現(xiàn)失誤，更正失誤的操作也會記錄在宏中。有時為了更正操作，可能需要重新錄制新宏宏錄制器在靈活度方面有所欠缺有些功能是無法通過錄制實現(xiàn)的因此需要用編寫宏程序第11頁，共61頁。11.1.3 利用VBA編寫宏程序_宏程序的分類子程序子程序是VBA的最小程序單位。 函數(shù)函數(shù)由Function和End Function 語句所包含起來的 VBA語句組成。子程序可以執(zhí)行某種操作，但無返回值。Function函數(shù)和Sub子

7、程序很類似，但函數(shù)有一個返回值。第12頁，共61頁。11.1.3 利用VBA編寫宏程序_結構化程序設計的控制結構（1）順序結構（2）選擇結構（3）循環(huán)結構第13頁，共61頁。11.1.3 利用VBA編寫宏程序（1）順序結構在一般情況下，一個VBA的Sub子程序和Function函數(shù)的執(zhí)行都是從程序代碼的第一個語句行開始，逐條運行程序代碼中的語句，直至遇到End Sub或End Function來結束整個程序的執(zhí)行。這就是順序結構。第14頁，共61頁。11.1.3 利用VBA編寫宏程序_（1）順序結構【例11-2】編寫一宏程序。過程名：PRO1功能：在cwgl工作簿中的“Sales”工作表中，賦

8、予B1單元報表的標題“銷售情況分析表”；賦予B2單元日期“2010年12月”；在A3、B3、C3、D3、E3單元格中依次輸入“部門”、“實際銷售額”、“保本銷售額”、“盈虧狀況”、和“銷項稅”；在A4、B4、C4單元格中依次輸入“計算機部”、“100”和“80”。第15頁，共61頁。11.1.3 利用VBA編寫宏程序_（1）順序結構【例11-2】編寫一宏程序。過程名：PRO1功能：在cwgl工作簿中的“Sales”工作表中，賦予B1單元報表的標題“銷售情況分析表”；賦予B2單元日期“2010年12月”；在A3、B3、C3、D3、E3單元格中依次輸入“部門”、“實際銷售額”、“保本銷售額”、“盈

9、虧狀況”、和“銷項稅”；在A4、B4、C4單元格中依次輸入“計算機部”、“100”和“80”。第16頁，共61頁。11.1.3 利用VBA編寫宏程序_（1）順序結構編制的宏程序如下：Sub pro1() Range(B1).Select ActiveCell.FormulaR1C1 = 銷售情況分析表 Range(B2).Select ActiveCell.FormulaR1C1 = 2010年12月 Range(A3).Select ActiveCell.FormulaR1C1 = 部門 Range(B3).Select ActiveCell.FormulaR1C1 = 實際銷售額 Rang

10、e(C3).Select第17頁，共61頁。11.1.3 利用VBA編寫宏程序_（1）順序結構 ActiveCell.FormulaR1C1 = 保本銷售額 Range(D3).Select ActiveCell.FormulaR1C1 = 盈虧狀況 Range(E3).Select ActiveCell.FormulaR1C1 = 銷項稅 Range(A4).Select ActiveCell.FormulaR1C1 = 計算機部 Range(B4).Value = 100 Range(C4).Value = 80End Sub 第18頁，共61頁。11.1.3 利用VBA編寫宏程序_（2）

11、選擇結構有些時候，需要程序代碼按一定的條件執(zhí)行，當條件成立時，執(zhí)行一部分程序代碼，條件不成立時執(zhí)行另外一部分程序代碼。這種功能需要用VBA的選擇結構來實現(xiàn)。VBA中的條件判斷語句有If語句和Select Case語句兩種第19頁，共61頁。11.1.3 利用VBA編寫宏程序_（2）選擇結構單分支語句IfThen。單行結構條件語句單行結構條件語句是最基本的條件語句，其語法結構為：If 邏輯表達式 Then 語句塊結構條件語句單行結構條件語句中，滿足條件時只執(zhí)行一條語句，若有多行語句需要執(zhí)行，則需使用塊結構條件語句。其語法結構如下：If 邏輯表達式 Then 語句序列1 語句序列2第20頁，共61

12、頁。11.1.3 利用VBA編寫宏程序_（2）選擇結構二分支語句IfThenElse。其語法結構如下： If 邏輯表達式 Then 語句序列1 Else 語句序列2 End If第21頁，共61頁。11.1.3 利用VBA編寫宏程序_（2）選擇結構【例11-5】編寫一宏程序。過程名：PRO3功能：如果實際銷售額大于等于保本點銷售額，在屏幕上輸出“勝利完成任務！”，并在盈虧狀態(tài)單元格賦“盈利”；否則在屏幕上輸出“仍需努力！”，并在盈虧狀態(tài)單元賦“危險”。第22頁，共61頁。11.1.3 利用VBA編寫宏程序_（2）選擇結構編寫的宏程序如下：Sub PRO3() If Range(B3).Valu

13、e = Range(C3).Value Then MsgBox 勝利完成任務！ Range(D4).Select ActiveCell.FormulaR1C1 = 盈利Else MsgBox 仍需努力！ Range(D4).Select ActiveCell.FormulaR1C1 = 危險End If End Sub第23頁，共61頁。11.1.3 利用VBA編寫宏程序_（2）選擇結構多分支語句：ifThenElseIf。語法結構為：If 條件1 Then 語句組1ElseIf 條件2 Then 語句組2 ElseIf 條件n Then 語句組nElse 語句組n+1End If第24頁，共

14、61頁。11.1.3 利用VBA編寫宏程序_（2）選擇結構【例11-6】某汽車出租公司可為顧客提供貨物運送服務，根據(jù)貨物的價值及運輸路程設置運費，并且可根據(jù)路程遠近給予適當?shù)恼劭?。設運費F（單位為元），貨物單價P（單位為元），貨物重量W（單位為噸），路程S（公里）及優(yōu)惠系數(shù)（D）之間的關系式為：F=P*W*S*(1-D)。優(yōu)惠系數(shù)d與路程遠近的關系如下，編寫計算折扣的函數(shù)。第25頁，共61頁。11.1.3 利用VBA編寫宏程序_（2）選擇結構編寫的函數(shù)如下：Function d(s) If s = 1000 Then d = 0.1 ElseIf s = 750 Then d = 0.07 E

15、lseIf s = 500 Then d = 0.05 ElseIf s = 250 Then d = 0.02 Else d = 0 End IfEnd Function 第26頁，共61頁。11.1.3 利用VBA編寫宏程序_（2）選擇結構多分支語句：Select Case。Select Case語句的語法結構如下：Select Case 測試表達式Case 表達式1 語句組1Case 表達式2 語句組2 Case Else 語句組n End Select第27頁，共61頁。11.1.3 利用VBA編寫宏程序_（2）選擇結構【例11-7】編寫一宏程序。過程名：PRO4功能：如果實際銷售額大

16、于保本銷售額，在屏幕上輸出“盈利！”，并在盈虧狀態(tài)單元格賦“盈利”；如果實際銷售額等于保本銷售額，在屏幕上輸出“保本！”，并在盈虧狀態(tài)單元格賦“保本”；如果實際銷售額小于保本銷售額，在屏幕上輸出“虧損！”，并在盈虧狀態(tài)單元格賦“虧損”。第28頁，共61頁。11.1.3 利用VBA編寫宏程序_（2）選擇結構編寫的程序如下：Sub PRO4() Dim sx As Double, bx As Double sx = Range(B4).Value bx = Range(C4).Value Select Case sx Case Is bx MsgBox 盈利！ Range(D4).Select A

17、ctiveCell.FormulaR1C1 = 盈利 End SelectEnd Sub 第29頁，共61頁。11.1.3 利用VBA編寫宏程序_（3）循環(huán)結構在順序結構的程序中，每個語句只執(zhí)行一次。在分支結構的程序中，根據(jù)邏輯表達式的值選擇某一分支執(zhí)行，所選分支的語句也只執(zhí)行一次。然而，在處理實際問題時，經(jīng)常要用同一種方法對不同的數(shù)據(jù)進行重復處理，這些相同的操作可以通過重復執(zhí)行同一程序段來實現(xiàn)。這種重復執(zhí)行具有特定功能程序段的程序就是循環(huán)程序。第30頁，共61頁。11.1.3 利用VBA編寫宏程序_（3）循環(huán)結構ForNext循環(huán)結構。語法結構為：For counter = start To

18、 end step 步長 循環(huán)語句1 循環(huán)語句2 循環(huán)語句3 循環(huán)語句nNext counter該結構的功能是：將初值賦給計數(shù)器counter。當初值start沒有達到終值end時，VBA自動循環(huán)執(zhí)行循環(huán)體中的循環(huán)語句，每執(zhí)行一次循環(huán)，計數(shù)器加1或增加指定的步長step步長；當計數(shù)器達到終值時，跳出循環(huán)。第31頁，共61頁。11.1.3 利用VBA編寫宏程序_（3）循環(huán)結構【例11-8】某公司職工檔案數(shù)據(jù)保存在Excel工作表中，如圖11-7所示。該公司共有1234名職工，每月要從工資表中扣除一定的住房公積金，假設住房公積金按以下的百分比扣除。 第32頁，共61頁。11.1.3 利用VBA編寫

19、宏程序_（3）循環(huán)結構首先，自定義函數(shù)計算公積金的百分比。Function countrate(salary As Double) Dim rate As Double If salary 2000 Then rate = 0.1 ElseIf salary 1500 Then rate = 0.07 ElseIf salary 1200 Then rate = 0.05 ElseIf salary 1000 Then rate = 0.02 ElseIf salary 800 Then rate = 0.01 Else rate = 0 End If countrate = rateEnd

20、Function第33頁，共61頁。11.1.3 利用VBA編寫宏程序_（3）循環(huán)結構編寫計算所有職工工積金的宏PRO5：Sub PRO5()For i = 3 To 1236 r = countrate(Cells(i, 2) Cells(i, 3) = r Cells(i, 3).Style = Percent Cells(i, 4) = r * Cells(i, 2) Cells(i, 5) = Cells(i, 2) - Cells(i, 4) NextEnd Sub第34頁，共61頁。11.1.3 利用VBA編寫宏程序_（3）循環(huán)結構DoLoop循環(huán)結構。語法結構是：Do While

21、 條件 語句序列Loop功能：當條件成立時，VBA重復執(zhí)行語句序列直到條件不成立，跳出循環(huán)。第35頁，共61頁。11.1.3 利用VBA編寫宏程序_（3）循環(huán)結構【例11-9】繼續(xù)例11-8，假設并不知道有多少位職工，在其他條件都相同的條件下，現(xiàn)用DoLoop循環(huán)結構來計算每位職工的住房公積金比例、應交公積金和實際發(fā)放工資。第36頁，共61頁。11.1.3 利用VBA編寫宏程序_（3）循環(huán)結構Sub PRO6()i = 3Do r = countrate(Cells(i, 2) Cells(i, 3) = r Cells(i, 3).Style = Percent Cells(i, 4) =

22、r * Cells(i, 2) Cells(i, 5) = Cells(i, 2) - Cells(i, 4) i = i + 1Loop Until (IsEmpty(Cells(i, 2)End Sub第37頁，共61頁。11.1.3 利用VBA編寫宏程序_（3）循環(huán)結構For EachNext語句。For EachNext循環(huán)與ForNext循環(huán)類似，但它針對數(shù)組或對象集合中的每一元素重復一組語句，而不是重復語句一定的次數(shù)。語法結構：For Each 對象元素變量 in 對象集合 語句序列1 Exit For 語句序列2Next 對象元素變量第38頁，共61頁。11.1.3 利用VBA編

23、寫宏程序_（3）循環(huán)結構【例11-10】編寫一程序過程名：PRO7功能：關閉所有打開的工作簿。編寫的宏程序如下：Sub PRO7() For Each Book in Workbooks() Book.Close Next BookEnd Sub第39頁，共61頁。11.1.4 宏程序的運行1.在EXCEL界面運行宏2.在開發(fā)環(huán)境下運行宏 第40頁，共61頁。11.1.4 宏程序的運行1.在EXCEL界面運行宏在Excel界面運行宏，適用于使用環(huán)境，可通過菜單命令打開“宏”界面，再選擇宏名，點擊【執(zhí)行】或者在工作表打開狀態(tài)下直接調用快捷鍵ALT+F8，彈出宏界面，再選擇宏名，點擊【執(zhí)行】。通過

24、選擇自定義菜單或點擊自定義按鈕的方式執(zhí)行宏（詳見本章11.2和11.3內(nèi)容）將宏配置為在打開工作簿時自動運行（詳見本章11.4節(jié)內(nèi)容）。第41頁，共61頁。11.1.4 宏程序的運行2.在開發(fā)環(huán)境下運行宏首先打開Microsoft Visual Basic界面，然后在【運行】菜單上單擊【運行子過程/用戶窗體】 或者按F5鍵調出“宏”對話框。第42頁，共61頁。第11章 Excel財務管理系統(tǒng)的建立 11.1 宏和VBA 11.2 財務管理模型用戶界面的設計 11.3 財務管理系統(tǒng)菜單的設計 11.4 控制應用系統(tǒng)自動啟動的方法 第43頁，共61頁。11.2 財務管理模型用戶界面的設計本節(jié)主要內(nèi)

25、容：11.2.1 界面設計的主要工具11.2.2 建立各模塊用戶界面11.2.3 建立財務管理系統(tǒng)界面第44頁，共61頁。11.2.1 界面設計的主要工具1.繪圖工具2.窗體第45頁，共61頁。11.2.2 建立各模塊用戶界面第46頁，共61頁。11.2.3 建立財務管理系統(tǒng)界面1.繪制圖形2.建立圖形與各功能模塊之間的鏈接第47頁，共61頁。第11章 Excel財務管理系統(tǒng)的建立 11.1 宏和VBA 11.2 財務管理模型用戶界面的設計 11.3 財務管理系統(tǒng)菜單的設計 11.4 控制應用系統(tǒng)自動啟動的方法 第48頁，共61頁。11.3 財務管理系統(tǒng)菜單的設計本節(jié)主要內(nèi)容：11.3.1 菜

26、單管理器11.3.2 在菜單欄中增加【財務管理】菜單11.3.3 財務管理系統(tǒng)框架第49頁，共61頁。11.3 財務管理系統(tǒng)菜單的設計_建立新菜單的步驟（1）打開工作簿，進入用戶界面。（2）選擇【工具】菜單上的【自定義】命令，出現(xiàn)自定義對話框。（3）在“命令”標簽中選擇“新菜單”類別。（4）選擇“新菜單”命令，并拖至菜單欄中相應的位置。（5）點擊鼠標右鍵得到定義新菜單的快捷菜單。（6）定義新菜單的名稱。（7）重復上述過程定義菜單項、子菜單以及子菜單命令的名稱。（8）為菜單項、子菜單命令指定對應的宏程序。第50頁，共61頁。11.3 財務管理系統(tǒng)菜單的設計_財務管理系統(tǒng)框架財務分析賬務處理籌資決

27、策數(shù)據(jù)處理基礎項目投資決策證券投資分析盈虧平衡與經(jīng)濟訂貨量最優(yōu)化問題財務預測比率分析杜邦分析長期借款籌資決策租賃借款決策.固定資產(chǎn)更新決策.債券投資分析模型股票投資分析模型盈虧平衡模型經(jīng)濟訂貨量模型資金管理生產(chǎn)管理投資組合銷售預測模型利潤預測模型Excel財務管理系統(tǒng)圖11-17 Excel財務管理系統(tǒng)功能結構圖第51頁，共61頁。第11章 Excel財務管理系統(tǒng)的建立 11.1 宏和VBA 11.2 財務管理模型用戶界面的設計 11.3 財務管理系統(tǒng)菜單的設計 11.4 控制應用系統(tǒng)自動啟動的方法 第52頁，共61頁。11.4 控制應用系統(tǒng)自動啟動的方法本節(jié)主要內(nèi)容：11.4.1 對話框操作

28、11.4.2 自動運行宏程序11.4.3 將財務管理系統(tǒng)所在的工作簿設置為在 Excel 啟動時自動打開第53頁，共61頁。11.4.1 對話框操作MsgBox函數(shù):MsgBox對話框函數(shù)可用以向用戶顯示一些提示信息。函數(shù)用法：MsgBox(prompt, buttons , title , helpfile, context)第54頁，共61頁。11.4.1 對話框操作其中各參數(shù)及其說明:參數(shù)說明prompt必選。字符串表達式，在對話框中顯示為消息。prompt的最大長度約為1024個字符，具體長度取決于所用字符的寬度。如果prompt包含多行，可在各行之間使用回車符、換行符或回車符與換行符的組合來分隔各行。buttons可選。其值為數(shù)值表達式，它是值的總和，這些值指定要顯示的按鈕的數(shù)目和類型、要使用的圖標樣式、默認按鈕