Wykresy Podstawy

Przykłady opisane w tej lekcji dostępne są w arkuszu Excela: Wykresy Podstawy.xlsx tylko ich samodzielne przerobienie daje gwarancję zapamiętania tej lekcji

Przykłady dla wszystkich lekcji szkolenia Excel 2013: ExcelSzkolenie.pl Cwiczenia Excel 2013.zip

Ta lekcja może być obejrzana lub przeczytana poniżej.

Film wygląda najlepiej jeśli będzie odtwarzany w rozdzielczości 720p HD, rozdzielczość można zmienić dopiero po uruchomieniu filmu klikając na ikonie trybika która pojawi się w prawym dolnym rogu poniższego ekranu. Po kilku sekundach od zmiany obraz wyostrzy się.

Przykład 1.

(Arkusz: ‘Wykresy 1’)

Podstawy przygotowywania wykresów zostaną zaprezentowane na przykładzie danych przedstawiających sprzedaż w okresie 12 miesięcy.

Pierwszym krokiem powinno być zaznaczenie tabeli z danymi wraz z nagłówkami wierszy i kolumn.

Następnie wybieramy z karty ‘WSTAWIANIE’ wykres ‘Kolumnowy’ i pierwszy jego podtyp: ‘Kolumnowy grupowy’, wykres jest widoczny na arkuszu już podczas wybierania, ale zostanie dodany dopiero po kliknięciu odpowiedniej ikony.

Wykres, który zostanie dodany do arkusza możemy przeciągnąć w wybrane miejsce, klikając go lewym klawiszem myszy i trzymając ten klawisz wciśnięty podczas przeciągania.

Możemy też zmienić wielkość wykresu ciągnąc za dowolny róg lub środek dowolnego z boków, rogi i środki boków oznaczone są kwadracikami, po najechaniu na nie pokazuje się strzałka umożliwiająca zmianę wielkości wykresu.

Dla początkujących lub niewymagających użytkowników najwygodniej będzie skorzystać z jednego z dostępnych Stylów.

Następnie po kliknięciu na polecenie ‘KOLOR’ można wybrać paletę kolorów, w przypadku tego wykresu wszystkie słupki będą w kolorze pierwszego z kwadratów w wybranym zbiorze kolorów ponieważ na wykresie pokazywana jest tylko jedna seria danych. Gdyby było ich więcej słupki drugiej serii miałyby kolor drugiego kwadratu itd.

W tej lekcji chciałbym jednak pokazać jak mieć kontrolę nad każdym elementem wykresu, wróćmy więc do pierwotnego stylu wykresu wybierając polecenie cofnij tyle razy ile zmian wprowadziliśmy.

Najwygodniej jest cofać przytrzymując lewy klawisz Ctrl i wciskając literę Z będącą na klawiaturze tuż przy nim.

Standardowo Excel dodaje linie siatki (poziome czarne linie na rysunku powyżej), możemy je usunąć odznaczając opcje ‘Linie siatki’ widoczną po kliknięciu ikony ‘+’.

Od razu dodajmy też ‘Etykiety danych’.

Aby zmienić tytuł klikamy go dwukrotnie i wpisujemy ‘Sprzedaż 2014’.

Następnie korzystając z ikon zwiększających czcionkę oraz umożliwiających modyfikowanie parametrów tekstu dodamy pogrubienie.

Pracując z wykresami ważne jest zrozumienie czym różni się ‘Obszar kreślenia’ od ‘Obszaru wykresu’.

Obszar kreślenie to na poniższym rysunku prostokąt wewnątrz którego znajdują się słupki i etykiety wykresu. Jest on otoczony pojedynczą linią z kwadratami na rogach i środkach boków.

Obszar wykresu zawiera w sobie obszar kreślenia, mieszczą się nim także opisy osi i tytuł wykresu, on także jest prostokątem ale otoczonym podwójną linią z kwadratami na rogach i środkach boków.

Wielkość obu obszarów zmieniamy przeciągając za kwadraty na ich rogach lub bokach. Obszar kreślenia musi zawierać się w obszarze wykresu.

Proponuję teraz zwiększyć obszar kreślenia tak aby marnowało się mniej miejsca w górnej części obszaru wykresu.

Długie opisy miesięcy są wyświetlane pod kątem i nie są czytelne.

