二级excel高级操作-停车--解题步骤.docx

计算机应用基础课程教学任务参考步骤设计者：计兑机一础明3-2打开素材文件,按下列要求操作。、.操作要求(1)在SheeI4的A1.单元格中设置为只能录入5位数字或文本，当录入位数错误时，提示辐误绿I1.1.样式为“济告”,W1.误信息为“只能录入5位数字或文本(2在She«14的B1.单元格中输入公式，推断当前年份是否为何年，结果为TRUE或FA1.SE.闰年定义：年数能被4整除而不能被I(X)整除，或者能被4«)整除的年份.(3)运用H1.OoKUP函数，对SheCI1.“停车状况记录表”中的单价”列进行填充。要求：依据ShCCt1.中的“停车价目表”价格,运用H1.OOKUP函数对“停车状况记录表“中的单价”列依据不同的车里进行侦充。留意：函数中假如须要用到肯定地址的请运用皆定地址进行计算.其他方式无效.(4)在Ske(I中，运用数组公式计算汽车在停车库中的停放时间。要求； 计算方法为：“停放时间=出库时间-入库时间” 格式为：“小时：分钟：秒”例如:一小时十五分十二秒在停放时间中的表示为:“1：15：12" 将结果保存在“停车状况记录表”中的“停放时间”列中。(5)运用函数公式，对“停车状况记录表”的停车费用进行计算.要求：依据ShCCu停放时间的长短计券停车费用，将计口结果填入到“停车状况记录表”的“应付金额”列中。留意: 停车按小时收费，对于不满一个小时的依据一个小时计费：对于超过整点小时数十五分钟(包含十五分钟)的多累枳一个小时。例如1小时23分，将以2小时计费(6)运用统计函数.对ShectI中的“停车状况记录表”依据下列条件进行统计. 统计停车费用大于等于40元的停乍记录条数，并将结果保存在J8单元格中： 统计最高的停车费用，并将结果保存在J9单元格中。(7)将SheeI1.中的“停车状况记录表-制到Sheei2中,时Shee1.2进行高级筛选,要求： 筛选条件为：“车型”一小汽车，“应付金额”=30： 将结果保存在Sheet2中。留意， 无需考虑是否删除筛选条件： 复制过程中，将标题项“停车状况记录表”连同数据一同复制： 组制数据表后，粘贴时，数据表必需顶格放置。(8)依据ShCCU中的“停车状况记录表”,创建一个数擀透视图,保存在ShCet3中. 显示各种车型所收费用的汇总： X坐标设优为“车型”； 求和项为“应付金额”：株作说明 将对应的数据透视表保存在Shc"3中.设置单元格限定录入字数数据有效性依据操作要求(1),打开EXCeI例302文件,选择Sheet4工作去，选择A1.单元格，在“数据”选项R的“数据工具”功能组.点击“(数据有效性)打开数据有效性对话框对话窗如图3I所示。±B图3-1迭揉数据有效性在“数据有效性”对话窗口选择“设置”选项卡，设置“有效性条件”允许“文本长度”数据“等于”长度“5”，如图3-2所示。点击选择“出错警告”项口选项卡，设置“出错样式”为“警告”，在“错误信息”栏下输入"只能录图32设置数据有效性2.推断闰年依据操作要求(2,对于闰年需表达两个可能分别成立的条件，所以运用OR函数.而条件之一“年数能被4整除而不能被100整除”为两个并列条件，须要运用AND函数，这里表达整除须要用到取余函数MoD,而其中的当年年份须要用到年函数YEAR和当日日期函数TODAY,选择ShCCU工作表的B1.单元格,在“公式”选项卡下“函数库”功能组中点击“做”(逻辑)，在弹出的逻辑函数列去中选择“OR”函数，在OR-函数参数”对话傥中，条件1.ogCaII的文本框中输入同年的第一种推断方式，即“年数能被4整除而不能被100整除”,也于是一组弁列条件，嵌入AND函数，将转到AND“函数参数”对话窗.分别在1.ogica1.1.条件文本框中输入条件式“MoD(YEAR(TODAYo).4)=0”(年份能被4整除，在1.ogiCa12条件文本框中箱入条件式“MOD(YEAR(ToDAYo),100)>O"(年份不能能被I(X)整除).如图3-3所示.图3-3闻年的并列条件“检4整除而不能被100整除”在焜料在点击AND函数的结尾处(最终一个“)”符号前>,回到OR“函数参数”对话也，在1.ogiCa12条件交本框中输入其次个推断条件“能被400整除的年份”的条件式,“MOD(YEAR(TODAY(»,400)=0”如图3-4所示。2NI1.pc*UAn<K0<rui<mun).«H<c,aut(11M0>.vx三11mW铎一Ut力erat*PuaMuraen*)utt力三M1.MUpcU1.pc11.14<*U.I，三?个。TW1.，3ffH图3-4通过OR函数表达闺年的两蛆推断方式在B2单元格给出的完整表达式为：,OR(AND<MOD<YER(TODYO).4)=O.MOD(YEAR<TODY(),100)>0),MOI><YER(TODAY()>.40=0)”3 .计算车辆停放收费“单价”-水平查询函数H1.oOkUP单击“杼乍状况记录表”中的C9的元格使其成为活动单元格.在功能区选择“公式”选项卡，在“函敷库”功能俎中点击“箴”（，询与引用）,在弹出的函数列表中选择水平查彻函数-H1.（X）KUP,在弹出的如图3*5所示“函数参数”对话框中编卷函数，在“函数参数”对话框中的第一行“1.ookup.Va1.UC”（查询参数值）椎幼人搜寻值“停车状况记录去”第一条记录'车型”的单元格地址“B9”（确定在数级区域首行进行搜寻时的搜寻值）。在第2行“Tab1jaay”（搜寻数据表也输入要搜寻区域“田车价目表”数据区的肯定地址“$A$2：SCS3"（可选择A2:C3单元格区域后按F4键来肯定引用），用于确定要搜寻的数组或数据表所在的区域.在第3行“Rowndex_num”（搜寻之目标数据所在行序号）框输入数字20,用以确定在数组区域首行搜寻到满意搜斗猿时,要取的值位于该列的第几行本题是取其次行的优.在笫4行“Range_100kUP”（杳询结果的匹配方式框输入"FA1.SE1*以设定杳找精确匹配的逻辑值.单击“确定”按钮，在“停车状况记录表”第一条记录的“单价”单元格C9得到运用了H1.OoKUP函数后小汽下的小时停车单价为“5”，双击C9单元格埴充柄，完成停车单价的自动填充.图35运用H1.OOKUP填入单价数据4 .计算汽车停放时间将光标定位于“停车状况记录表”第一条记录的“停放时间”第元格同.在煽辑栏尬入“=E9D9”后按回车键：双击F9单元格埴充柄完成“停放时间”列的自动埴充。5 .计停停车费将光标定位于“停车状况记录衣”第条记录的“应付金额”单元格G9,先计算停车计费时间（小时）,在功能区选择“公式”选项卡,在“函数库”功能组中点击“”（逻辑.在弹出的函数列我中选择条件函数“IF”，在弹出的如图3-6所示“函数参数”对话框中编辑函数，在第I行“IQgica1.test”（逻轮龙达式）框怆入逻辑友达式-H0UR（F9）1.",推断停车时间是否不足I小时，运用时间函数HOUR（）籽提取F9单元格内的小时数进行推断是否小于0.在第2行“Va1.ucjfjruc”框输入当逻辑发达式“HOUR(F9)<0”为“Hue”的伯.依据题超，在该栏输入数字如图36所示.在笫3行“Va1.ue匚fa1.se”框应输入当逻轼衣达式“HoUR(F9)<0”为“fa1.se”的值，由于涉及是否担过整点小时数十五分仲的推断,会有两种可能的结果.须要嵌入一层IF函数进行再推断，点击第三行文本框.点击工作表端辑区左上角的名称栏中的“1F”由数名(嵌套“IF”½),将进入新的“函数参数”窗口.在第2层“函数参数”窗口的笫I行“1.ogicaUesi",输入M1.NUTE(F9)<5以推断是否超过整点15分神，其中M1.NUTEO将捉取用总元格内的分钟数.在第2行“Va1.Ujifjrue”输入没超过15分钟的计费时间“HOUR(F9广，即按实际的停车小时数，不计分(舍弃)。在第3行-Va1.ucJtfa1.se”后的文本框输入超出整点15分以上的计时数(加计I小时)H0UR(F9)+1.如图3-7所示.图37推Bfi超15分钟收费计时单击“确定”按钮，完成停车计费小时数的统计，这时编辑栏上完成的表达式为“=1HHOUR(I9M).1.IF(MINUTE(卜9)>15.HOUR(F9)+1.HOUR小灯)二是按计时要求的停车小时数.停车小时数的表达式后输入-*C9”(乘上收费单价)，即得到每小时停车的，整个表达式为“=IF(HoUR(F9K0.1JF(MINUTE(F9)>I5.HOUR(F9)+1.HOUR(P9»>*C9”.双击G9单元格右下地的埴充柄，完成停车应付金额的自动坨充。6 .统计停车记录数和最高停车费将光标定位于J9单元格，在功能区选择“公式”选项P，在“函数库”功能组中点击“ST（其他函数组），在弹出的函数组列卷中选择“统计”函数表,在下级干菜单中选择wCOUNTIF"函数，在弹出的“函数参数”对话框中输入参数.在第1行“Range”（要统计的数据区域）部人“G9:G39”,在第2行“CritcmT（统计条件）框输入->=40"（不包括引号，运用非中文输入状态输入，如图3-8所示。单击“确定”按钮完成统计,在J9单元格得到符合条件的记录条数为“4”.（S3-8统计收费大于40元的记录将光标定位于JIo单元格.在功能区选择”公式”选项R,在“函数库”功能飙中点击第1杷函数（-"）的列衣绽开键“”，在弹出的函数组列表中选择“最大值（M）”,在函数括号中输入统计数据区域“G9:G39"，单击“确定”吩咐按钮，在JIO单元格得到最高停车费用为“50”.7 .记录数据的而级筛选选择ShCet1.工作表,选择“停车状况记录表”所在单元格区域,更制该数据（包括标遨）。选择SheeI2工作表，右击A1.单元格，在弹出菜单的“粘贴选项：”下选择“中”（粘贴数值）.将相关数据粘贴到Shcct2工作表,在“起先”选项卡“字体”功能组中点击“田”为数班区添加衣格税.选择“A1.：G1."单元格点击“起先”选项卡“对齐方式”功能组中的“国”（合并并居中）,复原原表中标题栏的设置,如图3-9所示.t'*riMi11,30.XZWd449】¾5T030.J7>11J?54：?0.62M£,V-1.的0.63SV'40HOUWioo<smsn,50.4S三22Q.Sai8O.UU1?0.56«*，C*ZCI，嗝就21一幺3才NMS小人多<fAXh5fA216fA66=TJ¥A3im中9<>AMf44XB*»JfAsbrtM人本10.丽田21中*iC*.4.A图3-9复制工作表数推车里I小汽率在ShCt2_£作衣数据区的右偏空臼区域设置筛选条件区（没关系按数据区），单击13单元格，设冏条件区如图310所示.ISBFF1.>30图370设置筛选条件区单击表Shect2要进行高级筛选的数据区中任单元格,在“数据”选项卡中“排序和篇选”功能组中点击“>"（高级筛选）,在打开的''高级筛选”对话框中“列表区域”己自动填入当前工作表的数据区单元格引川“$A$2:$GS33”,点击“条件区域”右（M的文本框