【干货】MaxDiff系列（六）--通过Excel Solver来了解条件逻辑模型-经管之家官网！

接下来我们需要将我们计算的概率和实际的选择进行对比，来衡量我们效用值的准确度。

一个最简单的对比原则是，如果根据某一组效用值计算出的“最喜欢”和“最不喜欢”的概率越接近实际选择，那么这组效用值越准确，因为它越能还原消费者的实际选择行为。这种“完美预测/拟合”如果反映在数据上，就应该是当这组效用值被代入上面的公式后，被选中的对象的概率接近于1。即下图的效果：

来看看我们刚才的猜测结果

我们发现这种猜测（对象8、4、5、3的效用值u分别为0、2、4、8）不够好，仍然有改善的空间。

当然，仅仅使得一个任务拟合得完美还不够，我们需要每个任务都拟合地很好。这时我们需要计算每个任务里被选为“最喜欢”和“最不喜欢”的对象的似然度，并且将其求和（或取几何平均），并且通过优化算法找到能使其最大的一组效用值。

回到我们的例子中，由于采用了dummy coding我们需要将对象的最后一列移除（或去除这8列中的任何一列），作为参照水平。然后将初始系数均设置为0（也可以随机设置成别的数值），如下图：

接下来我们用excel中的sumproduct函数，逐行将对象乘以系数，便可以得到一列每个概念的效用值（J列）。

随后我们用Excel中的exp函数将每个效用值转化为偏好分数（K列）。最后由偏好分数计算其被选择概率（L列），如下图：

需要注意的是，在运用公式计算每个任务中对象被选择概率的时候，其分母是相对应的每个任务的偏好分数之和。

由于已经将效用值的初始值都设置为0，此时每个任务中的对象被选择的概率都是1/4，这时可以视为受访者对任何对象都没有相对的偏好，于是每个产品被选中的概率就是均等的。

M列是受访者在每个任务中所选择“最喜欢”和“最不喜欢”的对象的概率拟合值（似然度），我们希望在估算后，这一列的每个数值都能接近于1。M3中的RLH是几何平均似然度（Root Likelihood），RLH估算的是给定效用值下这位受访者实际选择数据拟合的平均准确率。

在N列中我们将M列的数都取了自然对数，其总和就是N3中的自然对数似然度（LogLikelihood）；该参数往往是模型优化的目标，也就是该似然度越高则模型估算的越准确。从数学角度上说，无论取RLH还是Log likelihood作为优化的目标函数都是可以的，但是从优化算法角度看，对数似然度会对迭代过程更加敏感，能够更快地找到效用值的最优解。

以下是J:N列所对应的Excel函数。

接下来我们开始使用Solver（规划求解插件）进行求解。需要注意的是，这里我们对效用值系数的估算进行了某种约束，即限定估算出来的效用值系数最大不能超过100，全距（最大效用值减去最小效用值）也不能超过100。这一方面是为了避免Excel中Exp函数溢出导致的无法运算的情形，另一方面也是为了避免模型的过度拟合。因此，在Excel中做如下设置：在C3中计算最大系数，在F3中计算全距，并且在Solver中对这两个单元格都做出约束限定（小于等于100）

由于模型拟合的目标是让自然对数似然度最大化，所以N3是目标单元格，需要估算的是B2到H2中的系数。然后点击Solve（求解）。我们得到以下结果：

可以看到这个受访者最喜欢3号对象，其次是2号和7号对象；最不喜欢的是6号对象。

如果我们补上被视为参照水平的第8个对象的效用值0，那么ID=1的受访者的效用值就是

我们还发现估算的效用值已经能让模型在大多数任务上的预测准确，平均来说，我们的预测准确率接近80%（RLH=0.79）。

如果我们有很多个受访者，比如有500个受访者。而且每个人所对应的试验设计都足够好，每个对象的出现次数均衡。那么我们可以按照此方法计算每个受访者在每个对象上的效用值。进一步的，我们还可以计算这500个人在每个对象上的效用值的平均值（当然，这涉及到一些尺度调整工作），从而得到样本层面上每个对象的偏好效用值。

我们之前提到过专业统计软件R中的mlogit拓展包，我们可以将同样的数据用R来估算一下并和Excel的结果进行比对。相应的R代码如下图所示，对R有经验的读者可以不妨动手试一下：

其中的原始数据choicedata.csv（部分）如下所示：

如果我们将mlogit计算得到的效用值粘贴回我们的Excel表格B2:H2，如下图：

可以看到，尽管Excel的solver插件与mlogit计算的效用值不尽相同，但是两种方法都能使得模型的拟合优度RLH达到0.79，自然对数似然度都等于-2.77，效果一样好。

我们计算下两种软件估算的效用值之间的相关系数，见下图

在这里Excel与mlogit计算得到的效用值差异不大，接近于一个线性变换。

当然，如果Maxdiff对应的对象更多，数据量更大时，我们还是建议使用专业的统计软件来建立模型并进行效用值的估算，其计算效率和准确性要优于Excel。

小结

本文通过一步步的Excel的操作，让大家看到针对Maxdiff数据的条件逻辑模型该如何建立，编码及求解。实际上通过本文的介绍，大家可以看到Maxdiff的逻辑模型并不复杂，通过一般的Excel都可以进行一定的运算求解。

另外，Maxdiff可以视为一种仅含一个因子（或属性）的联合分析，本文所介绍的编码（coding）和估算过程稍加推广，就可以运用到一般的离散选择模型（或基于选择的联合分析CBC）的条件逻辑模型求解。我们也会在今后的系列中继续介绍如何使用Excel对一般的离散选择模型进行分析。请大家继续关注。

「经管之家」APP：经管人学习、答疑、交友，就上经管之家！
免流量费下载资料----在经管之家app可以下载论坛上的所有资源，并且不额外收取下载高峰期的论坛币。
涵盖所有经管领域的优秀内容----覆盖经济、管理、金融投资、计量统计、数据分析、国贸、财会等专业的学习宝库，各类资料应有尽有。
来自五湖四海的经管达人----已经有上千万的经管人来到这里，你可以找到任何学科方向、有共同话题的朋友。
经管之家（原人大经济论坛），跨越高校的围墙，带你走进经管知识的新世界。
扫描下方二维码下载并注册APP