Analiza danych Excel - wizualizacja danych

Raporty z analizy danych można wyświetlać na wiele sposobów w programie Excel. Jeśli jednak wyniki analizy danych można zwizualizować w postaci wykresów, które podkreślają istotne punkty danych, odbiorcy mogą szybko pojąć, co chcesz przedstawić w danych. Pozostawia również dobry wpływ na styl prezentacji.

W tym rozdziale dowiesz się, jak korzystać z wykresów programu Excel i funkcji formatowania programu Excel na wykresach, które pozwalają z naciskiem na przedstawienie wyników analizy danych.

Wizualizacja danych za pomocą wykresów

W programie Excel wykresy służą do graficznej reprezentacji dowolnego zestawu danych. Wykres to wizualna reprezentacja danych, w której dane są reprezentowane przez symbole, takie jak słupki na wykresie słupkowym lub linie na wykresie liniowym. Program Excel udostępnia wiele typów wykresów i możesz wybrać taki, który pasuje do Twoich danych, lub skorzystać z opcji Wykresy zalecane w programie Excel, aby wyświetlić wykresy dostosowane do Twoich danych i wybrać jeden z nich.

Więcej informacji na temat typów wykresów zawiera samouczek dotyczący wykresów programu Excel.

W tym rozdziale poznasz różne techniki, których możesz użyć z wykresami programu Excel, aby skuteczniej uwydatnić wyniki analizy danych.

Tworzenie wykresów kombinacji

Załóżmy, że masz docelowe i rzeczywiste zyski za rok podatkowy 2015–2016 uzyskane z różnych regionów.

Dla tych wyników utworzymy grupowany wykres kolumnowy.

Jak widać, trudno jest szybko wizualizować porównanie między celami a rzeczywistymi na tym wykresie. Nie ma to prawdziwego wpływu na Twoje wyniki.

Lepszym sposobem na rozróżnienie dwóch typów danych w celu porównania wartości jest użycie wykresów kombinacji. W programie Excel 2013 i wersjach powyżej możesz używać wykresów kombi w tym samym celu.

Użyj pionowych kolumn dla wartości docelowych i linii ze znacznikami dla rzeczywistych wartości.

• Kliknij kartę PROJEKT w zakładce NARZĘDZIA DO WYKRESÓW na Wstążce.
• Kliknij Zmień typ wykresu w grupie Typ. Zostanie wyświetlone okno dialogowe Zmień typ wykresu.

• Kliknij opcję Combo.

• Zmień typ wykresu dla serii Rzeczywisty na Linia ze znacznikami. Podgląd pojawi się w obszarze Kombinacja niestandardowa.

• Kliknij OK.

Zostanie wyświetlony Twój niestandardowy wykres kombinacji.

Jak widać na wykresie, wartości docelowe znajdują się w kolumnach, a wartości rzeczywiste są zaznaczone wzdłuż linii. Wizualizacja danych stała się lepsza, ponieważ pokazuje również trend wyników.

Jednak ten typ reprezentacji nie działa dobrze, gdy zakresy danych dwóch wartości danych znacznie się różnią.

Tworzenie wykresu kombi z osią pomocniczą

Załóżmy, że dysponujesz danymi dotyczącymi liczby wysłanych jednostek produktu oraz rzeczywistych zysków za rok obrotowy 2015–2016 uzyskanych z różnych regionów.

Jeśli użyjesz tego samego wykresu kombinacji co poprzednio, otrzymasz:

Na wykresie dane No. of Units nie jest widoczny, ponieważ zakresy danych znacznie się różnią.

W takich przypadkach można utworzyć wykres kombinowany z osią pomocniczą, tak aby na osi głównej był wyświetlany jeden zakres, a na osi pomocniczej drugi.

• Kliknij kartę WSTAW.
• Kliknij opcję Combo w grupie Wykresy.
• Kliknij opcję Utwórz niestandardowy wykres kombi z listy rozwijanej.

Pojawi się okno dialogowe Wstaw wykres z podświetlonym Combo.

Jako Typ wykresu wybierz -

• Wiersz ze znacznikami liczby jednostek serii

• Kolumna grupowana dla rzeczywistych zysków serii

• Zaznacz pole Oś pomocnicza po prawej stronie numeru serii i kliknij OK.

Podgląd wykresu pojawi się w obszarze Kombinacja niestandardowa.

Twój wykres kombi pojawia się z osią drugorzędną.

Możesz obserwować wartości Rzeczywistych zysków na osi głównej i wartości liczby jednostek na osi pomocniczej.

Istotna obserwacja na powyższym wykresie dotyczy kwartału 3, w którym liczba sprzedanych jednostek jest większa, ale rzeczywiste zyski są mniejsze. Prawdopodobnie można to przypisać kosztom promocji, które zostały poniesione w celu zwiększenia sprzedaży. Sytuacja uległa poprawie w czwartym kwartale, przy niewielkim spadku sprzedaży i znacznym wzroście osiągniętych Rzeczywistych Zysków.

Seria rozróżniająca i oś kategorii

Załóżmy, że chcesz przewidzieć rzeczywiste zyski osiągnięte w latach 2013-2016.

Utwórz kolumnę klastrową dla tych danych.

Jak widać, wizualizacja danych nie jest skuteczna, ponieważ lata nie są wyświetlane. Możesz temu zaradzić, zmieniając rok na kategorię.

Usuń rok nagłówka z zakresu danych.

Teraz rok jest traktowany jako kategoria, a nie seria. Twój wykres wygląda następująco -

Elementy wykresów i style wykresów

Elementy wykresu zapewniają więcej opisów wykresów, pomagając w ten sposób w bardziej znaczący sposób wizualizować dane.

• Kliknij wykres

Trzy przyciski pojawiają się obok prawego górnego rogu wykresu -

• 
  Elementy wykresu
• 
  Style wykresów
• 
  Filtry wykresów

Aby uzyskać szczegółowe wyjaśnienie, zapoznaj się z samouczkiem dotyczącym wykresów programu Excel.

• Kliknij opcję Elementy wykresu.
• Kliknij Etykiety danych.

• Kliknij Style wykresów
• Wybierz styl i kolor, który odpowiada Twoim danym.

Możesz użyć Trendline do graficznego wyświetlania trendów w danych. Możesz rozszerzyć linię trendu na wykresie poza rzeczywiste dane, aby przewidzieć przyszłe wartości.

Etykiety danych

Program Excel 2013 i nowsze wersje zapewniają różne opcje wyświetlania etykiet danych. Możesz wybrać jedną etykietę danych, sformatować ją tak, jak chcesz, a następnie użyć Clone Current Label, aby skopiować formatowanie do pozostałych etykiet danych na wykresie.

Etykiety danych na wykresie mogą mieć różne kształty i rozmiary.

Możliwe jest również wyświetlenie zawartości komórki jako części etykiety danych z polem Wstaw etykietę danych.

Szybki układ

Możesz użyć Szybkiego układu, aby szybko zmienić ogólny układ wykresu, wybierając jedną z wstępnie zdefiniowanych opcji układu.

• Kliknij wykres.
• Kliknij kartę PROJEKT w sekcji NARZĘDZIA DO WYKRESU.
• Kliknij opcję Szybki układ.

Zostaną wyświetlone różne możliwe układy. W miarę poruszania się po opcjach układu układ wykresu zmienia się na tę konkretną opcję.

Wybierz układ, który Ci się podoba. Wykres zostanie wyświetlony w wybranym układzie.

Korzystanie z obrazków na wykresach kolumnowych

Możesz położyć większy nacisk na prezentację danych, używając obrazu zamiast kolumn.

• Kliknij kolumnę na wykresie kolumnowym.

• W Formatuj serie danych kliknij Wypełnij.

• Wybierz Obraz.

• W obszarze Wstaw obraz z podaj nazwę pliku lub opcjonalnie schowka, jeśli wcześniej skopiowałeś obraz.

Obraz, który wybrałeś, pojawi się w miejscu kolumn na wykresie.

Wykres pasmowy

Konieczne może być przedstawienie wyników ankiet klientów dotyczących produktu z różnych regionów. Do tego celu nadaje się Band Chart. Wykres pasmowy to wykres liniowy z dodanym zacienionym obszarem do wyświetlania górnych i dolnych granic grup danych.

Załóżmy, że wyniki ankiety wśród klientów z regionów wschodnich i zachodnich, według miesiąca są -

Tutaj w danych <50% jest niskie, 50% - 80% to średnie, a> 80% to wysokie.

