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

Calcular el mínimo con criterios excluyendo ceros

Un lector me consultaba cómo calcular el precio mínimo de los tornillos en esta lista

Para hacerlo tenemos que crear una matriz (array) de valores de la lista que correspondan a la categoría tornillos. Esta lista la usamos como argumento en la función MIN para obtener el valor buscado. La fórmula matricial sería

=MIN((A2:A13="Tornillos")*B2:B13)

Esta es una fórmula matricial que introducimos apretando simultáneamente Ctrl+Mayúscula+Enter.

El problema con esta fórmula es que cuando un valor en la lista no corresponde a la categoría buscada ("tornillos" en nuestro caso), el valor en la matriz es 0 (cero) y éste pasa a ser el valor mínimo (a excepción de que la lista contenga valores negativos).

La solución es usar la función SI para crear una condición que excluya los ceros. La fórmula matricial que excluye los ceros del cálculo del mínimo es

=MIN(SI((A2:A13="Tornillos"),B2:B13))

Nótese que excluimos el tercer argumento de la función SI, es decir, ponemos solamente el resultado a calcular cuando la condición se cumple.

Los precios máximos se calculan sin mucho trámite con esta función matricial

=MAX((A2:A13="Tornillos")*(B2:B13))

Ahora vamos a darle un toque un poco más profesional a la solución agregándole la posibilidad de elegir el tipo de artículo en forma dinámica. Para esto creamos una lista desplegable con validación de datos en la celda E3

Nuestro modelo se ve ahora así

donde hemos modificado las fórmulas de esta manera

=MAX((A2:A13=E3)*(B2:B13))

=MIN(SI((A2:A13=E3),B2:B13))

Ahora, si queremos realmente impresionar al jefe (o mejor aún, a la secretaria) hacemos que también el tipo de valor buscado se establezca dinámicamente.

Agregamos dos columnas a la izquierda de la columna A, donde haremos cálculos auxiliares

En la celda A7 ponemos esta fórmula

=COINCIDIR(G2,A3:A4,0)

En las celdas A9 y A10 ponemos las fórmulas de máximo y mínimo respectivamente.

En G2 ponemos una lista desplegable con validación de datos con dos posibilidades: Máximo y Mínimo.

Finalmente, en la celda G4 ponemos la fórmula

=INDICE(A9:A10,A7)

Ahora, cuando el usuario elige el máximo, la celda A7 recibe el valor 1; si elige el mínimo la celda muestra el 2. Este valor es usado como argumento en la función INDICE de la celda G4, que extrae el valor de la celda A9 o A10, según el caso.

El último toque es ocultar la columna A.

El archivo con el ejemplo se puede descargar aquí.