excel高級教程高級應(yīng)用

/Excel高級應(yīng)用教程目錄TOC\o"1-4"\h\z\u第一節(jié)EXCEL公式與函數(shù)的高級應(yīng)用 11.1數(shù)組公式與其應(yīng)用 11.1.1數(shù)組公式的輸入、編輯與刪除 1一．?dāng)?shù)組公式的輸入 1二．編輯數(shù)組公式 2三．刪除數(shù)組公式 31.1.2數(shù)組公式的應(yīng)用 3一．用數(shù)組公式計算兩個數(shù)據(jù)區(qū)域的乘積 3二．用數(shù)組公式計算多個數(shù)據(jù)區(qū)域的和 3三．用數(shù)組公式同時對多個數(shù)據(jù)區(qū)域進行相同的計算 31.2常用函數(shù)與其應(yīng)用 41.2.1SUM函數(shù)、SUMIF函數(shù)和SUMPRODUCT函數(shù) 4一．無條件求和SUM函數(shù) 4二．條件求和SUMIF函數(shù) 4三．SUMPRODUCT函數(shù) 51.2.2AVERAGE函數(shù) 51.2.3MIN函數(shù)和MAX函數(shù) 51.2.4

COUNT函數(shù)和COUNTIF函數(shù) 51.2.5

IF函數(shù) 61.2.6AND函數(shù)、OR函數(shù)和NOT函數(shù) 61.2.7LOOKUP函數(shù)、VLOOKUP函數(shù)和HLOOKUP函數(shù) 7一．LOOKUP函數(shù) 7二．VLOOKUP函數(shù) 7三．HLOOKUP函數(shù) 81.2.8

MATCH函數(shù) 81.2.9INDEX函數(shù) 8一、返回數(shù)組中指定單元格或單元格數(shù)組的數(shù)值。 8二、返回引用中指定單元格。 91.2.10

ADDRESS函數(shù) 91.2.11

INDIRECT函數(shù) 91.2.12矩陣函數(shù)——TRANSPOSE函數(shù)、MINVERSE函數(shù)和MMULT函數(shù) 10一．TRANSPOSE函數(shù) 10二．MINVERSE函數(shù) 10三．MMULT函數(shù) 101.2.13

ROUND函數(shù) 10第二節(jié)EXCEL數(shù)據(jù)分析處理 112.1數(shù)據(jù)排序 112.1.1數(shù)據(jù)排序的規(guī)則 112.1.2數(shù)據(jù)排序步驟 112.1.3自定義排序 122.2數(shù)據(jù)的查找與篩選 122.2.1記錄單查找 13一．查找數(shù)據(jù)記錄 13二．修改或刪除記錄 13三．添加新的記錄 142.2.2自動篩選與自定義篩選 14一．自動篩選 14二．自定義篩選方式 142.2.3高級篩選 15一．一般情況下的高級篩選 15二．計算條件情況下的高級篩選 162.3數(shù)據(jù)的分類與匯總 162.3.1進行分類匯總 162.3.2分類匯總的撤消 172.4

數(shù)據(jù)透視表 172.4.1建立數(shù)據(jù)透視表 172.4.2

數(shù)據(jù)的透視分析 19第三節(jié)EXCEL圖表處理 193.1圖表類型 193.2

圖表的建立 193.3圖表的編輯、修改與格式化 21一．設(shè)置坐標(biāo)、標(biāo)題、圖例等的格式 21二．改變圖表大小 21三．移動或復(fù)制圖表 21四．添加數(shù)據(jù)標(biāo)志 21五．改變圖表顏色、圖案、邊框 223.4

地區(qū)銷售分布圖表的建立 223.5

動態(tài)圖表的建立 24第四節(jié)EXCEL數(shù)據(jù)分析工具的應(yīng)用 244.1模擬運算表 244.1.1單變量模擬運算表 244.1.2雙變量模擬運算表 254.2單變量求解 254.3

規(guī)劃求解 254.3.1

求解優(yōu)化問題 264.3.2

求解方程組 274.4方案分析 284.4.1

建立方案 284.4.2

顯示方案 294.4.3

修改、刪除或增加方案 294.4.4

建立方案報告 304.5

