Jak dodać i używać pola obliczeniowego tabeli przestawnej programu Excel

Często po utworzeniu tabeli przestawnej istnieje potrzeba rozszerzenia analizy i włączenia do niej większej liczby danych/obliczeń.

Jeśli potrzebujesz nowego punktu danych, który można uzyskać przy użyciu istniejących punktów danych w tabeli przestawnej, nie musisz wracać i dodawać go do danych źródłowych. Zamiast tego możesz użyć Pole obliczeniowe tabeli przestawnej aby to zrobić.

Pobierz zbiór danych i postępuj zgodnie z instrukcjami.

Co to jest pole obliczeniowe tabeli przestawnej?

Zacznijmy od podstawowego przykładu tabeli przestawnej.

Załóżmy, że masz zestaw danych sprzedawców detalicznych i tworzysz tabelę przestawną, jak pokazano poniżej:

Powyższa tabela przestawna podsumowuje wartości sprzedaży i zysku dla detalistów.

A co, jeśli chcesz również wiedzieć, jaka była marża zysku tych sprzedawców detalicznych (gdzie marża zysku to „zysk” podzielony przez „sprzedaż”).

Można to zrobić na kilka sposobów:

1. Wróć do oryginalnego zestawu danych i dodaj nowy punkt danych. Możesz więc wstawić nową kolumnę do danych źródłowych i obliczyć w niej marżę zysku. Gdy to zrobisz, musisz zaktualizować dane źródłowe tabeli przestawnej, aby ta nowa kolumna była jej częścią.
   • Chociaż ta metoda jest możliwa, musisz ręcznie wrócić do zestawu danych i wykonać obliczenia. Na przykład może być konieczne dodanie kolejnej kolumny w celu obliczenia średniej sprzedaży na jednostkę (Sprzedaż/Ilość). Ponownie będziesz musiał dodać tę kolumnę do danych źródłowych, a następnie zaktualizować tabelę przestawną.
   • Ta metoda również powiększa tabelę przestawną, gdy dodajesz do niej nowe dane.
2. Dodaj obliczenia poza tabelą przestawną. Może to być opcja, jeśli struktura tabeli przestawnej prawdopodobnie nie ulegnie zmianie. Ale jeśli zmienisz tabelę przestawną, obliczenia mogą nie zostać odpowiednio zaktualizowane i mogą dać błędne wyniki lub błędy. Jak pokazano poniżej, obliczyłem marżę zysku, gdy w rzędzie byli sprzedawcy. Ale kiedy zmieniłem go z klientów na regiony, formuła dała błąd.
3. Korzystanie z pola obliczeniowego tabeli przestawnej. To jest najbardziej wydajny sposób aby użyć istniejących danych tabeli przestawnej i obliczyć żądaną metrykę. Pole obliczeniowe należy traktować jako kolumnę wirtualną dodaną przy użyciu istniejących kolumn z tabeli przestawnej. Istnieje wiele korzyści z używania pola obliczeniowego tabeli przestawnej (co zobaczymy za chwilę):
   • Nie wymaga obsługi formuł ani aktualizacji danych źródłowych.
   • Jest skalowalny, ponieważ automatycznie uwzględnia wszelkie nowe dane, które możesz dodać do tabeli przestawnej. Po dodaniu pola obliczeniowego możesz go używać jak każdego innego pola w tabeli przestawnej.
   • Łatwa aktualizacja i zarządzanie. Na przykład, jeśli zmienią się metryki lub musisz zmienić obliczenia, możesz to łatwo zrobić z samej tabeli przestawnej.

Dodawanie pola obliczeniowego do tabeli przestawnej

Zobaczmy, jak dodać pole obliczeniowe tabeli przestawnej do istniejącej tabeli przestawnej.

Załóżmy, że masz tabelę przestawną, jak pokazano poniżej, i chcesz obliczyć marżę zysku dla każdego sprzedawcy:

Oto kroki, aby dodać pole obliczeniowe tabeli przestawnej:

