miércoles, 23 de abril de 2014

Resaltar Duplicados Concatenados

"Necesito una fórmula que localice códigos duplicados en la columna B y, si los encuentra, que los coloree sólo si en la columna C los nombres coinciden también, pero no puedo añadir columnas adicionales en la hoja."

Necesitamos concatenar la columna B y la C para comprobar si hay entradas duplicadas y, en tal caso, resaltar dichas celdas pero sin utilizar columnas adicionales en la hoja. Para ello formularemos directamente en la herramienta de Formato Condicional. Lo solucionaremos con la ayuda de la función SUMAPRODUCTO. Empezaremos formulando en la hoja para que se entienda mejor y luego pasaremos dicha formulación a la herramienta. Partimos del ejemplo de la primera imagen y queremos conseguir el resultado de la segunda imagen:



Para ello nos situamos en la celda E3 y escribimos la siguiente fórmula que copiaremos hasta la celda E10: 




SUMAPRODUCTO es una función que suma el producto de dos rangos (rangos que deben tener la misma dimensión). Si la fórmula fuese =SUMAPRODUCTO(B3:B10;C3:C10) excel ejecutaría (B3*C3)+(B4*C4)+(B5*C5)... Al introducir un criterio en la función (en nuestro caso el criterio es que el primer rango sea =$B3 y que el segundo sea =$C3) , excel genera una matriz de resultados tipo VERDADERO/FALSO que al multiplicarlo por 1 se convierte en una matriz del tipo 1/0. De esta manera estamos consiguiendo valores 1 para el rango B3:B10 en aquellos casos en los que un código esté repetido y valores cero para los que no lo estén.Y lo mismo en el rango C3:C10. Al combinar ambos resultados obtendremos valores mayores de 1 para aquellas combinaciones repetidas. Para aplicar esta formulación directamente en la herramienta de Formato Condicional debemos especificar una condición que, nuevamente, genere un resultado tipo VERDADERO/FALSO. Es por ello que introducimos el >1 del final de la fórmula. El resultado es el siguiente:

Como no podemos utilizar columnas adicionales en la hoja, seleccionamos ahora el rango B3:C10 y vamos a Formato condicional. Elegimos la opción de introducir una fórmula y escribimos (o copiamos y pegamos directamente) nuestra fórmula. En el botón Formato... damos la apariencia de relleno de color que deseemos y aceptamos:


Evidentemente, procedemos a borrar la formulación realizada en la columna E. Para finalizar correctamente el modelo debemos considerar que si dejamos celdas en blanco excel las rellenará con el formato que le hayamos asignado:

  Para evitar ésto, ampliamos la fórmula de la siguiente manera: