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

《二级excel高级操作-停车-解题步骤》由会员分享，可在线阅读，更多相关《二级excel高级操作-停车-解题步骤（8页珍藏版）》请在金锄头文库上搜索。

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

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

3、列中。注意： 停车按小时收费，对于不满一个小时的按照一个小时计费； 对于超过整点小时数十五分钟（包含十五分钟）的多累积一个小时。例如 1 小时 23 分，将以 2 小时计费（6）使用统计函数，对 Sheet1 中的“停车情况记录表”根据下列条件进行统计。 统计停车费用大于等于 40 元的停车记录条数，并将结果保存在 J8 单元格中； 统计最高的停车费用，并将结果保存在 J9 单元格中。（7）将 Sheet1 中的“停车情况记录表”复制到 Sheet2 中，对 Sheet2 进行高级筛选。要求： 筛选条件为：“车型”小汽车，“应付金额”30； 将结果保存在 Sheet2 中。注意： 无需考虑是否

4、删除筛选条件； 复制过程中，将标题项“停车情况记录表”连同数据一同复制； 复制数据表后，粘贴时，数据表必须顶格放置。（8）根据 Sheet1 中的“停车情况记录表” ，创建一个数据透视图，保存在 Sheet3 中。 显示各种车型所收费用的汇总； x 坐标设置为“车型”； 求和项为“应付金额”； 将对应的数据透视表保存在 Sheet3 中。操作说明1 设置单元格限定录入字数-数据有效性根据操作要求（1） ，打开 Excel 例 3-02 文件，选择 Sheet4 工作表，选择 A1 单元格，在“数据”选项卡的“数据工具”功能组，点击“ ”（数据有效性）打开数据有效性对话框对话窗。如 图 3- 1

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

