Comparación de tablas

Descargar el fichero: duplicados.xlsx

Más de una vez se habrá encontrado con la necesidad de comparar los datos de una tabla con los de otra. Seguramente habrá necesitado conocer que datos de un listado se encontraban repetidos. En esta ocasión vamos a estudiar cómo comparar datos.

Hoja1

Desde la celda B4 hasta la B24 introducimos la fórmula:

=ALEATORIO.ENTRE(1;50)

Esto genera números aleatorios entre 1 y 50, sin decimales. Si esta función te da error es posible que no tengas habilitados los complementos de "Herramientas para análisis".

Como son números aleatorios, cada vez que se pulsa la tecla F9 de recalculo manual o se escribe en cualquier celda, se recalcula toda la hoja, y por tanto varían los valores numéricos.

Pretendemos señalar con algún color los valores de la lista que se encuentran repetidos.

En el ejemplo de la imagen anterior observamos que se encuentran repetidos los números 27 y 39. El 27 está dos veces y el 39 está tres veces.

Para resaltar los repetidos con un cierto color utilizamos Formato Condicional. Nos situamos en la primera celda (B4) y en el Formato Condicional establecemos la siguiente fórmula:

=CONTAR.SI($B:$B;B4)>1

recordar que los argumentos de la función son:

=CONTAR.SI(rango;criterio)

Lo que le estamos diciendo es que cuente los valores de la columna B que sean iguales a la propia celda B4. Y luego pedimos que se analice si son o no mayores que 1. Si efectivamente fueran mayores que uno querría decir que ese valor numérico se encuentra repetido, y en ese caso quedaría marcado de color verde.

Hoja2

En al versión de Excel 2007 se ha potenciado bastante el tema del Formato Condicional en comparación con las versiones anteriores. En la Hoja2 realizamos algo similar a lo que hicimos en la Hoja1 pero en esta ocasión con una de las nuevas opciones de Formato Condicional que se encuentra en Excel 2007.

Excel 2007 dispone de una opción que permite marcar los valores duplicados.

Hoja3

Es una variante de la Hoja1. En este caso, cuando tenemos dos celdas con valores iguales únicamente deseamos que quede marcada con un color la segunda. Su hubiera tres iguales únicamente deseamos marcar la segunda y la tercera. Dicho de otro forma, cuando existen repeticiones no deseamos marcar el primero de los valores, únicamente los que se encuentran verdaderamente repetidos.

Con los mismos datos que los de la Hoja1 observamos que se repiten los mismo números: el 27 y el 39, pero en este caso, el 27 se encuentra coloreado únicamente la segunda vez dentro del listado, y el número 39 se encuentra coloreado únicamente la veces segunda y tercera.

La fórmula que hemos tenido que pone en la celda B4 es una variante de la anterior:

=CONTAR.SI($B$4:B4;B4)>1

Observar que en esta ocasión el rango ya no es toda la columna B, sino el rango $B$4:B4. Se ha marcado el primer B4 con dólares y el segundo B4 sin dólares para que al extender el formato al resto de las celdas, en cada caso, únicamente se analice como rango la celda en la que nos enontramos y las previas, pero no así las posteriores.

Hoja4

Dadas dos tablas (tabla1 y tabla2) deseamos detectar las diferencias entre ambas. Creamos la tabla 3 para detectar las diferencias. La fórmula de la celda AF6 es:

=--NO(B6=Q6)

Si hubiéramos puesto la fórmula =B6=Q6 Excel nos respondería poniendo VERDADERO o FALSO según que el valor de ambas celdas fuera el mismo o distinto. Sabemos que en informática los verdaderos son unos y los falsos son ceros. Podemos convertir un verdadero en un 1 y un falso en un 0, simplemente multiplicando por 1. Así, forzaríamos a que el resultado fuera numérico, bien 0 o bien 1.

 =(B6=Q6)*1

Otro método para conseguir convertir una expresión lógica de igualdad o desigualdad en numérica es anteponer dos signos menos.

=--(B6=Q6)

Uno de los signos menos equivale a multiplicar por -1, lo cual consigue que el resultado sea numérico, pero los valores obtenidos serían 0 para FALSO, y -1 para VERDADERO. Anteponiendo el otro signo menos conseguimos volver a multiplicar por -1, con lo que el resultado ahora ya es o bien 0, o bien 1.

Puesto que pretendemos determinar cuantos valores descuadran entre las dos tablas, nos interesa que el resultado sea 0 en caso de que los valores sean iguales y 1 en caso de que sean distintos. Así, sumando el número de unos habremos calculado el número de descuadres existentes.

Para conseguir esto podríamos poner

=--(B6<>Q6)

o bien, introducir la función lógica NO.

=--NO(B6=Q6)

Para calcular el número de descuadres por el método 1 sumamos todos los valores de la tabla3.

El método 2 no necesita crear la tabla3 y directamente mediante una fórmula matricial consigue sumar los descuadres entre las tablas 1 y 2.

En la celda AV8 la fórmula que se aplica es:

=SUMA(--NO(tabla1=tabla2))

observar que es una fórmula matricial. Esto se sabe porque al mirar la celda AV8 se ven unas llaves {} que engloban la fórmula.

Las fórmulas matriciales no se validan pulsando ENTER. Es necesario pulsar simultáneamente las 3 teclas siguientes:

CONTROL + MAYÚSCULAS + ENTER