Excel中逻辑值函数的使用方法

● 函数分类

▼表 4-1 逻辑值函数

函数名称 功能

TRUE 返回逻辑值 TRUE

FALSE 返回逻辑值 FALSE

▼表 4-2 条件判断函数

函数名称 功能

AND 判断多个条件是否同时成立

IF 根据条件判断返回不同结果

IFERROR 当公式计算结果为错误时，返回指定值；否则返回公式

计算结果

IFNA 若公式计算结果为错误，返回指定值；否则返回公式计

算结果

NOT 对参数的逻辑值求反

OR 判断多个条件中是否至少有一个条件成立

XOR 返回所有参数的"异或"值

04 逻辑函数 逻辑函数是指根据一定的条件，对数据进行处理的函数。

在条件式中使用比较运算符号指定逻辑式，使用逻辑值表示运

算结果。逻辑值是用 TRUE、FALSE 之类的特殊文本表示指定

条件是否成立。条件成立时为逻辑值 TRUE，条件不成立时为

逻辑值 FALSE。在 Excel 2013中的逻辑函数可以分为逻辑值函

数和条件判断函数两类，共 9个函数，下面介绍具体应用。

106

使用 TRUE函数，在单元格中即可显示 TRUE。

注意：

不使用 TRUE 函数，直接在单元格中输入文本 TRUE，然后

按下【Enter】键，则单元格中同样显示逻辑值 TRUE。

TRUE

返回逻辑值 TRUE

函数格式： TRUE()

参数说明： 无参数

注意事项： 该函数无参数。如果在()内制定参数，则会返回错误值。

可以在单元格或者公式中直接输入 TRUE，而不使用该函数，

Excel会自动将其转换为逻辑值 TRUE。

逻辑值

函数

案例 单元格内显示 TRUE

在 单 元 格 中 输 入 公 式

=TRUE()，按下【Enter】键即

可显示逻辑值 TRUE。

107

下面以判断两列数据是否相同为例，介绍 FALSE用法。

在单元格 D2 中输入公式

=B2=C2，然后按下【Enter】，

不同的返回 FALSE，相同的

返回 TURE。

FALSE

返回逻辑值 FALSE

函数格式： FALSE()

参数说明： 无参数

注意事项： 该函数无参数。如果在()内制定参数，则会返回错误值。可

以在单元格或者公式中直接输入 FALSE，而不使用该函数，

Excel会自动将其转换为逻辑值 FALSE。

AND

判断多个条件是否同时成立

逻辑值

函数

条件判断

函数

案例 单元格内显示 FALSE

108

判断员工的考试成绩是否为优秀，即所有的考试科目均大于

等于 90时才为优秀，只要有一科小于 90，其成绩都不是优秀。

①选中目标单元格

②单击【插入函数】

按钮，打开【函数

参数】对话框

案例 判断成绩是否为优秀

函数格式： AND(Logical1,Logocal2,…)

参数说明： Logical1：此参数为必需，是要测试的第一个条件，其计算

结果可以要测试的其他条件，其计算结果可以为 TRUE ，也可

以是 FALSE。最多可包含 255 个条件。

Logocal2…：要测试第 2~255个条件。

注意事项： 参数的计算结果必须是逻辑值 TRUE或 FALSE，或者是包

含逻辑值的数组或单元格引用。如果指定的单元格区域未包含

逻辑值，则会返回错误值#VALUE!。

如果参数为数组或单元格引用，其中的文本和空白单元格

会被 AND函数忽略。

109

③在【或选择类别】

下拉列表中选择【逻

辑】选项

④在【选择函数】列

表框中选择【AND 函

数】

⑤在【函数参数】对

话框中设置参数，然

后单击【确定】按钮

B2、C2、D2、E2中的

数据均大于等于 90，

所以显示为 TRUE

E2中的数据小于 90，

所以显示为 FALSE

110

判断成绩是否为优秀 案例

函数格式： IF(logical_test, [value_if_true], [value_if_false])

参数说明： logical_test：该参数为必需选项，计算结果为 TRUE 或

FALSE、用来测试的值或表达式。

value_if_true：该参数为可选选项，表示参数 logical_test

结果为 TRUE 时返回的值。如果参数 logical_test 的结果为

TRUE，但是省略了参数 value_if_true（即参数 logical_test后仅

有一个逗号时），IF函数将返回 0。

value_if_false：该参数同样为可选选项，表示参数

logical_test结果为 FALSE时返回的值。

如果参数 logical_test 的结果为 FALSE 并且省略参数

value_if_true，IF函数将返回 0，形如 IF(A1>60, "通过"，)，在

value_if_true参数后面保留一个逗号。

如果参数 logical_test 的结果为 FALSE 并且参数