Kolejnym krokiem będzie więc użycie skróconych nazw miesięcy, odpowiednia lista znajduje się pod tabelą z danymi.

Załóżmy, że tabela powinna pozostać nie zmieniona.

Dane mogą być pobierane przez wykres z innego miejsca niż ich opisy. Klikamy raz na dowolnym słupku, co spowoduje zaznaczenie wszystkich słupków (małe kółeczka na ich rogach) oraz pokazanie skąd wykres pobiera dane, opisy oraz tytuł (kolorowe obwódki na tabeli z danymi.

Możemy taką obwódkę przeciągnąć w inne miejsce, łapiąc za jej bok (przykładowe miejsce zaznaczone czerwonym kwadratem).

Przeciągamy opisy danych poniżej na skrócone nazwy miesięcy, po przeciągnięciu gdy tylko zwolnimy lewy klawisz myszy, opisy na wykresie zostaną zmienione.

Jak widać, dane i ich opisy mogą być pobierane przez wykres z innych miejsc arkusza.

Jeśli nie chcemy aby skrócone nazwy miesięcy w komórkach B18:B29 były widoczne najprościej będzie zmienić kolor ich czcionki na biały.

Standardowo Excel nie pokazuje wykresów lub ich elementów, jeśli dane lub opisy używane przez te wykresy znajdują się w ukrytych wierszach lub kolumnach, nie możemy więc skorzystać z polecenia ‘Ukryj’ dla tych wierszy. Ustawienie to można zmienić ale zajmiemy się tym na późniejszym etapie nauki.

Po tej operacji pozostało sporo wolnego miejsca u dołu, rozciągamy obszar kreślenia aby wykorzystać to miejsce.

Kolejnym krokiem będzie rozszerzenie słupków danych. Klikamy prawym klawiszem na słupkach i wybieramy polecenie ‘Formatuj serię danych…’.

Na karcie ‘Formatowanie serii danych’ ustalamy szerokość przerwy między słupkami na 50% i zamykamy kartę klikając znak X w jej prawym górnym rogu.

Po kliknięciu lewym przyciskiem myszy na dowolną z etykiet możemy sformatować wszystkie etykiety używając poleceń dostępnych na karcie ‘NARZĘDZIA GŁÓWNE’.

Zmniejszmy ich czcionkę do 9.

Dwa pojedyncze kliknięcia w wybraną z etykiet pozwolą nam sformatować wyłącznie tą jedną wybraną etykietę.

Używa się tego aby zwrócić uwagę osób, które będą oglądały nasz wykres, na wybrany przez nas słupek.

Proponuję powiększyć czcionkę i dodać pogrubienie.

Dokładnie w ten sam sposób możemy zmienić kolor wszystkich słupków (jedno kliknięcie i wybór nowego koloru) lub tylko jednego wybranego słupka (dwa pojedyncze kliknięcia i wybór nowego koloru).

Proponuję zmienić kolor słupka z danymi za październik na ‘Niebieski, Akcent 1, jaśniejszy – 60%’.

Wybierając kolory należy unikać nasyconych barw i kontrastowych połączeń. Więcej na ten temat w lekcji ‘Profesjonalne Raporty dla Zaawansowanych’.

Dla większości celów taki wykres można by już uznać za zakończony ale my dodamy jeszcze linię trendu.

Aby dodać linię trendu klikamy na symbol +, następnie na strzałkę przy ‘Linia trendu’ i wreszcie wybieramy ‘Więcej opcji…’.

Wyświetlone zostanie okno ‘Formatowanie linii trendu’.

Dla wielu rodzajów danych, szczególnie tych porównywalnych (np. sprzedaż w kolejnych latach) najlepszy będzie trend liniowy. W naszym przykładzie bardziej zależy nam na ustaleniu profilu sprzedaży (sezonowości) więc użyjemy trendu Wielomianowego 5 stopnia.

Przechodzimy na kartę formatowania – sekcja ‘LINIA’, gdzie zmniejszamy szerokość linii trendu a z typów kreski wybieramy przerywaną.

Robimy to aby trend był tylko delikatnie zarysowany a nie dominował nad podstawowymi danymi na tym wykresie.

W wyniku wyżej opisanych zmian uzyskamy wykres, który powinien wyglądać tak jak poniżej.

To że sprzedaż w październiku wybijają się ponad sezonowość pokazaną przez trend jest widoczne już na pierwszy rzut oka.

Przygotowanie innych wykresów na podstawie tego samego wzoru.

Załóżmy, że poproszono nas o przygotowanie wykresu w tym samym formacie dla sprzedaży w sztukach. Dane i opisy, dla których mamy przygotować nowy wykres znajdują się w tym samym arkuszu poniżej w komórkach: ‘B40:C52’.

Moglibyśmy powtórzyć wszystkie opisane wyżej operację, ale (ponieważ człowiek jest z natury istotą leniwą) skorzystamy z szybszego rozwiązania.

Zaznaczamy przygotowany wcześniej wykres i kopiujemy go (Ctrl+C).

Wybieramy komórkę przy tabeli ze sprzedażą w sztukach (np. D24) i wklejamy tam wykres (Ctrl+V).

Klikamy na jeden ze słupków nowego wykresu wybierając całą serie danych.

Powyżej w tabeli ze sprzedażą pokazane zostanie z jakich danych korzysta wykres. Klikamy lewym klawiszem myszy na bok niebieskiego prostokąta i przeciągamy go w dół na tabele z danymi ze sprzedażą w sztukach.

Po upuszczeniu prostokąta we właściwym miejscu wykres automatycznie zacznie pokazywać nowe dane.

Jest to bardzo przydatne, kiedy musimy utworzyć wiele tak samo wyglądających wykresów, przygotowujemy jeden wykres, kopiujemy go wiele razy, po czym dla każdego z nich przeciągamy obszar pobierania danych w nowe miejsce.

Pozostało już tylko zmienić tytuł, skala pionowej osi zostanie dopasowana automatycznie o ile nie zmieniliśmy skali ręcznie w oryginalnym wykresie.

Wykresy można także przenosić na inne arkusze lub do innych plików.

Inaczej mówiąc jeżeli mamy wykres w odpowiadającym nam formacie, przygotowywanie takiego samego jest stratą czasu, lepiej skopiować istniejący i zmienić źródło danych.

W przypadku kopiowania wykresów z innych arkuszy lub plików zmiany źródła danych dokonujemy poprzez kliknięcie na wykresie prawym klawiszem i wybranie polecenia ‘Zaznacz dane...’

Klikamy przycisk ‘Edytuj’ (serie danych) w oknie ‘Wybieranie źródła danych’.

Zmian dokonujemy w polach: ‘Nazwa serii:’ i ‘Wartości serii:’. Klikamy OK., dzięki czemu wrócimy do okna ‘Wybieranie źródła danych’.

Klikamy teraz drugi przycisk ‘Edytuj’, tym razem w okienku ‘Etykiety osi poziomej’.

W oknie ‘Etykiety osi’ zmieniamy zakres etykiet osi, czyli to skąd mają być pobierane opisy osi X.

Zmiana zakresu danych na mniejszy lub większy

Jeżeli wykres, który tworzymy, ma więcej lub mniej wartości lub serii danych, możemy dokonać zmiany wielkości obszaru z którego pobierane są dane. Klikamy na serie danych i po pokazaniu się obszarów z których pobierane są dane zmieniamy wielkość tego obszaru przeciągając jego róg w odpowiednie miejsce.

Dla przećwiczenia tego procesu ograniczmy nasz wykres tylko do pierwszego półrocza.

Poniżej efekt ograniczenia zakresu danych.

Przykład 2.

(Arkusz: ‘Wykresy 2’)

Aby w najlepszy możliwy sposób pokazać dane przedstawione w poniższej tabeli na jednym wykresie należy użyć wykresu o 2 osiach.

Zaczniemy od wybrania tabeli wraz z nagłówkami.

Częstym błędem jest zaznaczanie także sumy danych, wykres zawierający sumę będzie nieczytelny ponieważ pozostałe słupki będą zbyt małe aby można było właściwie ocenić różnice pomiędzy nimi.

Z karty ‘Wstawianie’ wybieramy wykres ‘Liniowy’ i jego pierwszy podtyp.

Wykres automatycznie przygotowany przez Excela będzie daleki od tego, co chcemy osiągnąć.

Marża w procentach została pokazana na tej samej osi, co sprzedaż i linia przedstawiająca marżę pokrywają się z osią X.

Klikamy na linię ze sprzedażą prawym klawiszem myszy i wybieramy polecenie ‘Formatuj serię danych…’.

W oknie ‘Formatowanie serii danych’, zmieniamy ‘Oś główną’ na ‘Oś pomocniczą’ i klikamy przycisk X aby zamknąć okienko.

Na skutek przerzucenia wykresu na oś pomocniczą do wykresu dodana została oś z prawej strony z podanymi wartościami sprzedaży.

Na lewej osi przedstawione są wartości dla marży.

Gdybyśmy z jakiegoś powodu woleli mieć sprzedaż z lewej a marżę z prawej należałoby powyżej opisaną operację wykonać dla linii przedstawiającej marżę. Ponieważ linia marży pokrywała się z osią X trafienie w nią myszką wymagałoby chwili cierpliwości.

Wykresy będą bardziej czytelne gdy nie będą się przecinały w tylu miejscach. Zmienimy teraz maksimum dla osi z danymi sprzedaży.

Dwukrotnie klikamy w oś lewym przyciskiem myszy otwierając okno ‘Formatowanie osi’ w którym wprowadzamy maksimum 1600.

Wartości sprzedaży nie uległy zmianie ale ponieważ oś ma teraz większy zakres wykres pokazywany jest niżej i nie przecina marży.

W tym momencie znacznie łatwiej analizować współzależność marży i sprzedaży.

Wprowadźmy teraz tytuł wykresu: Marża i Sprzedaż w 2014 roku.

Proponuję usunąć także poziome linie siatki wybierając je i wciskając przycisk ‘Delete’ na klawiaturze.

Możemy teraz dodać pogrubienie do tytułu, przeciągnąć legendę na obszar kreślenie i rozciągnąć obszar kreślenia łapiąc za zaznaczone poniżej kwadraty.

Wykres możemy uznać za zakończony.

Drugą opcją tego samego wykresu będzie połączenie wykresu liniowego ze słupkowym.

Gdybyśmy chcieli zastosować takie rozwiązanie należy umieścić legendę poza obszarem kreślenia.

Kliknąć linię pokazującą sprzedaż lewym klawiszem myszy i z Menu ‘WSTAWIANIE’ wybrać wykres słupkowy i pierwszy z jego podtypów.

Jeszcze przed jego kliknięciem Excel pokaże jak będzie wyglądał wykres.

Ostatnią operacją, którą proponuje przeprowadzić, będzie pogrubienie słupków.

Klikamy je prawym klawiszem myszy i wybieramy ‘Formatuj serię danych…’.

Następnie zmieniamy szerokość przerwy między słupkami na 50%.

Wykres możemy uznać za zakończony.

Przykład 3.

(Arkusz: ‘Wykresy 3’)

(Rozwiązanie arkusz: ‘Wykresy 3a’)

W tym przykładzie chcemy pokazać na jednym wykresie dane Sprzedaży i Zamówień dla pewnego producenta.

Obie zmienne przyjmują wartości w milionach jednak w przypadku sprzedaży są to setki milionów a ilość zamówień to milion i kilkaset tysięcy.

Dlatego też nie jest możliwe pokazanie obu danych na zwykłym wykresie liniowym, gdybyśmy spróbowali to zrobić, linia dla zamówień byłaby płaska i w okolicy zera, co nie dałoby nam możliwości przeanalizowania jej zmienności.

Dodatkowo chcielibyśmy na skali osi X mieć zarówno podział na miesiące jak i kwartały.

Skorzystamy z wykresu: Liniowego o dwóch osiach.

Zaznaczamy cały obszar danych wraz z nagłówkami wierszy i kolumn i z karty ‘WSTAWIANIE’ wybieramy wykres liniowy i pierwszy jego podtyp.

Wykres, który został wstawiony, automatycznie załatwił jeden z naszych problemów, oś X ma podwójne opisy.

Pierwszym krokiem będzie dodanie drugiej osi. W tym celu należy kliknąć na linię przedstawiającą Zamówienia prawym przyciskiem myszy. Ponieważ pokrywa się ona z osią X, może to wymagać kilku prób i nieco cierpliwości. Kiedy nam się to uda, wybieramy polecenie ‘Formatuj serię danych’ i na karcie ‘Opcje serii’ zaznaczamy ‘Oś pomocniczą’ (zostało to pokazane w poprzednim przykładzie).

Skutkiem ubocznym dodania drugiej osi jest to że obszar kreślenia stał się jeszcze węższy a sam wykres nieczytelny.

Zyskamy nieco na przejrzystości jeśli skasujemy linie siatki. Niestety wciąż linie na siebie zachodzą i przecinają się, a dziesiątki zer w jednostkach na osi nie wyglądają profesjonalnie.

Podwójne kliknięcie na prawej osi Y wyświetli okno ‘Formatowanie osi’, gdzie znajdujemy ‘Jednostki wyświetlania’ – a następnie wybieramy ‘Miliony’.

Okno ‘Formatowanie osi’ przewijamy w dół i klikamy w ‘LICZBY’ by następnie wpisać 1 w Miejsca dziesiętne.

Na skali mamy już czytelne liczby ale wykresy nadal na siebie nachodzą, aby były czytelne musimy ustalić minimum na zero a maksimum na 3,0E6.

Zapis 3,0E6 oznacza 3 * 10⁶ (trzy razy dziesięć do potęgi szóstej) czyli 3 000 000.

Teraz na osi głównej Y zmieniamy tylko jednostkę główną na miliony.

Przenosimy też legendę na obszar kreślenia i powiększamy obszar kreślenia.

Pozostało wprowadzenie tytułu, pozbycie się obramowania, możemy także pogrubić linie, klikając je prawym klawiszem myszy i wybierając poniżej pokazane polecenia.

Jeżeli dla wszystkich osób, które będą korzystały z tego wykresu jest oczywiste, że dane są w milionach, możemy tą informację wykasować, zyskując jeszcze trochę miejsca na zwiększenie obszaru kreślenia.

Niektóre osoby lubią też widzieć osi, aby je dodać klikamy dwukrotnie na dowolnej z nich i na karcie ‘Wypełnienie’ okna ‘Formatowanie osi’ zmieniamy kolor na czarny.

Nie zamykamy okna ‘Formatowanie osi’ przy zmianie osi, ale jedynie klikamy kolejną oś raz lewym klawiszem myszy.

Ostatecznie wykres wygląda tak jak poniżej.

Przykład 4.

(Arkusz: ‘Wykresy 4’)

Poproszono nas o przygotowanie wykresu kołowego z informacjami o stanach magazynowych 4 produktów wg ich wartości.

Zaznaczamy tabelę i z karty ‘Wstawianie’ wybieramy wykres ‘Kołowy’ i jego podtyp ‘Kołowy 2-W’.

Wykresy 2-W są bardziej czytelne, niestety ze względu na atrakcyjną formę wykresy 3-W stały się popularniejsze i często odbiorcy takich wykresów właśnie oczekują.

Znajdywanie produktów za pomocą małych kwadracików z ich kolorami w legendzie jest dość męczące, a w przypadku gdy elementów jest dużo lub ktoś ma problem w rozpoznawaniu zbliżonych kolorów wręcz niemożliwe.

Klikamy na znak ‘+’, odznaczamy ‘Legendę’, zaznaczamy ‘Etykiety danych’, po czym po kliknięciu strzałki przy ‘Etykiety danych’ wybieramy najpierw ‘Koniec zewnętrzny’ a następnie ‘Więcej opcji…’.

W oknie ‘Formatowanie etykiet danych’ zaznaczamy:

- ‘Nazwa kategorii’ – dzięki czemu będziemy wiedzieli który wycinek koła dotyczy którego produktu.

- ‘Wartość’ – aby wiedzieć o ile sztuk chodzi

- ‘Wartość procentowa’ – dzięki czemu obliczony i podany zostanie procent jaki ta ilość stanowi w całości.

Domyślnie zaznaczona jest też opcja ‘Pokaż linie wiodące’ – przydatna przy dużych ilościach wycinków koła, linie wiodące łączą wtedy wycinki z etykietami.

Trzy podawane na etykietach wiadomości rozdzielone są średnikami, możemy to zmienić dzięki menu ‘Separator’.

W tym oknie możliwe są też zmiany położenia etykiet oraz zmiany formatu liczb.

Jak we wszystkich wykresach możemy też skorzystać ze stylów lub palet kolorów dostępnych po kliknięciu ikony Pędzla.

Kolor wycinka możemy zmienić klikając na nim dwa razy pojedynczo (z przerwą pomiędzy kliknięciami) co spowoduje jego zaznaczenie (małe kółka), po czym klikamy go prawym klawiszem i wybieramy kolor, zmiany są widoczne na bieżąco na wykresie podczas przesuwania kursora myszki nad kolorami.

Aby wysunąć wybrany wycinek wystarczy go zaznaczyć i przeciągnąć od środka wykresu. Używa się tego aby zwrócić uwagę odbiorcy właśnie na ten fragment.

Po dodaniu pogrubienia do etykiet danych i dopisaniu tytułu, wykres będzie wyglądał tak jak na poniższym rysunku.

Aby pozbyć się obramowania wykresu kliknijmy go prawym klawiszem i wybierzmy polecenie ‘Formatuj obszar wykresu…’

W oknie ‘Formatowanie obszaru wykresu’ wybieramy ‘Brak linii’. Okno zamykamy X w jego prawym górnym rogu.

W każdej chwili możemy zmienić rodzaj wykresu klikając go prawym klawiszem i wybierając polecenie ‘Zmień typ wykresu seryjnego…’.

Wybierzmy wykres Kołowy 3-W, czyli w 3 wymiarach.

Dla wykresów 3-W istnieje wiele stylów, które na niektórych osobach mogą robić wrażenie.

A po kliknięciu prawym klawiszem wykresu i wybraniu polecenia ‘Obrót 3-W’ będziemy mogli wybrać położenie wykresu, które uwydatni to na czym nam zależy.

UWAGA:

Zwiększanie Perspektywy powoduje że wykres staje się mniej czytelny, a wycinek będący na dole wydaje się większy niż jest w rzeczywistości.

Dla początkujących użytkowników wykresy 3 -W stanowią dużą pokusę, sprawiają wrażenie zaawansowanych i świadczących o profesjonalizmie twórcy arkusza. W rzeczywistości te same dane pokazane na prostszym wykresie dwu wymiarowym będą bardziej czytelne.

Więcej na ten temat można przeczytać w lekcji ‘Profesjonalne Raporty dla Zaawansowanych’.

Wykres z danych, które nie znajdują się w ciągłej tabeli.

Nic nie stoi na przeszkodzie aby przygotować wykres dla danych, które nie znajdują się w ciągłej tabeli. W tym samym arkuszu, poniżej zaznaczamy odpowiednie wartości stanów magazynowych i odpowiadające im opisy. Gdyby połączyć zaznaczone komórki stworzyłyby prostokątną tabelę.

Aby zaznaczyć wiele oddzielonych od siebie komórek przytrzymujemy przycisk Ctrl i klikamy wybrane komórki lewym klawiszem myszy.

Excel może sobie nie poradzić z utworzeniem wykresu, jeśli dane będą rozmieszczone tak że z zaznaczonych komórek nie da się utworzyć prostokątnej tabeli z danymi lub gdy zaznaczony obszar będzie zawierał scalone komórki.

Wynikiem zastosowania powyższej metody będzie wykres niczym nieróżniący się od wykresu przygotowanego na podstawie jednej ‘ciągłej’ tabeli.

Przykład 5.

(Arkusz: ‘Wykresy 5’)

(Rozwiązanie w arkuszu: ‘Wykresy 5a’)

W dość prosty sposób można zmieniać kolory na wykresie w zależności od wartości jakie przyjmują dane.

W tym przykładzie chcielibyśmy aby od stycznia 2015, kiedy to władzę w firmie przejął nowy prezes linia wykresu miała inny kolor.

Do dotychczasowej tabeli dokładamy 2 kolumny i wprowadzamy tam odwołania do kolumny D, gdzie znajdują się dane.

W kolumnie E tylko od danych styczeń 2014 do styczeń 2015, w kolumnie F tylko od danych za styczeń 2015 do grudnia 2015.

Dana dla stycznia 2015 jest zarówno w kolumnie E jak i F.

Zaznaczamy obszary B2:C26 oraz jednocześnie (z Ctrl) E2:F26 i wstawiamy wykres liniowy – pierwszy jego podtyp.

Formatujemy oś umieszczając etykiety ‘Nisko’.

Kasujemy legendę oraz poziome linie siatki, dzięki czemu staje się widoczna oś x gdzie dane przyjmują wartość 0%.

Wprowadzamy tytuł wykresu.

Formatujemy serie danych, tą z danymi za 2014 na kolor zielony i grubość 4,5 punktu. Tą z danymi za 2015 na zielono i taka sama grubość.

Wykres jest już gotowy a jego przekaz wzmocniony odpowiednim kolorowanie.

Podobnie będziemy postępować w przypadku wykresu słupkowego.

Dane do tego wykresu znajdują się w tym samym arkuszu (Wykresy5) w komórkach B29:D53.

Ponieważ w tym przypadku przejść z plusa na minus jest więcej zamiast ręcznie wprowadzać odwołania do danych zastosujemy formułę z użyciem funkcji JEŻELI.

Jeżeli w komórce D30 znajduje się wartość większa od zera, wprowadzamy ją do tej komórki, jeżeli nie w komórkę wprowadzamy zero.

W kolumnie z liczbami ujemnymi wprowadzamy formułę, która jeżeli w kolumnie D jest liczba mniejsza lub równa 0, wprowadza tą wartość do komórki, jeśli dana w kolumnie D nie spełnia tego warunku wpisuje tam zero.

Mniejsze lub równe zapisujemy jako znak mniejszości i równości po sobie <=.

Po skopiowaniu formuł do komórek poniżej uzyskamy dane niezbędne do przygotowania wykresu.

Zaznaczamy dane z pominięciem kolumny D.

Wstawiamy wykres kolumnowy – drugi podtyp – kolumnowy skumulowany.

Formatowanie odbywa się analogicznie do powyżej opisanego wykresu liniowego.

Dodatkowo zmniejszamy tylko szerokość przerwy, proponuję 50%.

Po formatowaniu wykres powinien wyglądać tak jak poniżej. W przypadku zmiany danych tabela z formułami, wykres i kolory na wykresie aktualizują się automatycznie.

W komórkach B59:G83 te same dane są podzielone na 3 kategorie. Spadki sprzedaży, wzrosty sprzedaży do 5%, wzrosty sprzedaży powyżej 5%.

Do takiego podziału użyłem następujących formuł

Duże dodatnie =JEŻELI(D60>5%;D60;0)

Dodatnie =JEŻELI(ORAZ(5%>=D60;D60>0);D60;0)

Ujemne =JEŻELI(D60<=0;D60;0)

W formule dla liczb z przedziału powyżej zera i nie większych od 5% użyłem funkcji logicznej oraz, dzięki czemu mogłem zapisać te dwa warunki jednocześnie.

Wykres po formatowaniu będzie wyglądał tak jak jest to pokazane na rysunku poniżej.

Sugeruję umiar w ilości kolorów pokazywanych na wykresie, wykres powinien ułatwiać zrozumienie danych, a powyżej 3 kolorów słupków może to utrudniać.

Przykład 6.

(Arkusz: ‘Wykresy 6’)

Jeżeli w tabeli na podstawie której przygotowujemy wykres brakuje jednej z danych i zamiast niej wprowadzony jest tekst Excel pokaże wartość zerową dla tego punktu, co może całkowicie zniekształcić odbiór tego wykresu przez użytkowników.

Jeżeli zamiast tekstu nic tam nie będzie Excel najprawdopodobniej pokaże przerwę (w zależności od wcześniejszych ustawień).

To co w takich przypadkach ma być pokazywane możemy zmienić klikając na wykresie prawym klawiszem myszy i wybierając polecenie ‘Zaznacz dane…’.

Następnie w oknie ‘Wybieranie źródła danych’ klikamy przycisk ‘Ukryte i puste komórki’.

By wreszcie w oknie ‘Ustawienia ukrytych i pustych komórek’ wybrać jedną z trzech opcji.

Najmniej ingerującą w kształt wykresu jest ‘Połącz punkty danych linią’.

W tym oknie możemy też wybrać czy chcemy aby na wykresach były pokazywane dane z ukrytych kolumn i wierszy.

Domyślnie nie są pokazywane.

Po wybraniu ‘Połącz punkty danych linią’ wykres będzie wyglądał tak jak poniżej.

Rozwiązanie to działa tylko dla pustych komórek. Tekst typu ‘brak danych’ będzie pomimo takich ustawień pokazywany jako zero.

Więcej informacji na temat tworzenia wykresów znajdziesz w lekcjach:

Wykresy dla Zaawansowanych

Wykresy dla Ekspertów

Wykres Przestawny

Wykresy Matematyczne

Wykresy Przebiegu w Czasie