Excel 2013 zawiera 52 nowe funkcje, z których większość została dodana w celu zapewnienia zgodności ze standardami arkusza kalkulacyjnego Open Document.
Ten post dotyczy funkcji Gaussa w programie Excel 2013.
Obecnie pomoc programu Excel jest nieco nijaka w opisie funkcji.
Składnia: =GAUSS(x)- zwraca o 0,5 mniej niż standardowy normalny rozkład skumulowany.
Dla przypomnienia, standardowy rozkład normalny to szczególny przypadek ze średnią równą 0 i odchyleniem standardowym równym 1. Rozpoznasz to jako krzywą dzwonową.
Excel zawsze miał sposób obliczania prawdopodobieństw dla standardowej krzywej normalnej. Najpierw NORMSDIST, a następnie w programie Excel 2010 NORM.S. DIST (z, True) obliczyłyby prawdopodobieństwa. Argument „z” to liczba odchyleń standardowych od średniej.
Oto trywialny przykład użycia funkcji ROZKŁAD.NORMALNY.S do obliczenia prawdopodobieństwa. Jakie jest prawdopodobieństwo, że losowy członek populacji będzie mniejszy niż -0,5 odchylenia standardowego od średniej? To jest obszar zacieniowany na rysunku 2. Wzór jest prosty =NORM.S.DIST(-0.5,True).
Wystarczająco proste, prawda? Jeśli interesują Cię tylko małe rzeczy, ta formuła byłaby wszystkim, czego potrzebujesz. Jednak naukowców często interesują zakresy inne niż lewa strona krzywej.
Na rysunku 3 chcesz poznać prawdopodobieństwo przypadkowego elementu mieszczącego się między (średnia-0,5 odchylenia standardowego) a (średnia + 1 odchylenie standardowe). Nie ma funkcji ROZKŁAD.NORMALNY.S.RANGE, więc możesz po prostu zapytać o prawdopodobieństwo z zakresu -0,5,1). Zamiast tego musisz znaleźć odpowiedź w dwóch podformułach. Oblicz prawdopodobieństwo, że będzie mniejsze niż +1 z, =NORM.S.DIST(1,True)a następnie odejmij prawdopodobieństwo, że będzie mniejsze niż -0,5 z =NORM.S.DIST(-.5,True). Możesz to zrobić w jednej formule, jak pokazano na rysunku 3.
Zdaję sobie sprawę, że to długi post, ale powyższy obrazek jest najważniejszym obrazem dla zrozumienia nowej funkcji GAUSS. Przeczytaj ponownie ten akapit, aby upewnić się, że rozumiesz koncepcję. Aby uzyskać prawdopodobieństwo, że członek populacji znajdzie się między dwoma punktami na krzywej, należy rozpocząć od ROZKŁADU NORMALNEGO prawego punktu i odjąć ROZKŁAD.NORMALNY S. lewego punktu. To nie czarna magia. Nie jest to nawet tak skomplikowane, jak WYSZUKAJ.PIONOWO. Funkcja zawsze zwraca prawdopodobieństwo z lewej krawędzi krzywej (-nieskończoność) do wartości z.
Co jeśli interesuje Cię prawdopodobieństwo, że rozmiar będzie większy niż określony? Aby znaleźć prawdopodobieństwo bycia większym niż (średnia + 1 odchylenie standardowe), możesz zacząć od 100% i odjąć możliwość bycia mniejszym niż (średnia + 1 odchylenie standardowe). To byłoby =100%-NORM.S.DIST(1,True). Ponieważ 100% to to samo, co 1, możesz skrócić formułę do =1-NORM.S.DIST(1,True). Możesz też zdać sobie sprawę, że krzywa jest symetryczna i poprosić o ROZKŁAD.NORMALNY (-1, Prawda), aby uzyskać tę samą odpowiedź.
Dla tych z Was, którzy mają takie zaburzenia obsesyjno-kompulsyjne jak ja, mogę was zapewnić, że jeśli =SUM(30.85,53.28,15.87)wylądujecie na 100%. Wiem, bo sprawdziłem to w arkuszu.
Wracając do rysunku 3 - powinieneś wiedzieć, jak obliczyć prawdopodobieństwo z dowolnych dwóch punktów z1 i z2. Odejmij ROZKŁAD.NORMALNY.S (z2, Prawda)-ROZKŁAD.NORM.S (z1, Prawda), a otrzymasz odpowiedź. Rozważmy bardzo szczególny przypadek, w którym z1 jest średnią. Próbujesz obliczyć prawdopodobieństwo, że ktoś będzie między średnią a +1,5 odchylenia standardowego od średniej, jak pokazano na rysunku 6.
Korzystając z tego, czego nauczyłeś się na rysunku 3, który z nich określiłby prawdopodobieństwo obszaru pod krzywą powyżej?
-
=NORM.S.DIST(1.5,True)-NORM.S.DIST(0,True) -
=NORM.S.DIST(1.5,True)-NORM.S.DIST(0,True) -
=NORM.S.DIST(1.5,True)-NORM.S.DIST(0,True) - Żadne z powyższych
Jak ci poszło? Zakładając, że odpowiedziałeś A, B lub C, uzyskałeś 100% wyniku w teście. Gratulacje. Jak powiedziałem, to naprawdę nie jest fizyka jądrowa.
Ci z Was, którzy kochają skróty, powinni pamiętać, że istnieje 50% prawdopodobieństwo, że coś będzie mniejsze lub równe średniej. Kiedy zobaczysz = ROZKŁAD NORMALNY (0, Prawda), możesz natychmiast pomyśleć: „Och - to jest 50%!”. Zatem odpowiedź B powyżej może zostać przepisana jako
=NORM.S.DIST(1.5,True)-50%
Ale jeśli kochasz skróty, nienawidzisz wpisywania 50% i skracasz je do .5:
=NORM.S.DIST(1.5,True)-.5
Czy mógłbyś użyć symetrycznego przeciwieństwa obszaru pod krzywą? Tak, = 0,5-ROZKŁAD NORMALNY (-1,5, Prawda) da ten sam wynik. Zatem powyższy quiz może wyglądać następująco:
-
=NORM.S.DIST(1.5,True)-NORM.S.DIST(0,True) -
=NORM.S.DIST(1.5,True)-.5 -
=.5-NORM.S.DIST(-1.5,True) - Wszystkie powyższe
Pod warunkiem, że wybierzesz odpowiedź, dam ci pełny kredyt. W końcu to Excel. Jest pięć sposobów na zrobienie czegokolwiek i zaakceptuję każdą odpowiedź, która działa (no cóż, poza twardym kodowaniem = 0,433 w komórce).
Dla tych z Was, którzy odpowiedzieli poprawnie na ostatnie pytanie, przestańcie czytać. Wszyscy inni będą potrzebować GAUSS:
A co z funkcją GAUSS? Cóż, funkcja GAUSS daje nam jeszcze inny sposób rozwiązania konkretnego przypadku, w którym zakres rozciąga się od średniej do punktu powyżej średniej. Zamiast używać powyższych odpowiedzi, możesz użyć =GAUSS(1.5).
Tak… dodali funkcję dla osób, które nie mogą odjąć 0,5 od ROZKŁADU NORM.
Jeśli jesteś podobny do mnie, pytasz: „Poważnie? Zmarnowali zasoby na dodanie tej funkcji?” Cóż, w programie Excel 2007 zespół Excela podjął decyzję, aby umożliwić nam zapisywanie dokumentów w formacie .ODS. To jest format arkusza kalkulacyjnego Open Document. Nie jest to format kontrolowany przez firmę Microsoft. Ponieważ oferują obsługę ODS, Microsoft jest zmuszony dodać wszystkie funkcje obsługiwane przez arkusz kalkulacyjny Open Document. Najwyraźniej większość osób w konsorcjum Open Document Spreadsheet nie mogła zrozumieć, że odpowiedź na mój pierwszy quiz brzmiała A, więc dodali zupełnie nową funkcję.
Domyślam się, że Microsoft nie był zachwycony dodaniem obsługi funkcji, które były podobne do innych funkcji już w programie Excel. Niemal mogę sobie wyobrazić rozmowę między autorem technicznym, którego zadaniem jest pisanie o GAUSS w Pomocy programu Excel, a kierownikiem projektu w zespole programu Excel:
Pisarz: „Więc opowiedz mi o GAUSS”
PM: „To głupie. Weź =NORM.S.DISTi odejmij 0,5. Nie mogę uwierzyć, że musieliśmy to dodać”.
Następnie autor zredagował komentarze redakcyjne i zaproponował ten temat pomocy:
Więc - pozwólcie, że zaproponuję ten alternatywny temat pomocy:
GAUSS (z) - Oblicza prawdopodobieństwo, że członek standardowej populacji normalnej znajdzie się między średnią a + z odchyleniami standardowymi od średniej.
- z Wymagane. Liczba odchyleń standardowych powyżej średniej. Zwykle w zakresie od +0,01 do +3.
- Dodano do programu Excel 2013 w celu obsługi osób, które nie mogą odejmować dwóch liczb.
- Nie jest to szczególnie istotne dla ujemnych wartości Z. Aby obliczyć prawdopodobieństwo, że coś mieści się w przedziale od -1,5 do średniej, użyj
=GAUSS(1.5). - Nie będzie działać w programie Excel 2010 i wcześniejszych. W programie Excel 2010 i starszych wersjach użyj
=NORM.S.DIST(z,True)-0.5.
Masz to… więcej niż kiedykolwiek chciałeś wiedzieć o GAUSS. To z pewnością więcej, niż kiedykolwiek chciałem wiedzieć. Nawiasem mówiąc, moje książki Excel In Depth oferują pełny opis wszystkich 452 funkcji programu Excel. Sprawdź poprzednią edycję, Excel 2010 In Depth lub nowy Excel 2013 In Depth, który zostanie wydany w listopadzie 2012.
