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: MS Excel
Hola.
ResponderBorrarMuy buena la entrada, me acabas de salvar la vida.
¡Sigue así!
Hola Jorge
ResponderBorrar¿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
Sergio,
ResponderBorrarhe tocado el tema en varias notas del blog. Fijate en esta nota.
EN Excel 2007 tienes otras posibilidades como SUMIFS, por ejemplo.
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.
ResponderBorrarPerdoname por haberte mareado tanto con mis comentarios pero ahora ya lo tengo claro.
Un saludo,
Sergio
hola,
ResponderBorrartengo 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???
Evelyn,
ResponderBorrartal 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.
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
ResponderBorrarTendrías que adaptar que adaptar la fórmulas para los valores mayores, ya que lo que buscas es obtener los tres mayores.
ResponderBorrarHola tengo una pregunta, como puedo sacar el promedio de 5 notas eliminando las dos notas mas bajas.
ResponderBorrarSi 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.
ResponderBorrarCOMO PUEDO SACAR UN PROMEDIO DE LAS 5 NOTAS MAS ALTAS SI TENGO 7 NOTAS
ResponderBorrarUsando la técnica explicada en la nota.
ResponderBorrar