【笔记】Excl的函数

Excl的函数学习笔记

函数的构成

=函数名(参数列表)

=常量 运算符 变量

• 由数字直接构成

• 由单元格引用构成

单元格变量

• 由列编号和行编号组合构成
• 相对引用在单元格自动填充时会自动改变

A1

• 在列编号前或行编号前添加$符号
• 绝对引用在单元格自动填充时不会改变

$A$1

• 同时包含相对引用和绝对引用的变量

• 自动填充时，只有行自动改变

  • 通常自动填充时竖着拉单元格

$A1

• 自动填充时，只有列自动改变
  • 通常自动填充时横着拉单元格

A$1

整个区域的引用

• 在:前后指定选择的区域一组对角

A1:B2

整列的引用

• 如果加上了@，在单元格自动填充时会自动改变为当前行
• 省略表名表示当前表

[表头名]
[@表头名]

表名[表头名]
表名[@表头名]

• 也可以用:手动选择整列

算数运算符

比较运算符

文本链接符

引用运算符

数值处理函数

• 可以选择单元格，也可以选择连续的一组单元格

=SUM(A1,B1,...)

按条件求和

• 如果指定的整列，既是用作判断的单元格，又是需要计算的数据，那么可以传递2个参数

=SUMIF(同时用作判断的单元格和用于计算的单元格,"判断条件")

• 如果指定的整列，仅是用作判断的单元格，需要计算的数据在其他列，那么可以传递3个参数

=SUMIF(用作判断的单元格,"判断条件",用于计算的单元格)

=PRODUCT(A1,A2,...)

• 将数组中的数值乘积，将所有乘积求和

=SUMPRODUCT()

• 可以选择单元格，也可以选择连续的一组单元格

=AVERAGE(A1,B1,...)

按条件求平均值

=AVERAGEIF(A1:B2,"判断条件")

• 如果保留的位数为0，那么就是不保留小数

=ROUND(单元格,保留的位数)

=MAX(A1,B1,...)

=MIN(A1,B1,...)

求出现次数最多的值

=MODE(A1,B1,...)

求数据的个数

• 求包含数字的单元格的个数

=COUNT(A1:B2)

按条件求数据的个数

• 求满足条件的单元格的个数
• 如果想要获取指定字符串的数据个数，只需要将判断条件配置为需要计数的字符串既可

=COUNTIF(A1:B2,"判断条件")

求一个数值在列表中的排位

• 获取一个数据在一组数据中的顺序

排序规则

1：升序
0：降序

=RANK(需要排位的数据,所有数据,排序规则)

文本处理函数

手动选取数据

=CONCATENATE(A1,B1,...)

批量选取数据

• Office2019/Office365支持新的文本连接函数，可以通过选取一个区域的数据进行文本合并

=CONCAT(A1:B2)

添加分隔符

=CONCAT(A1:B2&" ")

• 清楚所有换行符
• 清除前、后所有的空格，字符串与字符串之间只保留1个空格

=TRIM(A1)

• 清楚所有换行符
• 清楚由不同操作系统生成的，不能被Excl打印的空字符

=CLEAN(A1)

• 从头开始截取，指定截取长度向右截取

<num>：截取的字符个数

=LEFT(A1,<num>)

• 从尾开始截取，指定截取长度向左截取

<num>：截取的字符个数

=RIGHT(A1,<num>)

指定开始位置和长度

• 从指定位置开始，截取到指定长度

<index>：开始位置索引，第一个字符索引为1
<num>：截取的字符个数

=MID(A1,<index>,<num>)

=IF(判断条件,正确时返回的结果,错误时返回的结果)

嵌套条件判断

=IF(判断条件,IF(判断条件,正确时返回的结果,错误时返回的结果),IF(判断条件,正确时返回的结果,错误时返回的结果))

多个条件的判断

• 仅Office2019/Office365支持

=IFS(判断条件1,判断条件1满足的时返回的结果,判断条件2,判断条件2满足的时返回的结果,...)

=IFS(判断条件1,判断条件1满足的时返回的结果,判断条件2,判断条件2满足的时返回的结果,TRUE,以上条件都不满足时返回的结果)

