如何利用excel做数据分析上下.docx

如何利用excel做数据分析上下.docx

《如何利用excel做数据分析上下.docx》由会员分享，可在线阅读，更多相关《如何利用excel做数据分析上下.docx（15页珍藏版）》请在冰豆网上搜索。

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

网站分析中专业的工具除了GoogleAnalytics,AdobeSitecatalyst,Webtrends,腾讯分析和XX

统计等外，我想最常用的数据处理工具就是Excel了，Excel里头最基础的就是运算和图表的制作，

稍微高级一点就是函数和数据透视表的使用了，当然你可能还会想到VBA和宏，但估计很少高手会

使用这些高级的功能。

那对于高级的数据分析而言，也就是涉及统计学的专业分析方法和原理的时候，是不是就一定得求助

于SPSS,SA磁类专业的分析工具呢？

数据分析从低级到高级层次的跳跃过程中有没有可以起承接作用的工具呢？

其实是有的，这就是Excel的数据分析功能。

貌似最近比较火的两本Excel书籍《谁说菜鸟不会数据分析》和《让Excel飞》都没有涉及这部分的内容。

高级的数据分析会涉及回归分析、方差分析和T检验等方法，不要看这些内容貌似跟日常工作毫无关系，其实往高处走，MBA勺

课程也是包含这些内容的，所以早学晚学都得学，干脆就提前了解吧，请查看以下内容。

在使用之前，首先得安装Excel的数据分析功能，默认情况下，Excel是没有安装这个扩展功能的,安装如下所示：

1）鼠标悬浮在Ofice按钮上，然后点击【Excel选项】：

2）找到【加载项】，在管理板块选择【Excel加载项】，然后点击【转到】：

3）选择【分析工具库】，点击【确定】：

4）安装完后，就可以【数据】板块看到【数据分析】功能，如下所示:

V

P|

吗数据分析

全部剧新”

财选

站工具

分希

安装完后，首先来了解一下回归分析的内容

、回归分析

在详细进行回归分析之前，首先要理解什么叫回归？

实际上，回归这种现象最早由英国生物统计学家高尔顿在研究父母亲和子女的遗传特性时所发现的一种有趣的现象：

身高这种遗传特性表现出”高

个子父母，其后代身高也高于平均身高；但不见得比其父母更高，到一定程度后会往平均身高方向发生’回归’”。

这种效应被称为”趋中回归”。

现在的回归分析则多半指源于高尔顿工作的那样一整套建立变量间的数量关系模型的方法和程序。

这里的自变量是父母的身高，因变量是子女的身高＜XX百科对于回归分析的定义是：

回归分析（regressionanalysis）是确定两种或两种以上变数间相互依赖的定量关系的一种统计分析方法。

运用十分广泛：

1）回归分析按照涉及的自变量的多少，可分为一兀回归分析和多兀回归分析；

2）按照自变量和因变量之间的关系类型，可分为线性回归分析和非线性回归分析。

这里举个电商的例子：

电子商务的转换率是一定的，网站访问数一般正比对应于销售收入，现在要建立不同访问数情况下对应销售的标准曲线，用来预测搞活动时的销售收入，如下所示：

访问数

销售额

0

0

100

10

489

49

1010

—1051

5230

507

20400

2053

51000

5026

100602

1如电

1•首先，禾I」用散点图描绘图形:

住形團徳图阱图案形图面秩團散点塑具悒图表超卷接

销售额

★销售额

2.添加趋势线，并且显示回归分析的公式和R平方值:

销售额

y二0.1028X-27.424

R3二0.3995

♦销售额

—姿性鹅售刼

从图得知，R平方值=0.9995，趋势线趋同于一条直线，公式是：

y=0.01028x-27.424

R平方值是介于0和1之间的数字，当趋势线的R平方值为1或者接近1时，趋势线最可靠。

因为R2>0.99，所以这是一个线性特征非常明显的数值，说明拟合直线能够以大于99.99%地解释、

涵盖了实际数据，具有很好的一般性，能够起到很好的预测作用。

3.使用Excel的数据分析功能

1）点击【数据分析】，在弹出的选择框中选择【回归】，然后点击【确定】：

2）【X值输入区域】选择访问数的单元格，【丫值输入区域】选择销售额的单元格，同时勾选如下所示的选项，包括残差、标准残差、残差图、线性拟合图和正态概率图。

3）以下内容是残差和标准残差:

SUMMARYOUTPUT

回归刼

MultipleR

a99980694

RSquare

6M5S13S17

AdjustedRSquare

0.856756775

标准误差

37.30185573

规测值

8

方是分析

df

SS

US

F

Signlfi

回归分祈

1

133132873

136132673

18J23.S34：

77

1.132S6E-11

碍差

7

5335L+2981

7621.6L4014

总计

8

138136ZZ4.3

RESIDUALOUTPUT

PROBABILITYOUTPUT

观测值

预测销售额

标准菇差

百分比排位

销售颔

1

0

0

0

6.25

0

2

