Come confrontare due colonne in Excel utilizzando CERCA.VERT

Quando si lavora con set di dati di grandi dimensioni in Excel, potrebbe essere necessario confrontare due colonne per trovare somiglianze o differenze.

VLOOKUP è una potente funzione che ti consente di cercare dati corrispondenti tra due colonne. La funzione consente di cercare i valori in una colonna che compaiono in un'altra.

Questo articolo mostra come confrontare due colonne utilizzando VLOOKUP, in modo da poter analizzare in modo efficiente i dati.

Sommario

• Fondamenti della funzione Vlookup
  • Funzione Vlookup
• Preparazione dei dati per CERCA.VERT
  • Formattazione dei dati
• Come utilizzare CERCA.VERT per confrontare due colonne
  • Gestione di altri errori
• Alternative a CERCA.VERT per il confronto delle colonne
  • 1. Utilizzo delle funzioni di indice e corrispondenza
  • 2. Utilizzo di CERCA.X in Excel
• Cinque suggerimenti per migliorare le prestazioni di VLOOKUP
  • 1. Limita il tuo intervallo di ricerca
  • 2. Usa riferimenti assoluti
  • 3. Ordina i tuoi dati
  • 4. Usa una doppia VLOOKUP
  • 5. Usa la formattazione condizionale
• Usi avanzati per VLOOKUP
• La nostra ultima parola

Fondamenti della funzione Vlookup

Supponiamo di avere un foglio di calcolo con due elenchi di elementi nella colonna A e nella colonna B. Si desidera trovare gli elementi nell'elenco 1 che compaiono anche nell'elenco 2.

Puoi immaginare che lavorare manualmente attraverso elenchi di migliaia di articoli richiederebbe un enorme dispendio di tempo. Per fortuna, Excel porta VLOOKUP in soccorso!

Il termine CERCA.VERT sta per Ricerca verticale. La funzione confronterà due colonne, troverà le corrispondenze tra di esse e restituirà i valori associati.

Funzione Vlookup

Ecco una sintassi di base della funzione CERCA.VERT:

=CERCA.VERT(valore_ricerca, matrice_tabella, numero_indice_col, [ricerca_intervallo])

Questa è una ripartizione degli elementi:

• lookup_value : il valore che vuoi cercare nella prima colonna di table_array.

• table_array : l'intervallo di celle contenente i dati in cui vuoi cercare.

• col_index_num : il numero di colonna nell'array_tabella da cui si desidera restituire il valore.

• range_lookup : facoltativo. È VERO (corrispondenza approssimativa) o FALSO (corrispondenza esatta). Il valore predefinito è TRUE.

Preparazione dei dati per CERCA.VERT

Prima di utilizzare CERCA.VERT per confrontare due colonne in Excel , è necessario preparare i dati.

Crea due colonne separate nel foglio di lavoro in cui desideri confrontare i valori. Questo articolo utilizza la colonna A e la colonna B per i nostri esempi.

Ecco i dati di esempio utilizzati in questo articolo:

Questa è la tabella di esempio con l'intestazione da aggiungere alla sezione intitolata "Preparazione dei dati per CERCA.VERT":

Lista 1 Lista 2
Voce 1
Voce 3
Voce 2 Voce 4 Voce 3 Voce 1
Voce 4 Voce 5

Formattazione dei dati

È importante assicurarsi che i dati in entrambe le colonne siano formattati in modo simile. CERCA.VERT distingue tra maiuscole e minuscole, il che significa che i caratteri maiuscoli e minuscoli contano.

Nel risultato finale possono verificarsi errori di corrispondenza se la formattazione non è coerente.

È anche una buona idea rimuovere eventuali valori duplicati o celle vuote per ridurre al minimo il rischio di errori.

Per rimuovere i valori duplicati:

1. Seleziona la colonna.

2. Vai alla scheda Dati nella barra multifunzione in alto.

3. Fare clic su Rimuovi duplicati nella sezione Strumenti dati.

Il pulsante può essere un po' difficile da vedere. Questa immagine ti aiuterà:

Per rimuovere le celle vuote:

1. Seleziona la colonna.

2. Vai alla scheda Home nella barra multifunzione in alto.

3. Espandi il menu "Trova e seleziona".

4. Scegli "Vai a Speciale".

5. Seleziona "Vuoti" dalle opzioni.

6. Fare clic su "Elimina" nella sezione Celle.

Come utilizzare CERCA.VERT per confrontare due colonne

Una volta preparati i dati, puoi scrivere la formula CERCA.VERT per confrontare due colonne in Excel e identificare le corrispondenze. Segui questi passi:

1. Selezionare una cella in una nuova colonna in cui si desidera visualizzare i risultati del confronto (ad esempio, cella C2).

2. Digita la seguente formula: =CERCA.VERT(A2, B:B, 1, FALSO))

3. Premere Invio per applicare la formula.

