fbpx
Trucos Excel Comparar y marcar dos tablas de datos en excel
23
septiembre
2020

Comparar listas, valores de una lista que no están en otra lista

En este Blog aprenderemos a cómo marcar artículos de una tabla que no están en otra tabla.

Es muy normal que en una hoja de Excel tengamos una lista de artículos y, en una nueva lista, bien sea porque la hayamos importado, o copiado de otro libro, o dichos artículos los introdujese un compañero, quisiera saber qué artículos de esta lista son nuevos, es decir, no están en la otra lista.

Para ello deberemos comparar las dos listas, buscando los elementos de la Lista 2 que no están en la Lista 1, aplicando, finalmente, el formato condicional.

Sabemos que Excel tiene una opción automática de eliminar filas repetidas, pero no una opción automática de buscar los que no están en otra lista.

Herramienta de Excel Quitar Duplicados

Acuérdate que, para eliminar las filas repetidas, debemos acceder a la opción Datos > Quitar duplicados.

excel quitar duplicados marcar articulos de una tabla

Como no hay una herramienta automática, recurriremos a las diferentes funciones y herramientas que nos suministra Excel, para lograr el objetivo.

Para ello utilizaremos las funciones EsError(), Coincidir() y la herramienta formato condicional.

En el anterior blog ya vimos la utilización de la función Coincidir(). Por esto, me centraré en comentar la función EsError() y la herramienta Formato condicional.

Función de Excel EsError()

Nos ayuda a comprobar si un valor introducido en la celda es un error, y nos devuelve el valor VERDADERO o FALSO.  El valor evaluado puede ser una celda o una fórmula cuyo resultado será evaluado.

Sintaxis de la función EsError()

=EsError(Valor)
Valor (Obligatorio)
Es el valor que se desea evaluar.

Ejemplo función EsError()

En una hoja de cálculo hemos introducido dos fórmulas que nos generan dos tipos de errores:

  • Dividir por cero, nos aparecerá #¡DIV/0!

En la celda D2, la fórmula =EsError(B2), nos dá Verdadero, porque es cierto que la fórmula contiene un error. Estamos intentando dividir 4/0 en la celda B2.

01 funcion excel eserror

En la celda D3, la fórmula =EsError(B3), da como resultado Falso, ya que la expresión introducida en la celda B3 =4/2, da 2.  Por lo tanto, es Falso que tenga error.

03 funcion de excel eserror

Error de nombre de fórmula # ¿NOMBRE?

Otro tipo de error es que introduzcamos mal el nombre de la fórmula. En la celda D4, la fórmula =Es Error(B4), devolverá Verdadero, ya que es cierto que la función está mal escrita (=Sumar(4;7)).

02 funcion excel eserror

En la celda B5, hemos corregido la fórmula:

04 funcion de excel eserror

En consecuencia, la función =Es Error(B5), devuelve Falso, porque la celda no contiene error.

Podemos deducir que, si la celda contiene cualquier tipo de error, la función EsError() devuelve Verdadero. Falso en caso contrario.

Si estás interesado en ampliar tus conocimientos sobre todo lo relacionado con Excel visita nuestro Curso de Excel

A comparar Listas

Lo que pretendemos es pintar, con un color de fondo, las celdas de la Lista 2, cuyo valor no esté presente en la Lista 1. Con lo visto anteriormente te será muy fácil.

Partiremos de la Lista1, que contiene una serie de libros, con su código correspondiente:

01 comparar listas

Y en una nueva lista (Lista2), tenemos dichos códigos de libros y algunos de nuevos que hemos añadido.

Queremos saber cuáles de estos libros son nuevos, es decir, los libros que no están en la otra lista, y que nos los marque:

02 comparar listas excel

Herramienta de Excel Formato condicional

Para ello recurriremos a la herramienta formato condicional.

Herramienta de Excel Formato Condicional

Seleccionamos la Lista 2, y accedemos a Inicio > Formato condicional > Nueva regla

02 herramienta de excel formato condicional nueva regla

Nos aparecerá la siguiente ventana:

03 Herramienta de formato condicional

A través de esta ventana podemos asignar un color a un conjunto de celdas que cumplen una condición.

Para ello en la ventana Seleccionar un tipo de regla, accedemos a Utilice una fórmula que determine las celdas para aplicar formato. Nos permitirá dar formato a los valores donde esta fórmula sea verdadera.

Acuérdate que la función EsError() nos daba Verdadero cuando el argumento de la función daba error.

Introducimos la fórmula:

=ESERROR(COINCIDIR(F4;$B$4:$B$16;0))

Clicando en el botón Formato, seleccionamos el color que queremos aplicar a las celdas que cumplen la condición.

cambiando formato de celda excel formato condicional

Si estás interesado en ampliar tus conocimientos sobre todo lo relacionado con Excel visita nuestro Curso de Excel

Remarco los pasos importantes para entender el ejercicio:

+ La primera función a estudiar es Coincidir()

= COINCIDIR(F4;$B$4:$B$16;0)

  • F4, la primera celda de la Lista 2, donde hay el primer código de libro a buscar.
  • $B$4:$B$16 es el rango donde vamos a buscar, para saber si encuentra dicho código. En nuestro caso los códigos de libros de la Lista 1.
  • 0 es la coincidencia, en nuestro caso la lista está ordenada, pero, por si en algún momento cambiamos el código de algún libro y, ya no sigue el orden, el algoritmo funcione igualmente.

+ Si el código de libro no está en la Lista 2, nos va a dar error. En este caso quiero que me pinte (marque) dicha celda. Por esto, añadimos en la formula la función EsError. Si nos da error la función, es verdadero y le digo píntame la celda.

+ Nos dará error cuando no encuentre el código de libro en la Lista 2

+ Podemos observar que ha marcado correctamente los códigos de los libros que no están en la Lista 1. En nuestro caso los códigos de libros correspondientes son el 5, 8, 10, 36 y 48.

Resultado final de la comparación de los diferentes códigos de artículos de las tablas:

tablas excel como marcar articulos de una tabla que no estan en otra tabla

Visualizar sólo los que no están en la otra lista usando los filtros por color de Excel

Para ver los que no están en otra lista, podemos filtrarla por color y visualizar sólo los que tienen un color determinado. Interesante si la lista de artículos es muy extensa. Los pasos para conseguirlo son:

Datos > filtro

visualizar solo los que no estan en otra lista excel

Seleccionamos Filtrar por Color > Filtrar por color de celda. Escogemos el color anaranjado de color de relleno, como criterio de filtro.

14 excel como marcar articulos de una tabla que no estan en otra tabla

Apareciendo, sólo los registros que están pintados con este color. Podemos ver que está filtrado por la columna Código de libro, por el icono del embudo.

15 excel como marcar articulos de una tabla que no estan en otra tabla

De esta manera hemos visto como marcar artículos de una lista que no están en la otra lista, y hemos aprovechado el artículo para repasar funciones que se utilizan mucho en Excel, como Coincidir, EsError y formato condicional.

Seguro que es una técnica que vas a utilizar en infinidad de ocasiones en tu trabajo y te será de gran utilidad.

Descargar archivo de ejemplo: Marcar_los_que_no_están.xlsx

Si estás interesado en ampliar tus conocimientos sobre todo lo relacionado con Excel visita nuestro Curso de Excel

Author

Jordi Llort

Profesor de Máster en Programación .NET . Máster de Gestión digital de Empresas, Máster de Videojuegos y SAP. Formador en CIPSA, con más de 30 años de experiencia.

Deja una respuesta

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *

Recibe de primero nuestras ofertas de empleo y noticias