10 个最重要的 excel 函数

1.中频函数

如果您指定的条件为 TRUE，则 IF 函数返回一个值，否则返回某个其他值。简单地说，IF 函数可以首先测试一个条件，并根据该条件的结果返回一个值。

句法

IF(逻辑测试, value_if_true, value_if_false)

论点

Logical_test：您要评估的条件。
value_if_true：如果此条件为 TRUE，您想要获取的值。
value_if_false：如果此条件为 FALSE，您想要获取的值。

您可以执行的嵌套条件的最大数量为 64。
您可以使用比较运算符来评估条件。

例子

在下面的示例中，我们使用比较运算符来评估不同的条件。

我们使用特定的文本来获取条件是否满足的结果。
您还可以使用 TRUE 和 FALSE 来获取结果。
如果您跳过指定值来获取结果（如果条件为 TRUE），则它将返回零。
如果条件为 FALSE，则跳过指定值来获取结果，它将返回零。

在下面的示例中，我们使用 IF 函数创建嵌套公式。

我们指定了一个条件，如果该条件为假，我们使用另一个 IF 来评估另一个条件并执行任务，如果该条件为 FALSE，我们使用另一个 IF。

就这样，我们使用了五次 IF 来创建一个嵌套公式。您可以在嵌套公式中使用相同的内容 64 次。

2.IFERROR函数

如果发生错误，IFERROR 函数将返回特定值。简而言之，它可以测试该值，如果该值是错误的，则它返回您指定的值。

句法

IFERROR(值, value_if_error)

论点

value：您要测试错误的值。
value_if_error：发生错误时想要返回的值。

IFERROR 函数关注错误的发生，而不是错误的类型。
如果忽略 value 或 value_if_error，结果将返回 0。
它可以测试#N/A、#REF!、#DIV/0!、#VALUE!、#NUM!、#NAME?和#NULL！。
如果您评估一个数组，它将返回每个指定元素的结果数组。

例子

在下面的示例中，我们使用 IFERROR 函数来替换 #DIV/0!带有有意义的文字。

IFERROR 仅与 2007 及更早版本兼容。要解决此问题，您可以使用 ISERROR。

3.TRUNC函数

TRUNC 函数在截断原始数字后返回一个整数。简单来说，它从具有特定精度的数字中删除小数位，然后返回结果的整数部分。

句法

TRUNC(数字, [数字位数])

论点

number：要截断的数字。
[num_digits]：指定截断数字精度的数字。

如果您忽略指定多个，它将返回错误。
它从零开始舍入。
如果您有两个距离相同的倍数，它将返回您要舍入的数字的较高倍数。

例子

在下面的示例中，我们使用 TRUNC 截断数据以从日期中删除时间。

4.SUMIF函数

SUMIF 函数返回满足指定条件的数字之和。简单来说，它只考虑并计算满足条件的值的总和。

句法

SUMIF(范围, 标准, [sum_range])

论点

范围：要检查条件的单元格范围。
criteria：条件可以是数字、文本、表达式、单元格引用或函数。
[sum_range]：包含要求和的值的单元格范围。

如果省略 sum_range，则对该范围内的单元格进行求和。
请务必使用双引号来指定文本条件或包含数学符号，这些符号应括在双引号中。
标准范围和总和范围的大小必须相同。

例子

在下面的示例中，我们指定 A1:A9 作为条件范围，B1:B9 作为总和范围，之后我们在 A12 中指定值为 C 的条件。

您还可以将条件直接插入到函数中。在下面的示例中，我们使用星号通配符来指定包含字母“S”的条件。

而且，如果您忽略总和范围，它将为您提供标准范围的总和。但这只有在标准范围具有数值时才有可能。

5.索引功能

INDEX 函数根据索引号从值列表中返回一个值。简单来说，INDEX 从值列表中返回一个值，并且您需要指定该值的位置。

句法

INDEX 有两种不同的语法。在第一个中，您可以使用索引的数组形式来简单地使用其位置从列表中获取值。

INDEX(数组, 行数, [列数])

第二种，您可以使用现实生活中较少使用的赞助形式，但如果您有多个范围需要推广，则可以使用它。

