Gráficos Excel - hacer que el eje horizontal cruce por el mínimo del vertical

Después de casi  un mes de descanso (vacaciones, fiestas, un poco más de vacaciones), vuelvo a la carga con una nota sencilla pero que espero resulte útil.

Supongamos este gráfico

Por definición Excel ubica el eje de las catergorías (el eje horizontal, X) de manera que cruza el eje de los valores (el vertical, Y) en el cero.
Como tenemos valores negativos, éstos aparecen por debajo dell eje de las categorías. En ciertos casos, en particular si tenemos un gráfico con muchos datos, podemos querer que el eje de las X cruce el eje de las Y por el valor mínimo de éste último, es decir, ubicarlo en la base del gráfico.

Para definir la ubicación del eje de las X tenemos que abrir el menú de formato del eje de las Y

pero vemos que si bien podemos definir que el eje horizontal cruce por el valor máximo del eje de las Y, la posibilidad opuesta, que cruce por el mínimo, no existe.

La solución es sencilla: en la ventanilla "Valor del Eje" ponemos un valor mucho más pequeño que el menor de los valores de la serie

Si queremos visualizar la línea del cero, para resaltar los valores que caen por debajo, podemos agregar una serie auxiliar a nuestro gráfico

También podemos lograr este efecto

Seleccionando la serie auxiliar y cambiando el tipo de gráfico a Área y cambiando la definición de la posición del eje de las X a "En marcas de graduación"

Como crear una presentación Power Point a partir de un cuaderno Excel

Empiezo este post con una aclaración: la autoría del concepto de esta nota y del código son de Mike Alexander y fue publicado en su blog hace unos años atrás.

Como muchos de los posts de este blog, la idea surgió a partir de la inquietud de uno de mis colegas de trabajo. Como muchos de los lectores de este blog, mi colega recoge, transforma, analiza datos y finalmente crea reportes. Estos reportes incluyen, además de tablas, gráficos. El producto final suele ser una presentación PowerPoint, donde pega los gráficos que quiere presentar.
La tarea de pasar los gráficos de Excel a la presentación PowerPoint puede ser engorrosa y tiene sus bemoles, ya sea que los peguemos como imágenes o los liguemos al cuaderno Excel.
Con un poco de código Vba podemos automatizar el proceso de crear una presentación PowerPoint a partir de un cuaderno Excel.

La idea central es crear un cuaderno donde manejamos los datos de los gráficos. Luego creamos hojas, una para diapositiva de la presentación, definiendo el rango de celdas que contiene el gráfico y los elementos que queremos que aparezcan en la diapositiva. El rango debe ser el mismo para todas las hojas. También podemos crear una hoja para el título (la primer diapositiva).

En este ejemplo he creado un cuaderno con cuatro hojas, una para el título de la presentación y las tres restantes para mostrar las ventas por mes de las sucursales

El rango D1:O24 contiene lo que queremos que aparezca en cada una de las diapositivas de la presentación.

Finalmente activamos el código siguiente que creará una presentación de PowerPoint, con una diapositiva por cada hoja del cuaderno:


Sub WorkbooktoPowerPoint()

'Paso 1:  Declarar variables
    Dim pp As Object
    Dim PPPres As Object
    Dim PPSlide As Object
    Dim xlwksht As Worksheet
    Dim MyRange As String
    Dim MyTitle As String
    Dim SlideCount As Integer

'Paso 2:  Abrir PowerPoint, agregar una nueva presentacion
         'y volverla visible visible
    Set pp = CreateObject("PowerPoint.Application")
    Set PPPres = pp.Presentations.Add
    pp.Visible = True
 

'Paso 3:  Definir el rango de las hojas que contiene la diapositiva
    MyRange = "D1:O24"

'Paso 4:  Empezar el loop por las hojas del cuaderno
    For Each xlwksht In ActiveWorkbook.Worksheets
    xlwksht.Select
    Application.Wait (Now + TimeValue("0:00:1"))

'Paso 5:  Copiar el rango como imagen
    xlwksht.Range(MyRange).CopyPicture _
    Appearance:=xlScreen, Format:=xlPicture

'Paso 6:  Contar las diapositivas y agregar una nueva
        '(el numero 12 representa la enumeracion para una
        'diapositiva en blanco)
    SlideCount = PPPres.Slides.Count
    Set PPSlide = PPPres.Slides.Add(SlideCount + 1, 12)
    PPSlide.Select
        
'Paso 7:  Pegar la imagen y ajustar la posicion
    PPSlide.Shapes.Paste.Select
    pp.ActiveWindow.Selection.ShapeRange.Align msoAlignCenters, True
    pp.ActiveWindow.Selection.ShapeRange.Top = 50
    pp.ActiveWindow.Selection.ShapeRange.Left = 1
    pp.ActiveWindow.Selection.ShapeRange.Width = 700

 
    Next xlwksht
     
'Paso 8:  Limpiar la memoria
    pp.Activate
    Set PPSlide = Nothing
    Set PPPres = Nothing
    Set pp = Nothing
              
End Sub

Este video muestra el proceso de creación de la presentación

El gráfico (de barras) de Wendy

Hace unos días me preguntaban si se puede construir este gráfico con Excel

La respuesta es si, pero haciendo algunas manipulaciones. Podemos crearlo usando un gráfico de barras apiladas (el proceso puede verse en este video, que también aparece al final de la nota). Los datos originales consisten de una única serie de valores (de color azul en la imagen: 9, 1, 0, 0,0) pero para nuestro gráfico deberemos crear una segunda serie de valores coompletarios (de color gris en la imagen)

Los datos originales están en el rango C3:D7. En la columna E (Auxiliar 1) calculamos los valores de la segunda serie, la complementaria, con la fórmula:

=(MAX($D$3:$D$7)+1)-D3

Empezamos por seleccionar el rango C2:D7 y creamos un gráfico de barras apiladas

resultando el siguiente gráfico

Hacemos los ajustes necesarios:

Abrimos el menú de formato del eje vertical y marcamos "Categorías en orden inverso" y "Eje horizontal cruza: en la categoría máxima".

Seleccionamos la serie Auxiliar 1 y cambiamos el color del relleno

Ahora quitamos las líneas y el relleno del área de trazado y del área del gráfico, la leyenda y las líneas de cuadrículas.
Ocultamos el eje horizontal con el menú Presentación-Ejes

y la línea del eje vertical

El último paso es agregar las etiquetas de valores. Podríamos hacerlo agregándolas y luego arrastrándolas manualmente al extremo derecho del gráfico. Pero, por supuesto, queremos ahorrarnos todo este trabajo manual, en particular si nuestro gráfico tiene una gran cantidad de series.
El truco consiste en agregar otra serie auxiliar, definirla en el eje secundario usando como eje de las categorías los valores de la serie de datos. Luego quitamos el relleno y las líneas de esta serie para volverla invisible y agregamos las etiquetas que muestren los valores de la categoría.

Los  valores de la segunda serie auxiliar los definimos sumando los valores de la serie a los de la serie Auxiliar 1 y agregándole 1

Agregamos la serie al gráfico pero al definir los rótulos del eje seleccionamos los valores de la serie de los datos (el rango D3:D7 en nuestro ejemplo)

Veremos que los valores del eje vertical cambian. Esto será corregido en el próximo paso que es definir la nueva serie auxiliar en el eje secundario.

Como podemos apreciar debemos hacer varias transformaciones. Nuestro gráfico tiene ahora dos ejes horizontales y dos verticales (si alguno de los ejes no aparece, debemos volverlo visible usando el menú Presentación-Ejes). Las transformaciones a realizar son:

• invertir el eje vertical secundario;
• cambiar la referencia a las etiquetas del eje vertical primario;
• cambiar el relleno y la línea de la serie Auxiliar 2 para volverla invisible (sin relleno, sin línea);
• volver invisible los ejes horizontales;
• volver invisible el eje vertical secundario;
• quitar la línea y las marcas del eje vertical primario.

Confío en que el lector ya sabe realizar estas tareas (todo el proceso puede verse en el video abajo). Una vez realizadas estas transformaciones nuestro gráfico se ve así:

Solo nos queda agregar las etiquetas a la serie invisible Auxiliar 2. Empezamos por seleccionar la serie Auxiliar 2 y agregamos las etiquetas. Por defecto Excel introduce los valores de la serie en el centro de la barra. Nosotros queremos mostrar los nombres de la categoría (el eje vertical secundario) en el extremo de la barra. Una vez introducidas las etiquetas modificamos las definiciones con el menú de Formato de Etiquetas de Datos

