Excel公式和函數(shù)的高級應用

公式和函數(shù)是Excel最基本、最重要的應用工具，是Excel的核心，因此，應對公式和函數(shù)熟練掌握，才能在實際應用中得心應手。2.1.1數(shù)組公式及其應用數(shù)組公式就是可以同時進行多重計算并返回一種或多種結(jié)果的公式。在數(shù)組公式中使用兩組或多組數(shù)據(jù)稱為數(shù)組參數(shù)，數(shù)組參數(shù)可以是一個數(shù)據(jù)區(qū)域，也可以是數(shù)組常量。數(shù)組公式中的每個數(shù)組參數(shù)必須有相同數(shù)量的行和列。2.1.1.1

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

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

選取單元格區(qū)域A1:D1（2）在公式編輯欄中輸入數(shù)組公式“={10,20,30,40}”，如圖2-2所示。圖2-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，則可以采用下述的方法：圖2-3

同時按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

同時按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所示。圖2-7

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

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

同時按Ctrl+Shift+Enter組合鍵，得到數(shù)組公式2．編輯數(shù)組公式數(shù)組公式的特征之一就是不能單獨編輯、清除或移動數(shù)組公式所涉及的單元格區(qū)域中的某一個單元格。若在數(shù)組公式輸入完畢后發(fā)現(xiàn)錯誤需要修改，則需要按以下步驟進行：（1）在數(shù)組區(qū)域中單擊任一單元格。（2）單擊公式編輯欄，當編輯欄被激活時，大括號“{}”在數(shù)組公式中消失。（3）編輯數(shù)組公式內(nèi)容。（4）修改完畢后，按“Crtl+Shift+Enter”組合鍵。要特別注意不要忘記這一步。3．刪除數(shù)組公式刪除數(shù)組公式的步驟是：首先選定存放數(shù)組公式的所有單元格，然后按Delete鍵。2.1.1.2數(shù)組公式的應用1．用數(shù)組公式計算兩個數(shù)據(jù)區(qū)域的乘積【例2-1】如圖2-10所示，已經(jīng)知道12個月的銷售量和產(chǎn)品單價，則可以利用數(shù)組公式計算每個月的銷售額，步驟如下：圖2-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(銷售量*單價)”，這樣不容易出錯。2．用數(shù)組公式計算多個數(shù)據(jù)區(qū)域的和如果需要把多個對應的行或列數(shù)據(jù)進行相加或相減的運算，并得出與之對應的一行或一列數(shù)據(jù)時，也可以使用數(shù)組公式來完成?！纠?-2】某企業(yè)20XX年銷售的3種產(chǎn)品的有關(guān)資料如圖2-11所示，則可以利用數(shù)組公式計算該企業(yè)20XX年的總銷售額，方法如下：圖2-11

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

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

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

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

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

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

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

SUMIF函數(shù)對話框當需要分類匯總的數(shù)據(jù)很大時，利用SUMIF函數(shù)是很方便的。3．SUMPRODUCT函數(shù)SUMPRODUCT函數(shù)的功能是在給定的幾組數(shù)組中，將數(shù)組間對應的元素相乘，并返回乘積之和。公式為=SUMPRODUCT(array1,array2,array3,…)式中，array1,array2,array3,...為1至30個數(shù)組。需注意的是，數(shù)組參數(shù)必須具有相同的維數(shù)，否則，函數(shù)SUMPRODUCT將返回錯誤值#VALUE!。對于非數(shù)值型的數(shù)組元素將作為0處理。例如，在例2-2中，要計算20XX年產(chǎn)品A的銷售總額，可在任一單元格（比如O2）中輸入公式“=SUMPRODUCT(C2:N2,C3:N3)”即可。2.1.2.2AVERAGE函數(shù)AVERAGE函數(shù)的功能是計算給定參數(shù)的算術(shù)平均值。公式為=AVERAGE（參數(shù)1，參數(shù)2，…，參數(shù)N）函數(shù)中的參數(shù)可以是數(shù)字，或者是涉及數(shù)字的名稱、數(shù)組或引用。如果數(shù)組或單元格引用參數(shù)中有文字、邏輯值或空單元格，則忽略其值。但是，如果單元格包含零值則計算在內(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ù)的功能是計算給定區(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ù)。2.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ù)在財務管理中具有非常廣泛的應用。【例2-5】例如，某企業(yè)對各個銷售部門的銷售業(yè)績進行評價，評價標準及各個銷售部門在20XX年的銷售業(yè)績匯總?cè)鐖D2-16所示，評價計算步驟如下：圖2-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,"較好","很好"))))”后，將其向下填充復制到C4～C12單元格中。2.1.2.6

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

獎金提成比例及提取額的計算2.1.2.7LOOKUP函數(shù)、VLOOKUP函數(shù)和HLOOKUP函數(shù)1．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。2．VLOOKUP函數(shù)VLOOKUP函數(shù)的功能是在表格或數(shù)值數(shù)組的首列查找指定的數(shù)值，并由此返回表格或數(shù)組當前行中指定列處的數(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ù)在財務管理與分析中是一個經(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。3．HLOOKUP函數(shù)HLOOKUP函數(shù)的功能是從表格或數(shù)值數(shù)組的首行查找指定的數(shù)值，并由此返回表格或數(shù)組當前列中指定行處的數(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ù)值