EXCEL程序教程PPT

1、计算机应用基础计算机应用基础 普宁广播电视大学普宁广播电视大学 黄文辉黄文辉电子报表处理软件电子报表处理软件Excel学习目标 掌握在excel中输入数据、处理工作表、设置工作表格式的方法 掌握常用函数的使用方法 掌握图表的制作、工作表的打印和在excel中保护数据的方法 掌握数据的排序、筛选、分类汇总方法 掌握用excel进行数据的分析的方法 内容提要ExcelExcel工作表工作表8 建立基本概念建立基本概念8 工作表的操作与编辑工作表的操作与编辑8 公式与函数的应用公式与函数的应用8 打印工作表打印工作表Excel Excel 图表图表8 图表相关名词术语图表相关名词术语8 创建和修饰图

2、表创建和修饰图表数据管理数据管理8 数据清单概念数据清单概念8 数据排序数据排序8 筛选数据筛选数据8 分类汇总分类汇总重点和难点 重点 公式与函数函数的应用 数据的排序、筛选 图表的修改 难点 绝对引用和相对引用的区别 数据透视表的建立 Excel 概述Office家族成员之一家族成员之一电子报表软件电子报表软件表格标题表格标题数据数据文字文字数值数值自动填充自动填充公式公式函数函数图表图表图形对象图形对象数据清单数据清单排序排序筛选筛选分类汇总分类汇总自动处理自动处理直观显示直观显示功能功能建立基本概念建立基本概念 Excel 工作界面公式栏公式栏列号列号名字框名字框行号行号当前单元格当前

3、单元格工作表标签工作表标签工作表工作表工作簿工作簿建立基本概念建立基本概念1. 标题栏: 当前编辑的工作簿名。 2. 工作簿: 一般由3张表格组成一个工作簿，最多255张。3. 工作表： 工作的主要对象，由行、列组成的表格。 列 A,B.,IV(256)；行 1,2.65536。4. 单元格：行和列的交叉，表格的最小单位。5. 活动单元格：当前正在操作的单元格，被黑线框住。6. 工作表标签： Sheet1Sheet3，当前活动的白色，其余灰色。7. 标签滚动按钮： 利用标签按钮，切换显示工作表。8. 滚动栏： 纵、横。工作簿窗口 工作簿与工作表工作簿工作簿（Workbook）工作簿是计算和存储

4、数据的文件，也就是通常意义上工作簿是计算和存储数据的文件，也就是通常意义上的的Excel文件。每一个工作簿可由一个或多个工作表文件。每一个工作簿可由一个或多个工作表组成，在默认的情况下是由组成，在默认的情况下是由3个工作表组成的。个工作表组成的。工作表工作表（Worksheet）它是工作簿的一部分，也称作电子报表。它是工作簿的一部分，也称作电子报表。工作簿（工作簿（255）建立基本概念建立基本概念 认识电子表格数据规律排列数据规律排列引入公式实现引入公式实现自动重算自动重算建立基本概念建立基本概念 创建工作表（输入数据）输入：原始数据（中英文字符、数字）和公式输入：原始数据（中英文字符、数字）

5、和公式步骤步骤1 1：明确内容输入的单元格：明确内容输入的单元格当前活动单元格当前活动单元格步骤步骤2 2：内容输入（注意观察公式栏）：内容输入（注意观察公式栏）确认操作确认操作取消操作取消操作数据类型：文本、数字、日期和时间、逻辑值和出错值。数据类型：文本、数字、日期和时间、逻辑值和出错值。工作表的操作与编辑工作表的操作与编辑公式特定符号公式特定符号区域范围引用区域范围引用单元格地址单元格地址数值型常量数值型常量Excel函数函数运算操作符号运算操作符号工作表的操作与编辑工作表的操作与编辑三使用公式使用公式 公式形式：操作数和运算符公式形式：操作数和运算符 操作数：单元格、数字、字符、区域名

