Le funzioni di Excel possono essere estremamente potenti se riesci a combinare diverse formule. Le cose che avrebbero potuto sembrare impossibili inizierebbero improvvisamente a sembrare un gioco da ragazzi.
Un esempio è trovare la corrispondenza più vicina di un valore di ricerca in un set di dati in Excel.
Ci sono un paio di utili funzioni di ricerca in Excel (come VLOOKUP & INDEX MATCH), che possono trovare la corrispondenza più vicina in alcuni semplici casi (come mostrerò con gli esempi di seguito).
Ma la parte migliore è che puoi combinare queste funzioni di ricerca con altre funzioni di Excel per fare molto di più (inclusa la ricerca della corrispondenza più vicina di un valore di ricerca in un elenco non ordinato).
In questo tutorial, ti mostrerò come trovare la corrispondenza più vicina di un valore di ricerca in Excel con le formule di ricerca.
Trova la corrispondenza più vicina in Excel
Ci possono essere molti scenari diversi in cui è necessario cercare la corrispondenza più vicina (o il valore di corrispondenza più vicino).
Di seguito sono riportati gli esempi che tratterò in questo articolo:
- Trova il tasso di commissione in base alle vendite
- Trova il miglior candidato (in base all'esperienza più vicina)
- Trovare la data del prossimo evento
Iniziamo!
Clicca qui per scaricare il file di esempio
Trova il tasso di commissione (ricerca del valore di vendita più vicino)
Supponiamo di avere un set di dati come mostrato di seguito in cui si desidera trovare le tariffe di commissione di tutto il personale di vendita.
La commissione viene assegnata in base al valore di vendita. E questo viene calcolato utilizzando la tabella a destra.
Ad esempio, se un venditore fa le vendite totali di 5000, la commissione è dello 0% e se fa le vendite totali di 15000, la commissione è del 5%.
Per ottenere il tasso di commissione, devi trovare l'intervallo di vendita più vicino appena inferiore a Il valore di vendita. Ad esempio, per un valore di vendita di 15000, la commissione sarebbe di 10.000 (che è il 5%) e per un valore di vendita di 25000, il tasso di commissione sarebbe di 20.000 (che è il 7%).
Per trovare il valore di vendita più vicino e ottenere il tasso di commissione, puoi utilizzare la corrispondenza approssimativa in CERCA.VERT.
La formula seguente farebbe questo:
=CERCA.VERT(B2,$E$2:$F$6,2,1)
Si noti che in questa formula, l'ultimo argomento è 1, che indica alla formula di utilizzare una ricerca approssimativa. Ciò significa che la formula passerà attraverso i valori di vendita nella colonna E e troverà il valore appena inferiore al valore di ricerca.
Quindi la formula CERCA.VERT fornirà il tasso di commissione per questo valore.
Nota: Affinché ciò funzioni, è necessario disporre i dati in ordine crescente.Clicca qui per scaricare il file di esempio
Trova il miglior candidato (in base all'esperienza più vicina)
Nell'esempio sopra, i dati dovevano essere ordinati in ordine crescente. Ma potrebbero esserci casi in cui i dati non vengono ordinati.
Quindi copriamo un esempio e vediamo come possiamo trovare la corrispondenza più vicina in Excel usando una combinazione di formule.
Di seguito è riportato un set di dati di esempio in cui ho bisogno di trovare il nome del dipendente che ha l'esperienza lavorativa più vicina al valore desiderato. Il valore desiderato in questo caso in 2,5 anni.
Notare che i dati non sono ordinati. Inoltre, l'esperienza più vicina può essere minore o maggiore dell'esperienza offerta. Ad esempio, 2 anni e 3 anni sono entrambi ugualmente vicini (differenza di 0,5 anni).
Di seguito la formula che ci darà il risultato:
=INDICE($A$2:$A$15,CONFRONTA(MIN(ABS(D2-B2:B15)),ABS(D2-$B$2:$B$15),0))
Il trucco in questa formula consiste nel modificare l'array di ricerca e il valore di ricerca per trovare la differenza minima di esperienza nei valori richiesti e effettivi.
Prima capiamo come lo faresti manualmente (e poi ti spiego come funziona questa formula).
Quando lo fai manualmente, passerai attraverso ogni cella nella colonna B e troverai la differenza nell'esperienza tra ciò che è richiesto e quello che ha una persona. Una volta che hai tutte le differenze, troverai quella minima e prendi il nome di quella persona.
Questo è esattamente ciò che stiamo facendo con questa formula.
Lasciatemi spiegare.
Il valore di ricerca nella formula CONFRONTA è MIN(ABS(D2-B2:B15)).
Questa parte ti dà la differenza minima tra l'esperienza data (che è 2,5 anni) e tutte le altre esperienze. In questo esempio, restituisce 0.3
Nota che ho usato l'ABS per assicurarmi di cercare il più vicino (che può essere più o meno dell'esperienza data).
Ora, questo valore minimo diventa il nostro valore di ricerca.
L'array di ricerca nella funzione MATCH è ABS(D2-$B$2:$B$15).
Questo ci dà una serie di numeri da cui è stato sottratto 2,5 (l'esperienza richiesta).
Quindi ora abbiamo un valore di ricerca (0,3) e un array di ricerca ({6,8;0,8;19,5;21,8;14,5;11,2;0,3;9,2;2;9,8;14,8;0,4;23,8;2,9})
La funzione CONFRONTA trova la posizione di 0.3 in questa matrice, che è anche la posizione del nome della persona che ha l'esperienza più vicina.
Questo numero di posizione viene quindi utilizzato dalla funzione INDICE per restituire il nome della persona.
Nota: nel caso in cui ci siano più candidati con la stessa esperienza minima, la formula di cui sopra darà il nome del primo dipendente corrispondente.
Trova la data del prossimo evento
Questo è un altro esempio in cui è possibile utilizzare le formule di ricerca per trovare la data successiva di un evento in base alla data corrente.
Di seguito è riportato il set di dati in cui ho i nomi degli eventi e le date degli eventi.
Quello che voglio è il nome del prossimo evento e la data dell'evento per questo prossimo evento.
Di seguito la formula che darà il nome al prossimo evento:
=INDICE($A$2:$A$11,CONFRONTA(E1,$B$2:$B$11,1)+1)
E sotto la formula darà la data dell'evento imminente:
=INDICE($B$2:$B$11,CONFRONTA(E1,$B$2:$B$11,1)+1)
Lascia che ti spieghi come funziona questa formula.
Per ottenere la data dell'evento, la funzione CONFRONTA cerca la data corrente nella colonna B. In questo caso, non stiamo cercando una corrispondenza esatta, ma approssimativa. E quindi, l'ultimo argomento della funzione CONFRONTA è 1 (che trova il valore più grande minore o uguale al valore di ricerca).
Quindi la funzione CONFRONTA restituirebbe la posizione della cella che ha la data che è appena inferiore o uguale alla data corrente. Quindi l'evento successivo, in questo caso, sarebbe nella cella successiva (poiché l'elenco è ordinato in ordine crescente).
Quindi, per ottenere la data dell'evento imminente, aggiungine una alla posizione della cella restituita dalla funzione CONFRONTA e ti darà la posizione della cella della data dell'evento successivo.
Questo valore è poi dato dalla funzione INDICE.
Per ottenere il nome dell'evento, viene utilizzata la stessa formula e l'intervallo nella funzione INDICE viene modificato dalla colonna B alla colonna A.
Clicca qui per scaricare il file di esempio
L'idea di questo esempio mi è venuta quando un amico si è avvicinato con una richiesta. Aveva un elenco di tutti i compleanni dei suoi amici/parenti in una colonna e voleva sapere il prossimo compleanno in arrivo (e il nome della persona).Questi sono tre esempi che mostrano come trovare il valore corrispondente più vicino in Excel utilizzando le formule di ricerca.
Potrebbero piacerti anche i seguenti suggerimenti/tutorial di Excel
- Ottieni l'ultimo numero da un elenco utilizzando la funzione CERCA.VERT.
- Ottieni più valori di ricerca senza ripetizione in una singola cella.
- VLOOKUP Vs. INDICE/MATCH
- CORRISPONDENZA INDICE Excel
- Trova l'ultima occorrenza di un valore di ricerca un elenco in Excel
- Trova e rimuovi duplicati in Excel
- Formattazione condizionale.
