有趣的函数 / 开普饭

函数是公式的“细胞”，构造强大而优雅的公式少不了函数的配合。

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函数那样要顺序查找，因此涉及大量单元格时更快速。

上表中有关函数的详细讲解分别参见完美Excel微信公众号（公众号名：excelperfect）推送的文章《Excel函数学习2：INDEX函数》、《Excel函数学习4：VLOOKUP函数》、《Excel函数学习6：HLOOKUP函数》、《Excel函数学习7：LOOKUP函数》、《Excel函数学习12：OFFSET函数》。

逻辑函数

下表列出了一些Excel逻辑函数及相应的说明：

可以使用数组来简化我们的逻辑测试。例如，要测试单元格A1中的数据是否为1、3或者5，可以使用公式：

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

但更简单的公式是：

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

上表中有关N函数的详细讲解参见完美Excel微信公众号（公众号名：excelperfect）推送的文章《Excel函数学习18：N函数》。

其他函数

下表列出了其他一些Excel函数及相关说明：

MOD函数返回两数相除的余数，而余数将在0至除数之间循环，因此，在公式中我们可以充分利用这个特点。例如，要使输入的数字永远在0至6之间循环，可以使用公式：

=MOD(Number,7)

其中，Number代表输入的数字。

又如，我们要设置每隔1行，给单元格设置背景色，那么可以在条件格式中使用公式：

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

效果如下图所示。

结语

Excel为我们提供了丰富的函数，用来解决各方面的问题。你使用Excel函数越多，就会越熟练。同时，在应用Excel函数的过程中，你不仅会发现很多有趣的函数，而且会体会到他们在解决问题时的美妙和展现的魅力。

有趣的函数

相关推荐