• Zaznacz dowolną komórkę w tabeli przestawnej.
• Przejdź do Narzędzia tabel przestawnych -> Analiza -> Obliczenia -> Pola, elementy i zestawy.
• Z listy rozwijanej wybierz Pole obliczeniowe.
• W oknie dialogowym Wstaw obliczone pole:
  • Nadaj mu nazwę, wpisując ją w polu Nazwa.
  • W polu Formuła utwórz odpowiednią formułę dla pola obliczeniowego. Zwróć uwagę, że możesz wybrać spośród nazw pól wymienionych poniżej. W tym przypadku formuła to „= Zysk/Sprzedaż”. Możesz ręcznie wprowadzić nazwy pól lub dwukrotnie kliknąć nazwę pola wymienioną w polu Pola.
• Kliknij Dodaj i zamknij okno dialogowe.

Gdy tylko dodasz pole obliczeniowe, pojawi się ono jako jedno z pól na liście Pola tabeli przestawnej.

Teraz możesz używać tego pola obliczeniowego jak dowolnego innego pola tabeli przestawnej (pamiętaj, że nie możesz używać pola obliczeniowego tabeli przestawnej jako filtru raportu lub fragmentatora).

Jak wspomniałem wcześniej, korzyścią płynącą z używania pola obliczeniowego tabeli przestawnej jest możliwość zmiany struktury tabeli przestawnej, która zostanie automatycznie dostosowana.

Na przykład, jeśli przeciągnę i upuszczę region w obszarze wierszy, otrzymasz wynik, jak pokazano poniżej, gdzie wartość marży zysku jest raportowana zarówno dla sprzedawców detalicznych, jak i dla regionu.

W powyższym przykładzie użyłem prostej formuły (=Profit/Sales) do wstawienia pola obliczeniowego. Możesz jednak również skorzystać z zaawansowanych formuł.

Zanim pokażę Ci przykład użycia zaawansowanej formuły do ​​utworzenia pola obliczeniowego tabeli przestawnej, oto kilka rzeczy, które musisz wiedzieć:

• NIE MOŻNA używać odwołań ani nazwanych zakresów podczas tworzenia pola obliczanego tabeli przestawnej. To wykluczałoby wiele formuł, takich jak WYSZUKAJ.PIONOWO, INDEKS, PRZESUNIĘCIE i tak dalej. Możesz jednak używać formuł, które mogą działać bez odwołań (takich jak SUMA, JEŻELI, LICZBA itd.).
• W formule możesz użyć stałej. Na przykład, jeśli chcesz poznać prognozowaną sprzedaż, w przypadku której ma wzrosnąć o 10%, możesz użyć formuły =Sprzedaż*1,1 (gdzie 1,1 to stała).
• W formule tworzącej pole obliczeniowe obowiązuje kolejność pierwszeństwa. W ramach najlepszej praktyki użyj nawiasów, aby upewnić się, że nie musisz pamiętać kolejności pierwszeństwa.

Zobaczmy teraz przykład użycia zaawansowanej formuły do ​​utworzenia pola obliczeniowego.

Załóżmy, że masz zestaw danych, jak pokazano poniżej, i musisz pokazać prognozowaną wartość sprzedaży w tabeli przestawnej.

Do prognozowanej wartości należy przyjąć 5% wzrost sprzedaży dla dużych detalistów (sprzedaż powyżej 3 milionów) oraz 10% wzrost sprzedaży dla małych i średnich detalistów (sprzedaż poniżej 3 milionów).

Uwaga: podane tutaj liczby sprzedaży są fałszywe i zostały użyte do zilustrowania przykładów w tym samouczku.

Oto jak to zrobić:

• Zaznacz dowolną komórkę w tabeli przestawnej.
• Przejdź do Narzędzia tabel przestawnych -> Analiza -> Obliczenia -> Pola, elementy i zestawy.
• Z listy rozwijanej wybierz Pole obliczeniowe.
• W oknie dialogowym Wstaw obliczone pole:
  • Nadaj mu nazwę, wpisując ją w polu Nazwa.
  • W polu Formuła użyj następującej formuły: =JEŻELI(Region =”Południe”,Sprzedaż *1.05,Sprzedaż *1.1)
• Kliknij Dodaj i zamknij okno dialogowe.

