User‎ > ‎

Grafici a dispersione - Treemap con Excel

Mi sono imbattuto nelle Treemap per caso leggendo un 3d sul NG di excel un anno fa circa. Si chiedeva come affrontare il problema con Excel. Io non sapevo neppure cosa fosse una Treemap!
Mentre cercavo materiale in internet ho trovato il lavoro di Fabrice Rimlinger.  Consiglio di scaricare dal suo blog Sparklines for Excel il Template file Excel in versione 2007, è un lavoro eccezionale! Oltre alle Treemap contiene molti altri tipi di grafici e spunti nel codice veramente interessanti. In particolare leggete poi la pagina con le specifiche delle Treemap.
Mi sono subito appassionato a questo tipo di visualizzazioni.
Ma cosa sono le Treemap?
Le Treemap sono delle visualizzazioni che consentono una facile interpretazione anche in presenza di grosse quantità di dati o di dati organizzati in modo gerarchico (non è il caso di questo lavoro ... ma chissà, magari più avanti). Il vantaggio più grande è lo sfruttamento totale dello spazio a disposizione per l'area grafico. Come in un grafico a torta dove ogni fetta rappresenta il dato in percentuale sul totale, l’area del grafico Treemap è un rettangolo suddiviso in altri rettangoli. La dimensione di ognuno è in relazione all'area totale come il valore rappresentato sulla somma dei dati. Il vantaggio è che l'intera area del grafico è sfruttata e ottimizzata per la visualizzazione.

Dopo aver provato il file di Fabrice mi sono reso conto che seguire la sua stessa strada, sarebbe stato solo il tentativo di replicare un lavoro già ottimo, molto corposo e difficilmente migliorabile. Nel suo Sparklines il grafico è ottenuto utilizzando le Shapes (forme) che consentono una personalizzazione dell’aspetto quasi totale. Nelle Treemap è possibile così ottenere rettangoli con colori diversi, un valore aggiunto alla visualizzazione.
Uno dei problemi principali è l’aggiornamento. Per ottenere un aggiornamento legato all’origine dati Fabrice ha escogitato un complesso sistema. A detta sua proprio questo sistema è il punto critico e che più andrebbe migliorato. Per consentire l’aggiornamento in pratica il grafico viene, ad ogni variazione, cancellato e rifatto da zero. Personalmente non credo sia possibile ottenere risultati migliori dei suoi. Legare l’origine dati con le dimensioni di una o più forme non è una funzionalità nativa di excel. Ci ho provato ... ho studiato il suo approcio. In quel file (il codice non è protetto) ci sono tanti spunti interessanti e ne ho fatto tesoro ... ma la mia indole di sperimentatore mi porta a voler affrontare il problema partendo da zero e cercando una via diversa.

L’algoritmo utilizzato è lo stesso (non il codice che ho scritto di mano mia) ed è spiegato a questo indirizzo :
http://www.codeproject.com/KB/recipes/treemaps.aspx

Il mio approcio nasce proprio dallo studio del meccanismo di aggiornamento del grafico all’aggiornanrsi dei dati fatto sul file di Fabrice.
Volevo usare le funzionalità di Excel in modo da ottenere almeno in questa direzione un vantaggio.
Così ho deciso di disegnare la Treemap usando un grafico a dispersione xy. Il risultato migliore si ottiene con Excel 2003 e il funzionamento si basa su 2 UDF che consentono di ottenere le serie (x e y) partendo da un origine dati che può essere indifferentemente un range di celle o una matrice restituita da una funzione matriciale. Le udf vengono utilizzate nella definizione di 4 nomi. Due sono dedicati alle x e alle y di una serie che disegna i bordi dei rettangoli. Dopo la definizione dei nomi usando la UDF treemap_r tali nomi vengono richiamati nella definizione della serie. Altri due nomi sono usati per disegnare i punti centrali di ogni rettangolo che verranno utilizzati per aggiungere le etichette. Il disegno dei bordi avviene come detto usando una sola serie, come disegnando con una penna senza mai staccarla dal foglio, alcuni tratti delle linee si sovrappongono ad altre già tracciate.
Con questo approcio l’utente può realizzare velocemente e con estrema facilità un grafico Treemap che ha il limite di non consentire la colorazione dei rettangoli ma il grosso vantaggio di poter essere dinamico.

