Excel在財(cái)務(wù)管理與分析中的應(yīng)用基礎(chǔ)知識(shí)

Excel在財(cái)務(wù)管理與分析中的應(yīng)用基礎(chǔ)知識(shí)2.1公式及函數(shù)的高級(jí)應(yīng)用(1)公式和函數(shù)是Excel最基本、最重要的應(yīng)用工具，是Excel的核心，因此，應(yīng)對(duì)公式和函數(shù)熟練掌握，才能在實(shí)際應(yīng)用中得心應(yīng)手。2.1.1

數(shù)組公式及其應(yīng)用數(shù)組公式就是可以同時(shí)進(jìn)行多重計(jì)算并返回一種或多種結(jié)果的公式。在數(shù)組公式中使用兩組或多組數(shù)據(jù)稱為數(shù)組參數(shù)，數(shù)組參數(shù)可以是一個(gè)數(shù)據(jù)區(qū)域，也可以是數(shù)組常量。數(shù)組公式中的每個(gè)數(shù)組參數(shù)必須有相同數(shù)量的行和列。2.1.1.1

數(shù)組公式的輸入、編輯及刪除1．?dāng)?shù)組公式的輸入數(shù)組公式的輸入步驟如下：（1）選定單元格或單元格區(qū)域。如果數(shù)組公式將返回一個(gè)結(jié)果，單擊需要輸入數(shù)組公式的單元格；如果數(shù)組公式將返回多個(gè)結(jié)果，則要選定需要輸入數(shù)組公式的單元格區(qū)域。（2）輸入數(shù)組公式。（3）同時(shí)按“Crtl+Shift+Enter”組合鍵，則Excel自動(dòng)在公式的兩邊加上大括號(hào){}。特別要注意的是，第（3）步相當(dāng)重要，只有輸入公式后同時(shí)按“Crtl+Shift+Enter”組合鍵，系統(tǒng)才會(huì)把公式視為一個(gè)數(shù)組公式。否則，如果只按Enter鍵，則輸入的只是一個(gè)簡(jiǎn)單的公式，也只在選中的單元格區(qū)域的第1個(gè)單元格顯示出一個(gè)計(jì)算結(jié)果。在數(shù)組公式中，通常都使用單元格區(qū)域引用，但也可以直接鍵入數(shù)值數(shù)組，這樣鍵入的數(shù)值數(shù)組被稱為數(shù)組常量。當(dāng)不想在工作表中按單元格逐個(gè)輸入數(shù)值時(shí)，可以使用這種方法。如果要生成數(shù)組常量，必須按如下操作：（1）直接在公式中輸入數(shù)值，并用大括號(hào)“{}”括起來(lái)。（2）不同列的數(shù)值用逗號(hào)“,”分開(kāi)。（3）不同行的數(shù)值用分號(hào)“;”分開(kāi)。?

輸入數(shù)組常量的方法：例如，要在單元格A1:D1中分別輸入10，20，30和40這4個(gè)數(shù)值，則可采用下述的步驟：（1）選取單元格區(qū)域A1:D1，如圖2-1所示。圖2-1

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

在編輯欄中輸入數(shù)組公式（3）同時(shí)按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，則可以采用下述的方法：圖2-3

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

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

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

同時(shí)按Ctrl+Shift+Enter組合鍵，得到數(shù)組常量?

輸入公式數(shù)組的方法例如，在單元格A3:D3中均有相同的計(jì)算公式，它們分別為單元格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所示。圖2-7

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

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

同時(shí)按Ctrl+Shift+Enter組合鍵，得到數(shù)組公式2.1公式及函數(shù)的高級(jí)應(yīng)用(2)2．編輯數(shù)組公式數(shù)組公式的特征之一就是不能單獨(dú)編輯、清除或移動(dòng)數(shù)組公式所涉及的單元格區(qū)域中的某一個(gè)單元格。若在數(shù)組公式輸入完畢后發(fā)現(xiàn)錯(cuò)誤需要修改，則需要按以下步驟進(jìn)行：（1）在數(shù)組區(qū)域中單擊任一單元格。（2）單擊公式編輯欄，當(dāng)編輯欄被激活時(shí)，大括號(hào)“{}”在數(shù)組公式中消失。（3）編輯數(shù)組公式內(nèi)容。（4）修改完畢后，按“Crtl+Shift+Enter”組合鍵。要特別注意不要忘記這一步。3．刪除數(shù)組公式刪除數(shù)組公式的步驟是：首先選定存放數(shù)組公式的所有單元格，然后按Delete鍵。2.1.1.2

數(shù)組公式的應(yīng)用1．用數(shù)組公式計(jì)算兩個(gè)數(shù)據(jù)區(qū)域的乘積【例2-1】如圖2-10所示，已經(jīng)知道12個(gè)月的銷(xiāo)售量和產(chǎn)品單價(jià)，則可以利用數(shù)組公式計(jì)算每個(gè)月的銷(xiāo)售額，步驟如下：圖2-10

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

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

產(chǎn)品降價(jià)計(jì)算（1）選取單元格區(qū)域G3:I8。（2）輸入公式“=B3:D8*(1-20%)”。（3）按Crtl+Shift+Enter組合鍵。此外，當(dāng)對(duì)結(jié)構(gòu)相同的不同工作表數(shù)據(jù)進(jìn)行合并匯總處理時(shí)，利用上述方法也將是非常方便的。有關(guān)不同工作表單元格的引用可參閱第1章的有關(guān)內(nèi)容，關(guān)于數(shù)據(jù)的合并計(jì)算可參閱本章2.3.5節(jié)的內(nèi)容。2.1.2

常用函數(shù)及其應(yīng)用在第1章中介紹了一些有關(guān)函數(shù)的基本知識(shí)，本節(jié)對(duì)在財(cái)務(wù)管理中常用的一般函數(shù)應(yīng)用進(jìn)行說(shuō)明，其他有關(guān)的專門(mén)財(cái)務(wù)函數(shù)將在以后的有關(guān)章節(jié)中分別予以介紹。2.1.2.1

