Kwartyle w programie Excel

10

Interesuje mnie definicja kwartylu, która jest zwykle używana, gdy jesteś w podstawowych statystykach. Mam książkę typu Stat 101, która daje intuicyjną definicję. „Około jedna czwarta danych przypada na pierwszy kwartyl lub poniżej ...”, ale daje przykład, w którym oblicza Q1, Q2 i Q3 dla zestawu danych

5, 7, 9, 10, 11, 13, 14, 15, 16, 17, 18, 18, 20, 21, 37

Ponieważ jest 15 danych, wybiera 15 jako medianę Q2. Następnie dzieli pozostałe dane na dwie połowy, od 5 do 14 i od 16 do 37. Każda z nich zawiera 7 części danych i znajduje medianę każdego z tych zbiorów, odpowiednio 10 i 18, odpowiednio jako Q1 i Q3. Tak bym to sam obliczył.

Przejrzałem artykuł Wikipedii, który podaje 2 metody. Jeden zgadza się z powyższym, a drugi mówi, że możesz również zawrzeć medianę 15 w obu zestawach (ale nie uwzględniłbyś mediany, gdyby była to średnia z dwóch środkowych liczb w przypadku parzystej liczby punktów danych). To wszystko ma dla mnie sens.

Ale potem sprawdziłem Excela, aby zobaczyć, jak Excel go oblicza. Korzystam z programu Excel 2010, który ma 3 różne funkcje. Kwartyl był dostępny w 2007 roku i poprzednich wersjach. Wygląda na to, że chcą, abyś przestał używać tego w 2010 roku, ale nadal jest dostępny. Kwartyl.Inc jest nowy, ale o ile wiem, zgadza się dokładnie z Kwartylem. I jest też Kwartyl. Exc. Wydaje mi się, że oba ostatnie 2 są nowe w 2010 roku. Tym razem po prostu spróbowałem użyć liczb całkowitych 1, 2, 3, ..., 10. Oczekuję, że Excel poda medianę 5,5, Q1 3 i Q3 8. Metoda z księgi statystycznej również ponieważ obie metody na Wikipedii dałyby te odpowiedzi, ponieważ mediana jest średnią z dwóch środkowych liczb. Excel daje

quartile number, Quartile.Inc, Quartile.Exc
1,               3.25,         2.75 
2,               5.5,          5.5
3,               7.75,         8.25

Żadne z nich nie zgadza się z tym, o czym wcześniej mówiłem.

Opisy w pliku pomocy dla programu Excel to:

Quartile.Inc - Zwraca kwartyl zestawu danych na podstawie wartości percentyla od 0..1 włącznie.

Quartile.Exc - Zwraca kwartyl zestawu danych na podstawie wartości percentyli od 0..1, wyłączne.

Czy ktoś może mi pomóc zrozumieć tę definicję, z której korzysta Excel?

excel quantiles

— Graphth
źródło

5

Kolejna świetna ilustracja, dlaczego nie używać Excela do niczego statystycznego. :-)

— Wayne

1

Znajomi nie pozwalają znajomym używać programu Excel do statystyk. Smutne, ale prawdziwe

— Chris Beeley,

11

Zazwyczaj ranga (od do dla danych) jest konwertowana na procent $r$ $1$ $n$ $n$ $p$ za pomocą wzoru

p = 100 \frac{r - α}{n + 1 - 2 α}

$p = 100\frac{r-\alpha}{n+1-2\alpha}$

dla pewnej z góry określonej „pozycji kreślenia” między a włącznie. Rozwiązanie dla kategoriach daje $\alpha$ $0$ $1$ $r$ $p$

r = (n + 1 - 2 α) (p / 100) + α .

$r = (n+1-2\alpha) (p/100) + \alpha.$

Excel historycznie używał dla swoich i funkcji $\alpha=1$ PERCENTILEQUARTILE . Dokumentacja online dla QUARTILE.INCiQUARTILE.EXC jest bezużyteczna, dlatego musimy dokonać inżynierii wstecznej, co robią te funkcje.

Na przykład dla danych mamy a dla trzech kwartylów. Zastosowanie w poprzednim wzorze daje rangę , $(1,2,3,4,5,6,7,8,9,10)$ $n=10$ $p \in \{25, 50, 75\}$ $\alpha=1$ $9(0.25)+1 = 3.25$ i , powielając wyniki dla. $9(0.50)+1 = 5.5$ $9(0.75)+1 = 7.75$ QUARTILE.INC

Jeśli zamiast tego ustawimy odpowiednie rangi to , i , powielając wyniki . $\alpha=0$ $11(0.25) = 2.75$ $11(0.50) = 5.5$ $11(0.75) = 8.25$ QUARTILE.EXC

Dalsze testy z twojej strony (nie mam najnowszej wersji programu Excel) mogą ustalić słuszność mojego przypuszczenia, że te dwie wersje funkcji kwartylu są określone przez te dwie (ekstremalne) wartości $\alpha$ .

