excel常用函数:有趣的函数

函数是公式的“细胞”，函数的配合是构建强大而优雅的公式不可或缺的。

Excel提供了300多个内置函数，其中一些函数非常活跃。它们看起来很普通，但经常可以在公式中看到。正是这些函数在公式中组合后才能发挥巨大的威力。

下面主要介绍一些公式中经常用到的函数，并讨论一些细节和技巧。在接下来的系列文章中，我将详细解释很多特性函数。

区分大小写的函数

下表列出了Excel函数或操作的区分大小写的比较：

获取数据位置或编号的函数。

下表列出了一些可以获取数据位置或编号的Excel函数及相关说明：

在上表中，ROW函数返回单元格所在行的行号。如果参数为单元格区域，则返回左上角单元格所在行的行号。COLUMN函数返回单元格所在列的列号。如果参数是单元格区域，它将返回左上角单元格所在列的列号。例如：

=ROW()

返回当前单元格的行号。

=ROW(B5)

返回值5，表示第五行。

=ROW(E9:H18)

返回值9，即单元格区域e 9:H18左上角单元格E9的行号。

COLUMN函数类似。将单元格区域作为参数传递给ROW函数和COLUMN函数时，可以返回一个数组值。正如我们将在后面看到的，使用这个特性，我们可以编写灵活而强大的公式。

要知道一个区域有多少行和列，应该使用行函数和列函数。例如：

=ROWS(C3:E6)

返回值4，表示单元格区域C3:E6有4行。

=COLUMNS(c : E6)

返回值3，表示单元格区域C3:E6有3列。

MATCH函数的语法是：

match(要查找的值、搜索区域或数组[，MATCH类型])。

其中，MATCHing类型是可选参数，如果将其值指定为0，match函数将在搜索区域或数组中找到与要搜索的值完全相等的第一个值。如果未指定参数值，或者指定为1，MATCH函数将找到小于或等于要在搜索区域或数组中找到的值的最大值，但搜索区域或数组需要按升序排序。如果指定为-1，MATCH函数将找到大于或等于要在搜索区域或数组中找到的值的最小值，但搜索区域或数组需要按降序排序。

如果搜索区域或数组需要按升序排列，那么各类数据的排列顺序为：数字、文字字符、FALSE或true，例如5、“Excel Perfect”，TRUE。降序则相反。

如果指定匹配类型的值为0，并且要查找的值为文本，则可以在要查找的值中使用通配符。问号(？)可以匹配任意一个字符，星号(*)可以匹配任意数量的字符。

获取数据值的函数。

下表列出了一些可以获取数据值的Excel函数及相关说明：

在上表中，VLOOKUP函数根据最后一个参数指定的模式向下查找数据区第一列的值，从指定的列中找到对应的数据。下图所示的工作表列出了使用VLOOKUP函数的不同情况：

如果指定的VLOOKUP函数的最后一个参数为TRUE(默认值)，则搜索区域的第一列应该按升序排序。此时，函数将找到并返回最后一个匹配。如果要查找并返回第一个匹配项，请将函数的最后一个参数设置为FALSE。例如，在示例工作表中查找数据10时。

如果在搜索的数据区域中没有要搜索的值，例如示例中的数据15、25和35，则VLOOKUP函数将根据指定的最后一个参数搜索该值。当最后一个参数被指定为“真”时，将获得小于和最接近搜索值的值，而当最后一个参数被指定为“假”时，将返回#N/A错误。

当VLOOKUP函数的最后一个参数指定为FALSE时，我们可以按升序排序，而不搜索区域，当有多行值与搜索到的值相同时，我们可以得到第一行对应的值，但是如果没有找到值，就会返回错误。

HLOOKUP函数的使用方式与VLOOKUP函数相同，只是它看起来就在数据区的第一行。

VLOOKUP函数和HLOOKUP函数只能用最左边一列或者最上面一行进行搜索，得到左边一列或者下面一行的值，这是它们的一个主要缺点。INDEX函数和MATCH函数有效地弥补了它们的不足。

INDEX函数通常与MATCH函数一起使用。MATCH函数查找相应的行号和列号，这些行号和列号用作INDEX函数的参数，以获得相应的值。INDEX函数不仅可以使用数组作为参数，还可以返回数组值，后面我们会看到相关的应用示例。

间接函数可以使用文本来构造单元格引用。例如：

=间接(“B”2)

或者

=间接(“B2”)

等于

=B2

为什么不直接进入=B2？在某些情况下，可能的参数是文本，而有些函数不接受文本作为参数，例如ROW函数。使用时。

=ROW("1:2 ")

Excel不会接受的。如果使用：

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

能满足要求。使用数组输入，返回{ 1；2}。这可以弥补ROW函数的不足。

在另一个例子中，ROW函数不接受其他函数作为参数。如果您输入：

第(1:)行(A1)

Excel返回一个错误。但是你可以这样输入：

行(间接(“1:”透镜(A1))

如果单元格A1中的字符串长度为3，则上述公式变为：

行(间接(“1:3”))

进一步计算如下：

{1;2;3}

注意：如果使用R1C1样式引用，则需要指定参数FALSE，例如。

=间接(“R2C2”，假)

代表参考单元格B2。

OFFSET函数可以引用其他单元格，并返回相关单元格或单元格区域。与VLOOKUP功能不同，当涉及大量细胞时，它更快。

逻辑函数

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

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

=或(A1=1，A1=3，A1=5)

但更简单的公式是：

=或(A1={1，3，5})

其他功能

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

MOD函数返回被两个数除的余数，余数会从0循环到除数，所以我们可以在公式中充分利用这个特性。例如，要使输入数字始终在0和6之间循环，可以使用以下公式：

=MOD(数字，7)

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

再举一个例子，如果我们想设置每1行单元格的背景色，那么我们可以使用条件格式的公式：

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

效果如下图所示。

标签

Excel为我们解决各种问题提供了丰富的功能。你使用的Excel函数越多，你就越熟练。同时，在应用Excel函数的过程中，你不仅会发现许多有趣的函数，还会体会到它们在解决问题时的美和魅力。