6、、区域操作数：单元格、数字、字符、区域名、区域 运算符：算术、运算符：算术、* *、% %（百分号）、（百分号）、（乘方）、（乘方）、（）（） 字符（文本连接）字符（文本连接） 关系关系 = =、 、=、=、 、引用运算符：引用运算符：1、:（冒号）区域运算符，产生对包括在两个引用之间的所有单元格的引用（冒号）区域运算符，产生对包括在两个引用之间的所有单元格的引用 (B5:B15)2、（逗号）联合运算符，将多个引用合并为一个引用、（逗号）联合运算符，将多个引用合并为一个引用 (SUM(B5:B15,D5:D15) 3、（空格）交叉运算符产生对两个引用共有的单元格的引用。、（空格）交叉运算符产生

7、对两个引用共有的单元格的引用。(B7:D7 C6:C8) 有关工作表的操作工作表的操作方法工作表的操作方法工作表的编辑工作表的编辑8编辑单元格数据（编辑单元格数据（F2F2或双击）或双击）8移动或拷贝数据（工具栏按钮或鼠标拖动）移动或拷贝数据（工具栏按钮或鼠标拖动）8有规律数据的快速填充有规律数据的快速填充8插入与删除行、列、区域插入与删除行、列、区域8选择工作表中的数据选择工作表中的数据8工作表的更名工作表的更名8插入与删除工作表插入与删除工作表8复制工作表复制工作表工作表的操作与编辑工作表的操作与编辑一、单元格选取一、单元格选取单个单元格：单击所需的单元格。单个单元格：单击所需的单元格。

8、连续多个单元格：单击首单元格，连续多个单元格：单击首单元格，Shift+Shift+单击末单元格。单击末单元格。 多个不连续单元格：多个不连续单元格：Ctrl+Ctrl+单击单元格。单击单元格。 整行或整列：单击行头或列头。整行或整列：单击行头或列头。 全部单元格：单击行头、列头相交的全选按钮。全部单元格：单击行头、列头相交的全选按钮。 清除选定的单元格：工作表任意处单击。清除选定的单元格：工作表任意处单击。 工作表的建立工作表的建立二、数据的输入1. 单元格数据的输入单击单元格直接在单元格输入数据双击单元格在编辑框内输入数据注意： 数值数字右对齐，字符左对齐，数字字符输入： 数字。 数值输入

9、超长，以科学记数法显示。 字符输入超长，扩展到右边列；若右边有内容，截断显示。 按Tab、Shift+Tab、 、 进行个单元格之间的移动。 2. 数据自动输入 (1)自动填充自动填充 。指一批有规律的数据指一批有规律的数据，例等差、等比数列例等差、等比数列 输入前2个数选中该2个单元格指向第2个单元格右下角的自动填充柄 拖曳填充柄到结束工具选项自定义序列在输入序列框依次输入序列，之间用逗号分隔(2) 系统提供的序列数据 例：甲、乙、丙.；?3、6、9.；星期一 、星期二. 方法：输入一个数据，利用自动填充柄拖曳到结束填充。(3) 用户自定义序列数据输入第一个数选中包含该数的区域设置填充序列编

10、辑/填充/序列等差比差浏览工作表的方法浏览工作表的方法工作表的修饰工作表的修饰 有关工作表的操作8用编辑键进行浏览（用编辑键进行浏览（PageUpPageUp、PageDownPageDown）8用用F5F5功能键快速定位到某个单元格功能键快速定位到某个单元格8分割屏幕法分割屏幕法( (冻结拆分窗口冻结拆分窗口) )8用工具栏中的按钮进行格式化用工具栏中的按钮进行格式化8工作表标题的处理工作表标题的处理8用用“格式格式/ /单元格单元格”命令修饰工作表命令修饰工作表8条件格式条件格式工作表的操作与编辑工作表的操作与编辑函数是函数是Excel自带的一些已经定义好的公式。自带的一些已经定义好的公式

11、。 如何在工作表中应用函数不同类型的函数要求给定不不同类型的函数要求给定不同类型的参数同类型的参数，代表函数功能代表函数功能= =（B2+B3+B4+B5+B6+B7+B8+B9+B10B2+B3+B4+B5+B6+B7+B8+B9+B10）/9/9= = AverageAverage（B2:B10B2:B10）公式与函数的应用公式与函数的应用 函数应用举例函数按功能分为以下几种类型：函数按功能分为以下几种类型：8 数学与三角函数（比如，数学与三角函数（比如，SUMSUM、 SUM IFSUM IF）8 统计函数（比如，统计函数（比如， AVERAGEAVERAGE、COUNTIFCOUNTI

12、F）8 逻辑函数（比如，逻辑函数（比如， IFIF）8 财务函数（比如，财务函数（比如， PMTPMT）8 日期和时间函数（比如，日期和时间函数（比如， TODAYTODAY）8 数据库函数（比如，数据库函数（比如， DCOUNTDCOUNT）8 查找与引用函数（比如，查找与引用函数（比如， VLOOKUPVLOOKUP）8 文本函数（比如，文本函数（比如， CONCATENATCONCATENAT）8 信息函数（比如，信息函数（比如， ISBLANKISBLANK）公式与函数的应用公式与函数的应用 Excel的函数（补充）类别常用函数功能统计函数CORREL返回单元格区域 array1 和

13、array2 之间的相关系数COUNTCOUNTA统计数字项的个数统计参数的个数（统计逻辑值、文字或错误值）FREQUENCY返回某个区域中数据的频率分布MEDIAN返回给定数值集合的中位数MODE求众数RANK返回一个数值在一组数值中的排位STDEV估算样本的标准偏差STDEVP返回以参数形式给出的整个样本总体的标准偏差VAR估算样本方差FORECAST根据给定的数据计算或预测未来值GROWTH根据给定的数据预测指数增长值 Excel的函数类别常用函数功能统计函数IF执行真假值判断，根据逻辑测试的真假值返回不同的结果COUNTIF计算给定区域内满足特定条件的单元格的数目RAND返回大于等于

14、0 小于 1 的均匀分布随机数TODAY返回当前日期的系列数，MONTH、YEAR、DAY返回以系列数表示的日期中的月份、年份、天数DATE返回代表特定日期的系列数 Excel的错误信息错误信息原因#！n公式所产生的结果太长，该单元格容纳不下n日期或时间格式的单元格中出现负值#DIV/0!公式中出现被零除的现象#N/A在函数或公式中没有可用数值时#NAME?在公式中出现excel不能识别的文本时#NULL!当试图为两个并不交叉的区域指定交叉点时#NUM!当函数或公式中某个数字有问题时#REF!当单元格引用无效时#VALUE!使用错误的参数或运算对象类型 单元格地址在公式中的作用地址：地址：A1

15、地址：地址：$A$1地址：地址：$A1 A$1E4=B1+A2+C2?公式的公式的功能基于参加计算的数据功能基于参加计算的数据均引用其存放位置（单元格地址）。均引用其存放位置（单元格地址）。公式与函数的应用公式与函数的应用 创建三维公式不同工作表中的数据所在单元格地址的表示：不同工作表中的数据所在单元格地址的表示：工作表名称工作表名称 单元格引用地址单元格引用地址不同工作簿中的数据所在单元格地址的表示：不同工作簿中的数据所在单元格地址的表示：工作簿名称工作簿名称 工作表名称工作表名称 单元格引用地址单元格引用地址=Sale1.xls 销售统计销售统计! $E$4+ Sale2.xls销售销售统

16、计统计! $E$4+ Sale3.xls 销售统计销售统计! $E$4 + Sale4.xls销售统计销售统计!$E$4公式与函数的应用公式与函数的应用数组公式（补充） 数组公式：对一组或多组值进行计算，返回一个或多个结果 数组公式的输入： 选中需要输入数组公式的单元格区域。 键入数组公式。 按 Ctrl+Shift+Enter。 打印工作表工作表的打印设置工作表的打印设置打印预览打印预览8设置页面设置页面8设置页边距设置页边距8设置页眉设置页眉/ /页脚页脚8设置工作表的选项设置工作表的选项打印工作表打印工作表 更直观地显示数据（图表）：数值数据的图形化：数值数据的图形化创建步骤：创建步骤：

17、选取数据选取数据插入图表插入图表8 独立图表独立图表8 嵌入图表嵌入图表编辑图表编辑图表修饰图表修饰图表数据表格数据表格图表相关名词术语图表相关名词术语图表相关名词术语图例图例数据标记数据标记数据系列数据系列类型轴类型轴 (X)刻度线刻度线值轴（值轴（Y）图表相关名词术语图表相关名词术语 编辑图表选择图表类型选择图表类型增加或删除图表元素增加或删除图表元素8图表标题图表标题8 X轴标题轴标题8 Y轴轴标题标题8 图表标记图表标记8 数据标签数据标签8 图例图例8 刻度线刻度线创建和修饰图表创建和修饰图表 修饰图表样例创建和修饰图表创建和修饰图表插入地图 条件: 1、需要有电子地图 2、原始数据

18、中需要有与电子地图匹配的数据列 步骤： 选定包含地名字段的一组数据，点击插入地图，出现“+”型的光标，拖动鼠标画一个显示地图的矩形空间即可 数据的组织与管理概念概念数据清单概念数据清单概念 数据清单正确创建规则：正确创建规则：列名唯一列名唯一纯数据（杜绝空行、非法数据）纯数据（杜绝空行、非法数据）同列数据的数据类型和格式应完全相同同列数据的数据类型和格式应完全相同避免在一张工作表上放置多份数据清单避免在一张工作表上放置多份数据清单数据清单概念数据清单概念使用使用“数据数据”/“记录单记录单”菜单命令，打开某数据菜单命令，打开某数据清单的记录单。清单的记录单。利用记录单可以进行下列操作：利用记录

19、单可以进行下列操作：8输入数据输入数据8修改和删除数据修改和删除数据8查找数据清单中的记录查找数据清单中的记录 使用记录单编辑数据清单数据清单概念数据清单概念排序条件：排序条件：的排序（简单排序）的排序（简单排序）的排序（轻重缓急）的排序（轻重缓急）数据类型：数据类型：升序：升序：降序：降序：文本文本A,B,Z Z,X, A数字数字1,2, 10,9,日期日期Jan 1, Dec 31, 数据排序数据排序数据排序简单排序 所谓简单排序就是根据数据清单中的某一列一列数据进行排序。只要单击用来排序的字段列中的任一单元字段列中的任一单元格格，然后再单击常用工具栏中的“降序”或“升序”按钮，数据清单中

20、的记录就会根据该列数据的大小按降序或升序重新排列。 多重条件的排序用以设置两个用以设置两个以上的排序约以上的排序约束条件。束条件。数据排序数据排序2) 多重条件的排序 如果排序的要求比较复杂(比如要先按总分进行排序，总分相同再按中医基础得分进行排序等)，这时排序不再局限于单列，须使用“数据”菜单中的“排序”命令。 进行复杂排序的方法是： 单击数据清单的任一单元格 选择“数据”菜单的“排序”命令 在“排序”对话框中设置排序的关键字以及排序的顺序(若关键字选择为“无”，则表示不参与排序操作)。 为保证数据库的字段名不参加排序，必须选中“当前数据清单”中的“有标题行”单选按钮，然后再单击“确定”按钮

