如何在Excel中构建一个加权评分模型，使其能从多列计算出最终分数，其中有些指标越高越好，有些指标越低越好？

Starting in Row 4

D - $ amount - higher is better D - 金额（$） - 越高越好

G - % - lower is better G - 百分比 - 越低越好

H - number - higher is better H - 数字 - 越高越好

I - % - lower is better I - 百分比 - 越低越好

K - % - higher is better K - 百分比 - 越高越好

L - % - higher is better L - 百分比 - 越高越好

N - % - lower is better N - 百分比 - 越低越好

Current Formula:

=MAX(0, MIN(100,
    ROUND(
        (D4 / MAX($D$4:$D$100)) * 20 +
        (1 - G4 / MAX($G$4:$G$100)) * 15 +
        ((H4 - 500) / (850 - 500)) * 15 +
        (1 - I4 / MAX($I$4:$I$100)) * 10 +
        (K4 / MAX($K$4:$K$100)) * 15 +
        (L4 / MAX($L$4:$L$100)) * 15 +
        (1 - N4 / MAX($N$4:$N$100)) * 10,
    0)
))

解决方案

你可以尝试如下方法。它会给你一个满分为100的加权分数，其中D、H、K、L的数值越高越好，而G、I、N的数值越低越好。

=ROUND(IFERROR((D4-MIN($D$4:$D$100))/(MAX($D$4:$D$100)-MIN($D$4:$D$100))*20,0)+IFERROR((MAX($G$4:$G$100)-G4)/(MAX($G$4:$G$100)-MIN($G$4:$G$100))*15,0)+IFERROR((H4-MIN($H$4:$H$100))/(MAX($H$4:$H$100)-MIN($H$4:$H$100))*15,0)+IFERROR((MAX($I$4:$I$100)-I4)/(MAX($I$4:$I$100)-MIN($I$4:$I$100))*10,0)+IFERROR((K4-MIN($K$4:$K$100))/(MAX($K$4:$K$100)-MIN($K$4:$K$100))*15,0)+IFERROR((L4-MIN($L$4:$L$100))/(MAX($L$4:$L$100)-MIN($L$4:$L$100))*15,0)+IFERROR((MAX($N$4:$N$100)-N4)/(MAX($N$4:$N$100)-MIN($N$4:$N$100))*10,0),0)

你可以根据该因素的重要性调整每一列的乘数。例如，D的乘数为20，G的乘数为15，因此D 对最终分数的影响比G 更大。如果希望所有列的权重相等，可以将每个乘数设为大约14.3（14.3 × 7 ≈ 100.1）。

备选方案

我认为你应该开发一个工具来完成这些计算。我已经根据你的需求改造，创建了一个可自定义的计算器，这样你就可以通过Higher或 Lower等规则，以及自定义系数来进行试验：

让我来解释它是如何工作的。

RULE行只是文本值，用来显示该列是 越高越好（H）还是 越低越好（L）。

COEFS行只是一个比值，用来显示该变量在你计算中的重要性。确保它们的和为100% 否则就无效！

REFERENCE VALUE用来显示在上述规则（H或 L）下该列中最重要的数值。如果规则是H，参考值是该列的最大值，否则是该列的最小值的一次变换（1减去最小值）。

在这个示例中，我对每一列的数值进行了手动强制并排序。请注意，所有规则为H 的列都是降序排序的数值，而规则为L 的列则是升序排序的数值。你可以看到被高亮显示为黄色的数值。这些数值是在该列中最佳的选项（遵循RULE H或 L）。

RESULT列就是你期望的输出。对于每一行，它用每一列的数值（若规则为L 则用1 减去该数值）除以各自的REFERENCE VALUE（以计算比值），然后再乘以相关的系数。

所以对于黄色行（手动强制为最佳数值）RESULT为 1，因为该行是 最好的中的最好（通常你不会看到这种情况）。

现在我使用的公式：

RULE和 COEFS是用于自定义行为的手动输入。相信我，值得这么做。

REFERENCE VALUE: 单元格D6的公式是 =IF(D2="H";MAX(D9:D23);1-MIN(D9:D23)).，向右拖拽

RESULT: 单元格K9的公式是 =SUMPRODUCT(SI($D$2:$J$2="H";D9:J9;1-D9:J9)/$D$6:$J$6*$D$4:$J$4)。向下拖动。

我用随机数值进行了测试，效果非常棒。你甚至可以根据需要改变规则为H 或L，并根据需要自定义系数。

我已经将一个示例上传到 Google Drive 以防你想看看。