1、 六、 Excel2003 高级应用 考生文件夹： d:result126204116 第一套 1使用 IF 函数，对 Sheet1 中的“温度较高的城市”列进行自动填充。 单击 D2 单元格 单击 “插入函数”按钮 选择“ IF”函数 设置如图： 选择 D2 单元格 鼠标定位到右下角变为十字光标时拖动（即自动填充） 2使用数组公式，对 Sheet1 中的相差温度值（杭州相对于上海的温差）进行填充。 选择 E2 单元格 输入“ =B2-C2” 按“ Ctrl+Shift+Enter”键输入数组公式 选 择 E2 单元格 鼠标定位到右下角变为十字光标时拖动（即自动填充） 3利用函数，根据 She
2、et1 中的结果，符合以下条件的进行统计。 a杭州这半个月以来的最高气温和最低气温； b上海这半个月以来的最高气温和最低气温。 单击 C18 单元格 单击 “插入函数”按钮 选择“ MAX”函数 拖动鼠标选择 B2 到 B16 单元格，确定，即求得杭州半个月以来的最高气温。 选择 C19 单元格 单击 “插入函数”按钮 选择“ MIN”函数 拖动鼠标选择 B2 到 B16 单元格，确定，即求得杭州半个月以来的最低气温。 同理可求得 上海这半个月以来的最高气温和最低气温。 4将 Sheet1 复制到 Sheet2 中，在 Sheet2 中，重新编辑数组公式， 将 Sheet2 中的“相差的温度值
3、”中的数值取绝对值（均为正数）。 选择 A1 到 E21 复制 单击 Sheet2 表 粘贴 选择 E2 单元格 按 Delete 键 单击 “插入函数”按钮 选择“ ABS”函数 输入“ B2-C2” 确定 按“ Ctrl+Shift+Enter”键输入数组公式 选择 E2 单元格 鼠标定位到右下角变为十字光标时拖动（即自动填充） 5将 Sheet2 复制到 Sheet3 中，并对 Sheet3 进行高级筛选，要求： 筛选条件：“杭州平均气温” =20，“上海平均气温” 1000000; b将结果保存在 Sheet3 中。 选择 A1 到 E26 复制 单击 Sheet3 表 粘贴 在表中输
4、入筛选条件，如图所示： 数据 筛选 高级筛选 设置如图所示： 6根据 Sheet1 中的结果，创建一张数据透视表 Chart1，要求： a显示每个销售人员销售房屋所缴纳契税总额； b行区域设置为“销售人员”； c计数项设置为契税总额。 d将对应的数据透视表保存在 Sheet4 中。 单击“ Sheet1”表 数据 数据透视表和数据透视图 按向导完成如下操作： 设置选定区域为“ Sheet1!$A$2:$E$26” 单击“下一步”按钮 选择“新建工作表” 单击“布局”按钮 将“ 销售人员 ”拖动到行区域，将“ 契税总额 ”拖动到数据区域 单击“完成”按钮 考生文件夹： d:result12620
5、4109 1使用时间函数，对 Sheet1 中用户的年龄进行计算。 要求：计算用户的年龄，并将其计算结果填充到“年龄”列当中。 单击 D2 单元格 输入“ =YEAR(TODAY()-YEAR(C2)” 回车 对该列下方的值实行自动填充 2使用 REPLACE 函数，对 Sheet1 中用户的电话号码进行升级。 要求：对“原电话号码”列中的电话号码进行升级。升级方法是在区号（ 0571）后面加上“ 8”， 并将其计算结果保存在“升级电话号码”列的相应单元格中。 单击 G2 单元格 单击 “插入函数”按钮 选择“ REPLACE”函数 设置参数如图所示： 对该列下方的值实行自动填充 3使用逻辑函
6、数，判断 Sheet1 中的“大于等于 40 岁的男性”， 将结果保存在 Sheet1 中的“是否 =40 男性”。 单击 H2 单元格 单击 “插入函数”按钮 选择“ IF”函数 设置参数如图所示： 对该列下方的值实行自动填充 4对 Sheet1 中的数据，根据以下条件，利用函数进行统计： a统计性别为“男”的用户人数，将结果填入 Sheet2 的 B2 单元格中； b统计年龄为“ 40”岁 的用户人数，将结果填入 Sheet2 的 B2 单元格中。 单击 Sheet2 表 单击 B2 单元格 单击 “插入函数”按钮 选择“ COUNTIF”函数 设置参数如图所示： 单击 B3 单元格 单击
7、 “插入函数”按钮 选择“ COUNTIF”函数 设置参数如图所示： 5将 Sheet1 复制到 Sheet3 中，并对 Sheet3 进行高级筛选，要求： a筛选条件为：“性别” 女、“所在区域” 西湖区； b将筛选结果保存在 Sheet3 中。 选择 A1 到 H37 复制 单击 Sheet3 表 粘贴 在表中输入筛选条件，如图所示： 数据 筛选 高级筛选 设置如图所示： 6根据 Sheet1 中的结果，创建一张数据透视表 Chart1，要求： a显示每个区域所拥有的用户数量； b x 坐标设置为“所在区域”； c计数项为“所在区域”； d将对应的数据透视表保存在 Sheet4 中。 单击
8、“ Sheet1”表 数据 数据透视表和数据透视图 按向导完成如下操作： 设置选定区域为“ Sheet1!$A$1:$H$37” 单击“下一步”按钮 选择“新建工作表” 单击 “布局”按钮 将“ 所在区域 ”拖动到行区域及数据区域中 单击“完成”按钮 考生文件夹： d:result126204102 第十五套 1使用 HLOOKUP 函数，对 Sheet1 中的停车单价进行自动填充。 要求：根据 Sheet1 中的“停车价目表”价格，利用 HLOOKUP 函数对“停车情况记录表”中的“单价”列根据不同的车型进行自动填充。 单击 C9 单元格 单击 “插入函数”按钮 选择“ HLOOKUP”函数
9、 设置参数如图所示： 对该列下方的值实行自动填充 2在 Sheet1 中，利用时间函数计算汽车在停车库中的停放时间。 要求： a公式计算方法为“出库时间 入库时间”； b格式为：“小时：分钟：秒”。 （例如：一小时十五分十二秒在停放时间中的表示为：“ 1： 15： 12”） 选择 F9 单元格 输入“ =E9-D9” 按 Enter 键 对该列下方的值实行自动填充 3使用函数公式，计算停车费用，要求： 根据停放时间的长短计算停车费用，将计算结果填入到“应付金额”列中。 注意： a停车按小时收费，对于不满一个小时的按照一个小时计费； b对于超过整点小时数十五分钟的多累积一个小时。 （例如： 1
10、小时 23 分，将以 2 小时计费） 选择 G9 单元格 输入如下公式 按 Enter 键 “ =IF(HOUR(F9)0,IF(MINUTE(F9)15,HOUR(F9)+1,HOUR(F9)*C9,C9)” 对该列下方的值实行自动填充 4使用统计函数，对 Sheet1 中的“停车情况记录表”根据下列条件进行统计，要求： a统计停车费用大于等于 40 元的停车记录条数； b统计最高的停车费用。 在 E3 单元格中输入“ 停车费用大于等于 40 元的停车记录条数 ” 单击 F3 单元格 单击 “插入函数”按钮 选择“ COUNTIF”函数 设置参数如图所示： 在 E4 单元格中输入“ 最高的停车费用 ” 单击 F4 单元格 单击 “插入函数”按钮 选择“ MAX”函数 输入“ G9:G39” 按确定按钮 5对 Sheet2，进行高级筛选，要求： a筛选条件为：“车型” 小汽车、“应付金额” =30； b将结果保存在 Sheet2 中。