數(shù)據(jù)分析工具庫 30第一節(jié)EXCEL公式與函數(shù)的高級應(yīng)用公式和函數(shù)是Excel最基本、最重要的應(yīng)用工具，是Excel的核心，因此，應(yīng)對公式和函數(shù)熟練掌握，才能在實際應(yīng)用中得心應(yīng)手。1.1數(shù)組公式與其應(yīng)用數(shù)組公式就是可以同時進行多重計算并返回一種或多種結(jié)果的公式。在數(shù)組公式中使用兩組或多組數(shù)據(jù)稱為數(shù)組參數(shù)，數(shù)組參數(shù)可以是一個數(shù)據(jù)區(qū)域，也可以是數(shù)組常量。數(shù)組公式中的每個數(shù)組參數(shù)必須有相同數(shù)量的行和列。1.1.1數(shù)組公式的輸入、編輯與刪除一．?dāng)?shù)組公式的輸入數(shù)組公式的輸入步驟如下：(1）選定單元格或單元格區(qū)域。如果數(shù)組公式將返回一個結(jié)果，單擊需要輸入數(shù)組公式的單元格；如果數(shù)組公式將返回多個結(jié)果，則要選定需要輸入數(shù)組公式的單元格區(qū)域。(2）輸入數(shù)組公式。(3）同時按“Crtl+Shift+Enter”組合鍵，則Excel自動在公式的兩邊加上大括號{}。特別要注意的是，第（3）步相當(dāng)重要，只有輸入公式后同時按“Crtl+Shift+Enter”組合鍵，系統(tǒng)才會把公式視為一個數(shù)組公式。否則，如果只按Enter鍵，則輸入的只是一個簡單的公式，也只在選中的單元格區(qū)域的第1個單元格顯示出一個計算結(jié)果。在數(shù)組公式中，通常都使用單元格區(qū)域引用，但也可以直接鍵入數(shù)值數(shù)組，這樣鍵入的數(shù)值數(shù)組被稱為數(shù)組常量。當(dāng)不想在工作表中按單元格逐個輸入數(shù)值時，可以使用這種方法。如果要生成數(shù)組常量，必須按如下操作：（1）直接在公式中輸入數(shù)值，并用大括號“{}”括起來。（2）不同列的數(shù)值用逗號“,”分開。（3）不同行的數(shù)值用分號“;”分開。★輸入數(shù)組常量的方法：例如，要在單元格A1:D1中分別輸入10，20，30和40這4個數(shù)值，則可采用下述的步驟：（1）選取單元格區(qū)域A1:D1，如圖2-1所示。圖1-1

選取單元格區(qū)域A1:D1（2）在公式編輯欄中輸入數(shù)組公式“={10,20,30,40}”，如圖2-2所示。圖1-2

在編輯欄中輸入數(shù)組公式（3）同時按Ctrl+Shift+Enter組合鍵，即可在單元格A1、B1、C1、D1中分別輸入了10、20、30、40，如圖2-3所示。假若要在單元格A1、B1、C1、D1、A2、B2、C2、D2中分別輸入10、20、30、40、50、60、70、80，則可以采用下述的方法：圖1-3

同時按Ctrl+Shift+Enter組合鍵，得到數(shù)組常量（1）選取單元格區(qū)域A1:D2，如圖2-4所示。圖1-4

選取單元格區(qū)域A1:D2（2）在編輯欄中輸入公式“={10,20,30,40;50,60,70,80}”，如圖2-5所示。圖1-5

在編輯欄中輸入數(shù)組公式（3）按Ctrl+Shift+Enter組合鍵，就在單元格A1、B1、C1、D1、A2、B2、C2、D2中分別輸入了10、20、30、40和50、60、70、80，如圖2-6所示。圖1-6

同時按Ctrl+Shift+Enter組合鍵，得到數(shù)組常量★輸入公式數(shù)組的方法例如，在單元格A3:D3中均有相同的計算公式，它們分別為單元格A1:D1與單元格A2:D2中數(shù)據(jù)的和，即單元格A3中的公式為“=A1+A2”，單元格B3中的公式為“=B1+B2”，…，則可以采用數(shù)組公式的方法輸入公式，方法如下：（1）選取單元格區(qū)域A3:D3，如圖2-7所示。（2）在公式編輯欄中輸入數(shù)組公式“=A1:D1+A2:D2”，如圖2-8所示。圖1-7

選取單元格區(qū)域A3:D3圖1-8

在編輯欄中輸入數(shù)組公式（3）同時按Ctrl+Shift+Enter組合鍵，即可在單元格A3:D3中得到數(shù)組公式“=A1:D1+A2:D2”，如圖2-9所示。圖1-9

同時按Ctrl+Shift+Enter組合鍵，得到數(shù)組公式二．編輯數(shù)組公式數(shù)組公式的特征之一就是不能單獨編輯、清除或移動數(shù)組公式所涉與的單元格區(qū)域中的某一個單元格。若在數(shù)組公式輸入完畢后發(fā)現(xiàn)錯誤需要修改，則需要按以下步驟進行：（1）在數(shù)組區(qū)域中單擊任一單元格。（2）單擊公式編輯欄，當(dāng)編輯欄被激活時，大括號“{}”在數(shù)組公式中消失。（3）編輯數(shù)組公式內(nèi)容。（4）修改完畢后，按“Crtl+Shift+Enter”組合鍵。要特別注意不要忘記這一步。三．刪除數(shù)組公式刪除數(shù)組公式的步驟是：首先選定存放數(shù)組公式的所有單元格，然后按Delete鍵。1.1.2數(shù)組公式的應(yīng)用一．用數(shù)組公式計算兩個數(shù)據(jù)區(qū)域的乘積【例2-1】如圖2-10所示，已經(jīng)知道12個月的銷售量和產(chǎn)品單價，則可以利用數(shù)組公式計算每個月的銷售額，步驟如下：圖1-10

用數(shù)組公式計算銷售額（1）選取單元格區(qū)域B4:M4。（2）輸入公式“=B2:M2*B3:M3”。（3）按“Crtl+Shift+Enter”組合鍵。如果需要計算12個月的月平均銷售額，可在單元格B5中輸入公式“=AVERAGE(B2:M2*B3:M3)”，然后按“Crtl+Shift+Enter”組合鍵即可，如圖2-10所示。在數(shù)組公式中，也可以將某一常量與數(shù)組公式進行加、減、乘、除，也可以對數(shù)組公式進行乘冪、開方等運算。例如在圖2-10中，每月的單價相同，故我們也可以在單元格B4:M4中輸入公式“=B2:M2*28”，然后按“Crtl+Shift+Enter”組合鍵；在單元格B5中輸入公式“=AVERAGE(B2:M2*28)”，然后按“Crtl+Shift+Enter”組合鍵。在使用數(shù)組公式計算時，最好將不同的單元格區(qū)域定義不同的名稱，如在圖2-10中，將單元格區(qū)域B2:M2定義名稱為“銷售量”，單元格區(qū)域B3:M3定義名稱為“單價”，則各月的銷售額計算公式為“=銷售量*單價”，月平均銷售額計算公式為“=AVERAGE(銷售量*單價)”，這樣不容易出錯。二．用數(shù)組公式計算多個數(shù)據(jù)區(qū)域的和如果需要把多個對應(yīng)的行或列數(shù)據(jù)進行相加或相減的運算，并得出與之對應(yīng)的一行或一列數(shù)據(jù)時，也可以使用數(shù)組公式來完成?！纠?-2】某企業(yè)2002年銷售的3種產(chǎn)品的有關(guān)資料如圖2-11所示，則可以利用數(shù)組公式計算該企業(yè)2002年的總銷售額，方法如下：圖1-11

某企業(yè)的月銷售總額計算（1）選取單元格區(qū)域C8:N8。（2）輸入公式“=C2:N2*C3:N3+C4:N4*C5:N5+C6:N6*C7:N7”。（3）按“Crtl+Shift+Enter”組合鍵。三．用數(shù)組公式同時對多個數(shù)據(jù)區(qū)域進行相同的計算【例2-3】某公司對現(xiàn)有三種商品實施降價銷售，產(chǎn)品原價如圖2-12所示，降價幅度為20%，則可以利用數(shù)組公式進行計算，步驟如下：圖1-12

產(chǎn)品降價計算（1）選取單元格區(qū)域G3:I8。（2）輸入公式“=B3:D8*(1-20%)”。（3）按Crtl+Shift+Enter組合鍵。此外，當(dāng)對結(jié)構(gòu)相同的不同工作表數(shù)據(jù)進行合并匯總處理時，利用上述方法也將是非常方便的。有關(guān)不同工作表單元格的引用可參閱第1章的有關(guān)內(nèi)容，關(guān)于數(shù)據(jù)的合并計算可參閱本章2.3.5節(jié)的內(nèi)容。1.2常用函數(shù)與其應(yīng)用在第1節(jié)中介紹了一些有關(guān)函數(shù)的基本知識，本節(jié)對在財務(wù)管理中常用的一般函數(shù)應(yīng)用進行說明，其他有關(guān)的專門財務(wù)函數(shù)將在以后的有關(guān)章節(jié)中分別予以介紹。1.2.在財務(wù)管理中，應(yīng)用最多的是求和函數(shù)。求和函數(shù)有三個：無條件求和SUM函數(shù)、條件求和SUMIF函數(shù)和多組數(shù)據(jù)相乘求和SUMPRODUCT函數(shù)。一．無條件求和SUM函數(shù)該函數(shù)是求30個以內(nèi)參數(shù)的和。公式為=SUM（參數(shù)1，參數(shù)2，…，參數(shù)N）當(dāng)對某一行或某一列的連續(xù)數(shù)據(jù)進行求和時，還可以使用工具欄中的自動求和按鈕。例如，在例2-1中，求全年的銷售量，則可以單擊單元格N2，然后再單擊求和按鈕，按回車鍵即可，如圖1-13所示。圖1-13

自動求和二．條件求和SUMIF函數(shù)SUMIF函數(shù)的功能是根據(jù)指定條件對若干單元格求和，公式：=SUMIF(range,criteria,sum_range)式中range—用于條件判斷的單元格區(qū)域；criteria—確定哪些單元格將被相加求和的條件，其形式可以為數(shù)字、表達式或文本；sum_range—需要求和的實際單元格。只有當(dāng)range中的相應(yīng)單元格滿足條件時，才對sum_range中的單元格求和。若省略sum_range,則直接對range中的單元格求和。利用這個函數(shù)進行分類匯總是很有用的?！纠?-4】某商場2月份銷售的家電流水記錄如圖1-14所示，則在單元格I3中輸入公式“=SUMIF(C3:C10,211,F3:F10)”，單元格I4中輸入公式“=SUMIF(C3:C10,215,F3:F10)”，在單元格I5中輸入公式“=SUMIF(C3:C10,212,F3:F10)”，單元格I6中輸入公式“=SUMIF(C3:C10,220,F3:F10)”，即可得到分類銷售額匯總表。圖1-14

商品銷售額分類匯總SUMIF函數(shù)的對話框如圖1-15所示。圖1-15

SUMIF函數(shù)對話框當(dāng)需要分類匯總的數(shù)據(jù)很大時，利用SUMIF函數(shù)是很方便的。三．SUMPRODUCT函數(shù)SUMPRODUCT函數(shù)的功能是在給定的幾組數(shù)組中，將數(shù)組間對應(yīng)的元素相乘，并返回乘積之和。公式為=SUMPRODUCT(array1,array2,array3,…)式中，array1,array2,array3,...為1至30個數(shù)組。需注意的是，數(shù)組參數(shù)必須具有相同的維數(shù)，否則，函數(shù)SUMPRODUCT將返回錯誤值#VALUE!。對于非數(shù)值型的數(shù)組元素將作為0處理。例如，在例1-2中，要計算2002年產(chǎn)品A的銷售總額，可在任一單元格（比如O2）中輸入公式“=SUMPRODUCT(C2:N2,C3:N3)”即可。1.2.2AAVERAGE函數(shù)的功能是計算給定參數(shù)的算術(shù)平均值。公式為=AVERAGE（參數(shù)1，參數(shù)2，…，參數(shù)N）函數(shù)中的參數(shù)可以是數(shù)字，或者是涉與數(shù)字的名稱、數(shù)組或引用。如果數(shù)組或單元格引用參數(shù)中有文字、邏輯值或空單元格，則忽略其值。但是，如果單元格包含零值則計算在內(nèi)。AVERAGE函數(shù)的使用方法與SUM函數(shù)相同，此處不再介紹。1.2.3MIN函數(shù)和MAX函數(shù)MIN函數(shù)的功能是給定參數(shù)表中的最小值，MAX函數(shù)的功能是給定參數(shù)表中的最大值。公式為=MIN（參數(shù)1，參數(shù)2，…，參數(shù)N）=MAX（參數(shù)1，參數(shù)2，…，參數(shù)N）函數(shù)中的參數(shù)可以是數(shù)字、空白單元格、邏輯值或表示數(shù)值的文字串。例如，MIN（3，5，12，32）=3；MAX（3，5，12，32）=32。1.2.4

COUNT函數(shù)和COUNTIF函數(shù)COUNT函數(shù)的功能是計算給定區(qū)域內(nèi)數(shù)值型參數(shù)的數(shù)目。公式為：=COUNT（參數(shù)1，參數(shù)2，…，參數(shù)N）COUNTIF函數(shù)的功能是計算給定區(qū)域內(nèi)滿足特定條件的單元格的數(shù)目。公式為：=COUNTIF（range，criteria）式中range—需要計算其中滿足條件的單元格數(shù)目的單元格區(qū)域；criteria—確定哪些單元格將被計算在內(nèi)的條件，其形式可以為數(shù)字、表達式或文本。COUNT函數(shù)和COUNTIF函數(shù)在數(shù)據(jù)匯總統(tǒng)計分析中是非常有用的函數(shù)。1.2.5

IF函數(shù)IF函數(shù)也稱條件函數(shù)，它根據(jù)參數(shù)條件的真假，返回不同的結(jié)果。在實踐中，經(jīng)常使用函數(shù)IF對數(shù)值和公式進行條件檢測。公式為=IF(logical_test,value_if_true,value_if_false)式中

logical_test—條件表達式，其結(jié)果要么為TRUE，要么為FALSE，它可使用任何比較運算符；value_if_true—logical_test為TRUE時返回的值；value_if_false—logical_test為FALSE時返回的值。IF函數(shù)在財務(wù)管理中具有非常廣泛的應(yīng)用?！纠?-5】例如，某企業(yè)對各個銷售部門的銷售業(yè)績進行評價，評價標(biāo)準(zhǔn)與各個銷售部門在2002年的銷售業(yè)績匯總?cè)鐖D1-16所示，評價計算步驟如下：圖1-16

銷售部門業(yè)績評價（1）選定單元格區(qū)域C3:C12。（2）直接輸入以下公式：“=IF(B3:B12<100000,"差",IF(B3:B12<200000,"一般",IF(B3:B12<300000,"好",IF(B3:B12<400000,"較好","很好"))))”。（3）按“Crtl+Shift+Enter”組合鍵。則各個銷售部門的銷售業(yè)績評價結(jié)果就顯示在單元格域C3:C12中。也可以直接在單元格C3中輸入公式“=IF(B3<100000,"差",IF(B3<200000,"一般",IF(B3<300000,"好",IF(B3<400000,"較好","很好"))))”后，將其向下填充復(fù)制到C4～C12單元格中。1.2.6A這3個函數(shù)的用法如下：=AND（條件1，條件2，…，條件N）=OR（條件1，條件2，…，條件N）=NOT（條件）AND函數(shù)表示邏輯與，當(dāng)所有條件都滿足時（即所有參數(shù)的邏輯值都為真時），AND函數(shù)返回TRUE，否則，只要有一個條件不滿足即返回FALSE。OR函數(shù)表示邏輯或，只要有一個條件滿足時，OR函數(shù)返回TRUE，只有當(dāng)所有條件都不滿足時才返回FALSE。NOT函數(shù)只有一個邏輯參數(shù)，它可以計算出TRUE或FALSE的邏輯值或邏輯表達式。如果邏輯值為FALSE，函數(shù)NOT返回TRUE；如果邏輯值為TRUE，函數(shù)NOT返回FALSE。這3個函數(shù)一般與IF函數(shù)結(jié)合使用?！纠?-6】某企業(yè)根據(jù)各銷售部門的銷售額與銷售費用確定獎金提成比例與提取額，若銷售額大于300000元且銷售費用占銷售額的比例不超過1%，則獎金提取比例為15%，否則為10%，則計算過程如下（如圖2-17所示）：（1）在單元格D3中輸入公式“=IF(AND(B3>300000,C3/B3<1%),15%,10%)”，將其向下填充復(fù)制到D4～C10單元格中。（2）選取單元格區(qū)域E3:E10，輸入公式“=B3:B10*D3:D10”，按“Crtl+Shift+Enter”組合鍵。則各銷售部門的銷售獎金提成比例與獎金提取額如圖1-17所示。圖1-17

獎金提成比例與提取額的計算1.2.7LOOKUP函數(shù)、VLOOKUP函數(shù)和HLOOKUP函數(shù)一．LOOKUP函數(shù)LOOKUP函數(shù)的功能是返回向量（單行區(qū)域或單列區(qū)域）或數(shù)組中的數(shù)值。函數(shù)LOOKUP有兩種語法形式：向量和數(shù)組。函數(shù)LOOKUP的向量形式是在單行區(qū)域或單列區(qū)域（向量）中查找數(shù)值，然后返回第二個單行區(qū)域或單列區(qū)域中相同位置的數(shù)值；函數(shù)LOOKUP的數(shù)組形式在數(shù)組的第一行或第一列查找指定的數(shù)值，然后返回數(shù)組的最后一行或最后一列中相同位置的數(shù)值。（1）向量形式：公式為=LOOKUP(lookup_value,lookup_vector,result_vector)式中l(wèi)ookup_value—函數(shù)LOOKUP在第一個向量中所要查找的數(shù)值，它可以為數(shù)字、文本、邏輯值或包含數(shù)值的名稱或引用；lookup_vector—只包含一行或一列的區(qū)域lookup_vector的數(shù)值可以為文本、數(shù)字或邏輯值；result_vector—為只包含一行或一列的區(qū)域其大小必須與lookup_vector相同。（2）數(shù)組形式：公式為=LOOKUP(lookup_value,array)式中array—包含文本、數(shù)字或邏輯值的單元格區(qū)域或數(shù)組它的值用于與lookup_value進行比較。例如：LOOKUP(5.2,{4.2,5,7,9,10})=5。注意：lookup_vector的數(shù)值必須按升序排列，否則函數(shù)LOOKUP不能返回正確的結(jié)果。文本不區(qū)分大小寫。如果函數(shù)LOOKUP找不到lookup_value，則查找lookup_vector中小于或等于lookup_value的最大數(shù)值。如果lookup_value小于lookup_vector中的最小值，函數(shù)LOOKUP返回錯誤值#N/A。二．VLOOKUP函數(shù)VLOOKUP函數(shù)的功能是在表格或數(shù)值數(shù)組的首列查找指定的數(shù)值，并由此返回表格或數(shù)組當(dāng)前行中指定列處的數(shù)值。公式為：=VLOOKUP（lookup_value，table_array，col_index_num，range_lookup）式中l(wèi)ookup_value—需要在數(shù)據(jù)表第一列中查找的數(shù)值，lookup_value可以為數(shù)值、引用或文字串；table_array—需要在其中查找數(shù)據(jù)的數(shù)據(jù)表，可以使用對區(qū)域或區(qū)域名稱的引用，例如數(shù)據(jù)庫或數(shù)據(jù)清單；如果range_lookup為TRUE，則table_array的第一列中的數(shù)值必須按升序排列，否則函數(shù)VLOOKUP不能返回正確的數(shù)值，如果range_lookup為FALSE，table_array不必進行排序。table_array的第一列中的數(shù)值可以為文本、數(shù)字或邏輯值，且不區(qū)分文本的大小寫；col_index_num—table_array中待返回的匹配值的列序號；col_index_num為1時，返回table_array第一列中的數(shù)值；col_index_num為2時，返回table_array第二列中的數(shù)值，以此類推。如果col_index_num小于1，函數(shù)VLOOKUP返回錯誤值#VALUE!；如果col_index_num大于table_array的列數(shù)，函數(shù)VLOOKUP返回錯誤值#REF!。range_lookup—邏輯值，指明函數(shù)VLOOKUP返回時是精確匹配還是近似匹配。如果其為TRUE或省略，則返回近似匹配值，也就是說，如果找不到精確匹配值，則返回小于lookup_value的最大數(shù)值；如果range_value為FALSE，函數(shù)VLOOKUP將返回精確匹配值。如果找不到，則返回錯誤值#N/A。VLOOKUP函數(shù)在財務(wù)管理與分析中是一個經(jīng)常用到的函數(shù)，因此熟悉它將會帶來很大便利。在以后的有關(guān)章節(jié)中會經(jīng)常用到它。例如，假設(shè)單元格A1:A4中的數(shù)據(jù)分別為1、30、80和90，單元格B1:B4中的數(shù)據(jù)分別為400、500、600和700，則有：VLOOKUP(5,A1:B4,2)=400，VLOOKUP(30,A1:B4,2)=500，VLOOKUP(79,A1:B4,2)=500，VLOOKUP(92,A1:B4,2)=700。三．HLOOKUP函數(shù)HLOOKUP函數(shù)的功能是從表格或數(shù)值數(shù)組的首行查找指定的數(shù)值，并由此返回表格或數(shù)組當(dāng)前列中指定行處的數(shù)值。公式為：=(lookup_value，table_array，row_index_num，range_lookup)式中

row_index_num—table_array中待返回的匹配值的行序號。row_index_num為1時，返回table_array第一行的數(shù)值，row_index_num為2時，返回table_array第二行的數(shù)值，以此類推。如果row_index_num小于1，函數(shù)HLOOKUP返回錯誤值#VALUE!；如果row_index_num大于table_array的行數(shù)，函數(shù)HLOOKUP返回錯誤值#REF!。式中的其他參數(shù)含義參閱VLOOKUP函數(shù)。HLOOKUP函數(shù)與VLOOKUP函數(shù)的區(qū)別是：當(dāng)比較值位于數(shù)據(jù)表的首行，并且要查找下面給定行中的數(shù)據(jù)時，使用函數(shù)HLOOKUP；當(dāng)比較值位于要進行數(shù)據(jù)查找的左邊一列時，使用函數(shù)VLOOKUP。VLOOKUP函數(shù)在首列進行檢索，先得到的是行號，然后根據(jù)col_index_num參數(shù)指定的列標(biāo)返回指定的單元格數(shù)值；而HLOOKUP函數(shù)在首行進行檢索，先得到的是列標(biāo)，然后根據(jù)row_index_num參數(shù)指定的行號返回指定的單元格數(shù)值。1.2.8

MATCH函數(shù)MATCH函數(shù)的功能是返回在指定方式下與指定數(shù)值匹配的數(shù)組中元素的相應(yīng)位置。公式為：=MATCH(lookup_value,lookup_array,match_type)式中

lookup_value—需要在數(shù)據(jù)表中查找的數(shù)值，可以是數(shù)值（數(shù)字、文本或邏輯值）或?qū)?shù)字、文本或邏輯值的單元格引用；lookup_array—可能包含所要查找的數(shù)值的連續(xù)單元格區(qū)域，可以是數(shù)組或數(shù)組引用；match_type—數(shù)字-1、0或1，它指明Excel如何在lookup_array中查找lookup_value。查找方式如下：當(dāng)match_type為-1時，lookup_array必須按降序排列，函數(shù)MATCH查找大于或等于lookup_value的最小數(shù)值；當(dāng)match_type為0時，lookup_array可以按任何順序排列，函數(shù)MATCH查找等于lookup_value的第一個數(shù)值；當(dāng)match_type為1時，lookup_array必須按升序排列，函數(shù)MATCH查找小于或等于lookup_value的最大數(shù)值。例如，MATCH(12,{23,43,12,55},0)=3，MATCH(40,{23,43,12,55})=1。EXCEL公式與函數(shù)的高級應(yīng)用(5)1.2.9INDEX函數(shù)INDEX函數(shù)的功能是返回表格或區(qū)域中的數(shù)值或?qū)?shù)值的引用。INDEX函數(shù)有以下兩種形式：一、返回數(shù)組中指定單元格或單元格數(shù)組的數(shù)值。公式為=INDEX(array,row_num,column_num)