value_if_true省略，IF函数将返回 FALSE，形如 IF(A1>60, "通

过")，在参数 value_if_true后面省略参数 value_if_false。

注意事项： IF函数最多可以嵌套 64层，创建条件复杂的表达式来进行

测试。

IF

根据条件判断返回不同的值

条件判断

函数

111

单元格区域 B3:B9 中的数据如果大于 30000，则评为优秀，

否则评为一般。

②单击【插入函数】按

钮，选择【IF】函数

①选中目标单元格

③在【函数参数】对话

框中输入参数，然后单

击【确定】按钮

④此时在编辑栏中显

示了公式，并向下填充

公式

⑤ B3 中的数据大于

3000，条件为真，所以

评定为优秀

112

条件为真且省略参数

value_if_true，在单

元格中显示为 0

在编辑栏中输入如图

所示公式并向下填充

在编辑栏中输入如图

所示公式

因为条件为真且参数

value_if_false 省略

时，所以在单元格中显

示为 FALSE

条件判断函数

IFERROR

若计算结果错误，返回指定值，否则返回公式结果

函数格式：

IFERROR(value, value_if_error)

参数说明： value：该参数为必需选项，检查是否存在错误参数。

value_if_error：该参数为必需选项，表示当计算结果发生错

误时返回的值。计算错误的类型：#N/A、#VALUE!、#REF!、

#p/0!、#NUM!、 #NAME? 或 #NULL!。

注意事项： 1. 如果两个蚕食是空单元格，则 IFERROR 将其视为空字

字符串值 ("")。

2.如果 Value 是数组公式，则 IFERROR 为 value 中指定

区域的每个单元格返回一个结果数组。

113

在下表中给出了两组数据，现在对两组数的比值进行计算，

我们可以利用 IFERROR函数检验公式是否存在错误。

案例 检验公式是否存在错误

①选中单元格 C2

②单击【插入函数】按钮

③选择【逻辑】函数

④选择【IFERROR】函数

⑤单击【确定】按钮

⑥输入参数内容

⑦单击【确定】按钮

114

利用 LOOKUP 函数可以在一些数据区域中查找某些符合条

件的数据，其具体内容我们在后面的章节进行介绍。此处我们介

绍利用 IFNA函数检验 LOOKUP函数是否存在错误。

⑧单元格 C2 中显示计

算结果

条件判断

函数

IFNA

若计算错误返回指定的值；否则返回公式的结果。

函数格式： IFNA(value, value_if_na)

参数说明： value：该参数为必需选项，是用于检查错误值 #N/A 的参

数。

value_if_na ：该参数为必需选项，表示公式计算结果为错

误值 #N/A 时要返回的值。

注意事项： 1. 若两个参数是空单元格，则 IFNA 将其视为空字符串

值 ("")。串值 ("")。

2. 如果 Value 是数组公式，则 IFNA 为 Value 中指定区

域的每个单元格以数组形式返回结果。

案例 检验 LOOKUP 函数是否存在错误

115

"LOOKUP("陈静",A2:A8),"表示在单元格区域 A2:A8中查找

姓名陈静，由于在姓名区域中不存在陈静，所以公式

"=IFNA(LOOKUP("陈静",A2:A8),"不存在")"返回指定值"不存

在"，当查找内容存在时，就返回查找内容，即 公式

"=IFNA(LOOKUP("陈琳琳",A2:A8),"不存在")"就会返回结果"陈

琳琳"。

①在单元格 A10中输

入如图所示公式，然

后按下【Enter】键

②在单元格 A11中输

入如图所示公式，然

后按下【Enter】键

NOT

对参数的逻辑值求反

条件判断

函数

函数格式： NOT(logical)

参数说明： logical ：该参数为必需条件，计算结果为 TRUE 或 FALSE

的任何值或表达式。

116

在某次考试中成绩大于等于 60分的，即可通过考试，成绩小

于 60 分，则需要参加第二次考试。在单元格 C2 中输入公式：

=NOT(>=60)，然后按下【Enter】键，并将公式填充到单元格区域

C3:C8中。

注意事项： 1.参数可以是逻辑值 TRUE 或 FALSE，或者是可以转换为

逻辑值的表达式。对于数字来说，0等价于逻辑值 FALSE，非 0

等价于逻辑值 TRUE。

2.若参数为数组或者单元格引用，NOT 函数将会忽略其中

包含的文本、空单元格。

3.若参数是直接输入的非逻辑值，NOT 函数将会返回错误

值#VALUE!。

案例 确定参加第二次考试的人员

条件判断

函数

OR

判断多个条件中是否至少有一个成立

因为单元格 B2 中的

数值大于 60，所以显

示 FALSE

因为单元格 C2 中的

数值大于 60，所以显

示 TRUE

117

在 OR 函数的参数指定多个条件中，若任意一个参数的计算

结果为 TRUE，则函数的返回值为 TRUE，如图所示。

公式 说明 结果

=OR(TRUE,TRUE…) 所有参数结果均 TRUE TRUE

=OR(FALSE,FALSE…) 所有参数结果均 FALSE FALSE

=OR(TRUE,FALSE,FALSE…) 至少有一个参数 TRUE TRUE

函数格式： OR(logical1, [logical2], ...)

参数说明： logical1：该参数为必选条件，表示要测试的第一个条件，

其结果可以是 TRUE或 FALSE。

logical2 ...：表示要检测的第 2~255个条件，其结果可以是

TRUE或 FALSE。

注意事项： 1.参数可以是逻辑值 TRUE 或 FALSE，或者是可以转换为

逻辑值的表达式。

2.如果数组或引用参数中包含文本或空白单元格，则这些值

将被忽略。

3.如果指定的区域中不包含逻辑值，则 OR 返回错误值

#VALUE!。

案例 1 判断一个以上条件是否成立

118

案例 2 判断各科成绩全部大于 90 分

①选中目标单元格

F2

②单击【插入函数】

按钮

③在【或选择类别】

下拉列表中，选择

【逻辑】选项

④在【选择函数】列

表框中，选择【OR】

函数

⑤在【函数参数】对

话框中设置参数，然

后单击【确定】按钮

119

⑥单元格 B2:E2中的

值均大于 90，所以显

示为 TRUE

⑦单元格 B4:E4中的

值均小于 90，所以显

示为 FALSE

条件判断

函数

XOR

返回所有参数的逻辑异或值

函数格式： XOR(logical1, [logical2],...)

参数说明： logical1：该参数为必选条件，表示要测试的第一个条件，

其结果可以是 TRUE或 FALSE。

logical2 ...：表示要检测的第 2~254个条件。

注意事项： 1. 参数计算结果必须为逻辑值，如 TRUE 或 FALSE，或

者为包含逻辑值的数组或引用。如果指定的区域中不包含逻辑

值，则 XOR 返回错误值 #VALUE! 。

2.如果数组或引用参数中包含文本或空白单元格，则这些值

将被忽略。

3.可以使用 XOR 数组公式检查数组中是否出现某个值。若

要输入数组公式，请按 Ctrl+Shift+Enter。

4.当 TRUE 输入的数字为奇数时，XOR 的结果为 TRUE；

当 TRUE 输入的数字为偶数时，XOR 的结果为 FALSE。

120

异或也叫半加运算，其运算法则相当于不带进位的二进制加

法：二进制下用 1 表示真，0 表示假，异或(xor)的逻辑符号"^"

形象表示为：

1 ^ 0 = 1

0 ^ 1 = 1

0 ^ 0 = 1

1 ^ 1 = 0

或者

True ^ False = True

False ^ True = True

False ^ False = False

True ^ True = False

案例 返回所有参数的逻辑异或值

①在编辑栏输入如

图所示公式，然后按

下【Enter】键

②在 A2 中显示了逻

辑值 FALSE

Left函数是指从一个文本字符串的左边第一个字符开始返回指定个数的字符。其公式是LEFT(text,num_chars)。其中text表示我们要截取的那个内容，num_chars表示要截取多少内容。

举例说明：

一、使用方法

1、要提取下表中班级学生的姓氏

2、按照中国的传统习俗，姓氏应该放在左边第一位，因此采用left方法，在H2中输入公式=left（A2,1)，由于姓氏是排在第一位的，因此后面的数字就是1。

