excel典型试题操作步骤.doc

6.2 典型试题

典型试题 1

打开素材库中的“典型试题 6-1.xls”文件，按下面的操作要求进行操作，并把操作
结果存盘。
注意：在做题时，不得将数据表进行更改。

一、操作要求

1. 在Sheet5的A1单元格中设置为只能录入5位数字或文本。当录入位数错误时，提示
错误原因，样式为“警告”，错误信息为“只能录入5位数字或文本”。
2. 使用数组公式，对 Sheet1 中“教材订购情况表”的订购金额进行计算，并将结果
保存在该表的“金额”列当中。
计算方法：金额 = 订数 * 单价
3. 使用统计函数，对 Sheet1 中“教材订购情况表”的结果按以下条件进行统计，并 将结果保存在 Sheet1 中的相应位置。
要求：
（1）统计出版社名称为“高等教育出版社”的书的种类数,并将结果保存在 sheet1
中 L2 单元格中。
（2）统计订购数量大于 110 且小于 850 的书的种类数,并将结果保存在 sheet1 中
L3 单元格中。
4. 使用函数，计算每个用户所订购图书所需支付的金额总数，并将结果保存在 Sheet1
中“用户支付情况表”的“支付总额”列中。
5. 使用函数，判断 Sheet2 中的年份是否为闰年，如果是，结果保存“闰年”，如果
不是，则结果保存“平年”，并将结果保存在“是否为闰年”列中。
说明：
闰年定义：年数能被 4 整除而不能被 100 整除，或者能被 400 整除的年份。
6. 将 Sheet1 中的“教材订购情况表”复制到 Sheet3 中，对 Sheet3 进行高级筛选.
要求：
（1）筛选条件为“订数>=500，且金额总数<=30000”。
（2）将结果保存在 Sheet3 中。
注意：
（1）无需考虑是否删除或移动筛选条件。

（2）复制过程中，将标题项“教材订购情况表”连同数据一同复制。
（3）数据表必须顶格放置。
7. 根据 Sheet1 中“教材订购情况表”的结果，在 Sheet4 中新建一张数据透视
表。 要求：
（1）显示每个客户在每个出版社所订的教材数目。
（2）行区域设置为“出版社”。
（3）列区域设置为“客户”。
（4）求和项为“订数”。
（5）数据区域设置为“订数”。

二、解答

步骤 1：选中 Sheet5 工作表中的 A1 单元格，选择菜单“数据”→“有效性”命 令（如图 6-13 所示），在打开的“数据有效性”对话框中，选择“设置”选项卡， 再选择“允许”为“文本长度”，“数据”为“等于”，在“长度”文本框中输入“5”， 如图 6-14 所示。
选择“出错警告”选项卡，再选择“样式”为“警告”，在“错误信息”文本框
中输入“只能录入 5 位数字或文本”，如图 6-15 所示，单击“确定”按钮。

图 6-13 选择“有效性”命令

图 6-14 “设置”选项卡

图 6-15 “出错警告”选项卡
步骤 2 ：在 Sheet1 工作表中，先选中 I3:I52 单元格区域，再输入公式

“=G3:G52*H3:H52”，然后同时按组合键 Shift+Ctrl+Enter，此时，公式编辑栏显示
“{=G3:G52*H3:H52}”。
说明：公式中单元格或单元格区域也可用鼠标选择输入，下同。
步骤 3：选中 L2 单元格，单击插入函数按扭，打开“插入函数”对话框，在 “选择类别”下拉列表框中选择“统计”，在“选择函数”列表框中选择“COUNTIF” 函数，如图 6-16 所示。单击“确定”按钮，弹出“函数参数”对话框，输入“Range” 参数为“D3：D52”，“Criteria”参数为“高等教育出版社”，如图 6-17 所示。再按 “确定”按钮，即可统计出出版社名称为“高等教育出版社”的书的种类数（6）。此 时，公式编辑栏显示“=COUNTIF(D3:D52,"高等教育出版社")”。

图 6-16 “插入函数”对话框

图 6-17 “函数参数”对话框

