EXCEL数据分析工具.ppt

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位身份证号码：330106195805210038，按回车确认后，显示为：3.3106E+17。因此，如果需要输入15位以上的数字，需要将单元格定义为文本格式，或者在常规或数值格式单元格中，先输入撇号，然后输入数字。,数据输入方法,自定义格式输入,如果某些单元格中输入的

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

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

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

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

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

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

14、2,7,4)&”-”&MID(A2,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 中待返回的匹

15、配值的列序号。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),=

16、VLOOKUP(B13,$G$2:$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菜单：“格式条件格式”：,条件格式工具,1.2 Excel 基础,在“条件1”下拉菜单中选择“单元格数值”，中间下拉菜单选择“等于”，右侧下拉菜单输入“A”。然后单击“格式”，选定文本字体和颜色。单击“确定”。,条件格式工具,1.2 Excel 基础,得到效果如下：,条件格式工具,1.2 Excel 基础,在“条件格式”对话窗口中单击“添加”，可以增加第二个条件。“条件格式”最多可以有三个条件