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

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?

Encontrar el último número positivo o negativo en un rango

En uno de los proyectos que estoy desarrollando me enfrenté con la necesidad de encontrar el último número negativo de una serie. Específicamente se trataba de calcular el período de recuperación de una inversión (Payback), pero este problema puede presentarse en varias situaciones.

En el pasado he mostrado cómo encontrar el último elemento en un rango usando la función BUSCAR (LOOKUP). Pero en este caso se trata de encontrar el último elemento bajo la condición que sea negativo.

Supongamos esta serie de números en el rango B1:B9

Nuestro objetivo es crear una fórmula que de cómo resultado el último número negativo de la serie, en nuestro caso -30

La fórmula que usamos es la siguiente:

=BUSCAR(2;1/(B1:B9<0);B1:B9) 

¿Cómo funciona esta fórmula?
 
La función BUSCAR tiene dos configuraciones: la vectorial y la matricial. Más sobre el tema puede leerse en la ayuda en línea de Excel. En nuestro caso usamos la forma vectorial que tiene esta sintaxis:

BUSCAR(valor_buscado, vector_de_comparación, [vector_resultado])

En nuestro ejemplo, el vector de comparación es creado por la expresión 1/(B1:B9<0) Esta resulta en una serie de unos y valores de error. 

Cuando el valor de la celda de la columna B es negativo el resultado es 1 (1/1); cuando es positivo el resultado es #DIV0! (1/0).
A pesar de lo que dice la ayuda en línea de Excel (Cuando BUSCAR no puede encontrar el valor buscado, la función muestra el valor más grande en vector_de_comparación que es menor o igual al valor_buscado), BUSCAR da el último valor que es menor o igual al buscado. Por este motivo usamos “2” como valor buscado. De la misma manera podríamos usar 3 o cualquier otro número mayor que 1. 


Para calcular el último número positivo en la serie modificamos levemente nuestra fórmula 

=BUSCAR(2;1/(B1:B9>=0);B1:B9)

Autofiltro de fechas con macros

Una inesperada lluvia acaba de arruinarme el “bautismo de fuego” de mi nueva bicicleta XC, una Scott Spark 60

que estaba programado para este sábado. Así que dedicaremos esta mañana a un problema que se nos puede presentar a los usuarios de Excel en castellano (o cualquier otra configuración regional fuera del inglés americano).

Supongamos una tabla con fechas y ventas. Podemos mostrar el detalle de ventas de un período con facilidad usando Autofiltro. Esto no es ninguna novedad para ninguno de mis lectores (ni para el 99% de los usuarios de Excel).

Si queremos analizar los datos de la tabla con frecuencia podemos crear una macro para ahorrarnos algunos de los pasos del uso de Autofiltro. La idea es que en una celda pongamos la fecha de inicio del período a analizar, en otra la fecha de cierre del período y apretando hagamos correr una macro que haga la tarea.
Suponiendo que no somos expertos en Vba, empezamos por grabar las acciones que hacemos para aplicar el autofiltro

Al grabar una macro es recomendable darle un nombre significativo, agregar un método abreviado de teclado (preferentemente combinando la tecla Mayúsculas) y poner una descripción de la tarea que cumple la macro.
Supongamos que queremos filtrar la lista para que muestre las ventas del segundo trimestre del 2007 (nótese que ya hemos agregado las celdas para introducir las fechas en la parte superior de la hoja)

EL código resultante es el siguiente

Sub Filtrar_periodo()
'
' Filtrar_periodo Macro
' Filtrar la lista entre dos fechas
'
' Acceso directo: Ctrl+May?s+F
'
    Selection.AutoFilter
    ActiveSheet.Range("$A$6:$F$2088").AutoFilter Field:=1, Criteria1:= _
        ">=01/04/2007", Operator:=xlAnd, Criteria2:="<=30/06/2007"
End Sub


El código que resulta de grabar las acciones presenta dos problemas:

• El rango de la tabla es fijo, Range("$A$1:$F$2083") 
• Los criterios de filtrado también están preestablecidos en el código

Para que nuestra macro sea útil tenemos que modificar el código de manera que podamos cambiar las fechas según nuestras necesidades y que el rango de la tabla se adapte cuando esta cambia.

Empecemos por crear dos nombres que se refieran a las celdas B2 (celFechaDe) y B3 (celFechaHasta). La forma má práctica de hacerlo es introduciendo los nombres en el cuadro de Nombres

Ahora editamos nuestro código donde definimos dos variables tipo String (texto) para los criterio de filtrado. 


El código es el siguiente

Sub filtrar_periodo_mejorado()
    Dim strCriterio1 As String
    Dim strCriterio2 As String

    strCriterio1 = ">=" & Range("celFechaDe")
    strCriterio2 = "<=" & Range("celFechaHasta")

    With ActiveSheet
        If .AutoFilterMode = True Then .AutoFilterMode = False
    End With

    Range("A6").AutoFilter field:=1, Criteria1:=strCriterio1, _
                            Operator:=xlAnd, Criteria2:=strCriterio2
    
