如何计算Google表格中的加权平均值？

+--------+-------+-------+-------+---------+
|        | Attr1 | Attr2 | Attr3 | Overall |
+--------+-------+-------+-------+---------+
| Prod 1 | 10    | 8     | 9     | 9       |
| Prod 2 | 2     | 10    | 7     | 6.33    |
| Prod 3 | 4     | 6     | 6     | 5.33    |
+--------+-------+-------+-------+---------+

+--------+-------------+-------+--------------+---------+
|        | Attr1 (50%) | Attr2 | Attr3 (300%) | Overall |
+--------+-------------+-------+--------------+---------+
| Prod 1 | 10          | 8     | 9            | 8.89    |
| Prod 2 | 2           | 10    | 7            | 7.11    |
| Prod 3 | 4           | 6     | 6            | 5.78    |
+--------+-------------+-------+--------------+---------+

(10*0.5 + 8*1 + 9*3) / (0.5+1+3) = 8.89

#1 楼

没有内置函数来计算加权平均值，因此，如果要避免使用过多的临时单元格，则必须编写自定义函数。因此，这是实现目标的方法。

转到工具>脚本>脚本编辑器...，复制/粘贴以下代码并保存：

function weightedAverage(v, w) {
  var sum_v = 0;
  var sum_w = 0;

  if (v[0].length != w[0].length) {
    return "#ERROR: Incorrect number of values and weights";  
  }

  for (var c = 0; c < v[0].length; ++c) {
    sum_v += v[0][c] * w[0][c]; 
    sum_w += w[0][c]; 
  }

  return sum_v/sum_w;
}​

使用它的方式：

=weightedAverage(B3:D3,$B:$D) 

其中B3:D3是您的值，$B:$D是您的体重。它不是防错的（唯一的检查是确保两个数组的长度都相同），但是可以解决问题。

在上面的示例中，我并不是试图从标题中提取权重属性，但是我正在从第二行（B2:D2）中阅读它们，以使我们的生活更轻松，更清晰。 $不会更改公式的结果。仅当您将公式复制到另一个单元格中时，它才会影响正在发生的事情。 $后面的单元格引用部分不会更改（有关更多详细信息，请链接）。在单元格E3中编写一次公式，然后将其复制到其余行以查看其实际效果。

#2 楼

例如，如果权重在从B到L的第2行中，并且想要平均的数据在第3行及更高行中，则可以使用sumproduct如下：

#3 楼

谷歌似乎已经听了。至少在我的Google表格实例中，存在AVERAGE.WEIGHTED函数。根据帮助：

=AVERAGE.WEIGHTED(values,
                  weights,
                  [additional_values, ...],
                  [additional_weights, ...])

#4 楼

Lipis的代码不适用于我，因此这里有一个更新。这段代码：


与当前版本的Google Spreadsheets配合使用，正确地解决了数组元素
检查值是否为数字
切换为零值还是

代码：

/**
  v - Values range
  w - Weights range
  z - count zero values?
*/

function weightedAverage(v, w, z) {
  var sum_v = 0;
  var sum_w = 0;

  if (v.length != w.length) {
    return "#ERROR: Incorrect number of values and weights";  
  }

  for (var c = 0; c < v.length; ++c) {
    if (!z && Number(v[c][0])!=0) {
      sum_v += Number(v[c][0]) * Number(w[c][0]); 
      sum_w += Number(w[c][0]); 
    }
  }

  return sum_v/sum_w;
}

#5 楼

更正Lipis的代码以使其适用于当前版本的Google Spreadsheets：

function weightedAverage(v, w) {
  var sum_v = 0;
  var sum_w = 0;

  if (v.length != w.length) {
    return "#ERROR: Incorrect number of values and weights";  
  }

  for (var c = 0; c < v.length; ++c) {
    sum_v += v[c] * w[c]; 
    sum_w += w[c] * 1; 
  }

  return sum_v/sum_w;
}​

#6 楼

ARRAYFORMULA（）可以计算Google Spreadsheets（以及更多）中的加权平均值。

将权重分别存储在B2：D2中可以简化公式的读取。计算E3的加权平均值看起来类似于上面的SUMPRODUCT（）（复制/粘贴E4和E5）：

使用RegExExtract（）从$ B $ 1：$ D $ 1获得权重是对ARRAYFORMULA（）的简单更改。用更复杂的表达式“ =ARRAYFORMULA(sum(B3:D3 * $B:$D)/sum($B:$D))”代替简单范围，E3的公式变为（E4和E5的复制/粘贴）：
该正则表达式要求属性名称不带数字；因此，请使用AttrA，AttrB和AttrC代替Attr1，Attr2和Attr3。

#7 楼

仔细阅读您的问题后，


理想情况下，我正在寻找一种无需创建临时单元格即可帮助进行计算的解决方案。


我想出了一种不使用临时单元的解决方案。

公式

weights = ARRAYFORMULA(IFERROR(VALUE(REGEXEXTRACT($B:$D,"\((\d+)")),100))

=SUMPRODUCT(B3:D3, weights) / SUM(weights)

copy / paste
=SUMPRODUCT(B2:D2, ARRAYFORMULA(IFERROR(VALUE(REGEXEXTRACT($B:$D,"\((\d+)")),100)))/SUM(ARRAYFORMULA(IFERROR(VALUE(REGEXEXTRACT($B:$D,"\((\d+)")),100)))

屏幕截图



说明

REGEXEXTRACT将在第一个直括号后提取标题中的值，而VALUE会将其转换为数字。如果未找到任何内容，则IFERROR会将值设置为100，而ARRAYFORMULA将可以选择范围而不是单个单元格。

示例

我为您创建了一个示例文件：如何在Google Spreadsheets中计算加权平均值？

#8 楼

由于接受的答案是使用Google Apps脚本，因此我还创建了一个小片段。 />

注意

该脚本将遵循与本文中介绍的逻辑相同的逻辑。主要优点是它将立即计算总体值。

示例

我为您创建了一个示例文件：如何计算Google Spreadsheets中的加权平均值？

#9 楼

对于简单的加权平均值，您可以多次添加像元值。例如，如果您希望A1为75％，B1为25％，则可以输入=AVERAGE (A1,A1,A1,B1)。因此，特别适合您的是=AVERAGE (B2,C2,C2,D1,D2,D2,D2,D2,D2,D2)，它将称B2为C2的一半（50％），D2为3x C2（300％）。 :)

#10 楼

实际上，两个版本的Average.weighted和sumproduct都给出相同的结果：不知道如何在此公式中添加其他权重。

这是工具提示，但不知道如何仅添加权重而不是其他值
>

#11 楼

仅使用给定的函数有一种非常简单的方法。