Jak obliczyć ważone odchylenie standardowe? W programie Excel?

29

Tak więc mam zestaw danych takich wartości procentowych:

100   /   10000   = 1% (0.01)
2     /     5     = 40% (0.4)
4     /     3     = 133% (1.3) 
1000  /   2000    = 50% (0.5)

Chcę znaleźć standardowe odchylenie procentowe, ale ważone dla ich objętości danych. tzn. pierwszy i ostatni punkt danych powinny dominować w obliczeniach.

W jaki sposób mogę to zrobić? Czy istnieje prosty sposób, aby to zrobić w programie Excel?

standard-deviation excel weighted-mean

— Yahel
źródło

Wzór na (M-1) / M jest poprawny. Jeśli masz wątpliwości, sprawdź to, ustawiając wszystkie wagi równe 1, a otrzymasz klasyczną formułę dla obiektywnego oszacowania odchylenia standardowego z (N-1) w mianowniku. Whuber: niezwykłe nie znaczy niepoprawne.

1

Wzór z (M-1) / M NIE JEST PRAWIDŁOWY. Wyobraź sobie, że dodajesz milion punktów o wadze jednego tryliona. W ogóle nie zmieniasz odpowiedzi, niezależnie od tego, jakie są te wagi, ale twój

termin staje się 1? Absolutnie nie! Jeśli zależy ci na tym

, to zależy ci również na tym, że to po prostu źle.

(M - 1) / M

$(M-1)/M$

(M - 1) / M \neq 1

$(M-1)/M \neq 1$

— Rex Kerr

Najwyższy głos jest poprawny. Sprawdź itl.nist.gov/div898/software/dataplot/refman2/ch2/weightsd.pdf

— Bo Wang

Zastanawiam się, dlaczego chcesz tutaj odchylenie standardowe? masz tylko

liczby! Jak to za dużo liczb? Zwłaszcza, gdy wartości procentowe są łatwiejsze do wyjaśnienia i zrozumienia.

4

$4$

— probabilityislogic

@probabilityislogic był to uproszczony przykład krótkiego pytania.

— Yahel

35

Formuła odchylenia standardowego ważonego jest:

\sqrt{\frac{\sum_{i = 1}^{N} w_{i} (x_{i} - {\bar{x}}^{*})^{2}}{\frac{(M - 1)}{M} \sum_{i = 1}^{N} w_{i}}},

$\sqrt{ \frac{ \sum_{i=1}^N w_i (x_i - \bar{x}^*)^2 }{ \frac{(M-1)}{M} \sum_{i=1}^N w_i } },$

gdzie

jest liczbą obserwacji. $N$

jest liczbą niezerowych wag. $M$

wagą $w_i$

są obserwacjami. $x_i$

jest średnią ważoną. $\bar{x}^*$

Pamiętaj, że wzór na średnią ważoną to:

{\bar{x}}^{*} = \frac{\sum_{i = 1}^{N} w_{i} x_{i}}{\sum_{i = 1}^{N} w_{i}} .

$\bar{x}^* = \frac{\sum_{i=1}^N w_i x_i}{\sum_{i=1}^N w_i}.$

Użyj odpowiednich ciężarków, aby uzyskać pożądany wynik. W twoim przypadku sugerowałbym użycie . $\frac{\mbox{Number of cases in segment}}{\mbox{Total number of cases}}$

Aby to zrobić w programie Excel, musisz najpierw obliczyć średnią ważoną. Następnie oblicz w osobnej kolumnie. Reszta musi być bardzo łatwa. $(x_i - \bar{x}^*)^2$

— deps_stats
źródło

2

@Gilles, masz rację. deps_stats, ułamek

w SD jest niezwykły. Czy masz cytat z tej formuły, czy możesz przynajmniej wyjaśnić powód włączenia tego terminu?

(M - 1) / M

$(M-1)/M$

— whuber

4

@ Wagi Aarona nie zawsze są definiowane jako suma jedności, czego przykładem są wagi podane w tym pytaniu!

— whuber

2

(-1) Głosuję za odrzuceniem tej odpowiedzi, ponieważ nie podano uzasadnienia ani odniesienia do terminu

(i jestem prawie pewien, że nie czyni to szacunku wariancji obiektywnym, co byłoby oczywiste motywacja).

(M - 1) / M

$(M-1)/M$

— whuber

