Tabele przestawne: dodawanie pól i elementów obliczeniowych

Z lekcji dowiesz się:

• Jak w TP wstawić elementy obliczeniowe?

• Komu przydatne będą funkcjonalności obliczeniowe?

• Przy jakich zadaniach wykorzystywać nowe pola z obliczeniami?

Zaczniemy od prostego przykładu w arkuszu Pole, gdzie w tabeli z danymi źródłowymi mamy kolumnę Sprzedaż i Koszt (rys. 1).

Rys. 1 Dane źródłowe

Na podstawie danych źródłowych została stworzona tabela przestawną przedstawiona na rys. 2, która podsumowuje sprzedaż i koszty według produktów.

Rys. 2 Tabela przestawna

   Na podstawie powyższych danych będziemy chcieli obliczyć dochód. Możemy dołożyć do tabeli z rys. 1 kolejną kolumnę, ale lepiej będzie stworzyć pole obliczeniowe w tabeli przestawnej z rys. 2, żeby wykonać odpowiednie obliczenia bezpośrednio w niej.

W tym celu zaznaczamy tabelę przestawną, czyli ustawiamy aktywną komórkę w jej obrębie i rozwijamy polecenie Pola, elementy i zestawy (punkt 2 na rys. 3) z karty Analiza tabeli przestawnej (punkt 1), następnie wybieramy polecenie Pole obliczeniowe (punkt 3).

Rys. 3 Ścieżka dostępu do polecenia Pole obliczeniowe

Otworzy nam się okno Wstawianie pola obliczeniowego, gdzie w polu Nazwa wpisujemy Dochód (punkt 1 na rys. 4), bo takie właśnie pole chcemy stworzyć. Natomiast w polu Formuła wpisujemy prostą formułę (punkt 2). Dochód obliczamy odejmując od Sprzedaży Koszt, więc zapis formuły powinien wyglądać następująco:

=Sprzedaż – Koszt

Nazwy pól możemy wpisać ręcznie albo wykorzystać nazwy z obszaru Pola (punkt 3). Pola i elementy obliczeniowe działają na wartościach podsumowanych w tabeli przestawnej. Tak ustawione parametry nowego pola obliczeniowego zatwierdzamy przyciskiem OK.

Rys. 4 Okno Wstawiania pola obliczeniowego (Dochód)

Do tabeli przestawnej zostanie dodana nowa kolumna o nazwie Suma z Dochód, co widać na rys. 5.

Rys. 5 Tabela przestawna z nową kolumną (Suma z Dochód)

Jak widać nowe pole ma inne formatowanie (może się tak czasem zdarzyć, gdy Excela zapamiętał wcześniejsze formatowania komórek – nie zostały one poprawnie wyczyszczone). Żeby to poprawić zaznaczamy komórkę w kolumnie Suma z Dochód, następnie klikamy prawym przyciskiem myszy i z podręcznego menu wybieramy polecenie Format liczby (rys. 6).

Rys. 6 Polecenie Format liczby

Otworzy się okno Formatowania komórek, gdzie w polu Kategoria wybieramy Walutowe, następnie w polu Miejsca dziesiętne ustawiamy wartość 0 (zero). Tak ustawione parametry Formatowania liczbowego zatwierdzamy przyciskiem OK (rys. 7).

Rys. 7 Okno Formatowania komórek (walutowe)

Otrzymamy odpowiednio sformatowane dane w tabeli przestawnej przedstawione na rys. 8.

Rys. 8 Tabela przestawna po zmianie formatowania liczbowego w kolumnie Suma z Dochód

Pole obliczeniowe możemy ponownie wykorzystać, ponieważ dodało się ono do Pól tabeli przestawnej widocznych w panelu bocznym (rys. 9).

Rys. 9 Pole obliczeniowe dodane do pól tabeli przestawnej

Ponownie rozwijamy polecenie Pola, elementy i zestawy z karty Analiza tabeli przestawnej, następnie wybieramy polecenie Pole obliczeniowe (jak na rys. 3). Otworzy się okno Wstawiania pola obliczeniowego, gdzie w obszarze Pola mamy dodany wcześniej stworzony Dochód. Chcemy teraz stworzyć pole o nazwie % Dochód (procentowy dochód), więc wpisujemy ją w polu Nazwa. W polu Formuła wpisujemy stosunek Dochodu do Sprzedaży, czyli zapis powinien wyglądać następująco:

=Dochód / Sprzedaż

Stworzenie nowego pola obliczeniowego zatwierdzamy przyciskiem OK (rys. 10).

Rys. 10 Okno Wstawiania pola obliczeniowego (% Dochód)

W tabeli przestawnej pojawi się nowa kolumna z danymi (pole) o nazwie Suma z % Dochodu (rys. 11).