Finalmente nuestro gráfico se ve así

Todo el proceso de creación puede verse en este video

Señalar series por color en gráficos de Excel

Supongamos esta situación:

Hemos representado en el gráfico una serie de valores que pertenecen a tres grupos (en la "vida real" podría tratarse de ventas de grupos de productos o de salarios de grupos de trabajadores, etc.).
Lo que queremos es representar cada grupo con un color distinto. Por defecto, Excel da a todos los puntos de una serie el mismo color. Podríamos cambiar el color de la columna de cada punto manualmente, pero podemos con hacerlo fácilmente usando la técnica que mostraré a continuación (y que ya he mostrado en alguna nota en el pasado).

El truco consiste en descomponer la única serie en tres series, una para cada grupo. Lo hacemos creando tres columnas auxiliares

En este ejemplo me he limitado a copiar los valores del grupo 1 a la columna D, los del grupo 2 a la columna E y los del grupo 3 a la columna F.
Para crear el nuevo gráfico seleccionamos el rango B2:B11 e inmediatamente, apretando y manteniendo apretada la tecla Ctrl. el rango D2:F11; seleccionamos Insertar-Gráfico-Columna, y obtenemos

Excel otorga automáticamente un color distinto a cada serie. Por supuesto podemos ocultar las columnas auxiliares o ponerlas en un rango oculto.

Mis perspicaces lectores habrán notado que existe un problema con las etiquetas del eje de las categorías (el eje de las X). Estas están alineadas a la izquierda, coincidiendo con el primer valor de cada grupo, cuando lo que quisiéramos es que estuvieran en el centro del grupo.

Para obligar a Excel a centrar los rótulos del eje agregamos una columna a nuestra tabla, entre las columna B y C; esta columna contiene un espacio (no está vacía, sencillamente seleccionamos la celda, apretamos la tecla de espacios e inmediatamente Enter)

La función INDICE con áreas - segundo episodio

Unos días después de haber publicado el post sobre la función INDICE con areas, entra en mi oficina el mencionado compañero de trabajo:

Compañero - Hola Jorge. Leí el post. Muy bueno...

JLD - Hola. Muy bueno...pero?

Compañero - Mirá, no si será abusar pero quisiera que en el gráfico la columna del mes analizado aparezca con otro color y también sería bueno si hubiera una línea horizontal que muestre el promedio.

JLD - ¡Ah! Vos querés ésto:

Compañero - ¡Si! ¿Cómo se hace?

JLD - Sentate que te explico

El gráfico de la nota anterior contenía una única serie; éste contiene tres:

1. la serie de las ventas sin el mes elegido (las columnas que aparecen en celeste)
2. la serie que contiene el mes elegido (aparece en  verde)
3. la serie que representa el promedio.

Como mostramos en la nota anterior, la serie de las ventas la generamos dinámicamente con un nombre definido. Ahora tenemos que crear columnas auxiliares para definir las tres series requeridas. Esto lo haccemos agregando columnas a las izquierda de la tabla de datos de manera que luego podamos ocultarlas.
Estas son las columnas:

La celda B5 contiene la fórmula =COINCIDIR(F19,E4:E15,0). El resultado, el número de orden  del mes elegido, lo usamos como parámetro en las fórmulas de las columnas A y B.

El rango A4:A15 contiene la fórmula

=SI(COINCIDIR(E4,$E$4:$E$15,0)<>$B$2,INDICE(F4:H4,,$F$18),ND())

El objetivo de esta fórmula es dar las ventas del mes de la tabla de ventas a condición que no sea el mes elegido (mayo, 5, en nuestro ejemplo). Em caso de tratarse del mes elegido el resultado es #N/A, lo que logramos con la función ND().

En modo similar ponemos en el rango B4:B15 la fórmula

=SI(COINCIDIR(E4,$E$4:$E$15,0)=$B$2,INDICE(F4:H4,,$F$18),ND())

Esta complementa la anterior: si no es el mes elegido el resultado es #N/A.

Los valores #N/A son ignorados en los gráficos de Excel (no aparecen).

El rango C4:C15 contiene sencillamente una referencia a la celda F22 donde calculamos el promedio.

La celda B17 contiene la fórmula matricial (se ingresa apretando Ctrl-Mayúsculas-Enter):

=MAX(SI(ESERROR(A4:A15),"",A4:A15))

El objetivo de esta fórmula es garantizar que ambos ejes Y en  el gráfico (el principal y el secundario) tengan la misma escala (el mismo valor máximo) para evitar distorsiones en la presentación de los valores. La técnica está explicada en esta nota.

Una vez que hemos definido las series creamos el gráfico asegurándonos de usar el eje Y principal para las ventas y el eje Y secundario para el mes elegido. y para el valor máximo.

Las series de ventas son gráficos de columnas; el promedio es un gráfico de línea y el valor máximo, para volverlo invisible, es un grafico de línea sin marcadores y sin línea

El último detalle a tener en cuenta es, por defecto, Excel no muestra los datos de series que se encuentras en filas o columnas ocultas. Para volverlos visibles tenemos que assgurarnos de cambiar la definición en "Seleccionar Datos-Celdas Ocultas y Vacías"

El archivo con el ejemplo se puede descargar aquí.

La función INDICE con áreas

Transcripción más o menos real de un diálogo con un compañero de trabajo:

Compañero: Hace unos días atrás leí tu post sobre la función INDICE. Desde entonces la uso todo el tiempo; no me había dado cuenta de las posibilidades que tiene.

JLD: me alegro!

Compañero: pero, ¿qué es esa segunda sintaxis con las áreas, para qué sirve?

JLD: ah!, ¿te referís a esto?:

Aquí va la explicación.

Supongamos este cuadro de ventas por mes y sucursal

Habiendo definido nombres que se refieren a los rangos de ventas de las sucursales (Sucursal_1 se refiere al rango C3:C14, por ejemplo), podemos obtener las ventas de mes de abril de las sucursal 1 con esta fórmula

=INDICE(Sucursal_1,COINCIDIR("abril",B3:B14,0))

Si queremos obtener las ventas de otra sucursal tendríamos que cambiar el rango de referencia en la fórmula.

Si usamos la segunda sintaxis de la función INDICE, podemos crear una fórmula que se refiera a las distintas sucursales dinámicamente. Consideremos este ejemplo

La fórmulas en la celda C20 (Ventas del mes) es la siguiente:

=INDICE((Sucursal_1,Sucursal_2,Sucursal_3),COINCIDIR(C18,$B$3:$B$14,0),,C17)

donde (Sucursal_1,Sucursal_2,Sucursal_3) es la referencia a las distintas áreas, en nuestro caso el rango de las ventas de cada sucursal;

COINCIDIR(C18,$B$3:$B$14,0) calcula el número de fila en el rango correspondiente al mes buscado

y C17 se refiere a la celda que contiene el número de área de donde queremos extraer el dato..

Las áreas no deben tener necesariamente el mismo tamaño o ser adyacentes.

La celda C21 contiene la fórmula

=PROMEDIO(INDICE((Sucursal_1,Sucursal_2,Sucursal_3),,,C17))

La particularidad aquí es que INDICE usa sólo los argumentos "ref" y "num_area". Como hemos explicado, ésto hace que INDICE considere todo el rango de la referencia; así, al combinarla con la función PROMEDIO obtenemos el promedio de las ventas de la sucursal. De esta manera podemos comparar las ventas del mes con el promedio.

Como puede apreciarse, esta técnica es muy útil cuando creamos dashboards y reportes dinámicos.

Como bono adicional, podemos usar INDICE con áreas para crear gráficos dinámicos. Siguiendo con nuestro ejemplo, podemos crear un nombre definido "grfVentas" que se refiera a la fórmula

INDICE((Sucursal_1,Sucursal_2,Sucursal_3),,,C17)

Después de crear un gráfico con una de las sucursales, reemplazamos la referencia en "Modificar serie-valores  de la serie"

por el nombre definido "grfVentas"

A partir de este momento, cada vez que cambiemos la referencia a la sucursal en la celda C17, el gráfico mostrará los datos de la sucursal elegida



El cuaderno con el ejemplo puede descargarse aquí.

