Tabele przestawne stanowią niezwykle funkcjonalne narzędzie raportujące w programie Excel. Choć najczęściej wykorzystywane są do podsumowywania danych poprzez obliczanie sum, można je również zastosować do obliczania procentowej zmiany między wartościami. Co więcej, wykonanie tej operacji jest niezwykle proste.
Dzięki tej metodzie możesz analizować różne zjawiska, wszędzie tam, gdzie interesuje cię porównanie dwóch wartości. W niniejszym artykule zaprezentujemy prosty przykład ilustrujący, jak obliczyć oraz wyświetlić procentową zmianę całkowitej wartości sprzedaży z miesiąca na miesiąc.
Poniżej znajduje się arkusz, który będziemy wykorzystywać.
To typowy przykład arkusza sprzedaży, zawierający datę zamówienia, nazwisko klienta, przedstawiciela handlowego, całkowitą wartość sprzedaży oraz inne istotne informacje.
Aby wykonać nasze obliczenia, najpierw sformatujemy zakres danych jako tabelę w Excelu, a następnie stworzymy tabelę przestawną, aby obliczyć i przedstawić procentową zmianę.
Formatowanie zakresu jako tabeli
Jeżeli twój zakres danych nie jest jeszcze sformatowany jako tabela, zdecydowanie warto to zrobić. Dane przechowywane w tabelach oferują znacznie więcej korzyści w porównaniu do danych w tradycyjnych zakresach komórek, szczególnie przy korzystaniu z tabel przestawnych (dowiedz się więcej o zaletach używania tabel).
Aby sformatować zakres danych jako tabelę, zaznacz odpowiednie komórki, a następnie przejdź do zakładki Wstaw > Tabela.
Upewnij się, że zakres jest poprawny i że nagłówki znajdują się w pierwszym wierszu, a następnie kliknij „OK”.
Twój zakres jest teraz sformatowany jako tabela. Aby ułatwić przyszłe odniesienia do niej przy tworzeniu tabel przestawnych, wykresów i formuł, warto nadać tabeli odpowiednią nazwę.
W zakładce „Projekt” w obszarze Narzędzia tabel, wpisz nazwę w polu na początku Wstążki. W tym przypadku tabela została nazwana „Sprzedaż”.
Jeśli chcesz, możesz także dostosować styl tabeli.
Tworzenie tabeli przestawnej do wyświetlania zmiany procentowej
Teraz przystąpimy do tworzenia tabeli przestawnej. W nowym dokumencie kliknij Wstaw > Tabela przestawna.
Pojawi się okno Utwórz tabelę przestawną, które automatycznie wykryje twoją tabelę. Na tym etapie możesz wybrać tabelę lub zakres, z którego chcesz korzystać w tabeli przestawnej.
Pogrupowanie dat w miesiące
Następnie przeciągnij pole daty, według którego chcesz pogrupować dane, do obszaru wierszy tabeli przestawnej. W tym przykładzie będziemy używać pola zatytułowanego Data zamówienia.
Od wersji Excel 2016 wartości dat są automatycznie grupowane w lata, kwartały oraz miesiące.
Jeśli twoja wersja programu Excel nie wykonuje tego automatycznie, lub chcesz zmienić grupowanie, kliknij prawym przyciskiem myszy pole z datą i wybierz opcję „Grupa”.
Wybierz odpowiednie grupy, które chcesz zastosować; w naszym przykładzie zaznaczono tylko lata i miesiące.
Rok i miesiąc są teraz polami, które możesz wykorzystać do analizy. Miesiące będą nadal widoczne jako Data zamówienia.
Dodanie pól wartości do tabeli przestawnej
Przenieś pole Rok z sekcji Wiersze do obszaru Filtry. Dzięki temu użytkownik będzie mógł filtrować dane tabeli przestawnej według roku, co zapobiegnie zaśmieceniu tabeli nadmiarem informacji.
Przeciągnij pole zawierające wartości (w naszym przypadku Łączna wartość sprzedaży), które chcesz obliczyć i przedstawić zmiany, do obszaru Wartości dwukrotnie.
Na razie może to wyglądać niezbyt interesująco, ale wkrótce się to zmieni.
Oba pola wartości będą domyślnie sumowane, ale obecnie nie mają formatowania.
Wartości w pierwszej kolumnie chcemy zachować jako sumy, ale wymagają one odpowiedniego formatowania.
Kliknij prawym przyciskiem myszy liczbę w pierwszej kolumnie i wybierz opcję „Formatowanie liczb” z menu kontekstowego.
Wybierz format „Księgowość” z 0 miejscami po przecinku w oknie dialogowym Formatowanie komórek.
Teraz tabela przestawna wygląda następująco:
Utworzenie kolumny Zmiana procentowa
Kliknij prawym przyciskiem myszy wartość w drugiej kolumnie, wybierz „Pokaż wartości”, a następnie kliknij „Różnica procentowa od”.
Wybierz „(Poprzedni)” jako element bazowy. Oznacza to, że bieżąca wartość miesiąca będzie zawsze porównywana z wartością z poprzednich miesięcy (pole Data zamówienia).
Teraz w tabeli przestawnej widoczne są zarówno wartości, jak i zmiany procentowe.
Kliknij na komórkę z etykietami wierszy i wprowadź „Miesiąc” jako nagłówek tej kolumny. Następnie kliknij na komórkę nagłówka drugiej kolumny wartości i wpisz „Odchylenie”.
Dodaj strzałki wariancji
Aby jeszcze bardziej upiększyć tę tabelę przestawną, dodajmy zielone i czerwone strzałki, które wizualizują procentową zmianę. Dzięki nim będziemy mogli szybko ocenić, czy zmiana była korzystna, czy niekorzystna.
Kliknij na dowolną wartość w drugiej kolumnie, a następnie przejdź do Strona główna > Formatowanie warunkowe > Nowa reguła. W otwartym oknie Edytuj regułę formatowania wykonaj następujące kroki:
Wybierz opcję „Wszystkie komórki pokazujące wartości „Odchylenia” dla daty zamówienia.
Wybierz „Zestawy ikon” z listy Styl formatowania.
Wybierz czerwony, bursztynowy i zielony trójkąt z listy Stylu ikon.
W kolumnie Typ zmień opcję listy na „Liczba” zamiast Procent. Dzięki temu kolumna Wartość zostanie ustawiona na 0, co jest dokładnie tym, czego potrzebujemy.
Po kliknięciu „OK”, formatowanie warunkowe zostanie zastosowane do tabeli przestawnej.
Tabele przestawne to niezwykle potężne narzędzie, które stanowi jeden z najprostszych sposobów prezentacji procentowych zmian w czasie.