3、点击“Enter”，就会得到班级学生的姓氏

二、注意事项

1、left(text,num_chars)，其中num_chars的值必须大于等于0.小于0时直接报错，等于0时为空值。

num_chars的值小于0

num_chars的值等于0

2、如果num_chars大于文本长度，则返回所有文本。很明显，A2：A13中每个单元格的字符都不超过3，所以当后面的数字超过3了，使用LEFT函数得到的结果还是原来的文本

（Right函数恰恰相反，小编在此就不给大家演示了，感谢大家阅读我的文章，欢迎大家批评指正）

Offset函数是 Excel 中用于返回距指定单元格或单元格区指定行数和列数区域的引用；它共有五个参数，前三个为必选项，后两个为可选项；其中第一个参数为引用单元格或单元格区域；第二、三个参数分别为行数和列数，它们不能超过表格范围，否则会返回错误；另外，行数和列数还有正负之分，取值不同，引用区域也不同。Offset函数可与Sum函数、Match函数组合使用，例如与Sum函数组合求一片选定区域的和。以下就是 Excel Offset函数的使用方法及与Sum函数组合的具体操作实例，实例中操作所用版本均为 Excel 2016。

一、Excel Offset函数语法

1、表达式：OFFSET(Reference, Rows, Cols, [Height], [Width])

中文表达示：OFFSET(引用单元格, 行数, 列数, [返回引用高度], [返回引用宽度])

2、说明：

A、Reference 是对单元格或相邻单元格的引用，否则会返回 #VALUE 错误。

B、Rows 距引用单元格左右上角或下角的行数；当 Rows 为正数时，表示距引用单元格左下角的行数；当 Rows 为负时，表示距引用单元格左上角的行数；如果指定的 Rows 超出表格范围，将返回 #REF! 错误。