21、。见图5-40。图图5-40 “排序排序”对话框图对话框图 如果想按自定义次序排序数据，或排列字母数据时想区分大小写，可在“排序”对话框中单击“选项”按钮，如图5-41所示“排序选项”对话框。 图图 5-41 “排序选项排序选项”对话框对话框 自定义排序序列8升序升序北京、东京、南京、西京北京、东京、南京、西京8降序降序西京、南京、东京、北京西京、南京、东京、北京8自定义自定义总部（南京）、分部（北总部（南京）、分部（北京、东京和西京）京、东京和西京）数据排序数据排序 筛选数据大表大表明晰的小表明晰的小表有条件的筛选有条件的筛选8自动筛选自动筛选8高级筛选高级筛选数据筛选数据筛选(1)自动筛选

22、 单击数据清单中任一单元格 选择“数据”菜单中的“筛选”命令的“自动筛选”子命令，每个字段右侧均出现下拉箭头。 单击某列的下拉箭头，选取筛选条件。如：单击“性别”列的筛选箭头，在选取筛选条件时，若选择“男”，如图 5-42所示，筛选结果只显示男生记录。 图图5-42 利用自动筛选箭头进行筛选利用自动筛选箭头进行筛选(2)取消自动筛选 选择“数据”菜单中“筛选”命令的 “全部显示”子命令，或再次选择“数据”菜单中的“筛选”命令的“自动筛选”命令以去掉其前面的对号，即可关闭自动筛选，复原数据。(3)高级筛选（补充） 如果需要输入复杂的筛选条件，或者将符合条件的数据输出到工作表的其它单元格中，可以使

