Cómo enumerar valores duplicados (o valores únicos) en Excel

El equipo de Excel anunció recientemente nuevas fórmulas de matriz dinámica, que pueden crear listas únicas, ordenar y filtrar con una fórmula simple. Estas nuevas fórmulas se implementarán para los suscriptores de Office 365 durante los próximos meses. Sin embargo, no todos tienen la suscripción y la tendrán. actualizar cuando sea sensato para su negocio, a menudo combinándolo con una actualización de hardware. Por lo tanto, podrían pasar 6 años o más antes de que suficientes usuarios tengan la nueva funcionalidad para usarla de forma segura y garantizar la compatibilidad. Entonces, ¿cómo podemos enumerar valores duplicados o valores únicos en Excel utilizando los métodos tradicionales de Excel? Cubriremos eso en esta publicación.

CONTAR.SI es una potencia sin explotar para la mayoría de los usuarios de Excel. Contar celdas que cumplen con criterios específicos puede no parecer particularmente útil, pero cuando se combina con otras funciones y lógica booleana (verdadero/falso), crea nuevas capacidades que nunca creyó posibles.

Esta publicación analiza un aspecto de esto y considera cómo usar la función CONTAR.SI para crear y comparar listas para verificar si hay valores duplicados o únicos. Comenzaremos con algunos escenarios básicos y poco a poco iremos ampliando la complejidad hasta lograr una fórmula mágica avanzada.

Comparando dos listas únicas

La función CONTAR.SI se puede utilizar para comparar dos listas y devolver el número de elementos dentro de ambas listas.

Veamos un ejemplo. En la captura de pantalla a continuación hay una lista de estudiantes de la escuela St John's (celdas A2 - A7) y una lista de estudiantes que asistieron a un examen específico (celdas B2-B6). Se nos pidió que identifiquemos la cantidad de personas que están en ambas listas (es decir, cuántos estudiantes de la escuela de St John asistieron al examen).

La fórmula en la celda E2 es:

{=SUM(CONTAR.SI(A2:A7,B2:B6))}

Por lo general, CONTAR.SI contará el número de elementos de una lista que cumplen con un único criterio. En este caso, no hemos proporcionado un único criterio, sino que hemos utilizado un rango de celdas. Como no hemos utilizado ningún operador lógico, como mayor que (), Excel de forma predeterminada, aplica igual (=) como operador lógico. Por lo tanto, esta fórmula comparará cada celda en el rango B2 - B6 para identificar si es igual a alguna celda en el rango A2 - A7.

En este ejemplo, hay varios cálculos que se realizan dentro de la misma fórmula. El cálculo 1 compara la celda B2 con las celdas A2:A7, el cálculo 2 compara la celda B3 con las celdas A2:A7, etc., etc. En total hay 5 resultados separados, todos calculados al mismo tiempo; este tipo de fórmula se conoce como fórmula matricial.

La función CONTAR.SI se calculará de la siguiente manera:

{=SUM({1;0;1;1;0})}

Observe cómo se muestran los 5 resultados, cada uno separado por un punto y coma. Al envolver esto dentro de la función SUMA, agregará la lista de 1 y 0, que es la cantidad de elementos que aparecen en ambas listas.

Como se trata de una fórmula matricial, no escriba las llaves al principio o al final de la fórmula, Excel las incluirá cuando presione Ctrl + Shift + Enter para ingresar la fórmula en la celda o barra de fórmulas.

Si quisiéramos evitar presionar Ctrl + Shift + Enter, podríamos usar la función SUMAPRODUCTO, en lugar de la función SUMA. La fórmula en la celda E4 es:

=SUMAPRODUCTO(CONTAR.SI(A2:A7,B2:B6))

SUMPRODUCT es una función especial que puede manejar matrices sin la necesidad de Ctrl + Shift + Enter.

Por lo general, SUMPRODUCTO se usa para multiplicar celdas o números y luego sumar los resultados de las multiplicaciones. En la forma en que lo estamos usando, no se produce ninguna multiplicación dentro de la función SUMPRODUCTO, por lo que simplemente sumará el resultado.

Comparar listas con duplicados

