In questo appunto vediamo in che modo costruire una macro excel capace di ottenere un istogramma delle frequenze con gaussiana:
attenzione, in questo istogramma delle frequenze con gaussiana, la funzione gaussiana rappresentata non è la distribuzione di densità di probabilità. L’asse delle ordinate, infatti, rappresenta le frequenze e non la densità di probabilità. Per ottenere quest’ultima si dovrebbe moltiplicare ogni valore per il numero di dati e dividerlo per il numero di categorie.
Abbiamo già visto in questo link come è possibile raggiungere tale risultato in excel. Le operazioni mostrate possono però risultare abbastanza complesse soprattutto per chi non è familiare con le istruzioni di excel. In questo appunto vogliamo semplificare le cose fornendo direttamente le istruzioni necessarie per costruire una macro. La macro che realizzeremo funziona nel caso di dati distribuiti normalmente. La presenza di eventuali outlier nel set di dati può comprometterne il risultato finale. Per questo motivo, suggeriamo di eliminare eventuali punti estremi nel set di dati a disposizione soprattutto se la loro presenza può essere facilmente spiegata come una deviazione non naturale di un processo.
Lo sviluppo della macro in questo appunto si dividerà nei seguenti paragrafi:
- Eliminare eventuali outlier dal set di dati
- Selezionare il file con i dati di interesse con VBA
- Organizzare i dati in classi o categorie di frequenze
- Codificare le istruzioni necessarie ad eseguire il grafico
Eliminare eventuali outlier dal set di dati
Come abbiamo detto nel paragrafo precedente, la macro che realizzeremo potrebbe generare un output poco significativo in caso di presenza di un outlier molto importante e di pochi dati. Infatti, la logica con cui costruiremo l’istogramma delle frequenze con gaussiana, identifica dal numero di dati a disposizione, le classi di frequenze. L’ampiezza di tali classi o categorie dipende dal valore massimo e minimo del nostro set di dati. La presenza di un massimo o un minimo troppo distanti dal set di dati comporta che l’ampiezza delle singole classi possa essere troppo grande. L’effetto è maggiore quando si hanno a disposizioni pochi valori. Il consiglio è di far girare la macro e qualora dovesse essere ottenuto un risultato del tipo:
di eliminare eventuali outlier dalla distribuzione prima di far girare nuovamente la macro. In generale, se il set di dati è numeroso consigliamo di eliminare eventuali dati che sono oltre la media +- 3 volte la standard deviation. Dopo aver eliminato gli outlier, la macro sarà in grado di fornire un output maggiormente significativo:
Selezionare il file con i dati di interesse con codice VBA
Abbiamo visto nel seguente appunto che è possibile utilizzare l’istruzione:
Application.GetOpenFilename(FileFilter:=”Excel Files,*.xls;*.xlsx”)
per caricare un file con l’utilizzo di una finestra di navigazione. Utilizziamo lo stesso codice nella seguente macro:
Sub Pulsante1_Click()
Dim sceglifile As Variant
sceglifile = Application.GetOpenFilename(FileFilter:=”Excel Files,*.xls;*.xlsx”)
If sceglifile <> False Then
Workbooks.Open Filename:=sceglifile
End If
UserForm1.Show
End Sub
Questo ci consentirà di selezionare il file di interesse. Come si può vedere dal codice sopra mostrato, le istruzioni terminano con UserForm1.Show. Abbiamo visto nell’appunto seguente in che modo creare una userform. La Userform1 realizzata per questo progetto è molto semplice. Essa è costituita da un RefEdit e da due command button:
Refedit consente di selezionare il set di dati di interesse per la realizzazione dell’istogramma delle frequenze con gaussiana. Affinché questa macro funzioni, i dati devono essere tutti organizzati in una singola colonna. Il comando esci contiene come unica istruzione quella di chiudere la userform:
Private Sub CommandButton2_Click()
Unload UserForm1
End Sub
Il comando esegui invece, consentirà di eseguire tutte le operazioni preliminari per la realizzazione del grafico.
Organizzare i dati in classi o categorie di frequenze
Il codice contenuto nel comando esegui consente di organizzare i dati in classi o categorie di frequenze.
Ricordiamo che il numero di classi dipende dalla numerosità del set di dati, mentre l’ampiezza delle classi dipende dal massimo e dal minimo del set stesso. Non spiegheremo tutto il codice ma in grassetto troverete le istruzioni che maggiormente determinano il risultato finale:
‘Codice: dichiarazione variabili
Private Sub CommandButton1_Click()
Dim Rng As Range
‘ dichiariamo workbook e worksheet
Dim wb As Workbook
Dim ws As Worksheet
‘definiamo le variabili long
Dim righe, categorie,i As Long
Dim minimo, massimo,amp,cat,cat2 As Double
‘definiamo le frequenze
Dim freq,freq2 As Variant
Dim j As Integer
‘Codice: definizione serie da usare per grafico
Set Rng = Range(Me.RefEdit1.Value)
Rng.Copy
j = Rng.Count
Set wb = Workbooks(“Gaussiana con istogramma.xlsm”)
Set ws = wb.Sheets(“Foglio2”)
ws.Activate
ws.Cells(2, 1).PasteSpecial xlPasteValues
righe = Sheets(“Foglio2”).Range(“A2”, Sheets(“Foglio2”).Range(“A2”).End(xlDown)).Count
categorie = 2 * righe ^ (0.4) + 10
massimo = Application.WorksheetFunction.Max(Rng)
minimo = Application.WorksheetFunction.Min(Rng)
amp = (massimo – minimo) / (categorie – 10)
‘calcoliamo la media
ws.Cells(2, 3) = “media”
ws.Cells(3, 3) = Application.WorksheetFunction.Average(Rng)
‘calcoliamo la deviazione standard
ws.Cells(4, 3) = “deviazione standard”
ws.Cells(5, 3) = Application.WorksheetFunction.StDev(Rng)
‘ciclo for per il calcolo dei valori per ogni classe/categoria
For i = 1 To categorie
cat = (minimo – 6 * amp / 2) + amp * (i – 1)
cat2 = (minimo – 6 * amp / 2) + amp * (i – 2)
freq = Application.WorksheetFunction.Frequency(Rng, cat)
freq2 = Application.WorksheetFunction.Frequency(Rng, cat2)
ws.Cells(i + 1, 5) = cat
ws.Cells(i + 1, 6) = freq
‘calcoliamo i valori delle frequenze degli istogrammi
ws.Cells(i + 1, 7) = freq2
ws.Cells(i + 1, 8) = (ws.Cells(i + 1, 6) – ws.Cells(i + 1, 7)) / amp
‘calcolo del valore teorico delle celle
ws.Cells(i + 1, 9) = (Application.WorksheetFunction.Norm_Dist(ws.Cells(i + 1, 5).Value, ws.Cells(3, 3), ws.Cells(5, 3), True) – Application.WorksheetFunction.Norm_Dist(ws.Cells(i, 5).Value, ws.Cells(3, 3), ws.Cells(5, 3), True)) * j / amp
Next i
ws.Columns(“F:G”).Select
Selection.Delete
ws.Cells(1, 1) = “Valori da Analizzare”
‘inseriamo i titoli delle colonne
ws.Cells(1, 5) = “Classi”
ws.Cells(1, 6) = “Frequenze”
‘calcoliamo i dati della prima cella della gaussiana
ws.Cells(2, 7) = (Application.WorksheetFunction.Norm_Dist(ws.Cells(2, 5).Value, ws.Cells(3, 3),ws.Cells(5, 3), True) – Application.WorksheetFunction.Norm_Dist(ws.Cells(2, 5).Value – amp / 2, ws.Cells(3, 3), ws.Cells(5, 3), True)) * j / amp
ws.Cells(1, 7) = “Distribuzione normale”
ws.Range(“D2”).Select
UserForm2.Show
End Sub
Abbiamo in particolare sottolineato:
- La formula per il calcolo del numero di categorie
- Il metodo per il calcolo dell’ampiezza delle classi
- Le istruzioni necessarie al calcolo delle frequenze dell’istogramme e della gaussiana teorica
Il codice termina con userform2. Si tratta di una userform dedicata alla realizzazione del grafico.
Codificare le istruzioni necessarie ad eseguire il grafico
La userform utilizzata consente all’utente di definire il titolo del grafico ed il titolo degli assi:
Il tasto esegui contiene il codice che realizza il grafico:
‘codice: dichiarazione variabili
Private Sub CommandButton1_Click()
Dim fogli As Worksheet
Dim rng2, rng3,rng4 As Range
‘ Dichiariamo che grafico è un oggetto del tipo chart
Dim grafico As ChartObject
Dim ser1,ser2 As Series
Set fogli = Sheets(“Foglio2”)
‘ mostriamo qui come creare l’oggetto grafico
Set grafico = ActiveSheet.ChartObjects.Add( _
Left:=ActiveCell.Left, _
Width:=600, _
Top:=ActiveCell.Top, _
Height:=400)
‘di seguito mostriamo come impostare i range e le serie da utilizzare per la realizzazione del grafico rng 2 sono i valori delle classi per l’asse delle x
Set rng2 = fogli.Range(“E2”, Range(“E2”).End(xlDown))
‘rng3 sono i valori delle frequenze osservate
Set rng3 = fogli.Range(“F2”, Range(“F2”).End(xlDown))
‘rng4 sono i valori delle frequenze teoriche
Set rng4 = fogli.Range(“G2”, Range(“G2”).End(xlDown))
‘creazione due serie per il grafico
Set ser1 = grafico.Chart.SeriesCollection.NewSeries
Set ser2 = grafico.Chart.SeriesCollection.NewSeries
‘ impostazioni per la prima serie. Si tratta dello istogramma delle frequenze (xlColumnstacked)
With ser1
.Values = rng3.Value
.XValues = rng2.Value
.Name = fogli.Cells(1, 6).Value
.ChartType = xlColumnStacked
End With
‘impostazioni per la seconda serie. Si tratta della gaussiana teorica (xlLine)
With ser2
.Values = rng4.Value
.XValues = rng2.Value
.Name = fogli.Cells(1, 7).Value
.ChartType = xlLine
End With
‘impostazioni del grafico. Utilizziamo gli input della userform per impostare I titoli dei grafici
With grafico
.Chart.HasTitle = True
.Chart.ChartTitle.Text = Me.TextBox1.Value
With grafico.Chart.Axes(xlCategory, xlPrimary)
.HasTitle = True
.AxisTitle.Text = Me.TextBox2.Value
End With
With grafico.Chart.Axes(xlValue, xlPrimary)
.HasTitle = True
.AxisTitle.Text = Me.TextBox3.Value
End With
.Chart.Shapes.AddLabel(msoTextOrientationHorizontal, 500, 50, 150, 100).TextFrame.Characters.Text = “media =” & ” ” & fogli.Cells(3, 3).Value & ” ” & “dev. st. =” & ” ” & fogli.Cells(5, 3).Value
End With
End Sub
L’output di questo codice è proprio l’istogramma delle frequenze con gaussiana visto ad inizio appunto
