学习中，我们常用Excel做表格，但我们没有发现Excel也有统计分析功能，我们可以对Excel稍作一下设置，就可以做出强大统计分析的功能了。 设置步骤如下: 1.在菜单栏里，点击右键，找到“自定义快速访问工具栏”，点击，选中加载项，在底栏找到一个“转到”，紧接着点击确定。 2.紧接着转到“加载宏”的界面，选择“分析工具库”即可，然后点击确定。 3.此时，打开excel，在数据下方就可以看见新增“数据分析”模块了。可以看见一些常见的统计分析过程，例如:t检验、z检验、方差分析、相关分析、回归分析等，这样就可以对一些数据进行简单的统计分析了，在这里就不举例分析了。

 
 
Excel数据分析工具库是个很强大的工具，可以满足基本的统计分析，这里介绍用Excel数据分析工具库中的回归做回归分析。本文仅作为学习笔记之用，欢迎各位交流指正。
 
 
本节知识点：
 
 
Excel数据分析工具库—回归
 
 
线性回归和非线性回归
 
 
简单线性回归和多重线性回归
 
 
逻辑斯蒂回归
 
 
 
 
一、什么是回归分析（Regression）
 
1、定义
 
确定两种或两种以上变量间相关关系的一种统计分析方法。通过数据间相关性分析的研究，进一步建立自变量(i=1,2,3,…)与因变量Y之间的回归函数关系，即回归分析模型，从而预测数据的发展趋势。
 
 
 
2、分类
 
 
按照涉及的变量的多少，分为一元回归和多元回归分析；
 
 
按照因变量的多少，可分为简单回归分析和多重回归分析；
 
 
按照自变量和因变量之间的关系类型，可分为线性回归分析和非线性回归分析。
 
 
 
二、线性回归
 
1、简单线性回归
 
简单线性回归又叫一元线性回归，即回归模型中只有一个自变量和一个因变量，其回归方程可以表示为：
 
Y=a+bx+
 
其中，Y表示因变量，x表示自变量，a是 常数，b是斜率，是随机误差。
 
 
 
2、最小二乘法：
 
如何确定参数a和b,则要用最小二乘法来实现。通过最小化误差的平方和寻找数据的最佳函数匹配，即使得观测点和估计点的距离的平方和最小。
 
 
 
3、线性回归分析的步骤：
 
 
确定自变量和因变量
 
 
绘制散点图，确定回归模型类型
 
 
估计模型参数，建立回归模型：最小二乘法进行模型参数估计
 
 
对回归模型进行检验
 
 
利用回归模型进行预测
 
 
 
4、多重线性回归
 
定义：一个因变量与多个自变量的线性回归问题，是一元线性回归的推广。其回归方程可以写为：
 
 
 
多重线性回归方程中回归系数的估计也是用到最小二乘法
 
 
 
三、用Excel做回归分析
 
我们研究销售额Y和推广费用X1之间的关系，数据如下：
 
 
首先我们用数据分析—相关系数分析计算一下自变量和因变量之间的相关系数为0.95157，为强相关。
 
 
 
 
绘制散点图如下：
 
 
 
 
然后，我们用数据分析库里的回归来做分析
 
 
 
 
注意Y值和X值输入区域，X值是自变量，Y是因变量。
 
 
 
 
四、线性回归方程的检验
 
评价回归拟合程度好坏（重要）：
 
 
1、  先看回归统计表，Multiple R即相关系数R的值，和我们之前做相关分析得到的值一样，大于0.8表示强正相关。
 
 
 
2、  回归统计表中的R Square是R平方值，R平方即R的平方，又可以叫判定系数、拟合优度，取值范围是[0,1],R平方值越大，表示模型拟合的越好。一般大于70%就算拟合的不错，60%以下的就需要修正模型了。这个案例里R平方0.9054，相当不错。
 
 
 
3、  Adjusted R是调整后的R方，这个值是用来修正因自变量个数增加而导致模型拟合效果过高的情况，多用于衡量多重线性回归。
 
 
 
4、  第二张表，方差分析表，df是自由度，SS是平方和，MS是均方，F是F统计量，Significance F是回归方程总体的显著性检验，其中我们主要关注F检验的结果，即Significance F值，F检验主要是检验因变量与自变量之间的线性关系是否显著，用线性模型来描述他们之间的关系是否恰当，越小越显著。这个案例里F值很小，说明因变量与自变量之间显著。
 
 
 
5、  残差是实际值与预测值之间的差，残差图用于回归诊断，回归模型在理想条件下的残差图是服从正态分布的。
 
 
 
6、   第三张表我们重点关注P-value，也就是P值，用来检验回归方程系数的显著性，又叫T检验，T检验看P值，是在显著性水平α（常用取值0.01或0.05）下F的临界值，一般以此来衡量检验结果是否具有显著性，如果P值>0.05，则结果不具有显著的统计学意义，如果0.01<P值<0.05，则结果具有显著的统计学意义，如果P<=0.01，则结果具有极其显著的统计学意义。T检验是看某一个自变量对于因变量的线性显著性，如果该自变量不显著，则可以从模型中剔除。
 
 
 
7、   从第三张表的第一列我们可以得到这个回归模型的方程：y=4361.486+1.198017x，此后对于每一个输入的自变量x,都可以根据这个回归方程来预测出因变量Y。
 
 
 
这里简单总结了一下什么是回归分析，以及如何用excel做线性回归分析，入门很简单，精通还很遥远，我们都在学习中。
 
 
 
下一节内容：用Excel做直方图（2）：频率分布直方图
 
 
 
@文章属原创，转载请联系作者
 
@作者：虾壳，在数据分析的道路上努力奔跑
 
@微信公众号：可乐的数据分析之路
 
 
 

欢迎关注微信公众号：excelwork
 
    Excel容易上手、直观展示，工作中遇到的大部分数据，初期可采用用Excel进行描述统计分析，以便进行后续动作。今天介绍Excel中如何完成描述性统计。
 
一、使用Excel自带数据分析工具
 
    先介绍自带的分析工具，可一次性输出一些统计项的数值，样例数据85行6列。
 
 
找不到数据分析工具？
 
需要将分析库加载项添加到选项卡展示：
 
 
依次点击“文件”-“选项”-“加载项”；
 
 
“管理”中点击“Excel加载项”，点击“转到”，勾选“分析工具库”即可。
 
 
添加好加载项后，使用步骤如下：
 
 
点击“数据”-右侧找到“数据分析”，弹出的窗口中，分析工具选择“描述统计”；
 
 
“输入区域”选择要分析的数据区域，根据需求选择分组方式及是否勾选“标志位于第一行”；
 
 
选择输出区域，以及勾选下面需要使用的统计选项，确定即可。
 
 
结果如下：
 
 
    详细内容会在介绍函数时进行说明。
 
二、使用函数进行描述性统计
 
2.1 数据分布趋势
 
最大值：max函数
 
最小值：min函数
 
极差：最大值-最小值
 
组距：极差/组数
 
 
频数分布：frequency函数
 
算数平均值：average函数
 
中位数：median函数
 
众数：mode函数
 
    算数平均值、中位数和众数是常用的中心趋势的度量。
 
相对位置度量-分位数：
 
 
quartile函数，分位数常见的有25%分位数，75%分位数；而0%分位数即我们常用的最小值，100%分位数即最大值，50%分位数即中值。
 
 
小数据集，一般约等于取整最近的数，对于下四分位数向上取整，下四分位数向上取整。
 
直方图:
 
 
选择数据区域-插入-直方图
 
 
设置横轴坐标轴格式，箱宽度输入前面计算好的组距（或者根据指定组数输入到箱数进行设置）
 
 
2.2 离散程度
 
极差：即最大值和最小值之差，一定程度上可以反应数据的差异程度，不过不能反应中间其他数据情况。Excel中可用：=MAX(数据区域)-MIN(数据区域)来计算。
 
平均差：也是表示数值间差异的指标之一，即数值与算数平均值差异值的绝对值之和除以数值个数，数值越大，数值间差异程度越大，Excel中使用公式：=AVEDEV（数据区域）计算
 
方差：反应数据集中数据对于算数平均值的偏离情况，
 
