如何在Excel/Google Sheets中反推加权平均的权重

这个场景本质是固定系数的线性拟合问题，Excel和Google Sheets都有成熟的内置功能可以实现，不需要写复杂代码，两种最稳定的方案如下： #

方案一：线性回归法（最快，零插件，优先使用） #

首先明确加权平均的公式逻辑：当所有权重和为1（这是加权平均权重的通用规则），最终得分完全满足 最终得分 = B列值*a + C列值*b + D列值*c + E列值*d，没有额外常数项，属于标准的无截距线性回归问题，用内置函数10秒就能出结果：

• 操作步骤：
  1. 找4个连续的空白单元格，用来存放最终要计算的4个权重
  2. 选中这4个单元格，输入数组公式：

  =LINEST(所有实际得分的单元格范围, B到E列对应的数据范围, FALSE)
  

  3. 旧版Excel/Sheets按Ctrl+Shift+Enter确认数组公式，新版Google Sheets直接回车就能出结果
     注意：公式输出的4个值是从右到左对应B到E列的权重，也就是最右边的返回值是B列的权重a，依次往左是C列权重b、D列权重c、E列权重d，直接记录数值即可。
• 验证方法：随便抽一行数据，把4个权重代入AVERAGE.WEIGHTED(B2,a,C2,b,D2,c,E2,d)计算，结果会和原有得分完全一致，几乎没有误差。

方案二：Solver规划求解法（适配特殊权重规则） #

如果场景里权重不需要满足和为1（允许权重和为任意正数，AVERAGE.WEIGHTED函数会自动做归一化），就用Solver工具求解，配置逻辑也很简单：

1. 先搭建计算模板：

• 单独留4个单元格作为可变参数格，存放a、b、c、d四个权重，初始值先都填0.25（四等权重，方便求解收敛）
• 新增一列「预测得分」，第一行输入公式AVERAGE.WEIGHTED(B2,$a所在单元格,C2,$b所在单元格,D2,$c所在单元格,E2,$d所在单元格)，下拉把所有行的预测分都算出来
• 新增一列「误差平方」，第一行输入=(预测得分单元格-该行实际得分单元格)^2，同样下拉填充
• 找个空白单元格计算「总误差」，输入公式对所有行的误差平方求和

2. 打开Solver工具：

• Excel直接在顶部「数据」选项卡找「规划求解」，如果没找到就去选项-加载项里启用「规划求解加载项」
• Google Sheets在顶部「扩展程序」菜单里，搜索Solver安装后即可打开

3. 参数按以下规则配置即可：

• 目标单元格选择计算的「总误差」单元格，选择「最小值」作为求解目标
• 可变单元格选择存放a、b、c、d的4个格子
• 约束按需添加：常规权重都是非负的，就加一条规则让4个可变单元格>=0；如果要求权重和必须为1，就加一条SUM(4个权重单元格)=1
• 求解方法选择非线性求解，点「求解」等几秒就能出结果，总误差会收敛到接近0，对应的权重就是要找的固定值。

几个避坑提醒 #

• 如果用线性回归算出来的结果误差很大，先检查原始数据有没有录错的空值、异常值，脏数据会直接导致拟合结果偏差
• Solver如果跑出来不收敛，就把初始权重都改成0.25再重新跑，不要把初始值设成0或者留空
• 如果算出来个别权重是负数，要么是数据有错误，要么是对应变量是扣分项，根据实际业务场景判断要不要加非负约束即可。

内容的提问来源于stack exchange，提问作者MMsmithH