Excel 如何对正向和逆向指标进行加权评分？

可以先将每个指标转换成 0～1 的分值，乘以对应权重后求和。七项权重合计为 100，因此只要每项分值都在 0～1 之间，总分就会落在 0～100 分之间。

现有公式的方向基本正确，需要处理两处问题：参考列最大值为 0 时会出现除零，H 列数值超出 500～850 时单项分值会越界。还需要明确评分标准：是相对于当前数据评分，还是相对于固定业务目标评分。

现有公式实际采用的评分规则

D、G、I、K、L、N 使用当前数据的最大值作为参考，H 使用固定区间。这两种标准可以组合使用，但含义不同，是否合适取决于业务规则。

保留现有规则时，如何修改公式

下面的公式适用于以下前提：

• 参与计算的各项指标都是有效数字，D、G、I、K、L、N 均为非负数。
• H 列以 500 为零分点、850 为满分点，超出区间时按边界计分。
• D、K、L 整列为 0 时，该项记零分。
• G、I、N 整列为 0 时，该项记满分，前提是业务上认可 0 为最佳值。

在结果列第 4 行输入以下公式，再向下填充：

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

MAX(0,MIN(1,...)) 将每个指标的分值限制在 0～1 之间。H 列低于 500 时贡献 0 分，高于 850 时最多贡献 15 分，不会挤占其他指标的权重。

原公式只限制最终总分。例如，H4 为 900 时，原来的 H 项会贡献约 17.14 分，超过其 15 分权重。即使总分没有超过 100，单项分值仍然越界。逐项限制可以解决这个问题。

整列为 0 时如何计分，需要由业务规则决定。上面只给出了一套处理方式；如果 0 表示缺失或无效数据，应先处理数据，不能直接套用这些分支。

为什么逆向指标的最小值不一定得满分

原来的逆向公式是：

=1-G4/MAX($G$4:$G$100)

它把 0 作为满分点，把本列最大值作为零分点。假设 G 列最小值为 10%、最大值为 20%，那么最小值对应的分值为：

1 - 10% / 20% = 0.5

乘以 15 分权重后得到 7.5 分，这是原公式定义下的正常结果。

如果要求“当前数据中最好的一项得满分，最差的一项得零分”，可以改用 Min-Max 归一化。

对于越高越好的指标，例如 D 列：

=(D4-MIN($D$4:$D$100))/(MAX($D$4:$D$100)-MIN($D$4:$D$100))

对于越低越好的指标，例如 G 列：

=(MAX($G$4:$G$100)-G4)/(MAX($G$4:$G$100)-MIN($G$4:$G$100))

计算出分值后，再乘以对应权重。如果本列所有值相同，分母会变成 0，需要另行规定统一给多少分。例如，约定全部相同时给 0.5 分，G 列可写为：

=IF(MAX($G$4:$G$100)=MIN($G$4:$G$100),0.5,
(MAX($G$4:$G$100)-G4)/(MAX($G$4:$G$100)-MIN($G$4:$G$100)))

这里的 0.5 是示例规则，具体分值需要自行确定。如果 H 列的 500 和 850 是业务确定的阈值，可以继续使用固定区间评分。

使用时需要检查哪些数据

百分比应存储为 Excel 数值，例如 15% 对应的内部数值是 0.15。避免将文本或不同口径的数字混在同一列。还需要先明确空白的含义：空白单元格参与算术时可能按 0 处理，尤其对于逆向指标，这可能导致缺失数据得到满分。

$D$4:$D$100 这样的绝对引用在向下填充时保持不变，D4 则随行号变化。数据超过第 100 行后，需要同步扩大所有参考范围。

使用样本最大值或 Min-Max 归一化时，新增或修改数据可能改变其他行的得分。如果需要跨批次比较，通常应先确定固定的业务评分阈值。最后统一使用 ROUND(...,0) 取整，避免每项提前取整带来累计偏差。

备注：内容仅供参考。