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

Są mniej elastyczne – jesteśmy ograniczeni do pól tabeli przestawnej i nie możemy dodać niestandardowych serii do wykresu.
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.
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.