10.24

-0.2426567

-0-0029714

19.75

10

3

50.09

-0.9532579

-0.011673

31.25

也1333333

4

103.45

1.88250073

0.023051^5

43.75

105.333333

5

535.G9

-28,357G11

-0.3472499

56,25

507,333333

G

2089.50

-36.1686S1

-0.44285E9

63.75

2053.33333

7

522S.75

-198.22158

-2-4272998

81.25

5025.53333

8

10304,32

109.282518

1.33820668

93.75

101L3.6

4）以下是残差图:

访问数ResidualPlot

200i

■

011

-200°2000040000^000080000100000120000

-400-

访向数

残差图是有关于实际值与预测值之间差距的图表，如果残差图中的散点在中轴上下两侧分布，那么拟

合直线就是合理的，说明预测有时多些，有时少些，总体来说是符合趋势的，但如果都在上侧或者下侧就不行了，这样有倾向性，需要重新处理。

5）以下是线性拟合图

访问数LineFitPlot

访问戳

在线性拟合图中可以看到，除了实际的数据点，还有经过拟和处理的预测数据点，这些参数在以上的表格中也有显示。

6）以下是正态概率图

NormalProbabilityPlot

昌20000-

tt10000-♦

S-♦

®0«*一~Y4_*——I1

020406080100

SairplePercentile

正态概率图一般用于检查一组数据是否服从正态分布，是实际数值和正态分布数据之间的函数关系散点图，如果这组数值服从正态分布，正态概率图将是一条直线。

回归分析不一定得符合正态分布，这里只是仅仅把它描绘出来而已。

以上数据表格和图表都说明公式y=0.01028x-27.424是一个值得信赖的预测曲线，假设搞活动时流量有50万访问数的话，那么预测销售将是51373,如下图所示：

访问数

销售颔

0

0

100

10

439

49

1010

105

5230

P507

20400

2053

51000

5026

100602

10414

500000

513T3

公式:

y=CL10281-27.424

VIA：

szwebanalytics

End.

上篇博文已经介绍了如何安装Excel的高级数据分析功能，并且介绍了回归分析，说实话篇幅有点长,主要是安装那块截图比较多；这篇主要介绍一下描述统计、抽样分析和直方图。

一、描述统计

中位数、众数、数据分布区间可能还比较容易可以算出，但是标准差和方差等的计算就比较麻烦了，这些都是描述样本数据的常用变量，使用Excel数据分析中的”描述统计”可以得到这些数据。

举个例子：

根据过去15天的电子商务转化率，想要得到它的数据分布区间、标准差、峰值和极差等描述统计指标。

一般来说，电子商务网站的转换率在3沖下，转换率指的是订单除以访问数，注意

不是除以pv,因为对于某些外贸站，访问深度可能比较深，每即次访问可能有>10的pv,所以用pv

来做电子商务的转换率不合适。

数据源如下所示：

按照以下图例进行设置:

设置后，会得到如下所示的图表：

（解释是我人为添加的，是对指标的解释）

数值

平均

1.90%

电子蔺务转换率的平均值

标准误差

0.00201896

电子闾爭转换率僉H谓呈的平方和闫平均恒E抨方複.又琢为均方澳差的平方根・

中位数

0.019

处于数列中间僅置的值

0.018

岀现反克懐多的裁

0,00781939

是各魏据侵离平均数的平均数’它是离均差平方和平均后的方根，1用示，标准差是方差西算术平方根

6.1143E-05

各个数据与平均数之差的平方的平沟数

-0.4960863

覺融拒分右起伏吏化的怖.以正态分右为垦准.出臬平農对is为IE「反之^为负：

偲燮

-0.4923336

蟹量数据懂值但移的播数.根据峰d左均恒左q或者右咔别为圧丁軽负值；

0,025

最大值与最少值的差

最小值

0,005

最大诣

0.03

求Ju

0,2&5

观矚

15

soans

®A

（1）

0.03

最小⑴

0,005

豊唁滾（95.0%）

0.00433023

所谓査信度,也叫畫信水平”它是指特左个o対特左冥例真实性相信的题

二、抽样分析

抽样分析工具以数据源区域为总体，从而为数据源创建一个样本。

当总体太大而不能进行处理或绘制时，可以选用具有代表性的样本。

举个例子：

假设这样一种情况，要抽查电子商务转换率的情况是否正常。

数据源如下所示：

按照以下图例进行设置，注意是抽8个样本:

设置后，会得到如下所示的图表:

抽样结果

10^70

1.90ft

10^39

2.80%

lo^ia

k90%

rid月2曰

0,50%

M月E曰

L90%

10^110

1.3CWt

ID月14日

2.30%

M月阳

k90%

三、直方图

直方图是最适合描述数据在不同选定区间分布情况的图表

数据源如下所示：

按照以下图例进行设置:

设置后，会得到数据和图表:

数抿阶梯

频率

累积%

数据阶梯

频率

累积筑

0

0

0.00%

6000

6

io,cm

2000

0