INDEX(参考、行号、[列号]、[区域号])

论点

数组：单元格范围或数组常量。
参考：一个单元格范围或多个范围。
row_number：要从中获取值的行号。
[col_number]：要从中获取值的列的编号。
[area_number]：如果要引用多个单元格范围（使用引用语法），请指定一个数字来引用所有单元格中的一个范围。

当指定 row_num 和 column_num 参数时，它将返回两者交集处的单元格中的值。
如果将 row_num 或 column_num 指定为 0（零），它将分别返回整个列或行的值数组。
当row_num和column_num超出范围时，会返回#REF！错误。
如果area_number大于您指定的数字范围，它将返回#REF!。

示例 1 – 使用 ARRAY 从列表中获取值

在下面的示例中，我们使用 INDEX 函数来获取 6 月份的数量。在列表中，Jun 位于第 6 位（第 6 行），这就是我在 row_number 中指定 6 的原因。 INDEX 在结果中返回值 1904。

如果您引用的范围包含多列，则必须指定列号。

示例 2 – 使用 REFERENCE 获取多个列表的值

在下面的示例中，我没有一次选择整个范围，而是在三个不同的范围中选择它。在最后一个参数中，我们在area_number中指定了2，它将定义这三个不同范围中要使用的范围。

现在，在第二行中，我们指的是第 5 行和第 1 列。 INDEX 返回值 172，该值位于第 2 行的第 5 行。

6.VLOOKUP函数

VLOOKUP 函数在表的第一列中查找值，并使用索引号返回相应值同一行中的值。简单来说，它执行垂直搜索。

句法

VLOOKUP（查找值，表数组，列索引编号，范围查找）

论点

lookup_value：您要在列中查找的值。您可以引用包含搜索值的单元格或直接将该值输入到函数中。
table_array：单元格范围，您要从中查找值的命名范围。
col_index_num：数字表示要从中检索值的列号。
range_lookup：使用 false 或 0 进行精确匹配，使用 true 或 1 进行正确匹配。默认值为 True。

如果 VLOOKUP 没有找到您要查找的值，它将返回 #N/A。
VLOOKUP 只能给出查找值右侧的值。如果您想从右侧查看，可以使用 INDEX 和 MATCH。
如果使用精确匹配，它将仅匹配列中第一个值。
您还可以在 VLOOKUP 中使用通配符。
如果您想要正确匹配，则可以使用 TRUE 或 1；如果想要完全匹配，则可以使用 FALSE 或 0。
如果使用正确匹配（True）：如果没有完全匹配，它将返回列表中的下一个最小值。
如果您要查找的值小于列表中的最小值，VLOOKUP 将返回#N/A。
如果您正在寻找确切的值，它将为您提供该确切的值。
确保您已按升序对列表进行排序。

例子

1.使用VLOOKUP查找类别

在下面的示例中，我们有一个学生的成绩列表，并且在备注栏中我们希望根据他们的成绩获得成绩。

在上面的品牌列表中，我们想根据下面的类别范围添加备注。

在此我们有两个选项可供使用。

第一个选项是使用 IF 创建嵌套公式，这需要一些时间，第二个选项是使用 VLOOKUP 创建一个具有合适匹配的公式。公式为：

=VLOOKUP(B2,$E$2:$G$5,3,TRUE)

怎么运行的

我使用“MIN MARKS”列来匹配查找值并从“Remarks”列中获取值。

我已经提到过，当您使用 TRUE 并且没有精确匹配搜索值时，它将返回搜索值中的下一个最小值。例如，当我们在类别表中搜索值77时，65是77之后的最小值。

这就是我们在评论中评分“良好”的原因。

2.VLOOKUP函数中的错误处理

使用 VLOOKUP 时出现的最常见问题之一是，只要未找到匹配项，就会得到 #N/A。但解决这个问题的方法很简单。让我用一个简单的例子来告诉你。

在下面的示例中，我们有一个姓名及其年龄的列表，在单元格 E6 中，我们使用 VLOOKUP 函数在列表中搜索姓名。每次我输入一个不在列表中的名字时，我都会得到#N/A。