I passaggi sono pochi davvero ... (riporto di seguito la definizione dei nomi per la versione in italiano e inglese)
Una volta copiate le udf in un modulo del foglio, si definisce un range dinamico che conterrà i dati.
Così con dati a partire da B1 e in presenza di una intestazione definiamo l’intervallo dinamico ad esempio in questo modo:

dati_1 =SCARTO($B$2;;;CONTA.SE($B:$B;"<>")-1)
dati_1 =OFFSET($B$2,,,COUNTIF($B:$B,"<>")-1)

In riferimento a questo intervallo definiamo quello delle etichette (colonna A) :

etichette =SCARTO(dati_1;;-1)
etichette =OFFSET(dati_1,,-1)

Gli altri nomi sono quelli che verranno usatio nelle serie e che sfruttano le due UDF :

x =treemap(dati_1;VERO)
y =treemap(dati_1;FALSO)

x =treemap(dati_1,TRUE)
y =treemap(dati_1,FALSE)

xe =treemap_e(dati_1;VERO)
ye =treemap_e(dati_1;FALSO)

xe =treemap_e(dati_1,TRUE)
ye =treemap_e(dati_1,FALSE)

Ora è sufficiente aggiungere un grafico a dispersione e riferire le x e le y di due serie ai nomi x,y e xe,ye.

Per aggiungere le etichette esistono componenti aggiuntivi molto famosi, oppure una semplice macro o ancora manualmente (usando la barra delle formule a etichetta selezionata) si potrà aggiugere il riferimento alle singole celle del range etichette.
Qui sotto un esempio di dati e relativa Treemap con dati non ordinati:
 
 
 
 
Per migliorare l'aspetto possiamo ordinare i dati.
L'ordinamento può essere ottenuto manualmente oppure sfruttare due colonne di appoggio con formule Excel. In quest’ultimo caso sarà possibile l'aggiornamento automatico all’accodamento di nuovi dati.
Qui sotto i dati con colonne di appoggio per ottenere l'ordinamento e relativa Treemap.
 
 
 


Il codice delle UDF da incollare in un modulo standard del progetto VBA:

Function treemap(rng, Optional xy As Boolean = True)
Dim i As Long, s As Long
Dim a As Long
Dim b, h, t, x, y, z, m, p As Long
Dim bb, hh, d
Dim sum_1, sum_2
Dim zx As Double, zy As Double
Dim v()
Dim res()
Application.Volatile
v = Raddrizza(rng)
h = (Application.Sum(v) / 2) ^ 0.5
b = h * 2
bb = b
hh = h
d = UBound(v)
ReDim res((d + 1) * 5 - 2)
d = d - 1
For i = LBound(v) To d
    x = 0
    y = 0
    sum_1 = 0
    For a = s To i
        sum_1 = v(a) + sum_1
    Next
    t = sum_1 / Application.Min(b, h)
    For a = s To i
        m = v(a) / t
        x = Application.Max(x, m / t, t / m)
    Next
    sum_1 = v(a) + sum_1
    z = sum_1 / Application.Min(b, h)
    For a = s To i + 1
        m = v(a) / z
        y = Application.Max(y, m / z, z / m)
    Next
    sum_1 = 0
    If y > x Then
        If b > h Then
            b = b - t
            sum_1 = zy
            For a = s To i
                sum_1 = sum_1 + v(a) / t
                If xy Then
                    res(p) = zx
                    p = p + 1
                    res(p) = zx
                    p = p + 1
                    res(p) = t + zx
                    p = p + 1
                    res(p) = t + zx
                    p = p + 1
                    res(p) = zx
                    p = p + 1
                Else
                    res(p) = zy
                    p = p + 1
                    res(p) = sum_1
                    p = p + 1
                    res(p) = sum_1
                    p = p + 1
                    res(p) = zy
                    p = p + 1
                    res(p) = zy
                    p = p + 1
                End If
            Next
            zx = zx + t
        Else
            h = h - t
            sum_1 = zx
            For a = s To i
                sum_1 = sum_1 + v(a) / t
                If xy Then
                    res(p) = zx
                    p = p + 1
                    res(p) = zx
                    p = p + 1
                    res(p) = sum_1
                    p = p + 1
                    res(p) = sum_1
                    p = p + 1
                    res(p) = zx
                    p = p + 1
                Else
                    res(p) = zy
                    p = p + 1
                    res(p) = zy + t
                    p = p + 1
                    res(p) = zy + t
                    p = p + 1
                    res(p) = zy
                    p = p + 1
                    res(p) = zy
                    p = p + 1
                End If
            Next
            zy = zy + t
        End If
        s = i + 1
    Else
   
    End If
