OFFICE EXCEL 常用函数

错误值类型

值类型	含义
#####	当使用了负的日期或负的时间时，出现错误
#VALUE!	当使用的参数或操作数类型错误时，出现错误
#DIV/O!	当数字被零(0)除时，出现错误
#NAME?	当Excel未识别公式中的文本时，出现错误
#N/A	当数值对函数或公式不可用时，出现错误
#REF!	当单元格引用无效时，出现错误
#NUM!	公式或函数中使用无效数字值时，出现错误
#NULL!	当指定并不相交的两个区域的交点时，出现错误

计算统计类

SUM 求和

=SUM(C14:C19) “括号中是求和的单元格

SUMIF 条件求和，可以指定一个求和条件

=SUMIF(range,criteria,sum_range)，条件区域，条件，为实际求和区域(当省略第三个参数时，则条件区域就是实际求和区域。)
=SUMIF(B2:B5,B2,C2:C5)

SUMIFS 多条件求和，可以指定多个条件

SUMIFS(C2:C6,B2:B6,“女”,A2:A6,“小赵”) "计算C2到C6区域的和，条件1：B2到B6区域等于女的，条件2：A2到A6区域等于小赵的。

AVERAGE 平均值

对一列或多列数字进行求平均值
AVERAGE(C14:C16)

AVERAGEIF 条件求均值

AVERAGEIF(B2:B6,“女”,C2:C6) "B2到B6区域等于女的，计算C2到C6区域的平均值。

AVERAGEIFS 多条件求均值

averageifs(average_range,criteria_range1,criteria1,criteria_range2,criteria2,…)

MAX 最大值

对一列或多列数字进行求最大值。
MAX(A1:A10)表示对A1到A10单元格中的数字进行求最大值。

MIN 最小值

对一列或多列数字进行求最小值。
MIN(A1:A10)表示对A1到A10单元格中的数字进行求最小值。

COUNT 单元格数

可以对一列或多列单元格进行计数。
COUNT(A1:A10)表示对A1到A10单元格中的单元格进行计数。

COUNTA 计算非空单元格的个数

COUNTA(C2:C9)

COUNTIF 条件计数

COUNTIF(B2:B9,“男”) "B2到B9区域，等于男的条数。

COUNTIF 显示一列单元格数据是否在另一列存在

IF(COUNTIF(B3:B42,D3),“是”,“否”) D3 在B3到B42中是否存在，存在是不存在否.
=countif(a2:a17,c2) 显示c2单元格的内容在A列数据中出现的次数

COUNTIFS 多条件计数

COUNTIFS(B2:B6,“女”,A2:A6,“小赵”)

---

逻辑运算类

大于>、等于=、小于<、大于等于>=、小于等于<=、不等于<>

IF 条件判断

可以根据条件返回不同的值。
IF(C19>10,“YES”,“NO”)表示如果C19大于10，则返回"Yes"，否则返回"No"。

AND

检查是否所有参数均为true，如果是就返回true否则返回false。
AND(逻辑值1，逻辑值2，……)
AND(C3<=20,C5=12)
IF( AND(C3<=20,C5=12),“对的”,“错的”)

OR

检查是否所有参数均为true，如果是就返回true否则返回false。
OR(逻辑值1，逻辑值2，……)
OR(C3<=20,C5=12)
IF( OR(C3<=20,C5=12),“对的”,“错的”)

NOT 逻辑值

对参数的逻辑值求反:参数为true时返回false，参数是false时返回true。
IF( NOT(C3=20),“对的”,“错的”) ”C3等于20，返回错的。

---

关联匹配类

VLOOKUP 查找和匹配(垂直查找函数)

可以根据指定的值在表格中查找并返回相应的值。
VLOOKUP(A1,B1:C10,1,FALSE)表示在B1到C10单元格中查找A1的值，并返回第一列的值。

HLOOKUP 查找和匹配(水平查找函数)

与VLOOKUP函数类似，但是是水平查找。
HLOOKUP(A1,B1:C10,1,FALSE)表示在B1到C10单元格中查找A1的值，并返回第一行的值。

xlookup：新版查找函数，比vlookup和hlookup更加强大

INDEX 返回特定区域，行列交叉的值

INDEX(A1:C9,7,2) "A1到C9区域(不能是某列或某行)，第7行，第2列的值。

MATCH：查找 根据单元格的值返回位置

=MATCH(lookup_value, lookup_array, [match_type])
=MATCH(查找值,查找区域,匹配模式)
匹配模式：范围{-1，0，1}，当省略此参数时默认是0。指定查找值在单元格区域中以什么样的方式进行匹配。

当match_type等于1时，小于匹配。MATCH 查找小于或等于 lookup_value 的最大值。 lookup_array必须以升序排序。

当match_type等于0时，精确匹配。MATCH查找完全等于 lookup_value 的第一个值，如果查找值是一个文本的时候，可以使用通配符（*和？）进行查找。这里的lookup_array参数中的值可按任何顺序排列。

当match_type等于-1时，大于匹配。MATCH 查找大于或等于 lookup_value 的最小值。lookup_array 参数中的值必须按降序排列。

MATCH(“小赵”,A:A,0) "0第一次出现的位置，1最后一次出现的位置.

FIND 发现

