Totales en gráficos de columnas apiladas

Sin lugar a dudas no es comparable a la invención de la rueda o de la pólvora, pero aquí les traigo un tip sobre como agregar automáticamente totales en gráficos de columnas apiladas en Excel.

Supongamos esta tabla de datos que muestra las ventas del año de las tres sucursales de una empresa imaginaria

Con los datos creamos este gráfico que muestra las ventas por mes

Hemos eliminado el eje de los valores (Y) y puesto etiquetas con los valores (en miles) en las porciones de las columnas. Lo que falta es una etiqueta para cada mes que muestre el total de las ventas.

Podemos, por supuesto, poner cuadros de texto sobre cada columna e inclusive ligarlos a los datos en la tabla. Pero queremos algo más automático.

Empezamos por seleccionar el rango F3:F14 (el total de ventas), lo copiamos y agregamos la serie al gráfico con Pegado Especial

Al hacer esto, Excel agrega la serie a las columnas apiladas deformando nuestro gráfico

Para solucionar esto relacionamos la nueva serie al eje de valores secundario, cambiamos el tipo de gráfico a “línea” y agregamos etiquetas

La línea es innecesaria y la hacemos desaparecer poniendo la opción “color de línea” a “sin línea”

Para que las etiquetas queda inmediatamente por encima de las columnas, usamos la opción “encima” de alineación de las etiquetas

Para quitar “serie 4” de la leyenda, lo seleccionamos y lo borramos (un clic para seleccionar la leyenda y un clic más para seleccionar la etiqueta “serie 4”)

Hacemos desaparecer los ejes de valores poniendo las opciones de marcas de graduación y etiquetas del eje a “ninguno”

Con esto terminamos. Podemos agregar un borde y sombra a las etiquetas del total

Uso de comodines (wildcards) en funciones de Excel

Cuando filtramos tablas o hacemos búsquedas en Excel podemos usar comodines (*, ?). ¿Podemos usarlos en las funciones? Bien, sí y no. Es decir, hay funciones que aceptan comodines en los argumentos y otras que no.

Por ejemplo, queremos evaluar si una celda contiene cierto texto. En caso afirmativo el resultado será "A", en caso negativo "B".

Obviamente =SI(A1="*no*";"A";"B") no funciona.

Pero veamos esta alternativa

Como pueden ver, =SI(ESNUMERO(HALLAR("no";A1));"A";"B") no requiere comodines para nuestro ejemplo.

Podemos ver cómo funciona esta fórmula descomponiéndola en sus partes:

La función HALLAR da la primera posición en la cadena de texto del texto buscado

=HALLAR("no";A2)

Si el texto no aparece el resultado el #¡VALOR!

La función ESNUMERO evalúa si el resultado de HALLAR es numérico. Cuando lo es da VERDADERO, cuando no lo es, también si el error, da FALSO. Estos resultados son los argumentos que usamos en SI.

Veamos un ejemplo más elaborado donde buscamos valores que en la segunda posición contienen una "n" y en la cuarta posición una "o".

Como ven, en =SI(ESNUMERO(HALLAR("?n?o";A2));"A";"B") estamos usando el comodín "?" para señalar que la primera y la tercera posición puede ser ocupada por cualquier valor, pero la segunda y la cuarta deben ser "n" y "o" respectivamente.

La función COINCIDIR también acepta comodines.

También la función CONTAR.SI. Por ejemplo, en la lista anterior, si queremos contar todos los nombres que terminan con la letra "o" usamos

=CONTAR.SI($A$2:$A$7;"*o")

De la misma manera SUMAR.SI comodines pero SUMAPRODUCTO no.

Lista desplegable con actualización automática

Sigo saldando las deudas con mis lectores y en esta oportunidad cómo crear dinámicamente una lista desplegable con valores únicos.

La situación es la siguiente: creamos una tabla de facturas en una hoja de Excel con los campos “fecha”, “Cliente” y “Total”.

El campo “Cliente” contiene una lista desplegable creada con Validación de Datos. El objetivo es que el usuario introduzca el nombre del cliente eligiéndolo de la lista desplegable.

Para eso necesitamos crear una lista desplegable que se actualice automáticamente cada vez que agregamos un registro con un nuevo cliente en la base de datos.

Supongamos que la los registros únicos de la lista desplegable los ponemos en la columna H (en un modelo real el rango de la lista desplegable estaría en una hoja oculta). Nuestro objetivo es actualizar la lista cada vez que ingresemos un cliente nuevo.

Para actualizar la lista desplegable vamos a programar un evento. Como verán a continuación el código es muy sencillo ya que vamos a usar la herramienta Filtro Avanzado para crear una lista de valores únicos.
Empecemos por señalar que el rango de la base de datos lo definimos como Tabla (Lista en Excel 2003), de manera que la validación de datos del campo Cliente (la columna B) se agregue en forma automática cada vez que agregamos un registro en la base de datos.

Veamos como funciona el modelo:



1 - Creamos un nombre que se refiere a un rango dinámico con la fórmula

=DESREF('base de datos'!$H$1,1,,CONTARA('base de datos'!$H:$H))

Esta fórmula genera un rango dinámico donde el último valor del rango es una celda vacía. Esto es necesario para evitar que la validación de datos nos impida ingresar un cliente que aún no encuentra en la lista de clientes. Obviamente, esto cancela la validación de datos, ya que al haber una celda vacía todo valor que ingresemos será valido, pero nos permite crear el efecto de actualización automática.

2 - Programamos un evento de hoja Worksheet_SelectionChange con este código

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    If Union(Target, Range("C:C")).Address = Range("C:C").Address Then
        Range("H:H").ClearContents
        Range("B:B").AdvancedFilter Action:=xlFilterCopy, _
                            CopyToRange:=Range("H1"), Unique:=True
    End If

End Sub

En este código el evento se produce cuando seleccionamos una celda en la columna C, es decir, después de haber ingresado el cliente en la columna B.

Si queremos que la lista desplegable aparezca ordenada alfabéticamente podemos en el evento una llamada a este código (que va en un módulo común del Vbe)

Sub ordenar_clientes()

    Range("H1").CurrentRegion.Sort Key1:=Range("H2"), _
                Order1:=xlAscending, Header:=xlYes _
                , OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
                 DataOption1:=xlSortNormal

End Sub

El código del evento en el módulo de la hoja Base de datos quedaría

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    If Union(Target, Range("C:C")).Address = Range("C:C").Address Then
        Range("H:H").ClearContents
        Range("B:B").AdvancedFilter Action:=xlFilterCopy, _
                            CopyToRange:=Range("H1"), Unique:=True
    End If

    Call ordenar_clientes
  
End Sub

Todo sobre listas desplegables en Excel, técnicas avanzadas y descarga gratuita de ejemplos  en la Caja de Herramientas Excel - Listas Desplegables de JLD. Ver la nota o ir a la página de descarga de la guía.