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
