要想精進Excel技巧，別急著投入研究各種複雜的函數，而是先弄懂Excel內建的「樞紐分析」。這個項目操作起來簡單、方便上手，但功能卻十分強大，可以說是Excel最重要的精髓！

《Excel工作現場實戰寶典》作者王作桓指出，精熟樞紐分析技巧，幾乎可以解決8成Excel分析需求，幫你洞察資料內真正有意義的訊息。下面以市場調查的問卷資料為例，說明樞紐分析的做法。

Step1. 設定資料分析範圍，建立樞紐分析表

假設《經理人月刊》希望針對願意花比較多錢買雜誌的人推出新雜誌，為了滿足潛在顧客的需求，特別請行銷部做了一次市場調查，以決定應該跨入的領域。身為專案負責人，如果想從這一批調查問卷資料裡快速歸納出建議的策略，最適合的處理工具就是樞紐分析表。

按圖可放大

1. 點選「插入」索引標籤裡的「樞紐分析表」

請Excel新增一張樞紐分析表。

2. 在「建立樞紐分析表」的對話框確認表格範圍「樞紐! $A$1:$I$151」

告訴Excel你想分析的資料在哪裡。我們把「樞紐」設定為這張工作表的名稱，「$A$1:$I$151」是指這張工作表中A1到I151的所有資料。大部分的時候Excel會預抓資料範圍，但假如你的資料中間有空白列，就必須自己重新輸入資料範圍來設定。

3. 選擇放置樞紐分析表的位置「新工作表」

你也可以選擇放在現有的工作表中，但除非原本的資料內容很少，否則為了避免雜亂，推薦做法還是把樞紐分析放在新工作表。

Step2. 調整樞紐分析表的組成，把想分析的欄位放進去

在樞紐分析的使用方法前，先解釋一下樞紐分析工作表的版面配置。左邊是最終報表呈現的區域，右邊的「樞紐分析表欄位」是用來控制報表呈現內容，只要調動設定，左邊報表會立即更新。

按圖可放大

樞紐分析表欄位又分為上下兩個區塊，上半部是勾選想出現在報表的資料欄位（貼心提醒：為了顯示資料欄位，原始資料清單的第一列都要有欄位名稱），下半部則是你希望這些資料出現在報表中的哪個位置（篩選、列、欄、值），就把上半的資料欄位拖曳到對應的區域：

A 篩選：拖曳至該區域的欄位，將做為篩選整張報表資料的依據。

比方說把「月收入」放在這，你就能透過篩選，讓報表只顯示3萬以上的資料。

B 列：拖曳至該區域的欄位，會變成樞紐分析表的列資料。

填入超過一個欄位資料，Excel會在報表上進行分組，例如先放入「性別」，再放「每月花費多少錢買雜誌」，報表呈現就會是同性別裡、不同花費金額的統計結果，如果想要改變分組的順序，變成像上一頁中每月花費相同金額買雜誌、不同性別的統計結果，就要透過拖曳，把「每月花費多少錢買雜誌」放在「性別」之後。

C 欄：拖曳至該區域的欄位，會變成樞紐分析表的欄資料。

拖曳至該區域的欄位，會變成樞紐分析表的欄資料。和列標籤一樣，只要填入超過一個欄位的資料，Excel就會幫忙分組來統計資料。想改變分組的方式，就要調動欄位在欄標籤的擺放順序。

D 值：拖曳至該區域的欄位，表示要請Excel統計匯總。

拖曳至該區域的欄位，表示要請Excel統計匯總。針對放入此區域的項目右側箭頭按下左鍵，點選「值欄位設定」，設定你希望Excel是計算加總、平均值、項目個數、最大值，還是最小值等。

這張分析表，你可以這樣解讀：

本來的銷售策略是希望針對願意花比較多錢買雜誌的人推出新雜誌，那應該把通路設定在哪？ 假設願意花比較多錢是指「每月花費400元以上」買雜誌的人。

你會發現如果要主打男生，網路書店會是不可或缺的通路之一，但願意花更高消費金額在雜誌上的女生，反而沒這麼依賴網路。而一般書店則是不論男女，都會購入雜誌的管道。

Step3. 時間、金額等數字資料，可以再做合併

如果本來的問卷設計是請受訪者直接填寫「實際年齡」，而不是勾選年齡範圍，當資料丟進樞紐分析後，就會出現各個歲數的分析結果，顯得太過詳細，不方便判別。

