EXCEL数据分析工具

1.1 数据1.2 Excel基础1.3 属性数据分析1.4 数据展示图1.5 数值型数据分析第第1章章 数据和数据分析数据和数据分析l根据数据的属性：数值型：123.1万元、23.5、4700点连续型数据：温度变化，如23.5、离散型数据：5个篮板、120个集装箱、属性型：用文字、判断等表示的数据天气晴、阴、雨；股市升、降；CPI高、中、低；质量好、中、差，属性型数据都是离散的。1.1 数据第第1章章 数据和数据分析数据和数据分析商务数据的分类l根据数据的变化 不随状态变化、相对稳定的数据称为静态数据。随状态变化的数据称为动态数据。项目数据数据性质姓名王大明静态性别男静态出生年月1970年4月静态年龄37岁动态出生地北京静态学历本科静态身高176厘米静态体重73公斤动态年收入13万元动态1.1 数据商务数据的分类时间序列数据时间序列数据截面数据年份199619971998199920002001200220032004农村居民家庭56.355.153.452.649.147.746.245.647.2城镇居民家庭48.846.644.742.139.438.237.737.137.71996年2004年农村与城镇家庭恩格尔系数（食品支出占家庭总支出的百分比，单位：）按照时间前后发生的数据称为时间序列数据。按照时间前后发生的数据称为时间序列数据。1.1 数据20022006年某市旅游接待与收入情况年某市旅游接待与收入情况 年份20022003200420052006人次（万）490229526565620人均消费（元）135138140145202收入总量（亿元）6.633.97.398.212.5l按时间划分序号地区2007年经济指标GDP（亿元）人口（万人）占地面积（平方千米）1汉中 291.21 379.36 27246 2商洛 135.8 238.45 19292 3安康 189.85 265.25 23539 陕南陕南 2007年经济年经济指标：指标：同一时间段发生的数据称为截面数据。同一时间段发生的数据称为截面数据。1.1 数据截面数据截面数据定类数据定类数据（Categorical/Nominal Data）由不同类别组成的数据称为定类数据。各类数据的类型和单位一般都不相同。不同类的数据之间有些可以运算，有些则不能运算。户总数（万户）总人口数（万人）男（万人）女（万人）全市204.52651.68332.62319.06市区120.56401.59204.43197.16桐庐县14.3239.4420.119.34淳安县14.6545.1623.3521.81建德市17.2150.8226.3724.45富阳市20.262.7832.0530.73临安市17.5951.8926.3125.571.1 数据l按相互关系划分定序数据定序数据（Ordinal Data）城市重庆德阳绵阳达县雅安西昌环境评价值环境评价值（无量纲）（无量纲）0.98830.1732 0.3172 0.7885 0.3713 0.6037评价等级评价等级三级一级二级三级二级二级仅表明对象的排列次序的数据称为定序数据，定序数据之间的差或比值一般没有意义。1.1 数据定距数据定距数据（Interval Data）数据没有绝对的零值，没有倍数、比例关系，只有相互之间的差距有实际意义。20082008年中国城市金融中心指数（引自年中国城市金融中心指数（引自“中国城市竞争力报告中国城市竞争力报告”）城市国际金融产业集聚金融业人才规模综合分上海 北京 深圳 天津 南京 杭州 西安 10.7470.2470.3030.1850.0280.00610.7340.470.3010.3740.1870.31510.9410.690.6410.4930.4920.3961.1 数据定比数据（定比数据（Ratio Data）定比数据具有明确的零点，可以计算数据的比例。例如2008年美国的GDP是中国的4.15倍。国家GDP（亿美元）人均（美元）1美国139800462802日本52900414803中国3370025204德国32800397105英国25700424306法国25200412007意大利20900359808西班牙14100308209加拿大136004147010俄罗斯1140080301.1 数据l根据数据的连续性 离散数据离散数据数据是整数或者若干可能的结果之一。例如顾客投诉的次数，产品中不合格的个数，天气是晴天、阴天或下雨。连续数据连续数据数据是连续变量。例如产品的成本、利润、交货时间等。1.1 数据数据数据按类型划分数值数据属性数据按相互关系划分时序数据截面数据定类数据定序数据定距数据定比数据连续数据离散数据按时间划分静态数据动态数据按变化划分1.1 数据EXCEL软件简介n Microsoft ExcelMicrosoft Excel是一个功能强大、使是一个功能强大、使用灵活方便的电子表格软件，也是最为流行用灵活方便的电子表格软件，也是最为流行的办公自动化软件，本课程主要利用的办公自动化软件，本课程主要利用EXCELEXCEL的统计分析功能和丰富的统计图表。的统计分析功能和丰富的统计图表。1.2 Excel 基础p 工作表和单元格工作表和单元格p 单元格的格式单元格的格式p 数据输入方法数据输入方法p 条件格式工具条件格式工具p 数据分列工具数据分列工具1.2 Excel 基础主要内容p工作表和单元格工作表和单元格1.2 Excel 基础默认的Excel工作薄由三张工作表组成。最多可以包括255张工作表；最多可以输入256列。p工作表和单元格工作表和单元格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*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)p工作表和单元格工作表和单元格1.2 Excel 基础公式单元格例例1.1：p 单元格的格式单元格的格式Excel单元格有多种格式，包括：“常规、数值、货币、日期、时间、百分比、分数、科学计数、文本、特殊、自定义”等11种格式。1.2 Excel 基础选定需要定义格式的单元格，打开Excel菜单：格式单元格，弹出单元格格式对话窗口：其中，“常规”和“文本”两种格式没有格式选项，其他格式都有不同的选项。例如“数值”格式有小数位数、是否选用千分位分隔符、负数的显示方式等选项。1.2 Excel 基础p 单元格的格式日期格式:可以把日期系列数显示为定义的日期格式。日期系列数是从1开始的整数，数字1表示1900年1月1日，2表示1900年1月2日等等。1.2 Excel 基础p 单元格的格式日期系列数和日期格式的转换如下图所示：=A2=A3=A201.2 Excel 基础p 单元格的格式不同的时间格式如下：1.2 Excel 基础p 单元格的格式数据输入是数据分析和建模的第一步，采用正确的数据输入方法可以提高数据输入的效率、减少数据输入的错误。p数据输入方法1.2 Excel 基础Excel数据输入的方法有：l直接键盘输入l用自定义格式输入l输入序列l用“有效性”工具输入l用条件函数IF输入l用字符提取函数RIGHT、LEFT、MID输入l用查找函数VLOOKUP输入l 直接键盘输入键盘输入是Excel数据输入最基本、最常用的输入方法。键盘输入遇到的常见问题有：数值格式输入的数字最大为15位，多于15位的数字将自动转换成科学计数格式。例如，常规或数值格式的单元格中输入18位身份证号码：330106195805210038，按回车确认后，显示为：3.3106E+17。因此，如果需要输入15位以上的数字，需要将单元格定义为文本格式，或者在常规或数值格式单元格中，先输入撇号，然后输入数字。p数据输入方法l自定义格式输入如果某些单元格中输入的数据有相同的字符，例如，出生于20世纪、出生地为杭州市的身份证号码前8个字符“33010619”，可以将需要输入身份证号码的单元格定义为“自定义格式”如下：“33010619”。这样，输入身份证号码时，只需要输入后面10个字符，即可完整显示身份证号码。p数据输入方法以上“自定义格式”的对话窗口如下：p 数据输入方法用自定义格式输入后的效果如下图。其中黄色的单元格是用”33010619”自定义格式。p数据输入方法l 输入序列1，2，3，4，；一，二，三，四，；A，B，C，D，；甲，乙，丙，丁，；子，丑，寅，卯，等都是序列。数字序列的输入方法：输入数字序列的前两个数字，拖动单元格右下十字钮。或用Excel菜单“编辑填充序列”工具生成数据序列。p数据输入方法1.2 Excel 基础 其他序列的方法：打开Excel菜单“工具选项自定义序列”；在对话框中输入一个序列，单击“添加”；定义序列以后，就可以用数字序列一样的方法输入。具体过程如下：Excel菜单：“工具选项自定义系列”对话窗口如下：p数据输入方法序列可以在对话窗口中直接输入，单击“添加”后生成。如下图：p数据输入方法也可以事先在Excel表中输入序列，然后在对话窗口中选用“从单元格中导入序列”，选定$A$1:$A$7，单击“导入”生成序列。这种方法比较适合生成较长的序列。p数据输入方法1.2 Excel 基础自定义序列后，只要输入序列第一个值，拖动单元格右下十字钮，就可以输入整个序列。如图：p数据输入方法l 用用“有效性有效性”工具输入工具输入Excel有效性工具是数据输入的常用方法，它可以提高输入效率，减少输入错误。打开Excel菜单：“数据有效性”，出现以下“数据有效性”对话窗口。其中有四个卡片：“设置”、“输入信息”、“出错警告”和“输入法模式”。p数据输入方法其中，“设置”卡片用来设置数据的有效性范围。例如，数值型数据可以设置最小值和最大值、字符型数据可以设置字符长度等。一旦输入的数据在有效范围以外，将会出现出错警告。例如，职工“出生年月”的值应该在1949年（60岁）到1991年（18岁）之间；手机号码应该为11位，固话号码应该为8位，等等。p数据输入方法如果输入的数据是职工出生年月，在“允许”下拉菜单中选定“日期”，输入“开始日期”和“结束”日期：p数据输入方法如果输入的数据是职工身份证号码，在“允许”下拉中选定“文本长度”，“数据”下拉选定“等于”，“长度”输入18.p数据输入方法“输入信息”卡片用于用户定制选定输入单元格时出现的提示信息。例如，对于输入身份证号码的单元格，提示信息如下：p数据输入方法当选定输入单元格时，出现提示信息的效果如下：p数据输入方法数据有效性工具还可以定制输入出错警告。定制输入职工出生日期的“出错警告”如下图：p数据输入方法定制输入职工身份证号码的“出错警告”如下图：数据输入方法输入的身份证号码不足18位时出现的错误警告。数据输入方法有效性工具中，可以建立输入项目的下拉菜单，特别适合输入定制的项目。先选定单元格C2:C17，然后打开“允许”下拉菜单，选定“序列序列”。p数据输入方法在“来源”中，输入定制项目单元格范围E2:E13，单击确定。将光标置于选定的C2:C17的每一个单元格，都将出现下拉箭头。数据输入方法选定下拉菜单中的院校名称，就可以方便地输入所选内容。数据输入方法如果出现的条件有两个以上，可用嵌套的IF函数实现。例如：如果奖金发放金额由考核等级确定：AB1考核等级奖金额度2A20003B10004C0设考核等级单元格为A2，计算奖金额度的函数为：IF(A2=“A”,2000,IF(A2=“B”,1000,0)IF函数嵌套的层数最多为7层。由于多层嵌套的IF函数构造比较复杂，容易出错。多个条件的情况，还是推荐用函数VLOOKUP来实现。数据输入方法l 用字符提取函数RIGHT、LEFT、MID输入（选读）许多数据包含不同的信息，例如18位身份证号码中就包含了出生地、出生日期、性别等信息。身份证中不同位置的数字对应的信息如下：123456789101112131415161718出生地出生日期序号出生省出生市年份月份日期性别出生县要从身份证号码中提取相应的信息，就需要用以上三个函数。数据输入方法123456789101112131415161718出生地出生日期序号出生省出生市年份月份日期性别出生县设身份证号码单元格为A2，提取出生省份：LEFT(A2,2)*10000提取出生市：MID(A2,3,2)*100提取出生县：MID(A2,3,4)提取出生日期：MID(A2,7,4)&”-”&MID(A2,11,2)&”-”&MID(A2,13,2)提取序号：RIGHT(A2,4)提取性别：MOD(MID(A2,17,1),2)返回值1为男性，0为女性数据输入方法l 用查找函数VLOOKUP输入（选读）在表格或数值数组的首列查找指定的数值，并由此返回表格或数组当前行中指定列处的数值。VLOOKUP 中的“V”代表垂直。VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)lookup_value 为需要在数组第一列中查找的数值。col_index_num 为 table_array 中待返回的匹配值的列序号。range_lookup 为一逻辑值，指明函数 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:$G$6,2,0)用VLOOKUP函数可以实现多个条件数据的查找和输入。数据输入方法l VLOOKUP在计算个人所得税中的应用=(B13-B14)*VLOOKUP(B13-B14,A2:E10,4,1)-VLOOKUP(B13-B14,A2:E10,5,1)数据输入方法“条件格式”工具是将符合一定的条件的单元格的格式（边框、底色、文本格式、文本颜色等）改变为用户定制的形式。条件格式常用于突出显示某些单元格的内容，也可以用于美化Excel表格。例如，在根据考核等级评定奖金的Excel表中，将考核等级为A用红色表示。选定“考核等级”B2:B13，打开Excel菜单：“格式条件格式”：p条件格式工具1.2 Excel 基础在“条件1”下拉菜单中选择“单元格数值”，中间下拉菜单选择“等于”，右侧下拉菜单输入“A”。然后单击“格式”，选定文本字体和颜色。单击“确定”。p条件格式工具1.2 Excel 基础得到效果如下：p条件格式工具1.2 Excel 基础在“条件格式”对话窗口中单击“添加”，可以增加第二个条件。“条件格式”最多可以有三个条件。p条件格式工具1.2 Excel 基础设置两个条件的“条件格式”产生的效果如下图：如果设置的多个条件是互相冲突的，不会出现任何提示，所定义的格式也不会起作用。p条件格式工具1.2 Excel 基础并不是所有的Excel的数据都需要用键盘输入，很多数据来自网络或其他信息系统，Excel读入这些数据后，往往需要根据数据不同的属性，将这些数据分为不同的列。Excel菜单：“数据分列”的数据分列工具就可以完成这一工作。Excel读入的数据有三种情况：读入的数据有三种情况：1.字段之间有空格、逗号、制表符等分割符。字段之间有空格、逗号、制表符等分割符。2.字段没有任何分隔符，但每个字段的长度是确定的。字段没有任何分隔符，但每个字段的长度是确定的。3.字段之间既没有分隔符，字段的长度也各不相同。字段之间既没有分隔符，字段的长度也各不相同。对于前两种情况，对于前两种情况，Excel分列工具可以方便地实现分列。第分列工具可以方便地实现分列。第三种情况则需要用户分析数据的特点，在字段之间插入分隔三种情况则需要用户分析数据的特点，在字段之间插入分隔符，然后再实现数据分列。符，然后再实现数据分列。p数据分列工具数据分列工具1.2 Excel 基础p工作表和单元格工作表和单元格p单元格的格式单元格的格式p数据输入方法数据输入方法p条件格式工具条件格式工具p数据分列工具数据分列工具从网上搜素并下载“身份证号码和地址对照表”如下：这个表只有一列，其中包括身份证号的前六位和相应的地址，两个字段之间有空格分隔。需要把身份证号和地址分为两列。p数据分列工具数据分列工具1.2 Excel 基础打开Excel菜单：“数据分列”，弹出“文本分列向导”，选择“分隔符号”，单击“下一步”。数据分列工具数据分列工具在“分隔符号”中选定“空格”，预览中出现分列的演示。单击“下一步”。数据分列工具数据分列工具设置数据的类型为“常规”，单击“完成”。数据分列工具数据分列工具这样就完成了数据分列。数据分列工具数据分列工具注意到这个数据的第一个字段“身份证号”是六个字符，因此也可以用“固定宽度”分列。选定“固定宽度”，单击“下一步”。数据分列工具数据分列工具用户可以用“拖动分列线”自定义列宽。单击“下一步”。数据分列工具数据分列工具同样可以完成分列。数据分列工具数据分列工具 实际应用中，所要分析的对象（公司、政府部门、人等）通常具有多个不同的属性，例如，部门、姓名、性别、年龄、职务、职称、专业等。在数据统计表中，这些数据称为“属性数据”。1.3 属性数据分析属性数据分析序号姓名性别生日专业部门职务职称学历月薪1邓赛鹏男1954年1月7日计算机控制研究室副所长高级工程师硕士78002吴起杭男1964年11月9日通信通信研究室工程师本科57003曹平原男1977年11月27日通信通信研究室工程师本科37004马良男1980年1月1日计算机控制研究室助理工程师本科31005顾琳英女1962年3月31日管理控制研究室工程师本科59006马大晖男1968年12月16日计算机控制研究室工程师硕士51007孙晓斌男1974年4月24日管理光电研究室工程师本科43008车大明男1969年10月28日通信通信研究室工程师本科50009侯显耀男1976年6月2日计算机图形研究室工程师本科3900第第1章章 数据和数据分析数据和数据分析1.1 数据1.2 Excel基础1.3 属性数据分析1.4 数据展示图1.5 数值型数据分析 实际应用中，所要分析的对象（公司、政府部门、人等）通常具有多个不同的属性，例如，部门、姓名、性别、年龄、职务、职称、专业等。在数据统计表中，这些数据称为“属性数据”。1.3 属性数据分析属性数据分析 运用数据透视表可以对属性数据进行统计分析运用数据透视表可以对属性数据进行统计分析。p1.3.1数据透视表数据透视表p1.3.2单变量属性数据分析单变量属性数据分析p1.3.3双变量属性数据分析双变量属性数据分析p1.3.4数据透视表在问卷调查中的应用数据透视表在问卷调查中的应用 数据透视表是Excel中的功能强大的工具。运用数据透视表可以对大量数据进行快速汇总，创建相应的多维统计表格。1.3 属性数据分析1.3.1数据透视表1.3 属性数据分析1.3.1数据透视表例如：例如：创建汇总统计表格 1.3 1.3 属性数据分析属性数据分析 1.3.2单变量属性数据分析单变量属性数据分析用途：对性别、职称、品牌等单列属性数据进行分布统计。用途：对性别、职称、品牌等单列属性数据进行分布统计。序号姓名性别生日专业部门职务职称学历月薪1邓赛鹏男1954年1月7日计算机控制研究室副所长高级工程师硕士78002吴起杭男1964年11月9日通信通信研究室工程师本科57003曹平原男1977年11月27日通信通信研究室工程师本科37004马良男1980年1月1日计算机控制研究室助理工程师本科31005顾琳英女1962年3月31日管理控制研究室工程师本科59006马大晖男1968年12月16日计算机控制研究室工程师硕士51007孙晓斌男1974年4月24日管理光电研究室工程师本科43008车大明男1969年10月28日通信通信研究室工程师本科50009侯显耀男1976年6月2日计算机图形研究室工程师本科39001.分析该研究所“男、女职工”的人数。n步骤：步骤：1.打开打开“数据数据”菜单；菜单；2.选择选择“数据透视表数据透视表”，按下一步；，按下一步；3.选择选择“布局布局”；4.单击变量单击变量“性别性别”，拖到，拖到“行行”区域中；区域中；5.单击变量单击变量“性别性别”，拖到，拖到“数据数据”区域中；区域中；6.双击双击“求和项求和项”，在，在“汇总方式汇总方式”中选择中选择“计计数数”；7.单击单击“确定确定”。1.3.2单变量属性数据分析单变量属性数据分析1.3 属性数据分析 示例：示例：对济南市饭店业进行前景调查，问卷数据如对济南市饭店业进行前景调查，问卷数据如下表所示。要求统计出下表所示。要求统计出业主类型业主类型的分类人数。的分类人数。1.3 属性数据分析1.3.2单变量属性数据分析单变量属性数据分析某 饭 店 调 查 数 据 序 号 问 卷 号 （ID）前 途 看 法 （o u tlo o k）业 主 类 型 （o w n e r）1 2 6 2 6 3 2 2 6 3 4 1 3 2 6 4 4 1 4 2 6 5 2 *5 2 6 6 1 3 6 2 6 7 3 1 7 2 6 8 3 3 8 2 6 9 4 2 9 2 7 0 5 3 1 0 2 7 1 4 2 11 2 7 2 3 1 1 2 2 7 3 1 1 前 途 看 法：1（很 不 乐 观）6（十 分 乐 观）业 主 类 型：1 股 份 制 2 合 伙 3 独 资 *-不 详 1.分析研究所的分析研究所的“职称结构职称结构”。序号姓名性别生日专业部门职务职称学历月薪1邓赛鹏男1954年1月7日计算机控制研究室副所长高级工程师硕士78002吴起杭男1964年11月9日通信通信研究室工程师本科57003曹平原男1977年11月27日通信通信研究室工程师本科37004马良男1980年1月1日计算机控制研究室助理工程师本科31005顾琳英女1962年3月31日管理控制研究室工程师本科59006马大晖男1968年12月16日计算机控制研究室工程师硕士51007孙晓斌男1974年4月24日管理光电研究室工程师本科43008车大明男1969年10月28日通信通信研究室工程师本科50009侯显耀男1976年6月2日计算机图形研究室工程师本科39001.3 属性数据分析例1.2，打开教学光盘中的“研究所员工资料”：1.3.2单变量属性数据分析单变量属性数据分析序号姓名性别生日专业部门职务职称学历月薪1邓赛鹏男1954年1月7日计算机控制研究室副所长高级工程师硕士78002吴起杭男1964年11月9日通信通信研究室工程师本科57003曹平原男1977年11月27日通信通信研究室工程师本科37004马良男1980年1月1日计算机控制研究室助理工程师本科31005顾琳英女1962年3月31日管理控制研究室工程师本科59006马大晖男1968年12月16日计算机控制研究室工程师硕士51007孙晓斌男1974年4月24日管理光电研究室工程师本科43008车大明男1969年10月28日通信通信研究室工程师本科50009侯显耀男1976年6月2日计算机图形研究室工程师本科39001.分析各部门中分析各部门中“男、女职工男、女职工”的人数。的人数。序号姓名性别生日专业部门职务职称学历月薪1邓赛鹏男1954年1月7日计算机控制研究室副所长高级工程师硕士78002吴起杭男1964年11月9日通信通信研究室工程师本科57003曹平原男1977年11月27日通信通信研究室工程师本科37004马良男1980年1月1日计算机控制研究室助理工程师本科31005顾琳英女1962年3月31日管理控制研究室工程师本科59006马大晖男1968年12月16日计算机控制研究室工程师硕士51007孙晓斌男1974年4月24日管理光电研究室工程师本科43008车大明男1969年10月28日通信通信研究室工程师本科50009侯显耀男1976年6月2日计算机图形研究室工程师本科39001.3 属性数据分析例1.2，打开教学光盘中的“研究所员工资料”：1.3.3双变量属性数据分析 两维表格（列联表）两维表格（列联表）用途：对性别、职称等属性数据进行相关关系描述。用途：对性别、职称等属性数据进行相关关系描述。1.3.1 数据透视表数据透视表1.3.2 单变量属性数据分析单变量属性数据分析1.3.3 双变量属性数据分析双变量属性数据分析1.3.4 数据透视表在问卷调查中的应用数据透视表在问卷调查中的应用序号姓名性别生日专业部门职务职称学历月薪1邓赛鹏男1954年1月7日计算机控制研究室副所长高级工程师硕士78002吴起杭男1964年11月9日通信通信研究室工程师本科57003曹平原男1977年11月27日通信通信研究室工程师本科37004马良男1980年1月1日计算机控制研究室助理工程师本科31005顾琳英女1962年3月31日管理控制研究室工程师本科59006马大晖男1968年12月16日计算机控制研究室工程师硕士51007孙晓斌男1974年4月24日管理光电研究室工程师本科43008车大明男1969年10月28日通信通信研究室工程师本科50009侯显耀男1976年6月2日计算机图形研究室工程师本科3900p数据透视表期望结果：二维列联表二维列联表1.3 属性数据分析1.3.3双变量属性数据分析 两维表格（列联表）两维表格（列联表）步骤：步骤：1.打开打开“数据数据”菜单；菜单；2.选择选择“数据透视表数据透视表”，按下一步；，按下一步；3.选择选择“版式版式”；4.单击变量单击变量“部门部门”，拖到，拖到“行行”区域中；区域中；5.单击变量单击变量“性别性别”，拖到，拖到“列列”区域中；区域中；5.单击变量单击变量“姓名姓名”，拖到，拖到“数据数据”区域中；区域中；6.双击双击“求和项求和项”，在，在“汇总方式汇总方式”中选择中选择“计计数数”；7.单击单击“确定确定”。打开菜单：“数据数据透视表和数据透视图”1.3 属性数据分析1.3.3双变量属性数据分析选择图表类型：选择数据区域：1.3 属性数据分析确定数据透视表生成的位置：1.3 属性数据分析接着，选择接着，选择“布局布局”:1.3.3双变量属性数据分析 分别将分别将“部门部门”拉到拉到行区域、行区域、“性别性别”拉到拉到列区域、列区域、“姓名姓名”拉到拉到数据区域。数据区域。1.3属性数据分析1.3.3双变量属性数据分析得到数据透视表：1.3属性数据分析1.3.3 双变量属性数据分析继续：继续：单击“部门部门”单元格的下拉菜单，可以选择统计的范围。例如，“部门”中不包括“办公室”：1.3 属性数据分析1.3.3双变量属性数据分析得到不包括部门“办公室”的人数统计：1.3 属性数据分析1.3.3双变量属性数据分析为什么拉到数据区域的是“姓名”，数据区域出现的数字是“人数”？右键单击数据区域，出现右键菜单，选定“字段属性”：1.3 属性数据分析1.3.3双变量属性数据分析可以看到默认的字段属性为“计数”，因此数据字段出现的数字是人数。1.3 属性数据分析1.3.3双变量属性数据分析2.分析分析“部门、职称、性别、学历部门、职称、性别、学历”平均工资。平均工资。序号姓名性别生日专业部门职务职称学历月薪1邓赛鹏男1954年1月7日计算机控制研究室副所长高级工程师硕士78002吴起杭男1964年11月9日通信通信研究室工程师本科57003曹平原男1977年11月27日通信通信研究室工程师本科37004马良男1980年1月1日计算机控制研究室助理工程师本科31005顾琳英女1962年3月31日管理控制研究室工程师本科59006马大晖男1968年12月16日计算机控制研究室工程师硕士51007孙晓斌男1974年4月24日管理光电研究室工程师本科43008车大明男1969年10月28日通信通信研究室工程师本科50009侯显耀男1976年6月2日计算机图形研究室工程师本科39001.3 属性数据分析1.3.3双变量属性数据分析 生成三维表格生成三维表格 在二维列联表上，把“学历”拉到“性别”下面，就可以产生三维列联表：1.3 属性数据分析1.3.3双变量属性数据分析 生成三维表格生成三维表格1.3 属性数据分析1.3.3双变量属性数据分析 生成三维表格生成三维表格 或者，在二维列联表上，把“职称”拉到“部门”右边，也可以产生三维列联表：1.3 属性数据分析1.3.3双变量属性数据分析 生成三维表格生成三维表格 或者，在二维列联表上，把“职称”拉到“部门”右边，也可以产生三维列联表：1.3 属性数据分析1.3.3双变量属性数据分析 生成三维表格生成三维表格 把“职称”拉到“部门”右边，把“学历”拉到“性别”下面，就可以产生四维列联表：1.3 属性数据分析1.3.3双变量属性数据分析 生成四维表格生成四维表格1.3 属性数据分析1.3.3双变量属性数据分析生成四维表格生成四维表格 把“职称”拉到“部门”右边，把“学历”拉到“性别”下面，把把“姓名姓名”拖出数据透视表外，把拖出数据透视表外，把“月薪月薪”拉拉进数据区域，进数据区域，就可以产生数据区域为“月薪”的四维列联表：1.3 属性数据分析1.3.3双变量属性数据分析 生成四维表格，且数据区域为生成四维表格，且数据区域为“月月薪薪”把“职称”拉到“部门”右边，把“学历”拉到“性别”下面，把“月薪”拉到数据区域，就可以产生数据区域为“月薪”的四维列联表：1.3 属性数据分析1.3.3双变量属性数据分析 生成四维表格，且数据区域为生成四维表格，且数据区域为“月月薪薪”右键单击数据区域，选择“字段设置字段设置”，将数据区域的属性定义为“平均工资”，则数据区域的数字是“合计工资”。同样的方法，也可以求“最高工资”、“最低工资”或“求和”。1.3属性数据分析1.3.3双变量属性数据分析 生成四维表格，且数据区域为生成四维表格，且数据区域为“月月薪薪”四维列联表如下：1.3 属性数据分析1.3.3双变量属性数据分析生成四维表格，且数据区域为生成四维表格，且数据区域为“月薪月薪”1.4 数据展示图数据展示图1.1 数据1.2 Excel基础1.3 属性数据分析1.4 数据展示图1.5 数值型数据分析p Excel数据展示图形有13种。p 柱形图垂直柱状组成的图形称为柱形图，水平条状组成的图形称为条形图。1.4 数据展示图数据展示图年份第一产业第二产业第三产业20011.541184.875003.3153020021.611735.298023.6074820031.692816.127413.91880 20042.076817.238724.37206例例1.7 创建我国创建我国20012004年第一、第二和第三产业产值年第一、第二和第三产业产值数量的变化的柱形图。数量的变化的柱形图。1.打开Excel表，单击“插入图标”工具图标，选择图表类型柱形图和子图标类型，点击“下一步”。p 用图表和曲线图展示数据1.4 数据展示图图表向导2.从“数据区域”选择目标数据，用鼠标选定B4:D8，单击“系列”卡片，点击“下一步”。p 用图表和曲线图展示数据1.4 数据展示图3.在“系列”中，选择输入系列名称，用鼠标选定“分类(X)轴标志”A5:A8，单击“下一步”p 用图表和曲线图展示数据1.4 数据展示图4.输入或修改“标题”、“坐标轴”、“网格线”、“图例”、“数据标志”、“数据表”等属性，单击“下一步”。p 用图表和曲线图展示数据1.4 数据展示图5.选择图表位置，单击“完成”。p 用图表和曲线图展示数据1.4 数据展示图6.图表完成。如果需要，可以双击图表中任何一部分进行修改。p 用图表和曲线图展示数据图表区分类轴分类轴标题数值轴图例图表标题系列“第一产业”绘图区数值轴主要网格线数值轴标题系列“第二产业”系列“第三产业”1.4 数据展示图 条形图的制作是类似柱形图。垂直柱状组成的图形称为柱形图，水平条状组成的图形称为条形图。例例1.8：我国20012004年三次产业的条形图如下：p 条形图1.4 数据展示图例1.9 2004年我国三类产业产值p 饼图 饼图常用来表示一项数据中各部分的比例。1.4 数据展示图年份第一产业第二产业第三产业20042.0768 7.2387 4.3721 p 折线图常用来表示数据随时间或项目不同的变化。年份总量煤 炭石 油天然气水电19785714440400.81 12971.69 1828.61 1942.90 19806027543518.55 12476.93 1868.53 2411.00 19857668258124.96 13112.62 1687.00 3757.42 19899693473766.77 16575.71 2035.61 4555.90 19909870375211.69 16384.70 2072.76 5033.85 199110378378978.86 17746.89 2075.66 4981.58 199210917082641.69 19104.75 2074.23 5349.33 199311599386646.77 21110.73 2203.87 6031.64 199412273792052.75 21356.24 2332.00 6996.01 例例1.10 1978年2004年我国能源消费总量及组成（单位：万吨标煤）1.4 数据展示图试创建“19782004年中国能源消费.xls”中的“总量”、“煤炭”、“石油”、“天然气”、“水电”消费量的折线图。1.4 数据展示图p折线图例例1.11 原油消费量和天然气消费量的散点图p 散点图用于表示两个变量数值之间的相关关系。1.4 数据展示图例1.12 有两家企业A和B，它们的资产属性评估结果如下表：收益性 安全性 流动性 成长性 生产性企业10.70.80.80.60.9企业20.90.60.40.50.7p 雷达图用来表示两组变量相应数值的比较。1.4 数据展示图例1.13 2004年10省市的GDP和人均GDP数据。p 气泡图可以对一组数据的三个指标进行展示和比较。1.4 数据展示图序号地 区GDP（亿元）人均GDP（元/人）1广东16039 197072山东15491 169253江苏15403 207054浙江11243 239425河南8815 94706河北8769 129187上海7450 553078辽宁6873 162979湖北6310 1050010福建6053 17218例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.3 属性数据分析1.4 数据展示图1.5 数值型数据分析1.5 数值型数据分析1.5.1.数值型单变量数据分析数值型单变量数据分析例 某班DMD考试成绩如下：80 85 76 78 67 93 88 90 75 66 65 77 74 80 85 76 78 67 93 88 90 75 66 65 77 74 83 81 70 83 64 96 60 79 86 80 7183 81 70 83 64 96 60 79 86 80 71试用相关统计量对成绩进行分析。ExcelExcel有有3 3种分析工具用来描述数值型单种分析工具用来描述数值型单变量数据：变量数据：1.1.描述性统计描述性统计2.2.直方图直方图3.3.排位和百分比排位排位和百分比排位1.5.1.数值型单变量数据分析p描述性统计（数据的分布属性）数据的分布属性包括数据的集中属性、数据的分散属性以及数据频数的形态三大类：n 数据集中属性的指标l均值Averagel中位数Medianl众数Moden 数据的分散属性的指标l极差Rangel方差Variance l标准差Standard Deviationl标准误Standard Errorn 数据频数的分布形态指标l偏度Skewnessl峰度Kurtosis1.5.1.数值型单变量数据分析p描述性统计（数据的分布属性）数据的分布属性包括数据的集中属性、数据的分散属性以及数据频数的形态三大类：n 数据集中属性的指标l均值Averagel中位数Medianl众数Moden 数据的分散属性的指标l极差Rangel方差Variance l标准差Standard Deviationl标准误Standard Errorn 数据频数的分布形态指标l偏度Skewnessl峰度Kurtosis1.5.1.数值型单变量数据分析Excel中的值型单变量数中的值型单变量数据统计功能有以下三种据统计功能有以下三种实现方法实现方法1.描述性统计描述性统计2.直方图直方图3.排位和百分比排位排位和百分比排位 步骤：步骤：1）打开）打开“工具工具”菜单；菜单；2）选择）选择“数据分析数据分析”；3）选择）选择“描述统计描述统计”；4)根据对话框，选择或输入相关数据。根据对话框，选择或输入相关数据。nC:My Documents模拟成绩.xlsp描述性统计（数据的分布属性）1.5.1.数值型单变量数据分析数值型单变量数据分析步骤：步骤：单击“工具”/“数据分析”：p描述性统计（数据的分布属性）例 某班DMD考试成绩统计。打开Excel菜单：“工具数据分析描述统计描述统计”:选择“描述统计”，单击“确定”。p描述性统计（数据的分布属性）1.5.1.数值型单变量数据分析数值型单变量数据分析步骤：步骤：选择“输入区域”、“分组方式”，在“输出选项”中选择“新工作表组”和新工作表名称，选定“汇总统计”。单击“确定”。DMDp描述性统计（数据的分布属性）步骤：步骤：得到DMD考试成绩的汇总统计结果：注：Excel2003中文版描述统计工具输出结果中，“区域”是“Range”的误译，应该译为“全距”。p描述性统计（数据的分布属性）l算术平均数算术平均数(Average)l设总体包括N个观察值x1，x2，.，xN，则总体平均值（均值）为：Nxnii1l设n个样本为x1，x2，xn，样本平均值（均值）为：nxxnii1p描述性统计（数据的分布属性）有关指标说明有关指标说明1.5.1.数值型单变量数据分析数值型单变量数据分析=AVERAGE(B2:B24)一组数据按大小顺序排列以后，处于中间位置的数据。对于奇数组，中位数是中间的一个，对于偶数组，中位数是中间的两个的平均值。l中位数(Median)=MEDIAN(B2:B24)1.5.1.数值型单变量数据分析数值型单变量数据分析众数是一组数据出现次数最多的数值。如果一组数据各不相同，则这组众数是一组数据出现次数最多的数值。如果一组数据各不相同，则这组数据不存在众数数据不存在众数.l众数(Mode)=MODE(B2:B24)1.5.1.数值型单变量数据分析数值型单变量数据分析全距是一组数据x1，x2，xn中最大值和最小值之差。即：max(x1，x2，xn)-min(x1，x2，xn)全距可以表示数据的离散程度，全距越大离散程度越大。全距只和数据中两个数值有关，容易受到奇异数据的影响。数据A的全距=18-11=7，数据B的全距=39-3=36，数据B的离散程度大于数据A。全距（Range）离散属性的测度1.5.1.数值型单变量数据分析数值型单变量数据分析方差分为总体方差和样本方差。n 总体方差的计算公式为：Nxnii122)(其中xi为第i个数据，N为总体数据的个数，为总体平均数。方差是数据离散程度的指标，方差越大，离散程度越大。方差的单位是数据单位的平方。Excel计算总体方差的函数是VARP。方差（Variance）1.5.1.数值型单变量数据分析数值型单变量数据分析序号xixi-(xi-)2111-39213-113162441841651511613-11712-241425.14序号xixi-(xi-)213-1112127-74931511439256255140065-98174-10100142139.57方差（Variance）n样本方差的计算公式为1)(122nxxsnii由于实际处理的数据大多数都是样本数据，因此样本方差应用更多。Excel计算样本方差的函数是VAR。1.5.1.数值型单变量数据分析数值型单变量数据分析标准差（Standard deviation）标准差是方差的平方根。n 计算总体标准差的公式为：Nxnii12)(n计算样本标准差的公式为：1)(12nxxsnii标准差的单位和数据的单位相同，因此比方差更容易理解，应用也更多。1.5.1.数值型单变量数据分析数值型单变量数据分析在工业生产中，产品的指标总会出现波动，一般会把产品指标的变动控制在指标平均值加减3个标准差的范围内，认为这是生产正常的标志。96624.002953.929543.如果产品指标波动超出这个范围，说明生产系统不正常。标准差的重要性1.5.1.数值型单变量数据分析数值型单变量数据分析 在投资风险分析中，评价投资收益有两个指标，一个是收益期望值（平均值），另一个是收益的标准差。投资收益的标准差表示投资风险的大小，标准差越大，风险越大。收益期望值收益标准差低收益低风险低收益高风险高收益低风险高收益高风险1.5.1.数值型单变量数据分析数值型单变量数据分析 在经济领域中，标准差常用来反映未来数据的不确定性，有主要的理论和应用价值。标准差是测定数据离散程度的标志，但由于不同的数据数值大小不同，相同的离散程度，数值比较大的标准差也会比较大。以下是不同年龄组每周上网时间的统计数据：为了比较大小不同的几组数据的离散程度，定义如下的变异系数（Coefficient of Variation）：均值标准差）变异系数（CV变异系数18岁以下18-24岁25-30岁31-40岁40岁以上均值6.588 20.922 20.293 16.324 15.281 标准差2.368 2.221 2.888 2.780 3.658 变异系数0.359 0.106 0.142 0.170 0.239 1.5.1.数值型单变量数据分析数值型单变量数据分析正态 CS=0.57左偏 CS=0.92右偏 CS=-0.27 偏态系数（Coefficient of Skewness）-1 -0.5 0 0.5 1 近似对称稍右偏稍左偏明显右偏明显左偏CS平均数中位数众数 对称分布平均数中位数众数右偏众数中位数平均数左偏分布形态的测度1.5.1.数值型单变量数据分析数值型单变量数据分析313)()2)(1(niixnnnCS Excel中有计算数据偏度的函数“=SKEW(数据范围)”，Excel的偏度公式为：有的文献计算数据偏度还有其他的公式。例如SPSS计算偏度的公式为：313)()1(1niixnCS1.5.1.数值型单变量数据分析数值型单变量数据分析分布形态的测度峰态系数测定分布形态的峰态系数（Coefficient of Kurtosis）CK0 尖峰分布CK=-1.13CK=18.241.5.1.数值型单变量数据分析数值型单变量数据分析)3)(2()1(3)()3)(2)(1()1(2414nnnxnnnnnCKnii Excel中有计算数据峰度的函数KURT，Excel的偏度公式为：有的文献计算数据峰度度还有其他的公式。例如SPSS计算偏度的公式为：3)()1(1414niixnCK1.5.1.数值型单变量数据分析数值型单变量数据分析 Excel以上对数据的描述统计的每一项内容，也可以单独用相应的Excel函数计算。这些函数是：均值AVERAGE 中位数MEDIAN众数MODE标准差STDEVP（总体）STDEV（样本）方差VARP（总体）VAR（样本）偏度SKEW峰度KURT最大值MAX最小值MIN求和SUM计数COUNT1.5.1.数值型单变量数据分析数值型单变量数据分析p直方图直方图Excel中的数值型单变量数中的数值型单变量数据统计功能有以下三种实据统计功能有以下三种实现方法现方法1.描述性统计描述性统计2.直方图直方图3.排位和百分比排位排位和百分比排位该统计工具提供一张频数分布表和一张直方图。该统计工具提供一张频数分布表和一张直方图。步骤：步骤：1）确定直方图的区间个数，填入每个组界值；）确定直方图的区间个数，填入每个组界值；2）打开）打开“工具工具”菜单；菜单；3）选择）选择“数据分析数据分析”；4）选择）选择“直方图直方图”；5）在）在“直方图直方图”对话框中填入数据。对话框中填入数据。C:My Documents模拟成绩.xls1.5.1.数值型单变量数据分析数值型单变量数据分析例 绘制“某班DMD考试成绩”频数直方图。首先，计算DMD考试成绩的最小值和最大值；其次，确定频数统计的分布区间：p直方图直方图Excel中的统计功能有以下中的统计功能有以下三种实现方法三种实现方法1.描述性统计描述性统计2.直方图直方图3.排位和百分比排位排位和百分比排位1.5.1.数值型单变量数据分析数值型单变量数据分析打开菜单：工具数据分析，出现以下窗口：选择“直方图”，单击“确定”。Excel“工具数据分析直方图”1.5.1.数值型单变量数据分析数值型单变量数据分析p直方图直方图“输入区域”为B2:B25，“接收区域”为G2:G7，“输出区域”为I17，选定“图表输出”，单击“确定”。1.5.1.数值型单变量数据分析数值型单变量数据分析p直方图直方图Excel“工具数据分析直方图对话框”得到某班DMD考试成绩的频数和频数直方图：$J$21.5.1.数值型单变量数据分析数值型单变量数据分析p直方图直方图即有：在指定位置I17生成发生频数统计表和频数直方图：注：Excel2003中文版直方图工具输出结果中，frequency误译为“频率”，在统计学中应译为“频数”。1.5.1.数值型单变量数据分析数值型单变量数据分析p直方图直方图书中例1.15：绘制“饮料全年日销售量”频数直方图。首先，计算365天销售量的最小值和最大值；其次，确定频数统计的分布区间：p直方图直方图Excel中的统计功能有以下中的统计功能有以下三种实现方法三种实现方法1.描述性统计描述性统计2.直方图直方图3.排位和百分比排位排位和百分比排位1.5.1.数值型单变量数据分析数值型单变量数据分析打开菜单：工具数据分析，出现以下窗口：选择“直方图”，单击“确定”。Excel“工具数据分析直方图”“输入区域”为B2:B366，“接收区域”为H2:H15，“输出区域”为J2，选定“累积百分率”和“图表输出”，单击“确定”。得到饮料销售量356个数据的频数和频数直方图：$J$2p直方图即有：在指定位置J2生成发生频数统计表和频数直方图：接收（箱）频率（天）累积%13010.2714092.741504113.971606130.681704