EXCEL数据分析工具.ppt

《EXCEL数据分析工具.ppt》由会员分享，可在线阅读，更多相关《EXCEL数据分析工具.ppt（192页珍藏版）》请在三一办公上搜索。

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 数据,商务数据的分类,时间序列数据截面数据,1996年2004年农村与城镇家庭

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

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

4、XCEL的统计分析功能和丰富的统计图表。,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*C8+$D$5*D8,=$B$5*B9+$C$5*

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 基础,Excel数据输入的方法有：直接键盘输入用自定义格式

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

8、纪、出生地为杭州市的身份证号码前8个字符“33010619”，可以将需要输入身份证号码的单元格定义为“自定义格式”如下：“33010619”。这样，输入身份证号码时，只需要输入后面10个字符，即可完整显示身份证号码。,数据输入方法,以上“自定义格式”的对话窗口如下：,数据输入方法,用自定义格式输入后的效果如下图。其中黄色的单元格是用”33010619”自定义格式。,数据输入方法,输入序列1，2，3，4，；一，二，三，四，；A，B，C，D，；甲，乙，丙，丁，；子，丑，寅，卯，等都是序列。数字序列的输入方法：输入数字序列的前两个数字，拖动单元格右下十字钮。或用Excel菜单“编辑填充序列”工具生成

9、数据序列。,数据输入方法,1.2 Excel 基础,其他序列的方法：打开Excel菜单“工具选项自定义序列”；在对话框中输入一个序列，单击“添加”；定义序列以后，就可以用数字序列一样的方法输入。具体过程如下：,Excel菜单：“工具选项自定义系列”对话窗口如下：,数据输入方法,序列可以在对话窗口中直接输入，单击“添加”后生成。如下图：,数据输入方法,也可以事先在Excel表中输入序列，然后在对话窗口中选用“从单元格中导入序列”，选定$A$1:$A$7，单击“导入”生成序列。这种方法比较适合生成较长的序列。,数据输入方法,1.2 Excel 基础,自定义序列后，只要输入序列第一个值，拖动单元格右

10、下十字钮，就可以输入整个序列。如图：,数据输入方法,用“有效性”工具输入Excel有效性工具是数据输入的常用方法，它可以提高输入效率，减少输入错误。打开Excel菜单：“数据有效性”，出现以下“数据有效性”对话窗口。其中有四个卡片：“设置”、“输入信息”、“出错警告”和“输入法模式”。,数据输入方法,其中，“设置”卡片用来设置数据的有效性范围。例如，数值型数据可以设置最小值和最大值、字符型数据可以设置字符长度等。一旦输入的数据在有效范围以外，将会出现出错警告。例如，职工“出生年月”的值应该在1949年（60岁）到1991年（18岁）之间；手机号码应该为11位，固话号码应该为8位，等等。,数据输

11、入方法,如果输入的数据是职工出生年月，在“允许”下拉菜单中选定“日期”，输入“开始日期”和“结束”日期：,数据输入方法,如果输入的数据是职工身份证号码，在“允许”下拉中选定“文本长度”，“数据”下拉选定“等于”，“长度”输入18.,数据输入方法,“输入信息”卡片用于用户定制选定输入单元格时出现的提示信息。例如，对于输入身份证号码的单元格，提示信息如下：,数据输入方法,当选定输入单元格时，出现提示信息的效果如下：,数据输入方法,数据有效性工具还可以定制输入出错警告。定制输入职工出生日期的“出错警告”如下图：,数据输入方法,定制输入职工身份证号码的“出错警告”如下图：,数据输入方法,输入的身份证号

