【excel怎么判断工龄】Excel逻辑函数——IF函数详细信息

函数功能

根据指定的条件判断“真”(TRUE)、“假”(FALSE)并返回其内容。

函数语法

If (logical _ test、value _ if _ true和value _ if _ false)

解析参数

IF函数可以嵌套7层关系，构成复杂的判断条件，进行综合评价。

Logical_test:表示逻辑决定表达式。

Value_if_TRUE:表示当判断条件为逻辑TRUE时，将显示此处指定的内容。如果省略，则返回TRUE。

Value_if_FALSE:如果判断条件为逻辑FALSE，则显示此处指定的内容。如果省略，则返回FALSE。

例1评价个人面试成绩是否合格

对应聘者进行面试后，高管可以评价员工的评价成绩。例如，如果每个成绩在60分以上，就可以通过评价和招聘。如果某个成绩低于60分，就会被评为不合格，不录用。

选择单元格E2，然后在公式编辑栏中输入公式。

=IF(AND(B2=60，C2=60，D2=60)，“通过”，“失败”)

按“Enter”键，根据员工的成绩判断面试者是否通过。

如图1所示，将光标移动到单元格E2的右下角，按住鼠标左键向下拖动，直到光标变成十字形状，进行正式填充，这样就可以确认其他人的面试成绩是否正确。

图1

公式分析

分别判断B2、C2、D2单元格中的数值是否都大于或等于60。如果满足条件，则返回TRUE值并显示“合格”。

如果单元格B2、C2、D2中的任何一个数字小于60，则返回FALSE，并标记为“不合格”。

范例2解决计算结果为零的问题

如果使用公式执行累计操作，但未在引用单元格中输入数值，则结果单元格将显示“0”值。例如，如果表格中储存格D5的公式参考储存格B5:C5的区域，则会显示「0」值，如图2所示。

图2

选择单元格D2，然后在公式编辑栏中输入公式。

=IF(AND(B2=''，C2=' ')，' '，B2 C2)

如果按Enter键，则在参照单元格中没有数值时为空。

如图3所示，如果将光标移动到单元格D2的右下角，按住鼠标左键并向下拖动直到光标变成十字形状，以填充公式，则当其他参考单元格没有数值时，不会显示为“0”值。

图3

公式分析

使用AND函数，如果B2、C2单元格同时为null，则返回null。

步骤如果条件为TRUE，则返回null否则返回B2、C2的总和。

例3评价员工的真实考试

在员工评价成绩统计表中，确认评价数据集之一大于“80”，如果有，则有资格参加培训，否则取消资格。

选择单元格E2，然后在公式编辑栏中输入公式。

=IF(OR(B280，C280，D2gt)

;80),"参与培训","取消资格")

按“Enter”键即可根据员工的考核成绩判断出其是否具备参与培训的资格。

➋将光标移到E2单元格的右下角，待光标变成十字形状后，按住鼠标左键向下拖动进行公式填充，即可得出其他员工参与培训的资格情况，如图4所示。

图4

公式解析

①判断B2>80、C2>80、D2>80这3个条件中是否有一个条件满足。

②当满足步骤①中的任何一个条件时，返回“参与培训”，否则返回“取消资格”。

实例5　根据消费卡类别和消费情况派发赠品

某商场元旦促销活动的规则如下。当卡种为金卡时，消费额小于2888，赠送“电饭煲”；消费金额小于3888时，赠送“电磁炉”，否则赠送“微波炉”。当卡种为银卡时，消费金额小于2888时，赠送“夜间灯”；消费金额小于3888时，赠送“雨伞”，否则赠送“摄像头”。未持卡且消费金额大于2888时，赠送“浴巾”。

➊选中D2单元格，在公式编辑栏中输入公式：

=IF(AND(B2="",C2<2888),"",IF(B2="金卡",IF(C2<2888,"电饭煲",IF(C2<3888,"电磁按“Enter”键即可返回各用户的持卡类别以及消费额所对应的赠品。

➋将光标移到D2单元格的右下角，待光标变成十字形状后，按住鼠标左键向下拖动进行公式填充，即可得出所有卡号的赠品派发情况，如图5所示。

图5

公式解析

①未持卡并且消费金额小于2888元，无赠品。

②对于持金卡的消费者按消费金额派发赠品。

③ 对于持银卡的消费者按消费金额派发赠品。

实例6　由员工的业绩计算需要发放多少奖金

公司规定，业务成绩大于100000元者将给予奖金2000元，否则为1000元。现在需要统计8个业务员总共需要发放的奖金金额。选中D2单元格，在公式编辑栏中输入公式：

=SUM(IF(B2:B9>100000,2000,1000))

按“Shift+Ctrl+Enter”组合键即可计算需要发放多少奖金，如图6所示。

图6

公式解析

①逐一判断B2:B9单元格区域中的每个值是否大于100000，如果是则返回2000，否则返回1000，整个公式返回的是一个数组。

②使用SUM函数将步骤①数组中的值求和。

实例7　根据工龄计算其奖金

公司规定工作时间在1年以下者给予200元年终奖，1～3年者为600元，3～5年者为1000元，5～10年者为1400元。现在需要计算每位员工12月份工资加年终奖合计值。

➊选中D2单元格，在公式编辑栏中输入公式：

=C2+SUM(IF(B2>{0,1,3,5,10},{200,400,400,400,400}))

按“Enter”键即可计算出第一位员工12月的工资。

➋将光标移到D2单元格的右下角，待光标变成十字形状后，按住鼠标左键向下拖动进行公式填充，即可快速计算出其他员工12月的工资，如图7所示。

图7

公式解析

①判断B2单元格中的工龄在1～3、3～5、5～10中的哪一个工龄段。

②根据B2单元格中显示的工龄分别返回对应的奖金。这里规定起步奖金为200，之后每个工龄段之间的奖金相差400元。

③ 根据步骤①和②的判断结果计算出奖金总额，并加上C2单元格中的工资从而得出结果。

实例8　有选择地汇总数据

在统计了各组的产量后，需要对A组、C组人员的产量进行汇总。选中E2单元格，在公式编辑栏中输入公式：

=SUM(IF(A2:A9={"A组","C组"}, C2:C9))

按“Shift+Ctrl+Enter”组合键即可计算出A组与C组的产量，如图8所示。

图8

公式解析

①在A2:A9单元格区域中筛选出A组与C组，并返回对应在C2:C9单元格区域上的产量值。

②将步骤①的返回值进行求和。

实例9　判断数据是否存在重复现象

如图9所示，B列为员工姓名，使用IF函数配合COUNTIF函数可以判断员工姓名是否重复。

图9

➊选中C2单元格，在公式编辑栏中输入公式：

=IF(COUNTIF(B$2:B2,B2)>1,"重复","")

按“Enter”键即可判断B2中的员工姓名是否存在重复现象，如果出现次数超过1次，则标识为“重复”。

➋将光标移到C2单元格的右下角，待光标变成十字形状后，按住鼠标左键向下拖动进行公式填充，即可快速判断出其他员工姓名是否存在重复，如图9所示。

公式解析

①判断B2单元格中的值在B2:B2单元格区域中出现的次数是否大于1。

②当出现次数大于1时返回“重复”，否则返回空值。

提示

本例中涉及公式数据源的引用方式，在公式的纵向复制中需要保持行号不变，这样才能便于向下进行公式复制，从而一次性得到批量结果。

实例10　比较各产品的两个部门的采购价格是否一致

在产品采购价格统计表中，如果想要比较采购一部与采购二部对每种产品采购的价格是否一致，可以按如下方法设置公式。

➊选中D2:D8单元格区域（即要显示结果的单元格区域），在公式编辑栏中输入公式：

=IF(NOT(B2:B8=C2:C8),"请核对","")

➋同时按“Shift+Ctrl+Enter”组合键，即可返回各部门的比较结果。价格相同时返回空值，否则返回“请核对”，如图10所示。

图10

公式解析

①依次判断B2:B8单元格区域中的值是否不等于C2:C8单元格区域中的值。

②如果步骤①的结果为TURE，则显示“请核对”；如果为FALSE，则返回空。

实例11　根据职工性别和职务判断退休年龄

某公司规定，男职工退休年龄为60岁，女职工退休年龄为55岁，如果是领导班子成员（总经理和副总经理），退休年龄则可以延迟5岁。

本例将介绍如何根据职工性别和职务判断退休年龄。

➊选中E2单元格，在公式编辑栏中输入公式：

=IF(C2="男",60,55)+IF(OR(D2="总经理",D2="副总经理"),5,0)

按“Enter”键即可计算出第一位员工的退休年龄。

➋将光标移到E2单元格的右下角，待光标变成十字形状后，按住鼠标左键向下拖动进行公式填充，即可快速计算出其他员工的退休年龄，如图11所示。

图11

公式解析

①如果C2="男"，返回60，否则返回55。

②判断D2="总经理"和D2="副总经理"两个条件是否有一个满足。

③ 如果步骤②中条件满足，返回5，否则返回0。

④ 将步骤①与步骤③得出的结果相加。

实例12　计算个人所得税

用IF函数配合其他函数计算个人所得税。相关规则如下：起征点为3500。税率及速算扣除数如图12所示。

图12

➊选中D2单元格，在公式编辑栏中输入公式：

=IF(B2>3500,B2-C2,0)

按“Enter”键得出第一位员工的“应纳税所得额”，如图13所示。

图13

➋选中E2单元格，在公式编辑栏中输入公式：

=IF(D2<=1500,0.03,IF(D2<=4500,0.1,IF(D2<=9000,0.2,IF(D2<= 35000,0.25,IF(按“Enter”键根据“应纳税所得额”得出第一位员工的纳税税率，如图14所示。

图14

➌选中F2单元格，在公式编辑栏中输入公式：

=VLOOKUP(E2,{0.03,0;0.1,105;0.2,555;0.25,1005;0.3,2755;0.35,5505;0.45,13505},按“Enter”键根据“税率”得出第一位员工的“速算扣除数”，如图15所示。

图15

➍选中G2单元格，在公式编辑栏中输入公式：

=D2*E2-F2

按“Enter”键计算得出第一位员工的“应缴所得税”，如图16所示。

图16

公式解析

公式1：

=IF(D2<=1500,0.03,IF(D2<=4500,0.1,IF(D2<=9000,0.2,IF(D2<=35000,0.25,IF(D2<=①是一个IF函数多层嵌套的公式。

②值得限定依据图1-19中的表格。

公式2：

=VLOOKUP(E2,

{0.03,0;0.1,105;0.2,555;0.25,1005;0.3,2755;0.35,5505;0.45, 13505},2,)

VLOOKUP是查找函数，表示在{0.03,0;0.1,105;0.2,555;0.25,1005;0.3,2755;

0.35,5505;0.45,13505}这个组的首列中找E2单元格的值，找到后返回对应在{0.03,

0;0.1,105;0.2,555;0.25,1005;0.3,2755;0.35,5505;0.45,13505}这个组中第2列的值。