EXCEL数据分析工具PPT课件

.,1.1数据1.2Excel基础1.3属性数据分析1.4数据展示图1.5数值型数据分析,第1章数据和数据分析,.,根据数据的属性：数值型：123.1万元、23.5、4700点连续型数据：温度变化，如23.5、离散型数据：5个篮板、120个集装箱、属性型：用文字、判断等表示的数据天气晴、阴、雨；股市升、降；CPI高、中、低；质量好、中、差，属性型数据都是离散的。,1.1数据,第1章数据和数据分析,商务数据的分类,.,根据数据的变化不随状态变化、相对稳定的数据称为静态数据。随状态变化的数据称为动态数据。,1.1数据,商务数据的分类,.,时间序列数据截面数据,1996年2004年农村与城镇家庭恩格尔系数（食品支出占家庭总支出的百分比，单位：）,按照时间前后发生的数据称为时间序列数据。,1.1数据,20022006年某市旅游接待与收入情况,按时间划分,.,陕南2007年经济指标：,同一时间段发生的数据称为截面数据。,1.1数据,截面数据,.,定类数据（Categorical/NominalData）由不同类别组成的数据称为定类数据。各类数据的类型和单位一般都不相同。不同类的数据之间有些可以运算，有些则不能运算。,1.1数据,按相互关系划分,.,定序数据（OrdinalData）,仅表明对象的排列次序的数据称为定序数据，定序数据之间的差或比值一般没有意义。,1.1数据,定距数据（IntervalData）数据没有绝对的零值，没有倍数、比例关系，只有相互之间的差距有实际意义。,1.1数据,.,定比数据（RatioData）,定比数据具有明确的零点，可以计算数据的比例。例如2008年美国的GDP是中国的4.15倍。,1.1数据,.,根据数据的连续性离散数据数据是整数或者若干可能的结果之一。例如顾客投诉的次数，产品中不合格的个数，天气是晴天、阴天或下雨。连续数据数据是连续变量。例如产品的成本、利润、交货时间等。,1.1数据,.,1.1数据,.,EXCEL软件简介,MicrosoftExcel是一个功能强大、使用灵活方便的电子表格软件，也是最为流行的办公自动化软件，本课程主要利用EXCEL的统计分析功能和丰富的统计图表。,1.2Excel基础,.,工作表和单元格单元格的格式数据输入方法条件格式工具数据分列工具,1.2Excel基础,主要内容,.,工作表和单元格,1.2Excel基础,默认的Excel工作薄由三张工作表组成。,最多可以包括255张工作表；最多可以输入256列。,.,工作表和单元格,1.2Excel基础,单元格是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),工作表和单元格,1.2Excel基础,公式单元格,例1.1：,.,单元格的格式,Excel单元格有多种格式，包括：“常规、数值、货币、日期、时间、百分比、分数、科学计数、文本、特殊、自定义”等11种格式。,1.2Excel基础,选定需要定义格式的单元格，打开Excel菜单：格式单元格，弹出单元格格式对话窗口：,.,其中，“常规”和“文本”两种格式没有格式选项，其他格式都有不同的选项。例如“数值”格式有小数位数、是否选用千分位分隔符、负数的显示方式等选项。,1.2Excel基础,单元格的格式,.,日期格式:可以把日期系列数显示为定义的日期格式。日期系列数是从1开始的整数，数字1表示1900年1月1日，2表示1900年1月2日等等。,1.2Excel基础,单元格的格式,.,日期系列数和日期格式的转换如下图所示：,=A2,=A3,=A20,1.2Excel基础,单元格的格式,.,不同的时间格式如下：,1.2Excel基础,单元格的格式,.,数据输入是数据分析和建模的第一步，采用正确的数据输入方法可以提高数据输入的效率、减少数据输入的错误。,数据输入方法,1.2Excel基础,Excel数据输入的方法有：直接键盘输入用自定义格式输入输入序列用“有效性”工具输入用条件函数IF输入用字符提取函数RIGHT、LEFT、MID输入用查找函数VLOOKUP输入,.,直接键盘输入键盘输入是Excel数据输入最基本、最常用的输入方法。键盘输入遇到的常见问题有：数值格式输入的数字最大为15位，多于15位的数字将自动转换成科学计数格式。例如，常规或数值格式的单元格中输入18位身份证号码按回车确认后，显示为：3.3106E+17。因此，如果需要输入15位以上的数字，需要将单元格定义为文本格式，或者在常规或数值格式单元格中，先输入撇号，然后输入数字。,数据输入方法,.,自定义格式输入,如果某些单元格中输入的数据有相同的字符，例如，出生于20世纪、出生地为杭州市的身份证号码前8个字符“33010619”，可以将需要输入身份证号码的单元格定义为“自定义格式”如下：“33010619”。这样，输入身份证号码时，只需要输入后面10个字符，即可完整显示身份证号码。,数据输入方法,.,以上“自定义格式”的对话窗口如下：,数据输入方法,.,用自定义格式输入后的效果如下图。其中黄色的单元格是用”33010619”自定义格式。,数据输入方法,.,输入序列1，2，3，4，；一，二，三，四，；A，B，C，D，；甲，乙，丙，丁，；子，丑，寅，卯，等都是序列。数字序列的输入方法：输入数字序列的前两个数字，拖动单元格右下十字钮。或用Excel菜单“编辑填充序列”工具生成数据序列。,数据输入方法,1.2Excel基础,其他序列的方法：打开Excel菜单“工具选项自定义序列”；在对话框中输入一个序列，单击“添加”；定义序列以后，就可以用数字序列一样的方法输入。具体过程如下：,.,Excel菜单：“工具选项自定义系列”对话窗口如下：,数据输入方法,.,序列可以在对话窗口中直接输入，单击“添加”后生成。如下图：,数据输入方法,.,也可以事先在Excel表中输入序列，然后在对话窗口中选用“从单元格中导入序列”，选定$A$1:$A$7，单击“导入”生成序列。这种方法比较适合生成较长的序列。,数据输入方法,1.2Excel基础,.,自定义序列后，只要输入序列第一个值，拖动单元格右下十字钮，就可以输入整个序列。如图：,数据输入方法,.,用“有效性”工具输入Excel有效性工具是数据输入的常用方法，它可以提高输入效率，减少输入错误。打开Excel菜单：“数据有效性”，出现以下“数据有效性”对话窗口。其中有四个卡片：“设置”、“输入信息”、“出错警告”和“输入法模式”。,数据输入方法,.,其中，“设置”卡片用来设置数据的有效性范围。例如，数值型数据可以设置最小值和最大值、字符型数据可以设置字符长度等。一旦输入的数据在有效范围以外，将会出现出错警告。例如，职工“出生年月”的值应该在1949年（60岁）到1991年（18岁）之间；手机号码应该为11位，固话号码应该为8位，等等。,数据输入方法,.,如果输入的数据是职工出生年月，在“允许”下拉菜单中选定“日期”，输入“开始日期”和“结束”日期：,数据输入方法,.,如果输入的数据是职工身份证号码，在“允许”下拉中选定“文本长度”，“数据”下拉选定“等于”，“长度”输入18.,数据输入方法,.,“输入信息”卡片用于用户定制选定输入单元格时出现的提示信息。例如，对于输入身份证号码的单元格，提示信息如下：,数据输入方法,.,当选定输入单元格时，出现提示信息的效果如下：,数据输入方法,.,数据有效性工具还可以定制输入出错警告。定制输入职工出生日期的“出错警告”如下图：,数据输入方法,.,定制输入职工身份证号码的“出错警告”如下图：,数据输入方法,.,输入的身份证号码不足18位时出现的错误警告。,数据输入方法,.,有效性工具中，可以建立输入项目的下拉菜单，特别适合输入定制的项目。先选定单元格C2:C17，然后打开“允许”下拉菜单，选定“序列”。,数据输入方法,.,在“来源”中，输入定制项目单元格范围E2:E13，单击确定。将光标置于选定的C2:C17的每一个单元格，都将出现下拉箭头。,数据输入方法,.,选定下拉菜单中的院校名称，就可以方便地输入所选内容。,数据输入方法,.,如果出现的条件有两个以上，可用嵌套的IF函数实现。例如：如果奖金发放金额由考核等级确定：,设考核等级单元格为A2，计算奖金额度的函数为：IF(A2=“A”,2000,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(A2,7,4)6)根据对话框，选择或输入相关数据。,散点图中插入趋势线,5.1一元线性回归,有两种方法求出单变量回归方程1.散点图中插入趋势线2.回归分析工具,.,作出“GDP”和“年用电量”的散点图,观察可以看出，“GDP”和“年用电量”具有相关关系。,散点图中插入趋势线,.,单击“图表/添加趋势线”（“图表”菜单只有散点图选中时才会出现）,散点图中插入趋势线,.,选择趋势线类型，选定“线性”,散点图中插入趋势线,.,单击“选项”，趋势线名称选定“自动设置”，选择“显示公式”和“显示R平方值”。,散点图中插入趋势线,.,得到“年用电量”和“GDP”一元线性回归的图形、回归直线和相关系数R2的值,散点图中插入趋势线,.,在例5.1中，选择“年用电量”为因变量，“GDP”为自变量，进行一元线性回归。,回归分析工具,有两种方法求出单变量回归方程1.散点图中插入趋势线2.回归分析工具,5.1一元线性回归,.,回归分析工具,有两种方法求出单变量回归方程1.散点图中插入趋势线2.回归分析工具,步骤：1）打开“工具”菜单；2）选择“数据分析”；3）选择“回归”。4)根据对话框，选择或输入相关,5.1一元线性回归,.,打开Excel“工具/数据分析/回归”菜单，分别输入因变量Y和自变量X的区域，选择在新工作表中输出结果。,回归分析工具,相关系数R,判定系数R2,观察值个数n,回归R,截距,自变量,回归系数,标准误差,回归分析工具,由上表可以看出，一元回归方程为：,年用电量=26768.264+0.0791GDP,.,各年份年用电量的观测值和预测值的图形,回归分析工具,关于回归分析的说明：用于衡量回归方程式优良性的工具通常是以下几种：1.标准误差用来说明回归方程代表性的大小。,其中：y是因变量实际值，是根据回归方程推算出来的因变量估计值。从计算公式可以看出，标准误差实际上是一个平均误差。数值越大，说明所有点离回归线越远，则回归方程的代表性越小；数值越小，说明所有点越靠近回归线，则回归方程的代表性越大。,2.判定系数反映自变量与因变量之间线性关系的强弱程度。记实际值y的总变差为：回归变差为：则：该公式表示因变量总差异中可以由回归解释的比例，数值越大，表明所有点越接近于回归直线。,判定系数R2,回归分析工具,年用电量=26768.264+0.0791GDP,