二级excel高级操作停车解题步骤.docx
《二级excel高级操作停车解题步骤.docx》由会员分享,可在线阅读,更多相关《二级excel高级操作停车解题步骤.docx(9页珍藏版)》请在冰豆网上搜索。
二级excel高级操作停车解题步骤
《计算机应用基础》课程教学任务参考步骤
设计者:
计算机基础教研室
例3-2打开素材文件,按下列要求操作。
操作要求
(1)在Sheet4的A1单元格中设置为只能录入5位数字或文本。
当录入位数错误时,提示错误原因,样式为“警告”,错误信息为“只能录入5位数字或文本”。
(2)在Sheet4的B1单元格中输入公式,判断当前年份是否为闰年,结果为TRUE或FALSE。
闰年定义:
年数能被4整除而不能被100整除,或者能被400整除的年份。
(3)使用HLOOKUP函数,对Sheet1“停车情况记录表”中的“单价”列进行填充。
要求:
根据Sheet1中的“停车价目表”价格,使用HLOOKUP函数对“停车情况记录表”中的“单价”列根据不同的车型进行填充。
注意:
函数中如果需要用到绝对地址的请使用绝对地址进行计算,其他方式无效。
(4)在Sheet1中,使用数组公式计算汽车在停车库中的停放时间。
要求:
⏹计算方法为:
“停放时间=出库时间-入库时间”
⏹格式为:
“小时:
分钟:
秒”
例如:
一小时十五分十二秒在停放时间中的表示为:
“1:
15:
12”
⏹将结果保存在“停车情况记录表”中的“停放时间”列中。
(5)使用函数公式,对“停车情况记录表”的停车费用进行计算。
要求:
根据Sheet1停放时间的长短计算停车费用,将计算结果填入到“停车情况记录表”的“应付金额”列中。
注意:
⏹停车按小时收费,对于不满一个小时的按照一个小时计费;
⏹对于超过整点小时数十五分钟(包含十五分钟)的多累积一个小时。
例如1小时23分,将以2小时计费
(6)使用统计函数,对Sheet1中的“停车情况记录表”根据下列条件进行统计。
⏹统计停车费用大于等于40元的停车记录条数,并将结果保存在J8单元格中;
⏹统计最高的停车费用,并将结果保存在J9单元格中。
(7)将Sheet1中的“停车情况记录表”复制到Sheet2中,对Sheet2进行高级筛选。
要求:
⏹筛选条件为:
“车型”-小汽车,“应付金额”>=30;
⏹将结果保存在Sheet2中。
注意:
⏹无需考虑是否删除筛选条件;
⏹复制过程中,将标题项“停车情况记录表”连同数据一同复制;
⏹复制数据表后,粘贴时,数据表必须顶格放置。
(8)根据Sheet1中的“停车情况记录表”,创建一个数据透视图,保存在Sheet3中。
⏹显示各种车型所收费用的汇总;
⏹x坐标设置为“车型”;
⏹求和项为“应付金额”;
⏹将对应的数据透视表保存在Sheet3中。
操作说明
1.设置单元格限定录入字数-数据有效性
根据操作要求
(1),打开Excel例3-02文件,选择Sheet4工作表,选择A1单元格,在“数据”选项卡的“数据工具”功能组,点击“
”(数据有效性)打开数据有效性对话框对话窗。
如图3-19所示。
图3-19选择数据有效性
在“数据有效性”对话窗口选择“设置”选项卡,设置“有效性条件”允许“文本长度”数据“等于”长度“5”,如图3-20所示。
点击选择“出错警告”项目选项卡,设置“出错样式”为“警告”,在“错误信息”栏下输入“只能录入5位数字或文本”。
图3-20设置数据有效性
2.判断闰年
根据操作要求
(2),对于闰年需表达两个可能分别成立的条件,所以使用OR函数。
而条件之一“年数能被4整除而不能被100整除”为两个并列条件,需要使用AND函数,这里表达整除需要用到取余函数MOD,而其中的当年年份需要用到年函数YEAR和当日日期函数TODAY。
选择Sheet4工作表的B1单元格,在“公式”选项卡下“函数库”功能组中点击“
”(逻辑),在弹出的逻辑函数列表中选择“OR”函数,在OR“函数参数”对话窗中,条件Logcal1的文本框中输入闰年的第一种判断方式,即“年数能被4整除而不能被100整除”,由于是一组并列条件,嵌入AND函数,将转到AND“函数参数”对话窗,分别在Logical1条件文本框中输入条件式“MOD(YEAR(TODAY()),4)=0”(年份能被4整除),在Logical2条件文本框中输入条件式“MOD(YEAR(TODAY()),100)>0”(年份不能能被100整除)。
如图3-21所示。
图3-21闰年的并列条件“被4整除而不能被100整除”
在编辑栏点击AND函数的结尾处(最后一个“)”符号前),回到OR“函数参数”对话窗,在Logical2条件文本框中输入第二个判断条件“能被400整除的年份”的条件式,“MOD(YEAR(TODAY()),400)=0”如图3-22所示。
图3-22通过OR函数表达闰年的两组判断方式
在B2单元格给出的完整表达式为:
“=OR(AND(MOD(YEAR(TODAY()),4)=0,MOD(YEAR(TODAY()),100)>0),MOD(YEAR(TODAY()),400)=0)”
3.计算车辆停放收费“单价”-水平查询函数Hlookup
单击“停车情况记录表”中的C9单元格,使其成为活动单元格。
在功能区选择“公式”选项卡,在“函数库”功能组中点击“
”(查询与引用),在弹出的函数列表中选择水平查询函数“HLOOKUP”,在弹出的如图3-23所示“函数参数”对话框中编辑函数。
在“函数参数”对话框中的第一行“Lookup_value”(查询参数值)框输入搜索值“停车情况记录表”第一条记录“车型”的单元格地址“B9”(确定在数组区域首行进行搜索时的搜索值)。
在第2行“Table_array”(搜寻数据表)框输入要搜索区域“停车价目表”数据区的绝对地址“$A$2:
$C$3”(可选择A2:
C3单元格区域后按F4键来绝对引用),用于确定要搜索的数组或数据表所在的区域。
在第3行“Row_index_num”(搜寻之目标数据所在行序号)框输入数字“2”,用以确定在数组区域首行搜索到满足搜索值时,要取的值位于该列的第几行,本题是取第二行的值。
在第4行“Range_lookup”(查询结果的匹配方式)框输入“FALSE”以设定查找精确匹配的逻辑值。
单击“确定”按钮,在“停车情况记录表”第一条记录的“单价”单元格C9得到使用了HLOOKUP函数后小汽车的小时停车单价为“5”。
双击C9单元格填充柄,完成停车单价的自动填充。
图3-23使用HLOOKUP填入单价数据
4.计算汽车停放时间
将光标定位于“停车情况记录表”第一条记录的“停放时间”单元格F9,在编辑栏输入“=E9-D9”后按回车键;双击F9单元格填充柄完成“停放时间”列的自动填充。
5.计算停车费
将光标定位于“停车情况记录表”第一条记录的“应付金额”单元格G9,先计算停车计费时间(小时),在功能区选择“公式”选项卡,在“函数库”功能组中点击“
”(逻辑),在弹出的函数列表中选择条件函数“IF”,在弹出的如图3-24所示“函数参数”对话框中编辑函数。
在第1行“Logical_test”(逻辑表达式)框输入逻辑表达式“HOUR(F9)<1”,判断停车时间是否不足1小时,使用时间函数HOUR()将提取F9单元格内的小时数进行判断是否小于0。
在第2行“Value_if_true”框输入当逻辑表达式“HOUR(F9)<0”为“true”的值,根据题意,在该栏输入数字“1”。
如图3-24所示。
图3-24判断不足I小时收费计时
在第3行“Value_if_false”框应输入当逻辑表达式“HOUR(F9)<0”为“false”的值,由于涉及是否超过整点小时数十五分钟的判断,会有两种可能的结果,需要嵌入一层IF函数进行再判断,点击第三行文本框,点击工作表编辑区左上角的名称栏中的“IF”函数名(嵌套“IF”函数),将进入新的“函数参数”窗口。
在第2层“函数参数”窗口的第1行“Logical_test”,输入MINUTE(F9)<15,以判断是否超过整点15分钟,其中MINUTE()将提取F9单元格内的分钟数。
在第2行“Value_if_true”输入没超过15分钟的计费时间“HOUR(F9)”,即按实际的停车小时数,不计分(舍弃)。
在第3行“Value_if_false”后的文本框输入超出整点15分以上的计时数(加计1小时)“HOUR(F9)+1”,如图3-25所示。
图3-25判断超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单元格右下角的填充柄,完成停车应付金额的自动填充。
6.统计停车记录数和最高停车费
将光标定位于J9单元格,在功能区选择“公式”选项卡,在“函数库”功能组中点击“
”(其他函数组),在弹出的函数组列表中选择“统计”函数组,在下级子菜单中选择“COUNTIF”函数,在弹出的“函数参数”对话框中输入参数。
在第1行“Range”(要统计的数据区域)输入“G9:
G39”,在第2行“Criteria”(统计条件)框输入“>=40”(不包括引号,使用非中文输入状态输入),如图3-26所示。
单击“确定”按钮完成统计,在J9单元格得到符合条件的记录条数为“4”。
图3-26统计收费大于40元的记录
将光标定位于J10单元格,在功能区选择“公式”选项卡,在“函数库”功能组中点击第1组函数(“
”)的列表展开键“”,在弹出的函数组列表中选择“最大值(M)”,在函数括号中输入统计数据区域“G9:
G39”,单击“确定”命令按钮,在J10单元格得到最高停车费用为“50”。
7.记录数据的高级筛选
选择Sheet1工作表,选择“停车情况记录表”所在单元格区域,复制该数据(包括标题)。
选择Sheet2工作表,右击A1单元格,在弹出菜单的“粘贴选项:
”下选择“
”(粘贴数值),将相关数据粘贴到Sheet2工作表,在“开始”选项卡“字体”功能组中点击“
”为数据区添加表格线。
选择“A1:
G1”单元格,点击“开始”选项卡“对齐方式”功能组中的“
”(合并并居中),恢复原表中标题栏的设置,如图3-27所示。
图3-27复制工作表数据
在Shet2工作表数据区的右侧空白区域设置筛选条件区(不要紧挨数据区),单击I3单元格,设置条件区如图3-28所示。
图3-28设置筛选条件区
单击表Sheet2要进行高级筛选的数据区中任一单元格,在“数据”选项卡中“排序和筛选”功能组中点击“
”(高级筛选),在打开的“高级筛选”对话框中“列表区域”已自动填入当前工作表的数据区单元格引用“$A$2:
$G$33”,点击“条件区域”右侧的文本框,在当前工作表选择上面设定的条件区域“Sheet2!
$I$3:
$J$4”,勾选“高级筛选”对话窗底部的“选择不重复记录”,单击“确定”按钮完成高级筛选。
如图3-29所示。
图3-29高级筛选
8.创建数据透视图
将光标定位于结果存放表Sheet3,选择“插入”选项卡,在“表格”功能组中点击“
”(数据透视表)右下的“”,在弹出的选项列表中选择“数据透视图”,在弹出的“创建数据透视表”对话框中,对于“请选择要分析的数据”拦下选择“选择一个表和区域”点击“表/区域”后面的文本框,选择Sheet1表中的“停车情况记录表”数据区“Sheet1!
$A$8:
$G$39”。
在“选择放置数据透视表及数据透视图的位置”栏下选择“现有工作表”,并点击“位置”后的文本框,点击Sheet3工作表的A1单元格(定位),点击“确定”即完成数据透视表的基本创建,如图3-30所示。
图3-30创建数据透视表
根据题目要求,在工作表Sheet3已创建的数据透视表界面,在右侧的“数据同时表字段列表”中,将“车型”字段拖到底部“轴字段(分类)”框中,将“应付金额”字段拖到底部“∑数值”框中,如图3-31所示,即完成数据透视图、表的建立。
图3-31为数据透视图表设置数据项