计算样本方差=(数值-均值)的平方和/（数值个数-1）,Excel中使用=VAR(数据区域)或VAR.S(数据区域)；
 
计算总体方差=(数值-均值的平方和)/（数值个数）,Excel中使用=VAR.P(数据区域)；
 
标准差：样本标准差=样本方差开根号；总体标准差=总体方差开根号，Excel中使用STDEV或STDEV.S计算样本标准差，STDEV.P计算总体标准差。
 
变异系数：标准差和平均值的比值，均值不为零时有意义，无量纲，可以消除尺度和量纲的影响。
 
峰度：KURT函数，反映分布曲线的峰部的尖峭或扁平程度。峰度系数>0，表示两侧分布极端值越少，呈尖峰分布；峰度系数<0，分布较平坦。
 
偏度：skew函数，反映分布的非正态程度。偏度系数>0时，呈现右偏，即长尾在右；偏度系数<0时，呈现左偏，长尾在左。

 
 
 
  
我们先来看看某公司全国产品销售的报表（截取了部分）
 
  
 
  
 
   
 
    
 
    

     
    
 
   
 
   

    某公司全国产品销售的报表
   
 
  
  
 
  
通过货品销售数据案例，需反映如下结果：
 
 

货品销售情况
货品交货情况
销售货品结构构成
货品销售区域构成
货品按时交货与合格品综合评价
 
我们使用 excel 进行数据分析，一般有以下步骤
 
需求收集
数据整理
数据简单统计
图表展示
分析结果撰写报告
 
第一步：需求分析
 
所谓需求，在这个情景下就是 我们处理这个excel 表格的意义在哪？
 老板可能需要定期查看这样的销售数据，通过销售数量案例，反应货品销售情况，货品交货情况，按时交货和合格品综合评价等
 
第二步：数据的整理
 
常用的一些函数
 数据重复(COUNTIF函数，删除重复项)
 缺失数据（IF AND Or嵌套函数等）
 数据抽样（Left,Right,VLOOKUP）
 数据计算(AVERAGE,SUM,MAX,MIN,DATE,IF)
 数据分组（VLOOKUP函数，采用近似匹配，SEARCH函数）
 数据抽样(RAND函数，RAND())
 
我们会发现其中有很多格式上的错误，所以分析的第一步是做数据的整理，整理可以从以下几个方面着手
 1.分列方式去掉空格
 选中需要去除空格的单元格,开始->查找和替换， 查找 " "(空格)，替换为"";
 2.规范错误格式的数值（本例中销售额的逗号应该为小数点）
 也是使用查找替换的方式
 3.拆分合并过的单元格
 4.整理日期格式
 使用两个函数：1.DATE(LEFT(C2,4),MID(C2,6,2),RIGHT(C2,2))
 2.YEAR(D6)&"-"&TEXT(MONTH(D6),"0#")
 
第三步：数据统计
 
1.计算按时交货百分比
 插入->数据透视表->选择要使用的字段在右侧拖动字段对应到右下角行标签列标签和数值
 
 
 
 
  
 
  

   
  
 
 
 
 

  11.png
 
 
 
生成的数据表如下：
 
 
 
 
  
 
  

   
  
 
 
 
 

  12.png
 
 


 
统计交货的百分比，选中数据框，右键->值字段设置->值显示方式->选占同行数据百分比
 结果如下
 
 
 
 
  
 
  

   
  
 
 
 
 

  333.png
 
 


 
2.查看每月的销售额柱状图，首先生成如下数据透视表
 

 
 
 
 
  
 
  

   
  
 
 
 
 

  每
 
 
 
然后点击插入->柱状图->二维柱状图 结果如下：
 
 
 
 
  
 
  

   
  
 
 
 
 

  66.png
 
 


 
3.使用三维饼状图分析合格货品的百分比
 1.还是新建数据透视表，字段分别为
 
 
 
 
  
 
  

   
  
 
 
 
 

  合格货品百分比数据表.png
 
 


 
2.统计质量合格的每一个货品的百分比，值的显示方式为，按列的百分比，如下图：
 

 
 
 
 
  
 
  

   
  
 
 
 
 

  .png
 
 


 