Za pomocą wykresu pasmowego możesz wyświetlić wyniki ankiety w następujący sposób -

Utwórz wykres liniowy na podstawie swoich danych.

Zmień typ wykresu na -

• Seria wschodnia i zachodnia do linii ze znacznikami.
• Seria niska, średnia i wysoka do kolumny piętrowej.

Twój wykres wygląda następująco.

• Kliknij jedną z kolumn.
• Zmień szerokość przerwy na 0% w Formatuj serie danych.

Zamiast kolumn otrzymasz pasma.

Aby wykres był bardziej reprezentacyjny -

• Dodaj tytuł wykresu.
• Dostosuj zakres osi pionowej.
• Zmień kolory pasków na zielono-żółto-czerwone.
• Dodaj etykiety do pasm.

Ostatecznym wynikiem jest wykres pasmowy z określonymi granicami i wynikami ankiety przedstawionymi w pasmach. Z wykresu można szybko i jednoznacznie wywnioskować, że o ile wyniki ankiety dla regionu zachodniego są zadowalające, o tyle dla regionu wschodniego odnotowano w ostatnim kwartale spadek i wymagają uwagi.

Wykres termometru

Kiedy musisz przedstawić wartość docelową i rzeczywistą, możesz łatwo utworzyć wykres termometru w programie Excel, który wyraźnie pokazuje te wartości.

Wykres termometru umożliwia wyświetlanie danych w następujący sposób -

Uporządkuj swoje dane, jak pokazano poniżej -

• Wybierz dane.
• Utwórz wykres kolumnowy grupowany.

Jak widać, kolumna po prawej stronie jest celem.

• Kliknij kolumnę na wykresie.
• Kliknij opcję Przełącz wiersz / kolumnę na Wstążce.

• Kliknij prawym przyciskiem myszy kolumnę docelową.
• Kliknij Formatuj serie danych.
• Kliknij Oś pomocniczą.

Jak zauważysz, oś główna i oś pomocnicza mają różne zakresy.

• Kliknij prawym przyciskiem myszy oś główną.
• W opcjach Formatuj oś, w obszarze Granice, wpisz 0 dla Minimum i 1 dla Maksimum.
• Powtórz to samo dla osi pomocniczej.

Zarówno oś główna, jak i oś pomocnicza zostaną ustawione na 0% - 100%. Kolumna docelowa ukrywa kolumnę rzeczywistą.

• Kliknij prawym przyciskiem myszy widoczną kolumnę (Cel)
• W polu Formatuj serie danych wybierz
  • Brak wypełnienia dla FILL
  • Linia ciągła dla GRANICA
  • Niebieski kolor

• W elementach wykresu usuń zaznaczenie
  • Oś → Główna pozioma
  • Oś → Drugorzędna pionowa
  • Gridlines
  • Tytuł wykresu
• Na wykresie kliknij prawym przyciskiem myszy Główna oś pionowa
• W opcjach formatowania osi kliknij ZNACZNIKI
• W polu Typ główny wybierz opcję Wewnątrz

• Kliknij prawym przyciskiem myszy obszar wykresu.
• W opcjach Formatuj obszar wykresu wybierz
  • Brak wypełnienia dla FILL
  • Brak linii dla BORDER

Zmień rozmiar obszaru wykresu, aby uzyskać kształt termometru.

Otrzymałeś wykres termometru z pokazaną wartością rzeczywistą w porównaniu z wartością docelową. Możesz uczynić ten wykres termometru bardziej imponującym, stosując pewne formatowanie.

• Wstaw prostokątny kształt nakładający się na niebieską prostokątną część na wykresie.
• W opcjach formatu kształtu wybierz -
  • Wypełnienie gradientowe dla FILL
  • Liniowy dla typu
  • 180 ⁰ dla Angle
• Ustaw gradienty zatrzymuje się na 0%, 50% i 100%.
• Aby Gradient zatrzymał się na 0% i 100%, wybierz kolor czarny.
• W przypadku zatrzymania gradientu na 50% wybierz kolor biały.

• Wstaw owalny kształt na dole.
• Sformatuj kształt przy użyciu tych samych opcji.

Rezultatem jest wykres termometru, od którego zaczęliśmy.

Wykres Gantta

Wykres Gantta to wykres, na którym seria poziomych linii przedstawia ilość pracy wykonanej w określonych okresach w stosunku do ilości pracy zaplanowanej na te okresy.

W programie Excel można utworzyć wykres Gantta, dostosowując typ wykresu słupkowego skumulowanego, tak aby przedstawiał zadania, czas ich trwania i hierarchię. Wykres Gantta w programie Excel zwykle używa dni jako jednostki czasu wzdłuż osi poziomej.

Rozważ następujące dane, w których kolumna -

• Zadanie reprezentuje zadania w projekcie
• Początek oznacza liczbę dni od daty rozpoczęcia projektu
• Czas trwania reprezentuje czas trwania Zadania

Zwróć uwagę, że Początek dowolnego zadania to Początek poprzedniego zadania + czas trwania. Dzieje się tak, gdy zadania są hierarchiczne.

• Wybierz dane.
• Utwórz skumulowany wykres słupkowy.

• Kliknij prawym przyciskiem myszy Start Series.
• W opcjach Formatuj serie danych wybierz Bez wypełnienia.

• Kliknij prawym przyciskiem myszy Oś kategorii.
• W opcjach Format Axis wybierz Kategorie w odwrotnej kolejności.

• W elementach wykresu usuń zaznaczenie
  • Legend
  • Gridlines
• Sformatuj oś poziomą na
  • Dostosuj zakres
  • Duże znaczniki kleszczy w odstępach 5-dniowych
  • Drobne zaznaczenia w odstępach 1-dniowych
• Sformatuj serię danych, aby wyglądała imponująco
• Podaj tytuł wykresu

Wykres wodospadu

Wykres kaskadowy to jedno z najpopularniejszych narzędzi wizualizacyjnych stosowanych w małych i dużych firmach. Wykresy kaskadowe są idealne do pokazania, w jaki sposób osiągnąłeś wartość netto, taką jak dochód netto, poprzez rozbicie skumulowanego wpływu dodatnich i ujemnych składek.

Excel 2016 zapewnia typ wykresu kaskadowego. Jeśli korzystasz z wcześniejszych wersji programu Excel, nadal możesz utworzyć wykres kaskadowy za pomocą skumulowanego wykresu kolumnowego.

Kolumny są oznaczone kolorami, dzięki czemu można szybko odróżnić liczby dodatnie od ujemnych. Kolumny wartości początkowej i końcowej zaczynają się na osi poziomej, podczas gdy wartości pośrednie są kolumnami pływającymi. Ze względu na ten wygląd wykresy kaskadowe są również nazywane wykresami mostowymi.

Rozważ następujące dane.

• Przygotuj dane do wykresu kaskadowego

• Upewnij się, że kolumna Przepływ gotówki netto znajduje się po lewej stronie kolumny Miesiące (dzieje się tak, ponieważ nie uwzględnisz tej kolumny podczas tworzenia wykresu)

• Dodaj 2 kolumny - Zwiększ i Zmniejsz odpowiednio dla dodatnich i ujemnych przepływów pieniężnych

• Dodaj kolumnę Początek - pierwsza kolumna na wykresie z wartością początkową w Przepływie pieniężnym netto

• Dodaj kolumnę Koniec - ostatnia kolumna na wykresie z wartością końcową w Przepływie pieniężnym netto

• Dodaj kolumnę Float - która obsługuje kolumny pośrednie

• Oblicz wartości dla tych kolumn w następujący sposób

• W kolumnie Float wstaw wiersz na początku i na końcu. Umieść n dowolną wartość 50000. To tylko po to, aby mieć trochę miejsca po lewej i prawej stronie wykresu

Dane będą następujące.

• Wybierz komórki C2: H18 (Wyklucz kolumnę Przepływ gotówki netto)
• Utwórz skumulowany wykres kolumnowy

• Kliknij prawym przyciskiem myszy Float Series.
• Kliknij opcję Formatuj serie danych.
• W opcjach Formatuj serie danych wybierz Bez wypełnienia.

• Kliknij prawym przyciskiem myszy serię ujemną.
• Wybierz Kolor wypełnienia jako czerwony.

• Kliknij prawym przyciskiem myszy serię pozytywną.
• Wybierz Kolor wypełnienia jako zielony.

• Kliknij prawym przyciskiem myszy Start Series.
• Wybierz kolor wypełnienia jako szary.
• Kliknij prawym przyciskiem myszy Koniec serii.
• Wybierz kolor wypełnienia jako szary.
• Usuń legendę.

• Kliknij prawym przyciskiem dowolną serię
• W opcjach Formatuj serie danych wybierz Szerokość przerwy jako 10% w obszarze Opcje serii

Podaj tytuł wykresu. Zostanie wyświetlony wykres wodospadu.

Wykresy przebiegu w czasie

Wykresy przebiegu w czasie to maleńkie wykresy umieszczone w pojedynczych komórkach, z których każda reprezentuje wiersz wybranych danych. Umożliwiają szybkie dostrzeżenie trendów.

Wykresy przebiegu w czasie można dodawać za pomocą narzędzia Szybka analiza.

• Wybierz dane, do których chcesz dodać wykresy przebiegu w czasie.
• Pozostaw pustą kolumnę po prawej stronie danych dla wykresów przebiegu w czasie.

Przycisk Szybka analiza

pojawi się w prawym dolnym rogu wybranych danych.

• Kliknij przycisk Szybka analiza

  
  . Pojawi się pasek narzędzi szybkiej analizy z różnymi opcjami.

Kliknij SPARKLINES. Wyświetlane opcje wykresów są oparte na danych i mogą się różnić.

Kliknij Line. Wykres liniowy dla każdego wiersza jest wyświetlany w kolumnie po prawej stronie danych.

PivotCharts

Wykresy przestawne służą do graficznego podsumowywania danych i eksploracji skomplikowanych danych.

Wykres przestawny przedstawia serie danych, kategorie i osie wykresu w taki sam sposób, jak na wykresie standardowym. Ponadto zapewnia również interaktywne elementy sterujące filtrowaniem bezpośrednio na wykresie, dzięki czemu można szybko analizować podzbiór danych.

Wykresy przestawne są przydatne, gdy masz dane w ogromnej tabeli przestawnej lub wiele złożonych danych arkusza, które zawierają tekst i liczby. Wykres przestawny może pomóc w zrozumieniu tych danych.

Możesz utworzyć wykres przestawny z

• Tabela przestawna.
• Tabela danych jako autonomiczna bez tabeli przestawnej.

PivotChart from PivotTable

Aby utworzyć wykres przestawny, wykonaj kroki podane poniżej -

• Kliknij tabelę przestawną.
• Kliknij ANALIZUJ w sekcji NARZĘDZIA TABEL PRZEWIJANYCH na Wstążce.
• Kliknij PivotChart. Zostanie wyświetlone okno dialogowe Wstaw wykres.

Wybierz Kolumna grupowana z opcji Kolumna.

Kliknij OK. Zostanie wyświetlony wykres przestawny.

Wykres przestawny ma trzy filtry - Region, Sprzedawca i Miesiąc.

• Kliknij opcję Region Filter Control. Pojawi się pole wyszukiwania z listą wszystkich regionów. Pola wyboru pojawiają się obok Regiony.

• Wybierz opcje Wschód i Południe.

Przefiltrowane dane pojawiają się zarówno na wykresie przestawnym, jak i w tabeli przestawnej.

PivotChart bez tabeli przestawnej

Możesz utworzyć autonomiczny wykres przestawny bez tworzenia tabeli przestawnej.

• Kliknij tabelę danych.
• Kliknij kartę Wstaw.
• Kliknij opcję Wykres przestawny w grupie Wykresy. Zostanie wyświetlone okno Utwórz wykres przestawny.
• Wybierz tabelę / zakres.
• Wybierz lokalizację, w której chcesz umieścić wykres przestawny.

Możesz wybrać komórkę w samym istniejącym arkuszu lub w nowym arkuszu. Kliknij OK.

Pusty wykres przestawny i pusta tabela przestawna pojawią się wraz z listą pól wykresu przestawnego w celu utworzenia wykresu przestawnego.

• Wybierz pola, które mają zostać dodane do wykresu przestawnego

• Ułóż pola, przeciągając je do FILTRÓW, LEGEND (SERII), OSI (KATEGORIE) i WARTOŚCI

• Użyj elementów sterujących filtru na wykresie przestawnym, aby wybrać dane do umieszczenia na wykresie przestawnym

Excel automatycznie utworzy połączoną tabelę przestawną.