Excel函数-总_excel函数 csdn-CSDN博客

本文链接：https://blog.csdn.net/2301_81133727/article/details/143706382

1.概述

在 Excel中，函数实际上是一个预先定义的特定计算公式。按照这个特定的计算公式对一个或多个参数进行计算，并得出一个或多个计算结果，叫做函数值。使用这些函数不仅可以完成许多复杂的计算，而且还可以简化公式的繁杂程度。如财务函数、日期与时间函数、数值与三角函数、统计函数、查找与引用函数、数据库函数、文字函数、逻辑函数、信息函数等。

2.数组公式

2.1.数组的类别

一维数组（横向）：数组元素间用逗号分隔
一维数组（纵向）：数组元素间用分号分隔
二维数组

2.2.数组的构成元素

数字、文本、逻辑值、错误值。

2.3.数组运算时需要用到的运算符

数学运算符、比较运算符、文本连接符

3.数学与三角函数

3.1. RANDBETWEEN

格式：randbetween(x，y)

作用：求x，y之间的随机整数，包含x和y

3.2. ABS

作用：求绝对值

格式：abs(数值)

3.3. MOD

结果符号和除数相同

作用：求两个数相除后的余数

格式：mod(被除数，除数)

注意：结果的符号为除数的符号，另外，如果被除数小于除数，那么结果为被除数本身

3.4. PI

作用：求圆周率

格式：pi()

3.5.PRODUCT

作用：求数值的乘积

格式：product（值1，值2，值3）

3.6.RADIANS

作用：将角度值转换为弧度值

格式：radians(角度)

3.7.DEGREES

作用：将弧度值转化为角度

格式：degrees（弧度）

3.8.POWER 或者数值^n

格式：power(x,y) 或者 (数值^n)

作用：求x的y次方

3.9.RAND

格式：求0到1间的随机小数，包含0，但是不包含1

格式：rand()

3.10.ROUND、ROUNDUP、ROUNDDOWN

作用：对数据进行四舍五入

格式：round（数值，要保留的小数位数）、
roundup（数值，要保留的小数位数）向上进位、
rounddown（数值，要保留的小数位数）向下舍去

注意：如果小数位数为负值，表示对小数点前面的数字进行四舍五入，如round(1284.567,-2)结果为1300

示例：
=ROUND(2.5, 0) // 结果为 3；
=ROUNDUP(2.5, 0) // 结果为 3；
=ROUNDDOWN(2.5, 0) // 结果为 2

3.11.SQRT

求数值的算术平方根

格式：sqrt(值)

4.逻辑函数

4.1.AND

作用：判断所有参数是否都为true，如果都为true，则结果为true，如果有一个伪false，则结果为false

格式：and(参数1，参数2…)

4.2.OR

作用：判断参数中是否有true，如果有true，则结果为true，如果所有参数都是false，则结果为false

格式：or(参数1，参数2….)

4.3.NOT

作用：对参数进行取反，如果参数为true，则结果为false，如果参数为false，则结果为true

格式：not(参数)

注意：Excel中0表示false，除了0之外的所有数值都表示true

4.4.IF

格式：if(条件，成立时的值，不成立时的值)

作用：判断条件是否成立，如果成立，则返回成立时的值，否则返回不成立时的值

4.5.FALSE

作用：生成false值

格式：false（）
示例：
=FALSE()；
=IF(A1<10, “Less than 10”, FALSE())

4.6.TRUE

作用：生成true

格式：true（）

注意事项：在excel中0可以转换为false、非0之外的数值会转化为true。另外false会转化为0，true会转化为1
示例：
=TRUE()
=IF(A1>10, TRUE(), “Not greater than 10”)

4.7 iferro(),Iserror()

