EXCEL数据分析工具

1.1数据1.2Excel基础1.3属性数据分析1.4数据展示图1.5数值型数据分析第1章数据和数据分析根据数据的属性：◊数值型：123.1万元、23.5℃、4700点……连续型数据：温度变化，如23.5℃、……离散型数据：5个篮板、120个集装箱、……◊属性型：用文字、判断等表示的数据天气－晴、阴、雨；股市－升、降；CPI－高、中、低；质量－好、中、差，……属性型数据都是离散的。1.1数据第1章数据和数据分析商务数据的分类根据数据的变化◊不随状态变化、相对稳定的数据称为静态数据。◊随状态变化的数据称为动态数据。项目数据数据性质姓名王大明静态性别男静态出生年月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数据2002~2006年某市旅游接待与收入情况年份20022003200420052006人次（万）490229526565620人均消费（元）135138140145202收入总量（亿元）6.633.97.398.212.5按时间划分序号地区2007年经济指标GDP（亿元）人口（万人）占地面积（平方千米）1汉中291.21379.36272462商洛135.8238.45192923安康189.85265.2523539陕南2007年经济指标：同一时间段发生的数据称为截面数据。1.1数据◊截面数据◊定类数据（Categorical/NominalData）由不同类别组成的数据称为定类数据。各类数据的类型和单位一般都不相同。不同类的数据之间有些可以运算，有些则不能运算。户总数（万户）总人口数（万人）男（万人）女（万人）全市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数据按相互关系划分◊定序数据（OrdinalData）城市重庆德阳绵阳达县雅安西昌环境评价值（无量纲）0.98830.17320.31720.78850.37130.6037评价等级三级一级二级三级二级二级仅表明对象的排列次序的数据称为定序数据，定序数据之间的差或比值一般没有意义。1.1数据◊定距数据（IntervalData）数据没有绝对的零值，没有倍数、比例关系，只有相互之间的差距有实际意义。2008年中国城市金融中心指数（引自“中国城市竞争力报告”）城市国际金融产业集聚金融业人才规模综合分上海北京深圳天津南京杭州…西安10.7470.2470.3030.1850.0280.00610.7340.470.3010.3740.1870.31510.9410.690.6410.4930.4920.3961.1数据◊定比数据（RatioData）定比数据具有明确的零点，可以计算数据的比例。例如2008年美国的GDP是中国的4.15倍。国家GDP（亿美元）人均（美元）1美国139800462802日本52900414803中国3370025204德国32800397105英国25700424306法国25200412007意大利20900359808西班牙14100308209加拿大136004147010俄罗斯1140080301.1数据根据数据的连续性◊离散数据数据是整数或者若干可能的结果之一。例如顾客投诉的次数，产品中不合格的个数，天气是晴天、阴天或下雨。◊连续数据数据是连续变量。例如产品的成本、利润、交货时间等。1.1数据数据按类型划分数值数据属性数据按相互关系划分时序数据截面数据定类数据定序数据定距数据定比数据连续数据离散数据按时间划分静态数据动态数据按变化划分1.1数据EXCEL软件简介MicrosoftExcel是一个功能强大、使用灵活方便的电子表格软件，也是最为流行的办公自动化软件，本课程主要利用EXCEL的统计分析功能和丰富的统计图表。0102030405060708090第一季度第二季度第三季度第四季度东部西部北部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=A201.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函数实现。例如：如果奖金发放金额由考核等级确定：AB1考核等级奖金额度2A20003B10004C0设考核等级单元格为A2，计算奖金额度的函数为：IF(A2=“A”,2000,IF(A2=“B”,1000,0))IF函数嵌套的层数最多为7层。由于多层嵌套的IF函数构造比较复杂，容易出错。多个条件的情况，还是推荐用函数VLOOKUP来实现。数据输入方法用字符提取函数RIGHT、LEFT、MID输入（选读）许多数据包含不同的信息，例如18位身份证号码中就包含了出生地、出生日期、性别等信息。身份证中不同位置的数字对应的信息如下：123456789101112131415161718出生地出生日期序号出生省出生市年份月份日期性别出生县要从身份证号码中提取相应的信息，就需要用以上三个函数。数据输入方法123456789101112131415161718出生地出生日期序号出生省出生市年份月份日期性别出生县设身份证号码单元格为A2，提取出生省份：LEFT(A2,2)*10000提取出生市：MID(A2,3,2)*100提取出生县