Wykres gęstości w Excelu i Power BI: jak porównać rozkłady lepiej niż na samym histogramie

Na niedawnym spotkaniu LIVE w społeczności I❤️DATA naszym gościem był Piotr Szulc, który podczas wykładu o rozkładach danych polecił, żeby obok histogramu pokazywać wykres gęstości. W tym artykule pokazuję, co to za wykres, jak go czytać i jak zbudować go w Excelu (na dwa sposoby) oraz w Power BI.

Nie musisz znać statystyki, bo wszystko wyjaśniam na prostym przykładzie: wzroście kobiet i mężczyzn.

Jaki problem rozwiązuje wykres gęstości?

Wyobraź sobie, że chcesz porównać dwie grupy, na przykład wzrost kobiet i mężczyzn, czas realizacji zamówień w dwóch magazynach albo wynagrodzenia w dwóch działach. Sama średnia to za mało, bo nie pokazuje, jak wartości się rozkładają, więc zwykle sięgasz po histogram.

I tu zaczynają się kłopoty:

• słupki dwóch grup nachodzą na siebie i trudno je odczytać,
• jeśli jedna grupa jest dużo większa, jej słupki przykrywają drugą grupę, choć kształt rozkładu może być podobny,
• wygląd histogramu zależy od tego, jak szerokie przedziały wybierzesz.

Wykres gęstości rozwiązuje te problemy, bo zamiast słupków pokazuje krzywą. Każda grupa ma własną krzywą, a pole pod każdą z nich jest takie samo, dzięki czemu porównujesz kształt rozkładu, a nie liczebność grup.

Co to jest wykres gęstości?

Wykres gęstości pokazuje, gdzie skupia się najwięcej obserwacji: im wyżej biegnie krzywa, tym więcej osób ma wartość w tym miejscu osi.

Najważniejsza jest jedna cecha: cała krzywa sumuje się do 1, czyli do 100%. Nie ma więc znaczenia, czy grupa liczy 300, czy 30 000 osób, bo wysokość krzywej mówi, jaka część grupy ma wartość w danym miejscu, a nie ile to osób.

Spójrzmy na przykład z naszego pliku: krzywa kobiet ma szczyt przy 165 cm i wysokość około 5,5%, co przy kroku co 1 cm oznacza w przybliżeniu, że około 5,5% kobiet ma wzrost w okolicy 165 cm. Krzywa mężczyzn ma szczyt przy 177 cm.

Skąd „suma 1”? Jeśli zsumujesz wszystkie udziały, dostaniesz 100% grupy. Na wykresie gęstości oznacza to, że pole pod krzywą jest równe 1. Gdy liczymy punkty co 1 cm, suma wartości z tabeli też wynosi około 1. To wygodna kontrola, że obliczenia są poprawne. Pokażę ją w Excelu i w Power BI.

Wykres gęstości wygładzony i niewygładzony

W praktyce spotkasz dwie wersje tego wykresu.

Niewygładzony: udział dla każdej wartości

Liczysz, jaki procent grupy ma wzrost 160 cm, 161 cm, 162 cm i tak dalej, a potem łączysz punkty linią. To w zasadzie histogram z bardzo wąskimi przedziałami, narysowany linią zamiast słupków, a w Excelu zrobisz go tabelą przestawną, bez żadnej formuły.

Wygładzony: każda obserwacja to mała górka

Wersja wygładzona działa inaczej. Wyobraź sobie, że każda osoba to garść piasku wysypana na osi w miejscu swojego wzrostu, ale piasek nie spada w jeden punkt, tylko rozsypuje się trochę na boki. Tam, gdzie wysypano dużo garści, rośnie wysoka górka, a tam, gdzie mało, górka jest niska. Krzywa gęstości to właśnie kształt całej usypanej górki.

W statystyce ta metoda nazywa się estymatorem jądrowym gęstości (ang. kernel density estimation, w skrócie KDE). Nazwy nie musisz pamiętać, ale przyda się, gdy zaczniesz szukać informacji w internecie.