12、码不足18位时出现的错误警告。,数据输入方法,有效性工具中，可以建立输入项目的下拉菜单，特别适合输入定制的项目。先选定单元格C2:C17，然后打开“允许”下拉菜单，选定“序列”。,数据输入方法,在“来源”中，输入定制项目单元格范围E2:E13，单击确定。将光标置于选定的C2:C17的每一个单元格，都将出现下拉箭头。,数据输入方法,选定下拉菜单中的院校名称，就可以方便地输入所选内容。,数据输入方法,如果出现的条件有两个以上，可用嵌套的IF函数实现。例如：如果奖金发放金额由考核等级确定：,设考核等级单元格为A2，计算奖金额度的函数为：IF(A2=“A”,2000,IF(A2=“B”,1000,0)

13、IF函数嵌套的层数最多为7层。由于多层嵌套的IF函数构造比较复杂，容易出错。多个条件的情况，还是推荐用函数VLOOKUP来实现。,数据输入方法,用字符提取函数RIGHT、LEFT、MID输入（选读）许多数据包含不同的信息，例如18位身份证号码中就包含了出生地、出生日期、性别等信息。身份证中不同位置的数字对应的信息如下：,要从身份证号码中提取相应的信息，就需要用以上三个函数。,数据输入方法,设身份证号码单元格为A2，提取出生省份：LEFT(A2,2)*10000提取出生市：MID(A2,3,2)*100提取出生县：MID(A2,3,4)提取出生日期：MID(A2,7,4)&”-”&MID(A2,

14、11,2)&”-”&MID(A2,13,2)提取序号：RIGHT(A2,4)提取性别：MOD(MID(A2,17,1),2)返回值1为男性，0为女性,数据输入方法,用查找函数VLOOKUP输入（选读）在表格或数值数组的首列查找指定的数值，并由此返回表格或数组当前行中指定列处的数值。VLOOKUP 中的“V”代表垂直。VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)lookup_value 为需要在数组第一列中查找的数值。col_index_num 为 table_array 中待返回的匹配值的列序号。range_looku

15、p 为一逻辑值，指明函数 VLOOKUP 返回时是精确匹配还是近似匹配。如果为 TRUE（1）或省略，则返回近似匹配值，也就是说，如果找不到精确匹配值，则返回小于 lookup_value 的最大数值；如果 range_value 为 FALSE（0），函数 VLOOKUP 将返回精确匹配值。table_array 为需要在其中查找数据的数据表。对于非精确匹配查找，table_array的第一列必须排序，对于精确匹配查找，则不必排序。,数据输入方法,=VLOOKUP(B3,$G$2:$G$6,2,0),=VLOOKUP(B2,$G$2:$G$6,2,0),=VLOOKUP(B13,$G$2:$

16、G$6,2,0),用VLOOKUP函数可以实现多个条件数据的查找和输入。,数据输入方法,VLOOKUP在计算个人所得税中的应用,=(B13-B14)*VLOOKUP(B13-B14,A2:E10,4,1)-VLOOKUP(B13-B14,A2:E10,5,1),数据输入方法,“条件格式”工具是将符合一定的条件的单元格的格式（边框、底色、文本格式、文本颜色等）改变为用户定制的形式。条件格式常用于突出显示某些单元格的内容，也可以用于美化Excel表格。例如，在根据考核等级评定奖金的Excel表中，将考核等级为A用红色表示。选定“考核等级”B2:B13，打开Excel菜单：“格式条件格式”：,条件格

17、式工具,1.2 Excel 基础,在“条件1”下拉菜单中选择“单元格数值”，中间下拉菜单选择“等于”，右侧下拉菜单输入“A”。然后单击“格式”，选定文本字体和颜色。单击“确定”。,条件格式工具,1.2 Excel 基础,得到效果如下：,条件格式工具,1.2 Excel 基础,在“条件格式”对话窗口中单击“添加”，可以增加第二个条件。“条件格式”最多可以有三个条件。,条件格式工具,1.2 Excel 基础,设置两个条件的“条件格式”产生的效果如下图：,如果设置的多个条件是互相冲突的，不会出现任何提示，所定义的格式也不会起作用。,条件格式工具,1.2 Excel 基础,并不是所有的Excel的数据

