(精品)电子表格软件Excel.ppt

《(精品)电子表格软件Excel.ppt》由会员分享，可在线阅读，更多相关《(精品)电子表格软件Excel.ppt（58页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、第第5章章 电子表格软件电子表格软件Excel 2003的使用的使用 日常生活和工作中，经常需要处理数据和表格，Microsoft Office Excel 2003具有强大的数据计算、分析和处理功能，并且可以完成各种统计图表的绘制，已经广泛地用于金融、财务、企业管理、行政管理等领域。15.1 认识Excel 20035.2实践案例1工资表编制5.3实践案例2学生成绩分析25.1 认识认识Excel 2003 Excel文件称为工作簿，其默认文件名为Book1（依次为Book2，Book3），扩展名为.xls。每个工作簿中可以创建多张工作表，每张工作表可以存储不同的数据。Excel 2003工

2、作窗口如图5.1所示。345.1.1 数据编辑5.1.2 公式和函数5.1.3 图表的建立5.1.4 其他功能55.1.1 数据编辑数据编辑 1数据输入 活动单元格 移动“活动单元格 2数据修改3单元格的选取与复制（1）单元格的选取：连续区域的选择、非连续区域的选择。（2）单元格的复制：选取了单元格后，单击【常用】工具栏上【复制】按钮；把鼠标定位到要复制到单元格区域的起始单元格，单击【粘贴】按钮。64数据的清除、删除与插入1）数据清除 2）数据的删除 删除单元格：选定一个或多个单元格，单击鼠标右键，在弹出的快捷菜单中选择【删除】选项，弹出【删除】对话框，可选取“右侧单元格左移”或“下方单元格上

3、移”，如图5.3所示。我们要删除第3、4行第一个单元格，并且让“下方单元格上移”，然后单击【确定】按钮，下方的单元格向上移动，第6、7行内单元格没有数据了。7 删除行（列）：选中要删除的一行（列）或多行（列），单击鼠标右键，在弹出的快捷菜单中选择【删除】选项，即可以把这些行（列）删除。单元格的插入、行（列）的插入，与单元格、行（列）的删除操作类似。85.单元格格式设置（1）数字分类设置：【格式】菜单下选择【单元格】选项，弹出【单元格格式】对话框 名 称说 明常规系统默认格式数值适用一般数字，可设置“小数位数”、“使用千位分隔符”、“负数”表示方式货币与数值类型相似，不过可以在数值前面添加货币符

4、号会计专用与货币相似，还可以进行货币符号和小数点对齐显示日期提供多种日期格式，并提供了不同国家的日期格式时间提供多种时间格式，并提供了不同国家的时间格式百分比以百分数的形式显示单元格数值，并可以设置小数位数科学计数数值以科学计数法表示，可以设置小数位数文本任何格式数据都可以以文本类型处理特殊提供特殊格式的选择，如邮政编码、电话号码等自定义用户可以根据自己需要自定义格式9（2）条件格式设置 题目：将图5.1的工作表中，价格大于50的单元格内容以斜体、加粗、红色显示，步骤如下：1）选择要设置格式的区域，即选择D列的第37行单元格，单击【格式】菜单，选择【条件格式】选项。在弹出的【条件格式】对话框中

5、的【条件1】选项区域中，选择“单元格数值”、“大于”，在最后的文本框中输入“50”，然后单击【格式】按钮，如图5.4所示。图5.4 条件设置图5.5 符合条件的单元格格式设置 102）在弹出的【单元格格式】对话框中，选择【字体】选项卡，将【字形】设置为“加粗，倾斜”，将【颜色】设置为“红色”，设置效果如图5.5所示。11（3）自动套用格式步骤：选择单元格区域第27行有数据的区域，单击【格式】菜单，选择【自动套用格式】选项，在【自动套用格式】对话框中选择【古典2】格式（见图5.6）。最后单击【确定】按钮，完成自动套用格式设置，设置效果如图5.7所示。图5.6 自动套用格式选择图5.7 自动套用格

6、式后效果返回125.1.2 公式和函数1）相对引用地址：这种方式的地址引用，会因为公式所在的位置的变化而发生对应的变化，例如某公式中引用了“A3”单元格，当该公式复制到其他单元格时，此公式中的相对地址会随之发生变化。2）绝对引用地址：这种方式的地址引用，地址不会因为公式所在的位置的变化而发生变化，当该公式复制到其他单元格时，此公式中的绝对地址是不会发生变化的，其引用方式是在单元格地址的列名、行标前都加上“$”符号，例如某公式中绝对引用了“A3”单元格，公式中引用的地址应写成“$A$3”。3）混合引用地址：如果我们需要固定某列而变化某行，或是固定某行而变化某列的引用时，采用混合引用地址，其表示方

