Una delle molte funzionalità di Microsoft Excel è la capacità di confrontare due elenchi di dati, identificare le corrispondenze tra gli elenchi e identificare quali elementi si trovano in un solo elenco. Ciò è utile quando si confrontano i record finanziari o si controlla se un nome particolare è presente in un database. È possibile utilizzare la funzione CONFRONTA per identificare e contrassegnare i record corrispondenti o non corrispondenti, oppure è possibile utilizzare la formattazione condizionata con la funzione CONTA. SE. I passaggi seguenti spiegano come utilizzare ciascuno per abbinare i dati.
Passi
Metodo 1 di 2: Identificazione dei record con la funzione MATCH
Passaggio 1. Copia gli elenchi di dati su un singolo foglio di lavoro
Excel può funzionare con più fogli di lavoro all'interno di una singola cartella di lavoro o con più cartelle di lavoro, ma troverai più facile confrontare gli elenchi se copi le loro informazioni su un singolo foglio di lavoro.
Passaggio 2. Assegna a ciascun elemento dell'elenco un identificatore univoco
Se i tuoi due elenchi non condividono un modo comune per identificarli, potresti dover aggiungere una colonna aggiuntiva a ciascun elenco di dati che identifichi quell'elemento in Excel in modo che possa vedere se un elemento in un determinato elenco è correlato a un elemento nell'altro elenco. La natura di questo identificatore dipenderà dal tipo di dati che stai cercando di abbinare. Avrai bisogno di un identificatore per ogni elenco di colonne.
- Per i dati finanziari associati a un determinato periodo, come i documenti fiscali, potrebbe trattarsi della descrizione di un bene, della data di acquisizione del bene o di entrambi. In alcuni casi, una voce può essere identificata con un numero di codice; tuttavia, se non viene utilizzato lo stesso sistema per entrambe le liste, questo identificatore può creare corrispondenze laddove non ce ne sono o ignorare le corrispondenze che dovrebbero essere effettuate.
- In alcuni casi, puoi prendere elementi da un elenco e combinarli con elementi di un altro elenco per creare un identificatore, come la descrizione di un bene fisico e l'anno in cui è stato acquistato. Per creare un tale identificatore, concatena (aggiungi, combina) i dati da due o più celle usando la e commerciale (&). Per combinare una descrizione dell'oggetto nella cella F3 con una data nella cella G3, separata da uno spazio, inserire la formula "= F3&" "&G3" in un'altra cella di quella riga, ad esempio E3. Se volessi includere solo l'anno nell'identificatore (perché un elenco utilizza date complete e l'altro utilizza solo anni), dovresti includere la funzione ANNO inserendo invece '=F3&" "&YEAR(G3)' nella cella E3. (Non includere le virgolette singole, sono lì solo per indicare l'esempio.)
- Una volta creata la formula, puoi copiarla in tutte le altre celle della colonna identificatore selezionando la cella con la formula e trascinando il quadratino di riempimento sulle altre celle della colonna in cui desideri copiare la formula. Quando rilasci il pulsante del mouse, ogni cella trascinata verrà popolata con la formula, con i riferimenti di cella adattati alle celle appropriate nella stessa riga.
Passaggio 3. Standardizzare i dati ove possibile
Mentre la mente riconosce che "Inc." e "Incorporato" significano la stessa cosa, Excel non lo fa a meno che tu non abbia riformattato una parola o l'altra. Allo stesso modo, puoi considerare valori come $ 11, 950 e $ 11, 999,95 abbastanza vicini da corrispondere, ma Excel non lo farà a meno che tu non lo dica.
- È possibile utilizzare alcune abbreviazioni, come "Co" per "Società" e "Inc" per "Incorporata utilizzando la funzione stringa SINISTRA per troncare i caratteri aggiuntivi. Altre abbreviazioni, come "Assn" per "Associazione", potrebbero essere affrontato stabilendo una guida allo stile di immissione dei dati e quindi scrivendo un programma per cercare e correggere formati non corretti.
- Per le stringhe di numeri, come i codici postali in cui alcune voci includono il suffisso ZIP+4 e altre no, è possibile utilizzare nuovamente la funzione stringa SINISTRA per riconoscere e abbinare solo i codici postali primari. Per fare in modo che Excel riconosca i valori numerici vicini ma non uguali, è possibile utilizzare la funzione ARROTONDA per arrotondare i valori di chiusura allo stesso numero e farli corrispondere.
- Gli spazi extra, come la digitazione di due spazi tra le parole invece di uno, possono essere rimossi utilizzando la funzione TRIM.
Passaggio 4. Creare colonne per la formula di confronto
Proprio come dovevi creare colonne per gli identificatori di elenco, dovrai creare colonne per la formula che esegue il confronto per te. Avrai bisogno di una colonna per ogni elenco.
Ti consigliamo di etichettare queste colonne con qualcosa come "Mancante?"
Passaggio 5. Immettere la formula di confronto in ogni cella
Per la formula di confronto, utilizzerai la funzione CONFRONTA annidata all'interno di un'altra funzione di Excel, ISNA.
- La formula assume la forma di "=ISNA(MATCH(G3, $L$3:$L$14, FALSE))", dove una cella della colonna identificatore del primo elenco viene confrontata con ciascuno degli identificatori nel secondo elenco per vedere se corrisponde a uno di loro. Se non corrisponde, manca un record e in quella cella verrà visualizzata la parola "VERO". Se corrisponde, il record è presente e verrà visualizzata la parola "FALSE". (Quando si inserisce la formula, non includere le virgolette allegate.)
- Puoi copiare la formula nelle celle rimanenti della colonna nello stesso modo in cui hai copiato la formula dell'identificatore di cella. In questo caso, cambia solo il riferimento di cella per la cella dell'identificatore, poiché mettere i simboli del dollaro davanti ai riferimenti di riga e colonna per la prima e l'ultima cella nell'elenco degli identificatori di seconda cella li rende riferimenti assoluti.
- È possibile copiare la formula di confronto per il primo elenco nella prima cella della colonna per il secondo elenco. Dovrai quindi modificare i riferimenti di cella in modo che "G3" venga sostituito con il riferimento per la prima cella identificativa del secondo elenco e "$L$3:$L$14" sia sostituito con la prima e l'ultima cella identificativa del secondo elenco. (Lascia stare i segni del dollaro e i due punti.) È quindi possibile copiare questa formula modificata nelle celle rimanenti nella riga di confronto del secondo elenco.
Passaggio 6. Ordinare gli elenchi per visualizzare più facilmente i valori non corrispondenti, se necessario
Se gli elenchi sono grandi, potrebbe essere necessario ordinarli per mettere insieme tutti i valori non corrispondenti. Le istruzioni nei passaggi seguenti convertiranno le formule in valori per evitare errori di ricalcolo e, se gli elenchi sono grandi, eviteranno lunghi tempi di ricalcolo.
- Trascina il mouse su tutte le celle di un elenco per selezionarlo.
- Selezionare Copia dal menu Modifica in Excel 2003 o dal gruppo Appunti della barra multifunzione Home in Excel 2007 o 2010.
- Selezionare Incolla speciale dal menu Modifica in Excel 2003 o dal pulsante a discesa Incolla nel gruppo Appunti della barra multifunzione di Excel 2007 o 2010s.
- Selezionare "Valori" dall'elenco Incolla come nella finestra di dialogo Incolla speciale. Fare clic su OK per chiudere la finestra di dialogo.
- Selezionare Ordina dal menu Dati in Excel 2003 o il gruppo Ordina e filtra della barra multifunzione Dati in Excel 2007 o 2010.
- Seleziona "Riga di intestazione" dall'elenco "Il mio intervallo di dati ha" nella finestra di dialogo Ordina per, seleziona "Mancante?" (o il nome che hai effettivamente dato all'intestazione della colonna di confronto) e fai clic su OK.
- Ripetere questi passaggi per l'altro elenco.
Passaggio 7. Confronta visivamente gli elementi non corrispondenti per vedere perché non corrispondono
Come notato in precedenza, Excel è progettato per cercare corrispondenze di dati esatte a meno che non lo si configuri per cercare quelle approssimative. La tua mancata corrispondenza potrebbe essere semplice come una trasposizione accidentale di lettere o cifre. Potrebbe anche essere qualcosa che richiede una verifica indipendente, come il controllo per vedere se le attività quotate dovevano essere segnalate in primo luogo.
Metodo 2 di 2: formattazione condizionale con CONTA. SE
Passaggio 1. Copia gli elenchi di dati su un singolo foglio di lavoro
Passaggio 2. Decidi in quale elenco desideri evidenziare i record corrispondenti o non corrispondenti
Se vuoi evidenziare i record in un solo elenco, probabilmente vorrai evidenziare i record univoci di quell'elenco; ovvero, record che non corrispondono a record nell'altro elenco. Se vuoi evidenziare i record in entrambi gli elenchi, ti consigliamo di evidenziare i record che corrispondono tra loro. Ai fini di questo esempio, supponiamo che il primo elenco occupi le celle da G3 a G14 e il secondo elenco occupi le celle da L3 a L14.
Passaggio 3. Selezionare gli elementi nell'elenco in cui si desidera evidenziare gli elementi unici o corrispondenti
Se desideri evidenziare gli elementi corrispondenti in entrambi gli elenchi, dovrai selezionare gli elenchi uno alla volta e applicare la formula di confronto (descritta nel passaggio successivo) a ciascun elenco.
Passaggio 4. Applicare la formula di confronto appropriata
Per fare ciò, dovrai accedere alla finestra di dialogo Formattazione condizionale nella tua versione di Excel. In Excel 2003, lo fai selezionando Formattazione condizionale dal menu Formato, mentre in Excel 2007 e 2010, fai clic sul pulsante Formattazione condizionale nel gruppo Stili della barra multifunzione Home. Seleziona il tipo di regola come "Formula" e inserisci la tua formula nel campo Modifica la descrizione della regola.
- Se vuoi evidenziare i record univoci del primo elenco, la formula sarebbe "=COUNTIF($L$3:$L$14, G3=0)", con l'intervallo di celle del secondo elenco reso come valori assoluti e il riferimento alla prima cella della prima lista come valore relativo. (Non inserire le virgolette chiuse.)
- Se vuoi evidenziare i record univoci del secondo elenco, la formula sarebbe "=COUNTIF($G$3:$G$14, L3=0)", con l'intervallo di celle del primo elenco reso come valori assoluti e il riferimento alla prima cella del secondo elenco come valore relativo. (Non inserire le virgolette chiuse.)
- Se vuoi evidenziare i record in ogni elenco che si trovano nell'altro elenco, avrai bisogno di due formule, una per il primo elenco e una per il secondo. La formula per il primo elenco è "=COUNTIF($L$3:$L$14, G3>0)", mentre la formula per il secondo elenco è COUNTIF($G$3:$G$14, L3>0)". annotato in precedenza, si seleziona il primo elenco per applicare la sua formula e quindi si seleziona il secondo elenco per applicare la sua formula.
- Applica la formattazione che desideri per evidenziare i record contrassegnati. Fare clic su OK per chiudere la finestra di dialogo.
Video - Utilizzando questo servizio, alcune informazioni potrebbero essere condivise con YouTube
Suggerimenti
- Invece di utilizzare un riferimento di cella con il metodo di formattazione condizionale COUNTIF, è possibile immettere un valore da cercare e contrassegnare uno o più elenchi per le istanze di quel valore.
- Per semplificare i moduli di confronto, puoi creare nomi per il tuo elenco, come "Lista1" e "Lista2". Quindi, quando si scrivono le formule, questi nomi di elenco possono sostituire gli intervalli di celle assoluti utilizzati negli esempi precedenti.
