EXCEL综合学习总结分析(2010版-).doc

《EXCEL综合学习总结分析(2010版-).doc》由会员分享，可在线阅读，更多相关《EXCEL综合学习总结分析(2010版-).doc（9页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、 EXCELEXCEL 20102010 综合练习综合练习1、EXCEL 环境设置：默认工作目录为 D: 默认字体是“仿宋体、12” 按回车键自动向右、自动设置小数点位数：1 位 新工作簿内的工作表数:32、新建一个工作簿文件，在 Sheet1 中输入以下表格：(以学号*-1.xls 存于文件夹“练习六”下) 从 A2 起的第二行分别输入： 学号姓名性别班级入学时间是否团员入学总分所交费用从 A3 起输入二十组数据： 学号：001001001020（文本格式）； 姓名：自定； 性别：除 001005、001018 是男性外，均为女性； 班级：医士（1）班医士（4）班，每班 5 人； 入学时间：

2、统一为 2003 年 9 月 1 日（格式为*年*月*日） 是否团员：除 001004、001008，均为团员；（团员为”是“、非团员为“否”） 入学总分：520.0580.0 之间，自定； 所交费用：2500.00 元（用会计格式）； 最后，在表格的上方（第一行表格所有列的中间）输入：入学新生情况统计表 输完后，先将文件以文件名“*-1.xls”（*为你的学号），保存在指定的文件夹下。3、对以上表格设置格式： （1）将整个表格（不含第一行）设置外框为“橙色、双线”、内框为“橄榄色（强调文字颜色 3)、单线”；（2）将第一行设置：底纹颜色为第 1 行、第 7 列（指：水绿色，强调文字颜色 5，

3、淡色 80%）；图案为“细 逆对角线条纹”、颜色选“橄榄色，强调文字颜色 3，淡色 60%”； （3）全表所有单元格字体水平居中、垂直居中；4、将下列两个表格的数据，导入到 Sheet2 中 A2 (表格一)、A11（表格二） 表格一表格一： 书名出版社单价数量总价实价 计算机基础教程清华大学23.9040 电子表格培训教 材电子工业33.0034上网手册水利水电15.0070 五笔字形输入法 手册电子工业8.8050总计 表格二表格二: 书名折扣率 计算机基础教程0.95 电子表格培训教材0.90 上网手册0.85 五笔字形输入法手册0.805、将表格一区域命名“售书统计表”，并用自动表格格

4、式“表样式 浅色 11”；（凡是涉及金额的数字均用 货币格式，并带人民币符号） 将表格二区域命名为“折扣率”，并用自动表格格式“表样式 浅色 13”；均去除自动筛选。 计算每本书的总价与实价（实价=总价*折扣率）； 最后计算出所有书的总价与实价的总计值。6、打开 example.xls,将其中的“电脑销售表”复制到你的工作簿（*-1.xls）文件中。 将所有的空格中的数据统计出来。（研究一下，有没有更快捷的工作）。 将单价表部分进行转置，再重算工作表。7、在 Sheet1 中的 A23：A26 中依次输入：平均分、人数、最高分、最低分。然后在总分一栏的下方，分别用 函数计算出平均分、人数、最高

5、分、最低分。8、在 Sheet2 中， “实价“一栏的右面,再添一列,标题为“折扣价“。再用函数计算出“折扣价” ，条件是：如果 4050 本（含 40 本）实价再打 95 折；如果 5060 本（含 50 本）实价再打 9 折；如果 60 本以上（含 60 本）实价再打 85 折；不满 40 本则不打折。 在在“折扣价折扣价”的右边，输入的右边，输入“百分比百分比” ，计算出折扣价在总收，计算出折扣价在总收 入（打折后）中的百分比。入（打折后）中的百分比。9、在 Sheet1 中，“所交费用”一列的右面，增加一列“等级”。在下方根据以下条件输入学生成绩的等第: 570 分（含 570 分）以

6、上为“优”，550 分以上（含 550 分）为“良”；530 分以上（含 530 分）以上为“中” ，其余为差。在在“等级等级”的右边输入的右边输入“排名排名”，并计算学生入学成绩的排名。，并计算学生入学成绩的排名。 在在 K K 列输入下列几行字列输入下列几行字, ,并在并在 L L 列分别计算出相应的数据列分别计算出相应的数据. . 平均成绩 男生人数 女生人数 男生平均成绩 女生平均成绩10、将 sheet1 改名为“学生情况” ， sheet2 改名为“售书统计“；将前两个表格的位置进行交换（拖动工作 表标签） 。11、在“售书统计”表前插入一个新工作表；再用复制的方法，在“售书统计”

