Le funzioni di Excel possono essere molto potenti se impari a combinare formule diverse.Ciò che una volta sembrava impossibile può improvvisamente diventare come un gioco da bambini.
Uno di questi esempi è trovare il valore più vicino in un set di dati Excel.
Esistono diverse utili funzioni di ricerca in Excel (come VLOOKUP e INDEX MATCH) che possono trovare il più vicino值(come mostrato nell'esempio seguente).
Ma la parte migliore è che puoi combinare queste funzioni di ricerca con altre funzioni di Excel per fare molto di più (incluso trovare la corrispondenza più vicina a un valore di ricerca in un elenco non ordinato).
In questo tutorial, ti mostrerò come utilizzare una formula di ricerca per trovare la corrispondenza più vicina a un valore di ricerca in Excel.
Esistono molti scenari diversi in cui è necessario trovare la corrispondenza più vicina (o il valore di corrispondenza più vicino).
Ecco un esempio che tratterò in questo articolo:
- Trova le commissioni in base alle vendite
- Trova il miglior candidato (in base all'esperienza più vicina)
- Trova la data dell'evento successivo
Iniziamo!
Contenuti
Trova i tassi di commissione (cerca il valore di vendita più vicino)
Supponiamo di avere un set di dati come quello di seguito in cui desideri trovare le commissioni per tutti i venditori.
Le commissioni sono distribuite in base al valore della vendita.Questo viene calcolato utilizzando la tabella a destra.
Ad esempio, se le vendite totali del venditore sono 5000, la commissione è dello 0% e se le sue vendite totali sono 15000, la commissione è del 5%.
Per ottenere un tasso di commissione, devi trovare l'intervallo di vendita più vicino che è appena al di sotto del valore di vendita.Ad esempio, per 15000 vendite, la commissione è 10,000 (o 5%) e per 25000 vendite, il tasso di commissione è 20,000 (o 7%).
Per trovare il valore di vendita più vicino e ottenere una commissione, puoi utilizzare la corrispondenza approssimativa in VLOOKUP.
La seguente formula può farlo:
= CERCA.VERT (B2, $ E $ 2: $ F $ 6,2,1)
Si noti che in questa formula, l'ultimo parametro è 1, che indica alla formula di utilizzare la ricerca approssimativa.Ciò significa che la formula esaminerà il valore di vendita nella colonna E e troverà il valore appena al di sotto del valore di ricerca.
La formula VLOOKUP fornirà quindi il tasso di commissione per quel valore.
注意: Per fare ciò, è necessario ordinare i dati in ordine crescente.
Trova il miglior candidato (in base all'esperienza più vicina)
Nell'esempio sopra, i dati devono essere ordinati in ordine crescente.Ma ci possono essere casi in cui i dati non sono ordinati.
Quindi facciamo un esempio e vediamo come possiamo utilizzare le combinazioni di formule per trovare la corrispondenza più vicina in Excel.
Di seguito è riportato un set di dati di esempio in cui è necessario trovare i nomi dei dipendenti la cui esperienza lavorativa è più vicina al valore desiderato.Il valore atteso in questo caso è di 2.5 anni.
Si noti che i dati non sono ordinati.Inoltre, le esperienze più vicine possono essere inferiori o superiori a una determinata esperienza.Ad esempio, 2 anni e 3 anni sono ugualmente vicini (0.5 anni di differenza).
Ecco la formula che ci dà il risultato:
=INDEX($A$2:$A$15,MATCH(MIN(ABS(D2-B2:B15)),ABS(D2-$B$2:$B$15),0))
Il trucco in questa formula consiste nel modificare la matrice di ricerca e i valori di ricerca per trovare la più piccola differenza empirica tra i valori desiderati e quelli effettivi.
Capiamo prima come lo faresti manualmente (e poi ti spiegherò come funziona questa formula).
Quando lo fai manualmente, esamini ogni cella nella colonna B e trovi la differenza tra l'esperienza che desideri e l'esperienza che si ha.Una volta che hai tutte le differenze, trovi quella più piccola e ottieni il nome di quella persona.
Questo è esattamente ciò che facciamo con questa formula.
Lasciatemi spiegare.
Il valore di ricerca nella formula CONFRONTA è MIN(ABS(D2-B2:B15)).
Questa sezione fornisce la differenza minima tra l'esperienza data (cioè 2.5 anni) e tutte le altre esperienze.In questo caso restituisce 0.3
Nota che uso l'ABS per assicurarmi di cercare il più vicino (probabilmente più o meno dell'esperienza data).
Ora, questo valore minimo diventa il nostro valore di ricerca.
L'array di ricerca nella funzione CONFRONTA è ABS(D2-$B$2:$B$15).
Questo ci fornisce una matrice di numeri da cui viene sottratto 2.5 (esperienza richiesta).
所以現在我們有一個查找值 (0.3) 和一個查找數組 ({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 MATCH trova la posizione di 0.3 in questo array, che è la posizione del nome della persona con l'esperienza più vicina.
La funzione INDICE utilizza quindi il numero del lavoro per restituire il nome della persona.
Nota: se ci sono più candidati con la stessa esperienza minima, la formula sopra riporterà il nome del primo dipendente corrispondente.
Trova la data dell'evento successivo
Ecco un altro esempio in cui puoi utilizzare una formula 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 di questo prossimo evento.
Ecco la formula che dà il nome del prossimo evento:
=INDEX($A$2:$A$11,MATCH(E1,$B$2:$B$11,1)+1)
La seguente formula darà le date del prossimo evento:
=INDEX($B$2:$B$11,MATCH(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 nella colonna B la data corrente.In questo caso, non stiamo cercando una corrispondenza esatta, ma una corrispondenza approssimativa.Pertanto, l'ultimo parametro della funzione CONFRONTA è 1 (trova il valore più grande minore o uguale al valore di ricerca).
Quindi la funzione CONFRONTA restituirà la posizione della cella la cui data risulta essere minore o uguale alla data corrente.Quindi in questo caso l'evento successivo sarà 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 MATCH e ti darà la posizione della cella della data dell'evento successivo.
Questo valore viene quindi fornito dalla funzione INDICE.
Per ottenere il nome dell'evento, utilizzare la stessa formula e modificare l'intervallo nella funzione INDICE dalla colonna B alla colonna A.
Questo esempio mi è venuto in mente quando un amico ha fatto una richiesta.Elenca i compleanni di tutti i suoi amici/parenti in una colonna e vuole sapere che sta arrivando il prossimo compleanno (e il nome della persona).
I tre esempi precedenti mostrano come utilizzare la formula di ricerca per trovare il valore più vicino in Excel.