En el ejemplo anterior, ambas listas son únicas, pero ¿qué pasa si una de las listas contiene duplicados? En este escenario, el resultado de la fórmula sería incorrecto. He ampliado el ejemplo para incluir duplicados de dos asistentes al examen (ver captura de pantalla a continuación), Lucy King y Billy Thompson ahora aparecen dos veces en la columna B. El resultado del cálculo anterior se muestra en las celdas E2 y E4. El resultado se calcula incorrectamente como 5. El resultado ha aumentado en 2 debido a los valores duplicados.

Cuando se trabaja con datos únicos, no importa qué lista se use en cada argumento de la función CONTAR.SI. Sin embargo, cuando hay duplicados, el primer rango en la función debe ser el rango que contiene los duplicados y el segundo rango que contiene los valores únicos. tenga en cuenta este cambio en los ejemplos del resto de esta publicación.

Contar artículos únicos

La fórmula en la celda E6 es:

{=SUMA(--(CONTAR.SI(B2:B8,A2:A7)=1))}

Esta fórmula es una extensión del ejemplo de la sección anterior, pero agrega cierta complejidad adicional. Dediquemos un tiempo a comprender cómo funciona.

Se agregó una prueba lógica a la fórmula para que solo se incluyan elementos cuyo recuento sea =1 (es decir, valores únicos).

{=SUMA(--(CONTAR.SI(B2:B8,A2:A7)=1))}

Esta declaración lógica se calculará como VERDADERO o FALSO.

{=SUM(--({FALSO;VERDADERO;VERDADERO;FALSO;FALSO;VERDADERO}))}

No es posible utilizar SUMA en valores VERDADERO o FALSO. Estos deberán convertirse en valores de 1 para VERDADERO o 0 para FALSO para permitir que la función SUMA funcione. Hay dos opciones para esto:

• Multiplicar valores VERDADERO/FALSO por 1
• Multiplique los valores VERDADERO/FALSO por – – (menos, menos).

En nuestro ejemplo, opté por utilizar el método del doble menos. Ahora la fórmula se calcula de la siguiente manera:

{=SUM({0;1;1;0;0;1})}

La función SUMA devolverá el valor de 3, que es correcto.

Si quisiéramos evitar Ctrl + Shift + Enter, SUMPRODUCT es una opción, como se muestra en la celda E8:

=SUMAPRODUCTO(--(CONTAR.SI(B2:B8,A2:A7)=1))

Contar duplicados

Aplicando las mismas fórmulas, pero cambiando el umbral lógico podemos calcular el número de valores duplicados.

La fórmula en la celda E10 es:

{=SUMA(--(CONTAR.SI(B2:B8,A2:A7)=2))}

Como la declaración lógica requiere valores mayores o iguales a 2, solo contará los duplicados.

Nuevamente podemos usar SUMPRODUCT para evitar presionar Ctrl + Shift + Enter, como se muestra en la celda E12:

=SUMAPRODUCTO(--(CONTAR.SI(B2:B8,A2:A7)=2))

Contar elementos no coincidentes

La medida final de interés puede ser la cantidad de elementos que no coinciden con ninguna de las listas. Para esto, la declaración lógica cambia nuevamente para mostrar solo los elementos donde el recuento es igual a cero.

La fórmula en la celda E14 es:

={SUM(--(CONTAR.SI(B2:B8,A2:A7)=0))}

La versión sin Ctrl + Shift + Enter en la celda E16 es:

=SUMAPRODUCTO(--(CONTAR.SI(B2:B8,A2:A7)=0))

Extraer una lista de valores únicos

Usando este concepto de comparar dos listas con CONTAR.SI, no solo podemos contar valores únicos, sino también extraer una lista de valores únicos.

Los datos del ejemplo ahora han cambiado. La columna A contiene una lista de estudiantes que asistieron a un examen, pero contiene duplicados. Usaremos estos datos para crear una lista única de valores, como se muestra en la columna B.

La fórmula en la celda B3 es:

{=SIERROR(ÍNDICE(A$2:A$16,COINCIDIR(0,CONTAR.SI(B2:B$2,A$2:A$16),0)),"")}

Esta es una fórmula matricial que requiere Ctrl + Shift + Enter.

