Czyli czasem trzeba trochę oszukać 🙂

Mamy dane źródłowe, z których chcemy stworzyć tabelę przestawną. Są to dane sprzedażowe: produkty, ilości, wartości, kategorie tych produktów… Nie wszystkie jednak kategorie są uzupełnione w źródle (żółte pola po lewej), co tabela przestawna wyświetla tak, jak na obrazku poniżej (żółte pole po prawej):

Formatka

Wszystko ok, kategorie dla czekolad faktycznie są puste, ale na raporcie nie chcemy, aby ten zapis (puste) się wyświetlał. Chcemy, aby kategoria faktycznie była pusta, czyli żeby nic w komórce nie było napisane. Jak to zrobić? Czytaj dalej 🙂

Opcja tabeli przestawnej?

Pierwsze, co przychodzi nam do głowy, to: na pewno jest jakaś opcja tabeli przestawnej. Szukamy i szukamy i znajdujemy to:

Opcje tabeli przestawnej

Problem jest jednak, że to nic nie daje ;(.

(BTW: ta opcja umożliwia pokazanie określonej przez użytkownika wartości w komórkach w polu wartości tabeli przestawnej, gdy nie ma w niej wartości, np. czekolady nie sprzedawały się w styczniu, tabela domyślnie nic nie wyświetla na przecięciu pól: Czekolada i Styczeń, ta opcja jednak umożliwia, aby wyświetlić tam np. 0).

To więc nie działa, więc co? Jeśli nie ma do czegoś opcji, tak, jak w tym przypadku, pozostaje nam już tylko… oszukiwanie :). Czyli Excel wpisze do komórki wartość (puste), ale my zrobimy tak, aby jej nie było widać. „We cheated„, jak to powiedział szef animatorów trzeciej części Harry’ego Pottera, kiedy okazało się, że aby hipogryf mógł faktycznie latać, animatorzy muszą mu zrobić tak duże skrzydła, że po ich złożeniu szurałby nimi o ziemię. Zrobili więc tak, że jak hipogryf latał – miał skrzydła odpowiedniej wielkości, rzeczywiste. A jak był na ziemi – zmniejszyli mu je. My zrobimy podobny manewr w Excelu.

Formatowanie warunkowe

Moje ukochane i niezastąpione! Przychodzą mi 2 rozwiązania tego problemu za pomocą formatowania warunkowego. Do wyboru do koloru. To, które z nich wybierzemy jak zwykle zależy od sytuacji.

Czyli mamy taką tabelę przestawną:

Widać (puste)

1. Zaznaczamy pole kategorii, czyli zakres I4:I13 (możemy to zrobić najeżdżając myszką od góry na słowo Kategoria, i kliknąć lewym przyciskiem myszki).

2. Wchodzimy do formatowania warunkowego, czyli Narzędzia główne/ Formatowanie warunkowe/ Nowa reguła

3. I wybieramy, jak na obrazku: Formatuj tylko komórki zawierające/ Wartość komórki jest równa (puste) 

3. Następnie klikamy przycisk Formatu, aby określić formatowanie, jakie ma zostać zastosowane do tych komórek, gdy warunek jest prawdziwy. Ja proponuję następujące: formatowanie niestandardowe

# ##0;-# ##0;0;

albo

Standardowy;Standardowy;0;

Formatowanie niestandardowe

Czyli korzystamy z formatowania niestandardowego, aby ukryć tekst – zobacz, że po ostatnim średniku (w obu wariantach) nic nie jest napisane.

Możemy też zastosować inne formatowania – jeśli wiemy, że kolor komórek to przezroczysty (domyślnie nie mają koloru) lub biały, możemy po prostu czcionkę pokolorować na biało:

Biała czcionka

I to cały trik. Efekt, w obu przypadkach, dostajemy taki:

Wynik

Tylko i aż tyle! Mam nadzieję, że to rozwiązanie pomoże Ci w pracy z tabelami przestawnymi. Jeśli tak, albo jeśli znasz kogoś, komu może pomóc – proszę udostępnij jej/mu ten wpis. Dzięki temu wiedza ta, wcale nieoczywista, dotrze do szerszego grona osób, które chcą usprawniać swoją pracę :). Dziękuję!

I film na YB: