Excel函数IF函数和VLOOKUP函数.docx

Excel函数IF函数和VLOOKUP函数.docx

《Excel函数IF函数和VLOOKUP函数.docx》由会员分享，可在线阅读，更多相关《Excel函数IF函数和VLOOKUP函数.docx（15页珍藏版）》请在冰点文库上搜索。

Excel函数IF函数和VLOOKUP函数

一、IF函数

（一）IF函数说明

IF函数用于执行真假值判断后，根据逻辑测试的真假值返回不同的结果，因此If函数也称之为条件函数。

它的应用很广泛，可以使用函数IF对数值和公式进行条件检测。

它的语法为IF（logical_test,value_if_true,value_if_false）。

其中Logical_test表示计算结果为TRUE或FALSE的任意值或表达式。

本参数可使用任何比较运算符。

Value_if_true显示在logical_test为TRUE时返回的值，Value_if_true也可以是其他公式。

Value_if_falselogical_test为FALSE时返回的值。

Value_if_false也可以是其他公式。

简言之，如果第一个参数logical_test返回的结果为真的话，则执行第二个参数Value_if_true的结果，否则执行第三个参数Value_if_false的结果。

IF函数可以嵌套七层，用value_if_false及value_if_true参数可以构造复杂的检测条件。

Excel还提供了可根据某一条件来分析数据的其他函数。

例如，如果要计算单元格区域中某个文本串或数字出现的次数，则可使用COUNTIF工作表函数。

如果要根据单元格区域中的某一文本串或数字求和，则可使用SUMIF工作表函数。

（二）IF函数应用

1、输出带有公式的空白表单

图5人事分析表1

以图中所示的人事状况分析表为例，由于各部门关于人员的组成情况的数据尚未填写，在总计栏（以单元格G5为例）公式为：

=SUM（C5:

F5）

我们看到计算为0的结果。

如果这样的表格打印出来就页面的美观来看显示是不令人满意的。

是否有办法去掉总计栏中的0呢？

你可能会说，不写公式不就行了。

当然这是一个办法，但是，如果我们利用了IF函数的话，也可以在写公式的情况下，同样不显示这些0。

如何实现呢？

只需将总计栏中的公式（仅以单元格G5为例）改写成：

=IF（SUM（C5:

F5）,SUM（C5:

F5）,""）

通俗的解释就是：

如果SUM（C5:

F5）不等于零，则在单元格中显示SUM（C5:

F5）的结果，否则显示字符串。

几点说明：

（1）SUM（C5:

F5）不等于零的正规写法是SUM（C5:

F5）<>0，在EXCEL中可以省略<>0；

（2）""表示字符串的内容为空，因此执行的结果是在单元格中不显示任何字符。

图4

2、不同的条件返回不同的结果

如果对上述例子有了很好的理解后，我们就很容易将IF函数应用到更广泛的领域。

比如，在成绩表中根据不同的成绩区分合格与不合格。

现在我们就以某班级的英语成绩为例具体说明用法。

图6

某班级的成绩如图6所示，为了做出最终的综合评定，我们设定按照平均分判断该学生成绩是否合格的规则。

如果各科平均分超过60分则认为是合格的，否则记作不合格。

根据这一规则，我们在综合评定中写公式（以单元格B12为例）：

=IF（B11>60,"合格","不合格"）

语法解释为，如果单元格B11的值大于60，则执行第二个参数即在单元格B12中显示合格字样，否则执行第三个参数即在单元格B12中显示不合格字样。

在综合评定栏中可以看到由于C列的同学各科平均分为54分，综合评定为不合格。

其余均为合格。

3、多层嵌套函数的应用

在上述的例子中，我们只是将成绩简单区分为合格与不合格，在实际应用中，成绩通常是有多个等级的，比如优、良、中、及格、不及格等。

有办法一次性区分吗？

可以使用多层嵌套的办法来实现。

仍以上例为例，我们设定综合评定的规则为当各科平均分超过90时，评定为优秀。

如图7所示。

图7

说明：

为了解释起来比较方便，我们在这里仅做两重嵌套的示例，您可以按照实际情况进行更多重的嵌套，但请注意Excel的IF函数最多允许七重嵌套。

根据这一规则，我们在综合评定中写公式（以单元格F12为例）：

=IF（F11>60,IF（AND（F11>90）,"优秀","合格"）,"不合格"）

语法解释为，如果单元格F11的值大于60，则执行第二个参数，在这里为嵌套函数，继续判断单元格F11的值是否大于90（为了让大家体会一下AND函数的应用，写成AND（F11>90），实际上可以仅写F11>90），如果满足在单元格F12中显示优秀字样，不满足显示合格字样，如果F11的值以上条件都不满足，则执行第三个参数即在单元格F12中显示不合格字样。

在综合评定栏中可以看到由于F列的同学各科平均分为92分，综合评定为优秀。

（三）根据条件计算值

在了解了IF函数的使用方法后，我们再来看看与之类似的Excel提供的可根据某一条件来分析数据的其他函数。

例如，如果要计算单元格区域中某个文本串或数字出现的次数，则可使用COUNTIF工作表函数。

如果要根据单元格区域中的某一文本串或数字求和，则可使用SUMIF工作表函数。

关于SUMIF函数在数学与三角函数中以做了较为详细的介绍。

这里重点介绍COUNTIF的应用。

COUNTIF可以用来计算给定区域内满足特定条件的单元格的数目。

比如在成绩表中计算每位学生取得优秀成绩的课程数。

在工资表中求出所有基本工资在2000元以上的员工数。

语法形式为COUNTIF（range,criteria）。

其中Range为需要计算其中满足条件的单元格数目的单元格区域。

Criteria确定哪些单元格将被计算在内的条件，其形式可以为数字、表达式或文本。

例如，条件可以表示为32、"32"、">32"、"apples"。

1、成绩表

这里仍以上述成绩表的例子说明一些应用方法。

我们需要计算的是：

每位学生取得优秀成绩的课程数。

规则为成绩大于90分记做优秀。

如图8所示

图8

根据这一规则，我们在优秀门数中写公式（以单元格B13为例）：

=COUNTIF（B4:

B10,">90"）

语法解释为，计算B4到B10这个范围，即jarry的各科成绩中有多少个数值大于90的单元格。

在优秀门数栏中可以看到jarry的优秀门数为两门。

其他人也可以依次看到。

2、销售业绩表

销售业绩表可能是综合运用IF、SUMIF、COUNTIF非常典型的示例。

比如，可能希望计算销售人员的订单数，然后汇总每个销售人员的销售额，并且根据总发货量决定每次销售应获得的奖金。

原始数据表如图9所示（原始数据是以流水单形式列出的，即按订单号排列）

图9原始数据表

按销售人员汇总表如图10所示

图10销售人员汇总表

如图10所示的表完全是利用函数计算的方法自动汇总的数据。

首先建立一个按照销售人员汇总的表单样式，如图所示。

然后分别计算订单数、订单总额、销售奖金。

（1）订单数--用COUNTIF计算销售人员的订单数。

以销售人员ANNIE的订单数公式为例。

公式：

=COUNTIF（$C$2:

$C$13,A17）

语法解释为计算单元格A17（即销售人员ANNIE）在"销售人员"清单$C$2:

$C$13的范围内（即图9所示的原始数据表）出现的次数。

这个出现的次数即可认为是该销售人员ANNIE的订单数。

（2）订单总额--用SUMIF汇总每个销售人员的销售额。

以销售人员ANNIE的订单总额公式为例。

公式：

=SUMIF（$C$2:

$C$13,A17,$B$2:

$B$13）

此公式在"销售人员"清单$C$2:

$C$13中检查单元格A17中的文本（即销售人员ANNIE），然后计算"订单金额"列（$B$2:

$B$13）中相应量的和。

这个相应量的和就是销售人员ANNIE的订单总额。

（3）销售奖金--用IF根据订单总额决定每次销售应获得的奖金。

假定公司的销售奖金规则为当订单总额超过5万元时，奖励幅度为百分之十五，否则为百分之十。

根据这一规则仍以销售人员ANNIE为例说明。

公式为：

=IF（C17<50000,10%,15%）*C17

如果订单总额小于50000则奖金为10%；如果订单总额大于等于50000，则奖金为15%。

至此，我们已完全了解了EXCEL函数的逻辑函数，相信大家在实际工作中会想出更多更有用的运用。

二、HLOOKUP、LOOKUP、MATCH、VLOOKUP

1、LOOKUP函数与MATCH函数

LOOKUP函数可以返回向量（单行区域或单列区域）或数组中的数值。

此系列函数用于在表格或数值数组的首行查找指定的数值，并由此返回表格或数组当前列中指定行处的数值。

当比较值位于数据表的首行，并且要查找下面给定行中的数据时，使用函数HLOOKUP。

当比较值位于要进行数据查找的左边一列时，使用函数VLOOKUP。

如果需要找出匹配元素的位置而不是匹配元素本身，则应该使用函数MATCH而不是函数LOOKUP。

MATCH函数用来返回在指定方式下与指定数值匹配的数组中元素的相应位置。

从以上分析可知，查找函数的功能，一是按搜索条件，返回被搜索区域内数据的一个数据值；二是按搜索条件，返回被搜索区域内某一数据所在的位置值。

利用这两大功能，不仅能实现数据的查询，而且也能解决如"定级"之类的实际问题。

2、LOOKUP用于返回向量（单行区域或单列区域）或数组中的数值。

函数LOOKUP有两种语法形式：

向量和数组。

（1）向量形式

函数LOOKUP的向量形式是在单行区域或单列区域（向量）中查找数值，然后返回第二个单行区域或单列区域中相同位置的数值。

其基本语法形式为LOOKUP（lookup_value,lookup_vector,result_vector）

Lookup_value为函数LOOKUP在第一个向量中所要查找的数值。

