六、Excel2003高级应用
考生文件夹：d:\\result\\126202716医院病人护理统计表（126202716DExcel）
1．在sheet4中，使用函数，根据A1单元格中的身份证号码判断性别，结果为“男”或“女”，存放在A2单元格中。
? 倒数第二位为奇数的为“男”，为偶数的为“女”
①在Sheet4中选择A2单元格——单击②设置参数如图所示：
“插入函数”按钮——选择“IF”函数
2．在sheet4中，使用函数，将B1单元格中的数四舍五入到整百，存放在C1单元格中。 单击sheet4中C1单元格，在编辑栏中输入
3．使用VLOOKUP函数，根据Sheet1中的“护理价格表”，对“医院病人护理统计表”中的“护理价格”列进行自动填充。
①单击F3单元格——单击②设置参数如图所示：
“插入函数”按钮——选择“VLOOKUP”函数
③对该列下方的值进行自动填充
4．使用数组公式，根据Sheet1中“医院病人护理统计表”中的“入住时间”列和“出院时间”列中的数据计算护理天数，并把结果保存在“护理天数”列中。计算方法为：护理天数=出院时间－入住时间。
①单击H3单元格——输入“G3-D3” —— 按“Ctrl+Shift+Enter”键输入数组公式 ②对该列下方的值进行自动填充
5．使用数组公式，根据Sheet1中“医院病人护理统计表”的“护理价格”和“护理天数”列，对病人的护理费用进行计算，并把结果保存在该表的“护理费用”列中。计算方法为：护理费用=护理价格*护理天数。
①选择I3单元格——输入“=F3*H3”——按“Ctrl+Shift+Enter”键输入数组公式 ②选择I3单元格——鼠标定位到右下角变为十字光标时拖动（即自动填充） 6．使用数据库函数，按以下要求计算。要求：
（a）计算Sheet1“医院病人护理统计表”中，性别为女性，护理级别为中级护理，护理天数大于30天的人数，并保存N13单元格中；
第 29 页 共 71 页
（b）计算护理级别为高级护理的护理费用总和，并保存N22单元格中。
①单击N13单元格——单击②设置参数如图所示：
“插入函数”按钮——选择“DCOUNTA”函数
③单击N22单元格——单击④设置参数如图所示：
“插入函数”按钮——选择“DSUM”函数
7．把Sheet1中的“医院病人护理统计表”复制到Sheet2，进行自动筛选。要求： （a）筛选条件为：“性别”—女、“护理级别”—高级护理； （b）将筛选结果保存在Sheet2中。
注意：（a）复制过程中，将标题项“医院病人护理统计表”连同数据一同复制。 （b）复制数据表后，粘贴时，数据表必须顶格放置。
①选择A1至I30单元格——“复制”——单击Sheet2工作表——单击A1单元格——“粘贴” ②复制Sheet工作表中F3至F30单元格——单击Sheet2工作表——单击F3单元格——“选择性粘贴”——“数值”
③将光标定位到Sheet2的A2单元格——“数据”菜单——“筛选”——“自动筛选” ④单击“性别”标题项右边的筛选按钮——选择“女”
⑤单击“护理级别”标题项右边的筛选按钮——选择“高级护理”
8．根据Sheet1中的“医院病人护理统计表”，创建一个数据透视图Chart1，要求： （a）显示每个护理级别的护理费用情况； （b）X坐标设置为“护理级别”； （c）数据区域设置为“护理费用”； （d）求和为护理费用；
（e）将对应的数据透视表保存在Sheet3中。
①单击“Sheet1”表中的数据区域的任意单元格内——选择“数据”菜单——数据透视表和数据透视图
②按向导完成如下操作：
第 30 页 共 71 页
最终效果图：
考生文件夹：d:\\result\\126202711温度情况表（126202711DExcel）
1．在sheet5的A1单元格中设置为只能录入5位数字或文本。当录入位数错误时，提示错误原因，样式为“警告”，错误信息为“只能录入5位数字或文本”。
①单击Sheet5单元格——单击A1单元格——“数据”菜单——“有效性”——设置如图所示：
第 31 页 共 71 页
2．在sheet5中，使用函数，根据A2单元格中的身份证号码判断性别，结果为“男”或“女”，存放在B2单元格中。
? 倒数第二位为奇数的为“男”，为偶数的为“女”
3．使用IF函数，对Sheet1“温度情况表”中的“温度较高的城市”列进行自动填充，填充结果为城市名称。如杭州、上海。
①单击D3单元格——单击②设置如图：
“插入函数”按钮——选择“IF”函数
③选择D3单元格——鼠标定位到右下角变为十字光标时拖动（即自动填充）
4．使用数组公式，对Sheet1“温度情况表”中的相差温度值（杭州相对于上海的温差）进行计算，并把结果保存在“相差温度值”列中。计算方法：相差温度值=杭州平均气温—上海平均气温。
①选择E3单元格——输入“=B3-C3”——按“Ctrl+Shift+Enter”键输入数组公式 ②选择E3单元格——鼠标定位到右下角变为十字光标时拖动（即自动填充） 5．利用函数，根据Sheet1“温度情况表”中的结果，符合以下条件的进行统计。 a．杭州这半个月以来的最高气温和最低气温，保存在相应单元格中。 b．上海这半个月以来的最高气温和最低气温，保存在相应单元格中。 ①单击C19单元格——单击
“插入函数”按钮——选择“MAX”函数
②拖动鼠标选择B3到B17单元格，确定，即求得杭州半个月以来的最高气温。 ③选择C20单元格——单击
“插入函数”按钮——选择“MIN”函数
④拖动鼠标选择B3到B17单元格，确定，即求得杭州半个月以来的最低气温。 ⑤同理可求得上海这半个月以来的最高气温和最低气温。
6．将Sheet1中的“温度情况表”复制到Sheet2中，在Sheet2中，重新编辑数组公式， 将Sheet2中的“相差的温度值”中的数值取绝对值（均为正数）。
第 32 页 共 71 页
注意：（a）复制过程中，将标题项“温度情况表”连同数据一同复制； （b）复制数据表后，粘贴时，数据表必须顶格放置。
①选择A1到E22——复制——单击Sheet2表——粘贴 ②选择E3单元格——按Delete键——单击
“插入函数”按钮——选择“ABS”函数
③输入“B3-C3”——确定——按“Ctrl+Shift+Enter”键输入数组公式 ④选择E3单元格——鼠标定位到右下角变为十字光标时拖动（即自动填充） 7．将Sheet2中的“温度情况表”复制到Sheet3中，并对Sheet3进行高级筛选，要求： （a）筛选条件：“杭州平均气温”>=20，“上海平均气温”<20 （b）将筛选结果保存在Sheet3中。 注意：（a）无需考虑是否删除筛选条件；
（b）复制过程中，将标题项“温度情况表”连同数据一同复制； （c）复制数据表后，粘贴时，数据表必须顶格放置。
①选择A1到E22——复制——单击Sheet3表——粘贴 ②在表中输入筛选条件，如图所示：
③“数据”菜单——“筛选”——“高级筛选” ④设置如图所示：
8．根据Sheet1中“温度情况表”的结果，在Sheet4中创建一张数据透视表，要求： a．显示温度较高天数的汇总情况； b．行区域设置为“温度较高的城市”； c．数据域设置为“温度较高的城市” d．计数项设置为温度较高的城市。
①单击“Sheet1”表——“数据”菜单——“数据透视表和数据透视图” ②按向导完成如下操作：
第 33 页 共 71 页