O wyglądzie krzywej decyduje jedno ustawienie: szerokość pasma, czyli to, jak szeroko rozsypuje się każda garść piasku.

W naszym przykładzie pasmo wynosi 2,5 cm, a zmienisz je w Excelu w jednej komórce, w Power BI zaś suwakiem.

Uwaga do naszych danych. W przykładzie obie wersje wyglądają prawie tak samo. Dane są modelowe: to 10 000 osób na płeć, a ich rozkład jest już bardzo regularny, więc nie ma czego wygładzać. Różnicę zobaczysz na mniejszych, „nierównych” danych, na przykład na wynikach ankiety wśród 200 pracowników. Tam wersja niewygładzona będzie postrzępiona, a wygładzona pokaże ogólny kształt.

Wykres gęstości a histogram

Histogram dzieli wartości na przedziały i pokazuje, ile obserwacji wpada do każdego z nich. Jeśli chcesz sobie go przypomnieć, zajrzyj do wpisów Jak utworzyć histogram w Excelu, Jak wstawić wykres typu histogram w Excelu 2016 i 365 oraz Histogram na wykresie przestawnym.

Pytanie	Histogram	Wykres gęstości
Co pokazuje?	Liczbę lub procent obserwacji w przedziałach	Kształt rozkładu jako ciągłą krzywą
Porównanie kilku grup	Trudne, słupki nachodzą na siebie	Łatwe, krzywe można nałożyć
Grupy różnej wielkości	Większa grupa dominuje (chyba że pokazujesz procenty)	Każda krzywa sumuje się do 1, więc porównanie jest uczciwe
Od czego zależy wygląd?	Od szerokości przedziałów	Od szerokości pasma
Ryzyko	Przy złych przedziałach kształt może być mylący	Wygładzanie może ukryć nagłe skoki i luki

Ostatni wiersz tabeli jest najważniejszy, ponieważ wygładzona krzywa jest czytelna, ale potrafi „zamazać” coś, co w danych jest ważne. Dobrze widać to na przykładzie wyników matury z języka polskiego z 2013 roku.

Źródło wykresu: użytkownik u/zupka_pryk_pryk, Reddit.

Na histogramie widać lukę tuż przed progiem 30% i nagły skok tuż za nim, a wygładzona krzywa połączyłaby te miejsca w łagodne zbocze i anomalia by zniknęła. Dlatego wykres gęstości polecam obok histogramu, a nie zamiast niego: histogram pokazuje szczegóły, a wykres gęstości ogólny kształt i porównanie grup.

Więcej takich nieoczywistych rozkładów zebrałem we wpisie Ciekawe histogramy: 7 wykresów, które pokazują więcej niż średnia.
.

Nie myl też wykresu gęstości z wykresem Gaussa. Krzywa Gaussa to idealny „dzwon” narysowany ze wzoru na podstawie średniej i odchylenia standardowego. Wykres gęstości rysujesz z prawdziwych danych, więc pokaże też rozkład niesymetryczny albo z dwoma szczytami.

Dane do przykładu

W pliku do pobrania jest tabela tblDaneWzrost z trzema kolumnami: Płeć, Wzrost (co 1 cm) i Liczba wystąpień, czyli ile osób danej płci ma taki wzrost.

Dane są modelowe, czyli to ilustracja przygotowana do ćwiczenia, a nie rzeczywiste pomiary. Na każdą płeć przypada 10 000 osób.

Twoje dane mogą wyglądać inaczej, na przykład mieć jeden wiersz na jedną osobę. W takim przypadku w tabeli przestawnej zamiast sumy kolumny Liczba wystąpień użyj liczby wierszy, a w formułach dodaj kolumnę z wartością 1 dla każdego wiersza.

Sposób 1: wykres gęstości w Excelu z tabelą przestawną (niewygładzony)