18、都需要用键盘输入，很多数据来自网络或其他信息系统，Excel读入这些数据后，往往需要根据数据不同的属性，将这些数据分为不同的列。Excel菜单：“数据分列”的数据分列工具就可以完成这一工作。Excel读入的数据有三种情况：字段之间有空格、逗号、制表符等分割符。字段没有任何分隔符，但每个字段的长度是确定的。字段之间既没有分隔符，字段的长度也各不相同。对于前两种情况，Excel分列工具可以方便地实现分列。第三种情况则需要用户分析数据的特点，在字段之间插入分隔符，然后再实现数据分列。,数据分列工具,1.2 Excel 基础,工作表和单元格单元格的格式数据输入方法条件格式工具数据分列工具,从网上搜素并

19、下载“身份证号码和地址对照表”如下：,这个表只有一列，其中包括身份证号的前六位和相应的地址，两个字段之间有空格分隔。需要把身份证号和地址分为两列。,数据分列工具,1.2 Excel 基础,打开Excel菜单：“数据分列”，弹出“文本分列向导”，选择“分隔符号”，单击“下一步”。,数据分列工具,在“分隔符号”中选定“空格”，预览中出现分列的演示。单击“下一步”。,数据分列工具,设置数据的类型为“常规”，单击“完成”。,数据分列工具,这样就完成了数据分列。,数据分列工具,注意到这个数据的第一个字段“身份证号”是六个字符，因此也可以用“固定宽度”分列。选定“固定宽度”，单击“下一步”。,数据分列工具

20、,用户可以用“拖动分列线”自定义列宽。单击“下一步”。,数据分列工具,同样可以完成分列。,数据分列工具,实际应用中，所要分析的对象（公司、政府部门、人等）通常具有多个不同的属性，例如，部门、姓名、性别、年龄、职务、职称、专业等。在数据统计表中，这些数据称为“属性数据”。,1.3 属性数据分析,第1章 数据和数据分析,1.1 数据1.2 Excel基础1.3 属性数据分析1.4 数据展示图1.5 数值型数据分析,实际应用中，所要分析的对象（公司、政府部门、人等）通常具有多个不同的属性，例如，部门、姓名、性别、年龄、职务、职称、专业等。在数据统计表中，这些数据称为“属性数据”。,1.3 属性数据分

21、析,运用数据透视表可以对属性数据进行统计分析。,数据透视表单变量属性数据分析双变量属性数据分析数据透视表在问卷调查中的应用,数据透视表是Excel中的功能强大的工具。运用数据透视表可以对大量数据进行快速汇总，创建相应的多维统计表格。,1.3 属性数据分析,数据透视表,1.3 属性数据分析,数据透视表,例如：,创建汇总统计表格,1.3 属性数据分析 单变量属性数据分析,用途：对性别、职称、品牌等单列属性数据进行分布统计。,1.分析该研究所“男、女职工”的人数。,步骤：1.打开“数据”菜单；2.选择“数据透视表”，按下一步；3.选择“布局”；4.单击变量“性别”，拖到“行”区域中；5.单击变量“性

22、别”，拖到“数据”区域中；6.双击“求和项”，在“汇总方式”中选择“计数”；7.单击“确定”。,单变量属性数据分析,1.3 属性数据分析,示例：对济南市饭店业进行前景调查，问卷数据如下表所示。要求统计出业主类型的分类人数。,1.3 属性数据分析,单变量属性数据分析,分析研究所的“职称结构”。,1.3 属性数据分析,例1.2，打开教学光盘中的“研究所员工资料”：,单变量属性数据分析,分析各部门中“男、女职工”的人数。,1.3 属性数据分析,例1.2，打开教学光盘中的“研究所员工资料”：,双变量属性数据分析 两维表格（列联表）,用途：对性别、职称等属性数据进行相关关系描述。,1.3.1 数据透视表

23、1.3.2 单变量属性数据分析1.3.3 双变量属性数据分析1.3.4 数据透视表在问卷调查中的应用,数据透视表,二维列联表,1.3 属性数据分析,双变量属性数据分析 两维表格（列联表）,步骤：1.打开“数据”菜单；2.选择“数据透视表”，按下一步；3.选择“版式”；4.单击变量“部门”，拖到“行”区域中；5.单击变量“性别”，拖到“列”区域中；5.单击变量“姓名”，拖到“数据”区域中；6.双击“求和项”，在“汇总方式”中选择“计数”；7.单击“确定”。,打开菜单：“数据数据透视表和数据透视图”,1.3 属性数据分析,双变量属性数据分析,选择图表类型：,选择数据区域：,1.3 属性数据分析,确

