【软件侠】公认最常用的20个函数，案例详解

写在前面：EXCEL在职场办公中的重要性不言而喻。学习更多EXCEL实战技能，特别推荐可以看看这套课（免费）

调整主题配色，提升表格N个档次_Excel进阶-表格美化与图表制作_表格美化 - 软件侠

1、IF函数-条件判断

函数说明：IF 函数根据提供的条件参数，条件计算结果为 TRUE 时，返回一个值；条件计算结果为 FALSE 时，返回另一个值。

返回值：根据条件参数的计算结果，返回其他两个参数其中的一个或其计算结果。

语法：=IF(logical_test, [value_if_true], [value_if_false])

参数：

logical_test 必需。 计算结果为 TRUE 或 FALSE 的任何值或表达式。

value_if_true 可选。 logical_test 参数的计算结果为 TRUE 时所要返回的值。

value_if_false 可选。 logical_test 参数的计算结果为 FALSE 时所要返回的值。

​=IF(B4>=9,"合格","不合格")

2、IF函数：多条件判断

=IF(AND(B16="生产",C16="主操"),"有","无")

3.SUMIF函数-条件求和

函数说明：计算一区域中符合指定条件的数字的和

返回值：求和值

语法：

=SUMIF(range, criteria, [sum_range])

=SUMIF(条件判断单元格区域, 条件, [求和单元格区域])

参数：

range 必需。 用于条件计算的单元格区域。空值和文本值将被忽略。

criteria 必需。 用于确定对哪些单元格求和的条件，其形式可以为数字、表达式、单元格引用、文本或函数。例如：

数字：27；

表达式：“<27”；

单元格引用：A1；

文本：“广州”；

函数：=LEFT(B1,1)

sum_range 可选。 要求和的实际单元格。如果省略 sum_range 参数，则对判断条件单元格区域进行求和

​

=SUMIF(C4:C7,E4,B4:B7)

​

4.SUMIFS 函数-多条件求和

函数说明：计算一区域中符合多个指定条件的数字的和

返回值：求和值

语法：

=SUMIFS(sum_range, criteria_range1, criteria1,[criteria_range2, criteria2], ...)

=SUMIFS(求和区域, 条件区域1, 条件1, [条件区域2, 条件2], ...)

参数：

Sum_range 必需。 需要求和的单元格区域，包括数字或包含数字的名称、区域或单元格引用。 空值和文本值将被忽略。

Criteria_range1 必需。 在其中计算判断条件的第一个条件区域。

Criteria1 必需。 需要在第一个条件区域判断是符合要求的条件1。其形式可以为数字、表达式、单元格引用、文本或函数。

Criteria_range2, criteria2, … 可选。 第二个条件区域和第二个条件。 最多允许 127 个区域/条件对

=SUMIFS(D4:D11,B4:B11,F4,C4:C11,G4)

​

5.COUNTIF-条件计数

函数说明：统计指定单元格区域中符合指定条件的单元格个数

返回值：符合条件的单元格个数

语法：

=COUNTIF(range, criteria)

=COUNTIF(单元格区域, 条件)

参数：

Range 必需。 需要计算的单元格区域。

Criteria 必须。对区域中进行判断的条件，条件可以有以下形式：

数字

文本

单元格引用

表达式，例如，”>60″

=COUNTIF(B4:B13,E4)

6.COUNTIFS-多条件计数

函数说明：COUNTIFS 函数统计指定单元格区域中符合多个指定条件的单元格个数。

返回值：符合多个条件的单元格个数。

语法：

=COUNTIFS(criteria_range1, criteria1,[criteria_range2, criteria2]…)

=COUNTIFS(条件区域1, 条件1, [条件区域2, 条件2]…)

参数：

Criteria_range 必需。 需要判断条件的第一个条件区域。

Criteria1 必须。对第一个区域中进行判断的条件1，条件可以有以下形式：

数字

文本

单元格引用

表达式，例如，”>60″

Criteria_range2, criteria2, … 可选。 其余条件区域及其关联条件。 最多可以写 127 个区域/条件对。

=COUNTIFS(B4:B11,F4,C4:C11,G4)

​

7.VLOOKUP-条件查找

函数说明：在指定单元格区域的第一列查找指定的查找值，返回所在行中指定列的值

返回值：匹配的值

语法：

=VLOOKUP(lookup_value, table_array, col_index_num,[range_lookup])

=VLOOKUP(查找值, 查询单元格区域, 列数, [匹配模式])

参数：

Lookup_value 必需。 需在指定单元格区域中查找的值。

Table_array 必需。 在其中查找数据的数组或单元格区域， 使用对区域或区域名称的引用。

Col_index_num 必需。 table_array 中将返回的匹配值的列号。

Range_lookup 可选。 一个逻辑值，指定查找精确匹配值还是近似匹配值。