式中

array—單元格區(qū)域或數(shù)組常數(shù)；row_num—數(shù)組中某行的行序號，函數(shù)從該行返回數(shù)值。如果省略row_num，則必須有column_num；column_num—數(shù)組中某列的列序號，函數(shù)從該列返回數(shù)值。如果省略column_num，則必須有row_num。需要注意的是：如果同時使用row_num和column_num，函數(shù)INDEX返回row_num和column_num交叉處的單元格的數(shù)值。如果數(shù)組只包含一行或一列，則相對應(yīng)的參數(shù)row_num或column_num為可選。如果數(shù)組有多行和多列，但只使用row_num或column_num，函數(shù)INDEX返回數(shù)組中的整行或整列，且返回值也為數(shù)組。如果將row_num或column_num設(shè)置為0，函數(shù)INDEX則分別返回整個列或行的數(shù)組數(shù)值。如果需要使用以數(shù)組形式返回的數(shù)值時，請在一個水平單元格區(qū)域中將函數(shù)INDEX作為數(shù)組公式輸入。此外，row_num和column_num必須指向array中的某一單元格，否則，函數(shù)INDEX返回錯誤值#REF!。例如：INDEX({1,2;3,4},2,2)=4。如果作為數(shù)組公式輸入，則：INDEX({1,2;3,4},0,2)={2;4}二、返回引用中指定單元格。公式為：INDEX(reference,row_num,column_num,area_num)式中

