Fórmula genérica
=COUNTIFS(A:A,$A1,B:B,$B1,C:C,$C1)
Resumen
Excel contiene un ajuste preestablecido incorporado para resaltar valores duplicados con formato condicional, pero solo funciona a nivel de celda. Si desea resaltar filas enteras que son duplicadas, deberá usar su propia fórmula, como se explica a continuación.
Si desea resaltar filas duplicadas en un conjunto de datos sin clasificar y no desea agregar una columna auxiliar, puede usar una fórmula que use la función CONTAR.SI para contar los valores duplicados en cada columna de los datos.
Por ejemplo, si tiene valores en las celdas B4: D11 y desea resaltar filas duplicadas completas, puede usar una fórmula bastante fea:
=COUNTIFS($B$4:$B$11,$B4,$C$4:$C$11,$C4,$D$4:$D$11,$D4)>1
Rangos con nombre para una sintaxis más limpia
La razón por la que la fórmula anterior es tan fea es que necesitamos bloquear completamente cada rango de columna, luego usamos una referencia mixta para probar cada celda en cada columna. Si crea rangos con nombre para cada columna en los datos: col_a, col_b y col_c, la fórmula se puede escribir con una sintaxis mucho más limpia:
=COUNTIFS(col_b,$B4,col_c,$C4,col_d,$D4)>1
Explicación
En la fórmula, COUNTIFS cuenta el número de veces que cada valor de una celda aparece en su columna "principal". Por definición, cada valor debe aparecer al menos una vez, por lo que cuando el recuento> 1, el valor debe ser un duplicado. Las referencias están cuidadosamente bloqueadas para que la fórmula se vuelva verdadera solo cuando las 3 celdas de una fila aparezcan más de una vez en sus respectivas columnas.
La opción de columna auxiliar "engaña" al combinar todos los valores en una fila en una sola celda usando la concatenación. Entonces COUNTIF simplemente cuenta el número de veces que este valor concatenado aparece en la columna D.
Columna auxiliar + concatenación
Si no le importa agregar una columna auxiliar a sus datos, puede simplificar un poco la fórmula de formato condicional. En una columna auxiliar, concatenar valores de todas las columnas. Por ejemplo, agregue una fórmula en la columna E que tenga este aspecto:
=B4&C4&D4
Luego use la siguiente fórmula en la regla de formato condicional:
=COUNTIF($E$4:$E$11,$E4)>1
Esta es una regla mucho más simple y puede ocultar la columna de ayuda si lo desea.
Si tiene una gran cantidad de columnas, puede usar la función TEXTJOIN (Excel 2016365) para realizar la concatenación usando un rango:
=TEXTJOIN(",",TRUE,A1:Z1)
A continuación, puede utilizar COUNTIF como se indicó anteriormente.
SUMPRODUCTO
Si está usando una versión de Excel anterior a 2007, puede usar SUMPRODUCT de esta manera:
=SUMPRODUCT((col_b=$B4)*(col_c=$C4)*(col_d=$D4))>1
