Metodi diversi per abbinare i dati in Excel
Esistono vari metodi per abbinare i dati in Excel, se vogliamo abbinare i dati nella stessa colonna diciamo che vogliamo verificare la duplicità possiamo usare la formattazione condizionale dalla scheda Home oppure se vogliamo abbinare i dati in due o colonne più diverse possiamo usare funzioni condizionali come la funzione if.
- Metodo n. 1: utilizzo della funzione Vlookup
- Metodo n. 2: utilizzo dell'indice e della funzione di corrispondenza
- Metodo n. 3 - Crea il tuo valore di ricerca
Ora esaminiamo in dettaglio ciascuno dei metodi
# 1 - Abbina i dati utilizzando la funzione CERCA.VERT
CERCA.VERT non viene utilizzato solo per ottenere le informazioni richieste dalla tabella dati; piuttosto, può essere utilizzato anche come strumento di riconciliazione. Quando si tratta di riconciliazione o abbinamento dei dati, la formula CERCA.VERT è in testa alla tabella.
Ad esempio, guarda la tabella sottostante.
Abbiamo due tabelle di dati qui, la prima è Data 1 e la seconda è Data 2.
Ora dobbiamo riconciliare se i dati nelle due tabelle corrispondono o meno. Il primo modo per abbinare i dati è la funzione SOMMA in Excel a due tabelle per ottenere le vendite totali.
Dati 1 - Tabella
Dati 2 - Tabella
Ho applicato la funzione SUM per entrambe le colonne Sale Amount della tabella. Nella fase iniziale stessa, abbiamo ottenuto la differenza di valori. La tabella Dati 1 mostra le vendite totali di 2,16,214 e la tabella Dati 2 mostra le vendite totali di 2,10,214 .
Ora dobbiamo esaminarlo in dettaglio. Quindi, applichiamo la funzione CERCA.VERT per ogni data.
Seleziona la matrice della tabella come intervallo Dati 1 .
Abbiamo bisogno dei dati della seconda colonna e l'intervallo di ricerca è FALSE, ovvero Corrispondenza esatta.
L'output è fornito di seguito:
Nella cella successiva, deduci il valore originale con il valore di arrivo.
Dopo aver dedotto, otteniamo il risultato pari a zero.
Ora copia e incolla la formula su tutte le celle per ottenere i valori di varianza.
Nella cella G6 e G12, abbiamo le differenze.
In Data 1, abbiamo 12104 per la data 04-mar-2019 e in Data 2, abbiamo 15104 per la stessa data, quindi c'è una differenza di 3000.
Allo stesso modo, per la data 18-Mar-2019 in Data 1, abbiamo 19351 e in Data 2, abbiamo 10351, quindi la differenza è 9000.
# 2 - Abbina i dati utilizzando la funzione INDICE + CONFRONTA
Per gli stessi dati, possiamo utilizzare la funzione INDICE + CONFRONTA. Possiamo usarlo come alternativa alla funzione CERCA.VERT.
La funzione INDICE viene utilizzata per ottenere il valore dalla colonna selezionata in base al numero di riga fornito. Per fornire il numero di riga, dobbiamo utilizzare la funzione CONFRONTA basata sul valore CERCA.
Apri la funzione INDICE nella cella F3.
Selezionare la matrice come intervallo di colonne dei risultati, ad esempio da B2 a B14.
Per ottenere il numero di riga, apri ora la funzione CONFRONTA come argomento successivo.
Seleziona il valore di ricerca come cella D3.
Quindi seleziona l'array di ricerca come colonna Data di vendita in Dati 1.
Nel tipo di corrispondenza, seleziona "0 - Corrispondenza esatta".
Chiudi due parentesi e premi il tasto Invio per ottenere il risultato.
Ciò fornisce anche lo stesso risultato di CERCA.VERT. Poiché abbiamo utilizzato gli stessi dati, abbiamo ottenuto i numeri così come sono
# 3 - Crea il tuo valore di ricerca
Ora abbiamo visto come abbinare i dati utilizzando le funzioni di Excel. Adesso vedremo i diversi scenari in tempo reale. Per questo esempio, guarda i dati seguenti.
Nei dati di cui sopra, abbiamo dati sulle vendite per zona e per data, come mostrato sopra. Dobbiamo ripetere il processo di corrispondenza dei dati. Applichiamo la funzione CERCA.VERT come nell'esempio precedente.
Abbiamo molte variazioni. Esaminiamo caso per caso.
Nella cella I5, abbiamo ottenuto la varianza di 8300. Diamo un'occhiata alla tabella principale.
Anche se il valore della tabella principale è 12104, abbiamo ottenuto il valore di 20404 dalla funzione CERCA.VERT. Il motivo è che CERCA.VERT può restituire il valore del primo valore di ricerca trovato.
In questo caso, il nostro valore di ricerca è una data, ovvero 20-Mar-2019. Nella cella sopra per la zona nord per la stessa data, abbiamo un valore di 20404, quindi CERCA.VERT ha restituito questo valore anche per la zona est.
Per superare questo problema, dobbiamo creare valori di ricerca univoci. Combina zona, data e importo delle vendite sia nei dati 1 che nei dati 2.
Dati 1 - Tabella
Dati 2 - Tabella
Ora abbiamo creato un valore univoco per ciascuna zona con il valore combinato di Zona, Data di vendita e Importo della vendita.
Usando questi valori univoci, applichiamo la funzione CERCA.VERT.
Applica la formula a tutte le celle; otterremo la varianza zero in tutte le celle.
In questo modo, utilizzando le funzioni di Excel, possiamo abbinare i dati e trovare le varianze. Prima di applicare la formula, è necessario esaminare i duplicati nel valore di ricerca per una riconciliazione accurata. L'esempio sopra è la migliore illustrazione dei valori duplicati nel valore di ricerca. In tali scenari, dobbiamo creare i nostri valori di ricerca univoci e arrivare al risultato.