reference—對一個或多個單元格區(qū)域的引用；如果為引用輸入一個不連續(xù)的選定區(qū)域，必須用括號括起來。如果引用中的每個區(qū)域只包含一行或一列，則相應(yīng)的參數(shù)row_num或column_num分別為可選項。例如，對于單行的引用，可以使用函數(shù)INDEX(reference,column_num)。row_num—引用中某行的行序號，函數(shù)從該行返回一個引用；column_num—引用中某列的列序號，函數(shù)從該列返回一個引用；area_num—選擇引用中的一個區(qū)域，并返回該區(qū)域中row_num和column_num的交叉區(qū)域。選中或輸入的第一個區(qū)域序號為1，第二個為2，以此類推。如果省略area_num，函數(shù)INDEX使用區(qū)域1。說明：row_num、column_num和area_num必須指向reference中的單元格,否則，函數(shù)INDEX返回錯誤值#REF!。如果省略row_num和column_num，函數(shù)INDEX返回由area_num所指定的區(qū)域。函數(shù)INDEX的結(jié)果為一個引用，且在其他公式中也被解釋為引用。根據(jù)公式的需要，函數(shù)INDEX的返回值可以作為引用或是數(shù)值。例如，公式CELL("width",INDEX(A1:B2,1,2))等價于公式CELL("width",B1)。CELL函數(shù)將函數(shù)INDEX的返回值作為單元格引用。而在另一方面，公式2*INDEX(A1:B2,1,2)將函數(shù)INDEX的返回值解釋為B1單元格中的數(shù)字。1.2.10

ADDRESS函數(shù)ADDRESS函數(shù)的功能是按照給定的行號和列標(biāo)，建立文本類型的單元格地址。公式為=ADDRESS(row_num,column_num,abs_num,a1,sheet_text)式中

row_num—在單元格引用中使用的行號；column_num—在單元格引用中使用的列標(biāo)；abs_num—指明返回的引用類型，其中：當(dāng)為1或省略時為絕對引用，當(dāng)為2時為絕對行號，相對列標(biāo)，當(dāng)為3時為相對行號，絕對列標(biāo)，當(dāng)為4時為相對引用；a1—用以指明A1或R1C1引用樣式的邏輯值。如果A1為TRUE或省略，函數(shù)ADDRESS返回A1樣式的引用，如果A1為FALSE，函數(shù)ADDRESS返回R1C1樣式的引用；sheet_text—一文本，指明作為外部引用的工作表的名稱，如果省略sheet_text，則不使用任何工作表名。例如，ADDRESS(2,3)等于“$C$2”；ADDRESS(2,3,2)等于“C$2”。1.2.11

INDIRECT函數(shù)INDIRECT函數(shù)的功能是返回由文字串指定的引用。此函數(shù)立即對引用進行計算，并顯示其內(nèi)容。當(dāng)需要更改公式中單元格的引用，而不更改公式本身時，可使用此函數(shù)。公式為：=INDIRECT(ref_text,a1)式中

ref_text—對單元格的引用，此單元格可以包含A1樣式的引用、R1C1樣式的引用、定義為引用的名稱或?qū)ξ淖执畣卧竦囊茫绻鹯ef_text不是合法的單元格的引用，函數(shù)INDIRECT返回錯誤值#REF!；a1——邏輯值，指明包含在單元格ref_text中的引用的類型，如果a1為TRUE或省略，ref_text被解釋為A1樣式的引用，如果a1為FALSE，ref_text被解釋為R1C1樣式的引用。需要注意的是，如果ref_text是對另一個工作簿的引用（外部引用），則那個工作簿必須被打開。如果源工作簿沒有打開，函數(shù)INDIRECT返回錯誤值#REF!。例如：如果單元格A1包含文本"B2"，且單元格B2包含數(shù)值1.333，則：INDIRECT($A$1)=1.333。上述介紹的幾個查找函數(shù)LOOKUP、VLOOKUP、HLOOKUP、MATCH、INDEX、ADDRESS、INDIRECT等在財務(wù)分析與決策、預(yù)測與建立動態(tài)圖表等中是非常有用的。1.2.12矩陣函數(shù)——TRANSPOSE函數(shù)、MINVERSE函數(shù)和MMULT函數(shù)一．TRANSPOSE函數(shù)TRANSPOSE函數(shù)的功能是求矩陣的轉(zhuǎn)置矩陣。公式為=TRANSPOSE(array)式中，Array—需要進行轉(zhuǎn)置的數(shù)組或工作表中的單元格區(qū)域。函數(shù)TRANSPOSE必須在某個區(qū)域中以數(shù)組公式的形式輸入，該區(qū)域的行數(shù)和列數(shù)分別與array的列數(shù)和行數(shù)相同?！纠?-7】假設(shè)矩陣A中的值如圖1-18中單元格區(qū)域A2:C5，求其轉(zhuǎn)置矩陣的步驟如下：圖1-18

求轉(zhuǎn)置矩陣（1）選取存放轉(zhuǎn)置矩陣結(jié)果的單元格區(qū)域，如E2:H4。（2）單擊工具欄上的【粘貼函數(shù)】按鈕，在【粘貼函數(shù)】對話框中選取函數(shù)TRANSPOSE，在該函數(shù)對話框中輸入（可用鼠標(biāo)拾?。﹩卧馎2:C5，按“Crtl+Shift+Enter”組合鍵，即得轉(zhuǎn)置矩陣如圖2-18所示。利用TRANSPOSE函數(shù)可以把工作表中的某些行（或列）排列的數(shù)據(jù)轉(zhuǎn)換成列（或行）排列的數(shù)據(jù)。例如，由于工作需要，要把工作表中的某些行數(shù)據(jù)改為列數(shù)據(jù)，若一個一個地改動數(shù)據(jù)，將是很麻煩也很費時的，而利用TRANSPOSE函數(shù)則可以很輕松地進行這項工作。但需要注意的是，利用TRANSPOSE函數(shù)對行（列）數(shù)據(jù)進行轉(zhuǎn)換，則無法單獨修改其中轉(zhuǎn)換單元格區(qū)域中的某單元格的數(shù)據(jù)。二．MINVERSE函數(shù)MINVERSE函數(shù)的功能是返回矩陣的逆矩陣。公式為=MINVERSE(array)式中，array—具有相等行列數(shù)的數(shù)值數(shù)組或單元格區(qū)域。MINVERSE函數(shù)的使用方法與TRANSPOSE函數(shù)是一樣的。在求解線性方程組時，常常用到MINVERSE函數(shù)。三．MMULT函數(shù)MMULT函數(shù)的功能是返回兩數(shù)組的矩陣乘積。結(jié)果矩陣的行數(shù)與array1的行數(shù)相同，列數(shù)與array2的列數(shù)相同。公式為=MMULT(array1,array2)式中

array1,array2—要進行矩陣乘法運算的兩個數(shù)組。array1的列數(shù)必須與array2的行數(shù)相同，而且兩個數(shù)組中都只能包含數(shù)值。array1和array2可以是單元格區(qū)域、數(shù)組常數(shù)或引用。如果單元格是空白單元格或含有文字串，或是array1的行數(shù)與array2的列數(shù)不相等時，則函數(shù)MMULT返回錯誤值#VALUE!。同樣地，由于返回值為數(shù)組公式，故必須以數(shù)組公式的形式輸入。以例1-7的原矩陣和其轉(zhuǎn)置矩陣為例，它們的乘積矩陣求解方法如下：（1）選取存放乘積矩陣結(jié)果的單元格區(qū)域，如J2:L5。（2）單擊工具欄上的【粘貼函數(shù)】按鈕，在【粘貼函數(shù)】對話框中選取函數(shù)MMULT，在該函數(shù)對話框中的array1欄中輸入（可用鼠標(biāo)拾取）單元格區(qū)域A2:C5，在array2欄中輸入單元格區(qū)域E2:H4，然后按“Crtl+Shift+Enter”組合鍵，即得矩陣的乘積如圖2-18所示。1.2.13

ROUND函數(shù)ROUND函數(shù)的功能是返回某個數(shù)字按指定位數(shù)舍入后的數(shù)字。公式為=ROUND(number,num_digits)式中

