Excel 工作表操作与图表制作.doc

《Excel 工作表操作与图表制作.doc》由会员分享，可在线阅读，更多相关《Excel 工作表操作与图表制作.doc（15页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、实验8.1Excel 2003工作表操作与图表制作8.1.1实验目的(1) 熟练掌握工作表中数据的输入。(2) 熟练掌握在工作表中应用公式和函数的方法。(3)熟练掌握工作表的编辑和格式化操作。(4) 熟练掌握图表的创建、编辑和格式化。8.1.2实验内容1.实验案例：工资表(1)操作步骤：建立金鑫公司员工工资明细表，如图8-1-1所示，以文件名E1.xls保存在D盘下，并进行下列操作：图8-1-1金鑫公司员工工资明细表在工资表最右边增加一列“实发工资”和最下边增加一列“总计”，并将结果计算出来，如图8-1-2所示。图8-1-2计算实发工资和总计工资表格式化：a.将A1到G1单元格合并为一个；标题

2、内容水平居中对齐，标题字体为黑体、14号、加粗，表头文字楷体_GB2312、12号，水平和垂直方向上均居中对齐；实发工资列小数位2位、添加千位分隔符、人民币符号“￥”；工作表边框外框为黑色粗线，内框为黑色细线，表头单元格底纹为淡蓝色；b.标题行高为27，姓名列宽10，其他列宽为最适合的列宽。c.条件格式：基本工资1000的单元格设置为蓝色、加粗倾斜、灰色-25%底纹，基本工资500的单元格设置为红色、加单下划线。结果如图8-1-3所示。图8-1-3工作表格式化结果根据“姓名”、“基本工资”、“奖金”、“津贴”、“实发工资”列(不包含总计)生成数据点折线图，图表标题为“金鑫公司员工工资情况”，X

3、轴标题为“员工姓名”，Y轴标题为“人民币(元)”，嵌入到当前工作表中；删除图表中“津贴”数据系列；将图表标题字体设置为蓝色、黑体、12磅，将图例位置改为“靠上”。结果如图8-1-4所示。图8-1-4金鑫公司员工工资数据点折线图以文件名E2.xls另存到盘或其它存储介质上以备后用。(2)操作提示：计算“实发工资”时可以直接输入公式，计算第一位员工“刘晓晓”实发工资的公式为“F3G3H3-I3”，其他员工的实发工资利用公式自动填充的方法完成；计算“总计”可以先选定求和结果单元格，再单击“常用”工具栏的按钮，在下拉菜单中单击“求和”命令了；也可以利用Sum函数或直接输入公式。工资表格式化第a题可以全

4、部在“单元格格式”对话框中设置完成，打开该对话框的方法是：选定单元格按右键，在快捷菜单中单击“设置单元格格式”命令。其中：表头即“姓名”所在行；合并单元格在“对齐”标签中设置；实发工资列的数值格式应选择“数字”标签“货币”分类进行相应设置，如图8-1-5所示，添加千位分隔符是在“数值”分类中进行设置，如图8-1-6所示。工作表边框设置应在“边框”标签中先选定线条的颜色和样式，再单击“预置”栏的相应按钮完成。图8-1-5“实发工资”数值格式设置图8-1-6添加千位分隔符工资表格式化第b题可利用“格式|行”、“格式|列”中的相应命令完成。工资表格式化第c题使关于格式条件的设置。选定“基本工资”列中

5、第一位至最后一位员工的数据，选择“格式|条件格式”命令，在“条件格式”对话框中进行相应设置，如图8-1-7所示。图8-1-7“基本工资”条件格式设置对数据进行图表化，首先应明确需要选定工作表中的哪些数据？图表的类型是什么？本案例中需选定5列数据：“姓名”、“基本工资”、“奖金”、“津贴”、“实发工资”，注意不包含“总计”行，因为是不连续的多列数据，所以在选定“姓名”列后，应按住“Ctrl”键，再选中其它3列数据。单击“常用”工具栏的按钮或选择“插入|图表”命令启动图表向导后，在第1步对话框中选择相应的图表类型，如图8-1-8所示。图8-1-8选择图表类型数据点折线图 对图表进行编辑和格式化，首