SUM函數(shù)、SUMIF函數(shù)和SUMPRODUCT函數(shù)在財(cái)務(wù)管理中，應(yīng)用最多的是求和函數(shù)。求和函數(shù)有三個(gè)：無(wú)條件求和SUM函數(shù)、條件求和SUMIF函數(shù)和多組數(shù)據(jù)相乘求和SUMPRODUCT函數(shù)。1．無(wú)條件求和SUM函數(shù)該函數(shù)是求30個(gè)以內(nèi)參數(shù)的和。公式為=SUM（參數(shù)1，參數(shù)2，…，參數(shù)N）當(dāng)對(duì)某一行或某一列的連續(xù)數(shù)據(jù)進(jìn)行求和時(shí)，還可以使用工具欄中的自動(dòng)求和按鈕。例如，在例2-1中，求全年的銷(xiāo)售量，則可以單擊單元格N2，然后再單擊求和按鈕，按回車(chē)鍵即可，如圖2-13所示。圖2-13

自動(dòng)求和2．條件求和SUMIF函數(shù)SUMIF函數(shù)的功能是根據(jù)指定條件對(duì)若干單元格求和，公式為=SUMIF(range,criteria,sum_range)式中

range—用于條件判斷的單元格區(qū)域；criteria—確定哪些單元格將被相加求和的條件，其形式可以為數(shù)字、表達(dá)式或文本；sum_range—需要求和的實(shí)際單元格。只有當(dāng)range中的相應(yīng)單元格滿足條件時(shí)，才對(duì)sum_range中的單元格求和。如果省略sum_range,則直接對(duì)range中的單元格求和。利用這個(gè)函數(shù)進(jìn)行分類匯總是很有用的?！纠?-4】某商場(chǎng)2月份銷(xiāo)售的家電流水記錄如圖2-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)”，即可得到分類銷(xiāo)售額匯總表。圖2-14

商品銷(xiāo)售額分類匯總SUMIF函數(shù)的對(duì)話框如圖2-15所示。圖2-15

SUMIF函數(shù)對(duì)話框當(dāng)需要分類匯總的數(shù)據(jù)很大時(shí)，利用SUMIF函數(shù)是很方便的。3．SUMPRODUCT函數(shù)SUMPRODUCT函數(shù)的功能是在給定的幾組數(shù)組中，將數(shù)組間對(duì)應(yīng)的元素相乘，并返回乘積之和。公式為=SUMPRODUCT(array1,array2,array3,…)式中，array1,array2,array3,...為1至30個(gè)數(shù)組。需注意的是，數(shù)組參數(shù)必須具有相同的維數(shù)，否則，函數(shù)SUMPRODUCT將返回錯(cuò)誤值#VALUE!。對(duì)于非數(shù)值型的數(shù)組元素將作為0處理。例如，在例2-2中，要計(jì)算2002年產(chǎn)品A的銷(xiāo)售總額，可在任一單元格（比如O2）中輸入公式“=SUMPRODUCT(C2:N2,C3:N3)”即可2.1公式及函數(shù)的高級(jí)應(yīng)用(3)2.1.2.2

AVERAGE函數(shù)AVERAGE函數(shù)的功能是計(jì)算給定參數(shù)的算術(shù)平均值。公式為=AVERAGE（參數(shù)1，參數(shù)2，…，參數(shù)N）函數(shù)中的參數(shù)可以是數(shù)字，或者是涉及數(shù)字的名稱、數(shù)組或引用。如果數(shù)組或單元格引用參數(shù)中有文字、邏輯值或空單元格，則忽略其值。但是，如果單元格包含零值則計(jì)算在內(nèi)。AVERAGE函數(shù)的使用方法與SUM函數(shù)相同，此處不再介紹。2.1.2.3

MIN函數(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。2.1.2.4

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

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

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

銷(xiāo)售部門(mén)業(yè)績(jī)?cè)u(píng)價(jià)（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”組合鍵。則各個(gè)銷(xiāo)售部門(mén)的銷(xiāo)售業(yè)績(jī)?cè)u(píng)價(jià)結(jié)果就顯示在單元格域C3:C12中。也可以直接在單元格C3中輸入公式“=IF(B3<100000,"差",IF(B3<200000,"一般",IF(B3<300000,"好",IF(B3<400000,"較好","很好"))))”后，將其向下填充復(fù)制到C4～C12單元格中。2.1.2.6

AND函數(shù)、OR函數(shù)和NOT函數(shù)這3個(gè)函數(shù)的用法如下：=AND（條件1，條件2，…，條件N）=OR（條件1，條件2，…，條件N）=NOT（條件）AND函數(shù)表示邏輯與，當(dāng)所有條件都滿足時(shí)（即所有參數(shù)的邏輯值都為真時(shí)），AND函數(shù)返回TRUE，否則，只要有一個(gè)條件不滿足即返回FALSE。OR函數(shù)表示邏輯或，只要有一個(gè)條件滿足時(shí)，OR函數(shù)返回TRUE，只有當(dāng)所有條件都不滿足時(shí)才返回FALSE。NOT函數(shù)只有一個(gè)邏輯參數(shù)，它可以計(jì)算出TRUE或FALSE的邏輯值或邏輯表達(dá)式。如果邏輯值為FALSE，函數(shù)NOT返回TRUE；如果邏輯值為T(mén)RUE，函數(shù)NOT返回FALSE。這3個(gè)函數(shù)一般與IF函數(shù)結(jié)合使用。【例2-6】某企業(yè)根據(jù)各銷(xiāo)售部門(mén)的銷(xiāo)售額及銷(xiāo)售費(fèi)用確定獎(jiǎng)金提成比例及提取額，若銷(xiāo)售額大于300000元且銷(xiāo)售費(fèi)用占銷(xiāo)售額的比例不超過(guò)1%，則獎(jiǎng)金提取比例為15%，否則為10%，則計(jì)算過(guò)程如下（如圖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”組合鍵。則各銷(xiāo)售部門(mén)的銷(xiāo)售獎(jiǎng)金提成比例及獎(jiǎng)金提取額如圖2-17所示。圖2-17

獎(jiǎng)金提成比例及提取額的計(jì)算2.1公式及函數(shù)的高級(jí)應(yīng)用(4).1.2.7

LOOKUP函數(shù)、VLOOKUP函數(shù)和HLOOKUP函數(shù)1．LOOKUP函數(shù)LOOKUP函數(shù)的功能是返回向量（單行區(qū)域或單列區(qū)域）或數(shù)組中的數(shù)值。函數(shù)LOOKUP有兩種語(yǔ)法形式：向量和數(shù)組。函數(shù)LOOKUP的向量形式是在單行區(qū)域或單列區(qū)域（向量）中查找數(shù)值，然后返回第二個(gè)單行區(qū)域或單列區(qū)域中相同位置的數(shù)值；函數(shù)LOOKUP的數(shù)組形式在數(shù)組的第一行或第一列查找指定的數(shù)值，然后返回?cái)?shù)組的最后一行或最后一列中相同位置的數(shù)值。（1）向量形式：公式為=LOOKUP(lookup_value,lookup_vector,result_vector)式中l(wèi)ookup_value—函數(shù)LOOKUP在第一個(gè)向量中所要查找的數(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進(jìn)行比較。例如：LOOKUP(5.2,{4.2,5,7,9,10})=5。注意：lookup_vector的數(shù)值必須按升序排列，否則函數(shù)LOOKUP不能返回正確的結(jié)果。文本不區(qū)分大小寫(xiě)。如果函數(shù)LOOKUP找不到lookup_value，則查找lookup_vector中小于或等于lookup_value的最大數(shù)值。如果lookup_value小于lookup_vector中的最小值，函數(shù)LOOKUP返回錯(cuò)誤值#N/A。2．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ù)表，可以使用對(duì)區(qū)域或區(qū)域名稱的引用，例如數(shù)據(jù)庫(kù)或數(shù)據(jù)清單；如果range_lookup為T(mén)RUE，則table_array的第一列中的數(shù)值必須按升序排列，否則函數(shù)VLOOKUP不能返回正確的數(shù)值，如果range_lookup為FALSE，table_array不必進(jìn)行排序。table_array的第一列中的數(shù)值可以為文本、數(shù)字或邏輯值，且不區(qū)分文本的大小寫(xiě)；col_index_num—table_array中待返回的匹配值的列序號(hào)；col_index_num為1時(shí)，返回table_array第一列中的數(shù)值；col_index_num為2時(shí)，返回table_array第二列中的數(shù)值，以此類推。如果col_index_num小于1，函數(shù)VLOOKUP返回錯(cuò)誤值#VALUE!；如果col_index_num大于table_array的列數(shù)，函數(shù)VLOOKUP返回錯(cuò)誤值#REF!。range_lookup—邏輯值，指明函數(shù)VLOOKUP返回時(shí)是精確匹配還是近似匹配。如果其為T(mén)RUE或省略，則返回近似匹配值，也就是說(shuō)，如果找不到精確匹配值，則返回小于lookup_value的最大數(shù)值；如果range_value為FALSE，函數(shù)VLOOKUP將返回精確匹配值。如果找不到，則返回錯(cuò)誤值#N/A。VLOOKUP函數(shù)在財(cái)務(wù)管理與分析中是一個(gè)經(jīng)常用到的函數(shù)，因此熟悉它將會(huì)帶來(lái)很大便利。在以后的有關(guān)章節(jié)中會(huì)經(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。3．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中待返回的匹配值的行序號(hào)。row_index_num為1時(shí)，返回table_array第一行的數(shù)值，row_index_num為2時(shí)，返回table_array第二行的數(shù)值，以此類推。如果row_index_num小于1，函數(shù)HLOOKUP返回錯(cuò)誤值#VALUE!；如果row_index_num大于table_array的行數(shù)，函數(shù)HLOOKUP返回錯(cuò)誤值#REF!。式中的其他參數(shù)含義參閱VLOOKUP函數(shù)。HLOOKUP函數(shù)與VLOOKUP函數(shù)的區(qū)別是：當(dāng)比較值位于數(shù)據(jù)表的首行，并且要查找下面給定行中的數(shù)據(jù)時(shí)，使用函數(shù)HLOOKUP；當(dāng)比較值位于要進(jìn)行數(shù)據(jù)查找的左邊一列時(shí)，使用函數(shù)VLOOKUP。VLOOKUP函數(shù)在首列進(jìn)行檢索，先得到的是行號(hào)，然后根據(jù)col_index_num參數(shù)指定的列標(biāo)返回指定的單元格數(shù)值；而HLOOKUP函數(shù)在首行進(jìn)行檢索，先得到的是列標(biāo)，然后根據(jù)row_index_num參數(shù)指定的行號(hào)返回指定的單元格數(shù)值。2.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時(shí)，lookup_array必須按降序排列，函數(shù)MATCH查找大于或等于lookup_value的最小數(shù)值；當(dāng)match_type為0時(shí)，lookup_array可以按任何順序排列，函數(shù)MATCH查找等于lookup_value的第一個(gè)數(shù)值；當(dāng)match_type為1時(shí)，lookup_array必須按升序排列，函數(shù)MATCH查找小于或等于lookup_value的最大數(shù)值。例如，MATCH(12,{23,43,12,55},0)=3，MATCH(40,{23,43,12,55})=1。2.1公式及函數(shù)的高級(jí)應(yīng)用(5)2.1.2.9

INDEX函數(shù)INDEX函數(shù)的功能是返回表格或區(qū)域中的數(shù)值或?qū)?shù)值的引用。INDEX函數(shù)有以下兩種形式：1．返回?cái)?shù)組中指定單元格或單元格數(shù)組的數(shù)值。公式為=INDEX(array,row_num,column_num)

式中

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

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

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

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

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

ref_text—對(duì)單元格的引用，此單元格可以包含A1樣式的引用、R1C1樣式的引用、定義為引用的名稱或?qū)ξ淖执畣卧竦囊茫绻鹯ef_text不是合法的單元格的引用，函數(shù)INDIRECT返回錯(cuò)誤值#REF!；a1—一邏輯值，指明包含在單元格ref_text中的引用的類型，如果a1為T(mén)RUE或省略，ref_text被解釋為A1樣式的引用，如果a1為FALSE，ref_text被解釋為R1C1樣式的引用。需要注意的是，如果ref_text是對(duì)另一個(gè)工作簿的引用（外部引用），則那個(gè)工作簿必須被打開(kāi)。如果源工作簿沒(méi)有打開(kāi)，函數(shù)INDIRECT返回錯(cuò)誤值#REF!。例如：如果單元格A1包含文本"B2"，且單元格B2包含數(shù)值1.333，則：INDIRECT($A$1)=1.333。上述介紹的幾個(gè)查找函數(shù)LOOKUP、VLOOKUP、HLOOKUP、MATCH、INDEX、ADDRESS、INDIRECT等在財(cái)務(wù)分析與決策、預(yù)測(cè)及建立動(dòng)態(tài)圖表等中是非常有用的。.1公式及函數(shù)的高級(jí)應(yīng)用(6)2.1.2.12

