Trovare discrepanze in Excel - Spiegazione di 5 semplici tecniche

Quando si lavora con set di dati di grandi dimensioni in Microsoft Excel, è inevitabile riscontrare discrepanze nei dati. Identificare e correggere queste incoerenze è fondamentale per garantire rapporti e analisi accurati.

Le buone notizie? Excel viene fornito con una varietà di strumenti e tecniche per aiutarti a trovare e risolvere questi problemi. In questo articolo imparerai alcuni dei metodi più efficaci. Ciò include il filtraggio, la formattazione condizionale e l'utilizzo di formule avanzate.

Incorporando queste tecniche nel tuo flusso di lavoro quotidiano, diventerai più efficiente nella gestione e nell'analisi dei tuoi dati. In definitiva, porta a un migliore processo decisionale e a risultati migliori per i tuoi progetti.

Sommario

• Comprensione delle discrepanze in Excel
• 5 modi per trovare discrepanze in Excel
• # 1 - Trovare discrepanze tramite la revisione manuale in Excel
• #2 - Utilizzo del filtro in Excel per rilevare e correggere dati incoerenti
• 3# – Evidenziare le discrepanze con la formattazione condizionale
• #4 – Trovare le discrepanze con le funzioni avanzate di Excel
  • Funzioni SE e IS
  • CERCA VERT, CERCA ORIZZONTALE e CERCA X
  • INCONTRO
• #5 – Utilizzo dei componenti aggiuntivi di Excel per il rilevamento delle discrepanze
• Come creare regole di convalida dei dati per evitare discrepanze?
• La nostra ultima parola

Comprensione delle discrepanze in Excel

Le discrepanze nelle righe e nelle colonne possono derivare da errori di immissione umana, voci duplicate o irregolarità nella formattazione dei dati.

Dai un'occhiata ai seguenti dati. Noterai che il genere di Walter White e Wayne Cena è scritto male: Mael invece di Male. In effetti, è semplice notare e correggere gli errori in questo scenario. Ma questo non è il caso dei fogli di calcolo contenenti migliaia di righe e colonne.

Per trovare e gestire queste discrepanze, puoi utilizzare le seguenti funzionalità di Excel :

• Filtri: applica filtri a colonne specifiche per visualizzare valori univoci e individuare rapidamente dati incoerenti.
• Formattazione condizionale: utilizza le regole di formattazione per evidenziare le celle che soddisfano criteri specifici o dati non corrispondenti tra le colonne.
• Funzioni avanzate di Excel: utilizza funzioni come CONFRONTA e CERCA.VERT per confrontare i set di dati e identificare le discrepanze tra di loro.

Comprendendo come utilizzare queste funzionalità in Excel, puoi individuare e correggere in modo efficiente le discrepanze nei tuoi set di dati.

5 modi per trovare discrepanze in Excel

Ci sono 5 modi principali in cui ci piace affrontare le discrepanze in Excel:

1. Revisione manuale
2. Filtraggio
3. Formattazione condizionale
4. Funzioni avanzate di Excel
5. Componenti aggiuntivi di Excel

Esaminiamo ciascuno di essi in modo che tu possa elaborare il modo migliore per te.

# 1 - Trovare discrepanze tramite la revisione manuale in Excel

Quando si lavora con Excel , una revisione manuale può essere un metodo utile per identificare le discrepanze nei dati. Questo processo richiede un attento esame dei set di dati per individuare eventuali incoerenze o errori.

Ecco alcuni passaggi che puoi seguire per eseguire una revisione manuale:

• Inizia comprendendo chiaramente i set di dati con cui stai lavorando. Acquisisci familiarità con le righe, le colonne e la formattazione.
• Scorri i dati e presta molta attenzione alle informazioni presentate in ogni cella. Puoi utilizzare strumenti come l'evidenziazione delle celle con il colore di riempimento, la formattazione del testo o la formattazione condizionale per semplificare la lettura dei dati.
• Confronta una riga con un'altra o una colonna con un'altra. Cerca informazioni non corrispondenti, voci duplicate, ecc. che potrebbero causare imprecisioni nei tuoi fogli Excel.
• Man mano che identifichi le discrepanze, crea un elenco o utilizza i commenti per documentarle. Successivamente, questi possono servire come riferimenti importanti per correggere i problemi.

Quando si esaminano manualmente i dati per le discrepanze in un foglio di calcolo Excel, la pazienza è la chiave. Questo processo richiede tempo. Ma è un buon punto di partenza per identificare potenziali errori.

#2 - Utilizzo del filtro in Excel per rilevare e correggere dati incoerenti

Uno dei modi più semplici per rilevare dati incoerenti consiste nell'utilizzare il filtro di Excel .

Con Filtro, isoleremo i dati errati nella colonna Sesso nel seguente set di dati:

1. Selezionare Ordina e filtra nella scheda Home .

