Excel財(cái)務(wù)應(yīng)用PPT完整全套教學(xué)課件

Excel財(cái)務(wù)應(yīng)用第1章Excel財(cái)務(wù)應(yīng)用基礎(chǔ)第2章財(cái)務(wù)分析模型第3章投資決策模型第4章?tīng)I(yíng)運(yùn)資金管理模型第5章財(cái)務(wù)預(yù)測(cè)模型第6章本量利分析模型第7章全面預(yù)算模型第8章作業(yè)成本法模型全套可編輯PPT課件Excel財(cái)務(wù)應(yīng)用

第1章Excel財(cái)務(wù)應(yīng)用基礎(chǔ)

第2章財(cái)務(wù)分析模型

第3章投資決策模型

第4章?tīng)I(yíng)運(yùn)資金管理模型

第5章財(cái)務(wù)預(yù)測(cè)模型

第6章本量利分析模型

第7章全面預(yù)算模型

第8章作業(yè)成本法模型第1章Excel財(cái)務(wù)應(yīng)用基礎(chǔ)【本章重點(diǎn)】單元格絕對(duì)引用、相對(duì)引用、混合引用及跨工作表引用統(tǒng)計(jì)函數(shù)、邏輯函數(shù)、查找引用函數(shù)、數(shù)據(jù)庫(kù)函數(shù)及財(cái)務(wù)函數(shù)柱形圖、折線圖、餅圖、XY散點(diǎn)圖等常用圖表的制作排序、篩選、分類匯總、數(shù)據(jù)透視表與報(bào)表匯總單變量求解、模擬運(yùn)算、規(guī)劃求解與方案管理滾動(dòng)條、微調(diào)鈕、組合框等窗體工具工作簿和工作表的保護(hù)1.1Excel的主要功能1.工作簿管理2.數(shù)據(jù)處理3.圖表制作4.應(yīng)用系統(tǒng)開(kāi)發(fā)1.2Excel公式1.2.1Excel公式的概念1.2.2Excel公式的創(chuàng)建與編輯1.Excel公式的創(chuàng)建（1）手動(dòng)輸入（2）移動(dòng)點(diǎn)擊輸入1.2Excel公式2.Excel公式的編輯（1）雙擊公式所在的單元格，然后直接在單元格內(nèi)修改。（2）選中公式所在的單元格，然后按【F2】鍵直接在單元格內(nèi)修改。（3）選中公式所在的單元格，然后單擊公式編輯欄，在公式編輯欄內(nèi)修改。3.公式的運(yùn)算次序1.2Excel公式1.2.3Excel公式運(yùn)算結(jié)果的顯示1.查看公式中某步驟的運(yùn)算結(jié)果（1）雙擊公式所在單元格，進(jìn)入編輯狀態(tài)。（2）選中公式中需要查看其運(yùn)算結(jié)果的運(yùn)算體和運(yùn)算符，然后按【F9】鍵，被選中的內(nèi)容將轉(zhuǎn)化為運(yùn)算結(jié)果，該運(yùn)算結(jié)果同時(shí)處于被選中狀態(tài)。（3）按【Esc】鍵或【Ctrl+Z】組合鍵或單擊【撤銷】按鈕，運(yùn)算結(jié)果將恢復(fù)為原公式表達(dá)式內(nèi)容。1.2Excel公式2.公式默認(rèn)顯示方式（1）在單元格顯示運(yùn)算結(jié)果時(shí)，按【Ctrl+`】組合鍵或者單擊【顯示公式】菜單命令，可切換為顯示公式表達(dá)式。（2）在單元格顯示公式表達(dá)式時(shí)，按【Ctrl+`】組合鍵或者單擊【顯示公式】菜單命令，可切換為顯示運(yùn)算結(jié)果。（3）將公式運(yùn)算結(jié)果轉(zhuǎn)換為常數(shù)。如果不希望保留公式而只是需要公式的運(yùn)算結(jié)果，只需將公式復(fù)制后原地進(jìn)行“選擇性粘貼”，并選擇只粘貼數(shù)值即可。1.3單元格引用1.3.1引用的類型1.相對(duì)引用2.絕對(duì)引用3.混合引用1.3單元格引用1.3.2輸入單元格地址⑴在單元格地址的行標(biāo)或列標(biāo)前直接輸入“$”符號(hào)。⑵輸入完單元格地址后立即重復(fù)按【F4】鍵選擇所需要的引用類型。也可在編輯狀態(tài)下，選中公式中的某個(gè)地址，然后再重復(fù)按【F4】鍵選擇所需要的引用類型。1.3單元格引用【例1-1】如何定義公式以快速計(jì)算各項(xiàng)制造費(fèi)用的分配結(jié)果？ABCDE1產(chǎn)品產(chǎn)量職工薪酬水電費(fèi)維修費(fèi)2甲產(chǎn)品1803乙產(chǎn)品2004丙產(chǎn)品1205合計(jì)5001000050008000

在C2單元格定義以下公式“=$C5/$B$5*B$2”，然后通過(guò)填充柄直接將該公式復(fù)制到其他單元格即可。1.3單元格引用1.3.3跨工作表單元格引用格式：“被引用工作表名稱！單元格引用”1.3.4跨工作簿單元格引用格式：“[工作簿名稱]工作表名稱！單元格引用”

當(dāng)被引用工作簿處于關(guān)閉狀態(tài)時(shí)，則必須將該工作簿存放的全部路徑寫(xiě)在工作簿名之前，并且用單引號(hào)將整個(gè)引用路徑括起來(lái)，例如‘D：\Excel\[Book1.xlsx]Sheet1’！D5。1.4常用函數(shù)1.4.1Excel函數(shù)簡(jiǎn)介1.Excel函數(shù)基本結(jié)構(gòu)函數(shù)名稱（參數(shù)1，參數(shù)2，…，參數(shù)n）2.參數(shù)的類型Excel函數(shù)中的參數(shù)可以是數(shù)字、文本、單元格或區(qū)域地址、邏輯值、名稱、公式和函數(shù)。1.4常用函數(shù)1.4.2統(tǒng)計(jì)函數(shù)1.MAX(number1,number2,…,number30)2.MIN(number1,number2,…,number30)3.SUM(number1,number2,…,number30)4.SUMIF(range,criteria[,sum_range])1.4常用函數(shù)【例1-2】1.4常用函數(shù)（1）公式“=SUM(SUMIF(A2:A13,{"銷售一部","銷售三部"},C2:C13))”用于計(jì)算銷售一部和銷售三部的銷量合計(jì)。（2）公式“=SUMIF(C2:C13,">="&LARGE(C2:C13,3))”用于計(jì)算前三名銷量合計(jì)。LARGE(C2:C13,3)用于計(jì)算“C2：C13”中第3大值。（3）公式“=SUM(SUMIF(D8:D19,"<="&{1300,1000})*{1,-1})”用于計(jì)算銷量介于(1000,3000]之間的銷量合計(jì)。1.4常用函數(shù)5.SUMIFS(sum_range,criteria_range1,criteria1,criteria_range2,criteria2…)【例1-3】1.4常用函數(shù)應(yīng)收賬款計(jì)算公式如下：=SUM(SUMIFS(D2:D9,A2:A9,{"應(yīng)收賬款*","預(yù)收賬款*"},C2:C9,"借"))預(yù)收賬款計(jì)算公式如下：=SUM(SUMIFS(D2:D9,A2:A9,{"應(yīng)收賬款*","預(yù)收賬款*"},C2:C9,"貸"))1.4常用函數(shù)應(yīng)付賬款計(jì)算公式如下：=SUM(SUMIFS(D2:D9,A2:A9,{"預(yù)付賬款*","應(yīng)付賬款*"},C2:C9,"貸"))預(yù)付賬款計(jì)算公式如下：=SUM(SUMIFS(D2:D9,A2:A9,{"預(yù)付賬款*","應(yīng)付賬款*"},C2:C9,"借"))1.4常用函數(shù)【例1-4】根據(jù)圖1-2所示銷售數(shù)據(jù)，在“B14”單元格輸入統(tǒng)計(jì)月份。1.4常用函數(shù)（1）計(jì)算銷售一部或銷售二部截至指定月份的銷量合計(jì)，公式如下：公式“=SUM(SUMIFS(C:C,A:A,{"銷售一部","銷售二部"},B:B,"="&B14))”（2）計(jì)算截至指定月份銷量大于等于1200或小于1000的銷量合計(jì)，公式如下：公式“=SUM(SUMIFS(C:C,C:C,{">=1200","<1000"},B:B,"<="&B14))”1.4常用函數(shù)6.AVERAGE(number1,number2,…,number30)7.AVERAGEIF(range,criteria,average_range)8.AVERAGEIFS(average_range,criteria_range1,criteria1,criteria_range2,criteria2...)9.COUNT(value1,value2,…,value30)10.COUNTA(value1,value2,...)11.COUNTIF(range,criteria)12.COUNTIFS(range1,criteria1,range2,criteria2…)1.4常用函數(shù)13.MODE(number1,number2,…,number30)14.LARGE(array,k)15.SMALL(array,k)1.4.3文本函數(shù)1.LEN(text)2.RIGHT(text,num_chars)3.LEFT(text,num_chars)1.4常用函數(shù)4.MID(text,start_num,num_chars)5.LOWER(text)6.UPPER(text)7.TRIM(text)8.REPLACE(old_text,start_num,num_chars,new_text)9.REPT(text,number_times)1.4常用函數(shù)【例1-5】假定各銷售員銷量如圖1-4所示。在C2單元格定義公式“=REPT("|",B2/10)&""&B2”，然后將該公式向下復(fù)制，將“C2：C8”字體設(shè)置為“Playbill”，即可顯示各銷量的條形圖。1.4常用函數(shù)1.4.4邏輯函數(shù)1.IF(logical_test,[value_if_true],[value_if_false])【例1-6】成績(jī)單如圖1-5所示，要求根據(jù)成績(jī)填寫(xiě)成績(jī)等級(jí)，條件如下：成績(jī)小于60為差；介于60至80（不含80）為中；介于80至90（不含90）為良；90以上為優(yōu)。