3.插入->饼状图->三维饼图 ，效果如下
 
 
 
 
  
 
  

   
  
 
 
 
 

  合格货品百分比三维图.png
 
 


 
可以得出结论，货品1的合格率是最高的，最低的是货品4
 
第四步 ：总结
 
数据分析阶段的一些常用分析法
 

 
 
 
 
  
 
  

   
  
 
 
 
 

  常用分析法
 
 
 
1.现状分析(对比)
 
1.1对比分析法
 
完成值和目标值得对比、时间维度上的对比、同公司各部门的对比、行业竞争对手间的对比，注意计算方法一致，对象一致，指标类型一致
 
1.2.综合评价分析法
 
基本思想是将多个指标转化为一个能反应综合情况的指标来进行分析评定，比如不同国家的经济实力、地区社会发展水平、企业的经济效益评价等等，主要有五个步骤
 
step1 确定综合评价指标体系，即包含哪些指标(字段)，是综合评价的基础和依据
step2 搜集数据，对不同计量单位(字段)进行标准化处理
 -step3 确定指标体系中各指标的权重，以保证评价评价的科学性
 -step4 对经处理的指标再进行汇总计算出综合评价指数或者综合评价分值
step5 根据评价指数或分值对参数单位进行排序，并由此得出结论
 
2.原因分析
 
1.结构分析法
 
结构相对指标（比例）的计算公式
 结构相对指标(比例) = （总体某部分的数值/总体总量）*100%
 
2.杜邦分析法
 
利用各主要比率之间的内在联系，建立财务比率分析的综合模型，来综合分析和评价企业财务状况和经营业绩的方法
 
3.漏斗图分析法
 
提供用户在业务中的转化率和流失率，还揭示了各种业务在网站中受欢迎的程度
 
 
 
 
  
 
  

   
  
 
 
 
 

  用户购买商品的交易情况
 
 
 
4.矩阵关联分析法
 
常用的系统综合评价法，主要是用矩阵形式来表示每个代替方案有关评价指标及其重要度和方案关于具体指标的价值评定量之间的关系
 
 
 
 
  
 
  

   
  
 
 
 
 

  关联分析法
 
 
 
3.其他高级方法
 
 
 
 
  
 
  

   
  
 
 
 
 
 
 
数据展示
 
可以参考这篇博客
 如何制作图表非常精美的 Excel 文档？ - 忽如远行客的回答
 成分:饼状图、柱形图、条形图、瀑布图
 排序：柱形图、条形图、气泡图、帕累托图
 时间序列：折线图、柱形图
 相关性：散点图、柱形图、对称条形图、气泡图
 多重数据比较：雷达图
 图形注意事项
 
信息完整：图表标题、单位、图例、脚注、来源等
避免无意义的图表
一表反映一个观点
只选对的不选复杂的图表
标题一句话阐述清楚反映观点
 
第五步：撰写报告
 
数据分析报告的作用：展示分析结果，验证分析质量，提供决策依据
 
1.标题页
 
标题类型：解析基本观点，概括主要内容，交代分析主题 提出问题
 标题要求：简洁明了
 
2.前言
 
分析背景：为何开展此次分析，有何意义
 分析目的:通过此次分析要解决什么问题，达到何种目的
 分析思路：如何开展此次分析，主要通过哪几方面开展
 
3.正文
 
报告的主体部分，包含所有数据分析事实和观点，通过数据 图表和相关文字结合分析，正文各部分具有逻辑关系
 
4.结语
 
通过上述的分析内容，得出总结结论，合理推测可能导致这种结果的原因，让领导心中有数，如果存在不足，也可以提出自己合理化的改进建议
 
到这里，使用excel 进行基础数据整理分析的内容就差不多完结了，做任何事情流程都是很重要的，合理化的流程可以很好的保证处理事情井然有序，当然也要明白一点，数据仅仅是改进产品或者分析问题的一个很重要的手段，并不能从数据结论中盖棺定论，毕竟数据只是客观描述，事情的发生还是有很多主观因素的存在，需要从各方面来综合分析得出结论
 
      </div>
    </div>