如何利用excel做数据分析(上下)

网站分析中专业的工具除了 Google Analytics, Adobe Sitecatalyst, Webtrends,腾讯分析和百度统计等外，我想最常用的数据处理工具就是Excel 了，Excel里头最基础的就是运算和图表的制作，稍微高级一点就是函数和数据透视表的使用了，当然你可能还会想到VBA和宏，但估计很少高手会使用 这些高级的功能。那对于高级的数据分析而言，也就是涉及统计学的专业分析方法和原理的时候，是不是就一定得求助于SPSS,SAS这类专业的分析工具呢？数据分析从低级到高级层次的跳跃过程中有没有可以起承接作用的工具呢？其实是有的，这就是Excel的数据分析功能。貌似最近比较火的两本Excel书籍谁说菜鸟 不会数据分析和让Excel飞都没有涉及这部分的内容。高级的数据分析会涉及回归分析、方差分析和T检验等方法，不要看这些内容貌似跟日常工作毫无关系，其实往高处走，MBA的课程也是包含这些内容的，所以早学晚学都得学，干脆就提前了解吧，请查看以下内容。在使用之前，首先得安装 Excel的数据分析功能，默认情况下，Excel是没有安装这个扩展功能的, 安装如下所示：1）鼠标悬浮在Office按钮上，然后点击【Excel选项】：2）找到【加载项】，在管理板块选择【Excel加载项】，然后点击【转到】：3）选择【分析工具库】，点击【确定】：4）安装完后，就可以【数据】板块看到【数据分析】功能，如下所示:连造 JII分析安装完后，首先来了解一下回归分析的内容、回归分析在详细进行回归分析之前，首先要理解什么叫回归？实际上，回归这种现象最早由英国生物统计学家高尔顿在研究父母亲和子女的遗传特性时所发现的一种有趣的现象：身高这种遗传特性表现出I个子父母，其后代身高也高于平均身高；但不见得比其父母更高，到一定程度后会往平均身高方向发 生回归”。这种效应被称为“趋中回归”。现在的回归分析则多半指源于高尔顿工作的那样一 整套建立变量间的数量关系模型的方法和程序。这里的自变量是父母的身高，因变量是子女的身高。百度百科对于回归分析的定义是：回归分析(regression analysis)是确定两种或两种以上变数间相互 依赖的定量关系的一种统计分析方法。运用十分广泛：1)回归分析按照涉及的自变量的多少，可分为一元回归分析和多元回归分析；2)按照自变量和因变量之间的关系类型，可分为线性回归分析和非线性回归分析。这里举个电商的例子：电子商务的转换率是一定的，网站访问数一般正比对应于销售收入，现在要建立不同访问数情况下对应销售的标准曲线，用来预测搞活动时的销售收入，如下所示：访问效销售额00100104:894910101055230507204002053510005026100602101141.首先，利用散点图描绘图形:销售额 10414销售颤12000100008000600040002000 2053105。卬吗.r.,10 490 020000 40000 600U0 80000 100000 1200002 .添加趋势线，并且显示回归分析的公式和 R平方值:销售额=O.1O20X - 27.424RJ = 0.9995惜售额雌隼意飘从图得知，R平方值=0.9995,趋势线趋同于一条直线，公式是：y=0.01028x-27.424R平方值是介于0和1之间的数字，当趋势线的R平方值为1或者接近1时，趋势线最可靠。因为R2 0.99 ,所以这是一个线性特征非常明显的数值，说明拟合直线能够以大于99.99%地解释、涵盖了实际数据，具有很好的一般性，能够起到很好的预测作用。3 .使用Excel的数据分析功能1）点击【数据分析】，在弹出的选择框中选择【回归】，然后点击【确定】：2）【X值输入区域】选择访问数的单元格，【Y值输入区域】选择销售额的单元格，同时勾选如下所示的选项，包括残差、标准残差、残差图、线性拟合图和正态概率图。叵归输人I值输入区域（0；初$106：$114 的剧工值输入区域值tEj 106 461114确定取消/标志&）送信度（D/常数为等帮助如953 %诃口数销售新0010010转910101055230sori204002058510005026100602104110的pv,所以用 pv来做电子商务 的转换率不合适。 数据源如下所示：时间1。月1日1 0月3日10月4日】0月白日1 口月了日电子商务转换率1. 90%r0. 50542. 80?&0. 70%1. 90%2. 5cmo. 1m分折工具值）F着分强无重复敦因素分析取消101001. 80%1。月11日1. 8W、-10月12日2. 60%10月/日P1.80%1。月14日2. 80S1 0月15日1. 80%1 1月8日I巾9日按照以下图例进行设置:设置后，会得到如下所示的图表：（解释是我人为添加的，是对指标的解释）指标数值解建平均1.90%电子商务转换率的平均值0.00201896电子商争转换率数值误叁的平方和日平均坪勺平方根，又称为均方 谩差的平方根.中位数0.019处于数列中间位置的直的0.018出现文教最多的毅0.00781939是各数据信高平均数的立均数r艺是离场差平方和平均后的方根n 用表示，.麻准差是出差的篁本平方根方差61143E-05各个薇据与平均数之差的三方的平均数嶂算-0.4960863箸忌斓分右起伏壶化的简示以正态分布为基准，比其平装时;1 为正，反之则为负：谑要-0.4923336衡量数据峰值修移的指数,根据峰值在围值左恻或者右制分别为IE 值或负值；区域0,025最大值与最小后的差最小值0,005最大值0.03求和0,285观测数15数值的数虽最大（1）0.03最小0,005要信度（95。湖0,00433023所谓置信度，也叫置信水平，它是指特定个体对特定实他原实性相 信的得度U二、抽样分析 抽样分析工具以数据源区域为总体，从而为数据源创建一个样本。当总体太大而不能进行处理或绘制时，可以选用具有代表性的样本。 举个例子：假设这样一种情况，要抽查电子商务转换率的情况是否正常。 数据源如下所示：按照以下图例进行设置，注意是抽 8个样本:设置后，会得到如下所示的图表:抽样结果月T日1. 90%1 口月加2. m10月1日L 90%1 口月汨0. 50%10月6日L 9阂1 口月11日L 8跳1 0月四日2. 80%10月E日1. 90%三、直方图 直方图是最适合描述数据在不同选定区间分布情况的图表 数据源如下所示：按照以下图例进行设置:数插分析分析工具电）取消帮助刊时间泊图七1 0月1日狙7011 口月2日2511 口月3日斗班13711。月4日国2111 0月5日丸 2951 口月汨 1241 口月？曰跄3291 0月8日7,2991。月g日睚，97510月1Q日瓯03710 月 1LB，氏 96210月2日438310月13日*匕 99610月14日3. 93610月15日840数据区间 0 ZOUO 4000L0000设置后，会得到数据和图表:数据阶梯频率累积%数据阶幅频率累积先000 00%tsoool640. cm200000. 00H100004213. 33%80003瓯.67%6000653. 33%r 40002loo.皿%E0Q。37 工 33H00100. 00%100004100. OOK20000100. 00%其他0100. OOX其他0100. 00%直方这样估计就很清晰的可以看出数据在哪个区间分布最广了 Excel的高级数据分析功能是为了提高工作效率，如果有其他经常使用的工具可以实现这些功能，例如SPSS、SAS等，那就不要更换了。 End.可能感兴趣的内容Welcome ToDownload !欢迎您的下载，资料仅供参考！