EXCEL 综合练习题主讲教师：朱扬清EXCEL EXCEL 综合练习题综合练习题一、打开工作薄一、打开工作薄一、打开工作薄一、打开工作薄2003A.XLS2003A.XLS2003A.XLS2003A.XLS，完成下列操作后仍按原名保存。，完成下列操作后仍按原名保存。，完成下列操作后仍按原名保存。，完成下列操作后仍按原名保存。1 1(15(15分分) )在在Sheet1Sheet1中完成下列计算：中完成下列计算： 将区域将区域B6:B25B6:B25的格式设置成与区域的格式设置成与区域C6:C25C6:C25相同的格式。相同的格式。 使用工作表函数在使用工作表函数在D6D6，D7D7，D25D25单元中计算，计算法则为：当所单元中计算，计算法则为：当所在行的入住日期的月份为在行的入住日期的月份为2 2月份时，其结果值为月份时，其结果值为“OkayOkay”，否则为，否则为“NoNo”。2 2(10(10分分) )对工作表对工作表Sheet2Sheet2中的数据清单进行分类汇总，求出各系的人数。中的数据清单进行分类汇总，求出各系的人数。3 3(5(5分分) )从工作表从工作表Sheet3Sheet3的数据清单中筛选出所有英文名称的最后一个字的数据清单中筛选出所有英文名称的最后一个字母为母为“M M”的记录，并将筛选结果的记录，并将筛选结果( (包括标题行包括标题行) )复制到以复制到以Sheet3Sheet3！H1H1为左为左上角的区域中。要求将条件区域放在以上角的区域中。要求将条件区域放在以SHEET3!E1SHEET3!E1为左上角的区域中。为左上角的区域中。4 4(5(5分分) )将考试目录中的将考试目录中的2003AM.xls2003AM.xls、 2003AN.xls2003AN.xls、 2003AP.xls2003AP.xls三个文三个文件中所有的工作表复制到一个新工作簿中，要求所有的工作表原来的名件中所有的工作表复制到一个新工作簿中，要求所有的工作表原来的名称都不能改变，最后将新工作簿以称都不能改变，最后将新工作簿以2003ATotal.xls2003ATotal.xls作文件名保存到考试作文件名保存到考试目录中。目录中。 EXCEL 综合练习题二、打开工作簿二、打开工作簿二、打开工作簿二、打开工作簿2003B.XLS2003B.XLS2003B.XLS2003B.XLS，完成下列操作后仍按原名保存。，完成下列操作后仍按原名保存。，完成下列操作后仍按原名保存。，完成下列操作后仍按原名保存。1 1（1515分分) ) 在在Sheet1Sheet1中完成下列计算：中完成下列计算： (1) (1) 将区域将区域B6B6：C25C25中所有大于中所有大于20022002年年4 4月月1 1日的单元格文字设置为蓝色。日的单元格文字设置为蓝色。 (2) (2) 使用工作表函数在使用工作表函数在D6D6，D7D7，D25D25单元中计算，计算法则为：单元中计算，计算法则为：当所在行的入住日期的年份为当所在行的入住日期的年份为20032003年时，其结果值为年时，其结果值为“OkayOkay”，否则为，否则为“NoNo”。2 2（5 5分）从工作表分）从工作表Sheet2Sheet2的数据清单中筛选出所有语文与数学成绩之差的数据清单中筛选出所有语文与数学成绩之差大于大于3030分的记录，并将筛选结果（包括标题行分的记录，并将筛选结果（包括标题行) )复制到以复制到以Sheet2Sheet2！H1H1为左为左上角的区域中。要求将条件区域放在以上角的区域中。要求将条件区域放在以Sheet2!E1Sheet2!E1为左上角的区域中。为左上角的区域中。3 3（1010分分) ) 对工作表对工作表Sheet3Sheet3中的数据清单进行分类汇总，求出各年级入学中的数据清单进行分类汇总，求出各年级入学成绩的平均分。成绩的平均分。4.(54.(5分分) )将考试目录中的将考试目录中的2003BM.xls2003BM.xls、 2003BN.xls2003BN.xls、 2003BP.xls2003BP.xls三个文件三个文件中所有的工作表复制到一个新工作簿中，要求所有的工作表原来的名称中所有的工作表复制到一个新工作簿中，要求所有的工作表原来的名称都不能改变，最后将新工作簿以都不能改变，最后将新工作簿以2003BTotal.xls2003BTotal.xls作文件名保存到考试目作文件名保存到考试目录中。录中。EXCEL 综合练习题三、打开工作薄三、打开工作薄三、打开工作薄三、打开工作薄2003C.XLS2003C.XLS2003C.XLS2003C.XLS，完成下列操作后仍按原名保存。，完成下列操作后仍按原名保存。，完成下列操作后仍按原名保存。，完成下列操作后仍按原名保存。1 1（1515分分) ) 在在Sheet1Sheet1中完成下列计算：中完成下列计算： (1) (1) 将区域将区域A6A6：A25A25的格式清除。的格式清除。（2 2） 在单元在单元F1F1内使用数据库统计函数求出区域内使用数据库统计函数求出区域A5:C25A5:C25所给数据清单中所所给数据清单中所有住店天数（离店日期与入住日期之差有住店天数（离店日期与入住日期之差) )为为2 2的记录的个数。的记录的个数。2 2(5(5分分) )从工作表从工作表Sheet2Sheet2的数据清单中筛选出所有英文名称的第的数据清单中筛选出所有英文名称的第3 3个字母为个字母为“P P”的记录，并将筛选结果的记录，并将筛选结果( (包括标题行包括标题行) )复制到以复制到以Sheet2Sheet2！H1H1为左上角为左上角的区域中。要求将条件区域放在以的区域中。要求将条件区域放在以Sheet2!E1Sheet2!E1为左上角的区域中。为左上角的区域中。3.(53.(5分分) ) 工作表工作表Sheet3Sheet3中图表所使用的数据取自中图表所使用的数据取自Sheet3!A1:D6Sheet3!A1:D6，试将其修，试将其修改为表现按城市分类比较每季度下雨天数的柱形图。改为表现按城市分类比较每季度下雨天数的柱形图。4 4(10(10分分) ) 根据区域根据区域Sheet4Sheet4中的数据清单，作一个以中的数据清单，作一个以“日期日期”为行字段，为行字段，“业务业务”为列字段的数据透视表，统计各项业务的金额之和，并使其显为列字段的数据透视表，统计各项业务的金额之和，并使其显示在新建工作表示在新建工作表Sheet5Sheet5中。中。EXCEL 综合练习题四、打开工作簿四、打开工作簿四、打开工作簿四、打开工作簿2003D.XLS2003D.XLS2003D.XLS2003D.XLS，完成下列操作后仍按原名保存。，完成下列操作后仍按原名保存。，完成下列操作后仍按原名保存。，完成下列操作后仍按原名保存。1 1(15(15分分) )在在Sheet1Sheet1中完成下列计算：中完成下列计算： 将区域将区域B6:B25B6:B25的格式设置成与区域的格式设置成与区域C6:C25C6:C25相同的格式。相同的格式。 使用工作表函数在使用工作表函数在D6D6，D7D7，D25D25单元中计算，计算法则为：当单元中计算，计算法则为：当所在行的离店日期与入住日期之差大于所在行的离店日期与入住日期之差大于3 3时，其结果值为时，其结果值为“OkayOkay”，否则，否则为为“NoNo”。2 2（5 5分）从工作表分）从工作表Sheet2Sheet2的数据清单中筛选出所有语文和数学的平均成的数据清单中筛选出所有语文和数学的平均成绩大于等于绩大于等于9090分的记录，并将筛选结果（包括标题行分的记录，并将筛选结果（包括标题行) )复制到以复制到以Sheet2Sheet2！H1H1为左上角的区域中。要求将条件区域放在以为左上角的区域中。要求将条件区域放在以Sheet2!E1Sheet2!E1为左上角的区域为左上角的区域中。中。3. 3. （5 5分）在工作表分）在工作表Sheet3Sheet3中的图表的数据取自各系人员情况数据库，对中的图表的数据取自各系人员情况数据库，对该图表完成下面的操作：加入该图表完成下面的操作：加入“教师教师”的数据并作为第一个数据系列；的数据并作为第一个数据系列；4 4(10(10分分) ) 根据区域根据区域Sheet4!Sheet4!中的数据清单，作一个以中的数据清单，作一个以“月月”为行字段，为行字段，“部门部门”为列字段的数据透视表，统计各部门的营业额之和，并使其显示为列字段的数据透视表，统计各部门的营业额之和，并使其显示在以在以Sheet4!G1Sheet4!G1为左上角的区域中。为左上角的区域中。EXCEL 综合练习题五、五、打开工作薄打开工作薄打开工作薄打开工作薄2003E.XLS2003E.XLS2003E.XLS2003E.XLS，完成下列操作后仍按原名保存。，完成下列操作后仍按原名保存。，完成下列操作后仍按原名保存。，完成下列操作后仍按原名保存。1 1 (15(15分分) ) 在在Sheet1Sheet1中完成下列计算中完成下列计算: : 将区域将区域B6:B36B6:B36中的值（不包含格式）复制到区域中的值（不包含格式）复制到区域C6C6：C36C36中。中。 在单元在单元E1E1内使用数据库统计函数求出区域内使用数据库统计函数求出区域A5A5：B36B36所给数据清单的名单的第所给数据清单的名单的第1 1个字母不是个字母不是“P P”的记录的个的记录的个数。数。2 2 (5(5分分) )从工作表从工作表Sheet2!Sheet2!的数据清单中筛选出所有姓名的的数据清单中筛选出所有姓名的最后一个字为最后一个字为“华华”的女生的记录，并将筛选结果的女生的记录，并将筛选结果( (包包括标题行括标题行) )复制到以复制到以Sheet2Sheet2！I1I1为左上角的区域中。要为左上角的区域中。要求将条件区域放在以求将条件区域放在以Sheet2!F1Sheet2!F1为左上角的区域中。为左上角的区域中。3 3 (5(5分分) )根据区域根据区域Sheet3!A3:E7Sheet3!A3:E7中的数据建立一个柱形图，中的数据建立一个柱形图，并将其放在同一工作表中以并将其放在同一工作表中以A10A10为左上角的区域中，要为左上角的区域中，要求图表分东方分店、大桥分店、双阳分店和昌图分店求图表分东方分店、大桥分店、双阳分店和昌图分店4 4类，第类，第1 1个数据系列为个数据系列为“服装服装”，第，第2 2个数据系列为个数据系列为“家家电电”。4 4(10(10分分) )根据区域根据区域Sheet4!A1:C184Sheet4!A1:C184的数据清单中所列的成的数据清单中所列的成绩，在区域绩，在区域Sheet4!C2:C184Sheet4!C2:C184中计算出每个学生的名次，中计算出每个学生的名次，规则为规则为: :成绩最高者名次为成绩最高者名次为1 1，然后随成绩递减而名次依，然后随成绩递减而名次依次加次加1 1，若成绩相同则名次也相同，但该名次之后的学，若成绩相同则名次也相同，但该名次之后的学生名次仍应为比其名次靠前的总人数加生名次仍应为比其名次靠前的总人数加1 1。例如下表所。例如下表所示。注意：不能改变原来数据的顺序，改变者不得分。示。注意：不能改变原来数据的顺序，改变者不得分。姓名成绩名次李书召954郑含因972吉 琴991赵敏生972EXCEL 综合练习题六、打开工作薄六、打开工作薄六、打开工作薄六、打开工作薄2003F.XLS2003F.XLS2003F.XLS2003F.XLS，完成下列操作后仍按原名保存。，完成下列操作后仍按原名保存。，完成下列操作后仍按原名保存。，完成下列操作后仍按原名保存。1 1 (15(15分分) ) 在在Sheet1Sheet1中完成下列计算：中完成下列计算： (1) (1) 将区域将区域B6:B36B6:B36中的数据设置为保留中的数据设置为保留2 2位小数的科学记数格式，并将所有大位小数的科学记数格式，并将所有大于于8080的数字设置为红色。的数字设置为红色。 (2) (2) 使用工作表函数在使用工作表函数在C6C6，C7C7，C36C36单元中计算，计算法则为：当所在单元中计算，计算法则为：当所在行的行的B B列单元中的数值不为整数时，其结果取值为列单元中的数值不为整数时，其结果取值为“RealReal”，否则为，否则为“IntegerInteger”。2 2(5(5分分) )从工作表从工作表Sheet2Sheet2的数据清单中筛选出所有的数据清单中筛选出所有20012001级的数学专业学生的记录，级的数学专业学生的记录，并将筛选结果并将筛选结果( (包括标题行包括标题行) )复制到以复制到以Sheet2Sheet2！I1I1为左上角的区域中。要求将条件为左上角的区域中。要求将条件区域放在以区域放在以Sheet2!F1Sheet2!F1为左上角的区域中。为左上角的区域中。3 3(5(5分分) )对工作表对工作表Sheet3Sheet3中的图表按下列要求进行修改；为图表增加一个标题，标中的图表按下列要求进行修改；为图表增加一个标题，标题的内容引用单元格题的内容引用单元格A1A1中的值。中的值。4 4(10(10分分)Sheet4)Sheet4中电话号码是原来佛山和顺德的电话号码，其中头中电话号码是原来佛山和顺德的电话号码，其中头4 4位为区号，现位为区号，现在要由七位升八位并且区号全部变成在要由七位升八位并且区号全部变成07570757，规则是：，规则是：(1) (1) 原佛山市号码（区号为原佛山市号码（区号为07570757），则新号码在原号码前加），则新号码在原号码前加8 8（如：原来是（如：原来是0757301610407573016104，变成，变成075783016104075783016104）。）。(2) (2) 原顺德市号码（区号为原顺德市号码（区号为07650765），则新号码在原号码前加），则新号码在原号码前加2 2（如原来是（如原来是0765282086207652820862，变成，变成075722820862075722820862）。）。根据区域根据区域Sheet4!B3:B12Sheet4!B3:B12中的原号码在区域中的原号码在区域Sheet4!C3:C12Sheet4!C3:C12中用公式计算出相应的新中用公式计算出相应的新号码。号码。 EXCEL 综合练习题七、打开工作薄七、打开工作薄七、打开工作薄七、打开工作薄2003G.XLS2003G.XLS2003G.XLS2003G.XLS，完成下列操作后仍按原名保存。，完成下列操作后仍按原名保存。，完成下列操作后仍按原名保存。，完成下列操作后仍按原名保存。1 1 (15(15分分) )在在Sheet1Sheet1中完成下列计算：中完成下列计算： 将区域将区域B6:D40B6:D40中的数据设置为中的数据设置为2 2位小数的数值格式，并将所有大位小数的数值格式，并将所有大于于6060的数字用蓝色显示。的数字用蓝色显示。 使用工作表函数在使用工作表函数在E6E6，E7E7，E40E40单元中计算，计算法则为：单元中计算，计算法则为：当所在行的当所在行的X X与与Y Y之和大于之和大于Z Z时，其结果值时，其结果值“YESYES”，否则为，否则为“NONO”。2 2 (5(5分分) ) 从工作表从工作表Sheet2Sheet2的数据清单中筛选出所有第一志愿为佛山大学，的数据清单中筛选出所有第一志愿为佛山大学，3+3+综合总分在综合总分在500500分及以上，分及以上，X X科分数在科分数在8080分及以上的记录，并将筛选结分及以上的记录，并将筛选结果（包括标题行）中果（包括标题行）中“准考证号准考证号”、“姓名姓名”、“3+3+综合总分综合总分”三个字三个字段的信息复制到以段的信息复制到以Sheet2Sheet2！H10H10为左上角的区域中。要求将条件区域放在为左上角的区域中。要求将条件区域放在以以Sheet2!H1Sheet2!H1为左上角的区域中。为左上角的区域中。3 3 (10(10分分) ) 根据区域根据区域Sheet3Sheet3中的数据清单，作一个以中的数据清单，作一个以“部门部门”为行字段，为行字段，“月月”为列字段的数据透视表，统计各部门的营业额之和，并使其显示为列字段的数据透视表，统计各部门的营业额之和，并使其显示在以在以Sheet3!F1Sheet3!F1为左上角的区域中。为左上角的区域中。4.(54.(5分分) )将考试目录中的将考试目录中的2003GM.xls2003GM.xls、 2003GN.xls2003GN.xls、 2003GP.xls2003GP.xls三个文件三个文件中所有的工作表复制到一个新工作簿中，要求所有的工作表原来的名称中所有的工作表复制到一个新工作簿中，要求所有的工作表原来的名称都不能改变，最后将新工作簿以都不能改变，最后将新工作簿以2003GTotal.xls2003GTotal.xls作文件名保存到考试目作文件名保存到考试目录中。录中。EXCEL 综合练习题八、打开工作簿文档八、打开工作簿文档八、打开工作簿文档八、打开工作簿文档2003H.XLS2003H.XLS2003H.XLS2003H.XLS，完成下列操作后仍按原名保存。，完成下列操作后仍按原名保存。，完成下列操作后仍按原名保存。，完成下列操作后仍按原名保存。1 1 (15(15分分) )在在Sheet1Sheet1中完成下列计算：中完成下列计算： 将区域将区域B6:D40B6:D40中的数据设置为中的数据设置为2 2位小数的数值格式位小数的数值格式, ,并将所有小于并将所有小于0.40.4的数字用红色显示。的数字用红色显示。 在单元在单元G1G1内使用数据库统计函数求出区域内使用数据库统计函数求出区域A5A5：D40D40所给数据清单中满足所给数据清单中满足条件的条件的“X X与与Y Y之和大于之和大于Z Z”的记录的的记录的Z Z值之和。值之和。2 2 (5(5分分) ) 从工作表从工作表Sheet2!Sheet2!的数据清单中筛选出所有第一志愿为广州大学，的数据清单中筛选出所有第一志愿为广州大学，X X科目为物理且分数在科目为物理且分数在8585分及以上的记录，并将筛选结果（包括标题行）分及以上的记录，并将筛选结果（包括标题行）中中“准考证号准考证号”、“姓名姓名”、“3+3+综合总分综合总分”三个字段的信息复制到以三个字段的信息复制到以Sheet2Sheet2！H10H10为左上角的区域中。要求将条件区域放在以为左上角的区域中。要求将条件区域放在以Sheet2!H1Sheet2!H1为左为左上角的区域中。上角的区域中。3 3 (10(10分分) ) 根据区域根据区域Sheet3Sheet3中的数据清单，中的数据清单，“业务业务”作一个以为行字段，作一个以为行字段，“日期日期”为列字段的数据透视表，统计各项业务的金额之和，并使其显为列字段的数据透视表，统计各项业务的金额之和，并使其显示在以示在以Sheet3!F1Sheet3!F1为左上角的区域中。为左上角的区域中。4.(54.(5分分) )将考试目录中的将考试目录中的2003HM.xls2003HM.xls、 2003HN.xls2003HN.xls、 2003HP.xls2003HP.xls三个文件三个文件中所有的工作表复制到一个新工作簿中，要求所有的工作表原来的名称中所有的工作表复制到一个新工作簿中，要求所有的工作表原来的名称都不能改变，最后将新工作簿以都不能改变，最后将新工作簿以2003HTotal.xls2003HTotal.xls作文件名保存到考试目作文件名保存到考试目录中。录中。