1.案例背景

某办公用品公司提供了2013年3月（旺季）的销售数据，要求分析这个月的销售数据，在能够追加其他数据的条件下，制定一份可行的办公用品营销战略。

2.案例数据

2.1 数据概况

图1.png

2.2 输出结果

很明显，这次分析的输出结果为销售额。

2.3 分析单位

回顾数据分析思路初步探索中的分析单位选择注意事项，分析图1中的各个数据项，从而确定分析单位。

注意事项一，从数据中能挖掘出的很多分析单位。满足这一点的有订单ID、订单日期、处理时间、客户ID、员工ID、商品类型。

注意事项二，一旦确定分析单位了，就能提出解决方案了。考虑到3月是销售旺季，那么订单ID、订单日期及处理时间能分析出来的结果是哪个时间段销售很好，从而能指定的策略是在该时间段重点营销。但是，3月份本来就是销售旺季，员工一直处于忙碌状态，这样一来，前述三个数据项用作分析单位并不合适。

筛选过后剩下的分析单位备选项有客户、员工和商品类型。

1. 如果考虑商品类型的小类别，可以划分出很多个分析单位。
2. 针对客户而言，如果找出了“购买金额大的客户与金额小的客户”之间的差异，可以重点营销该类客户，并根据该差异也可以开拓新客户。
3. 而对员工来说，如果找出了“销售额高的员工与销售额低的员工”存在什么差异，就能确定后面员工的培训方向，这有利于提高销售团队的销售水平。

图2.png

图3.png

所以，将分析单位设为员工。

2.4 解释变量

跟员工有关的姓名、销售额、测试成绩等特征都是解释变量。

3.案例过程

3.1 以员工为单位重新统计销售数据

1.重新统计销售数据

原始数据是以订单为单位进行统计的，但是现在要以员工为单位进行分析，所以首先需要用数据透视表按照员工来重新统计数据。步骤如下。

1. 选择全部数据，插入数据透视表。
2. 将行标签设为员工ID，将∑值取为销售额。

3. 生成如图4所示的数据透视表。
   图4.png

2 统计各商品类型的销售额在每个员工的总销售额中所占的比例

1. 选择销售记录表的全部数据，插入新的数据透视表。
2. 在数据透视表字段中，将行、列标签分别设为员工ID和商品大类名称，并将∑值设为销售额。

3. 将∑值的值显示方式更改为行汇总百分比，这样就得到了如图5所示的以员工为单位，各商品类型的销售在总销售额中的占比。
   图5.png

3.将订单处理时间设为解释变量

在销售数据表中，新建上午虚拟变量、加班虚拟变量、周一虚拟变量、周二虚拟变量、周三虚拟变量、周四虚拟变量以及周五虚拟变量，完成后的销售数据表如图6所示。

• 上午时间段为9点到12点。
• 加班时间为9点前或17点后。

图6.png

4.统计每位员工的订单处理时间

1. 选择包含虚拟数据的销售记录表的全部数据，插入新的数据透视表。
2. 在数据透视表字段中，将行标签设为员工ID，将∑值设为上午虚拟变量。

3. 将∑值的值汇总方式更改为平均值，得出每位员工上午处理的订单量占总处理量的比重（用小数表示）。用同样的方法处理其他虚拟变量，最终结果如图7所示。
   图7.png

5.验证分析单位员工ID是否错位

1. 新建“ID核查”列，用于核查员工ID 是否错位。
2. 输入=IF（AND（A4=C4，C4=O4），1，0），并将此单元格里的内容复制粘贴至整列。

3. 筛选该列是否存在除了1以外的值，如果没有的话，就确定员工ID没有发生错位。筛选结果如图8所示。
   图8.png

3.2 合并销售、入职测试、压力测试数据表

1.用VLOOKUP函数完成数据的合并

观察职员入职测试表，发现并不是所有的员工都参加了入职测试，所以直接复制粘贴数据会导致错位，因此这里用VLOOKUP函数引用表数据来完成数据的合并。这样，如果存在员工数据缺失的话，VLOOKUP函数就会将其处理为 #N/A。具体实现步骤如下。

1. 在之前完成的数据透视表后面引用员工入职测试表的数据，首先用= 录用时员工数据!C1引用测试的数据项列名。
2. 以引用“录用员工时数据”表的第4列“入职测试（语言处理）”这一列的数据为例，用=VLOOKUP（$A4,录用时员工数据!$A$1:$H$270，4，FALSE）来完成数据的引用。将VLOOKUP函数的第3 项参数，即“引用第几列的值”依次替换成5、6、7、8，完成对其他录用数据的引用。