矩陣函數(shù)——TRANSPOSE函數(shù)、MINVERSE函數(shù)和MMULT函數(shù)1．TRANSPOSE函數(shù)TRANSPOSE函數(shù)的功能是求矩陣的轉(zhuǎn)置矩陣。公式為=TRANSPOSE(array)式中，Array—需要進(jìn)行轉(zhuǎn)置的數(shù)組或工作表中的單元格區(qū)域。函數(shù)TRANSPOSE必須在某個(gè)區(qū)域中以數(shù)組公式的形式輸入，該區(qū)域的行數(shù)和列數(shù)分別與array的列數(shù)和行數(shù)相同?！纠?-7】假設(shè)矩陣A中的值如圖2-18中單元格區(qū)域A2:C5，求其轉(zhuǎn)置矩陣的步驟如下：圖2-18

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

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

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

number—需要進(jìn)行舍入的數(shù)字；num_digits—指定的位數(shù)，按此位數(shù)進(jìn)行舍入。如果num_digits大于0，則舍入到指定的小數(shù)位；如果num_digits等于0，則舍入到最接近的整數(shù)；如果num_digits小于0，則在小數(shù)點(diǎn)左側(cè)進(jìn)行舍入。利用ROUND函數(shù)可以防止利用格式工具欄上的【增加小數(shù)位數(shù)】或【減少小數(shù)位數(shù)】所帶來(lái)的看起來(lái)“假數(shù)據(jù)”問(wèn)題的出現(xiàn)，使得工作表上顯示的數(shù)據(jù)真實(shí)可靠。實(shí)際上，如果需要調(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，看起來(lái)似乎單元格A1的數(shù)據(jù)為14.38，但實(shí)際上仍為14.3772。若在單元格B1中輸入公式“=3*A1”，則單元格B1中的數(shù)據(jù)顯示為43.13，也許“不明真相”的人認(rèn)為單元格B1的數(shù)據(jù)算錯(cuò)了（14.38乘以3應(yīng)該等于43.14），但實(shí)際上單元格的數(shù)據(jù)為43.1316，這種看起來(lái)的“假”數(shù)據(jù)可能會(huì)對(duì)實(shí)際工作帶來(lái)不便。因此，正確的方法應(yīng)是：?jiǎn)卧馚1中應(yīng)輸入公式“=ROUND(3*ROUND(A1,2),2)”，結(jié)果為43.14，即先將單元格A1的數(shù)據(jù)用函數(shù)ROUND四舍五入，然后再對(duì)計(jì)算后的數(shù)據(jù)四舍五入。2.2圖表處理(1)Excel具有完整的圖表功能，它不僅可以生成諸如條形圖、折線圖、餅圖等標(biāo)準(zhǔn)圖表，還可以生成較為復(fù)雜的三維立體圖表。對(duì)各種財(cái)務(wù)數(shù)據(jù)進(jìn)行圖表處理，可以更直觀地進(jìn)行財(cái)務(wù)分析，找出工作表格不容易發(fā)現(xiàn)的問(wèn)題，使得財(cái)務(wù)管理工作更為有效。2.2.1

圖表類型Excel提供了約14種標(biāo)準(zhǔn)圖表類型，如面積圖、柱形圖、條形圖、折線圖、餅圖、圓環(huán)圖、氣泡圖、雷達(dá)圖、股價(jià)圖、曲面圖、散點(diǎn)圖、錐形圖、圓柱圖、棱錐圖等，每種圖表類型又都有幾種不同的子類型。此外，Excel還提供了約20種自定義圖表類型，用戶可根據(jù)不同的需要選用適當(dāng)?shù)膱D表類型。關(guān)于各種圖表類型的具體情況，可單擊工具欄上的【圖表向?qū)А堪粹o，或單擊【插入】菜單，選擇【圖表】項(xiàng)，即可查看各種圖表。2.2.2

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

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

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

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

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

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

銷(xiāo)售額與銷(xiāo)售費(fèi)用變化圖2.2.3

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

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

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

【數(shù)據(jù)系列格式】對(duì)話框5．改變圖表顏色、圖案、邊框改變圖表顏色、圖案、邊框，可通過(guò)【圖表區(qū)格式】對(duì)話框來(lái)完成，方法是：?jiǎn)螕魣D表區(qū)域，單擊鼠標(biāo)右鍵，在快捷菜單中選【圖表區(qū)格式】項(xiàng)，彈出【圖表區(qū)格式】對(duì)話框，即可進(jìn)行相應(yīng)的修改。2.2.4

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

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

【對(duì)象】對(duì)話框圖2-30

某企業(yè)的銷(xiāo)售地區(qū)分布圖2-31

地圖菜單項(xiàng)圖2-32

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

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

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

動(dòng)態(tài)銷(xiāo)售圖表（1）設(shè)計(jì)動(dòng)態(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)所在的行號(hào)，ADDRESS（行號(hào)，列標(biāo)）的意思是返回由行號(hào)和列標(biāo)確定的單元格，INDIRECT的意思是返回參數(shù)所確定的單元格內(nèi)容；（3）選中區(qū)域A12:M13，插入“折線圖”，并進(jìn)行相應(yīng)的格式設(shè)置，則動(dòng)態(tài)圖表就建立起來(lái)了。若鼠標(biāo)單擊A3單元格，再按F9鍵（即對(duì)工作表數(shù)據(jù)重新計(jì)算），就會(huì)顯示部門(mén)A的銷(xiāo)售圖；若鼠標(biāo)單擊A5單元格，再按F9鍵（即對(duì)工作表數(shù)據(jù)重新計(jì)算），就會(huì)顯示部門(mén)C的銷(xiāo)售圖。這樣，就可以很方便地對(duì)各個(gè)銷(xiāo)售部門(mén)的銷(xiāo)售量進(jìn)行直觀的觀察和分析。2.3數(shù)據(jù)分析處理(1)Excel提供了強(qiáng)大的數(shù)據(jù)分析處理功能，利用它們可以實(shí)現(xiàn)對(duì)數(shù)據(jù)的排序、分類匯總、篩選及數(shù)據(jù)透視等操作。在進(jìn)行數(shù)據(jù)分析處理之前，首先必須注意以下幾個(gè)問(wèn)題：（1）避免在數(shù)據(jù)清單中存在有空行和空列。（2）避免在單元格的開(kāi)頭和末尾鍵入空格。（3）避免在一張工作表中建立多個(gè)數(shù)據(jù)清單，每張工作表應(yīng)僅使用一個(gè)數(shù)據(jù)清單。（4）在工作表的數(shù)據(jù)清單應(yīng)與其他數(shù)據(jù)之間至少留出一個(gè)空列和一個(gè)空行，以便于檢測(cè)和選定數(shù)據(jù)清單。（5）關(guān)鍵數(shù)據(jù)應(yīng)置于數(shù)據(jù)清單的頂部或底部。2.3.1