选 中 L3 单 元 格 ， 在 公 式 编 辑 栏 中 输 入 公 式 “=COUNTIF(G3:G52,"<850")-COUNTIF(G3:G52,"<=110")”，再按回车键确认，即可统 计出订购数量大于 110 且小于 850 的书的种类数（28）。
说明：COUNTIF 函数的功能和用法，请参见上一节的相关内容。
步骤 4：选中 L8 单元格，单击插入函数按扭，打开“插入函数”对话框，在 “选择类别”下拉列表框中选择“数学与三角函数”，在“选择函数”列表框中选择 “SUMIF”函数。单击“确定”按钮，弹出“函数参数”对话框，输入“Range”参数 为“A3：A52”，“Criteria”参数为“"=c1"”，“Sum_range”参数为“I3:I52”，如图
6-18 所示。再按“确定”按钮，即可计算出 c1 用户所订购图书需支付的金额总数
（721301）。此时，公式编辑栏显示“=SUMIF(A3:A52,"=c1",I3:I52)”。

图 6-18 “函数参数”对话框
在 L9 单元格中输入公式“=SUMIF(A3:A52,"=c2",I3:I52)”，按回车键确认，即
可计算出 c2 用户所订购图书需支付的金额总数（53337）。
在 L10 单元格中输入公式“=SUMIF(A3:A52,"=c3",I3:I52)”，按回车键确认，即
可计算出 c3 用户所订购图书需支付的金额总数（65122）。
在 L11 单元格中输入公式“=SUMIF(A3:A52,"=c4",I3:I52)”，按回车键确认，即
可计算出 c4 用户所订购图书需支付的金额总数（71253）。
说明：SUMIF 函数的功能和用法，请参见上一节的相关内容。
步骤 5：在 Sheet2 工作表中，选择 B2 单元格，在公式编辑栏中输入公式 “=IF(OR(AND(MOD(A2,4)=0,MOD(A2,100)<>0),MOD(A2,400)=0),"闰年","平年")”， 按回车键确认，即可判断出该年份是“闰年”还是“平年”。
双击 B2 单元格的填充柄，即可判断出其余年份是“闰年”还是“平年”。
说明：IF、OR、AND、MOD 函数的功能和用法，请参见上一节的相关内容。
步骤 6：选中 Sheet1 工作表中的 A1：I52 单元格区域，单击右键在快捷菜单中选 择“复制”命令。单击 Sheet3 工作表中的 A1 单元格，单击右键在快捷菜单中选择“粘 贴”命令。
在 Sheet3 工作表的空白区域创建筛选条件，如图 6-19 所示，在 K2：L3 单元格 区域中输入筛选条件。单击 Sheet3 工作表中 A1：I52 单元格区域中的任一单元格， 选择菜单“数据”→“筛选”→“高级筛选”命令（如图 6-20 所示），打开“高级筛 选”对话框，“列表区域”文本框中已自动填入数据清单所在的单元格区域（如图 6-21 所示）。将光标定位在“条件区域”文本框内，用鼠标拖选前面创建的筛选条件单元

格区域 K2：L3，“条件区域”文本框内会自动填入该区域地址（如图 6-22 所示）。再
单击“确定”按钮。

图 6-19 创建筛选条件

图 6-20 选择“高级筛选”命令

图 6-21 设置“列表区域” 图 6-22 设置“条件区域”

步骤 7：单击 Sheet1 工作表中 A1：I52 单元格区域中的任一单元格，选择菜单“数 据”→“数据透视表和数据透视图”命令（如图 6-23 所示），打开“数据透视表和数 据透视图向导——3 步骤之 1”对话框（如图 6-24 所示），选中“数据透视表”单选 钮后，再单击“下一步”按钮，打开“数据透视表和数据透视图向导——3 步骤之 2” 对话框，“选定区域”文本框中已自动填入数据清单所在的单元格区域（如图 6-25 所 示），单击“下一步”按钮，打开“数据透视表和数据透视图向导——3 步骤之 3”对 话框，选中“现有工作表”单选钮，再单击 Sheet4 工作表中的 A1 单元格，该单元格 地址会自动填入“现有工作表”下方的地址文本框中（如图 6-26 所示），单击“完成” 按钮。
在弹出的如图 6-27 所示界面中，将字段“出版社”拖至行字段区域，将字段“客
户”拖至列字段区域，将字段“订数”拖至数据项区域，结果如图 6-28 所示。
保存文件后退出。

