photosdematures.com

Diferents mètodes per fer coincidir dades a Excel

Hi ha diversos mètodes per fer coincidir les dades en Excel, si volem fer coincidir les dades de la mateixa columna, diguem que volem comprovar la duplicitat, podem utilitzar el format condicional des de la pestanya d'inici o bé si volem fer coincidir les dades en dos o a les columnes més diferents podem utilitzar funcions condicionals com ara if function.

• Mètode # 1: utilitzar la funció Vlookup
• Mètode 2: utilitzar la funció Índex + coincidència
• Mètode 3: creeu el vostre propi valor de cerca

Ara analitzem detalladament cadascun dels mètodes

Podeu descarregar aquesta plantilla Excel de dades de coincidència aquí: coincideix amb la plantilla Excel de dades

# 1: coincideix amb les dades mitjançant la funció VLOOKUP

VLOOKUP no només s’utilitza per obtenir la informació necessària de la taula de dades, sinó que també es pot utilitzar com a eina de conciliació. Quan es tracta de conciliar o fer coincidir les dades, la fórmula VLOOKUP lidera la taula.

Per obtenir un exemple, consulteu la taula següent.

Aquí tenim dues taules de dades, la primera és Dades 1 i el segon és Dades 2.

Ara hem de conciliar si les dades de dues taules són coincidents o no. La primera forma de fer coincidir les dades és la funció SUM a excel a dues taules per obtenir el total de vendes.

Dades 1 - Taula

Dades 2 - Taula

He aplicat la funció SUMA tant a la columna Import de venda de la taula. Al propi pas inicial, vam obtenir la diferència de valors. Dades 1 taula que mostra les vendes totals de 2,16,214 i Dades 2 taula que mostra les vendes totals de 2,10,214.

Ara cal examinar-ho en detall. Per tant, apliquem la funció VLOOKUP per a cada data.

Seleccioneu la matriu de taula com a Dades 1 abast.

Necessitem les dades de la segona columna i l’interval de cerca és FALS, és a dir, coincidència exacta.

La sortida es dóna a continuació:

A la cel·la següent deduïu el valor original amb el valor d'arribada.

Després de deduir obtenim el resultat com a zero.

Ara copieu i enganxeu la fórmula a totes les cel·les per obtenir els valors de la variància.

A les cel·les G6 i G12 obtenim les diferències.

En Dades 1 tenim 12104 per a la data del 4 de març de 2019 i el següent Dades 2 tenim 15104 per a la mateixa data, de manera que hi ha una diferència de 3000.

De la mateixa manera, per a la data del 18 de març de 2019 a Dades 1, tenim 19351 i en Dades 2 tenim 10351, de manera que la diferència és de 9000.

# 2: coincideix amb les dades mitjançant la funció INDEX + MATCH

Per a les mateixes dades, podem utilitzar la funció INDEX + MATCH. Podem utilitzar-ho com a alternativa a la funció VLOOKUP.

Funció INDEX que s'utilitza per obtenir el valor de la columna seleccionada en funció del número de fila proporcionat. Per proporcionar el número de fila, hem d’utilitzar la funció MATCH basada en el valor LOOKUP.

Obriu la funció INDEX a la cel·la F3.

Seleccioneu la matriu com a rang de columnes de resultats, és a dir, de B2 a B14.

Per obtenir el número de fila, obriu la funció MATCH ara com a argument següent.

Seleccioneu el valor de cerca com a cel·la D3.

A continuació, seleccioneu la matriu de cerca com a columna Data de venda a Dades 1.

Al tipus de coincidència, seleccioneu "0 - Coincidència exacta".

Tanqueu dos claudàtors i premeu la tecla Retorn per obtenir el resultat.

Això també dóna el mateix resultat que només VLOOKUP. Com que hem utilitzat les mateixes dades, hem obtingut els números tal com són

# 3: creeu el vostre propi valor de cerca

Ara hem vist com fer coincidir les dades mitjançant funcions Excel. Ara veurem els diferents escenaris del temps real. Per a aquest exemple, mireu les dades següents.

A les dades anteriors, disposem de dades de vendes en funció de la zona i de la data, tal com es mostra més amunt. Hem de tornar a fer el procés de concordança de dades. Apliquem la funció VLOOKUP segons l’exemple anterior.

Tenim moltes variacions. Examinem cada cas per cas.

A la cel·la I5 hem obtingut la variància de 8300. Vegem la taula principal.

Tot i que a la taula principal el valor és 12104, obtenim el valor de 20404 de la funció VLOOKUP. La raó és que VLOOKUP pot retornar el valor del primer valor de cerca trobat.

En aquest cas, el nostre valor de cerca és la data, és a dir, el 20 de març de 2019. A la cel·la anterior per a la zona nord de la mateixa data tenim un valor de 20404, de manera que VLOOKUP també ha retornat aquest valor per a la zona est.

Per superar aquest problema, hem de crear valors de cerca únics. Combina Zona, data i quantitat de vendes ambdós Dades 1 i Dades 2.

Dades 1 - Taula

Dades 2 - Taula

Ara hem creat un valor únic per a cada zona amb el valor combinat de Zona, Data de venda i Import de venda.

L’ús d’aquests valors únics permet aplicar la funció VLOOKUP.

Aplicar la fórmula a totes les cel·les, obtindrem la variància de zero a totes les cel·les.

Així, mitjançant l'ús de funcions Excel podem fer coincidir les dades i trobar les variàncies. Abans d’aplicar la fórmula, hem de mirar els duplicats del valor de cerca per obtenir una conciliació precisa. A l'exemple anterior es mostra la millor il·lustració de valors duplicats en el valor de cerca. En aquests casos, hem de crear els nostres propis valors de cerca únics i arribar al resultat.