Come utilizzare la funzione CERCA.VERT per confrontare due elenchi in fogli di lavoro separati?

Confronta due elenchi in fogli separati con formule

Un approccio pratico e diretto per confrontare elenchi situati in diversi fogli di lavoro di Excel è utilizzare la funzione CERCA.VERT. Questo metodo ti aiuta ad estrarre o segnalare in modo efficiente tutti i nomi trovati sia in Nomi-1 che in Nomi-2:

1. Nel foglio Nomi-1, scegli una cella adiacente ai tuoi dati (ad esempio, cella B2) e inserisci la seguente formula:

=VLOOKUP(A2,'Names-2'!$A$2:$A$19,1,FALSE)

Premi quindi Invio. Se il nome nella riga corrente esiste in Nomi-2, la formula restituisce il nome; in caso contrario, verrà visualizzato un errore #N/D. Vedi l'esempio qui sotto:

2. Copia la formula trascinando il quadratino di riempimento per confrontare ogni nome in Nomi-1 con tutti i nomi in Nomi-2. Le voci corrispondenti mostreranno il nome, mentre quelle non trovate mostreranno un valore di errore:

Note:

1. Per maggiore chiarezza, potresti usare questa formula alternativa per restituire indicatori "Sì" o "No" per le corrispondenze:

=IF(ISNA(VLOOKUP(A2,'Names-2'!$A$2:$A$19,1,FALSE)), "No", "Yes")

Questa formula mostra "Sì" per i nomi presenti in entrambi i fogli e "No" per i nomi presenti solo in Nomi-1:

2. Quando utilizzi queste formule, sostituisci A2 con la prima cella del tuo elenco, Nomi-2 con il nome del foglio di riferimento e regola $A$2:$A$19 per adattarlo all'intervallo effettivo dei dati nel tuo foglio di lavoro. Ricorda, gli intervalli devono iniziare e terminare con i numeri di riga corretti per assicurarti che tutti i tuoi dati siano inclusi.

3. Suggerimenti per l'uso: se incontri errori #N/D dove ci dovrebbero essere corrispondenze, controlla attentamente eventuali problemi causati da spazi aggiuntivi, differenze di formattazione dei dati (testo vs numero) o errori di battitura nei tuoi elenchi. Usa TRIM o CLEAN in una colonna di supporto per pulire i dati se necessario.

4. Per evitare sovrascritture accidentali, considera di fare un backup dei tuoi dati prima di applicare formule in blocco. Inoltre, dopo il confronto, puoi utilizzare Filtro sulla colonna dei risultati della formula per visualizzare rapidamente tutte le corrispondenze o elementi unici.

Confronta due elenchi in fogli separati con Kutools per Excel

Se disponi di Kutools per Excel, la sua funzionalità Seleziona Stessi & Diversi Valori delle Celle può aiutarti a confrontare rapidamente due elenchi provenienti da diversi fogli di lavoro e selezionare o evidenziare i nomi comuni tra questi due fogli—tutto ciò senza inserire formule complesse. Questo metodo è particolarmente efficace quando si lavora con grandi volumi di dati o si vuole un risultato visuale, codificato a colori, facile da interpretare a colpo d'occhio.

Dopo aver installato Kutools per Excel, segui questi passaggi per confrontare facilmente i tuoi elenchi:

1. Vai alla scheda Kutools, poi clicca Seleziona > Seleziona Stessi & Diversi Valori delle Celle come mostrato di seguito:

2. Nella finestra di dialogo Seleziona Stessi & Diversi Valori delle Celle aperta:

(1.) Sotto Trova valori in, seleziona l’intervallo da Nomi-1 che devi confrontare;

(2.) Sotto Secondo, seleziona l’intervallo da Nomi-2 con cui confrontare;

(3.) Nella sezione Basato su, scegli Per riga per confrontare le righe rispettivamente;

(4.) Dalla sezione Trova, seleziona Valori uguali per identificare ed evidenziare i nomi corrispondenti;