在C8單元格定義公式“=IF(B8<60,"差",IF(B8<80,“中”,IF(B8<90,“良”,“優(yōu)”)))”；然后，將公式復(fù)制到“C9：C17”即可。1.4常用函數(shù)1.4.5查找與引用函數(shù)1.LOOKUP（1）向量形式LOOKUP（lookup_value,lookup_vector,result_vector）

用于在單行區(qū)域或單列區(qū)域中查找值，然后返回第二個(gè)單行區(qū)域或單列區(qū)域中相同位置的值。（2）數(shù)組形式LOOKUP（lookup_value,array）

用于在數(shù)組的第一行或第一列中查找指定的值，并返回?cái)?shù)組最后一行或最后一列內(nèi)同一位置的值。1.4常用函數(shù)【提示】查找列或行必須升序排序，否則可能出現(xiàn)錯(cuò)誤。

【例1-8】成績(jī)單如圖1-7所示，根據(jù)輸入的姓名查找對(duì)應(yīng)的成績(jī)。

假定在B8單元格輸入姓名，公式“=LOOKUP(B8,A2:A7,B2:B7)”將返回錯(cuò)誤值。此時(shí)用公式“=LOOKUP(1,0/(A2:A7=B8),B2:B7)”可正確地查找到對(duì)應(yīng)成績(jī)。1.4常用函數(shù)2.VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

【例1-9】假設(shè)科目余額表如圖1-8所示。要求在C27單元格輸入科目名稱后，希望自動(dòng)查詢并顯示該科目的期初余額、借方發(fā)生額、貸方發(fā)生額和期末余額。1.4常用函數(shù)

在“C28”單元格定義以下公式：

=IF(ISERROR(VLOOKUP($C$27,$B$19:$F$25,2,FALSE)),”“,VLOOKUP($C$27,$B$19:$F$25,2,FALSE))

然后，將該公式復(fù)制到“C29：C31”，并把公式中的第三個(gè)參數(shù)分別改為3、4、5。1.4常用函數(shù)【例1-10】銷售記錄如圖1-9所示。在B8單元格輸入銷售部門(mén)，在B9單元格輸入月份，定義公式查找指定部門(mén)和月份的銷量。

公式如下：=VLOOKUP(B8&B9,IF({1,0},A2:A7&B2:B7,C2:C7),2,FALSE)。

需要注意，此處為數(shù)組公式，輸入完公式后需要按組合鍵<Ctrl>+<shift>+<enter>確認(rèn)，否則會(huì)返回錯(cuò)誤值。1.4常用函數(shù)【例1-11】科目發(fā)生額余額表如圖1-10所示。在B8單元格輸入科目名稱，在B9單元格定義公式查找對(duì)應(yīng)的科目編碼。

公式如下：=VLOOKUP(B8,IF({1,0},B2:B7,A2:A7),2,FALSE)。

需要注意，此處為數(shù)組公式，輸入完公式后需要按下組合鍵<Ctrl>+<shift>+<enter>確認(rèn)，否則會(huì)返回錯(cuò)誤值。1.4常用函數(shù)【例1-12】如圖1-11所示，根據(jù)商品調(diào)撥記錄，在B57輸入需要查詢的商品名稱，然后返回該商品所有調(diào)撥日期。1.4常用函數(shù)（1）構(gòu)建輔助列，在C48單元格輸入公式：“=COUNTIF($A$48:A48,A48)”并向下復(fù)制到所有商品行，從而得到每種商品截止當(dāng)前行的總調(diào)撥次數(shù)。（2）在B58單元格輸入以下數(shù)組公式，并按組合鍵<Ctrl>+<shift>+<enter>確認(rèn)：=IFERROR(VLOOKUP($B$57&ROW(1:1),IF({1,0},$A$48:$A$56&$C$48:$C$56,$B$48:$B$56),2,FALSE),"")（3）將上述公式向下復(fù)制即可得到所有調(diào)撥日期。1.4常用函數(shù)3.INDEX(array,row_num,column_num)【例1-13】如圖1-12所示，已知2020年全球R&D經(jīng)費(fèi)投入前10名的公司。在A13單元格輸入名次，公式“=INDEX(B3:B12,A13)”，即可根據(jù)名次顯示相應(yīng)的公司名稱。1.4常用函數(shù)4.MATCH(lookup_value,lookup_array,match_type)Match_type為數(shù)字-1、0或1，如果match_type為1，函數(shù)MATCH查找小于或等于lookup_value的最大數(shù)值，此時(shí)Lookup_array必須按升序排列；如果match_type為0，函數(shù)MATCH查找等于lookup_value的第一個(gè)數(shù)值，此時(shí)Lookup_array可以按任何順序排列；如果match_type為-1，函數(shù)MATCH查找大于或等于lookup_value的最小數(shù)值，此時(shí)Lookup_array必須按降序排列，如果省略match_type，則假設(shè)為11.4常用函數(shù)【例1-14】仍以圖1-12數(shù)據(jù)為例，在A13單元格輸入公司名稱，公式“=MATCH(A15,B3:B12,0)”將返回該公司排名；公式“=INDEX(C3:C12,MATCH(A15,B3:B12,0))”將返回該公司R&D投入。1.4常用函數(shù)【例1-15】發(fā)生額余額表如圖1-13所示。1.4常用函數(shù)在B11單元格輸入科目名稱：

公式“=INDEX(A5:A10,MATCH($B$11,$B$5:$B$10,0))”可以查詢科目編碼

公式“=INDEX(C5:C10,MATCH($B$11,$B$5:$B$10,0))”可以查詢期初余額

公式“=INDEX(D5:D10,MATCH($B$11,$B$5:$B$10,0))”可以查詢借方發(fā)生額

1.4常用函數(shù)公式“=INDEX(E5:E10,MATCH($B$11,$B$5:$B$10,0))”可以查詢貸方發(fā)生額

公式“=INDEX(F5:F10,MATCH($B$11,$B$5:$B$10,0))”可以查詢期末余額。1.4常用函數(shù)

【例1-16】各商品出庫(kù)數(shù)量信息如圖1-14所示。在B26單元格輸入商品名稱，查詢?cè)撋唐匪谐鰩?kù)日期和出庫(kù)數(shù)量信息，相關(guān)公式如下。1.4常用函數(shù)公式“=IFERROR(INDEX(B:B,SMALL(IF($A$2:$A$25=$B$26,ROW($2:$25)),ROW(1:1))),"")”并按組合鍵<Ctrl>+<shift>+<enter>確認(rèn)，然后下拉復(fù)制公式，可得到該商品所有出庫(kù)的出庫(kù)日期（需要將相應(yīng)單元格數(shù)據(jù)類型設(shè)置為短日期格式）。公式“=IFERROR(INDEX(C:C,SMALL(IF($A$2:$A$25=$B$26,ROW($2:$25)),ROW(1:1))),"")”并按組合鍵<Ctrl>+<shift>+<enter>確認(rèn)，然后下拉復(fù)制公式，可得到該商品所有出庫(kù)數(shù)量信息。1.4常用函數(shù)

【例1-17】各商品出庫(kù)數(shù)量信息如上圖1-14所示。在B26單元格輸入商品名稱，在B27單元格輸入出庫(kù)日期。在某一單元格設(shè)置公式：

=IFERROR(INDEX(C:C,SMALL(IF(($A$2:$A$25=$B$26)*($B$2:$B$25=$B$27),ROW($2:$25)),ROW(1:1))),"")

按組合鍵<Ctrl>+<shift>+<enter>確認(rèn)，然后下拉復(fù)制公式，可得到該商品當(dāng)天所有出庫(kù)數(shù)量信息。1.4常用函數(shù)或者，先選中某一列的若干單元格，然后在公示欄輸入公式：

=IFERROR(INDEX(C:C,SMALL(IF(($A$2:$A$25=$B$26)*($B$2:$B$25=$B$27),ROW($2:$25)),ROW(1:100))),"")按組合鍵<Ctrl>+<shift>+<enter>確認(rèn)，也可以得到該商品當(dāng)天全部出庫(kù)數(shù)量信息。該公式中ROW(1:100)是為確保有足夠多的的行存放多次出庫(kù)數(shù)量信息，本例中100也可以改為2，因?yàn)镃商品當(dāng)天只有2次出庫(kù)。1.4常用函數(shù)

5.OFFSET(reference,rows,cols,height,width)

【例1-18】各銷售員1至3月銷量如圖1-15所示。1.4常用函數(shù)

在B8單元格輸入需要查詢的月份，然后選中“B9：B11”，在公示欄輸入公式“=SMALL(OFFSET(A1,1,MATCH(B8,B1:D1,0),6,1),ROW($1:$3))”并按組合鍵<Ctrl>+<Shift>+<Enter>確認(rèn)，將自動(dòng)查找該月份排名后三位的銷量。

也可以在B9單元格輸入公式“=SMALL(OFFSET($A$1,1,MATCH($G$1,$B$1:$D$1,0),6,1),ROW(1:1))”然后下拉復(fù)制該公式，也可以依次得到排名倒數(shù)銷量。1.4常用函數(shù)【例1-19】各銷售員1至3月銷量如圖1-16所示。1.4常用函數(shù)在B8單元格輸入銷售員姓名，在B9單元格輸入起始月份，在B10單元格輸入結(jié)束月份，公式“=SUM(OFFSET(A1,MATCH(B8,A2:A7,0),MATCH(B9,B1:D1,0),1,MATCH(B10,B1:D1,0)-MATCH(B9,B1:D1,0)+1))”可以匯總該銷售員起止月份的銷量合計(jì)。6.INDIRECT(ref_text[,a1])

該函數(shù)返回由文本字符串指定的引用并顯示其內(nèi)容，當(dāng)需要更改公式中單元格的引用，而不更改公式本身時(shí)，該函數(shù)非常有用。1.4常用函數(shù)

1.4.6數(shù)據(jù)庫(kù)函數(shù)1.DAVERAGE(database,field,criteria)2.DSUM(database,field,criteria)3.DCOUNT(database,field,criteria)4.DMAX(database,field,criteria)5.DMIN(database,field,criteria)6.DGET(database,field,criteria)1.4常用函數(shù)

【例1-20】已知科目余額表如圖1-17所示，要求分別統(tǒng)計(jì)資產(chǎn)類科目、負(fù)債類科目、所有者權(quán)益類科目個(gè)數(shù)，資產(chǎn)期初余額、負(fù)債和所有者權(quán)益期初余額，借方發(fā)生額合計(jì)、貸方發(fā)生額合計(jì)，資產(chǎn)期末余額、負(fù)債和所有者權(quán)益期末余額，期初余額借方最大值、期初余額貸方最大值、借方發(fā)生額最大值、貸方發(fā)生額最大值、期末余額借方最大值、期末余額貸方最大值，以及期初余額方向?yàn)橘J的資產(chǎn)類科目。1.4常用函數(shù)1.4常用函數(shù)（1）在B18單元格定義資產(chǎn)類科目個(gè)數(shù)計(jì)算公式：=DCOUNT(A8:H16,"期初余額",C18:C19)（2）在B19單元格定義負(fù)債類科目個(gè)數(shù)計(jì)算公式：=DCOUNT(A8:H16,4,D18:D19)（3）在B20單元格定義所有者權(quán)益類科目個(gè)數(shù)計(jì)算公式：=DCOUNT(A8:H16,4,E18:E19)（4）在B21單元格定義資產(chǎn)期初余額計(jì)算公式：=DSUM(A8:H16,4,C21:D22)-DSUM(A8:H16,4,E21:F22)1.4常用函數(shù)（5）在B22單元格定義負(fù)債和所有者權(quán)益期初余額計(jì)算公式：=DSUM(A8:H16,4,G21:G23)（6）在B23單元格定義借方發(fā)生額合計(jì)計(jì)算公式：=SUM(E9:E16)（7）在B24單元格定義貸方發(fā)生額合計(jì)計(jì)算公式：=SUM(F9:F16)（8）在B25單元格定義資產(chǎn)期末余額計(jì)算公式：=DSUM(A8:H16,8,C21:D22)-DSUM(A8:H16,8,E21:F22)1.4常用函數(shù)（9）在B26單元格定義負(fù)債和所有者權(quán)益期末余額計(jì)算公式：=DSUM(A8:H16,8,G21:G23)（10）在B27單元格定義期初余額借方最大值計(jì)算公式：=DMAX(A8:H16,4,C27:C28)（11）在B28單元格定義期初余額貸方最大值計(jì)算公式：=DMAX(A8:H16,4,D27:D28)（12）在B29單元格定義借方發(fā)生額最大值計(jì)算公式：=MAX(E9:E16)1.4常用函數(shù)（13）在B30單元格定義貸方發(fā)生額最大值計(jì)算公式：=MAX(F9:F16)（14）在B31單元格定義期初余額借方最大值計(jì)算公式：=DMAX(A8:H16,8,E27:E28)（15）在B32單元格定義期初余額貸方最大值計(jì)算公式：=DMAX(A8:H16,8,F27:F28)（16）在B33單元格定義查找期初余額為貸的資產(chǎn)類科目公式：=DGET(A8:H16,1,E21:F22)1.4常用函數(shù)1.4.7日期與時(shí)間函數(shù)1.NOW()2.TODAY()3.YEAR(serial_number)MONTH(serial_number)DAY(serial_number)4.WEEKDAY(serial_number[,return_type])1.4常用函數(shù)5.HOUR(serial_number)MINUTE(serial_number)HOUR(serial_number)6.TIME(hour，minute,second)7.DATE(year，month,day)8.DATEDIF(start_date,end_date,unit)1.4常用函數(shù)1.4.8數(shù)學(xué)和三角函數(shù)1.ABS(number)2.FACT(number)3.INT(number)4.ROUND(number,num_digits)5.LOG(number,base)6.POWER(number,power)7.SQRT(number)1.4常用函數(shù)8.MOD(number,divisor)9.RAND()10.SUMPRODUCT(array1,array2,array3,...)利用SUMPRODUCT函數(shù)也可以實(shí)現(xiàn)多條件求和。如例【1-3】所示，公式“SUMPRODUCT((B2:B9="資產(chǎn)")*(C2:C9="借")*D2:D9)”將計(jì)算資產(chǎn)類賬戶借方余額合計(jì)。1.4常用函數(shù)1.4.9財(cái)務(wù)函數(shù)1.SLN（cost，salvage，life）2.DDB（cost，salvage，life，per[，factor]）3.SYD（cost，salvage，life，per）

【例1-21】如圖1-18所示，某固定資產(chǎn)原值100000元，預(yù)計(jì)凈殘值5000元，預(yù)計(jì)使用年限5年，如何分別運(yùn)用直線法、年數(shù)總和法和雙倍余額遞減法計(jì)算該固定資產(chǎn)各年折舊？1.4常用函數(shù)1.4常用函數(shù)4.VDB（cost，salvage，life，start_period,end_period,[factor],[no_switch]）

【例1-22】如圖1-20所示，已知固定資產(chǎn)原值、預(yù)計(jì)凈殘值和預(yù)計(jì)使用年限，分別采用年限平均法、年數(shù)總和法和雙倍余額遞減法計(jì)算各年折舊額。1.4常用函數(shù)1.4常用函數(shù)5.NPV（rate,value1,value2,……）6.IRR（values,guess）

【例1-23】如圖1-22所示，某公司欲投資于某項(xiàng)目，初始投資額為100000元，項(xiàng)目周期為3年，預(yù)計(jì)未來(lái)三年各年收益分別為30000元、50000元、40000元。假設(shè)貼現(xiàn)率為5%，試計(jì)算該項(xiàng)目的凈現(xiàn)值以及內(nèi)含報(bào)酬率。1.4常用函數(shù)1.4常用函數(shù)（1）在C20單元格定義凈現(xiàn)值計(jì)算公式：=NPV(5%,C17:C19)+C16（2）在C21單元格定義內(nèi)含報(bào)酬率計(jì)算公式：=IRR(C16:C19)7.FV（rate，nper，pmt，[pv]，[type]）8.PV（rate，nper，pmt，fv，type）9.PMT（rate，nper，pv，fv，type）10.PPMT（rate，per，nper，pv，fv，type）11.IPMT（rate，per，nper，pv，fv，type）12.NPER（rate，pmt，pv，fv，type）1.4常用函數(shù)13.RATE（nper，pmt，pv，fv，type，guess）

【例1-24】某公司欲發(fā)行面值為100元的債券若干，債券期限為3年，票面利率為5％，市場(chǎng)實(shí)際利率為3％，如果每年年末支付利息，到期償還本金，請(qǐng)問(wèn)該債券的發(fā)行價(jià)格是多少？如果到期一次還本付息且單利計(jì)息，發(fā)行價(jià)格又應(yīng)該是多少？1.4常用函數(shù)

如果利息按年發(fā)放，可以利用年金現(xiàn)值函數(shù)求解債券發(fā)行價(jià)格，發(fā)行價(jià)格＝PV(3%,3,-100*5%,-100)＝105.66元。

如果到期一次還本付息，則可通過(guò)普通復(fù)利現(xiàn)值計(jì)算發(fā)行價(jià)格，發(fā)行價(jià)格=PV(3%,3,,-100-100*5%*3)=105.24元。1.4常用函數(shù)【例1-25】某公司欲以105元的價(jià)格發(fā)行面值為100元的債券若干，債券期限2年，到期償還本金及利息，若市場(chǎng)實(shí)際利率為5%，則票面利率應(yīng)定位多少？若每年年末支付利息，到期償還本金，則票面利率又應(yīng)定為多少？

如果到期償還本金及利息，票面利率=(105*(1+5%)^2/100-1)/2=7.88%。

