Localizar duplicados usando el formato condicional

Se aplica a
Microsoft Office Excel 2003

Logotipo de Mr. Excel Este artículo es una adaptación de MrExcel.com. Visite el sitio Web MrExcel.com para obtener más información y sugerencias.

Puede localizar los duplicados en un rango de datos utilizando el formato condicional y la función CONTAR.SI. A continuación se explica con detalle cómo hacerlo.

Configurar la primera fórmula de formato condicional

Comenzaremos por configurar un formato condicional para la primera celda de datos. Más adelante, copiaremos ese formato condicional para todo el rango de datos.

En el ejemplo, la celda A1 contiene un encabezado de columna (Factura), por lo que seleccionaremos la celda A2 y después haremos clic en el comando Formato condicional del menú Formato. Se abre el cuadro de diálogo Formato condicional. El primer cuadro contiene el texto Valor de la celda. Si hace clic en la flecha que aparece junto a él puede elegir Fórmula.

Ejemplo

Después de hacer clic en Fórmula, el cuadro de diálogo cambia de aspecto. En lugar de incluir cuadros para entre x e y, ahora hay un único cuadro de fórmula. Este cuadro de fórmula es muy poderoso. Puede utilizarlo para escribir cualquier fórmula que pueda soñar, con la única condición de que su resultado sea verdadero (TRUE) o falso (FALSE).

En este caso, debemos utilizar una fórmula CONTAR.SI. La sintaxis de la fórmula que escribiremos en el cuadro es:


=CONTAR.SI(A:A;A2)>1

Esta fórmula significa lo siguiente: recorrer todo el rango de la columna A. Contar cuántas celdas de ese rango tienen el mismo valor que la celda A2. Después, comparar si el número total es superior a 1.

Cuando no hay duplicados, el recuento siempre es 1; puesto que la celda A2 está en el rango, debemos encontrar exactamente una celda en la columna A que contiene el mismo valor que A2.

 Nota   En esta fórmula, A2 representa la celda actual, es decir, la celda para la que estamos configurando el formato condicional. Por tanto, si los datos se encuentran en la columna E y está configurando el primer formato condicional en la celda E5, la fórmula será =CONTAR.SI(E:E;E5)>1.

Elegir un color para resaltar las entradas duplicadas

Ahora es el momento de seleccionar un formato molesto (es decir, obvio) para identificar los duplicados que se encuentren. En el cuadro de diálogo Formato condicional, haga clic en el botón Formato.

Ejemplo

Haga clic en la ficha Tramas y seleccione un color que destaque, como rojo o amarillo. A continuación, haga clic en Aceptar para cerrar el cuadro de diálogo Formato de celdas.

Ejemplo

Verá el formato seleccionado en el cuadro de vista previa. Haga clic en Aceptar para cerrar el cuadro de diálogo Formato condicional y,…

Ejemplo

No ocurre nada. ¿Y eso? Si es la primera vez que configura un formato condicional, sería muy bueno poder tener aquí algún signo de que ha funcionado. Pero, a menos que tenga la suerte de que los datos de la celda A2 estén duplicados con los de otra celda, la condición será falsa (FALSE) y no se aplica ningún formato.

Copiar el formato condicional al resto de las celdas

Es necesario copiar el formato condicional de la celda A2 en las demás celdas del rango. Con el cursor todavía situado en la celda A2, haga clic en el comando Copiar del menú Edición. Presione CTRL+Barra espaciadora para seleccionar toda la columna. A continuación, haga clic en el comando Pegado especial del menú Edición. En el cuadro de diálogo Pegado especial, haga clic en Formatos y, a continuación, en Aceptar.

Ejemplo

Esta acción copiará el formato condicional en todas las celdas de la columna. Ahora, por fin,  podrá ver algunas celdas con el formato de relleno en color, que indica que se trata de un valor duplicado.

Obtendrá información si va a la celda A3 y examina la fórmula condicional (después de copiarla desde A2). Seleccione la celda A3 y haga clic en Formato condicional en el menú Formato. La fórmula que aparece en el cuadro Fórmula se ha modificado para mostrar un recuento de las veces que aparecen en la columna A los datos contenidos en la celda A3.

Ejemplo

Puede tener hasta 65536 celdas con formato condicional en la que cada una de ellas compara la celda actual con las demás 65535. Técnicamente, la fórmula del primer paso podría haber sido =CONTAR.SI($A$2:$A$1751;A2)>1.

Además, al copiar el formato condicional en toda la columna, podría haber seleccionado sólo las celdas que contenían datos antes de utilizar el comando Pegado especial.

Resaltar sólo la segunda aparición de un duplicado

La solución anterior presupone que desea resaltar ambos números de factura duplicados para poder averiguar manualmente cuál debe eliminar o corregir. Si no desea marcar la primera aparición del duplicado, puede ajustar la fórmula de esta manera:


=CONTAR.SI($A$2:$A2;A2)>1

 Nota    Es importante incluir los símbolos de dólar exactamente como se indica.

En el primer argumento de esta fórmula sólo se cambia la referencia de la segunda celda del rango de datos cuando se copia en el resto de la columna. Esto significa que la fórmula sólo comparará las celdas comprendidas entre la celda actual y la primera celda del rango de datos cuando se busque las entradas duplicadas.

Ordenar los datos

En realidad, no se puede ordenar columnas en función del formato condicional. Si desea ordenar los datos para que los duplicados estén en un área, siga estos pasos:

En primer lugar, escriba el título Duplicado en la celda B1. A continuación, escriba esta fórmula en la celda B2:


=CONTAR.SI(A:A;A2)>1

Ejemplo

Con el cursor en la celda B2, haga doble clic en el controlador Autorrellenar (el pequeño cuadrado de la esquina inferior derecha de la celda) para copiar la fórmula en todas las celdas de la columna situadas debajo.

Ejemplo

Ahora puede ordenar las columnas por la columna (en sentido descendente) y, a continuación, por la columna A (en sentido ascendente), para mostrar los números de factura duplicados al principio del rango.

Ejemplo

 
 
Corresponde a:
Excel 2003