二级excel高级操作-停车-解题步骤_第1页
二级excel高级操作-停车-解题步骤_第2页
二级excel高级操作-停车-解题步骤_第3页
二级excel高级操作-停车-解题步骤_第4页
二级excel高级操作-停车-解题步骤_第5页
免费预览已结束,剩余5页可下载查看

下载本文档

版权说明:本文档由用户提供并上传,收益归属内容提供方,若内容存在侵权,请进行举报或认领

文档简介

1、计算机应用基础课程教学任务参考步骤设计者:计算机基础教研室例3-2打开素材文件,按下列要求操作。>操作要求(1)在Sheet4的A1单元格中设置为只能录入 5位数字或文本。当录入位数错误时, 提示错误原因,样式为“警告”,错误信息为“只能录入 5位数字或文本”。(2) 在Sheet4的B1单元格中输入公式,判断当前年份是否为闰年,结果为TRUE或FALSE。闰年定义:年数能被 4整除而不能被100整除,或者能被400整除的年份。(3)使用HLooKUP函数,对Sheet1 “停车情况记录表”中的“单价”列进行填充。要求:根据SheetI中的“停车价目表”价格,使用HLOOKUP函数对“停

2、车情况记录表”中的“单价”列根据不同的车型进行填充。注意:函数中如果需要用到绝对地址的请使用绝对地址进行计算,其他方式无效。(4)在SheetI中,使用数组公式计算汽车在停车库中的停放时间。要求:计算方法为:“停放时间=出库时间-入库时间”格式为:“小时:分钟:秒”例如:一小时十五分十二秒在停放时间中的表示为:“1: 15: 12”将结果保存在“停车情况记录表”中的“停放时间”列中。(5)使用函数公式,对“停车情况记录表”的停车费用进行计算。要求:根据SheetI停放时间的长短计算停车费用,将计算结果填入到“停车情况记录 表”的“应付金额”列中。停车按小时收费,对于不满一个小时的按照一个小时计

3、费; 对于超过整点小时数十五分钟(包含十五分钟)的多累积一个小时。例如1小时23分,将以2小时计费(6)使用统计函数,对 SheetI中的“停车情况记录表”根据下列条件进行统计。统计停车费用大于等于40元的停车记录条数,并将结果保存在 J8单元 格中;统计最高的停车费用,并将结果保存在 J9单元格中。(7) 将SheetI中的“停车情况记录表”复制到Sheet2中,对Sheet2进行高级筛选。 要求:筛选条件为:“车型”小汽车,“应付金额”>=30;将结果保存在Sheet2中。注意:无需考虑是否删除筛选条件;复制过程中,将标题项“停车情况记录表”连同数据一同复制; 复制数据表后,粘贴时,

4、数据表必须顶格放置。(8)根据SheetI中的“停车情况记录表”,创建一个数据透视图,保存在Sheet3中。显示各种车型所收费用的汇总;X坐标设置为“车型”; 求和项为“应付金额”;将对应的数据透视表保存在Sheet3中。操作说明1.设置单元格限定录入字数-数据有效性根据操作要求(1),打开Excel例3-02文件,选择Sheet4工作表,选择A1单元格,在“数据”选项卡的“数据工具”功能组,点击“”(数据有效性)打开数据有效性对话框 对话窗。如图3- 1所示。I<ACrebat-.U»?! Bft-gJsfieSKtIIy.ALADEGI1图3- 1选择数据有效性在“数据有效

5、性”对话窗口选择“设置”选项卡,设置“有效性条件”允许“文本长度”数据“等于”长度“ 5”,如图3- 2所示。点击选择“出错警告 项目选项卡,设置“出错样式”为“警告”,在“错误信息”栏下输入“只能录 入5位数字或文本”。图3- 2设置数据有效性2.判断闰年根据操作要求(2),对于闰年需表达两个可能分别成立的条件,所以使用OR函数。而条件之一“年数能被 4整除而不能被100整除”为两个并列条件,需要使用 AND函数,这 里表达整除需要用到取余函数 MoD,而其中的当年年份需要用到年函数 YEAR和当日日期 函数TODAY。选择Sheet4工作表的B1单元格,在“公式”选项卡下“函数库”功能组中

