Transcripción del video
En Excel tenemos disponibles varios métodos para comparar listas de manera que podamos conocer sus similitudes y diferencias.
A continuación, te mostraré los detalles de esos métodos.
Para nuestros ejemplos trabajaremos con dos listas de nombres, cada una con 100 elementos, pero estas listas no son idénticas, sino que hay elementos de la Lista1 que no están en la Lista2 y viceversa.
El primer método que exploraremos será usando funciones de Excel.
En la celda B2 ingresaré el símbolo igual y en seguida la función COINCIDIR.
Esta función nos permite buscar un elemento dentro de un rango y si lo encuentra nos devolverá su posición.
El primer argumento es el valor buscado, así que ingresaré la referencia a la celda A2 porque queremos buscar el nombre Alberto dentro de la Lista2.
El segundo argumento es el rango de celdas sobre el cual se realizará la búsqueda y que en nuestro caso será el rango de la Lista2.
Haré clic en la celda D2 y en seguida pulsaré la combinación de teclas Ctrl + Shift + Flecha abajo para seleccionar todas las celdas y terminaré con la tecla F4 para convertir la referencia en absoluta.
El tercer y último argumento de la función COINCIDIR nos permite elegir el tipo de búsqueda que se realizará.
En nuestro caso necesitamos una búsqueda exacta y por lo tanto elegiré el valor cero que es precisamente la coincidencia exacta.
Cierro el paréntesis de la función y al pulsar Enter, obtenemos el número 11 que es la posición donde se ha encontrado el nombre Alberto dentro de la Lista2.
Puedes ver que el nombre Alberto se encuentra en la fila 12 de la hoja, pero ya que el rango de búsqueda que hemos indicado a la función COINCIDIR inicia en la fila 2 de la hoja, entonces el nombre Alberto se encuentra en la posición 11 de dicho rango de búsqueda.
Para saber si los demás nombres de la Lista1 se encuentran en la Lista2, copiaré la fórmula hacia abajo haciendo doble clic en el controlador de relleno de la celda B2.
Puedes observar que Marcos, Encarnación y Alejandro muestran el error No Disponible lo cual nos indica que dichos nombres no se encuentran en la Lista2.
Al desplazarnos hacia abajo podemos observar que hay otros nombres que tampoco se han encontrado en la Lista2 ya que también muestran el error No Disponible.
Por el contrario, aquellos nombres donde se muestra un número, significa que la función COINCIDR los ha encontrado en esa posición dentro de la Lista2.
Recuerda que esta fórmula solo nos ayuda a saber aquellos elementos de la Lista1 que se encuentran en la Lista2 y si queremos saber el caso contrario, debemos crear otra fórmula para los elementos de la Lista2.
En la celda E2 ingresaré el símbolo igual y en seguida la función COINCIDIR.
El primer argumento será la referencia a la celda D2 que tiene el nombre Raquel y el segundo argumento será el rango de la Lista1 para saber si Raquel se encuentra en esa lista.
Haré clic en la celda A2 y pulsaré el atajo Ctrl + Shift + Flecha abajo para seleccionar todas las celdas de la Lista1 y terminaré pulsando la tecla F4 para convertir la referencia en absoluta.
Finalmente ingresaré el número cero como el tercer argumento de la función para hacer una búsqueda exacta y cierro el paréntesis y pulso Enter.
En seguida copiaré la fórmula hacia abajo y podremos ver que Aurora no se encuentra en la Lista1, ni tampoco Rodrigo ni Olga.
Para el resto de los nombres, la función COINCIDIR nos devuelve la posición en donde ha sido encontrado el nombre.
Si tienes listas muy grandes y quieres conocer rápidamente aquellos elementos que no se encontraron en la otra lista puedes agregar filtros a los datos con el atajo Ctrl + Shift + L y ya sea que aplicamos un filtro, o los podemos ordenar de Mayor a menor, y eso colocará las celdas con error en primer lugar y podremos saber fácilmente todos los elementos que no se encontraron en la otra lista.
Otra función que podemos usar para comparar los valores de las listas es la función BUSCARV y la diferencia con la función COINCIDIR es que, en lugar de obtener un número como resultado, obtendremos el mismo elemento de la lista.
Para demostrarlo, en la celda F2 ingresaré el símbolo igual y la función BUSCARV, el primer argumento será la referencia a la celda D2 que es el valor que vamos a buscar y el segundo argumento será el rango de la Lista1, así que haré clic en la celda A2 y pulsaré la combinación de teclas Ctrl + Shift + Flecha abajo y terminaré con la tecla F4 para convertir la referencia en absoluta.
El tercer argumento de la función BUSCARV es la columna que queremos obtener como resultado, pero en este caso nuestro rango de búsqueda, que es la Lista1, solo tiene una columna así que ingresaré el número 1 y para finalizar ingresaré el valor FALSO en el cuarto argumento de la función porque quiero una búsqueda exacta.
Cierro el paréntesis y pulso Enter y como resultado obtenemos el mismo nombre Raquel lo cual nos indica que la función BUSCARV ha encontrado ese nombre en la Lista1.
Al copiar la fórmula hacia abajo podrás observar que la función devuelve el error No disponible en aquellos nombres que no ha podido encontrar y que coinciden con los errores devueltos por la función COINCIDIR.
Es bueno conocer ambas funciones, pero la realidad es que mi método preferido para esta tarea es usar la función COINCIDIR.
Para continuar, activaré la Hoja2 donde tengo una copia de las listas anteriores y en este ejemplo vamos a usar el formato condicional para encontrar las diferencias entre ambas listas.
El primer paso es seleccionar los rangos de las dos listas y comenzaré activando la celda A2 y pulsando la combinación de teclas Ctrl + Shift + Flecha abajo para seleccionar todas las celdas de la Lista1.
Pulsaré el atajo Ctrl + Backspace para regresar a la parte superior de la pantalla donde se ve la celda activa y mantendré pulsada la tecla Ctrl para hacer clic en la celda D2 y de nuevo la combinación de teclas Ctrl + Shift + Flecha abajo para tener seleccionadas ambas celdas.
Si se te complica seguir estos pasos con los atajos de teclado que he utilizado, solo haz la selección usando el ratón asegurándote de mantener pulsada la tecla Control al momento de seleccionar la segunda lista.
Una vez que hemos hecho la selección de celdas, pulsaré el comando Formato condicional que se encuentra en la pestaña Inicio y dentro de “Reglas para resaltar celdas” elegiré la opción Valores duplicados.
De inmediato se resaltarán las celdas que se repiten en ambas listas, es decir, aquellos valores que aparecen en ambas listas, pero en este momento estoy interesado en conocer las diferencias, y por lo tanto debo elegir la opción Únicos dentro de la primera lista desplegable y finalmente pulsaré el botón Aceptar.
Aquellos elementos que han sido resaltados de un color diferente son los que no se repiten, es decir, son los valores que solo se encuentran en una de las listas.
Por el contrario, aquellos nombres que no tienen un color de relleno se debe a que están presentes en ambas listas.
Para observar todos los valores de una sola vez, activaré una celda de la Lista1 y pulsaré el atajo de teclado Ctrl + Shift + L para activar el filtro y en seguida elegiré la opción Ordenar por color y dentro de esta opción elegiré Ordenar por color de celda y tendré los valores de la Lista1 que no se han encontrado en la Lista2.
Activaré la Hoja1 del libro y puedes notar que son los mismos nombres que encontramos previamente con las fórmulas, es decir los nombres: Marcos, Encarnación, Alejandro, Manuela, Andrea y Rubén.
De esta manera el formato condicional nos ha ayudado a conocer rápidamente los valores que no se repiten en ambas listas.
En seguida activaré la Hoja3 para mostrarte el último método que tenemos disponible para hacer la comparación de dos listas.
El primer paso de este método es identificar adecuadamente con un número a los elementos de cada lista.
Para lograrlo, en la celda B2 ingresaré el número 1 y lo copiaré hacia abajo y de esta manera habremos identificado correctamente los elementos de la Lista1.
En seguida, en la celda E2 ingresaré el número 2 y lo copiaré hacia abajo y con esto hemos identificado los elementos de la Lista2.
El siguiente paso es activar una celda donde se mostrará el resultado de la consolidación de datos que vamos a realizar y para eso activaré la celda H1.
A continuación, iré a la pestaña Datos, y dentro del grupo Herramientas de datos, pulsaré el botón Consolidar y se abrirá un cuadro de diálogo.
En la lista de función dejaré activa la opción Suma y en seguida vamos a agregar las referencias hacia ambas listas incluyendo el número identificador que hemos agregado.
Activaré el recuadro Referencia y en seguida seleccionaré las celdas A1 y B1 y pulsaré la combinación de teclas Ctrl + Shift + Flecha abajo para seleccionar todos los elementos y en el cuadro de diálogo pulsaré el botón Agregar.
Haré lo mismo para la segunda lista: Seleccionaré las celdas D1 y E1 y pulsaré Ctrl + Shift + Flecha abajo y en seguida pulsaré el botón Agregar y ya tenemos listas las referencias a ambos rangos.
Finalmente activaré las cajas de selección de rótulos en fila superior y columna izquierda y pulsaré el botón Aceptar.
Como resultado obtenemos una nueva lista de nombres con un valor numérico a la derecha.
El comando Consolidar, ha recorrido cada uno de los elementos de ambas listas y ha sumado los valores de aquellos nombres que se repiten.
Por lo tanto, aquellos nombres que tienen el valor 3 se debe a que el comando Consolidar los ha encontrado en la Lista1 y en la Lista2 y por lo tanto ha sumado el número 1 y el número 2.
Por el contrario, aquellos nombres que tienen el número 2 es porque solo están presentes en la Lista2.
Y los nombres que tienen el número 1 es porque solo se han encontrado en la Lista1.
Pulsaré Ctrl + Shift + L para activar los filtros y voy a ordenar los datos por la columna Número de menor a mayor y tendremos los nombres que solo se encontraron en la Lista1.
Puedes comprobar que esos nombres son los mismos de nuestros ejemplos anteriores, tanto en la Hoja1 donde usamos fórmulas, como en la Hoja2 donde usamos el formato condicional.
Por debajo de estos nombres están aquellos que tienen el número 2 los cuales se han encontrado en la Lista2, pero no en la Lista1.
Y finalmente, el resto de los nombres tiene el número 3 porque están en ambas listas.
El comando Consolidar es un método muy rápido y efectivo especialmente en aquellos casos en donde tenemos más de 2 listas y queremos compararlas entre ellas.
Con esto he terminado con la revisión de los métodos disponibles para comparar dos listas en Excel.
El último ejemplo que te mostraré es solo para hacer una aclaración para esas ocasiones en las que queremos comparar varias columnas.
Como has visto en los ejemplos anteriores, solo hemos comparado valores en una sola columna, pero en la Hoja4 de este libro tengo una lista de nombres y apellidos que quiero comparar.
Los métodos que revisamos anteriormente no funcionarán sobre múltiples columnas, así que la solución es crear una columna auxiliar donde concatenaremos los valores que queremos comparar.
En la celda C2 ingresaré el símbolo igual y la referencia a la celda A2 seguida de un ampersand y la referencia a la celda B2.
Pulso Enter y copio la fórmula hacia abajo para concatenar los nombres y apellidos.
Haré lo mismo para la otra lista, en la celda H2 ingresaré el símbolo igual y la referencia a la celda F2 concatenada con la celda G2 y finalmente pulsaré Enter y copiaré la fórmula hacia abajo.
Ahora que ya tenemos lista la concatenación, puedes usar cualquiera de los métodos mostrados anteriormente sobre las columnas Auxiliares.
Yo usaré el formato condicional, así que seleccionaré el rango de celdas de la primera lista y manteniendo pulsada la tecla Control, seleccionaré la segunda lista.
En seguida iré a la pestaña Inicio y pulsaré el botón Formato condicional > Reglas para resaltar celdas > Valores duplicados y cambio la opción de la lista desplegable a Únicos y finalmente pulso el botón Aceptar.
Aquellos nombres que están resaltados de color rojo son los que no se han encontrado en la otra lista.
Te invito a revisar con detenimiento y practicar cada uno de los métodos que he mostrado en esta ocasión de manera que puedas usarlos correctamente en tus libros de Excel.