number—需要進行舍入的數(shù)字；num_digits—指定的位數(shù)，按此位數(shù)進行舍入。如果num_digits大于0，則舍入到指定的小數(shù)位；如果num_digits等于0，則舍入到最接近的整數(shù)；如果num_digits小于0，則在小數(shù)點左側(cè)進行舍入。利用ROUND函數(shù)可以防止利用格式工具欄上的【增加小數(shù)位數(shù)】或【減少小數(shù)位數(shù)】所帶來的看起來“假數(shù)據(jù)”問題的出現(xiàn)，使得工作表上顯示的數(shù)據(jù)真實可靠。實際上，如果需要調(diào)整數(shù)據(jù)的小數(shù)位數(shù)，最好使用ROUND函數(shù)，而不要使用格式工具欄上的【增加小數(shù)位數(shù)】或【減少小數(shù)位數(shù)】按鈕。例如，若單元格A1中的數(shù)據(jù)為14.3772，若使用格式工具欄上的【減少小數(shù)位數(shù)】按鈕將小數(shù)位數(shù)設(shè)為兩位，則單元格A1中的數(shù)據(jù)顯示為14.38，看起來似乎單元格A1的數(shù)據(jù)為14.38，但實際上仍為14.3772。若在單元格B1中輸入公式“=3*A1”，則單元格B1中的數(shù)據(jù)顯示為43.13，也許“不明真相”的人認(rèn)為單元格B1的數(shù)據(jù)算錯了（14.38乘以3應(yīng)該等于43.14），但實際上單元格的數(shù)據(jù)為43.1316，這種看起來的“假”數(shù)據(jù)可能會對實際工作帶來不便。因此，正確的方法應(yīng)是：單元格B1中應(yīng)輸入公式“=ROUND(3*ROUND(A1,2),2)”，結(jié)果為43.14，即先將單元格A1的數(shù)據(jù)用函數(shù)ROUND四舍五入，然后再對計算后的數(shù)據(jù)四舍五入。第二節(jié)EXCEL數(shù)據(jù)分析處理Excel提供了強大的數(shù)據(jù)分析處理功能，利用它們可以實現(xiàn)對數(shù)據(jù)的排序、分類匯總、篩選與數(shù)據(jù)透視等操作。在進行數(shù)據(jù)分析處理之前，首先必須注意以下幾個問題：（1）避免在數(shù)據(jù)清單中存在有空行和空列。（2）避免在單元格的開頭和末尾鍵入空格。（3）避免在一張工作表中建立多個數(shù)據(jù)清單，每張工作表應(yīng)僅使用一個數(shù)據(jù)清單。（4）工作表的數(shù)據(jù)清單應(yīng)與其他數(shù)據(jù)之間至少留出一個空列和一個空行，以便于檢測和選定數(shù)據(jù)清單。（5）關(guān)鍵數(shù)據(jù)應(yīng)置于數(shù)據(jù)清單的頂部或底部。2.1數(shù)據(jù)排序2.1.1數(shù)據(jù)排序的規(guī)則Excel允許對字符、數(shù)字等數(shù)據(jù)按大小順序進行升序或降序排列，要進行排序的數(shù)據(jù)稱之為關(guān)鍵字。不同類型的關(guān)鍵字的排序規(guī)則如下：數(shù)值：按數(shù)值的大小。字母：按字母先后順序。日期：按日期的先后。漢字：按漢語拼音的順序或按筆畫順序。邏輯值：升序時FALSE排在TRUE前面，降序時相反?？崭瘢嚎偸桥旁谧詈蟆?.1.2數(shù)據(jù)排序步驟（1）單擊數(shù)據(jù)區(qū)中要進行排序的任意單元格。（2）單擊【數(shù)據(jù)】菜單，選擇【排序】項，系統(tǒng)將彈出【排序】對話框，如圖1-35所示。圖1-35

【排序】對話框（3）在【排序】對話框中用下拉列表框選擇要排序的關(guān)鍵字，關(guān)鍵字有“主要關(guān)鍵字”、“次要關(guān)鍵字”和“第三關(guān)鍵字”，根據(jù)需要分別選擇不同的關(guān)鍵字；（4）單擊【確定】按鈕，數(shù)據(jù)就按要求進行了排序。當(dāng)只有一個關(guān)鍵字時，可以單擊工具欄上的升序按鈕或降序按鈕，進行自動排序。2.1.3自定義排序在有些情況下，對數(shù)據(jù)的排序順序可能非常特殊，既不是按數(shù)值大小次序、也不是按漢字的拼音順序或筆畫順序，而是按照指定的特殊次序，如對總公司的各個分公司按照要求的順序進行排序，按產(chǎn)品的種類或規(guī)格排序等等，這時就需要自定義排序。利用自定義排序方法進行排序，首先應(yīng)建立自定義序列，其方法可參閱第1章的有關(guān)內(nèi)容。建立好自定義序列后，即可對數(shù)據(jù)進行排序，方法是：單擊數(shù)據(jù)區(qū)中要進行排序的任意單元格，單擊【數(shù)據(jù)】菜單，選擇【排序】項，在彈出的【排序】對話框中單擊【選項】按鈕，系統(tǒng)彈出【排序選項】對話框，如圖1-36所示，在【自定義排序次序】的下拉列表中，選擇前面建立的自定義序列，然后單擊【確定】按鈕，即可對數(shù)據(jù)進行自定義排序。圖1-36

【排序選項】對話框2.2數(shù)據(jù)的查找與篩選企業(yè)的管理人員經(jīng)常需要在數(shù)據(jù)庫或數(shù)據(jù)清單眾多的數(shù)據(jù)中找出需要的數(shù)據(jù)，Excel提供了功能強大的數(shù)據(jù)查找與篩選工具。數(shù)據(jù)查找是指從原始數(shù)據(jù)中提取滿足條件的數(shù)據(jù)記錄，源數(shù)據(jù)不會改變，也不會被隱藏；數(shù)據(jù)篩選是指把數(shù)據(jù)庫或數(shù)據(jù)清單中所有不滿足條件的數(shù)據(jù)記錄隱藏起來，只顯示滿足條件的數(shù)據(jù)記錄。常用的數(shù)據(jù)查找與篩選方法有：記錄單查找、自動篩選和高級篩選。下面結(jié)合實例說明各種查找方法的具體應(yīng)用?！纠?—11】圖2-37為某公司的部分商品銷售記錄清單。圖2-37

某公司的商品銷售明細清單根據(jù)圖2-37中的有關(guān)資料，可以分別采用記錄單查找、自動篩選或高級篩選的方式查找或選擇所需要的信息，如下所述：2.2.1記錄單查找記錄單是查找和編輯數(shù)據(jù)的最簡單的方法，利用記錄單，不僅可以查找數(shù)據(jù)記錄，還可以修改和刪除記錄、添加新的數(shù)據(jù)記錄等。一．查找數(shù)據(jù)記錄利用記錄單查找數(shù)據(jù)記錄的步驟如下：（1）用鼠標(biāo)單擊數(shù)據(jù)清單或數(shù)據(jù)庫中的任一非空單元格。（2）單擊【數(shù)據(jù)】菜單，選擇【記錄單】項，則系統(tǒng)彈出如圖1-38所示的記錄單。圖1-38

記錄單（3）單擊記錄單中的【條件】按鈕，則彈出記錄單條件對話框，如圖1-39所示。圖1-39

記錄單條件對話框（4）輸入條件，比如要查找“張三”的銷售記錄，則在【銷售人員】欄中輸入“張三”，然后單擊【上一條】按鈕或【下一條】按鈕，系統(tǒng)就逐次顯示滿足條件的記錄行。還可以使用多個條件聯(lián)合查找記錄，此處不再敘述。二．修改或刪除記錄在圖2-38所示的記錄單中，即可對某一記錄的各字段進行修改。若要刪除顯示的記錄，只需單擊記錄單上的【刪除】按鈕即可。三．添加新的記錄在圖2-38所示的記錄單中，單擊記錄單上的【新建】按鈕，則出現(xiàn)各字段均為空白的新建記錄單，在記錄單中輸入各字段的值，輸入完畢后，單擊【新建】按鈕，即完成添加新記錄。2.2.2自動篩選與自定義篩選一．自動篩選記錄單檢索數(shù)據(jù)每次只能顯示一個數(shù)據(jù)行，當(dāng)查詢的數(shù)據(jù)較多，或要把查詢的結(jié)果匯總成表時，就需要使用篩選工具了。自動篩選提供了快速檢索數(shù)據(jù)清單或數(shù)據(jù)庫的方法，通過簡單的操作，就能篩選出需要的數(shù)據(jù)。利用自動篩選查找數(shù)據(jù)的步驟如下：（1）用鼠標(biāo)單擊數(shù)據(jù)清單或數(shù)據(jù)庫中的任一非空單元格。（2）單擊【數(shù)據(jù)】菜單，選擇【篩選】項，在【篩選】子菜單中選擇【自動篩選】，則系統(tǒng)自動在數(shù)據(jù)清單的每列數(shù)據(jù)的標(biāo)題旁邊添加一個下拉列標(biāo)標(biāo)志，如圖1-40所示。圖1-40

自動篩選的下拉列表標(biāo)志（3）單擊需要篩選的下拉列表，系統(tǒng)顯示出可用的篩選條件，從中選擇需要的條件，即可顯示出滿足條件的所有數(shù)據(jù)。例如，要查找所有彩電的銷售記錄，單擊“商品”右邊的下拉列表，從中選擇“彩電”項，則所有的彩電銷售記錄就顯示出來，而其他的數(shù)據(jù)則被隱藏，如圖1-41所示。圖1-41

彩電銷售清單的篩選結(jié)果如果有關(guān)彩電的銷售記錄很多，超過了10個，當(dāng)需要只顯示10個記錄時，可單擊“單價”、“數(shù)量”、“金額”等右邊的下拉列表中的“前10個”項，系統(tǒng)彈出【自動篩選前10個】對話框，如圖2-42所示。這里，在【顯示】下拉列表中“最大”表示最大（最好）的前10個記錄，“最小”表示最?。ㄗ畈睿┑那?0個記錄。中間的編輯框中的數(shù)值表示顯示的記錄行數(shù)，系統(tǒng)默認(rèn)值為10，但可以修改，根據(jù)需要輸入數(shù)值即可。圖1-42

【自動篩選前10個】對話框若要恢復(fù)所有的記錄，則單擊“商品”右邊的下拉列表中的“全部”項。若要取消【自動篩選】狀態(tài)，則單擊【數(shù)據(jù)】菜單，選擇【篩選】項，在【篩選】子菜單中再次選擇【自動篩選】。二．自定義篩選方式當(dāng)在圖1-40所示的下拉列表中選擇“自定義”項時則會彈出【自定義自動篩選方式】對話框，如圖1-43所示，用戶可根據(jù)具體條件對各欄進行設(shè)置。如要查找銷售金額大于或等于“150000”且小于或等于“200000”的所有記錄，則單擊左上角的下拉箭頭，選擇“大于或等于”，右上角的條件值輸入“150000”，單擊左下角的下拉箭頭，選擇“小于或等于”，右下角的條件值輸入“200000”，單擊【確定】按鈕，并選擇“與”條件，則滿足這些條件的所有記錄就顯示出來了，如圖1-44所示。圖1-43

【自定義自動篩選方式】對話框圖1-44

