EXCEL与数据分析(1)ppt课件.ppt

单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,*,授课教师：马银戌,EXCEL,与数据分析,1,.,课程教学内容,（,34,课时）,第一章,EXCEL,数据处理入门,（,2,课时）,第二章 定性数据的基本特征分析（,6,课时）,第三章,定量,数据的基本特征分析（,4,课时）,第四章 数据间的相关性分析（,6,课时）,第五章 数据间的差异性分析（,4,课时）,第六章 数据的变动趋势分析（,6,课时）,第七章,EXCEL,与数据综合分析,（,6,课时）,2,.,第一节,EXCEL,概述,第二节,数据的录入与编辑,第三节 数据的类型与预处理,第一章,EXCEL,数据处理入门,3,.,第一节,EXCEL,概述,一、,EXCEL,的基本功能,二、,EXCEL,的基本概念,三、,EXCEL,数据分析工具的加载,4,.,一、,EXCEL,的基本功能,Microsoft,Excel,是由微软公司开发的一种,电子表格,程序，是,微软公司,的办公套装软件,Microsoft office,的重要组成部分。,Excel,功能强大、使用方便。为日常生活中处理各式各样的表格提供了良好的工具。,它可完成表格数据的输入、加工整理、分类汇总、简单计算等多项工作，可生成精美直观的数据清单、表格、图表。,Excel,中大量的公式函数可以应用选择，使用,Excel,可以执行繁重而复杂的计算，可以分析信息并管理,电子表格,或网页中的数据信息列表，带给使用者方便。,Excel,的数据分析工具，还可以进行各种数据的高级处理、统计分析和辅助决策操作，因而可以广泛地应用于统计、管理、财经、金融等众多领域。,5,.,二,、,EXCEL,的基本概念,Excel,中的基本概念有：工作簿、工作表、单元格及单元格地址。,工作簿,工作簿名,一张工作表及其名称,单元格,行号,列号,6,.,工作簿,是工作表、图表及宏表的集合，它以文件 的形式存放在计算机的外存贮器中。新创建的工作簿，,Excel,将自动给其命名，如,Book1,、,Book2,用户可以重新赋予工作簿有意义的名字，如“员工档案资料,”,。,工作表,是用于编辑、显示和分析一组数据的表格，它由排成行和列的单元格组成，每张工作表由,65536,行和,256,列单元格组成。工作表是包含在工作簿中的。一个工作簿最多可包含,255,张工作表。在新建工作簿时，,Excel,默认,3,张工作表，并自动给工作表命名为,sheet1,、,sheet2,、,sheet3,，用户可根据需要添加工作表，可对每张工作表重新命名，如“员工工资”等。,7,.,单元格,是构成工作表的基本元素，用于输入、显示和计算数据，一个单元格内只能存放一个数据。,单元格地址,用来标识一个单元格的坐标，它由列号行号组合表示。其中，行号由,1,、,2,、,3,表示，列号由,A,、,B,、,C,表示。,Excel,对单元格地址的引用有四种方式：,相对地址：,形式为“行号列号”，如“,A5,”,。,绝对地址：,形式为“,$,行号,$,列号”，如“,$A$5,”,。,混合地址：,是相对地址和绝对地址的混合，如“,A$5,”,。,三维地址：,是在单元格地址前面加上工作表的名称和感叹号。如“,Sheet3,！,A5,”,表示,Sheet3,工作表中的,A5,单元格。,8,.,三、,EXCEL,数据分析工具的加载,Excel,中提供了一组数据分析工具，称为“分析工具库”，使用这些工具在建立复杂统计或工程分析时可节省操作步骤和时间。应用时，只需为每一个分析工具提供必要的数据和参数，该工具就会使用适当的统计或工程宏函数，在输出表格中显示相应的结果，其中有些工具在生成输出表格的同时还能生成图表，用户通过这些分析工具可以很轻松地分析数据。,但需要注意的是，“分析工具库”在安装,Microsoft Office Excel,后，需要通过在,Excel,中加载宏之后才能使用。加载“分析工具库”有两种方法：,9,.,方法一：,（,1,）,打开,Excel,，单击“工具”，在下拉菜单中单击“加载宏”，打开如下的对话框：,（,2,）,勾选“可用加载宏”列表框中的,“分析工具库”，然后单击“确定”，系统将会引导用户进行安装。,（,3,）,安装完毕后，“数据分析”会自动出现在,Excel,的“工具”菜单中。,10,.,方法二：,（,1,）在,Windows,中单击“开始”“搜索”，在弹出的对话框中单击“所有文件和文件夹”，然后在“全部或部分文件名”下面的框内输入“,ANALYS32.XLL”,，单击“搜索”，搜索程序就会在硬盘中搜索该文件。,（,2,）双击搜索到的“,ANALYS32.XLL”,文件，在打开的对话框中单击“启用宏”，系统就会将该文件加载宏安装到,Excel,中。,（,3,）,安装完毕后，“数据分析”会自动出现在,Excel,的“工具”菜单中。,11,.,第二节,数据的录入与编辑,一、,工作簿和工作表的基本操作,二、,录入数据,三、,编辑,数据,12,.,一、,工作簿和工作表的基本操作,新建工作簿,有三种途径：,*,启动,Excel,自动创建。工作簿自动命名为,Book1,（或,Book2,、,Book3,），用户可根据需要对工作簿重命名，每个工作簿默认包含,3,张工作表。,*,单击工具栏中新建按钮，自动出现一新工作簿，其命名方法同上。,*,根据模板新建工作簿。单击“文件”菜单，选择“新建”，窗口右侧会出现一个对话框，允许用户选择“模板”，然后,Excel,再根据模板来创建新工作簿。,13,.,保存工作簿,新建的工作簿呈现打开的可编辑状态，如果不保存起来则所做的工作就无效。要养成随时保存的良好习惯。保存工作簿可以分为保存新建工作簿、另存工作簿和自动保存工作簿,3,种情况：,*,保存新建工作簿。新建的工作簿在第一次保存时，应该为其指定保存位置，并输入文件名。,*,另存工作簿。对打开的工作簿进行编辑后，可以直接保存，也可以将其另存：一是将工作簿以其他名字保存在相同路径下；二是以相同名字保存在其他路径下；三是以其他名字保存在其他路径下。,*,自动保存工作簿。单击“工具”菜单，点击“选项”，在选项对话框中选中“保存自动恢复信息”复选框，在右侧的数值框中输入进行自动保存的时间间隔（如,10,分钟），单击“确定”即可。也可在工作过程中随时点击保存按钮手动保存文件。,14,.,工作表的基本操作,每个工作簿默认包含,3,张工作表，用户可根据需要添加或删除工作表；可移动工作表的位置，可重新命名工作表。,*,添加工作表。单击“插入”菜单，点击“工作表”即可插入一张新工作表，一个工作簿最多可容纳,255,张工作表。,*,删除工作表。打开要删除的工作表，单击“编辑”菜单，点击“删除工作表”即可。,*,移动工作表。单击要移动的工作表，拖到相应的位置即可。,*,重新命名工作表。右击要重命名的工作表名称，在复选框中点击“重命名”，然后输入新的名称即可。,15,.,二、,录入数据,Excel,中广义的数据包括数字、文本、符号、字母等各种形式。录入各种形式数据的步骤相同：,*,单击要输入数据的单元格，使其成为当前单元格。,*,在此单元格中输入数据。,*,按回车键结束。,注意：,*,如果要将数字作为文本型数据保存，必须在前面加单撇号（,）。,*,输入日期和时间型数据，一般用斜线（,/,）和连字符（,-,）用作日期分隔符，冒号（：）用作时间分隔符。,16,.,快速填充数据,Excel,提供了数据填充功能，用以自动生成有规律的数据，如相同数据、等比、等差数列等，可提高输入数据的效率。,*,在一行或一列中产生相同数据：,向某一单元格输入第一个数据，然后单击该单元格右下角，此时鼠标变为,+,号，按住鼠标左键向下（或左右）拖动即可。,*,在一行或一列中产生等差或等比数列：,向某一单元格输入第一个数据，单击“编辑”菜单，单击“填充”子菜单，选择“序列”菜单项，出现如下对话框：,17,.,在对话框中，根据需要确定如下内容：数列在工作表上是以行还是列方式生成；数列的类型；日期型数据的时间单位；等差或对比数列的步长值及终止值,。,单击“确定”按钮，即可生成相应数列。,对于步长值为,1,的等差数列，也可按住,Ctrl,键，同时单击第一个单元格右下角，向下（或左右）拖动即可。,18,.,三、,编辑数据,*,数据移动、复制和粘贴,对于普通数据的移动、复制和粘贴操作，可通过三种方法实现：,（,1,）选中要操作的单元格,单击“编辑”菜单,点击“复制”或“剪切”,单击目标单元格,单击“编辑”菜单,点击“粘贴”。,（,2,）选中要操作的单元格,点击鼠标右键,点击“复制”或“剪切”,右击目标单元格,点击“粘贴”。,（,3,）使用快捷键,Ctrl+X,（剪切）、,Ctrl+C,（复制）和,Ctrl+V,（粘贴）完成。,19,.,*,使用“选择性粘贴”完成数据复制和粘贴,选中要操作的单元格,单击“编辑”菜单（或单击右键）,点击“复制”,（注意不能使用“剪切”）,单击目标单元格,单击“编辑”菜单（或右击目标单元格）,点击“选择性粘贴”。出现如下复选框：,可根据需要在复选框中“粘贴”子菜单下选择有关项目。,20,.,*,“选择性粘贴”具有特殊功能，常用的有：,以“全部”方式粘贴：将要复制的文件，按照原样粘贴到相应的位置。,以“公式”方式粘贴：将要复制的文件，按照公式方式粘贴到相应的位置。,以“数值”方式粘贴：将要复制的文件，从中去除格式、公式，仅提取数据内容，粘贴到相应的位置。,以“格式”方式粘贴：将要复制的文件，从中去除数据、公式，仅提取格式内容，粘贴到相应的位置。,21,.,*,使用“选择性粘贴”完成数据计算,“选择性粘贴”不仅可以进行数据的复制，还可进行数据计算。,如果要对表格中的特定数据进行加减乘除计算，,可在复选框中“运算”子菜单下选择有关项目。,22,.,案例：,一次性为所有员工增加,600,元节日奖金,（,1,）在任意单元格中输入,600,，并按,Ctrl+C,快捷键；,（,2,）选中所有员工奖金单元格,（,3,）选中“选择性粘贴”选项,（,4,）在“运算”子菜单下选中“加”,23,.,*,使用“选择性粘贴”完成行列转置,利用“选择性粘贴”还可以进行数据的行列转置。所谓行列转置，指的是将选择复制的行或列，在粘贴复制时转置为列或行。,只需在“选择性粘贴”复选框中选中“转置”项即可实现。,24,.,*,数据查找和替换,*,一般数据的查找和替换：,单击“编辑”菜单,点击“查找”,输入要查找的文本或数字,单击“查找下一个”,单击“替换”下拉子菜单在“替换为”后面的空白框中输入要替换的内容,。,*按照特定的格式条件查找指定数据,如果事先对某些数据设置了特定格式，要查找这些数据，单击“编辑”菜单,点击“查找”,单击“选项”,单击“格式”,单击“格式”，然后从各种格式的复选框中寻找要查找的格式,单击“确定”,单击“查找下一个”。,案例：,在员工初始档案中，有几个人的学历掌握不准，因此录入时用了红色字体。经核对后，需进行调整。,25,.,第三节 数据的类型和预处理,一、数据的类型划分,二、数据的预处理,26,.,一、,数据的类型划分,“数据,”,定性数据,定量数据,定性数据：不能用数字表示，可用文字、符号等表示。,又可分为分类数据和顺序数据。,分类数据是只能划分类别的非数值型数据；,顺序数据,是可以划分有序类别的非数值型数据。,定量数据：数值型数据，用数字表示。,不同类型的数据加工整理和分析的方法不同。,27,.,二、数据的预处理,数据的预处理是进行数据分析的前提步骤，包括数据的审核、筛选、排序、分类（分组）、简单汇总以及所需新指标数据的计算等。,（一）数据的审核和筛选,数据的审核,主要从完整性和准确性两个方面进行。,*完整性审核主要是检查数据是否有遗漏，各种相关项目如行列标题、编码、序号、计量单位等是否齐全。,*准确性审核主要是检查数据是否有错误，计算是否正确等。具体又包括逻辑检查和计算检查。逻辑检查主要是针对定性数据进行，计算检查主要是针对定量数据进行。,28,.,*逻辑检查主要是从定性角度审核数据是否符合逻辑，内容是否合理，各项目或数字之间有无相互矛盾的现象等。如工资总额等于基本工资加奖金，而某人的奖金高于其工资总额，显然属于逻辑性错误，应纠正。,*计算检查是检查各项数据在录入、汇总、计算结果和计算方法等方面是否有错。,*数据的筛选,数据筛选包括两方面内容：一是将某些不符合要求的数据或有明显错误的数据予以剔除；二是将符合某种特定条件的数据筛选出来，对不符合条件的数据予以剔除。利用,Excel,进行筛选，包括“自动筛选”和“高级筛选”两种形式。,29,.,*自动筛选的使用,“自动筛选”功能只能对单一条件进行筛选，是,Excel,最基本的操作。操作步骤为：,打开,Excel,文件，选中文件数据区域任一单元格，单击“数据”下拉菜单,点击“筛选”,“自动筛选”，然后在每列第一行的字段名下显出一个小三角，单击小三角，在其列表框中选中所要筛选的条件内容即可。若有多个条件需要筛选，则需多次从相应字段名小三角下的列表框中进行筛选。,案例：,从企业员工基本资料中筛选出性别为“男”、学历为“硕士”的员工。,30,.,*高级筛选的使用,“高级筛选”功能可以一次性对多个条件进行筛选，在数据较大较复杂时使用此功能较为快捷。操作步骤为：,打开,Excel,文件，在空白位置输入筛选条件，单击“数据”下拉菜单,点击“筛选”,打开“高级筛选”对话框设置高级筛选条件以及筛选结果显示的位置，即可显示出满足筛选条件的数据。,案例：,从企业员工基本资料中筛选出性别为“男”、年龄,40,、学历为“硕士”的员工。,31,.二）数据的排序和分类汇总,一般来说，录入数据清单的数据是无序的，不能反映现象是本质与规律性。为了方便分析，要将其进行排序、分组，以使数据按要求排列，同时把性质相同的数据归为一组，让不同组数据之间的差异性显示出来。,*数据的排序,，基本步骤如下：,打开,Excel,文件，选中文件数据区域任一单元格，单击“数据”下拉菜单,点击“排序”,，然后在“主要关键字”下的列表框中选择要排序数据的字段名，在右侧选择“升序”或“降序”。如果要把相同的记录再排序，则可在“次要关键字”下的列表框中选择要排序数据的字段名，在右侧选择“升序”或“降序”。,32,.,案例：,对所有员工按学历和工资总额排序,（,1,）打开,Excel,文件，单击数据区域任意单元格,；,（,2,）单击“数据”下拉菜单,点击“排序”，出现如下对话框：,（,3,）在,“主要关键字”下的列表框中选择“学历”，在右侧选择“升序”；,（,4,）,在“次要关键字”下的列表框中选择“工资总额”，在右侧选择“降序”；,（,5,）单击“确定”，完成排序。,33,.,*数据的分类汇总,，基本步骤如下：,（,1,）在数据排序的基础上，单击文件数据区域任一单元格；,（,2,）,单击“数据”下拉菜单,点击“分类汇总”,，出现分类汇总的对话框；,（,3,）在“分类字段”下的列表框中选择要分类的字段名；,（,4,）在“汇总方式”下的列表框中选择“求和”；5,）在“选定汇总项”下的列表框中选择要汇总数值的字段名。,（,6,）单击“确定”，完成分类汇总。,34,.,案例：,对所有员工按学历进行工资总额的分类汇总,（,1,）打开,Excel,文件，单击数据区域任意单元格,；,（,2,）单击“数据”下拉菜单,点击“分类汇总”，出现如下对话框：,（,3,）在“分类字段”下的列表框中,选择“学历”；,（,4,）在“汇总方式”下的列表框中,选择“求和”；,（,5,）在“选定汇总项”下的列表框,中选择“工资总额”；,（,6,）单击“确定”，完成分类汇总。,35,.,（三）新指标数据的生成和计算,在原始数据的基础上，有时需要计算一些新的指标数据。简单的计算可以直接在单元格中输入公式计算，复杂的计算也可通过插入函数公式计算。,Excel,中的公式都有语法规则，要对数据进行计算和操作需要严格遵守其语法。公式都以等号“,=”,开始，其后是公式的表达式，如“,A3/20+B3”,。,Excel,按照公式中每个运算符的特定次序从左到右进行计算。,几种输入公式的方法：,在单元格中输入：,当输入的公式比较简单时可在单元格中直接输入，然后按“,Enter”,键确认，可计算出结果。,案例：,根据基本工资和奖金计算工资总额。,36,.,在编辑栏中输入：,当输入的公式比较长而复杂时，在单元格中输入会影响其整体显示，于是可在编辑栏中进行输入。方法是：选择需要输入公式的单元格，然后将光标插入编辑栏中，输入所需的公式，然后按“,Enter”,键确认，可计算出结果。,结合键盘和鼠标输入：,在公式中常常需要使用单元格引用参与计算，如果手动输入引用地址比较麻烦，此时可用鼠标选择要引用的单元格，结合键盘和鼠标输入公式。其方法是,:,选择需要输入公式的单元格，根据情况输入运算符，当需要引用某单元格时，直接使用鼠标选择该单元格，其地址自动出现在公式中，完成公式的输入后，按“,Enter”,键确认，可计算出结果。,37,.,