Promedio ponderado en tablas con filas ocultas

sábado, mayo 24, 2014

Ya hemos tocado el tema de cómo calcular el promedio ponderado con Excel, si bien en la prehistoria de este blog, allá por el año 2006.

A diferencia del promedio normal, el promedio ponderado toma en cuenta el "peso" relativo de cada uno de los datos en relación al resto de los datos.
Por ejemplo, en este caso


El precio promedio, sin tomar en cuenta las cantidades vendidas por cada sucursal, es 15.50. Pero si tomamos en cuenta el "peso" de cada sucursal, vemos que en la mayor parte de las ventas el precio está por debajo del promedio, por lo cual el promedio ponderado noos da 12.84.
El promedio ponderado lo calculamos con la fórmula

=SUMAPRODUCTO(C3:C6,D3:D6)/SUMA(C3:C6)

Alternativamente podríamos agregar totales a la tabla y calcular el promedio ponderado dividiendo el total de venta (cantidad x precio) por el total de piezas vendidas

Ahora vamos a complicar las cosas y ver cómo podemos calcular el promedio ponderado en tablas filtradas (tablas en las que hemos aplicado Autofiltro u ocultado filas).

Veamos este ejemplo


El promedio sencillo del precio lo podemos calcular con SUBTOTALES, que ignora las celdas ocultas



Para calcular el promedio ponderado se nos presenta el problema el problema que, a diferencia de la función SUBTOTALES, SUMPRODUCTO toma en cuenta también las celdas ocultas en el rango.

Una forma de calcular el promedio ponderado en listas filtradas es usar columnas auxiliares (en este caso una única columna  auxiliar), usando la función SUBTOTALES con la opción 103 (CONTARA)


Al referirse a una única celda la función dará un valor 0 si la celda está en una fila oculta y 1 si la fila es visible. Luego podemos usar esta columna para calcular el promedio ponderado usando SUMPRODUCTO

=SUMAPRODUCTO(D4:D15*E4:E15*F4:F15)/SUMAPRODUCTO(D4:D15*F4:F15)



Si queremos evitar el uso de columnas auxiliares podemos usar esta fórmula que combina SUMAPRODUCTO con SUBTOTALES y DESREF

=SUMAPRODUCTO(SUBTOTALES(3,DESREF(B4:B15,FILA(B4:B15)-FILA(B4),,1)),D4:D15,E4:E15)/SUBTOTALES(9,D4:D15)


La expresión

SUBTOTALES(3,DESREF(B4:B15,FILA(B4:B15)-FILA(B4),,1))

da un vector de 1 (fila visible) o 0 (fila oculta). Para comprobarlo podemos seleccionar la expresión en la barra de las fórmulas y apretar F9 para ver el resultado



Esta fórmula de autoría incierta (posiblemente Bob Philips), aparece en varios foros de Excel.

8 comments:

Manukino 25 mayo, 2014 21:58  

Que fuerte Jorge. Esto son matemáticas puras no?

Jorge Dunkelman 26 mayo, 2014 06:45  

Más bien diría que es una prueba más de que siempre se puede encontrar la forma de hacer cálculos con Excel.

Carlos Pascual 26 mayo, 2014 20:37  

Jorge,
Ante todo felicitarte y agradecerte tanta divulgación de conocimiento y socialización del saber. Dicho esto queria consultarte si pudieras orientarme como poder establecer un "predictivo" de la cantidad de efectivo que pudiera dispensar un Cajero Automatico en los distintos dias de la semana y del mes, previendo la estacionalidad del mes y del año. Los datos con los que cuento son variados, pero considero que en relación a un historico de los días, cantidad de transaccion y dinero dispensado entiendo que lo debería poder establecer.
Quedo al aguardo de tu ayuda.
Desde ya muchisimas gracias.

Jorge Dunkelman 29 mayo, 2014 07:45  

Carlos, si cuentas con los datos diarios, puedes calcular promedios para días y meses del año. Pero, ¿cómo puedo ayudarte?

franciscoM1972 27 junio, 2014 20:55  

Jorge, buenas tardes muchas gracias por compartir tus conocimientos y mantener hasta el dia de hoy este gran e interesante blog.
He estado realizando algunos ejercicios con SUMAPRODUCTO y sin resultados mi problema es el siguiente, yo trabajo en un Call Center y lo que tengo que sacar es el Tiempo Promedio PONDERADO del Ejecutivo en forma mensual en la HOJA 1 tengo los datos :
LOGINID I Nombre Ejecutivo I Servicio I LLamadas I TMO
85670 Juanito mau 20 0:02:30
85670 Juanito saet 8 0:03:45
86489 Maria saet 5 0:01:55

Esta información va a variar mes a mes , va a depender de cuantos servicios se les asigno al ejecutivo, ahora he tratado que en la Hoja B, me traiga el TMO ponderado con condiciones , es decir mi clave primaria es el LOGINID pero no logro combinar las condiciones en la funcion, la idea es que me tome todos los registros que tuvo ej ejecutivo en el mes y me de ponderado el promedio.

HOJA B

LOGINID NOMBRE TMO PONDERADO

85670 Juanito """ ERROR""""
86489 Maria

realmente no se que es lo que no me funciona....

=SUMAPRODUCTO('DATOS HOJA A'!R2:R20;'DATOS HOJA A'!M2:M20)/SUMA('DATOS HOJA A'!M2:M20)

gracias

Jorge Dunkelman 28 junio, 2014 09:00  

Francisco, por favor fijaate en lo que pongo en el enlace Ayuda (en la parte superior de la plantilla) y ponte en contacto conmigo por mail privado.

Raúl Galindo Blanco 22 octubre, 2014 00:32  

Quiero comentar un asunto que tiene algo...de relación con este post.
Aplico fórmula de promedios en una celda y me aplica el redondeo; así:
6.3 aparece en pantalla; si yo doy clic en la celda me despliega 6.2666667. Yo necesito que el valor quede como 6.3 porque en otra celda se efectúa un cálculo con el valor de redondeo y no con el valor exacto. El tipo de dato debe ser numérico. ¿Es posible esto? Muchas gracias

Jorge Dunkelman 22 octubre, 2014 17:45  

SI, anidando PROMEDIO en REDONDEAR. Por ejemplo, =REDONDEAR(PROMEDIO(rango de valores),1)

Publicar un comentario

Google+ Followers

Seguidores

Google+ Badge

Términos Legales

  © Blogger template On The Road by Ourblogtemplates.com 2009

Back to TOP