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

lunes, mayo 09, 2011

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


1 comments:

Juan José 10 mayo, 2011 14:58  

Interesante.
Gracias !!!

Publicar un comentario

Google+ Followers

Seguidores

Google+ Badge

Términos Legales

  © Blogger template On The Road by Ourblogtemplates.com 2009

Back to TOP