6、点击“诊(逻辑),在弹出的逻辑函数列表中选择“ OR”函数,在OR “函数参数”对话窗中,条件LogCaII的文本框中输入闰年的第一种判断方式,即“年数能被4整除而不能被100整除”,由于是一组并列条件,嵌入条件文本框中输入条件式条件文本框中输入条件式AND函数,将转到 AND “函数参数”对话窗,分别在LOgiCal1MOD(YEAR(TODAY(),4)=0 ”(年份能被 4 整除),在 LOgiCal2MOD(YEAR(TODAY(),100)>0”(年份不能能被 100 整除)。如图3- 3所示。图3- 3闰年的并列条件“被4整除而不能被100整除”在编辑栏点击 AND函数的结尾

7、处(最后一个“)”符号前),回到OR “函数参数”对 话窗,在LOgiCal2条件文本框中输入第二个判断条件“能被 400整除的年份”的条件式,“MOD(YEAR(TODAY(),400)=0 ”如图 3- 4 所示。ZlDgSS的ff1齢馭亡L XMD CEEiMI4)=Cli MLOfiic<12 WyIy白EW¾CTffDAX 0).M)=DLD 於ILoeiC<14=可去的如案f-l -. ffl0 TmI貝有当所有魁尅值均为F心E jgffl TJiiSEI>Elc13: LciclL.lcickl2j. I 勿 225 弁Ii绑是 Tm FL5I 的煌知