數(shù)據(jù)排序2.3.1.1

數(shù)據(jù)排序的規(guī)則Excel允許對(duì)字符、數(shù)字等數(shù)據(jù)按大小順序進(jìn)行升序或降序排列，要進(jìn)行排序的數(shù)據(jù)稱之為關(guān)鍵字。不同類型的關(guān)鍵字的排序規(guī)則如下：數(shù)值：按數(shù)值的大小。字母：按字母先后順序。日期：按日期的先后。漢字：按漢語(yǔ)拼音的順序或按筆畫(huà)順序。邏輯值：升序時(shí)FALSE排在TRUE前面，降序時(shí)相反?？崭瘢嚎偸桥旁谧詈蟆?.3.1.2

數(shù)據(jù)排序步驟（1）單擊數(shù)據(jù)區(qū)中要進(jìn)行排序的任意單元格。（2）單擊【數(shù)據(jù)】菜單，選擇【排序】項(xiàng)，系統(tǒng)將彈出【排序】對(duì)話框，如圖2-35所示。圖2-35

【排序】對(duì)話框（3）在【排序】對(duì)話框中用下拉列表框選擇要排序的關(guān)鍵字，關(guān)鍵字有“主要關(guān)鍵字”、“次要關(guān)鍵字”和“第三關(guān)鍵字”，根據(jù)需要分別選擇不同的關(guān)鍵字；（4）單擊【確定】按鈕，數(shù)據(jù)就按要求進(jìn)行了排序。當(dāng)只有一個(gè)關(guān)鍵字時(shí)，可以單擊工具欄上的升序按鈕或降序按鈕，進(jìn)行自動(dòng)排序。2.3.1.3

自定義排序在有些情況下，對(duì)數(shù)據(jù)的排序順序可能非常特殊，既不是按數(shù)值大小次序、也不是按漢字的拼音順序或筆畫(huà)順序，而是按照指定的特殊次序，如對(duì)總公司的各個(gè)分公司按照要求的順序進(jìn)行排序，按產(chǎn)品的種類或規(guī)格排序等等，這時(shí)就需要自定義排序。利用自定義排序方法進(jìn)行排序，首先應(yīng)建立自定義序列，其方法可參閱第1章的有關(guān)內(nèi)容。建立好自定義序列后，即可對(duì)數(shù)據(jù)進(jìn)行排序，方法是：?jiǎn)螕魯?shù)據(jù)區(qū)中要進(jìn)行排序的任意單元格，單擊【數(shù)據(jù)】菜單，選擇【排序】項(xiàng)，在彈出的【排序】對(duì)話框中單擊【選項(xiàng)】按鈕，系統(tǒng)彈出【排序選項(xiàng)】對(duì)話框，如圖2-36所示，在【自定義排序次序】的下拉列表中，選擇前面建立的自定義序列，然后單擊【確定】按鈕，即可對(duì)數(shù)據(jù)進(jìn)行自定義排序。圖2-36

【排序選項(xiàng)】對(duì)話框2.3.2

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

某公司的商品銷(xiāo)售明細(xì)清單根據(jù)圖2-37中的有關(guān)資料，可以分別采用記錄單查找、自動(dòng)篩選或高級(jí)篩選的方式查找或選擇所需要的信息，如下所述：2.3數(shù)據(jù)分析處理(2)2.3.2.1

記錄單查找記錄單是查找和編輯數(shù)據(jù)的最簡(jiǎn)單的方法，利用記錄單，不僅可以查找數(shù)據(jù)記錄，還可以修改和刪除記錄、添加新的數(shù)據(jù)記錄等。1．查找數(shù)據(jù)記錄利用記錄單查找數(shù)據(jù)記錄的步驟如下：（1）用鼠標(biāo)單擊數(shù)據(jù)清單或數(shù)據(jù)庫(kù)中的任一非空單元格。（2）單擊【數(shù)據(jù)】菜單，選擇【記錄單】項(xiàng)，則系統(tǒng)彈出如圖2-38所示的記錄單。圖2-38

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

記錄單條件對(duì)話框（4）輸入條件，比如要查找“張三”的銷(xiāo)售記錄，則在【銷(xiāo)售人員】欄中輸入“張三”，然后單擊【上一條】按鈕或【下一條】按鈕，系統(tǒng)就逐次顯示滿足條件的記錄行。還可以使用多個(gè)條件聯(lián)合查找記錄，此處不再敘述。2．修改或刪除記錄在圖2-38所示的記錄單中，即可對(duì)某一記錄的各字段進(jìn)行修改。若要?jiǎng)h除顯示的記錄，只需單擊記錄單上的【刪除】按鈕即可。3．添加新的記錄在圖2-38所示的記錄單中，單擊記錄單上的【新建】按鈕，則出現(xiàn)各字段均為空白的新建記錄單，在記錄單中輸入各字段的值，輸入完畢后，單擊【新建】按鈕，即完成添加新記錄。2.3.2.2

自動(dòng)篩選與自定義篩選1．自動(dòng)篩選記錄單檢索數(shù)據(jù)每次只能顯示一個(gè)數(shù)據(jù)行，當(dāng)查詢的數(shù)據(jù)較多，或要把查詢的結(jié)果匯總成表時(shí)，就需要使用篩選工具了。自動(dòng)篩選提供了快速檢索數(shù)據(jù)清單或數(shù)據(jù)庫(kù)的方法，通過(guò)簡(jiǎn)單的操作，就能篩選出需要的數(shù)據(jù)。利用自動(dòng)篩選查找數(shù)據(jù)的步驟如下：（1）用鼠標(biāo)單擊數(shù)據(jù)清單或數(shù)據(jù)庫(kù)中的任一非空單元格。（2）單擊【數(shù)據(jù)】菜單，選擇【篩選】項(xiàng)，在【篩選】子菜單中選擇【自動(dòng)篩選】，則系統(tǒng)自動(dòng)在數(shù)據(jù)清單的每列數(shù)據(jù)的標(biāo)題旁邊添加一個(gè)下拉列標(biāo)標(biāo)志，如圖2-40所示。圖2-40

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

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

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

【自定義自動(dòng)篩選方式】對(duì)話框圖2-44

【自定義自動(dòng)篩選方式】篩選的結(jié)果2.3數(shù)據(jù)分析處理(3)2.3.2.3