返回一个字符串在另一个字符串中出现的起始位置（区分大小写）
FIND函数：FIND(find_text,within_text,[start_num])
find_text：查询的内容
within_text：被查询的单元格
Start_num ：指定开始进行查找的字符。比如Start_num为1，则从单元格内第一个字符开始查找目标字符。如果忽略 start_num，则默认其为 1。
FIND(“-”,G4,1)

SEARCH 搜索

返回一个字符串在另一个字符串中出现的起始位置从左到右（不区分大小写）
SEARCH(find_text,within_text,[start_num])
SEARCH(“0”,G4)

ISNUMBER 是否数值

检查一个值是否数值，返回true或false。
ISNUMBER(A3)

---

数据筛选类

&：拼接符

=(A2&A3)

CONCATENATE 将多个文本字符串合并为一个字符串

CONCATENATE(A1," ",B1)表示将A1和B1单元格中的文本字符串合并为一个字符串，并在它们之间添加一个空格。

TEXTJOIN：字符串拼接，可以指定分隔符（新版Excel才能使用）

LEFT 提取文本字符串左侧的字符

LEFT(A1,3)表示提取A1单元格中的文本字符串左侧的3个字符。

RIGHT 取文本字符串右侧的字符

RIGHT(A1,3)表示提取A1单元格中的文本字符串右侧的3个字符。

MID 提取文本字符串中指定位置的字符

MID(A1,2,3)表示提取A1单元格中的文本字符串中第2个字符开始的3个字符。

LEN 计算文本字符串长度

LEN(A1)表示计算A1单元格中的文本字符串的长度。

TRIM 去除文本字符串中的空格

TRIM(A1)表示去除A1单元格中的文本字符串中的空格。

ROUND 对数字进行四舍五入

ROUND(A1,2)表示对A1单元格中的数字进行保留两位小数的四舍五入。

REPLACE 替换

需要指定从第几个字符开始替换、替换几个字符
REPLACE(B7,3,1,“男”) "把区域B7，从第三位开始，保留1位，替换为男。

SUBSTITUTE 将字符串部分内容替换为新内容

需要指定替换的旧子字符串和新子字符串
(要替换的文本,旧文本,新文本,[替换第几个])
SUBSTITUTE(B7,“女”,“男”) "把B7中的女替换为男
SUBSTITUTE(B7,“女”,“男”,2) "把B7中的，第二次出现的女替换为男。

TEXT 文本格式

用于将数值或日期格式转换为特定的文本格式
日期格式转换

格式化数字
值、数字格式、公式及输出结果如下表所示。
解释说明：
①“0”：数字占位符。四舍五入显示整数不显示小数点。若单元格的内容大于占位符个数，则显示四舍五入的整数；若小于占位符的数量，则用0补足。例如：=TEXT(3.14,“00”)，输出结果为：03。
②“0.00”：显示小数点的个数，点的后面有几个0则表示保留小数点的后几位。
③“0.00%”：显示百分比，点的后面几个0表示保留小数点后几位小数。
④“#”：数字占位符。只显有意义的零而不显示无意义的零。小数点后数字如大于“#”的数量，则按“#”的位数四舍五入。例如：“###”,3.14显示为3;“##.#”,3.15显示为：3.2。
⑤“0，000”：千位加逗号显示。
⑥G/通用格式”：以常规的数字显示，相当于“分类”列表中的“常规”选项。

当前时戳
=TEXT(NOW(),“yyyy/mm/dd hh:mm:ss”)

LOWER 将所有字母转换为小写字母

LOWER(A2)

UPPER 将所有字母转换为大写字母

UPPER(A2)

PROPER 将字符串首字母转换为大写 ，其余字母为小写

PROPER(A2)

时间序列类

DATE 获取指定日期

DATE(2023,10,17)

YEAR 获取日期中的年份

YEAR(E6) E6为日期格式，日期必须有分隔符。

MONTH 获取日期中的月份

MONTH(E6) E6为日期格式，日期必须有分隔符。

DAY 获取日期中的天

DAY(E6) E6为日期格式，日期必须有分隔符。

TODAY 获取当前日期

TODAY() 2023/10/16

NOW 获取当前日期加时间

NOW() 2023/10/16 17:07
NOW()+7 返回7天后的日期和时间

TIME 获取指定时间

TIME(16,59,10) "值4:59 PM

WEEKDAY 获取日期，是一周中的第几天。

WEEKDAY(E6) 2023/10/16(星期二) 等于2 第二天。

WEEKNUM 获取日期为这年中的第几周

WEEKNUM(E6) E6为日期格式，日期必须有分隔符。

DATEDIF 获取两个日期之间的日期差，可以获取到年数差、月数差、天数差

DATEDIF(E8,TODAY(),“Y”) "E8与当前日期相差几年

“Y” 时间段中的整年数。
“M” 时间段中的整月数。
“D” 时间段中的天数。
“MD” 起始日期与结束日期的同月间隔天数，忽略日期中的月份和年份。
“YD” 起始日期与结束日期的同年间隔天数，忽略日期中的年份。
“YM” 起始日期与结束日期的同年间隔月数，忽略日期中的年份。

EOMONTH 获取指定日期所在月份的最后一天

EOMONTH(start_date,months)
Start_date：指定日期
months：start_date 之前或之后的月份数。 months 为正值将生成未来日期；为负值将生成过去日期;为0值表示指定日期start_date的所在月份。
EOMONTH(E3,2) "E3单元格中的月份，加2月后的月的最后一天。

安全模式

WIN + R 输入 Excel /safe
或者
按住 CRTL 然后打开excel。

以上内容部分转载。