Excel 工作表操作与图表制作Word格式.docx
《Excel 工作表操作与图表制作Word格式.docx》由会员分享，可在线阅读，更多相关《Excel 工作表操作与图表制作Word格式.docx（21页珍藏版）》请在冰点文库上搜索。
1000的单元格设置为蓝色、加粗倾斜、灰色-25%底纹，基本工资<
500的单元格设置为红色、加单下划线。
结果如图8-1-3所示。
图8-1-3 工作表格式化结果
③根据“姓名”、“基本工资”、“奖金”、“津贴”、“实发工资”列（不包含总计）生成数据点折线图，图表标题为“金鑫公司员工工资情况”，X轴标题为“员工姓名”，Y轴标题为“人民币（元）”，嵌入到当前工作表中；
删除图表中“津贴”数据系列；
将图表标题字体设置为蓝色、黑体、12磅，将图例位置改为“靠上”。
结果如图8-1-4所示。
图8-1-4 金鑫公司员工工资数据点折线图
④以文件名E2.xls另存到盘或其它存储介质上以备后用。
（2）操作提示：
①计算“实发工资”时可以直接输入公式，计算第一位员工“刘晓晓”实发工资的公式为“＝F3＋G3＋H3-I3”，其他员工的实发工资利用公式自动填充的方法完成；
计算“总计”可以先选定求和结果单元格，再单击“常用”工具栏的按钮
，在下拉菜单中单击“求和”命令了；
也可以利用Sum函数或直接输入公式。
②工资表格式化第a题可以全部在“单元格格式”对话框中设置完成，打开该对话框的方法是：
选定单元格按右键，在快捷菜单中单击“设置单元格格式”命令。
其中：
表头即“姓名”所在行；
合并单元格在“对齐”标签中设置；
实发工资列的数值格式应选择“数字”标签“货币”分类进行相应设置，如图8-1-5所示，添加千位分隔符是在“数值”分类中进行设置，如图8-1-6所示。
工作表边框设置应在“边框”标签中先选定线条的颜色和样式，再单击“预置”栏的相应按钮完成。
图8-1-5 “实发工资”数值格式设置
图8-1-6 添加千位分隔符
③工资表格式化第b题可利用“格式|行”、“格式|列”中的相应命令完成。
④工资表格式化第c题使关于格式条件的设置。
选定“基本工资”列中第一位至最后一位员工的数据，选择“格式|条件格式”命令，在“条件格式”对话框中进行相应设置，如图8-1-7所示。
图8-1-7 “基本工资”条件格式设置
⑤对数据进行图表化，首先应明确需要选定工作表中的哪些数据？
图表的类型是什么？
本案例中需选定5列数据：
“姓名”、“基本工资”、“奖金”、“津贴”、“实发工资”，注意不包含“总计”行，因为是不连续的多列数据，所以在选定“姓名”列后，应按住“Ctrl”键，再选中其它3列数据。
单击“常用”工具栏的按钮
或选择“插入|图表”命令启动图表向导后，在第1步对话框中选择相应的图表类型，如图8-1-8所示。
图8-1-8 选择图表类型—数据点折线图
⑥对图表进行编辑和格式化，首先要弄清楚图表中的各个对象，选择所需的对象通过快捷菜单或快捷键进行相应操作。
本案例图表编辑操作只需选定“津贴”数据系列，如图8-1-9所示，按“Delete”键。
如果图表区要增加一个系列，只要选定相应系列数据，直接拖动到图表区即可。
本案例图表格式化时，需要双击“图表标题”和“图例”两个图表对象在打开的对话框中进行相应设置。
图例格式对话框设置如图8-1-10所示。
图8-1-10 设置图例显示位置
2.实验练习（设计性实验）
依照实验案例，制作班级成绩表（不少于15位同学），栏目、数据自己设计，要求能够实现案例使用的所有功能。
实验8.2 公式和函数实验
8.2.1实验目的
（1）掌握在工作表中应用公式的方法。
（2）掌握在工作表中应用函数的方法。
8.2.2实验内容
实验案例1 在工作表1中完成下列操作
①在区域A2∶A21中用函数输入[40,100]的随机整数；
②在区域B2∶B21中用函数计算出与A2∶A21对应随机整数的平方根值；
③在区域C2∶C21中用函数计算出以A2∶A21对应随机整数为直径的圆面积；
④在区域D2∶D21中用函数计算出与A2∶A21对应随机整数的奇偶性。
对应的整数为奇数时输出文本“奇”，为偶数时输出文本“偶”。
图8.2-1 工作表1
操作步骤：
①在单元格A2中输入公式＝40＋INT（RAND（）*61），然后拖动单元格A2的填充柄将公式复制到A3∶A21；
②在单元格B2中输入公式＝SQRT（A2），然后双击单元格B2的填充柄将公式复制到B3∶B21；
③在单元格C2中输入公式＝PI（）*（A2/2）^2，然后双击单元格C2的填充柄将公式复制到C3∶C21；
④在单元格D2中输入公式=IF（MOD（A2,2）=0,"
偶"
"
奇"
），然后双击单元格D2的填充柄将公式复制到D3∶D21。
案例2 在工作表2中完成下列操作
在区域G2∶G27中用函数计算每个职工的工龄工资，工龄在5年以下的（含5年），每年的工龄工资为5元，5年以上的每年工龄工资为10元；
图8.2-2 工作表2
在单元格G2中输入公式=IF（F2<
=5,F2*5,F2*10），然后双击单元格G2的填充柄将公式复制到G2∶G27。
案例3 在工作表3中完成下列操作
区域A1∶D12为计算机水平考试获奖学生清单。
设奖励规则为：
①若笔试成绩和机试成绩均不低于80分，则奖励等级“优秀”；
②若笔试成绩或机试成绩低于80分，但二者平均分不低于80分，则奖励等级为“良好”；
③若平均分不足80分，则奖励等级为“及格”。
根据学生的考试成绩在区域D3∶D12中用公式计算出相应的奖励等级。
图8.2-3 工作表3
在单元格D3中输入公式
＝IF（AND（B3>
＝80，C3>
＝80），″优秀″，IF（AND（OR（B3<
80，C3<
80），AVERAGE（B3∶C3）>
＝80），″良好″，″及格″）），然后双击单元格D3的填充柄将公式复制到D4∶D12。
或在单元格D3中输入公式
＝80），″优秀″，IF（AVERAGE（B3∶C3）<
80，″及格″，″良好″）），然后双击单元格D3的填充柄将公式复制到D4∶D12。
案例4 在工作表4中完成下列操作
区域A1∶B11给出了某次考试的分数。
设计算成绩的规则为：
若分数不小于全体学生的平均分数，则成绩为“合格”，否则为“不合格”。
根据学生的成绩在区域C2∶C11中用公式计算出相应的成绩；
图8.2-4 工作表4
在单元格C2中输入公式＝IF（B2>
＝AVERAGE（$B$2∶$B$11），″合格″，″不合格″），然后双击单元格C2的填充柄将公式复制到C3∶C11。
案例5 在工作表5中完成下列操作
在区域F2∶F28中用函数计算每个学生的平均分；
在区域G2∶G28中用函数计算每个学生的总分；
在区域H2∶H28中用IF函数计算每个学生的综合分，综合分的评分标准为：
文科学生的语文成绩占综合分的70%、数学成绩占30%，理科学生的语文成绩占30%、数学成绩占70%。
图8.2-5 工作表5
①在单元格F2中输入公式＝AVERAGE（D2∶E2），然后双击单元格F2的填充柄将公式复制到F3∶F28；
②在单元格G2中输入公式＝SUM（D2∶E2），然后双击单元格G2的填充柄将公式复制到G3∶G28；
③在单元格H2中输入公式=IF（C2=$J$3,SUM（D2*$K$3,E2*$L$3）,SUM（D2*$K$4,E2*$L$4）），然后双击单元格H2的填充柄将公式复制到H3∶H28。
案例6 在工作表6中完成下列操作
在区域B5∶D5中用函数计算出相应科目最高分同学的姓名。
要求区域B5∶D5的姓名会随区域A1∶D4中的数据一同变化。
图8.2-6 工作表6
在单元格B5中输入公式＝IF（B2＝MAX（B2∶B4），$A2，IF（B3＝MAX（B2∶B4），$A3，$A4）），然后将公式复制到C5∶D5。
案例7 在工作表7中完成下列操作
在单元格G3中输入公式计算税金：
税金交纳规则为将年工资总额分段计算，15000元及以下部分扣税金0%,15001～25000元部分扣税金5%,25001元及以上部分扣税金10%，然后复制到G4∶G17，“税金”的数值取小数点后2位。
图8.2-7 工作表7
①在单元格F3中输入公式
=IF（E3<
=15000,0,IF（E3<
=25000,（E3-15000）*5%,10000*5%+（E3-25000）*10%）），然后将公式复制到F4∶F17。
②选中区域F3∶F17，将数据格式设置为数值取小数点后2位。
案例8：
在工作表8中完成下列操作
在D3∶D12单元格计算“存款年数”，并按照下表所给的年储蓄利率，在区域E3∶E12中根据定期存款的期限和存入金额用公式计算出存款到期时的支取金额；
图8-8 工作表8
1在单元格D3中输入公式=YEAR（C3）-YEAR（H$1），然后将公式复制到D4∶D12；
②在单元格E3中输入公式=B3+B3*D3*IF（D3=1,H$4,IF（D3=3,H$5,H$6））*（1-H$7），然后将公式复制到E4∶E12。
案例9 在工作表9中完成下列操作
①用公式计算各位顾客的“体重指数”，“体重指数”等于“体重”除以“身高”平方；
图8.2-9 工作表9
②用公式计算各位顾客的“体形”类别，如果“体重指数”大于25为：
“肥胖”，“体重指数”小于19为“瘦削”，其余为“正常”；
③把“身高”和“体重指数”的数据设置为带2位小数格式。
①在单元格F4中输入公式＝E4/D4^2，然后将公式复制到E5∶E9；
②在单元格G4中输入公式＝IF（F4>
25，″肥胖″，IF（F4<
19，″瘦削″，″正常″）），然后将公式复制到F5∶F9。
③选中区域D4∶D9和区域E4∶E9，将数据格式设置为数值取小数点后2位。
案例10 在工作表10中完成下列操作
区域A1∶C28给出了一个救灾捐款清单，根据此清单中的内容在区域F2∶G7的各单元中计算出相应的内容；
图8.2-10 工作表10
①在单元格F3中输入公式＝COUNTIF（B$3∶B$28，E3），然后将公式复制到F4∶F7；
②在单元格G3中输入公式＝SUMIF（B$3∶B$28，E3，C$3∶C$28），然后将公式复制到G4∶G7。
案例11 在工作表11中完成下列操作
东莞市电话号码由七位升八位的规则为：
①若原号码首位为2，则新号码在原号码前加2；
②若原号码首位为1或9，则原号码不变；
③若原号码首位既非2非1又非9，则新号码在原号码前加8。
根据区域B3∶B12中的原号码在区域C3∶C12中用公式计算出相应的新号码；
图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）），然后将公式复制到C4∶C14。
案例12 在工作表12中完成下列操作
根据C2∶C28数据清单中所列的年龄，在区域D2∶D28中计算出每个人的年龄排名，排名规则为：
年龄最长者排名为1，然后随年龄递减而排名依次加1，若年龄相同则排名也相同，但该排名之后的名次仍应为比其名次靠前的总人数加1。
图8.2-12 工作表12
在单元格D2中输入公式＝RANK（C2，C$2∶C$28,0），然后将公式复制到D3∶D28。
案例13 在工作表13中用数据库统计函数完成下列操作
①在H1单元内计算出所有上半年出生人员得分的总和；
（DSUM）
②在H2单元内计算出所有50年代出生人员得分的平均分；
（DAVERAGE）
③在H3单元内计算出所有少数民族的人员总数；
（DCOUNTA或DCOUNT）
④在H4单元内计算出所有汉族医生的最高得分；
（DMAX）
⑤在H5单元内计算出所有大学本科人员的最低得分。
（DMIN）
①A.在区域G7∶G13中建立比较条件区域。
B.在区域17∶18中建立计算条件区域。
身份证号码
?
01?
02?
03?
04?
05?
06?
或
tj
=MID（A3,9,2）<
="
06
在单元格H1中输入公式＝DSUM（A2∶E28，E2，G7∶G13）；
图8.2-13 工作表13
②A.在区域G15∶G16中建立比较条件区域B.在区域I15∶I16中建立计算条件区域。
5?
=MID（A3,7,1）="
5"
在单元格H2中输入公式＝DAVERAGE（A2∶E28，E2，G15∶G16）；
③在区域G18∶G19中建立比较条件区域：
民族
<
>
汉
在单元格H3中输入公式＝DCOUNTA（A2∶E28，B2，G18∶G19）；
④在区域G21∶H22中建立比较条件区域
职业
医生
在单元格H4中输入公式＝DMAX（A2∶E28，E2，G21∶H22）；
⑤在区域G24∶G25中建立比较条件区域：
文化程度
大学本科
在单元格H5中输入公式＝DMIN（A2∶E28，E2，G24∶G25）。
8.2.3实验练习（设计性实验）
1.在工作表14中完成下列操作：
①在E3单元格填入公式，计算出累积金牌数，然后复制到E4∶E18区域；
②在F3单元格填入公式，计算出累积奖牌数，然后复制到F4∶F18区域。
图8.2-14 工作表14
2.在工作表15中完成下列操作
根据区域A1∶B28中的数据清单，在区域F3∶F8中使用频率分布函数计算出各分数段的人数。
图8-2-15 工作表15
升级会员 