Next
If xy Then
    res(p) = zx
    p = p + 1
    res(p) = zx
    p = p + 1
    res(p) = bb
    p = p + 1
    res(p) = bb
Else
    res(p) = zy
    p = p + 1
    res(p) = hh
    p = p + 1
    res(p) = hh
    p = p + 1
    res(p) = zy
End If
treemap = res
End Function

Function treemap_e( _
    rng, _
    Optional xy As Boolean = True, _
    Optional bSort As Boolean = False)

'https://sites.google.com/site/e90e50/

Dim i As Long, s As Long
Dim a As Long
Dim b, h, t, x, y, z, m, p As Long
Dim bb, hh, d
Dim sum_1, sum_2
Dim zx As Double, zy As Double
Dim v()
Dim res()
Application.Volatile
v = Raddrizza(rng)

If bSort Then
    v = BoobleSort(v)
End If

h = (Application.Sum(v) / 2) ^ 0.5
b = h * 2
bb = b
hh = h
d = UBound(v)
ReDim res((d))
d = d - 1
For i = LBound(v) To d
    x = 0
    y = 0
    sum_1 = 0
    For a = s To i
        sum_1 = v(a) + sum_1
    Next
    t = sum_1 / Application.Min(b, h)
    For a = s To i
        m = v(a) / t
        x = Application.Max(x, m / t, t / m)
    Next
    sum_1 = v(a) + sum_1
    z = sum_1 / Application.Min(b, h)
    For a = s To i + 1
        m = v(a) / z
        y = Application.Max(y, m / z, z / m)
    Next
    sum_1 = 0
    If y > x Then
        If b > h Then
            b = b - t
            sum_1 = zy
            For a = s To i
                sum_1 = sum_1 + v(a) / t
                If xy Then
                    res(p) = (t / 2 + zx)
                    p = p + 1
                Else
                    res(p) = sum_1 - (v(a) / t) / 2
                    p = p + 1
                End If
            Next
            zx = zx + t
        Else
            h = h - t
            sum_1 = zx
            For a = s To i
                sum_1 = sum_1 + v(a) / t
                If xy Then
                    res(p) = sum_1 - (v(a) / t) / 2
                    p = p + 1
                Else
                    res(p) = (zy + t / 2)
                    p = p + 1
                End If
            Next
            zy = zy + t
        End If
        s = i + 1
    Else
   
    End If
Next
If xy Then
    res(p) = zx + (bb - zx) / 2
Else
    res(p) = zy + (hh - zy) / 2
End If
treemap_e = res
End Function

Function Raddrizza(ByVal arrA)

'https://sites.google.com/site/e90e50/

Dim v, t()
Dim i As Long
For Each v In arrA
    i = i + 1
Next
ReDim t(i - 1)
i = 0
For Each v In arrA
    t(i) = v
    i = i + 1
Next
Raddrizza = t
End Function

 

Function BoobleSort( _
    ByRef arrB As Variant, _
    Optional ByVal bAsc As Boolean = True)
   
'https://sites.google.com/site/e90e50/

Dim i As Long, a As Long, v
Dim arrA
arrA = arrB
  For i = LBound(arrA) To UBound(arrA) - 1
    For a = i + 1 To UBound(arrA)
      If arrA(i) < arrA(a) Xor bAsc Then
         v = arrA(i)
         arrA(i) = arrA(a)
         arrA(a) = v
      End If
    Next
  Next
BoobleSort = arrA
End Function

Ĉ
r,
Feb 10, 2011, 8:01 AM
ċ
treemap_Ordinata.xlsm
(35k)
r,
Feb 11, 2011, 3:27 PM
Ĉ
r,
Apr 24, 2013, 3:50 AM
Ĉ
r,
Apr 24, 2013, 3:50 AM
Comments