C、Cols 距引用单元格左边或右边的行数；当 Cols 为正时，表示距引用单元格右边的行数；当 Cols 为负时，表示距引用单元格左边的行数。如果指定的 Cols 超出表格范围，也返回 #REF! 错误。

D、Height 和 Width 为可选项，Height 表示返回引用的高度；Width 表示返回引用的宽度；当引用一个单元格时，Height 和 Width 都为1；当引用多个单元格时，Height 和 Width 可以都指定与引用单元格同样的高度和宽度，也可指定为大于引用单元格的高度和宽度。如果省略 Height 和 Width，将返回与引用单元格同等大小的区域。

二、Excel Offset函数的使用方法及实例

（一）引用一个单元格

1、返回引用单元格左下角的指定值

A、把公式 =OFFSET(D5,2,-2,1,1) 复制到 H2 单元格，按回车，则返回“黑色T恤”，操作过程步骤，如图1所示：

B、公式说明：引用单元格为 D5；行数为 2，列数为 -2，高度和宽度都为 1，公式的意思是：返回 D5 左下角距 D5 2 行与两列的单元格的引用；符合 Rows 为正时，返回引用单元格左下角的值；Cols 为负时，返回引用单元格左边的值。返回一个单元格的值，把高度和宽度都设置为 1 或省略，否会发生错误。

2、返回引用单元格左上角的指定值

A、把上面公式 =OFFSET(D5,2,-2,1,1) 中的 2 改为 -2，就可以返回引用单元格左上角的指定值，操作过程步骤，如图2所示：

B、说明：公式改为 =OFFSET(D5,-2,-2,1,1) 后，按回车，返回“长袖白衬衫”，正是 D5 左上角单元格的值，且距 D5 2 行 2 列。

3、返回引用单元格右下角的指定值

A、把上面公式 =OFFSET(D5,2,-2,1,1) 中的 -2 改为 2，则公式变为 =OFFSET(D5,2,2,1,1)，如图3所示：

B、按回车，返回 872，正是在 D5 右下角的值且距 D5 2 行 2 列，如图4所示：

（二）对多个单元格的引用并求和

1、把公式 =SUM(OFFSET(D5:E6,2,2,2,2)) 复制到 H2 单元格，按回车，返回结果 8021，操作过程步骤，如图5所示：

2、公式说明：

A、公式先用 OFFSET(D5:E6,2,2,2,2) 选定一片单元格区域，所选定的区域为在 D5:E6 右下角且距 D5:E6 左上角的顶点 2 行 2 列，也就是 F7:G8 这四个单元格，如图6所示：

B、为什么是 F7:G8 这四个单元格？因为高度和宽度都为 2。选定单元格后，再用 Sum 求它们的和，所以结果为 8021。

3、把高度和宽度都改为 3，则公式变为 =SUM(OFFSET(D5:E6,2,2,3,3))，此时，OFFSET(D5:E6,2,2,3,3) 选中的单元格区域为 F7:H9，如图7所示：

按回车，返回结果为 10546，如图8所示：

由此可知：返回的高度和宽度不必与引用单元格 D5:E6 一致。

4、把高度和宽度都改为 -3，则公式变为 =SUM(OFFSET(D5:E6,2,2,-3,-3))，此时，OFFSET(D5:E6,2,2,-3,-3) 选中的单元格区域为 D5:F7，如图9所示：

按回车，返回结果为 5398，如图10所示：

由此可知：当高度和宽度都变为负数时，返回的单元格区域会向左移。

今天和大家分享的是Excel中countif函数的使用方法，让你彻底告别Excel中countif函数的使用方法的烦恼!

这个函数官方的解释是这样的，Countif函数是表格中对指定区域中符合指定条件的单元格计数的一个函数。

该函数的语法规则如下：

countif（需要判断的数据区域，需要判断的条件）

参数1：要计算其中非空单元格数目的区域

参数2：以数字、表达式或文本形式定义的条件

简单举一个例子：

我们需要求在数据区域大于50的值，则我们可以这么写函数公式：COUNTIF(数据区,">50")

当然啦！这个函数还支持使用通配符查询，加入查询数据区域包含AA，则可以这么写 =COUNTIF(数据区,"*AA*")

上面的介绍都是普通的用法，我们今天和大家说的是一些其他的不常规用法！

自动输入序号

=COUNTIF($B$2:B2,B2)

其实这个用法在我们工作中，也是用的非常多的。这个公式的意思就是动态统计我们不重复的值的个数。

统计指定区域单元格空格的个数

=COUNTIF(A1:A32,">= ")-COUNTIF(A1:A32,">=!")

其实这函数的结果为2，我们特意在5和6行中间增加空格，当然这个函数的这个用法我们在很多时候都可用上。

提取不重复值

如图所示，我们需要提取B列的部门不重复值的个数

{=IFERROR(INDEX(B:B,1+MATCH(,COUNTIF($H$1:H1,$B$2:$B$15),))&"","")}

