Formatowanie warunkowe w tabeli przestawnej

Nasz histogram marginalny, będzie powiązany z tabelą przestawną, w której wykorzystamy opcję tzw. mapy cieplnej, utworzonej za pomocą formatowania warunkowego. A zatem, z naszych danych w zakładce "Dane", tworzymy tabelę przestawną rozmieszczając poszczególne pola jak na rysunku.

 

 

Następnie zaznaczamy komórkę E7 i wybieramy dla niej formatowanie warunkowe w postaci skali kolorów "zielony-biały".

 

 

Po dodaniu formatowania, w prawym dolnym rogu komórki, pojawi się ikonka podpowiadająca nam, dla jakiego obszaru chcemy zastosować formatowanie. Z jej opcji wybieramy rozszerzenie formatowania na pełen zakres tabeli, z pominięciem podsumowań globalnych.

 

 

Teraz możemy dodatkowo zwiększyć szczegółowość naszej mapy cieplnej, wyświetlając ją w skali, obejmującej 3 kolory. Dodatkowo możliwa jest również edycja kolorów - ja wybrałem odcienie koloru czerwonego.

 

 

W następnym kroku w Opcjach tabeli przestawnej, odznaczamy podane elementy na karcie Wyświetlanie. Elementy ukrywamy, gdyż nasza wizualizacja w opcjach filtrowania, będzie wykorzystywała fragmentatory tabeli przestawnej.

 

 

Przed przystąpieniem do prac związanych z wykresami, odpowiednio sformatujemy naszą tabelę. Po zaznaczeniu tabeli z karty Projektowanie --> Style tabeli przestawnej wybieramy styl, znajdujący się na samej górze listy o nazwie Jasny 1, a następnie po kliknięciu na niego PPM wybieramy Duplikuj, tworząc tym samym nowy styl. Styl nazywamy Pusta, a następnie za pomocą przycisku Wyczyść, czyścimy wszystkie elementy naszej tabeli, by za chwilę nadać im własny format. Dla elementu Cała tabela ustawiamy formatowanie w postaci obramowania, natomiast dla elementów: Etykiety filtrów raportu, Wartości filtrów raportu, Pierwsza kolumna, Wiersz nagłówka oraz Kolumna sumy końcowej formatowanie Obramowania ustawiamy na Brak konturu.

 

 

W naszej tabeli przestawnej ukryjemy jeszcze 2 pola. Pierwsze z nich to pole o nazwie Suma końcowa.

 

 

Drugim polem, które ukryjemy będzie pole o nazwie Suma z sprzedaż. W tym wypadku należy ustawić się kursorem w dowolnym polu z wartościami tabeli, a następnie ustawić nazwę pola również na znak "spacji".

 

 

 

W kolejnym kroku ukrywamy dane, tworzące naszą mapą cieplną, poprzez ustawienie ich formatowania niestandardowego oraz ustawienie koloru czcionki sumy głównej na kolor biały.

 

 

Nasza tabela jest już prawie gotowa. Teraz aby rozmiar tabeli nie dopasowywał się w sposób automatyczny po wybraniu danej wartości z filtra należy :

 - po kliknięciu na tabelę z jej Opcji odhaczyć w zakładce Układ i formatowanie opcję Automatycznie dopasuj szerokość kolumn podczas aktualizacji

 - w wierszach od C6:C16 wprowadzić przejście do kolejnej linii wciskając kombinację klawiszy Alt+Enter

 - wiersze i kolumny, w których znajdują się dane dopasować do wybranej szerokości/wysokości (ja wybrałem rozmiar 40 pikseli)

 

Zapisz

Zapisz

Przygotowanie przesuwających się wykresów