23、用高级筛选功能。 使用“高级筛选”功能“高级筛选高级筛选”功能完成筛选功能完成筛选那些需要匹配计算条件或那些需要匹配计算条件或筛选条件中含复杂的筛选条件中含复杂的“与与”和和“或或”关系的数据。关系的数据。数据筛选数据筛选高级筛选的操作方法： 将表格的字段名行复制到一个单独的区域(一般为表格的下方，与表格隔开数行)，称为条件设置区域。在条件设置区域中，在字段名行的下面一行开始设置筛选的条件，若多个字段的筛选条件处于同一行则表示“并且and”，不在同一行时表示“或者or”。单击数据清单中的任一单元格。 从“数据”菜单中选择“筛选”中的“高级筛选”命令，屏幕显示“高级筛选”对话框，如图5-43所示

24、。在给出的对话框中的“数据区域”选定原数据清单的数据区域(系统会自动选择，包括字段名行)，在“条件区域”中选择刚才设定的条件区域(包括字段名行)。图图5-43 “高级筛选高级筛选”对话框对话框 选定筛选出的数据显示的区域左上角单元格(否则将在原数据清单中显示筛选结果，原数据清单将不能再恢复)，然后单击“确定”按钮。其处理结果。见图5-44。图图5-44 选择选择“高级筛选高级筛选”命令后的工作表命令后的工作表 分类汇总先分类（排序），先分类（排序），后按类汇总分析。后按类汇总分析。分类汇总分类汇总 对分类汇总的字段进行简单排序，使相同的记录集中在一起。 单击数据清单的任一单元格，选择“数据”菜

