Hai una formula di Excel che annida più funzioni IF? Quando si arriva al punto con troppe istruzioni IF annidate, è necessario vedere se l'intera cosa diventerebbe più semplice con una semplice funzione CERCA.VERT. Ho visto formule di 1000 caratteri semplificate in una formula CERCA.VERT da 30 caratteri. È facile da mantenere quando è necessario aggiungere nuovi valori in un secondo momento.
Molto tempo fa ho lavorato per il vice presidente delle vendite al lavoro. 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 qui sotto è 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.
Mini suggerimento bonus
Corrispondenza delle parentesi
Excel scorre una varietà di colori per ogni nuovo livello di parentesi. Mentre Excel riutilizza i colori, utilizza il nero solo per la parentesi di apertura e per la parentesi di chiusura corrispondente. Mentre finisci la formula seguente, continua a digitare le parentesi di chiusura finché non digiti una parentesi nera.
Torna al suggerimento della formula annidata
Se stai usando Excel 2003, la tua formula si sta già avvicinando al limite. Allora non era 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 farlo, ma è bello sapere che non ci sono problemi ad annidare 8 o 9.
Invece di utilizzare la funzione SE nidificata, prova a utilizzare l'uso insolito per 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 quanto 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 nel caso di 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.
Utilizzando il cursore, selezionare l'intero secondo argomento: $ E $ 13: $ F $ 18.
Premi il tasto F9. Excel incorporerà 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.
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.
Guarda un video
- Con una commissione a più livelli, un bonus o un programma di sconti, spesso devi annidare le tue funzioni IF
- Il limite di Excel 2003 era di 7 istruzioni IF nidificate.
- Ora puoi annidare 32, ma non penso che dovresti mai annidare 32
- Quando useresti mai la versione corrispondente approssimativa di CERCA.VERT? Questo è il momento.
- Tradurre il programma di sconti in una tabella di ricerca
- CERCA.VERT non trova la risposta nella maggior parte dei casi.
- Mettendo, True alla fine dirà a CERCA.VERT di trovare il valore appena inferiore.
- Questa è l'unica volta che la tabella CERCA.VERT deve essere ordinata.
- Non vuoi la tabella CERCA.VERT a lato? Incorporalo nella formula.
- F2 per modificare la formula. Seleziona la tabella di ricerca. Premi F9. Accedere.
Trascrizione del video
Impara Excel dal podcast, episodio 2030 - Sostituito SE annidato con CERCA.VERT!
Trasmetterò in podcast l'intero libro, fai clic sulla "i" nell'angolo in alto a destra per accedere alla playlist!
Ehi, bentornato al netcast, sono Bill Jelen. Va bene, questo è davvero comune con un programma di commissioni, un programma di sconti o un programma bonus, in cui abbiamo i livelli, livelli diversi, giusto? Se hai più di $ 10000, ottieni l'1% di sconto, $ 50000 di sconto del 5%. Devi stare attento quando costruisci questa istruzione IF annidata, la inizi all'estremità superiore se stai cercando maggiore di, o all'estremità inferiore se stai cercando minore di. Quindi B10> 50000, 20%, altrimenti un altro IF, B10> 250000, 25% e così via e così via. Questa è solo una formula lunga e complicata, e se la tua tabella è più grande, in Excel 2003, non potresti annidare più di 7 istruzioni IF, qui siamo quasi vicini al limite. Puoi andare a 32 ora, non penso che dovresti mai andare a 32, e anche se stai urlando "Ehi aspetta,che dire della nuova funzione appena uscita in Excel 2016, la versione di febbraio 2016, con la funzione IFS? " Sì certo, qui ci sono meno parentesi e 87 caratteri invece di 97 caratteri, è ancora un casino, sbarazziamoci di questo e fallo con CERCA.
Bene, ecco i passaggi, prendi quelle regole e le capovolgi. La prima cosa che dobbiamo dire è che se avevi $ 0 di vendita, ottieni uno sconto dello 0%, se hai $ 10000 di vendite, ottieni uno sconto dell'1%, se hai $ 50000 di vendite, ottieni uno sconto del 5% . $ 100000, sconto del 10%, $ 250000, sconto del 15% e poi, infine, qualsiasi cosa> $ 500000 ottiene lo sconto del 20%, va bene. Molte volte, ogni volta che parlo di CERCA.VERT, dico che ogni CERCA.VERT che creerai finirà in FALSO e le persone diranno "Beh aspetta un secondo, qual è la versione VERO per lì comunque?" È proprio per questo caso in cui stiamo cercando un intervallo, quindi stiamo cercando questo valore, un VLOOKUP normale, ovviamente, 2, FALSE non troverà 550000, restituirà il # N / A!Quindi in questo caso molto speciale cambieremo quel FALSO in VERO, e questo dirà a Excel "Vai a cercare il 550000, se non riesci a trovarlo, dacci il valore che è solo inferiore". Quindi ci dà il 20%, è quello che fa, va bene? E questa è l'unica volta che la tua tabella CERCA.VERT deve essere ordinata, tutte le altre volte con, FALSE, può essere come desideri. E sì, potresti lasciare, TRUE spento, ma lo metto sempre lì solo per ricordare a me stesso che questo è uno di quegli strani e incredibilmente pericolosi VLOOKUP, e non voglio copiare questa formula da qualche altra parte. Va bene, quindi copieremo e incolleremo formule speciali, va bene.Tutto a posto? E questa è l'unica volta che la tua tabella CERCA.VERT deve essere ordinata, tutte le altre volte con, FALSE, può essere come desideri. E sì, potresti lasciare, TRUE spento, ma l'ho sempre messo lì solo per ricordare a me stesso che questo è uno di quegli strani e incredibilmente pericolosi CERCA.VERT, e non voglio copiare questa formula da qualche altra parte. Va bene, quindi copieremo e incolleremo le formule speciali, va bene.Tutto a posto? E questa è l'unica volta che la tua tabella CERCA.VERT deve essere ordinata, tutte le altre volte con, FALSE, può essere come desideri. E sì, potresti lasciare, TRUE spento, ma lo metto sempre lì solo per ricordare a me stesso che questo è uno di quegli strani e incredibilmente pericolosi VLOOKUP, e non voglio copiare questa formula da qualche altra parte. Va bene, quindi copieremo e incolleremo le formule speciali, va bene.
Prossima lamentela: il tuo manager non vuole vedere la tabella di ricerca, la vuole "Sbarazzati di quella tabella di ricerca". Va bene, possiamo farlo incorporando la tabella di ricerca, controlla questo, ottimo trucco che ho ricevuto da Mike Girvin su ExcelIsFun. F2 per mettere la formula in modalità Modifica, quindi selezionare con molta attenzione solo l'intervallo per la tabella di ricerca. Premi F9 e prende questa tabella e la inserisce nella nomenclatura degli array, quindi premo semplicemente Invio in questo modo. Copia quello, Incolla formule speciali, e poi sono libero di sbarazzarmi della tabella di ricerca, che continua a funzionare lungo la riga. Questa è una seccatura enorme se devi tornare e modificare questo, devi davvero fissarlo con molta chiarezza. La virgola significa che stiamo andando alla colonna successiva, il punto e virgola significa che stiamo andando alla riga successiva, va bene,ma in teoria potresti tornare lì e cambiare quella formula se uno dei numeri della catena cambia.
Va bene, quindi usare un CERCA.VERT invece di un'istruzione IF annidata è il suggerimento n. 32 sulla strada per 40, "40 migliori consigli di Excel di tutti i tempi", puoi avere questo intero libro. Fai clic sulla "i" nell'angolo in alto a destra, $ 10 per un e-book, $ 25 per il libro stampato, va bene. Quindi oggi stiamo cercando di risolvere un bonus di commissione a più livelli o un programma di sconti. Gli IF annidati sono davvero comuni, attenzione, 7 è tutto ciò che puoi avere in Excel 2003, oggi puoi averne 32, ma non dovresti mai averne 32. Quindi, quando usi la versione MATCH approssimativa di VLOOKUP, è questo. Prendi quel programma di sconti, capovolgilo, trasformalo in una tabella di ricerca che inizia dal numero più piccolo e arriva al numero più grande. CERCA.VERT, ovviamente, non troverà la risposta, ma cambiando CERCA.VERT in, VERO alla fine, gli dirà di trovare il valore appena inferiore,questa è l'unica volta che la tabella deve essere ordinata. Se non vuoi vedere quella tabella là fuori, puoi incorporarla nella formula usando il trucco di Mike Girvin, F2 per modificare la formula, selezionare la tabella di ricerca, premere F9, quindi Invio.
Va bene ehi, voglio ringraziarti per essere passato, ci vediamo la prossima volta per un altro netcast da!
Download file
Scarica il file di esempio qui: Podcast2030.xlsx
