Excel数据分析教程 教案 第4章上机题及课后习题.docx
- 文档编号:10260935
- 上传时间:2023-05-24
- 格式:DOCX
- 页数:15
- 大小:644.46KB
Excel数据分析教程 教案 第4章上机题及课后习题.docx
《Excel数据分析教程 教案 第4章上机题及课后习题.docx》由会员分享,可在线阅读,更多相关《Excel数据分析教程 教案 第4章上机题及课后习题.docx(15页珍藏版)》请在冰点文库上搜索。
Excel数据分析教程教案第4章上机题及课后习题
上机题4
1、利用LEFT和RIGHT函数判断客户性别
本练习的目标是在客户登记表中,通过客户身份证号码(身份证号码第17位奇数为男,偶数为女),利用函数判断客户的性别,主要涉及到LEFT、RIGHT、MOD和IF函数的相关知识。
原始数据如图所示:
操作步骤
①选择D3单元格,在“公式|函数库”选项卡中点击“插入函数”按钮,在弹出的“插入函数”对话框中依次选择“文本函数|RIGHT”选项,以插入该函数,如图所示:
②在“函数参数”对话框中,在“Text”文本框中选择B3,在“Num_chars”文本框中输入2,如图所示:
③再次选中D3单元格后,鼠标移动到编辑栏,在已经存在的“=RIGHT(B3,2)”输入外面嵌入LEFT函数“=LEFT(RIGHT(B3,2))”,如图所示:
④再次选择D3单元格后,鼠标移动到编辑栏,为已经存在的公式“=LEFT(RIGHT(B3,2))”继续嵌套MOD函数和IF函数,如图所示:
最后将该公式填充到D列余下的单元格就可以了。
说明:
MOD函数是取余函数,用于返回两个数相除后的余数,语法格式为:
=MOD(number,divisor)
其中:
number:
被除数
divisor:
除数
本例中,该函数的被除数为LEFT函数和RIGHT函数返回的数字,除数为2,将返回1或2,分别表示奇数或偶数。
2、利用VLOOKUP函数制作个人简历
本练习将使用查找与引用函数,利用员工个人资料档案来制作个人简历,会用到IF函数、VLOOKUP函数和ISERROR函数。
原始文件有两个,其中存放数据的文件如图所示:
操作步骤
在“简历”工作表的D2单元格输入函数公式:
=IF(ISERROR(VLOOKUP(B2,员工个人资料档案!
$A$3:
$K$9,3,
FALSE)),"",VLOOKUP(B2,员工个人资料档案!
$A$3:
$K$9,3,FALSE))
如图所示(其中ISERROR(value)函数用于测试返回值是否有错):
该公式含义是,如果VLOOKUP函数返回的值错误,则不会在单元格中显示类似“#VALUE!
”这样的错误提出,而是显示为空,否则(即VLOOKUP函数返回的值正确)就显示得到的结果。
然后在编辑栏中选中该公式,复制到其他空白单元格中,每个新公式只需要更改VLOOKUP函数的返回列值就可以了。
这样,就得到了一个简历中所有空白单元格的查询结果值。
现在,需要将整个简历表复制到另外的地方,如图所示:
此时就应该明白了,为什么公式中在引用“员工个人资料档案”工作表中的单元格时使用的是绝对引用了,如果当时使用了相对引用,则后面复制的这几个表又得全部重新改公式!
最终的结果如图所示:
3、用日期与时间函数计算停车费用
本练习将使用日期与时间函数计算停车费用。
原始数据如图所示:
操作步骤
第1步,在D5单元格输入“=MINUTE(C5-B5)”,并填充到D6:
D19单元格区域,注意选择不带格式填充;
第2步,在E5单元格输入“=HOUR(C5-B5)”,并填充到E6:
E19单元格区域,注意选择不带格式填充;
第3步,在F5单元格输入“=DAY(C5-B5)”,并填充到F6:
F19单元格区域,注意选择不带格式填充;
第4步,在G5单元格输入
“=F5*24+E5+IF(D5<15,0,IF(D5<30,0.5,1))”,并填充到G6:
G19单元格区域,注意选择不带格式填充;
第5步,在H5输入“=G5*2”,并填充到H6:
H19单元格区域,注意选择不带格式填充。
第6步,在H20用SUM函数对H5:
H19单元格区域求各。
最终完成的表格如图所示:
4、利用数学与三角函数管理原材料明细账
本练习的目标是在已经提供了原材料明细账表格的基础上,通过数学和三角函数,对数据进行相应处理,即可实现对原材料的管理。
在Excel中使用PRODUCT函数计算期初余额,使用SUM函数汇总结存的数量、金额,用PRODUCT函数计算出单价、金额,用TRUNC函数对单价进行截尾取整。
原文件如图所示:
操作步骤
①选择G6单元格中输入E6和F6之乘积,可用乘号,但最好用PRODUCT函数“=PRODUCT(E6,F6)”,计算出3月5日购入甲材料花费的费用,并填充到G6:
G15中,计算出其他日期购入材料的费用,如图所示:
②在I7单元格中计算发出材料的平均单价,因为上月结存的材料是按单价120购入的,而本月5日购入的材料是按单价116.56购入的,两次单价不一样,所以需要算一个平均单价作为本月8日发出该材料的统一单价。
但是,因为上月结存的数量和本月5日购买的数量又不一样,无法直接对两次单价求平均值,只好分别把结存的、本月5日购入的算出总金额,再分别把结存的、本月5日购入的算出总数量,由这个总金额除以总数量来得出单价,因此在I7单元格中应输入公式“=SUM(M5,G6)/SUM(K5,E6)”,如图所示:
③在J7单元格输入公式“=PRODUCT(I7,H7)”,计算出3月8日发出材料的总金额,因为虽然有了上月结存的数量和3月5日购入的总金额,但这些总金额所对应的材料数量并没有全部在3月8日发出去,所以要按3月8日实际发出的数量,乘以平均单价,得到3月8日实际发出的材料金额,如图所示:
④在K7单元格输入“=K5+E6-H7”,得出3月8日发出材料后的库存数量,同时可以根据已在I7单元格中得出的平均单价乘以3月8日发出材料后的库存数量,来算出3月8日发出材料后的库存金额,但为了财务科目的可读性,可以先在L7单元格输入“=I7”,将单价转到L7单元格上,然后使用“=PRODUCT(K7:
L7)”公式求出3月8日发出材料后的库存金额,如图所示:
⑤使用同样方法计算3月11日、17日、22日、29日的发出和结存的单价、数量和金额。
具体方法是,选中I7:
M7区域,然后向下填充到I15:
M15单元格,如图所示:
然后手工将显示“#DIV/0!
”错误的那几行单元格清空内容就是了。
注意:
此处向下填充公式时,一定要将I7:
M7区域(也就是这一行)中所有公式一起选中后向下填充,如果只选某一个单元格的公式向下填充,比如只选I7单元格向下填充,结果如图所示:
即只有其后第一个有效单元格(I9)能得到正确值,后面的几个有效单元格如I11、I13、I15都不能得到正确值。
为什么呢?
因为I11(即17日)的数据依赖于K9、M9、E10、G10这四个单元格的数据,而现在只有E10、G10有数据,K9、M9都为空。
⑥在E16单元格输入“=SUMIF($D$6:
$D$15,"购入材料",E6:
E15)”,计算本月购入材料的总数量;然后向右侧填充公式到G16,这就是为什么公式中D6:
D15单元格区域要使用绝对引用的原因,否则到G16单元格时,判断条件所在的单元格区域就会变成F6:
F15了。
不过,中间经过的F16在计算完成后清空后重新计算,因为不可能有单价求和的说法,单价只能求平均。
但如果使用“=SUM(F6:
F15)/COUNTA(F6:
F15)”的方法来求平均单价,由于每期购入的数量不一致,会导致此种方法得出的结果与实际情况有出入(本例中此种方法得到的值是118.754)因此正确的做法是用本月总计购入金额除以本月总计购入数量,即在F16单元格使用上一步已算出的G16除以F16。
其值在本例中是118.572,我们使用截尾取整函数TRUNC保留其2位小数,即“=TRUNC(G16/E16,2)”,如图所示:
不过,此时Excel又自作聪明地指出F16单元格的公式与该区域(即该单元格两侧单元格)的公式不同。
不要理它,或选择“忽略错误”即可。
⑦使用相同的方法计算出本月发出材料的总数量、总金额和平均单价:
先选中E16单元格后直接按Ctrl+C复制,然后选择H16,点击“选择性粘贴|粘贴公式”,你会发现新公式中单元格引用都是正确的,但结果值为0,这是因为该函数的第二个参数还是“购入材料”,因为这个参数是无法自动更新的,所以只有手动改成“发出材料”,结果就正确了。
然后同样向右填充到J16,并把中间I16清空后输入公式“=TRUNC(J16/H16,2)”,如图所示:
⑧在K16输入“=K5+E16-H16”,得到本月的结存数量;在M16输入“=M5+G16-J16”,得出本月的结存金额;最后再用结存总金额除以结存总数量,就得到结存单价,对此单价使用四舍五入保留两位小数的格式显示(=ROUND(M16/K16,2)),结果如图所示:
当然了,Excel会再次提示“公式不一致”,不用理睬它,忽略就可以了。
总结:
本案例使用Excel2016管理企业存货中的原材料,要求务必使原材料的收、支、存情况清晰,建立数量金额式的账页来管理原材料。
在账页中分设收入、支出、结存三大栏,每一栏下设置数量、单价、金额三列,此外凡是涉及金额的单元格格式都采用两位小数的货币形式,便于区分。
课后习题4
1、操作:
利用多种文本函数,为下表中的电话号码从11位升至12位,要求凡是13X开头的手机号码,X如果是奇数,升位为“131X”;X如果是偶数,则升位为“132X”。
原始数据如下:
提示:
根据升位的要求,可使用REPLACE函数,将手机号码的前两位数字替换为131或132替换的过程中需要使用IF函数对号码的前三位数字进行奇数和偶数的判断,可以使用MOD函数,另外,判断前三位数字可以使用MID函数或LEFT函数。
这里使用MID函数,选择D3单元,输入以下公式:
=IF(MOD(MID(C3,1,3),2)=1,REPLACE(C3,1,2,"131"),REPLACE(C3,1,2,"132"))
就可以给电话号码实现升位,如图所示。
可以使用LEFT函数“LEFT(C3,3)”代替MID函数,看结果是否一致。
(是一样的)。
2、操作:
利用LOOKUP函数的向量形式查询员工个人信息,原文件如下:
要求查询结果如下:
提示:
先按姓名进行升序排序,然后建立按性别、学历、职位查询的区域,在其后面的B20、B21、B22单元格分别输入以下函数公式:
=LOOKUP(B19,B3:
B16,C3:
C16)
=LOOKUP(B19,B3:
B16,F3:
F16)
=LOOKUP(B19,B3:
B16,H3:
H16)
注意,如果不按姓名进行升序排序,返回值可能出错。
3、操作:
运用TIME函数等日期与时间函数计算考核时间。
原始文件如下:
(1)操作思路
完成本练习需要先计算总分,再为计算的结果区设置格式,然后计算平均分。
(2)操作步骤
第1步,在E4单元格插入HOUR函数“=HOUR(D4-$H$2)”,然后填充到E5:
E13单元格区域中,算出考了多少小时。
第2步,在F4单元格插入MINUTE函数“=MINUTE(D4-$H$2)”,然后填充到F5:
F13单元格区域中,算出考了多少分钟时,注意,此时只算出分钟数,忽略小时数。
第3步,在G4单元格中插入SUM函数“=SUM(E4*60,F4)”,然后填充到G5:
G13单元格区域中,求出每个人总的分钟数。
第4步,在H4单元格插入TIME函数“=TIME(,G4,RIGHT(D4,2))”,然后填充到H5:
H13单元格区域中,求出每个人总的时分秒数。
这里有一个技巧,就是“时”数我们不填,“分”数超过60的会自动填入。
注意,这样设置的时间是以子时开始计算的,是一个时间序列值而不是时间间隔值,需在单元格格式中设置为时间中的时间值类型。
4、操作:
利用DATE函数和TODAY函数制作一个高考倒计时天数表,类似下图(假定是2022年6月8日高考):
在合并单元格F3中输入
=CONCATENATE((DATE(2021,6,7)-TODAY()),"天")
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- Excel数据分析教程 教案 第4章上机题及课后习题 Excel 数据 分析 教程 上机 课后 习题