这个公式其实利用的是COUNTIF统计我们的H列的值在源数据的重复的个数，然后利用match返回0和1这个值，使用index函数，进行查找计算。其实这个公式也是很简单的，只是我们有时候没有想到这里啦！

PS：需要注意到的是上面的函数公式为数组公式，同学们，记得输完公式，按CTRL+SHIFT+ENTER哦！

多条件计数

如图我们需要同时统计三个月的工资大于66666的人数

=SUM(COUNTIF(INDIRECT({"f2:f15","g2:g15","h2:h15"}),">=66666"))

函数的公式的用法，其实就是把三个区域连接起来，作为COUNTIF的参数，然后利用该函数，进行判断，最后对结果进行加总。

写在结尾：

我们学习在于点滴积累之间，循序慢进，选对了方式，水滴石穿，相反如果没有好的方式，会事半功倍。

希望以上对你有所帮助，谢谢支持！

PS:学习的苦难需要我们主动面对,生活的苦难你躺着它自己主动就过来啦!与君共勉之!!!

以上就是今天要和大家分享的技巧，希望对大家有所帮助，祝各位一天好心情！

唯有不断学习，才能不被淘汰！

Excel中每一个方法都有特定的用途，不是他们没有用处，只是你不了解或者暂时用不着，建议你收藏起来，万一哪天用着呢？

可能对于在用Excel的朋友来讲，平时使用的比较多的一个函数，那就是Vlookup函数，今天小编就来告诉大家一个Excel中最有用第一个函数，那就是Countif函数，这个函数的适用范围远远高于vlookup函数，所有把它列为最有用函数的NO.1也一点都不为过。下面我们就来学习一下这个函数。

今天教大家这个函数主要从以下6个场景来进行学习。

一对一对比两列数据多对多对比两列数据禁止重复输入输入时必须包含指定字符帮助Vlookup实现一对多查找统计不重复值的个数

:场景1：一对一核对两列数据

【例】如下图所示，要求对比A列和D列的姓名，在B和E列出哪些是相同的，哪些是不同的。

公式：

B2 =IF(COUNTIF(D:D,A2)>0,"相同","不同")

E2 =IF(COUNTIF(A:A,D2)>0,"相同","不同")

场景2：多对多核对两列数据

【例】如下面的两列数据，需要一对一的金额核对并用颜色标识出来。

步骤1 在两列数据旁添加公式，用Countif函数进行重复转化。

=COUNTIF(B$2:B2,B2)&B2

步骤2按ctrl键同时选取C和E列，开始 - 条件格式 - 突出显示单元格规则 - 重复值。

设置完成后后，红色的即为一一对应的金额，剩下的为未对应的。如下图所示

场景3：禁止重复录入

禁止在G列重复录入数据：

数据 - 有效性（2016版为数据验证） - 序列 - 输入公式

=COUNTIF(G:G,G1)=1

场景4：输入内容必须包括指定字符

【例】在列输入的内容，必须包含字母A。

=COUNTIF(H1,"*A*")=1

:如果输入不含A的字符就会警示并无法输入

场景5：帮助Vlookup函数实现一对多查找

【例】如下图所示左表为客户消费明细，要求在F:H列的蓝色区域根据F2的客户名称查找所有消费记录。

:步骤1 在左表前插入一列并设置公式，用countif函数统计客户的消费次数并用&连接成 客户名称+序号的形式。

A2: =COUNTIF(C$2:C2,C2)&C2

步骤2 在F5设置公式并复制即可得到F2单元格中客户的所有消费记录。

=IFERROR(VLOOKUP(ROW(A1)&$F$2,$A:$D,COLUMN(B1),0),"")

场景6：计算唯一值个数

【例】统计A列产品的个数

=SUMPRODUCT(1/COUNTIF(A2:A7,A2:A7))

总结：总的来讲Countif函数虽然只是单一条件的计数函数，但它在与其他函数搭配使用的时候，那他的作用将会变的无限大。所以说大家可以多去学习了解一下更多的函数嵌套的使用方法。

excel中rank函数的使用方法

1

在平时生活或者办公中，我们常常会用到excel表格，在数字排序的时候，除了用排序按钮，我们还可以使用rank函数的方法来排序，而且更简单更智能。下面就教大家怎么使用rank函数。

1、为举例子，我就简单以下图为例。先在excel中输入一些数据，不用排好序，随便打乱。

2、在B2单元格中输入以下公式：=RANK(A2,A2:A14);A2表示从第二行开始确定位次，A2:A14表示数据范围，这里我只有14行数据。

3、然后点击回车键，这个时候就会显示该数据在整个数据里面所排的大小顺序。

4、接着将鼠标放在单元格右下角，鼠标变成小十字的时候，按下鼠标左键不放，往下拉。

5、知道整个数据拉完，才放开鼠标左键，这个时候整个数据的排名就出来了。这就是excel中rank函数的使用方法。

