相信大家对即将讲述的数据分析内容很感兴趣，想知道Excel数据分析包含哪些知识？本文就言简意赅地后面的系列文章会涉及到的一些内容，在这里进行一下简单的概括，大致分为八大部分分别如下：

第一部分引入数据挖掘的概念。简要介绍什么是数据挖掘，介绍Excel强大的数据挖掘功能，excel不支持的功能需要使用“加载宏”。

第二部分介绍简单的数据挖掘和问卷调查；介绍最基本的数据挖掘方法，即利用“平均数”这种最简单的数据统计模型，分析身边的数据或少量数据，介绍问卷调查这种收集数据的常用手段的设计技巧。通过预测商品预期价格。证明从少量样本中也能提取重要信息。

第三部融入案例预估二手车价格，介绍使信用回归分析进行预测和因子分析的知识，多重回归分析是预估数值和分析因子时非常有效的统计方法，是多变量分析中最常用的统计方法之一．本章以“拍卖行的二手车数据”为例对其进行解说。数据包含定性数据和定量数据，统称为“混合型数据”、经常出现在商务领域中。

第四部分内容涉及求最优化的问题“规划求解”。Excel支持“规划求解”这个强大的工具。本章介绍用“规划求解”求最优化问题的方法。经营管理中经常遇到如何利用有限的资源，实现营业额和利润最大化，以及费用和成本最小化的问题．用一次方程表示约束条件和目标叫做线性规划，求解方法叫做线性规划法。 “规划求解”不仅适合线性规划法，也适合非线性规划法。还支持整数规划法（这些统称为数理规划法或最优化规划法）．本章通过具体实例说明“规划求解”的使用方法。

第五部分一起来学习分析交叉表，介绍用交叉表判断属性（年龄、性别、职业等）是否有差异的方法。用Excel的函数功能求解；用大量实例详细说明。

第六部分会通过开发畅销产品的概念组合的案例介绍联合分析，消费者选择或决定购买商品时最重视什么？若能预知消费者重视的内容，就能开发山非常畅销的产品。“联合分析”是以“开发畅销产品的概念组合”为目的。为了把把握消费者和巾场的动向，被广泛运用在市场营销领域中的一种分析方法。计多企业都采用这种调查方法．联合分析也可以用Excel数据分析。

第七部分通过软件故障何时了的案例，来介绍用规划求解制作生长曲线，预估故障总数，生长曲线可以根据初始数据对商品的需求趋势，未来的人口数量和知识的掌握程度等的变化结果进行预测。本章用规划求解得出最优生长曲线。介绍预测软件停止发生故障的时间的方法。

最后一部分就也很有趣，是经典的求最优投资组合问题。近年来，对股票感兴趣的人越来越多．投资者最关心资产的运营安全。大部分人都希望：（ａ）收益越高越好（ｂ）风险越低越好．但是，鱼与熊掌难以兼得。投贤中最基础的理论是“高风险、高收益”，“低风险、低收益”。本章介绍能够兼得鱼与熊掌的“投资组合”方法。即将资产划分成几部分，使各部分之间的正负变动相抵消，尽量降低投资风险。

以上就是Excel数据分析包含哪些知识的一个概要介绍，当然内容远远不止这些，在接下来的系列精彩文章会运用大量实例介绍了许多数据挖掘的方法。小编希望通过普及这些方法，使所有人都能够将其灵活运用到自己的工作或研究中．这将是我们最大的愿望

上一篇我们学习了：

预测方法的分类、时间序列的成分、时间序列预测模型

这一篇我们还不能按照上一篇得出的结论，直接选择预测方法进行预测

还有些基础知识需要了解

今天，我们从简单的静态预测法开始，来了解如何进行预测：

预测的步骤：

第一步、确定时间序列类型，主要确定的是趋势成分和季节成分

通过折线图加趋势线来判断

通过趋势线公式及

来确定是否存在趋势成分，

的值越大，趋势成分越明显。趋势线可以选择多次方程，拟合度更高。

两年的数据曲线对比，就可以看出明显的季节成分，同时通过这个图形还能看出，趋势成分，2014年整体比2013年销量增长。

当然，也可以通过相关性分析，来判断季节成分，判断2013年的数据与2014年数据的相关性。

可以看出2013年与2014年数据的相关系数是0.9，高度相关，存在季节成分。

第二步、选择可用的方法，借用上一篇的图形

第三步、对可能的方法进行评价

要评价预测结果，就需要下面的评价指标

