Tabele przestawne to niesamowite wbudowane narzędzie do raportowania w programie Excel. Chociaż zwykle jest używany do podsumowywania danych z sumami, można ich również użyć do obliczenia procentowej zmiany między wartościami. Jeszcze lepiej: jest to łatwe do zrobienia.
Możesz użyć tej techniki do robienia różnych rzeczy – prawie wszędzie, gdzie chciałbyś zobaczyć, jak jedna wartość porównuje się z inną. W tym artykule użyjemy prostego przykładu obliczania i wyświetlania procentu, o jaki całkowita wartość sprzedaży zmienia się z miesiąca na miesiąc.
Oto arkusz, którego będziemy używać.
To dość typowy przykład arkusza sprzedaży, który zawiera datę zamówienia, nazwę klienta, przedstawiciela handlowego, całkowitą wartość sprzedaży i kilka innych rzeczy.
Aby to wszystko zrobić, najpierw sformatujemy nasz zakres wartości jako tabelę w programie Excel, a następnie utworzymy tabelę przestawną, aby wykonać i wyświetlić nasze obliczenia zmiany procentowej.
Spis treści:
Formatowanie zakresu jako tabeli
Jeśli Twój zakres danych nie jest jeszcze sformatowany jako tabela, zachęcamy do tego. Dane przechowywane w tabelach mają wiele zalet w porównaniu z danymi w zakresach komórek arkusza, szczególnie w przypadku korzystania z tabel przestawnych (przeczytaj więcej o zaletach używania tabel).
Aby sformatować zakres jako tabelę, zaznacz zakres komórek i kliknij wstawka> Tabela.
Sprawdź, czy zakres jest poprawny, czy masz nagłówki w pierwszym wierszu tego zakresu, a następnie kliknij „OK”.
Zakres jest teraz sformatowany jako tabela. Nazwanie tabeli ułatwi w przyszłości odwoływanie się do niej podczas tworzenia tabel przestawnych, wykresów i formuł.
Kliknij kartę „Projekt” w obszarze Narzędzia tabel i wprowadź nazwę w polu znajdującym się na początku Wstążki. Ta tabela została nazwana „Sprzedaż”.
Jeśli chcesz, możesz także zmienić styl tabeli.
Utwórz tabelę przestawną, aby wyświetlić zmianę procentową
Przejdźmy teraz do tworzenia tabeli przestawnej. W nowej tabeli kliknij Wstaw> Tabela przestawna.
Zostanie wyświetlone okno Utwórz tabelę przestawną. Automatycznie wykryje twój stół. Ale w tym momencie możesz wybrać tabelę lub zakres, którego chcesz użyć dla tabeli przestawnej.
Pogrupuj daty w miesiące
Następnie przeciągniemy pole daty, według którego chcemy pogrupować, do obszaru wierszy tabeli przestawnej. W tym przykładzie pole nosi nazwę Data zamówienia.
Począwszy od programu Excel 2016, wartości dat są automatycznie grupowane w lata, kwartały i miesiące.
Jeśli Twoja wersja programu Excel tego nie robi lub po prostu chcesz zmienić grupowanie, kliknij prawym przyciskiem myszy komórkę zawierającą wartość daty, a następnie wybierz polecenie „Grupa”.
Wybierz grupy, których chcesz użyć. W tym przykładzie wybrane są tylko lata i miesiące.
Rok i miesiąc są teraz polami, które możemy wykorzystać do analizy. Miesiące nadal są nazywane datą zamówienia.
Dodaj pola wartości do tabeli przestawnej
Przenieś pole Year z Rows do obszaru Filter. Dzięki temu użytkownik może filtrować tabelę przestawną przez rok, zamiast zaśmiecać tabelę przestawną zbyt dużą ilością informacji.
Przeciągnij pole zawierające wartości (w tym przykładzie Łączna wartość sprzedaży), które chcesz obliczyć i przedstawić zmianę, do obszaru Wartości dwukrotnie.
Może to jeszcze nie wyglądać. Ale to się wkrótce zmieni.
Oba pola wartości będą domyślnie sumowane i obecnie nie mają formatowania.
Wartości w pierwszej kolumnie, które chcielibyśmy zachować jako sumy. Wymagają jednak formatowania.
Kliknij prawym przyciskiem myszy liczbę w pierwszej kolumnie i wybierz „Formatowanie liczb” z menu skrótów.
Wybierz format „Księgowość” z 0 miejscami po przecinku z okna dialogowego Formatowanie komórek.
Tabela przestawna wygląda teraz następująco:
Utwórz kolumnę Zmiana procentowa
Kliknij prawym przyciskiem myszy wartość w drugiej kolumnie, wskaż „Pokaż wartości”, a następnie kliknij opcję „Różnica procentowa od”.
Wybierz „(Poprzedni)” jako element bazowy. Oznacza to, że bieżąca wartość miesiąca jest zawsze porównywana z wartością z poprzednich miesięcy (pole Data zamówienia).
W tabeli przestawnej są teraz wyświetlane zarówno wartości, jak i zmiana procentowa.
Kliknij komórkę zawierającą etykiety wierszy i wpisz „Miesiąc” jako nagłówek tej kolumny. Następnie kliknij komórkę nagłówka drugiej kolumny wartości i wpisz „Odchylenie”.
Dodaj trochę strzałek wariancji
Aby naprawdę dopracować tę tabelę przestawną, chcielibyśmy lepiej zwizualizować zmianę procentową, dodając kilka zielonych i czerwonych strzałek.
Zapewnią nam one piękny sposób sprawdzenia, czy zmiana była pozytywna czy negatywna.
Kliknij dowolną wartość w drugiej kolumnie, a następnie kliknij Strona glowna> Formatowanie warunkowe> Nowa reguła. W otwartym oknie Edytuj regułę formatowania wykonaj następujące czynności:
Wybierz opcję „Wszystkie komórki pokazujące wartości„ Odchylenia ”dla daty zamówienia”.
Wybierz „Icon Sets” z listy Format Style.
Wybierz czerwony, bursztynowy i zielony trójkąt z listy Styl ikony.
W kolumnie Typ zmień opcję listy na „Liczba” zamiast Procent. Spowoduje to zmianę kolumny Wartość na 0. Dokładnie to, czego chcemy.
Kliknij „OK”, a formatowanie warunkowe zostanie zastosowane do tabeli przestawnej.
Tabele przestawne to niesamowite narzędzie i jeden z najprostszych sposobów wyświetlania wartości procentowych zmian w czasie.