To najszybsza metoda, która nie wymaga żadnej formuły.

1. Kliknij w tabelę z danymi i wybierz Wstawianie → Tabela przestawna.
2. Przeciągnij Wzrost do obszaru Wiersze, Płeć do obszaru Kolumny, a Liczba wystąpień do obszaru Wartości. Excel pokaże liczbę osób.
3. Kliknij prawym przyciskiem dowolną liczbę w tabeli i wybierz Pokaż wartości jako → % sumy kolumny.

Teraz każda kolumna, osobno dla kobiet i dla mężczyzn, sumuje się do 100%. To właśnie „suma 1” z tytułu, dzięki której krzywe obu grup są porównywalne, nawet gdyby jedna grupa była dziesięć razy większa.

4. Kliknij w tabelę przestawną i wybierz Wstawianie → Wykres warstwowy (powstanie wykres przestawny).
5. Kliknij serię danych, wybierz Formatuj serię danych → Wypełnienie i ustaw przezroczystość około 30–40%. Dzięki temu widać miejsce, w którym krzywe na siebie nachodzą.

Jeśli tabele przestawne to dla Ciebie nowość, zacznij od bezpłatnego kursu Excela. Systematycznie przechodzi przez nie kurs JUNIOR Analityk.

Sposób 2: wygładzony wykres gęstości w Excelu z formułami

Tabela przestawna nie potrafi wygładzać, dlatego wersję wygładzoną policzymy formułą.

Krok 1. Przygotuj oś i dwa parametry

Na nowym arkuszu przygotuj:

• w kolumnie A kolejne wartości wzrostu, dla których policzysz krzywą: od 140 do 210 cm co 1 cm,
• w komórce F26 szerokość pasma: 2,5,
• w komórce F27 krok osi: 1.

W komórce A4 wpisz 140, a w A5 formułę, którą skopiujesz w dół do wiersza 74 (wartość 210):

=A4+$F$27

Krok 2. Wpisz formułę gęstości

W komórce B4 (kobiety) wpisz:

=SUMA.ILOCZYNÓW((tblDaneWzrost[Płeć]="Kobieta")*tblDaneWzrost[Liczba wystąpień]*EXP(-0,5*((A4-tblDaneWzrost[Wzrost])/$F$26)^2))/(SUMA.WARUNKÓW(tblDaneWzrost[Liczba wystąpień];tblDaneWzrost[Płeć];"Kobieta")*$F$26*PIERWIASTEK(2*PI()))

W komórce C4 (mężczyźni) wpisz tę samą formułę, ale z tekstem „Mężczyzna”:

=SUMA.ILOCZYNÓW((tblDaneWzrost[Płeć]="Mężczyzna")*tblDaneWzrost[Liczba wystąpień]*EXP(-0,5*((A4-tblDaneWzrost[Wzrost])/$F$26)^2))/(SUMA.WARUNKÓW(tblDaneWzrost[Liczba wystąpień];tblDaneWzrost[Płeć];"Mężczyzna")*$F$26*PIERWIASTEK(2*PI()))

Skopiuj obie formuły w dół do wiersza 74 i ustaw format procentowy. W Excelu 365 wystarczy zatwierdzić formułę klawiszem Enter.

Co robi ta formuła? Krok po kroku

Formuła wygląda groźnie, ale robi kilka prostych rzeczy. Weźmy komórkę B4, czyli punkt 140 cm na osi.