1

W świetle dodanego odniesienia (które nie jest wiarygodne, ale jest odniesieniem) usuwam głosowanie negatywne. Nie popieram jednak tej odpowiedzi, ponieważ obliczenia pokazują, że proponowana waga nie daje w ogóle obiektywnego oszacowania (z wyjątkiem sytuacji, gdy wszystkie wagi są równe

). Prawdziwa trudność tutaj - która jest winą pytania, a nie odpowiedzi - polega na tym, że nie jest jasne, co próbuje oszacować to „ważone odchylenie standardowe”. Bez określonego oszacowania nie ma uzasadnienia dla wprowadzenia współczynnika

celu „zmniejszenia uprzedzeń” (lub z jakiegokolwiek innego powodu).

1

$1$

(M - 1) / M

$(M-1)/M$

— whuber

1

@Mikhail Masz rację, że „niezwykłe” i „właściwe” niewiele mają ze sobą wspólnego. Jednak nietypowe wyniki domyślnie wymagają nieco więcej uzasadnienia, ponieważ bycie nietypowym jest jednym ze wskaźników, że mógł zostać popełniony błąd. Twój argument jest nieważny: chociaż formuła rzeczywiście zmniejsza się do jednego dla obiektywnego estymatora, gdy wszystkie wagi są równe, nie oznacza to, że estymator pozostaje bezstronny, gdy używane są nierówne wagi. Nie twierdzę, że twoja konkluzja jest błędna, ale tylko to, że jak dotąd nie przedstawiono żadnego ważnego uzasadnienia.

— whuber

18

Formuły są dostępne w różnych miejscach, w tym w Wikipedii .

Kluczem jest zauważyć, że zależy to od tego, co oznaczają wagi . W szczególności otrzymasz różne odpowiedzi, jeśli wagi są częstotliwościami (tj. Próbujesz po prostu uniknąć zsumowania całej sumy), jeśli wagi są w rzeczywistości wariancją każdego pomiaru lub jeśli są to tylko niektóre wartości zewnętrzne nałożyć na twoje dane.

W twoim przypadku powierzchownie wygląda na to, że wagi są częstotliwościami, ale nimi nie są . Generujesz dane na podstawie częstotliwości, ale nie jest to proste, aby mieć 45 rekordów 3 i 15 rekordów 4 w zbiorze danych. Zamiast tego musisz użyć ostatniej metody. (W rzeczywistości wszystko to jest śmieciem - naprawdę musisz użyć bardziej wyrafinowanego modelu procesu, który generuje te liczby! Najwyraźniej nie masz czegoś, co wyrzuca liczby normalnie dystrybuowane, więc charakteryzujesz system standardowym odchyleniem nie jest właściwe.)

W każdym razie wzór na wariancję (z którego oblicza się odchylenie standardowe w normalny sposób) z wagami „niezawodności” to

\frac{\sum w_{i} (x_{i} - x^{*})^{2}}{\sum w_{i} - \frac{\sum w_{i}^{2}}{\sum w_{i}}}

${ \sum {w_i (x_i - x^*)^2} \over {\sum w_i - {\sum w_i^2 \over \sum w_i }} }$

gdzie $x^* = \sum w_i x_i / \sum w_i$

Nie masz szacunkowej masy, którą zakładam, że chcesz być proporcjonalny do niezawodności. Biorąc udział procentowy w taki sposób, w jaki jesteś, utrudnisz analizę, nawet jeśli są one generowane przez proces Bernoulli, ponieważ jeśli uzyskasz wynik 20 i 0, masz nieskończony procent. Ważenie przez odwrotność SEM jest powszechną, a czasem optymalną rzeczą do zrobienia. Być może powinieneś użyć szacunku Bayesa lub przedziału punktacji Wilsona .

— Rex Kerr
źródło

2

+1. Dyskusja na temat różnych znaczeń ciężarów była tym, czego cały czas szukałem w tym wątku. Jest to ważny wkład we wszystkie pytania tej witryny dotyczące ważonych statystyk. (Jestem trochę zaniepokojony nawiasami na temat normalnych rozkładów i odchyleń standardowych, ponieważ błędnie sugerują, że SD nie mają zastosowania poza modelem opartym na normalności.)

— whuber

