6.2 典型试
题
典型试题1
打开素材库中的“典型试题6-1.x ls”文件，按下面的操作要求进行操作，并把操作结果存盘。
注意：在做题时，不得将数据表进行更改。
一、操作要求
1. 在Sh e et5的A1单元格中设置为只能录入5位数字或文本。当录入位数错误时，提示错误原因，样式为“警告”，错误信息为“只能录入5位数字或文本”。
2. 使用数组公式，对S he e t1中“教材订购情况表”的订购金额进行计算，并将结果保存在该表的“金额”列当中。
计算方法：金额= 订数* 单价
3. 使用统计函数，对S he e t1中“教材订购情况表”的结果按以下条件进行统计，并将结果保存在S heet1 中的相应位置。
要求：
（1）统计出版社名称为“高等教育出版社”的书的种类数,并将结果保存在s heet1 中L2 单元格中。
（2）统计订购数量大于110 且小于850 的书的种类数,并将结果保存在sheet1 中L3 单元格中。
4. 使用函数，计算每个用户所订购图书所需支付的金额总数，并将结果保存在S h e et1
中“用户支付情况表”的“支付总额”列中。
5. 使用函数，判断S heet2 中的年份是否为闰年，如果是，结果保存“闰年”，如果不是，则结果保存“平年”，并将结果保存在“是否为闰年”列中。
说明：
闰年定义：年数能被4整除而不能被100 整除，或者能被400 整除的年份。
6. 将S hee t1中的“教材订购情况表”复制到S hee t3中，对S heet3 进行高级筛选.
要求：
（1）筛选条件为“订数>=500，且金额总数<=30000”。
（2）将结果保存在S heet3 中。
注意：
（1）无需考虑是否删除或移动筛选条件。
（2）复制过程中，将标题项“教材订购情况表”连同数据一同复制。
（3）数据表必须顶格放置。
7. 根据Sheet1 中“教材订购情况表”的结果，在S heet4 中新建一张数据透视表。要求：
（1）显示每个客户在每个出版社所订的教材数目。
（2）行区域设置为“出版社”。
（3）列区域设置为“客户”。
。
（4）求和项为“订数”
（5）数据区域设置为“订数”。
二、解答
步骤1：选中S heet5 工作表中的A1 单元格，选择菜单“数据”→“有效性”命令（如图6-13 所示），在打开的“数据有效性”对话框中，选择“设置”选项卡，再选择“允许”为“文本长度”，“数据”为“等于”，在“长度”文本框中输入“5”，如图6-14 所示。
选择“出错警告”选项卡，再选择“样式”为“警告”，在“错误信息”文本框
中输入“只能录入5位数字或文本”，如图6-15 所示，单击“确定”按钮。
图6-13 选择“有效性”命令
图6-14 “设置”选项卡
图6-15 “出错警告”选项卡
步骤2：在Sheet1 工作表中，先选中I3:I52单元格区域，再输入公式
“=G3:G52*H3:H52”，然后同时按组合键Sh i ft+Ctr l+Enter，此时，公式编辑栏显示“{=G3:G52*H3:H52}”。
说明：公式中单元格或单元格区域也可用鼠标选择输入，下同。
步骤3：选中L2 单元格，单击插入函数按扭，打开“插入函数”对话框，在“选
，在“选择函数”列表框中选择“COUNTIF”函数，择类别”下拉列表框中选择“统计”
如图6-16 所示。单击“确定”按钮，弹出“函数参数”对话框，输入“R ange”参数为“D3：D52”，“Criteria”参数为“高等教育出版社”，如图6-17 所示。再按“确定”按钮，即
。此时，公式编辑栏显示可统计出出版社名称为“高等教育出版社”的书的种类数（6）
。
“=COUNT I F(D3:D52,"高等教育出版社")”
图6-16 “插入函数”对话框
图6-17 “函数参数”对话框
选中L3 单元格，在公式编辑栏中输入公式“=COUNTIF(G3:G52,"<850")-C OUNTIF(G3:G52,"<=110")”
，再按回车键确认，即可统计出订购数量大于110 且小于850 的书的种类数（28）
。
说明：COUN T IF函数的功能和用法，请参见上一节的相关内容。
步骤4：选中L8 单元格，单击插入函数按扭，打开“插入函数”对话框，在“选
，在“选择函数”列表框中选择“SUMIF”择类别”下拉列表框中选择“数学与三角函数”
，函数。单击“确定”按钮，弹出“函数参数”对话框，输入“Range”参数为“A3：A52”“Cr i teria”参数为“"=c1"”，“Sum_range”参数为“I3:I52”
，如图
6-18 所示。再按“确定”按钮，即可计算出c1 用户所订购图书需支付的金额总数（721301）
。
。此时，公式编辑栏显示“=S UMIF(A3:A52,"=c1",I3:I52)”
图6-18 “函数参数”对话框
，按回车键确认，即在L9 单元格中输入公式“=SUMIF(A3:A52,"=c2",I3:I52)”
。
可计算出c2 用户所订购图书需支付的金额总数（53337）
，按回车键确认，即在L10 单元格中输入公式“=S UMIF(A3:A52,"=c3",I3:I52)”
。
可计算出c3 用户所订购图书需支付的金额总数（65122）
，按回车键确认，即在L11 单元格中输入公式“=S UMIF(A3:A52,"=c4",I3:I52)”
可计算出c4 用户所订购图书需支付的金额总数（71253）
。
说明：SUMIF 函数的功能和用法，请参见上一节的相关内容。
步骤5：在Sheet2 工作表中，选择B2 单元格，在公式编辑栏中输入公式“=IF(OR(AND(M OD(A2,4)=0,MOD(A2,100)<>0),MOD(A2,400)=0),"闰年","平年")”，按回车键确认，即可判断出该年份是“闰年”还是“平年”。
。
双击B2 单元格的填充柄，即可判断出其余年份是“闰年”还是“平年”
说明：IF、OR、AND、MOD函数的功能和用法，请参见上一节的相关内容。
步骤6：选中S heet1 工作表中的A1：I52 单元格区域，单击右键在快捷菜单中选择“复制”命令。单击S h eet3工作表中的A1 单元格，单击右键在快捷菜单中选择“粘贴”命令。
在Sheet3 工作表的空白区域创建筛选条件，如图6-19所示，在K2：L3 单元格区域中输入筛选条件。单击Sheet3 工作表中A1：I52单元格区域中的任一单元格，选
，打开“高级筛选”择菜单“数据”→“筛选”→“高级筛选”命令（如图6-20 所示）
。
“列表区域”文本框中已自动填入数据清单所在的单元格区域（如图6-21 所示）对话框，
将光标定位在“条件区域”文本框内，用鼠标拖选前面创建的筛选条件单元
前言126
。再格区域K2：L3，“条件区域”文本框内会自动填入该区域地址（如图6-22 所示）
单击“确定”按钮。
图6-19 创建筛选条件
图6-20 选择“高级筛选”命令
前言127
图6-21 设置“列表区域”图6-22 设置“条件区域”
步骤7：单击S heet1 工作表中A1：I52 单元格区域中的任一单元格，选择菜单“数据”→“数据透视表和数据透视图”命令（如图6-23 所示），打开“数据透视表和数据透视
，选中“数据透视表”单选钮后，再图向导——3 步骤之1”对话框（如图6-24 所示）
“选单击“下一步”按钮，打开“数据透视表和数据透视图向导——3 步骤之2”对话框，
，单击“下定区域”文本框中已自动填入数据清单所在的单元格区域（如图6-25 所示）
一步”按钮，打开“数据透视表和数据透视图向导——3 步骤之3”对话框，选中“现有工作表”单选钮，再单击S heet4 工作表中的A1 单元格，该单元格地址会自动填入“现
，单击“完成”按钮。
有工作表”下方的地址文本框中（如图6-26 所示）
在弹出的如图6-27 所示界面中，将字段“出版社”拖至行字段区域，将字段“客户”拖至列字段区域，将字段“订数”拖至数据项区域，结果如图6-28 所示。
保存文件后退出。
前言128
图6-23 选择“数据透视表和数据透视图”命令
图6-24 “数据透视表和数据透视图向导——3 步骤之1”对话框
图6-25 “数据透视表和数据透视图向导——3 步骤之2”对话框
前言129 图6-26 “数据透视表和数据透视图向导——3 步骤之3”对话框
图6-27 “数据透视表”布局
前言130
表
典型试题2
打开素材库中的“典型试题6-2.x ls”文件，按下面的操作要求进行操作，并把操作
前言131 结果存盘。
注意：在做题时，不得将数据表进行更改。
一、操作要求
1. 在Sh e et5的A1单元格中设置为只能录入5位数字或文本。当录入位数错误时，提示错误原因，样式为“警告”，错误信息为“只能录入5位数字或文本”。
2. 使用时间函数，对S heet1 中用户的年龄进行计算。
要求：假设当前时间是“2008-11-15”，结合用户的出生年月，计算用户的年龄，并将其计算结果保存在“年龄”列当中。计算方法为两个时间年份之差。
3. 使用R EPLACE 函数，对S heet1 中用户的电话号码进行升级。要求：对“原电话号
码”列中的电话号码进行升级。升级方法是在区号（0571）
后面加上“8”，并将其计算结果保存在“升级电话号码”列的相应单元格中。
例如：电话号码“0571*******”升级后为“0571********”
4. 在S h eet1中，使用A ND 函数，根据“性别”及“年龄”列中的数据，判断所有用户是否为大于等于40 岁的男性，并将结果保存在“是否>=40 男性”列中。
注意：如果是，保存结果为T RUE；否则，保存结果为F ALSE。
5. 根据S he e t1中的数据，对以下条件，使用统计函数进行统计。要
求：
（1）统计性别为“男”的用户人数，将结果填入S heet2 的B2 单元格中。
（2）统计年龄为“>40”岁的用户人数，将结果填入Sheet2 的B3 单元格中。
6. 将S hee t1复制到S heet3，并对S he e t3进行高级筛选。
要求：
（1）筛选条件为：“性别”－女、“所在区域”－西湖区。
（2）将筛选结果保存在Sheet3 中。
注意：
（1）无需考虑是否删除或移动筛选条件。
（2）数据表必须顶格放置。
7. 根据S he e t1的结果，创建一个数据透视图C h art1。
要求：
（1）显示每个区域所拥有的用户数量。
（2）x 坐标设置为“所在区域”。
（3）计数项为“所在区域”。
（4）将对应的数据透视表保存在Sheet4 中。
二、解答
前言
132
步骤 1：操作方法参见“典型试题 1”中的步骤 1。
步骤 2：在 Sheet1 工作表中，选中 D2 单元格，在公式编辑栏中输入公式 “=YEAR("2008-11-15")-YEAR (C2)”，再按回车键确认，即计算出该用户的年龄（41）。 双击 D 2 单元格的填充柄，即可计算出其余用户的年龄。
说明：YEAR 函数的功能和用法，请参见上一节的相关内容。 步骤 3：选中 G2 单元格，单击插入函数按扭
，打开“插入函数”对话框，在“选 择类别”下拉列表框中选择“文本”，在“选择函数”列表框中选择“REP L ACE ”函数， 单击“确定”按钮后，弹出“函数参数”对话框，输入“Old_text ”参数为“F2”， “Start_num ”参数为“5”，“Num_cha rs ”参数为“0”，“New_te xt ”参数为“8”，如 图 6-29 所示。再按“确定”按钮，即可对第一个电话号码进行升级。此时，公式编 辑栏显示“=REPLACE (F2,5,0,"8")”。 双击 G 2 单元格的填充柄，即可对其余电话号码进行升级。
说明：REPLACE 函数的功能和用法，请参见上一节的相关内容。
图 6-29 “函数参数”对话框
步骤 4：在 H2 单元格中，输入公式“=AND(B 2="男",D2>=40)”，再按回车键确认，即
可判断该用户是否为大于等于 40 岁的男性。 双击 H 2 单元格的填充柄，即可判断其余用户是否为大于等于 40 岁的男性。 步骤 5：在 Sheet2 工作表的 B 2 单元格中，输入公式“=COUNTIF (Sheet1!B 2:B37,"男 ")”，再按回车键确认，即可统计出男性用户人数（18）。 在 S heet2 工作表的 B 3 单元格中，输入公式“=COUN T IF(Sheet1!D 2:D37,">40")”，再
前言133
按回车键确认，即可统计出大于40 岁的用户人数（14）。
步骤6：选中S heet1 工作表中的A1：H37 单元格区域，单击右键在快捷菜单中选择“复制”命令。单击S h eet3工作表中的A1 单元格，单击右键在快捷菜单中选择“粘贴”命令。
在Sheet3 工作表的空白区域创建筛选条件，如图6-30所示，在J1：K2 单元格区域中输入筛选条件。单击S heet3 工作表中A1：H37 单元格区域中的任一单元格，选择菜
“列表区域”单“数据”→“筛选”→“高级筛选”命令，打开“高级筛选”对话框，
文本框中已自动填入数据清单所在的单元格区域。将光标定位在“条件区域”文本框
“条件区域”文本框内会自动内，用鼠标拖选前面创建的筛选条件单元格区域J1：K2，
填入该区域地址（如图6-31 所示）
。再单击“确定”按钮。
注意：J1 单元格中“性”和“别”之间有空格，原因是B1 单元格中“性”和“别”之间有空格。
图6-30 创建筛选条件
图6-31 设置“条件区域”
步骤7：单击S heet1 工作表中A1：H37 单元格区域中的任一单元格，选择菜单“数
前言134
据”→“数据透视表和数据透视图”命令，打开“数据透视表和数据透视图向导——
3 步骤之1”对话框，选中“数据透视图（及数据透视表）”单选钮（如图6-32 所示），再单击
“选“下一步”按钮，打开“数据透视表和数据透视图向导——3 步骤之2”对话框，
单击“下一步”
，
定区域”文本框中已自动填入数据清单所在的单元格区域（如图6-33 所示）
按钮，打开“数据透视表和数据透视图向导——3 步骤之3”对话框，选中“现有工作表”单选钮，再单击S heet4 工作表中的A1 单元格，该单元格地址会自动填入“现有工作表”下方的地址文本框中（如图6-34 所示）
。单击“布局”按钮，在弹出的如图6-35 所示界面中，将字段“所在区域”分别拖至“行”区域和“数据”区域内，然后按“确定”按钮返回图6-34所示对话框，按“完成”按钮。这时在S h eet4 工作表前会插入一张名为Cha r t1的工作表。打开该工作表可看到根据S h eet1工作表创建的数据透视图（如图
6-36 所示），打开S heet4 工作表有如图6-37 所示的相应的数据透视表。
保存文件后退出。
图6-32 “数据透视表和数据透视图向导——3 步骤之1”对话框
图6-33 “数据透视表和数据透视图向导——3 步骤之2”对话框
图6-34 “数据透视表和数据透视图向导——3 步骤之3”对话框
图6-35 “数据透视表”布局
图
表
前言138
典型试题3
打开素材库中的“典型试题6-3.x ls”文件，按下面的操作要求进行操作，并把操作
结果存盘。
注意：在做题时，不得将数据表进行更改。
一、操作要求
1. 在Sh e et4的A1单元格中设置为只能录入5位数字或文本。当录入位数错误时，提示错误原因，样式为“警告”，错误信息为“只能录入5位数字或文本”。
2. 使用V LOOKUP 函数，对S heet1 中的“三月份销售统计表”的“产品名称”列和“产品单价”列进行填充。
要求：根据“企业销售产品清单”，使用V LOOKUP 函数，将产品名称和产品单价填充
到“三月份销售统计表”的“产品名称”列和“产品单价”列中。
3. 使用数组公式，计算S h eet1中的“三月份销售统计表”中的销售金额，并将结果填入到该表的“销售金额”列中。
计算方法：销售金额= 产品单价* 销售数量
4. 使用统计函数，根据“三月份销售统计表”中的数据，计算“分部销售业绩统计表”中的总销售额，并将结果填入该表的“总销售额”列。
5. 在S heet1 中，使用R ANK 函数，在“分部销售业绩统计”表中，根据“总销售额”
对各部门进行排名，并将结果填入到“销售排名”列中。
6. 将s heet1 中的“三月份销售统计表”复制到S he e t2中，对S heet2 进行高级筛选。要
求：
（1）筛选条件为：“销售数量”－>3、“所属部门”－市场1部、“销售金额”-
>1000。
（2）将筛选结果保存在Shee t2中。
注意：
（1）无需考虑是否删除或移动筛选条件。
（2）复制过程中，将标题项“三月份销售统计表”连同数据一同复制。
（3）数据表必须顶格放置。
7. 根据S he e t1的“三月份销售统计表”中的数据，新建一个数据透视图C hart1。要
求：
（1）该图形显示每位经办人的总销售额情况。
（2）x 坐标设置为“经办人”。
（3）数据区域设置为“销售金额”。
（4）求和项为“销售金额”。
（5）将对应的数据透视表保存在Sheet3 中。
二、解答
步骤1：操作方法参见“典型试题1”中的步骤1。
步骤2：选中S heet1 工作表中的G3 单元格，单击插入函数按扭，打开“插入函数”对话框，在“选择类别”下拉列表框中选择“查找与引用”
，在“选择函数”列表框中选择“VLO O KUP”函数，单击“确定”按钮后，弹出“函数参数”对话框。输入
“L ookup_value ”参数为“F3 ”，“T able_array ”参数为“$A$3:$B$10”，“Col_index_num”参数为“2”，
“Ra n ge_look up”参数为“FALSE”
，如图6-38 所示。再按“确定”按钮，即可查找到产品型号为“A01”的产品名称（卡特扫描枪）
。此时，公式编
辑栏显示“=V L OOKUP(F3,$A$3:$B$10,2,
F ALS E)”。
双击G3 单元格的填充柄，即可查找到其余产品型号的产品名称。
在H3 单元格中，输入公式“=VLOOK U P(F3,$A$3:$C$10,3,FALSE)”
，再按回车键确认，即可查找到产品型号为“A01”的产品单价（368）。双击H3 单元格的填充柄，即可查找到其余产品型号的产品单价。
说明：VLOOKUP 函数的功能和用法，请参见上一节的相关内容。
图6-38 “函数参数”对话框
步骤3：先选中L3:L44 单元格区域，再输入公式“=H3:H44*I3:I44”
，然后同时
按组合键S h i ft+Ctr l+Enter，此时，公式编辑栏显示“{=H3:H44*I3:I44}”
。