【自定義自動篩選方式】篩選的結(jié)果2.2.3高級篩選高級篩選可以使用較多的條件來對數(shù)據(jù)清單進行篩選，這些條件既可以是與條件，也可以是或條件，或與條件，與或條件的組合使用，還可以使用計算條件。一．一般情況下的高級篩選利用高級篩選對數(shù)據(jù)清單進行篩選的步驟如下：（1）首先應(yīng)建立一個條件區(qū)域。在條件區(qū)域中，同一行中的條件是與條件，也就是這些條件必須同時滿足；不同行中的條件是或條件，也就是這些條件只要滿足其一即可。如需要查找張三銷售彩電的所有記錄，則建立條件區(qū)域如圖1-45所示。圖1-45

建立條件區(qū)域（2）單擊數(shù)據(jù)清單或數(shù)據(jù)庫中的任一非空單元格，然后單擊【數(shù)據(jù)】菜單，選擇【篩選】子菜單中的【高級篩選】項，則系統(tǒng)彈出如圖1-46所示的【高級篩選】對話框。圖1-46

【高級篩選】對話框（3）一般情況下，系統(tǒng)將自動給出了數(shù)據(jù)區(qū)域，用戶只需在【條件區(qū)域】欄中輸入條件區(qū)域（本例中為B19:C20，也可以用鼠標(biāo)拾取單元格區(qū)域，此時在條件區(qū)域中將顯示“銷售明細清單!$B$19:$C$20”。（4）高級篩選結(jié)果可以顯示在數(shù)據(jù)清單的原有區(qū)域中，也可以顯示在工作表的其他空白單元格區(qū)域，系統(tǒng)默認(rèn)的方式是在數(shù)據(jù)清單的原有區(qū)域中顯示結(jié)果。若需要在工作表的其他空白單元格區(qū)域顯示結(jié)果，則在【方式】項中選中“將篩選結(jié)果復(fù)制到其他位置”，并在【復(fù)制到】欄中輸入需要顯示篩選結(jié)果的單元格（開頭的一個單元格即可）。圖1-47為在原有區(qū)域顯示的高級篩選結(jié)果。圖1-47

在原有區(qū)域顯示的高級篩選結(jié)果當(dāng)需要顯示原始的全部數(shù)據(jù)時，可以單擊【數(shù)據(jù)】菜單，選擇【篩選】子菜單中的項目，在【篩選】子菜單中選擇【全部顯示】即可。同樣的方法可以進行建立或條件、與條件與或條件的組合使用情況下的高級篩選。二．計算條件情況下的高級篩選在有些情況下，篩選的條件不是一個常數(shù)，而是一個隨數(shù)據(jù)清單中數(shù)據(jù)變化的計算結(jié)果，此時無法直接利用高級篩選進行數(shù)據(jù)篩選。不過，我們可以通過計算條件的方法解決。以例2-20為例（見圖1-37），這里要找出銷售額大于平均銷售額的所有記錄。步驟如下：（1）在數(shù)據(jù)清單以外的任一空單元格內(nèi)輸入平均值計算公式，比如在單元格H20中輸入公式“=AVERAGE(E3:E16)”，這里要特別注意的是存放平均值計算公式的單元格的列標(biāo)不能與數(shù)據(jù)清單的任一列標(biāo)相同，如圖1-48所示。圖1-48

計算條件情況下的高級篩選（2）設(shè)置條件區(qū)域，條件區(qū)域的列表可以是除數(shù)據(jù)清單中數(shù)據(jù)標(biāo)題以外的任何文本，而篩選條件可在單元格B20中輸入“=E3>$H$20”，這里要特別注意：必須以絕對引用的方式引用銷售額平均值，以相對引用的方式引用數(shù)據(jù)清單中的數(shù)據(jù)。（3）按照前面介紹的步驟進行高級篩選，其中高級篩選的數(shù)據(jù)區(qū)域為$A$2:$G$16；高級篩選的條件區(qū)域為$B$19:$C$20，則篩選結(jié)果如圖2-48所示。2.3數(shù)據(jù)的分類與匯總在對數(shù)據(jù)進行分析時，常常需要將相同類型的數(shù)據(jù)統(tǒng)計出來，這就是數(shù)據(jù)的分類與匯總。在對數(shù)據(jù)進行匯總之前，應(yīng)特別注意的是：首先必須對要匯總的關(guān)鍵字進行排序。2.3.1進行分類匯總例如，在例2-11中，要按地區(qū)進行自動分類匯總，其步驟如下：（1）首先對“地區(qū)”進行排序，排序方法見前面所述。（2）單擊數(shù)據(jù)清單或數(shù)據(jù)庫中的任一非空單元格，然后單擊【數(shù)據(jù)】菜單，選擇【分類匯總】項，系統(tǒng)彈出如圖1-49所示的【分類匯總】對話框。圖1-49

【分類匯總】對話框（3）在【分類匯總】對話框中，【分類字段】選項下選擇“地區(qū)”，【匯總方式】選項下選擇“求和”，【選定匯總項】選項下選定“數(shù)量”和“金額”，單擊【確定】按鈕，則分類匯總的結(jié)果如圖2-50所示。圖1-50

按地區(qū)分類匯總結(jié)果在圖1-50中，左上角有3個按鈕，按鈕1表示1級匯總，顯示全部的銷售數(shù)量和銷售金額匯總；按鈕2表示2級匯總，顯示各地區(qū)的全部銷售數(shù)量和銷售金額匯總；按鈕3表示3級匯總，顯示各地區(qū)的銷售數(shù)量和銷售金額的匯總明細與匯總額（即圖1-50所示的匯總結(jié)果）。圖1-50中，左邊的滑動按鈕為隱藏明細按鈕，單擊此按鈕，則將隱藏本級的明細數(shù)據(jù)，同時變?yōu)轱@示明細按鈕，再單擊按鈕，則將顯示本級的全部明細數(shù)據(jù)，同時變?yōu)?。在上述自動分類匯總的結(jié)果上，還可以再進行分類匯總，例如再進行另一種分類匯總，兩次分類匯總的關(guān)鍵字可以相同，也可以不同，其分類匯總方法與前面的是一樣的，此處不再介紹。2.3.2分類匯總的撤消如果不再需要分類匯總結(jié)果，可在圖2-49所示的【分類匯總】對話框中單擊【全部刪除】，即可撤消分類匯總。2.4

數(shù)據(jù)透視表數(shù)據(jù)透視表是用于快速匯總大量數(shù)據(jù)的交互式表格，用戶可以旋轉(zhuǎn)其行或列以查看對源數(shù)據(jù)的不同匯總，也可以通過顯示不同的頁來篩選數(shù)據(jù)，還可以顯示所關(guān)心區(qū)域的數(shù)據(jù)明細。通過對源數(shù)據(jù)表的行、列進行重新排列，使得數(shù)據(jù)表達的信息更清楚明了。2.4.1建立數(shù)據(jù)透視表以例2-11的數(shù)據(jù)為例，建立數(shù)據(jù)透視表的步驟如下：（1）首先，要保證數(shù)據(jù)源是一個數(shù)據(jù)清單或數(shù)據(jù)庫，即數(shù)據(jù)表的每列必須有列標(biāo)。（2）單擊數(shù)據(jù)清單或數(shù)據(jù)庫中的任一非空單元格，然后單擊【數(shù)據(jù)】菜單，選擇【數(shù)據(jù)透視表和圖表報告】項，則系統(tǒng)彈出【數(shù)據(jù)透視表和數(shù)據(jù)透視圖向?qū)А?步驟之1】對話框，如圖1-51所示，根據(jù)待分析數(shù)據(jù)來源與需要創(chuàng)建何種報表類型，進行相應(yīng)的選擇，然后單擊【下一步】按鈕，系統(tǒng)彈出【數(shù)據(jù)透視表和數(shù)據(jù)透視圖向?qū)А?步驟之2】對話框，如圖1-52所示；圖1-51

【數(shù)據(jù)透視表和數(shù)據(jù)透視圖向?qū)А?步驟之1】對話框圖1-52

【數(shù)據(jù)透視表和數(shù)據(jù)透視圖向?qū)А?步驟之2】對話框（3）默認(rèn)情況下，系統(tǒng)自動將選取整個數(shù)據(jù)清單作為數(shù)據(jù)源，如果數(shù)據(jù)源區(qū)域需要修改，則可直接輸入“選定區(qū)域”，或單擊【瀏覽】按鈕，從其他的文件中提取數(shù)據(jù)源。確定數(shù)據(jù)源后，單擊【下一步】按鈕，系統(tǒng)彈出【數(shù)據(jù)透視表和數(shù)據(jù)透視圖向?qū)А?步驟之3】對話框，如圖1-53所示。圖1-53

【數(shù)據(jù)透視表和數(shù)據(jù)透視圖向?qū)А?步驟之3】對話框（4）在【數(shù)據(jù)透視表和數(shù)據(jù)透視圖向?qū)А?步驟之3】對話框中，單擊【版式】按鈕，出現(xiàn)【數(shù)據(jù)透視表和數(shù)據(jù)透視圖向?qū)А媸健繉υ捒?，如圖1-54所示。（5）【數(shù)據(jù)透視表和數(shù)據(jù)透視圖向?qū)А媸健繉υ捒蛑?，再根?jù)需要，將右邊的字段按鈕拖到左邊的圖上，這里，將“銷售人員”拖到“行(R)”圖上，將“商品”拖到“列(C)”圖上，將“數(shù)量（臺）”和“金額（元）”拖到“數(shù)據(jù)(D)”圖上，如圖1-55所示。圖1-54

【數(shù)據(jù)透視表和數(shù)據(jù)透視圖向?qū)А媸健繉υ捒驁D1-55

設(shè)置數(shù)據(jù)透視表的版式（6）設(shè)置好版式后，單擊【確定】按鈕，則系統(tǒng)就返回到圖2—44所示的【數(shù)據(jù)透視表和數(shù)據(jù)透視圖向?qū)А?步驟之3】對話框，然后單擊【完成】按鈕，數(shù)據(jù)透視表就完成了，如圖1-56所示。這樣，通過圖2-56的數(shù)據(jù)透視表，即可看出每個銷售人員所銷售商品的種類、數(shù)量、銷售額與其合計數(shù)，從而以此為基礎(chǔ)可很方便地對每個銷售人員的銷售業(yè)績進行評價。圖1-56

