这里用交叉表和回归分析方法来分析如何增加营业额。

1 顾客的性别和婚姻情况会对营业额有影响吗？

1.1 生成性别和总消费额的交叉表

做交叉表分析的目的是什么？

• 分析性别的差异会对“过去三个月夜间时段的总消费额”产生多少影响。这样，生成性别和总消费额的交叉表后，总消费额就是男女性各自消费额的总和。

但是，直接计算总和合适吗？

• 考虑到男女性顾客的比例具有差异性，这里将总消费额计算为平均值。

交叉表分析

• 按照属性和类别汇总数据。
• 在Excel中，用数据透视表功能生成交叉表。

生成性别和总消费额的交叉表来判断性别的差异是否会对总消费额造成差异。步骤如下。

1. 除去第1列的调查ID，将其他数据全部选定，在新的工作表中生成数据透视表。
2. 在数据透视表字段中，分别将性别、总消费额字段拖至行、∑值区域，并将总消费额的汇总方式改为平均值。

3. 计算男女性顾客总消费额的差值，完成性别交叉表。最终结果如图3所示。
   图1.png

第3步计算差值的公式为=GETPIVOTDATA("总消费额",A3,"性别","女")-GETPIVOTDATA("总消费额",A3,"性别","男")。

GETPIVOTDATA 函数

作用：从数据透视表中返回可见数据。
语法：GETPIVOTDATA(data_field, pivot_table, [field1, item1, field2, item2], ...)
参数：

1.2 性别差异造成的总消费额的差是偶然的吗？

根据图3所示的性别交叉表，男女顾客的平均总消费额相差1061日元。这个差额是由于数据的不规则分布而偶然出现的吗？用p值来判断。

p值

以掷一次硬币为例，可提出如下假设：

p值是当原假设为真时，所得到的样本观察结果或更极端结果出现的概率。如果p值很小，说明原假设情况发生的概率很小，拒绝原假设的理由越充分。

p值如何判断原假设是否成立？

p值与置信水平比较：

• p<α，则拒绝；
• p>α，则接受。

一般而言，p值是“因数据的不规则分布偶然出现这样（或高于这样）的差的概率”。如果p值小于5%时，就可以判定样本观察结果具有显著性差异。

显著性，是指零假设为真的情况下拒绝零假设要承担的风险水平（概率水平）。

参阅：

• 浅谈p值(p-value是什么)
• 显著性
• p值

在计算各类别的平均值时，用t检验方法求p值。在Excel中，用ttest函数求t检验的p值。

TTEST函数
作用： 确定两个样本是否可能来自两个具有相同平均值的基础总体。
语法：TTEST(array1,array2,tails,type)
参数：

T.TEST 函数

使用t检验方法确认性别交叉表中的汇总结果是否正确。步骤如下。

1. 选择调查结果表，按照性别分类，升序重排数据。结果总消费额会按照升序的方式进行排列。

2. 在性别交叉表中，追加p值列，用ttest函数计算p值。将性别为男的总消费额单元格区域作为ttest函数的array1，将性别为女的总消费额单元格区域作为ttest函数的array2，将分布曲线的尾数指定为双尾分布，选择双样本异方差假设。最终计算p值的公式为“=TTEST(调查结果!I2:I470,调查结果!I471:I1001,2,3)”。计算结果如图4所示。
   图2.png

   根据图4，p值约为0.00062，这说明男女之间在总销额上出现的差额，是由于数据的不规则分布而偶然导致的概率小于0.001。这属于显著性差异。

3. 利用汇总数据生成如图5所示的数据透视图。
   图3.png

用TTEST函数计算p值时，为什么选择双尾分布和异方差？

指示分布曲线的尾数有单尾分布和双尾分布，常用双尾分布。
t检验类型有成对、双样本等方差假设和双样本异方差假设。

• 成对，用于分析固定一人的数据变化。
• 双样本等方差假设，用于含有同样的数据波动情况。
• 其他无法确定的情况下，用双样本异方差假设。

注意：只要各类别之间多少存在平均值上的差异，随着分析单位数量的增加，p 值肯定会越来越小。

2.考察婚姻情况对总消费额的影响

婚姻情况的解释变量有未婚、已婚（已育）和已婚（未育），如何根据婚姻情况类别计算总消费额的差值？

• 方法一：当定性的解释变量存在三组以上时，任选其中一组作为基准，记录该组数据与其他组数据的差额。这里，如果以“未婚”为基准，用“-”表示，先计算其与“已婚（已育）”和“已婚（未育）”的差额，然后用t检验方法分别计算差额的p值。
• 方法二：将三组以上的元数据重新分类成两组数据，这里可以将已婚（已育）和已婚（未育）合并为已婚，然后只考虑是否已婚。

采用方法一来分析婚姻情况对总消费金额的影响，步骤如下。

1. 生成婚姻情况的交叉表。将数据全部选定，插入数据透视表。行标签指定为婚姻情况，∑值选择总消费额，并将其值汇总方式设为平均值。以未婚为基准计算平均消费额的差额。

2. 用TTEST函数分别计算婚姻情况之间差额的p值。最终结果如图4所示。
   图4.png

   从图4可知，“未婚”与“已婚（已育）”的p值为0.028，“未婚”与“已婚（未育）”是0.460。p值明显大大超过0.05，这表明“未婚”顾客与“已婚（未育）”顾客之间的总消费额出现差额是偶然的。而“未婚”顾客与“已婚（已育）”顾客之间的总消费金额存在729.8日元的差额，因数据偶然的不规则分布而产生这种差额的可能性为2.8%，p值小于0.05，表明“未婚”顾客与“已婚（已育）”顾客之间的总消费金额出现差额并非偶然。

3. 光顾次数与消费金额之间存在什么关系？

3.1 用卡方检验分析定性的输出结果

用到每一类别所占比例的汇总和卡方检验方法来考察定性的解释变量和定性的输出结果之间一对一的关联性。

这里的分析目的是增加工作日夜间的总消费额，如果将其设为增加工作日夜间时段的客流量，那么在这一时间段光顾过的顾客与未光顾过的顾客有什么差异？这需要对“光顾/未光顾过的顾客”与解释变量之间的关联进行分析。

但是，“光顾/未光顾过的顾客”数据项并不包含在调查结果中，需要自定义出来，步骤如下。

1. 判定光顾次数列或消费金额列是否为0，确定顾客的光顾记录。
2. 追加“光顾记录”列，输入=IF（F2>0，" 有"，"无"），计算每位顾客的光顾记录,最终结果如图5所示。
   图5.png

接下来，对性别的每一类别所占比例进行汇总，分析哪个性别的顾客会更多地光顾。这里用光顾率来衡量。光顾率=光顾记录为是的顾客数/总顾客数。

1. 对性别的每一类别所占比例进行汇总，如图6所示。
   图6.png

2. 生成如图7所示的期待值表，用卡方检验性别造成的光顾率的差异是否偶然。
   “期待值表”，是计算在假设性别对光顾率几乎没有影响的情况下，男女应该各有多少人“光顾过”或“未光顾”的数据表。根据图8，参与问卷调查的1000人中有319人光顾过，光顾率为31.9%。若“男女间的光顾率不存在差异”，就是说不论男女应该各有31.9% 的比例光顾过。若是男性，469人的31.9%约149 人，女性的话则是531人的31.9% 约169人为期待值。

   
   
   图7.png
3. 用CHITEST函数计算卡方检验的p值。CHITEST函数通过指定实际的交叉表范围和与之相对应的期待值范围，计算出卡方检验的p值。这里的计算公式为=CHITEST（B5:C6，G5:H6），计算得到的数值非常小，在最终形成报告时将其改写为“＜ 0.001”。
   
   图8.png

3.2 用散点图把握光顾次数与总消费金额的关系

用散点图和回归分析考察“定量解释变量”和“定性输出结果”之间的关联性。

光顾次数等定量解释变量越大，总消费额是不是也会增加或减少?
用Excel的数据透视表功能，将行标签设为“光顾次数”，Σ值设为“总消费金额”，计算出每次光顾时的总消费金额。然后，将“光顾次数”和“总消费金额”生成散点图。

1. 选定“光顾次数”和“总消费金额”的数据，插入“散点图”。

