Anoche, en el programa de radio Computer America de Craig Crossman, Joe de Boston tenía una pregunta:
Tengo una columna de números de factura. ¿Cómo puedo usar Excel para marcar los duplicados?
Sugerí usar formatos condicionales y la fórmula COUNTIF. Aquí están los detalles sobre cómo hacer que funcione.
Queremos configurar el formato condicional para todo el rango, pero es más fácil configurar un formato condicional para la primera celda del rango y luego copiar ese formato condicional. En nuestro caso, la celda A1 tiene un encabezado de número de factura, por lo que seleccionaré la celda A2 y, en el menú, seleccionaré Formato> Formato condicional. El cuadro de diálogo Formato condicional comienza con el menú desplegable inicial que dice "El valor de celda es". Si toca la flecha junto a esto, puede elegir "La fórmula es".
Después de seleccionar "La fórmula es", el cuadro de diálogo cambia de apariencia. En lugar de cuadros para "Entre xey", ahora hay un cuadro de fórmula único. Esta caja de fórmula es increíblemente poderosa. Puede escribir cualquier fórmula que se le ocurra, siempre que esa fórmula se evalúe como VERDADERA o FALSA.
En nuestro caso, necesitamos usar una fórmula COUNTIF. La fórmula para escribir en el cuadro es
=COUNTIF(A:A,A2)>1
En inglés, esto dice, "mire todo el rango de la columna A. Cuente cuántas celdas en ese rango tienen el mismo valor que en A2. (Es realmente importante que" A2 "en la fórmula apunte al celda actual: la celda en la que está configurando el formato condicional. Entonces, si sus datos están en la columna E y está configurando el primer formato condicional en E5, la fórmula sería =COUNTIF(E:E,E5)>0). Luego, comparamos para ver si eso cuenta es> 1. Idealmente, sin duplicados, el recuento siempre será 1, debido a que la celda A2 está en el rango, deberíamos encontrar exactamente una celda en la columna A que contenga el mismo valor que A2.
Haga clic en el botón Formatear …
Ahora es el momento de seleccionar un formato desagradable. Hay tres pestañas en la parte superior de este cuadro de diálogo Formato de celdas. La pestaña Fuente suele ser la primera, por lo que puede seleccionar una fuente roja en negrita, pero a mí me gusta algo más desagradable. Normalmente hago clic en la pestaña Patrones y elijo rojo brillante o amarillo brillante. Elija el color, luego haga clic en Aceptar para cerrar el cuadro de diálogo Formato de celdas.
Verá el formato seleccionado en el cuadro "Vista previa del formato a utilizar". Haga clic en Aceptar para cerrar el cuadro de diálogo Formato condicional …
… Y no pasa nada. Guau. Si esta es la primera vez que configura el formato condicional, sería muy bueno recibir comentarios aquí de que funcionó. Pero, a menos que tenga la suerte de que el 1098 en la celda A2 sea un duplicado de alguna otra celda, la condición no es cierta y parece que no pasó nada.
Debe copiar el formato condicional de A2 a las otras celdas de su rango. Con el alféizar del cursor en A2, haga Edición> Copiar. Presione Ctrl + barra espaciadora para seleccionar toda la columna. Haga Edición> Pegado especial. En el cuadro de diálogo Pegado especial, haga clic en Formatos. Haga clic en Aceptar.
Esto copiará el formato condicional a todas las celdas de la columna. Ahora, finalmente, verá algunas celdas con el formato rojo, lo que indica que tiene un duplicado.
Es informativo ir a la celda A3 y mirar el formato condicional después de la copia. Seleccione A3, presione od para que aparezca el formato condicional. La fórmula del cuadro Fórmula es modificada para contar cuántas veces aparece A3 en la columna A: A.
Notas
En la pregunta de Joe, solo tenía 1700 facturas en el rango. He configurado 65536 celdas con formato condicional y cada celda está comparando la celda actual con otras 65536 celdas. En Excel 2005, con más filas, el problema será aún peor. Técnicamente, la fórmula del primer paso podría haber sido:=COUNTIF($A$2:$A$1751,A2)>1
Además, al copiar el formato condicional a toda la columna, podría haber seleccionado solo las filas con datos antes de pegar formatos especiales.
Más
El otro problema que describí después de la pregunta es que realmente no se puede ordenar una columna en base a un formato condicional. Si necesita ordenar estos datos para que los duplicados estén en un área, siga estos pasos. Primero, agregue un encabezado a B1 llamado "¿Duplicar?". Escriba esta fórmula en B2: =COUNTIF(A:A,A2)>1.
Con el puntero de celda en B2, haga clic en el controlador de autocompletar (el pequeño cuadrado en la esquina inferior derecha de la celda) para copiar la fórmula hasta el final del rango.
Ahora puede ordenar por columna B descendente y A ascendente para tener las facturas problemáticas en la parte superior del rango.
Esta solución asume que desea resaltar AMBAS facturas duplicadas para que pueda determinar manualmente cuál eliminar o corregir. Si no desea marcar la primera ocurrencia del duplicado, se puede ajustar la fórmula a ser: =COUNTIF($A$2:$A2,A2)>1. Es importante ingresar los signos de dólar exactamente como se muestra. Esto buscará todas las celdas desde la celda actual hacia arriba, buscando entradas duplicadas.
¡Gracias a Joe de Boston por la pregunta!