2. Scegli Filtro : assicurati che il cursore si trovi nell'intervallo dei dati.

3. Fare clic sulla freccia dell'elenco a discesa sulla colonna con dati incoerenti.

4. Ora, deseleziona tutti i dati corrispondenti corretti. Nel nostro caso, è Male . Quindi, fare clic su OK per salvare le modifiche.

5. Il tuo foglio di lavoro Excel ora mostrerà solo righe con valori errati. Per risolverli, fai clic su Trova e seleziona nella scheda Home .

6. Fare clic su Sostituisci quando viene visualizzato l'elenco a discesa.

7. Digitare il valore errato in Trova cosa e quello corretto in Sostituisci con . Quindi, fai clic su Sostituisci tutto .

8. Apparirà una finestra di dialogo che confermerà le modifiche. Fare clic su OK per continuare.

3# – Evidenziare le discrepanze con la formattazione condizionale

Un altro modo per identificare le differenze tra colonne e righe consiste nell'usare la formattazione condizionale.

Puoi accedere a regole come Evidenzia celle, Alto/Basso, Barre dati, Scale colore e Set ferro. Inoltre, puoi creare una nuova regola e gestire o cancellare le regole precedenti.

Useremo una tabella di esempio per mostrarti la potenza della formattazione condizionale:

1. Seleziona la tabella o le righe e le colonne preferite. Stiamo scegliendo la colonna A (Azienda) e la colonna B (Paese). Quindi, seleziona Formattazione condizionale nella scheda Home .

2. Selezionare un tipo di regola e quindi la regola. Stiamo selezionando Evidenzia regole cella e Valori duplicati...

3. Ora, dobbiamo selezionare se evidenziare i valori duplicati o unici e il colore di riempimento e il carattere per formattare le celle. Al termine, fare clic su OK .

4. Tutti i valori duplicati sono ora evidenziati con Riempimento rosso chiaro con testo rosso scuro.

#4 – Trovare le discrepanze con le funzioni avanzate di Excel

Funzioni SE e IS

Per confrontare le celle e identificare le differenze, puoi utilizzare le funzioni IF e IS.

Ad esempio, abbiamo utilizzato la formula =IF(A2=B2,"MATCH","NOT MATCHED")per confrontare le celle A2 e B2. E abbiamo trascinato la formula per applicarla ad altre celle. Quindi, celle identiche restituiscono MATCH altrimenti NOT MATCHED .

È possibile combinare IF con funzioni IS come ISNUMBER o ISTEXT per verificare la presenza di tipi di dati specifici. Ad esempio, la formula =IF(ISNUMBER(B2), "Number", "NaN")restituirà Numero nella colonna c se B2 è numerico e NaN in caso contrario.

CERCA VERT, CERCA ORIZZONTALE e CERCA X

Le funzioni VLOOKUP (Verticale) e HLOOKUP (Orizzontale) sono utili quando si confrontano valori in diverse colonne o righe di due tabelle. Per farlo, usa la seguente formula: =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) o =HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup]).

Sostituisci lookup_value con il valore che desideri trovare nell'altra tabella, table_array con l'intervallo di celle nella seconda tabella, col_index_num o row_index_num con il numero di indice della colonna o riga da cui desideri restituire un valore e [range_lookup] con FALSE per corrispondenze esatte o TRUE per una corrispondenza approssimativa.

Nel seguente tutorial, stiamo usando la funzione VLOOKUP sulla colonna E per confrontare i nomi dei dipendenti nella colonna A e nella colonna D per estrarre i loro stipendi dalla colonna B se viene trovata una corrispondenza.

CERCA.X è una versione aggiornata di CERCA.VERT e CERCA.ORIZZ disponibile su Excel 2021 ed Excel 365. Per la formula CERCA.X è: =XLOOKUP(lookup_value, lookup_array, return_array,[if_not_found]).

Sostituisci lookup_value con il valore che vuoi trovare nell'altra tabella, lookup_array con l'intervallo di celle da cercare nella seconda tabella, return_array con l'array da cui vuoi restituire un valore e [if_nout_found] con un valore di testo se non c'è corrispondenza si trovano i valori

Ecco un rapido esempio di CERCA.X con le stesse tabelle:

INCONTRO

La funzione CONFRONTA può essere utilizzata anche per confrontare due elenchi per le discrepanze.

La funzione CONFRONTA, =MATCH(lookup_value, lookup_array, [match_type]), ​​cerca un elemento specificato in un intervallo e restituisce la posizione relativa dell'elemento all'interno di tale intervallo.

Qui, stiamo trovando la posizione delle angurie nell'array A2:A8 usando MATCH. Stiamo usando match_type come 0 per trovare il primo valore che è esattamente uguale a lookup_value .

#5 – Utilizzo dei componenti aggiuntivi di Excel per il rilevamento delle discrepanze