針對這種數字資料，Excel可以幫你用群組功能，把同一個區段的資料併成一組，以年齡來說，你可以讓Excel把各歲數合併為11～20、21～30、31～40、41～50、51～60來呈現。這個做法，在分析「時間」相關的資料也格外好用，可以把資料按照年、月、季、天數來分組呈現，方便你進行比較。

按圖可放大

做法如下：

1. 選擇樞紐分析表中、年齡列標籤的任一儲存格

2. 按右鍵點選「群組」

3. 開始點設為11，結束點設為60，間距為10

4. 按下「確定」就完成

说到Excel表格的使用，很多人首先想到的肯定是求和、统计、函数、透视表等等，对于这些常用的Excel功能技巧，我们就不多说了，

其实在Excel表格中，它还有一个隐藏的小技巧——快速分析，不知道有多少人知道了？

1.图表功能

在Excel表格制作中，我们可以通过快速分析功能快速制作图表，其实用这个快速分析功能是非常简单的，首先选中【数据】-点击数据右下角的【快速分析功能】-选择【图表】，这时我们选择的图表就会根据单元格中的数据进行变化，用这个完成图表的创建是十分的简单、方便。

2.数据条功能

数据条功能其实是相对于【条件格式】，首先全选数据，然后通过点击快速分析中的【格式化】选择【数据条】，然后点击【色阶】进行对数据的填充，最后点击【大于】标记处特定大小数据，这样我们的整个数据条就会变得很清晰

3.迷你图功能

迷你图功能也是包含在快速分析功能中的，其实他的操作和上面的差不多，都需要先选中数据，然后点击快速功能中的迷你图，然后我们可以对创建好的迷你图进行修改，如，点击【设计】，标出最高点。

4.汇总功能

很多时候，我们需要对数据进行汇总求和，有的是需要利用公式才能进行计算，今天的这种最常规的计算是不需要进行输入公式或是函数的。具体操作如下：

选中数据区域，然后通过快速分析功能选择【汇总】，然后就可以对数据进行求和、求平均值等计算。

5.表格功能

虽然我们可以用菜单中的功能对数据制作透视表，但今天我们不用菜单栏中的功能制作，用的是Excel中隐藏的功能——快速分析功能

首先选中数据区域，然后利用快速分析功能中的【表格】功能，然后点击【透视表】，就可以对数据制作透视表了。

在Excel表格制作时，这些都是我们需要用到的一些小技巧，虽然这只是一个小小的功能，但可以给你带来不少的便捷了，喜欢就赶紧动手试试吧。

每个人都会有机会进行数据展示，为什么别人展示永远获得正视，而我的展示永远只有自己愿意去看，别人在看手机？那怎样做数据图表分析呢请看以下步骤：

如何对表格进行修饰，本次小编带来两个技巧，一是使用“套用表格格式”，和使用“条件格式”。二是带领大家学会养成修饰表格的思维。

第一步是对表格进行粗略的修饰调整，思维：行高、列宽、对齐方式、表格线等；

使用“套用表格格式”、“条件格式”之后看数据不再枯燥无味，而且还更有看头。“条件格式”可以将筛选条件转换为颜色可视化，从而达到一目了然的效果。

第一个技巧，①“套用表格格式”。方法：任一单元格→开始→套用表格格式。

②“条件格式”，方法：选中单元格区域→开始→条件格式。

条件1：高于平均值

条件2：数据条

条件3：色阶

第二个技巧：养成修饰图表的思维。这次举例柱形图的修饰例子，其他希望大家动用类似的方法进行模拟实践。

步骤一：根据销售数据建立柱状图，建立方法可参考。选择数据源→插入→柱状图→选择数据源→编辑坐标

步骤二：添加辅助线。选择数据源→→添加→点击柱体右键，设置数据系列格式→次坐标轴→选中柱体，右键更改图表类型→折线图。

希望回答对你能有所帮助，如果觉得不错就来点个赞或关注吧，感谢各位了！

又是一年实习季......

很多优秀的金融小伙伴已经陆陆续续进入了各大券商实习，希望施展拳脚，争取留用机会。

最近接到几位朋友的倾诉：

“工作后发现，很多之前听过基础的办公软件，偷懒没学，没想到工作中经常需要用到，然而自己却用的不溜，有些甚至一点不会......”“像数据分析工具，基本从事券商行业都得用，可是老员工一般都比较忙，根本没时间教，只能自己毫无效率的一点一点摸索，唉......”“一起进去实习的有一位，因为提前学习了相关的技能，既会excel数据分析，又会用VBA编程，各种自动化操作，领导一直夸赞，感觉自己留用无望了......”

