Histogram marginalny w excelu

Histogram marginalny z mapą cieplną w Excelu

Posted on Posted in wykresy

Histogram marginalny w Excelu, to wykres, który podpatrzyłem u użytkowników posługujący się Tableau. Zrobił na mnie spore wrażenie i od tego momentu postanowiłem, że taki sam uda mi się stworzyć w Excelu. Oryginalnie histogram marginalny jest wykresem, który najczęściej prezentuje się w parze z wykresem punktowym. Takie połączenie, pozwala na lepszą analizę marginalnego rozłożenia dwóch zmiennych. W naszym przykładzie, histogram marginalny, udało mi się dodać do mapy cieplnej, tworząc wykres pozwalający na dokładniejszą analizę dwóch wymiarów, z uwzględnieniem ich wartości podsumowujących. Zapraszam zatem do przejścia ścieżki umożliwiającej stworzenie takiego wykresu - być może jedynego takiego połączenia, które zostało wykonane w Excelu 🙂 Pobierz wykres.

 

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.

 

Tabela przestawna w excelu

 

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

 

Skala kolorów formatowania warunkowego w Excelu

 

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.

 

Formatowanie warunkowe w tabeli przestawnej

 

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.

 

Edytowanie reguły formatowania warunkowego

 

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.

 

Opcje wyświetlania w 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.

 

Formatowanie elementów stylu tabeli przestawnej

 

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

 

Ukrywanie pól w tabeli przestawnej

 

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".

 

Ukrywanie pola wartości w tabeli przestawnej

 

 

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.

 

Formatowanie niestandardowe

 

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.

 

Dynamiczne wykresy w Excelu

 

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

 

Nasz wykres jest już gotowy 🙂

 

2 thoughts on “Histogram marginalny z mapą cieplną w Excelu

  1. Ale super 🙂 . Właściwie to należy traktować ten wykres jako trójwymiarową kostkę analityczną do zastosowań w Business Intelligence.

Dodaj komentarz

Twój adres email nie zostanie opublikowany. Pola, których wypełnienie jest wymagane, są oznaczone symbolem *