EXCEL公式及函數的進階應用(1-3)（轉載）

以下原文轉載自Excel - 有道部落格搜尋【博文】

公式和函數是Excel最基本、最重要的應用工具，是Excel的核心，是以，應對公式和函數熟練掌握，才能在實際應用中得心應手。

2.1.1  數組公式及其應用

數組公式就是可以同時進行多重計算并傳回一種或多種結果的公式。在數組公式中使用兩組或多組資料稱為數組參數，數組參數可以是一個資料區域，也可以是數組常量。數組公式中的每個數組參數必須有相同數量的行和列。

2.1.1.1  數組公式的輸入、編輯及删除

1．數組公式的輸入

數組公式的輸入步驟如下：

（1）標明單元格或單元格區域。如果數組公式将傳回一個結果，單擊需要輸入數組公式的單元格；如果數組公式将傳回多個結果，則要標明需要輸入數組公式的單元格區域。

（2）輸入數組公式。

（3）同時按“Crtl+Shift+Enter”組合鍵，則Excel 自動在公式的兩邊加上大括号{ } 。

特别要注意的是，第（3）步相當重要，隻有輸入公式後同時按“Crtl+Shift+Enter”組合鍵，系統才會把公式視為一個數組公式。否則，如果隻按Enter鍵，則輸入的隻是一個簡單的公式，也隻在選中的單元格區域的第1個單元格顯示出一個計算結果。

在數組公式中，通常都使用單元格區域引用，但也可以直接鍵入數值數組，這樣鍵入的數值數組被稱為數組常量。當不想在工作表中按單元格逐個輸入數值時，可以使用這種方法。如果要生成數組常量，必須按如下操作：

（1）直接在公式中輸入數值，并用大括号“{ }”括起來。

（2）不同列的數值用逗号“,”分開。

（3）不同行的數值用分号“;”分開。

         輸入數組常量的方法：

例如，要在單元格A1:D1中分别輸入10，20，30和40這4個數值，則可采用下述的步驟：

（1）選取單元格區域A1:D1，如圖2-1所示。

圖2-1  選取單元格區域A1:D1

（2）在公式編輯欄中輸入數組公式“={10,20,30,40}”，如圖2-2所示。

圖2-2  在編輯欄中輸入數組公式

（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組合鍵，得到數組常量

（1）選取單元格區域A1:D2，如圖2-4所示。

圖2-4  選取單元格區域A1:D2

（2）在編輯欄中輸入公式“={10,20,30,40;50,60,70,80}”，如圖2-5所示。

圖2-5  在編輯欄中輸入數組公式

（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組合鍵，得到數組常量

         輸入公式數組的方法

例如，在單元格A3:D3中均有相同的計算公式，它們分别為單元格A1:D1與單元格A2:D2中資料的和，即單元格A3中的公式為“=A1+A2”，單元格B3中的公式為“=B1+B2”，…，則可以采用數組公式的方法輸入公式，方法如下：

（1）選取單元格區域A3:D3，如圖2-7所示。

（2）在公式編輯欄中輸入數組公式“=A1:D1+A2:D2”，如圖2-8所示。

圖2-7  選取單元格區域A3:D3

圖2-8  在編輯欄中輸入數組公式

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

圖2-9  同時按Ctrl+Shift+Enter組合鍵，得到數組公式

2．編輯數組公式

數組公式的特征之一就是不能單獨編輯、清除或移動數組公式所涉及的單元格區域中的某一個單元格。若在數組公式輸入完畢後發現錯誤需要修改，則需要按以下步驟進行：

（1）在數組區域中單擊任一單元格。

（2）單擊公式編輯欄，當編輯欄被激活時，大括号“{ }”在數組公式中消失。

（3）編輯數組公式内容。

（4）修改完畢後，按“Crtl+Shift+Enter”組合鍵。要特别注意不要忘記這一步。

3．删除數組公式

删除數組公式的步驟是：首先標明存放數組公式的所有單元格，然後按Delete鍵。

2.1.1.2  數組公式的應用

1．用數組公式計算兩個資料區域的乘積

【例2-1】如圖2-10所示，已經知道12個月的銷售量和産品單價，則可以利用數組公式計算每個月的銷售額，步驟如下：

圖2-10  用數組公式計算銷售額

（1）選取單元格區域B4:M4。

（2）輸入公式“=B2:M2*B3:M3”。

（3）按“Crtl+Shift+Enter”組合鍵。