8、躁件计=可和的E询:flft曲SJi叩I薩电消图3- 4通过OR函数表达闰年的两组判断方式在B2单元格给出的完整表达式为:“ =OR(AND(MOD(YEAR(TODAY(),4)=0,MOD(YEAR(TODAY(),100)>0),MOD(YEAR(TODAY(),400)=0) ”3.计算车辆停放收费“单价”-水平查询函数Hlookup单击“停车情况记录表”中的C9单元格,使其成为活动单元格。在功能区选择“公式”选项卡,在“函数库”功能组中点击“”(查询与引用),在弹出的函数列表中选择水平查询函数“HLooKUP ”,在弹出的如图3- 5所示“函数参数”对话框中编辑函数。在“函数参

9、数”对话框中的第一行“ LookUP_value ”(查询参数值)框输入搜索值“停 车情况记录表”第一条记录“车型”的单元格地址“B9”(确定在数组区域首行进行搜索时的搜索值)。在第2行“ Table_array”(搜寻数据表)框输入要搜索区域“停车价目表”数据区的绝 对地址“ $A$2 : $C$3”(可选择A2:C3单元格区域后按 F4键来绝对引用),用于确定要搜 索的数组或数据表所在的区域。在第3行“ Row_index_num ”(搜寻之目标数据所在行序号)框输入数字“2 ”,用以确定在数组区域首行搜索到满足搜索值时,要取的值位于该列的第几行,本题是取第二行的值。在第4行“ Range

10、OokUP”(查询结果的匹配方式)框输入“FALSE ”以设定查找精确匹配的逻辑值。单击“确定”按钮,在“停车情况记录表”第一条记录的“单价”单元格C9得到使用了 HLOOKUP函数后小汽车的小时停车单价为 “ 5”。双击C9单元格填充柄,完成停车单价 的自动填充。JhCDL存车协冃亲3&a1<45$7a E=车SlWrT3HF9II347A!1!2Q110 IJfih 325SL丈春I=Fa:Sd:12:11 :fiA2!5&48C0361IZ .fiR7L小汽车59!3!4=11 IffA5 2? L923114 :54G44Ifl10:33:501ITi .iK-J

11、'A5lQ:i:£3Lii A3322l9IL 3W117 A6m牙丄1;和迢1Ia IffiA33547Ifl13!5!311 i*yT4i29L¾!15iC1 -HiJ C J OC小立氐IEI O "2 . CCI图3- 5使用HLOOKUP填入单价数据4计算汽车停放时间将光标定位于“停车情况记录表”第一条记录的“停放时间”单元格F9,在编辑栏输入“ =E9-D9 ”后按回车键;双击F9单元格填充柄完成“停放时间”列的自动填充。5 计算停车费将光标定位于“停车情况记录表”第一条记录的“应付金额”单元格G9 ,先计算停车计费时间(小时),在功能区选择“

12、公式”选项卡,在“函数库”功能组中点击“企”(逻辑), 在弹出的函数列表中选择条件函数“IF”,在弹出的如图3- 6所示“函数参数”对话框中编辑函数。在第1行“ LOgiCaI_test ”(逻辑表达式)框输入逻辑表达式“HOUR(F9)1 ”,判断停车 时间是否不足1小时,使用时间函数 HOUR()将提取F9单元格内的小时数进行判断是否小于0。在第2行“ Value_if_true ”框输入当逻辑表达式“HoUR(F9)<0 ”为“ true”的值,根据题意,在该栏输入数字“1”。如图3- 6所示。在第3行“ Value_if_ false ”框应输入当逻辑表达式“HOUR(F9)&l

13、t;0 ”为“ false”的值,由于涉及是否超过整点小时数十五分钟的判断,会有两种可能的结果,需要嵌入一层IF函数进行再判断,点击第三行文本框,点击工作表编辑区左上角的名称栏中的“IF”函数名(嵌套“ IF”函数),将进入新的“函数参数”窗口。在第2层“函数参数”窗口的第1行“ LOgiCaI_test ”,输入MINUTE(F9)<15 ,以判断是否超过整点15分钟,其中MINUTE()将提取F9单元格内的分钟数。在第2行“Value_if_true ” 输入没超过15分钟的计费时间“ HOUR(F9) ”,即按实际的停车小时数,不计分(舍弃) 。在 第3行“ Value_if_ f

14、alse”后的文本框输入超出整点15分以上的计时数(加计 1小时)“HOUR(F9)+1 ”,如图 3- 7 所示。图3- 7判断超15分钟收费计时单击“确定”按钮,完成停车计费小时数的统计,这时编辑栏上完成的表达式为“ =IF(HOUR(F9)<0,1,IF(MINUTE(F9)>15,HOUR(F9)+1,HOUR(F9)”,是按计时要求的停车小时数。停车小时数的表达式后输入“ *C9 ”(乘上收费单价),即得到每小时停车费,整个表达式为“ =IF(HOUR(F9)<0,1,IF(MINUTE(F9)>15,HOUR(F9)+1,HOUR(F9)*C9”。双击G9单

15、元格右下角的填充柄,完成停车应付金额的自动填充。6. 统计停车记录数和最高停车费将光标定位于J9单元格,在功能区选择“公式”选项卡,在“函数库”功能组中点击直I”(其他函数组),在弹出的函数组列表中选择“统计”函数组,在下级子菜单中选择“CoUNTIF ”函数,在弹出的“函数参数”对话框中输入参数。在第1行“ Range”(要统计的数据区域)输入“ G9:G39 ”,在第2行“ Criteria ”(统计 条件)框输入“ >=40”(不包括引号,使用非中文输入状态输入),如图3- 8所示。单击“确定”按钮完成统计,在 J9单元格得到符合条件的记录条数为“4”。图3- 8统计收费大于 40

16、元的记录将光标定位于J10单元格,在功能区选择“公式”选项卡,在“函数库”功能组中点击第1组函数(“£”)的列表展开键“”,在弹出的函数组列表中选择“最大值(M) ”,在函数括号中输入统计数据区域“G9:G39 ”,单击“确定”命令按钮,在J10单元格得到最高停车费用为“ 50”。7. 记录数据的高级筛选选择SheetI工作表,选择“停车情况记录表”所在单元格区域,复制该数据(包括标题)。选择Sheet2工作表,右击 A1单元格,在弹出菜单的“粘贴选项:”下选择“” (粘贴数值),将相关数据粘贴到为数据区添加表格线。选择Sheet2工作表,在“开始”选项卡“字体”功能组中点击A1 :

17、 G1”单元格,点击“开始”选项卡“对齐方式”功能组中的“ ”(合并并居中),恢复原表中标题栏的设置,如图3- 9所示。FBCDTC1ITf2车牌号车SBnA出阵盯直母!1间riliS3i5fiAi 345小汽车S0,駅T95E0. 469155154>lfi58i大客车100. S570B3CL T74LL105Z15a2U. S7541T0. 6E939O- 591Z4S6IFA6ES71屮汽车LDJ 3BCL 45B36 3fil3307fiA5127180, <02山 4230&7O- OIffT73S9>lfi 51844述0, 4395&0.531

18、516¢. 0&19562Dg乳血5閱945LL «55922fl.aa77(L 01305E5ID4fiAS3221中善车0. J4170 . 559543O-D91224停车4况记录事Z车铸; 车型 a-, 人时间出库时3 浙丄 12阳5 屮汽车 Or 3415& ¢, 46914 浙5S:二二'''T570S Ol 3977BnaiZ15B MWaR'TT541T ¢, 69E-浙A6687: J Ln J I:*3 0 -364 ¢, 634T浙A5171-tjs45UrW994U,43S

19、C8 浙A543t4S车IO0,. 43?5&9.531:9 浙j卜汽车S 0” 45532 6 468E10 浙A31=P書车90.4604175535m a.*4C a E MnJr.-TLi j-8 nrJXlj"si图3- 9复制工作表数据在Shet2工作表数据区的右侧空白区域设置筛选条件区(不要紧挨数据区),单击I3单元格,设置条件区如图3- 10所示。CHI1_J_倚前15I应忖金郃10小汽车>=3030图3- 10设置筛选条件区单击表Sheet2要进行高级筛选的数据区中任一单元格,在“数据”选项卡中“排序和筛选”功能组中点击“吧”(高级筛选)动填入当前工作表的数据区单元格引用“在当前工作表选择上面设定的条件区域“在打开的“高级筛选”对话框中“列表区域”已自$A$2:$G$33 ”,点击“条件区域”右侧的文本框,Sheet2!$l$3:$J$4”,勾选“高级筛选”对话窗底部的“选择不重复记录”,单击“确定”按钮完成高级筛选。如图3- 11所示。方式列 E(L): 棗件区城(E) lJ(T):如 3S)U PBC0EFG1借车唱况记禾乖2车型6小汽车5 O. 36410. 6345B3OI 3918330旧Q 5Q37旳3图3- 11高级

温馨提示

  • 1. 本站所有资源如无特殊说明,都需要本地电脑安装OFFICE2007和PDF阅读器。图纸软件为CAD,CAXA,PROE,UG,SolidWorks等.压缩文件请下载最新的WinRAR软件解压。
  • 2. 本站的文档不包含任何第三方提供的附件图纸等,如果需要附件,请联系上传者。文件的所有权益归上传用户所有。
  • 3. 本站RAR压缩包中若带图纸,网页内容里面会有图纸预览,若没有图纸预览就没有图纸。
  • 4. 未经权益所有人同意不得将文件中的内容挪作商业或盈利用途。
  • 5. 人人文库网仅提供信息存储空间,仅对用户上传内容的表现方式做保护处理,对用户上传分享的文档内容本身不做任何修改或编辑,并不能对任何下载内容负责。
  • 6. 下载文件中如有侵权或不适当内容,请与我们联系,我们立即纠正。
  • 7. 本站不保证下载资源的准确性、安全性和完整性, 同时也不承担用户因使用这些下载资源对自己和他人造成任何形式的伤害或损失。

评论

0/150

提交评论