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
descargar aquí.Technorati Tags: MS Excel
Muy buen trabajo el que estas realizando con este blog sobre excel. A mi me esta ayudando mucho y dandome ideas nuevas y frescas.
ResponderBorrarComo 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.
Si, se puede.
ResponderBorrarNo 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.
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.
ResponderBorrarsaludos
martin
q tal jorge
ResponderBorrarestoy 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.
Hola Martín,
ResponderBorrarsi, 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)))}
Hika Brian
ResponderBorrarfijate en las notas sobre Formato COndicional.
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
ResponderBorrarLuis Martinez
bombero8@gmail.com
PD: Muy buena la pagina
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
ResponderBorrarLuis Martinez
bombero8@gmail.com
PD: Muy buena la pagina
Hola Luis
ResponderBorrarno 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.
VERDAD!!!
ResponderBorrarGracias, 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
Tendrías que usar la función SUMAPRODUCTO de la siguiente manera:
ResponderBorrar=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.
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:
ResponderBorrarTengo 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
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).
ResponderBorrarBuenas Jorge y enhorabuena por el blog.
ResponderBorrar¿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
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.
ResponderBorrar¿Por qué no usar esa solución?
Hola Maestro!
ResponderBorrarCó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!
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.
ResponderBorrarHola Jorge, no sé si debido a las actualizaciones del Paquete Office u otro motivo, actualmente no funciona el ejemplo que muestras:
ResponderBorrar=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
Estimado
ResponderBorrarno 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.
Jorge,
ResponderBorrarQuerí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.
Hola Tomás,
ResponderBorrardisculpas 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.
Hola Jorge.
ResponderBorrarperdon 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.
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
ResponderBorrarPara evitar los espacios en blanco:
ResponderBorrar{=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
Hola Héctor,
ResponderBorrargracias por el aporte. Sería bueno si agregaras a tu comentario una explicación sobre la fórmula, para los lectores menos experimentados.
Hola Jorge:
ResponderBorrarEstoy 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
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).
ResponderBorrarHola Buenas tardes, estaba viendo su formula y me sirvio de mucho solo que aun tengo un problema para completar mi busqueda de datos y conteo. lo que tengo es lo siguiente en la columna A tengo varios numeros id(cuentas) y que se repiten algunos y en la columna B tengo varios ejecutivos asignados a esos id pero lo que tengo es que si un id se repite varias veces tengo asignado al mismo ejecutivo para trabaje ya ese id, an cuando este ejecutivo tenga varias cuentas ya asignadas (Espero haberme explicado). lo que quiero obtener es lo siguiente cuantas cuentas totales tiene asignado ese ejecutivo y de esas cuentas totales cuentas cuantas son unicas osea la cuenta real y asi para cada ejecutivo. espero me puedan ayudar
ResponderBorrarHola, no me queda del todo claro. Te sugiero que me envíes el archivo o un ejemplo con una descripción lo más clara posible del problema. Fijate en las instrucciones en el enlace Ayuda (en la parte superior de la plantilla del blog).
ResponderBorrarSU APOYO:
ResponderBorrar=SUMA(SI(FRECUENCIA(A2:A10;A2:A10)>0;1))
Cuenta el número de valores numéricos únicos en las celdas A2:A10, pero no cuenta las celdas vacías ni los valores de texto.
QUIERO ADICIONAR QUE APLIQUE ESTA FORMULA EN UN RANGO DE FECHAS (FECHA INICIAL Y FECHA FINAL)
Hola Miguel,
ResponderBorrarno me queda claro si tu comentario es una consulta o una colaboración.
Jorge buenas noches, me parece muy chevere los blog que esta implementando es una excelente guía, tengo una consulta respecto a esta función e intentado utilizar esta formula teniendo en cuenta un rango de fecha y también el rango de fecha y la variable <0; >0 y =0, no ha sido posible obtener resultado, he intentado también con suma producto pero en el caso de tener códigos con seriales no he podido traer el total de referencias contadas. agradezco su colaboración.
ResponderBorrarJorge buenas noches, me parece muy chevere los blog que esta implementando es una excelente guía, tengo una consulta respecto a esta función e intentado utilizar esta formula teniendo en cuenta un rango de fecha y también el rango de fecha y la variable <0; >0 y =0, no ha sido posible obtener resultado, he intentado también con suma producto pero en el caso de tener códigos con seriales no he podido traer el total de referencias contadas. agradezco su colaboración.
ResponderBorrarEdwin, tendrías que ser más explícito en la consulta. Te sugiero que pongas en contacto conmigo por mail privado (fijate en el enlace Ayuda, en la parte superior del blog).
ResponderBorrarMejora hecha con vba http://wp.informaticaea.com/contar-unico-vba/
ResponderBorrarBuen aporte, siempre estoy a favor que mis lectores conozcan otros sitios Excel.
ResponderBorrarTe sugiero ver las distantas notas sobre el tema pulsando la eqtiqueta Registos Unicos en la nube de etiquetas. En particular la última sobre cómo hacerlo con PowerPivot.
Estimado Jorge ... agradecido por tu blog (ya te lo diran mucho), el tema de mi consulta es que quiero extraer la cantidad de registros unicos para una condición dada.
ResponderBorrarPor ejemplo de una tabla como la que sigue (la tabla original tienen 6667 registros)
Localidad Certificado
A 3091
A 3091
A 3092
A 3092
B 3093
B 3093
C 3094
etc etc
Necesito que me devuelva
Localidad Certificado
A 2
B 1
C 1
Creo que con esta formula matricial puedo hacer algo pero me falta el condicional para que me la calcule para A, B, C. etc
Si me podes dar una mano en esto ... eternamente agradecido, hoy lo estuve buscando todo el dia y lo mas cerca que encontre es en este blog.
Muchas Gracias ... mi correo m.e.monti@gmail.com
Hola Mario, lamento la demora en responder (recién vuelvo del exterior). Te mandaré la respuesta por mail privado.
ResponderBorrarAh, me olvidaba! Si tenés Excel 2010 o posterior, instalá el PowerPivot y usá la solución que propongo en esta nota.
ResponderBorrarHola, hice el mismoejercicio y me da como resultado 0.5. Aqui la fórmula que utilice para el mismo ejercicio que proponen: =SUMA(1/CONTAR.SI($A$2:$A$13,$A$2:$A$13))
ResponderBorrarAlejandra, es una fórmula matricial que hay que ingresar apretando simultáneamente Ctrl-Mayúsculas-Enter.
ResponderBorrar