25、单的“分类汇总”命令，出现“分类汇总”对话框。如图5-46所示。 在“分类汇总”对话框中，选择分类字段、汇总计算的方式和要进行汇总的字段(可选择多个)。分类汇总的操作步骤 单击“确定”按钮。就显示分类汇总表，如图5-47所示。图图5-47 分类汇总后的工作表分类汇总后的工作表 分类汇总后，数据将分级显示，单击汇总表左上角的数字1、2、3或汇总表左侧的加、减号可选择汇总的明细级别。 选择“编辑”菜单中的“撤消”命令或再次选择“数据”菜单中的“分类汇总”命令，在“分类汇总”对话框中单击“全部删除”按钮，即可恢复原有数据格式。 分级显示数据分类汇总分类汇总数据透视表 分类汇总适合根据一个字段进行分类

26、，对一个或多个字段进行汇总，如果要求按多个字段进行分类汇总，用分类汇总就比较麻烦了。为此，Excel中提供了数据透视表功能。(1) 建立数据透视表(2) 修改数据透视表(3) 数据透视表的格式化(4) 删除数据透视表(1) 建立数据透视表 例如要统计各系男女生的人数，首先单击数据清单的任一单元格，选择“数据”菜单中的“数据透视表”命令，出现“数据透视表向导”窗口。如图5-48所示“数据透视表向导-步骤1”对话框。 图图5-48 “数据透视表向导数据透视表向导-步骤步骤1”对话框对话框 根据系统提示，选择数据来源。默认为“Microsoft Excel数据清单或数据库”，一般选择该项，单击“下一

27、步”按钮，出现如图5-49所示“数据透视向导-步骤2”对话框。 图图5-49 “数据透视向导数据透视向导-步骤步骤2”对话框对话框 步骤2用于选定数据区域，一般情况下它会选定整个列表，用户可在工作表中重新选定区域，单击“下一步”按钮，出现如图5-50所示“数据透视向导-步骤3”对话框。图图5-50 “数据透视向导数据透视向导-步骤步骤3”对话框对话框 单击“版式”按钮出现如图5-51所示的“数据透视向导-版式”对话框。将右边的字段拖到左边的图上，构造用户需要的数据透视表。图图5-51 “数据透视向导数据透视向导-版式版式”对话框对话框 单击“完成”按钮，完成数据透视表的创建，如图5-52所示。

28、图图5-52 数据透视图数据透视图在根据向导进行操作时，需注意以下几点：在选择数据来源时，若数据来源就是当前工作表或其它Excel工作表，可选择“Microsoft Excel数据清单或数据库”，否则应选择“外部数据源”，通过ODBC打开其它格式的数据文件。若在选择“数据”菜单的“数据透视表”前已经选择了数据清单中的某一单元格为当前单元格，系统会自动选定数据区域，否则需要用户自己选择数据区域。 在设置透视表布局时，若把某字段拖放到“页”中，则表示按该字段分页显示；若把某字段拖放到“行”中，则表示要按该字段进行分类汇总；若把某字段拖放到“列”中，则表示按该字段的值分列显示汇总结果；若把某字段拖放到“数据”中，则表示对该字段进行计算(如求和、求平均值等)。单击“数据”中的字段名，可以选择计算方式。(2) 修改数据透视表 用“数据透视表”工具栏(通过“视图”菜单中的“工具栏”命令可调出“数据透视表”工具栏)，可以方便地修改透视表。(3) 数据透视表的格式化 设计完数据透视表时，可以对透视表进行格式化。选取表中一个单元，再选择“格式”菜单中的“自动套用格式”命令，选取一个格式即可，也可以自定义格式化。(4)删除数据透视表 数据透视表一般存放在一个单独的工作表中，删除该工作表即可将数据透视表删除。 此外，先单击要删除的数据透视表的任一单元格，然后再选择“编辑”菜单中的“撤消数据透