Come confrontare due elenchi in Excel

Excel è un efficiente programma di elaborazione dati. E uno dei metodi di analisi delle informazioni è il confronto di due elenchi. Se confronti correttamente due elenchi in Excel, organizzare questo processo sarà molto semplice. Basta seguire alcuni dei punti che verranno discussi oggi. L'attuazione pratica di questo metodo dipende interamente dalle esigenze della persona o dell'organizzazione in un determinato momento. Pertanto, dovrebbero essere considerati diversi casi possibili.

Confronto di due elenchi in Excel

Naturalmente, puoi confrontare due elenchi manualmente. Ma ci vorrà molto tempo. Excel ha il suo toolkit intelligente che ti consentirà di confrontare i dati non solo rapidamente, ma anche di ottenere informazioni che non sono così facili da ottenere con i tuoi occhi. Supponiamo di avere due colonne con le coordinate A e B. Alcuni valori sono ripetuti in esse.

Formulazione del problema

Quindi dobbiamo confrontare queste colonne. La procedura per confrontare due documenti è la seguente:

1. Se le celle univoche di ciascuno di questi elenchi sono le stesse e il numero totale di celle univoche è lo stesso e le celle sono le stesse, allora questi elenchi possono essere considerati uguali. L'ordine in cui sono impilati i valori in questo elenco non ha molta importanza.
2. Possiamo parlare di coincidenza parziale delle liste se i valori univoci stessi sono gli stessi, ma il numero di ripetizioni è diverso. Pertanto, tali elenchi possono contenere un numero diverso di elementi.
3. Il fatto che le due liste non corrispondano è indicato da un diverso insieme di valori univoci.

Tutte e tre queste condizioni contemporaneamente sono le condizioni del nostro problema.

La soluzione del problema

Generiamo due intervalli dinamici per semplificare il confronto degli elenchi. Ciascuno di essi corrisponderà a ciascuna delle liste.

Per confrontare due elenchi, procedere come segue:

1. In una colonna separata, creiamo un elenco di valori univoci specifici per entrambi gli elenchi. Per questo usiamo la formula: ЕСЛИОШИБКА(ЕСЛИОШИБКА( ИНДЕКС(Список1;ПОИСКПОЗ(0;СЧЁТЕСЛИ($D$4:D4;Список1);0)); ИНДЕКС(Список2;ПОИСКПОЗ(0;СЧЁТЕСЛИ($D$4:D4;Список2);0))); «»). La formula stessa deve essere scritta come una formula di matrice.
2. Determiniamo quante volte ogni valore univoco si verifica nell'array di dati. Ecco le formule per farlo: =COUNTIF(Lista1,D5) e =COUNTI(Lista2,D5).
3. Se sia il numero di ripetizioni che il numero di valori univoci sono gli stessi in tutti gli elenchi inclusi in questi intervalli, la funzione restituisce il valore 0. Ciò indica che la corrispondenza è XNUMX%. In questo caso, le intestazioni di questi elenchi acquisiranno uno sfondo verde.
4. Se tutto il contenuto univoco è in entrambi gli elenchi, viene restituito dalle formule =СЧЁТЕСЛИМН($D$5:$D$34;»*?»;E5:E34;0) и =СЧЁТЕСЛИМН($D$5:$D$34;»*?»;F5:F34;0) il valore sarà zero. Se E1 non contiene zero, ma tale valore è contenuto nelle celle E2 e F2, in questo caso gli intervalli verranno riconosciuti come corrispondenti, ma solo parzialmente. In questo caso, le intestazioni delle liste corrispondenti diventeranno arancioni.
5. E se una delle formule sopra descritte restituisce un valore diverso da zero, gli elenchi saranno completamente non corrispondenti.

Questa è la risposta alla domanda su come analizzare le colonne per le corrispondenze utilizzando le formule. Come puoi vedere, con l'uso delle funzioni, puoi implementare quasi tutti i compiti che, a prima vista, non sono legati alla matematica.

Esempio di test

Nella nostra versione della tabella, ci sono tre tipi di elenchi di ciascun tipo descritto sopra. Ha una corrispondenza parziale e totale, nonché una non corrispondenza.

Per confrontare i dati, utilizziamo l'intervallo A5:B19, in cui inseriamo alternativamente queste coppie di elenchi. Di quale sarà il risultato del confronto, lo capiremo dal colore delle liste originali. Se sono completamente diversi, sarà uno sfondo rosso. Se una parte dei dati è la stessa, allora giallo. In caso di identità completa, le intestazioni corrispondenti saranno di colore verde. Come fare un colore a seconda del risultato? Ciò richiede la formattazione condizionale.

Trovare le differenze in due elenchi in due modi

Descriviamo altri due metodi per trovare le differenze, a seconda che gli elenchi siano sincroni o meno.

Opzione 1. Elenchi sincroni

Questa è un'opzione facile. Supponiamo di avere tali elenchi.

Per determinare quante volte i valori non sono convergenti, puoi utilizzare la formula: =PRODOTTOSOMMA(—(A2:A20<>B2:B20)). Se otteniamo 0 come risultato, significa che le due liste sono le stesse.

Opzione 2: elenchi mescolati

Se gli elenchi non sono identici nell'ordine degli oggetti che contengono, è necessario applicare una funzionalità come la formattazione condizionale e colorare i valori duplicati. Oppure usa la funzione COUNTIF, utilizzando il quale determiniamo quante volte un elemento di un elenco si verifica nel secondo.

Come confrontare 2 colonne riga per riga

Quando confrontiamo due colonne, spesso abbiamo bisogno di confrontare le informazioni che si trovano in righe diverse. Per fare questo, l'operatore ci aiuterà SE. Diamo un'occhiata a come funziona in pratica. Per fare ciò, presentiamo diverse situazioni illustrative.

Esempio. Come confrontare 2 colonne per corrispondenze e differenze in una riga

Per analizzare se i valori che si trovano nella stessa riga ma colonne diverse sono gli stessi, scriviamo la funzione IF. La formula viene inserita in ogni riga posta nella colonna ausiliaria dove verranno visualizzati i risultati dell'elaborazione dei dati. Ma non è affatto necessario prescriverlo in ogni riga, basta copiarlo nelle restanti celle di questa colonna o utilizzare il marcatore di completamento automatico.

Dovremmo scrivere una tale formula per capire se i valori in entrambe le colonne sono gli stessi o meno: =SE(LA2=B2, “Corrispondenza”, “”). La logica di questa funzione è molto semplice: confronta i valori nelle celle A2 e B2 e, se sono uguali, visualizza il valore "Coincide". Se i dati sono diversi, non restituisce alcun valore. Puoi anche controllare le celle per vedere se c'è una corrispondenza tra di loro. In questo caso la formula utilizzata è: =SE(A2<>B2, “Non corrispondono”, “”). Il principio è lo stesso, prima viene effettuato il controllo. Se risulta che le celle soddisfano il criterio, viene visualizzato il valore "Non corrisponde".

È inoltre possibile utilizzare la seguente formula nel campo della formula per visualizzare sia "Corrispondenza" se i valori sono gli stessi, sia "Non corrispondenza" se sono diversi: =SE(A2=B2; “Corrispondenza”, “Non corrispondenza”). Puoi anche usare l'operatore di disuguaglianza invece dell'operatore di uguaglianza. Solo l'ordine dei valori che verranno visualizzati in questo caso sarà leggermente diverso: =SE(A2<>B2, “Non coincidere”, “Coincidere”). Dopo aver utilizzato la prima versione della formula, il risultato sarà il seguente.

Questa variazione della formula non fa distinzione tra maiuscole e minuscole. Pertanto, se i valori in una colonna differiscono dagli altri solo per il fatto che sono scritti in lettere maiuscole, il programma non noterà questa differenza. Per rendere il confronto con distinzione tra maiuscole e minuscole, è necessario utilizzare la funzione nei criteri ESATTO. Il resto degli argomenti rimane invariato: =SE(ESATTO(A2,B2), “Corrispondenza”, “Unico”).

Come confrontare più colonne per le corrispondenze in una riga

È possibile analizzare i valori nelle liste secondo tutta una serie di criteri:

1. Trova quelle righe che hanno gli stessi valori ovunque.
2. Trova quelle righe in cui ci sono corrispondenze in solo due elenchi.

Diamo un'occhiata ad alcuni esempi di come procedere in ciascuno di questi casi.

Esempio. Come trovare corrispondenze in una riga in più colonne di una tabella

Supponiamo di avere una serie di colonne che contengono le informazioni di cui abbiamo bisogno. Ci troviamo di fronte al compito di determinare quelle righe in cui i valori sono gli stessi. Per fare ciò, è necessario utilizzare la seguente formula: =SE(E(LA2=B2,LA2=C2), “corrispondenza”, ” “).