图 6-23 选择“数据透视表和数据透视图”命令

图 6-24 “数据透视表和数据透视图向导——3 步骤之 1”对话框

图 6-25 “数据透视表和数据透视图向导——3 步骤之 2”对话框

图 6-26 “数据透视表和数据透视图向导——3 步骤之 3”对话框

图 6-27 “数据透视表”布局

图 6-28 数据透视表

典型试题 2

打开素材库中的“典型试题 6-2.xls”文件，按下面的操作要求进行操作，并把操作

结果存盘。
注意：在做题时，不得将数据表进行更改。

一、操作要求

1. 在Sheet5的A1单元格中设置为只能录入5位数字或文本。当录入位数错误时，提示
错误原因，样式为“警告”，错误信息为“只能录入5位数字或文本”。
2. 使用时间函数，对 Sheet1 中用户的年龄进行计算。
要求：假设当前时间是“2008-11-15”，结合用户的出生年月，计算用户的年龄，
并将其计算结果保存在“年龄”列当中。计算方法为两个时间年份之差。
3. 使用 REPLACE 函数，对 Sheet1 中用户的电话号码进行升级。 要求：对“原电话号码”列中的电话号码进行升级。升级方法是在区号（0571）
后面加上“8”，并将其计算结果保存在“升级电话号码”列的相应单元格中。
例如：电话号码“05716742808” 升级后为 “057186742808”
4. 在 Sheet1 中，使用 AND 函数，根据“性别”及“年龄”列中的数据，判断所有用
户是否为大于等于 40 岁的男性，并将结果保存在“是否>=40 男性”列中。
注意：如果是，保存结果为 TRUE；否则，保存结果为 FALSE。
5. 根据 Sheet1 中的数据，对以下条件，使用统计函数进行统计。 要求：
（1）统计性别为“男”的用户人数，将结果填入 Sheet2 的 B2 单元格中。
（2）统计年龄为“>40”岁的用户人数，将结果填入 Sheet2 的 B3 单元格中。
6. 将 Sheet1 复制到 Sheet3，并对 Sheet3 进行高级筛选。 要求：
（1）筛选条件为：“性别”－女、“所在区域”－西湖区。
（2）将筛选结果保存在 Sheet3 中。
注意：
（1）无需考虑是否删除或移动筛选条件。
（2）数据表必须顶格放置。
7. 根据 Sheet1 的结果，创建一个数据透视图 Chart1。 要求：
（1）显示每个区域所拥有的用户数量。
（2）x 坐标设置为“所在区域”。
（3）计数项为“所在区域”。
（4）将对应的数据透视表保存在 Sheet4 中。

二、解答

步骤 1：操作方法参见“典型试题 1”中的步骤 1。
步骤 2：在 Sheet1 工作表中，选中 D2 单元格，在公式编辑栏中输入公式 “=YEAR("2008-11-15")-YEAR(C2)”，再按回车键确认，即计算出该用户的年龄（41）。 双击 D2 单元格的填充柄，即可计算出其余用户的年龄。
说明：YEAR 函数的功能和用法，请参见上一节的相关内容。
步骤 3：选中 G2 单元格，单击插入函数按扭，打开“插入函数”对话框，在“选 择类别”下拉列表框中选择“文本”，在“选择函数”列表框中选择“REPLACE”函数， 单击“确定”按钮后，弹出“函数参数”对话框，输入“Old_text”参数为“F2”， “Start_num”参数为“5”，“Num_chars”参数为“0”，“New_text”参数为“8”，如 图 6-29 所示。再按“确定”按钮，即可对第一个电话号码进行升级。此时，公式编 辑栏显示“=REPLACE(F2,5,0,"8")”。
双击 G2 单元格的填充柄，即可对其余电话号码进行升级。
说明：REPLACE 函数的功能和用法，请参见上一节的相关内容。