• 当指定的函数有可能报错时，可以在报错的情况下指定一种显示的数据

=IFERROR(可能会出错的值,出错后返回的值)

• 返回布尔值TRUE或FALSE

AND(A1,B1,...)

OR(A1,B1,...)

根据关键字查找数据

• 通过关键字，查找符合关键字条件的行，将那一行的其他列数据返回

关键字：选择一个单元格，作为查找的关键字
查找范围：选择一个区域的单元格，作为查找范围
返回结果的列偏移量：将找到的行中第几列作为返回数据，从关键字存在的那一列开始算作第1列
匹配条件

0、True：精确查找
1、False：模糊查找

=VLOOKUP(关键字,查找范围,返回结果的列偏移量,匹配条件)

精确查找案例

模糊查找案例

• 通过关键字，查找符合关键字条件的列，将那一列的其他行数据返回

关键字：选择一个单元格，作为查找的关键字
查找范围：选择一个区域的单元格，作为查找范围
返回结果的行偏移量：将找到的列中第几行作为返回数据，从关键字存在的那一行开始算作第1行
匹配条件

0、True：精确查找
1、False：模糊查找

=HLOOKUP(关键字,查找范围,返回结果的行偏移量,匹配条件)

精确查找案例

根据行和列查找数据

查找范围：选择一个区域的单元格，作为查找范围
行偏移量：从查找范围中偏移指定行数
列偏移量：从查找范围中偏移指定列数

=INDEX(查找范围,行偏移量)

=INDEX(查找范围,行偏移量,列偏移量)

根据关键字获取行偏移量

查找范围：选择一个列区域的单元格，作为查找范围
关键字：选择一个单元格，作为查找的关键字
匹配条件

0、True：精确查找
1、False：模糊查找

=MATCH(关键字,查找范围,匹配条件)

• 只能选择一列区域作为查找范围，如果指定了多个列会报错

时间日期函数

获取当前系统时间

获取当前年月日

• 得到的年月日是一个变量，如果当前日期发生改变会自动变化

=TODAY()

• 如果只是要获取当前日期常量，也可以使用ctrl+;生成当前日期
• 如果需要同时获取当前日期和当前时间常量，可以先使用ctrl+;生成当前日期，然后添加一个空格，最后用ctrl+shift+;生成当前时间

可以增加偏移量

=TODAY()+1
=TODAY()-1

获取当前年月日时分秒

• 得到的年月日时分秒是一个变量，如果当前时间发生改变会自动变化

=NOW()

• 如果只是要获取当前时分秒常量，也可以使用ctrl+shift+;生成当前时分秒
• 如果需要同时获取当前年月日和当前时分秒常量，可以先使用ctrl+;生成当前年月日，然后添加一个空格，最后用ctrl+shift+;生成当前时分秒

获取指定日期格式的字符串

• 通常用于，两个不统一的时间格式之间的计算时，将两个时间格式进行统一

根据年月日获取时间格式字符串

=DATE(年,月,日)

• 如果年小于0或大于100000会报错
• 月和日的计算都是偏移量，当出现小于1的数据时，则按照-指定数据-1计算
  • 举例：如果函数为=DATE(2018,0,0)，那么得到的结果是2017/11/30

根据时分秒获取时间格式的字符串

=TIME(时,分,秒)

• 如果与年月日计算时，TIME的到的时分秒超过了24小时，需要/24
  • 举例=DATE(2018,1,1)+TIME(48,0,0)/24

获取日期的某一部分

• 传递的参数必须是日期格式的字符串

=YEAR("2018/1/1")

=MONTH("2018/1/1")

重新格式化数据

• 指定格式，重新格式化数据，返回字符串类型数据

传送门

=TEXT(A1,"yyyy-mm-dd")

获取指定单元格的列

• 可以用于控制可自动填充的变量

=COLUMN(A1)

生成随机数

=RANDBETWEEN(最小值,最大值)

哔哩哔哩——千锋教育
百度经验——shaowu459
Microsoft支持
知乎——百川资源库

</div