¿Te ha pasado alguna vez que, entre hojas y hojas de datos en Excel, te encuentras con información duplicada que complica tu análisis? ¡No estás solo! La repetición de datos puede convertirse en un verdadero dolor de cabeza, sobre todo cuando se trata de informes importantes o de la gestión de grandes bases de datos. Afortunadamente, Excel nos ofrece herramientas poderosas que nos permiten buscar y resaltar esos duplicados de manera fácil y rápida. En este artículo, descubrirás paso a paso cómo identificar y eliminar esas molestas repeticiones para que tus hojas de cálculo sean más limpias y eficaces. Prepárate para dominar Excel como un experto y optimizar tu flujo de trabajo de manera sorprendente. ¡Comencemos!
Excel es una herramienta muy poderosa que nos ayuda a organizar y analizar datos de manera efectiva. Sin embargo, a medida que trabajamos con grandes cantidades de información, es posible que nos encontremos con duplicados, lo cual puede complicar nuestra labor. Afortunadamente, Excel cuenta con una serie de funciones y herramientas que nos permiten buscar y resaltar duplicados de forma rápida y sencilla. En este artículo, te mostraremos paso a paso cómo utilizar estas funciones para optimizar tus tareas y conseguir resultados precisos. ¡No te lo pierdas!
Usando la regla incorporada, puede resaltar duplicados en una columna o en varias columnas como se muestra en la siguiente captura de pantalla:
>
Nota. Al aplicar la regla de duplicación incorporada a dos o más columnas, Excel no compara los valores en esas columnas, simplemente resalta todas las instancias duplicadas en el rango. Si desea encontrar y resaltar coincidencias y diferencias entre 2 columnas, siga los ejemplos del tutorial vinculado anteriormente.
Cuando utilice la regla incorporada de Excel para resaltar valores duplicados, tenga en cuenta las dos cosas siguientes:
- Funciona sólo para células individuales. A resaltar filas duplicadasnecesitaría crear sus propias reglas basadas en valores en una columna específica o por comparar valores en varias columnas.
- Sombrea las celdas duplicadas, incluidas sus primeras apariciones. Para resaltar todos los duplicados excepto primeras instanciascree una regla de formato condicional basada en una fórmula como se explica en el siguiente ejemplo.
Cómo resaltar duplicados sin 1calle ocurrencias
Para resaltar 2Dakota del Norte y todas las apariciones duplicadas posteriores, seleccione las celdas que desea colorear y cree una regla basada en fórmulas de esta manera:
Sobre el Hogar pestaña, en la Estilos grupo, haga clic Formato condicional > Nueva regla > Usar una fórmula para determinar qué celdas formatear.En el Dar formato a los valores donde esta fórmula es verdadera cuadro, ingrese una fórmula similar a esta:
=COUNTIF($A$2:$A2,$A2)>1 Donde A2 es la celda superior del rango seleccionado.
Haga clic en el Formato… y seleccione el color de relleno y/o fuente que desee.Finalmente, haga clic DE ACUERDO para guardar y aplicar la regla.
>Si no tiene mucha experiencia con el formato condicional de Excel, encontrará los pasos detallados para crear una regla basada en fórmulas en el siguiente tutorial: Formato condicional de Excel basado en otro valor de celda.
Como resultado, las celdas duplicadas excluyendo las primeras instancias se resaltarán con el color que elijas:
>Cómo mostrar el tercer, cuarto y todos los registros duplicados posteriores
Para ver duplicados que comienzan con la enésima aparición, cree una regla de formato condicional basada en la fórmula como en la ejemplo anterior, con la única diferencia de que reemplazas >1 al final de la fórmula con el número requerido. Por ejemplo:
Para resaltar 3tercero y todas las instancias duplicadas posteriores, cree una regla de formato condicional basada en esta fórmula:
=COUNTIF($A$2:$A2,$A2)>=3
Para sombrear4th y todos los registros duplicados posteriores, utilice esta fórmula:
=COUNTIF($A$2:$A2,$A2)>=4
Para resaltar sólo ocurrencias específicas, utilice el igual a operador (=). Por ejemplo, para resaltar solo 2Dakota del Norte En algunos casos, utilizarías esta fórmula:
=COUNTIF($A$2:$A2,$A2)=2
Cómo resaltar duplicados en un rango (varias columnas)
Cuando desee comprobar si hay duplicados en varias columnas, no comparando las columnas entre sí, sino encontrar todas las instancias del mismo elemento en todas las columnas, utilice una de las siguientes soluciones.
Resalte duplicados en varias columnas, incluida 1calle ocurrencias
Si la primera instancia de un elemento que aparece en el conjunto de datos más de una vez se considera un duplicado, la forma más sencilla de hacerlo es utilizar la regla integrada de Excel para duplicados.
O cree una regla de formato condicional con esta fórmula:
CONTAR.SI(rango, celda_superior)>1
Por ejemplo, para resaltar duplicados en el rango A2:C8, la fórmula es la siguiente:
=COUNTIF($A$2:$C$8, A2)>1
Observe el uso de referencias de celda absolutas para el rango ($A$2:$C$8) y referencias relativas para la celda superior (A2).
>Resalte duplicados en varias columnas excepto 1calle ocurrencias
La solución para este escenario es mucho más complicada, no es de extrañar que Excel no tenga una regla integrada para ello 🙂
Para resaltar entradas duplicadas en varias columnas ignorando el 1calle ocurrencias, tendrás que crear 2 reglas con las siguientes fórmulas:
Regla 1. Se aplica a la primera columna.
Aquí usas exactamente la misma fórmula que usamos para resaltar duplicados sin 1calle ocurrencias en una columna (los pasos detallados se pueden encontrar aquí).
En este ejemplo, estamos creando una regla para A2:A8 con esta fórmula:
=COUNTIF($A$2:$A2,$A2)>1
Como resultado, los elementos duplicados sin 1calle Las apariciones se resaltan en la columna más a la izquierda del rango (en nuestro caso, solo hay un elemento de este tipo):
>Regla 2. Se aplica a todas las columnas siguientes.
Para resaltar duplicados en las columnas restantes (B2:C8), utilice esta fórmula:
=COUNTIF(A$2:$A$8,B2)+COUNTIF(B$2:B2,B2)>1
En la fórmula anterior, la primera función CONTAR.SI cuenta las apariciones de un elemento determinado en la primera columna, y la segunda función CONTAR.SI hace lo mismo para todas las columnas posteriores. Y luego, sumas esos números y compruebas si la suma es mayor que 1.
Como resultado, todos los elementos duplicados, excepto su 1calle Las ocurrencias se encuentran y resaltan:
>Resalte duplicados en todas las columnas con una sola regla
Otra posible solución es agregar una columna vacía a la izquierda de su conjunto de datos y combinar las fórmulas anteriores en una sola fórmula como esta:
=IF(COLUMNS($B2:B2)>1,COUNTIF(A$2:$B$8,B2),0) + COUNTIF(B$2:B2,B2)>1
Donde B2 es la celda superior con datos en el 2Dakota del Norte columna del rango objetivo.
>Para comprender mejor la fórmula, dividámosla en 2 partes principales:
- Para la primera columna (B), la condición SI nunca se cumple, por lo que solo se calcula la segunda función CONTAR.SI (hemos usado esta fórmula para buscar duplicados excepto las primeras apariciones en una columna).
- Para todas las columnas siguientes (C2:D8), el punto clave es el uso inteligente de referencias absolutas y relativas en las dos funciones CONTAR.SI. Para que sea más fácil de entender, lo he copiado en la columna G, para que puedas ver cómo cambia la fórmula cuando se aplica a otras celdas:
>
Debido a que la condición IF siempre es VERDADERA para todas las columnas excepto la primera (el número de columnas es mayor que 1), la fórmula procede de esta manera:
- Cuenta el número de apariciones de un elemento determinado (D5 en la captura de pantalla anterior) en todas las columnas a la izquierda de la columna dada: COUNTIF(B$2:$C$8,D5)
- Cuenta el número de apariciones de un elemento determinado en la columna del elemento, hasta la celda del elemento: COUNTIF(D$2:D5,D5)
- Finalmente, la fórmula suma los resultados de ambas funciones CONTAR.SI. Si el número total es mayor que 1, es decir, si hay más de una aparición del elemento, se aplica la regla y el elemento se resalta.
Resaltar filas enteras basadas en valores duplicados en una columna
Si su tabla contiene varias columnas, es posible que desee resaltar filas enteras basadas en registros duplicados en una columna específica.
Como ya sabes, Excel regla incorporada para duplicados Funciona sólo a nivel celular. Pero una regla personalizada basada en fórmulas no tiene problemas para sombrear filas. El punto clave es seleccione las filas enterasy luego cree una regla con una de las siguientes fórmulas:
- Para resaltar filas duplicadas excluyendo 1calle ocurrencias:
=COUNTIF($A$2:$A2, $A2)>1 - Para resaltar filas duplicadas incluyendo 1calle ocurrencias:
=COUNTIF($A$2:$A$15, $A2)>1
Donde A2 es la primera celda y A15 es la última celda utilizada en la columna que desea verificar en busca de duplicados. Como puede ver, el uso inteligente de referencias de celda absolutas y relativas es lo que marca la diferencia.
La siguiente captura de pantalla muestra ambas reglas en acción:
>Cómo resaltar filas duplicadas en Excel
El ejemplo anterior ha demostrado cómo colorear filas enteras basándose en valores duplicados en una columna específica. Pero, ¿qué sucede si desea ver filas que tienen valores idénticos en varias columnas? O, ¿cómo se resaltan filas duplicadas absolutas, aquellas que tienen valores completamente iguales en todas las columnas?
Para ello, emplee la función CONTAR.SI que permite comparar celdas según múltiples criterios. Por ejemplo, para resaltar filas duplicadas que tienen valores idénticos en las columnas A y B, utilice una de las siguientes fórmulas:
- Para resaltar filas duplicadas excepto 1calle ocurrencias:
=COUNTIFS($A$2:$A2, $A2, $B$2:$B2, $B2)>1 - Para resaltar filas duplicadas con 1calle ocurrencias:
=COUNTIFS($A$2:$A$15, $A2, $B$2:$B$15, $B2)>1
La siguiente captura de pantalla demuestra el resultado:
>Como comprenderá, el ejemplo anterior es sólo para fines de demostración. Al resaltar filas duplicadas en sus hojas de la vida real, naturalmente no está limitado a comparar valores solo en 2 columnas, la función CONTAR.SI puede procesar hasta 127 pares de rango/criterio.
Resaltar celdas duplicadas consecutivas en Excel
A veces, es posible que no necesite resaltar todos los duplicados en una columna, sino mostrar solo las celdas duplicadas consecutivas, es decir, las que están una al lado de la otra. Para hacer esto, seleccione las celdas con datos (sin incluir el encabezado de la columna) y cree una regla de formato condicional con una de las siguientes fórmulas:
- Para resaltar duplicados consecutivos sin 1calle ocurrencias:
=$A1=$A2 - Para resaltar duplicados consecutivos con 1calle ocurrencias:
=OR($A1=$A2, $A2=$A3)
La siguiente captura de pantalla muestra cómo resaltar textos duplicados consecutivos, pero estas reglas también funcionarán para números y fechas duplicados consecutivos:
>Si su hoja de Excel puede tener filas vacías y no desea que se resalten las celdas en blanco consecutivas, realice las siguientes mejoras en las fórmulas:
- Para resaltar celdas duplicadas consecutivas sin 1calle ocurrencias y ignorar celdas en blanco:
=AND($A2<>»», $A1=$A2) - Para resaltar celdas duplicadas consecutivas con 1calle ocurrencias y ignorar celdas en blanco:
=AND($A2<>»», OR($A1=$A2, $A2=$A3))
Como puede ver, no es gran cosa resaltar duplicados en Excel usando formato condicional. Sin embargo, existe incluso una forma más rápida y sencilla. Para descubrirlo, lea la siguiente sección de este tutorial.
Cómo resaltar duplicados en Excel con Duplicate Remover
El complemento Duplicate Remover es la solución todo en uno para tratar registros duplicados en Excel. Puede buscar, resaltar, seleccionar, copiar o mover celdas duplicadas o filas duplicadas enteras.
A pesar de su nombre, el complemento puede rápidamente resaltar duplicados en diferentes colores sin borrar a ellos.
Duplicate Remover agrega 3 características nuevas a su cinta de Excel:
- Tabla de deduplicación – para buscar y resaltar inmediatamente duplicados en una tabla.
- Eliminador de duplicados – asistente paso a paso con opciones avanzadas para identificar y resaltar duplicados o valores únicos en 1 tabla.
- Comparar 2 tablas – busque y resalte duplicados comparando dos columnas o dos tablas separadas.
Después de instalar Ultimate Suite para Excel, encontrará estas herramientas en la página Datos de Ablebits pestaña en el Deduplicar grupo:
>Resaltar duplicados en Excel con un par de clics
Para este ejemplo, creé la siguiente tabla con unos cientos de filas. Y nuestro objetivo es resaltar las filas duplicadas que tienen valores iguales en las tres columnas:
>Lo creas o no, puedes obtener el resultado deseado con sólo 2 clics del ratón 🙂
Con cualquier celda de su tabla seleccionada, haga clic en el Tabla de deduplicación y el complemento inteligente seleccionará toda la tabla.El Tabla de deduplicación Se abrirá una ventana de diálogo con todas las columnas seleccionadas automáticamente y Duplicados de color opción elegida por defecto. Entonces, todo lo que tienes que hacer es hacer clic en Aceptar 🙂 ¡Listo!Consejo. Si desea detectar filas duplicadas en una o más columnas, desmarque todas las columnas irrelevantes y deje seleccionadas solo las columnas clave.
>
Y el resultado sería similar a este:
>Como puede ver en la imagen de arriba, la herramienta Tabla duplicada ha resaltado filas duplicadas sin primeras instancias.
Si desea resaltar duplicados incluyendo las primeras aparicioneso si desea colorear registros únicos en lugar de duplicados, o si no le gusta el color rojo predeterminado, utilice el Asistente para eliminar duplicados que tiene todas estas características y muchas más.
Resalte duplicados en Excel usando un asistente avanzado paso a paso
Comparado con el veloz Tabla de deduplicación herramienta, la Eliminador de duplicados El asistente requiere algunos clics más, pero lo compensa con una serie de opciones adicionales. Déjame mostrártelo en acción:
Seleccione cualquier celda dentro de su tabla donde desee resaltar duplicados y haga clic en el Eliminador de duplicados botón en la cinta. El asistente se ejecutará y se seleccionará toda la tabla. El complemento también sugerirá crear una copia de seguridad de su tabla, por si acaso. Si no lo necesita, desmarque esa casilla. Verifique que la tabla haya sido seleccionada correctamente y haga clic en Próximo.
>
Seleccione uno de los siguientes tipos de datos que desee encontrar:- Duplicados excepto las primeras apariciones
- Duplicados con primeras apariciones
- Valores únicos
- Valores únicos y primeras apariciones duplicadas
Para este ejemplo, busquemos Duplicados + 1.ª aparición:
>
Ahora, seleccione las columnas para buscar duplicados. Como queremos resaltar filas duplicadas completas, seleccioné las 3 columnas.
> Además, el complemento le permite especificar si su tabla tiene encabezados y si desea omitir celdas vacías. Ambas opciones están seleccionadas de forma predeterminada.Finalmente, elija la acción a realizar en duplicados. Tienes varias opciones como seleccionando, eliminando, copiar, mover duplicados o agregar una columna de estado a identificar los incautos.Desde hoy estamos explorando diferentes maneras de destacar duplicados en Excel, nuestra elección es obvia 🙂 Entonces, seleccione llenar de color y elija uno de los colores de tema estándar, o haga clic en Mas colores… y elija cualquier color RGB o HSL personalizado.
>Haga clic en el Finalizar botón y disfruta del resultado 🙂
>Así es como resalta los duplicados en Excel usando nuestro complemento Duplicate Remover. Si tiene curiosidad por probar esta herramienta en sus propias hojas de trabajo, puede descargar una versión de prueba completamente funcional de Ultimate Suite que incluye todas nuestras herramientas para Excel que le permitirán ahorrar tiempo. ¡Y sus comentarios en los comentarios serán muy apreciados!
Usted también podría estar interesado en
Un duplicado en Excel se refiere a una celda o conjunto de celdas que contienen el mismo valor que otra celda en la misma columna. En otras palabras, existen datos repetidos en la hoja de cálculo.
En un conjunto de datos extenso, es esencial identificar y eliminar duplicados para obtener información precisa y evitar errores. La detección de duplicados te permite mantener la integridad de tus datos y mejorar la eficiencia de tus análisis.
Existen varias formas de buscar duplicados en Excel. Puedes utilizar la función de «Formato condicional» para destacar automáticamente los valores duplicados en una columna. También puedes utilizar la función «Buscar duplicados» en el menú de «Datos» para obtener una lista de valores duplicados en una columna seleccionada.
Para resaltar duplicados en Excel, puedes utilizar la función de «Formato condicional». Selecciona la columna que deseas verificar, luego ve a «Inicio» > «Formato condicional» > «Reglas resaltadas de celdas» > «Valores duplicados». Puedes elegir el formato de resaltado que prefieras, como cambiar el color de fondo o la fuente del texto.
Sí, puedes buscar duplicados en varias columnas a la vez usando la función de «Buscar duplicados» en el menú de «Datos». Simplemente selecciona las columnas en las que deseas buscar duplicados y Excel te mostrará todos los valores duplicados en esas columnas.
Para eliminar duplicados en Excel, selecciona la columna o columnas en las que deseas buscar duplicados y ve a «Inicio» > «Eliminar duplicados» en el menú de «Datos». Asegúrate de marcar las columnas correctas en la ventana emergente y haz clic en «Aceptar» para eliminar los valores duplicados.
Sí, además de las funciones mencionadas anteriormente, hay varias herramientas y complementos disponibles en línea que pueden ayudarte a buscar y resaltar duplicados en Excel de manera más avanzada. Algunos ejemplos populares incluyen el complemento «Remove Duplicates» de AbleBits y el complemento «Duplicate Remover» de Excel Campus.
Espero que estas preguntas frecuentes hayan aclarado tus dudas sobre cómo buscar y resaltar duplicados en Excel. Recuerda que la detección y eliminación de duplicados es fundamental para tener datos confiables y precisos en tus hojas de cálculo.
[automatic_youtube_gallery type=»search» search=»Cómo buscar y resaltar duplicados en Excel» limit=»1″]
Buscar y resaltar duplicados, así como fórmulas como CONTAR.SI o CONTAR.SI.CONJUNTO. Alternativamente, puedes usar complementos como el «Duplicate Remover» para identificar y gestionar duplicados de manera más eficiente.
El formato condicional es una herramienta en Excel que permite cambiar la apariencia de las celdas basándose en reglas específicas. Puedes usarlo para resaltar celdas que cumplen ciertos criterios, como duplicados, valores superiores o inferiores a un umbral, etc.
La función CONTAR.SI cuenta el número de veces que un valor específico aparece en un rango dado. Se puede aplicar para identificar duplicados en una columna proporcionando la fórmula adecuada, como por ejemplo =CONTAR.SI(A:A, A1) > 1, que resaltaría duplicados en la columna A.
CONTAR.SI se utiliza para contar celdas que cumplen un solo criterio en un rango específico. CONTAR.SI.CONJUNTO permite contar celdas que cumplen múltiples criterios en diferentes rangos. Esto puede ser útil para identificar duplicados que ocurren bajo diversas condiciones.
Sí, puedes utilizar el formato condicional con fórmulas personalizadas para resaltar toda la fila basada en duplicados en una columna específica, utilizando fórmulas como =CONTAR.SI($A$2:$A2, $A2) > 1.
Para encontrar duplicados en más de una columna, puedes usar la función CONTAR.SI.CONJUNTO, formando condiciones para cada columna. Por ejemplo, =CONTAR.SI(CONJUNTO($A$2:$A2, $A2, $B$2:$B2, $B2) > 1) para resaltar filas donde ambos valores en columnas A y B se duplican.
Excel ofrece múltiples métodos para identificar y resaltar duplicados, tanto a través de funciones como del formato condicional, y complementos avanzados pueden hacerlo aún más fácil y rápido. Si tienes conjuntos de datos grandes, es fundamental eliminar duplicados para obtener análisis precisos y mantener la calidad de los datos.
>
>
>
>
> Además, el complemento le permite especificar si su tabla tiene encabezados y si desea omitir celdas vacías. Ambas opciones están seleccionadas de forma predeterminada.
Slagurinnr: ¡Exacto, laliga! Yo también me volví loco con eso antes. Una vez, estaba organizando una lista de proveedores y no sabía cómo lidiar con los duplicados. Desde que descubrí esa función en Excel, todo ha sido mucho más fácil. La semana pasada incluso me ayudó a simplificar mi base de datos de clientes. ¡Es un alivio ya no perder tiempo en cosas tan aburridas!
Laliga: ¡Totalmente de acuerdo, Agusti! La verdad es que antes me volvía loco buscando duplicados en mis archivos, pero ahora que sé cómo hacerlo, es pan comido. Hace poco también utilicé esa función para organizar mis gastos y me ayudó un montón a ver lo que realmente necesitaba. ¡Menos stress y más eficiencia!
Agusti: ¡Me encantó el artículo! Siempre he tenido problemas para encontrar duplicados en mis hojas de Excel, pero cuando probé la función que mencionas, fue un cambio total. Hace poco, estaba revisando una lista enorme de contactos y no sabía cómo manejarlos. Gracias a tus tips, pude resaltarlos en un dos por tres. ¡Mil gracias!
Figeze: ¡Qué bueno que te sirvió, Agusti! Yo también solía perder horas buscando duplicados hasta que descubrí esa función. Justo la semana pasada tuve que hacer un inventario y me salvó la vida. ¡Lástima que no lo encontré antes!