如果每年年末支付利息，票面利率=ABS(PMT(5%,2,105,-100))/100。1.4常用函數(shù)【例1-26】小趙今年30歲，年初購(gòu)買了某種養(yǎng)老保險(xiǎn)，從今年起每年年初交納1800元，交費(fèi)期為20年。從60歲開(kāi)始，保險(xiǎn)公司每月初向小李支付養(yǎng)老金6000元，一直到去世為止。假定保險(xiǎn)公司的投資收益率為5％，請(qǐng)問(wèn)：就小李個(gè)人而言，其至多活到多大歲數(shù)，保險(xiǎn)公司才不至于虧本？所求年齡公式：=NPER(5%/12,500,FV(5%,20,1800,,1)*(1+5%)^10,,1)/12+60＝97.34。1.4常用函數(shù)【例1-27】小李預(yù)取得一筆按揭貸款100000元，貸款年限5年，采用等額本息償還方式。如果小李每月最高可還貸金額為2000元，則可接受的最高年利率是多少？最高利率=RATE(5*12,-2000,100000)*12=7.42%。1.5圖表制作

1.5.1圖表類型1.柱形圖2.條形圖3.折線圖4.餅圖5.圓環(huán)圖6.xy散點(diǎn)圖1.5圖表制作7.面積圖8.雷達(dá)圖9.氣泡圖1.5.2制作圖表

【例1-28】某公司各年銷售統(tǒng)計(jì)資料如表1-4所示。1.5圖表制作一季度二季度三季度四季度合計(jì)北京1200800150020005500上海20001200130018006300天津8001000120020005000合計(jì)4000300040005800168001.5圖表制作1.5圖表制作1.6數(shù)據(jù)管理1.6.1排序1.快速排序2.自定義排序1.6數(shù)據(jù)管理1.6.2篩選1.快速篩選2.高級(jí)篩選

【例1-30】仍以圖1-30所示的銷售記錄為例，要求篩選銷售一部彩電或銷售二部空調(diào)的銷售記錄。1.6數(shù)據(jù)管理（1）設(shè)置篩選條件。1.6數(shù)據(jù)管理（1）設(shè)置篩選條件。HIJKLM3日期部門(mén)商品單價(jià)銷量銷售額4

銷售一部彩電

5

銷售二部空調(diào)

1.6數(shù)據(jù)管理（2）在銷售數(shù)據(jù)區(qū)域中任一單元格單擊鼠標(biāo)，然后在功能區(qū)“數(shù)據(jù)”選項(xiàng)卡中單擊【高級(jí)】按鈕，出現(xiàn)對(duì)話框，如圖1-32所示。將方式設(shè)置為“將篩選結(jié)果復(fù)制到其他位置”；將列表區(qū)域設(shè)置為原銷售記錄區(qū)域“A2：F18”；將條件區(qū)域設(shè)置為以上設(shè)置好的條件區(qū)域“H3：M5”；在復(fù)制到文本框中設(shè)置保存篩選結(jié)果的區(qū)域左上角單元格如H7。1.6數(shù)據(jù)管理（3）設(shè)置完畢單擊【確定】按鈕即可顯示篩選結(jié)果，如圖1-33所示。1.6數(shù)據(jù)管理1.6.3分類匯總

【例1-31】仍以圖1-30所示的銷售記錄為例，可以進(jìn)行以下相關(guān)分類匯總操作：1.按日期對(duì)銷售額進(jìn)行匯總

（1）按日期對(duì)銷售記錄進(jìn)行排序。

（2）選中銷售記錄所在區(qū)域，接著在“數(shù)據(jù)”選項(xiàng)卡中單擊【分類匯總】按鈕，出現(xiàn)分類匯總對(duì)話框，如圖1-34所示。1.6數(shù)據(jù)管理1.6數(shù)據(jù)管理（3）將分類匯總字段設(shè)置為“日期”；將匯總方式設(shè)置為“求和”；將匯總項(xiàng)設(shè)置為“銷售額”，然后單擊“確定”按鈕，匯總結(jié)果如圖1-35所示。1.6數(shù)據(jù)管理2.依次按日期和部門(mén)對(duì)銷售額進(jìn)行匯總（1）依次按日期和部門(mén)對(duì)銷售記錄進(jìn)行排序。（2）參照以上操作按日期匯總銷售額。（3）再次執(zhí)行分類匯總。在分類匯總對(duì)話框中，將分類匯總字段設(shè)置為“部門(mén)”；將匯總方式設(shè)置為“求和”；將匯總項(xiàng)設(shè)置為“銷售額”；單擊“替換當(dāng)前分類匯總”選項(xiàng)，去除其選中狀態(tài)；然后單擊“確定”按鈕，匯總結(jié)果如圖1-36所示。1.6數(shù)據(jù)管理1.6數(shù)據(jù)管理1.6.4數(shù)據(jù)透視表1.數(shù)據(jù)透視表的創(chuàng)建頁(yè)行列數(shù)據(jù)項(xiàng)1.6數(shù)據(jù)管理2.數(shù)據(jù)透視表的設(shè)置（1）重新設(shè)計(jì)版面布局（2）設(shè)置值的匯總依據(jù)（3）設(shè)置值的顯示方式（4）進(jìn)行數(shù)據(jù)的篩選（5）設(shè)定報(bào)表樣式1.6數(shù)據(jù)管理【例1-32】仍以圖1-30所示的銷售記錄為例，要求根據(jù)需要，在Excel2007中建立相關(guān)數(shù)據(jù)透視表。操作步驟如下：

（1）在數(shù)據(jù)清單中選中任一單元格，然后在“插入”選項(xiàng)卡中單擊【數(shù)據(jù)透視表】按鈕，出現(xiàn)對(duì)話框，如圖1-38所示。1.6數(shù)據(jù)管理

（2）在此設(shè)置數(shù)據(jù)來(lái)源和放置數(shù)據(jù)透視表的位置，也可保持默認(rèn)設(shè)置，直接單擊【確定】按鈕，出現(xiàn)“數(shù)據(jù)透視表字段列表”，如圖1-39所示。1.6數(shù)據(jù)管理

（3）在“數(shù)據(jù)透視表字段列表”中，用鼠標(biāo)將字段“日期”拖動(dòng)至“報(bào)表篩選”框（即設(shè)置為頁(yè)字段），將“部門(mén)”拖動(dòng)至“行標(biāo)簽”框（即設(shè)置為行字段），將“商品”拖動(dòng)至“列標(biāo)簽”框（即設(shè)置為列字段），將“銷售額”拖動(dòng)至“數(shù)值”框（即設(shè)置為數(shù)據(jù)項(xiàng)）。（4）設(shè)置完畢，工作表中會(huì)自動(dòng)生成數(shù)據(jù)透視表，據(jù)此就可以方便地進(jìn)行數(shù)據(jù)透視分析了，如圖1-40所示。1.6數(shù)據(jù)管理1.6數(shù)據(jù)管理

⑸在數(shù)據(jù)透視表區(qū)域單擊鼠標(biāo)，在功能區(qū)會(huì)自動(dòng)顯示“選項(xiàng)”和“設(shè)計(jì)”兩個(gè)選項(xiàng)可，可以根據(jù)需要利用選項(xiàng)卡中的各功能對(duì)數(shù)據(jù)透視表進(jìn)行設(shè)置。1.6.5數(shù)據(jù)有效性

【例1-33】如圖1-41所示。1.6數(shù)據(jù)管理假設(shè)各數(shù)據(jù)有效性要求如下：購(gòu)置日期只能輸入截至當(dāng)日的當(dāng)月有效日期；設(shè)備名稱長(zhǎng)度介于1至10之間；原值為大于等于1000的數(shù)；預(yù)計(jì)凈殘值大于等于0小于原值；預(yù)計(jì)使用年限為大于等于2的整數(shù)；折舊方法設(shè)置為序列，選項(xiàng)包括年限平均法、年數(shù)總和法和雙倍余額遞減法。1.6數(shù)據(jù)管理

