营业周期计算公式Excel中IF函数的嵌套用法

2020年9月20日发(作者：牛峤)
Excel中IF函数的嵌套用法（多条件）
函数格式：
if(logical_test,value_if_true,value_if_false)。
其中：“logical_test”表示设定的条件，“value_if_true”表示当目标单 元格与设
定条件相符时返回的函数值，“value_if_false”表示当目标单元格与设定条件 不
符时返回的函数值。
一、IF函数的语法结构：
IF：是执行真假值判断，根据 逻辑测试的真假值返回不同的结果。语法结构：IF(条
件，结果1，结果2)
二、操作方法
打开所需软件Excel，输入所需的的表格，再找到所填等级资料的第一行，然后，
找到工具 栏的的“fx”或者点菜单“插入”→“fx函数”→在出现的粘贴函数窗
口中选择“全部”→移动滚动 条选择“IF”此时出现IF函数编辑窗口，在第一
个文本框内输入第一个条件，第二个文本框内输入第 一个条件结果，第三个文本
框内输入以后所有的条件并相应的结果。如公
式:IF(B2>89 ,第一条件
B2>89,第一条件结果第三个文本框输
入:IF(B2>79,
第二个 方法是在编辑公式栏内直接输入以下的公式。三、示例1、在学生成绩工
作表中，单元格B2中包含计算 当前成绩等级的公式。如果B2中的公式结果大
于等于60，则下面的函数将显示“及格”，否则将显示 “不及格”。条件结果1
结果2IF(B2>=60,及格不及格、如果要给以学生成绩为名称所引用的 数字
设置字母级别，请参阅下表：
学生成绩统计情况
大于89
A或优
80到89
B或良
70到79
C或中
60到69
D或及格
小于60
F或差
可以使用下列嵌套IF函数：
IF(B2>89,
或
IF(B2>89,优良中及格差
还有一种方法为：
IF(B2<60,
或
IF(B2<60,差及格中良优
当在第一个空格出 现结果后，下面结果如下操作：按住Ctrl把鼠标放在格子右下
角，当鼠标变成十字时间向下拖动，即 可产生所有结果。注：1、B2是所要计算
的值所在的列和行号，“B”为列号，数字“2”为第一个值 所在的行。2、IF函
数的结尾的“）”反括号的个数应为IF的个数。如：IF(B2<60,”差” IF(B2<=69,”
及格”,IF(B2<=79,”中”,IF(B2<=89,”良”,”优” ))))，有4个IF，所以用了4
个“）”。
(2)语法
RANK(number,ref,order)
Number为需要找到排位的数字。
Ref为数字列表数组或对数字列表的引用。Ref中的非数值型参数将被忽略。
Order为一数字，指明排位的方式。
如果order为0（零）或省略，Microso ftExcel对数字的排位是基于ref为按照降
序排列的列表。
如果order不为零， MicrosoftExcel对数字的排位是基于ref为按照升序排列的列
表。
说明 < br>函数RANK对重复数的排位相同。但重复数的存在将影响后续数值的排位。例
如，在一列按升序 排列的整数中，如果整数10出现两次，其排位为5，则11的
排位为7（没有排位为6的数值）。 < br>由于某些原因，用户可能使用考虑重复数字的排位定义。在前面的示例中，用户
可能要将整数10 的排位改为。这可通过将下列修正因素添加到按排位返回的值
来实现。该修正因素对于按照升序计算排位 （顺序=非零值）或按照降序计算排
位（顺序=0或被忽略）的情况都是正确的。
重复数排位 的修正因素=[COUNT(ref)+1–RANK(number,ref,0)–
RANK(nu mber,ref,1)]2。
在下列示例中，RANK(A2,A1:A5,1)等于3。修正因素 是(5+1–2–3)2=，考虑重
复数排位的修改排位是3+=。如果数字仅在ref出现一次，由于 不必调整RANK，
因此修正因素为0。
示例
如果您将示例复制到空白工作表中，可能会更易于理解该示例。
操作方法
创建空白工作簿或工作表。
请在“帮助”主题中选取示例。不要选取行或列标题。
从帮助中选取示例。
按Ctrl+C。
在工作表中，选中单元格A1，再按Ctrl+V。
若要在查看结果和查看返回结果的公式之 间切换，请按Ctrl+`（重音符），或在
“工具”菜单上，指向“公式审核”，再单击“公式审核模 式”。
1
2
3
4
5
6
A
数据
7


1
2
公式说明（结果）
=RANK(A3,A2:A6,1)在上表中的排位(3)
=RANK(A2,A2:A6,1)7在上表中的排位(5)
(3)，“分类汇总”就是把数据分类别进行统计，便于对数据的分析管理。
举例
某学校某个年级学生成绩数据表
A列B列C列D列E列F列
姓名班级语文数学英语总分
张三76
李四73
……
王五287
钱顺65
……
分类汇总——统计年级中各个班的学科人平分、及人平总分
操作
按班级进行升序排列。
在数据区域任单击一个单元格，执行“数据分类汇总”命令，打开“分 类汇总”
对话框；在“分类字段”选择“班级”，在“汇总方式”中选择“平均值”，在
“选定 汇总项（可多个）”中选中“语文、数学、英语、总分”，单击“确定”
按钮。
汇总表
在表格的左上角，有1、2、3三个数字按钮，叫“分级显示级别按钮”。
按钮3是三级数据 按钮，单击此按钮展开的汇总表是一个明细工作表，工作表可
以显示前三级数据（每一个学生成绩数据、 每一个班的学科平均成绩和人平总分、
整个年级各学科的平均分和人平总分）。
执行完“分类汇总”操作后的当前表格就是一个3级数据表。
此时的工作表在每一个班级最后 一条记录的下一行，即为该班的学科平均分和人
平总分，数据表的最后一行是年级各学科平均分和人平总 分。
按钮2是二级数据按钮，单击此按钮，工作表显示的是每一个班级各学科的平均
分及班级 人平总分、年级各学科平均分和人平总分。
按钮1是一级数据按钮，单击此按钮，工作表显示整个年级各学科的平均分和年
级人平总分。
在按钮1、2、3下方出现左半边方括号及+号按钮和-号按钮。
+号按钮是显示明细数据按钮，单击此按钮则显示该按钮所包含的明细数据，并
切换到-按钮。
-按钮是隐藏明细数据按钮，单击此按钮则隐藏该按钮上部中括号所包含的明细
数据，并切换到 +按钮。
清除（退出）“分类汇总”的操作：
在含有分类汇总的数据表区域中，任意单击一 个单元格，执行“数据分类汇总”
命令，在打开的“分类汇总”对话框中，单击“全部删除”按钮即可退 出“分类
汇总”表格模式。

1.将七层之外的IF语句，放在另外的单元格内来处 理，例：
C5=if(if,...,(if...),B5))),B5单元格就是存放七层之外的I F语句。依此类
推，可以实现在数据库语言中CASE语句的功能。
函数的确有七层嵌套的限 制。遇到七层嵌套还解决不了的问题，可以尝试用
其它的函数组合和数组公式来解决；有时用VBA方案 可以有很好的效果。
这里给出一个解决IF函数嵌套超出范围的方法，可能比较容易使初学者看
懂。其思路是：一个单元格做不了的事，分给两个或更多的单元格来做，文字内
容是这样，函数内容也 是这样。
例子：假如A1＝1，则B1＝A；A1＝2，则B1＝B……A1＝26，则B1＝Z
解决方法如下：
B1=IF(A1=1,
(A1=6,
C1=IF(A1= 9,

D1=IF(A1=17,
U
E1=IF(A1=25,超出范围
根据情况，可以将C、D、E这些从事辅助运算的单元格放在其它任何地方
3.一个单元格也可以实现
=IF(A1=1,
1=6,
F(A1=11,
=16,

1=25,数组形式输入)。
5.以一例：a1=1,2,3,4,5,6,7,8,9,10
6.b=if(a1=1,一二，三九
十超过7层不起作用，我该如何办
解答：1、 b=if(a1>5,if(a1=6,六。。。。。。))，明白意思就是截为两
段再做判断，这样可 以不超过7重。
2、可以用自定义数字格式。也可以用=CHOOSE(A1+1,一二三四五六七八 九
十
问：实际上我的要求是现行高一成绩统计中:b=if(a1=语文语文
数学数学 英语英语物理物理
化学化学（a1=历史历史政治政治生物生
物“，if(a1=地理地理这样 超过了7层。我不知如何处理。因为
下面的公式要引用语、数、英、等。
答：新建一表，取名Data，找一区域设置名称为SubjectTable：
语文Chinese
英语English
..
=vlookup(Su bjectTable,a1,2,false)可以有65536个，够了吧。其实，稍
加改进，理论 上，可以有达到你硬盘空间的个数。或用if和or的组合可以解决
15个。
再举个例子：
=IF(A16=样办尚未交内部检查中
数据查询中数据查询中内部检查中品质检
测中 数据查询中图纸未确认为不合格需
要修正客户检查中待客回复
合格待PO生产取消客户取消为客 户设
变中客户设变中不合格需要修正
从个人所得税的计算谈IF函数的使用
最近，国 家采取提高公务员和职工的工资水平，低收入阶层的收入的政策以启动
消费推动经济增长，相当一部分人 工资收入达到和超过了交纳个人所得税的水
平。根据我国税法的规定，个人所得税是采用超额累进税率分 段计算，
见表一：
级数
1
2
3
4
5
6
7
8
9
应纳税所得额
不超过500元的
超过500元2000元的部分
超过2000元5000元的部分
超过5000元20000元的部分
超过20000元40000元的部分
超过40000元60000元的部分
超过60000元80000元的部分
超过80000元100000元的部分
超过100000元的部分
税率%
5
10
15
20
25
30
35
40
45
速算扣除数
0
25
125
375
1375
3375
6375
10375
15375
这时，用Excel的IF函数来进行计算是再方便不过的了。对原工资表只要增 加
“应纳税所得额”和“应交个人所得税”两列就可以，“应纳税所得额”根据“应
发工资”数 扣除800元和其他可免税的部分计算（这一列也可省略），“应交个
人所得税”设在工资表的应扣金额 部分，将其象“代扣房租”等等项目一样作为
工资表中的一个扣除项目，计算个人所得税的公式就设置在 此列。
Excel中的逻辑函数IF一个适用范围很广，功能极强的函数，IF函数在工作表
中的用途是用于对数值和公式进行条件检测，然后根据不同的检测结果，返回不
同的结果（执行不同的操 作命令）。它的这种功能用来判断应纳税所得额的适用
税率再合适不过。
理解IF函数在计算个人所得税中的用法
IF函数的语法
IF（参数1，参数2，参数3）
该函数的含义是在单元格中以参数1为条件进行检测，当检 测结果符合参数1
时，执行参数2的命令，反之则执行参数3的命令。其中：参数1为函数执行检
测的条件，它一般是一个公式或一个数值表达式，参数2和参数3可以是显示一
个字符串、显示一个数 值或显示某一公式的计算结果。参数之间用半角的“，”
隔开，字符串也要用半角引号括住。
设工资表中“应纳税所得额”在E列，“应交个人所得税”在H列。我们要在H
列的各行设置IF函数公 式，由函数公式来对E列各行的应纳税工资进行判断，
并自动套用适用税率和速算扣除数计算应纳税额。 可在H列设置函数(以第2行
为例)：
IF(E2>100000，E2*，IF（E2>8 0000，E2*，IF(E2>60000，E2*，IF(E2>40000，
E2*，IF(E2 >20000，E2*，IF(E2>5000，E2*，IF(E2>2000，E2*，IF(E2>500 ，E2*，
E2*)))))))。
该函数十分亢长，其中嵌套了7个同样的IF函数，从第 2个IF函数开始到最后
是第1个嵌套函数，从第3个IF开始到最后是第2个嵌套函数……，为帮助理
解，我们将这些嵌套函数分别设为X1、X2、……于是将整个函数简化如下：
IF（E2>100000，E2*，X1）
该函数意为：当E2中工资额大于100000 元时，H2中计算出的应纳所得税额为
E2*45%-15375，否则（指当工资额等于或小于100 000元时），则H2应按X1的
方法计算；把X1展开：
IF（E2>80000，E2*，X2）
其含义与上面相仿。最后一个嵌套函数X7展开为：
IF（E2>500，E2*，E2*）
该函数意为：当E2中工资额大于500元时，H2中计算出的应纳所得税额为
E2*10%- 25，否则（当工资额等于或小于500元时），H2等于E2*5%。
怎么样，明白了吗够复杂的， 好在计算个人所得税只有一种规定，将别人的公式
搬过来用就是了。一般单位个人的工资不会高的太吓人 ，我们可根据人员的实际
工资水平减少函数的嵌套数目简化运算。
学会灵活使用IF函数 < br>从以上计算个人所得税的例子中可以看出，学会在Excel的工作表中运用这个函
数设定各种条 件，可实现许多特定的操作要求，起到事半功倍的作用。本文再举
几个实例来讲解IF函数的使用方法， 读者们可举一反三，在自己的Excel工作
表中灵活运用。
例一
（图一） 图一是一份会计记帐凭证，为了判断输入的数据借贷是否相等，我们在A8中利
用IF函数进行检测 ，选定A8，双击“=”，输入函数：
=IF（C8＜＞D8，借贷不平，合计）
当C8= D8时，不符合参数1的条件，在A8中即显示参数3中的字符串“合计”，
当C8≠D8时，符合参数 1的条件，A8中显示的就是参数2中的字符串“借贷不
平”，给予提示（见图二）。
（图二）
例二
（图三）
在图三的表中，要求当C5：C12区域中的数 值为正数时，在D5：D12区域中用相
同的正数反映，当C5：C12区域中的数值为负数时，则要在 E5：E12区域中用它
们的绝对值反映。
在D5中建立函数如下：=IF（C5＞0，C5，）
在E5中建立函数如下：=IF（C5＜0，C5*（－1），）
再将已建立的函数复制到其他相应的单元格中去即可。
（图四）
如果在单元格中不 想将“0”显示出来，则第三个参数可表示为“”（如图四
单元格E5所示）。
例三
（图五）
在图五表中如果只要求计算盈利企业的利税总额则可在F列各单元格中建立函
数如下（以F2为例）：
=IF（B2＞0，SUM（B2：E2），）
当IF函数的参 数1中含有两个或两个以上的检测条件时，就要在参数中嵌套另
外两个逻辑函数AND和OR了。
例四
设单元格A4的数值只能严格为正小数，当A4符合此条件时，则在B4中显示该
数值，否则提示错误信息。从A4的条件看它应同时满足＞0且＜1的要求，因而
应结合运用AND函 数，在B4中建立函数如下：
=IF（AND（0＜A4，A4＜1），A4，数值超出范围）
例五
设单元格A4的数值应该是绝对值大于10的任何数值，当A4符合此条件时，则
在B4中显示该数值，否则提示错误信息。这里A4的条件既可＞10又可＜－10，
两个条件只要满 足一个即可，因而应结合OR函数，在B4中建立函数如下：
=IF（OR（10＜A4，A4＜－10），A4，数值超出范围）
从以上例子可以看出， IF函数是一个十分有用的函数，要用Excel，不可不学
IF函数。本文所举例子并不能包括它的所 有用法，相信大家一定能发现IF函数
的更多功能。
(原载CPCW网站)
例1发奖金：
姓名 销售额 奖金
10000以内的提1％ 王五 5124
20000以内的提％ 赵六 25135
30000以内的提％ 黑七 2154
40000以内的提％ 白云 21354
50000以内的提％ 红枣 45111
50000以上的提2％ 啊好 851352
公式：
=IF(C4<=10000,C4*,IF(C4<=2000 0,C4*,IF(C4<=3000,C4*,IF(C4<=40000,C4*,IF(
C4<= 50000,C4*,IF(C4>50000,C4*)))))
例2取款
654321卡资料 请插入磁卡！
卡号 654321 卡号： 654321
密码
余额






123456
1000元



请输入密码！
密码：
请输入您要支取的
数额!
金额：


123456

5000


公式： =IF(E16=请插入磁卡！输入密码！
您的磁卡无效，或密码不正确，请重新输入
请输 入金额!对不起你的卡上金额不足,请重输！
对不起，您输入的金额应是50的倍数！您的申请
成功，系统正在操作中，请稍候……
大凡所有的程序都是从这些最基础的判断做起的，例2模仿了取款机 用户取款的
反应过程。实际的程序比这复杂的多，但原理是一样的。看起来公式很长，但是
它是 由几个很简单的IF函数嵌套而成的。实际的应用当中有很多是比这个复杂
的。
我们看一这一 段公式：=IF(E16=请插入磁卡！输入密码！……,p
里面是两个IF的嵌套，第一个if：条件 E16=成立执行第一个分支请插入磁卡！
就不管后面的了，因为对于第一个IF来说后面的公式只是其 中的一个参数。同
理，条件E16=不成立，excel就不理第一个分支了，就会直接跳到第二个分支
了：IF(E18=输入密码！……碰到第二个if时，又对条件E18=进行判断，
条件E1 8=成立执行它的第一个分支输入密码！，不成立又会跳过第一个分支
输入密码！，执行后面的公式，以 此类推......
再示例：
=if(a2>700,
(a2>300,
取款机反
应： 对不起你的卡上金额不足,请重输！
新税率如何利用EXCEL计算个人所得税

国 家税务局国税发（２０１１）第２０号规定工资、薪金所得，以每月收入额减除费用3500元
后的余额 ，为应纳税所得额，计算征收个人所得税。
个人所得税为超额累进税，即超过一定的额度后按不同的税率计算。税率表如下：


1
2
3
4
5
6
8

不超过1500元的
超过1500元至4,500元的部分
超过4,500元至9,000元的部分
超过9,000元至35,000元的部分
超过35,000元至55,000元的部分
超过55,000元至80,000元的部分
超过80,000元的部分

1500
4500
9000
35000

3%
10%
20%
25%
30%
35%
45%

0
105
555
1005
2755
5505
13505

1500
3000
4500
26000
20000
25000
+308
下限
0
1500
4500
9000
35000 55000
55000 80000
80000 1E+308 < br>假设税前收入为8400（为简化说明不考虑社保公积金等），那么应纳税额为8400-3500=49 00元，
本文件中定义名称X=K24

为了便于理解超额累进税的课税原理，我们用坐标轴形象直观的说明：

以下各图中， 坐标轴上方的值为临界值（此处只标注到20000），坐标轴下方的值为相临临界值
的间隔。
根据个人所得税的计税原理，我们可以采取最少13种方法计算个人所得税，这里只举5种代表
13种 方法
因为以下讲解中的第四种，按9个税率计算的话，就是13种方法。

4900=1500+3000+500
那么所得税应当为:
=1500*3%+3000*10%+400*20%
运用此原理的相应的Excel公式比较少，我此处给出一例
=SUM((X>=D8:D1 4)*(E8:E14)*(G8:G14))+MIN(X-((X>=C8:C14)*(C8:C14)) )*MAX((X>=C8:C14)*(
E8:E14))

此公式较长，实用性不大，只在公式栏按F9，即可转为常量数组公式

第二种：
速算扣除数法
可能有人会问，速算扣除数是怎么计算得出的在些我们先简单介绍一下速算扣除数的由来
速算 扣除数实际上是在级距和税率不变条件下，全额累进税率的应纳税额比超额累进税率的应纳
税额多纳的一 个常数。
因此，在超额累进税率条件下，用全额累进的计税方法，只要减掉这个常数，就等于用超额累 进
方法计算的应纳税额，故称速算扣除数。
速算扣除数计算公式推导过程
第1级速算扣除数等于0，因为此处的全额累进税等于超额累进税
第2级速算扣除数，450 0按全额累计计算，4500*10%，但1500部分已经交纳3%的税，因此多交
纳的税为1500 *(10%-3%)
第3级速算扣除数，4900按全额累进计算，4900*20%，但1500已 按3%计算，3000已经按10%
交税，
多交的税为
1500*(20%-3%) +3000*(20%-10%)=4500*20%-1500*3%-3000*10%=4500*(20 %-10%)+1500*(10%-3%)
依此类推，但出速算扣除数公式如下：
本级速算扣除额=上一级最高所得额×（本级税率-上一级税率）+上一级速算扣除数
那么理解了上述原理后，公式
=4900*20%-555
运用速算扣除数计算税 的公式特别多，如IF函数法，Vlookup+辅助列方法等，对应EXCEL公式，
此类公式直观， 易理解，所以不做解释
此处仅解释比较难理解的一个公式，Max函数法
★ =MAX(X **{3,10,20,25,30,35,45}-5*{0,21,111,201,551,1101,2 701},0)
★ =MAX(X**{3,10,20,25,30,35,45}-5*{0,2 1,111,201,551,1101,2701},0)
我们知道个税的起征点为3500，因此 X少于等于3500时，不需交税，故要用得出的数值与0进
行比较。
为什么根据各级税率减速算扣除数的最大值为应交税呢
分解公式的结构
第一部分即X**{3,10,20,25,30,35,45}为全额累进计算的税
第二部 分5*{0,21,111,201,551,1101,2701}为速算扣除数，即全额累计计算所得税- 超额累计应
纳税
那么为什么最大值就是正确的税呢原因就在于减去重复计算值得时候，公式直 接按照重复的区间
计算减去值，减去的比正常减去的多。
所以高税率计算出的值是一个错误值。
所以，该公式计算得到的数组中最大值只有一个，等于 合适税率下计算的答案。而比这个值小的
数显然都不是正确答案。
以上述X为例子说明： < br>=MAX(X**{3,10,20,25,30,35,45}-5*{0,21,111,201,5 51,1101,2701},0)
=MAX({147,385,425,220,-1285,-3790,-11300},0) < br>4900按高税率20%计算时，多计算的税应当是=1500*3%+3000*10%+400*20 %=555
而运用速算扣除数时，是按高一区间的速算扣除数（多计算的税）计算的，上述公式中5* 111
计算的
所以按高税率计算时，减去多计算的税时（速算扣除数），比正常减去的要多
所以，公式中的最大值才是正确结果。
不知道理解了没有有点绕啊！呵呵。
第三种方法：
全额累进减多算的税
这实际上是根据计算速算扣除数的原理来计算税
如果4900按20%计算，那么对应的1500由于已经按1500*3%计算过一次税，所以500 部分多计
的税是1500*（20%-5%）
相应的3000部分，由于3000部分已经按 10%计算过税，所以3000部分多计的税是3000*（20%-10%）
相应的计算公式如下：
=4900*20%-3000*10%-1500*17%
EXCEL中应用此原理的也不多
=X*MAX((X>=C8:C14)*(E8:E14 ))-SUM((X>=D8:D14)*(G8:G14)*(MAX((X>=C8:C14)*(E8:E 14))-E8
:E14))
=SUMPRODUCT(TEXT(4900-D8:D14 ,
=SUMPRODUCT(TEXT(4900-D8:D14,
第四种方法：
按最低档的3%计算
假如都按3%来计税，怎么算4900都按3%计税时，其中的可以看到 1500部分已经交纳其应交的
税
3400也按3%计算了税，而3000本应按10%计算 税，400按20%交税，而4900部分的3400已经
按3%交了一部分税
所以说300 0部分只应再交7%的税，如果此时400再交7%的税，那么最后400只要再交10%的税
就是全部 应该交纳的全部税
那么计算公式：
=400*10%+3400*7%+4900*3%
为了进一步理解，对上述公式分解后就是：
4900*3%=1500*3%+3000*3%+400*3%
3000少交7%，400少交17%，那么再交变化
3400*7%=3000*7%+400*7%
那么最后400再交10%就是全部应交的税即400*10%
对应的Excel公式
★
=SUM(TEXT(X-C8:C14,
上述公式只要把D9：D16在公式编 辑栏中用F9变为常量数组，即可移植到您需要的地方。
此公式在Excel中应用比较广泛。