15 luglio 2008

Trasporre un foglio excel di dati "statistici" in formato "database"

L'operazione che vado a descrivere penso sia sostanzialmente comune per chi lavora con un dataset "statistico". In pratica chi utilizza un software per analisi statistiche, come R, SAS, SPSS e ci mettiamo in mezzo pure Excel, è abituato ad avere un insieme di dati alle cui righe corrispondo le singole osservazioni e alle cui colonne corrispondo le variabili. Immaginiamo che le variabili siano "sommabili" tra loro, ossia che si riferiscano ad un fenomeno per cui ha senso fare la somma di tutte le variabili. Ad esempio, consideriamo di avere un campione di aziende per ognuna delle quali osserviamo tutte le voci di costo che le caratterizzano nella loro attività. In tal caso, quindi, in Excel siamo abituati ad aggiungere un'ultima variabile (colonna / campo) che sia la somma di tutte le precedenti (nel nostro esempio, quindi, avremo l'ammontare totali dei costi).
E' poi usuale "salvare" i dati in un database in cui è sicuramente preferibile gestire il tutto in modo differente, ossia avere un'unico campo con i dati (ovviamente solo nel nostro caso in cui dati si riferiscono allo stesso fenomeno), utilizzando poi un campo con l'identificativo dell'osservazione ed un altro con il codice identificativo della variabile.
Sempre tornando al nostro esempio delle aziende e dei costi, salveremo i dati in un tabella di database avente tre campi: il primo con un codice che identifica l'azienda, il secondo con un codice che identifica il tipo di costo (energia, costo del lavoro, manutenzione, ecc...), il terzo con il valore del costo. Il primo ed il secondo campo formano una chiave per la tabella, ed avremo un numero distinto di voci di costo pari al numero di variabili (colonne) del nostro dataset iniziale.
Tale operazione di particolare trasposizione della tabella è immediata in Excel mediante la macro che riporto di seguito. Quindi, qualunque sia il software che usiamo, basterà importare la tabella in Excel ed eseguire la macro. L'unico vincolo è il numero massimo di record che si possono gestire. Come ricordato in questo mio post, questo limite si alza notevolmente con la versione più recente di Excel. In genere, comunque, nel caso fosse una "grosso" dataset, sarà sufficiente estrarre dei suoi sottoinsiemi di osservazioni e variabili per lavorare ugualmente con tale macro.
Nel mio esempio, comunque, ho ipotizzato che l'utente lavori con una versione di Excel che prevede al massimo 65,536 righe, restituendo un messaggio di errore nel caso la trasposizione del dataset dovessere portare ad un numero maggiore di record. Il codice vba, comunque, è immediatemante adattabile al caso di Excel 2007 (1,048,576 "righe" e 16,384 "colonne").
Ecco il codice, da associare ad un pulsante di comando:
'*****************************************************
Private Sub cmdTrasponi_Click()
Dim j As Long
Dim i As Long
Dim r As Long
Dim c As Long
r = 0
c = 0

For j = 2 To 256
If Worksheets(1).Cells(1, j).Value <> "" Then
c = c + 1
End If
Next

For j = 2 To 65536
If Worksheets(1).Cells(j, 1).Value <> "" Then
r = r + 1
End If
Next

If c * r + 1 > 65536 Then
MsgBox "Matrice troppo grande per essere gestita in excel"
Exit Sub
End If

If r <= 1 Or c <= 1 Then MsgBox "Numero di dati da trasporre troppo basso"
Exit Sub
End If

For j = 2 To r + 1
For i = 2 To c + 1
Worksheets(2).Cells(i + c * (j - 2), 3).Value = Worksheets(1).Cells(j, i).Value
Worksheets(2).Cells(i + c * (j - 2), 2).Value = Worksheets(1).Cells(1, i).Value
Worksheets(2).Cells(i + c * (j - 2), 1).Value = Worksheets(1).Cells(j, 1).Value
Next
Next

Worksheets(2).Cells(1, 1).Value = Worksheets(1).Cells(1, 1).Value
Worksheets(2).Cells(1, 2).Value = "Descrizione dati"
Worksheets(2).Cells(1, 3).Value = "Dati"

MsgBox "Trasposizione in formato database terminata"

End Sub