【笔记】Excel的函数

发表于 更新于 阅读次数：

前言

Excel的函数学习笔记

函数的构成

1
=函数名(参数列表)
1
=常量 运算符 变量

常量

  • 由数字直接构成

变量

  • 由单元格引用构成

单元格变量

相对引用

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

绝对引用

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

混合引用

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

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

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

整个区域的引用

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

整列的引用

  • 如果加上了@，在单元格自动填充时会自动改变为当前行
  • 省略表名表示当前表
1
2
3
4
5
[表头名]
[@表头名]

表名[表头名]
表名[@表头名]
  • 也可以用:手动选择整列

运算符

算数运算符

符号 备注
+ 加
- 减
* 乘
/ 除
% 取余
^ 乘方

比较运算符

符号 备注
= 等于
> 大于
< 小于
>= 大于等于
<= 小于等于
<> 不等于

文本链接符

符号 备注
& 文本链接符

引用运算符

符号 备注
: 连续区域引用
, 将多个引用合并为一个引用
取多个引用的交集作为一个引用

函数

数值处理函数

求和

  • 可以选择单元格，也可以选择连续的一组单元格
1
=SUM(A1,B1,...)
按条件求和
  • 如果指定的整列，既是用作判断的单元格，又是需要计算的数据，那么可以传递2个参数
1
=SUMIF(同时用作判断的单元格和用于计算的单元格,"判断条件")
  • 如果指定的整列，仅是用作判断的单元格，需要计算的数据在其他列，那么可以传递3个参数
1
=SUMIF(用作判断的单元格,"判断条件",用于计算的单元格)

求乘积

1
=PRODUCT(A1,A2,...)

乘积之和

  • 将数组中的数值乘积，将所有乘积求和
1
=SUMPRODUCT()

求平均数

  • 可以选择单元格，也可以选择连续的一组单元格
1
=AVERAGE(A1,B1,...)
按条件求平均值
1
=AVERAGEIF(A1:B2,"判断条件")

保留小数

  • 如果保留的位数为0，那么就是不保留小数
1
=ROUND(单元格,保留的位数)

求最大值

1
=MAX(A1,B1,...)

求最小值

1
=MIN(A1,B1,...)

求出现次数最多的值

1
=MODE(A1,B1,...)

求数据的个数

  • 求包含数字的单元格的个数
1
=COUNT(A1:B2)
按条件求数据的个数
  • 求满足条件的单元格的个数
  • 如果想要获取指定字符串的数据个数，只需要将判断条件配置为需要计数的字符串既可
1
=COUNTIF(A1:B2,"判断条件")
求非空单元格个数
1
=COUNTA(A1:B2)

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

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

排序规则

1：升序
0：降序

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

文本处理函数

文本合并

手动选取数据
1
=CONCATENATE(A1,B1,...)
批量选取数据
  • Office2019/Office365支持新的文本连接函数，可以通过选取一个区域的数据进行文本合并
1
=CONCAT(A1:B2)
添加分隔符
1
=CONCAT(A1:B2&" ")

清除空格

  • 清楚所有换行符
  • 清除前、后所有的空格，字符串与字符串之间只保留1个空格
1
=TRIM(A1)
  • 清楚所有换行符
  • 清楚由不同操作系统生成的，不能被Excl打印的空字符
1
=CLEAN(A1)

文本提取

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

<num>：截取的字符个数

1
=LEFT(A1,<num>)
从尾开始
  • 从尾开始截取，指定截取长度向左截取

<num>：截取的字符个数

1
=RIGHT(A1,<num>)
指定开始位置和长度
  • 从指定位置开始，截取到指定长度

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

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

判断函数

条件判断

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

多个条件的判断

  • 仅Office2019/Office365支持
1
2
3
=IFS(判断条件1,判断条件1满足的时返回的结果,判断条件2,判断条件2满足的时返回的结果,...)

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

报错判断

  • 当指定的函数有可能报错时，可以在报错的情况下指定一种显示的数据
1
=IFERROR(可能会出错的值,出错后返回的值)

逻辑函数

  • 返回布尔值TRUE或FALSE

逻辑与

1
AND(A1,B1,...)

逻辑或

1
OR(A1,B1,...)

查找函数

根据关键字查找数据

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

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

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

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

精确查找案例

模糊查找案例

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

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

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

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

精确查找案例

根据行和列查找数据

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

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

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

根据关键字获取行偏移量

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

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

1
=MATCH(关键字,查找范围,匹配条件)
  • 只能选择一列区域作为查找范围，如果指定了多个列会报错

时间日期函数

获取当前系统时间

获取当前年月日
  • 得到的年月日是一个变量，如果当前日期发生改变会自动变化
1
=TODAY()
  • 如果只是要获取当前日期常量，也可以使用ctrl+;生成当前日期
  • 如果需要同时获取当前日期和当前时间常量，可以先使用ctrl+;生成当前日期，然后添加一个空格，最后用ctrl+shift+;生成当前时间
可以增加偏移量
1
2
=TODAY()+1
=TODAY()-1
获取当前年月日时分秒
  • 得到的年月日时分秒是一个变量，如果当前时间发生改变会自动变化
1
=NOW()
  • 如果只是要获取当前时分秒常量，也可以使用ctrl+shift+;生成当前时分秒
  • 如果需要同时获取当前年月日和当前时分秒常量，可以先使用ctrl+;生成当前年月日，然后添加一个空格，最后用ctrl+shift+;生成当前时分秒

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

  • 通常用于，两个不统一的时间格式之间的计算时，将两个时间格式进行统一
根据年月日获取时间格式字符串
1
=DATE(年,月,日)
  • 如果年小于0或大于100000会报错
  • 月和日的计算都是偏移量，当出现小于1的数据时，则按照-指定数据-1计算
    • 举例：如果函数为=DATE(2018,0,0)，那么得到的结果是2017/11/30
根据时分秒获取时间格式的字符串
1
=TIME(时,分,秒)
  • 如果与年月日计算时，TIME的到的时分秒超过了24小时，需要/24
    • 举例=DATE(2018,1,1)+TIME(48,0,0)/24

获取日期的某一部分

  • 传递的参数必须是日期格式的字符串
获取年
1
=YEAR("2018/1/1")
获取月
1
=MONTH("2018/1/1")
获取日
1
=DAY("2018/1/1")
获取星期

日期显示类型

1：（缺省值）返回区间为[1,7]的数值，1表示星期日
2：返回区间为[1,7]的数值，1表示星期一
3：返回区间为[0,6]的数值，0表示星期一
11：返回区间为[1,7]的数值，1表示星期一
12：返回区间为[1,7]的数值，1表示星期二
13：返回区间为[1,7]的数值，1表示星期三
14：返回区间为[1,7]的数值，1表示星期四
15：返回区间为[1,7]的数值，1表示星期五
16：返回区间为[1,7]的数值，1表示星期六
17：返回区间为[1,7]的数值，1表示星期日

1
2
3
=WEEKDAY("2018/1/1")

=WEEKDAY("2018/1/1",日期显示类型)
获取时
1
2
3
=HOUR("12:00:00")

=HOUR("2018/1/1 12:00:00")
获取分
1
2
3
=MINUTE("12:00:00")

=MINUTE("2018/1/1 12:00:00")
获取秒
1
2
3
=SECOND("12:00:00")

=SECOND("2018/1/1 12:00:00")

重新格式化数据

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

传送门

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

获取指定单元格的列

  • 可以用于控制可自动填充的变量
1
=COLUMN(A1)

生成随机数

1
=RANDBETWEEN(最小值,最大值)

完成

参考文献

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