Ajustar texto de fechas con formato personalizado

Después de otra semana intensa de actividad, veo que llevo un buen atraso en responder a muchas de las consultas que me han llegado últimamente. Así que voy a aprovechar esta nota para disculparme y para volver a explicar que no siempre puedo (y a veces, no quiero) responder a las consultas.

Como prueba de buena voluntad voy a mostrar un pequeño truco con formato personalizado de números. En uno de los muchos foros de Excel apareció esta pregunta:

Hay alguna manera de ajustar el texto de una fecha con formato “dd/mm/aaaa hh:mm” de manera que la fecha aparezca en la primera línea de la celda y la hora en la segunda”

Una forma de hacerlo es editando la celda, poniendo el cursor delante de la hora y apretando Alt+Enter.

Pero la idea es definir un formato personalizado, de manera que no tengamos que editar cada una de las celdas.

El truco es el siguiente:


Seleccionamos el rango de las celdas y definimos el formato personalizado

En la ventanilla “Tipo” ponemos el cursor del mouse en el lugar del formato donde queremos dividir en dos líneas y pulsamos simultáneamente las teclas Ctrl+J y apretamos “Aceptar”

Con el rango seleccionado definimos “Ajustar texto”

o con el menú Formato de celdas

Finalmente ajustamos el alto de la fila

También podemos ir más adelante y dividir la celda en tres líneas, usando dos veces Ctrl+J

Para lograr este formato en la ventanilla “Tipo” del formulario de formato personalizado de números ponemos

“día:” dd (nótese las comilla) y un espacio

Apretamos Ctrl+J

“mes:” mmmm y un espacio

Apretamos Ctrl+J

“año:” aaaa

Apretamos “Aceptar” y definimos “Ajustar texto”. Si vemos esta situación

formFecha08

tenemos primero que cambiar el ancho de las columnas y luego el alto de la fila.

El truco fue mencionado por Rick Rothstein (si, el mismo de la prueba del nueve).

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í.