24、定数据透视表生成的位置：,1.3 属性数据分析,接着，选择“布局”:,双变量属性数据分析,分别将“部门”拉到行区域、“性别”拉到列区域、“姓名”拉到数据区域。,1.3属性数据分析,双变量属性数据分析,得到数据透视表：,1.3属性数据分析,1.3.3 双变量属性数据分析,继续：单击“部门”单元格的下拉菜单，可以选择统计的范围。例如，“部门”中不包括“办公室”：,1.3 属性数据分析,双变量属性数据分析,得到不包括部门“办公室”的人数统计：,1.3 属性数据分析,双变量属性数据分析,为什么拉到数据区域的是“姓名”，数据区域出现的数字是“人数”？右键单击数据区域，出现右键菜单，选定“字段属性”：,1

25、.3 属性数据分析,双变量属性数据分析,可以看到默认的字段属性为“计数”，因此数据字段出现的数字是人数。,1.3 属性数据分析,双变量属性数据分析,2.分析“部门、职称、性别、学历”平均工资。,1.3 属性数据分析,双变量属性数据分析 生成三维表格,在二维列联表上，把“学历”拉到“性别”下面，就可以产生三维列联表：,1.3 属性数据分析,双变量属性数据分析 生成三维表格,1.3 属性数据分析,双变量属性数据分析 生成三维表格,或者，在二维列联表上，把“职称”拉到“部门”右边，也可以产生三维列联表：,1.3 属性数据分析,双变量属性数据分析 生成三维表格,或者，在二维列联表上，把“职称”拉到“部

26、门”右边，也可以产生三维列联表：,1.3 属性数据分析,双变量属性数据分析 生成三维表格,把“职称”拉到“部门”右边，把“学历”拉到“性别”下面，就可以产生四维列联表：,1.3 属性数据分析,双变量属性数据分析 生成四维表格,1.3 属性数据分析,双变量属性数据分析生成四维表格,把“职称”拉到“部门”右边，把“学历”拉到“性别”下面，把“姓名”拖出数据透视表外，把“月薪”拉进数据区域，就可以产生数据区域为“月薪”的四维列联表：,1.3 属性数据分析,双变量属性数据分析 生成四维表格，且数据区域为“月薪”,把“职称”拉到“部门”右边，把“学历”拉到“性别”下面，把“月薪”拉到数据区域，就可以产生

27、数据区域为“月薪”的四维列联表：,1.3 属性数据分析,双变量属性数据分析 生成四维表格，且数据区域为“月薪”,右键单击数据区域，选择“字段设置”，将数据区域的属性定义为“平均工资”，则数据区域的数字是“合计工资”。同样的方法，也可以求“最高工资”、“最低工资”或“求和”。,1.3属性数据分析,双变量属性数据分析 生成四维表格，且数据区域为“月薪”,四维列联表如下：,1.3 属性数据分析,双变量属性数据分析生成四维表格，且数据区域为“月薪”,1.4 数据展示图,1.1 数据1.2 Excel基础1.3 属性数据分析1.4 数据展示图1.5 数值型数据分析,Excel数据展示图形有13种。,柱形

28、图垂直柱状组成的图形称为柱形图，水平条状组成的图形称为条形图。,1.4 数据展示图,例1.7 创建我国20012004年第一、第二和第三产业产值数量的变化的柱形图。,1.打开Excel表，单击“插入图标”工具图标，选择图表类型柱形图和子图标类型，点击“下一步”。,用图表和曲线图展示数据,1.4 数据展示图,图表向导,2.从“数据区域”选择目标数据，用鼠标选定B4:D8，单击“系列”卡片，点击“下一步”。,用图表和曲线图展示数据,1.4 数据展示图,3.在“系列”中，选择输入系列名称，用鼠标选定“分类(X)轴标志”A5:A8，单击“下一步”,用图表和曲线图展示数据,1.4 数据展示图,4.输入或