6、先要弄清楚图表中的各个对象，选择所需的对象通过快捷菜单或快捷键进行相应操作。本案例图表编辑操作只需选定“津贴”数据系列，如图8-1-9所示，按“Delete”键。如果图表区要增加一个系列，只要选定相应系列数据，直接拖动到图表区即可。本案例图表格式化时，需要双击“图表标题”和“图例”两个图表对象在打开的对话框中进行相应设置。图例格式对话框设置如图8-1-10所示。图8-1-9图8-1-10设置图例显示位置2.实验练习（设计性实验）依照实验案例，制作班级成绩表（不少于15位同学），栏目、数据自己设计，要求能够实现案例使用的所有功能。实验8.2公式和函数实验8.2.1实验目的(1)掌握在工作表中应用

7、公式的方法。 (2)掌握在工作表中应用函数的方法。8.2.2实验内容 实验案例1在工作表 1 中完成下列操作在区域A2A21中用函数输入40,100的随机整数；在区域B2B21中用函数计算出与A2A21对应随机整数的平方根值；在区域C2C21中用函数计算出以A2A21对应随机整数为直径的圆面积；在区域D2D21中用函数计算出与A2A21对应随机整数的奇偶性。对应的整数为奇数时输出文本“奇”，为偶数时输出文本“偶”。图8.2-1工作表 1操作步骤： 在单元格A2中输入公式 40INT(RAND()*61)，然后拖动单元格A2的填充柄将公式复制到A3A21； 在单元格B2中输入公式 SQRT(A2

8、)，然后双击单元格B2的填充柄将公式复制到B3B21； 在单元格C2中输入公式 PI()*(A2/2)2，然后双击单元格C2的填充柄将公式复制到C3C21； 在单元格D2中输入公式 =IF(MOD(A2,2)=0,偶,奇)，然后双击单元格D2的填充柄将公式复制到D3D21。案例2在工作表 2 中完成下列操作在区域G2G27中用函数计算每个职工的工龄工资，工龄在5年以下的(含5年)，每年的工龄工资为5元，5年以上的每年工龄工资为10元；图8.2-2工作表 2操作步骤： 在单元格G2中输入公式 =IF(F280，C380)，优秀，IF(AND(OR(B380，C380)，良好，及格)，然后双击单元

9、格D3的填充柄将公式复制到D4D12。或在单元格D3中输入公式IF(AND(B380，C380)，优秀，IF(AVERAGE(B3C3)AVERAGE($B$2$B$11)，合格，不合格)，然后双击单元格C2的填充柄将公式复制到C3C11。案例5在工作表 5 中完成下列操作在区域F2F28中用函数计算每个学生的平均分；在区域G2G28中用函数计算每个学生的总分；在区域H2H28中用IF函数计算每个学生的综合分，综合分的评分标准为：文科学生的语文成绩占综合分的70%、数学成绩占30%，理科学生的语文成绩占30%、数学成绩占70%。图8.2-5工作表 5操作步骤：在单元格F2中输入公式 AVERA

10、GE(D2E2)，然后双击单元格F2的填充柄将公式复制到F3F28；在单元格G2中输入公式 SUM(D2E2)，然后双击单元格G2的填充柄将公式复制到G3G28；在单元格H2中输入公式 =IF(C2=$J$3,SUM(D2*$K$3,E2*$L$3),SUM(D2*$K$4,E2*$L$4)，然后双击单元格H2的填充柄将公式复制到H3H28。案例6在工作表 6 中完成下列操作在区域B5D5中用函数计算出相应科目最高分同学的姓名。要求区域B5D5的姓名会随区域A1D4中的数据一同变化。图8.2-6工作表 6操作步骤： 在单元格B5中输入公式 IF(B2MAX(B2B4)，$A2，IF(B3MAX

11、(B2B4)，$A3，$A4)，然后将公式复制到C5D5。案例7在工作表 7 中完成下列操作在单元格G3中输入公式计算税金：税金交纳规则为将年工资总额分段计算，15000元及以下部分扣税金0%,1500125000元部分扣税金5%,25001元及以上部分扣税金10%，然后复制到G4G17，“税金”的数值取小数点后2位。图8.2-7工作表 7操作步骤：在单元格F3中输入公式 =IF(E3=15000, 0,IF(E325，肥胖，IF(F419，瘦削，正常)，然后将公式复制到F5F9。选中区域D4D9和区域E4E9，将数据格式设置为数值取小数点后2位。案例10在工作表 10 中完成下列操作区域A1

12、C28给出了一个救灾捐款清单，根据此清单中的内容在区域F2G7的各单元中计算出相应的内容；图8.2-10工作表 10操作步骤： 在单元格F3中输入公式 COUNTIF(B$3B$28，E3)，然后将公式复制到F4F7；在单元格G3中输入公式 SUMIF(B$3B$28，E3，C$3C$28)，然后将公式复制到G4G7。案例11在工作表 11 中完成下列操作东莞市电话号码由七位升八位的规则为：若原号码首位为2，则新号码在原号码前加2；若原号码首位为1或9，则原号码不变； 若原号码首位既非2非1又非9，则新号码在原号码前加8。根据区域B3B12中的原号码在区域C3C12中用公式计算出相应的新号码；

13、图8.2-11工作表 11操作步骤： 在单元格C3中输入公式 IF(LEFT(B3,1)2，2&B3，IF(OR(LEFT(B3,1)1，LEFT(B3,1)9)，B3，8&B3)，然后将公式复制到C4C14。案例12在工作表 12 中完成下列操作根据C2C28数据清单中所列的年龄，在区域D2D28中计算出每个人的年龄排名，排名规则为：年龄最长者排名为1，然后随年龄递减而排名依次加1，若年龄相同则排名也相同，但该排名之后的名次仍应为比其名次靠前的总人数加1。图8.2-12工作表 12操作步骤： 在单元格D2中输入公式 RANK(C2，C$2C$28,0)，然后将公式复制到D3D28。案例13在

14、工作表 13 中用数据库统计函数完成下列操作在H1单元内计算出所有上半年出生人员得分的总和；(DSUM)在H2单元内计算出所有50年代出生人员得分的平均分；(DAVERAGE)在H3单元内计算出所有少数民族的人员总数；(DCOUNTA或DCOUNT)在H4单元内计算出所有汉族医生的最高得分；(DMAX)在H5单元内计算出所有大学本科人员的最低得分。(DMIN)操作步骤：A.在区域G7G13中建立比较条件区域。 B.在区域1718中建立计算条件区域。身份证号码?01?02?03?04?05?06?或tj=MID(A3,9,2)=06在单元格H1中输入公式 DSUM(A2E28，E2，G7G13)

15、；图8.2-13工作表 13 A. 在区域G15G16中建立比较条件区域B. 在区域I15I16中建立计算条件区域。身份证号码?5?或tj=MID(A3,7,1)=5在单元格H2中输入公式 DAVERAGE(A2E28，E2，G15G16)； 在区域G18G19中建立比较条件区域：民族汉在单元格H3中输入公式 DCOUNTA(A2E28，B2，G18G19)； 在区域G21H22中建立比较条件区域民族职业汉医生在单元格H4中输入公式 DMAX(A2E28，E2，G21H22)； 在区域G24G25中建立比较条件区域：文化程度大学本科在单元格H5中输入公式 DMIN(A2E28，E2，G24G25)。8.2.3实验练习（设计性实验）1.在工作表 14 中完成下列操作：在E3单元格填入公式，计算出累积金牌数，然后复制到E4E18区域；在F3单元格填入公式，计算出累积奖牌数，然后复制到F4F18区域。图8.2-14工作表 142.在工作表 15 中完成下列操作根据区域A1B28中的数据清单，在区域F3F8中使用频率分布函数计算出各分数段的人数。 图8-2-15工作表 15