1.購(gòu)置日期有效性設(shè)置（1）單擊C2單元格，選擇菜單“數(shù)據(jù)|有效性”，打開(kāi)數(shù)據(jù)有效性對(duì)話框。（2）在該對(duì)話框中，切換至“設(shè)置”選項(xiàng)卡，進(jìn)行有效性條件設(shè)置，如圖1-42所示。在“允許”下拉框中選擇“日期”；在“數(shù)據(jù)”下拉框中選擇“介于”；在“開(kāi)始日期”文本框中輸入公式“=DATE(YEAR(TODAY()),MONTH(TODAY()),1)”；在“結(jié)束日期”文本框中輸入公式“=TODAY()”。1.6數(shù)據(jù)管理1.6數(shù)據(jù)管理（3）切換至“輸入信息”選項(xiàng)卡，設(shè)置提示信息，如圖1-43所示。輸入提示信息的標(biāo)題“提示”，輸入提示信息內(nèi)容“購(gòu)置日期只能輸入截至當(dāng)日的當(dāng)月有效日期”。當(dāng)然，也可以不設(shè)置輸入信息。1.6數(shù)據(jù)管理（4）切換至“出錯(cuò)警告”選項(xiàng)卡，設(shè)置出錯(cuò)警告信息，如圖1-44所示。選擇圖標(biāo)樣式，包括停止、警告、信息三種，在此選擇“警告”；輸入警告信息標(biāo)題“警告”；輸入錯(cuò)誤信息“購(gòu)置日期無(wú)效！”。當(dāng)然，也可以不設(shè)置出錯(cuò)警告。1.6數(shù)據(jù)管理（5）切換至“輸入法模式”選項(xiàng)卡，可以設(shè)置輸入法模式。輸入法模式包括三種：隨意、打開(kāi)和關(guān)閉。如果選擇隨意的話，當(dāng)選中該單元格時(shí)，不強(qiáng)行打開(kāi)和關(guān)閉當(dāng)前輸入法，而是保持現(xiàn)有狀態(tài)，該選項(xiàng)是默認(rèn)選項(xiàng)；如果選擇打開(kāi)的話，當(dāng)選中該單元格時(shí)，將自動(dòng)打開(kāi)輸入法；如果選擇關(guān)閉的話，當(dāng)選中該單元格時(shí)，將自動(dòng)關(guān)閉輸入法。也可不設(shè)置輸入法模式，在此使用默認(rèn)值。（6）所有參數(shù)設(shè)置完畢，單擊“確定”按鈕，單元格數(shù)據(jù)有效性即可生效。1.6數(shù)據(jù)管理2.設(shè)備名稱有效性設(shè)置（1）選中C3單元格，選擇菜單“數(shù)據(jù)|有效性”。（2）在“允許”下拉框中選擇“文本長(zhǎng)度”；在“數(shù)據(jù)”下拉框中選擇“介于”；在“最小值”文本框中輸入1；在“最大值”本框中輸入10。（3）輸入提示信息的標(biāo)題“提示”，輸入提示信息內(nèi)容“設(shè)備名稱長(zhǎng)度介于1至10之間”。1.6數(shù)據(jù)管理（4）設(shè)置出錯(cuò)警告。選擇圖標(biāo)樣式“警告”；輸入警告信息標(biāo)題“警告”；輸入錯(cuò)誤信息“設(shè)備名稱無(wú)效！”。（5）所有參數(shù)設(shè)置完畢，單擊“確定”按鈕即可。3.原值有效性設(shè)置（1）選中C4單元格，選擇菜單“數(shù)據(jù)|有效性”。（2）在“允許”下拉框中選擇“小數(shù)”；在“數(shù)據(jù)”下拉框中選擇“大于或等于”；在“最小值”文本框中輸入1000。1.6數(shù)據(jù)管理（3）輸入提示信息的標(biāo)題“提示”，輸入提示信息內(nèi)容“原值為大于等于1000的數(shù)”。（4）設(shè)置出錯(cuò)警告。選擇圖標(biāo)樣式“警告”；輸入警告信息標(biāo)題“警告”；輸入錯(cuò)誤信息“原值無(wú)效！”。（5）所有參數(shù)設(shè)置完畢，單擊“確定”按鈕即可。4.預(yù)計(jì)凈殘值（1）選中C5單元格，選擇菜單“數(shù)據(jù)|有效性”。1.6數(shù)據(jù)管理（2）在“允許”下拉框中選擇“自定義”；在“公式”文本框中輸入公式“=AND(C5>=0,C5<C4)”。（3）輸入提示信息的標(biāo)題“提示”，輸入提示信息內(nèi)容“預(yù)計(jì)凈殘值大于等于零小于原值”。（4）設(shè)置出錯(cuò)警告。選擇圖標(biāo)樣式“警告”；輸入警告信息標(biāo)題“警告”；輸入錯(cuò)誤信息“預(yù)計(jì)凈殘值無(wú)效！”。（5）所有參數(shù)設(shè)置完畢，單擊“確定”按鈕即可。1.6數(shù)據(jù)管理5.預(yù)計(jì)使用年限有效性設(shè)置（1）選中C6單元格，選擇菜單“數(shù)據(jù)|有效性”。（2）在“允許”下拉框中選擇“整數(shù)”；在“數(shù)據(jù)”下拉框中選擇“大于或等于”；在“最小值”文本框中輸入2。（3）輸入提示信息的標(biāo)題“提示”，輸入提示信息內(nèi)容“預(yù)計(jì)使用年限為大于等于2的整數(shù)”。1.6數(shù)據(jù)管理（4）設(shè)置出錯(cuò)警告。選擇圖標(biāo)樣式“警告”；輸入警告信息標(biāo)題“警告”；輸入錯(cuò)誤信息“預(yù)計(jì)使用年限無(wú)效！”。（5）所有參數(shù)設(shè)置完畢，單擊“確定”按鈕即可。6.折舊方法有效性設(shè)置（1）先在任一空白列的三個(gè)單元格，如F2：F4，依次輸入“年限平均法”、“年數(shù)總和法”、“雙倍余額遞減法”，然后選中C7單元格，再選擇菜單“數(shù)據(jù)|有效性”。1.6數(shù)據(jù)管理（2）在“允許”下拉框中選擇“序列”；單擊“來(lái)源”文本框中，然后在工作表中拖動(dòng)鼠標(biāo)選擇選項(xiàng)所作區(qū)域，如F2：F4。（3）在此，可以不設(shè)置輸入信息和出錯(cuò)警告，單擊“確定”按鈕即可。1.6數(shù)據(jù)管理【例1-34】利用數(shù)據(jù)有效性和INDIRECT函數(shù)可以實(shí)現(xiàn)多級(jí)下拉菜單的設(shè)置。如圖1-45所示，將“B1：B2”區(qū)域命名為“sj”，將區(qū)域“B3：B4”區(qū)域命名為“jd”。設(shè)置A6單元格數(shù)據(jù)有效性為序列，數(shù)據(jù)來(lái)源為“手機(jī),家電”；設(shè)置B6單元格數(shù)據(jù)有效性為序列，數(shù)據(jù)來(lái)源為“=INDIRECT(A6)”。經(jīng)過(guò)以上設(shè)置，便實(shí)現(xiàn)了二級(jí)菜單的制作。1.6數(shù)據(jù)管理1.6.6報(bào)表匯總1.按位置匯總

【例1-35】某公司下屬若干分公司，2011年和2012年銷售數(shù)據(jù)分別位于同一工作簿的不同工作表，如圖1-46和圖1-47所示。1.6數(shù)據(jù)管理

如果需要匯總2011年和2012年的報(bào)表數(shù)據(jù)，可按以下方法進(jìn)行操作：在另一新工作表某單元格中輸入等號(hào)，然后切換到工作表2011年工作表，并選定單元格B3；繼續(xù)在編輯欄中輸入“+”加號(hào)，再切換到2012年工作表，并選定單元格B3；公式輸入完畢，按回車鍵返回到原工作表，得到北京一季度銷售合計(jì)；最后，利用填充柄將公式復(fù)制到其他單元格，即可得到公司各地區(qū)各季度的匯總銷售報(bào)表。1.6數(shù)據(jù)管理

2.合并計(jì)算

【例1-36】某公司2011年銷售統(tǒng)計(jì)表如圖1-46所示，2012年銷售統(tǒng)計(jì)表如圖1-48所示。通過(guò)合并計(jì)算得到匯總報(bào)表的步驟如下：1.6數(shù)據(jù)管理

（1）首先，選定目標(biāo)工作表，并在某單元格輸入?yún)R總報(bào)表的標(biāo)題，然后單擊選中預(yù)存放匯總報(bào)表的起始單元格。

（2）然后，在功能區(qū)“數(shù)據(jù)”選項(xiàng)卡中的“數(shù)據(jù)工具”功能組中單擊“合并計(jì)算”，打開(kāi)合并計(jì)算對(duì)話框，如圖1-49所示。1.6數(shù)據(jù)管理

（3）在該對(duì)話框中設(shè)置相關(guān)參數(shù)。在“函數(shù)”編輯框中，選擇“求和”函數(shù)為合并計(jì)算數(shù)據(jù)的匯總函數(shù)；在引用位置編輯框中，單擊2011年銷售數(shù)據(jù)所在工作表，選定單元格區(qū)域A2：F6，然后單擊“添加”按鈕，接著在引用位置編輯框中，單擊2012年工作表，選定單元格區(qū)域A2：F6；選中標(biāo)簽位置下的“首行”復(fù)選框和“最左列”復(fù)選框。參數(shù)設(shè)置完畢后單擊“確定”按鈕，便可在目標(biāo)工作表中顯示合并計(jì)算結(jié)果，如圖1-50所示。1.6數(shù)據(jù)管理1.7假設(shè)分析1.7.1單變量求解

【例1-37】已知某公司生產(chǎn)單一產(chǎn)品，該產(chǎn)品今年的單價(jià)、單位變動(dòng)成本、銷量、固定成本分別為5000、3000、1000和1000000。該公司計(jì)劃明年實(shí)現(xiàn)利潤(rùn)1500000元，在單價(jià)、單位變動(dòng)成本和固定成本保持不變的情況下，銷量至少應(yīng)該達(dá)到多少才能保證目標(biāo)利潤(rùn)的實(shí)現(xiàn)？1.7假設(shè)分析（1）將今年的單價(jià)、單位變動(dòng)成本、銷量、固定成本數(shù)據(jù)輸入工作表，如圖1-51所示，并在B5單元格定義利潤(rùn)的計(jì)算公式“=(B1-B2)*B3-B4”。1.7假設(shè)分析（2）在“數(shù)據(jù)”功能區(qū)中的“數(shù)據(jù)工具”功能組中單擊“假設(shè)分析”菜單，出現(xiàn)下拉菜單，選擇其中的“單變量求解”，出現(xiàn)對(duì)話框，如圖1-52所示。1.7假設(shè)分析（3）將目標(biāo)單元格設(shè)置為“B5”，將目標(biāo)值設(shè)定為目標(biāo)利潤(rùn)1500000，將可變單元格設(shè)置為“B3”，然后單擊“確定”按鈕。（4）此時(shí)，系統(tǒng)給出單變量求解狀態(tài)，如圖1-53所示。由此可知，當(dāng)目標(biāo)利潤(rùn)為1500000時(shí)，銷量應(yīng)為1250。如果想保留該結(jié)果，單擊“確定”按鈕；如果想恢復(fù)單變量求解前的原值，可單擊“取消”按鈕。1.7假設(shè)分析1.7.2模擬運(yùn)算表1.單變量模擬運(yùn)算

