Tabele przestawne są niesamowitym wbudowanym raportowaniemnarzędzie w programie Excel. Chociaż zwykle służy do podsumowywania danych z sumami, można ich również użyć do obliczenia procentu zmiany między wartościami. Jeszcze lepiej: jest to łatwe do zrobienia.
Możesz użyć tej techniki do robienia wszelkiego rodzajurzeczy - właściwie wszędzie tam, gdzie chcesz zobaczyć, jak jedna wartość ma się do drugiej. W tym artykule wykorzystamy prosty przykład obliczania i wyświetlania procentu, o jaki zmienia się całkowita wartość sprzedaży z miesiąca na miesiąc.
Oto arkusz, którego będziemy używać.
Jest to dość typowy przykład arkusza sprzedaży, który pokazuje datę zamówienia, nazwę klienta, przedstawiciela handlowego, całkowitą wartość sprzedaży i kilka innych rzeczy.
Aby to wszystko zrobić, najpierw sformatujemy zakres wartości jako tabelę w programie Excel, a następnie utworzymy tabelę przestawną, aby wykonać i wyświetlić nasze obliczenia zmiany procentowej.
Formatowanie zakresu jako tabeli
Jeśli twój zakres danych nie jest już sformatowany jakozachęcamy do zrobienia tego. Dane przechowywane w tabelach mają wiele zalet w stosunku do danych w zakresach komórek arkusza roboczego, szczególnie w przypadku korzystania z tabel przestawnych (więcej informacji na temat zalet korzystania z tabel).
Aby sformatować zakres jako tabelę, wybierz zakres komórek i kliknij opcję Wstaw> Tabela.
Sprawdź, czy zakres jest prawidłowy, czy masz nagłówki w pierwszym rzędzie tego zakresu, a następnie kliknij „OK”.
Zakres jest teraz sformatowany jako tabela. Nazewnictwo tabeli ułatwi korzystanie z niej w przyszłości podczas tworzenia tabel przestawnych, wykresów i formuł.
Kliknij kartę „Projekt” w obszarze Narzędzia tabel i wprowadź nazwę w polu podanym na początku wstążki. Ta tabela została nazwana „Sprzedaż”.
Możesz także zmienić styl stołu, jeśli chcesz.
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 Excel 2016 wartości daty są automatycznie grupowane w latach, kwartałach i miesiącach.
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 „Grupuj”.
Wybierz grupy, których chcesz użyć. W tym przykładzie wybrano tylko lata i miesiące.
Rok i miesiąc to teraz pola, które możemy wykorzystać do analizy. Miesiące są nadal nazywane jako Data zamówienia.
Dodaj pola wartości do tabeli przestawnej
Przenieś pole Rok z Wierszy do obszaru Filtr. Umożliwia to użytkownikowi filtrowanie tabeli przestawnej przez rok, zamiast zaśmiecać tabelę przestawną zbyt dużą ilością informacji.
Przeciągnij pole zawierające wartości (Całkowita wartość sprzedaży w tym przykładzie), które chcesz obliczyć i zaprezentuj zmianę w obszarze Wartości dwa razy.
To może jeszcze nie wyglądać dużo. Ale to się wkrótce zmieni.
Oba pola wartości będą domyślnie sumowane i obecnie nie będą miały formatowania.
Wartości w pierwszej kolumnie chcielibyśmy zachować jako sumy. Wymagają jednak formatowania.
Kliknij liczbę prawym przyciskiem myszy w pierwszej kolumnie i wybierz „Formatowanie liczb” z menu skrótów.
Wybierz format „Księgowość” z 0 miejscami po przecinku w oknie dialogowym Formatuj komórki.
Tabela przestawna wygląda teraz tak:
Utwórz kolumnę zmiany procentowej
Kliknij prawym przyciskiem myszy wartość w drugiej kolumnie, wskaż „Pokaż wartości”, a następnie kliknij opcję „Różnica% od”.
Wybierz „(Poprzedni)” jako element podstawowy. Oznacza to, że bieżąca wartość miesiąca jest zawsze porównywana z wartością z poprzednich miesięcy (pole Data zamówienia).
Tabela przestawna pokazuje teraz zarówno wartości, jak i zmianę procentową.
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 „Wariancja”.
Dodaj strzały rozbieżności
Aby naprawdę dopracować tę tabelę przestawną, chcielibyśmy lepiej zwizualizować zmianę procentową, dodając zielone i czerwone strzałki.
Zapewni to nam cudowny sposób sprawdzenia, czy zmiana była pozytywna czy negatywna.
Kliknij dowolną z wartości 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„ wariancji ”dla daty zamówienia”.
- Wybierz „Zestawy ikon” z listy Styl formatu.
- Wybierz trójkąty czerwony, bursztynowy i zielony z listy Styl ikon.
- W kolumnie Typ zmień opcję listy na „Liczba” zamiast Procentu. Spowoduje to zmianę kolumny Wartość na 0. Dokładnie to, czego chcemy.
Kliknij „OK”, a formatowanie warunkowe zostanie zastosowane do tabeli przestawnej.
Tabele przestawne są niesamowitym narzędziem i jednym z najprostszych sposobów wyświetlania wartości procentowej zmiany w czasie.