各個銷售人員銷售商品的數(shù)據(jù)透視表2.4.2

數(shù)據(jù)的透視分析在圖1-56所建立的數(shù)據(jù)透視表上，可以很方便地進行多角度的統(tǒng)計與分析。比如要了解李四所銷售商品的情況，可在“銷售人員”下拉列標(biāo)中只選中“李四”，然后單擊“確定”按鈕，則李四的銷售情況如圖1-57所示。圖1-57

李四的銷售情況匯總還可以建立透視圖，方法是：單擊數(shù)據(jù)透視表中的任一單元格，單擊鼠標(biāo)右鍵，在快捷菜單中選擇【數(shù)據(jù)透視圖】項，則系統(tǒng)自動顯示出數(shù)據(jù)透視圖，從而得到每個銷售人員的更為直觀的銷售情況。第三節(jié)EXCEL圖表處理Excel具有完整的圖表功能，它不僅可以生成諸如條形圖、折線圖、餅圖等標(biāo)準(zhǔn)圖表，還可以生成較為復(fù)雜的三維立體圖表。對各種財務(wù)數(shù)據(jù)進行圖表處理，可以更直觀地進行財務(wù)分析，找出工作表格不容易發(fā)現(xiàn)的問題，使得財務(wù)管理工作更為有效。3.1圖表類型Excel提供了約14種標(biāo)準(zhǔn)圖表類型，如面積圖、柱形圖、條形圖、折線圖、餅圖、圓環(huán)圖、氣泡圖、雷達圖、股價圖、曲面圖、散點圖、錐形圖、圓柱圖、棱錐圖等，每種圖表類型又都有幾種不同的子類型。此外，Excel還提供了約20種自定義圖表類型，用戶可根據(jù)不同的需要選用適當(dāng)?shù)膱D表類型。關(guān)于各種圖表類型的具體情況，可單擊工具欄上的【圖表向?qū)А堪粹o，或單擊【插入】菜單，選擇【圖表】項，即可查看各種圖表。3.2

圖表的建立建立圖表的過程非常簡單，只要按照【圖表向?qū)А康挠嘘P(guān)說明，一步一步地進行操作，即可完成圖表的制作。下面結(jié)合實例進行說明?！纠?-8】某企業(yè)2002年12個月的銷售量與銷售費用的有關(guān)數(shù)據(jù)如圖1-19所示，繪制各月銷售額與銷售費用之間關(guān)系的圖表的步驟如下：圖1-19

銷售額與銷售費用有關(guān)數(shù)據(jù)（1）選取單元格區(qū)域A2：M3，單擊工具欄上的【圖表向?qū)А堪粹o，或單擊【插入】菜單，選擇【圖表】項，出現(xiàn)【圖表向?qū)В?步驟之1－圖表類型】對話框，如圖1-20所示，在【圖表類型】中選【折線圖】，在【子圖表類型】中選【數(shù)據(jù)點折線圖】，單擊【下一步】按鈕，出現(xiàn)【圖表向?qū)В?步驟之2－圖表數(shù)據(jù)源】對話框，如圖1-21所示。圖1-20

【圖表向?qū)В?步驟之1－圖表類型】對話框圖1-21

【圖表向?qū)В?步驟之2－圖表源數(shù)據(jù)】對話框（2）在【圖表向?qū)В?步驟之2－圖表源數(shù)據(jù)】對話框中，單擊【系列】，在【分類（X）軸標(biāo)志】欄中填入“=Sheet1!$B$1:$M$1”，單擊【下一步】按鈕，出現(xiàn)【圖表向?qū)В?步驟之3－圖表選項】對話框，如圖1-22所示。圖1-22

【圖表向?qū)?4步驟之3-圖表選項】對話框（3）在【圖表向?qū)В?步驟之3－圖表選項】對話框中，對【標(biāo)題】的各項進行輸入，即在【圖表標(biāo)題】欄中填入“銷售額和銷售費用的變化圖”，在【數(shù)值X軸】欄中填入“月份”，在【數(shù)值Y軸】欄中填入“金額（千元）”，單擊【下一步】按鈕，出現(xiàn)【圖表向?qū)В?步驟之4－圖表位置】如圖2-23所示。圖1-23

【圖表向?qū)?4步驟之4-圖表位置】對話框（4）在【圖表向?qū)В?步驟之4－圖表位置】對話框中，若要建立嵌入式圖表，即圖表嵌入在本工作表中，則選擇“作為其中的對象插入”；若要建立工作表圖表，則選擇“作為新工作表插入”。這里選擇“作為其中的對象插入”；然后單擊【完成】按鈕，即得到需要的圖表，如圖1-24所示。圖1-24

銷售額與銷售費用變化圖3.3圖表的編輯、修改與格式化通過圖表向?qū)Ы⒌膱D表可能不盡人意，如標(biāo)題太大或太小、坐標(biāo)系列太多、圖表尺寸太小、漏掉了數(shù)據(jù)系列、需要添加數(shù)據(jù)標(biāo)志等等，這時就需要對圖表進行修改和格式化。一．設(shè)置坐標(biāo)、標(biāo)題、圖例等的格式設(shè)置坐標(biāo)、標(biāo)題、圖例等的格式的方法非常簡單，可將鼠標(biāo)移到坐標(biāo)、標(biāo)題、圖例等上，單擊右鍵，在快捷菜單上選擇相應(yīng)的項目即可。例如要改變X坐標(biāo)大小，單擊右鍵，出現(xiàn)快捷菜單，選擇【坐標(biāo)軸格式】項，就會彈出【坐標(biāo)軸格式】對話框，如圖1-25所示，選擇需要修改的項目，進行設(shè)置即可。圖1-25

【坐標(biāo)軸格式】對話框二．改變圖表大小單擊圖表區(qū)域，將它激活，圖表邊框出現(xiàn)8個操作柄，用鼠標(biāo)指向某個操作柄，當(dāng)鼠標(biāo)指針呈現(xiàn)雙箭頭時，按住左鍵不放，拖動操作柄到需要的位置上，然后放開鼠標(biāo)左鍵，即可完成。三．移動或復(fù)制圖表移動：單擊圖表區(qū)域，將它激活，圖表邊框出現(xiàn)8個操作柄，在圖表區(qū)域按住鼠標(biāo)左鍵不放，拖動鼠標(biāo)將圖表移到需要的地方。復(fù)制：單擊圖表區(qū)域，將它激活，圖表邊框出現(xiàn)8個操作柄，在圖表區(qū)域按住鼠標(biāo)左鍵不放，拖動鼠標(biāo)將圖表移到需要的地方，按Ctrl鍵，然后放開鼠標(biāo)。若需要將圖表復(fù)制到其他工作表或其他文件中，可選中圖表，按“Ctrl+C”鍵，再在需要安置圖表的工作表或其他文件的適當(dāng)位置，按“Ctrl+V”鍵四．添加數(shù)據(jù)標(biāo)志在很多情況下，在圖表上添加數(shù)據(jù)標(biāo)志，可以更直觀地表示因素的變化情況。添加數(shù)據(jù)標(biāo)志的步驟如下：單擊需要顯示數(shù)據(jù)標(biāo)志的數(shù)據(jù)點，然后按鼠標(biāo)右鍵，出現(xiàn)快捷菜單，如圖2-26所示，選擇【數(shù)據(jù)系列格式】對話框，單擊【數(shù)據(jù)標(biāo)志】，選擇“顯示值”。圖2-26

快捷菜單通過【數(shù)據(jù)系列格式】對話框，還可以進行其他方面的修改或格式化，有關(guān)內(nèi)容可單擊【數(shù)據(jù)系列格式】對話框的有關(guān)項。圖1-27

【數(shù)據(jù)系列格式】對話框五．改變圖表顏色、圖案、邊框改變圖表顏色、圖案、邊框，可通過【圖表區(qū)格式】對話框來完成，方法是：單擊圖表區(qū)域，單擊鼠標(biāo)右鍵，在快捷菜單中選【圖表區(qū)格式】項，彈出【圖表區(qū)格式】對話框，即可進行相應(yīng)的修改。3.4

地區(qū)銷售分布圖表的建立企業(yè)的產(chǎn)品銷往全國各地與世界各地，各地的銷售量是不同的，我們可以利用Excel的地圖分析工具建立銷售數(shù)據(jù)地圖，從而可以將企業(yè)產(chǎn)品在各地的銷售情況更加直觀地表示出來?！纠?-9】某企業(yè)在某些省份的銷售數(shù)據(jù)如圖1-28所示，則建立數(shù)據(jù)地圖的步驟如下：圖1-28

銷售數(shù)據(jù)（1）選中數(shù)據(jù)區(qū)域A2:B15。（2）單擊【插入】菜單，執(zhí)行【對象】命令，彈出【對象】對話框，如圖2-29所示，選中“Microsoft地圖”，則Excel就會根據(jù)所選的工作表數(shù)據(jù)建立如圖2-30所示的數(shù)據(jù)地圖；在數(shù)據(jù)地圖中，數(shù)據(jù)越多的區(qū)域（省份），顏色就越深。（3）在圖2-30中所示的地圖中沒有標(biāo)明省份名稱，但可以通過下述方法加入省份名稱：雙擊地圖，出現(xiàn)地圖的菜單，如圖2-31所示，單擊地圖菜單上的【工具】，選擇【標(biāo)志】項，出現(xiàn)【地圖標(biāo)志】對話框，如圖2-32所示；（4）在【地圖標(biāo)志】對話框中，【需要設(shè)置標(biāo)志的地圖項】中選擇“中國”，【創(chuàng)建標(biāo)志】中選擇“地圖項名稱”，然后單擊【確定】按鈕。圖1-29

【對象】對話框圖1-30

某企業(yè)的銷售地區(qū)分布圖1-31

地圖菜單項圖1-32