End Sub

Al activar la macro obtenemos el siguiente resultado

Excel nos informa graciosamente que no se han encontrado filas que cumplan con los criterios! 


El problema consiste en que en Vba Excel usa la notación americana de fechas (mes/día/año) de manera que la fecha 01/04/2007 ha sido transformada en 04/01/2007

La segunda fecha no ha sufrido transformación dado que no existe el mes número 30.

La forma de solucionar este problema es pasar las fechas a una variable de tipo Long y con esta variable armar la cadena de texto del criterio.

El código es:

Sub filtrar_periodo_mejorado()
    Dim lFecha1 As Long, lFecha2 As Long


    lFecha1 = Range("celFechaDe")
    lFecha2 = Range("celFechaHasta")

    With ActiveSheet
        If .AutoFilterMode = True Then .AutoFilterMode = False
    End With

    Range("A6").AutoFilter field:=1, Criteria1:=">=" & lFecha1, _
                            Operator:=xlAnd, Criteria2:="<=" & lFecha2
    
End Sub
Como puede verse solo definimos dos variables de tipo Long par alas fechas y la cadena de texto de los criterios la armamos con el operador “ &”

Ahora nuestro modelo funciona sin inconvenientes, sin importar cuáles sean las definiciones regionales del sistema


El archivo con ejemplo y el código puede descargarse aquí .

Separar valores según la alineación de la celda

Una de las peores prácticas en Excel es usar fondos de color para clasificar datos (a pesar de que a partir de Excel 2007 podemos hacer ciertas operaciones basándonos en colores). A partir de la consulta de un lector he descubierto otra mala práctica que nunca hubiera podido imaginar: el uso de la alineación de la celda para clasificar datos.

El caso es el siguiente: mi lector utiliza un programa de contabilidad que, como la mayoría de los sistemas, tiene un mecanismo incorporado para exportar reportes a Excel. Uno de ellos es el libro de asientos (en nuestro ejemplo usaremos un modelo arbitrariamente sencillo: fecha, asiento, concepto, importe).
Obviamente, el importe debe ser clasificado como "debe" o "haber", pero el reporte no tiene un campo para indicar el tipo de movimiento. Los movimientos aparecen en la misma columna y la forma de saber si se trata de "debe" o "haber" en la alineación del importe en la celda

Como puede apreciarse en la imagen, todos los importes están en la columna E. La única forma de saber si un importe es un débito o un crédito es fijarse en la alineación de la celda.

Obviamente tenemos que separar entre débitos y créditos si queremos hacer cualquier operación con nuestros datos. El problema es cómo.

Excel no tiene una función que nos indique la alineación de la celda cuando ésta contiene números (la función CELDA funciona con el argumentos “prefijo” sólo para celdas que contienen texto).

Como ya habrán intuido solucionaremos el problema con Vba. Con Vba podemos extraer la orientación de la celda usando la propiedad HorizontalAligment

Lo valores posibles de la propiedad HorizontalAligmente son: xlLeft (izquierda), xlRight (derecha) y xlCenter (centrado).

En nuestro caso tenemos dos posibilidades:

# - importes alineados a la izquierda: “DEBE”
# - importes alineados a la derecha: “HABER”

En esta nota mostraremos dos soluciones posibles: una UDF (función definida por el usuario) y una macro.

Nuestra UDF evalúa la celda que contiene el importe y da como resultado “DEBE” si la alineación es a la izquierda, “HABER” si la alineación es a la derecha y “#N/A!” en caso de centro.

Function separar_debe_haber(rngCell As Range)

    Select Case rngCell.HorizontalAlignment
        Case Is = xlLeft: separar_debe_haber = "DEBE"
        Case Is = xlRight: separar_debe_haber = "HABER"
        Case Else: separar_debe_haber = CVErr(xlErrNA)
    End Select

End Function
Sub horiz_align()

Basándonos en la columna auxiliar podemos totalizar los créditos y débitos con funciones o con tablas dinámicas

Otro enfoque es utilizar código Vba. Con rutinas de Vba podemos hacer cosas que no se pueden con funciones UDF. En nuestro caso, separar los importes en dos columnas, una para el “debe” y otra para el “haber”.

La macro evalúa el rango de los importes, previamente seleccionado por el usuario, y de acuerdo a la orientación los separa en dos columnas según el caso. Los débitos son copiados a la primer columna a la derecha del rango de los importes; los créditos a la segunda columna a la derecha.

Sub separa_debe_haber()
    Dim rngCell As Range
        
        For Each rngCell In Selection
            Select Case rngCell.HorizontalAlignment
                Case Is = xlRight: rngCell.Offset(0, 2) = rngCell.Value
                Case Is = xlLeft: rngCell.Offset(0, 1) = rngCell.Value
            End Select
        Next rngCell
End Sub

Este video muestra el funcionamiento de la macro