听完之后，小编迅速打开招聘网站，果然...... 大部分券商投行等岗位招聘要求是这样的：

毫无疑问，快速搜集、处理大量金融数据并形成客户和老板想要的结果，是每一个金融从业者都必须具备的核心技能。数据的收集处理能力和财务分析能力一样，构成了金融大咖们的两大核心竞争力。

因此，对于游走在业绩分析会与路演会间隙做相关数据报告的资深金融人来说，会数据分析尤为重要。

除此之外

金融人的工作里头还有大量的Excel表格需要处理，而且需要做的非常高端上档次。因为这样，领导拿出去用，才倍儿有面儿！

像这样......

这样......

因此，花极少的时间，就掌握一门高大上的数据可视化技能，非Excel数据分析不可！这也是金融人士应该掌握的技能。

还没结束......

仅简单的Excel技能还不足以满足金融人士的处理需求，在很多条件下，高阶函数和Excel VBA才是制胜王道。如何使用Excel胜任批量操作、处理复杂函数甚至自定义Excel选项卡，是投行大咖的基本标配，也是金融人士的进阶技能——Excel VBA。

Excel VBA批处理起来，效率那叫一个快！主要用于处理Wind终端导出来的数据，学起来比较简单，属于金融从业者性价比最高的技能之一。

还可以自动制做可视化图表，批量整理各种数据......

回归线Excel，致力于为客户提供基于Excel的解决方案。帮助客户提高Excel应用水平，扩展Excel应用能力，改进工作效率，对数据收集，数据清洗，数据分析，数据展现等都有非常直接的经验和方法。

前言

1.“回归”一词的由来

我们不必在“回归”一词上费太多脑筋。英国著名统计学家弗朗西斯·高尔顿（Francis Galton,1822—1911）是最先应用统计方法研究两个变量之间关系问题的人。“回归”一词就是由他引入的。他对父母身高与儿女身高之间的关系很感兴趣，并致力于此方面的研究。高尔顿发现，虽然有一个趋势：父母高，儿女也高；父母矮，儿女也矮，但从平均意义上说，给定父母的身高，儿女的身高却趋同于或者说回归于总人口的平均身高。换句话说，尽管父母双亲都异常高或异常矮，儿女身高并非也普遍地异常高或异常矮，而是具有回归于人口总平均高的趋势。更直观地解释，父辈高的群体，儿辈的平均身高低于父辈的身高；父辈矮的群体，儿辈的平均身高高于其父辈的身高。用高尔顿的话说，儿辈身高的“回归”到中等身高。这就是回归一词的最初由来。

回归一词的现代解释是非常简洁的：回归时研究因变量对自变量的依赖关系的一种统计分析方法，目的是通过自变量的给定值来估计或预测因变量的均值。它可用于预测、时间序列建模以及发现各种变量之间的因果关系。

使用回归分析的益处良多，具体如下：

1) 指示自变量和因变量之间的显著关系；

2) 指示多个自变量对一个因变量的影响强度。

回归分析还可以用于比较那些通过不同计量测得的变量之间的相互影响，如价格变动与促销活动数量之间的联系。这些益处有利于市场研究人员，数据分析人员以及数据科学家排除和衡量出一组最佳的变量，用以构建预测模型。

2.为什么使用回归分析

1）更好地了解

对某一现象建模，以更好地了解该现象并有可能基于对该现象的了解来影响政策的制定以及决定采取何种相应措施。基本目标是测量一个或多个变量的变化对另一变量变化的影响程度。示例：了解某些特定濒危鸟类的主要栖息地特征（例如：降水、食物源、植被、天敌），以协助通过立法来保护该物种。

2）建模预测

对某种现象建模以预测其他地点或其他时间的数值。基本目标是构建一个持续、准确的预测模型。示例：如果已知人口增长情况和典型的天气状况，那么明年的用电量将会是多少？

3）探索检验假设

还可以使用回归分析来深入探索某些假设情况。假设您正在对住宅区的犯罪活动进行建模，以更好地了解犯罪活动并希望实施可能阻止犯罪活动的策略。开始分析时，您很可能有很多问题或想要检验的假设情况。

回归分析的作用主要有以下几点：

1）挑选与因变量相关的自变量；