不应担心，公式并不复杂，而且在EXCEL中，计算各项的值很容易，也很方便。

这些指标中，最好的评价指标是MSE、MAD

MSE可以避免分量出现较大误差

MAD在不关心偶然误差是使用

我们来评价一下静态预测的几种方法：

可以看出指数平滑法的MAD、MSE误差最小，这种方法是几种方法中最好的。

第四步、按要求预测

静态预测只能预测下一期的值，这里就演示一下指数平滑的预测，

其实很简单，有现成的工具，不过工具中的阻尼系数，需要不断实验，来寻求最小的误差，那么最后一期的预测结果就是2016年10月478台

下一期，我们学习趋势预测方法

Excel 提供了几种常见的拟合函数类型（如指数、对数、多项式函数等），以下方法有助于选择合适的拟合函数：

根据散点图的走势判断函数类型切换不同的拟合类型，从图形上直观观察拟合效果显示拟合结果的R平方值，此值越接近于1，说明拟合结果越好

下面以Excel 2010为例进行演示：

1、插入散点图

2、选中数据系列→右键菜单→添加趋势线

3、在弹出的“设置趋势线格式”对话框中，分别选择类型为：指数、幂函数、多项式，并且勾选“显示公式”和显示R平方值

4、对比结果如下，从图中可知，三次多项式拟合结果的R平方值为1，因此效果最好。

4.1 指数函数拟合结果

4.2 幂函数拟合结果

4.3 二次多项式拟合结果

4.4 三次多项式拟合结果

（本文内容由百度知道网友learneroner贡献）

原创：有点瘦的胖子零一

需要预测的场景太多这里就不一一赘述了，在师傅的指导下，我对excel的认知水平又提升了一大截，学会了用excel做多元回归分析。这个预测方法不仅适用绝大部分行业，并且也适用没有业务基础的小白操作。附上师父的一句教诲：相信相信的力量。下面进入主题：

1.打开一张多字段数据的excel表格

导盲犬：excel中每一列就是一个字段,其第一个单元格内容就是字段名。

→剪切20%的数据做为测试集，剩余的80%数据做为训练集。→将需要预测的列剪切并复制在其它变量的前面，也就是第2列，这里我们对“无线端下单金额“进行预测，确定影响它的相关因子。

导盲犬：将需要预测的数据放在首列是为了保持预测时的连续性，另外相关因子的数量最多为16个。

→数据→数据分析

→Y值所在区域：预测值所在列的第一行开始至最后一行；X值所在区域：其余变量所在列的第一行开始至最后一行→勾选标志→勾选残差→确定

导盲犬：残差=实际y值-预测y值，利用条件格式筛选掉残差>两个标准误差的异常值。

→选中所有残差→开始→条件格式

→突出显示单元格规则→大于

→输入2倍标准误差值→确定

→找出异常值所在行

→返回数据源将异常值所在行删除即第10行和第39行（注：原数据因为有标题，所以残差异常值所在第9行相当于源数据第10行，又因为第一次删除后导致后面的行数均会上移一行，所以残差异常值所在第39行相当于源数据39行）

→数据→数据分析

→回归→确定

→Y值所在区域：预测值所在列的第一行开始至最后一行；X值所在区域：其余变量所在列的第一行开始至最后一行→勾选标志→勾选残差→确定

→筛选出<0.05的P值

导盲犬:统计学家普遍的共识，p<0.05的时候，自变量对预测y才有用.

→开始→条件格式

→突出显示单元格规则→小于→0.05→确定

为了预测更加准确，这里还需考虑多重共线性，利用半相关矩阵检查。

导盲犬：如果说两个或多个自变量是高度相关的，很可能产生多重共线性。

→返回数据源→数据→数据分析→相关系数→确定