图 6-29 “函数参数”对话框
步骤 4：在 H2 单元格中，输入公式“=AND(B2="男",D2>=40)”，再按回车键确认，即
可判断该用户是否为大于等于 40 岁的男性。
双击 H2 单元格的填充柄，即可判断其余用户是否为大于等于 40 岁的男性。
步骤 5：在 Sheet2 工作表的 B2 单元格中，输入公式“=COUNTIF(Sheet1!B2:B37,"男
")”，再按回车键确认，即可统计出男性用户人数（18）。
在 Sheet2 工作表的 B3 单元格中，输入公式“=COUNTIF(Sheet1!D2:D37,">40")”，再

按回车键确认，即可统计出大于 40 岁的用户人数（14）。
步骤 6：选中 Sheet1 工作表中的 A1：H37 单元格区域，单击右键在快捷菜单中选 择“复制”命令。单击 Sheet3 工作表中的 A1 单元格，单击右键在快捷菜单中选择“粘 贴”命令。
在 Sheet3 工作表的空白区域创建筛选条件，如图 6-30 所示，在 J1：K2 单元格区域 中输入筛选条件。单击 Sheet3 工作表中 A1：H37 单元格区域中的任一单元格，选择 菜单“数据”→“筛选”→“高级筛选”命令，打开“高级筛选”对话框，“列表区 域”文本框中已自动填入数据清单所在的单元格区域。将光标定位在“条件区域”文 本框内，用鼠标拖选前面创建的筛选条件单元格区域 J1：K2，“条件区域”文本框内 会自动填入该区域地址（如图 6-31 所示）。再单击“确定”按钮。
注意：J1 单元格中“性”和“别”之间有空格，原因是 B1 单元格中“性”和“别”
之间有空格。

图 6-30 创建筛选条件

图 6-31 设置“条件区域”
步骤 7：单击 Sheet1 工作表中 A1：H37 单元格区域中的任一单元格，选择菜单“数

据”→“数据透视表和数据透视图”命令，打开“数据透视表和数据透视图向导——
3 步骤之 1”对话框，选中“数据透视图（及数据透视表）”单选钮（如图 6-32 所示）， 再单击“下一步”按钮，打开“数据透视表和数据透视图向导——3 步骤之 2”对话 框，“选定区域”文本框中已自动填入数据清单所在的单元格区域（如图 6-33 所示）， 单击“下一步”按钮，打开“数据透视表和数据透视图向导——3 步骤之 3”对话框， 选中“现有工作表”单选钮，再单击 Sheet4 工作表中的 A1 单元格，该单元格地址会 自动填入“现有工作表”下方的地址文本框中（如图 6-34 所示）。单击“布局”按钮， 在弹出的如图 6-35 所示界面中，将字段“所在区域”分别拖至“行”区域和“数据” 区域内，然后按“确定”按钮返回图 6-34 所示对话框，按“完成”按钮。这时在 Sheet4 工作表前会插入一张名为 Chart1 的工作表。打开该工作表可看到根据 Sheet1 工作表 创建的数据透视图（如图 6-36 所示），打开 Sheet4 工作表有如图 6-37 所示的相应的 数据透视表。
保存文件后退出。

图 6-32 “数据透视表和数据透视图向导——3 步骤之 1”对话框

图 6-33 “数据透视表和数据透视图向导——3 步骤之 2”对话框

图 6-34 “数据透视表和数据透视图向导——3 步骤之 3”对话框

图 6-35 “数据透视表”布局

图 6-36 数据透视图

图 6-37 数据透视表

典型试题 3

打开素材库中的“典型试题 6-3.xls”文件，按下面的操作要求进行操作，并把操作
结果存盘。
注意：在做题时，不得将数据表进行更改。

一、操作要求

1. 在Sheet4的A1单元格中设置为只能录入5位数字或文本。当录入位数错误时，提示
错误原因，样式为“警告”，错误信息为“只能录入5位数字或文本”。
2. 使用 VLOOKUP 函数，对 Sheet1 中的“三月份销售统计表”的“产品名称”列和“产 品单价”列进行填充。
要求：根据“企业销售产品清单”，使用 VLOOKUP 函数，将产品名称和产品单价填充
到“三月份销售统计表”的“产品名称”列和“产品单价”列中。
3. 使用数组公式，计算 Sheet1 中的“三月份销售统计表”中的销售金额，并将结果
填入到该表的“销售金额”列中。
计算方法：销售金额 = 产品单价 * 销售数量
4. 使用统计函数，根据“三月份销售统计表”中的数据，计算“分部销售业绩统计
表”中的总销售额，并将结果填入该表的“总销售额”列。
5. 在 Sheet1 中，使用 RANK 函数，在“分部销售业绩统计”表中，根据“总销售额”
对各部门进行排名，并将结果填入到“销售排名”列中。
6. 将 sheet1 中的“三月份销售统计表”复制到 Sheet2 中，对 Sheet2 进行高级筛选。 要求：
（1）筛选条件为：“销售数量”－ >3、“所属部门”－ 市场 1 部、“销售金额”-
>1000。
（2）将筛选结果保存在 Sheet2 中。
注意：
（1）无需考虑是否删除或移动筛选条件。
（2）复制过程中，将标题项“三月份销售统计表”连同数据一同复制。
（3）数据表必须顶格放置。
7. 根据 Sheet1 的“三月份销售统计表”中的数据，新建一个数据透视图 Chart1。 要求：
（1）该图形显示每位经办人的总销售额情况。
（2）x 坐标设置为“经办人”。
（3）数据区域设置为“销售金额”。

（4）求和项为“销售金额”。
（5）将对应的数据透视表保存在 Sheet3 中。

二、解答

步骤 1：操作方法参见“典型试题 1”中的步骤 1。
步骤 2：选中 Sheet1 工作表中的 G3 单元格，单击插入函数按扭，打开“插入函数” 对话框，在“选择类别”下拉列表框中选择“查找与引用”，在“选择函数”列表框 中选择“VLOOKUP”函数，单击“确定”按钮后，弹出“函数参数”对话框。输入 “ Lookup_value ” 参 数为 “ F3 ”，“ Table_array ” 参 数为 “ $A$3:$B$10 ”， “Col_index_num”参数为“2”，“Range_lookup”参数为“FALSE”，如图 6-38 所示。 再按“确定”按钮，即可查找到产品型号为“A01”的产品名称（卡特扫描枪）。此时， 公式编辑栏显示“=VLOOKUP(F3,$A$3:$B$10,2,FALSE)”。
双击 G3 单元格的填充柄，即可查找到其余产品型号的产品名称。
在 H3 单元格中，输入公式“=VLOOKUP(F3,$A$3:$C$10,3,FALSE)”，再按回车键确认， 即可查找到产品型号为“A01”的产品单价（368）。双击 H3 单元格的填充柄，即可查 找到其余产品型号的产品单价。
说明：VLOOKUP 函数的功能和用法，请参见上一节的相关内容。

图 6-38 “函数参数”对话框
步骤 3：先选中 L3:L44 单元格区域，再输入公式“=H3:H44*I3:I44”，然后同时
按组合键 Shift+Ctrl+Enter，此时，公式编辑栏显示“{=H3:H44*I3:I44}”。

步骤 4：选中 O3 单元格，单击插入函数按扭，打开“插入函数”对话框，在
“选择类别”下拉列表框中选择“数学与三角函数”，在“选择函数”列表框中选择
“SUMIF”函数。单击“确定”按钮，弹出“函数参数”对话框，输入“Range”参数 为“K3：K44”，“Criteria”参数为“"=市场 1 部"”，“Sum_range”参数为“L3:L44”， 如图 6-39 所示。再按“确定”按钮，即可计算出市场 1 部的总销售额（35336）。此 时，公式编辑栏显示“=SUMIF(K3:K44,"=市场 1 部",L3:L44)”。
在 O4 单元格中，输入公式“=SUMIF(K3:K44,"=市场 2 部",L3:L44)”后，按回车键确
认。
在 O5 单元格中，输入公式“=SUMIF(K3:K44,"=市场 3 部",L3:L44)”后，按回车键确
认。

图 6-39 “函数参数”对话框

步骤 5：选中 P3 单元格，单击插入函数按扭，打开“插入函数”对话框，在 “选择类别”下拉列表框中选择“统计”，在“选择函数”列表框中选择“RANK”函 数。单击“确定”按钮，弹出“函数参数”对话框，输入“Number”参数为“O3”，“Ref” 参数为“$O$3:$O$5”，“Order”参数为“0”，如图 6-40 所示。再按“确定”按钮， 即 可 统 计 出 市场 1 部 的 总 销售额 排 名 （ 1 ）。 此 时 ，公 式 编 辑 栏显 示 “=RANK(O3,$O$3:$O$5,0)”。
双击 P3 单元格的填充柄，可统计出其他部门的总销售额排名。
说明：RANK 函数的功能和用法，请参见上一节的相关内容。

图 6-40 “函数参数”对话框

步骤 6：选中 Sheet1 工作表中的 E1：L44 单元格区域，单击右键在快捷菜单中选 择“复制”命令。单击 Sheet2 工作表中的 A1 单元格，单击右键在快捷菜单中选择“选 择性粘贴”命令，打开如图 6-41 所示的“选择性粘贴”对话框，选中“值和数字格 式”单选钮，再单击“确定”按钮。
在 Sheet2 工作表的空白区域创建筛选条件，如图 6-42 所示，在 J2：L3 单元格 区域中输入筛选条件。单击 Sheet2 工作表中 A1：H44 单元格区域中的任一单元格， 选择菜单“数据”→“筛选”→“高级筛选”命令，打开“高级筛选”对话框，“列 表区域”文本框中已自动填入数据清单所在的单元格区域。将光标定位在“条件区域” 文本框内，用鼠标拖选前面创建的筛选条件单元格区域 J2：L3，“条件区域”文本框 内会自动填入该区域地址（如图 6-43 所示）。再单击“确定”按钮。

图 6-41 “选择性粘贴”对话框

图 6-42 创建筛选条件

图 6-43 设置“条件区域”
步骤 7：单击 Sheet1 工作表中 E1：L44 单元格区域中的任一单元格，选择菜单“数
据”→“数据透视表和数据透视图”命令，打开“数据透视表和数据透视图向导——
3 步骤之 1”对话框，选中“数据透视图（及数据透视表）”单选钮，再单击“下一步” 按钮，打开“数据透视表和数据透视图向导——3 步骤之 2”对话框，“选定区域”文 本框中已自动填入数据清单所在的单元格区域，单击“下一步”按钮，打开“数据透 视表和数据透视图向导——3 步骤之 3”对话框，选中“现有工作表”单选钮，再单 击 Sheet3 工作表中的 A1 单元格，该单元格地址会自动填入“现有工作表”下方的地 址文本框中。单击“布局”按钮，在弹出的如图 6-44 所示界面中，把字段“经办人” 拖至“行”区域内，把字段“销售金额”拖至“数据”区域内，然后按“确定”按钮 返回，再按“完成”按钮。这时在 Sheet3 工作表前会插入一张名为 Chart1 的工作表。 打开该工作表可看到根据 Sheet1 中“三月份销售统计表”创建的数据透视图（如图
6-45 所示），打开 Sheet3 工作表有如图 6-46 所示的相应的数据透视表。
保存文件后退出。

图 6-44 “数据透视表”布局

图 6-45 数据透视图

图 6-46 数据透视表

典型试题 4

打开素材库中的“典型试题 6-4.xls”文件，按下面的操作要求进行操作，并把操作
结果存盘。
注意：在做题时，不得将数据表进行更改。

一、操作要求

1. 在Sheet4的A1单元格中设置为只能录入5位数字或文本。当录入位数错误时，提示
错误原因，样式为“警告”，错误信息为“只能录入5位数字或文本”。
2. 使用 HLOOKUP 函数，对 Sheet1“停车情况记录表”中的“单价”列进行填充。 要求：根据 Sheet1 中的“停车价目表”价格，使用 HLOOKUP 函数对“停车情况记
录表”中的“单价”列根据不同的车型进行填充。
注意：函数中如果需要用到绝对地址的将使用绝对地址进行计算，其他方式无效。
3. 在 Sheet1 中，使用时间函数计算汽车在停车库中的停放时间。 要求：
（1）计算方法为：“停放时间 = 出库时间 -入库时间”。
（2）格式为：“小时：分钟：秒”。
（3）将结果保存在“停车情况记录表”中的“停放时间”列中。
（例如：一小时十五分十二秒在停放时间中的表示为：“1：15：12”）
4. 使用函数公式，对“停车情况记录表”的停车费用进行计算。
要求：根据 Sheet1 停放时间的长短计算停车费用，将计算结果填入到“停车情况
记录表”的“应付金额”列中。
注意：
（1）停车按小时收费，对于不满一个小时的按照一个小时计费。
（2）对于超过整点小时数十五分钟（包含十五分钟）的多累积一个小时。
（例如 1 小时 23 分，将以 2 小时计费）
5. 使用统计函数，对 Sheet1 中的“停车情况记录表”根据下列条件进行统计。 要求：
（1）统计停车费用大于等于 40 元的停车记录条数，并将结果保存在 J8 单元格中。
（2）统计最高的停车费用，并将结果保存在 J9 单元格中。
6. 将 Sheet1 中的“停车情况记录表”复制到 Sheet2 中，对 Sheet2 进行高级筛选。 要求：
（1）筛选条件为：“车型”－ 小汽车，“应付金额”>＝30。
（2）将结果保存在 Sheet2 中。

注意：
（1）无需考虑是否删除筛选条件。
（2）复制过程中，将标题项“停车情况记录表”连同数据一同复制。
（3）数据表必须顶格放置。
7. 根据 Sheet1 中的“停车情况记录表”，创建一个数据透视图 Chart1。 要求：
（1）显示各种车型所收费用的汇总。
（2）x 坐标设置为“车型”。
（3）求和项为“应付金额”。
（4）将对应的数据透视表保存在 Sheet3 中。

二、解答

步骤 1：操作方法参见“典型试题 1”中的步骤 1。
步骤 2：选中 Sheet1 工作表中的 C9 单元格，单击插入函数按扭，打开“插入函数” 对话框，在“选择类别”下拉列表框中选择“查找与引用”，在“选择函数”列表框 中选择“HLOOKUP”函数，单击“确定”按钮后，弹出“函数参数”对话框。输入 “ Lookup_value ” 参 数为 “ B9 ”，“ Table_array ” 参 数为 “ $A$2:$C$3 ”， “Row_index_num”参数为“2”，“Range_lookup”参数为“FALSE”，如图 6-47 所示。 再按“确定”按钮，即可查找到第一辆车的停车单价（5）。此时，公式编辑栏显示 “=HLOOKUP(B9,$A$2:$C$3,2,FALSE)”。
双击 C9 单元格的填充柄，即可查找到其余车辆的停车单价。
说明：HLOOKUP 函数的功能和用法，请参见上一节的相关内容。

图 6-47 “函数参数”对话框
步骤 3：在 F9 单元格中，输入公式“=E9-D9”，再按回车键确认，即计算出第一
辆车的停放时间（3:03:10）。
双击 F9 单元格的填充柄，即可计算出其余车辆的停放时间。
步 骤 4 ： 选 中 G9 单 元 格 ， 在 公 式 编 辑 栏 中 输 入 公 式 “=IF(HOUR(F9)=0,1,IF(MINUTE(F9)>=15,HOUR(F9)+1,HOUR(F9)))*C9”，再按回车键 确认，即可计算出第一辆车的停车费用（15）。
双击 G9 单元格的填充柄，即可计算出其余车辆的停车费用。
说明：HOUR、MINUTE 函数的功能和用法，请参见上一节的相关内容。上述公式中 用到了 IF 函数的嵌套（即函数中还有函数）。
步骤 5：选中 J8 单元格，单击插入函数按扭，打开“插入函数”对话框，在“选 择类别”下拉列表框中选择“统计”，在“选择函数”列表框中选择“COUNTIF”函数，
单击“确定”按钮后，弹出“函数参数”对话框，输入“Range”参数为“G9:G39”， “Criteria”参数为“">=40"”，如图 6-48 所示。再按“确定”按钮，即可统计出停 车费 用大 于 等 于 40 元 的 停 车 记 录 条 数 （ 4 ）。 此 时 ，公 式 编 辑 栏显 示 “=COUNTIF(G9:G39,">=40")”。

图 6-48 “函数参数”对话框

选中 J9 单元格，单击插入函数按扭，打开“插入函数”对话框，在“选择类别” 下拉列表框中选择“统计”，在“选择函数”列表框中选择“MAX”函数，单击“确定” 按钮后，弹出“函数参数”对话框，输入“Number1”参数为“G9:G39”，“Number2” 参数不输入，如图 6-49 所示。再按“确定”按钮，即可统计出最高的停车费用（50）。 此时，公式编辑栏显示“=MAX(G9:G39)”。
说明：MAX 函数的功能和用法，请参见上一节的相关内容。

图 6-49 “函数参数”对话框
步骤 6：选中 Sheet1 工作表中的 A7：G39 单元格区域，单击右键在快捷菜单中选

择“复制”命令。单击 Sheet2 工作表中的 A1 单元格，单击右键在快捷菜单中选择“选
择性粘贴”命令，在弹出的“选择性粘贴”对话框中，选中“值和数字格式”单选钮，
再按“确定”按钮。
在 Sheet2 工作表的空白区域创建筛选条件，如图 6-50 所示，在 I2：J3 单元格区域 中输入筛选条件。单击 Sheet2 工作表中 A1：G33 单元格区域中的任一单元格，选择 菜单“数据”→“筛选”→“高级筛选”命令，打开“高级筛选”对话框，“列表区 域”文本框中已自动填入数据清单所在的单元格区域。将光标定位在“条件区域”文 本框内，用鼠标拖选前面创建的筛选条件单元格区域 I2：J3，“条件区域”文本框内 会自动填入该区域地址（如图 6-51 所示）。再单击“确定”按钮。

图 6-50 创建筛选条件

图 6-51 设置“条件区域”
步骤 7：单击 Sheet1 工作表中 A7：G39 单元格区域中的任一单元格，选择菜单“数
据”→“数据透视表和数据透视图”命令，打开“数据透视表和数据透视图向导——

3 步骤之 1”对话框，选中“数据透视图（及数据透视表）”单选钮，再单击“下一步”
按钮，打开“数据透视表和数据透视图向导——3 步骤之 2”对话框，“选定区域”文 本框中已自动填入数据清单所在的单元格区域，单击“下一步”按钮，打开“数据透 视表和数据透视图向导——3 步骤之 3”对话框，选中“现有工作表”单选钮，再单 击 Sheet3 工作表中的 A1 单元格，该单元格地址会自动填入“现有工作表”下方的地 址文本框中。单击“布局”按钮，在弹出的如图 6-52 所示界面中，将字段“车型” 拖至“行”区域内，将字段“应付金额”拖至“数据”区域内，然后按“确定”按钮 返回，再按“完成”按钮。这时在 Sheet3 工作表前会插入一张名为 Chart1 的工作表。 打开该工作表可看到根据 Sheet1 中“停车情况记录表”创建的数据透视图（如图 6-53 所示），打开 Sheet3 工作表有如图 6-54 所示的相应的数据透视表。
保存文件后退出。

图 6-52 “数据透视表”布局

图 6-53 数据透视图

图 6-54 数据透视表