29、修改“标题”、“坐标轴”、“网格线”、“图例”、“数据标志”、“数据表”等属性，单击“下一步”。,用图表和曲线图展示数据,1.4 数据展示图,5.选择图表位置，单击“完成”。,用图表和曲线图展示数据,1.4 数据展示图,6.图表完成。如果需要，可以双击图表中任何一部分进行修改。,用图表和曲线图展示数据,图表区,分类轴,分类轴标题,数值轴,图例,图表标题,系列“第一产业”,绘图区,数值轴主要网格线,数值轴标题,系列“第二产业”,系列“第三产业”,1.4 数据展示图,条形图的制作是类似柱形图。垂直柱状组成的图形称为柱形图，水平条状组成的图形称为条形图。例1.8：我国20012004年三次产业的条形

30、图如下：,条形图,1.4 数据展示图,例1.9 2004年我国三类产业产值,饼图 饼图常用来表示一项数据中各部分的比例。,1.4 数据展示图,折线图常用来表示数据随时间或项目不同的变化。,例1.10 1978年2004年我国能源消费总量及组成（单位：万吨标煤）,1.4 数据展示图,试创建“19782004年中国能源消费.xls”中的“总量”、“煤炭”、“石油”、“天然气”、“水电”消费量的折线图。,1.4 数据展示图,折线图,例1.11 原油消费量和天然气消费量的散点图,散点图用于表示两个变量数值之间的相关关系。,1.4 数据展示图,例1.12 有两家企业A和B，它们的资产属性评估结果如下表：

31、,雷达图用来表示两组变量相应数值的比较。,1.4 数据展示图,例1.13 2004年10省市的GDP和人均GDP数据。,气泡图可以对一组数据的三个指标进行展示和比较。,1.4 数据展示图,例1.13 2004年10省市的GDP和人均GDP数据。,1.4 数据展示图,气泡的高度表示GDP总量的大小，气泡的大小表示人均GDP的高低。,数值型数据分析的主要内容，包括：1.5.1.数值型单变量数据分析 1.5.2.数值型双变量数据分析,1.5 数值型数据分析,Excel中的数值型数据统计功能有以下二种实现方法使用Excel菜单的数据分析工具使用Excel统计函数,1.1 数据1.2 Excel基础1.

32、3 属性数据分析1.4 数据展示图1.5 数值型数据分析,1.5 数值型数据分析,1.5.1.数值型单变量数据分析例 某班DMD考试成绩如下：80 85 76 78 67 93 88 90 75 66 65 77 74 83 81 70 83 64 96 60 79 86 80 71试用相关统计量对成绩进行分析。,Excel有3种分析工具用来描述数值型单变量数据：,1.描述性统计2.直方图3.排位和百分比排位,1.5.1.数值型单变量数据分析,描述性统计（数据的分布属性）,数据的分布属性包括数据的集中属性、数据的分散属性以及数据频数的形态三大类：数据集中属性的指标均值Average中位数Med

33、ian众数Mode 数据的分散属性的指标极差Range方差Variance 标准差Standard Deviation标准误Standard Error 数据频数的分布形态指标偏度Skewness峰度Kurtosis,1.5.1.数值型单变量数据分析,描述性统计（数据的分布属性）,数据的分布属性包括数据的集中属性、数据的分散属性以及数据频数的形态三大类：数据集中属性的指标均值Average中位数Median众数Mode 数据的分散属性的指标极差Range方差Variance 标准差Standard Deviation标准误Standard Error 数据频数的分布形态指标偏度Skewness

34、峰度Kurtosis,1.5.1.数值型单变量数据分析,Excel中的值型单变量数据统计功能有以下三种实现方法1.描述性统计2.直方图3.排位和百分比排位,步骤：1）打开“工具”菜单；2）选择“数据分析”；3）选择“描述统计”；4)根据对话框，选择或输入相关数据。C:My Documents模拟成绩.xls,描述性统计（数据的分布属性）,1.5.1.数值型单变量数据分析,步骤：单击“工具”/“数据分析”：,描述性统计（数据的分布属性）,例 某班DMD考试成绩统计。,打开Excel菜单：“工具数据分析描述统计”:,选择“描述统计”，单击“确定”。,描述性统计（数据的分布属性）,1.5.1.数值型

