目录
目录
Excel 习题指南 (2)
第01 套三科成绩 (2)
第02 套服装采购 (4)
第03 套教材订购 (8)
第04 套电话升级 (11)
第05 套灯泡采购 (13)
第06 套房产销售 (15)
第07 套公务员考试 (18)
第08 套员工信息 (20)
第09 套停车记录 (23)
第10 套温度情况 (26)
第11 套学生成绩 (28)
第 12 套销售统计 (30)
第13 套等级考试 (32)
第14 套通讯费计划表 (35)
第15 套医院病人护理 (37)
第16 套图书订购信息 (40)
第17 套学生体育成绩 (42)
第18 套员工资料表 (45)
第19 套公司员工信息表 (48)
第20 套优等生 (50)
第21 套零件检测 (53)
Excel 习题指南
本文针对 AOA 练习系统中的 21 套 Excel 题目进行分析，并给出参考答案。
第1 套三科成绩
在考生文件夹的Excel子文件夹中，已有Excel.xlsx文件。按下列要求操作，并将结果存
盘。
1. 在Sheet1的A50 单元格中输入分数1/3。
输入：0 1/3
2. 在Sheet1中使用函数计算全部语文成绩中奇数的个数，结果存放在 B50 单元格中。
答案：{=SUM(MOD($C$2:$C$39,2))}
说明：奇数除以 2 的余数为 1，偶数除以 2 的余数为 0，余数的总和即为奇数的个数。这里须用数组公式，否则出错，公式中的“{ }”不能输入，须用 Ctrl+Shift+Enter 组合健产生。
3.使用数组公式， Sheet1 计算总分和平均分，对将其计算结果保存到表中的“总分”“平列和均分”列当中。
总分：
先选中 F2:F39，输入 =C2:C39+D2:D39+E2:E39，再按 Ctrl+Shift+Enter
平均分：
先选中 G2:G39，输入 =F2:F39/3，再按 Ctrl+Shift+Enter
注意：须用数组公式，否则不得分。按 Ctrl+Shift+Enter 组合健后，公式自动用“{ }”括起。
4.使用 RANK 函数，对 Sheet1 中的每个同学排名情况进行统计，并将排名结果保存到表中的“排名”列当中。
排名：=RANK(F2,$F$2:$F$39)
然后利用填充柄复制公式。
5.使用逻辑函数判断 Sheet1 中每个同学的每门功课是否均高于平均分，如果是，保存结果为TRUE，否则，保存结果为 FALSE，将结果保存在表中的“三科成绩是否均超过平均”列当中。
公式为：
=IF(AND(C2>AVERAGE($C$2:$C$39),D2>AVERAGE($D$2:$D$39),E2>AVERAGE($E$2:$E$39)
),TRUE,FALSE)
或者：
=IF(C2>AVERAGE($C$2:$C$39),IF(D2>AVERAGE($D$2:$D$39),IF(E2>AVERAGE($E$2:$E$3 9),TRUE,FALSE),FALSE),FALSE)
6.根据 Sheet1 中的结果，使用统计函数，统计“数学”考试成绩各个分数段的同学人数，将
统计结果保存到 Sheet2 中的相应位置。
数学分数位于 0 到 20 分的人数：
=COUNTIF(Sheet1!$D$2:$D$39,"<20")
数学分数位于 20 到 40 分的人数：
=COUNTIF(Sheet1!$D$2:$D$39,"<40")-COUNTIF(Sheet1!$D$2:$D$39,"<20")
数学分数位于 40 到 60 分的人数：
=COUNTIF(Sheet1!$D$2:$D$39,"<60")-COUNTIF(Sheet1!$D$2:$D$39,"<40")
数学分数位于 60 到 80 分的人数：
=COUNTIF(Sheet1!$D$2:$D$39,"<80")-COUNTIF(Sheet1!$D$2:$D$39,"<60")
数学分数位于 80 到 100 分的人数：
=COUNTIF(Sheet1!$D$2:$D$39,"<=100")-COUNTIF(Sheet1!$D$2:$D$39,"<80")
或
=COUNTIF(Sheet1!$D$2:$D$39,">=80")
7.将 Sheet1 复制到 Sheet3 中，并对 Sheet3 进行高级筛选，要求：
◆ 筛选条件：“语文”>＝75，“数学”>＝75，“英语”>＝75，“总分”>＝250；
◆ 将结果保存在 Sheet3 中。
先复制表格，再在 Sheet3 中设置高级筛选所需的条件区域：
（注：“>=”要在英文状态下输入）
然后点击[数据]→[筛选]→[高级]，在对话框选好列表区域和条件区域，
根据条件区域设
置，不一定相同
得到筛选结果：
学号姓名语文数学英语总分平均排名三科成绩是否均超过平均
20041004 20041005 20041010 20041012 20041018 20041019 20041026 20041032 20041033 20041038陆东兵
闻亚东
周旻璐
吕秀杰
程俊
黄威
万基莹
赵援
罗颖
张立娜
94
84
94
81
94
82
81
94
84
94
90
87
87
83
89
87
83
90
87
82
91
88
82
87
91
88
89
88
83
82
275
259
263
251
274
257
253
272
254
258
91.67
86.33
87.67
83.67
91.33
85.67
84.33
90.67
84.67
86.00
1
5
4
10
2
7
9
3
8
6
TRUE
TRUE
TRUE
TRUE
TRUE
TRUE
TRUE
TRUE
TRUE
TRUE
8.根据 Sheet1 中的结果，在 Sheet4 中创建一张数据透视表，要求：
◆ 显示是否三科均超过平均分的学生人数；
◆ 行区域设置为：“三科成绩是否均超过平均”；
◆ 计数项为三科成绩是否均超过平均。
鼠标先点在 Sheet1 的成绩表上，再选[插入]→[数据透视表]，在对话框中选“现有工作表”，如下图：
然后在布局窗口拖动字段“三科成绩是否均超过平均”至“行标签”和“数值”区，如下图：
拖动
完成后的数据透视表如下所示：
行标签FALSE TRUE 总计计数项:三科成绩是否均超过平均
27
11
38
第2 套服装采购
在考生文件夹的Excel子文件夹中，已有Excel.xlsx文件。按下列要求操作，并将结果存
盘。
1．在Sheet5中，使用函数，将A1单元格中的数四舍五入到整百，存放在B1单元格中。
答案：=ROUND(A1,-2)
说明：第二个参数表示圆整的位数，表示小数点后2位，“2”“-2”表示小数点前2位（即百位）。2．在Sheet1中，使用条件格式将“采购数量”列中数量大于100的单元格中字体颜色设置为
红色、加粗显示。
先选中“采购数量”这列的单元格，再点击[开始]→[条件格式]→[新建规则]，跳出如下对话
框进行相应设置：
选择
设置
3.使用 VLOOKUP 函数，对 Sheet1 中的商品单价进行自动填充。
◆ 要求：根据“价格表”中的商品单价，利用 VLOOKUP 函数，将其单价自动填充到采购表
中的“单价”列中。
公式为：
=VLOOKUP(A11,$F$2:$G$5,2,0)
4.使用逻辑函数，对 Sheet1 中的商品折扣率进行自动填充。
◆ 要求：根据“折扣表”中的商品折扣率，利用相应的函数，将其折扣率自动填充到采购
表中的“折扣“列中。
公式为：
=IF(B11>=A$6,B$6,IF(B11>=A$5,B$5,IF(B11>=A$4,B$4,B$3)))
或者：
=IF(B11<$A$4,$B$3,IF(B11<$A$5,$B$4,IF(B11<$A$6,$B$5,$B$6)))
注：嵌套的 IF 函数，判断时一般都用“>”或者都用“<”。
5.利用公式，计算 Sheet1 中的“合计金额”。
◆ 要求：根据“采购数量”，“单价”和“折扣”，计算采购的“合计金额”。
◆ 计算公式：单价*采购数量*（1-折扣）
公式为：
=D11*B11*(1-E11)
注意：使用公式“=B11*D11*(1-E11)”理论上也对，但系统不给分。
6.使用 SUMIF 函数，计算各种商品的采购总量和采购总金额，将结果保存在 Sheet1 中的“统计表”当中相应位置。
在 J12 单元格填入采购总量：
=SUMIF(A$11:A$43,I12,B$11:B$43)
然后用填充柄填充至 J13、J14。
在 K12 单元格填入采购总金额：
=SUMIF(A$11:A$43,I12,F$11:F$43)
然后用填充柄填充至 K13、K14。
7. 将 Sheet1 中的“采购表”复制到 Sheet2 中，并对 Sheet2 进行高级筛选。要求：
◆ 筛选条件为：“采购数量”>150，“折扣”>0；
◆ 将筛选结果保存在 Sheet2 中。
先复制“采购表”至 Sheet2，其中“单价”和“折扣”两列数据出错，需重新复制、粘贴（用“值”粘贴）。
再设置条件区域如下：
然后用高级筛选设置好列表区域和条件区域，得如下结果：
项目采购数量采购表
采购时间单价折扣合计
裤子衣服裤子鞋子衣服裤子鞋子鞋子衣服衣服裤子衣服裤子鞋子衣服裤子衣服裤子鞋子衣服裤子鞋子185
225
210
260
385
350
315
340
265
320
400
385
275
240
360
325
295
155
210
395
160
275
2008/2/5
2008/3/14
2008/3/14
2008/3/14
2008/4/30
2008/4/30
2008/4/30
2008/5/15
2008/6/24
2008/7/10
2008/7/10
2008/8/19
2008/8/19
2008/8/19
2008/9/27
2008/9/27
2008/10/24
2008/10/24
2008/10/24
2008/11/4
2008/11/4
2008/11/4
80
120
80
150
120
80
150
150
120
120
80
120
80
150
120
80
120
80
150
120
80
150
0.06
0.08
0.08
0.08
0.1
0.1
0.1
0.1
0.08
0.1
0.1
0.1
0.08
0.08
0.1
0.1
0.08
0.06
0.08
0.1
0.06
0.08
13,912.00
24,840.00
15,456.00
35,880.00
41,580.00
25,200.00
42,525.00
45,900.00
29,256.00
34,560.00
28,800.00
41,580.00
20,240.00
33,120.00
38,880.00
23,400.00
32,568.00
11,656.00
28,980.00
42,660.00
12,032.00
37,950.00
8.根据 Sheet1 中的采购表，新建一个数据透视图 Chart1，要求：
◆ 该图形显示每个采购时间点所采购的所有项目数量汇总情况；
◆ x 坐标设置为“采购时间”；
◆ 求和项为采购数量；
◆ 将对应的数据透视表保存在 Sheet3 中。
鼠标先点在 Sheet1 的成绩表上，再选[插入]→[数据透视图]，在对话框中选“现有工作表”，如下图：
在布局窗口，拖动字段“采购时间”至“行标签”、“采购数量”至“数值”区，如下图所示：
拖动
点中产生的图表，[鼠标右键]→[移动图表]，出现下图窗口，选“新工作表”：
Chart1 中产生的透视图如下所示：
汇总
1200
1000
800
600
400
汇总
200
同时，在 Sheet3 中产生了相应的数据透视表，如下所示：
行标签求和项:采购数量
2008/1/12135
2008/2/5450
2008/3/14695
2008/4/301050
2008/5/15485
2008/6/24490
2008/7/10845
2008/8/19900
2008/9/27805
2008/10/24660
2008/11/4830
总计7345
第3 套教材订购
在考生文件夹的Excel子文件夹中，已有Excel.xlsx文件。按下列要求操作，并将结果
存盘。
1.在Sheet5的A1单元格中设置为只能录入5位数字或文本。当录入位数错误时，提示错误原因，样式为“警告”，错误信息为“只能录入5位数字或文本”。
选定Sheet5的A1，点击[数据]→[数据有效性]，打开对话框进行设置：
2.在Sheet5的B1单元格中输入分数1/3
输入：0 1/3
3. 使用数组公式，对 Sheet1 中“教材订购情况表”的订购金额进行计算。
◆ 将结果保存在该表的“金额”列当中。
◆ 计算方法为：金额=订数*单价。
先选中 I3:I52，输入 =G3:G52*H3:H52，再按 Ctrl+Shift+Enter
注意：按 Ctrl+Shift+Enter 组合健后，公式自动用“{ }”括起。
4. 使用统计函数，对 Sheet1 中“教材订购情况表”的结果按以下条件进行统计，并将结果保存在 Sheet1 中的相应位置。要求：
◆◆统计出版社名称为“高等教育出版社”的书的种类数，并将结果保存在 Sheet1 中 L2 单元格中；
统计订购数量大于 110 且小于 850 的书的种类数，并将结果保存在 Sheet1 中 L3 单元格中。
L2 单元格：
=COUNTIF(D3:D52,"高等教育出版社")
L3 单元格：
=COUNTIF(G3:G52,">110")-COUNTIF(G3:G52,">850")
5. 使用函数，计算每个用户所订购图书所需支付的金额，并将结果保存在 Sheet1 中“用户支付情况表”的“支付总额”列中。
在单元格 L8 中输入公式：
=SUMIF(A$3:A$52,K8,I$3:I$52)
然后填充至 L11。
6. 使用函数，判断 Sheet2 中的年份是否为闰年，如果是，结果保存“闰年”；如果不是，则结果保存“平年”，并将结果保存在“是否为闰年”列中。
◆ 闰年定义：年数能被 4 整除而不能被 100 整除，或者能被 400 整除的年份。
公式为：
=IF(MOD(A2,400)=0,"闰年",IF(MOD(A2,4)<>0,"平年",IF(MOD(A2,100)<>0,"闰年","平年")))
或者：
=IF(OR(MOD(A4,400)=0,AND(MOD(A4,4)=0,MOD(A4,100)<>0)),"闰年","平年")
7. 将 Sheet1 中的“教材订购情况表”复制到 Sheet3 中，对 Sheet3 进行高级筛选。要求
◆ 筛选条件为“订数>=500，且金额<=30000”；
◆ 将结果保存在 Sheet3 中。
先复制表格，再在 Sheet3 中设置筛选所需的条件区域：
用高级筛选置好列表区域和条件区域，得到如下结果：
客户ISSN教材名称出版社版次作者订数单价金额
c1 c1 c1 c1 c1 c1 c1 c3 c4 c47-03-027426-7大学信息技术基础二版科学出版社胡同森
7-03-012346-8化工原理(上）科学出版社一版何潮洪冯霄
7-121-02828-9数字电路电子工业出版社一版贾立新
7-04-021908-1复变函数高等教育出版社四版西安交大
7-04-001245-2大学文科高等数学 1一版高等教育出版社姚孟臣
7-81080-159-7大学英语上海外语教育出版社谌馨蒸快读 2 修订
7-5341-1523-4C程序设计基础浙江科学技术出版社胡同森一版
7-5303-8878-0国际贸易中国金融出版社05版刘诚
7-402-15710-6新编统计学原理立信会计二版唐庆银
7-04-113245-8经济法（含学习卡）二版高等教育曲振涛
1249
767
555
540
518
500
500
645
637
589
18
38
34
29
26
28
30
35
32
35
22482
29146
18870
15660
13468
14000
15000
22575
20384
20615
8. 根据 Sheet1 中“教材订购情况表”的结果，在 Sheet4 中新建一张数据透视表。要求：显示每个客户在每个出版社所订的教材数目；
◆◆◆◆行区域设置为“出版社”；列区域设置为“客户”；求和项为订数；
数据区域设置为“订数”。
鼠标先点在 Sheet1 的表上，再选[插入]→[数据透视表]，“现有工作表”“Sheet4!$A$1”选为，进入以下数据布局窗口：
拖动
在 Sheet4 上得到数据透视表，如下所示：
求和项:订数
行标签
北京航大
北京理工
电子工业出版社
东北财经大学出版社复旦大学
高等教育
高等教育出版社
高教
华东师大
科学
科学出版社
立信会计
立信会计出版社
辽宁美术出版社
南京大学
清华大学
人民大学
人民卫生
上海外语教育出版社天津人民美术出版社外语教学与研究出版社浙江科技出版社
浙江科学技术
浙江科学技术出版社中国金融
中国金融出版社
中国人大
中国人民大学出版社中国物资
总计列标签
c1c2c3
63
421
555
106
1061
10719
509
76
203
2940
637
80
58
240
120
721
366
500
58
9855
1504
106
500
160
645
224
585167
109
27158 1965 1968 2343
71
75
c4总计
63
421
626
75
106
1061
10719
509
76
203
2940
637
80
58
240
120
721
366
500
58
9855
1504
106
500
160
645
224
752
109
33434
第4 套电话升级
在考生文件夹的Excel子文件夹中，已有Excel.xlsx文件。按下列要求操作，并将结果存盘。
1.在Sheet5的A1单元格中设置为只能录入5位数字或文本。当录入位数错误时，提示错误原因，样式为“警告”，错误信息为“只能录入5位数字或文本”。
选定Sheet5的A1，点击[数据]→[数据有效性]，打开对话框进行设置：
2.在Sheet5的B1单元格中输入公式，判断当前年份是否为闰年，结果为TRUE或FALSE。
◆ 闰年定义：年数能被4整除而不能被100整除，或者能被400整除的年份。
输入公式：
=IF(OR(AND(MOD(YEAR(NOW()),4)=0,MOD(YEAR(NOW()),100)<>0),MOD(YEAR(NOW()),400
)=0),TRUE,FALSE)
注：当前年份用YEAR(NOW())表示，也可要YEAR(TODAY())。
3.使用时间函数，对 Sheet1 中用户的年龄进行计算。要求：
◆ 使用当前时间，结合用户的出生年月，计算用户的年龄，并将其计算结果保存在“年龄”
列当中。计算方法为两个时间年份之差。
公式为：
=YEAR(NOW())-YEAR(C2)
或者：
=YEAR(TODAY())-YEAR(C2)
4.使用 REPLACE 函数，对 Sheet1 中用户的电话号码进行升级。要求：
◆ 对“原电话号码”列中的电话号码进行升级。升级方法是在区号（0571）后面加上“8”，
并将其计算结果保存在“升级电话号码”列的相应单元格中。
◆ 例如：电话号码“0571*******”升级后为“0571********”
公式为：
=REPLACE(F2,5,0,"8")
说明：从电话号码的第 5 位起选 0 位，指在第 5 位前插入。
5. 在 Sheet1 中，使用 AND 函数，根据“性别”及“年龄”列中的数据，判断所有用户是否
为大于等于 40 岁的男性，并将结果保存在“是否>=40 男性”列中。
◆ 注意：如果是，保存结果为 TRUE；否则，保存结果为 FALSE。
公式为：
=IF(AND(B2="男",D2>=40),TRUE,FALSE)
6.根据 Sheet1 中的数据，对以下条件，使用统计函数进行统计。要求：
◆ 统计性别为“男”的用户人数，将结果填入 Sheet2 的 B2 单元格中；
◆ 统计年龄为“>40”岁的用户人数，将结果填入 Sheet2 的 B3 单元格中。
“男”的用户人数：
=COUNTIF(Sheet1!B2:B37,"男")
“>40”岁的用户人数：
=COUNTIF(Sheet1!D2:D37,">40")
7. 将 Sheet1 复制到 Sheet3，并对 Sheet3 进行高级筛选。要求：
◆ 筛选条件为：“性别”－女，“所在区域”－西湖区；
◆ 将筛选结果保存在 Sheet3 中。
先将表格从 Sheet1 复制到 Sheet3，再设置筛选所需的条件区域为：
注意：“性别”中间有空格。
应用高级筛选后的结果为：
姓名性别出生年月年龄所在区域原电话号码升级后号码是否>=40男性
韩九许九叶五郁九女
女
女
女
1973/4/17
1972/9/1
1970/7/19
1967/4/5
40
41
43
46
西湖区
西湖区
西湖区
西湖区
0571*******
0571*******
0571*******
0571*******
0571********
0571********
0571********
0571********
FALSE
FALSE
FALSE
FALSE
8.◆◆◆
◆根据 Sheet1 的结果，创建一个数据透视图 Chart1。要求：显示每个区域所拥有的用户数量；
x 坐标设置为“所在区域”；
计数项为“所在区域”；
将对应的数据透视表保存在 Sheet4 中。
鼠标先点在 Sheet1 的表上，再选[插入]→[数据透视图]，“现有工作表”“Sheet4!$A$1”选为；进入数据布局窗口后，把字段“所在区域”分别拖入行标签和数值区，如下图：
点中产生的图表，[鼠标右键]→[移动图表]，出现窗口，选“新工作表”为“Chart1”。即在 Chart1 上产生如下透视图：
汇总
8
7
6
5
4
3
2
1
拱墅区江干区上城区西湖区下城区余杭区
汇总
同时，在 Sheet4 上产生的数据透视表如下：
行标签拱墅区江干区上城区西湖区下城区余杭区总计计数项:所在区域
6
6
5
6
6
7
36
第5 套灯泡采购
在考生文件夹的Excel子文件夹中，已有Excel.xlsx文件。按下列要求操作，并将结果存盘。
1.在Sheet1的B30 单元格中输入分数1/3 。
输入：0 1/3
2.在Sheet1中设定第31 行中不能输入重复的数值。
先选中31行，点击[数据]→[数据有效性]，打开对话框，输入自定义公式：
说明：在Excel中第31行用“31:31”表示；在选中第31行时，单元格A31被激活，公式
“=COUNTIF(31:31,A31)=1 ”实际是输入在A31 上的，所以在B31 会自动复制成“=COUNTIF(31:31,B31)=1”，以此类推，各单元格的值只能出现一次即不能重复。
3.使用数组公式，计算 Sheet1 中的每种产品的价值，将结果保存到表中的“价值”列中。
◆ 计算价值的计算方法为：“单价*每盒数量*采购盒数”。
数组公式为： {=E2:E17*F2:F17*G2:G17}
说明：先选中 H2:H17，再输入公式，结束按 Ctrl+Shift+Enter。
4.在 Sheet2 中，利用数据库函数及已设置的条件区域，计算以下情况的结果，并将结果保存
相应的单元格中。
a. 计算：商标为上海，瓦数小于 100 的白炽灯的平均单价；
=DAVERAGE(A1:H17,E1,J2:L3)
b. 计算：产品为白炽灯，其瓦数大于等于 80 且小于等于 100 的数量。
=DSUM(A1:H17,G1,J7:L8)
5.某公司对各个部门员工吸烟情况进行统计，作为人力资源搭配的一个数据依据。对于调查
对象，只能回答 Y（吸烟）或者 N（不吸烟）。根据调查情况，制做出 Sheet3。请使用函数，
统计符合以下条件的数值。
a.统计未登记的部门个数；
=COUNTBLANK(B2:E11)
b.统计在登记的部门中，吸烟的部门个数。
=COUNTIF(B2:E11,"Y")
6.使用函数，对 Sheet3 中的 B21 单元格中的内容进行判断，判断其是否问文本，如果是，结
果为“TRUE”；如果不是，结果为“FALSE”，并将结果保存在 Sheet3 中的 B22 单元格当中。
在 B22 单元格写入公式：
=ISTEXT(B21)
7.将 Sheet1 复制到 Sheet4 中，对 Sheet4 进行高级筛选，要求：
◆ 筛选条件：“产品为白炽灯，商标为上海”，并将结果保存；
◆ 将结果保存在 Sheet4 中。
先将表格从 Sheet1 复制到 Sheet4，再设置筛选所需的条件区域为：
点击[数据]→[筛选]→[高级]，经高级筛选后的结果如下：
产品瓦数寿命（小时）商标单价每盒数量采购盒数价值
白炽灯白炽灯白炽灯白炽灯白炽灯200
80
10
100
40
3000
1000
800
2000
1000
上海
上海
上海
上海
上海
4.50
0.20
0.20
0.80
0.10
4
40
25
10
20
3
3
2
5
5
54.00
24.00
10.00
40.00
10.00
8.根据 Sheet1 的结果，在 Sheet5 中创建一张数据透视表，要求：
◆ 显示不同商标的不同产品的采购数量；
◆ 行区域设置为“产品”；
◆ 列区域设置为“商标”；
◆ 计数项为“采购盒数”。
鼠标先点在 Sheet1 的表上，再选[插入]→[数据透视表]，“现有工作表”“Sheet5!$A$1”选为；进入数据布局窗口后，把字段“产品”拖入行标签、“商标”拖入列标签、“采购盒数”拖入
数值区，如下图：
在 Sheet5 上产生的数据透视表如下所示：
计数项:采购盒数行标签
白炽灯
氖管
其他
日光灯
总计列标签
北京上海 (空白) 总计
459
112
22
22
7815
第6 套房产销售
在考生文件夹的Excel子文件夹中，已有Excel.xlsx文件。按下列要求操作，并将结果存盘。
1.在Sheet5的A1单元格中设置为只能录入5位数字或文本。当录入位数错误时，提示错误原因，
样式为“警告”，错误信息为“只能录入5位数字或文本”。
选定Sheet5的A1，点击[数据]→[数据有效性]，打开对话框进行设置：
2.在Sheet1中，使用条件格式将“预定日期”列中日期为2008-4-1后的单元格中字体颜色设置
为红色、加粗显示。
先选中“预定日期”这列的单元格，再点击[开始]→[条件格式]→[新建规则]，跳出如下对话框进行相应设置：
3. 使用公式，计算 Sheet1 中“房产销售表”的房价总额，并保存在“房产总额”列中。
◆ 计算公式为：房价总额 = 面积 * 单价。
公式为： =F3*G3
4.使用数组公式，计算 Sheet1 中“房产销售表”的契税总额，并保存在“契税总额”列中。
◆ 计算公式为：契税总额 = 契税 * 房价总额。
数组公式为： {=H3:H26*I3:I26}
先选中 J3:J26，再输入公式，结束按 Ctrl+Shift+Enter。
5.使用函数，根据 Sheet1 中“房产销售表”的结果，在 Sheet2 中统计每个销售人员的销售总额，将结果保存在 Sheet2 中的“销售总额”列中。
“人员甲”的销售总额：
=SUMIF(Sheet1!K$3:K$26,A2,Sheet1!I$3:I$26)
其它人员的销售总额可用填充柄复制公式求得。
注意：由于销售总额数值较大，应拉大 B 列的宽度，使得数据全部显示出来，否则扣分。
6.使用 RANK 函数，根据 Sheet2 中“销售总额”列的结果，对每个销售人员的销售情况进行排序，并将结果保存在“排名”列当中。
“人员甲”的排名：
=RANK(B2,$B$2:$B$6)
然后利用填充柄复制公式。
7.将 Sheet1 中“房产销售表”复制到 Sheet3 中，并对 Sheet3 进行高级筛选。要求：
◆ 筛选条件为：“户型”为两室一厅，“房价总额”>1000000；
◆ 将结果保存在 Sheet3 中。
先将表格从 Sheet1 复制到 Sheet3，再设置条件区域为：
户型房价总额
两室一厅 >1000000
点击[数据]→[筛选]→[高级]，高级筛选后的结果如下：
姓名联系电话预定日期楼号
房产销售表
户型面积单价契税房价总额契税总额销售人员
客户7客户9客户11客户13客户15客户17客户19客户21客户2313557112364
135********
135********
135********
135********
135********
135********
135********
135********
2008/5/6 5-401 两室一厅
2008/4/19 5-501 两室一厅
2008/2/26 5-601 两室一厅
2008/9/25 5-701 两室一厅
2008/9/16 5-801 两室一厅
2008/5/6 5-901 两室一厅
2008/7/26 5-1001 两室一厅
2008/7/23 5-1101 两室一厅
2008/4/6 5-1201 两室一厅
125.12
125.12
125.12
125.12
125.12
125.12
125.12
125.12
125.12
8023
8621
8925
9358
9624
9950
11235
13658
14521
1.50%
1.50%
1.50%
1.50%
1.50%
1.50%
1.50%
1.50%
1.50%
1003837.76
1078659.52
1116696.00
1170872.96
1204154.88
1244944.00
1405723.20
1708888.96
1816867.52
15057.57
16179.89
16750.44
17563.09
18062.32
18674.16
21085.85
25633.33
27253.01
人员戊
人员乙
人员丙
人员乙
人员乙
人员甲
人员戊
人员丙
人员丙
8.根据 Sheet1 中“房产销售表”的结果，创建一个数据透视图 Chart1。要求：
◆ 显示每个销售人员所销售房屋应缴纳契税总额混总情况；
◆ x 坐标设置为“销售人员”；
◆ 数据区域为“契税总额”；
◆ 求和项设置为契税总额；
◆ 将对应的数据透视表保存在 Sheet4 中。
鼠标先点在 Sheet1 的表上，再选[插入]→[数据透视图]，“现有工作表”“Sheet4!$A$1”选为；进入数据布局窗口后，把字段“销售人员”拖入行标签、“契税总额”拖入数值区，如下图：
点中产生的图表，[鼠标右键]→[移动图表]，出现窗口，选“新工作表”为“Chart1”。
即在 Chart1 上产生如下透视图：
汇总
300000
250000
200000
150000
100000
50000
人员丙人员丁人员甲人员戊人员乙
汇总与此同时，在 Sheet4 上产生的数据透视表如下：
行标签人员丙人员丁人员甲人员戊人员乙总计求和项:契税总额 199857.4008
59564.1012
244122.8748
147790.5024
86253.5637 737588.4429
第7 套公务员考试
在考生文件夹的Excel子文件夹中，已有Excel.xlsx文件。按下列要求操作，并将结果存盘。
1.在Sheet1的A30 单元格中输入分数2/3。
输入：0 2/3
2.在Sheet1中，设定第31 行中不能输入重复的数值。
先选中31行，点击[数据]→[数据有效性]，打开对话框，输入自定义公式：
说明：在Excel中第31行用“31:31”表示；在选中第31行时，单元格A31被激活，公式
“=COUNTIF(31:31,A31)=1 ”实际是输入在A31 上的，所以在B31 会自动复制成“=COUNTIF(31:31,B31)=1”，依此类推，各单元格的值只能出现一次即不能重复。
3.使用 IF 函数，对 Sheet1 中的“学位”列进行自动填充。
◆ 要求：填充的内容根据“学历”列的内容来确定（假定学生均已获得相应学位）：
- 博士研究生－博士
- 硕士研究生－硕士
- 本科－学士
- 其他－无
公式为：
=IF(G3="博士研究生","博士",IF(G3="硕士研究生","硕士",IF(G3="本科","学士","无
")))
4.使用数组公式，在 Sheet1 中计算：
◆ “笔试比例分”，计算方法为：（笔试成绩/3）*60％
◆ “面试比例分”，计算方法为：面试成绩*40％
◆ “总成绩”，计算方法为：笔试比例分+面试比例分
a.计算公式： {=(I3:I18/3)*%60}
b.计算公式： {=K3:K18*%40}
c.计算公式： {=J3:J18+L3:L18}
注意：a 公式中“（）”不能少，须用数组公式，结束按“Shift+Ctrl+Enter”。
5.修改数组公式，将 Sheet1 复制到 Sheet2，在 Sheet2 中计算：
◆ 要求：修改“笔试比例分”的计算，计算方法为：（（笔试成绩/2）*60％）。
修改后公式为： {=(I3:I18/2)*%60}
注意：先选中 J3:J18 整体后再修改，结束按“Shift+Ctrl+Enter”。
6.在 Sheet2 中，添加一列，将其命名为“排名”。
◆ 要求：使用 RANK 函数，根据“总成绩”对所有考生排名。
公式为： =RANK(M3,$M$3:$M$18)
7.将 Sheet2 复制到 Sheet3，并对 Sheet3 进行高级筛选。
◆ 筛选条件为：“报考单位”－中院、“性别”－男、“学历”－硕士研究生
◆ 将筛选结果保存在 Sheet3 中
先把表从 Sheet2 复制到 Sheet3，再设置条件区域如下：
注意：中院包括“一中院”或“三中院”,条件区域多 1 行表示。
点击[数据]→[筛选]→[高级]，经高级筛选后的结果为：
公务员考试成绩表
报考单位报考职位准考证号姓名
郎怀民
李小珍
项文双性别
男
男
男
出生年月
1970/07/30
1979/02/16
1972/10/31
学历
硕士研究生
硕士研究生
硕士研究生
学位
硕士
硕士
硕士
笔试成绩
笔试成绩比
例分
面试成绩
面试成绩比
例分
总成绩排名
8
1
10
一中院法官(刑事、男)050008502309一中院法官(民事、男)050008501663一中院法官(民事、男)050008504259134.00
153.50
133.50
40.20
46.05
40.05
86.50
90.67
85.00
34.60
36.27
34.00
74.80
82.32
74.05
8.根据 Sheet2，在 Sheet4 中新建一数据透视表。要求：
◆ 显示每个报考单位的人的不同学历的总人数
◆ 行区域设置为“报考单位”
◆ 列区域设置为“学历”
◆ 数据区域设置为“学历”
◆ 计数项为学历
注：第 1 条要求只是概括性描述，没有对应的实际操作，只需按后面几点设置即可。
鼠标先点在 Sheet2 的表上，再选[插入]→[数据透视表]，“现有工作表”“Sheet4!$A$1”选为；进入数据布局窗口后，把字段“报考单位”拖入行标签、“学历”拖入列标签和数值区，如
下图：
在 Sheet4 上产生的数据透视表如下：
计数项:学历行标签
区法院
三中院
市高院
一中院
总计列标签
本科
3
3
1
7
博士研究生大专硕士研究生
1
1
1
2
1
2
2
3
5
总计
4
3
5
4
16
第8 套员工信息
在考生文件夹的Excel子文件夹中，已有Excel.xlsx文件。按下列要求操作，并将结果
存盘。
1.在Sheet4的A1单元格中设置为只能录入5位数字或文本。当录入位数错误时，提示错误原因，样式为“警告”，错误信息为“只能录入5位数字或文本”。
选定Sheet4的A1，点击[数据]→[数据有效性]，打开对话框进行设置：