Cómo quitar duplicados con una función de Excel

Si eres lector de este blog, seguramente recuerdas haber leído algún artículo para quitar duplicados de listas, ¿verdad? Ahora lo veremos desde una perspectiva distinta, porque quitaremos los duplicados con una función de Excel.

Que las siguientes secciones nos sirvan de repaso para ver las posibles formas de quitar duplicados:

1.ª forma: Botón de Excel

En este artículo de Gonduana:

Un ejemplo de comodines de Word y quitar los duplicados en Excel

vimos que hay una forma directa de borrar duplicados en Excel. Para ello, encuentra el botón Quitar duplicados, que está en cinta de Excel, ficha Datos y dentro del grupo Herramientas de datos:

Este procedimiento cambiaría esta lista:

a esta otra:

Si este es el resultado que quieres, porque no quieres distinguir mayúsculas de las minúsculas («Sun» y «SUN» son indistinguibles), entonces ya tienes la lista que necesitas.

Pero ¿y es no es lo que quieres?

Pues entonces hay otras formas. Sigue leyendo, por favor.

⁂

2.ª forma: Macro VBA

Vimos en el artículo:

Cómo borrar los duplicados de una lista productividad

que se podía ejecutar una macro de VBA para borrar los duplicados, pero manteniendo los que solo se diferencian por las mayúsculas y minúsculas.

No voy a repetir la macro, porque la tienes en el enlace anterior. Solo quiero apuntar que, si ejecutas esta macro, la lista cambiará así:

Como ves, tiene en cuenta las letras mayúsculas y minúsculas para diferenciar los términos «Sun» y «SUN».

⁂

3.ª forma: Función de Excel

La 3.ª forma tiene el mismo resultado que la 2.ª forma, es decir, que distingue entre «Sun» y «SUN» y mantiene ambos términos en la lista final. La diferencia es que aprovecha las funciones de Excel para conseguir el objetivo de borrar los duplicados.

Te estarás preguntando cuál es la fórmula para borrar los duplicados, ¿verdad?

Te la digo enseguida, pero te aviso de que no es una única fórmula, sino dos, y de que las funciones de Excel están anidadas.

Para quitar duplicados usaremos esta función de Excel:

=SI(SUMAPRODUCTO(--IGUAL(A2;$B$1:B1));"";A2)

☛ En función de la configuración de tu sistema operativo, es posible que necesites cambiar los puntos y comas de la fórmula por comas.

No te preocupes si no entiendes esta fórmula: En seguida la veremos paso a paso:

Cómo se usa la fórmula

Para usar la fórmula, vamos a suponer que tenemos la lista original de términos que queremos filtrar en la columna A de una hoja de Excel:

En la columna B esperamos tener la lista filtrada, sin los términos repetidos.

Atento ahora, que empieza lo bueno…

Es fácil entender que el primer término de una lista no puede estar repetido, ¿verdad?

Por este motivo, en la celda B1 escribimos lo siguiente:

=A1

Recuerda pulsar la tecla INTRO.

Hasta aquí, bien, ¿verdad?

En la celda B2 escribimos la fórmula larga que te di antes:

=SI(SUMAPRODUCTO(--IGUAL(A2;$B$1:B1));"";A2)

Antes de pulsar INTRO, tienes que tener algo muy parecido a:

☛ No desesperes con esta formulita: La analizaremos en breve.

Ahora solo tenemos que copiar esa celda B2 a toda la columna B (al menos hasta la última fila con datos de la columna A). Hay varias formas de hacer esto; aquí te muestro una de ellas:

Como ves, la columna B ya ha borrado los duplicados, que era lo que queríamos, ¿verdad?

No es magia… Todo tiene su sentido, así que en la siguiente sección se explicarán las funciones de Excel empleadas que han borrado los duplicados de la lista.

☛ La mejor forma de aprender a crear tus propias fórmulas de Excel es entendiendo la lógica de otras fórmulas ya creadas.

Explicación de la función de Excel

La fórmula de la celda B1 es muy sencilla y no necesita más explicación que la dada antes.

Para explicar la fórmula insertada en la columna B, te copio aquí la fórmula de la celda B7, un poco más abajo, para entender mejor lo que hace:

=SI(SUMAPRODUCTO(--IGUAL(A7;$B$1:B6));"";A7)

☛ La función no es mía, sino que la encontré en internet.
☛ Recuerda que en la columna B solo tendremos los valores sin repetición y analizaremos la fórmula de dentro a fuera.

Bien, empezamos:

1. Función IGUAL

La función IGUAL compara dos celdas. Si son iguales, devuelve el valor VERDADERO; si son distintas, la función devuelve FALSO. Lo interesante es que, como queríamos, esta función distingue entre «Sun» y «SUN».

Como puedes ver en la fórmula de la celda B7 de Excel, se compara el valor de la celda A7 con cada uno de los valores desde B1 hasta B6, es decir, hasta la fila justo anterior. Esto nos da una matriz de valores VERDADERO y FALSO que guarda Excel internamente.