3. 同理引用“压力测试”的数据。数据引用最终结果如图9所示。
   图9.png

VLOOKUP函数

作用：根据指定的索引，去某个区域查找与该索引匹配的数据。
语法：=VLOOKUP（要查找的值，在哪个区域查找，查找第几列的数据，是否精确匹配）。
参数：

• 要查找的值
• 在哪个区域查找，即查阅值所在的区域。 注意，查阅值应该始终在该区域的第一列。 例如，如果查阅值位于单元格 C2 内，那么区域应该以C开头。
• 查找第几列的数据，即被查找区域中包含返回值的列号。 例如，如果指定区域B2： D11，则应将 B 作为第一列，将 C 作为第二列进行计数，依此类推。
• （可选）如果需要返回值的近似匹配，用TRUE；如果需要返回值的精确匹配，用FALSE。默认值为 TRUE。

示例：=VLOOKUP（$A4，录用时员工数据!$A$1:$H$270，3，FALSE）

• 在“录用时员工数据”表的A1:H270工作区域中，以精确匹配的方式，从A4开始，返回该工作区域的第3列数据。

注意：

• 相对引用、绝对引用和混合引用：$表示对单元格内容的绝对引用。
• 对示例=VLOOKUP（$A4，录用时员工数据!$A$1:$H$270，3，FALSE）来说，$A4是对行的相对引用、对列的绝对引用，即复制该引用到其他单元格，行数会进行相应的变化而列数始终不变。
• 如果将$A4更改为$A$4，并复制该引用到其他单元格，无论如何变化，其他单元格的数值始终是对该对内容的复制，这是对行和列的绝对引用。
• 而$A$1:$H$270是对A1:H270工作区域的绝对引用，这是为了防止引用数据时发生错位。在案例操作时，我曾尝试用A1:H270来引用数据，发现数据引用位置会超出H270。而用A4引用该列数据时，数次实验，发现每次引用到的数据都不一样。

还可以用Power Query来完成数据的合并。

2.调整合并后的数据

将合并后的数据复制到新工作表中，步骤如下。

1. 选定全部数据，以“值”的形式选择性粘贴到新工作表中。
2. 删除重复的员工ID列。
3. 删除不必要的核查ID列、总计列以及总计行。

4. 统计没有参加入职测试的员工的比例，结果如图10所示，比例约为10%，用查找功能找到包含“#N/A”的行，并按照80%原则直接删除这些数据。
   图10.png
5. 核查压力测试相关数据列，看是否还存在“#N/A”行。

6. 调整列名称，将行标签改为员工ID，“求和项: 销售额”改为“销售额”，“PC 其他用品”及其他办公用品以“XXX占比”的形式改名，比如“PC其他用品占比”；“平均值项: 上午虚拟变量”及其他虚拟变量则以“XXX处理率”的形式改名，比如“上午处理率”。调整后的结果如图11所示。
   图11.png

3.有些解释变量不可以同时进行分析

注意，在用回归分析方法分析如何增加营业额案例中，我分两次对解释变量进行了回归分析，这是因为每位顾客的消费额可以由顾客是否点酒、点套餐等解释变量间接分析得出，像这样一个解释变量的值能够通过其他解释变量的值得出，表明这两个解释变量不是相互独立的，而回归分析的对象要求互相独立。

因此，进行多元回归分析之前，如果存在相加必得一定值（如100%）、成组的解释变量，需要剔除部分解释变量。

以订单处理为例，如果订单处理率有工作日处理率和休息日处理率，那么两者的比率相加必得1。因此，如果工作日处理率为80%，那么休息日处理率则为20%。这反映在回归分析中，即分析“休息日处理率每提高1%，销售额随之增加多少”，与分析“（1 －工作日处理率）每提高1%，销售额随之增加多少”是一样的。

思考：在相加必得一定值、成组的解释变量中，要剔除哪些解释变量？

• 用试错的方法，不断尝试排除普通的或例外的数据。
• 以各办公用品所占销售额的比例为例，其占比总和为1，在不确定哪个商品类型的销售额看起来最普通的话，可以从删除“其他”这个例外的解释变量开始进行试错处理。

3.3 明确每位员工身上影响销售的特征

1.从产品类型和处理时间开始分析

在Excel中，多元回归分析的对象最多只能有15个。但是，这里的解释变量有25个：