Gráficos Excel - Fijar máximos del eje vertical con macro

En la nota anterior sobre el tema mencionamos que la solución tradicional para coordinar el máximo del eje de las Y (eje de los valores en términos de Excel) es usar una macro.
El método manual, con fórmulas y series auxiliares, tiene la ventaja que puede ser usado también en entornos donde las macros están bloqueadas, por ejemplo,  por el administrador de la red. Las deventajas son menores pero existen:

• el valor máximo es determinado por el algoritmo de Excel, por lo que siempre será cercano al valor deseado pero no siempre exactamente igual;
• hay que modificar cada uno de las gráficos que queremos coordinar.

Las macros son más flexibles y eficientes, ya que podemos establecer exactamente el valor que queremos que aparezca como máximo y podemos aplicarlas a todos los gráficos de la hoja.

El código es muy sencillo y podemos usarlo de dos maneras distintas: como sub (macro común) o como evento. Al usarlo como sub, tenemos que crear también la forma de disparar el código, por ejemplo, ligando la macro a un botón; al usar eventos podemos hacer que la macro corra cuando, por ejemplo, el usuario cambia algún dato en la tabla de los datos.

Como en el ejemplo de la nota anterior, tenemos dos gráficos basados en dos tablas de datos y en la celda A3 calculamos el valor máximo de ambas tablas (hemos creado el nombre "cellValMax" que se refiere a la celda A3)

Como podemos ver en la imagen, la escala del eje de las Y es diferente en cada gráfico y por lo tanto la comparación visual es engañosa.
Como mencionamos más arriba podemos usar el código como sub (macro) o como evento; el código es el mismo pero la forma de hacerlo correr es distinta.

Uso del código como sub (macro)

En un módulo compun del editor de Vb, ponemos este código

Sub coordinar_max_graficos()
   Dim dbMax As Double
   Dim objCht As ChartObject

   dbMax = Range("cellValMax").Value
  
   For Each objCht In ActiveSheet.ChartObjects
      With objCht.Chart.Axes(xlValue)
        .MaximumScale = dbMax
        .MinimumScale = 0
      End With
   Next objCht
End Sub

En la hoja que contiene los gráficos agregamos un botón ligado a la macro




Uso del código como evento

Para usarlo como evento el código debe ir en un módulo de la hoja que contiene los gráficos. Una de las formas de acceder a este módulo es apunta con el mouse a la pestaña con el nombre de la hoja y activar la opción Ver código

En nuestro caso vamos a crear un evento de manera que cuando el usuario haga algún cambio en la hoja, se dispare el código. Para esto usamos un evento del tipo Change

Como puede apreciarse, el código es el mismo que en la rutina sub.

Coordinar el valor máximo del eje vertical en varios gráficos de Excel

Una participante en el foro Expertos en Excel en Español consultaba si se puede condicionar el valor máximo de un grupo de gráficos de manera que viniese determinado por el gráfico que tenga la cifra mayor.

Para ejemplificar, digamos que tenemos dos gráficos que muestran dos series de valores (val1 y val2 en la imagen)

Sin entrar en la la cuestión si poner un gráfico al lado del otro o crear un único gráfico con dos series de datos, podemos ver que en nuestro ejemplo los máximos del eje de los valores (el eje Y) son distintos en cada gráfico. Esto hace que la comparación visual sea engañosa; lo que buscamos es que en los dos gráficos el eje de los Y tenga el mismo valor máximo.

Como los valores van cambiando no podemos fijar la definición del máximo del eje. La solución tradicional es programar un evento (Vba), de manera que cuando algún valor cambia en alguna de las tablas el evento cambia el valor del máximo en ambos gráficos.

Sin embargo, existe la posibilidad de lograr el mismo efecto sin macros. El truco consiste en incluir en ambos gráficos una serie auxiliar con un sólo punto cuyo valor sea el máximo de los los valores de ambas tablas.
Además de tener el valor máximo de todos los valores de las tablas esta serie debe cumplir con otros dos requisitos:

1. ser "invisible";
2. ser "inmaterial", con lo que quiero decir que no influye en como el resto de los puntos de las series se muestran en los gráficos (este concepto quedará más claro en las subsiguientes explicaciones).

