Bottom line: Scopri come calcolare le commissioni in Excel per un piano a più livelli di base e la tabella dei tassi utilizzando le funzioni IF, VLOOKUP o XLOOKUP.
Livello di abilità: Intermedio
Video Tutorial
Il calcolo delle commissioni in Excel può essere un compito molto complicato. Ciò è particolarmente vero se si è tentato di utilizzare più istruzioni IF per calcolare le commissioni per ogni livello in una tabella dei tassi.
Questo articolo spiegherà come utilizzare la funzione VLOOKUP per rendere questo processo molto più semplice. Il segreto è impostare l’ultimo argomento nel vlookup su TRUE, per trovare la corrispondenza più vicina.
Si prega di controllare il mio articolo su Come utilizzare VLOOKUP per trovare la corrispondenza più vicina per una spiegazione dettagliata sull’impostazione dell’ultimo argomento su TRUE.
Aggiorna: Il post è stato aggiornato per includere le istruzioni per il calcolo delle commissioni con la nuova funzione XLOOKUP. Ecco un articolo & video su XLOOKUP.
Scarica file
Scarica il file per seguirlo.
Calcoli commissioni – SE Vs VLOOKUP Vs XLOOKUP.xlsx (31.5 KB)
Calcolo delle commissioni con VLOOKUP
Con un semplice piano di commissioni in genere si dispone di una tabella dei tassi che elenca il tasso di pagamento ad ogni livello di vendite. Come il rappresentante di vendita rende più vendite, la sua/il suo tasso di payout in genere aumentare.
Il tasso di pagamento potrebbe essere un importo forfettario in dollari, percentuale di entrate,percentuale di quota, ecc. Il lavoro del VLOOKUP è quello di trovare l’importo delle vendite del rappresentante nella tabella dei tassi e restituire il tasso di pagamento corrispondente.
Per questo esempio il nostro piano di commissioni si presenta come segue:
- Rep vende $0-$50,000, guadagna il 5%
- Rep vende $51,000-a$100.000, che guadagnano il 7%
- Rep vende $da 100.001-150.000 dollari, si guadagna il 10%
- Rep vende per oltre $150,001, che guadagnano 15%
Il rappresentante farà fare una certa quantità di vendite durante il mese. Lui / lei guadagnerà un certo tasso di pagamento a seconda del livello di vendite raggiunto.
Possiamo utilizzare una formula VLOOKUP per calcolare il tasso di pagamento per un determinato importo di vendita (valore di ricerca). Perché questo funzioni, dobbiamo impostare l’ultimo argomento nel vlookup su TRUE.
Con l’ultimo argomento impostato su TRUE, vlookup troverà la corrispondenza più vicina al valore di ricerca minore o uguale all’importo di ricerca. Questo fondamentalmente ci permette di trovare un valore tra intervalli di due numeri (livelli).
Questo è il motivo per cui l’ultimo argomento in VLOOKUP è chiamato range_lookup. Quando questo argomento è VERO, VLOOKUP sta cercando tra intervalli di valori nella colonna tier minimum per trovare una corrispondenza esatta o il valore inferiore al valore di ricerca.
Come impostare la tabella dei tassi
Quando si inserisce questa tabella dei tassi in Excel, è sufficiente elencare il livello minimo per l’intervallo di ricerca. Anche in questo caso il VLOOKUP cercherà una “corrispondenza più vicina” minore o uguale al valore di ricerca. Se trova un valore maggiore del valore di ricerca, restituirà la riga precedente.
In questo esempio per una tabella dei tassi delle commissioni, la prima riga nell’intervallo di ricerca deve essere zero. Questo perché il rappresentante di vendita potrebbe potenzialmente avere vendite di $0 e il valore di ricerca sarebbe zero. Se il valore di ricerca (importo delle vendite) fosse un numero negativo, il vlookup restituirebbe un errore.
È importante saperlo e impostare la tabella dei tassi per tutti i possibili valori di ricerca.
Se l’importo delle vendite è maggiore dell’ultima riga nell’intervallo di ricerca, vlookup restituirà l’ultima riga. Ad esempio, se il rappresentante ha effettuato vendite di $175.000, vlookup restituirebbe il 15%.
Calcola le commissioni per restituire un valore in dollari
Il pagamento può anche essere restituito come valore in dollari, invece di una percentuale. Con questa configurazione la vincita sarà una tariffa forfettaria.
Ciò significa che il pagamento sarà lo stesso, indipendentemente dall’importo delle vendite all’interno del livello. Nell’esempio seguente, se la vincita sarà di $1.000 se l’importo delle vendite è di $55.000 o 9 95.000.
La vincita NON è su scala mobile. Si tratta di una tariffa forfettaria per ogni livello.
Se stai cercando un calcolo a scala mobile, vedi il mio articolo sul calcolo della commissione con una struttura a più livelli utilizzando SUMPRODUCT.
Calcoli della commissione con XLOOKUP
Possiamo anche utilizzare la nuova funzione XLOOKUP per questo calcolo. Funziona molto simile a VLOOKUP quando si trova la corrispondenza più vicina.
Vantaggi con XLOOKUP
Ci sono due differenze e vantaggi principali con XLOOKUP:
- Con XLOOKUP specifichiamo l’array di ricerca e l’array di ritorno come intervalli separati. Questo è B4: B7 e D4: D7 nell’immagine sopra. Questo è un vantaggio rispetto a VLOOKUP perché possiamo inserire o eliminare righe tra le colonne B e D e la formula funzionerà ancora.
Quando aggiungiamo/cancelliamo colonne all’interno dell’array di tabelle di VLOOKUP, dobbiamo modificare manualmente il numero di indice della colonna o utilizzare una formula per calcolarlo dinamicamente. - XLOOKUP NON richiede l’ordinamento dei dati quando si utilizza la corrispondenza esatta o l’elemento successivo più piccolo/più grande per la modalità corrispondenza. XLOOKUP cercherà effettivamente il prossimo elemento più piccolo / più grande e restituirà il risultato da quella riga/colonna.
VLOOKUP richiede che i dati siano ordinati quando l’ultimo argomento è VERO. In genere vorrai comunque ordinare i tuoi dati per questi tipi di tabelle, ma è bene sapere che puoi semplicemente inserire nuovi livelli di frequenza nella parte inferiore della tabella e XLOOKUP funzionerà.
Svantaggio di XLOOKUP
Lo svantaggio principale di XLOOKUP è la compatibilità. Sia tu che tutti gli utenti del tuo file devi essere su una versione di Microsoft/Office 365 con XLOOKUP. NON è retrocompatibile o disponibile su versioni precedenti di Excel.
Pertanto, VLOOKUP funziona alla grande in questo scenario ed è molto più facile da scrivere di una formula IF nidificata.
Evita le formule IF annidate
Un approccio comune per calcolare le commissioni utilizza le istruzioni IF. Con una tabella dei tassi come questa dovresti scrivere più istruzioni IF. Fondamentalmente devi scrivere una dichiarazione IF per ogni livello (riga) nella tabella.
Devi quindi unire tutte le istruzioni IF in una formula lunga e brutta. Questi sono chiamati istruzioni IF nidificate.
Cerco sempre di evitare IF annidati quando possibile perché sono difficili da leggere e capire e possono essere più lenti per il calcolo di Excel. Se si dispone di migliaia di formule IF nidificate nella cartella di lavoro, il tempo di calcolo potrebbe rallentare.
La formula IF è molto più difficile da mantenere. Richiede modifiche quando le righe vengono aggiunte / eliminate dalla tabella. VLOOKUP e XLOOKUP NON richiedono questo tipo di manutenzione.
Se si utilizza IF, assicurarsi di rendere ogni riferimento di cella alla tabella dei tassi un riferimento assoluto (F4 sulla tastiera). Altrimenti si otterranno risultati errati quando si copia la formula verso il basso.
L’utilizzo di VLOOKUP è molto più semplice e più pulito rispetto all’utilizzo di formule IF nidificate. Come puoi vedere con questo esempio, il vlookup ti consente di utilizzare una formula per calcolare il tasso di pagamento della commissione per un determinato importo di vendita.
Risorse Aggiuntive
- Come Utilizzare VLOOKUP per Trovare la Corrispondenza più Vicina – Ultimo Argomento è Uguale a TRUE
- Come Calcolare Cumulativo Livelli Commissioni con SUMPRODUCT
- VLOOKUP Spiegato in Termini Semplici a Starbucks
- VLOOKUP & MATCH – UN Duo Dinamico
- Il Nuovo XLOOKUP Funzione di Excel + Video Tutorial
- SE la Funzione di ha Spiegato: Come Scrivere un’Istruzione IF Formula in Excel
Come Si fa a Utilizzare VLOOKUP con la Corrispondenza più Vicina?
Il VLOOKUP con la tecnica di corrispondenza più vicina può essere utilizzato anche per i calcoli della staffa fiscale, la corrispondenza dei prezzi, ecc. Cosa usi questa tecnica per calcolare?