4. In caso di un valore corrispondente, il valore verrà visualizzato nella stessa riga della colonna del risultato (ad esempio, Colonna C).

5. Trascina la formula verso il basso da C2 per copiarla su tutte le celle di cui hai bisogno.

Se utilizzi i nostri dati di esempio, inserisci i risultati del confronto nella terza colonna. La formula troverà tre corrispondenze tra i quattro elementi nell'elenco 1.

Si noti che l'elemento 2 viene visualizzato come #N/D. Excel ci dice che la corrispondenza non è applicabile, ovvero non è stata trovata.

Ciò è corretto ed è un'informazione utile sui valori mancanti. Tuttavia, alcuni utenti di Excel potrebbero pensare che ci sia qualche problema di dati o errore di funzione.

È buona norma mostrare un'indicazione diversa che non è stata trovata alcuna corrispondenza. Potrebbe essere semplicemente uno spazio vuoto.

Per fare ciò, combina la funzione CERCA.VERT con la funzione IFNA in questo modo:

=SENA(CERCA.VERT(A1; B:B; 1; FALSO); “”)

La funzione IFNA rileva l'errore #N/A e sostituisce l'output con uno spazio vuoto (""). È inoltre possibile utilizzare la funzione ISNA o avere una logica aggiuntiva con la funzione IF.

Gestione di altri errori

VLOOKUP a volte può generare altri errori nei risultati del confronto. Il #REF! L'errore in Excel è un vero problema con i tuoi dati.

Di solito si verifica quando l'intervallo specificato non è corretto. Il nostro esempio ha fatto riferimento all'intera colonna, ma puoi anche utilizzare intervalli di celle verticali.

Assicurati che l'intervallo di ricerca a cui ti riferisci copra tutti i valori che desideri confrontare.

Alternative a CERCA.VERT per il confronto delle colonne

Esistono due principali funzioni di ricerca alternative quando si confrontano due colonne in Excel per trovare le corrispondenze.

1. Utilizzo delle funzioni di indice e corrispondenza

Invece di utilizzare CERCA.VERT, puoi confrontare due colonne in Excel combinando le funzioni INDICE e CONFRONTA.

Questo metodo fornisce un modo più flessibile per cercare i dati ed è particolarmente utile quando si lavora con colonne non adiacenti o quando l'indice di colonna può cambiare.

Questa è la sintassi da inserire nella colonna dei risultati:

=INDICE(intervallo_ritorno, CONFRONTA(valore_ricerca, intervallo_ricerca, 0))

• Return_range : l'intervallo di celle che contengono i dati che si desidera restituire.

• Lookup_value : il valore che desideri cercare all'interno dell'intervallo di ricerca.

• Lookup_range : l'intervallo di celle all'interno del quale si desidera trovare il valore di ricerca.

Utilizzando gli stessi dati negli esempi precedenti, sostituiamo la formula CERCA.VERT come segue:

=(INDICE(B:B; CONFRONTA(A2; B:B; 0)))

La funzione CONFRONTA restituisce la posizione relativa del valore di ricerca all'interno dell'intervallo di ricerca e la funzione INDICE restituisce il valore corrispondente dall'intervallo restituito.

I risultati sugli stessi dati saranno gli stessi del VLOOKUP che abbiamo usato in precedenza. Questa immagine mostra le funzioni in uso:

Puoi anche sostituire l'errore #N/D con un messaggio personalizzato. Ecco un esempio di utilizzo della funzione IFERROR.

=SE.ERRORE(INDICE(B:B, CONFRONTA(A2, B:B, 0)), “Non trovato”)

2. Utilizzo di CERCA.X in Excel

Per gli utenti con Excel 365 o Excel 2019, CERCA.X è un test logico alternativo a CERCA.VERT per trovare valori comuni in due colonne.

CERCA.X offre diversi vantaggi. Puoi usarlo per cercare dati sia in orizzontale che in verticale, lavorare con colonne non adiacenti e specificare valori personalizzati per gli errori.

La sintassi di CERCA.X è:

=CERCA.X(valore_ricerca, intervallo_ricerca, intervallo_ritorno, [se_non_trovato], [modalità_corrispondenza], [modalità_ricerca])

• lookup_value : il valore che vuoi cercare all'interno dell'intervallo di ricerca.

• lookup_range : l'intervallo di celle all'interno del quale si desidera trovare il valore di ricerca.

• return_range : l'intervallo di celle che contengono i dati che si desidera restituire.

• Aggiungi valori di errore personalizzati, modalità di corrispondenza e parametri della modalità di ricerca secondo necessità.

CERCA.X troverà il valore di ricerca all'interno dell'intervallo di ricerca e restituirà il valore corrispondente dall'intervallo restituito.

La formula è particolarmente utile quando hai due elenchi incorporati in set di dati di più colonne. Nei nostri esempi precedenti, gli elenchi erano nella prima e nella seconda colonna, ma il tuo foglio potrebbe contenere più dati di quelli.