【例1-38】已知目前單價(jià)、單位變動(dòng)成本、銷量、固定成本分別為5000、3000、1000和1000000。利用模擬運(yùn)算考察銷量變動(dòng)對(duì)收入、總成本和利潤(rùn)的影響。1.7假設(shè)分析

（1）輸入單價(jià)、單位變動(dòng)成本、銷量、固定成本的當(dāng)前值，以及銷量的不同變化取值。（2）在B5單元格設(shè)置收入的計(jì)算公式“=B2*D2”，在C5單元格設(shè)置總成本的計(jì)算公式“=B3*D2+D3”，在D5單元格設(shè)置利潤(rùn)的計(jì)算公式“=B5-C5”。（3）選中區(qū)域“A5：D12”，然后在功能區(qū)“數(shù)據(jù)”選項(xiàng)卡中的“數(shù)據(jù)工具”功能組中單擊“假設(shè)分析”，出現(xiàn)下拉菜單，選擇其中的“數(shù)據(jù)表”，出現(xiàn)數(shù)據(jù)表對(duì)話框，如圖1-55所示。1.7假設(shè)分析1.7假設(shè)分析

（4）此例中，定義收入、總成本及利潤(rùn)的計(jì)算公式時(shí)，銷量直接或間接引用了D2單元格的值，在模擬運(yùn)算區(qū)域中，變化量銷量的值位于第一列，因此，應(yīng)將引用列的單元格設(shè)置為“D2”，然后單擊“確定”按鈕即可得到模擬運(yùn)算結(jié)果，如圖1-56所示。1.7假設(shè)分析2.雙變量模擬運(yùn)算

【例1-39】已知目前單價(jià)、單位變動(dòng)成本、銷量、固定成本分別為5000、3000、1000和1000000。利用模擬運(yùn)算考察固定成本本和銷量的組合變動(dòng)對(duì)利潤(rùn)的影響。1.7假設(shè)分析

（1）輸入單價(jià)、單位變動(dòng)成本、銷量、固定成本的當(dāng)前值，以及固定成本和銷量的不同變化取值。（2）在F4單元格設(shè)置利潤(rùn)的計(jì)算公式“=(G2-K2)*I2-M2”。（3）選中區(qū)域“F4：M14”，然后在功能區(qū)“數(shù)據(jù)”選項(xiàng)卡中的“數(shù)據(jù)工具”功能組中單擊“假設(shè)分析”，出現(xiàn)下拉菜單，選擇其中的“數(shù)據(jù)表”，出現(xiàn)數(shù)據(jù)表對(duì)話框，出現(xiàn)模擬運(yùn)算表對(duì)話框，如圖1-58所示:1.7假設(shè)分析1.7假設(shè)分析

（4）此例中，定義利潤(rùn)的計(jì)算公式時(shí)，銷量直接引用了I2單元格的值，固定成本直接引用了M2單元格的值，在模擬運(yùn)算區(qū)域中，銷量的值位于第一行，固定成本的值位于第一列，因此，應(yīng)將引用行的單元格設(shè)置為“I2”，將引用列的單元格設(shè)置為“M2”，然后單擊“確定”按鈕即可得到模擬運(yùn)算結(jié)果，如圖1-59所示:1.7假設(shè)分析1.7假設(shè)分析

1.7.3方案管理

【例1-40】某公司只生產(chǎn)銷售一種產(chǎn)品，已知目前該產(chǎn)品的單價(jià)、單位變動(dòng)成本、銷量、固定成本分別為5000、3000、1000和1000000。為進(jìn)一步提高利潤(rùn)，現(xiàn)有兩種可行方案。第一種方案，投入廣告費(fèi)100000元同時(shí)單價(jià)提高10%，并預(yù)計(jì)銷量擴(kuò)大5%。第二種方案，提高生產(chǎn)效率，變動(dòng)成本壓縮10%。試問(wèn)：兩種方案對(duì)利潤(rùn)的影響各為多少？單純從利潤(rùn)角度考慮，哪種方案更可行？1.7假設(shè)分析1.建立方案（1）在工作表中輸入基本數(shù)據(jù)，并定義利潤(rùn)的計(jì)算公式，如圖1-60所示。1.7假設(shè)分析

（2）在功能區(qū)“數(shù)據(jù)”選項(xiàng)卡中的“數(shù)據(jù)工具”功能組中單擊“假設(shè)分析”，出現(xiàn)下拉菜單，選擇其中的“方案管理器”，出現(xiàn)方案管理器對(duì)話框，如圖1-61所示。1.7假設(shè)分析

（3）此時(shí)尚無(wú)任何方案，單擊“添加”按鈕，出現(xiàn)編輯方案對(duì)話框，如圖1-62所示。輸入方案名稱，如“固定成本增加100000單價(jià)提高10%銷量擴(kuò)大5%”；將可變單元格設(shè)置為“B1：B4”，然后單擊“確定”按鈕，出現(xiàn)對(duì)話框，如圖1-63所示。1.7假設(shè)分析（4）該方案中，單價(jià)提高10%，原單價(jià)為5000，變化后單價(jià)為5500，將B1值設(shè)置為5500；銷量擴(kuò)大5%，原銷量為1000，變化后為1050，將B3值設(shè)置為1050；固定成本增加100000，原固定成本1000000，變化后為1100000，將B4設(shè)置為1100000。輸入完畢，單擊“確定”按鈕，方案增加完畢，返回方案管理器對(duì)話框。1.7假設(shè)分析（5）在方案管理器中，單擊“添加”按鈕，繼續(xù)設(shè)置第二種方案。參照以上步驟，將方案名稱設(shè)置為“變動(dòng)成本壓縮10%”，將可變單元格設(shè)置為“B1：B4”，將單位變動(dòng)成本B2的方案值設(shè)置為2700（原3000，壓縮10%后為2700）。

2.顯示方案結(jié)果在方案管理器中，單擊某個(gè)方案，再單擊“顯示”按鈕，工作表中便會(huì)顯示該方案的最終結(jié)果。1.7假設(shè)分析3.對(duì)比各方案（1）在方案管理器中，單擊“摘要”按鈕，出現(xiàn)對(duì)話框，如圖1-64所示。1.7假設(shè)分析（2）選擇“方案摘要”，保持結(jié)果單元格為目標(biāo)單元格B5，然后單擊“確定”按鈕，此時(shí)會(huì)自動(dòng)插入一張新工作表，并顯示方案摘要，如圖1-65所示。1.7假設(shè)分析（3）如果選擇“方案數(shù)據(jù)透視表”，則結(jié)果如圖1-66所示。4.刪除或編輯方案如果不再需要某個(gè)方案，可在方案管理器中，選中該方案然后單擊“刪除”按鈕即可刪除方案；如果需要修改某個(gè)方案，選中某個(gè)方案后單擊“編輯”按鈕即可修改該方案。1.7假設(shè)分析1.7.4規(guī)劃求解

【例1-41】假定某企業(yè)有A、B、C三個(gè)生產(chǎn)車間，共生產(chǎn)甲、乙、丙三種家電產(chǎn)品，各產(chǎn)品的售價(jià)、單位變動(dòng)成本以及在各車間的單位加工工時(shí)，各車間能夠提供的總工時(shí)，固定成本總額等相關(guān)數(shù)據(jù)如表1-6所示。請(qǐng)問(wèn)：該企業(yè)應(yīng)當(dāng)如何安排生產(chǎn)才能使得利潤(rùn)最大化？最大利潤(rùn)是多少？1.7假設(shè)分析項(xiàng)目甲產(chǎn)品乙產(chǎn)品丙產(chǎn)品車間總工時(shí)A車間211240B車間121360C車間112300產(chǎn)品售價(jià)251815

單位變動(dòng)成本16109

