电子表格Excel2000.doc

第五章 电子表格Excel2000本章教学目的与教学要求 教学目的： 1、掌握 Excel2000 的启动与退出操作。 2、熟练掌握工作表的建立和编辑方法。 3、掌握工作薄的管理和编辑方法。 4、熟练掌握图表处理方法。 5、掌握数据管理的操作。 教学要求： 1、领会工作薄、工作表、单元格的基本概念。 2、熟练应用工作表的编辑方法（数据输入及修改、自定义序列及填充、公式和函数、数据的编辑、工作表格式化等）。 3、掌握工作薄的管理和编辑。 4、综合应用创建和编辑图表。 5、掌握数据管理与分析的过程。一、Excel 2000基本知识1) Excel(文件扩展名为“.xls”)2) Excel的特点P.144.3) Excel的启动与退出4) Excel的工作窗口工作簿窗口上方的水平条称为编辑栏（包括：名称框、编辑工具按钮、编辑框），活动单元格的单元格引用（地址）总是出现在编辑栏的名称框中，在编辑栏中总是显示当前活动单元格的内容或公式，用户可以对当前活动单元格的内容或公式进行编辑；工作表区域是Excel的主窗体，其每个单元格由列号和行号来确定；工作表标签位于工作簿文档窗口的左下底部，当标签栏显示不下所有标签时(Excel中每个工作簿可以包含1255个工作表)，可单击标签栏左侧滚动箭头使标签滚动，快速找到工作表；拆分框可用来同时查看同一工作表的不同部分。5) 创建、打开和保存文件二、建立和编辑工作表1) 单元格选择：在任意时刻（包括在选中区域时），Excel97中的活动单元格只有一个P.148.表5-1（、Home、Ctrl+Home、PageUp、PageDown、Enter、Shift+Enter、Tab、Shift+Tab）使用鼠标或键盘（Tab键和光标键）选择单元格；选择连续单元格区域：用鼠标直接拖动或使用shift和光标组合键进行选择；选择不连续单元格区域：用鼠标和Ctrl选择；编辑菜单定位，输入要选择的单元格地址或地址范围；*对选定单元格区域使用自动汇总功能：在状态栏上使用鼠标进行定义。2) 输入、编辑数据：2.1 输入数值用英文半角输入数字09运算符：+、-、*、/、%、 ；=、 =、 =、 2.2文字连接符：& 2.3 输入日期时间2000/10/1 2000-10-1输入分数，如：8/9，方法是：输入0空格8/92.4 数字组成的文本邮政编码：5280002.5 在单元格中换行格式单元格对齐自动换行。2.6 输入序列1.输入等差序列 选起始单元格输入初值选定区域编辑填充序列等差序列、步长。 例：2000-10-1为星期天，列出后面9个星期天日期。 在A1中输入初值：2000-10-1区域：A1:A10编辑填充序列序列：列、类型：日期、步长：7。2.输入等比序列 例：2，4，8，16，32，64，128，1024。（思考）3.输入数字与文本的组合序列 例：1990年、1991年、。（提示：在类型中选择“自动填充”）4.按下鼠标右键进行序列输入* 快捷菜单填充数据序列选起始单元格输入初值单击起始单元格填充柄按下鼠标右键拖动黑“+”字光标到结束单元格释放鼠标填充序列 。2.7 自定义序列1.从工作表导入选定序列工具选项自定义序列导入。2.直接在自定义序列中建立序列工具选项自定义序列输入序列添加。例：Windows98，Word97，Excel97，PowerPoint97；计算机，主机，显示器，打印机，键盘，鼠标，音箱。2.8 自动填充1.复制填充拖动填充柄进行复制填充。2.填充增1、减1序列按Ctrl键，拖动填充柄填充序列，向右、下增1、向左、上减1。*2.9 有效数据1.设置有效条件数据有效性设置许可：数字、选择条件。例如：日期介于2000-1到2003-1。*2.10 输入到同组单元格选中一组单元格输入数据按Ctrl+Enter键。*2.11 输入到同组工作表建立工作组：包含 Sheet1、Sheet2、Sheet3；当前窗口为Sheet1，按住Ctrl选中Sheet2、Sheet3；在Sheet1工作表输入数据，如 Excel 97，其它两个工作表同样出现 Excel 97。2.12复制/粘贴复制单元格或单元格区域的内容的方法：1.鼠标箭头指向单元区域边缘+“Ctrl”；2.菜单：编辑复制、粘贴；3.工具按钮：复制、粘贴；4.快捷键：“Ctrl”+C，“Ctrl”+V。5.快捷菜单：复制、粘贴；2.13 选择性粘贴选择性复制单元格中的公式、格式或数值。复制选定区指定粘贴区编辑选择性粘贴公式、格式或数值。三、工作表的管理与格式化1) 工作表的删除、插入和重命名：操作前都要对工作表进行选定操作“插入”菜单工作表；“编辑”菜单删除工作表（不能恢复的操作）；重命名：鼠标双击工作表标签输入工作表名回车2) 复制和移动工作表：“编辑”菜单移动和复制工作表，选择目标工作簿选择操作是复制还是移动；（Ctrl）鼠标键拖动源工作表至目标工作表，将（复制）移动工作表至目标工作表前。3) 工作表的拆分：水平、垂直、水平和垂直拆分选中行、列或单元格窗口菜单拆分；双击拆分线或利用窗口菜单撤销拆分窗口可以撤销拆分。4) 工作表的冻结：水平、垂直、水平和垂直冻结窗口菜单冻结拆分窗口5) 工作表的格式化：（P155）5.1 数字格式1.改变数字的小数位数、千分位格式。工具栏增加/减少小数位、千位分隔样式。5.2 字符格式字符格式：字体、字号、字形、下划线、颜色。5.3 时间和日期格式格式单元格数字日期类型。5.4 自动套用格式选定表格区域格式自动套用格式。*5.5 条件格式选定表格区域格式条件格式条件1：单元格数值为小于60分红色字；条件2：单元格数值为大于或等于60分绿色字。5.6 合并单元格格式单元格对齐合并单元格。5.7 数据的对齐与旋转1.数据的对齐与旋转格式单元格对齐水平对齐/方向。2.跨列居中*5.8 控制单元格内容的缩进工具栏增加/减小缩进量。5.9 控制行高和列宽1.自动调整：双击行标、列标分隔线。2.鼠标拖动：鼠标指针移到行标、列标间变成双向箭头时拖动。3.菜单调整：格式列列宽输入列宽值。5.10 添加边框1.取消网格线：工具选项视图网格线。2.添加边框：格式单元格边框；或工具栏边框按钮。5.11 添加底纹和图案格式单元格图案。四、公式与函数（P158）1) 输入公式：选择要输入公式的单元格输入“” 输入公式回车或单击编辑栏中的“输入”按钮，在单元格中显示计算结果。（使用相对地址和绝对地址）1.1公式的组成以（=）开始的引导符、数字、单元格引用和运算符。1.2 Excel 97 公式中的运算符1.数学运算符：+、-、*、/、%、 ；2.比较运算符：=、 =、 =、 ，结果为TRUE或FALSE；例如：85 结果为(TRUE), 35 结果为(FALSE).3.文本运算符：&，如 =Win & dows 的结果为 Windows。4.引用运算符：:冒号：区域运算符，如 A2:A9，=SUM(B3:C5)。,逗号：联合运算符，如 =SUM(B3,B15,D5:D15)。空格*：交叉运算符，两单元区域公有部分，如 =SUM(A1:C3 B2:D4)=SUM(B2:C3)=SUM(B2,C2,B3,C3)。A1 B2 C2 B3 C3 D4 5.运算符的优先级引用运算符：: 空格 ，算术运算符：- % */ + -连接运算符：&比较运算符：= = 1.3 绝对引用(P159) 绝对引用：公式所引用的单元格固定不变。 如，A3=$A$1+$A$21.4 相对引用(P159)相对引用：公式所引用的单元格跟随当前行列变化。如，A3=A1+A2 把公式A3 复制到 B3 后，列增1 成为B3=B1+B2 A列 B列 C列 D列 1行 A1 B1 C1 D1 2行 A2 B2 C2 D2 3行 A3 B3 C3 D3 1.5 混合引用及各种引用间的切换（P159）1.混合引用：A3=A$1+A$22.各种引用间的切换按功能键F4 第1次$A$1 第2次A$1 第3次$A1 第4次A1 行 绝对引用 绝对引用 相对引用 相对引用 列 绝对引用 相对引用 绝对引用 相对引用 *1.6 日期和时间的运算1.日期运算 =1995-10-20-1995-8-92.时间运算 =11:09:09-5:09:002) 函数：函数包含三个部分：函数名、圆括号、参数；2.1 函数的输入1. 使用函数向导（P162）插入函数fx ；或工具栏函数按钮fx常用函数AVERAGE键入求平均值的数据区域或用鼠标拖动选定区域。2.键盘输入函数（P162）=SUM(A1:A10)3.使用公式选项板单击编辑公式按钮单击公式选项板下拉钮选择函数，如，AVERAGE()选定单元区域。2.2 自动求和（P162）选存和数的单元格常用工具栏自动求和按钮“”。2.3 常用函数（P160）常用函数：SUM、COUNT、AVERAGE、INT、MAX、IF。数学与三角函数：SUM、INT、ROUND、MOD、SQRT。统计函数：COUNT、AVERAGE、MAX、STDEV。文本函数：MID。逻辑函数：IF、NOT、AND、OR。日期与时间函数：DATE、TIME、NOW、YEAR。财务函数：PMT 还贷款额函数。1. 和函数 SUM（P127）SUM 是数学与三角函数。2. 计数函数 COUNT（P128）COUNT 是统计函数，统计工作表中有效数据个数。例如：COUNT(A3:G6)3. 算术平均值函数 AVERAGE（P129）AVERAGE 是统计函数。4. 取整函数 INT（P131）INT 是数学与三角函数。例如：INT(0.99)=05. 舍入函数 ROUND（P131）ROUND 是数学与三角函数，按指定位数舍入。例如：ROUND(2.15,1)=2.26. 最大值函数 MAX（P132）MAX 是统计函数。例如：MAX(3,5)=53) *公式和函数的深入使用3.1工作表之间引用数据1.工作表间的函数和公式运算举例：把 Sheet1 的 A1 与 Sheet2 的 B2 数据和存放在 Sheet3 的 C3 中。操作：选定 Sheet3 的 C3 单元格工具栏函数按钮fx常用函数SUMNumber1，如 Sheet1!A1；Number2，如 Sheet2!B2。2.工作簿间的函数和公式运算举例：把 Book1.xls 中 Sheet1 的 A1与 Book2.xls 中 Sheet2 的 B2 数据和存放在 Book3.xls 中 Sheet3 的 C3 中。操作：打开工作簿，如 Book1.xls, Book2.xls, Book3.xls，窗口平铺；选定Book3.xls 中 Sheet3 的 C3 单元格工具栏函数按钮fx常用函数SUM Number1，如 Book1.xlsSheet1!$A$1； Number2，如 Book2.xlsSheet2!$B$2。4) 错误值的含义1.错误值 #!单元格显示不下。2.错误值 #VALUE!输入数字或逻辑值时，错为文本。3.错误值 #DIV/0! 除数引用了“空”或“0”的单元格。4.错误值 #NAME?删除了在公式中使用的名称，或使用了不存在的名称。如，在公式中含有全角引号“”、逗号，。5.错误值 #N/A在函数或公式中没有可用数值。6.错误值 #REF!单元格引用无效。7.错误值 #NUM!函数的数字参数错用了其它参数。8.错误值 #NULL!使用了不正确的区域运算符或不正确的单元格引用。9.公式错误疑难解答避免出错应注意的事项：圆括号要成对，引用单元格区域要正确使用区域运算符，函数要输入必选参数，函数嵌套不能超过7级，引用的工作簿或工作表名称中包含非字符，名称要加单引号。外部引用要包含工作簿的路径和名称。公式输入数字不要加格式符号，如 $1,000，只输入1000。五、数据管理1）数据排序：（P164）数据菜单排序定义主、次、第三关键字和排序方式确定。在浏览Excel表格中的数据时，为了查找的方便，我们经常要对数据进行排序。下面，我们以“员工工资表”为例，来看看具体的操作过程： 职工号姓名职称工龄基本工资奖金水电气实发工资101李芳副教授20880280110.5102王刚教授301200310125103周舟讲师1075021088104吴双副教授15920260101105张峰讲师7788160971、快捷排序：如果我们希望对员工资料按“工龄”由长到短进行排列，可以这样操作：选中“工龄”列任意一个单元格，然后按一下“常用”工具栏上的“降序排序”按钮即可。 注意：如果按“常用”工具栏上的“升序排序”按钮，则将“工龄”由短到长进行排序。如果排序的对象是中文字符，则按“汉语拼音”顺序排序。如果排序的对象是西文字符，则按“西文字母”顺序排序。 2、多条件排序：如果我们需要按“基本工资、职称、工龄”对数据进行排序，可以这样操作：选中数据表格中任意一个单元格，执行“数据排序”命令，打开“排序”对话框，将“主要关键词、次要关键词、第三关键词”分别设置为“基本工资、职称、工龄”，再设置一下排序方式（“升序”或“降序”），然后按下“确定”按钮就行了。 注意：在“排序”对话框中，应根据数据表的具体情况，确定选中“有标题行”或“无标题行”选项（默认为“有标题行”。）2）数据筛选：数据菜单筛选自动筛选（筛选只是暂时隐藏不满足条件的记录，而不是删除）。Excel中提供了两种数据的筛选操作，即“自动筛选”和“高级筛选”。如何区分这两种筛选模式，以便熟练掌握和应用，让我们来看看吧：2.1 自动筛选（P165）“自动筛选”一般用于简单的条件筛选，筛选时将不满足条件的数据暂时隐藏起来，只显示符合条件的数据。图1为某单位的职工工资表，打开“数据”菜单中“筛选”子菜单中的“自动筛选” 命令，以“基本工资”字段为例，单击其右侧向下的列表按钮，可根据要求筛选出基本工资为某一指定数额或筛选出基本工资最高（低）的前10个（该数值可调整）记录。还可以根据条件筛选出基本工资在某一范围内符合条件的记录，“与”、“或”来约束区分条件。如图2，根据给定条件筛选出基本工资大于等于300且小于350的记录。另外，使用“自动筛选”还可同时对多个字段进行筛选操作，此时各字段间限制的条件只能是“与”的关系。如筛选出“基本工资”和“职务工资”都超过380的记录。*2.2 高级筛选“高级筛选”一般用于条件较复杂的筛选操作，其筛选的结果可显示在原数据表格中，不符合条件的记录被隐藏起来；也可以在新的位置显示筛选结果，不符合的条件的记录同时保留在数据表中而不会被隐藏起来，这样就更加便于进行数据的比对了。例如我们要筛选出“基本工资”或“职务工资”超过380且“实发”工资超过700的符合条件的记录，用“自动筛选”就无能为力了，而“高级筛选”可方便地实现这一操作。将“基本工资”、“职务工资”和“实发”三字段的字段名称复制到数据表格的右侧（表格中其他空白位置也可以），在图中所示位置输入条件，条件放在同一行表示“与”的关系，条件不在同一行表示“或”的关系。3）分类汇总：（P166）选定要汇总的列数据菜单分类汇总图1所示是我们日常工作中经常接触到Excel二维数据表格，我们经常需要通过需要根据表中某列数据字段（如“工程类型”）对数据进行分类汇总。下面我们针对两种不同的分类汇总需求，介绍不同的解决方案。这两种需求分别是：既想分类汇总又想分类打印、不想分类打印只是想随时查看各类数据的明细和统计情况（此处假定将统计结果保存在另外一个工作表中）。需求一、既想分类汇总，又想分类打印解决方案：直接利用Excel内置的“分类汇总”功能来实现。 1、选中工程类型列的任意一个单元格，按一下“常用”工具栏上的“升序排序”或“降序排序”按钮，对数据进行一下排序。 注意：使用“分类汇总”功能时，一定要按分类对象进行排序！ 2、执行“数据分类汇总”命令，打开“分类汇总”对话框（如图2）。 3、将“分类字段”设置为“工程类型”；“汇总方式”设置为“求和”；“选定汇总项”为“面积”和“造价”；再选中“每组数据分页”选项。最后，确定返回。 4、分类汇总完成（图3是“综合办公”类的打印预览结果）。 需求二、不想分类打印，只是想随时查看各类数据的明细和统计情况解决方案：利用Excel自身的“自动筛选”功能来实现。 1、任意选中数据表格中的某个单元格，执行“数据筛选自动筛选”命令，进入“自动筛选”状态。 2、分别选中F203、G203单元格（此处假定表格中共有200条数据），输入公式：=SUBTOTAL(9,F3:F202)和=SUBTOTAL(9,G3:G202)。 小提示：此函数有一个特殊的功能，就是后面进行自动筛选后，被隐藏行的数据不会被统计到其中，达到分类统计的目的。 3、以后需要随时查看某类（如“经济住宅”）数据的明细和统计情况时，点击“工程类型”