2. Operador --

Tal y como está, una matriz de VERDADEROS y FALSOS no es de gran utilidad, así que con el operador -- (dos signos menos) indicamos a Excel que convierta los VERDADEROS al valor 1 (uno) y los FALSOS a 0 (cero).

☛ Este operador (--) puede resultar muy útil, así que recuerda lo que hace: Convertir los valores lógicos VERDADERO y FALSO a valores numéricos 1 y 0, respectivamente.

Así pues, Excel tiene en memoria una matriz de unos y ceros.

Seguimos con la fórmula…

3. Función SUMAPRODUCTO

La función SUMAPRODUCTO sumará todos los unos (1) de la matriz que Excel guardó con la operación más interna IGUAL.

¿Qué valor puede dar la función SUMAPRODUCTO?

• 1 si la celda A7 está duplicada;
• 0 si la celda A7 NO está duplicada.

4. Función SI

Por último, la función más externa, SI, comprueba si su primer parámetro es VERDADERO (es decir, si A7 está duplicada) y, si es así, vacía la celda B7 (segundo parámetro de la función SI). En cambio, si el primer parámetro es FALSO, eso significa la celda A7 no está duplicada y establece la celda B7 con el valor que tenga la celda A7 (tercer parámetro de la función SI).

Si lo piensas bien, con esta fórmula hemos conseguido una columna B sin duplicados.

Es verdad que la columna B tiene algunos huecos que deberás borrar, pero esa parte ya es fácil, ¿verdad?

⁂

Algunas consideraciones técnicas sobre la fórmula de Excel

La fórmula ya está explicada, ya sabes por qué funciona, que no es cuestión de magia, aunque quizás entenderla tiene su complicación.

Ahora te estarás preguntando «¿Qué otros detalles técnicos me puedes contar, Jesús?».

La respuesta, en la siguiente sección, querido lector.

Tiempos de cálculo

El detalle técnico que te quiero comentar está relacionado con el procesamiento que le vas a exigir a Excel. Para listas pequeñas, de hasta un millar de términos, Excel filtrará tu lista de términos en un santiamén.

Por ejemplo, según mis pruebas, una lista de 1000 términos se «limpia» de forma instantánea; una de 10.000 términos tarda unos 5 segundos en limpiarse. No es mucho tiempo, pero si hubiera 100.000 términos, Excel tardaría 10 minutos en borrar los duplicados, un tiempo que ya empieza a ser preocupante…

El motivo de este tiempo tan dilatado es que para números grandes, el cálculo no es más complicado, pero sí mucho más laborioso.

☛ Si tuviste pesadillas con las matemáticas en tu época de estudiante, puedes saltarte la siguiente sección; pero si te maravillan las implicaciones de los números enormes, puedes leer la breve sección a continuación.

Un poco de aritmética

Imagínate la línea 10.000: Excel tiene que comparar el valor de la celda A10000 con los 9.999 valores que tiene por encima de la columna B. Solo entonces podrá confirmar si A10000 es un duplicado o no.

A continuación, Excel lidiará con la celda A10001, con 10.000 comparaciones más y así sucesivamente.

Mientras está en este cálculo intensivo, Excel parecerá «colgado» y no podrás hacer nada con el libro de trabajo de Excel donde tienes la lista que quieres filtrar.

Para una lista de 100.000 términos, Excel deberá comparar valores del orden de 5.000.000.000 de veces. ¡Cinco mil millones de comparaciones!

Son números mu grandes, de ahí que Excel tarde unos minutos en hacer el cálculo.

☛ Se trata de una seria aritmética, de esas que estudiamos en secundaria, que no deja de crecer y crecer con el tamaño de la lista y que tiende a infinito.

Cuidadito, entonces, con los tiempos, porque si multiplicas por 2 la lista de términos, el tiempo de cálculo se multiplicará por 4.

Bueno, ya está la aritmética… No era para tanto, ¿verdad?

Conclusiones de los considerandos técnicos

Además de parecerte que Excel está «colgado» y que no responde mientras hace los cálculos, el procesador de tu ordenador estará al 100 % de su capacidad, por lo que, en general, el ordenador te irá más lento…

Además de la lentitud general, el ordenador consumirá más potencia mientras está haciendo los cálculos, así que cuidadito con los portátiles desconectados de la red eléctrica, ya que al funcionar solo con batería, la autonomía será menor a la habitual.

⁂

Funciones de Excel para quitar duplicados

⁂

Nada más, solo un par de recomendaciones si te interesan las funciones de Excel:

• Lee los otros artículos de Excel de este mismo blog. Los tienes todos si filtras por «Excel» en la nube de etiquetas (columna derecha del blog).
• Suscríbete al boletín del blog, o sígueme en LinkedIn o Twitter (@jssprt). De esta manera, serás el primero en enterarte de la publicación de los artículos.