(5.) Opzionalmente, puoi impostare un colore di sfondo o carattere per evidenziare i risultati e far risaltare visivamente le corrispondenze.

3. Clicca Ok, e vedrai una casella di avviso che mostra quante celle corrispondenti sono state trovate ed evidenziate. Tutti i nomi presenti in entrambi gli elenchi saranno selezionati ed enfatizzati visivamente, semplificando ulteriori revisioni o modifiche:

Clicca per Scaricare e provare gratuitamente Kutools per Excel Ora !

Suggerimenti pratici: Se i tuoi fogli di lavoro contengono grandi set di dati, considera di utilizzare la funzione filtro dopo l'evidenziazione per rivedere rapidamente solo le corrispondenze. Inoltre, prima di eseguire il confronto, verifica attentamente che le selezioni degli intervalli siano allineate correttamente e non includano righe di intestazione a meno che non sia intenzionale, poiché discrepanze possono influenzare i risultati.

In casi rari, se la funzione non restituisce i risultati attesi, verifica se entrambi gli elenchi sono formattati allo stesso modo (ad esempio, entrambi come testo, senza spazi nascosti iniziali/finali), poiché discrepanze di formattazione potrebbero causare mancate corrispondenze.

Formattazione condizionale con formula tra fogli

Se preferisci non scrivere formule nelle colonne o utilizzare componenti aggiuntivi, puoi utilizzare la Formattazione condizionale con una formula personalizzata per identificare visivamente i nomi corrispondenti in un foglio basandoti sui dati di un altro foglio. Questo metodo è semplice e non richiede VBA, ma non restituisce un elenco separato di risultati—piuttosto, formatta semplicemente le corrispondenze per una rapida revisione a colpo d'occhio.

Scenari applicabili: Questa soluzione è ideale per gli utenti che desiderano un indicatore visivo non intrusivo dei valori corrispondenti e non vogliono alterare la struttura del foglio di lavoro. La limitazione è che le regole di Formattazione condizionale non possono fare riferimento direttamente a un altro file di lavoro, e il riferimento incrociato tra fogli funziona solo all'interno dello stesso file.

Passaggi:

1. In Nomi-1, seleziona l’intervallo a cui desideri applicare l’evidenziazione (ad esempio, A2:A19).

2. Vai su Home > Formattazione condizionale > Nuova regola > Utilizza una formula per determinare quali celle formattare.

3. Nella casella della formula, inserisci la seguente formula:

=COUNTIF('Names-2'!$A$2:$A$19,A2)>0

Questo verifica se il valore in A2 di Nomi-1 esiste da qualche parte in Nomi-2!A2:A19.

4. Clicca Formato per scegliere un colore di evidenziazione, quindi clicca OK per applicare la regola. Qualsiasi corrispondenza verrà evidenziata automaticamente nell'intervallo selezionato.

Suggerimenti pratici: Puoi adattare gli intervalli in base ai tuoi dati effettivi, e il passaggio CONTA.SE può essere combinato con il filtro per concentrarsi solo sulle celle evidenziate. Assicurati che entrambi i fogli di lavoro siano all'interno dello stesso file di lavoro quando configuri i riferimenti tra fogli, poiché Excel non supporta regole di formattazione condizionale che fanno riferimento a file esterni.

Promemoria di errore: Se le evidenziazioni non appaiono come previsto, controlla le tue selezioni di intervalli di celle e i riferimenti tra fogli per eventuali errori. Assicurati che non ci siano spazi iniziali/finali o inconsistenze di formato che causano mancate corrispondenze. Se necessario, usa TRIM in una colonna di supporto per pulire gli elenchi per un confronto accurato.

Codice VBA - Confronta automaticamente gli elenchi ed evidenzia o estrai le corrispondenze

Per gli utenti a proprio agio con le macro, utilizzare il codice VBA offre un modo altamente flessibile e automatizzato per confrontare due elenchi in fogli di lavoro separati. Questo approccio ti consente di evidenziare i nomi corrispondenti o di estrarre i valori corrispondenti in una nuova posizione, il che può essere particolarmente utile quando si gestiscono grandi volumi di dati o si necessita di aggiornamenti rapidi man mano che cambiano gli elenchi.

