Excel公式與函數之美03：有趣的函數

06-02

函數是公式的「細胞」，構造強大而優雅的公式少不了函數的配合。

Excel提供了300多個內置函數，有些函數很活躍，他們看似平常，但在公式中能夠經常看到他們的「身影」。正是這些函數，在公式中組合後卻能發揮出巨大的威力。

下面主要介紹一些在公式中經常會用到的函數，探討一些細節和技巧。在後面的系列文章中，我會對很多有特點的函數進行專門的詳解。

區分大小寫的函數

下表列出了區分大小寫的Excel函數或運算的對比：

上表中有關函數的詳細講解分別參見完美Excel微信公眾號（公眾號名：excelperfect）推送的文章《Excel函數學習15：EXACT函數》、《Excel函數學習11：SUBSTITUTE函數》、《Excel函數學習9：FIND函數》、《Excel函數學習8：SEARCH函數》。

獲取數據位置或者個數值的函數

下表列出了一些能夠獲取數據位置或者個數值的Excel函數及相關說明：

上表中，ROW函數返回單元格所在行的行號，如果參數為單元格區域，則返回左上角單元格所在行的行號。COLUMN函數返回單元格所在列的列號，如果參數為單元格區域，則返回左上角單元格所在列的列號。例如：

=ROW()

返回當前單元格所在行的行號。

=ROW(B5)

返回數值5，表示第5行。

=ROW(E9:H18)

返回數值9，即單元格區域E9:H18左上角單元格E9所在行的行號。

COLUMN函數類似。當傳遞單元格區域作為ROW函數和COLUMN函數的參數時，可以返回數組值。在後面我們會看到，利用這個特點，可以編寫出靈活強大的公式。

要想知道一個區域有多少行多少列，則應使用ROWS函數和COLUMNS函數。例如：

=ROWS(C3:E6)

返回數值4，表示單元格區域C3:E6有4行。

=COLUMNS(C3:E6)

返回數值3，表示單元格區域C3:E6有3列。

MATCH函數的語法為：

MATCH(要查找的值,查找區域或數組[,匹配類型])

其中，匹配類型為可選參數，如果指定其值為0，那麼MATCH函數將在查找區域或數組中查找和要查找的值完全相等的第一個值。如果沒有指定該參數值，或者指定其值為1，那麼MATCH函數將在查找區域或數組中查找小於或等於要查找的值的最大值，但是查找區域或數組需要按升序排列。如果指定其值為-1，那麼MATCH函數將在查找區域或數組中查找大於或等於要查找的值的最小值，但是查找區域或數組需要按降序排列。

如果查找區域或數組需要按升序排列，那麼各類數據的排列順序是：數字、文本字元、FALSE或TRUE，例如5,」excelperfect」,TRUE。降序排列與之相反。

如果指定匹配類型的值為0，並且要查找的值是文本，那麼可以在要查找的值中使用通配符。問號（？）可以匹配任何一個字元，星號（*）可以匹配任意多個字元。

上表中有關函數的詳細講解分別參見完美Excel微信公眾號（公眾號名：excelperfect）推送的文章《Excel函數學習1：MATCH函數》、《Excel函數學習22：COLUMNS函數》、《Excel函數學習13：ADDRESS函數》。

獲取數據值的函數

下表列出了一些可以獲取數據值的Excel函數及相關的說明：

上表中，VLOOKUP函數按照最後一個參數指定的模式，在數據區域的第1列向下查找值，從指定列中找到相應的數據。下圖所示的工作表列舉了VLOOKUP函數使用的不同情形：

如果指定VLOOKUP函數的最後一個參數為TRUE（默認值），那麼查找區域的第1列應該按照升序排列。此時，該函數會查找並返回最後一個匹配項。如果要查找並返回第一個匹配項，那麼將該函數的最後一個參數設置為FALSE。例如，示例工作表中查找數據10時的情形。

如果在查找的數據區域中沒有想要查找的值，如示例中的數據15、25、35，那麼VLOOKUP函數會根據指定的最後一個參數來查找值。當指定最後一個參數為TRUE時，獲取小於且與所查找值最接近的值，而當指定最後一個參數為FALSE時，會返回#N/A錯誤。

在指定VLOOKUP函數的最後一個參數為FALSE時，我們可以不需要查找區域按升序排列，並且在有多行值與查找的值相同時，可以獲得第1行所對應的值，但在沒有找到值時會返回錯誤。

HLOOKUP函數的用法與VLOOKUP函數相同，只是在數據區域的第1行向右查找。

VLOOKUP函數和HLOOKUP函數只能使用最左側列或者最頂部的行進行查找，獲取左側列或者下方行中的值，這是它們的一個主要缺點。INDEX函數和MATCH函數有效彌補了其不足。

INDEX函數經常與MATCH函數配合使用，由MATCH函數找到相應的行列號，作為INDEX函數的參數，獲取相應的值。INDEX函數不僅可以使用數組作為參數，而且還可以返回數組值，在後面我們將會看到相關應用示例。

INDIRECT函數可以使用文本來構造單元格引用。例如：

=INDIRECT("B" &2)

或者

=INDIRECT(「B2」)

等價於

=B2

那為什麼不直接就輸入=B2呢？在某些情形下，可能參數就是文本，而一些函數不會接受文本作為參數，例如ROW函數。當使用

=ROW(」1:2」)

時，Excel不會接受。如果使用：

=ROW(INDIRECT("1:2"))

可以滿足要求。使用數組輸入，返回｛1;2｝。這正可以彌補ROW函數的不足。

又如，ROW函數不接受其它函數作為參數，如果輸入：

ROW(1:LEN(A1))

Excel會返回錯誤。但可以這樣輸入：

ROW(INDIRECT(「1:」 &LEN(A1))

如果單元格A1中字元串的長度為3，那麼上述公式變為：

ROW(INDIRECT(「1:3」))

進一步計算為：

{1;2;3}

註：如果使用R1C1樣式的引用，那麼需要指定參數FALSE，例如

=INDIRECT("R2C2",FALSE)

表示引用單元格B2。

OFFSET函數都可以引用其他的單元格，返回相關的單元格或單元格區域。不像VLOOKUP函數那樣要順序查找，因此涉及大量單元格時更快速。