• 商品类型占比：PC 其他用品占比、PC 主机占比、办公家具占比、包装用品占比、打印占比、电气化产品占比、生活用品占比、文件夹占比、文具占比；
• 处理时间率：上午处理率、加班处理率、周一处理率、周二处理率、周三处理率、周四处理率、周五处理率；
• 入职测试：数量处理、语言处理、好奇心、勤奋程度、外向性、协调性；
• 压力测试：加班时间、身体负荷、人际关系。

按照解释变量的重要性，可以用前进法（变量增加）和“后退法”（变量减少），以p值为判断基准，追加或删除解释变量。

但是，这里并不清楚哪些解释变量比较重要。所以用最基本的办法从左到右依次选择解释变量。处理步骤如下：

1. 排除明显不重要的商品类型“其他”；

2. 以“加班n”的形式新建工作日的加班处理时间数据列，如“加班1”表示周一是否加班。用IF和AND函数统计周一到周五是否都加班了。以周一为例，如图12所示，用筛选功能查看“加班1”这一列数据是否存在1，如果有，则说明周一加班处理订单了。用类似的方法，经筛选得知，在3月份这个销售旺季，工作日每天都加班了。
   图12.png
3. 考虑到周一是工作日的起始日，而周五最后一日，再看周二、周三、周四也都存在加班现象，似乎没有特殊的地方，这里选择保留周三处理率，而排除周二和周四处理率。
4. 最后选择用来进行回归分析的对象有9个商品类型的解释变量、除去周二和周四的5个时间处理解释变量，一共14个解释变量。

图13.png

图15.png

从图15可知，包装用品占比的p值最高。按照上述步骤“进行多元回归分析-删除p值最高的解释变量-进行多元回归分析”，结果分别如图16、图17、图18、图19、图20、图21所示，依次得到p值占比最高的是包装用品占比、周五处理率、上午处理率、周三处理率、生活用品占比、文件夹占比、办公家具占比，删除办公家具再次进行回归分析，结果如图22所示，各项p值都小于0.05，说明余下的解释变量都对销售额影响较大。

图16.png

图17.png

图18.png

图19.png

图20.png

图21.png

图22.png

图23.png

图24.png

图25.png

多元回归分析的最终结果如图25所示，剩下的解释变量有如下11个：

• 商品类型占比：PC 其他用品、PC 主机、打印、电气化产品。
• 处理时间：加班、周一。
• 入职测试：数量处理、勤奋程度、外向性。
• 压力测试：加班时间、身体负荷。

4.案例结论

图26.png

就p值而言，都小于0.05，说明这些数据项都能对销售额产生比较大的影响。

回归系数表示“解释变量每增加1，输出结果相应地平均增加多少”，比如，PC 其他用品占比增加了1，则销售额平均增加“9925476”。

观察回归系数的正负，推断员工的销售额高低。先看商品类型，经常销售PC其他用品、PC 主机、打印用品、电气化产品的员工的销售额比较高。在看订单处理时间，在加班时间处理订单的员工销售额较低，而在周一处理订单的则销售额较高。然后看入职测试项，有关数量处理和勤奋程度的测试成绩越高的员工，越容易提高销售额，但越是外向的员工反而销售额越低。最后看压力测试项，对加班时间或身体负荷有极大大压力的员工，其销售额情况不佳。

综上，最终分析结果如下：

1. PC主机、PC其他用品、打印用品、电气化产品占比高的员工，销售额高。
2. 数量处理和勤奋程度的测试结果高的员工，销售额高。
3. 性格很外向的员工，销售额低。
4. 对加班时间和身体负荷有极大压力的员工，销售额低。
5. 周一处理订单的员工，销售额高。

为了提高销售额，有哪些建议值得一试？

1. 考虑到PC、电气化产品等电子仪器相关的商品占比越高，员工的销售额越高的分析结果，加上数量处理的得分越高，销售额越高，这些要素与数理背景相关，因此可以考虑重点招聘具有理工科背景的员工。
2. 越加班，销售额越低，可以考虑尝试一段时间禁止加班，观察销售额有无变化，再相应地调整与加班相关的费用支出。
3. 对员工进行为什么在周一处理订单或为什么不在周一处理订单的抽样调查，找出在周一处理订单的好坏。
4. 虽然数据结论得出性格越外向，销售额越低，但事实上人们普遍认为外向是销售员的一个重要特点，所以还需进行进一步调查分析。

案例报告所需的图表制作参阅用Power View生成报告图表。