2）描述因变量与自变量之间的关系强度；

3）生成模型，通过自变量来预测因变量；

4）根据模型，通过因变量，来控制自变量。

回归分析方法

现在有各种各样的回归技术可用于预测，这些技术主要包含三个度量：自变量的个数、因变量的类型以及回归线的形状。

1.回归分析方法

1）线性回归

线性回归它是最为人熟知的建模技术之一。线性回归通常是人们在学习预测模型时首选的少数几种技术之一。在该技术中，因变量是连续的，自变量（单个或多个）可以是连续的也可以是离散的，回归线的性质是线性的。线性回归使用最佳的拟合直线（也就是回归线）建立因变量 (Y) 和一个或多个自变量 (X) 之间的联系。用一个等式来表示它，即：

Y=a+b*X + e

其中a 表示截距，b 表示直线的倾斜率，e 是误差项。这个等式可以根据给定的单个或多个预测变量来预测目标变量的值。

一元线性回归和多元线性回归的区别在于，多元线性回归有一个以上的自变量，而一元线性回归通常只有一个自变量。

线性回归要点：

1）自变量与因变量之间必须有线性关系；

2）多元回归存在多重共线性，自相关性和异方差性；

3）线性回归对异常值非常敏感。它会严重影响回归线，最终影响预测值；

4）多重共线性会增加系数估计值的方差，使得估计值对于模型的轻微变化异常敏感，结果就是系数估计值不稳定；

5）在存在多个自变量的情况下，我们可以使用向前选择法，向后剔除法和逐步筛选法来选择最重要的自变量。

2）Logistic回归

Logistic回归可用于发现 “事件=成功”和“事件=失败”的概率。当因变量的类型属于二元（1 / 0、真/假、是/否）变量时，我们就应该使用逻辑回归。这里，Y 的取值范围是从 0 到 1，它可以用下面的等式表示：

odds= p/ (1-p) = 某事件发生的概率/ 某事件不发生的概率

ln(odds) = ln(p/(1-p))

logit(p) = ln(p/(1-p)) =b0+b1X1+b2X2+b3X3....+bkXk

如上，p表述具有某个特征的概率。在这里我们使用的是的二项分布（因变量），我们需要选择一个最适用于这种分布的连结函数。它就是Logit 函数。在上述等式中，通过观测样本的极大似然估计值来选择参数，而不是最小化平方和误差（如在普通回归使用的）。

Logistic要点：

1）Logistic回归广泛用于分类问题；

2）Logistic回归不要求自变量和因变量存在线性关系。它可以处理多种类型的关系，因为它对预测的相对风险指数使用了一个非线性的 log 转换；

3）为了避免过拟合和欠拟合，我们应该包括所有重要的变量。有一个很好的方法来确保这种情况，就是使用逐步筛选方法来估计Logistic回归；

4）Logistic回归需要较大的样本量，因为在样本数量较少的情况下，极大似然估计的效果比普通的最小二乘法差；

5）自变量之间应该互不相关，即不存在多重共线性。然而，在分析和建模中，我们可以选择包含分类变量相互作用的影响；

6）如果因变量的值是定序变量，则称它为序Logistic回归；

7）如果因变量是多类的话，则称它为多元Logistic回归。

3）Cox回归

Cox回归的因变量就有些特殊，它不经考虑结果而且考虑结果出现时间的回归模型。它用一个或多个自变量预测一个事件（死亡、失败或旧病复发）发生的时间。Cox回归的主要作用发现风险因素并用于探讨风险因素的强弱。但它的因变量必须同时有2个，一个代表状态，必须是分类变量，一个代表时间，应该是连续变量。只有同时具有这两个变量，才能用Cox回归分析。Cox回归主要用于生存资料的分析，生存资料至少有两个结局变量，一是死亡状态，是活着还是死亡；二是死亡时间，如果死亡，什么时间死亡？如果活着，从开始观察到结束时有多久了？所以有了这两个变量，就可以考虑用Cox回归分析。

4）poisson回归

通常，如果能用Logistic回归，通常也可以用poission回归，poisson回归的因变量是个数，也就是观察一段时间后，发病了多少人或是死亡了多少人等等。其实跟Logistic回归差不多，因为logistic回归的结局是是否发病，是否死亡，也需要用到发病例数、死亡例数。

5）Probit回归

