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 之间的数字。为了理解这个公式,我们需要将其分为三个部分:
- 首先,当它检测到较高数字中的较低数字时,您会得到两者之间的差异。
- 然后将该差值乘以减法后返回的随机数。
- 第三,将该数字与等式第三部分中剩余的最小数字相加。
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 函数将这些逻辑值视为数字。