35、单变量数据分析,步骤：,选择“输入区域”、“分组方式”，在“输出选项”中选择“新工作表组”和新工作表名称，选定“汇总统计”。单击“确定”。,DMD,描述性统计（数据的分布属性）,步骤：,得到DMD考试成绩的汇总统计结果：,注：Excel2003中文版描述统计工具输出结果中，“区域”是“Range”的误译，应该译为“全距”。,描述性统计（数据的分布属性）,算术平均数(Average)设总体包括N个观察值x1，x2，.，xN，则总体平均值（均值）为：,设n个样本为x1，x2，xn，样本平均值（均值）为：,描述性统计（数据的分布属性）有关指标说明,1.5.1.数值型单变量数据分析,=AVERAGE(

36、B2:B24),一组数据按大小顺序排列以后，处于中间位置的数据。对于奇数组，中位数是中间的一个，对于偶数组，中位数是中间的两个的平均值。,中位数(Median),=MEDIAN(B2:B24),1.5.1.数值型单变量数据分析,众数是一组数据出现次数最多的数值。如果一组数据各不相同，则这组数据不存在众数.,众数(Mode),=MODE(B2:B24),1.5.1.数值型单变量数据分析,全距是一组数据x1，x2，xn中最大值和最小值之差。即：max(x1，x2，xn)-min(x1，x2，xn)全距可以表示数据的离散程度，全距越大离散程度越大。全距只和数据中两个数值有关，容易受到奇异数据的影响。

37、,数据A的全距=18-11=7，数据B的全距=39-3=36，数据B的离散程度大于数据A。,全距（Range）离散属性的测度,1.5.1.数值型单变量数据分析,方差分为总体方差和样本方差。总体方差的计算公式为：,其中xi为第i个数据，N为总体数据的个数，为总体平均数。方差是数据离散程度的指标，方差越大，离散程度越大。方差的单位是数据单位的平方。Excel计算总体方差的函数是VARP。,方差（Variance）,1.5.1.数值型单变量数据分析,方差（Variance）,样本方差的计算公式为,由于实际处理的数据大多数都是样本数据，因此样本方差应用更多。Excel计算样本方差的函数是VAR。,1.

38、5.1.数值型单变量数据分析,标准差（Standard deviation）,标准差是方差的平方根。计算总体标准差的公式为：,计算样本标准差的公式为：,标准差的单位和数据的单位相同，因此比方差更容易理解，应用也更多。,1.5.1.数值型单变量数据分析,在工业生产中，产品的指标总会出现波动，一般会把产品指标的变动控制在指标平均值加减3个标准差的范围内，认为这是生产正常的标志。,如果产品指标波动超出这个范围，说明生产系统不正常。,标准差的重要性,1.5.1.数值型单变量数据分析,在投资风险分析中，评价投资收益有两个指标，一个是收益期望值（平均值），另一个是收益的标准差。投资收益的标准差表示投资风险

39、的大小，标准差越大，风险越大。,收益期望值,收益标准差,低收益低风险,低收益高风险,高收益低风险,高收益高风险,1.5.1.数值型单变量数据分析,在经济领域中，标准差常用来反映未来数据的不确定性，有主要的理论和应用价值。,标准差是测定数据离散程度的标志，但由于不同的数据数值大小不同，相同的离散程度，数值比较大的标准差也会比较大。以下是不同年龄组每周上网时间的统计数据：,为了比较大小不同的几组数据的离散程度，定义如下的变异系数（Coefficient of Variation）：,变异系数,1.5.1.数值型单变量数据分析,正态 CS=0.57,左偏 CS=0.92,右偏 CS=-0.27,偏态