Lookup_value可以为数字、文本、逻辑值或包含数值的名称或引用。

Lookup_vector为只包含一行或一列的区域。

Lookup_vector的数值可以为文本、数字或逻辑值。

需要注意的是Lookup_vector的数值必须按升序排序：

...、-2、-1、0、1、2、...、A-Z、FALSE、TRUE；否则，函数LOOKUP不能返回正确的结果。

文本不区分大小写。

Result_vector只包含一行或一列的区域，其大小必须与lookup_vector相同。

如果函数LOOKUP找不到lookup_value，则查找lookup_vector中小于或等于lookup_value的最大数值。

如果lookup_value小于lookup_vector中的最小值，函数LOOKUP返回错误值#N/A。

示例详见图3

图3

（2）数组形式

函数LOOKUP的数组形式在数组的第一行或第一列查找指定的数值，然后返回数组的最后一行或最后一列中相同位置的数值。

通常情况下，最好使用函数HLOOKUP或函数VLOOKUP来替代函数LOOKUP的数组形式。

函数LOOKUP的这种形式主要用于与其他电子表格兼容。

关于LOOKUP的数组形式的用法在此不再赘述，感兴趣的可以参看Excel的帮助。

3、HLOOKUP与VLOOKUP

HLOOKUP用于在表格或数值数组的首行查找指定的数值，并由此返回表格或数组当前列中指定行处的数值。

VLOOKUP用于在表格或数值数组的首列查找指定的数值，并由此返回表格或数组当前行中指定列处的数值。

当比较值位于数据表的首行，并且要查找下面给定行中的数据时，请使用函数HLOOKUP。

当比较值位于要进行数据查找的左边一列时，请使用函数VLOOKUP。

语法形式为：

HLOOKUP（lookup_value,table_array,row_index_num,range_lookup）

VLOOKUP（lookup_value,table_array,col_index_num,range_lookup）

其中，Lookup_value表示要查找的值，它必须位于自定义查找区域的最左列。

Lookup_value可以为数值、引用或文字串。

Table_array查找的区域，用于查找数据的区域，上面的查找值必须位于这个区域的最左列。

可以使用对区域或区域名称的引用。

Row_index_num为table_array中待返回的匹配值的行序号。

Row_index_num为1时，返回table_array第一行的数值，row_index_num为2时，返回table_array第二行的数值，以此类推。

Col_index_num为相对列号。

最左列为1，其右边一列为2，依此类推.

Range_lookup为一逻辑值，指明函数HLOOKUP查找时是精确匹配，还是近似匹配。

下面详细介绍一下VLOOKUP函数的应用。

简言之，VLOOKUP函数可以根据搜索区域内最左列的值，去查找区域内其它列的数据，并返回该列的数据，对于字母来说，搜索时不分大小写。

所以，函数VLOOKUP的查找可以达到两种目的：

一是精确的查找。

二是近似的查找。

下面分别说明。

（1）精确查找--根据区域最左列的值，对其它列的数据进行精确的查找

示例：

创建工资表与工资条

首先建立员工工资表

图4

然后，根据工资表创建各个员工的工资条，此工资条为应用Vlookup函数建立。

以员工Sandy（编号A001）的工资条创建为例说明。

第一步，拷贝标题栏

第二步，在编号处（A21）写入A001

第三步，在姓名（B21）创建公式

=VLOOKUP（$A21,$A$3:

$H$12,2,FALSE）

语法解释：

在$A$3:

$H$12范围内（即工资表中）精确找出与A21单元格相符的行，并将该行中第二列的内容计入单元格中。

第四步，以此类推，在随后的单元格中写入相应的公式。

图5

（2）近似的查找--根据定义区域最左列的值，对其它列数据进行不精确值的查找

示例：

按照项目总额不同提取相应比例的奖金

第一步，建立一个项目总额与奖金比例的对照表，如图6所示。

项目总额的数字均为大于情况。

即项目总额在0~5000元时，奖金比例为1%，以此类推。

图6

第二步假定某项目的项目总额为13000元，在B11格中输入公式

=VLOOKUP（A11,$A$4:

$B$8,2,TRUE）

即可求得具体的奖金比例为5%，如图7。

图7

4、MATCH函数

MATCH函数有两方面的功能，两种操作都返回一个位置值。

一是确定区域中的一个值在一列中的准确位置，这种精确的查询与列表是否排序无关。

二是确定一个给定值位于已排序列表中的位置，这不需要准确的匹配.

语法结构为：

MATCH（lookup_value,lookup_array,match_type）　

lookup_value为要搜索的值。

lookup_array：

要查找的区域（必须是一行或一列）。

match_type：

匹配形式，有0、1和－1三种选择：

"0"表示一个准确的搜索。

"1"表示搜索小于或等于查换值的最大值，查找区域必须为升序排列。

"－1"表示搜索大于或等于查找值的最小值，查找区域必须降序排开。

以上的搜索，如果没有匹配值，则返回＃N/A。