在 Excel 中，Match函数用于返回查找值在数组的位置，它共有三个参数，分别为 lookup_value、lookup_array 和 match_type；其中 match_type 有三个参数，即 1、0、-1，当取不同值时返回不同的结果，其中取 0 时，可用通配符问号（?）和星号（*）进行模糊查找。Match函数除单独使用外，还常常与Index函数一起使用。以下就是Match函数的使用方法及与Index组合使用实例的具体操作方法，实例中操作所用版本均为 Excel 2016。

一、Excel Match函数语法

1、表达式：MATCH(lookup_value, lookup_array, [match_type])

中文表达式：MATCH(查找值, 查找数组, [查找类型])

2、说明：

A、Match函数用于返回 lookup_value 在数组中的位置；lookup_value 和 lookup_array 是必选项，match_type 是可选项；如果省略 match_type，则默认值为 1。

B、match_type 的取值说明：取 1 表示查找小于或等于查找值 lookup_value 的最大值，返回该值在数组中的位置，要求数组按升序排序；取 0 表示查找等于 lookup_value 的第一个值，返回该值在数组中的位置，不要求数组按升序或降序排序；取 -1 表示查找大于或等于 lookup_value 的最小值，返回该值在数组中的位置，要求数组按降序排序。

C、当 match_type 为 0 且 lookup_value 是文本，可在 lookup_value 中使用通配符“问号 (?) 和星号 (*)”；问号表示任意一个字符，星号表示任意一个或一串字符；如果要查找问号或星号，需要在它们前加转义波形符（~）。

二、Excel Match函数的使用方法

（一）match_type 取 1（或省略）的使用实例

1、假如要查找服装销量表中价格为 80 与 95 元在 E 列中的位置。首先把 E 列按升序排序，框选 E2:E12，选择“数据”选项卡，单击“升序”，在弹出的“排序提醒”窗口中，选择“扩展选定区域”，单击“确定”，则表格记录按价格升序排序；在 G2 中输入公式 =MATCH(80,E2:E12)，按回车，则返回 80 在 E2:E12 中的位置 2；再在 G3 中输入公式 =MATCH(95,E2:E12,1)，按回车，则返回 5，操作过程步骤，如图1所示：

2、说明：公式 =MATCH(80,E2:E12) 和 =MATCH(95,E2:E12,1)，一个省略 1，另一个未省略，都表示查找小于或等于 lookup_value 的最大值；80 在 E2:E12 中有，因此返回它所在的位置 2，95 在 E2:E12 中没有，所以返回小于它的最大值 89 所在位置，E2:E12 中有两个 89，返回的是第二个 89 的位置。

（二）match_type 取 0 的使用实例

1、查找数值

A、假如要查找价格为 89 元在 E 列中的位置。把公式 =MATCH(89,E2:E12,0) 复制到 G2 单元格，按回车，返回结果为 4，正是 89 在 E2:E12 中位置，操作过程步骤，如图2所示：

B、当 match_type 取 0 时，数组不用排序。如果查找数组中没有的值，则会返回 #N/A 错误，例如查找 87，如图3所示：

按回车后，返回 #N/A 错误，如图4所示：

2、用通配符问号（?）与星号（*）

A、假如要查找以“T恤”结尾的服装在 B 列中的位置。把公式 =MATCH("*T恤",B2:B12,0) 复制到 G2 单元格，按回车，返回结果 2，正是“绿色T恤”在 B2:B12 中的位置（返回的是第一个以“T恤”结束的服装在 B2:B12 中的位置），操作过程步骤，如图5所示：

B、假如要查找只有三个字的服装在 B 列中的位置。把公式 =MATCH("???",B2:B12,0) 复制到 G2 单元格，如图6所示：

按回车，返回结果 10，正是“白衬衫”在 B2:B12 中的位置，如图7所示：

（三）match_type 取 -1 的使用实例

1、假如要查找销量为 681 与 750 在 E 列中的位置。框选 F2:F12，选择“数据”选项卡，单击“降序”，在打开“排序提醒”窗口中，选择“扩展选项区域”，单击“确定”，将记录按销量降序排列；把公式 =MATCH(681,F2:F12,-1) 复制到 G2 单元格，按回车，返回结果为 5，正是 681 在 F:F12 中位置；把 681 改为 750，公式变为 =MATCH(750,F2:F12,-1)，按回车，结果为 4，操作过程步骤，如图8所示：

B、说明：第二个公式 =MATCH(750,F2:F12,-1) 要查找 750，由于 F2:F12 中没有这个值，因此返回大于或等于 750 的最大值 874 在 F2:F12 中的位置，所以结果为 4。

三、Excel Index 与 Match 组合使用实例

1、假如要查找价格为 65 元的服装的编号。把公式 =INDEX(A:A,MATCH(65,E1:E12,0)) 复制到 G2 单元格，按回车，返回结果为 WS-585，正是价格为 65 元的服装编号；操作过程步骤，如图9所示：

B、公式说明：

