Jednym z wykresów finansowych, polecanych w infografice 25 przydatnych wykresów Excel i Power BI, jest wykres odchylenia. Wykorzystuje on kombinację koloru czerwony-zielony i może zostać wzbogacony o znacznik celu. Czy da się zbudować taki wykres w poziomie i to najlepiej na tabeli przestawnej?

Dlaczego opierać wykres na tabeli przestawnej?

• Najefektywniejszy proces raportowy w Excelu zbudujemy z wykorzystaniem Power Query > Power Pivot > tabeli przestawnej.
• Na tabeli przestawnej działają fragmentatory, czyli najłatwiejsze do wstawienia i użytkowania filtry Excel.
• Nawet jeśli nie korzystasz z dobrodziejstw Power Pivot (z dowolnymi miarami w tabeli przestawnej), masz do dyspozycji Pole obliczeniowe.

3 ograniczenia wykresów przestawnych

1. Są mniej elastyczne – jesteśmy ograniczeni do pól tabeli przestawnej i nie możemy dodać niestandardowych serii do wykresu.
2. Jest ich mniej do wyboru – m.in. nie działają wykres punktowy oraz wszystkie nowe wykresy dodane do Excela 2016 / 365 – histogram, kaskadowy, skrzynka-wąsy, mapa drzewa itd.
3. Trudniej zapanować nad niestandardowymi ustawieniami (np. etykietami).

Krok 1: Przygotuj tabelę przestawną

Dążmy do przygotowania tabeli przestawnej, która będzie wyglądała następująco:

Sposób 1: Zwykła tabela przestawna

Mając dane dotyczące sprzedaży i planów, tworzymy 3 pola obliczeniowe:

Wykonanie OK = JEŻELI(Wykonanie>=Plan;Wykonanie;0)
Wykonanie nOK = JEŻELI(Wykonanie<Plan;Wykonanie;0)
% Realizacji = Wykonanie/Plan

Sposób 2: Tabela przestawna zbudowana z wykorzystaniem Power Pivot

Mając do dyspozycji Power Pivot, będziemy mieli o wiele większą elastyczność. Zadanie połączenia tabeli transakcyjnej Dane (sprzedażowe) i Plany może wymagać utworzenia modelu danych i dodania miar w języku DAX:

Wykonanie:=SUM(dane[Sprzedaż])
Plany:=SUM(plany[Plan])
Wykonanie OK:=IF([Wykonanie]>= [Plany];[Wykonanie];0)
Wykonanie nOK:=IF([Wykonanie] < [Plany];[Wykonanie];0)
% Realizacji:=[Wykonanie] / [Plany]

Krok 2: Wstaw wykres kombi

Wstawiamy wykres kombi (stanie się on automatycznie wykresem przestawnym), którego serie będą miały następujący układ:

Krok 3: Dodaj znacznik celu i usuń linię dla serii Plan

Sztuczka polega na tym, aby domyślny znacznik wykresu liniowego ze znacznikami zastąpić poziomą linią. W tym celu rysujemy zwykłym kształtem krótką czarną linię > Ctrl + C > zaznaczamy znacznik na wykresie > Ctrl + V.

Następnie ustawiamy kontury samej linii na brak konturu.

Krok 4: Dodaj etykiety danych dla % Realizacji

Możesz dodać je prawym przyciskiem lub plusem przy wykresie.

Sformatuj etykiety tak, aby były powyżej punktu, zaś samą serię danych ukryj (kontur = brak).

Krok 5: Nadaj kolory czerwony-zielony

Tu zastosowanie ma zwykłe formatowanie koloru.

Krok 6: Ukryj przyciski pól na wykresie i usuń legendę

Osobiście nie lubię tych wszystkich dodatków na wykresach przestawnych i je wyłączam. Nie potrzebujemy też legendy.

Krok 7: Stwórz wykres słupkowy (opcjonalne)

Jeśli chcesz, aby wykres z kolumnowego stał się słupkowym, pozostało kilka kroków:

Obróć wszystkie etykiety w górę.

Zaznacz komórki pod wykresem > Ctrl + C > prawy przycisk myszy w pustej komórce > Wklej specjalnie > Jako obraz połączony.

Obróć obraz.

Pobierz szablon wykresu Excel i wstaw Twoje dane

Tu możesz pobrać szablon wykresu w wariancie zwykłej tabeli przestawnej i tabeli przestawnej z Power Pivot.