@whuber - Cóż, oczywiście centralne twierdzenie graniczne na ratunek! Ale ze względu na to, co robił OP, próba scharakteryzowania tego zestawu liczb za pomocą średniej i standardowego odchylenia wydaje się niezwykle niewskazana. Ogólnie rzecz biorąc, w przypadku wielu zastosowań odchylenie standardowe prowadzi do fałszywego poczucia zrozumienia. Na przykład, jeśli rozkład jest inny niż normalny (lub jego dobre przybliżenie), poleganie na standardowym odchyleniu da złe wyobrażenie o kształcie ogonów, kiedy to dokładnie te ogony są dla ciebie najbardziej istotne w statystyce testowanie.

— Rex Kerr,

@ RexKerr Nie możemy winić standardowego odchylenia, jeśli ludzie umieszczają na nim interpretacje, które są niezasłużone. Ale odejdźmy od normalności i rozważmy znacznie szerszą klasę ciągłych, symetrycznych rozkładów unimodalnych ze skończoną wariancją (na przykład). Zatem od 89 do 100 procent rozkładu mieści się w dwóch standardowych odchyleniach. To często bardzo przydatne, aby wiedzieć (a 95% leży prawie w środku, więc nigdy nie jest to więcej niż około 7% zniżki); przy wielu powszechnych dystrybucjach aspekt symetrii porzucającej niewiele się zmienia (np. spójrz na wykładniczy, na przykład) .... ctd

— Glen_b

ctd ... - lub jeśli nie przyjmiemy żadnego z tych założeń, zawsze istnieją zwykłe granice Czebyszewa, które przynajmniej mówią coś o ogonach i standardowym odchyleniu ..

— Glen_b

1

@Gabriel - Tak, przepraszam, byłem niechlujny. (Sądzę, że ludzie mogą stwierdzić, który z nich jest przez spojrzenie.) Poprawiłem swój opis.

— Rex Kerr

5

=SQRT(SUM(G7:G16*(H7:H16-(SUMPRODUCT(G7:G16,H7:H16)/SUM(G7:G16)))^2)/
     ((COUNTIFS(G7:G16,"<>0")-1)/COUNTIFS(G7:G16,"<>0")*SUM(G7:G16)))

Kolumna G to wagi, kolumna Hto wartości

— użytkownik35936
źródło

Używanie Ctrl + Shift + Enter było dla mnie problemem, ale wydaje się, że działa inaczej.

— philipkd

1

p_{ja} = \frac{v_{ja}}{\sum_{ja} v_{ja}},

$p_i=\frac{v_i}{\sum_iv_i},$

v_{i}

$v_i$

\hat{μ} = \sum_{ja} p_{ja} x_{ja},

$\hat\mu=\sum_ip_ix_i,$ i wariancja:

{\hat{σ}}^{2)} = \sum_{ja} p_{ja} (x_{ja} - \hat{μ})^{2)}

$\hat\sigma^2=\sum_ip_i(x_i-\hat\mu)^2$

— Aksakal
źródło

0

Option Explicit

Function wsdv(vals As Range, wates As Range)
Dim i, xV, xW, y As Integer
Dim wi, xi, WgtAvg, N
Dim sumProd, SUMwi

    sumProd = 0
    SUMwi = 0
    N = vals.Count  ' number of values to determine W Standard Deviation
    xV = vals.Column  ' Column number of first value element
    xW = wates.Column  ' Column number of first weight element
    y = vals.Row - 1  ' Row number of the values and weights

    WgtAvg = WorksheetFunction.SumProduct(vals, wates) / WorksheetFunction.Sum(wates)

    For i = 1 To N  ' step through the elements, calculating the sum of values and the sumproduct
        wi = ActiveSheet.Cells(i + y, xW).Value  ' (i+y, xW) is the cell containing the weight element
        SUMwi = SUMwi + wi
        xi = ActiveSheet.Cells(i + y, xV).Value  ' (i+y, xV) is the cell containing the value element
        sumProd = sumProd + wi * (xi - WgtAvg) ^ 2
    Next i

    wsdv = (sumProd / SUMwi * N / (N - 1)) ^ (1 / 2)  ' output of weighted standard deviation

End Function

— użytkownik71015
źródło

2

Witamy na stronie, @ uswer71015. To chyba tylko kod. Czy możesz dodać tekst / objaśnienie działania kodu i odpowiedzi na pytanie?

— gung - Przywróć Monikę