Calcular promedio ponderado en tablas dinámicas

Si queremos calcular un promedio ponderado en una tabla dinámica de Excel, lo más probable es que intentemos hacerlo creando un campo calculado. Esto es lo que intentó uno de mis lectores sólo para descubrir que SUMAPRODUCTO no da los resultados esperados cuando se usa en un campo calculado.

Vamos a describir el problema (y luego mostrar la solución). Supongamos esta tabla de datos

Queremos calcular la venta promedio por operación de cada vendedor, es decir, el promedio de las ventas ponderado por la cantidad de operaciones que cada uno efectuó. La idea es obtener los resultados que aparecen en la columna “ponderado” (nótese la diferencia en relación al promedio simple)

Para realizar el cálculo en una tabla dinámica (lo que es muy conveniente si el tamaño y los componentes de nuestros datos cambian con frecuencia) podríamos estar tentados a crear un campo calculado con esta fórmula

=SUMAPRODUCTO('Total de Ventas','Nro. De Ventas' )/SUMA('Nro. De Ventas' )

Pero, como señalo más arriba, esta fórmula no funciona en campos calculados de tablas dinámicas.

La solución es crear un campo auxiliar (promedio ponderado) con esta fórmula


=(D3*E3)/SUMAR.SI($C$3:$C$11,C3,$E$3:$E$11)

Ahora podemos crear la tabla dinámica, donde el promedio ponderado para cada vendedor será calculado en el campo Promedio Ponderado usando la operación Suma

Como ven, la tabla no muestra la fila de totales. Esto se debe a que el total acumulado que mostraría la tabla dinámica para el campo “promedio ponderado” sería incorrecto:

Si queremos mostrar los totales para las columnas “Ventas” y “Nro. De Ventas”, que están calculados correctamente, podemos ocultar el total de “Promedio Ponderado” dándole a la fuente el mismo color que el fondo de la celda volviéndolo de esta manera “invisible”

Validación de datos - Tamaño de la fuente de la lista desplegable

En la última semana tres lectores me han consultado sobre el mismo tema: el tamaño de la fuente en las listas desplegables creadas con validación de datos.

El tamaño de la fuente en estas listas está definido por defecto y no puede ser cambiado. Esto genera dos problemas:

1 – Algunos de los valores de la lista desplegable aparecen “cortados”

2 – Si el zoom de la hoja está por debajo del 100%, los valores puede llegar a ser casi ilegibles

En esta nota mostraré dos estrategias para solucionar estos problemas:

1 – usar eventos para cambiar el ancho de la columna que contiene la celda con la validación de datos y el nivel de zoom de la hoja;

2 – usar una combobox de la colección de controles ActiveX.

Partimos de esta situación donde tenemos una lista de departamentos de una empresa, que hemos incluido en el nombre definido “lstDepartamentos” y una lista desplegable en la celda E2. Las columnas A, B y C nos sirven como columnas auxiliares y en aplicaciones prácticas estarán ocultas. EL nivel de zoom es 80%

Solución con un evento simple.


La estrategia es programar un evento de manera que cuando el usuario seleccione la celda que contiene la lista (E2), el ancho de la columna E pase a ser 30 (antes del evento es 15) y el zoom será del 120%. Al seleccionar cualquier celda fuera de E2, el zoom vuelve a ser del 80% y el ancho de la columna 15.
En el módulo de la hoja correspondiente ponemos este código

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  
    If Target.Address = "$E$2" Then
        ActiveWindow.Zoom = 120
        Range("E2").ColumnWidth = 30
    Else
        Range("E2").ColumnWidth = 15
        ActiveWindow.Zoom = 80
    End If

End Sub

Solución con evento complejo

La estrategia es la misma que en la solución anterior, pero el ancho de la columna será fijado dinámicamente de acuerdo al valor de la lista más largo. Para esto creamos una función UDF (definida por el usuario) para determinar este valor y lo pasamos al código del evento

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    Dim iColWidth As Double
'
    iColWidth = max_len_in_range(Range("lstDepartamentos"))
      
    If Target.Address = "$E$2" Then
        ActiveWindow.Zoom = 120
        Target.ColumnWidth = iColWidth
    Else
        ActiveWindow.Zoom = 80
        Range("E2").ColumnWidth = 15
    End If

End Sub

Private Function max_len_in_range(rngList As Range)
    Dim iR As Long
    Dim tmpLen As Integer
  
    For iR = 2 To rngList.Count
        If Len(rngList(iR)) > Len(rngList(iR - 1)) Then
            tmpLen = Len(rngList(iR))
        End If
    Next iR
  
    max_len_in_range = tmpLen
  
End Function
Solución con ComboBox de la colección ActiveX

La ventaja de los controles ActiveX es que sus propiedades y sus eventos pueden ser programados. Esto nos permite “detectar” cuando el usuario activa el control y también cuando lo desactiva. Programamos un evento para cada una de las situaciones:

Private Sub ComboBox1_GotFocus()
    ActiveWindow.Zoom = 120
    Application.EnableEvents = False
    Me.Select
    Application.EnableEvents = True
End Sub

Private Sub ComboBox1_LostFocus()
ActiveWindow.Zoom = 80
End Sub

Los códigos hay que ponerlos en el módulo de la hoja correspondiente.

El archivo con los ejemplos se puede descargar aquí.

Ser experto en Excel

En un foro de Excel alguien empezó una discusión con el pomposo título:

¿Cuáles son los indicios de que alguien no es un experto en Excel?

¿Por qué comento esto? Una de las consultas que recibo con cierta frecuencia es cómo convertirse en un experto o en un usuario avanzado de Excel.

Por supuesto, la pregunta generó una larga cadena de respuestas, cada una más fuera de lugar que lo otra. Con excepción de una de ellas que transcribo a continuación.

La traducción no es literal, pero refleja cabalmente la intención del autor, y apoyo cada uno de los conceptos vertidos.

No creo que la pregunta pueda o deba ser contestada. En primer lugar, es relativa. Hay talentosos desarrolladores de Excel que han asumido el título de "experto", ya que pueden hacer algo que nadie más puede hacer dentro de un radio de 100 kilómetros- lo que significa que no hay nadie alrededor para criticar sus métodos. Por otro lado, hay personas en el sector financiero haciendo cosas con Excel que requieren conocimientos altamente especializados -, pero nunca pretenden ser un "experto ". ¿Quién es el verdadero experto? Depende del proyecto.
En segundo lugar, hay diferentes maneras de ser un "experto " en Excel. Hay gente que me sorprende con su capacidad de desarrollar hojas de cálculo basadas en fórmulas - pero nunca ha diseñado un módulo de clase que implemente varias interfaces o controladores de eventos para procesar los mensajes en tiempo real de middleware de terceros. ¿Quién es el verdadero experto? Depende del proyecto.
En realidad, me pregunto si es aconsejable utilizar semejante término en forma tan absoluta. Hay muy pocos criterios objetivos para juzgar esto, y aún menos los que son relevantes en el mundo real. Además, siempre habrá alguien por ahí que es mejor que nosotros – lo garantizo. Entonces, ¿cuál es el punto de pretender ser un "experto "? Uno puede ser realmente bueno, pero a menos que usted esté escribiendo libros técnicos sobre Excel / VBA - o trabajando directamente con los ingenieros de Microsoft para mejorar el software - probablemente no sea un experto. Lo único que importa es si usted puede encontrar la manera inteligente de utilizar Excel para resolver problemas y / o aumentar la eficiencia, y por lo tanto añadir valor a la organización que usted trabaja.

Yo no podría haberlo dicho mejor.

¿Qué opinan?