Probit回归意思是“概率回归”。用于因变量为分类变量数据的统计分析，与Logistic回归近似。也存在因变量为二分、多分与有序的情况。目前最常用的为二分。医学研究中常见的半数致死剂量、半数有效浓度等剂量反应关系的统计指标，现在标准做法就是调用Pribit过程进行统计分析。

6）负二项回归

所谓负二项指的是一种分布，其实跟poission回归、logistic回归有点类似，poission回归用于服从poission分布的资料，logistic回归用于服从二项分布的资料，负二项回归用于服从负二项分布的资料。如果简单点理解，二项分布可以认为就是二分类数据，poission分布就可以认为是计数资料，也就是个数，而不是像身高等可能有小数点，个数是不可能有小数点的。负二项分布，也是个数，只不过比poission分布更苛刻，如果结局是个数，而且结局可能具有聚集性，那可能就是负二项分布。简单举例，如果调查流感的影响因素，结局当然是流感的例数，如果调查的人有的在同一个家庭里，由于流感具有传染性，那么同一个家里如果一个人得流感，那其他人可能也被传染，因此也得了流感，那这就是具有聚集性，这样的数据尽管结果是个数，但由于具有聚集性，因此用poission回归不一定合适，就可以考虑用负二项回归。

7）weibull回归

中文有时音译为威布尔回归。关于生存资料的分析常用的是cox回归，这种回归几乎统治了整个生存分析。但其实夹缝中还有几个方法在顽强生存着，而且其实很有生命力。weibull回归就是其中之一。cox回归受欢迎的原因是它简单，用的时候不用考虑条件（除了等比例条件之外），大多数生存数据都可以用。而weibull回归则有条件限制，用的时候数据必须符合weibull分布。如果数据符合weibull分布，那么直接套用weibull回归自然是最理想的选择，它可以给出最合理的估计。如果数据不符合weibull分布，那如果还用weibull回归，那就套用错误，结果也就会缺乏可信度。weibull回归就像是量体裁衣，把体形看做数据，衣服看做模型，weibull回归就是根据某人实际的体形做衣服，做出来的也就合身，对其他人就不一定合身了。cox回归，就像是到商场去买衣服，衣服对很多人都合适，但是对每个人都不是正合适，只能说是大致合适。至于到底是选择麻烦的方式量体裁衣，还是选择简单到商场直接去买现成的，那就根据个人倾向，也根据具体对自己体形的了解程度，如果非常熟悉，自然选择量体裁衣更合适。如果不大了解，那就直接去商场买大众化衣服相对更方便些。

8）主成分回归

主成分回归是一种合成的方法，相当于主成分分析与线性回归的合成。主要用于解决自变量之间存在高度相关的情况。这在现实中不算少见。比如要分析的自变量中同时有血压值和血糖值，这两个指标可能有一定的相关性，如果同时放入模型，会影响模型的稳定，有时也会造成严重后果，比如结果跟实际严重不符。当然解决方法很多，最简单的就是剔除掉其中一个，但如果实在舍不得，觉得删了太可惜，那就可以考虑用主成分回归，相当于把这两个变量所包含的信息用一个变量来表示，这个变量我们称它叫主成分，所以就叫主成分回归。当然，用一个变量代替两个变量，肯定不可能完全包含他们的信息，能包含80%或90%就不错了。但有时候我们必须做出抉择，你是要100%的信息，但是变量非常多的模型？还是要90%的信息，但是只有1个或2个变量的模型？打个比方，你要诊断感冒，是不是必须把所有跟感冒有关的症状以及检查结果都做完？还是简单根据几个症状就大致判断呢？我想根据几个症状大致能确定90%是感冒了，不用非得100%的信息不是吗？模型也是一样，模型是用于实际的，不是空中楼阁。既然要用于实际，那就要做到简单。对于一种疾病，如果30个指标能够100%确诊，而3个指标可以诊断80%，我想大家会选择3个指标的模型。这就是主成分回归存在的基础，用几个简单的变量把多个指标的信息综合一下，这样几个简单的主成分可能就包含了原来很多自变量的大部分信息。这就是主成分回归的原理。

9）岭回归

当数据之间存在多重共线性（自变量高度相关）时，就需要使用岭回归分析。在存在多重共线性时，尽管最小二乘法（OLS）测得的估计值不存在偏差，它们的方差也会很大，从而使得观测值与真实值相差甚远。岭回归通过给回归估计值添加一个偏差值，来降低标准误差。

上面，我们看到了线性回归等式：

y=a+ b*x