如果需要計算12個月的月平均銷售額，可在單元格B5中輸入公式“=AVERAGE(B2:M2*B3:M3)”，然後按“Crtl+Shift+Enter”組合鍵即可，如圖2-10所示。

在數組公式中，也可以将某一常量與數組公式進行加、減、乘、除，也可以對數組公式進行乘幂、開方等運算。例如在圖2-10中，每月的單價相同，故我們也可以在單元格B4:M4中輸入公式“=B2:M2*28”，然後按“Crtl+Shift+Enter”組合鍵；在單元格B5中輸入公式“=AVERAGE(B2:M2*28)”，然後按“Crtl+Shift+Enter”組合鍵。

在使用數組公式計算時，最好将不同的單元格區域定義不同的名稱，如在圖2-10中，将單元格區域B2:M2定義名稱為“銷售量”，單元格區域B3:M3定義名稱為“單價”，則各月的銷售額計算公式為“=銷售量*單價”，月平均銷售額計算公式為“=AVERAGE(銷售量*單價)”，這樣不容易出錯。

2．用數組公式計算多個資料區域的和

如果需要把多個對應的行或列資料進行相加或相減的運算，并得出與之對應的一行或一列資料時，也可以使用數組公式來完成。

【例2-2】某企業2002年銷售的3種産品的有關資料如圖2-11所示，則可以利用數組公式計算該企業2002年的總銷售額，方法如下：

圖2-11  某企業的月銷售總額計算

（1）選取單元格區域C8:N8。

（2）輸入公式“=C2:N2*C3:N3+C4:N4*C5:N5+C6:N6*C7:N7”。

（3）按“Crtl+Shift+Enter”組合鍵。

3．用數組公式同時對多個資料區域進行相同的計算

【例2-3】某公司對現有三種商品實施降價銷售，産品原價如圖2-12所示，降價幅度為20%，則可以利用數組公式進行計算，步驟如下：

圖2-12  産品降價計算

（1）選取單元格區域G3:I8。

（2）輸入公式“=B3:D8*(1-20%)”。

（3）按Crtl+Shift+Enter組合鍵。

此外，當對結構相同的不同工作表資料進行合并彙總處理時，利用上述方法也将是非常友善的。有關不同工作表單元格的引用可參閱第1章的有關内容，關于資料的合并計算可參閱本章2.3.5節的内容。

2.1.2  常用函數及其應用

在第1章中介紹了一些有關函數的基本知識，本節對在财務管理中常用的一般函數應用進行說明，其他有關的專門财務函數将在以後的有關章節中分别予以介紹。

2.1.2.1  SUM函數、SUMIF函數和SUMPRODUCT函數

在财務管理中，應用最多的是求和函數。求和函數有三個：無條件求和SUM函數、條件求和SUMIF函數和多組資料相乘求和SUMPRODUCT函數。

1．無條件求和SUM函數

該函數是求30個以内參數的和。公式為

= SUM（參數1，參數2，…，參數N）

當對某一行或某一列的連續資料進行求和時，還可以使用工具欄中的自動求和按鈕。

例如，在例2-1中，求全年的銷售量，則可以單擊單元格N2，然後再單擊求和按鈕，按Enter鍵即可，如圖2-13所示。

圖2-13  自動求和

2．條件求和SUMIF函數

SUMIF函數的功能是根據指定條件對若幹單元格求和，公式為

=SUMIF(range,criteria,sum_range)

式中  range—用于條件判斷的單元格區域；

criteria—确定哪些單元格将被相加求和的條件，其形式可以為數字、表達式或文本；

sum_range—需要求和的實際單元格。

隻有當range中的相應單元格滿足條件時，才對 sum_range 中的單元格求和。如果省略 sum_range,則直接對 range 中的單元格求和。

利用這個函數進行分類彙總是很有用的。

【例2-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函數的對話框如圖2-15所示。

圖2-15  SUMIF函數對話框

當需要分類彙總的資料很大時，利用SUMIF函數是很友善的。

3．SUMPRODUCT函數

SUMPRODUCT函數的功能是在給定的幾組數組中，将數組間對應的元素相乘，并傳回乘積之和。公式為

= SUMPRODUCT(array1,array2,array3,…)

式中，array1,array2,array3,...為1至30個數組。

需注意的是，數組參數必須具有相同的維數，否則，函數 SUMPRODUCT 将傳回錯誤值 #VALUE!。對于非數值型的數組元素将作為0處理。

例如，在例2-2中，要計算2002年産品A的銷售總額，可在任一單元格（比如O2）中輸入公式“=SUMPRODUCT(C2:N2,C3:N3)”即可。

