Calcolare provvigioni a scalare con il Vba

Ancora un esercizio con un calcolo provvigionale, con utilizzo di una Funzione Utente oppure di una routine.

A differenza del paragrafo precedente "Provvigioni differenziate" dove le provvigioni si modificavano in funzione dell'importo della fattura e dello sconto concesso al cliente, restando computate per intero con l'aliquota provvigionale prevista (in toto), questa volta ci occupiamo di provvigioni a scalare in funzione dello scaglione di importo fattura (al di là dello sconto concesso, che considereremo comunque a parte).

Per meglio presentare questo esercizio, presento due soluzioni, entrambe portano allo stesso risultato. La prima è una macro, la seconda l'ho impostata come Funzione, in modo da poterla usare direttamente sul foglio di lavoro. Vediamo la macro: nelle istruzioni esemplifico identificando l'origine del campo importo fattura e sconto concesso, ipotizzando che i due valori si trovino il primo in A1 (importo) e il secondo in B1(sconto). Uso ancora due variabili per assegnare le due costanti relative agli scaglioni che determinano l'assegnazione delle aliquote provvigionali, base (per lo scaglione fino a 5000) e basedue (per lo scaglione da 5000 25000) e oltre. L'identificazione degli scaglioni di pertinenza delle provvigioni le identifico con dei cicli If..Then...Else. Una volta assegnato alle variabili b - c - d gli importi degli scaglioni, uso Select Case per diversificare le due condizioni degli sconti ( fino a 5% o maggiore di 5%). All'interno di questi due casi, eseguo i conteggi di assegnazione delle provvigioni. Il risultato lo riporto in una messagebox, ma può essere tranquillamente assegnata una cella dove restituire il risultato.

Sub ProvvScalare()

'assegnazione alle variabili sconto e importo dei valori contenuti nelle due celle
sconto = Range("B1").Value
importo = Range("A1").Value

'assegnazione alle variab. base e basedue delle costanti dei valori degli scaglioni
base = 5000
basedue = 25000

'controllo se base (5000) è maggiore o uguale a importo (Range("A1"))
If base >= importo Then

'nel qual caso assegno alla variab. minimo lo stesso valore di importo
minimo = importo
Else

'in caso contrario rendo la variab. minimo uguale a base (5000)
minimo = base
End If

'inizio l'assegnazione alle variab, b - c - d dei valori che corrisponderanno agli importi 'relativi ai vari scaglioni
b = minimo
If basedue >= importo Then
c = importo - b
Else
c = basedue - b
End If
d = importo - (c + b)

'si controlla che esistano gli importi relativi ai tre scaglioni, altrimenti si assegna zero alla 'variabile dello scaglione. Se il valore fosse nullo (non esistesse), si genererebbe un errore 'nelle istruzioni successive dove si eseguono i calcoli su questi valori.
If b < 0 Then b = 0
If c < 0 Then c = 0
If d < 0 Then d = 0

'inizio il controllo sugli sconti, usando Select Case, il primo caso è che lo sconto sia 5 o 'inferiore a 5
Select Case sconto
Case Is <=5

'si assegnano le aliquote provvigionali i % a tre variabili: pp=prima provv - sp=seconda provv. - tp=terza provvigione
pp = 8 / 100
sp = 4 / 100
tp = 2.5 / 100
'indi si assegnano a tre variabili (F-G-H) i valori che si ottengono dalla moltiplicazione degli scaglioni di importi per le aliquote provv.
F = b * pp
G = c * sp
H = d * tp

'poi restituisco il totale delle provvigioni così ottenute attraverso un messaggio, ma il risultato lo potremmo tranquillamente inviare ad una cella, esempio: 'Range("D1")=F+G+H
MsgBox F + G + H
'ora iniziamo i conteggi nel secondo caso, cioè che lo sconto sia maggiore del 5
Case Is > 5

'assegnazione alle variabili delle aliquote derivanti dalla differenza dello sconto maggiore con lo sconto 5, diviso per 1/3, e sottratto alla provvigione normalmente prevista
pp = 8 - ((sconto - 5) / 3)
sp = 4 - ((sconto - 5) / 3)
tp = 2.5 - ((sconto - 5) / 3)

'controlliamo che il valore ora assegnato ad ogni provvigione non sia inferiore a 1,5, nel qual caso lo rendiamo uguale a 1,5
If pp < 1.5 Then pp = 1.5
If sp < 1.5 Then sp = 1.5
If tp < 1.5 Then tp = 1.5

'e poi anche qui otteniamo l'assegnazione alle tre variabili (F-G-H) dei valori scaturiti dalla moltiplicazione degli importi per le aliquote rese in % ( diviso 100 )
F = b * pp / 100
G = c * sp / 100
H = d * tp / 100

'e si finisce come nel caso precedente
MsgBox F + G + H
'questa istruzione sotto è necessaria quando si usa il costrutto della selezione dei casi (Select Case), alla fine delle istruzioni si deve inserire: fine delle selezioni (End Select)
End Select

'fine della routine
End Sub

Bene, questa routine è finita, e potrà essere associata ad un pulsante per averla funzionante sul foglio di lavoro. Va da se che se il conteggio provvigionale dovesse interessare un elenco di dati, sarà necessario inserire nella routine le istruzioni necessarie a reperire gli importi, gli sconti, e restituire le provvigioni spettanti con, per esempio, un'istruzione come quella presentata alla fine del paragrafo precedente.

Ora vediamo le stesse istruzioni della macro ProvvScalare() trasformate in Funzione Utente, cioè personalizzata; in questo caso sconto e importo diventeranno argomenti della funzione stessa che quindi ci permetterà o di essere richiamata direttamente sul foglio di lavoro, come una qualsiasi funzione di Excel, oppure di poter essere richiamata in un'altra macro come nell'esempio della pagina precedente. Ma vediamo la funzione. Non sto ad inserire commenti perchè sono gli stessi della macro:

Function ProvvScalare(importo, sconto) As Double
base = 5000
basedue = 25000
If base >= importo Then
minimo = importo
Else
minimo = base
End If
b = minimo
If basedue >= importo Then
c = importo - b
Else
c = basedue - b
End If
d = importo - (c + b)
If b < 0 Then b = 0
If c < 0 Then c = 0
If d < 0 Then d = 0

Select Case sconto

Case Is <= 5
pp = 8 / 100
sp = 4 / 100
tp = 2.5 / 100
F = b * pp
G = c * sp
H = d * tp
ProvvScalare = F + G + H

Case Is > 5
pp = 8 - ((sconto - 5) / 3)
sp = 4 - ((sconto - 5) / 3)
tp = 2.5 - ((sconto - 5) / 3)
If pp < 1.5 Then pp = 1.5
If sp < 1.5 Then sp = 1.5
If tp < 1.5 Then tp = 1.5
F = b * pp / 100
G = c * sp / 100
H = d * tp / 100
ProvvScalare = F + G + H
End Select

End Function

Gli argomenti della funzione: importo 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 in esempio si citava la A1 e la B1, e sarà : =ProvvScalare(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.

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.