Scenari applicabili: Questa soluzione è particolarmente efficace quando si desidera eseguire ripetutamente confronti, gestire set di dati molto grandi, automatizzare la creazione di report o personalizzare ulteriormente come vengono elaborati o presentati i risultati corrispondenti. Anche se è necessaria la conoscenza di VBA, si ottiene il vantaggio di una completa automazione e controllo. Uno svantaggio è che le macro devono essere abilitate nel file di lavoro, il che potrebbe non essere permesso in tutti gli ambienti a causa delle impostazioni di sicurezza.

Come eseguire la macro per evidenziare le corrispondenze in Nomi-1 se presenti in Nomi-2:

1. Clicca Strumenti di sviluppo > Visual Basic per lanciare la finestra Microsoft Visual Basic per Applicazioni. Nella finestra, clicca Inserisci > Modulo e incolla il seguente codice nel nuovo modulo:

Sub HighlightMatchingNames()
    Dim ws1 As Worksheet
    Dim ws2 As Worksheet
    Dim rng1 As Range
    Dim cell As Range
    Dim matchFound As Range
    
    On Error Resume Next
    xTitleId = "KutoolsforExcel"
    
    Set ws1 = Worksheets("Names-1")
    Set ws2 = Worksheets("Names-2")
    
    Set rng1 = ws1.Range("A2", ws1.Cells(ws1.Rows.Count, "A").End(xlUp))
    
    ws1.Range("A2:A" & ws1.Cells(ws1.Rows.Count, "A").End(xlUp).Row).Interior.ColorIndex = xlNone
    
    For Each cell In rng1
        Set matchFound = ws2.Range("A2:A" & ws2.Cells(ws2.Rows.Count, "A").End(xlUp).Row).Find( _
            What:=cell.Value, LookIn:=xlValues, LookAt:=xlWhole)
        
        If Not matchFound Is Nothing And cell.Value <> "" Then
            cell.Interior.Color = vbYellow
        End If
    Next cell
End Sub

2. Nell'editor VBA, clicca il pulsante per eseguire il codice. Questa macro scansionerà i nomi nella colonna A del foglio di lavoro "Nomi-1", e se un nome appare anche nella colonna A del foglio di lavoro "Nomi-2", evidenzierà quella cella in "Nomi-1" con un colore di riempimento giallo. Eventuali evidenziazioni precedenti nell'intervallo verranno cancellate prima del nuovo confronto.

Risoluzione dei problemi: Se nessuna cella viene evidenziata, verifica che entrambi i fogli di lavoro siano denominati esattamente "Nomi-1" e "Nomi-2", e che i tuoi intervalli di dati inizino da A2. Assicurati che le macro siano abilitate, e che nessuno dei fogli di lavoro sia protetto o filtrato. Questo approccio può essere facilmente personalizzato; ad esempio, puoi cambiare il colore di evidenziazione, o adattare il codice per copiare i risultati corrispondenti in un altro foglio o colonna.

Riepilogo e suggerimenti: A seconda delle tue esigenze e del tuo livello di comfort tecnico, puoi scegliere tra soluzioni di formule integrate, automazione tramite macro, componenti aggiuntivi intelligenti come Kutools, o una semplice visualizzazione con Formattazione condizionale. Quando utilizzi formule o VBA, rivedi sempre i tuoi dati per spazi extra o formattazione inconsistente, che sono fonti comuni di errori. Fai un backup dei tuoi dati prima di apportare modifiche in blocco, soprattutto quando usi macro o componenti aggiuntivi per la prima volta. Se incontri problemi come formule che non si aggiornano o corrispondenze errate, controlla eventuali errori di intervallo relativo/assoluto e verifica i nomi dei fogli di lavoro. Scegliendo il metodo che meglio si adatta al tuo flusso di lavoro, puoi confrontare in modo efficace ed efficiente gli elenchi tra fogli diversi in Excel.