如果为 TRUE（1） 或省略，则返回近似匹配值。 如果找不到精确匹配值，则返回小于 lookup_value 的最大值。

如果为 False（0），则将查找精确匹配值。

注意要点：

1、第4参数一般用0（或FASLE）以精确匹配方式进行查找。

2、第3参数中的列号，不能理解为工作表中实际的列号，而是指定返回值在查找范围中的第几列。

3、如果查找值与数据区域关键字的数据类型不一致，会返回错误值#N/A。

4、查找值必须位于查询区域中的第一列。

​=VLOOKUP(F4,A4:D11,3,0)

​

8.VLOOKUP-条件查找

函数说明：在指定单元格区域的第一列查找指定的查找值，返回所在行中指定列的值

返回值：匹配的值

语法：

=LOOKUP(1,0/((条件区域1=条件1)*(条件区域2=条件2)),查询区域)

=LOOKUP(1,0/((B4:B11=F4)*(C4:C11=G4)),A4:A11)

9.计算文本算式

如下图，要计算单元格中的文本算式，直接使用函数= EVALUATE(A4)

10.合并多个单元格内容

连接合并多个单元格中的内容，可以使用&符号完成。如下图，要合并A列的姓名和B列的手机号码，可以使用公式：

=A2&B$1&B2

​

11.合并带格式的单元格内容

合并带有格式的内容时，Excel默认按常规格式进行合并，但是如果是日期、时间或是其他有格式的数值，格式就会容易出错，这个时候可以使用TEXT 函数将数值转换为文本，并以指定格式显示，然后再合并

12.比较大小写的单词是否相同

如下图，分别在A列和C列单元格中分别输入大小写的单词，如使用=A4=B4，Excel会忽略大小写默认二者是相同的

这个时候可以使用函数=EXACT(A4,B4)判断

​

13.提取混合内容中的姓名

如下图，要从A列姓名手机中提取出姓名，除了使用高版本的自动填充功能（CTRL+E），还可以使用公式完成：

=LEFT(A4,LENB(A4)-LEN(A4))

LENB函数将每个汉字（双字节字符）的字符数按2计数

LEN函数则对所有的字符都按1计数。

因此“LENB(A2)-LEN(A2)”返回的结果就是文本字符串中的汉字个数。

LEFT函数从文本字符串的第一个字符开始，返回指定个数的字符，最终提取出员工姓名

​

14.根据身份证号码提取出生年月

计算公式为：

=1*TEXT(MID(B4,7,8),”0-00-00″)

首先使用MID函数从B4单元格的第7位开始，提取出表示出生年月的8个字符，结果为：

“19780710”

再使用TEXT函数将字符串转换为日期样式(注意这里只是字符串，不是真正的日期)：

“1978-07-10”

然后通过*1计算，将其转换为真正的日期。但是格式不是日期的格式，最后设置为日期格式即可。

​

15.替换部分电话号码

如下图所示，要将手机号码的中间四位换成星号，公式为：

=SUBSTITUTE(B4,MID(B4,4,4),”****”,1)

SUBSTITUTE函数的用法是：

SUBSTITUTE(要替换的文本,旧文本,新文本,[替换第几个])

先使用MID函数取得B列号码中的中间4位，再用“*****”替换掉这部分内容。

最后一个参数使用1，表示只替换第一次出现的内容。比如第一行的电话号码是13801010101，最后四位和中间四位相同，如果不指定1，就会全部替换掉了

16.屏蔽函数公式返回的错误值

在使用函数公式过程中，经常会返回一些诸如#N/A、#NAME?之类的错误值，要屏蔽这些错误值其实很简单，只需在原公式外侧加上一个IFERROR函数就好。

IFERROR函数的用法为：

=IFERROR(原公式,出现错误时要返回的内容)

如果公式正确，就返回原有计算结果，如果公式返回的是错误值，就返回用户指定的显示内容。

17.四舍五入函数

ROUND函数这个想必大家经常用到吧，就是对数值按指定的位数四舍五入。比如：

=ROUND(8/9,3)

就是将8/9的计算结果四舍五入到三位小数，结果为0.889

18.四舍五入函数

取整的间隔小时数

计算两个时间的间隔小时数，不足一小时部分舍去，计算加班时经常会用到

=TEXT(B2-B1,”[h]”)

19.提取日期时间中的日期值

要从日期时间数据中提取出日期，可以使用以下公式：

=INT(A4)

要继续提取时间，只需要做个减法=A4-B4，就欧了：

20.RANDBETWEEN

说明:

RANDBETWEEN 函数返回介于指定两个数之间随机整数，每次计算工作表时返回新的随机数。

返回值:

随机整数

语法:

=RANDBETWEEN(bottom, top)

=RANDBETWEEN(最小数字, 最大数字)

参数:

Bottom 必需。 最小随机整数。

Top 必需。最大随机整数。