Fragment formuły	Co robi po ludzku
(A4-tblDaneWzrost[Wzrost])/$F$26	Dla każdego wiersza danych liczy, jak daleko jego wzrost jest od punktu na osi. Odległość mierzy w „pasmach”: 5 cm przy paśmie 2,5 cm to 2 pasma.
EXP(-0,5*(…)^2)	Zamienia odległość na wagę. Osoby dokładnie w tym punkcie dostają wagę 1. Im dalej, tym waga szybciej spada do zera. To jest nasza „garść piasku”, czyli mała górka wokół każdej obserwacji.
(tblDaneWzrost[Płeć]=”Kobieta”)	Zostawia tylko wiersze kobiet. PRAWDA działa jak 1, FAŁSZ jak 0.
*tblDaneWzrost[Liczba wystąpień]	Mnoży wagę przez liczbę osób w wierszu. Wiersz z 500 osobami waży 500 razy więcej niż wiersz z jedną osobą.
SUMA.ILOCZYNÓW(…)	Sumuje wszystkie wagi. Wynik: ile „piasku” leży w tym punkcie osi.
SUMA.WARUNKÓW(…)	Liczy, ile jest wszystkich kobiet (tu 10 000).
*$F$26*PIERWIASTEK(2*PI())	Stały mnożnik (pasmo × około 2,507). Sprawia, że pojedyncza górka ma pole równe 1. Nie musisz go rozumieć. Ważne, że dzięki niemu cała krzywa sumuje się do 1.

Podsumowując: licznik mówi, ile osób jest „w pobliżu” danego wzrostu, a mianownik zamienia to na udział w całej grupie.

Krok 3. Sprawdź, czy suma wynosi 1

Pod parametrami dodaj kontrolę:

=SUMA(B4:B74)
=SUMA(C4:C74)

W naszym pliku wychodzi 0,9998 dla kobiet i 1,0000 dla mężczyzn. Kobiety nie mają równego 1, bo mały fragment krzywej zaczyna się przed 140 cm, czyli poza naszą osią. Jeśli wynik jest daleki od 1, sprawdź, czy oś obejmuje wszystkie dane i czy krok w F27 wynosi 1. Przy kroku 2 cm suma wartości wyniesie około 0,5, choć pole pod krzywą nadal będzie równe 1.

Krok 4. Wstaw wykres

Zaznacz zakres A3:C74 i wybierz Wstawianie → Wykres warstwowy. Ustaw przezroczystość wypełnienia, tak jak w sposobie pierwszym.

Teraz poeksperymentuj: zmień pasmo w komórce F26 na 1, a potem na 6. Przy małym paśmie krzywa zrobi się nierówna, a przy dużym zbyt płaska, i właśnie tak najlepiej zrozumiesz, co znaczy wygładzanie.

Wykres gęstości w Power BI

Power BI nie ma gotowego wykresu gęstości, ale zbudujemy go z dwóch małych tabel pomocniczych i jednej miary DAX. Logika jest dokładnie taka sama jak w Excelu.

Na start zaimportuj tabelę tblDaneWzrost z pliku Excel (Pobierz dane → Skoroszyt programu Excel).

Krok 1. Tabela z osią wzrostu

Wybierz Modelowanie → Nowa tabela i wpisz:

Punkty = SELECTCOLUMNS ( GENERATESERIES ( 140, 210, 1 ), "Wzrost (cm)", [Value] )

To odpowiednik kolumny A z Excela, czyli lista punktów od 140 do 210 cm co 1 cm. Nie łącz tej tabeli relacją z danymi, a dlaczego, wyjaśniam niżej.

Krok 2. Tabela z szerokością pasma

Pasmo = SELECTCOLUMNS ( GENERATESERIES ( 1, 6, 0.5 ), "Pasmo", [Value] )

W tabeli Pasmo dodaj miarę, która odczyta pasmo wybrane na fragmentatorze. Jeśli nic nie wybierzesz, użyje 2,5:

Pasmo (cm) = SELECTEDVALUE ( Pasmo[Pasmo], 2.5 )

Krok 3. Miara gęstości wygładzonej

Gęstość wygładzona =
IF (
    HASONEVALUE ( Punkty[Wzrost (cm)] ),
    VAR h = [Pasmo (cm)]
    VAR x = SELECTEDVALUE ( Punkty[Wzrost (cm)] )
    VAR n = SUM ( tblDaneWzrost[Liczba wystąpień] )
    VAR s =
        SUMX (
            tblDaneWzrost,
            tblDaneWzrost[Liczba wystąpień]
                * EXP ( -0.5 * ( ( x - tblDaneWzrost[Wzrost] ) / h ) ^ 2 )
        )
    RETURN
        DIVIDE ( s, n * h * SQRT ( 2 * PI () ) )
)

Porównaj ją z formułą z Excela:

• h to pasmo (w Excelu komórka F26),
• x to punkt na osi (w Excelu komórka A4),
• n to liczba osób w grupie (w Excelu SUMA.WARUNKÓW),
• s to suma wag (w Excelu SUMA.ILOCZYNÓW),
• na końcu dzielimy przez n × h × √(2π), tak samo jak w Excelu.

Płci nie musisz filtrować w formule. Zrobi to legenda wykresu. HASONEVALUE sprawia, że miara liczy się tylko dla pojedynczego punktu osi, a w wierszu sumy zwraca pusty wynik.

Ustaw format miary jako procent.

Krok 4. Miara niewygładzona i kontrola sumy

Odpowiednik tabeli przestawnej z „% sumy kolumny”:

Udział (niewygładzony) =
VAR liczba =
    CALCULATE (
        SUM ( tblDaneWzrost[Liczba wystąpień] ),
        TREATAS ( VALUES ( Punkty[Wzrost (cm)] ), tblDaneWzrost[Wzrost] )
    )
VAR razem =
    CALCULATE (
        SUM ( tblDaneWzrost[Liczba wystąpień] ),
        REMOVEFILTERS ( tblDaneWzrost[Wzrost] )
    )
RETURN
    DIVIDE ( liczba, razem )

TREATAS przenosi punkt z osi na kolumnę Wzrost w danych, a REMOVEFILTERS liczy wszystkie osoby z grupy niezależnie od wzrostu. Dzięki temu obie miary działają na tej samej osi i możesz je pokazać na jednym wykresie: słupki z udziałem i linię z gęstością wygładzoną. To właśnie zestawienie histogramu z wykresem gęstości.

Kontrola sumy, odpowiednik formuły SUMA z Excela:

Suma kontrolna gęstości =
SUMX ( VALUES ( Punkty[Wzrost (cm)] ), [Gęstość wygładzona] )

W tabeli z kolumną Płeć powinna pokazać około 1,000 dla każdej grupy.

Krok 5. Zbuduj wykres

1. Wstaw wykres warstwowy.
2. Oś X: Punkty[Wzrost (cm)].
3. Oś Y: miara Gęstość wygładzona.
4. Legenda: tblDaneWzrost[Płeć].

Dodaj fragmentator z kolumną Pasmo[Pasmo] i ustaw styl Pojedyncza wartość. Przesuwając suwak, zobaczysz, jak szerokość pasma zmienia kształt krzywej.

Dlaczego oś jest w osobnej tabeli?

Naturalne wydaje się wzięcie wzrostu na oś prosto z danych, ale wtedy punkt 170 cm na osi odfiltruje dane tylko do osób o wzroście 170 cm. Miara nie zobaczy sąsiadów, więc nie będzie czego wygładzać. Osobna tabela bez relacji filtruje tylko oś, dzięki czemu miara zawsze widzi wszystkie dane.

Osobna tabela ma też inne zalety:

• nie ma luk: jeśli w danych nikt nie ma 151 cm, oś i tak będzie miała ten punkt,
• pokazuje „ogony”: krzywa może wyjść trochę poza najmniejszą i największą wartość w danych,
• działa z surowymi danymi: przy jednym wierszu na osobę i wzroście typu 172,4 cm oś z danych miałaby setki nierównych punktów,
• daje równy krok: suma wartości wynosi 1 tylko przy stałym kroku 1 cm.

