Contar valores únicos en un rango de Excel

jueves, agosto 02, 2007

En ciertas situaciones necesitamos saber cuantos valores únicos hay en un rango. Supongamos una hoja Excel donde tenemos esta tabla:



Si queremos calcular las ventas promedio por agente, tenemos que saber primero cuántos agentes hay en nuestra lista. En nuestro ejemplo hay cuatro agentes, Pedro, Roberto, Juan Carlos y Alberto, en doce filas de la tabla. Pero en la vida real nos enfrentamos con tablas que tienen cientos o miles de filas y por lo tanto necesitamos una forma más práctica de contar.
Para contar cuantos valores únicos hay en una lista usamos esta fórmula matricial:

={SUMA(1/CONTAR.SI(rango;rango))}

donde rango es un nombre que define el rango de celdas donde queremos contar los valores únicos

En nuestro caso rango = A2:A13, nuestra fórmula será

={SUMA(1/CONTAR.SI($A$2:$A$13,$A$2:$A$13))}



Esta es una buena oportunidad para volver a explicar cómo funcionan las fórmulas matriciales.

En primer lugar recordemos que al introducir estas fórmulas en una celda, apretamos simultáneamente Ctrl+Mayúsculas(Shift)+Enter. Los corchetes aparecen al introducir la fórmula de esta manera, y no deben ser puestos por el usuario.

Para ver los pasos del cálculo de la fórmula matricial, hay que pulsar el botón "Ver Detalle".

En nuestra fórmula matricial, la función CONTAR.SI crea una matriz de resultados que es número de veces que cada nombre aparece, como vemos en la columna B donde hemos introducido esta formula (no matricial)
=CONTAR.SI($A$2:$A$13,A2)



En la columna C calculamos la inversa de los valores de la columna B, por ejemplo en C2 ponemos la fórmula =1/B2

Luego sumamos todos los valores de la columna C, lo que nos da el número de valores únicos en el rango.

Todo esto es hecho en una sola fórmula, usando la técnica de fórmulas matriciales. El archivo con el ejemplo se puede valores unicosdescargar aquí.

Technorati Tags:

27 comments:

Anónimo,  03 agosto, 2007 01:31  

Muy buen trabajo el que estas realizando con este blog sobre excel. A mi me esta ayudando mucho y dandome ideas nuevas y frescas.
Como no domino bien el tema de los blogs, te dejo esta pregunta aqui ya que no he visto otro modo.

Tengo una salida de un programa en formato csv, que no tengo ningun problema para abrir con excel, en el que tengo una serie de datos que tengo que pasar a otro programa distinto.
El problema viene dado porque en el programa destino hay que introducir los datos en formato ascii y con unas posiciones fijas para que las reconozca como tal.
¿Seria posible desde excel fijarle el tamaño para cada columna, entendiendo como tamaño los caracteres que debe tener ese campo? Si utilizo el formato de celda no tengo claro que a la hora de guardarlo conserve los datos tal y como aparecen en excel.
Y sobre todo, ¿seria posible guardar desde excel en ese formato?
Todo viene dado porque queremos cambiar la aplicacion que nos confecciona las nominas, y el nuevo programa solo admite como entrada un fichero secuencial.
No se si excel es lo mas indicado pero dados mis conocimientos y el requerimiento de la empresa para llevar a cabo el proyecto sin mucho coste añadido, he pensado que desde excel se podria intentar.
Gracias de antemano por la atencion.

Jorge L. Dunkelman 03 agosto, 2007 23:53  

Si, se puede.
No extiendo el comentario aquí por dos motivos. El primero es que la consulta no está relacionada con el tema de la nota (siempre se me puede consultar vía e-mail: jorgedun@gmail.com). La segunda es que tengo planeado publicar una nota sobre el tema.

AeroMartin 05 agosto, 2007 00:14  

Jorge: esta matriz que vos mostras funciona sobre una columna en donde ya tenemos la lista definida, pero hay alguna opcion de que en la funcion este contemplados las celdas vacias asi por ejemplo uno puede setear la funcion desde la celda a2:a300 aunque nuestra lista actual llegue hasta el a150. asi cada vez que se agregan valores no hay que modificar la lista.

saludos
martin

Anónimo,  05 agosto, 2007 04:23  

q tal jorge
estoy comenzando a usar excel por lo que no tengo mucha experiencia, e visto tus trabajos en excel y me parecen muy interesantes, quisiera saber con q formula puedo hacer citas al medico cada seis meses y me resalte cuando ya les toque en el mes, e tratado de hacerlo pero no e podido te agradecere tu ayuda

saludos cordiales
brian fdo.

Jorge L. Dunkelman 05 agosto, 2007 21:16  

Hola Martín,
si, existen opciones. En el caso que proponés, donde el rango es continuo, pero hay celdas vacías al final, podes usar un nombre conteniendo un rango dinámico. Si se trata de un rango no continuo (con celdas vacías entre el primer y el último miembro del rango) podés usar esta fórmula matricial:
={SUMA(SI(CONTAR.SI($A$2:$A$13;A2:A13)=0;"";1/CONTAR.SI($A$2:$A$13;$A$2:$A$13)))}

