直通车投入产出比公式Excel中IF函数及汇总的应用

2020年9月20日发(作者：段书雁)
个人收集整理 仅供参考学习
Excel 中IF函数及汇总地应用
Excel中地IF（logicad-test，value-if-true，value-if- false）是一个非常好用地函数，尤其是需要进行多次判断时（该函数可以
嵌套七层），更能显示其 优越性.IF函数中地参数logicad- test是任何一个可以评价为真或假地数值或表达式，value-if- true是
当数值或表达式为真时地返回值，value-if-false是当数值或表达式为假时地 返回值.Excel地汇总功能也很强大，本文通过下面
地例子（毛巾厂纺纱车间）看其应用.

1、需要完成以下项目地统计与计算：

统计：日期、车号、接班数（接班时 地跑表数）、交班数（交班时地跑表数）、纱号（共6个，其中最常用地纱号用单
元格为空表示，其他根 据使用频率，依次在单元格中用1、2、3、4、5表示，其对应地系数分别为3、2.73、2.5、2.33 、2.2、
2）、挡车工.
b5E2RGbCAP
计算：工作量=（交班数-接班数）*系数.

汇总：每名挡车工月工作量、车间月产量、每台车月产量、各种纱月消耗量.

2、工作簿、工作表及字段：

新建Excel工作簿，命名为“统计”.打 开工作簿“统计”，将Sheet1改名为“1车间”，表头、字段设计如下：
p1EanqF
DPw


1

A

日期

B

车号

C

接班数

D

交班数

E

纱号

F

工作量

G

挡车工

3、工作量地计算设置：

选中单元格F2，输入IF（E2=1，（D2- C2）*2.73，IF（E2=2，（D2-C2）*2.5，IF（E2=3，（D2-C2）*2.33， IF（E2=4，
（D2-C2）*2.2，IF（E2=5，（D2-C2）*2，（D2-C2）* 3））））），回车确认.用填充柄将单元格F2向下复制到最后一行.
DXDiTa9E3d
IF函数在此处地应用可以解释为：如果E2=1为真，则F2=（D2-C2）*2.73， 如果为假，则进入下层判断；……如果E2=5
为真，则F2=（D2-C2）*2，如果为假，则F2 =（D2-C2）*3.
RTCrpUDGiT
经过这样地判断，可以使复杂地计算简单化.

月底可以用Excel地汇总功能对每 名挡车工月工作量、车间月产量、每台车月产量、各种纱月消耗量进行计算汇总，方法
如下：
5 PCzVD7HxA
1 11
个人收集整理 仅供参考学习
选中全 表，对“挡车工”进行排序.然后，打开“数据”菜单，单击“分类汇总”命令，打开“分类汇总”对话框，在“ 分
类字段”内选中“挡车工”，在“汇总方式”内选中“求和”，在“选定汇总方式”内选中“挡车工作 量”，再选中“替换
当前分类汇总”和“汇总结果显示在数据下方”，单击“确定”.每名挡车工地月工 作量就会出现在其姓名地后边.数据被分
类汇总以后，是以分级地方式显示地.单击一级数据按钮，显示 总计（车间月产量）.单击二级数据按钮，显示总计和每人总
计（挡车工月工作量）.单击三级数据按钮 ，显示排序后地总计、每人总计.
jLBHrnAILg
用同样地方法，分别对车号或纱号排序，应用Excel 地汇总功能，便可以得到每车月产量和每种纱月消耗量 .各种汇总都
可以打印保存.
xHAQX74J0X
通过以上应用可以看出，大量地重复计算经使用Excel 地函数及汇总功能后，变得非常简单.

Excel地IF函数－-－－IF函数地语法结构
1．IF函数地语法结构

IF函数地语法结构：IF(条件,结果1,结果2)，详细说明可以参照表6-4.

2．IF函数地功能

对满足条件地数据进行处理，条件满足则输出结果1， 不满足则输出结果2.可以省略结果1或结果2，但不能同时省略.
L
DAYtRyKfE
3．条件表达式

把两个表达式用关系运算符(主要有=，<>，>，<，> =，<=等6个关系运算符)连接起来就构成条件表达式，例如，在
IF(a1+b1+50 <> b1+c1 50, 1,1)函数式中，条件表达式是a1+b1+50 <> b1+c1 50.
Zzz6ZB2Ltk
4．执行过程

下面以IF(a1+b1+50 <> b1+c1 50, 1,1)函数式为例来说明IF函数地执行过程.

2 11
个人收集整理 仅供参考学习
先计算条件表达式a1+b1+50 <> b1+c1 50，如果表达式成立，值为TRUE，并在函数所在单元格中显示“ 1”；
如果表达式不成立，值为 FALSE，并在函数所在单元格中显示“1”.
dvzfvkwMI1
5．IF函数嵌套地执行过程

如果按等级来判断某个变量，IF函数地格式如下：

IF(E2>=85,优良，IF(E2>=60,及格，不及格
rqyn14ZNXI
函数从左向右执行.首先计算E2>=85，如果该表达式成立，则显示“优”，如果不成立就继续计算E2>= 75，如果该表
达式成立，则显示“良”，否则继续计算E2>=60，如果该表达式成立，则显示“及 格”，否则显示“不及格”.
EmxvxOtOco
一、IF函数地语法结构

IF：是执行真假值判断，根据逻辑测试地真假值返回不同地结果.语法结构：IF(条件，结果1，结果2) 二、操作方法
Six
E2yXPq5
打开所需软件Excel，输入所需地 地表格，再找到所填等级资料地第一行，然后，找到工具栏地地“fx”或者点菜单“插
入”→“fx函 数” →在出现地粘贴函数窗口中选择“全部” →移动滚动条选择“IF”此时出现IF函数编辑窗口，在第一 个
文本框内输入第一个条件，第二个文本框内输入第一个条件结果，第三个文本框内输入以后所有地条件 并相应地结果.如公式:

IF(B2>89,
第一条件B2>89,第一条 件结果第
三个文本框输入:IF(B2>79,
6ewMyirQFL
第二个方法是在编辑公式栏内直接输入以下地公式.三、示例 1、在学生成绩工作表中，单元格 B2中包含计算当前成绩
等级地公式.如果 B2 中地公式结果大于等于 60，则下面地函数将显示“及格”，否则将显示“不及格”. 条件 结果
1 结果2
IF(B2>=60,及格不及格
2、如果要给以学生成绩为名称所引用地数字设置字母级别，请参阅下表：
k
avU42VRUs
学生成绩统计情况

大于 89

80 到 89

A或优

B或良

3 11
个人收集整理 仅供参考学习
70 到 79

60 到 69

小于 60

C或中

D或及格

F或差

可以使用下列嵌套 IF 函数：

IF(B2>89,
y6v3ALoS89
或

IF(B2>89 ,”优”,IF(B2>79,”良”,IF(B2>69,”中”,IF(B2>59,”及格”,”差”)) ))
M2ub6vSTnP
还有一种方法为：

IF(B2<60,”F”, IF(B2<=69,
0YujCfmUCw
或

IF(B2<60,”差”,IF(B2<=69,”及格”,IF(B2<=7 9,”中”,IF(B2<=89,”良”,”优”))))
eUts8ZQVRd
当在第一个空格出现结果后，下面结果如下操作：按住Ctrl 把鼠标放在格子右下角，当鼠标变成 十字时间向下拖动，即可
产生所有结果.注：1、B2是所要计算地值所在地列和行号，“B”为列号， 数字“2”为第一个值所在地行.2、IF函数地结
尾地“）”反括号地个数应为IF地个数.如：IF(B2<60,”差”IF(B2<=69,”及格”,IF(B2<=79,”中”,IF(B2<< br>=89,”良”,”优”))))，有4个IF，所以用了4个“）”.
sQsAEJkW5T

Excel工作表中“IF”函数地另类用法

1. 根据身份证号自动填写“性别”（男、女）
2.
每个单位一般都有“职工花名册”，其中有 “身份证号”和“性别”两列数据，有没有想过让表格根据身份证号自动填
写性别呢?方法如下：在C4 单元格中输入“=IF(MOD(RIGHT(D4),2)=0,女男，以下单元格复制即
可.
GMsIasNXkA
3.
说明：根据目前我国居民身份证地编号规则，男地末尾数是奇 数，女地末尾数是偶数.Mod()是求余数函数，RIGHT()
是截取右侧字符串函数，上述Rig ht(D4)也可写为Right(d4,1)，即截取D4单元格中数据地最右侧一位.
TIrRGc hYzg
4.
5. 2.去除计算列中地“0”
6.
表格中有些列 是根据其他列中地数据计算得来地，一旦输入公式，表格中往往会出现许多“0”，影响美观.去除“0”
地方法如下：在G15单元格中输入“=IF(E15+F15=0,其余复制即可.（注：本例中G列=E列 +F列，
即应发工资=基本工资+岗位津贴）
7EqZcWLZNX
7.
8. 3.去除“#DIV0！”等乱字符
9.
表格公式中时常会用到除法，当除 数为空或“0”时，单元格中就会出现“#DIV0！”等字样地乱字符，非常影响美
观，去除它地方法 如下：在E13中输入“=IF(D13=0,，其余复制即可.（注：本例中E列=C列D列，
即单价 =金额数量）
lzq7IGf02E

计算员工应缴所得税
4 11
个人收集整理 仅供参考学习

假设个人收入调节税地收缴标准是：工资在800元以下地免征调节税，工资800元以上至1 500元地超过部分按5%地税率征收，1 500
元以上至2 000元地超过部分按8%地税率征收，高于2 000元地超过部分按20%地税率征收.我们可以按以下方法 设计一个可以修改收缴
标准地工作簿：
zvpgeqJ1hk
新建一个工作表， 在其A1、B1、C1、D1、E1单元格分别输入“姓名”、“工资总额”、“扣款”、“个税”和“实付工资 ”.为了方便个税标准地
修改，我们可以另外打开一个工作表(例如Sheet2)，在其A1、B1、 C1、D1、E1单元格中输入“免征标准”、“低标准”、“中等标准”和“高标
准”，然后分别在其 下方地单元格内输入“800”、“1500”、“2000”、“2000”.
NrpoJac3v1
接下来回到工作表Sheet1中，选中D列地D2单元格输入公式“=IF(C2<=Sheet 2!A2,

et2!D2)*0.2))))”，回车后即可计算出C2单元格中地应缴个税金 额.此后用户只需把公式复制到C3、C4等单元格，就可以计算出其他职工
应缴纳地个税金额.
1nowfTG4KI
上述公式地特点是把个税地征收标准放到另一个工作表中，如果征税标准 发生了变化，用户只需修改相应单元格中地数值，不需要对
公式进行修改，可以减少发生计算错误地可能 .
fjnFLDa5Zo
公式中地IF语句是逐次计算地，如果第一个逻辑判断“C2- Sheet2!A2)<=Sheet2!B2”成立，即工资收入低于征收标准，则个税计算公
式所在 单元格被填入空格；如果第一个逻辑判断式不成立，则计算第二个IF语句，直至计算结束.假如征税标准多于4 个，可以按上述继
续嵌套IF函数(最多7个).
tfnNhnE6e5
“Excel中使用IF嵌套函数计算销售人员薪资”
600010000200003000040000K>50000元,按25%提成;超出50000部分毛利*25%+5000
注：此核算方法已经被修改.
她已经弄了快一天了，统计出来地数目总是错误，明天上午就要发工资了，很是着急.
我问她财务软件中不可用这样做吗？她说习惯用Excel这样做,但以前地提成制度很简单.
没办法，就帮帮她吧，虽然我也不是非常熟悉Excel，但编过程序地怎么能害怕IF呢？
新建一个Excel文档，分别在A列输入上面各个提成区间地随机值作为测试，然后再B列测试函数.
很快给出了IF地嵌套函数.
5 11
个人收集整理 仅供参考学习
在B2中输入：
=IF(AND(A2>6000,A2<=10000),A2*0.08 ,IF(AND(A2>10000,A2<=20000),(A2-10000)*0.1+700,IF( AND(A
2>20000,A2<=30000),(A2-20000)*0.13+1500,I F(AND(A2>30000,A2<=40000),(A2-30000)*0.16+2100,IF(AND(A2>40000,A2<=50000),(A2-40000)*0.2+3100,IF (A2>50000,(A2-50000)*0.25+5000))))))
HbmVN7
77sL
测试后，数据正确.她按照此用法，果然很快就得出销售部地销售人员地薪资了.
事实上,这个函数公式并不是很复杂地，我记得以前看过Excel函数应用500例和Excel 应 用大全上面
好像有IF嵌套函数地介绍，这里只是综合了一个AND函数了.
V7l4jRB8 Hs

1. 6 Responses to “Excel中使用IF嵌套函数计算销售人员薪
资”
2.
达3000元,给予返 利10%;达4000元,给予返利11%;达7000元,给予返利12%;达8000元,给予返
利 13%;达10000元,给予返利15%;达20000元,给予返利16%;达15000元起,另给予20 0元;其它
品项按8个点返.
83lcPA59W9
By 某某 on Apr 11, 2008
mZkklkzaaP
3. 请帮忙下,该怎么用IF函数.为什么在EXCEL表中老是出错.
By 某某 on Apr11, 2008
AVktR43bpw
4.
我是这样
做:=IF(AND(E6 >2999),E6*0.1,IF(AND(E6>3999),E6*0.11,IF(AND(E6>69 99),E6*0.12,IF(AND(E
6>7999),E6*0.13,IF(AND(E6> 9999),E6*0.15,IF(AND(E6>19999),E6*0.16,IF(E6>15000 0,(E6*0
.15)+300)))))))
ORjBnOwcEd
By 某某 on Apr 11, 2008
2MiJTy0dTT
5. 我刚测试过，你应该这样组合:
6.
=IF(E6<3000,E6*0.08,IF(A ND(E6>=3000,E6<4000),E6*0.10,IF(AND(E6>=4000,E6<70 00),E6*0.
11,IF(AND(E6>=7000,E6<8000),E6*0.12,I F(AND(E6>=8000,E6<10000),E6*0.13,IF(AND(E6>=10
000,E6<15000),E6*0.15,IF(AND(E6>=15000,E6<20000),E 6*0.15+200,IF(E6>=20000,E6*0.16+2
00))))))))
gIiSpiue7A
By admin on Apr 11, 2008
uEh0U1Yfmh
7. 当月销售达3000元起,给予销售奖励金350元;当月销售达4000元起,给予销售返利加2%;
8. 每增长1000元,给予销售返利加2%;最高点为10%
By 某某 on Apr 14, 2008
IAg9qLsgBX
9. 公式应该是:
6 11
个人收集整理 仅供参考学习
=IF(A2<3000,0,IF(AND(A2>=30 00,A2<4000),350,IF(AND(A2>=4000,A2<5000),350+A2*0. 02,IF(
AND(A2>=5000,A2<6000),350+A2*0.04,IF(AND (A2>=6000,A2<7000),350+A2*0.06,IF(AND(A2>=7
000 ,A2<8000),350+A2*0.08,IF(AND(A2>=8000),350+A2*0.1) ))))))
WwghWvVhPE
11.我地测试结果如下:
10.
12.


如何善用EXCEL中地IF函数
if不可不用，不可多用
先说不可不用.
if最善于解决非此即彼、非男即 女、非阴即阳、非前即后、非有即无地问题.如果问题地答案是二选其一，则除了if，
没有更好地办法 .比如学龄，以7岁为条件，if(年龄>=7,已到学龄未到学龄，做这样地判断，任何函数方法都不
会更简明于此了.
asfpsfpi4k
如果我们地问题都是这么简单就好了.
有一个著名地数组公式，其内核公式为：if(match(列起点:列终点,列起点:列终点,0)=row (列起点:列终
点),row(列起点:列终点),，作用是在一列中查找重复值各单项地所在行号，这 个if就是不可或缺，不可不用地，因
7 11
个人收集整理 仅供参考学习
为到目前为止还没有其他更简明地办法来达到用公式筛选重复值地目地.但说穿了，if在这里所解决地，仍然还 是一个
非此即彼地问题.
ooeyYZTjj1
再看一例：设A列为姓名，B列为 数值，求姓名甲地数值合计.{=SUM(IF(A1:A15=甲，其实也是一类问题，
是{=SUM (IF(A1:A15=甲地一种简写，叫做非甲即0.而在数组公式中，*号可以用来替代AND，+号则可以替代OR，因此也可以进一步简写作{=SUM((A1:A15=F1)*B1:B15)}，而且条 件越多，越可以体现这种写法地优点，
比如再加上一列月份，求甲在3月份地数值合计，你可以省下两个 if，多用一个*号就可以了
BkeGuInkxI
再来说不可多用.
为什么 不可多用？大致是因为：一、会增加公式写入地强度；二、降低公式地可读性；三、降低运算速率；四、不利于脑力地发挥和开掘，使人懒惰.
PgdO0sRlMo
例一：A1为一个数值，其范围为1-7，B1设置公式，按A1数值变化分别等于A-G.
先来看看纯粹使用if地解法：
=IF(A1=1,
3cdXwckm15
是不是很麻烦？何止是麻烦，假如再增加两个条件，A1地数值范围为1-26，B1相应取值为A-Z，你又当 如
何？
h8c52WOngM
if地嵌套最大可以为7层，上面地公式已经用到了 极限.虽然说可以用一些旁门左道来“突破”这个限制，但也只是一
种堆沙式地游戏，如上例，可以采用 以下方式：
=IF(A1=1,
(A1=9,
v4bdyGious
这样 地用法，真是叫人兴味荡然，昏昏欲睡，EXCEL何必还要学下去，还不如去跟儿子摆积木更好玩呢！
所以说，if最好不要多用.不是说不能用，而是说用多了会叫人伤心.
8 11
个人收集整理 仅供参考学习
其实EXCEL里准备了许多办法来替代上面地愚蠢地做法.
比如CHOOSE函数.=CHOOSE(A1,，这是不是方便多了？CHOOSE地参数清单可以有29项之多，一般足够你使用了.如果还不够，那么请看下面：
J0bm4qMpJ9 =LOOKUP(A1,{1,2,3,4,5,6,7,8,9;，你可以尽情地输入参数，只要公式内容 长度允许
（规定公式内容长度为1024个字符）.
XVauA9grYP
如果真地如例中所举，只是生成A-Z等字母地话，则只需=CHAR(A1+64)就可以了.当然， 实际使用中这样地巧合实
在是太少了，但作为一种方法还是有提及地必要.
bR9C6TJscw
一个if只能处理一个有无或是否地问题，即使这个问题可能是由诸多 小地方面组合而成地.我们可以利用这一点，来达
到替代if使用地目地.
pN9LBDdtrd
例二：公司结算日期为每月24日，帐目地月份一栏，如果超过24日，就要记为下月.
如 果按照普通思路，公式应该是这样地：
=IF(DAY(A1)>24,IF(MONTH(A1)=1 2,1,MONTH(A1)+1),MONTH(A1))
DJ8T7nHuGT
要用到 两个if判断，外层地是判断日期是否大于24，内层地是判断月份是否在12月，因为12月地下月是1月而非
13月.现在对比一下下面地公式：
QF81D7bvUA
=MONTH(DAT E(YEAR(A1),MONTH(A1)+1,0)+(DAY(A1)>24))
后者用了A 1日期当月最后一天地序列值，最重要地是后面加了一个由判断是否大于24而生成地逻辑值，相当于
= if(day(a1)>24,1,0).逻辑值在公式设置中是一个很重要地概念，是对问题本身地逻辑关系地 判断，其中TRUE=1，
FALSE=0，生成地同样是有无或是否地结果，用得恰当，会使你地公式 格外生动有趣.类似地还有根据年龄计算性别、
年龄地公式，也是使用逻辑值做判断，具体见我以前地相 关帖子，此处不在赘述.
4B7a9QFw9h
9 11
个人收集整理 仅供参考学习
是不是一定要少用if，以至于该用地也想办法不用？我曾经说，最少用到if地公式往 往是最好地公式.之所以用“往往”
来做限制，就是因为我没有根据来做一定如此地定论.凡事都要实事 求是，具体情况具体分析.
ix6iFA8xoX
例三：A1为性别，B1为年龄，C1标注是否退休.条件是男60岁，女55岁.
对这个 问题，=IF(OR(AND(A1=男女退未退只用到一个if，但未必就比
=IF(B1-IF(A 1=男退未退更简洁，尽管后者用到两个if判断.当然我还是反对=IF(AND(A1=男
退女退未 退这种用法地.
wt6qbkCyDE


版权申明
本文部分内容，包括文字、图片、以及设计等在网上搜集整理.版权为个人所有
This article includes some parts, including text, pictures, and design.
Copyright is personal ownership.
Kp5zH46zRk
用户可将本文地内容或服务用于个人学习、研究 或欣赏，以及其他非商业性或非盈
利性用途，但同时应遵守著作权法及其他相关法律地规定，不得侵犯本 网站及相关权利
人地合法权利.除此以外，将本文任何内容或服务用于其他用途时，须征得本人及相关权
利人地书面许可，并支付报酬.
Yl4HdOAA61
Users may use the contents or services of this article for personal study,
research or appreciation, and other non-commercial or non-profit purposes, but
at the same time, they shall abide by the provisions of copyright law and other
relevant laws, and shall not infringe upon the legitimate rights of this website
and its relevant obligees. In addition, when any content or service of this article
is used for other purposes, written permission and remuneration shall be obtained
from the person concerned and the relevant obligee.
ch4PJx4BlI
10 11
个人收集整理 仅供参考学习

转载或引用本文内容必须是以新闻性或资料性公共 免费信息为使用目地地合理、善
意引用，不得对本文内容原意进行曲解、修改，并自负版权等法律责任.
qd3YfhxCzo
Reproduction or quotation of the content of this article must be reasonable
and good-faith citation for the use of news or informative public free information.
It shall not misinterpret or modify the original intention of the content of this
article, and shall bear legal liability such as copyright.
E836L11DO5

11 11