Excel 工作表操作与图表制作Word格式.docx

1、1000的单元格设置为蓝色、加粗倾斜、灰色-25%底纹，基本工资500的单元格设置为红色、加单下划线。结果如图8-1-3所示。图8-1-3工作表格式化结果根据“姓名”、“基本工资”、“奖金”、“津贴”、“实发工资”列（不包含总计）生成数据点折线图，图表标题为“金鑫公司员工工资情况”，X轴标题为“员工姓名”，Y轴标题为“人民币（元）”，嵌入到当前工作表中；删除图表中“津贴”数据系列；将图表标题字体设置为蓝色、黑体、12磅，将图例位置改为“靠上”。结果如图8-1-4所示。图8-1-4金鑫公司员工工资数据点折线图以文件名E2.xls另存到盘或其它存储介质上以备后用。（2）操作提示：计算“实发工资”时

2、可以直接输入公式，计算第一位员工“刘晓晓”实发工资的公式为“F3G3H3-I3”，其他员工的实发工资利用公式自动填充的方法完成；计算“总计”可以先选定求和结果单元格，再单击“常用”工具栏的按钮，在下拉菜单中单击“求和”命令了；也可以利用Sum函数或直接输入公式。工资表格式化第a题可以全部在“单元格格式”对话框中设置完成，打开该对话框的方法是：选定单元格按右键，在快捷菜单中单击“设置单元格格式”命令。其中：表头即“姓名”所在行；合并单元格在“对齐”标签中设置；实发工资列的数值格式应选择“数字”标签“货币”分类进行相应设置，如图8-1-5所示，添加千位分隔符是在“数值”分类中进行设置，如图8-1-

3、6所示。工作表边框设置应在“边框”标签中先选定线条的颜色和样式，再单击“预置”栏的相应按钮完成。图8-1-5“实发工资”数值格式设置图8-1-6添加千位分隔符工资表格式化第b题可利用“格式|行”、“格式|列”中的相应命令完成。工资表格式化第c题使关于格式条件的设置。选定“基本工资”列中第一位至最后一位员工的数据，选择“格式|条件格式”命令，在“条件格式”对话框中进行相应设置，如图8-1-7所示。图8-1-7“基本工资”条件格式设置对数据进行图表化，首先应明确需要选定工作表中的哪些数据？图表的类型是什么？本案例中需选定5列数据：“姓名”、“基本工资”、“奖金”、“津贴”、“实发工资”，注意不包含

4、“总计”行，因为是不连续的多列数据，所以在选定“姓名”列后，应按住“Ctrl”键，再选中其它3列数据。单击“常用”工具栏的按钮或选择“插入|图表”命令启动图表向导后，在第1步对话框中选择相应的图表类型，如图8-1-8所示。图8-1-8选择图表类型数据点折线图 对图表进行编辑和格式化，首先要弄清楚图表中的各个对象，选择所需的对象通过快捷菜单或快捷键进行相应操作。本案例图表编辑操作只需选定“津贴”数据系列，如图8-1-9所示，按“Delete”键。如果图表区要增加一个系列，只要选定相应系列数据，直接拖动到图表区即可。本案例图表格式化时，需要双击“图表标题”和“图例”两个图表对象在打开的对话框中进行

5、相应设置。图例格式对话框设置如图8-1-10所示。图8-1-10设置图例显示位置2.实验练习（设计性实验）依照实验案例，制作班级成绩表（不少于15位同学），栏目、数据自己设计，要求能够实现案例使用的所有功能。 实验8.2公式和函数实验8.2.1实验目的（1）掌握在工作表中应用公式的方法。 （2）掌握在工作表中应用函数的方法。8.2.2实验内容 实验案例1在工作表 1 中完成下列操作在区域A2A21中用函数输入40,100的随机整数；在区域B2B21中用函数计算出与A2A21对应随机整数的平方根值；在区域C2C21中用函数计算出以A2A21对应随机整数为直径的圆面积；在区域D2D21中用函数计算