2. 生成图9所示的散点图。横轴上是光顾次数，纵轴是总消费金额。从散点图可知，随着光顾次数的增加，总消费金额也有增加的趋势。

   
   
   图9.png

3. 添加散点图的趋势线，并将趋势线的公式显示在图表上，结果如图10所示。

   
   
   图10.png

   散点图上添加了回归直线y=1277.8x+191.65，意思是x 轴的光顾次数每增加1次，总消费金额就平均增加1277.8 日元。

回归分析

形式：一次函数（y=ax+b ）
作用：

• 分析x轴表示的解释变量每增加1，y 轴的输出结果会随之产生多少变化（回归系数）。
• 分析解释变量为0时，输出结果为何值（截距）。

3.3 用回归分析方法验证散点图所显示的倾向是否为偶然现象

散点图中的点呈现逐渐上升的趋势，但是，散点图上点的分布不规则。这些点是偶然呈上升趋势排列的吗？用p值验证。计算回归分析的p值，判断直线的倾斜度（回归系数）“是否是因偶然的不规则分布造成的”。操作步骤如下：

1. 在“数据分析”中选择“回归”。
2. 设定回归分析的参数。包括项目名在内，“Y 值输入区域”处选定输出结果总消费金额列，“X 值输入区域”处选定解释变量光顾次数列。勾选“标志”前的方框，点击确认。

3. 生成如图11所示的回归分析的输出结果。

   
   
   图11.png

   结果中最先需要确认的，是Coeffi cients的值。Intercept、光顾次数的Coeffi cients应该跟图10中公式数值一致。P-value是各种系数“因数据的不规则分布偶尔出现此种程度的概率”。截距的p值可以忽略。

从图11可知，回归系数“光顾次数每增加1 次，总消费金额会增加1277.8 日元”因数据的不规则分布偶然出现的概率为1.3E-207（1.3×10-207），可理解为“几乎为零”。

另外，p值右侧两列显示了95%的信赖区间的上下限。也就是说，如果考虑误差的存在，光顾次数每增加1次，总消费额增加1214 ～ 1340日元也基本没错。

综上，利用Excel进行回归分析，首先需要画出散点图得出数据趋势，然后用数据分析工具计算p值和信赖区间来判断该趋向是否可信。

回归分析陷阱

1. 分析的结果有意义吗？像“光顾次数多的顾客总消费金额高”的结论是没有意义的。

2. 回归系数接近0，p值又很大，其解释变量也并不一定跟输出结果毫无关系。 下图所显示的状态是“回归系数几乎为零，p 值很大，却有明显关联性”的例子。

回归分析意义

回归分析方法只能用来考察“解释变量越大，输出结果越大或越小”这种关联性。因此，用这种方法就无法发现上图所体现出来的关联性：解释变量处于中等位置时输出结果很小。此外，不管解释变量是大还是小，输出结果都会变大。所以，先生成散点图，大致了解一下其关联性，再做后续分析。

生成散点图，确定了上图所示关联性，接下来该怎么办？

常用的处理方法是选择适当的值将定量解释变量分成三组，当作定性数据进行分析。比如说年龄，根据实际数据确定分界点，将其分为“20 岁以下”“21 ～ 39 岁”“40 岁以上”三组。在数据表中添加一列名为“年龄分类”的解释变量，用IF函数将得出的输出结果体现出来。

IF 函数

• 可以根据条件判别单元格的值，分别锁定条件成立时、条件不成立时的值。
• 也可以在“条件成立时的值”“条件不成立时的值”中输入嵌套IF函数，进行一些复杂条件的判定。