7、式为“$A3”或“A$3”。131运算符（1）算术运算符（6个）算术运算符的作用是完成基本的数学运算，包括加（）、减（）、乘（*）、除（/）、百分数（）和乘方（）。（2）比较操作符（6个）比较运算符的作用是可以比较两个值，结果为一个逻辑值，是“TRUE”或是“FALSE”。包括等于（=）、大于（）、小于（、大于等于（=）、小于等于（=）和不等于（）。14（3）文本连接符（1个）使用文本连接符（）可连接一个或更多个字符串以产生一长文本。例如：“2008年”“北京奥运会”就产生“2008年北京奥运会”。（4）引用操作符（3个）冒号（：）连续区域运算符，对两个引用之间（包括两个引用在内）的所有单元格

8、进行引用。如SUM（B5：C10），计算B5到C10的连续12个单元格之和。逗号（，）联合操作符，可将多个引用合并为一个引用。如SUM（B5：B10，D5：D10），计算B列、D列共12个单元格之和。空格取多个引用的交集为一个引用，该操作符在取指定行和列数据时很有用。如SUM（B5：B10 A6：C8），计算B6到B8三个单元格之和。152常用函数 Excel 2003函数一共有11类，分别是数据库函数、日期与时间函数、工程函数、财务函数、信息函数、逻辑函数、查询和引用函数、数学和三角函数、统计函数、文本函数以及用户通过Visual Basic编辑器创建的自定义函数。（1）求和函数SUM格式：

9、SUM（number1,number2,）功能：返回参数对应的数值的和。16（2）求平均值函数AVERAGE格式：AVERAGE（number1,number2,）功能：返回参数对应的数值的平均值。（3）求最大值函数MAX、最小值函数MIN格式：MAX（number1,number2,）、MIN（number1,number2,）功能：返回参数对应的数值的最大值、最小值。17（4）四舍五入函数ROUND格式：ROUND（number,num_digits）功能：返回参数number按指定位数num_digits的四舍五入值。如果num_digits=0，则四舍五入到整数；如果num_digit