40、系数（Coefficient of Skewness）,平均数中位数众数对称分布平均数中位数众数右偏众数中位数平均数左偏,分布形态的测度,1.5.1.数值型单变量数据分析,Excel中有计算数据偏度的函数“=SKEW(数据范围)”，Excel的偏度公式为：,有的文献计算数据偏度还有其他的公式。例如SPSS计算偏度的公式为：,1.5.1.数值型单变量数据分析,分布形态的测度峰态系数,测定分布形态的峰态系数（Coefficient of Kurtosis）,CK0 尖峰分布,CK=-1.13,CK=18.24,1.5.1.数值型单变量数据分析,Excel中有计算数据峰度的函数KURT，Excel的

41、偏度公式为：,有的文献计算数据峰度度还有其他的公式。例如SPSS计算偏度的公式为：,1.5.1.数值型单变量数据分析,Excel以上对数据的描述统计的每一项内容，也可以单独用相应的Excel函数计算。这些函数是：,均值AVERAGE 中位数MEDIAN众数MODE标准差STDEVP（总体）STDEV（样本）方差VARP（总体）VAR（样本）偏度SKEW峰度KURT最大值MAX最小值MIN求和SUM计数COUNT,1.5.1.数值型单变量数据分析,直方图,Excel中的数值型单变量数据统计功能有以下三种实现方法1.描述性统计2.直方图3.排位和百分比排位,该统计工具提供一张频数分布表和一张直方图

42、。步骤：1）确定直方图的区间个数，填入每个组界值；2）打开“工具”菜单；3）选择“数据分析”；4）选择“直方图”；5）在“直方图”对话框中填入数据。C:My Documents模拟成绩.xls,1.5.1.数值型单变量数据分析,例 绘制“某班DMD考试成绩”频数直方图。首先，计算DMD考试成绩的最小值和最大值；其次，确定频数统计的分布区间：,直方图,Excel中的统计功能有以下三种实现方法1.描述性统计2.直方图3.排位和百分比排位,1.5.1.数值型单变量数据分析,打开菜单：工具数据分析，出现以下窗口：,选择“直方图”，单击“确定”。,Excel“工具数据分析直方图”,1.5.1.数值型单变

43、量数据分析,直方图,“输入区域”为B2:B25，“接收区域”为G2:G7，“输出区域”为I17，选定“图表输出”，单击“确定”。,1.5.1.数值型单变量数据分析,直方图,Excel“工具数据分析直方图对话框”,得到某班DMD考试成绩的频数和频数直方图：,$J$2,1.5.1.数值型单变量数据分析,直方图,即有：在指定位置I17生成发生频数统计表和频数直方图：,注：Excel2003中文版直方图工具输出结果中，frequency误译为“频率”，在统计学中应译为“频数”。,1.5.1.数值型单变量数据分析,直方图,书中例1.15：绘制“饮料全年日销售量”频数直方图。首先，计算365天销售量的最小

44、值和最大值；其次，确定频数统计的分布区间：,直方图,Excel中的统计功能有以下三种实现方法1.描述性统计2.直方图3.排位和百分比排位,1.5.1.数值型单变量数据分析,打开菜单：工具数据分析，出现以下窗口：,选择“直方图”，单击“确定”。,Excel“工具数据分析直方图”,“输入区域”为B2:B366，“接收区域”为H2:H15，“输出区域”为J2，选定“累积百分率”和“图表输出”，单击“确定”。,得到饮料销售量356个数据的频数和频数直方图：,$J$2,直方图,即有：在指定位置J2生成发生频数统计表和频数直方图：,用Excel函数FREQUENCY生成数据频数图,先用鼠标选择准备好的频数

45、区域I2:I15，然后在函数栏中输入函数“=FREQUENCY()”。从提示可以看出，这个函数有两个参数，第一个参数data_array为销售量 B2:B366，第二个参数bin_array为区间H2:H15。,直方图,输入完毕后，按组合键“CTRL+SHIFT+ENTER”，频数就出现在I2:I15区域中。Excel有一些函数，它们的输出结果不是产生的一个单元格中，而是同时产生的一个区域中，这样的函数称为“向量函数”。FREQUENCY就是一个向量函数。,用Excel函数FREQUENCY生成数据频数图,直方图,绘制I2:I15频数柱形图如图1.54，得到和“工具数据分析直方图”同样的结果。