固定成本總額10001.7假設(shè)分析（1）輸入基本數(shù)據(jù)，如圖1-67所示。1.7假設(shè)分析（2）定義相關(guān)公式。需要設(shè)置目標(biāo)單元格、約束單元格的計(jì)算公式，包括以下幾個(gè)方面：計(jì)算單位邊際貢獻(xiàn)。在B11單元格定義甲產(chǎn)品單位邊際貢獻(xiàn)計(jì)算公式“=B5-B6”，然后將該公式復(fù)制到C11、D11單元格，可得乙產(chǎn)品和丙產(chǎn)品的單位邊際貢獻(xiàn)。計(jì)算產(chǎn)品邊際貢獻(xiàn)。在B12單元格定義甲產(chǎn)品邊際貢獻(xiàn)計(jì)算公式“=B10*B11”，然后將該公式復(fù)制到C12、D12單元格，可得乙產(chǎn)品和丙產(chǎn)品的邊際貢獻(xiàn)。1.7假設(shè)分析計(jì)算利潤(rùn)總額。在B13單元格定義利潤(rùn)總額的計(jì)算公式“=SUM(B12:D12)-B7”。計(jì)算車間實(shí)際工時(shí)。在B14單元格定義A車間實(shí)際耗用工時(shí)計(jì)算公式“=SUMPRODUCT(B10:D10,B2:D2)”或“B2*B10+C2*C10+D2*D10”；在C14單元格定義B車間實(shí)際耗用工時(shí)計(jì)算公式“=SUMPRODUCT(B10:D10,B3:D3)”或“B3*B10+C3*C10+D3*D10”；在D14單元格定義C車間實(shí)際耗用工時(shí)計(jì)算公式“=SUMPRODUCT(B10:D10,B4:D4)”或“B4*B10+C4*C10+D4*D10”。1.7假設(shè)分析（3）設(shè)置規(guī)劃求解參數(shù)。在功能區(qū)“數(shù)據(jù)”選項(xiàng)卡中的“分析”命令組中單擊“規(guī)劃求解”命令，如圖1-68所示，需要設(shè)置的參數(shù)如下：1.7假設(shè)分析①目標(biāo)單元格。本例中的目標(biāo)是求解最大利潤(rùn)，因此將目標(biāo)單元格設(shè)置為“B13”。②可變單元格。本例中可變量是各產(chǎn)品產(chǎn)量，因此將可變單元格設(shè)置為“B10：D10”。③約束條件。本例中，約束條件有兩種。一種約束是各車間生產(chǎn)各產(chǎn)品耗用的實(shí)際工時(shí)不能大于該車間能夠提供的總工時(shí)；另一種約束是各產(chǎn)品產(chǎn)量應(yīng)為整數(shù)，本例中產(chǎn)品為家電產(chǎn)品，因此產(chǎn)量不能為小數(shù)。添加約束條件的具體步驟如下：1.7假設(shè)分析第一步：?jiǎn)螕簟疤砑印卑粹o，出現(xiàn)添加約束對(duì)話框，如圖1-69所示。各車間實(shí)際耗用工時(shí)應(yīng)小于等于其能夠提供的最大工時(shí)，因此約束條件為“B14：D14<=E2：E4”。單擊“添加”按鈕即可添加該約束條件。1.7假設(shè)分析

第二步：繼續(xù)在上述對(duì)話框中設(shè)置產(chǎn)品產(chǎn)量為整數(shù)的約束條件，該條件表示為“B10：D10int整數(shù)”，如圖1-70所示。單擊“添加”按鈕添加該約束條件。所有約束條件設(shè)置完畢，單擊“取消”按鈕返回規(guī)劃求解參數(shù)對(duì)話框。1.7假設(shè)分析（4）參數(shù)設(shè)置完畢，在規(guī)劃求解參數(shù)對(duì)話框中單擊“求解”按鈕進(jìn)行規(guī)劃求解，求解后出現(xiàn)圖1-71所示對(duì)話框。1.7假設(shè)分析（5）如果希望保存規(guī)劃求解結(jié)果，選擇“保存規(guī)劃求解結(jié)果”并單擊“確定”按鈕；如果不希望保存規(guī)劃求解結(jié)果，可選擇“恢復(fù)為原值”并單擊“確定”按鈕，或單擊“取消”按鈕。在此，保存求解結(jié)果，最終結(jié)果如圖1-72所示。1.7假設(shè)分析（6）規(guī)劃求解分析報(bào)告。如果需要查看規(guī)劃求解分析報(bào)告，可再次執(zhí)行規(guī)劃求解，并在規(guī)劃求解結(jié)果對(duì)話框中選擇需要的報(bào)告類型，單擊“確定”按鈕即可自動(dòng)插入一張新工作表并現(xiàn)實(shí)分析報(bào)告。1.8窗體工具1.8.1標(biāo)簽1.8.2分組框1.8.3按鈕1.8.4復(fù)選框1.8.5單選鈕1.8.6列表框1.8.7組合框1.8.8滾動(dòng)條1.8.9微調(diào)項(xiàng)1.8窗體工具

【例1-42】下面以圖1-73所示的調(diào)查表為例，來(lái)說(shuō)明窗體工具的使用方法。1.8窗體工具

1．設(shè)置控件

(1)性別先畫(huà)出一個(gè)分組框并將標(biāo)題改為“性別”，然后在其中畫(huà)兩個(gè)單選鈕以表示男、女兩個(gè)選項(xiàng)。將“男”單選鈕的值設(shè)置為已選擇，將其單元格鏈接設(shè)置為“H1”，則“女”單選鈕的值自動(dòng)設(shè)置為未選擇，單元格鏈接也自動(dòng)設(shè)置為“H1”。1.8窗體工具

(2)年齡先在區(qū)域“G1:G4”中輸入各年齡選項(xiàng)“30歲以下”、“30歲至50歲”、“50歲至60歲”、“60歲以上”。然后，在工作表中畫(huà)一個(gè)組合框，并將其數(shù)據(jù)源區(qū)域設(shè)置為“G1:G4”，單元格鏈接設(shè)置為“H2”。1.8窗體工具

(3)收入先在區(qū)域“G5:G8”中輸入各收入選項(xiàng)“10000元以下”、“10000至30000元”、“30000至100000元”、“100000元以上”。然后，在工作表中畫(huà)一個(gè)組合框，并將其數(shù)據(jù)源區(qū)域設(shè)置為“G5:G8”，單元格鏈接設(shè)置為“H3”。接著畫(huà)出三個(gè)復(fù)選框分別代表收入來(lái)源。其中“本職”復(fù)選框的初值設(shè)置為已選擇，單元格鏈接設(shè)置為“H4”；“兼職”復(fù)選框的初值設(shè)置為未選擇，單元格鏈接設(shè)置為“H5”；“其它”復(fù)選框的初值設(shè)置為未選擇，單元格鏈接設(shè)置為“H6”。1.8窗體工具

(4)期望利率假定利率范圍在1.0％至20.0%之間，且保留一位小數(shù)。先畫(huà)出一個(gè)滾動(dòng)條，并將其當(dāng)前值、最小值、最大值、步長(zhǎng)、頁(yè)步長(zhǎng)分別設(shè)置為100、10、200、1、10，將單元格鏈接設(shè)置位“H7”。因?yàn)椤癏7”中保存的是中間值，還要定義計(jì)算利率的公式，在“B9”單元格輸入公式“=H7/1000”。1.8窗體工具(5)重置按鈕重置按鈕的作用是使各項(xiàng)目的值恢復(fù)初始設(shè)置，具體設(shè)置方法如下。①先在適當(dāng)位置畫(huà)出一個(gè)按鈕，此時(shí)會(huì)出現(xiàn)對(duì)話框要求為其指定宏，單擊“錄制”按鈕。②然后會(huì)出現(xiàn)錄制新宏對(duì)話框，可保持默認(rèn)設(shè)置，直接單擊“確定”按鈕，進(jìn)入錄制狀態(tài)，同時(shí)屏幕上會(huì)出現(xiàn)停止錄制工具欄。1.8窗體工具③重新將“H1”單元格的值設(shè)置為1；將“H2”單元格的內(nèi)容全部刪除；將“H3”單元格的內(nèi)容全部刪除；將“H4”單元格的值設(shè)置為T(mén)RUE；將“H5”單元格的值設(shè)置為FALSE；將“H6”單元格的值設(shè)置為FALSE；將“H7”單元格的值設(shè)置為100。④宏錄制完畢后，在停止錄制工具欄中單擊停止錄制按鈕“”，停止宏的錄制。⑤最后，右鍵單擊該按鈕，在快捷菜單中選擇“編輯文字”，將其文本內(nèi)容改為“重置”，然后在任一單元格單擊鼠標(biāo)即可。1.8窗體工具

2.定義調(diào)查結(jié)果公式⑴性別當(dāng)選擇“男”時(shí)，鏈接單元格“H1”中保存的是1；當(dāng)選擇“女”時(shí)，鏈接單元格“H1”中保存的是2。那在此應(yīng)該如何顯示男和女呢？只要利用IF函數(shù)（函數(shù)將在1.8中介紹）就可以達(dá)到目的。在“B13”單元格輸入公式“=IF(H1=1,"男","女")”，即如果“H1”的值為1就取“男”，否則就取“女”。1.8窗體工具⑵年齡類似以上的性別，在此還要利用函數(shù)來(lái)顯示用戶選擇的年齡。在“B14”單元格輸入公式“=IF(ISBLANK(H2),”“,INDEX(G1:G4,H2))”。⑶收入收入金額的設(shè)置方法與年齡類似，只要在單元格“B15”中輸入公式“=IF(ISBLANK(H3),"",INDEX(G5:G8,H3))”即可。在“B16”單元格輸入公式“=IF(H4=TRUE,"本職","")”，在“C16”單元格輸入公式“=IF(H5,"兼職","")”，在“D16”單元格輸入公式“=IF(H6,"其它","")”。1.8窗體工具⑷期望利率因?yàn)槠谕室言凇癇9”單元格中定義，所以在“B17”單元格輸入公式“＝B9”即可。通過(guò)以上設(shè)置，用戶就可以通過(guò)各種控件進(jìn)行選擇了，同時(shí)選擇的結(jié)果會(huì)自動(dòng)顯示在調(diào)查結(jié)果中，如果單擊“重置”按鈕各控件將恢復(fù)到初始值?？梢詫⒉槐匾腉列和H列隱藏起來(lái)，并對(duì)工作表進(jìn)行保護(hù)。1.9數(shù)據(jù)的保護(hù)1.9.1保護(hù)工作簿1.保護(hù)工作簿結(jié)構(gòu)和窗口（1）打開(kāi)需要保護(hù)的工作簿。（2）在功能區(qū)“審閱”選項(xiàng)卡“更改”組中單擊【保護(hù)工作簿】按鈕，然后在出現(xiàn)的下拉菜單中選擇【保護(hù)結(jié)構(gòu)和窗口】命令，出現(xiàn)對(duì)話框，如圖1-74所示。1.9數(shù)據(jù)的保護(hù)

