Promedio ponderado en tablas con filas ocultas

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.

Criterios aplicados en Autofiltro - tip

Hemos recibido una hoja de Excel con una tabla  que contiene miles de filas y filtrada con Autofiltro. ¿Cómo hacemos para ver qué criterios han sido aplicados?

A partir de Excel 2007 podemos hacerlo con facilidad. Todo lo que hay que hacer es apuntar con el mouse al icono del filtro (el "embudo") y esperar unos segundos

El (no tan) misterioso caso de la columna A oculta

Las últimas semanas he estado muy ocupado como ya habrán notado buena parte de mis lectores por la demora en responder a sus consultas o por las pocas publicaciones en el blog. Buena parte del tiempo que suelo dedicarle al blog ha sido invertido en el desarrollo del modelo de la factura con base de datos, que espero publicar en breve (y de paso, gracias por adelantado a los correctores por las observaciones y sugerencias que me ha hecho llegar).

Este jueves viajo a Berlín, la ciudad natal de mi madre, por una semana. Por este motivo y aunque trataré de hacerlo, no puedo prometer responder a las consultas que me lleguen.

Mientras tanto dedicaré esta nota al problema de la columna A que ha sido ocultada y no parece haber forma de volver a mostrarla. En realidad no se trata de un problema y mucho menos de un bug. El usuario desprevenido, en particular el principiante, puede pasar por alto algunos detalles y no ver la forma de hacerlo.

Supongamos que alguien no ha mandado este cuaderno

Normalmente seleccionamos las columnas adyacentes a la columna o columnas ocultas y las mostramos con Celdas-Formato-Mostrar Columnas o con el menú contextual (seleccionar las columnas y usar el botón derecho del mouse). Pero en este caso parece que no hay forma de seleccionar la columna A. Hay varias formas de hacerlo:

• hacemos un clic sobre la letra de la columna B para seleccionar toda la columna y arrastramos el mouse hacia la izquierda; de esta manera seleccionamos ambas columnas.
• Lo mismo podemos hacer usando Ir a-Referencia (apretando F5 o Ctrl-I).
• En el cuadro de nombres (en la imagen de arriba, el rectángulo arriba de la letra B donde se ve "B1") escribimos "A1" y apretamos ENTER. Esta acción selecciona la celda A1; luego usamos Celdas-Formato-Mostrar Columnas.
• Usamos el triángulo que aparece a la izquierda de "B" y arriba de "1" para seleccionar todas la hoja y usamos alguna de las opciones mencionadas para mostrar las columnas. Esta acción, a diferencia de las anteriores, mostrará todas las columnas ocultas de la hoja.

Hay una situación en la cual estos métodos no producen el resultado esperado. Cuando ocultamos una columna Excel define el ancho a 0

Al usar "Mostrar", Excel reestablece el ancho de la columna. Ahora supongamos que, intencionadamente o no, establecemos el ancho de la columna a 0.08 (o 0,08 según usen el punto o la coma como separador)

Como pueden apreciar, la diferencia con el caso anterior es prácticamente imperceptible. De hecho la columna está oculta pero los métodos para mostrarla no funcionarán ya que el ancho no es cero. Para mostrarla tenemos que, sencillamente, cambiar el ancho.