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盘进行安装。 安装完“加载宏”后，打开“工具菜单”，即可发现“数据分析”