46、,用Excel函数FREQUENCY生成数据频数图,直方图,该统计工具提供一张含有升序排列的数据表格以及其排列后的序号、百分位号。步骤：1）打开“工具”菜单；2）选择“数据分析”；3）选择“排位与百分比排位”。4)根据对话框，选择或输入相关数据。,排位和百分比排位,1.5.1.数值型单变量数据分析,Excel中的统计功能有以下三种实现方法1.描述性统计2.直方图3.排位和百分比排位,排位和百分比排位,1.5.1.数值型单变量数据分析,Excel中的统计功能有以下三种实现方法1.描述性统计2.直方图3.排位和百分比排位,得到某班DMD考试成绩的数据排序表格。,1.5.2.数值型双变量数据分析,对

47、两组数值型数据分析它们之间的相关关系。有两种分析工具。1.散点图2.相关系数,变量之间的因果关系固定资产投资增加和GDP增长二氧化碳排放量和平均气温上升吸烟数量和年限与肺癌的发病率,变量之间的相关关系,变量之间的相关关系 海水平均温度与灾害性天气天数（厄尔尼诺现象）公司高尔夫球水平与公司股票市场表现 超市啤酒销售量和纸尿布销售量,1.5.2.数值型双变量数据分析,散点图,该统计工具用来描述两个变量之间的关系。通常用在直观判断基本关系或用在回归模型使用前。步骤：1）打开Excel表，输入变量X和Y的数值；2）选中数据；3）单击“图表向导”；4）选择图表类型XY散点图 5）对散点图进行编辑。,1.

48、5.2.数值型双变量数据分析,有两种分析工具描述数据之间的相关关系。1.散点图2.相关系数,例：,某公司欲调查办公楼的租金和空置率间的关系，工作人员在10个不同的城市里，对办公楼中每平方米的月租金和空置率的数据记录如下：试作散点图，并判断两变量间的相关性。,散点图,散点图,相关系数用来说明两个变量之间的相关密切程度。步骤：1）打开“工具”菜单；2）选择“数据分析”；3）选择“相关系数”。4)根据对话框，选择或输入相关数据。,相关系数,1.5.2.数值型双变量数据分析,有两种分析工具描述数据之间的相关关系。1.散点图2.相关系数,选择“相关系数”,相关系数,步骤：,选择变量范围，“输出选项”选择

49、“新工作表组”，单击“确定”。,步骤：,相关系数,相关系数,相关系数的基本计算公式：两个数列的协方差：自变量数列的标准差：因变量数列的标准差：,相关程度判断标准：,1.当 R=1时，X与Y为线性相关；2.当R0.3时，X与Y为微弱相关；3.当 0.3R0.5时，X与Y为低度相关；4.当 0.5R0.8时，X与Y为显著相关；5.当 0.8R1时，X与Y为高度相关。R越接近1，变量的相关关系越明显。,相关系数,5.1 一元线性回归5.2 多元线性回归5.3 非线性回归,第5章 回归分析,一、数据与统计第1章 数据和数据分析第5章 回归分析第6章 统计预测,5.1 一元线性回归,回归分析是一种分析随

50、机变量之间相关关系的统计理论和统计方法，可以根据自变量的取值，预测相应的因变量的取值。在工业、农业、商业以及经济学、社会学、医学等领域有着非常广泛的应用。,一些变量之间存在相关关系。例如一个城市的空调产品的销售量和夏季平均气温或冬季平均气温有相关关系，股票市场的价格和银行存贷款利率水平有相关关系。如果能够建立这些相关关系的数量表达式，就可以根据一个变量的值来预测另一个变量的变化。回归分析的目的就是构建变量之间数量关系的模型。回归分析包括只有一个自变量的一元回归和包括多个自变量的多元回归。只有一个自变量的回归设自变量为x，因变量为y，它们之间的数量关系可以有以下几种：,5.1 一元线性回归,单变