Do tak sformatowanej mapy cieplnej dodajemy histogram marginalny, a więc 2 wykresy, słupkowy i kolumnowy, które będą dopasowywać swoje położenie oraz zakres prezentowanych danych w stosunku do wybranego filtru. W tym celu z karty Formuły-->Menadżer nazw, dodajemy 2 nazwy zdefiniowane, które posłużą nam za źródło danych do wykresów. Pierwsza dla wykresu słupkowego o nazwie wykres_a =PRZESUNIĘCIE(Wykres!$D$6;1;ILE.NIEPUSTYCH(Wykres!$6:$6)-1;ILE.NIEPUSTYCH(Wykres!$D:$D)-2;1), a druga dla wykresu kolumnowego wykres_b =PRZESUNIĘCIE(Wykres!$D$6;ILE.NIEPUSTYCH(Wykres!$D:$D)-1;1;1;ILE.NIEPUSTYCH(Wykres!$6:$6)-2). Następnie tworzymy wykres słupkowy, w którego źródle danych podajemy odwołanie do pierwszego zakresu danych ='tutaj podajemy nazwę naszego pliku.tutaj jego rozszerzenie'!wykres_a i tak samo postępujemy w przypadku wykresu kolumnowego, za wyjątkiem podania nazwy zdefiniowanej jako wykres_b. Dla wykresu kolumnowego musimy jeszcze dodatkowo odwrócić wartości na osi pionowej, tak aby wykres wyświetlany był z góry do dołu. Po sformatowaniu wykresów całość powinna wyglądać jak poniżej.

 

 

Wykresy mamy już prawie gotowe. Ich działanie możemy sprawdzić, dodając do tabeli przestawnej fragmentatory dla serii Rok i Miesiąc. Aby osiągnąć ostateczny efekt, związany z dopasowaniem rozmiaru wykresu do filtrowanego obszaru tabeli przestawnej, należy do Modułu arkusza wkleić następujące makro.

 

Zapisz

Zapisz

Option Explicit

Sub histogram_marginalny()

Dim arkusz As Worksheet
Dim wykres_a As ChartObject
Dim wykres_b As ChartObject
Dim tp As PivotTable
Dim tp_adres As Range
Dim tp_lewy_gora
Dim tp_lewy_dol
Dim tp_wysokosc
Dim tp_szerokosc

Set arkusz = Sheets("Wykres")
Set wykres_a = arkusz.ChartObjects("Wykres 4")
Set wykres_b = arkusz.ChartObjects("Wykres 5")
Set tp = arkusz.PivotTables("Tabela przestawna1")
Set tp_adres = tp.TableRange1

tp_lewy_dol = tp_adres.Cells(tp_adres.Rows.Count, 2).Address
tp_szerokosc = tp.PivotFields("Miesiąc").DataRange.Address
tp_wysokosc = tp.PivotFields("Rok").DataRange.Address
tp_lewy_gora = tp_adres.Cells(3, tp_adres.Columns.Count).Address

With wykres_a
.Top = Range(tp_lewy_gora).Top
.Left = Range(tp_lewy_gora).Left
.Height = Range(tp_wysokosc).Height
.Width = 200
With wykres_b
.Top = Range(tp_lewy_dol).Top
.Left = Range(tp_lewy_dol).Left
.Width = Range(tp_szerokosc).Width
.Height = 200
End With
End With

End Sub

 

Makro to, pozwoli nam dopasować wymiary wykresu, bazując na aktualnym położeniu pierwszych wartości w kolumnach Suma końcowa, odpowiednio dla wierszy i dla kolumn. Aby makro działało poprawnie musimy je przypisać do zdarzenia arkusza, którym będzie wprowadzenie zmian w tabeli przestawnej. Do makra dodatkowo dodajemy opcję dopasowania szerokości wierszy, tak aby nasz wykres kolumnowy miał stałą wysokość.

 

Option Explicit

Private Sub Worksheet_PivotTableChangeSync(ByVal Target As PivotTable)
Call histogram_marginalny
Range(Range("A1"), Range("A" & Rows.Count).End(xlUp)).RowHeight = 15
End Sub