Rys. 11 Tabela przestawna z nową kolumną (Suma z % Dochód)

Na nową kolumnę powinniśmy nałożyć odpowiednie formatowanie, w tym celu zaznaczamy dane, klikamy prawym przyciskiem myszy i z podręcznego menu wybieramy polecenie Format liczby (jak na rys. 6).

Otworzy się okno Formatowania komórek, gdzie w polu Kategoria ustawiamy Procentowe, następnie w polu Miejsca dziesiętne ustawiamy 2 miejsca po przecinku. Tak ustawione parametry formatowania zatwierdzamy przyciskiem OK (rys. 12).

Rys. 12 Okno Formatowania komórek (procentowe)

Otrzymamy sformatowaną procentowo kolumnę Suma % Dochodu przedstawioną na rys. 13.

Rys. 13 Tabela przestawna po zmianie formatowania liczbowego w kolumnie Suma z % Dochód

W ten sposób otrzymaliśmy obliczone elementy bez dodawania dodatkowych kolumn do danych źródłowych.

Przechodzimy teraz do arkusza Element, gdzie mamy tabelę z danymi źródłowymi oraz stworzoną na ich podstawie tabelę przestawną (rys. 14).

Rys. 14 Dane źródłowe oraz tabela przestawna

Teraz zajmiemy się dodawaniem elementów obliczeniowym, który jest można powiedzieć o poziom niżej niż pole obliczeniowe. Pole w tabeli przestawnej to cała kolumna danych źródłowych, a element to np. dla kategorii Kraj – Niemcy czy Francja, czyli unikatowa wartość, która znajduje się w danej kolumnie.

Naszym celem będzie stworzenie elementu, który zsumuje sprzedaż z kategorii Internet, Partnerzy i Sklep, a pominie kategorię Anulowana. Interesuje nas sumaryczna sprzedaż z tych trzech elementów, bez rozpisywania na poszczególne elementy.

Przede wszystkim zaznaczamy jeden z elementów obliczeniowych (np. Internet) w tabeli przestawnej, następnie rozwijamy polecenie Pola, elementy i zestawy (punkt 2 na rys. 15) z karty Analiza tabeli przestawnej (punkt 1) i wybieramy polecenie Element obliczeniowy (punkt 3).

Rys. 15 Ścieżka dostępu do polecenia Element obliczeniowy

Otworzy się okno o nazwie Wstaw element obliczeniowy w "Status", gdzie w polu Nazwa wpisujemy np. Sprzedaż, która oznacza sprzedaż sumaryczną ze wszystkich kanałów sprzedaży. Jak widać na rys. poniżej oprócz sekcji Pola, mamy również sekcję Elementy. W polu Formuła napiszemy proste dodawanie trzech elementów. Zapis formuły powinien wyglądać następująco:

= Internet + Partnerzy + Sklep

Zamiast wpisywać nazwy elementów ręcznie możemy kliknąć dwukrotnie w nazwy z obszaru Elementy albo skorzystać z przycisku Wstaw element. Dzięki temu mamy pewność, że nie zrobimy literówki w odwołania. Tak ustawione parametry nowego elementu zatwierdzamy przyciskiem OK (rys. 16).

Rys. 16 Okno Wstawiania elementu obliczeniowego w "Status" (Sprzedaż)

Otrzymaliśmy elementy dodane do tabeli przestawnej w kolumnie Status przedstawiony na rys. 17.

Rys. 17 Tabela przestawna z dodanymi elementami obliczeniowymi (Sprzedaż)

Bardzo ważne jest, żeby pamiętać, że każdy taki dodany element obliczeniowy wpływa na obliczenia w tabeli przestawnej. Sumy częściowe dla poszczególnych krajów biorą pod uwagę zarówno te wyszczególnione dane (osobno Internet, Partnerzy i Sklep), jak i sumaryczną Sprzedaż. Aby otrzymać prawidłowe wyniki, które będą podliczały tylko element Sprzedaż, wystarczy odpowiednio odfiltrować dane. W tym celu klikamy na ikonkę z trójkątem obok nazwy kolumny Status i odznaczamy checkboxy przy odpowiednich danych (Internet, Partnerzy i Sklep). Tak ustawione filtrowanie zatwierdzamy przyciskiem OK (rys. 18).

Rys. 18 Odfiltrowanie danych

W efekcie otrzymamy odfiltrowaną tabelę przestawną, która podlicza tylko zsumowaną sprzedaż z tych trzech elementów przedstawiona na rys. 19.

Rys. 19 Odfiltrowana tabela przestawna