7、表后增加一个新工作表（用 ctrl+拖动） 。注意：新增的工作表不要改名。12、打开 example.xls，将“模拟运算表 (2)”和“利润分析表”复制到你的工作簿的最后。 （具体操作如下： 打开两个工作表，切换到 example 工作簿窗口，按 ctrl 键选中这两个工作表，点右键“复制或移动工作表” 选择你的工作表，并一定要选上“建立副本”选项（否则为移动而不是复制。确定。 ）13、当你打开多个文档时，可用“窗口重排” ，同时显示打开的文档。试一试！ 14、关闭 example.xls，并将你的文档最大化，切换到“学生情况”工作表，用“拆分窗口” ，上下：将标题 栏与下面的数据区分开；左

8、右：将学号、姓名与后面的列分开；选冻结窗口。滚动工作表的显示区域，观察 窗口的冻结效果。 恢复原来的窗口，只需先“取消冻结” ，再“取消拆分”即可。打开 example.xls 工作簿文件，将 RSDA 工作表复制到你的工作簿文件中去，放在最后一张工作表，并改名为 “职工工资” 。 15、在“职工工资”表中，先计算实发工资，并将各项收入与支出的格式，均改为保留 2 位小数。 在第 5 条纪录后添加记录：0085，张大有, 办公室, 男, 285.00, 260.00, 75.00, 31.20。16、删除目前的第 12 条记录。17、把第 13 条记录移动到第 1 条记录前。18、先在工作簿中

9、复制 “职工工资” ，更名为“工资排序” 。对“工资排序”表按“基本工资” （第一关键字） 进行排序，工资相同的按奖金（第二关键字）排，如前两项都相同则按加班工资（第三关键字）排（均为递 减） 。19、先在工作簿中复制“职工工资” ，更名为“部门排序” 。在“部门排序”表中，先选中第一关键字，对 “部门”按下列序列进行排序：办公室、人事处、总务处、事业一处、事业二处、事业三处。 （如果发现不能 按指定序列排序，则应先建立自定义序列，然后再按自定义序列排序）;再选中第二关键字，对“姓名”按笔 划进行排序（均为递增） 。 （注意：自定义序列建立的方式有两种，直接输入或导入）20、在“工资排序”表中

10、，先在第 37 行相应的位置，计算出三项收入的平均值，再按第 37 行重排三项收入 的位置（递增） 。 （具体操作：先选中三项收入所在的列，排序选项按行确定第 37 行） 。21、在“学生情况”工作表中，用“自动筛选”的方法，筛选出总分在 540570（不含 570）之间的女学生。 然后再恢复成“显示全部” 。22、筛选总分在前十名之内、且 1 班或 3 班的学生。最后取消自动筛选。23、在“职工工资”表中，用“自动筛选”的方法，选出所有在事业一处、二处、三处的男性职工。观察自 动筛选的结果，分析出各个字段条件之间是什么关系。能不能根据“或”的关系（即不同时满足所有的关系） 进行筛选？最后取消

11、自动筛选。 24、在“学生情况”表中，筛选出总分在 555 分以上的团员，或等级为“良”或“优”的男生。结果显示在 原处。25、在“职工工资”表中，同时筛选出 “事业一处的男职工及办公室的女职工”。并将结果保存在 A40 开始 的区域内。26、在“职工工资”表中，先按部门对工资、奖金、加班工资及实发工资进行分类汇总（汇总方式为“均值” ） ；再按部门对工资、奖金、加班工资及实发工资进行分类汇总（汇总方式为“求和” ） ，应保留均分的汇总结 果。 27、在“工资排序”表中，建立部门在行，性别在列的数据透视表。并比较分类汇总与数据透视的区别。28、将 “部门排序”表再复制四个工作表,分别重命名为“

12、一月”、“二月”、“三月”、 “季薪季薪”，将季 薪中的数值部分都删除，然后将三个月的工资进行合并计算，选中标志区域，选中选中“链接到源链接到源”,”,观察最后结 果，进行比较。29、找到 TEMP99.xls，将其中的“题目”工作表名称改为“图表练习 1” ，并复制到“*-1.xls”中，在 该表中的 A13 处，创建一个“三维簇状柱形图” 。图表的标题是“一季度农产品销售图表” ；分类轴标题为 “月份” ，放置在分类轴的右边；数值轴标题为“销售量” ，字体竖排，放置在数值轴的上方；图表标题的格 式为：蓝色、隶书、18 磅；X、Z 轴标题的字体是：绿色、楷体、12 磅。 （注意：关闭（注意：

