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 Produkt i Sprzedaż 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 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

Efekt jest następujący:

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

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

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

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: