Hay Paula, puoi confrontare gli elenchi in Excel?
o
Qual è il modo migliore per confrontare due set di dati in Excel?
Molto spesso c’è la necessità in Excel di confrontare due elenchi, o due set di dati per trovare elementi mancanti o corrispondenti. Trattandosi di Excel, ci sono sempre più modi per fare le cose, compreso il confronto dei dati. Da formule e formattazione condizionale a Power Query. In questo articolo vedremo un certo numero di modi per confrontare due elenchi in Excel e vedremo anche come confrontare intere righe di un set di dati.
Forse il tuo modo preferito non è incluso qui sotto. In caso contrario, perché non lasciate un commento qui sotto e condividete con noi come vi piace confrontare due elenchi o set di dati in Excel. Non vedo l’ora di leggere i vostri commenti.
Vogliamo confrontare la lista 1 con la lista 2.
Contenuti
Formattazione condizionale veloce per confrontare due colonne di dati
Cancellazione della formattazione condizionale
Confronto di dati in Excel usando la funzione MATCH
Confronto di 2 liste in Excel 365 con MATCH o XMATCH come funzione Dynamic Array
MATCH e Dynamic arrays per confrontare 2 elenchi
XMATCH Excel 365 per confrontare due elenchi
Tabelle – Confrontare elenchi in Excel dove le dimensioni degli intervalli potrebbero cambiare
Evidenzia le differenze negli elenchi usando la Formattazione condizionale personalizzata
Copia formula alla Formattazione condizionale personalizzata
Altre formule usate per confrontare due elenchi in Excel
VLOOKUP per confrontare due elenchi in Excel
XLOOKUP per confrontare due elenchi in Excel
COUNTIF per confrontare due elenchi in Excel
Come confrontare 2 serie di dati in Excel
Confrontare liste o insiemi di dati usando Power Query
Formattazione condizionale rapida per confrontare due colonne di dati
La formattazione condizionale ti permetterà di evidenziare una cella o un intervallo basato su criteri predefiniti. Il modo più semplice e veloce per confrontare visivamente queste due colonne velocemente è quello di utilizzare la regola predefinita per evidenziare i valori duplicati.
Inizia selezionando le due colonne di dati.
Dalla scheda Home, seleziona il menu a tendina Formattazione condizionale. Poi seleziona Regole di evidenziazione delle celle. Poi seleziona Valori duplicati.
Si aprirà una casella di impostazioni Valori duplicati dove potrai definire la formattazione e selezionare tra Valori duplicati o unici.
Selezionando Duplicato, tutte le voci ricorrenti saranno impostate sulla formattazione selezionata. Ora puoi vedere rapidamente le voci della lista 1 che sono nella lista 2, poiché queste sono le voci formattate. Puoi anche vedere rapidamente le voci nella lista 2 che non sono nella lista 1, dato che queste non hanno la formattazione applicata.
Tuttavia, puoi anche formattare le voci uniche. Questo può essere ottenuto selezionando Unique dalla casella di impostazione Duplicate Values.
In questo caso, abbiamo applicato due diversi formati condizionali. Il rosso indica i duplicati e il verde indica gli elementi unici.
Nota, non ho preso solo le celle con dati, ho preso tutte le colonne da A a C. La colonna B non ha dati, quindi non può influenzare i risultati. Tuttavia, queste celle contengono la formattazione condizionale applicata, quindi sarebbe una pratica migliore selezionare solo le celle di cui hai bisogno.
Cancellare la formattazione condizionale
Per cancellare tutta la formattazione condizionale, prima seleziona la cella o l’intervallo. Poi seleziona il menu a tendina della formattazione condizionale sulla barra multifunzione Home. Poi seleziona Cancella regole. Infine seleziona Cancella regole dalle celle selezionate.
Se hai più di una formattazione condizionale applicata contemporaneamente e vuoi rimuovere solo una di queste, seleziona Gestisci regole dal menu a tendina della formattazione condizionale. Seleziona la regola che vuoi eliminare e poi seleziona Elimina regola.
Premendo OK, la regola verrà rimossa dal Gestore regole e le celle non conterranno più la formattazione.
Quindi questo è il modo più semplice per confrontare due liste in Excel. È veloce, semplice ed efficace. Puoi anche applicare una formattazione condizionale basata su formule, che vedremo più avanti in questo articolo.
Abbinare i dati in Excel usando la funzione MATCH
Ci sono molte formule di ricerca che puoi usare per confrontare due intervalli o liste in Excel. La prima che vedremo è la funzione MATCH.
La funzione MATCH restituisce la posizione relativa in un elenco. Un numero basato sulla sua posizione, se trovata, nella matrice di ricerca.
La sintassi di MATCH è
=MATCH (lookup value, Lookup array, Match type)
Dove lookup value è il valore per cui volete trovare una corrispondenza. Lookup array è la lista in cui state cercando una corrispondenza. E il tipo di corrispondenza ti permette di scegliere tra una corrispondenza esatta o approssimativa.
Vogliamo scrivere una formula di corrispondenza per vedere se gli elementi nella Lista 2 sono nella Lista 1.
Nella cella E3 possiamo inserire la formula
=MATCH(C2, $A$2:$A$21,0)
Compilando questa formula, per i valori dove Excel trova una corrispondenza, verrà restituita la posizione di quella corrispondenza. Dove non c’è corrispondenza, il valore di ritorno sarà un #N/A.
Molto spesso, la posizione relativa o il #N/A non hanno valore per noi e abbiamo bisogno di convertire questi valori in vero o falso. Per fare questo possiamo facilmente espandere la nostra formula Match usando una funzione logica. Poiché Match restituisce un numero, possiamo usare la funzione ISNUMBER
=ISNUMBER(MATCH(C2, $A$2:$A$21,0))
Confrontare 2 liste in Excel 365 con MATCH o XMATCH come funzione Dynamic Array
Se stai usando Excel 365 hai ulteriori alternative quando usi MATCH per confrontare liste o dati. Poiché Excel 365 pensa in matrici, ora possiamo passare una matrice come valore di ricerca di MATCH e i nostri risultati si riverseranno per noi. Questo elimina la necessità di copiare la formula e con solo 1 formula, il tuo foglio di calcolo sarà meno soggetto a errori e più compatto.
MATCH e gli array dinamici per confrontare 2 liste
Se non hai ancora familiarità con gli array dinamici, ti suggerisco di leggere questo articolo: Excel Dynamic Arrays – Un nuovo modo di modellare i vostri fogli di calcolo Excel: per capire meglio come funzionano e gli intervalli di versamento.
L’unica modifica alla formula di abbinamento è che invece di selezionare la cella C2 come valore di ricerca, selezioneremo l’intervallo C2:12
=ISNUMBER(MATCH (C2:C12,$A$2:$A$21,0))
Come potete vedere, la formula unica rovescia i risultati nella colonna E.
XMATCH Excel 365 per confrontare due liste
Excel 365 introduce anche la nuova funzione XMATCH. Proprio come la funzione MATCH, XMATCH restituisce una posizione relativa in un elenco. Ora che avete familiarità con XLOOKUP, che sostituisce la vecchia funzione VLOOKUP, sapete che XLOOKUP viene con un potere aggiuntivo. Questo si presenta sotto forma di nuove condizioni nella sintassi della formula, come la modalità di ricerca e i tipi di corrispondenza. Bene, anche XMATCH ha questo potere in più rispetto al suo predecessore MATCH.
La sintassi per XMATCH è
XMATCH (Lookup Value, Lookup Array, ,)
dove
Lookup Value è il valore che state cercando per trovare la posizione relativa
Lookup Array è la riga o colonna che contiene il Lookup Value
La modalità di corrispondenza è opzionale. A differenza della vecchia funzione MATCH, l’impostazione predefinita è una corrispondenza esatta. Puoi anche scegliere tra
- Corrispondenza esatta o successiva più piccola
- Corrispondenza esatta o successiva più grande
- Corrispondenza jolly
Anche la modalità di ricerca è opzionale. L’impostazione predefinita (e unica opzione nella vecchia funzione MATCH) è di cercare dall’alto verso il basso. Puoi anche selezionare ricerche da ultimo a primo e binarie. Se state lavorando con ricerche binarie. L’opzione di ricerca con caratteri jolly non funziona.
Con XMATCH possiamo usare sia gli array dinamici che i riferimenti di cella per creare la formula, proprio come abbiamo visto con MATCH. Per questo esempio, useremo gli array dinamici. La formula è molto simile a quella che abbiamo usato con MATCH; tranne che non dobbiamo selezionare 0 per una corrispondenza esatta, perché in XMATCH questa è l’impostazione predefinita. Mescoliamo un po’ le cose questa volta per trovare elementi nella lista 2 e non nella lista uno.
In questo caso possiamo usare la formula
=NOT(ISNUMBER(XMATCH(C2:C12,A2:A21)))
dove not trasformerà i veri in falsi e i falsi in veri.
Tabelle – Confrontare elenchi in Excel dove le dimensioni degli intervalli possono cambiare
In ciascuna delle formule che abbiamo visto finora, abbiamo selezionato un intervallo di celle nelle nostre funzioni Match che non è dinamico. Questo significa che se aggiungiamo nuovi dati a uno degli elenchi, abbiamo un passo manuale per aggiornare la nostra formula per includere i nuovi dati.
Per convertire gli elenchi in tabelle, selezionate uno degli elenchi e premete CTRL. Questa è la scorciatoia da tastiera per convertire in una tabella. Se hai selezionato l’intestazione nell’intervallo di celle, assicurati di spuntare la casella per confermare che la tua tabella ha l’intestazione.
Le tabelle per loro natura usano una denominazione strutturata. Pertanto, quando si scrive una formula e si seleziona una colonna da una tabella, non verranno mostrati i riferimenti alle celle, ma il nome della colonna.
Guardando la nostra formula precedente che usa XMATCH per trovare gli elementi nella lista 2 e non nella lista 1, possiamo riscrivere questa funzione ora usando i nostri riferimenti alla tabella
=NOT (ISNUMBER(XMATCH(list2,list1)))
Ora, siccome abbiamo usato delle tabelle, se aggiungiamo una nuova riga a una delle tabelle, anche il nostro intervallo di versamento aumenterà per includere i nuovi dati.
Evidenzia le differenze negli elenchi usando la formattazione condizionale personalizzata
Prima in questo articolo abbiamo visto un modo molto veloce di confrontare questi due elenchi usando una regola predefinita per i duplicati. Possiamo però usare anche la Formattazione condizionale personalizzata. Se non avete familiarità con la formattazione condizionale personalizzata, vi suggerisco di dare un’occhiata a questo articolo: Trucchi di formattazione condizionale dinamica di Excel:
Abbiamo bisogno di guardare due approcci diversi qui per evidenziare le differenze. Quando non stiamo usando tabelle e abbiamo creato una formula vero/falso per identificare le differenze, possiamo prendere una copia della formula e aggiungerla alla nostra formattazione personalizzata. Tuttavia, con le tabelle abbiamo bisogno di forzare l’uso dei riferimenti alle celle.
Copiare la formula nella Formattazione condizionale personalizzata
Iniziare prendendo una copia della formula. Avendo testato la formula nel foglio di calcolo, possiamo vedere che funziona prima di usarla nella formattazione condizionale. Questa è la migliore pratica perché molto spesso con i riferimenti di cella relativi e assoluti può essere difficile ottenere la formula corretta.
Selezionate le celle a cui volete applicare la formattazione personalizzata. Poi dalla barra multifunzione Home seleziona la formattazione condizionale e seleziona Nuova regola
Si aprirà la finestra di impostazione della nuova regola di formattazione e seleziona Usa formula per determinare le celle da formattare. Poi incolla la formula e imposta il tipo di formattazione
Questo avrà come risultato che tutte le celle che sono in entrambe le liste saranno formattate nel formato scelto.
Tenete a mente che abbiamo selezionato un intervallo di celle per applicare questa formattazione condizionale e non è dinamica. Se usassimo delle tabelle, questo si aggiornerebbe senza bisogno di cambiare nulla!
Altre formule usate per confrontare due elenchi in Excel
Ci sono molte formule che puoi usare per confrontare due elenchi in Excel. Abbiamo già visto MATCH e XMATCH, ma ora ne vedremo altre. Qualsiasi funzione di ricerca funzionerà davvero insieme ad altre!
VLOOKUP per confrontare due liste in Excel
Se non hai familiarità con VLOOKUP, puoi leggere qui. In poche parole VLOOKUP restituirà un valore corrispondente da una cella, se non c’è un valore corrispondente verrà restituito un errore #N/A. Nel nostro esempio stiamo lavorando con il testo. Quindi, possiamo eseguire un VLOOKUP e testare per vedere se restituisce del testo. Se stessimo usando dei numeri, potremmo sostituire ISTEXT con ISNUMBER.
Potremmo usare la funzione =ISTEXT(VLOOKUP(C2, $A$2:$A$21,1,FALSE))
O se stessimo usando gli array dinamici in Excel 365, potremmo usare la funzione
=ISTEXT(VLOOKUP (C2:C12,$A$2:$A$21,1,FALSE))
XLOOKUP per confrontare due liste in Excel
XLOOKUP è stato introdotto in Excel 365 e puoi trovare maggiori informazioni qui. Molto simile a VLOOKUP, XLOOKUP restituirà un valore corrispondente da una cella, ed è possibile definire un risultato se il valore non viene trovato. Usando gli array dinamici, la funzione sarebbe
=ISTEXT(XLOOKUP (C2:C12,A2:A21,A2:A21))
COUNTIF per confrontare due liste in Excel
La funzione COUNTIF conterà il numero di volte che un valore, o testo è contenuto in un intervallo. Se il valore non viene trovato, viene restituito 0. Possiamo combinarla con un’istruzione IF per restituire i nostri valori vero e falso.
=IF(COUNTIF (A2:A21,C2:C12)<>0, “True”, “False”)
Come confrontare due serie di dati in Excel
Comparare due liste è abbastanza facile e abbiamo visto diversi modi per farlo. Ma confrontare due serie di dati può essere un po’ più difficile.
Guardiamo un esempio. Abbiamo due tabelle di dati, ciascuna contenente le stesse intestazioni di colonna. Guardando l’immagine, possiamo vedere che abbinare queste due tabelle richiederebbe di guardare più di una colonna.
Quando avete bisogno di guardare più di una colonna, la soluzione sarebbe quella di creare una colonna composita combinando i dati in una colonna. Questo creerà una colonna unica per ogni riga che possiamo poi usare come colonna corrispondente
In questo esempio potremmo combinare il Nome e il DoB per dare ad ogni tabella un identificatore unico
Ci sono molti modi per unire il contenuto di una cella, in questo caso faremo una semplice concatenazione. Dato che stiamo usando delle tabelle, la formula seguirà il formato di denominazione delle tabelle.
= &”-“&
Ripetere i passi sulla seconda tabella.
Ora possiamo usare uno qualsiasi degli esempi precedenti per far corrispondere queste due nuove colonne di dati. Dove corrispondono, sappiamo che corrispondono all’intera riga.
Confrontare liste o set di dati usando Power Query
Puoi anche confrontare liste e set di dati usando Excels Power Query. Collegandosi alle tabelle e poi unendo le tabelle, usando diversi tipi di join possiamo confrontare entrambi gli elenchi.
In questo video imparerete come confrontare o riconciliare due diversi set di dati usando Excels Power Query
C’è un set di dati per accompagnare questo video e la pratica che potete prendere da questo articolo.