Dzięki odfiltrowaniu danych otrzymaliśmy prawidłowe wyniki, bez zbędnych danych, które mogły wpływać na nasze sumaryczne wyniki w tabeli przestawnej.

Teraz przechodzimy na arkusz JEŻELI, gdzie pokażemy jak w elementach i polach obliczeniowych stosować proste funkcje (np. funkcja JEŻELI). Będziemy pracować na danych źródłowych i tabeli przestawnej z rys. 20.

Rys. 20 Dane źródłowe i tabela przestawna

Zakładamy, że chcemy sprawdzić procentowy stosunek sprzedaży w Niemczech do sprzedaży w Hiszpani. Dane przygotowaliśmy tak, że nie ma danych ze sprzedaży w Hiszpanii w styczniu (mamy pustą komórkę). Jednak nie chcemy otrzymać błędu, ponieważ nie możemy dzielić przez zero. W tym celu dodamy prostą funkcję JEŻELI. Najpierw musimy dodać nowy element obliczeniowy. W tym celu rozwijamy polecenie Pola, elementy i zestawy z karty Analiza tabeli przestawnej i wybieramy polecenie Element obliczeniowy (jak na rys. 15).

Otworzy się okno o nazwie Wstaw element obliczeniowy w "Kraj", gdzie w polu Nazwa wpisujemy np. % Niemcy, która oznacza procentowy stosunek sprzedaży w Niemczech do sprzedaży w Hiszpanii. W polu Formuła użyjemy funkcji JEŻELI. Zapis formuły powinien wyglądać następująco:

= JEŻELI(Hiszpania=0;0;Niemcy/Hiszpania)

Musimy pamiętać, że do zapisu formuły używamy nazw z obszaru Elementy, a nie wpisujemy ich ręcznie. Tak ustawione parametry nowego elementu zatwierdzamy przyciskiem OK (rys. 21).

Rys. 21 Okno Wstawiania elementu obliczeniowego w "Kraj" (%Niemcy)

Otrzymamy tabelę przestawną z dołożonymi elementami w kolumnie Kraj o nazwie %Niemcy przedstawioną na rys. 22.

Rys. 22 Tabela przestawna z elementami obliczeniowymi %Niemcy

Istotne jest, że jeśli mamy nałożone formatowanie dla całego pola, to nie możemy standardowo ustawić formatowania liczbowego. W pierwszej kolejności musimy zaznaczyć trzy osobne komórki %Niemcy przytrzymując klawisz Ctrl, potem rozwijamy style formatowania w grupie poleceń Liczba z karty Narzędzia główne, następnie wybieramy Procentowe (rys. 23).

Rys. 23 Zmiana formatowania na procentowe

Otrzymamy tabelę przestawną ze zmienionym formatowaniem w zaznaczonych komórkach przedstawioną na rys. 24.

Rys. 24 Tabela przestawna ze zmienionym formatowaniem w zaznaczonych komórkach

Jeśli chcemy edytować lub usunąć element obliczeniowy, musimy tak jak przy tworzeniu nowego elementu rozwinąć polecenie Pola, elementy i zestawy z karty Analiza tabeli przestawnej, a następnie wybrać polecenie Element obliczeniowy. Otworzy się okno Wstaw element obliczeniowy w "Kraj", gdzie w polu Nazwa musimy na liście rozwijanej odnaleźć interesujący nas element. Możemy w polu Formuła zmodyfikować jego działanie, a następnie zatwierdzić zmiany za pomocą przycisku Modyfikuj lub całkowicie go usunąć za pomocą przycisku Usuń (rys. 25).

Rys. 25 Modyfikacja lub usuwanie elementu obliczeniowego

Jeśli zależy nam na liście wszystkich formuł dotyczących wszystkich pól i elementów obliczeniowych, które opierają się o to samo źródło danych, to musimy rozwinąć polecenie Pola, elementy i zestawy (punkt 2 na rys. 26) z karty Analiza tabeli przestawnej (punkt 1), a następnie wybrać polecenie Lista formuł (punkt 3).

Rys. 26 Ścieżka dostępu do polecenia Lista formuł

Utworzy się nowy arkusz, gdzie będziemy mieć przedstawione wszystkie użyte formuły (zarówno dla pól jak i elementów obliczeniowych) korzystających z tego samego źródła danych co aktywna tabela przestawna (rys. 27). Co istotne mamy wypisane dwie formuły, ponieważ element stworzony w arkuszu o nazwie Element opierał się na tych samych danych źródłowych co element z arkusza JEŻELI.

Rys. 27 Lista formuł elementów obliczeniowych opierających się na tych samych danych źródłowych

Polecamy także:

Konsolidacja danych: jak sprytnie połączyć różniące się raporty?

Jak radzić sobie z błędami w Power Query?

Adam Kopeć