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 della funzione indice + corrispondenza
- Metodo n. 3: crea il tuo valore di ricerca
Ora discutiamo in dettaglio ciascuno dei metodi
Puoi scaricare questo modello Match Data Excel qui - Match Data Excel Template# 1 - Abbina i dati utilizzando la funzione CERCA.VERT
CERCA.VERT non viene utilizzato solo per ottenere le informazioni richieste dalla tabella dati, ma 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.
Per un 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 in 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. Tabella dei dati 1 che mostra le vendite totali di 2,16,214 e tabella dei dati 2 che 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 dedurre 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 usando la funzione INDICE + CONFRONTA
Per gli stessi dati, possiamo utilizzare la funzione INDICE + CONFRONTA. Possiamo usarlo come alternativa alla funzione CERCA.VERT.
Funzione INDICE 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 la funzione CONFRONTA ora 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. Ora vedremo il diverso scenario del tempo reale. Per questo esempio, guarda i dati seguenti.
Nei dati sopra, abbiamo i dati di vendita 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 nella tabella principale il valore è 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 è la data, ovvero 20 marzo 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.
L'utilizzo di questi valori univoci consente di applicare la funzione CERCA.VERT.
Applicando la formula a tutte le celle, otterremo la varianza zero in tutte le celle.
In questo modo, utilizzando le funzioni excel possiamo abbinare i dati e trovare le varianze. Prima di applicare la formula, dobbiamo 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.
