Tabela przestawna - zastosowanie pola obliczeniowego

Tabela przestawna przez wielu z nas wykorzystywana jest jako przyjemne w obsłudze narzędzie, służące do agregowania w jednej tabeli wielu różnych danych. Co jednak w przypadku, gdy na danych źródłowych dokonaliśmy już obliczeń i je również chcemy zaprezentować w tabeli? Czy mamy pewność, że zaprezentowane dane będą poprawne?
Jak widać na poniższym przykładzie dla takich samych danych, stosując dwa sposoby obliczeń, można osiągnąć dwa różne wyniki.

Spójrzmy na poniższy przykład, dane bazowe:

Ostatnia kolumna jest formułą wyliczającą narzut, czyli Sprzedaż / Zakup -1

Z tak przygotowanych danych stworzona została tabela przestawna, gdzie wyliczyliśmy średni narzut na zrealizowanym zamówieniu, który kształtuje się na poziomie 4%.


Jak można jednak łatwo zauważyć, łączna kwota za jaką sprzedaliśmy towar jest równa kwocie za jaką go kupiliśmy, stąd narzut powinien wynosić 0%, a nie przedstawione 4%.

Co jest powodem takiej sytuacji i jak temu zaradzić?

Powodem takiej sytuacji jest fakt, iż tabela przestawna nie oblicza narzutu tak jak zrobiliśmy w tabeli wejściowej (czyli Sprzedaż / Zakup - 1), a jedynie oblicza średnią z danych, które znajdują się w kolumnie „% narzutu”. Innymi słowy, wyciąga średnią z 25% narzutu Produktu A i z -17% Produktu B, co daje (25%-17%)/2 = 4%, zamiast oczekiwanego 0%.

Prawidłowy wynik otrzymamy tylko wtedy, gdy tabela najpierw zsumuje wartości zakupu i sprzedaży, a dopiero na tych danych dokona kalkulację. Taką funkcjonalność daje nam wprowadzenie Pola Obliczeniowego.

W tym celu należy kliknąć na tabeli przestawnej i na pasku zadań Narzędzia Tabel Przestawnych | Analiza | Obliczenia wybrać Pola, elementy i zestawy > Pole obliczeniowe. W nowo otwartym oknie ustawiamy parametry naszego pola: nazwę oraz formułę, która ma być wykonywana. W naszym przypadku będzie to:

• nazwa: %_narzutu (nazwa nie może się pokrywać z innym polem tabeli), 

• formuła: wpisujemy =Sprzedaż /Zakup -1 (zamiast wpisywać ręcznie nazwy pól tabeli przestawnej, możemy wybrać je z dostępnej niżej listy pól). 

Po naciśnięciu przycisku Dodaj, nasze pole obliczeniowe pojawi się jako kolejne pole do umieszczenia w tabeli.



Po dodaniu nowoutworzonego pola, finalnie uzyskujemy tabelę z poprawnymi wynikami.

Można również zaproponować inne rozwiązanie.

Alternatywnym sposobem uzyskania poprawnych wyników jest stworzenie tabeli przestawnej jedynie z danymi zakup i sprzedaż oraz wykonanie obliczenia marży w komórkach obok tabeli. Rozwiązanie to ma jednak kilka wad: 

• Dodatkowe komórki należy osobno formatować (formatowanie tabeli przestawnej nie obejmuje tych komórek),

• Przy zmianie ilości danych do analizy należy ręcznie dostosować ilość komórek z obliczeniami (może być ich za dużo, bądź za mało),

• Rozszerzenie tabeli przestawnej o jedną lub więcej kolumn spowoduje usunięcie naszych obliczeń, bądź będzie wymagało ich przesunięcia.

Z tego powodu rozwiązanie alternatywne sprawdzi się w przypadku jednorazowych analiz, jednak w przypadku cyklicznych opracowań zalecałbym zastosowanie pola obliczeniowego.