Excel中IF函数的嵌套用法.docx
- 文档编号:28044729
- 上传时间:2023-07-07
- 格式:DOCX
- 页数:19
- 大小:27.90KB
Excel中IF函数的嵌套用法.docx
《Excel中IF函数的嵌套用法.docx》由会员分享,可在线阅读,更多相关《Excel中IF函数的嵌套用法.docx(19页珍藏版)》请在冰豆网上搜索。
Excel中IF函数的嵌套用法
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,"A",IF(B2>79,"B",IF(B2>69,"C",IF(B2>59,"D","F"))))第一条件B2>89,第一条件结果"A",第三个文本框输入:
IF(B2>79,"B",IF(B2>69,"C",IF(B2>59,"D","F"
第二个方法是在编辑公式栏内直接输入以下的公式。
三、示例1、在学生成绩工作表中,单元格B2中包含计算当前成绩等级的公式。
如果B2中的公式结果大于等于60,则下面的函数将显示“及格”,否则将显示“不及格”。
条件结果1结果2IF(B2>=60,"及格","不及格")2、如果要给以学生成绩为名称所引用的数字设置字母级别,请参阅下表:
学生成绩统计情况
大于89
A或优
80到89
B或良
70到79
C或中
60到69
D或及格
小于60
F或差
可以使用下列嵌套IF函数:
IF(B2>89,"A",IF(B2>79,"B",IF(B2>69,"C",IF(B2>59,"D","F"))))
或
IF(B2>89,"优",IF(B2>79,"良",IF(B2>69,"中",IF(B2>59,"及格","差"))))
还有一种方法为:
IF(B2<60,"F",IF(B2<=69,"D",IF(B2<=79,"C",IF(B2<=89,"B","A",))))
或
IF(B2<60,"差",IF(B2<=69,"及格",IF(B2<=79,"中",IF(B2<=89,"良","优"))))
当在第一个空格出现结果后,下面结果如下操作:
按住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(零)或省略,MicrosoftExcel对数字的排位是基于ref为按照降序排列的列表。
如果order不为零,MicrosoftExcel对数字的排位是基于ref为按照升序排列的列表。
说明
函数RANK对重复数的排位相同。
但重复数的存在将影响后续数值的排位。
例如,在一列按升序排列的整数中,如果整数10出现两次,其排位为5,则11的排位为7(没有排位为6的数值)。
由于某些原因,用户可能使用考虑重复数字的排位定义。
在前面的示例中,用户可能要将整数10的排位改为5.5。
这可通过将下列修正因素添加到按排位返回的值来实现。
该修正因素对于按照升序计算排位(顺序=非零值)或按照降序计算排位(顺序=0或被忽略)的情况都是正确的。
重复数排位的修正因素=[COUNT(ref)+1–RANK(number,ref,0)–RANK(number,ref,1)]/2。
在下列示例中,RANK(A2,A1:
A5,1)等于3。
修正因素是(5+1–2–3)/2=0.5,考虑重复数排位的修改排位是3+0.5=3.5。
如果数字仅在ref出现一次,由于不必调整RANK,因此修正因素为0。
示例
如果您将示例复制到空白工作表中,可能会更易于理解该示例。
操作方法
创建空白工作簿或工作表。
请在“帮助”主题中选取示例。
不要选取行或列标题。
从帮助中选取示例。
按Ctrl+C。
在工作表中,选中单元格A1,再按Ctrl+V。
若要在查看结果和查看返回结果的公式之间切换,请按Ctrl+`(重音符),或在“工具”菜单上,指向“公式审核”,再单击“公式审核模式”。
1
2
3
4
5
6
A
数据
7
3.5
3.5
1
2
公式说明(结果)
=RANK(A3,A2:
A6,1)3.5在上表中的排位(3)
=RANK(A2,A2:
A6,1)7在上表中的排位(5)
(3),“分类汇总”就是把数据分类别进行统计,便于对数据的分析管理。
举例
某学校某个年级学生成绩数据表
A列B列C列D列E列F列
姓名班级语文数学英语总分
张三1909888276
李四1959880273
……
王五29210095287
钱顺2859288265
……
分类汇总——统计年级中各个班的学科人平分、及人平总分
操作
按班级进行升序排列。
在数据区域任单击一个单元格,执行“数据/分类汇总”命令,打开“分类汇总”对话框;在“分类字段”选择“班级”,在“汇总方式”中选择“平均值”,在“选定汇总项(可多个)”中选中“语文、数学、英语、总分”,单击“确定”按钮。
汇总表
在表格的左上角,有1、2、3三个数字按钮,叫“分级显示级别按钮”。
按钮3是三级数据按钮,单击此按钮展开的汇总表是一个明细工作表,工作表可以显示前三级数据(每一个学生成绩数据、每一个班的学科平均成绩和人平总分、整个年级各学科的平均分和人平总分)。
执行完“分类汇总”操作后的当前表格就是一个3级数据表。
此时的工作表在每一个班级最后一条记录的下一行,即为该班的学科平均分和人平总分,数据表的最后一行是年级各学科平均分和人平总分。
按钮2是二级数据按钮,单击此按钮,工作表显示的是每一个班级各学科的平均分及班级人平总分、年级各学科平均分和人平总分。
按钮1是一级数据按钮,单击此按钮,工作表显示整个年级各学科的平均分和年级人平总分。
在按钮1、2、3下方出现左半边方括号及+号按钮和-号按钮。
+号按钮是显示明细数据按钮,单击此按钮则显示该按钮所包含的明细数据,并切换到-按钮。
-按钮是隐藏明细数据按钮,单击此按钮则隐藏该按钮上部中括号所包含的明细数据,并切换到+按钮。
清除(退出)“分类汇总”的操作:
在含有分类汇总的数据表区域中,任意单击一个单元格,执行“数据/分类汇总”命令,在打开的“分类汇总”对话框中,单击“全部删除”按钮即可退出“分类汇总”表格模式。
Excel如何实现函数IF的嵌套超过七层
1.将七层之外的IF语句,放在另外的单元格内来处理,例:
C5=if(if,...,(if...),B5))),B5单元格就是存放七层之外的IF语句。
依此类推,可以实现在数据库语言中CASE语句的功能。
2.IF?
函数的确有七层嵌套的限制。
遇到七层嵌套还解决不了的问题,可以尝试用其它的函数组合和数组公式来解决;有时用?
VBA?
方案可以有很好的效果。
这里给出一个解决IF函数嵌套超出范围的方法,可能比较容易使初学者看懂。
其思路是:
一个单元格做不了的事,分给两个或更多的单元格来做,文字内容是这样,函数内容也是这样。
例子:
假如?
A1=1,则?
B1=A;A1=2,则?
B1=B?
……?
A1=26,则?
B1=Z
解决方法如下:
B1?
=?
IF(A1=1,"A",IF(A1=2,"B",IF(A1=3,"C",IF(A1=4,"D",IF(A1=5,"E",IF(A1=6,"F",IF(A1=7,"G",IF(A1=8,"H",C1))))))))
C1?
=?
IF(A1=9,"I",IF(A1=10,"J",IF(A1=11,"K",IF(A1=12,"L",IF(A1=13,"M",IF(A1=14,"N",IF(A1=15,"O",IF(A1=16,"P",D1))))))))
D1?
=?
IF(A1=17,"Q",IF(A1=18,"R",IF(A1=19,"S",IF(A1=20,"T",IF(A1=21,"U",IF(A1=22,"V",IF(A1=23,"W",IF(A1=24,"X",E1))))))))
E1?
=?
IF(A1=25,"Y",IF(A1=26,"Z","超出范围"))
根据情况,可以将?
C、D、E?
这些从事辅助运算的单元格放在其它任何地方
3.?
?
一个单元格也可以实现
=IF(A1=1,"A",IF(A1=2,"B",IF(A1=3,"C",IF(A1=4,"D",IF(A1=5,"E",IF(A1=6,"F",IF(A1=7,"G",IF(A1=8,"H",""))))))))&IF(A1=9,"I",IF(A1=10,"J",IF(A1=11,"K",IF(A1=12,"L",IF(A1=13,"M",IF(A1=14,"N",IF(A1=15,"O",IF(A1=16,"P",""))))))))&IF(A1=17,"Q",IF(A1=18,"R",IF(A1=19,"S",IF(A1=20,"T",IF(A1=21,"U",IF(A1=22,"V",IF(A1=23,"W",IF(A1=24,"X",""))))))))&IF(A1=25,"Y",IF(A1=26,"Z",""))(数组形式输入)。
5.?
以一例:
a1=1,2,3,4,5,6,7,8,9,10
6.?
?
b=if(a1=1,"一",if(a1=2,"二",if(a3=3,"三",.......if(a1=9,"九",if(a1=10,"十")))))),if超过7层不起作用,我该如何办
解答:
1、b=if(a1>5,if(a1=6,"六"。
。
。
。
。
。
)),明白意思?
就是截为两段再做判断,这样可以不超过7重。
2、可以用自定义数字格式。
也可以用=CHOOSE(A1+1,"一二三四五六七八九十")
问:
实际上我的要求是现行高一成绩统计中:
b=if(a1="语文","语文",if(a1="数学","数学",if(a1="英语","英语",if(a1="物理","物理",if(a1="化学","化学",if(a1="历史","历史",if(a1="政治",政治",if(a1="生物","生物“,if(a1="地理","地理")))))))),这样超过了7层。
我不知如何处理。
因为下面的公式要引用语、数、英、等。
答:
新建一表,取名Data,找一区域设置名称为SubjectTable:
语文Chinese
英语English
..
=vlookup(SubjectTable,a1,2,false)可以有65536个,够了吧。
其实,稍加改进,理论上,可以有达到你硬盘空间的个数。
或用if和or的组合可以解决15个。
再举个例子:
=IF(A16="","",IF(B16="","样办尚未交",IF(OR(B16="内部检查中",B16="数据查询中",B16="数据查询中"),CONCATENATE(IF(B16="内部检查中","品质检测中",""),IF(B16="数据查询中","图纸未确认",""),IF(B16="为不合格","需要修正","")),CONCATENATE(IF(C16="客户检查中","待客回复",""),IF(C16="合格","待P/O生产",""),IF(C16="取消","客户取消",""),IF(C16="为客户设变中","客户设变中",""),IF(C16="不合格","需要修正","")))))
从个人所得税的计算谈IF函数的使用
?
?
?
最近,国家采取提高公务员和职工的工资水平,低收入阶层的收入的政策以启动消费推动经济增长,相当一部分人工资收入达到和超过了交纳个人所得税的水平。
根据我国税法的规定,个人所得税是采用超额累进税率分段计算,
?
?
?
见表一:
级数
应纳税所得额
税率%
速算扣除数
1
不超过500元的
5
0
2
超过500元2000元的部分
10
25
3
超过2000元5000元的部分
15
125
4
超过5000元20000元的部分
20
375
5
超过20000元40000元的部分
25
1375
6
超过40000元60000元的部分
30
3375
7
超过60000元80000元的部分
35
6375
8
超过80000元100000元的部分
40
10375
9
超过100000元的部分
45
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*0.45-15375,IF(E2>80000,E2*0.40-10375,IF(E2>60000,E2*0.35-6375,IF(E2>40000,E2*0.30-3375,IF(E2>20000,E2*0.25-1375,IF(E2>5000,E2*0.20-375,IF(E2>2000,E2*0.15-125,IF(E2>500,E2*0.10-25,E2*0.05))))))))。
?
?
?
该函数十分亢长,其中嵌套了7个同样的IF函数,从第2个IF函数开始到最后是第1个嵌套函数,从第3个IF开始到最后是第2个嵌套函数……,为帮助理解,我们将这些嵌套函数分别设为X1、X2、……于是将整个函数简化如下:
?
?
?
IF(E2>100000,E2*0.45-15375,X1)
?
?
?
该函数意为:
当E2中工资额大于100000元时,H2中计算出的应纳所得税额为E2*45%-15375,否则(指当工资额等于或小于100000元时),则H2应按X1的方法计算;把X1展开:
?
?
?
IF(E2>80000,E2*0.40-10375,X2)
?
?
?
其含义与上面相仿。
最后一个嵌套函数X7展开为:
?
?
?
IF(E2>500,E2*0.10-25,E2*0.05)
?
?
?
该函数意为:
当E2中工资额大于500元时,H2中计算出的应纳所得税额为E2*10%-25,否则(当工资额等于或小于500元时),H2等于E2*5%。
?
?
?
怎么样,明白了吗?
够复杂的,好在计算个人所得税只有一种规定,将别人的公式搬过来用就是了。
一般单位个人的工资不会高的太吓人,我们可根据人员的实际工资水平减少函数的嵌套数目简化运算。
?
?
?
学会灵活使用IF函数
?
?
?
从以上计算个人所得税的例子中可以看出,学会在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,"0")
?
?
?
在E5中建立函数如下:
=IF(C5<0,C5*(-1),"0")
?
?
?
再将已建立的函数复制到其他相应的单元格中去即可。
(图四)
?
?
?
如果在单元格中不想将“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
51.24
20000以内的提1.2%
赵六
25135
402.16
30000以内的提1.4%
黑七
2154
21.54
40000以内的提1.6%
白云
21354
341.664
50000以内的提1.8%
红枣
45111
811.998
50000以上的提2%
啊好
851352
17027.04
公式:
=IF(C4<=10000,C4*0.01,IF(C4<=20000,C4*0.012,IF(C4<=3000,C4*0.014,IF(C4<=40000,C4*0.016,IF(C4<=50000,C4*0.018,IF(C4>50000,C4*0.02))))))
例2取款
654321卡资料
请插入磁卡!
卡号
654321
卡号:
654321
密码
123456
请输入密码!
余额
1000元
密码:
123456
请输入您要支取的数额!
金额:
5000
取款机反应:
对不起你的卡上金额不足,请重输!
公式:
=IF(E16="","请插入磁卡!
",IF(E18="","输入密码!
",IF(OR(E16<>B16,B17<>E18),"您的磁卡无效,或密码不正确,请重新输入",IF(E20="","请输入金额!
",IF(E20>B18,"对不起你的卡上金额不足,请重输!
",IF(INT(E20/50)*50<>E20,"对不起,您输入的金额应是50的倍数!
","您的申请成功,系统正在操作中,请稍候……"))))))
大凡所有的程序都是从这些最基础的判断做起的,例2模仿了取款机用户取款的反应过程。
实际的程序比这复杂的多,但原理是一样的。
看起来公式很长,但是它是由几个很简单的IF函数嵌套而成的。
实际的应用当中有很多是比这个复杂的。
我们看一这一段公式:
=IF(E16="","请插入磁卡!
",IF(E18="","输入密码!
",……,p里面是两个IF的嵌套,第一个if:
条件E16=""成立执行第一个分支"请插入磁卡!
"就不管后面的了,因为对于第一个IF来说后面的公式只是其中的一个参数。
同理,条件E16=""不成立,excel就不理第一个分支了,就会直接跳到第二个分支了:
IF(E18="","输入密码!
",……碰到第二个if时,又对条件E18=""进行判断,条件E18=""成立执行它的第一个分支"输入密码!
",不成立又会跳过第一个分支"输入密码!
",执行后面的公式,以此类推......
再示例:
=if(a2>700,">700",if(a2>600,"600-699",if(a2>500,"500-599",if(a2>400,"400-499",if(a2>300,"300-399",if(a2>200,"200-299",if(a2>100,"100-199","<100")))))))
新税率如何利用EXCEL计算个人所得税
国家税务局国税发(2011)第20号规定工资、薪金所得,以每月收入额减除费用3500元后的余额,为应纳税所得额,计算征收个人所得税。
个人所得税为超额累进税,即超过一定的额度后按不同的税率计算。
税率表如下:
下限
1
不超过1500元的
0
1500
3%
0
1500
2
超过1500元至4,500元的部分
1500
4500
10%
105
3000
3
超过4,500元至9,000元的部分
4500
9000
20%
555
4500
4
超过9,000元至35,000元的部分
9000
35000
25%
1005
26000
5
超过35,000元至55,000元的部分
35000
55000
30%
2755
20000
6
超过55,000元至80,000元的部分
55000
80000
35%
5505
25000
8
超过80,000元的部分
80000
1E+308
45%
13505
1.E+308
假设税前收入为8400(为简
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- Excel IF 函数 嵌套 用法
![提示](https://static.bdocx.com/images/bang_tan.gif)