Per questo esercizio vediamo come creare una funzione personalizzata simile (perché non andremo ad implementare tutte le alternative) al SOMMA.PIÙ.SE in VBA per Excel.
Anzitutto individuiamo le criticità:
- Avremo bisogno di accettare un range per la somma ed n coppie di parametri come intervalli e criteri, la nostra funzione avrà quindi la forma di FUNZIONE(somma, [intervallo 1], [criterio 1], …)
- I criteri possono essere numerici oppure stringhe di confronto, come per esempio “>2”
Anzitutto vediamo come creare una funzione che accetti n parametri come abbiamo definito di sopra. Per cui digitiamo in VBA:
|
|
Function SOMMAPIUSE(ByVal somma As Range, ParamArray args() As Variant) As Double Dim totale As Double totale = 0 SOMMAPIUSE = totale End Function |
In questo caso abbiamo inserito il minimo indispensabile, definendo il risultato come un Double, dichiarando internamente una variabile totale ed impostandola su 0. Questa variabile la utilizzeremo in seguito. L’argomento somma è di tipo Range (qui occhio agli indici, come vedremo tra poco!) e poi segue un vettore di parametri args definiti come Variant.
Per scorrere i vari parametri possiamo usare l’istruzione:
|
|
For i = LBound(args) To UBound(args) ' qui le nostre operazioni su args(i) Next i |
A questo punto vogliamo costruire un vettore di criteri lungo quanto l’intervallo della somma e che per ogni chiave contenga un valore equivalente a 0 oppure ad 1, in funzione che si debba o meno sommare il valore nell’intervallo somma.
Per fare questo definiamo:
|
|
Dim criteri() As Variant Dim max, count As Integer max = somma.count ReDim criteri(max) For i = 0 To max - 1 criteri(i) = 1 Next i |
Con il metodo somma.count misuriamo la dimensione del Range somma, attenzione perché il range si muove da 1 fino a somma.count, mentre il nostro vettore criteri andrà da 0 fino a somma.count – 1
Infine impostiamo tutto il vettore criteri() su 1, questo ci servirà tra poco per le operazioni logiche.
A questo punto non ci rimane che valutare tutti i criteri, tenendo presente che vanno a coppie di intervallo criteri e criterio. Perciò utilizzeremo l’istruzione:
|
|
If i Mod 2 = 0 Then End If |
Gli intervalli dei criteri si trovano in posizioni pari, mentre il criterio in posizione dispari.
Infine utilizzeremo il metodo Evaluate sfruttando la funzione SE di Excel per valutare ogni criterio, nel modo seguente:
|
|
Evaluate("IF(" & a.Value & "=" & args(i + 1) & ",1,0)") |
Faccio notare che in Evaluate va scritta la funzione col nome in inglese. Dal momento che i criteri possono essere numerici oppure stringhe, come abbiamo detto all’inizio, dobbiamo distinguere tra le due cose e quindi scriveremo:
|
|
If IsNumeric(args(i + 1)) Then '... valore tipo 1, 2 ecc Else '... valore tipo >2, <=4 ecc End If |
Infine ricordiamoci che tutti i criteri devono essere validi, quindi per modificare ogni posizione del vettore criteri() useremo il prodotto. In questo modo se sono tutti veri risulterà 1, un prodotto di tutti 1 insomma, mentre se anche un solo criterio sarà falso allora il prodotto risulterà 0, dal momento che basta uno 0 nel prodotto per azzerare tutto.
Detto tutto ciò la nostra funzione finale sarà simile a questa:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 |
Function SOMMAPIUSE(ByVal somma As Range, ParamArray args() As Variant) As Double Dim totale As Double Dim criteri() As Variant Dim max, count As Integer max = somma.count ReDim criteri(max) For i = 0 To max - 1 criteri(i) = 1 Next i totale = 0 For i = LBound(args) To UBound(args) If i Mod 2 = 0 Then count = 0 For Each a In args(i) If count < max Then If IsNumeric(args(i + 1)) Then criteri(count) = criteri(count) * Evaluate("IF(" & a.Value & "=" & args(i + 1) & ",1,0)") Else criteri(count) = criteri(count) * Evaluate("IF(" & a.Value & args(i + 1) & ",1,0)") End If End If count = count + 1 Next a End If Next i For i = 0 To max - 1 If criteri(i) = 1 Then totale = totale + somma(i + 1) End If Next i SOMMAPIUSE = totale End Function |
Inutile dire che la funzione potrebbe essere ulteriormente affinata per coprire tutti i possibili casi in cui può essere utilizzata SOMMA.PIÙ.SE 😉