高級(jí)篩選高級(jí)篩選可以使用較多的條件來(lái)對(duì)數(shù)據(jù)清單進(jìn)行篩選，這些條件既可以是與條件，也可以是或條件，或與條件，與或條件的組合使用，還可以使用計(jì)算條件。1．一般情況下的高級(jí)篩選利用高級(jí)篩選對(duì)數(shù)據(jù)清單進(jìn)行篩選的步驟如下：（1）首先應(yīng)建立一個(gè)條件區(qū)域。在條件區(qū)域中，同一行中的條件是與條件，也就是這些條件必須同時(shí)滿足；不同行中的條件是或條件，也就是這些條件只要滿足其一即可。如需要查找張三銷(xiāo)售彩電的所有記錄，則建立條件區(qū)域如圖2-45所示。圖2-45

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

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

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

計(jì)算條件情況下的高級(jí)篩選（2）設(shè)置條件區(qū)域，條件區(qū)域的列表可以是除數(shù)據(jù)清單中數(shù)據(jù)標(biāo)題以外的任何文本，而篩選條件可在單元格B20中輸入“=E3>$H$20”，這里要特別注意：必須以絕對(duì)引用的方式引用銷(xiāo)售額平均值，以相對(duì)引用的方式引用數(shù)據(jù)清單中的數(shù)據(jù)。（3）按照前面介紹的步驟進(jìn)行高級(jí)篩選，其中高級(jí)篩選的數(shù)據(jù)區(qū)域?yàn)?A$2:$G$16；高級(jí)篩選的條件區(qū)域?yàn)?B$19:$C$20，則篩選結(jié)果如圖2-48所示。2.3.3

數(shù)據(jù)的分類與匯總在對(duì)數(shù)據(jù)進(jìn)行分析時(shí)，常常需要將相同類型的數(shù)據(jù)統(tǒng)計(jì)出來(lái)，這就是數(shù)據(jù)的分類與匯總。在對(duì)數(shù)據(jù)進(jìn)行匯總之前，應(yīng)特別注意的是：首先必須對(duì)要匯總的關(guān)鍵字進(jìn)行排序。2.3.3.1

進(jìn)行分類匯總例如，在例2-11中，要按地區(qū)進(jìn)行自動(dòng)分類匯總，其步驟如下：（1）首先對(duì)“地區(qū)”進(jìn)行排序，排序方法見(jiàn)前面所述。（2）單擊數(shù)據(jù)清單或數(shù)據(jù)庫(kù)中的任一非空單元格，然后單擊【數(shù)據(jù)】菜單，選擇【分類匯總】項(xiàng)，系統(tǒng)彈出如圖2-49所示的【分類匯總】對(duì)話框。圖2-49

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

按地區(qū)分類匯總結(jié)果在圖2-50中，左上角有3個(gè)按鈕，按鈕1表示1級(jí)匯總，顯示全部的銷(xiāo)售數(shù)量和銷(xiāo)售金額匯總；按鈕2表示2級(jí)匯總，顯示各地區(qū)的全部銷(xiāo)售數(shù)量和銷(xiāo)售金額匯總；按鈕3表示3級(jí)匯總，顯示各地區(qū)的銷(xiāo)售數(shù)量和銷(xiāo)售金額的匯總明細(xì)及匯總額（即圖2-50所示的匯總結(jié)果）。圖2-50中，左邊的滑動(dòng)按鈕為隱藏明細(xì)按鈕，單擊此按鈕，則將隱藏本級(jí)的明細(xì)數(shù)據(jù)，同時(shí)變?yōu)轱@示明細(xì)按鈕，再單擊按鈕，則將顯示本級(jí)的全部明細(xì)數(shù)據(jù)，同時(shí)變?yōu)?。在上述自?dòng)分類匯總的結(jié)果上，還可以再進(jìn)行分類匯總，例如再進(jìn)行另一種分類匯總，兩次分類匯總的關(guān)鍵字可以相同，也可以不同，其分類匯總方法與前面的是一樣的，此處不再介紹。2.3數(shù)據(jù)分析處理(4).3.3.2

分類匯總的撤消如果不再需要分類匯總結(jié)果，可在圖2-49所示的【分類匯總】對(duì)話框中單擊【全部刪除】，即可撤消分類匯總。2.3.4

數(shù)據(jù)透視表數(shù)據(jù)透視表是用于快速匯總大量數(shù)據(jù)的交互式表格，用戶可以旋轉(zhuǎn)其行或列以查看對(duì)源數(shù)據(jù)的不同匯總，也可以通過(guò)顯示不同的頁(yè)來(lái)篩選數(shù)據(jù)，還可以顯示所關(guān)心區(qū)域的數(shù)據(jù)明細(xì)。通過(guò)對(duì)源數(shù)據(jù)表的行、列進(jìn)行重新排列，使得數(shù)據(jù)表達(dá)的信息更清楚明了。2.3.4.1

建立數(shù)據(jù)透視表以例2-11的數(shù)據(jù)為例，建立數(shù)據(jù)透視表的步驟如下：（1）首先，要保證數(shù)據(jù)源是一個(gè)數(shù)據(jù)清單或數(shù)據(jù)庫(kù)，即數(shù)據(jù)表的每列必須有列標(biāo)。（2）單擊數(shù)據(jù)清單或數(shù)據(jù)庫(kù)中的任一非空單元格，然后單擊【數(shù)據(jù)】菜單，選擇【數(shù)據(jù)透視表和圖表報(bào)告】項(xiàng)，則系統(tǒng)彈出【數(shù)據(jù)透視表和數(shù)據(jù)透視圖向?qū)А?步驟之1】對(duì)話框，如圖2-51所示，根據(jù)待分析數(shù)據(jù)來(lái)源及需要?jiǎng)?chuàng)建何種報(bào)表類型，進(jìn)行相應(yīng)的選擇，然后單擊【下一步】按鈕，系統(tǒng)彈出【數(shù)據(jù)透視表和數(shù)據(jù)透視圖向?qū)А?步驟之2】對(duì)話框，如圖2-52所示；圖2-51

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

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

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

【數(shù)據(jù)透視表和數(shù)據(jù)透視圖向?qū)А媸健繉?duì)話框圖2-55

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

各個(gè)銷(xiāo)售人員銷(xiāo)售商品的數(shù)據(jù)透視表2.3.4.2

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

李四的銷(xiāo)售情況匯總還可以建立透視圖，方法是：?jiǎn)螕魯?shù)據(jù)透視表中的任一單元格，單擊鼠標(biāo)右鍵，在快捷菜單中選擇【數(shù)據(jù)透視圖】項(xiàng)，則系統(tǒng)自動(dòng)顯示出數(shù)據(jù)透視圖，從而得到每個(gè)銷(xiāo)售人員的更為直觀的銷(xiāo)售情況。2.3數(shù)據(jù)分析處理(5)2.3.4.3