→输入区域(除预测值外的所有数据）→标志位于第一行→确定

→开始→条件格式→突出显示单元格规则

→大于→0.998→确定

→删除字段下单父订单数、无线端支付父订单数。

导盲犬：所谓多重共线性（Multicollinearity）是指线性回归模型中的解释变量之间由于存在精确相关关系或高度相关关系而使模型估计失真或难以估计准确。

→源数据→数据→数据分析→回归→确定

→观察R方与P值

导盲犬：所有自变量共同作用具有显著性的结论，通俗的讲，只有R方大于0.6的时候，预测y才有意义。

→选中所有变量的P值→开始→条件格式→突出显示单元格规则→小于

→0.05→确定→删除其它P值>0.5的变量

→源数据→数据→数据分析→回归→确定

→Y值所在区域：预测值所在列的第一行开始至最后一行；X值所在区域：其余变量所在列的第一行开始至最后一行→勾选标志→确定

→观察R值和P值，均符合要求。

→得出公示：预测值无线端下单金额=-84341.91323+无线端下单买家数*365.259139-392.2248391*无线端支付买家数+1.200575347*无线端支付金额

导盲犬：Intercept为截距的意思。

→返回测试集验证

通过验证发现预测的点跟测试集的点高度吻合，该模型可以使用。

预测是商业分析的核心，企业之所以能产生利润主要就是因为企业获得了信息差，而预测就是帮助企业创造信息差。因此，预测能力是最能体现数据分析师价值的点。

1、以下图中的数据为例，来详细描述趋势线的添加方法。如图所示，图表中给出了某产品从7月1号，到7月8号的产量：

　　2、从图表中可以看出，由于这段时间内，产量的数值有波动，所有对于未来产量趋势的判断就很不明显，如果能给出一条预测的趋势线就有助于分析了。

　　3、添加趋势线的步骤：

　　1）、首先，单击图表中的红色走势线，使其处于选中状态。然后单击右键，紧接着单击出现的“添加趋势线”按钮，如下图所示：

　　2）行完步骤1后会弹出“设计趋势线格式”的对话框

　　3）选择所需要的趋势线类型（包含有指数、线性、对数、多项式、幂、移动平均6中线型），然后根据需要选择是否需要“设置截距”、是否需要“显示公式”、是否需要“显示R平方值”（R值平方值越大说明拟合的趋势线越精确）。

　　4）本例选择趋势线类型为“线性”，不“设置截距”，“显示公式”，“显示R平方值”。如下图所示：

　　5）选择好以后，单击“关闭”按钮，所得趋势线结果如下图所示，其中黑色直线即为趋势线，趋势线下方是趋势线的拟合公式和R平方值。即达到本次操作的目标：

（本文内容由百度知道网友茗童贡献）

最近几天，不断有小伙伴在后台问到使用excel做数据分析的相关问题，今天，数据君（ID：shendufenxi）就为大家推送一篇实用技巧。

高级的数据分析会涉及回归分析、方差分析和T检验等方法，不要看这些内容貌似跟日常工作毫无关系，其实往高处走，MBA的课程也是包含这些内容的，所以早学晚学都得学，干脆就提前了解吧，请查看以下内容。

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

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

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

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

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

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

回归分析

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

实际上，回归这种现象最早由英国生物统计学家高尔顿在研究父母亲和子女的遗传特性时所发现的 一种有趣的现象：身高这种遗传特性表现出”高个子父母，其后代身高也高于平均身高；但不见得比其父母更高，到一定程度后会往平均身高方向发生’回归’”。

这种效应被称为”趋中回归”。现在的回归分析则多半指源于高尔顿工作的那样一整套建立变量间的数量关系模型的方法和程序。 这里的自变量是父母的身高，因变量是子女的身高。

百度百科对于回归分析的定义是: 回归分析（regression analysis)是确定两种或两种以上变数间相互依赖的定量关系的一种统计分析方法。运用十分广泛：

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

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

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

1、利用散点图描绘图形：

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

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

R 平方值是介于 0 和 1 之间的数字，当趋势线的 R 平方值为 1 或者接近 1 时，趋势线最可靠。因为R2 >0.99，所以这是一个线性特征非常明显的数值，说明拟合直线能够以大于99.99%地解释、涵盖了实际数据，具有很好的一般性, 能够起到很好的预测作用。

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

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

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

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

4）以下是残差图：

残差图是有关于实际值与预测值之间差距的图表，如果残差图中的散点在中轴上下两侧分布，那么拟合直线就是合理的，说明预测有时多些，有时少些，总体来说是符合趋势的，但如果都在上侧或者下侧就不行了，这样有倾向性，需要重新处理。

5）以下是线性拟合图

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

6）以下是正态概率图

正态概率图一般用于检查一组数据是否服从正态分布，是实际数值和正态分布数据之间的函数关系散点图，如果这组数值服从正态分布，正态概率图将是一条直线。回归分析不一定得符合正态分布，这里只是仅仅把它描绘出来而已。

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

2018年天猫淘宝的双十一购物狂欢节落下帷幕，总成交量达惊人的2135亿人民币。这个数据关心双十一的人，早就知道。但是2135亿的交易额数据是如何产生的，本文试着胡言调侃一番。

首先我们看看历年的数据，双十一元年是2009年拉开序幕当年交易额是0.52亿，我们将历年双十一的数据用excel表展示如下：