公式 =INDEX(A:A,MATCH(65,E1:E12,0)) 先用 MATCH(65,E1:E12,0) 返回价格为 65 元的服装在 E 列中的位置，结果为 8；再用Index函数返回行号为 8 的服装编号。

index函数的表达式为：INDEX(array, row_num, [column_num])，第一个参数为数组，第二个参数为行号，第三个参数为列号（可选）；示例中省略了列号，因此只返行号为的服装编号。如果既有行号又有列号，则返回行号与列号对应的值。

Excel曾经一度出现了严重Bug，主要有两种比较悲催的情况，首先是这种：

更加悲催的是这种：

言归正传，今天和大家分享一组常用函数公式的使用方法：职场人士必须掌握的12个Excel函数，用心掌握这些函数，工作效率就会有质的提升。

建议收藏备用着，有时间多学习操练下。

目录：数字处理、判断公式、统计公式、求和公式、查找与引用公式、字符串处理公式、其他常用公式等

一、数字处理

01.取绝对值

=ABS(数字)

02.数字取整

=INT(数字)

03.数字四舍五入

=ROUND(数字,小数位数)

二、判断公式

04.把公式返回的错误值显示为空

公式：C2

=IFERROR(A2/B2,"")

说明：如果是错误值则显示为空，否则正常显示。

05.IF的多条件判断

公式：C2

=IF(AND(A2<500,B2="未到期"),"补款","")

说明：两个条件同时成立用AND,任一个成立用OR函数。

三、统计公式

06.统计两表重复

公式:B2

=COUNTIF(Sheet15!A:A,A2)

说明：如果返回值大于0说明在另一个表中存在，0则不存在。

07.统计年龄在30~40之间的员工个数

=FREQUENCY(D2:D8,{40,29})

08.统计不重复的总人数

公式：C2

=SUMPRODUCT(1/COUNTIF(A2:A8,A2:A8))

说明:用COUNTIF统计出每人的出现次数，用1除的方式把出现次数变成分母，然后相加。

09.按多条件统计平均值

F2公式

=AVERAGEIFS(D:D,B:B,"财务",C:C,"大专")

10.中国式排名公式

=SUMPRODUCT(($D$4:$D$9>=D4)*(1/COUNTIF(D$4:D$9,D$4:D$9)))

四、求和公式

11.隔列求和

公式：H3

=SUMIF($A$2:$G$2,H$2,A3:G3)

或

=SUMPRODUCT((MOD(COLUMN(B3:G3),2)=0)*B3:G3)

说明：如果标题行没有规则用第2个公式

12.单条件求和

公式：F2

=SUMIF(A:A,E2,C:C)

说明：SUMIF函数的基本用法

13.单条件模糊求和

公式：详见下图

说明：如果需要进行模糊求和，就需要掌握通配符的使用，其中星号是表示任意多个字符，如"*A*"就表示a前和后有任意多个字符，即包含A。

14.多条求模糊求和

公式：C11

=SUMIFS(C2:C7,A2:A7,A11&"*",B2:B7,B11)

说明：在sumifs中可以使用通配符*

15.多表相同位置求和

公式：b2

=SUM(Sheet1:Sheet19!B2)

说明：在表中间删除或添加表后，公式结果会自动更新。

16.按日期和产品求和

公式：F2

=SUMPRODUCT((MONTH($A$2:$A$25)=F$1)*($B$2:$B$25=$E2)*$C$2:$C$25)

说明：SUMPRODUCT可以完成多条件求和

五、查找与引用公式

17.单条件查找

公式1：C11

=VLOOKUP(B11,B3:F7,4,FALSE)

说明：查找是VLOOKUP最擅长的，基本用法

18.双向查找

公式：

=INDEX(C3:H7,MATCH(B10,B3:B7,0),MATCH(C10,C2:H2,0))

说明：利用MATCH函数查找位置，用INDEX函数取值

19.查找最后一个符合条件记录

公式：详见下图

说明：0/(条件)可以把不符合条件的变成错误值，而lookup可以忽略错误值

20.多条件查找

公式:详见下图

说明:公式原理同上一个公式

21.指定非空区域最后一个值查找

公式;详见下图

说明：略

22.区间取值

公式：详见下图

公式说明：VLOOKUP和LOOKUP函数都可以按区间取值，一定要注意，销售量列的数字一定要升序排列。

六、字符串处理公式

23.多单元格字符合并

公式：c2

=PHONETIC(A2:A7)

说明：Phonetic函数只能对字符型内容合并，数字不可以。

24.截取除后3位之外的部分

公式：

=LEFT(D1,LEN(D1)-3)

说明：LEN计算出总长度,LEFT从左边截总长度-3个

25.截取 - 之前的部分

公式:B2

=Left(A1,FIND("-",A1)-1)

说明：用FIND函数查找位置，用LEFT截取。

26.截取字符串中任一段

公式:B1

=TRIM(MID(SUBSTITUTE($A1," ",REPT(" ",20)),20,20))

说明:公式是利用强插N个空字符的方式进行截取

27.字符串查找

公式：B2