例如，公式“=IF(B2<20,"20 岁以下","X")”表示单元格C2的数值如果不满20就显示“20 岁以下”，否则就显示“X”。如果在“X”处输入公式“IF(B2<40，"20 ～ 39 岁"，"40 岁以上"）”，那么不属于“20 岁以下”且不满40岁的情况下就显示为“20 ～ 39 岁”，否则就显示“40 岁以上”。

之后的分析步骤跟“考察婚姻情况对总消费金额的影响”相同。

4.交叉表分析与多元回归分析

分析一个“解释变量”与一个“输出结果”之间的关联性：

1. 对定性解释变量，用交叉表分析和t检验；
2. 对定量解释变量，用回归分析。
3. 多元回归分析方法借助虚拟变量可以对多个定性解释变量进行分析，这样，定性解释变量的t检验与定量解释变量的回归分析几乎是一样的。

注意：在使用数据分析工具时，t检验与使用虚拟变量的回归分析之间，“体现数组间差额的p值”与“与虚拟变量相关的回归系数的p值”是一样的。但在Excel中，这两个数值不一定完全相同，但基本上可以认为差距不大。

如何理解“几乎一样”？

1. 生成男性虚拟变量列。在输出结果“总消费金额”左侧插入“男性虚拟变量”列。用IF函数，新建表示“性别为男性时1，非男性时0”的单元格,输入=IF（C2=" 男"，1，0）,并将该单元格的内容复制到列表最后一行。

2. 男性虚拟变量与总消费金额的回归分析。将“男性虚拟变量”作为解释变量，总消费金额为输出结果，然后用分析工具做回归分析。结果如图12所示。

   
   
   图12.png

   由图12可知， 截距为1323， 回归系数为1061.5。截距是通过交叉表分析得到的“女性平均消费金额”，回归系数与“男女间平均消费金额之差”一致。

图13.png

只做多元回归分析就够了吗？

如果得到的性别交叉表结果表明，男性与女性的消费金额几乎没有差别，那么大多数人会觉得性别不会对消费额造成影响。

但是，除了性别之外，考虑“是否点过酒类”，在男女之间点过酒的比例极端不同时，如果女性消费金额较高的话，交叉表分析结果也有可能会得出男女间消费金额几乎没有差别的结果。多元回归分析方法能够解决这个问题。

此外，多元回归分析的结果不便于做汇报，需要配合交叉表或散点图进行说明。方法如下。

1. 先对对个解释变量进行多元回归分析，根据p值找出跟“输出结果和有意义的解释变量”。
2. 对选出的解释变量进行交叉表分析或生成散点图,得到的结果可直接用于汇报。

注意，如果多元回归分析的回归系数与交叉表分析或散点图的结果不同，怎么办？用其他p值小的解释变量“分类”进行交叉表分析。

以性别和是否点过酒类为例。假设多元回归分析的结果是“性别的不同有明显差异”。但是，以性别分类进行交叉表分析而得到的结果却是几乎无差异。如果“是否点过酒类”在多元回归分析中也是很小的p值，就按照“点过酒类”和“没点过酒类”的条件进行男女性别的交叉表分析。

如何设定虚拟变量

同性别一样，定性的“两组解释变量”中的一方设为 0，另一方为 1，可以将其转化为定量解释变量，生成的解释变量取“设定为1的数组”的名称，命名为“×× 虚拟变量”。在上述中，将“若为男性即为1”的虚拟变量命名为“男性虚拟变量”。

前面将定性解释变量“婚姻情况”分为三组——“未婚/ 已婚（已育）/已婚（未育）”，进行交叉表分析，计算得“未婚”与“已婚（已育）”“未婚”与“已婚（未育）”的输出结果差，并分别进行了t检验。这里，新建“已婚（已育）虚拟变量”——“已婚（已育）”为1，其他情况为0，以及“已婚（未育）虚拟变量”—“已婚（未育）”为1，其他情况为0。方法如下：

• 新建“已婚（已育） 虚拟变量”/“已婚（未育）虚拟变量”列，在项目名下输入=IF（D2=" 已婚（已育）"，1，0）或=IF（D2=" 已婚（未育）"，1，0），然后将该单元格内容复制至最后一行。

注意：用一个定性解释变量生成两个以上的虚拟变量时，要求同时对多个虚拟变量做多元回归分析，否则t检验与回归分析的结果会不同。

如何设定虚拟变量的基准？
例如，男性虚拟变量是为了明确“以女性为基准，如果是男性的话，输出结果会有何不同”而设定的解释变量。

• 只选一个不设虚拟变量的类别，以此为基准考察输出结果的差异。

用多元回归分析方法一次性对解释变量（无论是定性还是定量）进行分析参阅用回归分析方法分析如何增加营业额。