Jorge L. Dunkelman 06 agosto, 2007 19:14  

Hika Brian

fijate en las notas sobre Formato COndicional.

Anónimo,  11 octubre, 2007 20:55  

Hola jorge, ignoro muchas cosas de excel y esta pagina esta muy buena para empezar a aprender. Bueno despues de los alabos te pregunto como puedo hacer para contar con dos o mas columnas (ej: nombre, apellido y cuidad) ya que trate de anidarlas con el contexto adicional y no me funciono, estaría muy agradecido si me ayudaras

Luis Martinez
bombero8@gmail.com
PD: Muy buena la pagina

Anónimo,  11 octubre, 2007 20:59  

Hola jorge, ignoro muchas cosas de excel y esta pagina esta muy buena para empezar a aprender. Bueno despues de los alabos te pregunto como puedo hacer para contar con dos o mas columnas (ej: nombre, apellido y cuidad) ya que trate de anidarlas con el contexto adicional y no me funciono, estaría muy agradecido si me ayudaras

Luis Martinez
bombero8@gmail.com
PD: Muy buena la pagina

Jorge L. Dunkelman 12 octubre, 2007 09:13  

Hola Luis

no se si entiendo tu consulta, pero me parece que lo que tienes que hacer es crear una columna auxiliar cuyos valores sean la concatenación de las columnas donde quieres contar. Para eso usas el operador "&" o la función CONCATENAR.

Anónimo,  12 octubre, 2007 22:37  

VERDAD!!!
Gracias, pero la idea era la siguiente:

nombre Apellido Ciudad
luis martinez valparaiso
luis martinez viña del mar
luis martinez valparaiso
luis araya valparaiso
andrea araya valparaiso


la cosa era que cuente cuantas personas hay en total, en el ejemplo hay 4 personas distintas, ya que si bien hay 2 luis martinez son distintos ya que viven en ciudad distinta (sorry por el egocentrismo xD). Mi pregunta era como quedaria la funcion CONTAR si se sabe que se pueden tener los mismos nombres y los mismos apellidos, pero como ves pueden pertenecer a otra ciudad.
Igual me sirve la idea de concatenar, pero si es posible me darias una idea de como quedaria comparando las 3 juntas sin concatenar? porque puede ser que tambien lo tenga que usar con numeros y comparaciones logicas. De ante mano muchas gracias.


PD: he estado viendo el blog y me ha servido mucho, esta muy bueno... sigue asi

Jorge L. Dunkelman 13 octubre, 2007 14:53  

Tendrías que usar la función SUMAPRODUCTO de la siguiente manera:
=SUMAPRODUCTO((rango de Nombres)="Luis";(rango de Apellido)="Martinez";(rango de Ciudad)="Valparaiso").
Puedes leer esta nota sonre Contar condicional con más de un criterio.

Anónimo,  03 noviembre, 2010 18:59  

Hola a todos, especialmente a ti Jorge, solo una consulta soy certificado MOS pero parace que tengo la certificacion por las puras, pero bueno mi consulta es:

Tengo la siguiente lista:

Cliente Venta Vendedor
Juan 1000 Gabriel
Rosa 2300 Gabriel
Angel 1700 Susana
Milagros 500 Yolanda
Juan 800 Gabriel
Daniel 1560 Susana

Mi pregunta es : como hago para contar la cantidad de clientes atendidos por Gabriel, sin que estos clientes se repitan, en el ejemplo solo deberia mostrarme 2 clientes porque Juan compro 2 veces y solo se deberia de contar como uno. Que funcion utilizo estoy intentando con una matricial pero no lo logro.

Atte. MOS

Jorge L. Dunkelman 03 noviembre, 2010 21:00  

Por ahora sólo te puedo ofrecer una solución usando tablas auxiliares (puedes descargar el ejemplo aquí). Se podría hacer también una UDF (función definida por el usuario).

Jim McLean 09 marzo, 2011 10:49  

Buenas Jorge y enhorabuena por el blog.
¿Cómo se pueden contar valores únicos en una tabla dinámica si no se puede modificar la tabla de datos origen? He visto soluciones en http://www.contextures.com/xlPivot07.html pero siempre con tabla auxiliar.
Agradecido de antemano por la respuesta

Jorge L. Dunkelman 09 marzo, 2011 20:18  

La solución que en Contextures es la única que conozco que funciona dentro de una tabla dinámica. En realidad no usa una tabla auxiliar sino que agrega un campo a la base de datos para generar la cuenta.
¿Por qué no usar esa solución?

Anónimo,  28 junio, 2011 14:29  

Hola Maestro!
Cómo se puede incorporar la función de contar distintos en una tabla dinámica? es posible?
Tengo códigos de clientes que se repiten y la fórmula de CONTAR no me los distingue, qué puedo hacer?
GRACIAS MIL!