Excel offre vari componenti aggiuntivi e strumenti integrati che consentono di rilevare e analizzare le discrepanze in modo più efficiente. In questa sezione, discuteremo alcuni di questi utili componenti aggiuntivi.

Per prima cosa, prendi in considerazione l'utilizzo del componente aggiuntivo Spreadsheet Inquire . Questo può aiutarti a confrontare due cartelle di lavoro ed evidenziare le differenze cella per cella. Per abilitare il componente aggiuntivo Informazioni foglio di lavoro:

1. Fare clic sulla scheda File .

2. Selezionare Opzioni , quindi fare clic sulla categoria Componenti aggiuntivi .

3. Nella casella Gestisci , selezionare Componenti aggiuntivi COM e fare clic su Vai .

4. Selezionare la casella Richiesta foglio di calcolo e fare clic su OK .

5. Una volta abilitato, accedere alla scheda Informazioni per utilizzare il comando Confronta file .

Un altro strumento utile per il rilevamento delle discrepanze è l' Analysis ToolPak . Questo componente aggiuntivo fornisce funzioni statistiche avanzate e strumenti di analisi dei dati, che possono essere utili durante l'analisi delle discrepanze.

Per abilitare Analysis ToolPak, seguire gli stessi passaggi dell'abilitazione del componente aggiuntivo Spreadsheet Inquire. Ma seleziona Componenti aggiuntivi di Excel nella casella Gestisci e seleziona la casella Strumenti di analisi .

In sintesi, Excel offre vari componenti aggiuntivi per aiutarti a rilevare e gestire le discrepanze nei dati. Puoi migliorare la precisione dei tuoi dati familiarizzando con questi strumenti e formule.

Come creare regole di convalida dei dati per evitare discrepanze?

Per creare regole di convalida dei dati in Excel che possono aiutare a prevenire discrepanze, attenersi alla seguente procedura:

1. Seleziona le celle che desideri limitare. Può trattarsi di una singola cella, di un intervallo di celle o di un'intera colonna.

2. Vai alla scheda Dati nella barra degli strumenti e fai clic sul pulsante Convalida dati (contrassegnato da due caselle orizzontali, un segno di spunta verde e un cerchio barrato rosso).

3. Nella finestra Convalida dati, assicurarsi di essere nella scheda Impostazioni . Qui avrai l'opportunità di definire i tuoi criteri di convalida in base alle tue esigenze.

Esistono vari tipi di criteri di convalida disponibili in Excel, alcuni dei quali includono:

• Numero intero: consente solo numeri interi all'interno di un intervallo specificato.
• Decimale: consente solo numeri decimali all'interno di un intervallo specificato.
• Elenco: limita l'input a un elenco predefinito di valori accettabili.
• Data: richiede date all'interno di un intervallo di date specifico.
• Orario: accetta solo orari all'interno di un determinato intervallo di tempo.
• Lunghezza del testo: impone vincoli sulla lunghezza del testo inserito.
• Personalizzato: consente di creare regole di convalida personalizzate utilizzando le formule di Excel.

Dopo aver selezionato i criteri di convalida appropriati, specificare i parametri secondo necessità. Ad esempio, se scegli Numero intero, dovresti impostare il minimo e il massimo con un validatore di intervallo di dati come tra, uguale a, minore di, ecc.

Oltre ai criteri di convalida, puoi anche impostare messaggi di errore personalizzati e suggerimenti di input per aiutare gli utenti a comprendere e rispettare le tue regole di convalida. Per fare ciò, passare alle schede Input Message e Error Alert per inserire i messaggi desiderati.

Implementando le regole di convalida dei dati, riduci in modo significativo la probabilità di incoerenze dei dati e migliora l'accuratezza complessiva dei tuoi fogli di lavoro Excel.

La nostra ultima parola

In questo articolo, hai appreso vari metodi per trovare e risolvere le discrepanze nei dati di Excel. Ricapitoliamo queste strategie chiave:

• Utilizzo degli elenchi a discesa Filtro nelle colonne per identificare valori univoci e dati incoerenti.
• Utilizzo della formattazione condizionale nelle celle selezionate per evidenziare le discrepanze utilizzando una serie di modelli di regole.
• Applicazione di funzioni Excel avanzate come CONFRONTA , CERCA.VERT , CERCA.X , ecc. per eseguire il controllo incrociato dei dati in due elenchi o tabelle.
• Utilizzo dei componenti aggiuntivi di Excel per rilevare le discrepanze.
• Impostazione delle regole di convalida dei dati per evitare errori nei dati

Con queste tecniche nel tuo set di competenze di Excel, i tuoi dati saranno più accurati e affidabili.

Ricorda di esercitarti regolarmente con questi metodi per diventare più efficiente nel rilevare discrepanze nei tuoi fogli di calcolo.

Continua ad affinare le tue competenze iscrivendoti alla nostra e continua a esplorare nuove caratteristiche e funzionalità per diventare ancora più esperto in Excel.