fbpx

Dynamiczny wykres słupkowy niepokazujący zerowych wartości

04.11.2019 | Analizy sprzedaży, ECP2, Tabele, Triki, Wykresy

Czyli sprytne użycie obiektu tabeli

Załóżmy, że chcemy przedstawić dużo danych, np. sprzedaż produktów w pewnym okresie. Produktów mamy dużo, jedne się sprzedają lepiej, inne gorzej, inne w ogóle. Co pewien czas mamy stworzyć wykres (słupkowy) sprzedających się produktów. Zerowa sprzedaż nas nie interesuje.

Gdyby zrobić wykres ze wszystkich danych – te zerowe też byłyby widoczne. Nam natomiast chodzi o proste rozwiązanie, bez specjalnego kombinowania, które pokaże jedynie wartości większe od zera. Czyli coś takiego:

Rozwiązanie tego problemu jest bardzo proste – wystarczy posłużyć się obiektem tabela… Rozwiązanie będzie wtedy dynamiczne, a dzięki autofiltrom dostaniem jeszcze za darmo mechanizm odświeżania wykresu.

Formatka

Zacznijmy od danych. Założenie jest takie, że dostajemy dane gotowe do pokazania na wykresie (za wyjątkiem tych zerowych, oczywiście). Czyli coś takiego:

Dane do wykresu

Zobaczcie, że w tych danych są wartości zerowe. Nie chcemy ich na wykresie! Jeśli bowiem zrobilibyśmy z nich wykres, to wyszłoby coś, jak na pierwszym obrazku wyżej. Nie chcemy zaśmiecać wykresu takimi danymi.

Najprostszym sposobem, jaki przychodzi mi do głowy jest odfiltrowanie ich. Można to zrobić zwykłym filtrem, natomiast warto posłużyć się obiektem tabela. Zyskamy dzięki temu dodatkowe „dynamiczne” możliwości.

Obiekt tabela

Wrzućmy więc nasze dane to tabeli. Aby to zrobić – ustaw się w dowolnym miejscu danych, np. B14 i użyj skrótu klawiszowego Ctrl + t. Upewnij się, że Moje dane mają nagłówki i zatwierdź OK.

Tworzenie tabeli

W obiekcie tabela dostajemy autofiltry gratis.

Wykres słupkowy

Teraz kwestia stworzenia wykresu. Wykres ma przedstawiać sprzedaż produktów, czyli interesują nas kolumny ProduktSprzedaż w naszych danych. Zaznaczmy je więc, najeżdżając od góry na nagłówek najpierw Produkt, a potem (z wciśniętym klawiszem Ctrl, by zaznaczyć nieprzylegające do siebie kolumny) – Sprzedaż:

Zaznaczenie danych do wykresu

Teraz tworzymy wykres z menu Wstawianie/Wykresy/Słupkowy grupowany:

Wstawianie wykresu słupkowego

To, co otrzymamy, mistrzostwem świata nie będzie, ale jeszcze nie ma być :). Dokonamy w tym wykresie kilka zmian.

1. Ponieważ ma to być wykres oparty na filtrowanych danych, a z pewnością wstawił się tuz obok nich, dla świętego spokoju ustawmy, aby nie zmieniał on rozmiaru, gdy wiersze pod nim się ukryją. W tym celu trzeba pogrzebać we właściwościach wykresu, a konkretnie: zaznacz wykres, Ctrl + 1 i dalej zaznacz opcję Nie przenoś ani nie zmieniam rozmiaru z komórkami:

Zostaw wyrkes!

Zostaw wykres!

2. Następnie załóżmy na dane filtr taki, aby ukryć te nieszczęsne zera. To jest zwykłe filtrowanie danych. Proponuję tutaj skorzystać z filtra liczb w kolumnie Sprzedaż i zaznaczyć opcję większe niż i wpisać 0 (zero):

Filtrowanie wartości zerowych

Filtrowanie wartości zerowych

Efekt jest następujący:

Po odfiltrowaniu zer

Po odfiltrowaniu zer…

3. Dalej to już kosmetyka, jak choćby odwrócenie kategorii (zobacz, że wykres na górze pokazuje Haribo misie, a tabela Milka czekolada pomarańczowa). Aby to odwrócić, trzeba wejść w opcje osi pionowej (zaznacz ją i Ctrl +1) i zaznaczyć Kategorie w kolejności odwrotnej:

Kategorie w kolejności odwrotnej

Kategorie w kolejności odwrotnej

4. Można od razu ustawić tytuł wykresu. Jeśli chcesz, aby był od dynamiczny – zobacz tutaj.

5. Ostatnia rzecz, o jaką warto tutaj się pokusić, to filtrowanie kategorii produktów za pomocą fragmentatora (można też filtrem oczywiście, jednak fragmentatorem łatwiej i przyjaźniej). Fragmentator to filtr tabeli, więc aby go dodać – należy ustawić się w tabeli, a następnie menu Projekt tabeli/Wstaw fragmentator i wybrać Kategoria.

Wstawianie fragmentatora

Wstawianie fragmentatora

Tylko uwaga! Wstawienie fragmentatora zlikwiduje nam dotychczasowe filtrowanie wartości! Trzeba będzie je filtrować jeszcze raz, albo – jeśli chcesz fragmentator – najpierw go wstaw, a potem filtruj.

Wszystko razem wzięte wygląda następująco:

Wynik

Wynik

Fragmentatory jeszcze ładnie przefiltrują, a gdy zmienisz coś w wartościach (w tabeli) wykres automatycznie się do tego dostosuje.

Można tutaj jeszcze cuda robić: ładnie posortować dane, np. wg kategorii, a po zmianie- gdy będziemy stosowali ponownie filtr (Dane/Zastosuj ponownie) wszystko ładnie się ponownie posortuje i przefiltruje. Ale to już zostawiam Tobie do poeksperymentowania :).

 A tymczasem załączam plik:

MalinowyExcel Dynamiczny wykres oparty na tabeli dw.xlsx

 

I film wideo z prezentowanym rozwiązaniem:

 

 

 

 

 

Jeżeli chcesz lepiej poznać Excela na poziomie średniozaawansowanym zapisz się na listę zainteresowanych kursem Excel w codziennej pracy cz. 2! Podczas oczekiwania na kurs będę Ci wysyłała informacje, ciekawostki i excelowe triki. Bezpłatnie! 

Pomogłam Ci? Postaw mi wirtualną kawę! →

Przeczytaj podobne wpisy

Kategorie

2 komentarze

  1. Witam,
    czy takim samym sposobem można ukryć wartości zerowe z wykresu kołowego? A właściwie żeby nie pokazywały się etykiety danych gdzie są wartości zerowe.

    Reply
    • Hej, możesz wtedy zastosować formowanie liczb na wykresie (opcja: format liczby), takie, że w typie niestandardowe jest taki wpis: # ##0;-# ##0;;
      To sformatuje liczby dodatnie (ujemne też, choć pewnie ich nie będzie ;)) i zer nie wyświetli 🙂

      Reply

Submit a Comment

Twój adres e-mail nie zostanie opublikowany. Wymagane pola są oznaczone *

Pin It on Pinterest