excel常用函數：有趣的函數

excel常用函數：有趣的函數

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

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

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

區分大小寫的函數

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

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

下表列出了一些能夠獲取數據位置或者個數值的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函數及相關的說明：

上表中，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函數那樣要順序查找，因此涉及大量單元格時更快速。

邏輯函數

下表列出了一些Excel邏輯函數及相應的說明：

可以使用數組來簡化我們的邏輯測試。例如，要測試單元格A1中的數據是否為1、3或者5，可以使用公式：

=OR(A1=1,A1=3,A1=5)

但更簡單的公式是：

=OR(A1={1,3,5})

其他函數

下表列出了其他一些Excel函數及相關說明：

MOD函數返回兩數相除的余數，而余數將在0至除數之間循環，因此，在公式中我們可以充分利用這個特點。例如，要使輸入的數字永遠在0至6之間循環，可以使用公式：

=MOD(Number,7)

其中，Number代表輸入的數字。

又如，我們要設置每隔1行，給單元格設置背景色，那么可以在條件格式中使用公式：

=MOD(ROW(A1),2)=0

效果如下圖所示。

結語

Excel為我們提供了豐富的函數，用來解決各方面的問題。你使用Excel函數越多，就會越熟練。同時，在應用Excel函數的過程中，你不僅會發現很多有趣的函數，而且會體會到他們在解決問題時的美妙和展現的魅力。

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。