EXCEL数据分析工具

1、1.1 数据 1.2 Excel基础 1.3 属性数据分析 1.4 数据展示图 1.5 数值型数据分析,第1章 数据和数据分析,根据数据的属性： 数值型：123.1万元、23.5、4700点 连续型数据： 温度变化，如23.5 、 离散型数据： 5个篮板、120个集装箱、 属性型：用文字、判断等表示的数据 天气晴、阴、雨；股市升、降； CPI高、中、低；质量好、中、差， 属性型数据都是离散的。,1.1 数据,第1章 数据和数据分析,商务数据的分类,根据数据的变化 不随状态变化、相对稳定的数据称为静态数据。 随状态变化的数据称为动态数据。,1.1 数据,商务数据的分类,时间序列数据 截面数据,1

2、996年2004年农村与城镇家庭恩格尔系数（食品支出占家庭总支出的百分比，单位：）,按照时间前后发生的数据称为时间序列数据。,1.1 数据,20022006年某市旅游接待与收入情况,按时间划分,陕南 2007年经济指标：,同一时间段发生的数据称为截面数据。,1.1 数据,截面数据,定类数据（Categorical/Nominal Data） 由不同类别组成的数据称为定类数据。各类数据的类型和单位一般都不相同。不同类的数据之间有些可以运算，有些则不能运算。,1.1 数据,按相互关系划分,定序数据（Ordinal Data）,仅表明对象的排列次序的数据称为定序数据，定序数据之间的差或比值一般没有意

3、义。,1.1 数据,定距数据（Interval Data） 数据没有绝对的零值，没有倍数、比例关系，只有相互之间的差距有实际意义。,1.1 数据,定比数据（Ratio Data）,定比数据具有明确的零点，可以计算数据的比例。例如2008年美国的GDP是中国的4.15倍。,1.1 数据,根据数据的连续性 离散数据 数据是整数或者若干可能的结果之一。例如顾客投诉的次数，产品中不合格的个数，天气是晴天、阴天或下雨。 连续数据 数据是连续变量。例如产品的成本、利润、交货时间等。,1.1 数据,1.1 数据,EXCEL软件简介,Microsoft Excel是一个功能强大、使用灵活方便的电子表格软件，也

4、是最为流行的办公自动化软件，本课程主要利用EXCEL的统计分析功能和丰富的统计图表。,1.2 Excel 基础,工作表和单元格 单元格的格式 数据输入方法 条件格式工具 数据分列工具,1.2 Excel 基础,主要内容,工作表和单元格,1.2 Excel 基础,默认的Excel工作薄由三张工作表组成。,最多可以包括255张工作表； 最多可以输入256列。,工作表和单元格,1.2 Excel 基础,单元格是Excel工作表的基本元素。 单元格可分为数据单元格和公式单元格。,数据单元格,=SUM(B$8:B$11),=SUM(C$8:C$11),=SUM(D$8:D$11),=$B$5*B8+$C

5、$5*C8+$D$5*D8,=$B$5*B9+$C$5*C9+$D$5*D9,=$B$5*B10+$C$5*C10+$D$5*D10,=$B$5*B11+$C$5*C11+$D$5*D11,=SUM(F8:F11),工作表和单元格,1.2 Excel 基础,公式单元格,例1.1：,单元格的格式,Excel单元格有多种格式，包括：“常规、数值、货币、日期、时间、百分比、分数、科学计数、文本、特殊、自定义”等11种格式。,1.2 Excel 基础,选定需要定义格式的单元格，打开Excel菜单：格式单元格，弹出单元格格式对话窗口：,其中，“常规”和“文本”两种格式没有格式选项，其他格式都有不同的选项

6、。 例如“数值”格式有小数位数、是否选用千分位分隔符、负数的显示方式等选项。,1.2 Excel 基础,单元格的格式,日期格式: 可以把日期系列数显示为定义的日期格式。日期系列数是从1开始的整数，数字1表示1900年1月1日，2表示1900年1月2日等等。,1.2 Excel 基础,单元格的格式,日期系列数和日期格式的转换如下图所示：,=A2,=A3,=A20,1.2 Excel 基础,单元格的格式,不同的时间格式如下：,1.2 Excel 基础,单元格的格式,数据输入是数据分析和建模的第一步，采用正确的数据输入方法可以提高数据输入的效率、减少数据输入的错误。,数据输入方法,1.2 Excel

