Jak obliczyć średnie ważone w Arkuszach Google?

Mam Arkusze Google, w których produkty są wymienione jako wiersze, a atrybuty jako kolumny. Atrybut każdego produktu jest oceniany w skali 1-10. Moja ostatnia kolumna to średnia z tych wartości (tj =Average(B2:D2).). Działa to dobrze, jeśli każdy atrybut ma taką samą wagę.

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

Problem polega na tym, że chcę, aby każdy atrybut miał inną wagę. Na przykład Attr1 może nie być ważny i powinien być wart tylko 50%, podczas gdy Attr3 jest bardzo ważny i powinien być wart 300%.

+--------+-------------+-------+--------------+---------+
|        | 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    |
+--------+-------------+-------+--------------+---------+

Wartość dla pierwszego rzędu to:

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

które można obliczyć za pomocą:

(
  B2*(IFERROR(REGEXEXTRACT(B1, "\d+"), 100)/100) 
  + C2*(IFERROR(REGEXEXTRACT(C1, "\d+"), 100)/100)
  + D2*(IFERROR(REGEXEXTRACT(D1, "\d+"), 100)/100) 
) / (
  IFERROR(REGEXEXTRACT(B1, "\d+"), 100)/100
  + IFERROR(REGEXEXTRACT(C1, "\d+"), 100)/100
  + IFERROR(REGEXEXTRACT(D1, "\d+"), 100)/100
)

które, jak widać, mogą stać się bardzo trudne do zarządzania w miarę dodawania kolejnych atrybutów. Idealnie szukam rozwiązania, które nie wymaga tworzenia tymczasowych komórek, aby pomóc w obliczeniach.

Czy jest jakaś wbudowana funkcja lub wspólna konwencja, która może pomóc mi obliczyć te średnie ważone?

Nie ma wbudowanej funkcji do obliczania średniej ważonej, więc musisz napisać niestandardową funkcję, jeśli chcesz uniknąć używania zbyt wielu komórek tymczasowych. Oto jak możesz osiągnąć swój cel.

Przejdź do Narzędzia > Skrypty > Edytor skryptów ... , Skopiuj / Wklej poniższy kod i zapisz go:

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;
}​

Użyj tego jak:

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

Gdzie B3:D3są twoje wartości i $B$2:$D$2waga. Nie jest to dowód na błędy (jedynym sprawdzeniem jest upewnienie się, że obie tablice mają tę samą długość), ale da radę.

W powyższym przykładzie nie próbuję wyodrębniać wag z tytułu atrybutu, ale czytam je z drugiego wiersza ( B2:D2), aby nasze życie było łatwiejsze i jaśniejsze. $Nie zmienia wynik formuły. Ma to wpływ tylko na to, co się dzieje, gdy kopiujesz formułę w innej komórce. Część odwołania do komórki po znaku $ nie zmieni się ( link, aby uzyskać więcej informacji). Napisz formułę raz w komórce E3i skopiuj ją do pozostałych wierszy, aby zobaczyć, jak działa.

Jeśli twoje wagi są w rzędzie 2 od B do L, a dane, które chcesz uśrednić, znajdują się w wierszach 3 i wyższych, powiedzmy, możesz użyć sumproduct w następujący sposób:

=sumproduct(B$2:L$2, C3:L3)/sum(B$2:L$2)

Wyglądało na to, że Google nasłuchiwał. Przynajmniej w moim przypadku Arkuszy Google funkcja ta AVERAGE.WEIGHTEDistnieje. Za pomoc:

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

Kod Lipisa nie działał dla mnie, więc oto aktualizacja. Ten kod:

• współpracuje z bieżącą wersją Arkuszy kalkulacyjnych Google poprawnie adresujących elementy tablicy

• sprawdza, czy wartości są liczbami

• ma przełącznik do rozważania wartości zerowych lub nie

Kod:

/**
  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;
}

Poprawianie kodu Lipis do pracy w bieżącej wersji Arkuszy kalkulacyjnych Google:

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;
}​

ARRAYFORMULA () może obliczyć średnią ważoną w arkuszach kalkulacyjnych Google (i nie tylko).

Przechowywanie odważników oddzielnie w B2: D2 upraszcza czytanie wzoru. Obliczanie średniej ważonej dla E3 wygląda wtedy podobnie do SUMPRODUCT () powyżej (kopiuj / wklej dla E4 i E5):

=ARRAYFORMULA(sum(B3:D3 * $B$2:$D$2)/sum($B$2:$D$2))

+--------+-------------+-------+-------------+---------+
|        | AttrA (50%) | AttrB | AttrC (300%)| Overall |
| Weight:|        50%  |  100% |        300% |         |
+--------+-------------+-------+-------------+---------+
| Prod 1 |       10    |  8    |       9     | 8.8889  |
| Prod 2 |        2    | 10    |       7     | 7.1111  |
| Prod 3 |        4    |  6    |       6     | 5.7778  |
+--------+-------------+-------+-------------+---------+

Użycie RegExExtract () w celu uzyskania wag od $ B $ 1: $ D $ 1 to prosta zmiana w ARRAYFORMULA (). Zamień prosty zakres na bardziej złożone wyrażenie „ iferror(regexextract($B$1:$D$1,"\d+"),100)/100”, a formuła dla E3 staje się (kopiuj / wklej dla E4 i E5):

=ARRAYFORMULA(sum(B3:D3 * iferror(regexextract($B$1:$D$1,"\d+"),100)/100)/sum(iferror(regexextract($B$1:$D$1,"\d+"),100)/100))

Uwaga: Wyrażenie regularne wymaga nazw atrybutów bez liczb; więc użyj AttrA, AttrB i AttrC zamiast Attr1, Attr2 i Attr3.

Po uważnym przeczytaniu pytania,

Idealnie szukam rozwiązania, które nie wymaga tworzenia tymczasowych komórek, aby pomóc w obliczeniach.

Wymyśliłem to rozwiązanie, które nie wykorzystuje komórek tymczasowych.

Formuła

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

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

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

Zrzut ekranu

Wyjaśnił

REGEXEXTRACTBędzie wyodrębnić wartość w nagłówku po pierwszej prostej wspornika i VALUEbędzie przekształcić go w szeregu. IFERRORBędzie ustawiona na wartość 100, jeśli nic nie zostanie znaleziony i ARRAYFORMULApozwoli, aby wybrać zakres, zamiast pojedynczych komórek.

Przykład

Utworzyłem dla Ciebie przykładowy plik: jak obliczyć średnie ważone w Arkuszach kalkulacyjnych Google?

Ponieważ zaakceptowana odpowiedź korzysta z Google Apps Script, stworzyłem również krótki fragment.

Kod

function weightedAverage(v, w) {
  var weights = [], sumWeights = 0;
  for(var k = 0, kLen = w[0].length; k < kLen; k++) {
    var m = w[0][k].match(/\((\d+)/), value;
    if(!m) {
      value = 100;
    } else {
      value = Number(m[1]);
    }
    weights.push(value);
    sumWeights += value;
  }

  var output = [];
  for(var i = 0, iLen = v.length; i < iLen; i++) {
    var sumProduct = 0;
    for(var j = 0, jLen = v[0].length; j < jLen; j++) {
      sumProduct += v[i][j] * weights[j]; 
    }
    output.push(sumProduct / sumWeights);
  } 
  return output;
}

Zrzut ekranu

Uwaga

Skrypt będzie działał zgodnie z tą samą logiką, jaką przedstawiono w tym poście. Główną zaletą jest to, że jednocześnie obliczy ogólne wartości.

Przykład

Utworzyłem dla Ciebie przykładowy plik: jak obliczyć średnie ważone w Arkuszach kalkulacyjnych Google?

W przypadku prostych średnich ważonych można po prostu wielokrotnie dodać wartość komórki. Na przykład, jeśli chcesz, aby A1 wynosiła 75%, a B1 25%, możesz włożyć =AVERAGE (A1,A1,A1,B1). Byłoby to specjalnie dla Ciebie, =AVERAGE (B2,C2,C2,D1,D2,D2,D2,D2,D2,D2)które ważyłoby B2 jako połowę C2 (50%), a D2 jako 3x C2 (300%).

Bardziej skomplikowane rzeczy są powyżej mojej oceny płacowej. :)

W rzeczywistości obie wersje Average.weighted i sumproduct dają ten sam wynik:

AVERAGE.WEIGHTED("Values","Weight")

SUMPRODUCT("Numbers","Weight")/sum("Weight")

Raczej używam pierwszego, ponieważ jest o wiele prostszy w obsłudze, ale nie wiem, jak dodać inne wagi do tej formuły.

Jest to podpowiedź, ale nie wiem, jak dodawać tylko wagi, a nie dodatkowe wartości

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

Jest dość prosty sposób używania tylko podanych funkcji.

=(sumproduct(E5:E9;G5:G9)/sum(E5:E9))   

gdzie zawartość E jest liczbą punktów, a G znaczenie tych punktów.