Jimmy z Huntsville chce wykreślić krzywą dzwonową pokazującą średnie wyniki kilku osób. Kiedy Jimmy zadał pytanie podczas mojego seminarium Power Excel, wróciłem myślami do jednego z moich bardziej popularnych filmów na YouTube.
W Podcastu 1665 - Utwórz krzywą dzwonową w programie Excel wyjaśniam, że aby utworzyć krzywą dzwonową, musisz obliczyć średnią i odchylenie standardowe. Następnie generuję 30 punktów wzdłuż osi X, które obejmują hipotetyczną populację ludzi. W tym filmie wygenerowałem zakres od -3 odchylenia standardowego do + 3 odchylenia standardowego wokół średniej.
Na przykład, jeśli masz średnią równą 50 i odchylenie standardowe 10, utworzę oś x, która biegnie od 70 do 130. Wysokość każdego punktu jest obliczana za pomocą =NORM.DIST(x,mean,standard deviation,False).
Na powyższym obrazku liczby w A10: A40 to w zasadzie „fałszywe punkty danych”. Generuję 31 liczb, aby stworzyć ładną, gładką krzywą. Gdybym użył tylko 7 punktów danych, krzywa wyglądałaby tak:
W przypadku zbioru danych Jimmy'ego rzeczywiste średnie wyniki jego pracowników są zasadniczo punktami na osi x. Aby dopasować je do krzywej dzwonowej, musisz obliczyć wysokość lub wartość Y dla każdego pracownika.
Wykonaj następujące kroki:
-
Posortuj dane tak, aby wyniki były najniższe do najwyższych.
Sortuj dane - Oblicz średnią za pomocą funkcji ŚREDNIA.
- Oblicz odchylenie standardowe za pomocą funkcji ODCH.STANDARDOWE.
-
Oblicz wartość Y po prawej stronie wyników za pomocą
=NORM.DIST(L2,$H$2,$H$3,FALSE). Wartość Y wygeneruje wysokość punktu każdej osoby wzdłuż krzywej dzwonka. Funkcja ROZKŁAD.NORMALNY zajmie się wykreślaniem ludzi w pobliżu średniej w wyższej lokalizacji niż osoby na górze lub na dole.
Wygeneruj serię wartości Y. - Wybierz swoje dane w L1: M15
-
W programie Excel zaczął się ostatnio pojawiać dziwny błąd, więc aby zapewnić sukces, wybierz Wszystkie wykresy na karcie Wstawianie.
Program uruchamiający okno dialogowe przeniesie Cię do wszystkich typów wykresów W oknie dialogowym Wstaw wykres kliknij kartę Wszystkie wykresy. Kliknij XY (rozproszenie) po lewej stronie. Wybierz drugą ikonę u góry. Wybierz podgląd po prawej stronie.
Cztery kliknięcia, aby wybrać wykres Twoja początkowa krzywa dzwonka będzie wyglądać następująco:
Krzywa dzwonu
Aby wyczyścić krzywą dzwonową, wykonaj następujące kroki:
- Kliknij tytuł i naciśnij klawisz Delete.
- Kliknij dwukrotnie dowolną liczbę wzdłuż osi Y u dołu wykresu. Pojawi się panel Format Axis.
-
Wpisz nowe wartości Minimum i Maksimum. Zakres tutaj powinien być wystarczająco szeroki, aby pokazać wszystkich na wykresie. Używałem od 50 do 90.
Zmień minimum i maksimum - Rozszerz wykres, przeciągając krawędź wykresu.
- Kliknij ikonę + po prawej stronie wykresu i wybierz Etykiety danych. Nie martw się, że etykiety nie mają jeszcze sensu.
- Kliknij dwukrotnie jedną etykietę, aby otworzyć panel Formatuj etykiety.
- W górnej części panelu znajdują się cztery ikony. Wybierz ikonę przedstawiającą wykres kolumnowy.
- Kliknij strzałkę obok opcji Opcje etykiety, aby rozwinąć tę część panelu.
- Wybierz wartość z komórek. Pojawi się okno dialogowe z pytaniem o lokalizację etykiet. Wybierz nazwy w K2: K15.
-
Pozostając w panelu Formatuj etykietę danych, odznacz wartości Y. Ważne jest, aby zakończyć krok 15 przed wykonaniem kroku 16, w przeciwnym razie nieumyślnie usuniesz etykiety.
Pobierz etykiety z komórek zawierających nazwy.
Uwaga
Możliwość pobierania etykiet z komórek została dodana w programie Excel 2013. Jeśli używasz programu Excel 2010 lub wcześniejszego, pobierz dodatek XY Chart Labeler od Roba Boveya. (Google, aby go znaleźć).
W tym momencie sprawdź, czy masz jakieś etykiety wykresów, które ulegają awariom. Aby je naprawić, uważnie wykonaj następujące kroki.
- Pojedyncze kliknięcie na jedną etykietę wykresu. Spowoduje to zaznaczenie wszystkich etykiet.
- Kliknij raz na jedną z etykiet, która znajduje się nad inną etykietą, aby wybrać tylko tę etykietę.
- Najedź na różne części etykiety, aż zobaczysz czterogłową strzałkę. Kliknij i przeciągnij etykietę w nowe miejsce.
-
Po wybraniu tylko jednej etykiety możesz kliknąć pojedynczo dowolną inną etykietę, aby wybrać tę etykietę. Powtórz te czynności dla innych etykiet, które mają zostać przeniesione.
Ostateczny wykres
Obejrzyj wideo
Transkrypcja wideo
Naucz się programu Excel z podcastu, odcinek 2217: Umieść ludzi na krzywej dzwonka.
Hej, witaj z powrotem w netcastie, jestem Bill Jelen. Dzisiejsze pytanie od Jimmy'ego na moim seminarium w Huntsville w Alabamie. Jimmy ma dane, chce je podsumować, a następnie wykreślić wyniki na krzywej dzwonowej.
W porządku? Jeden z moich najpopularniejszych filmów na YouTube to ten: numer 1663, Utwórz krzywą dzwonową w programie Excel. Biorąc pod uwagę średnią i odchylenie standardowe, obliczyłem najniższy, który jest 3 razy większy od odchylenia standardowego niż średnia, a wysoki - 3 razy większy od odchylenia standardowego niż średnia - gdzie różnica wynosi - i seria wartości X tutaj i aby obliczyć wysokość, użyj tej funkcji: = ROZKŁ.NORMALNY wartości X, średniej i odchylenia standardowego, przecinek fałsz (= ROZKŁAD.NORMALNY (A10, $ B $ 2, $ B 3 $, FAŁSZ)).
A jeśli się nad tym zastanowić, w tym filmie tak naprawdę po prostu wykorzystujemy serię fałszywych wartości X, aby uzyskać ładnie wyglądającą krzywą. Użyjemy tutaj tej samej koncepcji, ale zamiast fałszywych wartości X, będziemy mieć ludzi tutaj na dole, a wtedy wysokość będzie dokładnie taka sama. W porządku.
Więc teraz Jimmy chciał utworzyć tabelę przestawną. Więc wstawimy, tabelę przestawną, umieścimy to tutaj na tym arkuszu, kliknij OK. Ludzie po lewej stronie, a następnie ich średni wynik. W porządku, więc zaczyna się od sumy wyniku, kliknę tam dwukrotnie i zmienię to na średnią. Świetny. Teraz, na samym dole, nie chcę sumy całkowitej - kliknij prawym przyciskiem myszy i usuń sumę całkowitą - i chcemy ustawić te osoby od góry do dołu, a to jest łatwe do zrobienia w tabeli przestawnej. Dane, od A do Z - doskonale. W porządku. Teraz zrobimy dokładnie to samo, co w Podcastu 1663, a to obliczyć średnią i odchylenie standardowe. Zatem średnia jest średnią tych wyników, a następnie równa się odchyleniu standardowemu tych wyników. W porządku. Teraz, kiedy to wiem, jestem w stanie stworzyć moją wartość y.
W porządku, więc mamy zamiar zrobić kilka rzeczy. Po pierwsze, nie możesz utworzyć tabeli przestawnej - wykresu punktowego - z tabeli przestawnej. Więc skopiuję wszystkie te dane i zrobię to z = D2. Zauważ, że uważam, aby nie używać myszy ani klawiszy strzałek do wskazywania na nie. Dlatego mamy tutaj nasze wartości. Będą one wartościami X, wartość Y stanie się = ROZKŁAD.NORMALNY, tutaj jest wartość x, przecinek, dla średniej, tej liczby, naciśnę F4, aby ją zablokować; dla odchylenia standardowego jest to ta liczba, ponownie naciśnij klawisz F4, aby ją zablokować, i skumulowany FALSE. (= NORM.DIST (K2, $ H $ 2, $ H $ 3, FALSE)) I klikniemy dwukrotnie, aby skopiować to w dół. W porządku. A potem nie wybieraj etykiet,po prostu wybierz XY, a wstawimy wykres punktowy z liniami - możesz wybrać wykres z liniami zakrzywionymi lub trochę prostymi. Tutaj pójdę z takimi zakrzywionymi liniami. A teraz mamy wszystkich naszych Ludzi umieszczonych na krzywej dzwonowej.
W porządku. Teraz, kilka rzeczy - pewne rzeczy związane z formatowaniem - zrobimy tutaj: Po pierwsze, kliknij dwukrotnie w dół na skali i wygląda na to, że nasza najniższa liczba prawdopodobnie wynosi około 50 - więc ustaw minimum 50 - a nasza największa liczba - nasza największa liczba - to 88 - więc ustawię maksimum na 90. W porządku. A teraz musimy oznaczyć te punkty. Jeśli korzystasz z programu Excel 2013 lub nowszego, jest to łatwe; ale jeśli korzystasz ze starszej wersji programu Excel, będziesz musiał wrócić i użyć dodatku Chart Labeler Roba Boveya, aby etykiety punktów pochodziły z miejsca, którego nie ma na wykresie. W porządku, więc zaczynamy tutaj. Zamierzamy dodać etykiety danych, a to dodaje liczby i wyglądają okropnie. Przyjdę tutaj i powiem, że chcę Więcej opcji, Opcje etykiet,i chcę uzyskać wartość z komórek - wartość z komórek. W porządku? Więc zakres komórek jest właśnie tam, kliknij OK. Bardzo ważne jest, aby użyć wartości z komórek przed odznaczeniem wartości Y. Zaczyna dobrze wyglądać. Pozbędę się tego. Teraz cały klucz tutaj - ponieważ masz ludzi, którzy w pewnym sensie nadpisują się nawzajem - polega na próbie powiększenia wykresu tak, jak to tylko możliwe. Nie potrzebujemy tam kierowania. Czemu? Po prostu to usuń. I nadal widzę, jak Kelly i Lou, Andy i Flo są prawie w tym samym miejscu; Jared i … W porządku. Więc teraz to będzie frustrujące - te, które się pokrywają. Ale kiedy klikamy etykietę, wybieramy wszystkie etykiety, a następnie ponownie klikamy etykietę i wybieramy tylko jedną etykietę. W porządku? Więc teraz. bardzo ostrożnie. spróbuj i kliknij Andy'ego i po prostu przeciągnij Andy'ego w lewo.Wygląda na to, że Jared i Ike są razem, więc teraz, gdy jestem w trybie wyboru jednej etykiety, jest łatwiej. A potem Kelly i Lou, pociągnij ich w ten sposób. Może jest lepsze miejsce, w którym nie przeważa Lou, albo nawet, na przykład, tutaj mogę przeciągnąć go w dowolną stronę. W porządku, więc co mamy? Zaczęliśmy od zbioru danych, utworzyliśmy tabelę przestawną, obliczyliśmy średnią i odchylenie standardowe, co pozwala nam tylko obliczyć wysokość - pozycję Y dla każdego z tych wyników i, miejmy nadzieję, wysokość tych wyników doprowadzimy ludzi do ładnej krzywej dzwonu w kształcie paraboli, w ten sposób.W porządku, więc co mamy? Zaczęliśmy od zbioru danych, utworzyliśmy tabelę przestawną, obliczyliśmy średnią i odchylenie standardowe, co pozwala nam tylko obliczyć wysokość - pozycję Y dla każdego z tych wyników i, miejmy nadzieję, wysokość tych wyników doprowadzimy ludzi do ładnej krzywej dzwonu w kształcie paraboli, w ten sposób.W porządku, więc co mamy? Zaczęliśmy od zbioru danych, utworzyliśmy tabelę przestawną, obliczyliśmy średnią i odchylenie standardowe, co pozwala nam tylko obliczyć wysokość - pozycję Y dla każdego z tych wyników i, miejmy nadzieję, wysokość tych wyników doprowadzimy ludzi do ładnej krzywej dzwonu w kształcie paraboli, w ten sposób.
Uwielbiam to pytanie od Jimmy'ego, tego pytania nie ma w tej książce, ale będzie następnym razem, gdy napiszę tę książkę. Muszę to dodać - to fajna prośba i fajna mała sztuczka. Krzywe dzwonowe są bardzo popularne w programie Excel.
Ale sprawdź moją książkę LIVe, The 54 Greatest Excel Tips of All Time.
W porządku, podsumowanie tego odcinka: Jimmy z Huntsville chce ustawić ludzi na krzywej dzwonka. Więc używamy tabeli przestawnej, aby obliczyć średni wynik, sortujemy tabele przestawne według wyników - ułożonych od góry do dołu - pozbywamy się sumy końcowej na dole - będą to zasadniczo wartości X - a następnie z boku obliczyć średnią i odchylenie standardowe tych wyników i użyć formuł, aby skopiować dane z tabeli przestawnej do nowego zakresu, ponieważ nie można mieć wykresu XY, który przecina się z tabelą przestawną. Oblicz wartość y dla każdej osoby z = ROZKŁ.NORMALNY ich wartości x, średniej, odchylenia standardowego, przecinka FAŁSZ; utwórz wykres punktowy XY z gładkimi liniami - jeśli korzystasz z programu Excel 2010 lub starszego, będziesz używać dodatku Chart Labeler Rona Boveya. Chcę, żebyś to wygooglował, ponieważna wypadek, gdyby Rob zmienił swój adres URL, nie chcę tutaj niewłaściwego adresu URL. W programie Excel 2013 miałem Etykiety danych, Z komórek, określ nazwy, a następnie pewne korekty - zmieniam skalę na dole, zmieniam je na i Max, a następnie przesuwam etykiety, które się nakładają.
Aby pobrać skoroszyt z dzisiejszego wideo, użyj adresu URL w opisie YouTube. Chcę podziękować Jimmy'emu za to niesamowite pytanie w Huntsville i chcę podziękować za zatrzymanie się. Do zobaczenia następnym razem z kolejnym netcastem od.
Pobierz plik Excel
Aby pobrać plik Excela: place-people-on-bell-curve.xlsx
Dziękuję Jimmy'emu z Huntsville za dzisiejsze pytanie!
Myśl dnia Excela
Poprosiłem moich znajomych z programu Excel Master o radę dotyczącą programu Excel. Dzisiejsza myśl do rozważenia:
„Jeśli ustawiłeś program Excel w trybie ręcznego przeliczania w zeszłym miesiącu, nadszedł czas na Power Pivot (nigdy więcej nie będziesz potrzebować trybu ręcznego)”
Rob Collie