10、s0,G3-SUM(I3:K3)-2000,0)”，按Enter键，Excel 2003会自动计算出员工“王遐”的4月份应税金额。406）计算“个人所得税”。与不同的“应税金额”相对应的税率和速算扣除数，如表5.3所示。利用IF函数的嵌套功能来实现个人所得税的计算。在单元格M3中输入公式“=IF(L3=500,L3*5%,IF(L3=2000,L3*10%-25,IF(L3=5000,L3*15%-125,IF(L3=20000,L3*20%-375,IF(L3=40000,L3*25%-1375,IF(L3=60000,L3*30%-3375,IF(L3=80000,L3*35%-6375,

11、IF(L3100 00045%15 375表5.3 税率及速算扣除数计算规则表 图5.56 “个人所得税”的计算417）计算“实发金额”。“实发金额”是员工在缴纳各类保险、税收等各类款项后实际发到手里的工资金额。很显然，“实发金额”=“应发金额”“失业保险”“医疗保险”“养老保险”“个人所得税”。在N4中输入公式“=G3-SUM(I3:K3)-M3”，按Enter键，Excel 2003会自动计算出员工“王遐”的实发金额为3031.20元。428）完成其他员工工资计算。因为其他员工未计算的工资分布在两个区域中，我们分两步完成。选中G3单元格，按住鼠标左键向下拖动单元格右下角的填充柄，直到最后一

12、位员工所在单元格（“G14”），松开鼠标左键，完成其他员工“应发金额”的计算。选中I3：N3单元格区域，按住鼠标左键向下拖动单元格右下角的填充柄，直到最后一位员工所在行，松开鼠标左键，完成其他员工“失业保险”、“医疗保险”、“养老保险”、“应税金额”、“个人所得税”、“实发金额”的计算，如图5.57所示。图5.57 其他员工工资的计算 返回435.3.4 数据排序 要按员工所在部门进行统计，所以我们需要对“部门”进行排序，同一个部门里的员工，我们按“实发金额”由高到低排序。排序步骤如下：1）选取除标题行外所有区域的单元格，在【数据】菜单下选择【排序】选项，弹出【排序】对话框。2）在【排序】对话

13、框中，从【主要关键字】的下拉列表中选择“部门”，并选择排序方式为“降序”；从【次要关键字】的下拉列表中选择“实发金额”，并选择排序方式为“降序”；并在【我的数据区域】中选择“有标题行”，被选中的区域中第一行（即表头行）就不会参与排序了，如图5.58所示。3）单击【确定】按钮，完成排序，排序结果如图5.59所示。从中可以看到，首先按“部门”的字典音序降序排列，顺序为“销售部”、“网络集成部”、“软件开发部”；同一部门中，再按“实发金额”从高到低降序排列。图5.59 进行数据排序后的结果 图5.58 排序关键字的设置返回445.3.5 进行列隐藏 比如“应税金额”项主要是在计算个人所得税时做辅助用

14、的，我们可以把该列隐藏起来。步骤如下：1）选中L列（“应税金额”项所在列），单击【格式】菜单，在其下拉菜单中选择【列】|【隐藏】选项。L列被隐藏2）此时，L列被隐藏起来了，效果如图5.60所示。L列被隐藏图5.60 L列被隐藏后的结果 返回455.3.6 数据自动筛选 要求在工资表中实现对员工的“姓名”、“部门”、“职务”和“岗位工资”的查询，操作步骤如下：1）选中B2：E2单元格区域，单击【数据】菜单，选择【筛选】|【自动筛选】选项，可以看到工作表中B2：E2列（“姓名”、“部门”、“职务”和“岗位工资”）右侧出现了自动筛选箭头 。2）如果要查看“网络集成部”所有员工的工资信息，可单击C2单

15、元格的筛选箭头，在其下拉列表中选择“网络集成部”（见图5.61），得到筛选结果，如图5.62所示。图5.61 选择自动筛选条件 图5.62 选择自动筛选条件 463）如果要查看职务中属于“经理”一级的员工的工资信息，则需要单击D2单元格的筛选箭头，选择“自定义”选项，在弹出的【自定义自动筛选方式】对话框中，在【显示行】的【职务】选项区左边下拉列表中选择“包含”，在右边的下拉列表文本框中输入“经理”。4）单击【确定】按钮，进行自动筛选，筛选结果将列出“职务”中包含“经理”两个字（如部门经理、项目经理等）的员工的工资信息，如图5.63所示。5）若要显示全部的记录，则单击被筛选项（筛选箭头为蓝色，本

16、例中是D2）箭头，在其下拉列表中选择“全部”选项即可。图5.63 自定义筛选的结果返回475.3.7 数据分类汇总1平均值汇总1）单击工作表中任一单元格，再选择【数据】|【分类汇总】选项，弹出【分类汇总】对话框。2）在【分类字段】下拉列表中选择“部门”；在【汇总方式】下拉列表中选择“平均值”；在【选定汇总项】列表中的“实发金额”前的复选框中打上“”，并清除其他复选框，选中“替换当前分类汇总”和“汇总结果显示在数据下方”复选框，清除“每组数据分页”复选框，如图5.64所示，即可以进行各部门员工“实发金额”平均值的分类汇总。3）单击【确定】按钮，即完成部门员工“实发金额”平均值的分类汇总，如图5.

17、65所示。图5.64 平均值分类汇总 图5.65 平均值分类汇总结果 482求和汇总 如果要在平均值汇总的基础上，再按部门进行“实发金额”求和汇总，其操作步骤如下：1）单击工作表中任一单元格，再选择【数据】|【分类汇总】选项，弹出【分类汇总】对话框。2）在【分类字段】下拉列表中选择“部门”；在【汇总方式】下拉列表中选择“求和”；在【选定汇总项】列表中的“实发金额”前的复选框中打上“”，并清除其他复选框，清除“每组数据分页”和“替换当前分类汇总”复选框，此时“汇总结果显示在数据下方”自动变为灰色，即可以得到各部门员工“实发金额”求和的分类汇总。3）单击【确定】按钮，即完成部门员工“实发金额”平均

18、值、求和的分类汇总，效果如本节开头部分的图5.42所示。返回495.3.8 制作工资条 浙江丰达计算机有限公司还需要把工资表打印成“工资条”发给每位员工。下面我们用Excel2003的“宏”功能，自动将编制好的工作表转换为工资条。上面我们曾经进行过“排序”、“数据自动筛选”、“数据分类汇总”的步骤，制作工作条之前，我们先把工作表复原。1）删除“分类汇总”。单击工作表中任一单元格，再选择【数据】|【分类汇总】选项，弹出【分类汇总】对话框中，单击【全部删除】按钮，这样就取消了“分类汇总”。2）删除“自动筛选”。单击工作表中任一单元格，再选择【数据】|【筛选】|【自动筛选】选项，“自动筛选”前面的“

19、”清除了，“自动筛选”功能也就被删除了。3）按“员工编号”排序。单击“员工编号”列任一单元格，再单击【常用】工具栏中的【降序排列】按钮，这样工资表中数据就按照“员工编号”升序排列。504）在工资表中，选择【工具】|【宏】|【录制新宏】选项，如图5.69所示，弹出【录制新宏】对话框。5）在【录制新宏】对话框中，将【宏名】设置为“制作工资条”，在【快捷键】文本框中输入“g”，设定Ctrl+G作为快捷键，如图5.70所示，再单击【确定】按钮返回工作表。6）选择【工具】|【宏】|【停止录制】选项，这样就完成了一个空的宏的录制。图5.69 录制新宏图5.70 【录制新宏】对话框 517）选择【工具】|【

20、宏】|【宏】选项，打开【宏】对话框，选择刚刚录制的“制作工资条”宏，然后单击【编辑】按钮，如图5.71所示，打开了Visual Basic编辑器。8）在Visual Basic编辑器中输入如下代码：Sub 制作工资条()Selection.CurrentRegion.Offset(1,0).Select Cells(Selection.Row,Selection.Column).Select Range(Selection,Selection.End(xlToRight).Select Selection.Copy ActiveCell.Offset(2,0).Range(A1).Select

21、 Do Until ActiveCell=Selection.Insert Shift:=xlDown Range(Selection,Selection.End(xlToRight).Select Selection.Copy ActiveCell.Offset(2,0).Range(A1).Select LoopApplication.CutCopyMode=FalseEnd Sub图5.71 【宏】对话框 529）代码输入完毕后，单击Visual Basic编辑器中【标准】工具栏中的【视图Microsoft Excel】按钮 ，返回工作表。10）选中工作表中任一单元格，选择【工具】|【宏

22、】|【宏】选项，打开【宏】对话框。选中刚刚录制的“制作工作条”宏后，单击【执行】按钮，完成每位员工自动添加表头的功能，效果如本节开篇处图5.43所示。返回535.3.9 为工资表添加密码 工资表中的信息对于企业来说是一个较为重要的信息，是比较隐私的信息，既不允许随意被改动，也不能轻易被其他员工看到，因此，为其设置打开权限密码是必要的。只有工资表的使用者才可以打开或修改次工作表。1）选择【工具】菜单下【选项】选项，在弹出的【选项】对话框中选择【安全性】选项卡，在其中的【打开权限密码】文本框中输入密码，如图5.72所示。2）同样，可以在【修改权限密码】文本框中设定修改权限密码，只有知道此密码的使用

23、者才可以修改工资表的权限。图5.72 设置【打开权限密码】543）单击【确定】按钮，打开了【确认密码】对话框，在【重新输入密码】文本框中再次输入密码确认，如图5.73所示；如果设定了【修改权限密码】，还会弹出一个【确认密码】对话框进行【重新输入修改权限密码】设置。4）在【确认密码】对话框中单击【确定】按钮，如果输入的密码正确，对话框就会被关闭，完成打开、修改权限密码设置，返回工作表。图5.73 输入“确认密码”555）关闭工作簿，再次打开“4月份员工工资表.xls”工作簿时，打开前会弹出【密码】对话框，如图5.74所示，输入正确的打开权限密码后，可以浏览工作表信息，否则弹出“您所提供的密码不正

24、确”的警告对话框，无法进入工作簿。6）如果还设置了【修改权限密码】，则输入打开权限密码后，还会弹出一个【密码】对话框，如图5.75所示。如果单击【只读】按钮，则进入工作表，可以浏览工作表内信息，但不能进行工作表信息的修改；如果在【密码】文本框中输入正确的“修改权限密码”，单击【确定】按钮后进入工作表，既可以浏览工作表信息，也可以对工作表进行修改。图5.74 打开权限【密码】对话框 图5.75 修改权限【密码】对话框565.3 教学案例2学生成绩分析案例综述 本案例是对学生的成绩进行相关统计和分析，主要完成如下工作：单元格内容的输入数据的简单运算修改单元格插入单元格数据快速输入技巧 图5.42 制作完成的工作表57总评成绩的计算名次计算其他的统计条件格式设置设置数据有效性、输入数据对数据进行自动筛选对数据进行高级筛选对表中数据进行分类汇总图表创建图表基本操作58