=IF(COUNT(FIND("河南",A2))=0,"否","是")

说明: FIND查找成功，返回字符的位置，否则返回错误值，而COUNT可以统计出数字的个数，这里可以用来判断查找是否成功。

28.字符串查找一对多

公式：B2

=IF(COUNT(FIND({"辽宁","黑龙江","吉林"},A2))=0,"其他","东北")

说明：设置FIND第一个参数为常量数组，用COUNT函数统计FIND查找结果

七、日期计算公式

29.两日期间隔的年、月、日计算

A1是开始日期（2011-12-1），B1是结束日期(2013-6-10)。计算：

相隔多少天？=datedif(A1,B1,"d") 结果：557

相隔多少月? =datedif(A1,B1,"m") 结果：18

相隔多少年? =datedif(A1,B1,"Y") 结果：1

不考虑年相隔多少月？=datedif(A1,B1,"Ym") 结果：6

不考虑年相隔多少天？=datedif(A1,B1,"YD") 结果：192

不考虑年月相隔多少天？=datedif(A1,B1,"MD") 结果：9

datedif函数第3个参数说明：

"Y" 时间段中的整年数。

"M" 时间段中的整月数。

"D" 时间段中的天数。

"MD" 天数的差。忽略日期中的月和年。

"YM" 月数的差。忽略日期中的日和年。

"YD" 天数的差。忽略日期中的年。

30.扣除周末的工作日天数

公式：C2

=NETWORKDAYS.INTL(IF(B2

说明：返回两个日期之间的所有工作日数，使用参数指示哪些天是周末，以及有多少天是周末。周末和任何指定为假期的日期不被视为工作日

八、其他常用公式

31.创建工作表目录的公式

把所有的工作表名称列出来，然后自动添加超链接，管理工作表就非常方便了。

使用方法：

第1步：在定义名称中输入公式：

=MID(GET.WORKBOOK(1),FIND("]",GET.WORKBOOK(1))+1,99)&T(NOW())

第2步、在工作表中输入公式并拖动，工作表列表和超链接已自动添加

=IFERROR(HYPERLINK("#'"&INDEX(Shname,ROW(A1))&"'!A1",INDEX(Shname,ROW(A1))),"")

32.中英文互译公式

=FILTERXML(WEBSERVICE("http://fanyi.youdao/translate?&i="&A2&"&doctype=xml&version"),"//translation")

excel中的函数公式千变万化，今天就整理这么多了。如果你能掌握一半，在工作中也基本上遇到不难题了。

建议收藏备用着，有时间多学习操练下。

vlookup函数是excel表格中高级的用法，通过vlookup函数我们可以调用符合条件的数据，在大量调用时可以节省我们查找复制excel数据的时间，今天我就教下大家vlookup函数的使用方法吧。

vlookup函数的使用方法

如图我准备了一张员工入职时间表，员工有非常多，如果我要在这里面一一找出张三李四王五等人的入职时间的话，可以通过查找黏贴的方式，但是这样的效率就很低了，特别是要找的人多的话，那使用vlookup函数是最简单的方法。

接下来我们就需要根据vlookup函数的公式来做调整，使vlookup函数的基本公式变成我们excel表格需要的公式，vlookup函数的语法结构是VLOOKUP(lookup_value,table_array, col_index_num, [range_lookup])。

如我们的表格，我们要查找的是人名，那么对应的lookup_value就是D2张三，而table_array查找区域就是A2到B14这个区域，那么table_array就写$A$2:$B$14。

接下来我们再看col_index_num，这个就是返回值代表在查找区域中返回哪一列的数据，而入职时间在第二列，所以这里就填写2就可以了。而[range_lookup]代表精确查找，我们输入0就可以了。

这样子我们就限定了E2是取A2到B14这个区域的入职时间，那么我们输入完公式后，直接按回车键运行一下，就可以取到姓名为张三的入职时间了。

取完一个正确的数据后，我们要把公式应用到下方的单元格中，只需要将鼠标移动到E2单元格的右下角，鼠标符号变成十字加号后按住鼠标左键往下拉就可以了。

这样vlookup函数的使用方法就教大家了，是不是觉得很简单，只要记好vlookup函数的公式，大家都能成为excel高手。

霸气走火就是我，我就是百度经验作者：ixlxt7Xzsl7，百度经验和百家号首发原创内容分享。

前期我们讲了

满足某个条件下求出所需值的if；

两个条件及以上只要满足其中1个条件即可求出所需值的or；

今天，给大家介绍的是同时满足两个及以上的条件求出所需值的and

视频讲解

文字详解

案例：每门课程都在80分及以上值为"TRUE"，反之"FALSE"。

公式使用：AND(B3>=80,C3>=80)

鼠标放置D3单元格右下角，变为黑色十字，双击往下自动填充公式

结果：

以上就是and函数的用法，有什么不明白的地方欢迎在下方留言，我们将及时给你答复~~

下期你最想学习哪个函数

也欢迎在底下给我们留言