È possibile utilizzare la funzione CONFRONTA di Excel per trovare la posizione relativa di un valore specifico in un intervallo di celle o in una matrice.
La funzione CONFRONTA è simile alla funzione CERCA.VERT in quanto entrambe classificate in Funzioni di ricerca/riferimento di Excel. CERCA.VERT cerca un valore specifico in una colonna e restituisce un valore nella stessa riga mentre la funzione CONFRONTA cerca un determinato valore in un intervallo e restituisce la posizione di quel valore.
La funzione CONFRONTA di Excel cerca un valore specificato in un intervallo di celle o in una matrice e restituisce la posizione relativa della prima apparizione di quel valore nell'intervallo. La funzione CONFRONTA può essere utilizzata anche per cercare un determinato valore e restituire il valore corrispondente con l'aiuto della funzione INDICE (proprio come Vlookup). Vediamo come utilizzare la funzione CONFRONTA di Excel per trovare la posizione di un valore di ricerca in un intervallo di celle.
Funzione CONFRONTA di Excel
La funzione CONFRONTA è una funzione incorporata in Excel ed è utilizzata principalmente per individuare la posizione relativa di un valore di ricerca in una colonna o in una riga.
Sintassi della funzione MATCH:
=CONFRONTA(valore_ricerca,array_ricerca,[tipo_corrispondenza})Dove:
valore di ricerca - Il valore che vuoi cercare in un intervallo di celle specificato o in una matrice. Può essere un valore numerico, un valore di testo, un valore logico o un riferimento di cella che ha un valore.
lookup_array – Gli array di celle in cui stai cercando un valore. Deve essere una singola colonna o una singola riga.
match_type – È un parametro opzionale che può essere impostato su 0,1 o -1 e il valore predefinito è 1.
- 0 cerca una corrispondenza esatta, quando non la trova restituisce un errore.
- -1 cerca il valore più piccolo maggiore o uguale a lookup_value quando l'array di ricerca in ordine crescente.
- 1 cerca il valore più grande che è minore o uguale al valore look_up quando l'array di ricerca in ordine decrescente.
Trova la posizione di una corrispondenza esatta
Supponiamo di avere il seguente set di dati in cui vogliamo trovare la posizione di un certo valore.
In questa tabella, vogliamo trovare la posizione di un nome di città (Memphis) nella colonna (A2:A23), quindi usiamo questa formula:
=CONFRONTA("memphis",A2:A23,0)Il terzo argomento è impostato su "0" perché vogliamo trovare una corrispondenza esatta del nome della città. Come puoi vedere che il nome della città “memphis” nella formula è in minuscolo mentre nella tabella la prima lettera del nome della città è in maiuscolo (Memphis). Tuttavia, la formula è in grado di trovare la posizione del valore specificato nell'intervallo specificato. È perché la funzione CONFRONTA non fa distinzione tra maiuscole e minuscole.
Nota: Se il valore_ricerca non viene trovato nell'intervallo di ricerca o se si specifica l'intervallo di ricerca errato, la funzione restituirà l'errore #N/D.
Puoi usare un riferimento di cella nel primo argomento della funzione invece di un valore diretto. La formula seguente trova la posizione del valore nella cella F2 e restituisce il risultato nella cella F3.
Trova la posizione di una corrispondenza approssimativa
Esistono due modi per cercare una corrispondenza approssimativa o esatta del valore di ricerca e restituirne la posizione.
- Un modo consiste nel trovare il valore più piccolo maggiore o uguale (la corrispondenza più grande successiva) al valore specificato. Può essere ottenuto impostando l'ultimo argomento (match_type) della funzione come '-1'
- Un altro modo è il valore più grande che è minore o uguale (la corrispondenza più piccola successiva) al valore dato. Può essere ottenuto impostando il match_type della funzione come '1'
La prossima partita più piccola
Se la funzione non riesce a trovare una corrispondenza esatta con il valore specificato quando il tipo di corrispondenza è impostato su "1", individua il valore più grande leggermente inferiore al valore specificato (che significa il valore più piccolo successivo) e restituisce la sua posizione . Affinché ciò funzioni, è necessario ordinare l'array in ordine crescente, in caso contrario si verificherà un errore.
Nell'esempio, usiamo la formula seguente per trovare la corrispondenza più piccola successiva:
=CONFRONTA(F2,D2:D23,1)Quando questa formula non riesce a trovare la corrispondenza esatta per il valore nella cella F2, punta alla posizione (16) del successivo valore più piccolo, cioè 98.
Prossima partita più grande
Quando il tipo di corrispondenza è impostato su "-1" e la funzione CONFRONTA non riesce a trovare una corrispondenza esatta, trova il valore più piccolo maggiore del valore specificato (che significa il valore successivo più grande) e restituisce la sua posizione. L'array di ricerca deve essere ordinato in ordine decrescente per questo metodo, altrimenti restituirà un errore.
Ad esempio, inserisci la seguente formula per trovare la corrispondenza più grande successiva al valore di ricerca:
=CONFRONTA(F2,D2:D23,-1)Questa funzione CONFRONTA cerca il valore in F2 (55) nell'intervallo di ricerca D2: D23 e, quando non riesce a trovare la corrispondenza esatta, restituisce la posizione (16) del valore successivo più grande, ovvero 58.
Partita con caratteri jolly
I caratteri jolly possono essere utilizzati nella funzione CONFRONTA solo quando match_type è impostato su "0" e il valore di ricerca è una stringa di testo. Ci sono caratteri jolly che puoi usare nella funzione CONFRONTA: un asterisco (*) e un punto interrogativo (?).
- Punto interrogativo (?) viene utilizzato per abbinare qualsiasi singolo carattere o lettera con la stringa di testo.
- Asterisco (*) viene utilizzato per abbinare un numero qualsiasi di caratteri alla stringa.
Ad esempio, abbiamo usato due caratteri jolly '?' nel lookup_value (Lo??n) della funzione MATCH per trovare un valore che corrisponda alla stringa di testo con due caratteri qualsiasi (nei posti dei caratteri jolly). E la funzione restituisce la posizione relativa del valore corrispondente nella cella E5.
=CONFRONTA("Lo??n",A2:A22,0)
È possibile utilizzare il carattere jolly (*) allo stesso modo di (?), ma viene utilizzato un asterisco per corrispondere a qualsiasi numero di caratteri mentre un punto interrogativo viene utilizzato per corrispondere a un singolo carattere.
Ad esempio, se usi "sp*", la funzione potrebbe corrispondere a speaker, speed o spielberg, ecc. Ma se la funzione trova valori multipli/duplicati che corrispondono al valore di ricerca, restituirà solo la posizione del primo valore.
Nell'esempio, abbiamo inserito "Kil*o" nell'argomento lookup_value. Quindi la funzione MATCH() cerca un testo che contiene "Kil" all'inizio, "o" alla fine e qualsiasi numero di caratteri nel mezzo. 'Kil*o' corrisponde a Kilimanjaro nell'array e quindi la funzione restituisce la posizione relativa di Kilimanjaro, che è 16.
INDICE e CORRISPONDENZA
Le funzioni MATCH sono usate raramente da sole. Spesso si associavano ad altre funzioni per creare formule potenti. Quando la funzione CONFRONTA è combinata con la funzione INDICE, può eseguire ricerche avanzate. Molte persone preferiscono ancora utilizzare CERCA.VERT per cercare un valore, perché è più semplice ma INDEX MATCH è più flessibile e veloce di CERCA.VERT.
CERCA.VERT può solo cercare un valore verticalmente, ad esempio colonne, mentre la combinazione INDEX MATCH può eseguire ricerche sia verticali che orizzontali.
Funzione INDICE utilizzata per recuperare un valore in una posizione specifica in una tabella o in un intervallo. La funzione CONFRONTA restituisce la posizione relativa di un valore in una colonna o in una riga. Quando combinato, CONFRONTA trova il numero di riga o colonna (posizione) di un valore specifico e la funzione INDICE recupera un valore in base a tale numero di riga e colonna.
Sintassi della funzione INDICE:
=INDICE(array,num_riga,[num_col],)Comunque vediamo come funziona INDEX MATCH con un esempio.
Nell'esempio seguente, vogliamo recuperare il punteggio "Quiz2" per lo studente "Anne". Per farlo utilizzeremo la seguente formula:
=INDICE(B2:F20,CONFRONTA(H2,A2:A20,0),3)INDEX necessita di un numero di riga e di colonna per recuperare un valore. Nella formula sopra, la funzione MATCH annidata trova il numero di riga (posizione) del valore "Anne" (H2). Quindi forniamo quel numero di riga alla funzione INDICE con un intervallo B2: F20 e un numero di colonna (3), che specifichiamo. E la funzione INDICE restituisce il punteggio '91'.
Ricerca bidirezionale con INDEX e MATCH
È inoltre possibile utilizzare le funzioni INDICE e CONFRONTA per cercare un valore in un intervallo bidimensionale (ricerca bidirezionale). Nell'esempio sopra, abbiamo utilizzato la funzione CONFRONTA per individuare il numero di riga di un valore, ma abbiamo inserito manualmente il numero di colonna. Ma possiamo trovare sia riga che colonna nidificando due funzioni CONFRONTA, una nell'argomento num_riga e un'altra nell'argomento num_colonna della funzione INDICE.
Usa questa formula per una ricerca bidirezionale con INDICE e CONFRONTA:
=INDICE(A1:F20,CONFRONTA(H2,A2:A20,0),CONFRONTA(H3,A1:F1,0))Come sappiamo, la funzione CONFRONTA può cercare un valore sia orizzontalmente che verticalmente. In questa formula, la seconda funzione CONFRONTA nell'argomento num_colonna trova la posizione di Quiz2 (4) e la fornisce alla funzione INDICE. E l'INDICE recupera il punteggio.
Ora sai come usare la funzione Abbina in Excel.