Ricerca della corrispondenza più vicina
Per trovare la corrispondenza più vicina a un valore di ricerca in un set di dati numerici in Excel, puoi combinare le funzioni INDICE, CONFRONTA, ASS e MIN.
Come trovare la corrispondenza più vicina in Excel?
Per scoprire quale venditore ha realizzato vendite più vicine all’obiettivo di $20.000, come mostrato sopra, puoi utilizzare una formula che combina le funzioni INDICE, CONFRONTA, ASS e MIN nel modo seguente: la funzione ASS converte in valori positivi tutte le differenze tra le vendite effettuate da ciascun venditore e l’obiettivo di vendita; successivamente, la funzione MIN individua la differenza minima, ovvero il risultato più vicino all’obiettivo. A quel punto, la funzione CONFRONTA trova la posizione di questa corrispondenza più vicina, permettendo a INDICE di recuperare il nome del venditore corrispondente.
Sintassi generica
=INDEX()return_range,MATCH(MIN(ABS()))lookup_array-lookup_value)),ABS(lookup_array-lookup_value),0))
√ Nota: si tratta di una formula matriciale che richiede di essere inserita con Ctrl+Maiusc+Invio.
- intervallo_restituito: L'intervallo da cui si desidera che la formula combinata restituisca il nome del venditore. In questo caso, corrisponde all'intervallo contenente i nomi.
- matrice_di_ricerca: L'intervallo di celle contenente i valori da confrontare con il valore_di_ricerca. In questo caso, si riferisce all'intervallo delle vendite.
- valore_di_ricerca: Il valore da confrontare per trovare la corrispondenza più vicina. In questo caso, si riferisce all'obiettivo di vendita.
Per sapere quale venditore ha realizzato vendite più vicine all'obiettivo di $20,000, copiare o inserire la formula seguente nella cella F5 e premere Ctrl+Maiusc+Invioper ottenere il risultato:
=INDICE()B5:B10,CONFRONTA(MIN(ASS()))C5:C10-20000)),ASS(C5:C10-20000),0))
In alternativa, utilizzare Un riferimento di cella per rendere la formula dinamica:
=INDICE()B5:B10,CONFRONTA(MIN(ASS()))C5:C10-F4)),ASS(C5:C10-F4),0))
Spiegazione della formula
=INDEX()B5:B10,MATCH()MIN()ABS(C5:C10-F4)),ABS(C5:C10-F4),0))
- ABS(C5:C10-F4):La parte C5:C10-F4 calcola tutte le differenze tra ciascuna vendita nell'intervallo C5:C10 e l'obiettivo di vendita $20.000 nella cella F4, restituendo un array di questo tipo: {-4322;2451;6931;-1113;6591;-4782}. La funzione ASS trasforma tutti i numeri negativi in positivi, ottenendo così: {4322;2451;6931;1113;6591;4782}.
- MIN()ASS(C5:C10-F4))=MIN(){4322;2451;6931;1113;6591;4782}):La funzione MIN individua il numero più piccolo nell'array {4322;2451;6931;1113;6591;4782}, ovvero la differenza minima, cioè la corrispondenza più vicina. Di conseguenza, la funzione restituisce 1113.
- CONFRONTA()MIN()ASS(C5:C10-F4)),ASS(C5:C10-F4)Il parametro tipo_corrispondenza 0 obbliga la funzione CONFRONTA a individuare la posizione esatta del numero 1113 nell'array {4322;2451;6931;1113;6591;4782}. La funzione restituirà 4 poiché il numero si trova alla 4ª posizione.
- INDICE()B5:B10,CONFRONTA()MIN()ASS(C5:C10-F4)),ASS(C5:C10-F4)La funzione INDICE restituisce il 4° valore nell'intervallo dei nomi B5:B10, ovvero Bale.
Funzioni correlate
La funzione INDICE di Excel restituisce il Valore visualizzato in base a una posizione specificata all'interno di un intervallo o di una matrice.
La funzione CONFRONTA di Excel cerca un valore specifico in un intervallo di celle e ne restituisce la posizione relativa.
La funzione ASS restituisce il Valore assoluto di un numero. I numeri negativi vengono convertiti in positivi, mentre i numeri positivi e lo zero non subiscono alcuna modifica.
Formule correlate
Ricerca del valore con corrispondenza più vicina in base a criteri multipli
In alcuni casi potrebbe essere necessario cercare il valore con corrispondenza più vicina o approssimata in base a più di un criterio. Combinando le funzioni INDICE, CONFRONTA e SE, è possibile ottenere rapidamente il risultato desiderato in Excel.
Corrispondenza approssimata con INDICE e CONFRONTA
A volte è necessario trovare corrispondenze approssimate in Excel per valutare le prestazioni dei dipendenti, assegnare voti agli studenti, calcolare le tariffe postali in base al peso, ecc. In questa guida spiegheremo come utilizzare le funzioni INDICE e CONFRONTA per ottenere i risultati desiderati.
Ricerca del valore con corrispondenza più vicina in base a criteri multipli
In alcuni casi potrebbe essere necessario cercare il valore con corrispondenza più vicina o approssimata in base a più di un criterio. Combinando le funzioni INDICE, CONFRONTA e SE, è possibile ottenere rapidamente il risultato desiderato in Excel.
I Migliori Strumenti per la Produttività in Office
Kutools per Excel – Aiuta te a distinguerti dalla folla
Kutools per Excel Vanta oltre 300 funzionalità,assicurandoti che ciò di cui hai bisogno sia sempre a portata di clic...
Office Tab – Abilita la lettura e la modifica a schede in Microsoft Office (incluso Excel)
- Passa tra decine di documenti aperti in un secondo!
- Riduci centinaia di clic del mouse ogni giorno e dì addio al “mouse hand”.
- Aumenta la tua produttività del 50 % visualizzando e modificando più documenti contemporaneamente.
- Porta l’efficienza delle schede in Office (incluso Excel), proprio come in Chrome, Edge e Firefox.