Ecco un esempio con due elenchi nella prima e nella terza colonna di un foglio di calcolo:

La formula CERCA.X ha questo aspetto:

=CERCA.X(A2;C:C;C:C,””)

Questa immagine mostra il risultato con il confronto nella colonna D. Il primo valore è presente ma manca il secondo.

Si noti che non ho formule di errore aggiuntive, ma il valore mancante viene visualizzato come vuoto. Questo perché sto usando il quarto parametro che è un valore di errore personalizzato. In questo caso, è una stringa vuota.

Come bonus, ti mostrerò come utilizzare XLOOKUP per confrontare le righe. Se hai due elenchi che si trovano nella riga 1 e nella riga 2, l'uso più semplice della formula è il seguente:

=CERCAX(B1; 2:2; 2:2; “”)

Questa immagine mostra i risultati sulle due righe.

Con la sua funzionalità e flessibilità migliorate, CERCA.X è un'ottima alternativa a CERCA.VERT per confrontare elenchi in Excel per determinare se sono presenti corrispondenze.

Cinque suggerimenti per migliorare le prestazioni di VLOOKUP

L'ottimizzazione della funzione CERCA.VERT può aiutarti a evitare lunghi tempi di attesa e migliorare la reattività generale del tuo foglio di lavoro Excel.

Ecco sei suggerimenti e trucchi che puoi applicare per migliorare le prestazioni della tua CERCA.VERT in Excel.

1. Limita il tuo intervallo di ricerca

Ho usato intere colonne negli esempi per renderli semplici. Se stai lavorando con una grande quantità di dati, dovresti evitare di farlo. L'utilizzo di colonne complete può rallentare il processo di calcolo di Excel.

Invece, prova a utilizzare l'intervallo esatto richiesto per i tuoi dati (ad esempio, A1: A100). Ciò riduce il numero di celle che la funzione CERCA.VERT deve valutare.

2. Usa riferimenti assoluti

Quando si specifica un intervallo di dati (ad esempio le celle da B2 a B5), utilizzare riferimenti assoluti. Ciò garantisce che la formula sia coerente e che i dati esistano quando li copi su più celle.

Ecco un esempio:

=CERCA.VERT(A2; $B$2:$B$5; 1; FALSO)

3. Ordina i tuoi dati

Se sai che i dati nella colonna di ricerca sono ordinati in ordine crescente, puoi utilizzare VLOOKUP con TRUE o 1 per l'argomento "range_lookup".

Ciò farà sì che Excel esegua una corrispondenza approssimativa, che è più veloce di una corrispondenza esatta in tutte le celle. Tuttavia, prestare attenzione quando si utilizza questa opzione, poiché un ordinamento errato potrebbe portare a risultati errati.

4. Usa una doppia VLOOKUP

È possibile utilizzare due funzioni CERCA.VERT per velocizzare il processo di ricerca in Excel.

Il primo VLOOKUP determinerà se il valore di ricerca esiste impostando 'col_index_num' su 1 e 'range_lookup' su TRUE.

Se restituisce TRUE, un secondo VLOOKUP recupera il valore desiderato con 'range_lookup' impostato su TRUE.

5. Usa la formattazione condizionale

È possibile utilizzare la formattazione condizionale nel foglio di lavoro di Excel per evidenziare i valori corrispondenti o mancanti nella colonna specificata. Puoi anche applicare i colori a valori univoci. Questo rende i tuoi dati più facili da leggere.

Troverai il menu di formattazione condizionale nel gruppo degli stili nella scheda Home.

Usi avanzati per VLOOKUP

I test T per campioni accoppiati vengono utilizzati per confrontare le medie di due campioni correlati. Questo video mostra il loro utilizzo in Excel.

Se disponi di dati o variabili aggiuntivi a cui devi fare riferimento durante l'analisi, puoi utilizzare CERCA.VERT per recuperare i valori necessari da un'altra tabella o foglio di lavoro.

Ad esempio, puoi utilizzare CERCA.VERT per recuperare informazioni demografiche o condizioni di trattamento per ciascuna osservazione accoppiata nel tuo set di dati.

La nostra ultima parola

Ormai hai una solida conoscenza dell'uso di CERCA.VERT. Questa potente funzione consente di identificare rapidamente le differenze e i valori corrispondenti tra due elenchi, rendendo l'analisi dei dati più efficiente e accurata.

Hai visto anche esempi di altre funzioni di ricerca e riferimento, come CERCA.X, INDICE e CONFRONTA. L'aggiunta di questi elementi alle tue competenze rafforzerà ulteriormente le tue capacità di analisi dei dati.

Continua a esercitarti e ad affinare le tue capacità di CERCA.VERT e presto diventerai un esperto nel confrontare le colonne in Excel, risparmiando tempo e aumentando la tua produttività.