Excel公式与函数实例详解.docx
- 文档编号:9220358
- 上传时间:2023-02-03
- 格式:DOCX
- 页数:25
- 大小:1.61MB
Excel公式与函数实例详解.docx
《Excel公式与函数实例详解.docx》由会员分享,可在线阅读,更多相关《Excel公式与函数实例详解.docx(25页珍藏版)》请在冰豆网上搜索。
Excel公式与函数实例详解
Excel2007公式与函数实例详解
1、使用IF函数判断员工是否完成任务:
1)在Excel中打开“员工销售业绩”工作薄。
2)选择G3单元格,用鼠标单击编辑栏。
3)在编辑栏中输入公式“=IF(F3>=E3,”完成”,”未完成”)”,如下图所示。
公式的说明:
使用IF函数判断F3单元格的数据是否大于或等于E3单元格的数据。
如果F3单元格的数据大于或等于E3单元格的数据,则结果为“完成”;如果F3单元格的数据小于E3单元格的数据,则结果为“未完成”。
4)按键盘上的
5)将鼠标指针移到G3单元格区域的右下角,当鼠标指针变成十字形状时,单击鼠标左键并拖曳鼠标至G10单元格,然后释放鼠标,即可计算出其他员工是否完成销售任务。
2、使用SUMIF函数按条件对数字进行求和:
1)在Excel中打开“业绩表”工作薄。
2)选择C18单元格,用鼠标单击编辑栏。
3)在编辑栏中输入公式“=SUMIF(B3:
B17,”顾×”,C3:
C17)”,如下图所示。
公式的说明:
在B3:
B1单元格区域中查找作者等于“顾×”的,然后在C3:
C17区域中查找与“顾×”所对应的页码进行累计求和。
4)按键盘上的
3、计算电话的通话费用:
1)在Excel中打开“电话费用”工作薄。
2)选择C3单元格,用鼠标单击编辑栏。
3)在编辑栏中输入公式“=60*MINUTE(B3)+SECOND(B3))”,如下图所示。
公式的说明:
①MINUTE(B3):
表示提取B3单元格中通过时间的分钟数,其结果为5。
②60×MINUTE(B3):
表示将分钟数转换为秒数(即,60×5),其结果为300。
③SECOND(B3):
表示B3单元格中通话时间的秒数,其结果为10。
4)按键盘上的
5)选择C3单元格,将“时间”格式更改为“常规”格式。
6)将鼠标指针移到C3单元格区域的右下角,当鼠标指针变成十字形状时,单击鼠标左键并拖曳鼠标至C6单元格,然后释放鼠标,即可计算出其他员工的通话时间。
7)选择E3单元格,用鼠标单击编辑栏。
3)在编辑栏中输入公式“=CEILING(C3/9,1)*D3”,如下图所示。
公式的说明:
①C3/9:
表示每9S计一次通话费用,其结果为34.444444。
②CEILING(C3/9,1):
表示对C3/9向上舍入并最接近1的倍数,其结果为35。
9)按键盘上的
10)将鼠标指针移到E3单元格区域的右下角,当鼠标指针变成十字形状时,单击鼠标左键并拖曳鼠标至E6单元格,然后释放鼠标,即可计算出其他员工的通话费用。
4、使用ROUND函数按位数进行四舍五入:
1)在Excel中打开“学生成绩表”工作薄。
2)选择J3单元格,用鼠标单击编辑栏。
3)在编辑栏中输入公式“=ROUND(AVERRAGE(B3:
H3),3)”,如下图所示。
公式的说明:
①AVERRAGE(B3:
H3:
表示对B3:
H3单元格区域求平均值。
②ROUND(AVERRAGE(B3:
H3),3):
表示根据所求的平均值,对平均成绩的数据保留小数点后3位数,并对最后一位小数进行四舍五入。
4)按键盘上的
5、计算员工工资所需各种面值的人民币张数:
1)在Excel中打开“工资表”工作薄。
2)在“工资”工作表中,将B4:
B11和I4:
I11的单元格区域复制到“面值张数”工作表中的A4:
B11单元格区域。
3)选择G4单元格,用鼠标单击编辑栏。
4)在编辑栏中输入公式
“=INT(MOD(MOD(MOD(MOD(B4,$C$3),$D$3),$E$3),$F$3)/$G$3)”,按键盘上的
公式的说明:
①MOD(B4:
$C$3):
表示用MOD函数对B4单元格的数据除以C3单元格的数据取余数,其结果为30.00。
②MOD(MOD(B4:
$C$3),$D$3):
表示用MOD函数对根据取得的余数(30.00)除以D3单元格的数据取余数,其结果为30.00。
③MOD(MOD(MOD(B4,$C$3),$D$3),$E$3):
表示用MOD函数对根据取得的余数(30.00)除以E3单元格的数据取余数,其结果为0.00。
④MOD(MOD(MOD(MOD(B4,$C$3),$D$3),$E$3),$F$3):
表示用MOD函数对根据取得的余数(0.00)除以F3单元格的数据取余数,其结果为0.00。
⑤此时的公式变为“=INT(0.00/$G$3)”:
表示用INT函数对根据取得的余数(0.00)除以G3单元格的数据向下舍入取整,其结果为0。
5)选择C4:
G4单元格区域,将鼠标指针移到E3单元格区域的右下角,当鼠标指针变成十字形状时,单击鼠标左键并拖曳鼠标至C11:
G11单元格区域,然后释放鼠标,即可计算出全体员工所需各种面值的人民币张数。
6、使用LOOKUP函数查找员工信息:
1)在Excel中打开“员工登记表”工作薄。
2)选择K3单元格,输入数据“张×”。
3)选择K5单元格,在编辑栏中输入公式“=LOOKUP(K3,A3:
H12)”,即可查询到该员工的基本工资,如下图所示。
公式的说明:
在A3:
H12单元格区域中查找K3单元格中的数值(即A10单元格中的数值“张×”),然后返回最后一列(即,H列)与A10单元格所对应的同一行的数值(即,700)。
7、使用HLOOKUP函数实现水平查找:
1)在Excel中打开“业绩奖金核算表”工作薄,如下图所示。
2)选择K3单元格,在编辑栏中输入公式“=HLOOKUP(E4,Sheet1!
$B$4:
$G$5,2)”,即可查询到该员工的奖金比例数据,如下图所示。
公式的说明:
在“Sheet1”工作表的“$B$4:
$G$5”单元格区域中的第1行查找匹配E4单元格中的数值(即,G4单元格的数据15000),然后在第2行中查找与G4单元格中所对应的同一列值即“32%”。
8、使用VLOOKUP函数实现竖直查找:
1)在Excel中打开“第一季度加班费”工作薄,如下图所示。
2)在“统计”工作表中选择B4单元格,在编辑栏中输入公式=VLOOKUP(A4,’1月’!
A4:
AH4,34)”,即可得到其他员工的1月份加班费金额,如下图所示。
公式的说明:
在“1月”工作表的“A4:
AH4”单元格区域中的第1列查找A4单元格中的数值,然后返回第34列中与A4单元格中所对应的同一行值即“160”。
9、使用INDEX函数得到指定的内容:
1)在Excel中打开“日常费用统计表”工作薄,如下图所示。
2)在“12月”工作表中选择C4单元格,在编辑栏中输入公式=INDEX(’11月’!
$A$4:
$E$11,1,4)”,即可得到水费到上期费用的数据,如下图所示。
公式的说明:
在“11月”工作表的“$A$4:
$E$11”单元格区域中查找第1行与第4列交叉处的数值(即,2,420.00)。
10、根据位置查询信息:
1)在Excel中打开“产品生产单”工作薄,如下图所示。
2)在“Sheet2”工作表中分别选择B3、C3、D3单元格,分别输入数据“982HR”、“电脑”、“4车间”。
3)选择E3单元格,在编辑栏中输入公式=INDEX(Sheet1!
A3:
G42,MATCH(B3,Sheet1!
D3:
D42,0),6),即可得到型号为“982HR”的产品的总成本,如下图所示。
公式的说明:
①MATCH(B3,Sheet1!
D3:
D42,0):
表示在“Sheet1”工作表的“D3:
D42”单元格区域中查找B3单元格中的数据,然后返回该数据的位置(即,36)。
②此时的公式变为“INDEX(Sheet1!
A3:
G42,36,6):
表示在“Sheet1”工作表的“A3:
G42”单元格区域中查找第36行与第6列交叉处的数据。
11、使用OFFSET函数建立动态图表数据源:
1)在Excel中打开“产品销售数量表”工作薄),如下图所示。
2)在“Sheet2”工作表中分别选择A2单元格,输入数据“0”。
3)选择D3单元格,在编辑栏中输入公式=OFFEST(Sheet1!
B2,0,$A$2),如下图所示。
公式的说明:
显示“Sheet1”工作表中的B2单元格的数值,因以“Sheet1”工作表中的B2单元格的数值为参照系,而偏移行数与偏移列数分别为0(即,没有偏移,所以返回B2单元格的数值),即可得到各地区“键盘”销售数量。
将A2单元格中的数据更改为1,则偏移列数为1,即可得到各地区“鼠标”销售数量。
12、使用COUNTIF函数按性别统计员工人数:
1)在Excel中打开“员工信息表”工作薄。
2)选择H6单元格,在编辑栏中输入公式“=COUNTIF(B3:
B14,”男”)”,即可统计出男员工的总人数,如下图所示。
公式的说明:
在B3:
B14单元格区域中统计性别为“男”的个数。
13、使用COUNTA函数自动添加员工编号:
作为公司的人事部管理人员,经常要统计一些数据。
例如在“人事档案”工作薄中,每增加或减少一名员工时,就需要手动对全体员工重新进行编号,这样操作非常浪费时间,为了提高工作效率,管理人员可以使用COUNTA函数为员工自动添加编号。
使用COUNTA函数为员工添加编号以后,如果在“人事档案”工作薄中删除任何一名员工的资料(例如:
删除“田X”员工的资料),而其他员工的编号将自动更新。
1)在Excel中打开“人事档案”工作薄。
2)选择B4单元格,在编辑栏中输入公式“=COUNTA($C$2:
C3)”,即可员工编号,如下图所示。
公式的说明:
统计$C$2:
C3单元格区域中非空白单元格的个数,其结果为1。
3)将鼠标指针移到B4单元格区域的右下角,当鼠标指针变成十字形状时,单击鼠标左键并拖曳鼠标至B15单元格,然后释放鼠标,即可计算出其他员工的编号。
说明:
此时公式中的参数依次为“$C$2:
C4”、“$C$2:
C5”、…、“$C$2:
C15”,其对应的结果为2、3、…、12。
4)如果员工“田X”离职,此时在人事档案中要将该员工的资料删除,而员工编号自动调整。
14、统计未完成工作量的总人数:
1)在Excel中打开“计划表”工作薄。
2)选择E16单元格,在编辑栏中输入公式“=COUNTBLANK(E3:
E14)”,即可统计未完成工作量的总人数,如下图所示。
公式的说明:
在B3:
B14单元格区域中统计性别为“男”的个数。
15、使用RANK函数对学生成绩排名:
1)在Excel中打开“学生成绩排行榜”工作薄。
2)选择A3单元格,在编辑栏中输入公式“=RANK(J3,$J$3:
$J$10,0)”,即可得到该学生的排名结果,如下图所示。
公式的说明:
按降序的方式查找J3单元格的数据在J3:
J10单元格区域中排位的位置。
16、使用DATEIF函数计算员工年龄:
1)在Excel中打开“员工信息表”工作薄。
2)选择H4单元格,在编辑栏中输入公式“=DATEIF(E4,TODAY(),”Y”)”,即可计算出该员工的年龄,如下图所示。
公式的说明:
用当前日期减去出生日期,然后返回两个日期之间的年数。
提示:
利用DATEIF函数计算员工的年龄,计算结果按周岁显示。
17、从一段信息中分离三段信息:
现在大多数公司有自己的集团卡,在记录这些集团卡时,是由姓名、电话、集团号三部分组成的,并且中间分别用两个分隔号分开。
为了方便查看,本实例使用几个函数将三段信息显示在不同的列中。
1)在Excel中打开“电话记录表”工作薄。
2)选择B3单元格,在编辑栏中输入公式“=LEFT(A3,FIND(“-”,A3)-1)”,即可显示姓名,如下图所示。
公式的说明:
首先使用FIND函数获取“—”的位置,其结果为4,接着4减1,然后使用LEFT函数提取A3单元格3个字符,其结果为高X艳。
3)选择C3单元格,输入公式“=MID(A3,FIND(“-”,A3)+1,FIND(“-”,A3,FIND(“-”,A3)+1)-FIND(“-”,A3)-1)”,即可显示电话,如下图所示。
公式的说明:
①“FIND(“-”,A3)+1”:
表示要从A3单元格中查找“-”的位置,然后加1(即,4+1)。
②“FIND(“-”,A3,FIND(“-”,A3)+1”:
表示要从A3单元格中第5个字符的位置开始查找“-”的位置(即,因为FIND(“-”,A3)+1等于5,所以从第5个字符“1”开始查找第2个字分隔符“-”的位置,其结果为16)。
③“FIND(“-”,A3)”:
表示要从A3单元格中查找“-”的位置(即,结果为4)。
④此时的公式变为“=MID(A3,5,16-4-1)”:
表示从A3单元格中获取11个字符,但需要从第5个字符开始获取。
4)选择D3单元格,输入公式“=RIGHT(A3,LEN(A3)-FIND(“-”,A3,FIND(“-”,A3)+1))”,即可显示集团号,如下图所示。
公式的说明:
①“LEN(A3)”:
表示获得A3单元格中字符串的长度,其结果为20。
②“FIND(“-”,A3,FIND(“-”,A3)+1”:
表示要从A3单元格中第5个字符的位置开始查找“-”的位置(即,因为FIND(“-”,A3)+1等于5,所以从第5个字符“1”开始查找第2个字分隔符“-”的位置,其结果为16)。
③此时的公式变为“=RIGHT(A3,20-16)”:
表示从A3单元格的右方提取4个字符。
18、使用EXACT函数比较两个字符串是否相同:
1)在Excel中打开“库存管理”工作薄。
2)选择E3单元格,在编辑栏中输入公式“=EXACT(A3,D3)”,即可显示该商品的编号是否发生变化,如下图所示。
说明:
在E3单元格显示逻辑值真(TRUE),这表示该商品编号没有发生变化。
19、使用PMT函数来分析贷款的每期偿还金额:
作为公司的财务人员,如果公司投资一个项目,需要向银行贷款50万元(其贷款年限为10-15年,年利率为4%-6.5%,付款方式分别以期初和期末来计算),现根据以上数据来分析本公司按年或月偿还的还款金额,以便安排另外的投资计划。
本实例使用PMT函数来计算每期的偿还金额。
1)在Excel中打开“项目投资分析表”工作薄。
2)选择D4单元格,在编辑栏中输入公式“=PMT(C4,B4,A4,0,1)”,即可计算该项贷款每年初的偿还金额,如下图所示。
3)选择E4单元格,在编辑栏中输入公式“=PMT(C4,B4,A4)”,即可计算该项贷款每年末的偿还金额,如下图所示。
4)选择F4单元格,在编辑栏中输入公式“=PMT(C4/12,B4*12,A4,0,1)”,即可计算该项贷款每月初的偿还金额,如下图所示。
5)选择G4单元格,在编辑栏中输入公式“=PMT(C4/12,B4*12,A4)”,即可计算该项贷款每月末的偿还金额,如下图所示。
说明:
“=PMT(C4,B4,A4,0,1)”中的0表示未来值,即在最后一次付款后希望得到的现金余额,如果省略则认为其值为0;1表示是期初付款时间,如果是0或者省略则表示期末。
20、判断查询的信息是否在单元格内:
1)在Excel中打开“库存报表”工作薄。
2)选择G5单元格,在编辑栏中输入公式“=VLOOKUP(G4,A3:
D10,2,0)”,即可显示查询结果。
3)选择G6单元格,输入公式“=IF(ISNA(VLOOKUP(G4,A3:
D10,2,0)),”型号错误”,VLOOKUP(G4,A3:
D10,2,0))”,即可显示查询结果,如下图所示。
公式的说明:
①VLOOKUP(G4,A3:
D10,2,0)):
表示在A3:
D10区域中查询G4单元格中的内容(即产品型号)所对应的产品名称,其结果没有查询到该型号的产品名称,即返回错误值“#N/A”。
②ISNA(VLOOKUP(G4,A3:
D10,2,0)):
表示利用“ISAN”函数进行判断是否有错误值,因为有错误值,所以返回值为“True”。
③利用“IF”函数进行判断,因为判断条件为“True”,所以返回值为“型号错误”信息。
21、判断员工在考核中是否有缺考:
1)在Excel中打开“员工考核表”工作薄。
2)选择G5单元格,在编辑栏中输入公式“=IF(OR(ISBLANK(C4),ISBLANK(D4),ISBLANK(E4),”缺考”,SUM(C4:
E4))”,即可显示该员工是否有缺考的课程,如下图所示。
公式的说明:
首先利用多个“ISBLANK”函数来判断C4、D4、E4单元格是否为空白单元格,如果为空白单元格,则返回值为真(True),否则返回值为(FLASE)。
然后再利用“OR”函数判断这3个单元格中是否有真值,如果有一个单元格为真,则返回值为真,否则返回值为假。
最后再利用“IF”函数进行判断,如果条件为真,则返回值为“缺考”,否则返回员工考核的总成绩。
22、统计进入录取分数线的人数:
作为招生办的办公人员,经常需要按指定的条件统计一些数据。
例如,在研究生“笔试成绩统计表”工作薄中,需要统计有多少考生可以进入录取分数线(录取的条件为:
考生的总成绩超过360分),以便安排下一轮的考试。
本实例使用DCOUNT函数来统计进入录取分数线的人数。
1)在Excel中打开“笔试成绩统计表”工作薄。
2)选择G4单元格,单击“公式”选项卡,在“函数库”组中单击“插入函数”图标按钮。
3)打开“插入函数”窗口,在“或选择类别”下拉列表框中选择“数据库”项,在“选择函数”下拉列表框中选择“DCOUNT”函数。
4)单击“确定”按钮,打开“函数参数”窗口。
5)在“Database”文本框中用鼠标直接选择A2:
E2单元格区域;在“Field”文本框中输入数据“5”(还可以输入数据“2、3、4”;在“Criteria”文本框中用鼠标直接选择F2:
F4单元格区域。
6)单击“确定”按钮,即可显示符合条件的数字。
7)用鼠标单击编辑栏,并将鼠标指针移至右括号之后,接着继续输入数据“&“人””,即可统计出进入录取分数线的人数,如下图所示。
23、统计某个职业的平均工资:
1)在Excel中打开“调查统计表”工作薄。
2)选择G3单元格,在编辑栏中输入公式“=DAVERAGE(A2:
D31,4,F2:
F3)”,即可统计出会计职业的平均工资,如下图所示。
24、统计指定销售日期和产品名称对应的销量:
1)在Excel中打开“销售业绩表”工作薄。
2)选择K3单元格,在编辑栏中输入公式“=DGET(A2:
G20,3,I2:
J3)”,即可检查出该产品的销售数量是否正确,如下图所示。
25、统计销售员一个月对应的销售数量:
1)在Excel中打开“销售业绩表”工作薄。
2)选择K3单元格,在编辑栏中输入公式“=DSUM(A2:
G58,3,I2:
J3)”,即可统计出某个销售员的销售数量,如下图所示。
26、综合运用函数进行人事管理:
对员工的有效管理是一个企业成功的关键因素之一,如果企业中的部分员工工作态度不积极,经常迟到、请假,不但会影响其他员工的工作热情,也势必会导致企业业绩的下降。
因此作为人事部的管理人员将会制作细致规范的考勤系统,并制定相应的奖惩制度,避免上述现象的发生。
由此可见制定员工考勤管理系统是对员工进行有效管理的手段之一。
下面通过几个工作表来完成员工考勤管理系统的定制。
本实例所介绍的“员工考勤表”工作薄中包括“员工请假明细表”、“员工年假表”、“员工考勤明细表”、“加班明细表”和“加班时间统计表”5个工作表。
“员工请假明细表”主要用于记录一个月中员工请假的详细情况,效果如下图所示。
“员工年假表”主要用于统计员工年假的使用情况,并将已休完年假的员工记录突出显示,效果如下图所示。
“员工考勤明细表”主要用于体现所有员工每个月的总体出勤情况,并将全勤和没有迟到记录的员工突出显示,效果如下图所示。
在“员工加班明细表”中记录每天员工加班的详细情况,并计算出每个员工加班的时间,效果如下图所示。
在“员工加班时间统计表”中对每个员工当月的加班时间进行汇总,效果如下图所示。
A、员工请假明细表:
员工请假明细表的基本框架。
1)在“日期“列中输入员工请假的日期;在“员工编号”列中输入请假员工的员工编号。
2)当输入员工姓名、部门及职位时,可以使用VLOOKUP函数来完成。
选择C4单元格,输入公式=“VLOOKUP($B4,员工考勤明细表!
$A$3:
$D$32,2,0)”,然后按键盘上的
(公式说明:
在“员工考勤明细表”的“$A$3:
$D$32”单元格区域中第1列查找$B4单元格中的数值,然后返回第2列中与$B4单元格中所对应的同一行值即“李X亮”。
3)将鼠标指针移到C4单元格区域的右下角,当鼠标指针变成十字形状时,单击鼠标左键并拖曳鼠标至E4单元格,然后释放鼠标,即可自动输入该员工所在的部门和职位。
4)当考勤人员再次输入员工请假明细时,只需要输入请假日期和员工编号,而姓名、部门、职位可以自动填充完成。
说明:
当员工请假明细表设置好以后,就可以将员工每天的请假情况录入到表中。
通常一个月中的请假记录会比较多,为了方便输入数据,可以将窗格冻结,使工作表的标题部分始终在窗口的上方显示。
说明:
将一个月中的所有请假记录都录入完成以后,需要对所有的请假天数进行汇总。
5)选择F4:
F41单元格区域,然后单击“公式”选项卡,在“函数库”组中单击“自动求和”图标按钮,即可计算出事假的总天数。
技巧:
按键盘上的
6)将鼠标指针移到F42单元格区域的右下角,当鼠标指针变成十字形状时,单击鼠标左键并拖曳鼠标至I42单元格,然后释放鼠标,即可计算出各种假别的合计数。
说明:
为了方便以后的公式引用,可以将“员工请假明细表”中相关数据定义名称,例如,将“员工请假明细表!
$B$4:
$I$41”单元格区域定义名称为:
“请假明细”。
7)在“员工请假明细表”中选择B4:
I41单元格区域。
8)单击“公式”选项卡,在“定义的名称”组中选择“定义名称”—“定义名称”命令,打开“新建名称”窗口。
9)在“名称”文本框中输入要定义的名称“请假明细”。
10)单击“确定”按钮即可。
11)可以用同样的方法将“事假”所在的F4:
F41单元格区域定义名称为:
“事假”;将“年假”所在的G4:
G41单元格区域定义名称为:
“年假”;将“病假”所在的H4:
H41单元格区域定义名称为:
“病假”;将“其他”所在的I4:
I41单元格区域定义名称为:
“其他”。
说明:
如果要在公式中引用定义名称的单元格区域,可以单击“定义的名称”组中的“用于公式”下拉按钮,然后在弹出的下拉菜单中进行选择。
12)此时,完成“员工请假明细表”的制作。
B
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- Excel 公式 函数 实例 详解