Jednym z najbardziej pożądanych narzędzi specjalnie zaprojektowanych do analizy dużych zbiorów danych jest tabela przestawna (PivotTable). Jej główną zaletą jest to, że pozwala dosłownie w mgnieniu oka zrozumieć jakie relacje zachodzą pomiędzy poszczególnymi grupami danych, co w przypadku tradycyjnej tabeli byłoby niezwykle trudne, jeżeli nie niemożliwe. Tabela przestawna jest szybka w działaniu, elastyczna, bardzo dokładna i nadaje się do zastosowania w każdym środowisku biznesowym. W dzisiejszym artykule przedstawiam informacje, które pozwolą Ci opracowywać tabele przestawne na jeszcze wyższym poziomie zaawansowania.
Wyobraź sobie taką sytuację. Dostajesz od przełożonego „surowe” dane zawierające informacje na temat sprzedaży, jaką wygenerowali przedstawiciele handlowi w ciągu pierwszych jedenastu miesięcy bieżącego roku. Twój szef chce, abyś skonstruował tabelę przestawną, w której przedstawisz informację o średniej cenie jednostkowej po jakiej każdy handlowiec sprzedawał produkt X. Ponadto musisz obliczyć wysokość premii, która jest uzależniona od osiągniętej sprzedaży. I tak, jeśli sprzedaż nie przekroczyła 1 mln zł to premia ma wynieść 0,75% wartości sprzedaży wygenerowanej przez przedstawiciela handlowego, w przeciwnym razie (sprzedaż powyżej 1 mln zł) wypłacona będzie premia na poziomie 2,5% wartości sprzedaży.
Na przygotowanie zestawienia masz czas do końca dniówki. Myślisz sobie, że to nic trudnego, ale szef postawił jeden warunek – nie wolno Ci umieścić żadnych dodatkowych kolumn w danych źródłowych. Jedyne co możesz zrobić to zastosować tabelę przestawną ze wszystkimi jej funkcjonalnościami.
Jak się zabrać do tego zadania? Co jest Ci potrzebne, żeby zaimponować szefowi swoimi excelowymi umiejętnościami? Odpowiedź jest jedna: pole obliczeniowe.
Pole obliczeniowe – krok po kroku
Pole obliczeniowe to pole w tabeli przestawnej wykorzystujące formułę utworzoną przez użytkownika. Pola obliczeniowe mogą wykonywać obliczenia na zawartości (sumach) innych pól tabeli przestawnej.
Zakładając, że mamy już przygotowaną wyjściową tabelę przestawną wykorzystującą tylko dwa elementy, a mianowicie Sprzedawca oraz Sprzedaż w zł dodamy teraz pole obliczeniowe reprezentujące średnią cenę jednostkową produktu X, po jakiej sprzedawał każdy przedstawiciel handlowy.
Aby to uczynić postępuj według następujących kroków:
KROK 1: Zaznacz dowolną komórkę tabeli przestawnej, aby uruchomić pasek Narzędzia tabel przestawnych, który pojawi się nad wstążką Excela.
KROK 2: Kliknij kartę Analiza (umieszczoną pod wspomnianym paskiem), w grupie Obliczenia kliknij Pola, elementy i zestawy, a następnie wybierz polecenie Pole obliczeniowe.
KROK 3: W oknie dialogowym Wstawianie pola obliczeniowego wprowadź nazwę dla tworzonego pola, natomiast w pasku Formuła wpisz formułę obliczającą średnią cenę jednostkową. Pamiętaj, aby każdą formułę zaczynać od znaku równości (=).
Nasza formuła wygląda następująco:
=’Sprzedaż w zł’/’Sprzedaż w szt.’
Aby wprowadzić formułę wciśnij klawisz znaku równości, następnie w oknie Pola wskaż element Sprzedaż w zł i kliknij przycisk Wstaw pole. Teraz wprowadź znak dzielenia (/), po czym w oknie Pola wybierz element Sprzedaż w szt. i kliknij przycisk Wstaw pole. Jeżeli nie wprowadzasz innych pól obliczeniowych do tabeli przestawnej, to naciśnij przycisk OK, w przeciwnym razie możesz kliknąć przycisk Dodaj, aby rozpocząć definiowanie nowego pola obliczeniowego.
Zauważ, że wszystkie pola, w których nazwie znajduje się odstęp (spacja), w formule występują ograniczone z obu stron znakiem apostrofu (’). Warto o tym pamiętać, gdybyś wpisywał całą formułę ręcznie.
KROK 4: Gotowe! Tabela przestawna wyświetla teraz poza sumą sprzedaży również średnią cenę jednostkową produktu X po jakiej był sprzedawany przez każdego z handlowców.
Jak widzisz dodawanie dodatkowych informacji do pivota jest jak najbardziej możliwe i to bez „dotykania” danych źródłowych.
Teraz czas na obliczenie premii. Dla przypomnienia, wypłacamy 2,5% premii od wartości sprzedaży, jeśli ta przekroczyła 1 mln zł. W przeciwnym razie premia wynosi tylko 0,75%.
Aby dodać pole Premia:
KROK 1: Kliknij kartę Analiza, w grupie Obliczenia kliknij Pola, elementy i zestawy, a następnie wybierz polecenie Pole obliczeniowe.
KROK 2: W oknie dialogowym Wstawianie pola obliczeniowego wprowadź nazwę Premia oraz zdefiniuj formułę, która będzie odpowiedzialna za prawidłowe wyliczenie premii dla handlowców. Nie zapomnij o znaku równości (=) na początku formuły.
Potrzebujesz pomocy z formułą? Nie ma sprawy! Oto ona:
= JEŻELI(‘Sprzedaż w zł’>1000000;0,025;0,0075)*’Sprzedaż w zł’
Użyta formuła nie jest skomplikowana. Bada tylko jeden warunek (sprzedaż powyżej 1 mln zł) i w zależności od tego czy jest on spełniony czy nie oblicza stosowny procent od sprzedaży jako premię dla handlowca.
Oczywiście moglibyśmy skomplikować zadanie i uzależnić wysokość premii nie tylko od wielkości sprzedaży w zł, ale również od ilości sprzedanych sztuk (np. premia powyżej 35 tys. szt). W takim przypadku nasza formuła wyglądałaby następująco:
= JEŻELI(ORAZ(‘Sprzedaż w zł’>1000000;’Sprzedaż w szt.’>35000);0,025;0,0075)*’Sprzedaż w zł’
Pozostańmy jednak przy pierwotnych założeniach na potrzeby tego artykułu.
Zadanie przekazane przez przełożonego wydawało się trudne, a tym czasem dane są gotowe do prezentacji. Utworzyłeś dwa pola obliczeniowe – pierwsze ze średnią ceną jednostkową, drugie z wysokością premii.
Już masz ochotę odesłać gotowy skoroszyt do szefa, zrobiłeś wszystko bez ruszania danych źródłowych i w dodatku skończyłeś przed czasem. Ale czy nie poszło zbyt łatwo? Spójrz jeszcze raz na tabelę przestawną zanim naciśniesz przycisk Wyślij wiadomość.
Pole obliczeniowe – niespodziewane problemy
Tworząc pole obliczeniowe spodziewałeś się zobaczyć sumę obliczonych kwot zarówno w sumach częściowych jak i sumach końcowych tabeli przestawnej (w naszym przykładzie mamy tylko sumy końcowe). Jednakże przyglądając się im nieco bardziej z bliska dochodzisz do wniosku, że zamiast sum w sumach częściowych i sumach końcowych pole obliczeniowe dokonuje takich samych obliczeń jak na pozostałych elementach. Niestety ten błąd występuje w Excelu i póki co nie został poprawiony. W naszym przykładzie suma końcowa dla Premii to nic innego jak 2,5% całkowitej sprzedaży, czyli 279 445 zł. A powinno być 231 498 zł.
Jak w takim razie poradzić sobie z tym problemem? Niestety w opcjach tabel przestawnych nie ma żadnych ustawień, które pozwoliłyby sumować pola obliczeniowe zamiast stosowania formuły z tego pola w sumach (końcowych czy częściowych).
Czy w takim razie jest jakieś rozwiązanie? Kiedy słyszę to pytanie na szkoleniach, zawsze pojawia się uśmiech na mojej twarzy i odpowiadam, że w Excelu każdy problem można rozwiązać przynajmniej na trzy (3) sposoby. Tak też jest i w tym przypadku.
Pierwszym jest użycie filtra. Aby ukryć wiersze nie spełniające warunku niezbędnego do otrzymania premii, przefiltruj sprzedawców używając filtra wartości określając, że chcesz wyświetlać tylko wartości większe niż 1 mln zł. Z tak określonym filtrem, suma końcowa będzie prawidłowa.
Drugim rozwiązaniem jest usunięcie sum końcowych. Aby zrobić to szybko, kliknij prawym klawiszem myszy na etykiecie Suma końcowa, a następnie wybierz polecenie Usuń sumę końcową.
Trzecie rozwiązanie przewiduje założenie, że nie chcesz lub nie możesz pozbyć się sum końcowych. W takim przypadku będziesz musiał ją obliczyć poza tabelą przestawną, w kolumnie na prawo. W naszym przykładzie nie jest to trudne. Wystarczy użyć formuły:
=JEŻELI(K2=””;0;K2)
a w wierszu sumy:
=SUMA(L2:L11)
Teraz należy ukryć kolumnę Suma Premia i gotowe. Wyliczenia są prawidłowe.
Tylko od Ciebie zależy, która forma „naprawienia” wartości liczbowych (sum częściowych i końcowych) zostanie użyta. Jeśli dokonałeś już niezbędnych korekt, to najwyższy czas przesłać gotowy raport szefowi i czekać na… awans/premię/podwyżkę/pochwałę (niepotrzebne skreślić). 🙂
Cieszę się, że pomimo trudnego tematu jakim są pola obliczeniowe (przynajmniej na początku) dotarłeś do końca artykułu. Mam nadzieję, że pomoże Ci on wspinać się na wyżyny excelowych umiejętności. Chciałbym Cię również prosić o podzielenie się ze mną i innymi Czytelnikami Twoimi doświadczeniami w pracy z tabelami przestawnymi. Dlaczego je lubisz lub nie znosisz? Co sprawia Ci największą trudność, albo jakiej funkcjonalności brakuje w nich?
Tradycyjnie zamieszczam link do skoroszytu, z którego korzystałem pisząc ten tekst. Jeśli chciałbyś poćwiczyć właśnie na nim, to kliknij na link poniżej i otwórz lub zapisz na dysku.
Pobierz plik: Pole obliczeniowe
I na koniec mam do Ciebie jeszcze jedną gorącą prośbę. Otóż przekaż proszę informację o tym artykule swoim znajomym lub współpracownikom, aby i oni mogli czerpać pełnymi garściami z prezentowanej tu wiedzy. Nie zapomnij też wspomnieć na Facebooku, Twitterze czy Google+ skąd czerpiesz informacje na temat Excela. Dziękuję!