7、 基础,Excel数据输入的方法有： 直接键盘输入 用自定义格式输入 输入序列 用“有效性”工具输入 用条件函数IF输入 用字符提取函数RIGHT、LEFT、MID输入 用查找函数VLOOKUP输入,直接键盘输入 键盘输入是Excel数据输入最基本、最常用的输入方法。键盘输入遇到的常见问题有： 数值格式输入的数字最大为15位，多于15位的数字将自动转换成科学计数格式。 例如，常规或数值格式的单元格中输入18位身份证号码按回车确认后，显示为：3.3106E+17。因此，如果需要输入15位以上的数字，需要将单元格定义为文本格式，或者在常规或数值格式单元格中，

8、先输入撇号，然后输入数字。,数据输入方法,自定义格式输入,如果某些单元格中输入的数据有相同的字符，例如，出生于20世纪、出生地为杭州市的身份证号码前8个字符“33010619”，可以将需要输入身份证号码的单元格定义为“自定义格式”如下：“33010619”。这样，输入身份证号码时，只需要输入后面10个字符，即可完整显示身份证号码。,数据输入方法,以上“自定义格式”的对话窗口如下：,数据输入方法,用自定义格式输入后的效果如下图。其中黄色的单元格是用”33010619”自定义格式。,数据输入方法,输入序列 1，2，3，4，；一，二，三，四，；A，B，C，D，；甲，乙，丙，丁，；子，丑，寅，卯，等都

9、是序列。 数字序列的输入方法： 输入数字序列的前两个数字，拖动单元格右下十字钮。 或用Excel菜单“编辑填充序列”工具生成数据序列。,数据输入方法,1.2 Excel 基础,其他序列的方法： 打开Excel菜单“工具选项自定义序列”； 在对话框中输入一个序列，单击“添加”； 定义序列以后，就可以用数字序列一样的方法输入。 具体过程如下：,Excel菜单：“工具选项自定义系列”对话窗口如下：,数据输入方法,序列可以在对话窗口中直接输入，单击“添加”后生成。如下图：,数据输入方法,也可以事先在Excel表中输入序列，然后在对话窗口中选用“从单元格中导入序列”，选定$A$1:$A$7，单击“导入”

10、生成序列。这种方法比较适合生成较长的序列。,数据输入方法,1.2 Excel 基础,自定义序列后，只要输入序列第一个值，拖动单元格右下十字钮，就可以输入整个序列。如图：,数据输入方法,用“有效性”工具输入 Excel有效性工具是数据输入的常用方法，它可以提高输入效率，减少输入错误。 打开Excel菜单：“数据有效性”，出现以下“数据有效性”对话窗口。其中有四个卡片：“设置”、“输入信息”、“出错警告”和“输入法模式”。,数据输入方法,其中，“设置”卡片用来设置数据的有效性范围。例如，数值型数据可以设置最小值和最大值、字符型数据可以设置字符长度等。一旦输入的数据在有效范围以外，将会出现出错警告。

11、例如，职工“出生年月”的值应该在1949年（60岁）到1991年（18岁）之间；手机号码应该为11位，固话号码应该为8位，等等。,数据输入方法,如果输入的数据是职工出生年月，在“允许”下拉菜单中选定“日期”，输入“开始日期”和“结束”日期：,数据输入方法,如果输入的数据是职工身份证号码，在“允许”下拉中选定“文本长度”，“数据”下拉选定“等于”，“长度”输入18.,数据输入方法,“输入信息”卡片用于用户定制选定输入单元格时出现的提示信息。例如，对于输入身份证号码的单元格，提示信息如下：,数据输入方法,当选定输入单元格时，出现提示信息的效果如下：,数据输入方法,数据有效性工具还可以定制输入出错警