借助excel的预测工作表功能，进行预测2018年双十一交易额。

预测结果设定为2018年，预测开始从2017年开始，置信区间设定18%。开始创建预测工作表。

结果出来了，趋势预测金额为2122.18亿元。

为何置信区间设定为18%呢，因为是18年呀，就这么任性。

如果官方以此数据公布，在众多精明网友面前，岂不是闹成笑话，以后如何在这个行业混呢，也没办法跟马总交代呀。更重要这个数据一出来，不管实际交易额多少，大家都会认为这个交易额是用一个简单软件自动预测出来的。

这种事情哪能难住聪明的阿里人呢！！！

阿里人知道大家只盯着趋势预测数据，所以如何公布的数据必须与excel预测出来的数据要有偏差。怎么办呢？

勤劳的阿里excel部门，马上想到了算术平均数，根据刚预测工作表中出来的数值，置信上限是2147.33亿。为何不用置信下限数据，因为里面下，表示走下坡路，不吉利，所以就用上限数值与趋势预测数据一起做算术平均数：

结果是2134.75亿。

把此数据报请负责领导后，根据领导的指示，数据不可拖泥带水，要完整！

于是阿里excel部门，马不停蹄借助公式round，去掉小数点后面的数据。

亮瞎眼的数据，呈现出来的结果是：

2135亿！！！

惊喜不惊喜，意外不意外，是不是与官方公布的数据一样啦！！！

以上纯属调侃，且不可当真。

季节预测法有四种具体方法：

多元回归

季节自回归

温特模型

时间序列分解

季节预测法里将月度数据整理成极度数据，然后做的预测

一、多元回归预测

多元回归模型，仍然使用数据分析中的回归工具，输入数据是这样的，选取哪一季数据哪一季为1，其余季节为0，得出回归方程系数，以及预测值。然后列出方程，测算未来几期的预测值。

回归系数：

二、季节自回归

同样使用数据分析--回归工具

使用滞后4个季度的数据作为参数，来计算回归方程系数：

回归工具输出的结果的一部分截图：Coefficients-回归方程的系数

三、温特模型（Winter‘s model）

稍微复杂一些：一共5个公式，三个可变的平滑系数

不过都很简单的公式，最后输出的结果是个样子：

再来看看和原值的比较：

是不是拟合程度特别高，当然，这个结果是不断调整α、β、γ三个平滑系数，根据计算的各项误差指标的最小值，才得来的这个曲线。

四、时间序列分解

是基于时间序列乘法模型

先计算季节系数，然后所有数据除以季节系数，消除季节影响，然后使用适当的趋势预测方法进行预测，再将预测值乘以季节系数，得到最终预测结果的方法

这个模型更为复杂，而且有很多种组合。

这里简单做了一个：

偷了个懒，借用温特模型中的季节指数，简单做个一元自回归，得到上面的结果。

至此，时间序列的三大类、12种预测模型，全部学习了

针对这组数据，我们最后再横向比较一下12中预测方法的误差情况：

最后的结论是，温特模型的各项评价指标更出色，温特模型所得出的预测结果可信度更高。

年底了，总结汇报和来年规划必不可少，如何让大家能够直观看到明年一年的情况，比如预测一下明年的可能性？

Excel在2016版本中，提供了一个数据预测功能，通过它就可以做出一个预测图表，这样一目了然的方式自然可以让大家看得更加清楚。

以销售数据来说好了，先录入销售数据，这里以2015年到2016年的数据为例，圈选这些数据项，然后切换选项卡到“数据”，找到右侧的“预测工作表”点击。

弹出的图表中，蓝色代表实际销售数据，趋势预测、置信下限、置信上限都是橙色——也就是预测数据。不过这三个数据几乎都是平直的，显然不是正常的预测值，没有参考价值。

这是因为没有正确设置范围数据的缘故。点击图表中的“选项”折开选项内容，首先是预测结束日期，比如我们设置到2018年4月1日（根据自己实际情况设置），预测启始时间设置为2017年4月1日（这之后都是未发生的数据，要预测的起点）。之后是预测中周期的规律，切换“季节性”到“手动设置”，设置一个周期比如12，代表一个周期为12个月，在这个基础上计算预测数据，这里也可以跟据自己需求修改，比如4个月（1个季度）。

这里要讲解一下选项设置的几个重要参数

预测开始：从历史数据中的哪一期数据开始预测。

置信区间：设置预测值的上限和下限；该值越小，则上下限之间的范围越小。

