Pole obliczeniowe jest użyteczne w przypadku chęci dodania do tabeli przestawnej w Excelu (pivot table) wskaźnika, którego nie ma w danych źródłowych. Oczywiście jest możliwość dodania w danych źródłowych odpowiednich kategorii i danych, ale szybszą metodą, jeżeli dodatkowe dane chcemy pokazywać tylko w tabeli przestawnej, będzie dodanie pola obliczeniowego.

1. Wykorzystanie pola obliczeniowego do obliczenia różnicy między Sprzedażą a Zyskiem

Poniższa tabela przestawna prezentuje wartość sprzedaży oraz zysk ze sprzedaży dla poszczególnych segmentów rynku oraz kategorii produktu.  Dodatkowo będziemy chcieli pokazać wszelkie koszty (koszty wytworzenia/zakupu, koszty ogólne zarządu, koszty sprzedaży), które stanowią różnicę między wartością sprzedaży a właśnie zyskiem. Moglibyśmy oczywiście dodać koszty w danych źródłowych. Jest to jakieś rozwiązanie. Co jednak, gdy zmianę chcemy wprowadzić szybko bez bezpośredniej ingerencji w dane źródłowe? Do obliczeń wykorzystamy pole obliczeniowe.

Pole obliczeniowe wstawia się wybierając: kartę Analiza > Pola, elementy i zestawy > Pole obliczeniowe.

Pojawi się okno, w którym mamy możliwość wpisania nazwy nowego pola oraz wpisania formuły, według której wartości w polu będą się wyliczać. Formuła może się składać ze wszystkich kolumn dostępnych w źródle.

Pole obliczeniowe będzie nosiło nazwę Koszty i będzie obliczać się jako różnica pomiędzy Sprzedażą a Zyskiem.

Dodane pole obliczeniowe prezentuje się jak poniżej.

2. Pole obliczeniowe na zgrupowanych danych

Poniższa tabela przestawna zawiera Zysk w podziale na lata oraz wielkość poszczególnych zamówień.

Taka szczegółowość wierszy zaburza obraz, dlatego wielkość zamówień zostanie pogrupowana do ‘paczek’ po 30.

Grupy zostaną następnie odpowiednio ponazywane: 1-30, 31-60, 61-90, 91-120, 121-150 oraz >150.

Pogrupowane dane prezentują się jak poniżej.

Do tabeli przestawnej zostanie dodane jeszcze pole ze Sprzedażą.

Kolejnym krokiem będzie dodanie pola obliczeniowego.

Nazwa pola to Zysk ze sprzedaży [%] a formuła to stosunek Zysku do Sprzedaży.

Po zmianie formatu nowych danych na procentowy gotowa tabela przestawna będzie prezentować się jak poniżej.

 

3. Jednostkowy koszt dostawy

Poniższa tabela zawiera Koszt dostawy oraz Wielkość zamówienia w podziale na lata, Rodzaj transportu oraz Priorytet zamówienia.

Pole obliczeniowe zostanie wykorzystane do obliczenia jednostkowego kosztu dostawy.

Jednostkowy koszt dostawy zostanie obliczony jako stosunek Kosztu dostawcy do Wielkości zamówienia.

Obliczony Jednostkowy koszt dostawy prezentuje się jak poniżej.

 

4. Element obliczeniowy – zmiana vs poprzedni rok

Nie tylko pole obliczeniowe może być wykorzystane do obliczeń w tabeli przestawnej. W poniższym przykładzie zostanie wykorzystany element obliczeniowy. Element obliczeniowy działa na podobnej zasadzie do pola, jednakże obliczenia można dokonać na bardziej szczegółowych danych.

 

Element obliczeniowy zostanie wykorzystany do obliczenia różnicy na wybranych wartościach pomiędzy 2014 a 2013 rokiem. Stojąc na Roku wybieramy na karcie Analiza > Element obliczeniowy.

Następnie pojawia się podobne okno do tego, które dotyczyło Pola obliczeniowego. Jest jednak bardziej szczegółowe i można zauważyć, że do każdego z dostępnych pól są przyporządkowane elementy – lista pojedynczych wartości z każdej kolumny z danych źródłowych.

Nazwą elementu będzie 2014 vs 2013 [%]. A formuła to stosunek wartości dla 2014 roku do roku poprzedniego, pomniejszone o 1.

 

Tabela przestawna powiększyła się o trzy dodatkowe kolumny, które zwracają procentową zmianę między dwoma latami. Obliczenia dotyczą wybranych wierszy.

Format nowych danych zmieniamy na procentowy.

 

5. Udział danych rocznych w sumie

Tabela przestawna zawiera wartość sprzedaży dla wszystkich lat dla poniższych regionów i województw.

Element obliczeniowy przeliczy udział wartości sprzedaży w roku 2014 do wartości sprzedaży w czterech dostępnych latach.

Nazwa elementu to Udział 2014 w Totalu,  formuła to stosunek 2014 roku do sumy wszystkich lat.

Po dodaniu elementu tabela przestawna prezentuje się jak poniżej.

Ograniczenia pola obliczeniowego

• Pole obliczeniowe umożliwia zastosowanie podstawowych działań, takich jak: dodawanie, odejmowanie, dzielenie, mnożenie oraz funkcji JEŻELI
• Pola sumy częściowe i końcowe są liczone wg tej samej formuły, co pojedyncza komórka tabeli przestawnej (a nie jako suma komórek podrzędnych), co może nie pokrywać się z naszymi oczekiwaniami. W takich przypadkach należy je ukryć.
• W kalkulacjach nie można odnosić się do sum częściowych i końcowych (to zapewnia opcja Pokaż wartości jako…), ani do komórek spoza tabeli przestawnej
• Pola kalkulowane nie są dostępne w tabelach przestawnych opartych o kostki OLAP
• Element obliczeniowy nie sprawdza się w przypadku chęci obliczenia różnicy w punktach procentowych między prezentowanymi wartościami procentowymi – pojawiają się błędy obliczeń.

Co, jeśli pole obliczeniowe nie daje właściwych rezultatów?

Rozważ skorzystanie z języka DAX w modelu danych Power Pivot.