12、告。定制输入职工出生日期的“出错警告”如下图：,数据输入方法,定制输入职工身份证号码的“出错警告”如下图：,数据输入方法,输入的身份证号码不足18位时出现的错误警告。,数据输入方法,有效性工具中，可以建立输入项目的下拉菜单，特别适合输入定制的项目。 先选定单元格C2:C17 ，然后打开“允许”下拉菜单，选定“序列”。,数据输入方法,在“来源”中，输入定制项目单元格范围E2:E13，单击确定。 将光标置于选定的C2:C17的每一个单元格，都将出现下拉箭头。,数据输入方法,选定下拉菜单中的院校名称，就可以方便地输入所选内容。,数据输入方法,如果出现的条件有两个以上，可用嵌套的IF函数实现。例如：如

13、果奖金发放金额由考核等级确定：,设考核等级单元格为A2，计算奖金额度的函数为： IF(A2=“A”,2000,IF(A2=“B”,1000,0) IF函数嵌套的层数最多为7层。由于多层嵌套的IF函数构造比较复杂，容易出错。多个条件的情况，还是推荐用函数VLOOKUP来实现。,数据输入方法,用字符提取函数RIGHT、LEFT、MID输入（选读） 许多数据包含不同的信息，例如18位身份证号码中就包含了出生地、出生日期、性别等信息。身份证中不同位置的数字对应的信息如下：,要从身份证号码中提取相应的信息，就需要用以上三个函数。,数据输入方法,设身份证号码单元格为A2， 提取出生省份：LEFT(A2,2

14、)*10000 提取出生市：MID(A2,3,2)*100 提取出生县：MID(A2,3,4) 提取出生日期：MID(A2,7,4) 6) 根据对话框，选择或输入相关数据。,散点图中插入趋势线,5.1 一元线性回归,有两种方法求出单变量回归方程 1.散点图中插入趋势线 2.回归分析工具,作出“GDP”和“年用电量”的散点图,观察可以看出， “GDP”和“年用电量”具有相关关系。,散点图中插入趋势线,单击“图表/添加趋势线”（“图表”菜单只有散点图选中时才会出现）,散点图中插入趋势线,选择趋势线类型，选定“线性”,散点图中插入趋势线,单击“选项”，趋势线名称选定“自动设置”，选择“显示公式”和“

15、显示R平方值”。,散点图中插入趋势线,得到“年用电量”和“GDP”一元线性回归的图形、回归直线和相关系数R2的值,散点图中插入趋势线,在例5.1中，选择“年用电量”为因变量，“GDP”为自变量，进行一元线性回归。,回归分析工具,有两种方法求出单变量回归方程 1.散点图中插入趋势线 2.回归分析工具,5.1 一元线性回归,回归分析工具,有两种方法求出单变量回归方程 1.散点图中插入趋势线 2.回归分析工具,步骤： 1） 打开“工具”菜单； 2） 选择“数据分析”； 3） 选择“回归”。 4) 根据对话框，选择或输入相关,5.1 一元线性回归,打开Excel“工具/数据分析/回归”菜单，分别输入因

16、变量Y和自变量X的区域，选择在新工作表中输出结果。,回归分析工具,相关系数R,判定系数R2,观察值个数n,回归R,截距,自变量,回归系数,标准误差,回归分析工具,由上表可以看出，一元回归方程为：,年用电量=26768.264+0.0791 GDP,各年份年用电量的观测值和预测值的图形,回归分析工具,关于回归分析的说明： 用于衡量回归方程式优良性的工具通常是以下几种： 1.标准误差 用来说明回归方程代表性的大小。,其中：y是因变量实际值， 是根据回归方程推算出来的因变量估计值。 从计算公式可以看出，标准误差实际上是一个平均误差。数值越大，说明所有点离回归线越远，则回归方程的代表性越小；数值越小，说明所有点越靠近回归线，则回归方程的代表性越大。,2. 判定系数 反映自变量与因变量之间线性关系的强弱程度。 记实际值y的总变差为： 回归变差为： 则 ： 该公式表示因变量总差异中可以由回归解释的比例，数值越大，表明所有点越接近于回归直线。,判定系数R2,回归分析工具,年用电量=26768.264+0.0791 GD