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

Subtotales personalizados en tablas dinámicas

Todo usuario Excel que trabaje con tablas dinámicas conoce la posibilidad de establecer la función para totalizar los datos (Suma, Cuenta, Promedio, etc.). No todos conocen la posibilidad de crear subtotales personalizados

Esta opción está disponible cuando abrimos el menú de configuración de campo para algunos de los campos de filas o columnas. Si abrimos este menú para el campo de valores, la opción no aparece

Para mostrar el uso de esta funcionalidad construimos una tabla dinámica basada en la base de datos Northwind incluida en el paquete Office. En este caso mostramos los totales de ventas por ciudad de tres países elegidos al azar

Las cifras en el campo Total resumen todas las facturas de las ventas de cada ciudad en el año 1997. Si queremos mostrar el promedio para cada ciudad podemos cambiar la función de resumen de datos para el campo, como haríamos habitualmente. En lugar de esto, vamos a usar la opción de personalizar los subtotales para el campo ShipCountry

Nótese que hemos marcado las funciones SUMA y PROMEDIO. El resultado es


Este método nos permite poner mucha más información usando menos espacio. Por ejemplo, podemos mostrar la suma de los datos y agregar el promedio y la cuenta, dando de esta manera una visión completa de todas las dimensiones de nuestros datos

subtpt05