Molto tempo fa, ho lavorato per il vice presidente delle vendite in un'azienda. Ho sempre modellato un nuovo programma bonus o un piano di commissioni. Sono diventato piuttosto abituato a commissionare piani con tutti i tipi di condizioni. Quello mostrato in questo suggerimento è piuttosto docile.
L'approccio normale consiste nell'iniziare a creare una formula IF annidata. Inizi sempre dalla parte alta o dalla parte bassa dell'intervallo. "Se le vendite superano i $ 500.000, lo sconto è del 20%; altrimenti, … " Il terzo argomento della funzione IF è una funzione IF completamente nuova che verifica il secondo livello: "Se le vendite sono superiori a $ 250.000, lo sconto è del 15%; altrimenti …"
Queste formule diventano sempre più lunghe man mano che ci sono più livelli. La parte più difficile di una formula del genere è ricordare quante parentesi di chiusura mettere alla fine della formula.
Se stai usando Excel 2003, la tua formula si sta già avvicinando al limite. Con quella versione, non è possibile annidare più di 7 funzioni IF. È stata una brutta giornata in cui i poteri che hanno cambiato il piano della commissione e hai bisogno di un modo per aggiungere un'ottava funzione IF. Oggi puoi annidare 64 funzioni IF. Non dovresti mai annidarne così tanti, ma è bello sapere che non ci sono problemi ad annidare 8 o 9.
Invece di utilizzare la funzione SE nidificata, prova a utilizzare la funzione CERCA.VERT. Quando il quarto argomento di CERCA.VERT cambia da Falso a Vero, la funzione non cerca più una corrispondenza esatta. Bene, il primo CERCA.VERT cerca di trovare una corrispondenza esatta. Ma se non viene trovata una corrispondenza esatta, Excel si deposita nella riga appena meno di quello che stai cercando.
Considera la tabella seguente. Nella cella C13, Excel cercherà una corrispondenza per $ 28.355 nella tabella. Quando non riesce a trovare 28355, Excel restituirà lo sconto associato al valore che è appena inferiore, in questo caso, lo sconto dell'1% per il livello di $ 10.000.
Quando converti le regole nella tabella in E13: F18, devi iniziare dal livello più piccolo e procedere al livello più alto. Sebbene non fosse specificato nelle regole, se qualcuno ha un fatturato inferiore a $ 10.000, lo sconto sarà dello 0%. È necessario aggiungerlo come prima riga nella tabella.
Attenzione
Quando si utilizza la versione "True" di CERCA.VERT, la tabella deve essere ordinata in ordine crescente. Molte persone credono che tutte le tabelle di ricerca debbano essere ordinate. Ma una tabella deve essere ordinata solo per una corrispondenza approssimativa.
E se il tuo manager desidera una formula completamente autonoma e non vuole vedere la tabella dei bonus a destra? Dopo aver creato la formula, puoi incorporare la tabella direttamente nella formula. Metti la formula in modalità Modifica facendo doppio clic sulla cella o selezionando la cella e premendo F2. Utilizzare il cursore per selezionare l'intero secondo argomento: $ E $ 13: $ F $ 18.
Premi il tasto F9. Excel incorpora la tabella di ricerca come costante di matrice. Nella costante di matrice, un punto e virgola indica una nuova riga e una virgola indica una nuova colonna. Vedere Comprensione delle costanti di matrice.
Premere Invio. Copia la formula nelle altre celle.
È ora possibile eliminare la tabella. La formula finale è mostrata di seguito.
Grazie a Mike Girvin per avermi insegnato le parentesi corrispondenti. La tecnica CERCA.VERT è stata suggerita da Danny Mac, Boriana Petrova, Andreas Thehos e @mvmcos.