Esta fórmula utiliza lo que ya hemos aprendido sobre CONTAR.SI y lo amplía aún más. Exploremos esto con más detalle.

Entendiendo la fórmula

{=SIERROR(ÍNDICE(A$2:A$16,COINCIDIR(0,CONTAR.SI(B2:B$2,A$2:A$16),0)),"")}

Observe cómo la función CONTAR.SI incluye una referencia de celda relativa y absoluta a los números de fila dentro del primer argumento (B2:B$2), esto garantiza que cuando la función copia hacia abajo, el rango de celda aumenta de tamaño.

Para esta primera celda no hay nada en nuestra lista de valores únicos (ya que la lista única actualmente es solo el valor en blanco en la celda B2), por lo tanto, CONTAR.SI devuelve ceros para cada resultado.

{=SIERROR(ÍNDICE(A$2:A$16,COINCIDIR(0,{0;0;0;0;0;0;0;0;0;0;0;0;0;0;0},0) ),"")}

A continuación, veremos la función COINCIDIR.

{=SIERROR(ÍNDICE(A$2:A$16,COINCIDIR(0,{0;0;0;0;0;0;0;0;0;0;0;0;0;0;0},0) ),"")}

Esta función devuelve la posición del primer 0 utilizando el método de coincidencia exacta. El resultado de la función COINCIDIR es 1 porque el primer 0 está en la primera posición.

{=SIERROR(ÍNDICE(A$2:A$16,1),"")}

A continuación, la función ÍNDICE encontrará la celda en el rango A2 - A16 que tiene una posición de 1 (es decir, la primera celda). Esto se calcula de la siguiente manera:

{=SIERROR("Lucy King","")}

Al usar esta fórmula, no tenemos que saber cuántos valores únicos hay, ya que la función SI.ERROR garantiza que se devuelva un espacio en blanco una vez que se llega al final de la lista única.

Copia la fórmula

Arrastre la fórmula en la celda B3 hacia abajo para proporcionar suficientes filas para los datos actuales y cualquier crecimiento futuro.

Es difícil apreciar esta fórmula mirando solo el primer resultado; por lo tanto, para comprender mejor cómo funciona realmente esta fórmula, mire la celda B4.

{=SIERROR(ÍNDICE(A$2:A$16,COINCIDIR(0,CONTAR.SI(B$2:B3,A$2:A$16),0)),"")}

CONTAR.SI ahora calculará de la siguiente manera:

{=SIERROR(ÍNDICE(A$2:A$16,COINCIDIR(0,{1;0;0;0;0;0;1;0;0;0;0;1;0;0;0},0) ),"")}

Ahora hay 1 en el resultado. Esto ocurre cada vez que los valores de las celdas B2 – B3 aparecen en las celdas A2 – A16. Cuando la función COINCIDIR busca 0, ahora devolverá el segundo elemento de la lista (que es el siguiente elemento único). ).

Para ver el siguiente ejemplo, consulte la celda B6:

{=SIERROR(ÍNDICE(A$2:A$16,COINCIDIR(0,{1;1;1;0;0;0;1;0;0;0;0;1;1;0;0},0) ),"")}

A medida que se incluyen más elementos dentro de la lista única (que ahora ha crecido a las celdas B2 – B5), aparecerán más unos. El cuarto elemento de la lista es el primer cero, por lo que se devuelve ese valor.

A medida que la fórmula se copia más, quedarán menos ceros.

Cuando no quedan ceros (es decir, la lista contiene todos los valores únicos), la función COINCIDIR devolverá errores. Estos errores son capturados por la declaración IFERROR y convertidos en celdas en blanco con dos comillas (“”).

Si desea evitar Ctrl + Shift + Enter, no podemos usar SUMPRODUCT como antes. En su lugar, podemos usar la función ÍNDICE para procesar la matriz. El cambio requerido se resalta a continuación.

=SIERROR(ÍNDICE(A$2:A$16,COINCIDIR(0,ÍNDICE(CONTAR.SI(B2:B$2,A$2:A$16),),0)),"")

Listar valores duplicados

Después de analizar la extracción de una lista de valores únicos, ahora pasamos a considerar la extracción de una lista de valores duplicados.

