Excel在日常工作中的应用.ppt

1,Excel在日常工作中的应用,2,Excel工作界面 由Excel应用程序窗口和Excel文件窗口两个部分组成。,Excel 简 介,文档题栏,菜单栏,工具栏,编辑栏,文件标题,列标签,行标签,单元格,垂直滚动条,工作表标签,标签滚动按钮,状态栏,水平滚动条,3,提 纲,4,第一部分 基本操作技巧,5,1、复制、粘贴 1）使用工具栏按钮 2）使用菜单 3）使用快捷键复制：Ctrl+C；Ctrl+X；Ctrl+V；4）使用拖放复制；2、使用自动填充功能 1）自动填充（拖动“填充柄”进行填充）1）输入初始单元数据，1个或2个单元格。2）选定包含初始值的单元格。3）指向“填充柄”并拖动填充柄经过待填充区域。2）编辑填充操作操作：1）首先在工作表中输入一个初始值。2）选择包括初始值的区域，操作编辑填充。,一、数据填充,6,使用选择性粘贴；,7,二、冻结窗口,1）冻结行：选择冻结线的下一行（或光标置于冻结的下一行）。窗口冻结拆分窗口。2）冻结列：选择冻结线的右一列（或光标置于冻结的右一列）。窗口冻结拆分窗口。3）冻结行和列：选择行和列冻结线的交叉单元格。窗口冻结拆分窗口4）撤消冻结：窗口撤消窗口冻结。,8,三、拆分窗口,1）水平拆分：选择拆分线的下一行。2）垂直拆分：选择拆分线的右一列。3）水平和垂直拆分：选择行和列拆分线的交叉单元格。4）撤消拆分窗口。,9,四、保护工作表,10,当排序含有公式的数据列表时，应该谨慎行事。简单排序 步骤：1)选择参与排序的数据区域（注：一般不包括字段名行，但包括所有列）。2)单击工具栏中的升序或降序按钮。,五、排序,练习:对“report.xls”进行操作,对年龄进行排序。,11,复杂排序 步骤：1)选择参与排序的数据区域。2)数据排序。3)选择关键字和排序方向。最多可以选择3个关键字段进行排序。,五、排序,12,五、排序,13,筛选指只显示满足条件的数据记录。有自动筛选和高级筛选两种方法。操作步骤：1)光标位于数据列表上。2)数据筛选自动筛选。3)单击筛选按钮，取值或选取自定义后给出筛选条件。4)取消筛选：数据筛选自动筛选,六、筛选-自动筛选,筛选按钮,14,练习：利用自定义筛选方式选择年龄40岁，姓张的观测，并将其存为新的Excel文件。,六、筛选-自动筛选,15,六、筛选-高级筛选,高级筛选能够提供以下功能：（1）可以指定更复杂的筛选条件；（2）可以指定计算的筛选条件；（3）可以从另一个地址中摘录适合条件的记录副本。操作：数据筛选 高级筛选,步骤：一、为高级筛选建立条件区域；至少有两行组成，在第一行中必须包括包含有数据清单中的一些或全部字段名称；条件区域的另一行必须由筛选条件构成。二、在筛选过程中使用多个条件条件在同一行中，为“AND”操作；条件在不同行中，为“OR”操作。,选择不重复的记录：会隐藏所有符合指定条件的行的副本，如果没有指定条件区域，此选项会隐藏数据列表中的所有重复的行的副本。,16,六、筛选-高级筛选,使用计算条件进行筛选：条件区域为B1:B2，这个公式计算“发病数”列的平均值，并将它与“发病数”列的第一个单元格中的数据进行比较。注意：必须使用新的字段标题，而不使用列表中的字段标题；如果计算公式涉及到数据列表外的值，要使用绝对引用而不是相对引用；使用数据列表中首行数据创建计算条件公式（不是字段名称），使用相对引用而不是绝对引用。,条件区域,17,六、筛选,18,操作：1)按某关键字段进行排序（分类）。2)数据分类汇总。3)对“分类汇总”对话框进行如下操作：分类字段。汇总方式。选定汇总项（可有多个）。替换当前分类汇总。每组数据分页/不分页。汇总结果显示在数据下方。,七、分类汇总,19,练习：对“exp3.xls”操作，求各年的发病数平均值。操作：（1）先对数据按年份进行排序；（2）数据分类汇总（3）取消分类汇总：数据分类汇总，选择全部删除,七、分类汇总,20,八、行列转置,21,九、标题设置,22,十、设置打印区域,23,十一、设置页眉和页脚,24,十二、数据有效性,25,十三、数据合并,26,十四、设置下拉列表,27,第二部分 公式和函数,28,第二部分 公式及引用,29,单元地址及其引用 公式 函数,Excel公式和函数的使用,30,公式的格式 公式=操作数+运算符 要求：1）由等号开始；2）等号后面是由操作数和运算符组成的表达式。操作数：可以是数字、单元地址、字符、区域名、区域、函数等。运算符：1）算术运算符：可以完成基本的数学运算，并产生数字结果。,一、公式,31,2）比较运算符：可以比较两个数值，并产生逻辑值True 或 False。,Excel公式和函数的使用公式,32,3）文本运算符“&”:可以将一个或多个文本连接为一个组合文本。,4）括号：（）5）运算符的优先级（）算术运算符文本运算符比较运算符，（）最优先。,Excel公式和函数的使用公式,33,公式的改变 1)改变被引用的单元数据时，公式的值相应地改变。2)移动公式时，其引用的单元地址不改变，值也不变。3)移动被引用的单元数据时，公式中被引用的地址相应地改变，其值不改变。公式的复制 公式在复制过程中，其引用的相对地址将相应地发生改变，其结果也将改变。,Excel公式和函数的使用公式,34,Excel公式和函数的使用单元地址及其引用,地址的分类 相对地址:相对当前单元的相对位置。表示如：A5:B9、A5、B8。绝对地址:单元格在表中的绝对位置,表示在相对地址前加“$”。如，$A$5:$B$9$A$5$B$8。混合地址：相对地址和绝对地址的混合。表示如：A5:$B$9 A$5$B8。单元地址的引用 格式：工作簿名 工作表名!单元格地址 例：BOOK 1 Sheet1！A4；BOOK 1 Sheet1！$B$6 Sheet1！A3；Sheet1！$B$5（注：省略工作簿名，则默认为当前工作簿）A4；$B$9（注：默认为当前工作簿的当前工作表）,35,格式 函数名（参数1，参数2，）参数的取值范围是：常数、单元格、区域、区域名、逻辑值、文本、公式、其他函数。now()函数没有参数。,二、函数,36,函数的输入方法 方法一：由等号开始，按照函数的格式直接输入函数表达式。如：=SUM（A1：B6），=DATE（99，10，1）。方法二：1)插入函数。2)选择函数，确定后进入参数提示框。3)输入或选择函数的参数。4)确定。方法三：单击“粘贴函数”按钮,二、函数,37,常用函数,二、函数,38,数据库的概念 数据库是一个二维表，按字段和记录的形式组织数据。Excel数据库指基本符合数据库特征的Excel工作表区域，也称Excel 数据列表。Excel数据库的特点 字段：同一类型的一列数据。字段名：第一行为字段名，各字段名在同一行上，不能有相同的字段名。记录：含有每一个字段的一行数据，每行表示一个对象。行、列的顺序关系不 影响二维表所包含的信息内容。,Excel数据库操作简介,39,第三部分 数据透视表和透视图,40,“数据透视表与数据透视图”有机地综合了前面介绍的排序、筛选、分类汇总、统计图绘制等数据分析的优点，可方便地调整分类汇总的方式，灵活地以多种不同方式展示数据的特征。计算均数标准差（分类汇总）、建立列联表、计算百分比、建立新的数据子集、绘制统计图形等均可利用这一工具完成。因此，该工具是最常用、功能最全的Excel数据分析工具之一。掌握这一功能，可以帮助用户解决许多数据分析问题。,Excel的数据透视表和数据透视图,41,Excel的数据透视表数据透视表的创建,源数据如右边的截图所示。,42,步骤一：在存放数据的工作表中，选取任一单元格，以便Excel自动确定用户想用的数据范围，单击“数据数据透视表和数据透视图”，弹出“数据透视表和数据透视图向导”对话框。,Excel的数据透视表数据透视表的创建,43,步骤二：单击“下一步”按钮，弹出如下截图：,步骤三：确定Excel已正确识别数据的单元格区域。单击“下一步”按钮，可得下面截图。该步骤指定数据透视表显示的位置。本例按默认选定新建工作表。,Excel的数据透视表数据透视表的创建,44,步骤四：单击完成键，Excel自动创建一张新的工作表。在该工作表中显示了创建的数据透视表雏形，此外，数据透视表工具栏以及数据透视表字段列表也显示在工作表界面。,Excel的数据透视表数据透视表的创建,45,数据区：数据透视表的雏形中分位4个数据区。“数据字段区”，此处提示“请将数据项拖至此处”。为了计数或求频数，一般将个体编号变量拖入此处；为了计算定量数据描述性统计量（如平均值、标准差等），应将定量数据（如年龄、身高、体重、发病数）拖入此处。“行字段”区，此处提示“请将行字段拖至此处”，该区域应拖入分类变量，该变量的分组类别排成一列，每个类别占一行。“列字段”区，此处提示“请将列字段拖至此处”，该区域也应拖入分类变量，该变量的分组类别排成一行，每个类别占一列。“页字段”区，此处提示“请将页字段拖至此处”，如果将分类变量放在此处，选择这一变量的某一类别，则产生一个数据透视表，选择这一变量的另一类别，则覆盖前次产生的表格，产生一个新的数据透视表，这样可查看在某一变量的不同值时，数据透视分析的结果。选取“（全部）”时，则忽略该变量的作用。这一区域的作用相当于统计上所说的分层，即拖入的变量分层后参看各层的数据分析结果,Excel的数据透视表数据透视表的结构及其工具栏,46,数据透视表工具栏,设置报告格式,图表向导,隐含明细数据,显示明细数据,更新数据,汇总包含隐含项,始终显示项目,字段变量设置,显示字段列表,编辑与组织数据透视表 创建了数据透视表雏形后，应根据分析要求，设置数据透视表的版式。该步骤也是数据透视表的最关键的一步。,Excel的数据透视表数据透视表的结构及其工具栏,47,例：查看2002年各月四种疾病的发病情况,Excel的数据透视表数据透视表的结构及其工具栏,48,数据透视图,有两种方法可以创建数据透视图。方法一：在数据透视表的工具栏中单击“图表向导”图标；方法二：直接利用“数据透视表和数据透视图向导”对话框创建。这里我们用第一种方法，利用刚才创建的数据透视表创建数据透视图。,49,Excel的数据透视图创建数据透视图,50,选择性显示分类变量；设置字段的汇总方式（如通过双击“求和项：发病数”实现汇总方式的更改）；图表类型的选择（右键选择快捷菜单中的“图表类型”）；分页显示图形（如可以分页查看各年的情况）；创建完数据透视表后，可以和其它统计图一样编辑数据透视图。将数据透视图更改为静态图。,Excel的数据透视图调整数据透视图,51,第四部分 统计分析功能,52,调用“数据分析”工具的方法：1）工具加载宏，弹出“加载宏”界面。,Excel的数据分析功能数据分析工具的调用,53,2）选取分析工具库、分析数据库VBA函数、规划求解等，单击“确定”按钮。根据屏幕提示进行安装。如果office不是完全安装，还需要按照安装提示插入Office盘进行安装。安装完“加载宏”后，打开“工具菜单”，即可发现“数据分析”选项。单击菜单中的“工具数据分析”，可弹出数据分析菜单界面。（注意，数据分析工具只对当前的工作表有效，如果要对一个工作薄中的其余工作表进行同样的分析，则应分别在每一张工作表上运行一次分析工具。）,Excel的数据分析功能数据分析工具的调用,54,例：计算“exp4.xls”中三个地区的相关描述性统计量操作：工具数据分析，根据下图所示设置进行分析。,Excel的数据分析功能常用描述统计量的计算,55,Excel的数据分析功能常用描述统计量的计算,分析结果如下所示：,56,谢谢大家的聆听,