Excel Avanzado: Identifica Duplicados Multi-Criterio de Forma Inteligente

En el análisis de datos con Excel, una de las tareas más comunes y a la vez más complejas es la identificación de registros duplicados. Las herramientas estándar son útiles, pero a menudo se quedan cortas cuando un «duplicado» no se define por una fila entera, sino por la combinación de valores en columnas específicas. ¿Cómo podemos, por ejemplo, encontrar ventas duplicadas de un mismo producto por un mismo vendedor, pero solo marcar como «duplicado» la segunda, tercera o sucesivas ocurrencias, conservando la primera como única?

La fórmula que presentamos aquí es una solución robusta y elegante para este desafío. Combina la potencia de las funciones SUMAPRODUCTO y FILA para crear un sistema de detección de duplicados multi-criterio que examina únicamente las filas precedentes, ofreciendo un control total sobre el proceso de identificación.

Sintaxis

=SI(SUMAPRODUCTO(--(rango_columna_1=valor_columna_actual_1), --(rango_columna_2=valor_columna_actual_2), --(FILA(rango_columna_1)<FILA()))>0, "DUPLICADO DETECTADO", "ÚNICO O PRIMERA OCURRENCIA")

Para comprender su funcionamiento, es mejor descomponer la fórmula en sus partes fundamentales. La magia reside en la función SUMAPRODUCTO, que aquí no se usa para multiplicar y sumar, sino para contar bajo varias condiciones de forma matricial.

• SUMAPRODUCTO(array1, [array2], …)

  El corazón de la fórmula. Multiplica los componentes correspondientes de los arrays proporcionados y devuelve la suma de esos productos. En nuestro caso, los «arrays» son el resultado de operaciones lógicas.

• –(rango_columna_1=valor_columna_actual_1)

  Esta es la primera condición. Compara un rango completo (ej. $A$2:$A$10) con el valor de la celda en la fila actual (ej. A2). El resultado es una matriz de valores VERDADERO o FALSO. El doble guion (--), conocido como doble negación o unario doble, convierte estos valores lógicos en números: VERDADERO se convierte en 1 y FALSO en 0.

• –(rango_columna_2=valor_columna_actual_2)

  Funciona exactamente igual que la primera condición, pero para el segundo criterio. Se pueden añadir tantos criterios como sea necesario, separados por comas.

• –(FILA(rango_columna_1)<FILA())

  Este es el componente «inteligente». FILA(rango_columna_1) crea una matriz con los números de fila de todo el rango, mientras que FILA() devuelve el número de la fila actual donde se está evaluando la fórmula. Al compararlas con <, creamos una matriz de 1s y 0s que solo considera las filas que están por encima de la fila actual. Esto es crucial para que la primera aparición de una combinación no se marque a sí misma como duplicada.

• … > 0

  SUMAPRODUCTO multiplica los elementos de estas matrices (fila por fila). El resultado solo será 1 si todas las condiciones para una fila dada son 1 (1 * 1 * 1 = 1). La suma final nos dice cuántas filas anteriores cumplen todos los criterios. Si este total es mayor que cero, significa que ya hemos visto esta combinación antes, y por lo tanto, la fila actual es un duplicado.

• SI(…)

  La función SI envuelve toda la lógica. Si el resultado de SUMAPRODUCTO es mayor que cero (la prueba lógica es VERDADERO), devuelve el texto «DUPLICADO DETECTADO». De lo contrario, devuelve «ÚNICO O PRIMERA OCURRENCIA».

Ejemplos

Imaginemos que tenemos una tabla de registro de ventas y queremos identificar las entradas donde un mismo vendedor ha vendido el mismo producto más de una vez.

Ejemplo 1 Identificar duplicados basados en las columnas «Vendedor» y «Producto».

En la celda D2, introducimos la siguiente fórmula y la arrastramos hacia abajo hasta la última fila de datos (D8).

=SI(SUMAPRODUCTO(--($A$2:$A$8=A2), --($B$2:$B$8=B2), --(FILA($A$2:$A$8)<FILA(A2)))>0, "DUPLICADO DETECTADO", "ÚNICO O PRIMERA OCURRENCIA")

Análisis del resultado:

• La fila 5 (Ana, Portátil) se marca como duplicada porque ya existe una entrada para «Ana» y «Portátil» en la fila 2.
• La fila 7 (Luis, Monitor) se marca como duplicada por la existencia de la fila 3.
• La fila 8 (Ana, Portátil) también es un duplicado, ya que la fórmula detecta las ocurrencias previas en las filas 2 y 5.

Aplicaciones Prácticas

• 1Depuración de Datos: Antes de realizar un análisis o migrar datos a otro sistema, esta fórmula es ideal para limpiar bases de datos identificando registros redundantes basados en claves lógicas (ej: ID de cliente + ID de producto).
• 2Auditoría de Transacciones: En contabilidad o finanzas, permite detectar rápidamente posibles errores de entrada, como facturas o pagos registrados más de una vez para el mismo proveedor y con el mismo concepto.
• 3Gestión de RRHH: Ayuda a encontrar registros de empleados que puedan estar duplicados por error, basándose en la combinación de Nombre, Apellido y Fecha de Nacimiento.
• 4Análisis de Encuestas: Permite filtrar respuestas duplicadas de un mismo encuestado (identificado por email o ID) para un mismo cuestionario.

Observaciones

Referencias Absolutas vs. Relativas: Es fundamental usar referencias absolutas (con $, ej. $A$2:$A$8) para los rangos de búsqueda. Esto asegura que el rango de búsqueda no cambie al arrastrar la fórmula hacia abajo. Por el contrario, las referencias al valor de la celda actual (A2, B2) deben ser relativas para que se actualicen en cada fila.

Rendimiento: La función SUMAPRODUCTO, al operar con matrices, puede consumir bastantes recursos. En hojas de cálculo con decenas de miles de filas, su cálculo podría volverse lento. Se recomienda evitar el uso de referencias a columnas completas (como A:A) y acotar los rangos al tamaño real de los datos.

Errores comunes

• #¡VALOR! Este error aparecerá si los rangos especificados dentro de SUMAPRODUCTO no tienen exactamente las mismas dimensiones (el mismo número de filas). Asegúrate de que todos los rangos de columna (ej. $A$2:$A$8, $B$2:$B$8) cubran el mismo conjunto de filas.
• Resultados Incorrectos Un error común es no fijar los rangos de búsqueda con referencias absolutas ($). Si usas A2:A8 en lugar de $A$2:$A$8, al arrastrar la fórmula a la fila 3, el rango se convertirá en A3:A9, omitiendo filas anteriores y llevando a una detección incorrecta.

Alternativas

Aunque la fórmula con SUMAPRODUCTO es muy potente, existen alternativas modernas que pueden ser más eficientes o intuitivas.

• Función CONTAR.SI.CONJUNTO:

  =SI(CONTAR.SI.CONJUNTO($A$2:A2, A2, $B$2:B2, B2)>1, "DUPLICADO DETECTADO", "ÚNICO O PRIMERA OCURRENCIA")

  Esta es a menudo la alternativa preferida. Utiliza «rangos expansibles» ($A$2:A2). Al arrastrar la fórmula hacia abajo, el rango crece ($A$2:A3, $A$2:A4, etc.), contando las ocurrencias desde el inicio hasta la fila actual. Si el recuento es mayor que 1, significa que la combinación ya apareció antes. Es más legible y generalmente más rápida que SUMAPRODUCTO.

• Power Query (Obtener y transformar datos):

  Para conjuntos de datos muy grandes o flujos de trabajo de limpieza de datos recurrentes, Power Query es la herramienta superior. Permite seleccionar las columnas clave y usar la opción «Quitar duplicados» para eliminar filas duplicadas de forma permanente o crear una nueva tabla con valores únicos, todo ello en un proceso automatizable y muy eficiente.

• Formato Condicional:

  Si el objetivo es solo resaltar visualmente los duplicados sin necesidad de una columna auxiliar, se puede usar la misma lógica de la fórmula en una regla de formato condicional. Se seleccionaría el rango de datos (ej. A2:C8) y se aplicaría una nueva regla con la fórmula: =SUMAPRODUCTO(--($A$2:$A$8=$A2);--($B$2:$B$8=$B2);--(FILA($A$2:$A$8)0. Esto aplicará el formato elegido a todas las filas que cumplan la condición de ser un duplicado.