La fórmula de la celda D3 es tan larga que se incluye en dos líneas a continuación, pero puedes tenerla en una sola línea dentro de Excel.

=SIERROR(ÍNDICE(A$2:A$16,COINCIDIR(1,((CONTAR.SI(D2:D$2,A$2:A$16)=0)*(CONTAR.SI(A$2:A$16,A$2:A$16)= 2)),0)),"")

Las diferencias con la fórmula única de la sección anterior se destacan a continuación:

{=SIERROR(ÍNDICE(A$2:A$16,COINCIDIR(1,((CONTAR.SI(D2:D$2,A$2:A$16)=0)*(CONTAR.SI(A$2:A$16,A$2:A$16) =2)),0)),"")}

La primera función CONTAR.SI ha cambiado para incluir una prueba lógica donde el valor es igual a 0. Sabemos que 0 aparece donde el elemento aún no aparece en la lista. Si es igual a 0, el resultado será VERDADERO, de lo contrario será FALSO.

La segunda función CONTAR.SI compara la lista fuente consigo misma para contar el número de instancias de cada valor. Cuando el recuento es mayor o igual a 2 (es decir, es un valor duplicado), se devolverá VERDADERO; de lo contrario, se devolverá FALSO.

Como ahora tenemos dos listas de VERDADERO/FALSO, podemos multiplicarlas juntas. Cuando hay dos valores VERDADEROS (es decir, el valor aparece más de una vez en la lista maestra y aún no aparece en nuestra lista de duplicados). la multiplicación devuelve 1, todos los demás valores contendrán un valor FALSO, por lo que se multiplicarán a 0.

Cambiamos la función COINCIDIR para encontrar 1, en lugar de 0.

Arrastre esta fórmula hacia abajo y habremos creado una lista de valores duplicados.

Una vez más, si desea evitar Ctrl + Shift + Enter, use la función ÍNDICE para procesar la matriz. El cambio requerido se resalta a continuación.

=SIERROR(ÍNDICE(A$2:A$16,COINCIDIR(1,ÍNDICE(((CONTAR.SI(D2:D$2,A$2:A$16)=0)*(CONTAR.SI(A$2:A$16,A$2:A$16 )=2)),),0)),"")

¿Por qué utilizar una fórmula complicada?

Pero existen muchas otras opciones para crear listas únicas e identificar duplicados:

• Tabla dinámica
• Consulta de energía
• Eliminar duplicados de la cinta de datos

Cada una de estas opciones es más fácil de entender que las fórmulas complejas que hemos creado, así que ¿por qué molestarse? ¿No sería más fácil utilizar uno de esos otros métodos? Sí y No.

Todas esas opciones requieren una macro o la interacción del usuario para funcionar. Para tablas dinámicas y Power Query, el usuario debe "Actualizar" los datos. Para Eliminar duplicados, el usuario debe eliminar manualmente los duplicados cada vez que se produce un cambio.

Una fórmula tiene una ventaja significativa; puede ser completamente dinámico y actualizarse automáticamente cuando los datos cambian. Si los datos de origen son una tabla de Excel o un rango con nombre dinámico, la lista de datos únicos o duplicados se puede actualizar incluso cuando se agregan nuevos datos a los datos de origen. En mi opinión, esto la convierte en una solución mucho más utilizable.

Hasta que las nuevas fórmulas de matriz dinámica estén implementadas y bien establecidas, los métodos anteriores probablemente sean estas opciones disponibles.

Conclusión

Como hemos visto, la función CONTAR.SI simple es significativamente más poderosa de lo que la mayoría de los usuarios de Excel creen. Al usar un rango como criterio, pudimos crear una fórmula matricial que contiene múltiples cálculos dentro de una sola celda. Luego, cuando se combina con ÍNDICE y MATCH es posible enumerar valores únicos o duplicados.

Comprender cómo Excel maneja VERDADERO/FALSO es una parte crucial de la creación de fórmulas avanzadas en Excel.

Artículos Relacionados:

• Función ÚNICA en Excel
• Comprender las fórmulas matrices básicas
• Matrices dinámicas en Excel

---