判断错误：
iferror(指令，指令错误显示的内容）
Iserror(指令) 指令错误返回True，指令正确返回False
示例1：
=IFERROR(A1/B1, “除数不能为0”) #如果B1是0，则会返回"除数不能为0"。
示例2：

5.文本函数

5.1.CONCATENATE

作用：对文本内容进行拼接

格式：concatenate(“文本1”，“文本2”…)

5.2.EXACT

作用：判断两个字符串是否相同，区分大小写，返回值为true、false

格式：exact(字符串1，字符串2)

5.3.FIND

作用：在指定的字符串中查找某个字符，如果存在则返回其在该字符串中的位置编号

格式：find(“要找的字符”，“字符串”，开始查找的位置编号)

注意：在查找时是从前往后查找，如果找到则停止查找，如果找不到该子串那么返回值为#value

5.4.LEFT

作用：从前向后截取n个字符

格式：left（字符串，个数）

注意：如果省略第二个参数，那么返回值为字符串中的第一个字符

5.5.RIGHT

作用：从后向前截取n个字符

格式：right（字符串，个数）

注意：如果省略第二个参数，那么返回值为字符串中的第一个字符

5.6.MID

作用：在字符串中从指定位置开始截取n个字符

格式：mid(字符串，开始位置，截取个数)

5.7.LEN

作用：求字符串中包含的字符的个数

格式：len(字符串)

5.8.LENB

作用：求字符串中包含的字节数，一个汉字占2个字节

格式：lenb（字符串）

5.9.SUBSTITUTE

作用：将字符串中的指定字符替换成新的字符

格式：substitute（字符串，要被替换的字符，被替换成的字符，要被替换的字符的编号）

注意：如果省略最后一个参数，那么字符串中所有符合条件的字符都会被替换

示例：

SUBSTITUTE(“helloworlod”,“o”,"",1)，结果为hellworlod

SUBSTITUTE(“helloworlod”,“o”,"",2)，结果为hellowrlod

SUBSTITUTE(“helloworlod”,“o”,"",3)，结果为helloworld

5.10.UPPER

作用：将字符串中的所有字符变为大写

格式：upper(字符串)

5.11.LOWER

作用：将字符串红的所有字符变为小写

格式：lower(字符串)

5.12.PROPER

作用：将字符串中每个单词的首字母变成大写

格式：proper（字符串）

5.13.REPLACE

作用：将字符串中的指定字符替换成新的字符

格式：replace(字符串，起始位置的编号，个数，新的字符)

示例：replace(“abcabc”, 2,3,””)结果为abc
因为从第2个字符开始的3个字符 “bca” 被替换为了空字符串，所以只剩下 “a*bc”。

5.14.REPT

作用：将字符串重复指定遍数

格式：rept（字符串，遍数）
示例：=REPT(“abc”, 5) 结果为： “abcabcabcabcabc”，即字符串 “abc” 被重复了5次。

5.15.SEARCH

作用：从字符串中查找某个字符第一次出现的位置

格式：search(要找的字符，字符串，查找的起始位置)

5.16.TRIM

作用：将字符串中多余的空格删除，但是英文单词间保留一个空格

格式：trim(字符串)

注意：两端的空格会被删除，中间的空格会被合并

5.17.VALUE

将以文本形式表示的数值变为真正的数值

value(字符)

6.统计函数

6.12. count()

count(数据范围)数值单元格计数

6.13 counta()

counta(数据范围)非空单元格计数

6.14. countblank(范围)

作用：求单元格的个数

格式：countblank(范围)

6.15.countif()

countif(数据范围，条件值1)单条件计数

6.16.countifs()

countifs(数据范围1，条件值1，数据范围2，条件值2，)多条件计数

6.17.SUM

作用：求数值的和

格式：sum(值1，值2，值3….)

6.18.SUMIF

作用：求满足单个条件的数值的和

格式：sumif(条件所在范围，“条件”，求和范围)

注意：如果条件范围和求和范围相同，那么求和范围可以省略

6.19.SUMIFS

作用：求满足多个条件的数值的和

格式：sumifs（求和范围，条件范围1，”条件”，条件范围2，”条件”…）

6.20. average()

作用：求满足条件的数值的均值

格式：average(数据范围) 求平均

6.21. averageif()

作用：求满足单个条件的数值的均值

格式：averageif(数据范围，条件值1，求平均范围)单条件求平均

6.22. averageifs()

作用：求满足多个条件的数值的均值
格式：averageifs(求平均范围，数据范围1，条件值1，数据范围2，条件值2.) 多条件求平均

6.23. max()

作用：求满足单个条件的数值的最大值

格式：max(数据范围) 求最大值

6.24. maxifs()

作用：求满足多个条件的数值的最大值
格式：maxifs(求最大范围，数据范围1，条件值1，数据范围2，条件值2，…) 条件求最大值

6.25. min()

作用：求满足单个条件的数值的最小值
格式：min(数据范围) 最小值

6.26. minifs()

作用：求满足多个条件的数值的最小值

格式：minifs(求最小范围，数据范围1，条件值1，数据范围2，条件值2，…)条件求最小值

6.27.LARGE

作用：在某个数据范围中查找第n个最大值

格式：large(范围，个数)

6.28.SMALL

作用：在某个数据范围中查找第n个最小值

格式：small(范围，个数)

7.日期和时间

7.1.DATE

作用：生成日期

格式：date(年，月，日)

7.2.YEAR

作用：返回日期中的年份

格式：year(日期)

7.3.MONTH

作用：返回日期中的月份

格式：month(日期)

7.4.DAY

作用：返回日期中的天

格式：day(日期)

7.5.WEEKDAY

返回1-7数字，1表示周日

作用：返回参数所代表的的日期是星期几，注意返回值为1-7，可以通过第二个参数来确定1-7分别表示什么

格式：weekday（日期，返回值的类型）

1-从 1(星期日)到 7 (星期六)的数字

2-从 1(星期一)到 7(星期日)的数字

3-从 0(星期一)到 6(星期日)的数字

11-数字 1(星期一)至7(星期日)

12-数字 1(星期二)至7(星期一)

13-数字 1(星期三)至7(星期二)

14-数字 1(星期四)至7(星期三)

15-数字 1(星期五)至7 (星期四)

16-数字 1(星期六)至7(星期五)

17-数字 1(星期日)至7 (星期六)

7.6.NOW

作用：返回系统当前的日期和时间

格式：now()

7.7.HOUR

作用：返回时间中的小时

格式：hour（时间）

7.8.MINUTE

作用：返回时间中的分钟

格式：minute(时间)

7.9.SECOND

作用：返回时间中的秒

格式：second(时间)

7.10.DAYS

作用：返回两个日期间隔了多少天

格式：days(结束日期，开始日期)

示例：days(“2023/1/31”,”2023/1/1”)

7.11.TIME

作用：生成时间

格式：time（时，分，秒）

7.12.TODAY

作用：返回系统当前的日期

格式：today()

7.13.WEEKNUM

返回某个日期是这一年中的第几周

格式：weeknum(日期)

8.信息函数

8.1.CELL

作用：返回被引用单元格的信息

在Excel中，CELL函数用于获取有关单元格的信息。
它的语法是：

格式： CELL(info_type, reference)

info_type：一个文本字符串，指定要返回的信息类型。常用的信息类型包括：
- "address"：返回单元格的地址。
- "contents"：返回单元格的内容。
- "filename"：返回包含该单元格的工作簿的文件名。
- "format"：返回单元格的数字格式。
- "type"：返回单元格的类型（例如，"v"表示数值，"b"表示空白，"e"表示错误）。
reference：要获取信息的单元格的引用。

示例：

获取单元格A1的地址：
```
=CELL("address", A1)
```
这将返回"A1"。
获取单元格B2的内容：
```
=CELL("contents", B2)
```
如果B2的内容是"Hello"，则返回"Hello"。
获取单元格C3的格式：
```
=CELL("format", C3)
```
返回C3单元格的数字格式代码。

8.2.INFO

作用：返回操作环境的相关信息

在Excel中，INFO函数用于返回有关当前操作环境的信息。
该函数的语法结构为：

格式： =INFO(type_text)

其中，type_text是必需的参数，用于指定需要返回的信息类型。以下是一些常见的type_text参数及其返回的信息：

"directory"：返回包含当前工作簿的文件夹路径。
"filename"：返回当前工作簿的文件名。
"osversion"：返回正在运行Excel的操作系统的版本。
"username"：返回当前用户的用户名。

例如，如果你想获取当前工作簿的文件名，可以在一个单元格中输入以下公式：

=INFO("filename")

这将在单元格中显示当前工作簿的文件名。同样地，如果你想获取当前用户的名称，可以输入：

=INFO("username")

这将在单元格中显示当前登录系统的用户名。INFO函数在自动化报表生成、记录日志等方面非常有用，可以帮助你在处理Excel数据时更加高效。

8.3.ISBLANK

作用：判断引用的单元格是否为空单元格

格式：isblank(引用的单元格)

在Excel中，ISBLANK 函数用于检查指定的单元格是否为空。如果单元格为空，则返回 TRUE；如果单元格不为空（无论它包含的是文本、数字、错误值还是公式），则返回 FALSE。

函数的语法如下：

ISBLANK(reference)

reference：要检查的单元格引用。

示例：

假设你有一个单元格A1，你想检查它是否为空：

如果A1是空的，你可以使用以下公式：
```
=ISBLANK(A1)
```
这将返回 TRUE。
如果A1包含任何内容（例如文本、数字或公式），使用同样的公式：
```
=ISBLANK(A1)
```
这将返回 FALSE。

8.4.ISERROR

作用：判断引用的单元格是否为错误

格式：iserror(被引用的单元格)

在Excel中，ISERROR 函数用于检查一个值是否会导致错误。如果被引用的单元格包含一个错误值，ISERROR 函数将返回 TRUE；如果单元格中没有错误值，它将返回 FALSE。

函数的语法如下：

ISERROR(value)

value：要检查是否有错误的值或单元格引用。

示例：

假设你有一个单元格A1，你想检查它是否包含错误：

如果A1包含错误值（如#DIV/0!、#VALUE!、#REF!等），你可以使用以下公式：
```
=ISERROR(A1)
```
这将返回 TRUE。
如果A1不包含错误值（无论是空单元格、文本、数字还是公式），使用同样的公式：
```
=ISERROR(A1)
```
这将返回 FALSE。

8.5.ISEVEN

作用：判断被引用的单元格中的值是否为偶数

格式：iseven（被引用的单元格/数值）

8.6.ISODD

作用：判断被引用的单元格是否为奇数

格式：isodd(被引用的单元格/数值)

8.7. ISFORMULA

作用：判断被引用的单元格是否包含公式

格式：isformula(被引用的单元格)

8.8. ISLOGICAL

作用：判断参数是否为逻辑值

格式：islogical（参数）

8.9. ISNONTEXT

作用：判断参数是否为非文本类型

格式：isnontext（参数）

8.10.ISNUMBER

作用：判断参数是否为数值型

格式：isnumber（参数）

8.11. ISTEXT

作用：判断参数是否为文本类型

格式：istext(参数)

8.12 IFERROR()

IFERROR(value, value_if_error)
IFERROR 函数的基本语法如下：

IFERROR(value, value_if_error)

value：这是你想要测试是否有错误的公式或表达式。
value_if_error：如果 value 产生错误，IFERROR 将返回 value_if_error。

示例：
IFERROR 函数的使用方法非常简单，它可以帮助处理公式中可能出现的错误，使得电子表格更加用户友好和专业。以下是一些使用场景和步骤：

基本用法

场景：避免除以零的错误。

公式：

=IFERROR(A1/B1, "除以零错误")

解释：如果 B1 单元格为空或为零，公式将返回 “除以零错误”，而不是显示错误。

处理查找错误

场景：在使用 VLOOKUP 或 HLOOKUP 函数时，如果找不到匹配项，会返回错误。

公式：

=IFERROR(VLOOKUP("查找值", 查找范围, 列索引), "未找到")

解释：如果 VLOOKUP 函数找不到 “查找值”，它将返回 “未找到”，而不是显示错误。

处理空值

场景：当引用的单元格为空时，避免错误。

公式：

=IFERROR(SUM(A1:A10), 0)

解释：如果 A1:A10 范围内的所有单元格都为空，SUM 函数将返回错误。使用 IFERROR 后，将返回 0。

组合多个错误处理

场景：在复杂的公式中处理多个潜在错误。

公式：

=IFERROR(IFERROR(A1/B1, "除以零错误"), "其他错误")

解释：首先检查 A1/B1 是否除以零错误，如果是，则返回 “除以零错误”；如果不是，再检查其他错误，如果是，则返回 “其他错误”。

使用自定义值

场景：在错误发生时返回自定义值。

公式：

=IFERROR(A1^2, "输入值无效")

解释：如果 A1 单元格中的值导致 A1^2 计算错误（例如，非数字），则返回 “输入值无效”。

通过这些示例，你可以看到 IFERROR 函数的灵活性和实用性。它可以帮助确保你的电子表格在面对错误时能够优雅地处理，而不是显示令人困惑的错误消息。

9.查找函数

9.1.ROW

作用：返回被引用单元格的行号

格式：row(被引用的单元格)

9.2.COLUMN

作用：返回被引用单元格的列号（注意以数字表示）

格式：column（被引用的单元格）

9.3.HLOOKUP

示例：hlookup（要找的值，查找范围，找到后返回的值得列号，模糊匹配/精确匹配）

9.4.VLOOKUP

示例：vlookup（要找的值，查找范围，找到后返回的值得列号，模糊匹配/精确匹配）
在这里插入图片描述

9.5.INDEX

作用：用于返回表格或数组中特定行和列交叉点处的值。
格式1：（从一个区域中查找指定的值）：index(区域，行数，列数)

格式2：（从多个区域中查找指定的值）：index（（区域1，区域2，区域3…），行数，列数，区域编号）

示例：

单行或单列数组

假设你有一个单行数组（例如，A1:A5），你想返回第3个元素的值：
```
=INDEX(A1:A5, 3)
```
如果你想返回第3行第2列的值（例如，B3）：
```
=INDEX(A1:C5, 3, 2)
```
二维数组

假设你有一个二维数组（例如，A1:C5），你想返回第2行第3列的值（即C2）：
```
=INDEX(A1:C5, 2, 3)
```

动态数组：

在支持动态数组的Excel版本中（如Excel 365和Excel 2019），INDEX 函数可以返回一个数组，而不仅仅是单个值。这使得你可以在不需要拖动填充柄的情况下，一次性返回多个值。

9.6.MATCH

作用：返回所在区域的行数

MATCH 函数在Excel中用于搜索指定项在一个单元格区域中的相对位置。这个函数非常有用，尤其是在你想要基于某个条件找到对应的值时。

格式：

MATCH(lookup_value, lookup_array, [match_type])

lookup_value：需要查找的值。
lookup_array：包含可能包含lookup_value的一行或一列。
match_type（可选）：指定如何匹配数据。可以是以下值之一：
- 0 或省略：精确匹配。
- 1：小于等于lookup_value的最大值（要求lookup_array按升序排序）。
- -1：大于等于lookup_value的最小值（要求lookup_array按降序排序）。

返回值

MATCH 函数返回lookup_value在lookup_array中的相对位置。如果lookup_value未找到，函数将返回错误。

示例

假设你有以下数据：

A	B
1	Apple
2	Banana
3	Cherry
4	Date
5	Elderberry

精确匹配

如果你想找到"Cherry"在B列的位置：

=MATCH("Cherry", B2:B6, 0)

这将返回3，因为"Cherry"是B列中的第三个项目。

最大值匹配

如果你想找到小于或等于3的最大值：

=MATCH(3, A2:A6, 1)

这将返回3，因为3是A列中小于或等于3的最大值。

最小值匹配

如果你想找到大于或等于3的最小值：

=MATCH(3, A2:A6, -1)

这将返回3，因为3是A列中大于或等于3的最小值。

注意事项

当match_type为1或-1时，lookup_array必须按升序或降序排序，否则MATCH函数可能返回错误的结果。
如果lookup_value在lookup_array中未找到，MATCH函数将返回错误。你可以使用IFERROR函数来处理这种情况，提供一个默认值或错误消息。