excel函數(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數(shù)組公式的輸入數(shù)組公式的輸入步驟如下：（1）選定單元格或單元格區(qū)域。如果數(shù)組公式將返回一個(gè)結(jié)果，單擊需要輸入數(shù)組公式的單元格；如果數(shù)組公式將返回多個(gè)結(jié)果，則要選定需要輸入數(shù)組公式的單元格區(qū)域。

2、（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)“

3、”括起來(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、

4、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

5、、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ù)組公式

6、“=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編輯數(shù)組公式數(shù)組公式的特征之一就是不能單獨(dú)編輯、清除或移動(dòng)數(shù)組公式所涉及的單元格區(qū)域中的某一個(gè)單元格。若在數(shù)組公式輸入完畢后發(fā)現(xiàn)錯(cuò)誤需要修改，則需要按以下步驟進(jìn)行：（1）在數(shù)組區(qū)域中單擊任一單元格。（2）單擊公式編輯欄，當(dāng)編輯欄被激活時(shí)，大括號(hào)“

7、”在數(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è)月的月

8、平均銷(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中，將單

9、元格區(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】某企業(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:

10、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

11、.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)的專(zhuā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

12、中，求全年的銷(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 中的單元格求和。

13、利用這個(gè)函數(shù)進(jìn)行分類(lèi)匯總是很有用的?！纠?-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)”，即可得到分類(lèi)銷(xiāo)售額匯總表。圖2-14  商品銷(xiāo)售額分類(lèi)匯總SUMIF函數(shù)的對(duì)話框如圖2-15所示。圖2-15  SUMIF函數(shù)對(duì)話框當(dāng)需要分類(lèi)匯總的數(shù)據(jù)很大時(shí)，利用SUMIF

14、函數(shù)是很方便的。3SUMPRODUCT函數(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.2.2 AVERAGE函數(shù)

15、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ù)值的文字串。例如，MI

16、N（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函

17、數(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é)果要么為 TRUE，要么為 FALSE，它可使用任何比較運(yùn)算符；value_if_truelogical_test 為 TRUE 時(shí)返回的值；value_if_falselogical_test 為 FALSE 時(shí)返回的值。IF函數(shù)在財(cái)務(wù)管理中具有非常廣泛的應(yīng)用?！纠?-5】例如，某企業(yè)對(duì)各個(gè)銷(xiāo)售部門(mén)的銷(xiāo)售業(yè)績(jī)進(jìn)行評(píng)價(jià)，評(píng)價(jià)標(biāo)

18、準(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:C1

19、2中。也可以直接在單元格C3中輸入公式“=IF(B3<100000,"差",IF(B3<200000,"一般",IF(B3<300000,"好",IF(B3<400000,"較好","很好")”后，將其向下填充復(fù)制到C4C12單元格中。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í)）

20、，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；如果邏輯值為 TRUE，函數(shù) NOT 返回FALSE。這3個(gè)函數(shù)一般與IF函數(shù)結(jié)合使用?！纠?-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

21、-17所示）：（1）在單元格D3中輸入公式“=IF(AND(B3>300000,C3/B3<1%),15%,10%)”，將其向下填充復(fù)制到D4C10單元格中。（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.2.7 LOOKUP函數(shù)、VLOOKUP函數(shù)和HLOOKUP函數(shù)1LOOKUP函數(shù)LOOKUP函數(shù)的功能是返回向量（單行區(qū)域或單列區(qū)域）或數(shù)組中的數(shù)值。函數(shù) LOOKUP 有兩種語(yǔ)法形式：向

22、量和數(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ù)字或邏輯

23、值；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ù)值。如果loo

24、kup_value小于lookup_vector中的最小值，函數(shù)LOOKUP返回錯(cuò)誤值#N/A。2VLOOKUP函數(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為

25、TRUE，則table_array的第一列中的數(shù)值必須按升序排列，否則函數(shù)VLOOKUP不能返回正確的數(shù)值，如果range_lookup為FALSE，table_array不必進(jìn)行排序。table_array的第一列中的數(shù)值可以為文本、數(shù)字或邏輯值，且不區(qū)分文本的大小寫(xiě)；col_index_numtable_array中待返回的匹配值的列序號(hào)；col_index_num為1時(shí)，返回table_array第一列中的數(shù)值；col_index_num為2時(shí)，返回table_array第二列中的數(shù)值，以此類(lèi)推。如果col_index_num小于1，函數(shù)VLOOKUP返回錯(cuò)誤值#VALUE!；如果col

26、_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，單元格B

27、1: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。3HLOOKUP函數(shù)HLOOKUP函數(shù)的功能是從表格或數(shù)值數(shù)組的首行查找指定的數(shù)值，并由此返回表格或數(shù)組當(dāng)前列中指定行處的數(shù)值。公式為= (lookup_value，table_array，row_index_num，range_lookup)式中  row_index_numtable_array中待返回的匹配值的行序號(hào)。row_ind

28、ex_num為1時(shí)，返回table_array第一行的數(shù)值，row_index_num為2時(shí)，返回table_array第二行的數(shù)值，以此類(lèi)推。如果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)行檢索

29、，先得到的是行號(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可能包含所要查

30、找的數(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

31、(12,23,43,12,55,0)=3，MATCH(40,23,43,12,55)=1。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，則必須有 r

32、ow_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和c

33、olumn_num必須指向array中的某一單元格，否則，函數(shù)INDEX返回錯(cuò)誤值#REF!。例如：INDEX(1,2;3,4,2,2) = 4。如果作為數(shù)組公式輸入，則：INDEX(1,2;3,4,0,2) = 2;42返回引用中指定單元格。公式為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ù)IND

34、EX(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，以此類(lèi)推。如果省略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_nu

35、m所指定的區(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)，建立文本類(lèi)型的單元格地址。公式為= ADDRESS(ro

36、w_num,column_num,abs_num,a1,sheet_text)式中  row_num在單元格引用中使用的行號(hào)；column_num在單元格引用中使用的列標(biāo)；abs_num指明返回的引用類(lèi)型，其中：當(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，則不使用任

37、何工作表名。例如，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一邏輯

38、值，指明包含在單元格ref_text中的引用的類(lèi)型，如果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、INDI

39、RECT等在財(cái)務(wù)分析與決策、預(yù)測(cè)及建立動(dòng)態(tài)圖表等中是非常有用的。2.1.2.12 矩陣函數(shù)TRANSPOSE函數(shù)、MINVERSE函數(shù)和MMULT函數(shù)1TRANSPOSE函數(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ù)相同。【例2-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ú)修改