但我在这里想要的是显示有意义的消息而不是错误。公式为： =IFNA(VLOOKUP(D6,Sheet3!$A$1:$B$14,2,0),”Not Found”)

工作原理：IFNA 可以测试 #N/A 的值，如果出现错误，您可以指定一个值来代替错误。

7. IFNA功能

如果发生 #N/A 错误，IFNA 函数将返回特定值。与 IFERROR 不同，它仅评估 #N/A 错误并返回您指定的值。

句法

IFNA（值，value_if_na）

论点

value：您要测试 #N/A 错误的值。
value_if_na：发生错误时要返回的值。

如果不指定任何参数，IFNA 会将其视为空字符串 (“”)。
如果值是数组，它将以数组形式返回结果。
它将忽略所有其他错误 #REF!、#DIV/0!、#VALUE!、#NUM!、#NAME?和#NULL！。

例子

在 VLOOKUP 函数中，当查找值不在查找范围内时，会出现#N/A，为此我们使用 IFNA 指定了一条有意义的消息。

注意： IFNA 是在 Excel 2013 中引入的，因此在以前的版本中不可用。

8.兰德函数

RAND 函数返回 0 到 1 之间的随机数。简单来说，您可以生成 0 到 1 之间的随机数（每次更改工作表时它都会更新其值）。

句法

兰德()

论点

RAND 函数中没有要指定的参数

如果在倍数中输入零，结果将返回零。
如果您忽略指定多个，它将返回错误。
它从零开始舍入。
如果您有两个距离相同的倍数，它将返回您要舍入的数字的较高倍数。

例子

除了 0 到 1 之间的数字之外，您还可以使用 RAND 来获取两个特定数字之间的随机数。在下面的示例中，我使用它创建了一个生成 50 到 100 之间的随机数的公式。

当您将此公式输入单元格时，它会通过将 RAND 返回的值与我们使用的方程相乘来返回 100 到 50 之间的数字。为了理解这个公式，我们需要将其分为三个部分：

首先，当它检测到较高数字中的较低数字时，您会得到两者之间的差异。
然后将该差值乘以减法后返回的随机数。
第三，将该数字与等式第三部分中剩余的最小数字相加。

相关：如何在Excel中快速生成随机字母

9.求和函数

SUM 函数返回所提供值的总和。简单来说，使用 SUM 函数您可以计算一系列值的总和（您可以直接在函数中输入一个值或引用一个单元格区域。

句法

SUM(数字 1,[数字 2],…)

论点

number1 ：数字、包含数字的单元格范围或包含数字的单个单元格。
[number2] ：数字、包含数字的单元格范围或包含数字的单个单元格。

它忽略文本值。

例子

在下面的示例中，您可以使用逗号将数字直接插入到函数中。

您也可以只引用一个范围来计算数字的总和，如果有任何文本、逻辑值或空单元格，它将忽略它们。

如果您引用的单元格中存在错误值，则会在结果中返回#N/A。

如果您有格式化为文本的数值，它将忽略它们。建议在使用 SUM 之前将它们转换为数字。

10. 或函数

OR 函数在测试您指定的条件后返回布尔值（TRUE 或 FALSE）。简单来说，您可以使用 AND 函数测试多个条件，如果其中任何一个（或全部）条件为 TRUE，则返回 TRUE；仅当所有这些条件均为 FALSE 时，返回 FALSE。

句法

OR(逻辑 1, [逻辑 2], …)

论点

逻辑1：您要检查的条件。
[逻辑2]：您要检查的附加条件。

如果引用单元格或表格包含空单元格或文本，则值将被忽略。
条件的结果必须是逻辑值（TRUE 或 FALSE）。
如果没有返回逻辑值，它将返回错误。

例子

在下面的示例中，我们使用 IF 函数创建了一个条件：如果学生在两门科目中的任何一科中得分高于 60 分，则公式将返回 TRUE。

现在，在下面的示例中，我们使用数字来获取公式中的逻辑值。您也可以按相反顺序执行上述条件。

您可以使用 TRUE 和 FALSE 代替数字。 OR 函数将这些逻辑值视为数字。