6、选择 Sheet4 工作表的 B1 单元格，在“公式”选项卡下“函数库”功能组中点击“ ”（逻辑） ，在弹出的逻辑函数列表中选择“OR”函数，在 OR“函数参数”对话窗中，条件Logcal1 的文本框中输入闰年的第一种判断方式，即 “年数能被 4 整除而不能被 100 整除” ，由于是一组并列条件，嵌入 AND 函数，将转到 AND“函数参数”对话窗，分别在Logical1 条件文本框中输入条件式“MOD(YEAR(TODAY(),4)=0” （年份能被 4 整除） ，在Logical2 条件文本框中输入条件式“MOD(YEAR(TODAY(),100)0” （年份不能能被 100整除） 。如

7、 图 3- 3 所示。图 3- 3 闰年的并列条件 “被 4 整除而不能被 100 整除”在编辑栏点击 AND 函数的结尾处（最后一个“） ”符号前） ，回到 OR“函数参数”对话窗，在 Logical2 条件文本框中输入第二个判断条件“能被 400 整除的年份”的条件式，“MOD(YEAR(TODAY(),400)=0”如 图 3- 4 所示。图 3- 4 通过 OR 函数表达闰年的两组判断方式在 B2 单元格给出的完整表达式为：“=OR(AND(MOD(YEAR(TODAY(),4)=0,MOD(YEAR(TODAY(),100)0),MOD(YEAR(TODAY(),400)=0)”3

8、计算车辆停放收费“单价”-水平查询函数 Hlookup单击“停车情况记录表”中的 C9 单元格，使其成为活动单元格。在功能区选择“公式”选项卡，在“函数库”功能组中点击“ ”（查询与引用） ，在弹出的函数列表中选择水平查询函数“HLOOKUP ”，在弹出的如 图 3- 5 所示“函数参数”对话框中编辑函数。在“函数参数”对话框中的第一行“Lookup_value ”（查询参数值）框输入搜索值 “停车情况记录表”第一条记录“车型”的单元格地址“B9” （确定在数组区域首行进行搜索时的搜索值） 。在第 2 行“Table_array” （搜寻数据表）框输入要搜索区域“停车价目表”数据区的绝对地址“

9、$A$2：$C$3” （可选择 A2:C3 单元格区域后按 F4 键来绝对引用） ，用于确定要搜索的数组或数据表所在的区域。在第 3 行“Row_index_num” （搜寻之目标数据所在行序号）框输入数字“2” ，用以确定在数组区域首行搜索到满足搜索值时，要取的值位于该列的第几行，本题是取第二行的值。在第 4 行“Range_lookup” （查询结果的匹配方式）框输入“FALSE”以设定查找精确匹配的逻辑值。单击“确定”按钮，在“停车情况记录表”第一条记录的“单价”单元格 C9 得到使用了 HLOOKUP 函数后小汽车的小时停车单价为 “5”。双击 C9 单元格填充柄，完成停车单价的自动填

10、充。图 3- 5 使用 HLOOKUP 填入单价数据4计算汽车停放时间将光 标 定 位 于 “停 车 情 况 记 录 表 ”第 一 条 记 录 的 “停 放 时 间 ”单 元 格 F9， 在 编 辑 栏 输入 “=E9-D9”后 按 回 车 键 ； 双 击 F9 单 元 格 填 充 柄 完 成 “停 放 时 间 ”列 的 自 动 填 充 。5计算停车费将光标定位于“停车情况记录表”第一条记录的“应付金额”单元格 G9，先计算停车计费时间（小时） ，在功能区选择“公式”选项卡，在“函数库”功能组中点击“ ”（逻辑） ，在弹出的函数列表中选择条件函数“IF” ，在弹出的如 图 3- 6 所示“函数

11、参数”对话框中编辑函数。在第 1 行“Logical_test” （逻辑表达式）框输入逻辑表达式“HOUR(F9)15,HOUR(F9)+1,HOUR(F9)”，是按计时要求的停车小时数。停 车 小 时 数 的 表 达 式 后 输 入 “*C9”（ 乘 上 收 费 单 价 ） ， 即 得 到 每 小 时 停 车 费 ， 整 个 表 达式 为 “=IF(HOUR(F9)15,HOUR(F9)+1,HOUR(F9)*C9”。双击 G9 单元格右下角的填充柄，完成停车应付金额的自动填充。6统计停车记录数和最高停车费将光标定位于 J9 单元格，在功能区选择“公式”选项卡，在“函数库”功能组中点击“ ”

12、（其他函数组） ，在弹出的函数组列表中选择 “统计 ”函数组，在下级子菜单中选择“COUNTIF”函数，在弹出的“函数参数”对话框中输入参数。在第 1 行“Range” （要统计的数据区域）输入“G9:G39 ”，在第 2 行“Criteria” （统计条件）框输入“=40” （不包括引号，使用非中文输入状态输入） ，如 图 3- 8 所示。单击“确定”按钮完成统计，在 J9 单元格得到符合条件的记录条数为“4” 。图 3- 8 统计收费大于 40 元的记录将光标定位于 J10 单元格，在功能区选择“公式”选项卡，在“函数库”功能组中点击第 1 组函数（“ ”）的列表展开键 “”，在弹出的函数

13、组列表中选择 “最大值( M)”，在函数括号中输入统计数据区域“G9:G39 ”，单击“确定 ”命令按钮，在 J10 单元格得到最高停车费用为“50” 。 7记录数据的高级筛选选择 Sheet1 工作表，选择“停车情况记录表”所在单元格区域，复制该数据（包括标题） 。选择 Sheet2 工作表，右击 A1 单元格，在弹出菜单的 “粘贴选项：”下选择“ ”（粘贴数值） ，将相关数据粘贴到 Sheet2 工作表，在“开始”选项卡“字体”功能组中点击“ ”为数据区添加表格线。选择“A1：G1 ”单元格，点击“开始”选项卡“对齐方式”功能组中的“ ”（合并并居中） ，恢复原表中标题栏的设置，如 图 3

14、- 9 所示。图 3- 9 复制工作表数据在 Shet2 工作表数据区的右侧空白区域设置筛选条件区（不要紧挨数据区） ，单击 I3单元格，设置条件区如 图 3- 10 所示。图 3- 10 设置筛选条件区单击表 Sheet2 要进行高级筛选的数据区中任一单元格，在“数据”选项卡中“排序和筛选”功能组中点击“ ”（高级筛选） ，在打开的“高级筛选”对话框中“列表区域”已自动填入当前工作表的数据区单元格引用“$A$2:$G$33 ”，点击“条件区域”右侧的文本框，在当前工作表选择上面设定的条件区域“Sheet2!$I$3:$J$4” ，勾选“高级筛选”对话窗底部的“选择不重复记录” ，单击“确定”

15、按钮完成高级筛选。如 图 3- 11 所示。图 3- 11 高级筛选8创建数据透视图将光标定位于结果存放表 Sheet3，选择“插入”选项卡，在“表格”功能组中点击“”（数据透视表）右下的 “”，在弹出的选项列表中选择 “数据透视图” ，在弹出的“创建数据透视表”对话框中，对于“请选择要分析的数据”拦下选择“选择一个表和区域”点击“表/区域”后面的文本框，选择 Sheet1 表中的“停车情况记录表”数据区“Sheet1!$A$8:$G$39”。在“选择放置数据透视表及数据透视图的位置”栏下选择“现有工作表” ，并点击“位置”后的文本框，点击 Sheet3 工作表的 A1 单元格（定位） ，点击“确定”即完成数据透视表的基本创建，如 图 3- 12 所示。图 3- 12 创建数据透视表根据题目要求，在工作表 Sheet3 已创建的数据透视表界面，在右侧的“数据同时表字段列表”中，将“车型”字段拖到底部“轴字段（分类） ”框中，将“应付金额”字段拖到底部“数值”框中，如 图 3- 13 所示，即完成数据透视图、表的建立。图 3- 13 为数据透视图表设置数据项