miércoles, 3 de febrero de 2010

Promedio con la condición O.

Explicamos en una entrada de hace algunos días el empleo de la función PROMEDIO.SI.CONJUNTO, donde podíamos aplicar diferentes criterios o condiciones a campos de nuestra base de datos, obteniendo el promedio de los valores de uno de esos campos. Recordad que es una función similar a SUMAR.SI.CONJUNTO.
Todas estas funciones condicionales, en general, tienen un inconveniente, y es que no admiten la condición 'O' sobre un mismo campo. Lo vemos más claro con un ejemplo.
Tenemos nuestra siguiente tabla de datos:


Sabiendo que los rangos tienen los siguiente nombres:
día =Hoja1!$A$2:$A$22
importe1=Hoja1!$B$2:$B$22
importe2=Hoja1!$C$2:$C$22
importe3=Hoja1!$D$2:$D$22

Si quisieramos obtener un promedio de los campos 'Importe1', 'Importe2' e 'Importe3' pero sólo de los días que sean 'lunes', esto es, cuyo campo 'día' sea lun, fácilmente aplicamos la función:
=PROMEDIO.SI.CONJUNTO(importe1;día;"lun")
es decir, he podido aplicar un sólo criterio a un campo.
¿Y si necesito saber el promedio del campo 'Importe1' para los fines de semana, es decir, para aquellos valores del campo 'día' que sean 'sáb' o 'dom'? (lo importante aquí es el concepto de unión, i.e, el operador lógico O).
En estos casos, implementar una función PROMEDIO.SI.CONJUNTO con esta condición se complica, yo al menos no he sido capaz; por lo que la solución pasaría por ejecutar una función matricial que reuna los requisitos para obtener el resultado esperado.

Lo primero que necesitamos es sumar todos aquellos valores del campo 'Importe1' coincidentes con los días 'sáb' o 'dom', lo que sacamos de la fórmula matricial:

{=(SUMA((día="sáb")*importe1)+SUMA((día="dom")*importe1))}

cuando sea VERDADERO que el valor del campo 'día' es 'sáb', lo multiplicamos por el valor del campo 'Importe1'. Recordemos que a efectos de cálculo VERDADERO es uno y FALSO es cero. De igual forma para los valores de 'día' que sean 'dom'.
Con esta función obtengo el sumatorio de todos los valores que coincidan con 'sáb' o 'dom'.
Para calcular el promedio sólo me queda, entonces, dividir ese sumando por el número de registros que coincidan con 'sáb' o 'dom'; de manera muy similar podría formular:

{=SUMA((día="sáb")*1)+SUMA((día="dom")*1)}

o también

=(CONTAR.SI(día;"sáb")+CONTAR.SI(día;"dom"))

ya es fácil sacar nuestro promedio del campo 'Importe1' para los fines de semana ('sáb' o 'dom')

Por último, si quiero conocer el promedio de los días laborables escribiría:
{=(SUMA(importe1)-SUMA((día="sáb")*importe1)-SUMA((día="dom")*importe1))/(CONTARA(día)-SUMA((día="sáb")*1)-SUMA((día="dom")*1))}
es decir, aplicaría en forma matricial un sumando como complementario del anterior.

Los resultados obtenidos serían:



No olvidemos que en términos de conjuntos el operador Y representa la intersección, de alguna forma la sustracción de elementos; mientras que el operador O es la unión, o lo que es lo mismo la suma de elementos.

2 comentarios:

  1. Hola ExcelForo,
    Una consulta quisiera saber que fórmula as utilizado en la celda $B$26, y si puedes demostrármelo en fórmula normal y Matricial de antemano muchas gracias.
    Atentamente:
    Ana Carrillo.

    ResponderEliminar
    Respuestas
    1. Hola Ana,
      en la celda B26, la fórmula matricial que aparece es
      {=(SUMA((día="sáb")*importe1)+SUMA((día="dom")*importe1))/(SUMA((día="sáb")*1)+SUMA((día="dom")*1))}
      y con fórmulas 'normales':
      =(SUMAR.SI(día;"dom";importe1)+SUMAR.SI(día;"sáb";importe1))/(CONTAR.SI(día;"dom")+CONTAR.SI(día;"sáb"))
      Slds

      Eliminar

Nota: solo los miembros de este blog pueden publicar comentarios.