1.Excel实现回归分析的两种方式

在如何制定办公用品的营销战略的案例中，Excel标准的多元回归分析要求解释变量不得大于15，所以首先排除了周二和周四处理率这两个解释变量。而用挖掘功能来实现多元回归分析时，并没有这个限制，因此不需要手动重复筛选变量。

Excel 2016安装数据挖掘插件

原理：
Excel通过office的数据挖掘外部链接程序来连接analysis server，进而使用analysis server的服务来完成数据挖掘操作。而analysis server的安装则是在sql sersver的安装过程中一起完成的。

安装步骤：

1. 安装sql server
2. 安装office 2013
3. 安装sql server office数据挖掘外部链接程序
4. 卸载2013

说明：

• 总体安装步骤参阅excel数据挖掘插件的安装。

• 参阅Analysis Services各个版本的功能，发现SQL Server Analysis Services 2017弃用了数据挖掘功能，所以我选择安装的是免费版的SQL Server Devoplper 2016。安装步骤参阅SQL Server安装。

• 我在了解Analysis Services 实例的服务器模式后，第一次安装sql server时，选择的是以“表格”模式安装analysis server，但是这会出现一些问题。问题1，配置office外部链接程序时，无法创建新的数据库。问题2，Excel连接analysis server后，尝试创建挖掘结构时失败，原因是以表格模式运行的analysis server中无默认数据库。问题3，用SSMS试图在analysis server中新建数据库失败，原因是以表格模式运行的analysis server没有新建数据库这个功能。其实，这3个问题是一样的。参阅Changing an Analysis Services instance to tabular mode，我试着更改Analysis Services的服务器模式为多维模式，但是不起作用，微软官网也有提示“不支持在安装服务器后手动更改该属性”，以及“如果您不想使用所安装的服务器模式，则必须卸载后再重新安装该软件，并且选择想要的模式。”没有找到比较好的解决方案，我最后选择卸载重来，在第二次安装的时候，将analysis server指定为“多维模式”。

• 下载SQL Server Office 数据挖掘外接程序，安装时要勾选“Excel数据挖掘客户端”。安装完成后，启动Excel 2016，会自动弹出配置对话窗，参阅配置步骤完成相关配置。

体会：

• 不熟悉软件，经常卡死在软件的安装或配置上，追根到底，软件安装最重要的是懂得如何配置，为什么要这样配置。
• 本来打算不用Excel来进行数据挖掘了，但是一方面想要直接用SPSS软件完成，另一方面又非常意动，放弃又不甘心。一想到问题既然出现了，按照以往的经验，现在不解决，以后还会遇到同样的问题，所以最终下定决心查阅解决方案，不断尝试。
• 综上，事情很简单，但心路是曲折的。

2.创建挖掘结构

1. 按图1所示的步骤，打开“创建挖掘结构”向导对话框。

   
   
   图1.png

2. 如图2所示，选择合并后的表格化数据作为数据源。

   
   
   图2.png

3. 选择要追加到挖掘结构中的数据列，如图3所示，“员工ID” 选择“键”，其他列全部选择“包括”。

   
   
   图3.png

4. 点击图3中“用法”后面的“...”，在弹出的如图4所示的设置列建模标志对话框中，确定内容类型为“Continuous”，确保用于分析的数据是定量数据。

   
   
   图4.png

5. 在图5所示的对话框中，将“要测试的数据的百分比”指定为“0”，因为这里不需要留一部分数据作为测试数据以验证预测的精确度。

   
   
   图5.png

有时候会先用一部分定性数据做回归分析，然后验证用这部分数据得到的结果跟剩余的数据（测试数据）有多大的匹配度（交叉验证法）。

6. 如图6所示，修改结构名称。

   
   
   图6.png

3.用创建的挖掘结构做多元回归分析

1. 如图7和图8所示，将模型添加到结构。

   
   
   图7.png
   
   
   图8.png

2. 如图9所示，指定分析方法为“线性回归”。

   
   
   图9.png

3. 在图10所示的对话框中，“员工ID”选择“键”，输出结果“销售额”选择“仅预测”，其他所有解释变量选择“输入”。

   
   
   图10.png

4. 确认图11所示的信息无误后，点击“完成”确认添加模型到结构。

   
   
   图11.png

5. 打开SSMS，浏览挖掘模型“各员工销售额分析-线性回归”，输出结果如图12和图13所示。

   
   
   图12.png
   
   
   图13.png

未解决的错误
Reference object not set to instance of object

步骤4完成后，弹出如图14所示的错误，点击“确定”关闭该窗口后，接着弹出如图15所示的窗口。但是，打开SSMS查看analysis server服务器中的数据挖掘模型，“各员工销售额分析-线性回归”挖掘模型成功建立，浏览该模型的如图12、图13所示的结果。这说明，office数据挖掘外部链接程序成功连接了analysis sever，并成功将excel中创建的数据挖掘模型存入数据库，但是excel引用analysis services的实例失败，导致excel中无法正常输出分析结果。

图14.png

图15.png

一开始以为是excel版本问题，但用2013版操作也同样出现了该问题。后来又以为是sql server版本问题，但google到有2014版sql server一开始能正常使用，过了一段时间，也出现了上述问题的情况。

暂时还未找到有效的解决方案。
https://stackoverflow.com/questions/31636714/sql-server-excel-data-mining-addin-error-message
https://answers.microsoft.com/en-us/msoffice/forum/all/data-mining-add-ins-for-excel-2013/9d6af00d-7e1f-4c66-8588-bbb9390871f3

5.比较两种分析结果

图13中，系数是各解释变量对应的回归系数，最上面一行的空白项，是截距。

观察每项解释变量：

• “PC 其他用品”每增加1，销售额随之增加约3132566日元；
• “压力测试_ 加班时间”每增加1，销售额随之减少约126791日元；
• “生活用品”“文件夹”“文具”占比每增加1，销售额分别随之减少约11210606日元、13073854日元、5276984日元。

与如何制定办公用品的营销战略中的结果相比，“PC其他用品”的销售额占比最多和对加班敏感的员工销售额低这两项结果大致一样。另外，文件夹、生活用品和文具的占比越高，销售额越低，与PC主机、打印、电气化产品等占比越高销售额越高是相对的。

注意，用Excel标准回归分析工具进行分析时，加班处理率、入职测试、性格测试等与销售额体现了关联性，但是这里却没有在输出结果中保留下来。原因可能是之前是按p值小于0.05的标准进行变量取舍的，这里采用了更加严格的变量选择法，所以导致了结果偏差。

用Excel标准回归分析工具对数据挖掘输出结果中的几个解释变量进行回归分析，结如图16所示。将图16所得的输出结果与数据挖掘回归分析结果整理成图17所示的表格，发现两者的回归系数相差不大，且Excel标准回归分析得到的p值都小于0.05，说明两种实现回归分析的方式是一样的。

图16.png

图17.png