这个等式也有一个误差项。完整的等式是：

y=a+b*x+e (误差项), [误差项是用以纠正观测值与预测值之间预测误差的值]

=> y=a+y= a+ b1x1+ b2x2+....+e, 针对包含多个自变量的情形。

在线性等式中，预测误差可以划分为 2 个分量，一个是偏差造成的，一个是方差造成的。预测误差可能会由这两者或两者中的任何一个造成。在这里，我们将讨论由方差所造成的误差。岭回归通过收缩参数 λ（lambda）解决多重共线性问题。请看下面的等式：

在这个等式中，有两个组成部分。第一个是最小二乘项，另一个是 β2（β-平方）和的 λ 倍，其中 β 是相关系数。λ 被添加到最小二乘项中用以缩小参数值，从而降低方差值。

岭回归要点：

1）除常数项以外，岭回归的假设与最小二乘回归相同；

2）它收缩了相关系数的值，但没有达到零，这表明它不具有特征选择功能；

3）这是一个正则化方法，并且使用的是 L2 正则化。

10）偏最小二乘回归

偏最小二乘回归也可以用于解决自变量之间高度相关的问题。但比主成分回归和岭回归更好的一个优点是，偏最小二乘回归可以用于例数很少的情形，甚至例数比自变量个数还少的情形。所以，如果自变量之间高度相关、例数又特别少、而自变量又很多，那就用偏最小二乘回归就可以了。它的原理其实跟主成分回归有点像，也是提取自变量的部分信息，损失一定的精度，但保证模型更符合实际。因此这种方法不是直接用因变量和自变量分析，而是用反映因变量和自变量部分信息的新的综合变量来分析，所以它不需要例数一定比自变量多。偏最小二乘回归还有一个很大的优点，那就是可以用于多个因变量的情形，普通的线性回归都是只有一个因变量，而偏最小二乘回归可用于多个因...

点击上方

“中税网”

可关注我们！

Excel的功能很强大，可以做各种数据处理和分析。下面给大家分享一下如何运用最新版的Excel2016做线性回归图。

1、首先输入你要做线性图的数据，我这里举例输入了五组数据。

2、用鼠标选中你要做线性图的数据，再点击菜单栏的“插入”菜单。

3、在插入菜单下选择插入散点图，弹出如图所示的一个散点图。

4、鼠标对着散点图中的其中一个点，点击鼠标右键，选着“添加趋势线R”。

5、在右边弹出的“添加趋势线格式”栏中分别选着“线性”、“设置截距”、“显示公式”、“显示R平方值”等你需要的项目。

6、如果要添加横、纵坐标，则鼠标点击线性图右上角的“+”号按钮，在弹出的图标元素中选着"坐标轴标题",再进一步选着“主要横坐标轴”和“主要纵坐标轴”。

7、最后在线性图中分别输入你的坐标轴名称和图表标题。

原创：有点瘦的胖子零一

需要预测的场景太多这里就不一一赘述了，在师傅的指导下，我对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为截距的意思。

→返回测试集验证

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

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

前面对时间序列的预测方法做了简单的介绍，

其中反复提到的数据分析工具--回归

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

今天就通过一组数据，来演示一下回归工具的使用方法：

第一种：线性回归

时间做为自变量，销量为因变量

按步骤选择好，按确定，会自动输出结果：

这个结果，大家可以研究一下，统计学的相关指标里面都有

我们关注两点：

R Square：的值越大，这个这条回归线越贴合实际值

Intercept：回归线与Y轴的交点

X Variable 1：系数

方程式就是这样的：Y=Intercept+X Variable 1*TIME

第二种：非线性回归

我们学过的一元多次方程

把时间变量，平方、立方列出来，做自变量

我们得到的结果就多了几个系数：

第三种：多元回归

时间、汽车产量作为自变量

第四种：自回归

把汽车销量滞后一期作为自变量

最后我们来看看成果

多元回归的R Square值是最大的，多元回归的曲线与实际销量曲线最为贴合

也说明，汽车产量与销量高度相关。

说在最后：今年汽车销售市场应该说不景气，到了增长的拐点。

数据分析只是根据历史数据做的一个判断

然而，市场环境恶化，经济下行等宏观因素，对未来的汽车销售的影响巨大

在有宏观环境因素变化时，就要谨慎使用历史数据

在平稳运行时，依靠数据预测更为准确。

最近几天，不断有小伙伴在后台问到使用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，如下图所示：

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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