excel实用技术基础

第四部分 Excel实用技术基础,第17章Excel 技术基础,Excel的功能 Excel 的界面特征,工作簿的操作,基本概念 工作簿：（workbook）是Excel 专门用于计算及存放数据的文件，其文件类型为 .xls。在每一本工作簿中包含了多张工作表，最多为255张，故Excel 可在单个文件中管理多种类型的相关信息。 工作表：(worksheet)：通常称为电子表格 (spreadsheet)，是用来存放和组织、处理、分析数据的文档。每个工作表由行和列构成，行与列相交处称为单元格。,工作簿的创建 创建新的空白工作簿 基于默认的工作簿模板，新建工作簿 基于其他模板创建工作簿 工作簿的保存,工作表的操作,工作表间的切换与数据传递 工作表间的切换 工作表间的数据传递,工作表的管理 插入工作表 删除工作表 移动工作表 复制工作表 重命名工作表 隐藏显示工作表 隐藏显示工作表中的行或列 选定工作簿中的工作表,工作表中数据的输入 单元格区域：每一行与一列交叉处构成一个单元格，单元格是Excel工作表的基本编辑单位。 用户可对多个单元格区域进行操作。由多个单元格组成的区域可用两个单元格名称来描述。,单元格地址 单元格的列标和行号又称为地址。地址分为相对地址、绝对地址和混合地址三种。仅写出列标和行号即为相对地址，如A5、B16。在列标和行号前均加入“$”符，便构成了绝对地址，如 $A$1、$C$20。仅在行号或列标前加“$”符，则构成混合地址。如$A5为绝对列相对行，而A$5为绝对行相对列。,单元格的数据输入 通常Excel工作表由单元格组成，每一单元格可定义一种数据类型，如文本、数值、公式等。Excel中单元格的常用数据类型如表17-2所示。,表17-2 Excel单元格的常用数据类型,Excel 的单元格中可以输入两种数据，即常量和公式。 常量：不以等号（=）开头的单元格数据，可以是日期、数字和文本。公式或由公式得出的数值都不是常量。 公式：公式总是以等号（=）开头，其后为算式。（单元格内的一系列数值、单元格引用、名称、函数和操作符的集合，可以通过现有的数据计算出新的数值。） 数据输入格式的修改,序列的自动填充输入 填充相同的数据: 填充序列数据： 等差序列 等比序列,工作表中区域的选定 选定连续区域 选定不连续区域 选定行或列 选定全部单元格 取消选定的区域,工作表中内容的编辑,修改单元格中的内容 完全修改单元格中的内容： 修改单元格中的部分内容： 清除单元格中的内容： 清除单元格中的格式或内容：,工作表中内容的编辑,删除单元格或区域 插入单元格、行或列 工作表区域的复制、移动,工作表的格式化操作,工作表的自动格式化：（自动套用格式 ） 单元格的格式化 调整行高与列宽,工作表与图表的打印,页面设置 页面： 页边距： 页眉/页脚 工作表,调整分页 打印预览 打印工作表 打印图表,引用与公式,使用引用 引用的作用在于标识工作表上的单元格或单元格区域，并指明公式中所使用的数据的位置。通过引用，可以在公式中使用工作表不同部分的数据，或者在多个公式中使用同一单元格的数值。还可以引用同一工作簿不同工作表的单元格、不同工作簿的单元格、甚至可应用程序中的数据。 在默认状态下，Excel 工作表单元格的位置都以列标和行号来表示，这种引用类型称为A1引用类型。除A1引用类型外，Excel还可采用R1C1引用类型。其中R表示行号，C表示列号。,引用与公式,A1引用类型用字母标志“列” （从 A 到 IV ，共 256 列）、数字标志“行” （从 1 到 65536）。A1类引用分为相对引用、绝对引用和混合引用三种。如A1、B2、C5是相对引用位置；而$A$1、$B$2、$C$5为绝对引用位置；$A1、A$1、$B2、B$2、$C5、C$5是混合引用位置。,表17-3引用示例,引用与公式,相对引用 相对引用是指引用相对于公式所在单元格位置的单元格，当复制“使用相对引用的公式”时，被粘贴公式中的“引用”将被更新，并指向与当前公式位置相对应的其他单元格。,绝对引用： 绝对引用是指引用工作表中固定不变的单元格。如果在复制公式时，不希望引用发生改变，即从一个单元格复制到另一个单元格时，公式不会因位置变化而发生变化，则要使用绝对引用。在不希望改变的引用前加一个美元符号“$”，就可以对单元格进行绝对引用。,混合引用： 包含一个绝对引用和一个相对引用的公式引用时，对被引用的某个单元格而言，它也许是行固定，也许是列固定。,运算符及其优先级,公式输入,直接输入公式 使用编辑栏的公式区输入,公式的编辑,公式的修改： 公式的移动或复制 公式的删除 引用与公式举例：,函数,函数是对一个或多个执行运算的数据进行指定的计算,并且返回计算值的公式.执行运算的数据称为函数的参数。参数可以是文本、数字或逻辑值。函数运行后传回来的数据称为函数的结果。,函数,函数的分类 Excel 中的函数通常分为常用函数、工程函数、财务函数、数学与三角函数、统计函数、查询与引用函数、数据库函数、文本函数、逻辑函数、信息函数等。,函数的书写格式 函数的一般格式为： 函数名(参数1，参数2，参数3) 如：SUM(Numberl,Number2,Number3) 函数的调用方法,常用函数介绍,数学函数 统计函数 文本函数 财务函数 逻辑函数 日期和时间函数 查找与引用函数,公式出错的原因及排除方法,如果公式不能正确计算出结果，Excel 将显示一错误值。 出现错误值 “#！”的原因 输入到单元格中的数值太长，单元格容纳不下。可以通过增加列宽，来纠错。 单元格公式产生的结果太长，单元格容纳不下。可以通过增加列宽，来纠错。 产生错误值 “#VALUE！” 使用错误的参数或运算对象类型。 产生错误值 “#DIV/0！” 公式被0（零）除。,产生错误值 “#NAME?” 在公式中使用了Excel不能识别的文字。 产生错误值 “#NA” 在函数或公式中没有可用数值。如果工作表中某些单元格暂时没有数值，请在这些单元格里输入“#NA”。公式在引用这些单元格时，将不进行数值计算，而是返回“#NA”。 产生错误值 “#REF!” 单元格引用无效。 产生错误值 “#NUM” 公式或函数中某个数字有问题。 产生错误值 “#NULL!” 试图为两个并不相交的区域指定交叉点。,公式错误的排除步骤如下： 确认所有的圆括号都成对出现。当创建公式时，输入的圆括号将以彩色显示。 确认在引用单元格区域时，使用了正确的区域运算符。引用单元格区域时，使用冒号来分隔区域中的第一个单元格和最后一个单元格。,公式错误的排除方法,确认已经输入了所有必选的参数。有些函数的参数是必选的，还要确认没有输入过多的参数。 可以在函数中输入(或称嵌套)不超过七级的函数。 如果引用的工作簿或工作表名称中包含非字母字符，必须用单引号把名称引起来。 确认每个外部引用都包含工作簿名称和相应的路径。 在公式中输人数字时不要设置格式。例如，即使需要输入的数值是“$1,000”，也应在公式中输入“1000”。,公式错误的排除方法,Excel 图表技术,创建图表,创建图表的方法： 使用图表向导 快速创建默认图表（二种方法）,图表的编辑与格式化,添加及改变图表中的文字说明 编辑图表中的文字说明 编辑图例 更改图表区的颜色、字体与图表的属性。 修改生成图表的单元格区域 更改图表区中的数值,图表的编辑与格式化,改变图表的位置 作为新工作表插入 作为其中的对象插入 改变图表的显示方式 设置与修改图表的各种选项 图表类型的修改,关于图表中的误差线 误差线以图形形式显示了与数据系列中每个数据标志相关的可能误差量。例如，您可以在科学实验结果中显示正负 5% 的可能误差量： 支持误差线的图表 可向二维的面积图、条形图、柱形图、折线图、XY（散点）图和气泡图的数据系列中添加误差线。对于 XY（散点）图和气泡图，可单独显示 X 值或 Y 值的误差线，也可同时显示两者的误差线。,什么是趋势线 趋势线用图形的方式显示数据的预测趋势并可用于预测分析，也称回归分析。利用回归分析，可以在图表中扩展趋势线，根据实际数据预测未来数据。例如，下面的图表运用前四个季度的简单线性趋势预测第二年的趋势。,移动平均 移动平均可以平滑处理数据的波动，使图像及预测趋势的显示更加清晰。 支持趋势线的图表类型 可以向非堆积型二维面积图、条形图、柱形图、折线图、股价图、气泡图和 XY 散点图的数据系列中添加趋势线；但不能向三维图表、堆积型图表、雷达图、饼图或圆环图的数据系列中添加趋势线。如果更改了图表或数据系列而使之不再支持相关的趋势线，例如将图表类型更改为三维图表或者更改了数据透视图报表或相关联的数据透视表，则原有的趋势线将丢失。,常用图表类型,条形图,条形图是用宽度相同的条形的高度或长短来表示数据变动的图形。 条形图有单式、复式等形式。 在表示定类数据的分布时，是用条形图的高度来表示各类别数据的频数或频率,饼图,饼图，是用圆形及园内扇形的面积来表示数值大小的图形。主要用于表示总体中各组成部分所占的比例，对于研究结构性问题十分有用。 在绘制圆形图时，总体中各部分所占的百分比用园内的各个扇形面积表示，这些扇形的中心角度，是按各部分百分比占3600的相应比例确定的。,环形图,中间有一个“空洞”，总体中的每一部分数据用环中的一段表示。 环形图与圆形图类似，但又有区别 饼图只能显示一个总体各部分所占的比例 环形图则可以同时绘制多个总体的数据系列，每一个总体的数据系列为一个环 环形图可用于进行比较研究。 环形图可用于展示定类和定序的数据。,直方图(Histogram),用矩形的宽度和高度来表示频数分布的图形，实际上是用矩形的面积来表示各组的频数分布 在直角坐标中，用横轴表示数据分组，纵轴表示频数或频率，各组与相应的频数就形成了一个矩形，即直方图(Histogram) 直方图下的总面积等于1,直方图与条形图的区别,条形图是用条形的长度(横置时)表示各类别频数的多少，其宽度(表示类别)则是固定的 直方图是用面积表示各组频数的多少，矩形的高度表示每一组的频数或百分比，宽度则表示各组的组距，其高度与宽度均有意义。 直方图的各矩形通常是连续排列，条形图则是分开排列。,折线图的制作,折线图也称频数多边形图(Frequency polygon) 是在直方图的基础上，把直方图顶部的中点(组中值)用直线连接起来，再把原来的直方图抹掉 折线图的两个终点要与横轴相交，具体的做法是 第一个矩形的顶部中点通过竖边中点（即该组频数一半的位置）连接到横轴，最后一个矩形顶部中点与其竖边中点连接到横轴 折线图下所围成的面积与直方图的面积相等，二者所表示的频数分布是一致的。,雷达图,雷达图（Radar Chart）是显示多个变量的常用图示方法 在显示或对比各变量的数值总和时十分有用 假定各变量的取值具有相同的正负号，总的绝对值与图形所围成的区域成正比 可用于研究多个样本之间的相似程度,【例3.7】为研究某条河流的污染程度，环保局分别在上游、中游和下游设立取样点，每个取样点化验水中的五项污染指标，所得数据如表3-13。将各指标用雷达图表示出来，并分析该河流的主要污染源。,第十九章 数据管理,本章主要内容,数据库的管理功能 外部数据的获取 记录单的使用 数据的排序 数据的筛选 数据分类汇总,数据统计与分析 t检验 方差分析 相关分析 直方图分析 回归分析,第一节 数据库的管理功能,外部数据的获取 如果用户要所使用数据已存在文本文件中,要用Excel对该数据进行分析处理，则不必重新输入数据，只需将其导入至Excel工作簿。 对于文本文件，需按一定的格式输入，数据之间均由特殊的分隔符(如空格、逗号、Tab键)分隔开。,导入文本文件的操作步骤如下： 单击“数据” “导入外部数据” “导入数据”命令，弹出“选取数据源”对话框。选择所需的文件后，单击“打开”按钮，将打开“文本导入向导”对话框 “文本导入向导”共分三步，可按每一步要求做出选择，每完成一步可单击“下一步”按钮或直接单击“完成”按钮以默认格式快速导入文本文件。,记录单的使用,Excel具备了数据库的一些特点，可以把工作表中的数据做成一个类似数据库的数据清单(二维表)。,数据清单是指：包含相关数据的一系列工作表行，清单的第一行具有列标志。例如，可以将学生成绩单中的姓名、学号、科目成绩等建立数据清单，其列标志就是姓名、学号、科目成绩等。 数据清单中可使用“记录”命令实现行的增加、修改、删除与查找等操作。在数据清单中，可将字段名所在的一行定为表头，在字段名下的每一行数据可看作为一个记录。,记录单的使用,数据库的建立 记录的增加 记录修改 记录删除 记录的条件查寻,数据的排序,排序是数据库的基本功能之一，为了数据查找方便，往往需要对数据进行排序。排序是根据某一指定列的数据的顺序重新对行的位置进行调整。 Excel 2002为用户提供了三级排序，分别为主要关键字、次要关键字、第三关键字。每个关键字均可按：“升序”(即递增方式)或“降序”(即递减方式)进行排序。排序可使用工具按钮，也可使用命令菜单。,图19-6 排序对话框,使用菜单命令则需借助排序对话框,若要按行进行排序则需打开“排序选项”对话框。选择排序方向“按行排序”，确定后再回到“排序”对话框中进行排序关键字的设置。,数据筛选,筛选数据只是将数据清单中满足条件的记录显示出来，而将不满足条件的记录暂时隐藏。使用筛选功能可从一个很大的数据库中检索到所需的信息，实现的方法是使用筛选命令的“自动筛选”或“高级筛选”。 其中“自动筛选”是进行简单条件的筛选；而“高级筛选”是针对复杂的条件进行筛选；“全部显示”是将筛选后剩余的部分数据全部恢复为原来的数据。,数据筛选,自动筛选 高级筛选,分类汇总,分类汇总是指在数据清单中快速汇总各项数据的方法。在Excel 中提供了分类汇总的命令，通过这些命令，可直接对数据清单进行汇总。 在数据清单中执行分类汇总功能之前，首先对数据清单中用以分类汇总的项（字段）进行排序，如下例中要按医生的职称汇总，则应先按职称排序，排序完成后进行如下操作。,汇总前先对汇总变量“职称”进行排序,图19-12 分类汇总对话框,单击“数据” “分类汇总”命令，弹出“分类汇总”对话框。在对话框中进行 “分类字段”、“汇总方式”、并选定“汇总项”等。,分类汇总报表中组及分级显示,经过工作表中数据分类汇总，表中的数据被分类汇总成组，组下面包含各级成员。如上页图中“主任医师”即为一组。在图中，分类汇总表的最左边可看到“分级显示”按钮，其中按钮为第一级，代表总的汇总结果的范围；按钮为第二级，代表参加汇总的各个记录项。上页图显示的是三级全部呈现的结果。,数据统计与分析,Excel用户提供了一组数据分析工具，如：方差分析、协方差分析、相关系数、指数平滑、直方图、随机数发生器工具；回归、抽样、排位与百分比排位等，称为“分析工具库”。用户可以使用这些“分析工具”在工作表中寻找出一些数据的变化规律，帮助决策。使用“分析工具”只需为每一个分析工具提供必要的数据和参数，该工具便会使用相应的统计或工程函数，在输出表格中显示相应的结果。其中的一些工具在生成输出表格时还能同时产生图表。,使用“分析工具”的操作如下： 单击“工具”“数据分析” 命令，弹出 “数据分析”对话框。,图19-17 数据分析对话框,如果“数据分析”不存在，请运行安装程序来加载“分析工具库”。安装完毕之后，必须通过“工具” “加载宏”命令，在“加载宏”对话框中选择“分析工具库”前的复选框，启动它。,主要数据分析方法,t 检验 方差分析 相关分析 直方图分析 回归分析,