Jorge L. Dunkelman 28 junio, 2011 21:28  

No entiendo qué es lo que quieres hacer en la tabla dinámica. Si queremos daber cuantas veces aparece un cliente, basta con poner el campo "código cliente" en el área de datos y usar la función CONTAR.

Anónimo,  20 abril, 2012 14:19  

Hola Jorge, no sé si debido a las actualizaciones del Paquete Office u otro motivo, actualmente no funciona el ejemplo que muestras:

=SUMA(1/CONTAR.SI($A$2:$A$13,$A$2:$A$13))

He estado probando cosas y buscando por internet, y para que funcione, simplemente hay que sustituir "SUMA" por "SUMAPRODUCTO"

Un saludo

Jorge L. Dunkelman 20 abril, 2012 21:23  

Estimado

no tiene que ver con las versiones de Excel. Si usas SUMA hay que aplicar la fórmula en forma matricial (introducirla apretando simultáneamente Ctrl-Mayúsculas-Enter). SUMAPRODUCTO funciona como función matricial sin necesidad de combinar las teclas Ctrl-May.-Enter.

Tomas Morra,  10 agosto, 2012 22:42  

Jorge,
Quería saber si me podes ayudar a contar valores sin sus duplicados pero ademas que cada fila a contar cumpla con una condición.
Si bien se como contar valores como lo has explicado previamente, no le puedo agregar una condición adicional, en referencia al ejemplo podría ser contar los agentes que sean de una región determinada.

Jorge L. Dunkelman 18 agosto, 2012 12:45  

Hola Tomás,
disculpas por la demora. En el caso que presentas mi propuesta sería usar una tabla dinámica en lugar de complicarnos con fórmulas. Estaré publicando una nota sobre el tema.

MatildaMouse.ar@gmail.com 21 agosto, 2012 21:15  

Hola Jorge.
perdon si te molesta que te haga una consulta de un post viejo; pero llegue a este a aprtir del ultim ode las 2 condiciones.
mi consulta es por el ejemplo que le diste al usuario que deseaba no contar las celdas vacias entre las celdas con valor unico :
={SUMA(SI(CONTAR.SI($A$2:$A$13;A2:A13)=0;"";1/CONTAR.SI($A$2:$A$13;$A$2:$A$13)))} ,

pero no me funciona a pesar de modificarla a :
={SUMA(SI(CONTAR.SI($A$2:$A$13;A2:A13)="";0;1/CONTAR.SI($A$2:$A$13;$A$2:$A$13)))}, y validar que mis celdas vacias son iguales a "".
me cuenta un "registro unico" de mas, por las celdas vacias.
quisiera que me confirmaras si es correcta la formula y en todo caso, que puede estar sucediendo.
desde ya , muchisimas gracias por el aporte.

Jorge L. Dunkelman 23 agosto, 2012 15:06  

Matilda, una celda que contiene un texto vacío ("") bo está vacía. El hecho que no muestre ningún contenido no significa que no lo tenga. En caso que tengas celdas con fórmulas que dan un texto vación (por ejemplo =SI(A1=1;"aaa";"")), tendrías que usar la condición =LARGO(celda a evaluar)=0

Hector Sosa 04 junio, 2013 16:37  

Para evitar los espacios en blanco:
{=SUMAPRODUCTO(SI(FRECUENCIA(SI(LARGO(E4:E100)>0,COINCIDIR(E4:E100,E4:E100,0),""), SI(LARGO(E4:E100)>0,COINCIDIR(E4:E100,E4:E100,0),""))>0,1))
recordar indicar fórmula matricial

Jorge Dunkelman 05 junio, 2013 07:11  

Hola Héctor,
gracias por el aporte. Sería bueno si agregaras a tu comentario una explicación sobre la fórmula, para los lectores menos experimentados.

Javier Sitges,  04 enero, 2014 19:44  

Hola Jorge:

Estoy tratando de utilizar ambas fórmulas (evitando o no los espacios en blanco) y me es muy complicado, el trabajar en la hoja se ralentiza enormemente) y en general no me es práctico, ya que el problema lo sigo teniendo cuando genero una tabla dinámica a partir de la hoja; me sigue contando valores duplicados. Hay alguna forma de que cuando genero la tabla dinamica sólo considere los valores únicos. Es excel 2010. Gracias

Jorge Dunkelman 05 enero, 2014 07:43  

Hola Javier, no me queda claro tu problema (el planteo en el foro de LInkedIn es un distinto). Cuando generas una tabla dinámica ésta regfleja los registros de la hoja donde se encuentran los datos. ¿Dónde quieres que aparezcan los valores únicos, en el área de los datos? En el área de las filas o de las columnas siempre te aparecen datos únicos (es decir, un valor para cada dato).

Publicar un comentario

Google+ Followers

Seguidores

Google+ Badge

Términos Legales

  © Blogger template On The Road by Ourblogtemplates.com 2009

Back to TOP