使用以下方式填充缺失插值点：默认为“内插”，是根据数据的加权平均值计算出的插值；也可以将其置为“0”，即不进行缺值的插值计算。

使用以下方式聚合重复项：举例来说，如果2016年1月份有好几个数据，Excel在计算预测值时会将一个月内的多个值进行“聚合”，“聚合”的方式包括平均（默认）、计数、最大/最小/中值等，这就要看你表格数据的具体情况了。

设置完毕点击确认，可以看到图表左侧的数据处多了要预测但未实际发生的数据，三列数据分别是趋势预测（数字）、置信下限（数字）、置信上限（数字），这分别代表着预测值，预测下限、预测上限。而右侧的图表，可以看到波峰波谷的情况，当然，这种预测表格是根据往年的销售波动来检测的，Excel还没有到可以将不可预测的情况包含在内哦。最后，这个图表不仅可以用波峰形式来体现，也可以用柱状图的形态来表现，在图表中的右上角有切换选项，不妨看看哪个更适合自己。完成设置后，还可以将图表复制到PPT中作为路演素材，是不是方便呢？

大家在使用Office2016的时候有没有发现：以往我们需要大量代码、公式才能制作出的内容，好多都已经集成在程序内，只是微软并没有很大方的将它们展现出来。大家在遇到某些难题的时候，可以随时咨询“办公便签”，办公小达人们都是这么养成的哦！

                                                                                                                ——电脑爱好者

回归分析法，即二元一次线性回归分析预测法

先以一个小故事开始本文的介绍。十三多年前，笔者就职于深圳F集团时，曾就做年度库存预测报告，与笔者新入职一台籍高管Edwin分别按不同的方法模拟预测下一个年度公司总存货库存。令我吃惊的是，本人以完整的数据推算做依据，做出的报告结果居然与仅入职数周，数据不齐全的Edwin制定的报告结果吻合度达到99%以上。仍清楚记得，笔者曾用得是标准的周转天数计算公式反推法，而Edwin用的正是本文重点介绍的二元一次线性回归分析法。

二元一次线性回归分析法是一种数据分析模型。

在EXCEL函数公式是FORECAST（英文意思是：预测），其用途是根据一条线性回归拟合线返回一个预测值，此函数使用可对未来销售额、库存需求或未来数据趋势进行预测分析。

要做好库存预测须具备几个条件，首先须具备过去较长的某个时间段的完整整的数据。这里说的时间段最好是上一年度一整年或最近两年的数据。

完整的数库据指的是需要有年度对应每个月的实际库存与营收额或销货成本。

同样我们把库存预测肢解成几个关键步骤。

第一步：数据准备，依要求对EXCEL公式数据输入

先看一组实际的数据，其中蓝色字体是已知具备的数据，黄色则是需要预测的库存数据。预测库存，则至少需要具备的数据是标注蓝色三行数据。为别是：上一年度月营收，上一年度月实际库存，本年度月营收目标。可参照始下截图与视频。

第二步：依KPI目标调整预测数据

假设要求实际目标要求对总体存货周转率提升10%，则总体平均存货库存也减少10%，具体数据如下截图标注粉色行。

第三步：把总库存分解成不同物料形态的库存。这里讲的不同类别可以指的是：

物料形态分类：原材料、半成品、在制品以及成品等。

仓码分类：原材料仓、包装仓、成品仓、重要物资仓、五金仓、配件仓以及辅助物料仓等。

这里我们以第一种物料类型实例说明。须依据上年度不同物料类别占总库存的比率，再计算对应类别库存总额，如下截图。

第四：验证二无一次线性回归分析方法的准确度。

存货周转天数=(（期初库存+期末库存）/2*30）/(营收*物料成本率）=(平均库存*30）/销售成本。

依公式反推预测库存，平均库存=（目标周转天数*营收*物料成本率）/30，前提需要更多的数据信息，包括物料成本率与以往的周转天数做为计划依据。

如下截图，两种不同的方法得出库存预测吻度为97%（或103%）。

企业管理中，要快速地对企业活动做出判断，需要完整的数据管理积累支撑

二元一次回归分析法做库存预测速度快，效率更高。而标准的周转天数计算预法会更准确与准确。到底应当选择哪个方法？不同的时期，不同的方法如何选择则是仁者见仁，没有对或错，只有合适与否。但有肯定的一点，那就是类似二元一次回归分析法管理工具的熟练应用，则一定对会对企业管理起到更好的帮助，在做数据调研时也是个好的选择。