6、出与A2A21对应随机整数的奇偶性。对应的整数为奇数时输出文本“奇”，为偶数时输出文本“偶”。图8.2-1工作表 1操作步骤： 在单元格A2中输入公式 40INT（RAND（）*61），然后拖动单元格A2的填充柄将公式复制到A3A21； 在单元格B2中输入公式 SQRT（A2），然后双击单元格B2的填充柄将公式复制到B3B21； 在单元格C2中输入公式 PI（）*（A2/2）2，然后双击单元格C2的填充柄将公式复制到C3C21； 在单元格D2中输入公式 =IF（MOD（A2,2）=0,偶,奇），然后双击单元格D2的填充柄将公式复制到D3D21。案例2在工作表 2 中完成下列操作在区域G2G27

7、中用函数计算每个职工的工龄工资，工龄在5年以下的（含5年），每年的工龄工资为5元，5年以上的每年工龄工资为10元；图8.2-2工作表 2 在单元格G2中输入公式 =IF（F280，C380），优秀，IF（AND（OR（B380，C380），良好，及格），然后双击单元格D3的填充柄将公式复制到D4D12。或在单元格D3中输入公式80），优秀，IF（AVERAGE（B3C3）AVERAGE（$B$2$B$11），合格，不合格），然后双击单元格C2的填充柄将公式复制到C3C11。案例5在工作表 5 中完成下列操作在区域F2F28中用函数计算每个学生的平均分；在区域G2G28中用函数计算每个学生的总分

8、；在区域H2H28中用IF函数计算每个学生的综合分，综合分的评分标准为：文科学生的语文成绩占综合分的70%、数学成绩占30%，理科学生的语文成绩占30%、数学成绩占70%。图8.2-5工作表 5在单元格F2中输入公式 AVERAGE（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 中完成下

9、列操作在区域B5D5中用函数计算出相应科目最高分同学的姓名。要求区域B5D5的姓名会随区域A1D4中的数据一同变化。图8.2-6工作表 6 在单元格B5中输入公式 IF（B2MAX（B2B4），$A2，IF（B3MAX（B2B4），$A3，$A4），然后将公式复制到C5D5。案例7在工作表 7 中完成下列操作在单元格G3中输入公式计算税金：税金交纳规则为将年工资总额分段计算，15000元及以下部分扣税金0%,1500125000元部分扣税金5%,25001元及以上部分扣税金10%，然后复制到G4G17，“税金”的数值取小数点后2位。图8.2-7工作表 7在单元格F3中输入公式 =IF（E3=1

10、5000, 0,IF（E325，肥胖，IF（F419，瘦削，正常），然后将公式复制到F5F9。选中区域D4D9和区域E4E9，将数据格式设置为数值取小数点后2位。案例10在工作表 10 中完成下列操作区域A1C28给出了一个救灾捐款清单，根据此清单中的内容在区域F2G7的各单元中计算出相应的内容；图8.2-10工作表 10 在单元格F3中输入公式 COUNTIF（B$3B$28，E3），然后将公式复制到F4F7；在单元格G3中输入公式 SUMIF（B$3B$28，E3，C$3C$28），然后将公式复制到G4G7。案例11在工作表 11 中完成下列操作东莞市电话号码由七位升八位的规则为：若原号码

11、首位为2，则新号码在原号码前加2；若原号码首位为1或9，则原号码不变；若原号码首位既非2非1又非9，则新号码在原号码前加8。根据区域B3B12中的原号码在区域C3C12中用公式计算出相应的新号码；图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，若年龄相同则排名也相同，但

12、该排名之后的名次仍应为比其名次靠前的总人数加1。图8.2-12工作表 12 在单元格D2中输入公式 RANK（C2，C$2C$28,0），然后将公式复制到D3D28。案例13在工作表 13 中用数据库统计函数完成下列操作在H1单元内计算出所有上半年出生人员得分的总和；（DSUM）在H2单元内计算出所有50年代出生人员得分的平均分；（DAVERAGE）在H3单元内计算出所有少数民族的人员总数；（DCOUNTA或DCOUNT）在H4单元内计算出所有汉族医生的最高得分；（DMAX）在H5单元内计算出所有大学本科人员的最低得分。（DMIN）A.在区域G7G13中建立比较条件区域。 B.在区域1718中

13、建立计算条件区域。身份证号码?01?02?03?04?05?06?或tj=MID（A3,9,2）=06在单元格H1中输入公式 DSUM（A2E28，E2，G7G13）；图8.2-13工作表 13 A. 在区域G15G16中建立比较条件区域B. 在区域I15I16中建立计算条件区域。5?=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