Spowoduje to dodanie nowej kolumny do tabeli przestawnej z wartością prognozy sprzedaży.

Kliknij tutaj, aby pobrać zbiór danych.

Problem z polami obliczeniowymi tabeli przestawnej

Obliczone pole to niesamowita funkcja, która naprawdę zwiększa wartość tabeli przestawnej dzięki obliczeniom pola, jednocześnie zachowując skalowalność i łatwość zarządzania.

Istnieje jednak problem z polami obliczeniowymi tabeli przestawnej, który należy znać przed ich użyciem.

Załóżmy, że mam tabelę przestawną, jak pokazano poniżej, w której użyłem pola obliczeniowego, aby uzyskać prognozowane liczby sprzedaży.

Należy zauważyć, że sumy częściowe i sumy końcowe nie są poprawne.

Chociaż powinny one dodawać indywidualną wartość prognozy sprzedaży dla każdego sprzedawcy, w rzeczywistości jest to ta sama formuła pola obliczeniowego, którą utworzyliśmy.

Tak więc dla South Total, podczas gdy wartość powinna wynosić 22 824 000, South Total błędnie podaje ją jako 22 287 000. Dzieje się tak, ponieważ używa formuły 21 225 800 * 1,05, aby uzyskać wartość.

Niestety nie ma sposobu, aby to naprawić.

Najlepszym sposobem na poradzenie sobie z tym byłoby usunięcie sum częściowych i sum całkowitych z tabeli przestawnej.

Możesz także przejść przez kilka innowacyjnych obejść, które pokazała Debra, aby poradzić sobie z tym problemem.

Jak zmodyfikować lub usunąć pole obliczeniowe tabeli przestawnej?

Po utworzeniu pola obliczeniowego tabeli przestawnej możesz zmodyfikować formułę lub ją usunąć, wykonując następujące czynności:

• Zaznacz dowolną komórkę w tabeli przestawnej.
• Przejdź do Narzędzia tabel przestawnych -> Analiza -> Obliczenia -> Pola, elementy i zestawy.
• Z listy rozwijanej wybierz Pole obliczeniowe.
• W polu Nazwa kliknij strzałkę w dół (mała strzałka w dół na końcu pola).
• Z listy wybierz pole obliczeniowe, które chcesz usunąć lub zmodyfikować.
• Zmień formułę, jeśli chcesz ją zmodyfikować lub kliknij Usuń, jeśli chcesz ją usunąć.

Jak uzyskać listę wszystkich obliczonych formuł pól?

Jeśli tworzysz dużo pól obliczeniowych tabeli przestawnej, nie martw się o śledzenie formuły używanej w każdym z nich.

Excel pozwala szybko utworzyć listę wszystkich formuł używanych podczas tworzenia pól obliczeniowych.

Oto kroki, aby szybko uzyskać listę formuł wszystkich pól obliczeniowych:

• Zaznacz dowolną komórkę w tabeli przestawnej.
• Przejdź do Narzędzia tabel przestawnych -> Analiza -> Pola, elementy i zestawy -> Formuły list.

Gdy tylko klikniesz Formuły listy, program Excel automatycznie wstawi nowy arkusz roboczy, który będzie zawierał szczegóły wszystkich obliczonych pól/elementów użytych w tabeli przestawnej.

Może to być naprawdę przydatne narzędzie, jeśli musisz wysłać swoją pracę do klienta lub podzielić się nią z zespołem.

Przydatne mogą być również następujące samouczki dotyczące tabel przestawnych:

• Przygotowywanie danych źródłowych do tabeli przestawnej.
• Korzystanie z fragmentatorów w tabeli przestawnej programu Excel: przewodnik dla początkujących.
• Jak grupować daty w tabelach przestawnych w programie Excel.
• Jak grupować liczby w tabeli przestawnej w programie Excel.
• Jak filtrować dane w tabeli przestawnej w programie Excel.
• Jak zastąpić puste komórki zerami w tabelach przestawnych programu Excel.
• Jak zastosować formatowanie warunkowe w tabeli przestawnej w programie Excel.
• Pamięć podręczna przestawna w programie Excel - co to jest i jak najlepiej z niej korzystać?