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 工作表中的 A 1 单元格,选择菜单“数据”→“有效性”命 令(如图 6-13 所示),在打开的“数据有效性”对话框中,选择“设置”选项卡, 再选择“允许”为“文本长度”,“数据”为“等于”,在“长度”文本框中输入“5”, 如图 6-14 所示。 选择“出错警告”选项卡,再选择“样式”为“警告”,在“错误信息”文本框 中输入“只能录入 5 位数字或文本”,如图 6-15 所示,单击“确定”按钮。
图 6-13 选择“有效性”命令
图6-14 “设置”选项卡
图6-15 “出错警告”选项卡
步骤2:在Sheet1 工作表中,先选中I3:I52单元格区域,再输入公式
“=G 3:G 52*H 3:H
,然后同时按组合键 Sh i ft+Ctr l +Enter ,此时,公式编辑栏显示
“{=G 3:G 52*H 3:H 5。 说明:公式中单元格或单元格区域也可用鼠标选择输入,下同。 步
骤 3:选中 L 2 单元格,单击插入函数按扭,打开“插入函数”对话框,在 “选择
图 6-16 “插入函数”对话框
图 6-17 “函数参数”对话框
选 中 L3 单 元 格 , 在 公 式 编 辑 栏 中 输 入
公 式 “=C O U N T I F (G 3:G 52说明:COUN T IF 函数的功能和用法,请参见上一节的相关容。 步骤
4:选中 L8 单元格,单击插入函数按扭,打开“插入函数”对话框,在 “选择类别”下拉列表框中选择“数学与三角,在“选择函数”列表框中选择 “SUMIF ”函数。单击“确定”按钮,弹出“函数参数”对话框,6-18 所示。再按“确定”按钮,即可计算出 c1 用户所订购图书需支付的金额总数 (7213。此时,公式编辑栏显示“
图 6-18 “函数参数”对话框
在
L 9 单元格中输
入公式“=S U M I F (A 3:A 5可计算出 c 2 用户所订购图书需支付的金额总在 L 10 单元格中输入公式“=S U
M I F 可计算出 c 3 用户所订购图书需支付的金在
L 11 单元格中输入公式
“
=S U M 可计算出 c 4 用户所订购图书需支付说明:SUMIF 函数的功能和用法,请参见上一节的相关容。
步骤 5:在 Sheet2 工作表中,选择 B2 单元格,在公式编辑栏中输入公式 “=IF(OR(AND(M OD(A2,4)=0,MOD(A 2,100)<>0),MOD (A 2,400)=0),"闰年","平年")”, 按回车键确认,即可判断出该年双击 B 2 单元格的填充柄说明:IF 、OR 、AND 、MOD 函数的功能和用法,请参见上一节的相关容。 步骤 6:选中 S heet1 工作表中的 A1:I52 单元格区域,单击右键在快捷菜单中选 择“复制”命令。单击 S h eet3 工作表中的 A1 单元格,单击右键在快捷菜单中选择“粘 贴”命令。 在
Sheet3 工作表的空白区域创建筛选条件,如图 6-19 所示,在 K2:L3 单元格 区域中输入筛选条件。单击 Sheet3 工作表中 A1:I 52 单元格区域中的任一单元格, 选择菜单“数据
格区域 K 2:“
条件区域”文本框会自动填
入该区域地址(如图 6-22 。再
单击“确定”按钮。
图 6-19 创建筛选条件
图 6-20 选择“高级筛选”命令
图6-21 设置“列表区域”图6-22 设置“条件区域”
步骤7:单击S heet1 工作表中A1:I52 单元格区域中的任一单元格,选择菜单“数据”→“
数
据
透
视表和数据透视图”命令(如图在弹出的如图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.x ls”文件,按下面的操作要求进行操作,并把操作
结果存盘。
注意:在做题时,不得将数据表进行更改。
一、操作要求
1. 在Sh e et5的A1单元格中设置为只能录入5位数字或文本。当录入位数错误时,提示错误原因,样式为“警告”,错误信息为“只能录入5位数字或文本”。
2. 使用时间函数,对S heet1 中用户的年龄进行计算。
要求:假设当前时间是“2008-11-15”,结合用户的出生年月,计算用户的年龄,并将其计算结果保存在“年龄”列当中。计算方法为两个时间年份之差。
3. 使用R EPLACE 函数,对S heet1 中用户的进行升级。要求:对“原”列中的进行升级。
升级方法是在区号(0571)
后面加上“8”,并将其计算结果保存在“升级”列的相应单元格中。
例如:“”升级后为“8”
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 中。
二、解答
步骤 1:操作方法参见“典型试题 1”中的步骤 1。
步骤 2:在 Sheet1 工作表中,选中 D2 单元格,在公式编辑栏中输入公式
“=Y E A R ("2008-11-15")
-Y E A R (C ,再按回车键说明:YEAR 函数的功能和用法,请参见上一节的相关容。 步骤 3:
选中 G 2 单元格,单击插入函数按扭,打
开“插入函数”对话框,在“选 双击 G 2 单元格的填充柄,即可对其余进行升级。 说明:REPLACE 函数的功能和用法,请参见上一节的相关容。
图 6-29 “函数参数”对话框 步骤 4:在 H 2 单元格中可判断该用户是否为大于等于 40 岁的男性。 双击 H 2 单元格的填充柄,即可判断其余用户是否为大于等于 40 岁的男性。 步骤 5:在 Sheet2 工作表的 B 2 单元格中,输入公式“=COUNTIF (Sheet1!B 2:B37,"男 ,再按回车键确认在 S h e e t
按
回车键确
认,即
可
统
计出大于 40 岁的用户人数(。 步骤 6:选中 S heet1 工作表中的 A 1:H37 单元格区域,单击右键在快捷菜单中选 择“复制”命令。单击 S h eet3 工作表中的 A 1 单元格,单击右键在快捷菜单中选择“粘 贴”命令。 在 Sheet3 工作表的空白区域创建筛选条件,如图 6-30 所示,在 J1:K2 单元格区域 中输入筛选条件。单击 S heet3 工作表中 A1:H37 单元格区域中的任一单元格,选择 菜单“数据”→“筛
选”→“高级筛选”命令,打开“高
级筛选”对
“列表区 域”文本框中已自动填入数据清单所在的单元格区域。将光标定位在“条件区域注意:J1 单元格中“性”和“别”之间有空格,原因是 B1 单元格中“性”和“别” 之间有空格。 图 6-30 创建筛选条件
图 6-31 设置“条件区域” 步骤 7:单击 S heet1 工作表中 A 1:H37 单元格区域中的任一单元格,选择菜单“数
据”→“数据透视表和数据透视图”命令,打开“数据透视表和数据透视图向导——3
步
骤
之
1”对话框,选中“数据透视图(及数据透”单选钮(如图
6 -3保存文件后退出。
图6-32 “数据透视表和数据透视图向导——3 步骤之1”对话框
图6-33 “数据透视表和数据透视图向导——3 步骤之2”对话框
图6-34 “数据透视表和数据透视图向导——3 步骤之3”对话框
图6-35 “数据透视表”布局
图6-36 数据透视
图
图6-37 数据透视
表
典型试题 3
打开素材库中的“典型试题 6-3.x ls ”文件,按下面的操作要求进行操作,并把操作 结果存盘。
注意:在做题时,不得将数据表进行更改。
一、操作要求
1. 在Sh e et4的A1单元格中设置为只能录入5位数字或文本。当录入位数错误时,提示 错误原因,样式为“警告”,错误信息为“只能录入5位数字或文本”。
2
.
使用
V
L
O
O
K
U
P
对 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 工作表中的 G 3 单元格,单击插入函数按扭,打开“插入函数” 对话框,在“选择类别
”下拉列表框中选择“查找与,在“选择函数”列表框 中选择“VLO O KUP ”函数,单击“确定”按钮后,弹出“函数参数”对话框。输入 “ L ookup_value ” 参 数为 “ F 3 ”,“ T able_array ” 参 数为 “ $A
$
3:$B $10 ”,
双击 G 3 单元格的填充柄,即可查找到其余产品型号的产品名称。 在 H 3 单元格中,输入公式“=V L O O K U P (F 3,$说明:VLOOKUP 函数的功能和用法,请参见上一节的相关容。 图 6-38 “函数参数”对话框 步骤 3:先选中 L 3按组合键 S h i f