數(shù)據(jù)更新當(dāng)數(shù)據(jù)清單中的數(shù)據(jù)發(fā)生變化時(shí)，需要對(duì)數(shù)據(jù)透視表進(jìn)行更新，方法是：?jiǎn)螕魯?shù)據(jù)透視表中的任一單元格，單擊鼠標(biāo)右鍵，在快捷菜單中選擇【更新數(shù)據(jù)】項(xiàng)，也可在數(shù)據(jù)透視表的【數(shù)據(jù)】菜單中選擇【更新數(shù)據(jù)】項(xiàng)。2.3.4.4

顯示數(shù)據(jù)項(xiàng)的明細(xì)數(shù)據(jù)要想查看數(shù)據(jù)透視表中某數(shù)據(jù)項(xiàng)的明細(xì)數(shù)據(jù)，只需雙擊該數(shù)據(jù)項(xiàng)即可。若要查看某一種商品的銷(xiāo)售明細(xì)，比如要查看彩電的銷(xiāo)售明細(xì)，可雙擊數(shù)據(jù)透視表中的數(shù)據(jù)項(xiàng)“彩電”，系統(tǒng)彈出【顯示明細(xì)數(shù)據(jù)】對(duì)話框，選擇需要顯示的數(shù)據(jù)字段，單擊【確定】按鈕即可。2.3.5

數(shù)據(jù)的合并計(jì)算在很多情況下，企業(yè)的銷(xiāo)售數(shù)據(jù)分門(mén)分類記錄，到年末時(shí)需要匯總計(jì)算到一張年度報(bào)表上，這時(shí)最簡(jiǎn)單的方法就是對(duì)它們進(jìn)行合并計(jì)算，尤其是在需要合并的數(shù)據(jù)很多時(shí)，利用合并計(jì)算工具能節(jié)省很大的工作量，并且也不容易出錯(cuò)?，F(xiàn)舉一個(gè)簡(jiǎn)單的例子來(lái)說(shuō)明合并計(jì)算的方法和步驟?！纠?-12】某企業(yè)四個(gè)季度的銷(xiāo)售量統(tǒng)計(jì)如圖2-58所示，它們分別存放在不同的工作簿——銷(xiāo)售統(tǒng)計(jì)-1.xls，銷(xiāo)售統(tǒng)計(jì)-2.xls，銷(xiāo)售統(tǒng)計(jì)-3.xls和銷(xiāo)售統(tǒng)計(jì)-4.xls中，現(xiàn)要將它們匯總合并到另外一張名為銷(xiāo)售統(tǒng)計(jì)-年度.xls）的工作簿中，則合并計(jì)算步驟如下：圖2-58

某企業(yè)四個(gè)季度的銷(xiāo)售量統(tǒng)計(jì)（1）在“銷(xiāo)售統(tǒng)計(jì)-年度.xls”的工作簿中選取單元格區(qū)域B3:B9。（2）單擊【數(shù)據(jù)】菜單，選取【合并計(jì)算】項(xiàng)，則彈出【合并計(jì)算】對(duì)話框，如圖2-59所示。圖2-59

【合并計(jì)算】對(duì)話框（3）在【函數(shù)】項(xiàng)中選“求和”。（4）在【引用位置】中輸入第一季度銷(xiāo)售統(tǒng)計(jì)工作簿“銷(xiāo)售統(tǒng)計(jì)-1.xls”的數(shù)據(jù)區(qū)域B3:B9，最好用鼠標(biāo)選取，即：激活工作簿“銷(xiāo)售統(tǒng)計(jì)-1.xls”，然后選取該工作表的B3:B9單元格區(qū)域，輸入完畢后，單擊【添加】按鈕，則該單元格區(qū)域自動(dòng)加入【所有引用位置】中去，并在【引用位置】的文字呈反黑顯示。（5）仿照上述方法輸入其他三個(gè)季度的資料，最后的結(jié)果如圖2-59所示。（6）選中【創(chuàng)建連至數(shù)據(jù)源的連接】，這是為了能夠查看最終匯總數(shù)據(jù)的來(lái)源，若沒(méi)有這個(gè)要求，可不選此項(xiàng)。（7）單擊【確定】按鈕，則最后的合并計(jì)算結(jié)果如圖2-60所示。圖2-60

合并計(jì)算結(jié)果在圖2-60中，左上角的按鈕1為總的合并數(shù)據(jù)的顯示按鈕，按鈕2為所有合并數(shù)據(jù)的明細(xì)顯示按鈕。在每個(gè)合并數(shù)據(jù)的左邊有一個(gè)滑動(dòng)按鈕，單擊此按鈕，則將在此合并數(shù)據(jù)的上方顯示隱藏的合并明細(xì)數(shù)據(jù)，同時(shí)變?yōu)轱@示明細(xì)按鈕，再單擊此按鈕，則合并明細(xì)數(shù)據(jù)再度隱藏起來(lái)，同時(shí)變?yōu)椤Ｈ舨贿x中【創(chuàng)建連至數(shù)據(jù)源的連接】，則合并計(jì)算結(jié)果如圖2-61所示。圖2-61

合并計(jì)算結(jié)果需要說(shuō)明的是，合并計(jì)算并不意味著只是簡(jiǎn)單的求和匯總（SUM函數(shù)），還包括求平均值（AVERAGE函數(shù)）、求最大值（MAX函數(shù)）、求最小值（MIN函數(shù)）、求對(duì)應(yīng)單元格的乘積（PRODUCT函數(shù)）、計(jì)數(shù)（COUNT函數(shù)）、求標(biāo)準(zhǔn)差（STDDEV函數(shù)）、求總體標(biāo)準(zhǔn)差（STDDEVP函數(shù)）、求方差（VAR函數(shù)）、求總體方差（VARP函數(shù)）等運(yùn)算。2.4數(shù)據(jù)分析工具的應(yīng)用(1)Excel提供了非常實(shí)用的數(shù)據(jù)分析工具，利用這些分析工具，可解決財(cái)務(wù)管理中的許多問(wèn)題，例如財(cái)務(wù)分析工具、統(tǒng)計(jì)分析工具、工程分析工具、規(guī)劃求解工具、方案管理器等等。下面介紹財(cái)務(wù)管理與分析中常用的一些數(shù)據(jù)分析工具。2.4.1