【地圖標(biāo)志】對話框（5）在地圖上移動鼠標(biāo)，在鼠標(biāo)移動過程中，Excel會顯示該區(qū)域所對應(yīng)的省份名稱，單擊左鍵，該省份名稱就會標(biāo)注在對應(yīng)的省份區(qū)域上（圖表太小，此處省略）。（6）如果需要，還可以對各省份添加數(shù)量標(biāo)志，方法是：雙擊地圖，出現(xiàn)【Microsoft地圖控件】對話框，如圖1-33所示，根據(jù)需要選擇數(shù)據(jù)類型格式，用鼠標(biāo)把需要的格式拖放在對話框右邊區(qū)域中的格式上，然后把要設(shè)置這種格式的數(shù)據(jù)列（對話框中的【第B列】）拖放在圖中的“列”字框上即可?！綧icrosoft地圖控件】對話框提供了6種不同的數(shù)據(jù)類型格式。圖1-20就是第1種數(shù)據(jù)類型格式。圖1-33

【Microsoft地圖控件】對話框需要注意的是，圖1-28的工作表數(shù)據(jù)中的各省份名稱必須與Excel所規(guī)定的相同，否則可能會出現(xiàn)意想不到的錯誤。Excel所規(guī)定的各省份名稱如下：黑龍江省、吉林省、遼寧省、內(nèi)蒙古自治區(qū)、新疆維吾爾自治區(qū)、北京市、天津市、河北省、山西省、陜西省、青海省、寧夏回族自治區(qū)、西藏自治區(qū)、山東省、河南省、江蘇省、浙江省、上海市、安徽省、湖北省、湖南省、福建省、廣東省、廣西壯族自治區(qū)、江西省、四川省、云南省、貴州省、海南省、香港、臺灣省、澳門。3.5

動態(tài)圖表的建立在企業(yè)的經(jīng)營活動中，往往需要為每個部門建立大量相似的圖表，如果在一張工作表上建立太多的圖表，既費時也使得圖表顯得凌亂不堪。我們可以建立動態(tài)圖表來解決這個問題，當(dāng)需要了解某個部門的銷售情況時，只需將鼠標(biāo)移到工作表中該部門的單元格上，即可立即顯示出該部門的銷售圖表?！纠?-10】某企業(yè)的8個銷售部門一年內(nèi)各月的銷售量數(shù)據(jù)如圖1-34所示，建立各部門的動態(tài)圖表的步驟如下：圖1-34

動態(tài)銷售圖表（1）設(shè)計動態(tài)圖表數(shù)據(jù)區(qū)域，如圖2-34所示。（2）在單元格A13中輸入公式“=INDIRECT(ADDRESS(CELL("row"),COLUMN(A3)))”，并把該公式向右填充復(fù)制到M13中，這里COLUMN的意思是返回參數(shù)所在的列標(biāo)，CELL("row")的意思是返回當(dāng)前光標(biāo)所在的行號，ADDRESS（行號，列標(biāo)）的意思是返回由行號和列標(biāo)確定的單元格，INDIRECT的意思是返回參數(shù)所確定的單元格內(nèi)容；（3）選中區(qū)域A12:M13，插入“折線圖”，并進行相應(yīng)的格式設(shè)置，則動態(tài)圖表就建立起來了。若鼠標(biāo)單擊A3單元格，再按F9鍵（即對工作表數(shù)據(jù)重新計算），就會顯示部門A的銷售圖；若鼠標(biāo)單擊A5單元格，再按F9鍵（即對工作表數(shù)據(jù)重新計算），就會顯示部門C的銷售圖。這樣，就可以很方便地對各個銷售部門的銷售量進行直觀的觀察和分析。第四節(jié)EXCEL數(shù)據(jù)分析工具的應(yīng)用Excel提供了非常實用的數(shù)據(jù)分析工具，利用這些分析工具，可解決財務(wù)管理中的許多問題，例如財務(wù)分析工具、統(tǒng)計分析工具、工程分析工具、規(guī)劃求解工具、方案管理器等等。下面介紹財務(wù)管理與分析中常用的一些數(shù)據(jù)分析工具。4.1模擬運算表模擬運算表就是將工作表中的一個單元格區(qū)域的數(shù)據(jù)進行模擬計算，測試使用一個或兩個變量對運算結(jié)果的影響。在Excel中，可以構(gòu)造兩種模擬運算表：單變量模擬運算表和多變量模擬運算表。4.1.1單變量模擬運算表單變量模擬運算表就是基于一個輸入變量，用它來測試對公式計算結(jié)果的影響。【例2-13】企業(yè)向銀行貸款10000元，期限5年，則可以使用【模擬運算表】工具來測試不同的利率對月還款額的影響，步驟如下：（1）設(shè)計模擬運算表結(jié)構(gòu)，如圖2-62所示。圖1-62

單變量模擬運算表（2）在單元格B4中輸入公式“=PMT(A4/12,5*12,B1)”。（3）選取包括公式和需要進行模擬運算的單元格區(qū)域A4:B13。（4）單擊【數(shù)據(jù)】菜單，選擇【模擬運算表】項，彈出【模擬運算表】對話框，如圖1-63。圖1-63

【模擬運算表】對話框（5）由于本例中引用的是列數(shù)據(jù)，故在【輸入引用列的單元格】中輸入“$A$4”。單擊【確定】按鈕，即得到單變量的模擬運算表，如圖2-62所示。4.1.2雙變量模擬運算表雙變量模擬運算表就是考慮兩個變量的變化對公式計算結(jié)果的影響，在財務(wù)管理中應(yīng)用最多的是長期借款雙變量分析模型，有關(guān)詳細內(nèi)容可參閱第3章的有關(guān)章節(jié)。4.2單變量求解單變量求解就是求解只有一個變量的方程的根，方程可以是線性方程，也可以是非線性方程。單變量求解工具可以解決許多財務(wù)管理中涉與到一個變量的求解問題?！纠?-14】某企業(yè)擬向銀行以7%的年利率借入期限為5年的長期借款，企業(yè)每年的償還能力為100萬元，那么企業(yè)最多總共可貸款多少？設(shè)計如圖2-64所示的計算表格，在單元格B2中輸入公式“=PMT(B1,B3,B4)”，單擊【工具】菜單，選擇【單變量求解】項，則彈出【單變量求解】對話框，如圖1-65所示，在【目標(biāo)單元格】中輸入“B2”，在【目標(biāo)值】中輸入“100”，在【可變單元格】中輸入“$B$4”，然后單擊【確定】按鈕，則系統(tǒng)立即計算出結(jié)果，如圖1-64所示，即企業(yè)最多總共可貸款410.02萬元。

圖1-64

貸款總額計算

圖1-65

【單變量求解】對話框4.3

規(guī)劃求解規(guī)劃求解是Excel的一個非常有用的工具，不僅可以解決運籌學(xué)、線性規(guī)劃等問題，還可以用來求解線性方程組與非線性方程組。【規(guī)劃求解】加載宏是Excel的一個可選安裝模塊，在安裝MicrosoftExcel時，如果采用【典型安裝】，則【規(guī)劃求解】工具沒有被安裝，只有在選擇【完全/定制安裝】時才可選擇安裝這個模塊。在安裝完成進入Excel后，單擊【工具】菜單，選擇【加載宏】項，在【加載宏】對話框中選定【規(guī)劃求解】復(fù)選框，然后單擊【確定】按鈕，則系統(tǒng)就安裝和加載【規(guī)劃求解】工具，可以使用它了。4.3.1

求解優(yōu)化問題財務(wù)管理中涉與到很多的優(yōu)化問題，如最大利潤、最小成本、最優(yōu)投資組合、目標(biāo)規(guī)劃、線性回歸與非線性回歸等等。下面僅舉一個簡單的例子來說明利用規(guī)劃求解工具解決最大利潤的問題，有關(guān)規(guī)劃求解工具的更多實際應(yīng)用可參閱后面的有關(guān)章節(jié)?！纠?-15】某企業(yè)在某月份生產(chǎn)甲、乙兩種產(chǎn)品，其有關(guān)資料如圖2-66所示，則企業(yè)應(yīng)如何安排兩種產(chǎn)品的產(chǎn)銷組合，使企業(yè)獲得最大銷售利潤？利用規(guī)劃求解工具求解這個問題的步驟如下：圖1-66

產(chǎn)品有關(guān)資料與優(yōu)化結(jié)果（1）首先建立優(yōu)化模型，（設(shè)x和y分別表示甲產(chǎn)品和乙產(chǎn)品的生產(chǎn)量）：目標(biāo)函數(shù)：max{銷售利潤}=(140－60)×x+(180－100)×y約束條件：6x+9y≤3607x+4y≤24018x+15y≤850y≤30x≥0,

y≥0，且為整數(shù)（2）單元格B11和C11為可變單元格，分別存放甲、乙產(chǎn)品的生產(chǎn)量。（3）單元格B12為目標(biāo)單元格（銷售利潤），計算公式為“=SUMPRODUCT(B4:C4-B5:C5,B11:C11)”；（4）在單元格B14中輸入產(chǎn)品消耗工時合計計算公式“=SUMPRODUCT(B6:C6,B11:C11)”。在單元格B15中輸入產(chǎn)品消耗材料合計計算公式“=SUMPRODUCT(B7:C7,B11:C11)”，在單元格B16中輸入產(chǎn)品消耗能源合計計算公式“=SUMPRODUCT(B8:C8,B11:C11)”。（5）單擊【工具】菜單，選擇【規(guī)劃求解】項，則系統(tǒng)彈出【規(guī)劃求解參數(shù)】對話框，如圖1-67。圖1-67

【規(guī)劃求解參數(shù)】對話框（6）在【規(guī)劃求解參數(shù)】對話框中，【設(shè)置目標(biāo)單元格】中輸入“$B$12”；【等于】選“最大值”；【可變單元格】中輸入“$B$11:$C$11”；在【約束】中添加以下的約束條件：“$B$11:$C$11=整數(shù)”、“$B$11:$C$11>=0”、“$B$14<=$E$3”、“$B$15<=$E$4”、“$B$16<=$E$5”、“$B$11<=$C$9”；這里，添加約束條件的方法是：單擊【添加】按鈕，系統(tǒng)會彈出【添加約束】對話框，如圖1-68所示，輸入完畢一個約束