Jak obiektywnie przedstawić wyniki ankiet? Jednym ze sposobów jest pokazanie nie tylko średniej, ale też zakresu w którym padały odpowiedzi. Taki zakres możemy narysować w programie Excel za pomocą słupków, błędów, wykorzystanych już choćby w przypadku wykresu kolumnowego dla finansistów.

Przygotowanie danych

W arkuszu znajdują się dane w postaci liczb w kolejnych kolumnach. Są to punkty, w skali od 1 do 5, które ankietowani dali w odpowiedzi na kolejne pytania (kolumny to kolejne zadawane pytania). Pierwszy wykres, który zbuduję, będzie zawierał oznaczenie, jaka największa i najniższa ocena została podana w odpowiedzi na pytanie. Wyznaczę również średnią dla każdego pytania. Na wykresie umieszczę również średnią „globalną” – czyli średnią z odpowiedzi na wszystkie pytania.

W pierwszym kroku wyliczę średnią globalną w wierszu 16 (poniżej odpowiedzi) wpiszę formułę w zakresie D16:J16 (posłuży później do wykreślenia pionowej linii na wykresie ze średnią).

Następnie w wierszu 16 wyliczę średnią dla każdej kolumny – czyli średnią ocen dla każdego z pytań.

A w kolejnych wierszach odpowiednio, dla kolejnych kolumn minimum i maksimum. W wierszu 19 umieszczam jedynki (posłużą do stworzenia serii danych dla których przypiszę jako etykiety nazwy  pytań). W  ostatnim wierszu czyli 20 wpisuje wartości osi y. Kolejne wartości jakie pojawią się na osi (od 1 do 7).

Budowa wykresu punktowego typu dot plot w Excelu

Na wykresie chciałbym pokazać oś pionową prezentującą globalną średnią, dla każdej z odpowiedzi, minimum, średnią oraz maksimum dla każdego z pytań.

Zacznę od umieszczenia średniej globalnej jako pierwszej serii na wykresie. Zaznaczam wiersz ze średnią.

Przechodzę do Wstawianie > Wykresy > Wyświetl wszystkie wykresy > Wszystkie wykresy > Punktowy.

Następnie modyfikuję wstawiony wykres, wartości osi y pokazuję w odwrotnej kolejności > Formatowanie Osi > Opcje Osi > Opcje Osi > Wartości w kolejności odwrotnej.

Do widocznych na wykresie punktów, dołączę słupki błędów czyli odchylenie średniej od maksymalnych i minimalnych wartości punktów przydzielonych w danym pytaniu. Na początek obliczę różnicę : max – średnia oraz średnia – min. W wierszu 22 oraz 23.

Aby wstawić słupki błędów aktywuję obszar wykresu > wybieram znak „+” obok > zaznaczam słupki błędów > Więcej opcji (by uruchomić widoczne po prawej stronie menu formatowania słupków).

Wokół punktów na wykresie widać pionowe i poziome linie, są to zarówno pionowe i poziome słupki błędów. Pionowych (słupki błędów osi Y)  pozbędę się zaznaczając jeden z nich > prawy przycisk myszy > usuń.

Poziome będę formatować. Zaznaczam słupki błędów osi  X > Formatowanie słupka błędów > Opcje słupka błędu > Wielkość błędu niestandardowa > zaznaczam > Określ wartość. W tym miejscu wybiorę wcześniej obliczone różnice między średnią a minimum i maksimum.

Dla wartości dodatnich wybieram wartości z wiersza 22 czyli różnica maksimum i średniej. W ujemnej wartości dane z wiersza numer 23.

Tak na wykresie prezentują się słupki błędów.

Na wykresie chciałbym umieścić jeszcze serię z tajemniczymi jedynkami zaznaczam obszar wykresu > prawy przycisk myszy > Zaznacz dane > Dodaj > jako wartości osi Y wybieram te z wiersza numer 20 a wartości osi X to jedynki z wiersza 19. Na wykresie pojawią się dodatkowe punkty, ale mają mi one posłużyć jedynie jako miejsce przypisania do każdego z „wierszy” wykresu Pytań jakiego dotyczy.

W tym celu :

– początek osi X ustawiam by rozpoczynał się nie w „0” a w „1”

– punkty nowo umieszczonej serii ustawiam na niewidoczne – brak wypełnienia oraz brak linii

– następnie do już bezbarwnych znaczników przypiszę etykiety w postaci pytań > „+” > Etykiety danych > Więcej opcji > formatowanie etykiet danych > opcje etykiety > Etykieta zawiera wartość z komórek > wybieram zakres komórek (pytania w komórkach A27:A33). W opcjach etykiet odznaczam „Wartość Y”.

Następnie formatuję umieszczone etykiety :

– Położenie po lewej

– Odznaczam „Zawijaj tekst w kształcie”

– Zmieniam wielkość wykresu (poszerzam cały wykres, obszar kreślenia zmniejszam)
– dla osi Y wyłączam etykiety wartości.

– Dla osi X maksimum ustawiam na „5”

Na wykresie umieszczę jeszcze średnią globalną w postaci linii prostej. W tym celu w obszarze kreślenia wybieram prawy przycisk myszy > Zaznacz dane > Dodaj > jako wartości osi X średnią z wiersza numer 15 (D15:J15), jako wartości osi Y wartości z wiersza 20 (D20:J20) > OK. Domyślnie na wykresie seria zostanie pokazana w postaci punktów. Chciałbym widzieć ją w postaci linii, więc wybieram Narzędzia wykresów > Projektowanie > Typ > Zmień typ wykresu > Wszystkie wykresy > Kombi > Dla nowej serii wybieram typ Punktowy z prostymi liniami.

Wykres prezentuje się następująco:

Uzupełnienie o drugi wykres kolumnowy

Zgodnie z zapowiedzią do powyższego wykresu dodam kolejny, który będzie uzupełnieniem informacji dla pierwszego. Na drugim wykresie (słupkowym) pokażę jaką część wszystkich odpowiedzi na pytania stanowią najwyższe oceny (>=4).

W komórkach M1:S14 wprowadzę formułę JEŻELI sprawdzającą czy ocena jest większa lub równa od 4. Gdy warunek będzie spełniony funkcja da wartość „1”, w przeciwnym wypadku „0”

W komórkach M15:S15 obliczam średnia dla każdej z kolumn, którą później zaprezentuję na wykresie słupkowym. Zmieniam ich format na procentowy. Poprzez Wstawianie > Wykresy wybieram wykres słupkowy grupowany. Wartości osi Y ustawiam w wartości odwrotnej, dla osi X maksimum ustawiam „1”. Dostosowuję szerokość drugiego wykresu by dopasować go do pierwszego. Pozbywam się linii siatki, obramowań a także wybranych osi. Dodaję tytuły wykresów oraz kolory do słupków danych.

Szablon Excel do pobrania

Pobierz plik z dwoma wykresami do zaprezentowania wyników ankiety i wstaw swoje dane.