Suma y promedio de los valores mayores de una lista.

sábado, mayo 31, 2008

Supongamos esta lista de personas (Pedro, Rubén y Marcos) con valores arbitrarios para los meses de enero hasta octubre



Definimos dos nombres, uno que contiene el rango A2:A31 (Personas) y otro que contiene el rango C2:C30 (Valores)



Ahora podemos calcular con facilidad el total y el promedio de los valores para cada una de las personas



La función para SUMA es

=SUMAR.SI(personas;$E2;valores)

y para PROMEDIO

=SUMAPRODUCTO((personas=E2)*valores)/CONTAR.SI(personas;E2)

Para el promedio también pueden usar esta fórmula matricial:

={PROMEDIO(SI(((personas=E2)*valores)<>0;valores;FALSO))}

Un lector me consultaba hace unos días como calcular la suma (o el promedio) de los cinco mayores valores de cada persona.

Para esta tarea usamos esta fórmula matricial, que a continuación explicaré


={SUMA(K.ESIMO.MAYOR(((personas=$E10)*valores);FILA(INDIRECTO("1:"&$G$6))))}



La clave de esta fórmula es la función K.ESIMO.MAYOR. Esta función tiene dos argumentos: una lista de valores y el número de orden del valor que queremos obtener. Al usar esta función en una fórmula matricial obtenemos una matriz de valores de acuerdo al criterio que fijamos con la función.
La expresión (personas=$E10)*valores) genera una matriz con los valores del rango "valores" que corresponden a Pedro o cero si corresponde a un nombre distinto.
Para indicar que queremos, por ejemplo, los cinco valores mayores, tenemos que poner como segundo argumento de K.ESIMO.MAYOR una matriz como {1,2,3,4,5}. En lugar de esto usamos FILA con INDIRECTO, poniendo en la celda G6 la cota superior de nuestra matriz. En nuestro ejemplo

FILA(INDIRECTO("1:"&$G$6))

dentro de a fórmula matricial genera la matriz {1,2,3,4,5}. La ventaja de este método es que con cambiar el valor en G6, cambiamos la cantidad de elementos a ser tenidos en cuenta, sin necesidad de reescribir la fórmula.

Si queremos hacer las operaciones para los cinco menores valores de cada persona, por ejemplo, tenemos que complicar un poco nuestra fórmula (que ya dista de ser sencilla!)

={SUMA(K.ESIMO.MENOR(SI((personas=$E17)*valores<>0;(personas=$E17)*valores;FALSO);FILA(INDIRECTO("1:"&$G$6))))}



En esta fórmula tenemos que agregar la función SI para eliminar los ceros que aparecen en la matriz (personas=$E17)*valores
En la fórmula para calcular los valores mayores estos ceros no "molestan" ya que de por si son los menores. Pero cuando queremos calcular los valores menores de cada persona, tenemos que eliminarlos.
Para ver este problema más claramente podemos usar el botón Evaluar de la barra de herramientas Auditoría de fórmulas



Como ven la matriz generada contiene ceros en los valores que no corresponden al criterio elegido ("Pedro" en nuestro ejemplo). Al usar la función SI

SI((personas=$E17)*valores<>0;(personas=$E17)*valores;FALSO)

dentro de la fórmula matricial, obtenemos FALSO en lugar de ceros




Esto nos permite calcular correctamente nuestra suma y promedio para los valores menores del rango.



El cuaderno con el ejemplo se puede descargar aquí.

Buen fin de semana y mis disculpas por la demora en responder a las muchas consultas que llegan a mi mail. Lamentablemente algunas de ellas quedarán sin responder.

Technorati Tags:

12 comments:

* Evil - Liver * 25 febrero, 2009 13:49  

Hola.

Muy buena la entrada, me acabas de salvar la vida.

¡Sigue así!

Anónimo,  17 noviembre, 2009 23:54  

Hola Jorge
¿Cómo puedo poner una fórmula matricial que tenga como criterio a 2 personas (por ejemplo Pedro y Rubén) y sume la columna valores para esas 2 personas?
Sólo me sale si hago algo así como
{=SUMA((A2:A16="Pedro")*C2:C16)+SUMA((A2:A16="Rubén")*C2:C16)}
Pero así estaría usando suma 2 veces y es medio trampa. Eso lo podría hacer también con Sumar.Si sin fórmula matricial. Intenté meter una función O anidada pero no me lo hizo bien.
Gracias por tu tiempo,
Sergio

Jorge L. Dunkelman 18 noviembre, 2009 07:11  

Sergio,
he tocado el tema en varias notas del blog. Fijate en esta nota.
EN Excel 2007 tienes otras posibilidades como SUMIFS, por ejemplo.

Anónimo,  19 noviembre, 2009 13:01  

Increíble. Termino de escribirte y veo que en tu nota dice que hay que utilizar + en lugar de * cuando se trata de aplicar una O en el mismo rango. Era así de sencillo y no lo había leído bien, MEA CULPA.

Perdoname por haberte mareado tanto con mis comentarios pero ahora ya lo tengo claro.

Un saludo,
Sergio

evelyn 12 septiembre, 2012 23:44  

hola,

tengo una consulta, mira tengo una matriz con una columna de fechas y distintos valores asociados a cada fecha, pero las fechas se repiten, es decir, siempre hay más de un 1-1-12 por ejemplo, lo que necesito es obtener el promedio de los valores asociados por cada día. es posible de hacer???

Jorge L. Dunkelman 13 septiembre, 2012 08:58  

Evelyn,

tal como muestro en la nota

=SUMAPRODUCTO((personas=E2)*valores)/CONTAR.SI(personas;E2)

donde en lugar de "personas" hay que poner el rango de las fechas.

También puede hacerse con facilidad usando tablas dinámicas.

Anónimo,  15 septiembre, 2013 18:06  

HOLA .. TENGO UNA COLUMNA 5 DE NOTAS LA CUAL REQUIERO SACAR el promedio de notas considerando la eliminación de las 2 menos notas de las cinco notas. ES POSIBLE OBTENER DICHO RESULTADO

Jorge Dunkelman 16 septiembre, 2013 09:07  

Tendrías que adaptar que adaptar la fórmulas para los valores mayores, ya que lo que buscas es obtener los tres mayores.

Anónimo,  25 febrero, 2014 19:38  

Hola tengo una pregunta, como puedo sacar el promedio de 5 notas eliminando las dos notas mas bajas.

Jorge Dunkelman 03 marzo, 2014 19:48  

Si te fijas es la misma consulta del comentario del 15 de setiembre del 2013. La respuesta est[a en mi comentario del d[ia siguiente.

Pedro Luis 11 diciembre, 2015 07:12  

COMO PUEDO SACAR UN PROMEDIO DE LAS 5 NOTAS MAS ALTAS SI TENGO 7 NOTAS

Jorge Dunkelman 11 diciembre, 2015 09:32  

Usando la técnica explicada en la nota.

Publicar un comentario

Google+ Followers

Seguidores

Google+ Badge

Términos Legales

  © Blogger template On The Road by Ourblogtemplates.com 2009

Back to TOP