Calcolare provvigioni differenziate con il Vba

Utilizzo di una Funzione Utente per il calcolo (vedi in questa sezione: Funzioni Utente in vba).

Rifacendomi al problema esposto nella sezione Formule, articolo: "Condizioni SE a raffica", vorrei presentare una soluzione a quel problema, che comunque riporto di seguito, realizzata mediante l'uso di istruzioni in vba. Dovendo realizzare una routine che si presume interessare molteplici celle (visto che un calcolo provvigionale su importi fattura in genere lo si esegue su un riepilogo fatture generale o per agente) ho ritenuto più sfruttabile utilizzare una Funzione personalizzata. In questo modo sarà possibile scegliere le celle destinate a contenere detta funzione che sarà usata come si usa una tradizionale Funzione di un foglio di lavoro, facendo precedere al nome della funzione il segno = e indicando i riferimenti alle celle che formeranno gli "argomenti" della funzione.

"un rappresentante prende delle provvigioni in %
Come dati ho il valore in € della fattura (A1) e lo sconto che il rappresentante ha fatto (B1)
se fattura <5000€ prende l'8%
5000-25000€ prende il 4%
>25000€ prende il 2,5%
Se applica fino al 5% di sconto sulla fattura il rappresentante prende la provvigione piena (l'8% o il 4% o il 2,5% a seconda dell'importo della fattura)
ma se applica sconti >5% la sua provvigione dovrà essere decurtata di 1/3 dello sconto oltre il 5%
Ultimissima cosa: minimo provvigionale=1,5% ".

Abbiamo quindi a che fare con una raffica di SE ( If ). Per sveltire l'esecuzione del codice, che dovrebbe leggere tutte le condizioni ( If ) immesse, facciamo uso dell'istruzione Select Case che rende la lettura delle condizioni più veloce, affidandogli il compito di decidere se lo sconto sarà inferiore o uguale a 5% oppure maggiore, ed annidare le istruzioni If all'interno dei Case. Come già visto nella pagina "Funzioni Utente in vba", assegneremo un nome alla funzione, seguito, tra parentesi, dai nomi che rappresentano gli argomenti della funzione. Per chi ha un minimo di conoscenza in vba, non sarà difficile "leggere" le istruzioni impiegate; si basano su valori assegnati alle provvigioni, e su variabili (uso lettere dell'alfabeto) che raccolgono le varie condizioni. Non ho previsto argomenti "legati" alle provvigioni in quanto in genere, una volta stabilite le provvigioni, queste restano tali, e allora tanto vale inserirle nella funzione ( la funzione con meno argomenti risulta più "leggera"). Questa la funzione:

Function Provv(cifra, sconto) As Double
Select Case sconto
Case 5
If cifra <= 5000 And sconto = 5 Then X = 8
If cifra >= 5001 And cifra <= 25000 And sconto = 5 Then X = 4
If cifra >= 25001 And sconto = 5 Then X = 2.5
Case Is > 5
y = 8 - ((sconto - 5) / 3)
z = 4 - ((sconto - 5) / 3)
w = 2.5 - ((sconto - 5) / 3)
If y < 1.5 Then y = 1.5
If z < 1.5 Then z = 1.5
If w < 1.5 Then w = 1.5
If cifra <= 5000 Then X = y
If cifra >= 5001 And cifra <= 25000 Then X = z
If cifra >= 25001 Then X = w
End Select
Provv = (X * cifra) / 100
End Function

Gli argomenti cifra e sconto non saranno altro che il riferimento alla cella che contiene l'importo fattura il primo, e una cella che destineremo a contenere il valore dello sconto concesso, il secondo. Questa funzione potrà essere inserita in una cella, esempio la C1, visto che nel problema su esposto si citava la A1 e la B1, e sarà : =Provv(A1;B1) ed in C1 avremo il risultato della formula. Se l'elenco fosse su più righe, sarà sufficiente usare il "trascinamento" per copiare la formula dalla C1 nelle celle sottostanti, ed il bravo Excel provvederà ad aggiornare i riferimenti alle celle di pertinenza.

Se poi si volesse, nel caso di elenchi lunghi, affidarsi ad una macro per inserire la funzione nelle celle previste, sarà possibile richiamare la funzione assegnandogli gli argomenti, come faremmo con qualsiasi altra funzione. Questo un esempio: elenco importo fatture nella colonna A, nella B gli sconti concessi, a partire dalla riga 2. Nella colonna C vorremo il conteggio provvigioni. la macro:

Sub provvigioni()

Dim CL As Object

For Each CL In Range("C2:C200")

'definizione con "importo" dell'importo fattura che si trova stessa riga, 2 colonne a sinistra '(la A), rispetto a CL, primo argomento della funzione Provv
importo = CL.Offset(0, -2).Value

'definizione con "scon" del valore dello sconto che si trova stessa riga, 1 colonne a sinistra '(la A), rispetto a CL, secondo argomento della funzione Provv
scon = CL.Offset(0, -1).Value

'se la cella CL è vuota, allora
If CL = "" Then

'nella cella CL si mette il risultato della funzione: la provvigione spettante
CL = Provv(importo, scon)
End If
Next
End Sub

non ho inserito un controllo se l'importo fattura o lo sconto non sono presenti, perchè in caso manchino dati il risultato della funzione sarà zero.

Due precisazioni: nel campo "sconto" (colonna B) dovrà essere inserito lo sconto in formato numero e NON percentuale ( 5 e non 5%); i campi importo fattura (colonna A) e provvigioni (colonna C) dovranno avere il formato celle a numero con 2 decimali.