Jeśli chcesz lepiej rozumieć, jak filtry działają w DAX (CALCULATE, REMOVEFILTERS, TREATAS), to temat, który szczegółowo omawiam w kursie POWER PRO Analityk. Dla zespołów raportowych prowadzę też szkolenie Power BI dla firm, które dopasowuję do danych i raportów zespołu.

Ograniczenia: na co uważać

• Wysokość krzywej to nie liczba osób. To udział w grupie. Przy kroku 1 cm możesz ją czytać jako przybliżony procent osób w przedziale o szerokości 1 cm.
• Szerokość pasma zmienia obraz. Zanim pokażesz wykres innym, sprawdź kilka wartości pasma. Jeśli wniosek zmienia się przy niewielkiej zmianie pasma, nie jest pewny.
• Wygładzanie ukrywa skoki i luki. Progi, limity i zaokrąglenia (jak przy maturze) zobaczysz na histogramie, a nie na krzywej. Dlatego pokazuj oba wykresy.
• Krzywa wychodzi poza dane. Może pokazać małe wartości tam, gdzie nie ma żadnej obserwacji, a nawet poniżej zera. Uważaj na dane, które mają naturalną granicę, na przykład czas realizacji, wiek albo kwoty.
• Mała próba wygląda zbyt pewnie. Gładka krzywa z 30 obserwacji sprawia wrażenie solidnej wiedzy, choć danych jest bardzo mało. Podawaj liczebność grup.

Podsumowanie

Wykres gęstości nie zastępuje histogramu, tylko go uzupełnia. Histogram pokazuje szczegóły i anomalie, a wykres gęstości ogólny kształt rozkładu i pozwala uczciwie porównać grupy różnej wielkości, bo każda krzywa sumuje się do 1.

W Excelu wersję niewygładzoną zrobisz tabelą przestawną z opcją „% sumy kolumny”, a wygładzoną policzysz jedną formułą z SUMA.ILOCZYNÓW. W Power BI wystarczą natomiast tabela z osią, tabela z pasmem i jedna miara DAX.

Ten artykuł powstał po spotkaniu LIVE z Piotrem Szulcem. Takie spotkania z ekspertami, wyzwania na prawdziwych danych i społeczność analityków znajdziesz w programie I❤️DATA.

Najczęstsze pytania

Czym różni się wykres gęstości od histogramu?

Histogram pokazuje liczbę lub procent obserwacji w przedziałach w postaci słupków, a wykres gęstości pokazuje kształt rozkładu jako krzywą, której pole wynosi 1. Dzięki temu znacznie łatwiej porównać kilka grup na jednym wykresie.

Czy Excel ma wbudowany wykres gęstości?

Nie, ale możesz go zbudować samodzielnie. Wersję niewygładzoną zrobisz tabelą przestawną z opcją „Pokaż wartości jako → % sumy kolumny”, a wygładzoną formułą z funkcjami SUMA.ILOCZYNÓW i EXP oraz wykresem warstwowym.

Jak dobrać szerokość pasma?

Zacznij od wartości, przy której krzywa nie jest ani poszarpana, ani zupełnie płaska, a potem sprawdź kilka mniejszych i większych ustawień. W przykładzie ze wzrostem dobrze sprawdza się 2–3 cm.

Dlaczego suma wartości gęstości nie wynosi dokładnie 1?

Najczęściej dzieje się tak dlatego, że oś nie obejmuje całej krzywej i fragment „ogona” leży poza zakresem. Drugi możliwy powód to krok osi inny niż 1: przy kroku 2 cm suma wartości wynosi około 0,5, choć pole pod krzywą nadal jest równe 1.

Czy w Power BI da się zrobić wykres gęstości bez Pythona i R?

Tak. Wystarczy tabela z osią utworzona przez GENERATESERIES i miara DAX opisana w tym artykule, a całość działa na zwykłym wykresie warstwowym.

Pobierz pliki

Pobierz plik Excel z danymi, tabelą przestawną i wykresem wygładzonym oraz plik Power BI z gotowymi miarami.