excel - 在 Excel 中计算多数值

Question

我正在使用 Majority Judgment 投票系统，需要计算 Excel 中所谓的“多数值”，以便我可以对候选人进行排名。

对于那些不熟悉多数判决的人，我将尽力简洁地解释。在下面的示例中，假设有一个系统，选民为每个候选人分配四个分数之一:0、1、2 或 3，其中 0 表示最差，3 表示最好。我将首先解释“多数级”，这是计算多数值所必需的。

多数等级:这本质上是特定候选人的所有选民分数的“下限”中位数。如果选民人数为奇数，则只是中位数，可以计算出 MEDIAN(C27:F27) 。如果选民数量为偶数，则为两个中间值中较低的一个。因此，如果有四名选民且一名候选人的分数为 {0, 1, 2, 3}，则多数分数将为 1(而不是 1.5)。这可以计算为 FLOOR(MEDIAN(C27:F27), 1) 。但是，如果候选人的分数为 {0, 0, 2, 2}，则多数分数为 0(而不是 1)，在本例中 FLOOR(MEDIAN(C27:F27), 1)会失败的。因此，为了计算多数等级，我认为以下数组公式适用于所有情况:MAX(IF(C27:F27<=MEDIAN(C27:F27), C27:F27)) .

正如您可能想象的那样，对于多数评分，通常可能存在平局，许多候选人具有相同的多数评分。通过计算“多数值”来打破平局。

多数值:多数值是为候选人计算的多数分数序列，如果您在每一步中放弃一个与其之前的多数分数相匹配的分数。例如，假设五名选民给候选人打分:{0, 1, 1, 2, 3}

• 第一多数成绩:{0, 1, 1, 2, 3} = 1
• 第二多数等级:{0, 1, 2, 3} = 1
• 第三多数等级:{0, 2, 3} = 2
• 第四多数成绩:{0, 3} = 0
• 第五多数等级:{3} = 3

多数值为 1.1203，并且可以排序以生成候选者的有序列表。

为了简化事情，我会知道有多少选民，并且可以为每个多数等级设置一个列，然后将它们连接在一起。

我认为我需要的是弄清楚如何计算一个数组作为函数的输入，该函数是另一个数组减去与计算值匹配的单个元素。

可以使用公式而不是 VBA 来完成此操作吗？

Answer 1

我能够为这个问题制定一个非 VBA 解决方案。操作方法如下:

首先，对分数进行排序。然后，我们可以使用公式根据模式构建多数值。

例如，对于您的分数列表:{ 0, 1, 1, 2, 3 }由于此列表已排序，因此可以通过根据元素在数组中的位置连接元素来找到多数值(这就是排序真正帮助我们的地方)。在本例中，模式如下所示(数字表示数组中的位置):3, 2, 4, 1, 5

元素 3:1

元素 2:1

元素 4:2

元素 1:0

元素 5:3

看到规律了吗？这就像在线进行智商测试之一。从第三个元素开始，出现了一种模式:我们对出现在两个元素之前的元素减 1 或加 1。当元素总数为奇数或偶数时，此模式会反转。

为了确定前两个元素，我们必须进行一些预先计算。第一个元素就在中间。如果分数为偶数，则为中间减一。第二个元素是第一个元素的 +1 或 -1，具体取决于分数的数量是奇数还是偶数。然后从第三个元素开始我们的模式开始。我们查看后面的两个元素，它要么是+1，要么是-1，这取决于数据集是奇数还是偶数。

假设 B4:B13 列是您的分数。我们首先确定集合中的元素数量是奇数还是偶数:

单元格 D2:=IF(MOD(COUNT($B$4:$B$13),2)=0,1,-1)

然后我们开始构建索引:

单元格 C4:=CEILING(COUNT($B$4:$B$13)/2,1)

这给了我们中间元素索引。这将是多数值中的第一个数字。

单元格 C5:=C4+$D$2

根据值的数量是否为奇偶添加到 C4。

单元格 C6:=C4-$D$2

与 C5 的做法相反。这为我们提供了三个完整的索引来开始我们的模式。

C7:=C5+$D$2

C8:=C6-$D$2

现在我们在整个数据集中继续这种模式。

然后我们可以获取生成的索引并将它们插入到 INDEX 函数中。

D4 如下所示:=INDEX($B$4:$B$13,C4,1)

将该公式向下拖动。这按照我们需要的多数值的顺序为我们提供了分数本身。

最后，我们将所有这些连接在一起并转换为另一个单元格中的值以获得多数值:

D14:=VALUE(D4&"."&D5&D6&D7&D8&D9&D10&D11&D12&D13)

我已经为您共享了一个文件夹，其中包含我用来解决此问题的 Excel 工作表。 https://drive.google.com/folderview?id=0BwOgSMhqS_zHb3pwRUVsVEZSNlE&usp=sharing

我们可以更进一步，将 C 列和 D 列组合成一个公式。为了清楚起见，我将它们分开。

<小时/>

更新:

自从您指定您知道选民数量以来，我已经提供了您所谈论的 5 票制的工作示例。我已经更新了上面链接的驱动器中的 Excel 工作表。

解决方案:

多数值公式最终为=VALUE(INDEX(G$4:G$8,$P$4,1)&"."&INDEX(G$4:G$8,$P$5,1)&INDEX(G$4:G$8,$P$6, 1))&INDEX(G$4:G$8,$P$7,1)&INDEX(G$4:G$8,$P$8,1)

其中 G4:G8 是候选人的一组已排序分数。

P4 是 =CEILING(COUNT(G$4:G$8)/2,1)

P5 是 =P4+$P$2

P6 是 =P4-$P$2

P7 是 =P5+$P$2

P8 是 =P6-$P$2

P2 为 =IF(MOD(COUNT($G$4:$G$8),2)=0,1,-1)

希望这能让事情变得更清楚。

干杯，

Z