Excel 十大必须掌握的函数 精选 请珍藏以备不时之需

1. 逻辑判断函数：IF AND OR

广泛应用于有分支的情况，满足情况1时返回值A，满足情况2时返回值B。例如 语文成绩在70分-80分时设置一朵花，80分-90分时设置两朵花，90分-100分时设置三朵花

函数说明：

IF(判断条件，条件满足时的返回值，条件不满足时的返回值)

示例：

IF(G4>=5000,"有折扣","没有折扣"): 含税金额RMB在5000以上返回有折扣，否则返回没有折扣

其中G2>=5000这一段为逻辑表达式，如果成立的话，在是否有折扣列中 返回有折扣，不成立的话，返回没有折扣.

扩展：如果逻辑表达式里面包含有多个条件怎么办？我们可以使用逻辑表达式中的AND, OR等，

这里再举一个例子，如果币种为人民币并且含税金额大于1000，则设置为1，否则设置为0.

结果：IF(AND([@币种]="CNY",[@含税金额]>1000),1,0)

2. 求和以及按条件求和函数SUM, SUMIF, SUMIFS

广泛用于对某列或者某行的数值进行合计，或者按照条件合计，即满足条件的数值才进行合计。例如统计 花费在100元以上的交易的总花费是多少

函数说明：

SUM(求和区域)：对指定的所有单元格求和

SUMIF(作为条件的区域，作为条件区域需要满足的条件，求和的区域)：对指定单元格区域中，满足条件的单元格求和，只有一个条件

SUMIFS(求和的区域, 作为条件的区域1，作为条件区域1需要满足的条件, 作为条件的区域2，作为条件区域2需要满足的条件...)：对指定单元格区域中，满足条件的单元格求和，可以有多个条件

注意：[作为条件区域需要满足的条件] 参数，可以为某个单元格，或者类似 ">=10" "

SUMIFS函数：

SUMIFS(G2:G16, C2:C16, ">=10", F2:F16, J2)，返回一次性采购数量在10以上，并且币种为CNY的含税金额（RMB）合计

3. 统计个数 以及 统计满足条件的个数：COUNTIF, COUNTIFS, COUNT, COUNTA, COUNTBLANK

广泛用于统计某列或者某行的个数，或者统计满足条件的个数，例如总成绩大于80分的学生个数。

函数说明：

COUNTIF(作为条件的区域，作为条件区域需要满足的条件)： 对指定单元格区域中，满足条件的单元格个数，只有一个条件

COUNTIFS(作为条件的区域1，作为条件区域需要满足的条件1，作为条件的区域2，作为条件区域需要满足的条件2...)：对指定单元格区域中，满足条件的单元格个数，可以有多个条件

COUNT（求和区域）: 计算区域内包含数字的个数

COUNTA（求和区域）：计算区域内非空的个数

COUNTBLANK（求和区域）：计算区域内空的个数

示例：

COUNTIF函数：

COUNTIF(C2:C16, ">=10"), 返回 一次性采购数量在10以上的个数。

COUNTIFS函数：

COUNTIFS(C2:C16,">=10", F2:F16, J2)，返回 一次性采购数量在10以上，币种为CNY的个数。

4. 查询主档案信息 根据某个关键字查询对应记录的详细信息 VLookup, lookup

这个查询函数用途非常广泛，出镜率非常高，常用于根据某些值去其他地方定义的数据档案中查找对应的其他信息，例如根据学号查成绩，根据身份证查籍贯 年龄等。

Vlookup(要查询的字段，查询区域，返回查询区域的第几列，查找方式 0:精确查找 1:非精确查找):

到参数2指定的查询区域中的第一列中，查询参数1指定的字段值，查到了就返回 查询区域中 参数3指定的第几列。参数4 指定是精确查找还是非精确查找，如果为非精确查找，就会找到最接近于查询字段值的那个值（字符串为模糊搜索，数字的话为最接近这个数字）。

如果查到多行，也只返回从上到下第一个找到的。只能实现在指定区域中从左到右查找。

示例：

Vlookup函数：

VLOOKUP(F2, K$2:L$3, 2), 根据币种去取对应人民币的汇率。

注意：这里查询区域要设置为K$2:L$3, 不能设置为K2:L3，不然在该列往下复制到其他行的时候查询区域也会跟着变，会导致结果异常。

根据币种查找汇率

LookUp函数：

有以下两种基本的形式

数组型查找 LOOKUP（要查找的值，查找区域）：从参数2指定的查询区域中的第一列中 查找参数1指定的值，找到后，返回查找区域的最后一列值。如果查到多行，也只返回从上到下第一个找到的。这个函数只能实现从左到右查询。

我们还是用上面的例子（根据币种查找汇率），结果为：LOOKUP(F2, K$2:L$3)

这个函数局限性很大，使用上还不如Vlookup灵活，都可以用VLookup替代，建议不要使用。

那如何实现从右到左逆向查询呢？我们可以用到Looup函数的第二种形式。

向量型查找 LOOKUP（要查找的值，查找区域， 结果区域）：

从参数2指定的查询区域中的第一列中 查找参数1指定的值，找到后，返回参数3指定的结果区域中的结果。如果查到多行，也只返回从上到下第一个找到的。结果区域可以位于查找区域的左边。

示例：

LOOKUP(F2, K$2:K$3, J$2:J$3)： 根据参数1指定的币种，去参数2指定的区域中的第一列中查找，查找到后，返回参数3指定的结果。

扩展：

Lookup还有一种很经典的用法，可以根据多个条件正向或者逆向查询。

Lookup(1, 0/(判断条件1)*(判断条件2)...(判断条件N), 结果区域): 根据参数2指定的多个条件，返回参数3的结果区域。

