Excel製表技巧（49）公式及函數B

06-02

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 獎金提成比例及提取額的計算

2.1.2.7 LOOKUP函數、VLOOKUP函數和HLOOKUP函數

1．LOOKUP函數

LOOKUP函數的功能是返迴向量（單行區域或單列區域）或數組中的數值。函數 LOOKUP 有兩種語法形式：向量和數組。函數 LOOKUP 的向量形式是在單行區域或單列區域（向量）中查找數值，然後返回第二個單行區域或單列區域中相同位置的數值；函數 LOOKUP 的數組形式在數組的第一行或第一列查找指定的數值，然後返回數組的最後一行或最後一列中相同位置的數值。

（1）向量形式：公式為

= LOOKUP(lookup_value,lookup_vector,result_vector)

式中　lookup_value—函數LOOKUP在第一個向量中所要查找的數值，它可以為數字、文本、邏輯值或包含數值的名稱或引用；

lookup_vector—只包含一行或一列的區域lookup_vector 的數值可以為文本、數字或邏輯值；

result_vector—為只包含一行或一列的區域其大小必須與 lookup_vector 相同。

（2）數組形式：公式為

= LOOKUP(lookup_value,array)

式中　array—包含文本、數字或邏輯值的單元格區域或數組它的值用於與 lookup_value 進行比較。

例如：LOOKUP(5.2,{4.2,5,7,9,10})=5。

注意：lookup_vector的數值必須按升序排列，否則函數LOOKUP不能返回正確的結果。文本不區分大小寫。如果函數LOOKUP找不到lookup_value，則查找lookup_vector中小於或等於lookup_value的最大數值。如果lookup_value小於lookup_vector中的最小值，函數LOOKUP返回錯誤值#N/A。

2．VLOOKUP函數

VLOOKUP函數的功能是在表格或數值數組的首列查找指定的數值，並由此返回表格或數組當前行中指定列處的數值。公式為

= VLOOKUP（lookup_value，table_array，col_index_num，range_lookup）

式中　lookup_value—需要在數據表第一列中查找的數值，lookup_value 可以為數值、引用或文字串；

table_array—需要在其中查找數據的數據表，可以使用對區域或區域名稱的引用，例如資料庫或數據清單；

如果range_lookup為TRUE，則table_array的第一列中的數值必須按升序排列，否則函數VLOOKUP不能返回正確的數值，如果range_lookup為FALSE，table_array不必進行排序。table_array的第一列中的數值可以為文本、數字或邏輯值，且不區分文本的大小寫；

col_index_num—table_array中待返回的匹配值的列序號；

col_index_num為1時，返回table_array第一列中的數值；col_index_num為2時，返回table_array第二列中的數值，以此類推。如果col_index_num小於1，函數VLOOKUP返回錯誤值#VALUE!；如果col_index_num大於table_array的列數，函數VLOOKUP返回錯誤值#REF!。

range_lookup—邏輯值，指明函數 VLOOKUP 返回時是精確匹配還是近似匹配。

如果其為TRUE或省略，則返回近似匹配值，也就是說，如果找不到精確匹配值，則返回小於lookup_value的最大數值；如果range_value為FALSE，函數VLOOKUP將返回精確匹配值。如果找不到，則返回錯誤值#N/A。

VLOOKUP函數在財務管理與分析中是一個經常用到的函數，因此熟悉它將會帶來很大便利。在以後的有關章節中會經常用到它。

例如，假設單元格A1:A4中的數據分別為1、30、80和90，單元格B1:B4中的數據分別為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函數

HLOOKUP函數的功能是從表格或數值數組的首行查找指定的數值，並由此返回表格或數組當前列中指定行處的數值。公式為

= (lookup_value，table_array，row_index_num，range_lookup)

式中 row_index_num—table_array中待返回的匹配值的行序號。

row_index_num為1時，返回table_array第一行的數值，row_index_num為2時，返回table_array第二行的數值，以此類推。如果row_index_num小於1，函數HLOOKUP返回錯誤值 #VALUE!；如果row_index_num大於table_array的行數，函數HLOOKUP返回錯誤值#REF!。

式中的其他參數含義參閱VLOOKUP函數。

HLOOKUP函數與VLOOKUP函數的區別是：當比較值位於數據表的首行，並且要查找下面給定行中的數據時，使用函數HLOOKUP；當比較值位於要進行數據查找的左邊一列時，使用函數VLOOKUP。VLOOKUP函數在首列進行檢索，先得到的是行號，然後根據col_index_num參數指定的列標返回指定的單元格數值；而HLOOKUP函數在首行進行檢索，先得到的是列標，然後根據row_index_num參數指定的行號返回指定的單元格數值。

2.1.2.8 MATCH函數

MATCH函數的功能是返回在指定方式下與指定數值匹配的數組中元素的相應位置。公式為：

= MATCH(lookup_value,lookup_array,match_type)

式中 lookup_value—需要在數據表中查找的數值，可以是數值（數字、文本或邏輯值）或對數字、文本或邏輯值的單元格引用；

lookup_array—可能包含所要查找的數值的連續單元格區域，可以是數組或數組引用；

match_type—數字-1、0或1，它指明Excel如何在lookup_array中查找lookup_value。

查找方式如下：當match_type為-1時，lookup_array必須按降序排列，函數MATCH查找大於或等於lookup_value的最小數值；當match_type為0時，lookup_array可以按任何順序排列，函數MATCH 查找等於lookup_value的第一個數值；當match_type為1時，lookup_array必須按升序排列，函數MATCH查找小於或等於lookup_value的最大數值。

例如，MATCH(12,{23,43,12,55},0)=3，MATCH(40,{23,43,12,55})=1。

顯示部分公式的運行結果

在輸入較長的公式時容易出錯，如何測試其中的部分公式呢？選中要測試公式的某一部分，按下F9鍵，Excel會將選定的部分替換成相應的結果，若想恢復為原來的公式只須按Esc鍵或「Ctrl+Z」即可。