模擬運(yùn)算表模擬運(yùn)算表就是將工作表中的一個(gè)單元格區(qū)域的數(shù)據(jù)進(jìn)行模擬計(jì)算，測(cè)試使用一個(gè)或兩個(gè)變量對(duì)運(yùn)算結(jié)果的影響。在Excel中，可以構(gòu)造兩種模擬運(yùn)算表：?jiǎn)巫兞磕M運(yùn)算表和多變量模擬運(yùn)算表。2.4.1.1

單變量模擬運(yùn)算表單變量模擬運(yùn)算表就是基于一個(gè)輸入變量，用它來(lái)測(cè)試對(duì)公式計(jì)算結(jié)果的影響?！纠?-13】企業(yè)向銀行貸款10000元，期限5年，則可以使用【模擬運(yùn)算表】工具來(lái)測(cè)試不同的利率對(duì)月還款額的影響，步驟如下：（1）設(shè)計(jì)模擬運(yùn)算表結(jié)構(gòu)，如圖2-62所示。圖2-62

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

【模擬運(yùn)算表】對(duì)話框（5）由于本例中引用的是列數(shù)據(jù)，故在【輸入引用列的單元格】中輸入“$A$4”。單擊【確定】按鈕，即得到單變量的模擬運(yùn)算表，如圖2-62所示。2.4.1.2

雙變量模擬運(yùn)算表雙變量模擬運(yùn)算表就是考慮兩個(gè)變量的變化對(duì)公式計(jì)算結(jié)果的影響，在財(cái)務(wù)管理中應(yīng)用最多的是長(zhǎng)期借款雙變量分析模型，有關(guān)詳細(xì)內(nèi)容可參閱第3章的有關(guān)章節(jié)。2.4.2

單變量求解單變量求解就是求解只有一個(gè)變量的方程的根，方程可以是線性方程，也可以是非線性方程。單變量求解工具可以解決許多財(cái)務(wù)管理中涉及到一個(gè)變量的求解問(wèn)題?！纠?-14】某企業(yè)擬向銀行以7%的年利率借入期限為5年的長(zhǎng)期借款，企業(yè)每年的償還能力為100萬(wàn)元，那么企業(yè)最多總共可貸款多少？設(shè)計(jì)如圖2-64所示的計(jì)算表格，在單元格B2中輸入公式“=PMT(B1,B3,B4)”，單擊【工具】菜單，選擇【單變量求解】項(xiàng)，則彈出【單變量求解】對(duì)話框，如圖2-65所示，在【目標(biāo)單元格】中輸入“B2”，在【目標(biāo)值】中輸入“100”，在【可變單元格】中輸入“$B$4”，然后單擊【確定】按鈕，則系統(tǒng)立即計(jì)算出結(jié)果，如圖2-64所示，即企業(yè)最多總共可貸款410.02萬(wàn)元。

圖2-64

貸款總額計(jì)算

圖2-65

【單變量求解】對(duì)話框2.4.3

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

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

產(chǎn)品有關(guān)資料及優(yōu)化結(jié)果.4數(shù)據(jù)分析工具的應(yīng)用(2)1）首先建立優(yōu)化模型，（設(shè)x和y分別表示甲產(chǎn)品和乙產(chǎn)品的生產(chǎn)量）：目標(biāo)函數(shù)：max{銷(xiāo)售利潤(rùn)}=(140－60)×x+(180－100)×y約束條件：6x+9y≤360

7x+4y≤240

18x+15y≤850

y≤30

x≥0,

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

【規(guī)劃求解參數(shù)】對(duì)話框（6）在【規(guī)劃求解參數(shù)】對(duì)話框中，【設(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”；這里，添加約束條件的方法是：?jiǎn)螕簟咎砑印堪粹o，系統(tǒng)會(huì)彈出【添加約束】對(duì)話框，如圖2-68所示，輸入完畢一個(gè)約束條件后，單擊【添加】按鈕，則又彈出空白的【添加約束】對(duì)話框，再輸入第二個(gè)約束條件。當(dāng)所有約束條件都輸入完畢后，單擊【確定】按鈕，則系統(tǒng)返回到【規(guī)劃求解參數(shù)】對(duì)話框。圖2-68

【添加約束】對(duì)話框如果發(fā)現(xiàn)輸入的約束條件有錯(cuò)誤，還可以對(duì)其進(jìn)行修改，方法是：選中要修改的約束條件，單擊【更改】按鈕，則系統(tǒng)彈出【改變約束】對(duì)話框，如圖2-69所示，再進(jìn)行修改即可。圖2-69

【改變約束】對(duì)話框輸入完畢約束條件后，若還需要添加約束條件，單擊【添加】按鈕，在彈出的【添加約束】對(duì)話框中輸入約束條件即可。（7）如果需要，還可以設(shè)置有關(guān)的項(xiàng)目，即單擊【選項(xiàng)】按鈕，彈出【規(guī)劃求解選項(xiàng)】對(duì)話框，如圖2-70所示，對(duì)其中的有關(guān)項(xiàng)目進(jìn)行設(shè)置即可；圖2-70

【規(guī)劃求解選項(xiàng)】對(duì)話框（8）在建立好所有的規(guī)劃求解參數(shù)后，單擊【求解】，則系統(tǒng)將顯示如圖2-71所示的【規(guī)劃求解結(jié)果】對(duì)話框，選擇【保存規(guī)劃求解結(jié)果】項(xiàng)，單擊【確定】，則求解結(jié)果顯示在工作表上，如圖2-66所示。圖2-71

【規(guī)劃求解結(jié)果】對(duì)話框（9）如果需要，還可以單擊【規(guī)劃求解結(jié)果】對(duì)話框中的【保存方案】，以便于對(duì)運(yùn)算結(jié)果做進(jìn)一步的分析。2.4.3.2

求解方程組利用規(guī)劃求解工具還可以求解線性或非線性方程組，下面舉例說(shuō)明：【例2-16】有如下的非線性方程組：則利用規(guī)劃求解工具求解方程組的解步驟如下：（1）設(shè)計(jì)工作表格，如圖2-72所示；圖2-72

利用規(guī)劃求解工具求解方程組（2）單元格E2：E4為變動(dòng)單元格，存放方程組的解，其初值可設(shè)為零（空單元格）；（3）在單元格B2中輸入求和公式“=3*E2^2+2*E3^2-2*E4-8”；在單元格B3中輸入求和公式“=E2^2+(E2+1)*E3-3*E2+E4^2-5”；在單元格B4中輸入求和公式