Nawiasem mówiąc, szeregi ułamkowe są przekształcane w wartości danych za pomocą interpolacji liniowej. Proces ten został wyjaśniony i zilustrowany w moich notatkach z kursu pod adresem wykresach Percentiles i EDF wykresach Percentiles patrz u dołu tej strony. Istnieje również łącze do arkusza kalkulacyjnego Excel ilustrującego obliczenia.

Jeśli chcesz zaimplementować ogólną funkcję percentyla w programie Excel , oto makro VBA, aby to zrobić:

'
' Converts a percent, computed using plotting position constant A,
' into a percent appropriate for the Excel Percentile() and
' Quartile() functions.  (The default value of A for Excel is 1;
' most values in use are between 0 and 0.5.)
'
Public Function PercentileA(P As Double, N As Integer, A As Double) As Double
    If N < 1 Or A < 0# Or A > 1# Or P < 0# Or P > 1# Then
        Exit Function
    End If
    If N < 2 Then
        PercentileA = 0.5
    Else
        PercentileA = ((N - 2 * A + 1) * P + A - 1) / (N - 1)
    End If
End Function

Konwertuje nominalny procent (np. 25/100) na procent, który spowodowałby, że PERCENTILEfunkcja Excel zwrócił pożądaną wartość. Jest przeznaczony do stosowania w formułach komórkowych, jak w =PERCENTILE(Data, PercentileA(0.25, Count(Data), 0.5)).

— whuber
źródło

Pamiętaj, że gdy dokładnie zrozumiesz, co robi Excel, możesz go efektywnie wykorzystać do prac statystycznych.

— whuber

5

Jeśli mogę humorystycznie nie zgodzić się z twoim komentarzem: Excel może być skutecznie wykorzystywany do pracy statystycznej, jeśli jesteś statystycznym czarodziejem, który może z podstawowych zasad udowodnić, co należy zrobić, to zastosuj metody Excela, aby ustalić, co faktycznie robi. Gdy jesteś tak dobry, większość narzędzi wystarczy. Chociaż chciałbym również zauważyć, że w tym wątku 100% czarodziejów uczestniczących w tej dyskusji do tej pory nie ma dostępu do najnowszej wersji programu Excel, więc jest mało prawdopodobne, aby faktycznie z niego korzystali.

— Wayne

Touche ”, @Wayne. (Ale niektórzy z nas nadal używają starszych wersji Excela :-).)

— whuber

1

Whuber, dziękuję za udostępnienie rozwiązania VBA. To będzie bardzo pomocne. Jako ktoś, kto musi wykonywać statystyki, ale utknął w programie Excel jako jedynym dostępnym narzędziu (tak, wypróbowałem R, ale nie potrafię się nad tym zastanowić), doceniam narzędzia, które pomagają zgiąć Excela do moich potrzeb.

— dav

4

Wydaje mi się, że Excel quartile.inczgadza się z oryginałemquartile , który zgadza się z domyślnymi i innymi definicjami R.

Korzystając z pomocnej wskazówki od whubera, stwierdziłem, że Excel quartile.excwydaje się zgadzać (w przypadku 1..10) z type=6definicją kwantylu przez R :

   > For types 4 through 9, Q[i](p) is a continuous function of p, with
    > gamma = g and m given below. The sample quantiles can be obtained
    > equivalently by linear interpolation between the points (p[k],x[k])
    > where x[k] is the kth order statistic. Specific expressions for p[k]
    > are given below.
    > 
    > ...
    > 
    > 
    > Type 6 m = p
    >       .p[k] = k / (n + 1). Thus p[k] = E[F(x[k])].
    >       This is used by Minitab and by SPSS.

Co najwyraźniej stanowi odpowiedź na twoje pytanie: „Tak, robią to Minitab i SPSS”.

— Wayne
źródło

Czy R nie ma dziewięciu definicji kwantyli? (+1 za edycję, btw)

— whuber

@whuber: Nie zwracaj uwagi na człowieka za zasłoną! (Zmienię swoją odpowiedź. Przy dalszym badaniu, pasuje ona do jednej z pozostałych definicji R. Jest to wyraźnie to, czego używają Minitab i SPSS. Dzięki!)

— Wayne

2

Myślę, że doskonałym smakiem kwartylu jest po prostu ignorowanie 5 i 37 (min i maks w oryginalnych danych).

W wersji Stata zarówno wersja domyślna, jak i wersja alternatywna dają wartości quartile.exc z tymi danymi.

— Dimitriy V. Masterov
źródło

To przypuszczenie wydaje się być niespójne z dokumentacją, która twierdzi, że wartości maksymalne i minimalne mogą być rzeczywiście zwrócone QUARTILE.EXC.

— whuber

W mojej wersji Excel 2010 QUARTILE.EXC (zakres komórek, k) zwróci #LICZBA! chyba że k = {1,2,3}, co odpowiada 25., 50. i 75. percentylowi zgodnie z wyświetlonym menu podręcznym. Pierwotny KWARTYL zaakceptuje również 0 i 4 jako drugi argument, co odpowiada wartości min i max.

— Dimitriy V. Masterov,

1

Dokumentacja stwierdza „Jeśli kwarta ≤ 0 lub jeśli kwarta ≥ 4, QUARTILE.EXC zwraca wartość błędu #LICZBA!”. To wydaje się prawdą. Druga instrukcja „MIN, MEDIAN i MAX zwracają tę samą wartość, co QUARTILE.EXC, gdy kwarta jest równa odpowiednio 0 (zero), 2 i 4” wydaje się fałszywa, chyba że czegoś brakuje. Co za bałagan!

— Dimitriy V. Masterov,

+1 Dziękujemy za sprawdzenie tego, Dimitriy! Rzeczywiście, jedyna różnica między moim zgadywaniem a twoim (co skutecznie się zmienia

n

$n$ w

n - 1

$n-1$ i odejmuje

1

$1$ z każdej rangi) jest to, że moja formuła naprawdę powinna zwracać min i maksimum odpowiednio dla 0 i 100 percentyli #NUM!, więc wydaje się, że twoja charakterystyka jest lepsza (ale moja charakterystyka zapewnia twoje). Zastanawiam się, jaka jest obecna PERCENTILEfunkcja programu Excel ? :-)

— whuber

1

Trzy smaki percentyla zachowują się dla mnie tak samo jak kwartyl. Dla danych 5-37 PERCENTILE.EXC (zakres, k) daje # LICZBA! dla k = {0,1}. Dla k = 0,25 PERCENTILE.EXC daje 10. Jeśli wyrzucę 5 i 37, daje 10,5, co zgadza się z pozostałymi 2 sposobami.

— Dimitriy V. Masterov,

2

Wiele interesujących szczegółów, ale wracając do pierwotnego pytania, nie widzę dwóch naprawdę różnych sposobów, które mogą nie dać dokładnie tej samej odpowiedzi. Pierwszym kwartalnym jest punkt, w którym 25% obserwacji znajduje się poniżej lub poniżej niego. W zależności od wielkości próbki, która może, ale nie musi być dokładnym punktem w danych. Więc jeśli jeden punkt znajduje się poniżej, a następny powyżej, ten pierwszy kwartyl nie jest tak naprawdę dobrze zdefiniowany, a jakikolwiek punkt między tymi dwoma może służyć równie dobrze. To samo dotyczy mediany, gdy wielkość próbki jest parzysta. Reguła wybiera punkt środkowy między punktami danych poniżej i powyżej. Ale tak naprawdę nic nie mówi, że wybór podany przez regułę jest naprawdę lepszy niż jakikolwiek inny punkt.

— Michael R. Chernick
źródło

+1. Chciałbym jednak zasugerować, że takie rozumowanie, choć odpowiednie dla niektórych zastosowań, może być nieco zbyt ograniczone do celów ogólnych: niektóre opcje pozycji kreślenia (

α

$\alpha$ pomiędzy

1 / 3

$1/3$ i

1 / 2

$1/2$ , zwykle) zapewniają na przykład nieco lepsze wartości do wykreślenia prawdopodobieństwa. Jak zauważyłeś, nie będzie to miało zastosowania do obliczania kwartyli, ale będzie miało zastosowanie do obliczania bardziej ekstremalnych percentyli. Obliczanie percentyli jest tak ściśle związane z obliczeniami kwartylowymi (jestem pewien, że jest to ten sam kod podstawowy), że warto pamiętać o tych „szczegółach”, IMHO.

— whuber

0

Dla tych, którzy używają Excela, istnieje całkiem dobry podział różnych metod wersji tutaj http://peltiertech.com/WordPress/comparison/

— Piotr
źródło

2

Byłoby lepiej, gdybyś mógł podsumować je w swojej odpowiedzi. Chociaż link może odpowiedzieć na pytanie, które czasem stają się nieaktualne, w takim przypadku twoja odpowiedź nie będzie pomocna dla przyszłych czytelników.

— Andy,

0

w programie Excel 2016 zauważyłem, że można uzyskać prawidłowe wartości kwartyli, jeśli:

zestaw danych ma nieparzystą liczbę wpisów: użyj QUARTILE.EXC
zestaw danych ma parzystą liczbę wpisów: użyj średniej z QUARTILE.EXC i QUARTILE.INC

— Max Knyazeff
źródło

1

Biorąc pod uwagę, ile definicji kwantyli znajduje się w dowodach innych odpowiedzi, czy możesz wyjaśnić, co rozumiesz przez właściwe wartości?

— mdewey