EXCEL数据分析工具.ppt
- 文档编号:18695926
- 上传时间:2023-09-17
- 格式:PPT
- 页数:192
- 大小:5.89MB
EXCEL数据分析工具.ppt
《EXCEL数据分析工具.ppt》由会员分享,可在线阅读,更多相关《EXCEL数据分析工具.ppt(192页珍藏版)》请在冰点文库上搜索。
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位身份证号码:
330106195805210038,按回车确认后,显示为:
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)&”-”&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中待返回的匹配值的列序号。
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函数可以实现多个条件数据的查找和输入。
数据输入方法,VLOOKUP在计算个人所得税中的应用,=(B13-B14)*VLOOKUP(B13-B14,A2:
E10,4,1)-VLOOKUP(B13-B14,A2:
E10,5,1),数据输入方法,“条件格式”工具是将符合一定的条件的单元格的格式(边框、底色、文本格式、文本颜色等)改变为用户定制的形式。
条件格式常用于突出显示某些单元格的内容,也可以用于美化Excel表格。
例如,在根据考核等级评定奖金的Excel表中,将考核等级为A用红色表示。
选定“考核等级”B2:
B13,打开Excel菜单:
“格式条件格式”:
条件格式工具,1.2Excel基础,在“条件1”下拉菜单中选择“单元格数值”,中间下拉菜单选择“等于”,右侧下拉菜单输入“A”。
然后单击“格式”,选定文本字体和颜色。
单击“确定”。
条件格式工具,1.2Excel基础,得到效果如下:
条件格式工具,1.2Excel基础,在“条件格式”对话窗口中单击“添加”,可以增加第二个条件。
“条件格式”最多可以有三个条件。
条件格式工具,1.2Excel基础,设置两个条件的“条件格式”产生的效果如下图:
如果设置的多个条件是互相冲突的,不会出现任何提示,所定义的格式也不会起作用。
条件格式工具,1.2Excel基础,并不是所有的Excel的数据都需要用键盘输入,很多数据来自网络或其他信息系统,Excel读入这些数据后,往往需要根据数据不同的属性,将这些数据分为不同的列。
Excel菜单:
“数据分列”的数据分列工具就可以完成这一工作。
Excel读入的数据有三种情况:
字段之间有空格、逗号、制表符等分割符。
字段没有任何分隔符,但每个字段的长度是确定的。
字段之间既没有分隔符,字段的长度也各不相同。
对于前两种情况,Excel分列工具可以方便地实现分列。
第三种情况则需要用户分析数据的特点,在字段之间插入分隔符,然后再实现数据分列。
数据分列工具,1.2Excel基础,工作表和单元格单元格的格式数据输入方法条件格式工具数据分列工具,从网上搜素并下载“身份证号码和地址对照表”如下:
这个表只有一列,其中包括身份证号的前六位和相应的地址,两个字段之间有空格分隔。
需要把身份证号和地址分为两列。
数据分列工具,1.2Excel基础,打开Excel菜单:
“数据分列”,弹出“文本分列向导”,选择“分隔符号”,单击“下一步”。
数据分列工具,在“分隔符号”中选定“空格”,预览中出现分列的演示。
单击“下一步”。
数据分列工具,设置数据的类型为“常规”,单击“完成”。
数据分列工具,这样就完成了数据分列。
数据分列工具,注意到这个数据的第一个字段“身份证号”是六个字符,因此也可以用“固定宽度”分列。
选定“固定宽度”,单击“下一步”。
数据分列工具,用户可以用“拖动分列线”自定义列宽。
单击“下一步”。
数据分列工具,同样可以完成分列。
数据分列工具,实际应用中,所要分析的对象(公司、政府部门、人等)通常具有多个不同的属性,例如,部门、姓名、性别、年龄、职务、职称、专业等。
在数据统计表中,这些数据称为“属性数据”。
1.3属性数据分析,第1章数据和数据分析,1.1数据1.2Excel基础1.3属性数据分析1.4数据展示图1.5数值型数据分析,实际应用中,所要分析的对象(公司、政府部门、人等)通常具有多个不同的属性,例如,部门、姓名、性别、年龄、职务、职称、专业等。
在数据统计表中,这些数据称为“属性数据”。
1.3属性数据分析,运用数据透视表可以对属性数据进行统计分析。
1.3.1数据透视表1.3.2单变量属性数据分析1.3.3双变量属性数据分析1.3.4数据透视表在问卷调查中的应用,数据透视表是Excel中的功能强大的工具。
运用数据透视表可以对大量数据进行快速汇总,创建相应的多维统计表格。
1.3属性数据分析,1.3.1数据透视表,1.3属性数据分析,1.3.1数据透视表,例如:
创建汇总统计表格,1.3属性数据分析1.3.2单变量属性数据分析,用途:
对性别、职称、品牌等单列属性数据进行分布统计。
1.分析该研究所“男、女职工”的人数。
步骤:
1.打开“数据”菜单;2.选择“数据透视表”,按下一步;3.选择“布局”;4.单击变量“性别”,拖到“行”区域中;5.单击变量“性别”,拖到“数据”区域中;6.双击“求和项”,在“汇总方式”中选择“计数”;7.单击“确定”。
1.3.2单变量属性数据分析,1.3属性数据分析,示例:
对济南市饭店业进行前景调查,问卷数据如下表所示。
要求统计出业主类型的分类人数。
1.3属性数据分析,1.3.2单变量属性数据分析,分析研究所的“职称结构”。
1.3属性数据分析,例1.2,打开教学光盘中的“研究所员工资料”:
1.3.2单变量属性数据分析,分析各部门中“男、女职工”的人数。
1.3属性数据分析,例1.2,打开教学光盘中的“研究所员工资料”:
1.3.3双变量属性数据分析两维表格(列联表),用途:
对性别、职称等属性数据进行相关关系描述。
1.3.1数据透视表1.3.2单变量属性数据分析1.3.3双变量属性数据分析1.3.4数据透视表在问卷调查中的应用,数据透视表,二维列联表,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.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.2Excel基础1.3属性数据分析1.4数据展示图1.5数值型数据分析,Excel数据展示图形有13种。
柱形图垂直柱状组成的图形称为柱形图,水平条状组成的图形称为条形图。
1.4数据展示图,例1.7创建我国20012004年第一、第二和第三产业产值数量的变化的柱形图。
1.打开Excel表,单击“插入图标”工具图标,选择图表类型柱形图和子图标类型,点击“下一步”。
用图表和曲线图展示数据,1.4数据展示图,图表向导,2.从“数据区域”选择目标数据,用鼠标选定B4:
D8,单击“系列”卡片,点击“下一步”。
用图表和曲线图展示数据,1.4数据展示图,3.在“系列”中,选择输入系列名称,用鼠标选定“分类(X)轴标志”A5:
A8,单击“下一步”,用图表和曲线图展示数据,1.4数据展示图,4.输入或修改“标题”、“坐标轴”、“网格线”、“图例”、“数据标志”、“数据表”等属性,单击“下一步”。
用图表和曲线图展示数据,1.4数据展示图,5.选择图表位置,单击“完成”。
用图表和曲线图展示数据,1.4数据展示图,6.图表完成。
如果需要,可以双击图表中任何一部分进行修改。
用图表和曲线图展示数据,图表区,分类轴,分类轴标题,数值轴,图例,图表标题,系列“第一产业”,绘图区,数值轴主要网格线,数值轴标题,系列“第二产业”,系列“第三产业”,1.4数据展示图,条形图的制作是类似柱形图。
垂直柱状组成的图形称为柱形图,水平条状组成的图形称为条形图。
例1.8:
我国20012004年三次产业的条形图如下:
条形图,1.4数据展示图,例1.92004年我国三类产业产值,饼图饼图常用来表示一项数据中各部分的比例。
1.4数据展示图,折线图常用来表示数据随时间或项目不同的变化。
例1.101978年2004年我国能源消费总量及组成(单位:
万吨标煤),1.4数据展示图,试创建“19782004年中国能源消费.xls”中的“总量”、“煤炭”、“石油”、“天然气”、“水电”消费量的折线图。
1.4数据展示图,折线图,例1.11原油消费量和天然气消费量的散点图,散点图用于表示两个变量数值之间的相关关系。
1.4数据展示图,例1.12有两家企业A和B,它们的资产属性评估结果如下表:
雷达图用来表示两组变量相应数值的比较。
1.4数据展示图,例1.132004年10省市的GDP和人均GDP数据。
气泡图可以对一组数据的三个指标进行展示和比较。
1.4数据展示图,例1.132004年10省市的GDP和人均GDP数据。
1.4数据展示图,气泡的高度表示GDP总量的大小,气泡的大小表示人均GDP的高低。
数值型数据分析的主要内容,包括:
1.5.1.数值型单变量数据分析1.5.2.数值型双变量数据分析,1.5数值型数据分析,Excel中的数值型数据统计功能有以下二种实现方法使用Excel菜单的数据分析工具使用Excel统计函数,1.1数据1.2Excel基础1.3属性数据分析1.4数据展示图1.5数值型数据分析,1.5数值型数据分析,1.5.1.数值型单变量数据分析例某班DMD考试成绩如下:
808576786793889075666577748381708364966079868071试用相关统计量对成绩进行分析。
Excel有3种分析工具用来描述数值型单变量数据:
1.描述性统计2.直方图3.排位和百分比排位,1.5.1.数值型单变量数据分析,描述性统计(数据的分布属性),数据的分布属性包括数据的集中属性、数据的分散属性以及数据频数的形态三大类:
数据集中属性的指标均值Average中位数Median众数Mode数据的分散属性的指标极差Range方差Variance标准差StandardDeviation标准误StandardError数据频数的分布形态指标偏度Skewness峰度Kurtosis,1.5.1.数值型单变量数据分析,描述性统计(数据的分布属性),数据的分布属性包括数据的集中属性、数据的分散属性以及数据频数的形态三大类:
数据集中属性的指标均值Average中位数Median众数Mode数据的分散属性的指标极差Range方差V
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- EXCEL 数据 分析 工具