Se lavori con fogli di calcolo di grandi dimensioni (molti dati/colonne), è molto difficile controllare l'accuratezza/pertinenza di tutte le informazioni. Pertanto, molto spesso è necessario analizzare due o più colonne in un documento Excel per rilevare la duplicazione. E se l'utente non dispone di informazioni su tutte le funzionalità del programma, potrebbe logicamente avere una domanda: come confrontare due colonne in Excel?
La risposta è stata a lungo escogitata dagli sviluppatori di questo programma, che inizialmente vi hanno inserito comandi che aiutano a confrontare le informazioni. In generale, se approfondisci questo problema, puoi trovare circa una dozzina di modi diversi, inclusa la scrittura di singole macro e formule. Ma la pratica mostra: basta conoscere tre o quattro modi affidabili per far fronte alle esigenze emergenti di confronto.
Come confrontare due colonne in Excel per le corrispondenze
I dati in Excel vengono solitamente confrontati tra righe, tra colonne, con un valore fornito come benchmark. Se è necessario confrontare le colonne, è possibile utilizzare la funzionalità integrata, ovvero le azioni Match e If. E tutto ciò di cui hai bisogno è Excel non prima del settimo anno di "rilascio".
Iniziamo con la funzione "Coincidenza". Ad esempio, i dati confrontati sono nelle colonne con gli indirizzi C3 e B3. Il risultato del confronto deve essere inserito in una cella, ad esempio D3. Facciamo clic su questa cella, entriamo nella directory del menu "formule", troviamo la riga "libreria di funzioni", apriamo le funzioni poste nell'elenco a discesa, troviamo la parola "testo" e facciamo clic su "Coincidenza".
In un attimo vedrai sul display nuova forma, dove ci saranno solo due campi: "testo uno", "testo due". Devi inserire solo gli indirizzi delle colonne confrontate (C3, B3), quindi fare clic sul solito tasto "OK". Di conseguenza, vedrai il risultato con le parole "Vero" / "Falso". In linea di principio, niente di particolarmente complicato anche per un utente alle prime armi! Ma questo è tutt'altro che l'unico metodo. Analizziamo la funzione "If".
Possibilità di confrontare due colonne in Excel per le corrispondenze utilizzando "If" consente di inserire valori che verranno visualizzati alla fine dopo l'operazione. Il cursore viene posizionato nella cella in cui verrà effettuato l'inserimento, si apre la directory del menu "libreria di funzioni", la riga "logica" viene selezionata nell'elenco a discesa e la prima posizione in essa sarà occupata dal " Se" comando. Lo scegliamo.
Inoltre decolla la forma del riempimento ragionato. "Logic_expression" è la formulazione della funzione stessa. Nel nostro caso, questo è un confronto di due colonne, quindi inseriamo "B3 \u003d C3" (o gli indirizzi delle tue colonne). Ulteriori campi "value_if true", "value_if_false". Qui dovresti inserire i dati (etichette/parole/numeri) che dovrebbero corrispondere ad un risultato positivo/negativo. Dopo il riempimento, fai clic, come al solito, su "ok". Facciamo conoscenza con il risultato.
Se vuoi eseguire l'analisi riga per riga di due colonne, nella terza colonna inseriamo qualsiasi funzione discussa sopra ("se" o "coincidenza"). La sua azione si estende a tutta l'altezza delle colonne riempite. Successivamente, seleziona la terza colonna, fai clic sulla scheda "principale", cerca la parola "stili" nel gruppo che appare. Si apriranno le "regole di selezione colonna/cella".
In essi è necessario selezionare il comando "uguale", quindi fare clic sulla prima colonna e premere "Invio". Di conseguenza, risulta "colorare" le colonne in cui sono presenti risultati corrispondenti. E vedrai immediatamente le informazioni di cui hai bisogno. Inoltre, nell'analisi dell'argomento "come confrontare i valori di due colonne in Excel", passiamo a un metodo come la formattazione condizionale in Excel.
Excel: formattazione condizionale
La formattazione di un tipo condizionale ti consentirà non solo di confrontare due diverse colonne/celle/righe, ma anche di evidenziare dati diversi in esse con un determinato colore (rosso). Cioè, non stiamo cercando coincidenze, ma differenze. Per ottenerlo, ci comportiamo così. Selezioniamo le colonne necessarie, senza toccare i loro nomi, andiamo alla directory del menu "principale", in essa cerchiamo la sottosezione "stili".
Conterrà la riga "formattazione condizionale". Facendo clic su di esso, otteniamo un elenco in cui abbiamo bisogno dell'elemento della funzione "crea regola". Passaggio successivo: nella riga "formato", devi inserire la formula = $ A2<>$B2. Questa formula aiuterà Excel a capire di cosa abbiamo esattamente bisogno, ovvero colorare in rosso tutti i valori della colonna A che non corrispondono ai valori della colonna B. Un modo leggermente più complicato per applicare le formule riguarda alla partecipazione di strutture come HLOOKUP / VLOOKUP. Queste formule si riferiscono a ricerche di valori orizzontali/verticali. Consideriamo questo metodo in modo più dettagliato.
CERCA.ORI e CERCA.VERT
Queste due formule consentono di cercare i dati in orizzontale/verticale. Cioè, H è orizzontale e V è verticale. Se i dati da confrontare si trovano nella colonna di sinistra relativa a quella in cui si trovano i valori confrontati, utilizzare il costrutto CERCA.VERT. Ma se i dati per il confronto sono orizzontalmente in cima alla tabella dalla colonna in cui sono indicati i valori di riferimento, utilizziamo la costruzione HLOOKUP.
Per capire come confrontare verticalmente i dati in due colonne Excel, è necessario utilizzare la seguente formula completa: lookup_value,table_array,col_index_num,range_lookup.
Il valore da trovare è indicato come "lookup_value". Le colonne di ricerca sono inserite come un "array di tabelle". Il numero di colonna deve essere specificato come "sol_index_num". Inoltre, questa è la colonna il cui valore coincideva e che deve essere restituita/corretta. Il comando "ricerca intervallo" qui funge da comando aggiuntivo. Può indicare se il valore deve essere reso esatto o approssimativo.
Se questo comando non viene specificato, verranno restituiti i valori per entrambi i tipi. La formula HLOOKUP ha questo aspetto completo: lookup_value,table_array,row_index_num,range_lookup. Lavorare con esso è quasi identico a quello sopra descritto. È vero, c'è un'eccezione qui. Questo è l'indice della stringa, specificando la stringa i cui valori devono essere restituiti. Se impari ad applicare chiaramente tutti i metodi di cui sopra, diventa chiaro: non esiste un programma più conveniente e universale con cui lavorare grande quantità dati tipi diversi rispetto a Excel. Il confronto di due colonne in Excel è, tuttavia, solo metà del lavoro. Dopotutto, è necessario fare qualcos'altro con i valori ottenuti. Cioè, le corrispondenze trovate devono ancora essere elaborate in qualche modo.
Come gestire i valori duplicati
Quindi, ci sono numeri trovati nella prima, supponiamo, colonna, che si ripetono completamente nella seconda colonna. È chiaro che correggere manualmente le ripetizioni è un lavoro irrealistico, che richiede molto tempo prezioso. Pertanto, dovresti utilizzare una tecnica già pronta per la correzione automatica.
Perché funzioni, devi prima nominare le colonne se non esistono. Regoliamo il cursore sulla prima riga, tasto destro del mouse, nel menu che appare, selezioniamo "inserisci". Diciamo che le intestazioni sono "nome" e "duplicato". Successivamente, hai bisogno della directory "data", in essa - "filtro". Fai clic sulla piccola freccia accanto al "duplicato" e rimuovi tutti gli "uccelli" dall'elenco. Ora fai clic su OK e i valori duplicati della colonna A diventano visibili.
Successivamente, selezioniamo queste celle, perché avevamo bisogno non solo di confrontare il testo in due colonne in Excel, ma anche di rimuovere i duplicati. Selezionato, fatto clic con il pulsante destro del mouse, selezionato "elimina riga", fatto clic su "ok" e ottenuto una tabella senza valori corrispondenti. Il metodo funziona se le colonne sono sulla stessa pagina, cioè sono adiacenti.
Pertanto, abbiamo analizzato diversi modi per confrontare due colonne in Excel. Volutamente non ti ho mostrato screenshot, perché ti confonderesti.
MA ho preparato un fantastico video di uno dei più popolari e modi semplici confrontando due colonne nel documento e ora ti suggerisco di familiarizzare con esso per consolidare il materiale trattato.
Se l'articolo ti è stato ancora utile, condividilo sui social media. reti o valuta facendo clic sul numero di stelle che ritieni opportuno. Grazie, per oggi è tutto, a presto.
© Aleksandr Ivanov.
Spesso il compito è confrontare due elenchi di elementi. Farlo manualmente è troppo noioso e inoltre non si può escludere la possibilità di errori. Excel semplifica questa operazione. Questo suggerimento descrive un metodo che utilizza formattazione condizionale.
Sulla fig. 164.1 è un esempio di due elenchi di nomi a più colonne. L'applicazione della formattazione condizionale può rendere più evidenti le differenze negli elenchi. Questi esempi di elenchi contengono testo, ma questo metodo funziona anche con dati numerici.
Il primo elenco è A2:B31, questo intervallo è chiamato vecchio elenco. Il secondo elenco è D2:E31 , viene chiamato l'intervallo nuovo elenco. Gli intervalli sono stati denominati con il comando Formule Nomi definiti Assegnare un nome. Assegnare un nome agli intervalli è facoltativo, ma semplifica il loro utilizzo.
Iniziamo aggiungendo la formattazione condizionale al vecchio elenco.
- Seleziona le celle dell'intervallo vecchio elenco.
- Selezionare .
- Nella finestra Crea una regola di formattazione selezionare l'elemento denominato Usa Formula
- Immettere questa formula nel campo della finestra (Fig. 164.2): =COUNTIF(NewList;A2)=0 .
- Fare clic sul pulsante Formato e impostare la formattazione da applicare quando la condizione è vera. È meglio scegliere diversi colori di riempimento.
- Fare clic su OK.
Celle in un intervallo nuovo elenco utilizzare una formula di formattazione condizionale simile.
- Seleziona le celle dell'intervallo nuovo elenco.
- Selezionare Home Formattazione condizionale Crea regola per aprire una finestra di dialogo Crea una regola di formattazione.
- Nella finestra Crea una regola formattazione dell'elemento selezionato Usa Formula per definire le celle da formattare.
- Inserisci questa formula nella finestra: =COUNTIF(OldList;D2)=0 .
- Fare clic sul pulsante Formato e impostare la formattazione che verrà applicata quando la condizione è vera (diverso colore di riempimento).
- Fare clic su OK.
Di conseguenza, verranno evidenziati i nomi che si trovano nel vecchio elenco, ma che non si trovano in quello nuovo (Fig. 164.3). Inoltre, vengono evidenziati anche i nomi nel nuovo elenco che non sono presenti nel vecchio elenco, ma con un colore diverso. I nomi che compaiono in entrambi gli elenchi non sono evidenziati.
Entrambe le formule di formattazione condizionale utilizzano la funzione CONTA.SE. Conta quante volte un certo valore appare in un intervallo. Se la formula restituisce 0, significa che l'elemento non è nell'intervallo. Quindi la formattazione condizionale prende il sopravvento e il colore di sfondo della cella cambia.
Questo è un capitolo di un libro: Michael Girvin. Ctrl+Maiusc+Invio. Padroneggiare le formule di matrice in Excel.
Selezioni basate su una o più condizioni. Riga Funzioni di Excel utilizzare gli operatori di confronto. Ad esempio, SOMMA.SE, SOMMA.SE, CONTA.SE, CONTA.SE, MEDIA.SE e MEDIA.SE. Queste funzioni effettuano selezioni in base a una o più condizioni (criteri). Il problema è che queste funzioni possono solo sommare, contare e fare la media. E se vuoi imporre condizioni alla ricerca, ad esempio il valore massimo o la deviazione standard? In questi casi, poiché non esiste una funzione incorporata, è necessario inventare una formula di matrice. Spesso ciò è dovuto all'uso dell'operatore di confronto di array. Il primo esempio in questo capitolo mostra come calcolare il valore minimo in una condizione.
Usiamo la funzione SE per selezionare gli elementi di un array che soddisfano una condizione. Sulla fig. 4.1 nella tabella di sinistra c'è una colonna con i nomi delle città e una colonna con l'ora. È necessario trovare il tempo minimo per ogni città e inserire questo valore nella cella corrispondente della tabella a destra. La condizione di selezione è il nome della città. Se usi la funzione MIN, puoi trovare il valore minimo della colonna B. Ma come selezioni solo quei numeri che si applicano solo ad Auckland? E come si copiano le formule lungo la colonna? Poiché Excel non ha una funzione MINESLI incorporata, è necessario scrivere una formula originale che combini le funzioni IF e MIN.
Riso. 4.1. Scopo della formula: selezionare il tempo minimo per ogni città
Scarica la nota in formato o in formato
Come mostrato in fig. 4.2, dovresti iniziare a inserire la formula nella cella E3 con la funzione MIN. Ma non puoi mettere in discussione numero 1 tutti i valori della colonna B!? Vuoi selezionare solo quei valori relativi ad Auckland.
Come mostrato in fig. 4.3, nel passaggio successivo, inserisci la funzione IF come argomento numero 1 per MIN. Metti un IF all'interno di un MIN.
Posizionando il cursore nel punto in cui viene inserito l'argomento espressione_log funzione IF (Fig. 4.4), si seleziona l'intervallo con i nomi delle città A3:A8, quindi si preme F4 per rendere assoluti i riferimenti di cella (si veda, ad esempio, per maggiori dettagli). Quindi digiti l'operatore di confronto, il segno di uguale. Infine, selezionerai la cella a sinistra della formula, D3, lasciando relativo il riferimento ad essa. La condizione formulata ti consentirà di selezionare solo Aucklands quando visualizzi l'intervallo A3: A8.
Riso. 4.4. Crea un operatore di matrice in un argomento espressione_log SE funzioni
Quindi hai creato un operatore di matrice con un operatore di confronto. In qualsiasi momento durante l'elaborazione dell'array, l'operatore di array è un operatore di confronto, quindi il suo risultato sarà un array di valori VERO e FALSO. Per verificarlo, seleziona l'array (per farlo, fai clic sull'argomento nel tooltip espressione_log) e premere F9 (Fig. 4.5). Di solito usi un argomento espressione_log, restituendo VERO o FALSO; qui, l'array risultante restituirà più valori VERO e FALSO, quindi la funzione MIN selezionerà il numero minimo solo per quelle città che corrispondono al valore VERO.
Riso. 4.5. Per visualizzare un array di valori VERO e FALSO, fare clic sull'argomento nella descrizione comandi espressione_log e premi F9
La capacità di confrontare due serie di dati in Excel è spesso utile per le persone che elaborano grandi quantità di dati e lavorano con enormi fogli di calcolo. Ad esempio, il confronto può essere utilizzato nella correttezza dell'inserimento dei dati o nell'inserimento dei dati in una tabella in tempo. L'articolo seguente descrive diversi trucchi per confrontare due colonne con i dati in Excel.
Usando l'istruzione IF condizionale
Il metodo di utilizzo dell'operatore IF condizionale differisce in quanto viene utilizzata solo la parte necessaria per il confronto per confrontare due colonne e non l'intero array. I passaggi per implementare questo metodo sono descritti di seguito:
Posiziona entrambe le colonne per il confronto nelle colonne A e B del foglio di lavoro.
Nella cella C2, inserisci la seguente formula =IF(ISERROR(MATCH(A2,$B$2:$B$11,0)),"";A2) e trascinala nella cella C11. Questa formula scorre la presenza di ciascun elemento dalla colonna A nella colonna B e restituisce il valore dell'elemento se è stato trovato nella colonna B.
Utilizzo della formula di sostituzione CERCA.VERT
Il principio di funzionamento della formula è simile al metodo precedente, la differenza sta invece nel MATCH. Caratteristica distintiva di questo metodo è anche la possibilità di confrontare due array orizzontali utilizzando la formula HLOOKUP.
Per confrontare due colonne con i dati nelle colonne A e B (simile al metodo precedente), inserisci la seguente formula =CERCA.VERT(A2,$B$2:$B$11,1,0) nella cella C2 e trascinala nella cella C11 .
Questa formula esamina ogni elemento dell'array principale nell'array confrontato e ne restituisce il valore se è stato trovato nella colonna B. In caso contrario, Excel restituirà un errore #N/D.
Utilizzo di una macro VBA
L'utilizzo di macro per confrontare due colonne consente di unificare il processo e ridurre i tempi di preparazione dei dati. Decidere quale risultato del confronto visualizzare dipende interamente dalla tua immaginazione e dalle tue abilità macro. Di seguito è riportata una tecnica pubblicata sul sito Web ufficiale di Microsoft.
1 | Sub Find_Match() |
In questo codice, alla variabile CompareRange viene assegnato un intervallo con la matrice da confrontare. Quindi esegue un ciclo che scorre ogni elemento nell'intervallo selezionato e lo confronta con ogni elemento nell'intervallo confrontato. Se sono stati trovati elementi con valori identici, la macro inserisce il valore dell'elemento nella colonna C.
Per utilizzare la macro, torna al foglio di lavoro, seleziona l'intervallo principale (nel nostro caso si tratta delle celle A1:A11), premi Alt + F8. Nella finestra di dialogo visualizzata, seleziona una macro Trovare_Partite e fare clic sul pulsante Esegui.
Dopo aver eseguito la macro, il risultato dovrebbe essere simile a questo:
Utilizzando il componente aggiuntivo Richiedi informazioni
Risultato
Quindi, abbiamo esaminato diversi modi per confrontare i dati in Excel che ti aiuteranno a risolvere alcuni problemi analitici e semplificheranno il lavoro nella ricerca di valori duplicati (o univoci).
Abbiamo due tabelle di ordini copiate in un foglio di lavoro. È necessario confrontare i dati di due tabelle in Excel e verificare quali posizioni si trovano nella prima tabella, ma non nella seconda. Non ha senso confrontare manualmente il valore di ogni cella.
Confronta due colonne per le corrispondenze in Excel
Come confrontare i valori in Excel di due colonne? Per risolvere questo problema, ti consigliamo di utilizzare la formattazione condizionale, che evidenzia rapidamente le posizioni che si trovano in una sola colonna. Foglio di lavoro con tabelle:
Il primo passo è nominare entrambe le tabelle. In questo modo è più facile capire quali intervalli di celle vengono confrontati:
- Selezionare lo strumento FORMULA - Nomi definiti - Assegna nome.
- Nella finestra che appare, nel campo "Nome:", inserisci il valore - Tabella_1.
- Fare clic con il tasto sinistro del mouse sul campo di immissione "Intervallo:" e selezionare l'intervallo: A2:A15. E fai clic su OK.
Per il secondo elenco, segui gli stessi passaggi, assegna solo un nome - Table_2. E specificare l'intervallo C2:C15 - rispettivamente.
Consigli utili! I nomi degli intervalli possono essere assegnati più velocemente utilizzando il campo del nome. Si trova a sinistra della barra della formula. Basta selezionare gli intervalli di celle e, nel campo del nome, inserire il nome appropriato per l'intervallo e premere Invio.
Ora usiamo la formattazione condizionale per confrontare due elenchi in Excel. Dobbiamo ottenere il seguente risultato:
Verranno visualizzati gli elementi che si trovano in Table_1 ma non in Table_2 in verde. Allo stesso tempo, le posizioni che sono in Table_2 ma non in Table_1 saranno evidenziate in blu.
Principio del confronto dei dati di due colonne in Excel
Quando si definiscono le condizioni per la formattazione delle celle delle colonne, è stata utilizzata la funzione CONTA.SE. IN questo esempio questa funzione controlla quante volte il valore del secondo argomento (ad esempio, A2) ricorre nell'elenco del primo argomento (ad esempio, Table_2). Se numero di volte = 0, la formula restituisce VERO. In questo caso, alla cella viene assegnato il formato personalizzato specificato nelle opzioni di formattazione condizionale. Il collegamento nel secondo argomento è relativo, il che significa che tutte le celle nell'intervallo selezionato verranno controllate una per una (ad esempio, A2:A15). La seconda formula funziona in modo simile. Lo stesso principio può essere applicato a vari compiti simili. Ad esempio, per confrontare due listini prezzi in Excel, anche