BUSCARX Valores Duplicados

Este tutorial demostrará como BUSCARX duplicar valores en Excel. Si su versión de Excel no soporta BUSCARX, lea cómo usar  BUSCARV para duplicar valores en su lugar.

 

Búsqueda de valores duplicados

En Excel 365, buscar valores duplicados es más fácil gracias a la función FILTRAR. Primero demostraremos la función FILTRAR y luego cómo buscar valores duplicados con BUSCARX.

Con la función FILTRAR, sólo hay que introducir la matriz (por ejemplo, las notas) que queremos devolver y el rango de criterios (por ejemplo, el ID del estudiante) con los criterios (por ejemplo, 2021-A).

=FILTRAR(C3:C7;B3:B7=E2)

 

También podemos anidar la función FILTRAR dentro de la función INDICE e introducir un número de fila (por ejemplo, F3) para devolver la enésima coincidencia que queramos en lugar de devolver todas las coincidencias.

=INDICE(FILTRAR(C3:C7;B3:B7=E3);F3)

 

Los escenarios anteriores muestran la función FILTRAR como la solución más conveniente para ese tipo de problemas en Excel 365. Sin embargo, los escenarios de búsqueda que requieren una lista de valores de búsqueda a menudo se resuelven mejor a través de fórmulas BUSCARX porque podemos convertirlas en fórmulas de matriz dinámica, lo que las hace más flexibles que la Función FILTRAR.

Veamos primero la solución de matriz no dinámica para desglosar su funcionamiento antes de utilizar la matriz dinámica BUSCARX.

BUSCARX – Valores de búsqueda duplicados

Digamos que quieres buscar una lista de valores duplicados usando la función BUSCARX. Abajo, a la derecha, tenemos nuestros valores de búsqueda. A la izquierda tenemos la tabla de búsqueda. Queremos buscar cada valor duplicado y obtenerlo en filas separadas.

 

Podemos utilizar la solución de fórmula de matriz no dinámica que se indica a continuación

=BUSCARX(F3&"-"&CONTAR.SI($F$3:F3;F3);$D$3:$D$7;$C$3:$C$7)

 

Repasemos la fórmula:

ID único – CONTAR.SI

En primer lugar, unimos el ID original (por ejemplo, ID de estudiante) con la función CONTAR.SI para crear una lista de IDs únicos que distinguirá la primera entrada de un elemento de sus duplicados.

=B3&"-"&CONTAR.SI($B$3:B3;B3)

 

El bloqueo de una parte de la referencia del rango dentro de la función CONTAR.SI nos permite contar la aparición de un valor a medida que el rango se expande.

=CONTAR.SI($B$3:B3;B3)

 

Ahora, utilizamos el Operador & para unir la Función CONTAR.SI al ID original. También añadimos un separador (por ejemplo, «-«).

=B3&"-"&D3

 

Combinando todo junto se obtiene nuestra fórmula de identificación única:

=B3&"-"&CONTAR.SI($B$3:B3;B3)

Valores de búsqueda duplicados – Enésima coincidencia

Al igual que con el ID original, también necesitamos crear una lista de IDs únicos para los valores de búsqueda. Aplicando el mismo método:

=G3&"-"&CONTAR.SI($G$3:G3;G3)

 

Función BUSCARX

Ahora, introducimos la nueva matriz de búsqueda y la lista de valores de búsqueda en la función BUSCARX:

=BUSCARX(H3;$E$3:$E$7;$C$3:$C$7)

 

BUSCARX – Valores de búsqueda duplicados (matriz dinámica)

En lugar de añadir nuevas columnas y copiar o arrastrar fórmulas, podemos convertir las fórmulas BUSCARX anteriores en una fórmula de matriz dinámica que pueda generar la misma salida. Así es como se ve:

=BUSCARX(E3:E7&"-"&CONTAR.SI(DESREF(E3;0;0;SECUENCIA(FILAS(E3:E7)));E3:E7);
         B3:B7&"-"&CONTAR.SI(DESREF(B3;0;0;SECUENCIA(FILAS(B3:B7)));B3:B7);
         C3:C7)

 

Repasemos la fórmula anterior: Al igual que con el método anterior, primero necesitamos crear una lista de IDs únicos utilizando la función CONTAR.SI. El reto sería cómo crear una fórmula de array que genere un array de referencias de rango (por ejemplo, B3, B3:B4, B3:B5 y así sucesivamente).

Fórmula DESREF-SECUENCIA-FILAS 

Podemos utilizar la combinación de las funciones DESREF, SECUENCIA y FILAS para devolver un array de referencias de rango. Empecemos con la lista del array de búsqueda (por ejemplo, B3:B7):

=DESREF(B3;0;0;SECUENCIA(FILAS(B3:B7))

Nota: La fórmula anterior no funcionará por sí sola. Excel no puede devolver una matriz de rangos o matrices, pero sí puede evaluarla como veremos más adelante.

Función FILAS

Primero, necesitamos determinar el número total de filas usando la función FILAS.

=FILAS(B3:B7)

 

Función SEQUENCIA

A continuación, introducimos el resultado de la Función FILAS en la Función SECUENCIA para generar una lista de números de conteo, que también representa el número de celdas por rango en la matriz de rangos.

=SECUENCIA(C2)

 

Fórmula DESREF de la matriz

A continuación, introducimos los resultados de la Función SECUENCIA en la altura (4to argumento) de la Función DESREF para construir los rangos.

=DESREF(B3;0;0;C3:C7)

Como se mencionó anteriormente, esto no funcionará, pero así es como se supone que debe ser el array de rangos:

 

Empezamos con B3 y lo ampliamos para cada altura (por ejemplo, C3:C7).

Nota: DESREF es una función volátil, lo que significa que recalcula cada vez que hay un cambio en la hoja de cálculo aunque no esté relacionado con las entradas del propio DESREF.

Función CONTAR.SI de la matriz

Ahora que tenemos un array de rangos, lo introducimos en la función CONTAR.SI y devolvemos un array de enésimas ocurrencias.

=CONTAR.SI(DESREF(B3;0;0;C3:C7);B3:B7)

Array ID Único

Por último, concatenamos la salida del array de la función CONTAR.SI con el array de búsqueda (por ejemplo, B3:B7) para generar el array de IDs únicos.

=B3:B7&"-"&F3:F7

 

La combinación de todas las funciones da como resultado nuestra matriz de ID únicos Fórmula:

=B3:B7&"-"&CONTAR.SI(DESREF(B3;0;0;SECUENCIA(FILAS(B3:B7)));B3:B7)

También hacemos lo mismo con los valores de búsqueda:

=I3:I7&"-"&CONTAR.SI(DESREF(I3:I7;0;0;SECUENCIA(FILAS(I3:I7)));I3:I7)

Combinando todo el resultado es nuestra fórmula original de la matriz:

=BUSCARX(E3:E7&"-"&CONTAR.SI(DESREF(E3;0;0;SECUENCIA(FILAS(E3:E7)));E3:E7);
         B3:B7&"-"&CONTAR.SI(DESREF(B3;0;0;SECUENCIA(FILAS(B3:B7)));B3:B7);
         C3:C7)