13、关闭 TEMP99.xls 时，不要保存）时，不要保存）30、设置以上图表的三维格式，上下仰角改为 25，左右转角为 30 度。在图表下方显示数据表。调节图表的 大小，将标题以外的所有文字的字体大小改为 10 磅。31、找到 TEMP112.xls，将其中的“题目”工作表名称改为“图表练习 2” ，并复制到“*-1.xls”中，在 G1 处创建一个数据透视表， “地区”在页、 “经理”在行、 “险种”在列,统计“保费收入“的总计值。 然后，根据数据透视表建立一个“簇状柱形图”,图表标签名为“保费收入图“。 （注意：关闭（注意：关闭 TEMP112.xls 时，不要保存）时，不要保存）32、将第

14、 3 题中创建的柱形图，改为“中部地区人寿保险”的饼形图，并在图形旁显示百分比和数据标志。 最后要保存文件。33、建立一个学生成绩表工作簿（文件名为“学号-2.xlt）的模板，要有 16 个工作表，每个表均有标题行：学号、姓名、语文、数学、英语、平均分、总分、等级、名次 9 列；（想一想：如何做才能又快又好）34、其中输入平均分与总分公式，并复制到第 51 行； 用 IF 函数,输入等级的公式(平均分不满 60 分的为差,60 至 70(不含 70)的为中, 70 至 85(不含 85)的为良,85 以上的是优。35、对标题行设置统一的字体、对齐、图案等格式，并将除需要数据输入的部分（即学号、

15、姓名、语文、数 学、英语 5 列下的区域）外，其余的部分均保护，其中平均分、总分和等级的公式应隐藏。注：保护工作表 的设置只要设前两页工作表（sheet1,sheet2） 。 完成后，将模板文件作业上交到 class2,你们班级的文件夹下的“练习 8”文件夹下。 最后，打开自己创建的模板，并输入一些具体的数值，观察结果，体会模板以及你在模板文件中所作的设置 的作用。Excel 综合练习91按下列要求对 工作表进行操作，结果存盘。 （1）用函数计算各门成绩的平均分，要求保留两位小数。 （2）将大于等于 85 的成绩用粗体及双下划线显示。 92按下列要求对 工作表进行操作，结果存盘。 1、用公式计

16、算价值，并将数据格式与价格的数据格式相同。 2、将“题目”工作表的日期为 1995-12-31 的数据复制到 Sheet2 中，并将 Sheet2 改名为“1995 年末数据” 。 93按下列要求对 工作表进行操作，结果存盘。 1、将标题设置为黑体，24 号字；并将其进行从 C1 至 H1 的合并及居中。 2、将所有的数值格式设置为货币型，千分位并留两位小数。 95. 按下列要求对 工作表进行操作，结果存盘。 （1）用公式计算合计。 （2）按商品名称进行升序排列，并按商品名称对合计进行分类求和。 97. 按下列要求对 工作表进行操作，结果存盘。 1.在 H 列中，增加合计列；并用公式计算出逐年

17、的合计。 2.生成一个销售收入与销售成本的柱型图表，最大刻度为 1400，主要刻度单位为 200。 98. 按下列要求对 工作表进行操作，结果存盘。 1. 用公式计算出各位职工的工资总额，并删除职称为“临时工”的所有职工。 2. 给职称为“高工”的职工的“补贴”增加 50 元。 99. 按下列要求对 工作表进行操作，结果存盘。 1、用公式计算出一季度销售的总数。 2、将所有数值单元格色设置为千分位分隔样式，列宽为 18。 100. 按下列要求对 工作表进行操作，结果存盘。 1、为所有数据添加双线框。 2、从 E 列开始建立一个销售额大于等于 40000 的数据清单，标题在第一行。 103. 按

18、下列要求对 工作表进行操作，结果存盘。 1、在“上官云”后插入一行，数据为：胡斐，70，90，56 2、从 I 列开始，建立一个有不及格成绩学生的数据清单，标题在第一行。 104. 按下列要求对 工作表进行操作，结果存盘。 1、在 D19 中用函数计算出保费收入的总计，并将 A19 到 C19 进行合并及居中。 2、从 G 列开始建立一个人寿保险的数据清单，标题在第一行。 105. 按下列要求对 工作表进行操作，结果存盘。 1、将标题设置为合并及居中，粗体，18 号，双下划线。 2、将所有产品代号为 AB-123 的数据复制到 Sheet2 中，并改名为“AB-123 销售情况” 。 109.

19、 按下列要求对 工作表进行操作，结果存盘。 1、将标题设置为合并及居中，字体为黑体，18 号。 2、用函数计算出每月农产品的总销售量。 110. 按下列要求对 工作表进行操作，结果存盘。 1、利用函数计算业绩评价。 （大于等于 40000 的为“优秀” ，小于 40000 的为“一般” ） 2、按照销售额进行降序排列。 111. 按下列要求对 工作表进行操作，结果存盘。 （1）在 A8 中输入“合计” ，并在 B8,C8 中用函数计算出金额总计与构成总计。 （2）建立一个饼图,数据区为 A1:B7。 112. 按下列要求对 工作表进行操作，结果存盘。 1、将险种作为主关键字进行升序排列。 2、

20、对险种进行分类求和。 114按下列要求对 工作表进行操作，结果存盘。 （1）用函数计算各门成绩的最高分。 （2）删除“熊天平”这一行，并为所有的单元格设置双线框。 116按下列要求对 工作表进行操作，结果存盘。 （1） 把“单价“与“金额“字段设置成货币样式，货币符号为￥，小数点后保留 1 位。 （2） 将数量超过 1000 的数据在第 G 列开始的单元格中生成数据清单，标题在第一行。118按下列要求对 工作表进行操作，结果存盘。 （1）建立一个柱型图表，数据列为“去年实际”与“本年实际” 。 （2）将所有数据的格式设为千分位分隔样式，并为所有单元格添加双线边框。 119按下列要求对 工作表进

21、行操作，结果存盘。 1、将数据的第一列设置为居中，其他全部为右对齐。 2、建立一个折线图表，用于比较三个月的农产品的销售情况，最小刻度为 5000。 121按下列要求对 工作表进行操作，结果存盘。 1、将价格的数据格式设置为货币样式，并将列宽设置为 20。 2、利用 A1:C6 的数据，建立折线一个折线图表。 123按下列要求对 工作表进行操作，结果存盘。 1. 将“性别”为“T”的所有性别字段用“男”来替换； 124按下列要求对 工作表进行操作，结果存盘。 1. 将“性别”为“T”的所有性别字段用“男”来替换； “性别”为“F”的所有性别字段用“女”来替换。 2.按照奖学金的多少由小到大进行

22、排序，如果相同，则按照出生年月,由小到大进行排列。129按下列要求对 工作表进行操作，结果存盘。 1、用公式计算出总差额（不是单股差价，而是总股数的差值） 。 2、将第一行居中对齐，并将其他数据右对齐。 135. 按下列要求对 工作表进行操作，结果存盘。 1.求出每人的总成绩，并放入字段“总分”中。 2.显示数学和语文成绩都大于或者等于 90 分的学生名单，并复制到 I 至 L 列上。要求复制的标题行在第二 行上 139. 按下列要求对 工作簿工作表进行操作，结果存盘 1.在第一条记录后，插入一条新记录：文学 2.17 10 21.7。 2.建立该工作表的柱型图表,最大刻度为 1000。 14

23、4. 按下列要求对 工作表进行操作，结果存盘。 1.修改工作表，在“职称”字段后新增加一个“教龄”字段。 2.把所有年龄减 20，分别存入“教龄”字段中。 145. 1、删除表格的最后三行。 2、将当前工作表复制成一新的工作表，新工作表取名为“new” 。 149. 1、在表格尾追加一行，内容是“总分 各科成绩的总分” 。 2、将表格的各科成绩锁定。 151. 1、用函数求出学生每门课的“平均分” 。 2、删除赵安顺的所有成绩。 155. 1.根据“工作时间”字段，计算工龄，工龄填入“工龄”字段，工龄计算至 1998 年工龄=1998- “工作时间” 的年份 ,例如 工作时间=1996.12.

24、12 ,则 工龄=1998-1996=2 2.以“工龄”为主关键字按升序， “姓名”为次关键字按降序进行排序。 162. 按下列要求对 工作表进行操作，结果存盘。 1.统计女职工人数(要求使用函数） ，并把结果放入字段名为“人数” 的第一个记录中。 2.在 I 至 M 中显示已婚妇女表格,要求复制的标题行在第二行上163. 按下列要求对 工作表进行操作，结果存盘。 1、修改工作表，增添字段“简历” 。 2、在 工作表中 H 到 J 列 增添一个只包含“博士”的数据清单。要求复制的标题行在第一行上166. 按下列要求对工作表进行操作，结果存盘。 1. 在 J 至 P 列上，复制至少有一门不及格的

25、记录。要求复制的标题行在第一行上 2.在原有数据清单中删除课程有不及格的记录171. 按下列要求对 工作表进行操作，结果存盘。 1.在年龄大于 40 且小于 60 的记录中，其分类内容用“中年”替换。 2.求平均年龄(结果取整数)，存放在字段“合计”第一条记录中。 （要求使用函数）NIT 考试练习题之一考试练习题之一一、新建一个工作表（1001.xls）,用以下素材制作一个表格，表格的要求如下：_序号,用户姓名,所在公司,电话,购买产品的种类1,李国庆,整流器厂,64933376,继电器2,王大名,化工厂,63323456,开关3,宋力,合成纤维,64432211,继电器4,赵飞,轧钢厂,62

26、214345,电缆5,王起,自行车厂,63333542,继电器_（1）表格的左上角从“序号”开始， “序号”放在 A2 单元格；（2）把客户信息素材中的数据填充到表格中；（3）把表格所在区域 的所有边框线设置成可见（单线） ；（4）完成以上表格后，保存该工作簿。二、：（1）按部门和出生日期进行排序，部门为主要关键字降序，出生日期为次关键字升序。（2）将你的工作表改名为“人员基本情况” 。三、(1)计算每笔款项的余额(使用单元格地址方式)(提示:余额=收入-支出+上次余额).(2)为了使用方便,请您使用冻结拆分窗口命令将表格的列标题以上各行冻结.(3)将本工作表中含有公式的单元格进行保护,使其不

27、可修改,但不要加密码.四、 （1）将工作表名称改为“1 月” 。（2）算出每个职工的“应发额”与“实发额” 。再算出最后一行的合计值。（3）复制三张工作表，分别命名为“2 月” 、 “3” 、 “汇总表” 。（4）在“汇总表”计算出所有数据项的 1-3 月的汇总值（利用 SUM 函数） 。五、根据比赛成绩,为所有参加锻炼的人员评定一个等级.还要为参赛者打分,以便计算各部门的总分,评比长跑优秀部门.评定等级和打分的标准保存在工作表“评定标准“中.（1）使用 LOOKUP 函数计算出“等级“和“分数“列的值.（2）对工作表的“分数”列按“部门”进行分类汇总。六、先在 G3 起的区域创建一个数据透视

28、表， “国家”在行， “类别”在列,汇总数据为“张数“求和。再根据该数据透视表，在工作表的区域 G10：M24 嵌入柱形统计图表，该图表应显示美国和中国的故事片和动画片的合计值。（1）选择柱形统计图表，子图表类型取第一种。（2）分类轴为“美国”和“中国” ，系列为“故事片”和“动画片” 。（3）图表标题：个人 VCD 管理图表。（4）图表区背景色设为蓝白双色的过渡色。（5）绘图区格式的填充效果，选择预设“雨后初晴” 。NIT 考试练习题之二考试练习题之二一、 (1)对“抗洪救灾捐赠财物统计表“统计表按专业进行分类汇总,汇总字段为“人民币“和“衣物“.(2)设置标题所在行标题所在行的行高为 40

29、,标题为黑体,字号 18,在表格上方合并居中,垂直居中。除标题以外的表格部分使用自动套用格式是“经典” 。二、 (1)将学员序号改写为 001-010.(2)计算出学员的总分(SUM 函数).(3)计算出学员的平均分(四舍五入函数与平均值函数的嵌套,保留两位小数).(4)对工作表进行保护,使得记录区域内容可以修改,其他单元格不能被修改,(不要设置密码).三、(1)在“总计“行中,利用自动求和按钮,计算各月的总销售额.(2)在合计列中,利用自动求各按钮,计算机各销售部及总计的三个月的销售额合计.(3)在存放总月平均销售额的单元格(D11)中,利用函数,计算五个销售部三个月的平均月销售额.(4)在

30、“月平均额“列中,利用函数,计算机各销售部前三个月的平均销售额.(5)在“奖金系数“列中,利用函数,按下述原则计算各销售部的奖金系数:如果该部的月平均销售额低于或等于全公司的本季度总月平均销售额,则奖金系数为 1.如果该部的月平均销售额高于全公司的本季度总月平均销售额,则奖金系数为 1.5.四、 (1)将数据表单部分命名为“SJK“.(2)采用 DSUM 函数分类汇总下列数据:1.采用 DSUM 函数,分类汇总 Windows 系统环境中的“网络“类总张数,分别限定区域 G4:H5 和单元 I5 作为数据库分类汇总条件区域和 DSUM 函数输入单元.2.采用 DSUM 函数,分类汇总 Windows 系统环境中的“娱乐“类总张数,分别限定区域 G8:H9 和单元 I9 作为数据库分类汇总条件区域和 DSUM 函数输入单元.3.采用 DSUM 函数,分类汇总 Windows 系统环境中的“文化“类总张数,分别限定区域 12:H13 和单元 I13 作为数据库分类汇总条件区域和 DSUM 函数输入单元.