Contar valores únicos en un rango con dos criterios.

sábado, agosto 18, 2012

Hace ya cinco años atrás publique una nota sobre cómo contar valores únicos en un rango. Siguiendo con el tema, un lector me consulta cómo contar los valores únicos en el rango pero con más de un criterio.
Por ejemplo, en nuestro ejemplo, contar vendedores por región. Digamos que tenemos una tabla de vendedores por región, pero por algún motivo hay líneas duplicadas



Podemos ver que el agente 3 y el agente 5 aparecen dos veces en la zona Norte. Tenemos seis líneas para la zona Norte pero sólo cuatro agentes.

Mi propuesta para este tipo de situaciones es usar tablas dinámicas (mi herramienta preferida, como ya habrán notado mis lectores habituales).

Empezamos por agregar una columna auxiliar a la tabla de datos para identificar el número de aparición del vendedor (suponemos que un vendedor no puede aparecer en dos zonas distintas)



En la columna “aux” usamos la fórmula =CONTAR.SI($B$2:B2,B2).

Luego creamos una tabla dinámica poniendo los campos Región y Agente en el área de filas, el campo “aux” como filtro del informe y el campo “Agente” también en el área de valores. Como éste no es un valor numérico, Excel usa CUENTA para totalizar los valores



Como puede verse, filtramos el informe poniendo el valor “1” en el campo “aux”.
Otra variación es usar la tabla dinámica como “motor de cálculo” y usar la función IMPORTARDATOSDINAMICOS para extraer el valor requerido.



Sencillamente ponemos en la celda C3 “=” y apuntamos a la celda correspondiente en la tabla dinámica; Excel crea la fórmula

=IMPORTARDATOSDINAMICOS("Agente",Hoja4!$A$3,"Region","Este")

Ahora remplazamos “Este” en la función por una referencia a la celda C2



Cada vez que remplazamos el valor de C2 por otra región, la fórmula se actualiza. El cuaderno que aparece abajo es interactivo.

6 comments:

Lucio Arango 18 agosto, 2012 16:30  

Hola Jorge se me ocurre que podriamos crear una columna auxiliar con la siguiente fórmula matricial (en la columna C por ejemplo) : =1/SUMA((Region=A2)*(Agente=B2)) Luego si queremos la cantidad de valores diferentes =SUMA(C2:C24) o si deseamos la cantidad de valores únicos (los que aparecen solo una vez) la siguiente fórmula matricial =SUMA((C2:C24=1)*1)

Natxo G 22 agosto, 2012 17:14  

Hola, Jorge. Se me ocurre que otra alternativa podría consistir en introducir esta fórmula en la columna auxiliar C:

=(CONTAR.SI.CONJUNTO($A$2:A2;A2;$B$2:B2;B2)=1)*1

La solución de Lucio me ha gustado bastante también, ya que ofrece un "ratio de repetición" que podría ser de utilidad en hipotéticos cálculos posteriores.

Una vez más, felicidades por tu estupendo blog y un saludo desde Bilbao.

Anónimo,  01 septiembre, 2014 18:01  

Hola Jorge,
En su post comenta; Empezamos por agregar una columna auxiliar a la tabla de datos para identificar el número de aparición del vendedor (suponemos que un vendedor no puede aparecer en dos zonas distintas)
Cómo sería la solución si pudiera aparecer en zonas distintas?
gracias!

Jorge Dunkelman 01 septiembre, 2014 21:06  

Fijate en los comentarios de Luciano y Natxo. Si usas la fórmula de Luciano (que es matricial, es decir se introduce apretando simultáneamente Ctrl-Mayusc-Enter) no usamos filtro en la tabla de datos y ponemos la colunma Aux en el campo de los valores.
Si usas la fórmula de Natxo tienes crear la tabla tal como muestro en la nota (poniendo el campo Agente en el área de datos y Aux como filtro con el valor 1).

Anónimo,  02 septiembre, 2014 13:39  

Hola de nuevo Jorge, cada vez me doy cuenta de lo amateur que soy la persona que ha escrito el anterior comentario. Estoy intentando aplicar la formula matricial sin embargo me da error..tengo la siguiente duda con =1/SUMA((Region=A2)*(Agente=B2)) no estás sumando datos? por eso me dará error?
Un saludo y gracias por su mágnifico blog,
Tania

Jorge Dunkelman 02 septiembre, 2014 21:15  

Hola Tania, la fórmula divide el número de veces que aparece el vendedor. Es decir, si un vendedor aparece en tres celdas (filas) el resultado en cada celda de la columna auxiliar será 1/3. Luego, en la tabla dinámica, al totalizar el vendedor el resultado será 1 (1/3 * 3).
Si te da error, posiblemente se deba a que no estás introduciendo la fórmula en forma matricial (pulsar simultáneamente Ctrl-Mayúsucula-Enter al introducir la fórmula en la celda).

Publicar un comentario

Google+ Followers

Seguidores

Google+ Badge

Términos Legales

  © Blogger template On The Road by Ourblogtemplates.com 2009

Back to TOP