Wykresy skumulowane w Excelu

W programie Microsoft Excel ma miejsce pewna niejasność terminologiczna, niezgodność z używanymi w statystyce pojęciami. Warto wyraźnie pokazać problem i wyjaśnić, jak sobie z nim poradzić (w artykule mowa o wersji 2007).

Przedstawiając dane statystyczne, posługujemy się nierzadko wartościami skumulowanymi. To cenna metoda przedstawiania danych, zwłaszcza na wykresach, obrazuje bowiem, jaka część populacji znajduje się poniżej pewnej wartości, a jaka powyżej. Prosty przykład: na roku akademickim na jakimś wydziale z 80 studentów płci męskiej 14 ma do 165 cm wzrostu, 28 ma do 170 cm, 45 do 175 cm, 68 do 180 cm, 76 do 185, a 80 do 190 cm. Chodzi o wartości łączne, a nie w pojedynczych przedziałach.

Co oczywiste, gdybyśmy przedstawili dane w częstościach lub procentach, górna wartość wynosiłaby nie 80, lecz odpowiednio 1 lub 100% (dane się kumulują).

Naturalnie dane wyjściowe przedstawiane są przede wszystkim w postaci statystycznego szeregu przedziałowego - to najczęściej spotykana forma tabelarycznej prezentacji danych. Pokażmy teraz przykład spółdzielni mieszkaniowej, w której mieszkania mają różne powierzchnie i chcemy się dowiedzieć, jak wygląda rozkład danych, jaka jest liczebność mieszkań o powierzchni w danym przedziale.

Tabela pokazuje dokładne liczby, natomiast wykres wizualizuje poglądowo liczebność poszczególnych przedziałów powierzchni i relacje tych liczb. Widać, że najczęściej występują mieszkania o powierzchni w granicach 60-80 metrów kwadratowych.

Wykres sporządziliśmy bardzo prosto, sięgając do pierwszego wariantu typu kolumnowego, jako najczęściej chyba stosowanego w praktyce (i jeszcze “dosmaczając” go nieco graficznie na kontekstowej karcie Projektowanie).

I tu dochodzimy do sedna sprawy. Staje oto przed nami zadanie zbadania i pokazania na wykresie, ile mieszkań ma do 60 metrów kw., a ile do 120. Już na pierwszy rzut oka widać, że trzeba dodać do siebie liczebności kilku przedziałów - w pierwszym przypadku dwóch poniżej/do 60 metrów, a w drugim pięciu poniżej/do 120 metrów.

Zaznaczamy więc myszką wykres i na karcie Projektowanie, w grupie Typ, klikamy przycisk Zmień typ wykresu. Na ekranie pojawia się okno Zmienianie typu wykresu. Na liście wykresów na drugim miejscu jest wariant skumulowany kolumnowy.

Zaznaczamy wariant, klikamy OK i… nic się nie dzieje. Wykres pozostaje niezmieniony. Dlaczego?

Ulegliśmy tutaj nieporozumieniu terminologicznemu - w języku Excela wykres skumulowany jest czym innym niż w języku statystyki. Pokażmy to znowu na przykładzie.

Dodajemy drugą spółdzielnię i badamy rozkład powierzchni jej mieszkań. Dane widzimy w kolumnie C. Gdy teraz zechcemy zwizualizować rozkład i sięgniemy do typu kolumnowy skumulowany stanie się jasne, że kumulowany znaczy w Excelu coś innego. Excel kumuluje wartości dwóch (lub więcej) szeregów statystycznych, a nie kolejne wartości jednego szeregu. Tutaj kumuluje poziomo, “w poprzek”, a nie pionowo w kolumnie. A więc sumuje liczby mieszkań spółdzielni A i B osobno w przedziale 20-40, osobno w 40-60 itd. Na wykresie to suma niebieskiego i czerwonego prostokąta wyliczona dla poszczególnych przedziałów.

W statystyce natomiast kumulacja oznaczałaby sumy komórek w danej kolumnie (w jednym szeregu statystycznym), najpierw B2, potem B2+B3, potem B2+B3+B4 itd. W ten sposób otrzymalibyśmy tzw. dystrybuantę empiryczną - jej przykład jest na pierwszej ilustracji.

Jak więc poradzić sobie z problemem?

Musimy utworzyć w tabeli dodatkową kolumnę zliczającą kolejne takie sumy. Zadanie jest banalne.

• Ustaw kursor w pierwszej komórce kolumny liczebności skumulowanych (tutaj C2) i wpisz adres pierwszej komórki z liczbą mieszkań, np. =b3.
• Ustaw kursor w kolejnej komórce kolumny liczebności skumulowanych (tutaj C3) i wpisz formułę dodającą drugą komórkę z liczbą mieszkań i pierwszą komórkę w kolumnie liczebności skumulowanych, np. =b3+c4 (na ilustracji formuła podkreślona jest żółtym kolorem).
• Ustaw kursor w trzeciej komórce kolumny liczebności skumulowanych (tutaj C4) i wpisz formułę dodającą trzecią komórkę z liczbą mieszkań i drugą komórkę w kolumnie liczebności skumulowanych, np. =b4+c3. Rozumiesz już mechanizm? Kumulujesz w ten sposób liczby z kolumny B.

Tak naprawdę wystarczy skopiować już pierwszą formułę w komórce C3, zaznaczyć blokiem wszystkie komórki w dół i wkleić formułę (Ctrl+v). Excel wypełni automatycznie komórki i natychmiast obliczy wartości.

Co nam pozostaje? Już tylko zrobienie wykresu naszej dystrybuanty empirycznej. Zaznaczamy dane z kolumny A (od A1 do A7), wciskamy klawisz Ctrl i zaznaczamy dane z kolumny C, po czym tworzymy zwykły wykres kolumnowy. Dla porządku należałoby jeszcze nazwać inaczej przedziały, gdyż chodzi już nie o wielkości w przedziałach lecz do określonej wysokości. Pomijamy to jednak w tym miejscu.

W ofercie rozmaitych firm produkujących dodatki do Excela (to potężny przemysł programistyczny) są naturalnie automaty generujące takie wykresy bez żadnych dodatkowych zabiegów. Warto się przy okazji rozejrzeć w Internecie - okazuje się, że wbudowane w Excela czy Calca wykresy, choć wystarczają w typowych zastosowaniach, to zaledwie niewielka część możliwości, jakie można uzyskać dzięki wyspecjalizowanym dodatkom (add-ins).