En nuestro ejemplo empezamos por crear la serie auxiliar poniendo esta fórmula en una celda: =MAX(D3:E7,L3:M7)

Como puede verse, ponemos el fórmula en la columna A, dejando la columna B libre. El motivo es poder luego ocultar la columna A para no recargar nuestro modelo mostrando datos auxiliares.

El próximo paso es agregar la serie auxiliar en los gráficos. Seleccionamos el primer gráfico y en el menú Diseño de la opción Herramienta de gráficos activamos "Seleccionar Datos-Agregar". y en el formulario que se abre ponemos estas definiciones

Apretamos "Aceptar" y en formulario que se abre apretamos el botón "Celdas ocultas y vacías"

En el formulario de configuración de celdas ocultas y vacías marcamos la opción "Mostrar datos..."

Ahora tenemos que ocuparnos de los requisitos que debe cumplir la serie auxiliar. Cambiamos el tipo de serie a "Línea" sin marcadores

Esto hace que la la serie "desaparezca" (al tener un solo punto no aparece la línea ni el marcador; tampoco "comparte" lugar en el eje de las X con los otros puntos de la serie).

Repetimos el proceso para el segundo gráfico. Ahora podemos ver que en ambos gráficos el eje de las Y tiene el mismo valor máximo

Cada vez que cambiemos algún valor en alguna de las tablas, Excel calculará el mismo máximo para el eje de las Y en ambos gráficos.

En esta nota pueden ver la solución usando macros.

Gráficos cascada (Waterfall) con valores negativos

Hace poco más de cuatro años atrás publiqué este post sobre cómo crear gráficos de tipo "Waterfall" ("Cascada" o "Flying Bricks") con Excel.
Este tipo de gráficos permite ver el efecto acumulado de valores positivos y negativos en una secuencia. En el post mostramos este ejemplo, partiendo de los resultados anuales de una cadena de tiendas

mostramos este gráfico para mostrar el aporte de cada tiena al cambio en el resultado anual de la tienda

Este gráfico lo creamos usando el gráfico de columnas apiladas elaborando los datos con una tabla auxiliar (las fórmulas y técnicas pueden verse en la nota del enlace, también podrán descargar el archivo del ejemplo).

Esta técnica tiene un inconveniente, como me lo hace notar uno de mis lectores. Los valores de los cambios no cruzan el eje horizontal. Para demostrarlo, supongamos que nuestra cadena de tiendas ha tenido un año 2013 terrible

Aplicando la técnica que usamos hasta ahora obtendríamos este gráfico

La columna ("ladrillo") tendría que aparecer cruzando el eje horizontal, entrando en la zona de los números negativos:

Para solucionar este problema tendremos que cambiarlas fórmulas en la tabla auxiliar. Esta es la tabla del modelo sencillo

Esta es la nueva tabla auxiliar sobre la cual construimos el gráfico

A diferencia del modelo sencillo, calculamos dos columnas para los valores positivos (Positivo +; Positivo -) y dos columnas para los valores negativos (Negativo +; Negativo -). "Positivo +" y "Negativo +" para los valores que se mostrarán por encima del eje horizontal; "Positivo -" y "Negativo -" para los valores que aparecerán por debajo del eje de la X.

Las fórmulas de las columnas G y H son obvias.

La fórmula de la columna I (Acumulado) es:

=MAX(0,MIN(SUMA(G$3:G3),SUMA(G$3:G4)))+MIN(0,MAX(SUMA(G$3:G3),SUMA(G$3:G4)))

Esta fórmula crea la parte "invisible" de la columna.

La fórmula en la columna J (Positivo +): =MAX(0,MIN(SUMA(G$3:G4),G4))

La fórmula en la columna K (Positivo -): =-MAX(0,G4-J4) (prestar atención al "-")

La fórmula en la columna L (Negativo +): =MAX(0,M4-G4)

La fórmula en la columna M (Negativo -): =MIN(0,MAX(SUMA(G$3:G4),G4))

Para crear el gráfico seleccionamos primero el rango F2:F8 y manteniendo el botón Ctrl apretado seleccionamos el rango H2:N8. Seleccionamos el gráfico de columnas apiladas

El último paso es aplicar los formatos necesarios.

El archivo se puede descargar aquí.