（3）在該對(duì)話框中設(shè)置保護(hù)對(duì)象和密碼后，單擊【確定】按鈕。如果輸入了密碼，系統(tǒng)會(huì)提示再次輸入密碼，只需再次輸入相同的密碼即可。如果希望取消對(duì)工作簿的保護(hù)，在功能區(qū)“審閱”選項(xiàng)卡“更改”組中單擊【保護(hù)工作簿】按鈕，然后在出現(xiàn)的下拉菜單中選擇【保護(hù)結(jié)構(gòu)和窗口】命令，此時(shí)出現(xiàn)“撤銷工作簿保護(hù)”對(duì)話框，在此輸入正確的保護(hù)密碼后單擊【確定】按鈕即可，如圖1-75所示。1.9數(shù)據(jù)的保護(hù)2.設(shè)置工作簿打開(kāi)權(quán)限密碼和修改權(quán)限密碼（1）保存當(dāng)前工作簿，并打開(kāi)保存對(duì)話框。（2）在保存對(duì)話框中，單擊【工具｜常規(guī)選項(xiàng)】命令，打開(kāi)常規(guī)選項(xiàng)對(duì)話框，如圖1-76所示。1.9數(shù)據(jù)的保護(hù)

（3）在此可分別設(shè)置打開(kāi)權(quán)限密碼和修改權(quán)限密碼，也可將文件設(shè)置為只讀。（4）設(shè)置完畢，單擊【確定】按鈕。1.9.2保護(hù)工作表1.保護(hù)整張工作表（1）打開(kāi)工作簿并切換至需要保護(hù)的工作表。（2）在功能區(qū)“審閱”選項(xiàng)卡“更改”組中單擊【保護(hù)工作表】按鈕，出現(xiàn)對(duì)話框，如圖1-77所示。1.9數(shù)據(jù)的保護(hù)1.9數(shù)據(jù)的保護(hù)（3）可以根據(jù)需要在“取消工作表保護(hù)時(shí)使用的密碼”框中輸入密碼。單擊【確定】按鈕，彈出“確認(rèn)密碼”對(duì)話框，在“重新輸入密碼”編輯框中再次輸入密碼，單擊【確定】按鈕，即可完成工作表保護(hù)的設(shè)置。設(shè)置了工作表保護(hù)后，如果用戶更改工作表中的數(shù)據(jù)，系統(tǒng)將彈出警告對(duì)話框。1.9數(shù)據(jù)的保護(hù)2.保護(hù)工作表中的部分區(qū)域（1）選擇工作表中不需要保護(hù)的單元格區(qū)域。（2）在功能區(qū)“開(kāi)始”選項(xiàng)卡“字體”組中單擊右下角的【】按鈕，出現(xiàn)對(duì)話框，如圖1-78所示。1.9數(shù)據(jù)的保護(hù)（3）選擇“保護(hù)”選項(xiàng)卡，去掉“鎖定”的選中狀態(tài)，然后單擊【確定】按鈕。所有單元格默認(rèn)為“鎖定”狀態(tài)，在保護(hù)工作表后處于鎖定狀態(tài)的單元格將無(wú)法編輯，如果不需要保護(hù)某些單元格，去掉這些單元格的“鎖定”狀態(tài)即可。

（4）執(zhí)行“保護(hù)工作表”操作，設(shè)置對(duì)工作表的保護(hù)。1.9數(shù)據(jù)的保護(hù)

3.設(shè)置允許用戶編輯區(qū)域（1）打開(kāi)需要保護(hù)的工作簿并切換至目標(biāo)工作表。（2）在功能區(qū)“審閱”選項(xiàng)卡“更改”組中單擊【允許用戶編輯區(qū)域】按鈕，出現(xiàn)對(duì)話框，如圖1-79所示。1.9數(shù)據(jù)的保護(hù)（3）單擊【新建】按鈕，出現(xiàn)對(duì)話框，如圖1-80所示。1.9數(shù)據(jù)的保護(hù)（4）設(shè)置區(qū)域的標(biāo)題，也可以保持默認(rèn)值；在“引用單元格”框中輸入或選取目標(biāo)區(qū)域；在“區(qū)域密碼”框中輸入保護(hù)密碼；單擊【確定】按鈕后提示再次輸入密碼，輸入完畢單擊【確定】按鈕。（5）單擊【保護(hù)工作表】按鈕對(duì)工作表進(jìn)行保護(hù)設(shè)置。（6）最后，單擊【確定】按鈕即可。1.10宏1.10.1宏簡(jiǎn)介1.10.2錄制宏

【例1-43】新建一個(gè)工作簿文件，然后在工作表中制作如圖1-82所示的表格。分別錄制以下六個(gè)宏，實(shí)現(xiàn)對(duì)該表格字體、字號(hào)、顏色的自動(dòng)設(shè)置：宏“zt_kt”，該宏的功能是將表格字體設(shè)置為宋體；宏“zt_st”，該宏的功能是將表格字體設(shè)置為楷體；宏“zh_8”，該宏的功能是將表格字號(hào)設(shè)置為8；宏“zh_10”，該宏的功能是將表格字號(hào)設(shè)置為10；宏“ys_ls”，該宏的功能是1.10宏將表格文字設(shè)置為藍(lán)色；宏“ys_hs”，該宏的功能是將表格文字設(shè)置為黑色。1.10宏以第一個(gè)宏“zt_kt”的為例，錄制步驟如下：（1）在功能區(qū)“開(kāi)發(fā)工具”選項(xiàng)卡中，單擊“錄制宏”，出現(xiàn)錄制新宏對(duì)話框，如圖1-83所示。1.10宏（2）輸入宏的名稱“zt_kt”，也可為宏指定快捷鍵，如“<Ctrl>+a”，然后單擊“確定”按鈕，進(jìn)入錄制狀態(tài)，同時(shí)“錄制宏”按鈕變?yōu)椤巴Ｖ逛浿啤?。注意，此后的操作將被一一錄制，直至停止錄制為止。?）選擇表格所在區(qū)域“A2：R31”，然后通過(guò)菜單或工具欄將其字體設(shè)置為“楷體_GB2312”，之后可單擊A1單元格取消表格區(qū)域的選中狀態(tài)。（4）該宏錄制完畢，單擊“停止錄制”按鈕。1.10宏1.10.3執(zhí)行宏1.通過(guò)工具按鈕執(zhí)行宏2.通過(guò)快捷鍵執(zhí)行宏3.通過(guò)表單工具中的命令按鈕執(zhí)行宏4.通過(guò)圖形對(duì)象執(zhí)行宏5.將已有宏添加至快速訪問(wèn)工具欄6.通過(guò)自定義工具按鈕執(zhí)行宏7.通過(guò)自定義菜單執(zhí)行宏1.10.4數(shù)字簽名1.11綜合案例1.11.1自動(dòng)折舊模型1.11綜合案例1.設(shè)置單元格的數(shù)據(jù)有效性2.設(shè)置使用年限滾動(dòng)條3.設(shè)置折舊方法下拉框4.定義公式（1）年限公式。在“A5”單元格中輸入公式“=IF(A4=”“,”“,IF(A4+1>$D$1,”“,A4+1))”。1.11綜合案例（2）本年折舊公式。在“B4”單元格中定義公式：=IF(A4="","",IF($D$2=1,SLN($B$1,$B$2,$D$1),IF($D$2=2,SYD($B$1,$B$2,$D$1,A4),VDB($B$1,$B$2,$D$1,A4-1,A4))))（3）累計(jì)折舊公式。在“C4”單元格中定義公式：=IF(A4="","",IF(A4=1,B4,B4+C3))1.11綜合案例5.分割窗體并凍結(jié)窗格

當(dāng)使用年限過(guò)大，而折舊表無(wú)法在一屏中顯示時(shí)，可以將窗口在第3行處進(jìn)行拆分，然后在功能區(qū)“視圖”選項(xiàng)卡中單擊“凍結(jié)窗格”出現(xiàn)下拉框，再選擇其中的“凍結(jié)首行”即可。此時(shí)，垂直滾動(dòng)顯示時(shí)，基本數(shù)據(jù)及標(biāo)題行將保持不動(dòng)，這樣可能更便于查看折舊記錄。1.11綜合案例6.保護(hù)工作表在該模型中，只有“B1”、“B2”、“D1”、“D2”等四個(gè)單元格允許輸入值，所以應(yīng)該將這四個(gè)單元格以外的整個(gè)工作表進(jìn)行保護(hù)，方法是：選中這四個(gè)單元格，然后在設(shè)置單元格格式對(duì)話框的“保護(hù)”頁(yè)單擊“鎖定”復(fù)選框取消其選中狀態(tài)，然后單擊“確定”按鈕。最后，對(duì)工作表進(jìn)行保護(hù)即可。1.11綜合案例1.