LOOKUP(1, 0/(C2>=K$8:K$11)*(G2>=L$8:L$11), J$8:J$11)： 根据采购数量和含税金额RMB去找相应的优惠折扣。(C2>=K$8:K$11)作为条件1表示采购数量大于折扣表中的采购数量，(G2>=L$8:L$11)作为条件2表示含税金额RMB大于折扣表中含税金额RMB，J$8:J$11表示返回的结果集。从示例中知道，在折扣表中会找到多行的情况，默认返回找到的最后一行，也就是最下面一行。这个和前面说的VLookup和Lookup函数的两种基本形式是不一样的，因为它们返回找到的第一条记录，也就是最上面的记录。

5. 字符处理函数 find Mid Len，用于字符串截取，比如把家庭住址截取成大地址和明细地址

函数说明：

find(要查询的关键字，从哪个目的字符串中查询，从目的字符串中的第几位开始查): 查询关键字在目的字符串的位置

Mid(要截取的字符串， 起始位置， 截取多少位字符）：从字符串的第几位开始，截取多少位字符，返回截取后的字符串

Len(字符串): 返回字符串有多少个字符

示例：

MID(A2, 1, FIND("号", A2))： 从地址中取得具体住在多少路多少号。

思路：先获取"号"字符在目的字符串A2的位置，然后从第一位开始截取到"号"的位置。

MID(A2, FIND("号", A2) + 1, LEN(A2) - FIND("号", A2))： 从地址中取得具体住在哪个房间。

思路：从号的位置+1位置开始截取，一直到最后。当然，也可以用RIGHT(A2, LEN(A2) - FIND("号", A2))来替代，是一个意思。

当然上面两个公式可以分别被下面两个函数替代：

LEFT(A2, FIND("号", A2))

RIGHT(A2, LEN(A2) - FIND("号", A2))

但是最为灵活的还是Mid函数，可以截取任意位置的字符。而left和right只能截取最左方的字符和最右方的字符。

6. 日期处理函数

示例：

获取交易日期的 年: Year(date1)，季度，月: Month(date1)，日: Day(date1), 该月最后一天: EODate(date1, monthNum)，处于周几: WeekDay(date1)， 周数: WeekNum(date1)， 下一个工作日: WorkDay(date1, Daynum)。

7. 格式转换函数 Text，例如将八位字符串转化为日期

函数说明：

Text(需要转换的字符串， 需要的格式)

示例：

将身份证上的出生年月转化为日期格式，并算出对应年龄

--TEXT(MID(A2, 7, 8), "00-00-00")： 先截取身份证的出生日期，然后通过Text转换函数转换为日期格式的字符串，然后在前面加上--，就能转换为真正的日期格式

DATEDIF(B2, TODAY(), "Y")： 该函数计算 Today()也就是今日 和 出生年月 差了多少年，其中第三个参数“Y”指定的是要计算相差多少年 , 如果要计算相差多少月时可以把第三个参数换成"M"。

Text函数，不仅可以把字符串转为日期格式，还可以做数值格式转换等等各类转换。

8. 区段分布函数 Frequency 例如计算学生的成绩区段分布

函数说明：

Frequency(源数据数组区域， 区段数组区域)： 返回一个数组，表示参数1指定的源数据数组区域中，落于参数2指定的各区段的频度分布。

示例：

例如 计算落于各个采购数量区段中的采购订单行数量。

注意：该函数返回的是一个数组，所以在编写函数之前，先要选择返回值落在哪个区域，而不是单单选择一个单元格。然后函数编写好之后，需要一起按Ctrl + shift + Enter，数组返回值才会设置到选择的区域中。

9. Offset函数

函数说明：

Offset（参照单元格，行偏移量，列偏移量，返回几行，返回几列）：参考单元格 按参数2指定的行偏移量在行上偏移，以及按参数3指定的列偏移量在列上偏移后，返回参数4和5指定的单元格区域。

示例：

获取最后三笔订单行的采购订单号，行号，数量，单价

结果：OFFSET($A$1, COUNTA($A:$A) - 3, 0, 3, 4)

首先，选择结果区域，编写公式。

公式中，COUNTA($A:$A) 计算A列中非空单元格有多少，返回值应该为16

然后将$A$1单元格，行偏移13位，列偏移0位，也就是到了$A$14的位置。最后返回 参数4和参数5指定的3行4列的单元格。

公式完成后，需要同时按住 Ctrl + Shift +Enter，才能将返回的数组返回到选择的区域中。

10. 四舍五入

函数说明：

round: 四舍五入

rounddown: 向下取整

roundup: 向上取整

在这一篇中，虽然说是列举十个经常使用的函数，其实因为相关函数都列进来了，所以这次讲的函数远远超过十个，因为都是经常使用的函数，希望朋友们多练练，有什么不明白的，请关注后私信联系。

最后总结下今天讲的函数：

逻辑判断函数 IF AND OR

求和以及按条件求和函数SUM, SUMIF, SUMIFS

统计个数 以及 统计满足条件的个数：COUNTIF, COUNTIFS, COUNT, COUNTA, COUNTBLANK

信息查询函数 VLookup, lookup

字符处理函数 find Mid Len

日期处理函数 年: Year(date1)，季度，月: Month(date1)，日: Day(date1), 该月最后一天: EODate(date1, monthNum)，处于周几: WeekDay(date1)， 周数: WeekNum(date1)， 下一个工作日: WorkDay(date1, Daynum)

格式转换函数 Text

区段分布函数 Frequency

区域位移函数 Offset

四舍五入函数 round, rounddown, roundup

希望大家可以收藏一下，以备不时之需。