Se ci sono troppe colonne nella tabella, devi solo usarla insieme alla funzione IF operatore COUNTIF: =SE(COUNTIF($A2:$C2,$A2)=3;”corrispondenza”;” “). Il numero utilizzato in questa formula indica il numero di colonne da controllare. Se è diverso, allora devi scrivere quanto è vero per la tua situazione.

Esempio. Come trovare corrispondenze in una riga in 2 colonne qualsiasi di una tabella

Diciamo che dobbiamo controllare se i valori in una riga corrispondono in due colonne da quelli nella tabella. Per fare ciò, è necessario utilizzare la funzione come condizione OR, dove scrivi alternativamente l'uguaglianza di ciascuna delle colonne all'altra. Ecco un esempio.

Usiamo questa formula: =ЕСЛИ(ИЛИ(A2=B2;B2=C2;A2=C2);”Совпадают”;” “). Potrebbe verificarsi una situazione in cui sono presenti molte colonne nella tabella. In questo caso, la formula sarà enorme e potrebbe essere necessario molto tempo per selezionare tutte le combinazioni necessarie. Per risolvere questo problema, è necessario utilizzare la funzione COUNTIF: =SE(COUNTIF(B2:D2,A2)+COUNTIF(C2:D2,B2)+(C2=D2)=0; “Stringa univoca”; “Stringa non univoca”)

Vediamo che in totale abbiamo due funzioni COUNTIF. Con la prima determiniamo alternativamente quante colonne hanno una somiglianza con A2 e con la seconda controlliamo il numero di somiglianze con il valore di B2. Se, come risultato del calcolo con questa formula, otteniamo un valore zero, ciò indica che tutte le righe in questa colonna sono uniche, se di più ci sono somiglianze. Pertanto, se calcolando con due formule e sommando i risultati finali otteniamo un valore zero, viene restituito il valore di testo "Stringa univoca", se questo numero è maggiore, viene scritto che questa stringa non è univoca.

Come confrontare 2 colonne in Excel per le partite

Ora facciamo un esempio. Supponiamo di avere una tabella con due colonne. Devi controllare se corrispondono. Per fare ciò, è necessario applicare la formula, in cui verrà utilizzata anche la funzione IF, e l'operatore COUNTIF: =SE(COUNTIF($B:$B,$A5)=0, “Nessuna corrispondenza nella colonna B”, “Ci sono corrispondenze nella colonna B”)

Non è richiesta alcuna ulteriore azione. Dopo aver calcolato il risultato con questa formula, otteniamo se il valore del terzo argomento della funzione IF partite. Se non ce ne sono, allora il contenuto del secondo argomento.

Come confrontare 2 colonne in Excel per le corrispondenze ed evidenziare con il colore

Per facilitare l'identificazione visiva delle colonne corrispondenti, puoi evidenziarle con un colore. Per fare ciò, è necessario utilizzare la funzione "Formattazione condizionale". Vediamo in pratica.

Trovare ed evidenziare corrispondenze per colore in più colonne

Per determinare le corrispondenze ed evidenziarle, è necessario prima selezionare l'intervallo di dati in cui verrà effettuato il controllo, quindi aprire la voce “Formattazione condizionale” nella scheda “Home”. Lì, seleziona "Valori duplicati" come regola di selezione della cella.

Successivamente, apparirà una nuova finestra di dialogo, in cui nell'elenco a comparsa di sinistra troviamo l'opzione "Ripetizione", e nell'elenco a destra selezioniamo il colore che verrà utilizzato per la selezione. Dopo aver fatto clic sul pulsante "OK", verrà selezionato lo sfondo di tutte le celle con somiglianze. Quindi confronta le colonne a occhio.

Trovare ed evidenziare le linee corrispondenti

La tecnica per verificare se le stringhe corrispondono è leggermente diversa. Innanzitutto, dobbiamo creare una colonna aggiuntiva e lì utilizzeremo i valori combinati usando l'operatore &. Per fare ciò, è necessario scrivere una formula del modulo: =A2&B2&C2&D2.

Selezioniamo la colonna che è stata creata e contiene i valori combinati. Successivamente, eseguiamo la stessa sequenza di azioni descritta sopra per le colonne. Le linee duplicate verranno evidenziate nel colore specificato.

Vediamo che non c'è niente di difficile nel cercare le ripetizioni. Excel contiene tutti gli strumenti necessari per questo. È importante esercitarsi prima di mettere in pratica tutte queste conoscenze.