2.1.2.2  AVERAGE函數

AVERAGE函數的功能是計算給定參數的算術平均值。公式為

= AVERAGE（參數1，參數2，…，參數N）

函數中的參數可以是數字，或者是涉及數字的名稱、數組或引用。如果數組或單元格引用參數中有文字、邏輯值或空單元格，則忽略其值。但是，如果單元格包含零值則計算在内。

AVERAGE函數的使用方法與SUM函數相同，此處不再介紹。

2.1.2.3  MIN函數和MAX函數

MIN函數的功能是給定參數表中的最小值，MAX函數的功能是給定參數表中的最大值。公式為

= MIN（參數1，參數2，…，參數N）

= MAX（參數1，參數2，…，參數N）

函數中的參數可以是數字、空白單元格、邏輯值或表示數值的文字串。

例如，MIN（3，5，12，32）=3；MAX（3，5，12，32）=32。

2.1.2.4  COUNT函數和COUNTIF函數

COUNT函數的功能是計算給定區域内數值型參數的數目。公式為

= COUNT（參數1，參數2，…，參數N）

COUNTIF函數的功能是計算給定區域内滿足特定條件的單元格的數目。公式為

= COUNTIF（range，criteria）

式中 range—需要計算其中滿足條件的單元格數目的單元格區域；

criteria—确定哪些單元格将被計算在内的條件，其形式可以為數字、表達式或文本。

COUNT函數和COUNTIF函數在資料彙總統計分析中是非常有用的函數。

2.1.2.5  IF函數

IF函數也稱條件函數，它根據參數條件的真假，傳回不同的結果。在實踐中，經常使用函數IF對數值和公式進行條件檢測。公式為

= IF(logical_test,value_if_true,value_if_false)

式中  logical_test—條件表達式，其結果要麼為 TRUE，要麼為 FALSE，它可使用任何比較運算符；

value_if_true—logical_test 為 TRUE 時傳回的值；

value_if_false—logical_test 為 FALSE 時傳回的值。

IF函數在财務管理中具有非常廣泛的應用。

【例2-5】例如，某企業對各個銷售部門的銷售業績進行評價，評價标準及各個銷售部門在2002年的銷售業績彙總如圖2-16所示，評價計算步驟如下：

圖2-16  銷售部門業績評價

（1）標明單元格區域C3:C12。

（2）直接輸入以下公式：“=IF(B3:B12<100000,"差",IF(B3:B12<200000,"一般",IF(B3:B12<300000,"好",IF(B3:B12<400000,"較好","很好"))))”。

（3）按“Crtl+Shift+Enter”組合鍵。

則各個銷售部門的銷售業績評價結果就顯示在單元格域C3:C12中。

也可以直接在單元格C3中輸入公式“=IF(B3<100000,"差",IF(B3<200000,"一般",IF(B3<300000,"好",IF(B3<400000,"較好","很好"))))”後，将其向下填充複制到C4～C12單元格中。

2.1.2.6  AND函數、OR函數和NOT函數

這3個函數的用法如下：

= AND（條件1，條件2，…，條件N）

= OR（條件1，條件2，…，條件N）

= NOT（條件）

AND函數表示邏輯與，當所有條件都滿足時（即所有參數的邏輯值都為真時），AND函數傳回TRUE，否則，隻要有一個條件不滿足即傳回FALSE。

OR函數表示邏輯或，隻要有一個條件滿足時，OR函數傳回TRUE，隻有當所有條件都不滿足時才傳回FALSE。

NOT函數隻有一個邏輯參數，它可以計算出TRUE或FALSE的邏輯值或邏輯表達式。如果邏輯值為 FALSE，函數 NOT 傳回 TRUE；如果邏輯值為 TRUE，函數 NOT 傳回FALSE。

這3個函數一般與IF函數結合使用。

【例2-6】某企業根據各銷售部門的銷售額及銷售費用确定獎金提成比例及提取額，若銷售額大于300000元且銷售費用占銷售額的比例不超過1%，則獎金提取比例為15%，否則為10%，則計算過程如下（如圖2-17所示）：

（1）在單元格D3中輸入公式“=IF(AND(B3>300000,C3/B3<1%),15%,10%)”，将其向下填充複制到D4～C10單元格中。

（2）選取單元格區域E3:E10，輸入公式“=B3:B10*D3:D10”，按“Crtl+Shift+Enter”組合鍵。

則各銷售部門的銷售獎金提成比例及獎金提取額如圖2-17所示。

圖2-17  獎金提成比例及提取額的計算