10 个最重要的 excel 函数

1.中频函数

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

句法

IF(逻辑测试, value_if_true, value_if_false)

论点

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

评论

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

例子

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

excel-if-函数示例-1
  1. 我们使用特定的文本来获取条件是否满足的结果。
  2. 您还可以使用 TRUE 和 FALSE 来获取结果。
  3. 如果您跳过指定值来获取结果(如果条件为 TRUE),则它将返回零。
  4. 如果条件为 FALSE,则跳过指定值来获取结果,它将返回零。

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

excel-if-函数示例-2

我们指定了一个条件,如果该条件为假,我们使用另一个 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!带有有意义的文字。

excel-iferror-函数-示例-1

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

3.TRUNC函数

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

句法

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

论点

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

评论

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

例子

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

excel-trunc-函数示例-1

4.SUMIF函数

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

句法

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

论点

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

评论

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

例子

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

excel-sum-函数示例-1

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

excel-sum-函数示例-2

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

excel求和函数示例3

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。

excel-索引-函数-示例-1

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

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

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

excel-索引-函数-示例-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查找类别

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

excel-vlookup-函数-示例-1

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

excel-vlookup-函数示例-2

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

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

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

excel-vlookup-函数-示例-3

怎么运行的

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

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

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

2.VLOOKUP函数中的错误处理

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

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

excel-vlookup-函数-示例-4

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

excel-vlookup-函数-示例-5

工作原理: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 指定了一条有意义的消息。

excel-ifna-函数示例-1

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

8.兰德函数

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

句法

兰德()

论点

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

评论

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

例子

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

excel-rand-函数-示例-1

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

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

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

9.求和函数

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

句法

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

论点

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

评论

  • 它忽略文本值。

例子

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

excel-sum-函数示例-1

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

excel-sum-函数示例-2

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

excel求和函数示例3

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

excel求和函数示例4

10. 或函数

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

句法

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

论点

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

评论

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

例子

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

excel 或函数示例 1

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

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

添加评论

您的电子邮箱地址不会被公开。 必填项已用 * 标注