Necesito formatear condicionalmente celdas con diferentes valores entre las mismas columnas,sisu valor para una columna específica es el mismo.
Guión
Mi hoja de cálculo es una base de datos de contactos, donde cada fila es un registro de contacto, y cada columna contiene un tipo de dato (apellido, calle 1, calle 2, ciudad, estado, etc) para cada registro de contacto.
La hoja de cálculo es una combinación de la información de contacto mantenida por diferentes oficinas de nuestra empresa; hemos combinado toda la información de contacto en la misma fuente para que podamos acordar cualquier discrepancia y mantener todos nuestros contactos en el mismo lugar en el futuro.
Algunos contactos solo tienen un registro, como cuando solo una de nuestras oficinas tenía datos para ese contacto, por lo que no puede estar en desacuerdo con los datos que tenemos de cualquier otra oficina; otros contactos tienen varios registros (es decir, puede haber sólo una fila para Fred Johnson pero cuatro filas para Jane Smith). Cada contacto debe tener exactamente la misma información de contacto antes de que podamos cargarla en una base de datos en la nube o se crearán duplicados.
Hemos pasado mucho tiempo intentando concordar datos entre registros de la misma persona y ahora necesitamos descubrir dónde existen las discrepancias restantes.
He concatenado los campos Apellido y Nombre para que las filas con el mismo valor en este campo se puedan comparar entre sí; el objetivo es resaltar celdas con datos diferentes en la misma columna cuando se comparan con otros registros con el mismo valor en la columna LastFirst (si corresponde).
¿Cómo puedo hacer esto?
Hasta ahora, soy consciente de que la función Buscar especial puede resaltar celdas cuando se comparan con una fila o columna designada, pero necesito que las comparaciones se realicen con filas que tienen el mismo valor para una columna en particular.
Tengo acceso a Excel 2010 y 2013 para realizar esta función.
¡Gracias!
Respuesta1
Puede haber soluciones más interesantes, pero no debería ser necesario concatenar nombres y apellidos para crear una columna "Nombre completo".
Vista previa de nuestra producción
(las celdas blancas a la derecha muestran qué resultado producirá nuestra fórmula para impulsar el formato condicional):
Aquí está nuestra fórmula
=COUNTIFS($A$1:$A$100,$A2,$B$1:$B$100,$B2)
-COUNTIFS($A$1:$A$100,$A2,$B$1:$B$100,$B2,C$1:C$100,IF(C2="","",C2))
Recuerde ajustar para adaptarse a la cantidad de filas que tiene en su conjunto de datos y a la posición de las columnas de nombre y apellido si no están en A y B.
También asumí que sus primeros datos que no son de nombre comienzan en la celda C2, es decir, este es el bit de datos superior izquierdo que desea verificar para detectar discrepancias. Edite las "C" en esta fórmula si es necesario.
Aplicar el formato condicional
Copie la fórmula anterior (después de realizar los cambios necesarios), luego haga clic en la celda superior izquierda que desea verificar para ver si hay discrepancias (me referiré a "C2" desde aquí). Ahora, seleccione el Conditional Formattingbotón de la cinta y elija New Rule.
Seleccione Use a formula to determine which cells to formaty luego pegue su fórmula en el cuadro de texto (asegúrese de que no haya espacios; si copió desde arriba, deberá eliminar algunos). Ahora puede hacer clic Formatpara establecer cómo desea resaltar las discrepancias. He optado por un relleno rojo (¡llamativo!). Haga clic OKhasta regresar a la hoja de cálculo.
Si no hay discrepancias en la celda C2, puede parecer que no pasó nada, pero eso se debe a que aún necesitamos aplicar la regla a todo el conjunto de datos. Manteniendo C2 aún seleccionado, haga clic Manage Rulesen el Conditional Formattingmenú de la cinta.
Ahora puede seleccionar todo el rango en el que desea comprobar si hay discrepancias. En el Applies tocuadro, haga clic y arrastre para seleccionar todo lo que desea comparar (o si tiene muchas filas, para mayor velocidad simplemente escriba la referencia de la celda =$C$2:$Z$999)
Haga clic en Aceptar y ¡listo!
Cómo funciona
Esta fórmula sirve COUNTIFS()para contar cuántas filas hay para esa persona:
=COUNTIFS($A$1:$A$100,$A2,$B$1:$B$100,$B2)
Luego calculamos cuántas filas tienen el nombre de esa persona Y el valor en la columna que estás verificando. Si todas las filas son idénticas, entonces este número debería coincidir exactamente con el primero COUNTIFS().
COUNTIFS($A$1:$A$100,$A2,$B$1:$B$100,$B2,C$1:C$100,IF(C2="","",C2))
Si restamos el último del primero y todas las filas coinciden, la fórmula genera 0 y no aplica ningún formato condicional. Sin embargo, si algo es diferente, entonces el resultado será 1 o superior, lo que activará el formato condicional.
Nota
Tuve que incluir los COUNTIFS()criterios finales en una IF()declaración para lidiar con los espacios en blanco; COUNTIFSno me gustan mucho los espacios en blanco (parece inseguro si contarlos como 0 o "". Raro).
Descarga de archivos
Este documento de ejemplo también esdisponible para descargar.