Como Crear gráficos de pendiente en Excel

No les voy a mentir, la primera vez que vi un gráfico de pendiente, quedé completamente decepcionado, pensé “es sólo un gráfico de líneas con dos puntos, no vale la pena emocionarse por ello”. Pero la reacción, la primera vez que usé uno en una presentación, fue increíble. La gente incluso me pidió mi plantilla para poder crear ellos mismos gráficos de pendientes. En esta publicación, quiero mostrarte cómo crear gráficos de pendientes en Excel.

Tabla de contenido

• ¿Qué son los gráficos de pendientes?
• Gráfico de pendiente de formato fijo
• Los datos
• Crear el gráfico
• Formatear el gráfico
• Crear automáticamente con macro vba
• Gráfico de pendiente de formato dinámico
• Los datos
• Crear el gráfico
• Crear automáticamente con macro vba
• Conclusión

Descargue el archivo de ejemplo: únase al programa Insiders gratuito y obtenga acceso al archivo de ejemplo utilizado para esta publicación.

Nombre del archivo: 0074 Slopegraphs en Excel.zip

Tener acceso

¿Qué son los gráficos de pendientes?

Los Slopegraphs fueron introducidos por primera vez por Edward Tufte en 1983. Pero, creo, sólo en los últimos años, con el aumento en la visualización de datos, este gráfico ha llamado la atención de las masas.

Funcionan muy bien porque nuestros cerebros son buenos comparando pendientes y ángulos. Son útiles para comparar dos puntos equivalentes entre sí pero separados por el tiempo o por un evento específico. Los buenos usos serían el margen del producto entre meses o las ganancias de la empresa a lo largo de años.

Mire el cuadro a continuación: ¿qué le dice?

Con sólo un breve vistazo, el gráfico de pendiente muestra que Delta ha aumentado significativamente. Incluso sin resaltar, podemos ver que Alpha ha aumentado y Echo ha disminuido. Sencillo, no se necesita capacidad cerebral. Las pendientes/ángulos nos dan una manera fácil de entender la historia.

Gráfico de pendiente de formato fijo

Comencemos con un gráfico de pendiente de formato fijo. Esta es una visualización estática, por lo que debemos actualizar los colores manualmente si queremos resaltar diferentes elementos.

Los datos

Los datos que usaremos en este ejemplo son los siguientes:

Crear el gráfico

El gráfico se basa en un gráfico de líneas estándar.

1. Seleccione una celda dentro de la tabla.
2. En la cinta, haga clic en Insertar gráficos Gráfico de líneas
   
3. Aparece el gráfico de líneas estándar. Haga clic derecho en el área del gráfico , en el menú, haga clic en Seleccionar datos… .
   
4. Dentro del cuadro de diálogo Seleccionar fuente de datos , hay dos cuadros principales (el cuadro de la izquierda contiene años; el cuadro de la derecha contiene los productos). Necesitamos cambiarlos. Haga clic en el botón Cambiar fila/columna y luego haga clic en Aceptar.
   

Ahora estamos empezando a llegar a alguna parte. Ni siquiera hemos formateado este gráfico y ya está empezando a contarnos algunas historias clave.

Formatear el gráfico

Ahora, comencemos a formatear el gráfico. El formateo rápido y sencillo es:

• Elimina la leyenda (el cuadro que contiene la clave de color en la parte inferior).
• Cambiar, formatear o eliminar el título (en el archivo de ejemplo, lo he eliminado)
• Eliminar las líneas de cuadrícula principales
• Elimina el eje izquierdo (radical, lo sé).
• Haga clic derecho en el eje inferior y seleccione Formatear eje…/fuerte en el menú.
  
  En el panel Formato de eje , seleccione En marcas de graduación .
  

El siguiente paso de formato requiere bastante tiempo ya que tenemos que aplicarlo línea por línea; Sería genial hacerlo todo de una vez, pero desafortunadamente Excel no quiere funcionar de esa manera.

• Haga clic derecho en una línea , en el menú haga clic en Formatear serie de datos...
  
• En el panel Formatear serie de datos , haga clic en la opción Línea de lata de pintura Línea sólida y luego seleccione un color. Recomiendo usar un gris medio para cualquier cosa que no valga la pena resaltar.
  
• Ahora, ordenemos las etiquetas de datos. Haga clic derecho en la línea y seleccione Agregar etiquetas de datos en el menú.
  
• Establezca el color del texto de la etiqueta para que sea el mismo que el de la línea (es decir, el mismo color gris).
• Haga clic dos veces en la etiqueta de datos a la izquierda (ahora debería tener puntos blancos vacíos rodeándola), luego haga clic derecho y seleccione Formatear etiqueta de datos...
  
  
• En el panel Formatear etiqueta de datos, seleccione lo siguiente:
  • Nombre de la serie – seleccionado
  • Valor – seleccionado
  • Separador – (espacio)
  • Posición de la etiqueta: izquierda

• Formatee la etiqueta de datos a la derecha, de manera similar, pero no incluya el nombre de la serie; sólo necesitamos el Valor.

Repita los pasos de formato anteriores para cada línea; es un poco complicado, lo sé.

El gráfico ahora debería verse así:

Ahora es el momento de las ediciones finales:

• Cambiar el ancho del área de trazado.
• Cambie el tamaño del gráfico para que sea más alto en lugar de más ancho.

Finalmente, cambie el color de cualquier elemento que desee resaltar y haga que las etiquetas de datos tengan el mismo color que la línea.

Tiene muy buena pinta, ¿verdad?

Fue necesario un poco de formateo, pero al final lo logramos.

Crear automáticamente con macro vba

Todo ese formateo lleva bastante tiempo. Entonces, escribí la siguiente macro para manejarlo por nosotros. Agregue el código a un módulo de código estándar.

Para que la macro funcione, los datos deben almacenarse en una tabla de Excel con las columnas en el mismo orden que en el ejemplo anterior. Los encabezados de las columnas pueden ser diferentes, pero el orden de las columnas debe ser el mismo.

Para adaptar el código a sus necesidades, cambie los parámetros resaltadoCodeColor y resaltadoRowIndex .

Para ejecutar la macro, seleccione una celda dentro de la tabla de Excel y ejecute la macro.

Sub createSimpleSlopegraph()Dim ws As WorksheetDim tbl As ListObjectDim cht As ChartDim srs As SeriesDim i As LongDim standardColorCode As LongDim resaltadoColorCode As LongDim resaltadoRowIndex As Long'Seleccione el color a usar para resaltarstandardColorCode = RGB(125, 125, 125)highlightColorCode = RGB( 102, 30, 91)'Qué fila resaltarhighlightRowIndex = 4'Asigne las variablesSet ws = ActiveSheet'Seleccione la tabla desde la cual construir el SlopegraphEn caso de error Reanudar SiguienteEstablezca tbl = ActiveCell.ListObjectEn error Ir a 0Si tbl no es nada, salga de SubEnd si'Crear el chartSet cht = ws.Shapes.AddChart.Chart'Eliminar los valores predeterminados ya aplicados para cada srs en cht.SeriesCollection srs.DeleteNext srs'Eliminar la leyendacht.Legend.Delete'Eliminar el título del gráficoSi cht.HasTitle = True Entonces cht.ChartTitle.DeleteEnd If 'Eliminar el valor Axischt.Axes(xlValue).Delete'Eliminar gridlinescht.Axes(xlValue).MajorGridlines.Delete'Agregar una nueva serie de gráficos para cada fila en TableFor i = 1 To tbl.ListRows.Count Establecer srs = cht. SeriesCollection.NewSeries Con srs .Values ​​= ws.Range(tbl.ListColumns(2).DataBodyRange(i, 1), _ tbl.ListColumns(3).DataBodyRange(i, 1)) .XValues ​​= ws.Range(tbl. HeaderRowRange(2), tbl.HeaderRowRange(3)) .Name = "='" ws.Name "'!" tbl.ListColumns(1). _ DataBodyRange(i, 1).Address .ChartType = xlLine .Format.Fill.Visible = msoTrue .HasDataLabels = True Si i = resaltadoRowIndex Entonces .Format.Line.ForeColor.RGB = resaltadoColorCode .DataLabels.Format.TextFrame2.TextRange.Font .Rellenar.ForeColor. _ RGB = HighlightColorCode .DataLabels.Format.TextFrame2.TextRange.Font.Bold = msoTrue Else .Format.Line.ForeColor.RGB = StandardColorCode .DataLabels.Format.TextFrame2.TextRange.Font.Fill.ForeColor. _ RGB = standardColorCode End If Termina With srs.Points(1) .DataLabel.Position = xlLabelPositionLeft .DataLabel.ShowSeriesName = True .DataLabel.ShowValue = True .DataLabel.Separator = " " Termina With With srs.Points(2) . DataLabel.Position = xlLabelPositionRight .DataLabel.ShowSeriesName = False .DataLabel.ShowValue = True End WithNext Establezco el gráfico en las marcas de verificacióncht.Axes(xlCategory).AxisBetweenCategories = False'Cambiar el ancho del área de trazadocht.PlotArea.Width = cht.ChartArea .Ancho * 0.6cht.PlotArea.Left = cht.ChartArea.Width * 0.2End Sub

Gráfico de pendiente de formato dinámico

Si incluimos gráficos de pendientes en informes actualizados periódicamente o paneles dinámicos, no queremos cambiar los colores manualmente. Entonces, creemos un gráfico de pendiente que pueda resaltar dinámicamente líneas específicas.

Los gráficos de Excel tienen una característica útil mediante la cual el valor #N/A no se representa en el gráfico. Usamos mucho esto al crear gráficos dinámicos.

Los datos

Usaremos los mismos datos que en el ejemplo anterior, pero ahora hemos agregado algunas columnas duplicadas (no te preocupes, todo quedará claro).

Las fórmulas de la tabla son las siguientes:

Celda H2 (columna Incluir):

=[@Producto]=$J$3

La celda J3 contiene el nombre de la línea que queremos resaltar. En la captura de pantalla de datos anterior, se seleccionó Delta, por lo tanto, este elemento muestra VERDADERO en la columna Incluir.

Celda D2 (columna Estándar 2021):

=SI(tblDataDynamic[@[Include]:[Include]]=TRUE,NA(),[@2021])

La celda D2 contiene la lógica de que si la columna Incluir es VERDADERA, entonces muestra #N/A; de lo contrario, muestra el valor de 2021.

Celda E2 (columna Estándar 2022):

=SI(tblDataDynamic[@[Incluir]:[Incluir]]=TRUE,NA(),[@2022])

La celda E2 contiene la misma lógica que la anterior, pero devuelve el valor de 2022.

Celda F2 (columna Destacada 2021):

=SI(tblDataDynamic[@[Include]:[Include]]=TRUE,[@2021],NA())

La celda F2 contiene la lógica de que, si la columna Incluir es VERDADERA, se devuelve el valor de 2021; de lo contrario, se devuelve #N/A.

Celda G2 (columna Destacada 2022):

=SI(tblDataDynamic[@[Include]:[Include]]=TRUE,[@2022],NA())

La celda G2 contiene la misma lógica que la anterior, pero devuelve el valor de 2022.

Resumen de fórmula

Las fórmulas anteriores garantizan que los valores existan en las columnas Estándar o en las columnas Resaltadas, pero nunca en ambas. Eche otro vistazo a la captura de pantalla de la tabla anterior.

• Alfa tiene valores en el Estándar, pero las columnas Resaltadas muestran #N/A.
• Delta (el elemento seleccionado) tiene valores en Resaltado, pero muestra #N/A en las columnas Estándar.

Crear el gráfico

Usando los valores en las columnas Producto, Estándar 2021 y Estándar 2022, cree un gráfico usando exactamente el mismo método que el formato fijo que se muestra arriba, manteniendo todas las líneas en gris medio.

Ahora agregue series de gráficos para cada fila usando los valores en las columnas Destacado 2021 y Destacado 2022.

• Haga clic derecho en el gráfico , en el menú, haga clic en Seleccionar datos...
• En el cuadro de diálogo Seleccionar fuente de datos , haga clic en Agregar
• Agregue el nombre de la serie y los valores de la serie para esa fila de la tabla, luego haga clic en Aceptar .

Repita estas acciones para cada fila de la tabla de Excel.

El gráfico de pendiente ahora tendrá los datos originales de las columnas Estándar, con los valores de las columnas Resaltadas colocados directamente encima.

Formatee las nuevas líneas resaltadas en el color que desee utilizar.

Como los valores #N/A no se muestran en el gráfico, deberá cambiar los parámetros de valor para asegurarse de haber formateado cada línea correctamente.

He conectado la celda J3 a una lista de validación de datos. Después de formatear el gráfico, se verá así:

Si queremos resaltar una línea diferente, simplemente podemos seleccionar otro elemento de la lista de validación de datos.

Crear automáticamente con macro vba

Ahorrémonos mucho tiempo de formateo, ¿de acuerdo? Una vez más, podemos simplemente hacer clic en una celda dentro de la tabla y ejecutar la siguiente macro de VBA.

El código debe ingresarse en un módulo de código estándar y las columnas deben estar en el mismo orden que se muestra en la sección directamente arriba.

Para adaptar el código a sus necesidades, deberá cambiar el resaltadoCodeColor .

Sub createDyanamicSlopegraph()Dim ws As WorksheetDim tbl As ListObjectDim cht As ChartDim srs As SeriesDim i As LongDim standardColorCode As LongDim highlightsColorCode As Long'Seleccione el color que se utilizará para resaltarstandardColorCode = RGB(125, 125, 125)highlightColorCode = RGB(102, 30 , 91)'Asigne las variablesSet ws = ActiveSheet'Seleccione la tabla para construir el Slopegraph desde On Error Resume NextSet tbl = ActiveCell.ListObjectOn Error GoTo 0 Si tbl no es nada, salga de SubEnd If'Cree el chartSet cht = ws.Shapes.AddChart. Chart'Eliminar los valores predeterminados ya aplicados para cada srs en cht.SeriesCollection srs.DeleteNext srs'Eliminar la leyendacht.Legend.Delete'Eliminar el título del gráficoSi cht.HasTitle = True Entonces cht.ChartTitle.DeleteEnd If'Eliminar el valor Axischt.Axes(xlValue ).Delete'Eliminar gridlinescht.Axes(xlValue).MajorGridlines.Delete'Agregar una nueva serie de gráficos para cada fila en TableFor i = 1 Para tbl.ListRows.Count Establecer srs = cht.SeriesCollection.NewSeries Con srs .Values ​​= ws .Range(tbl.ListColumns(4).DataBodyRange(i, 1), _ tbl.ListColumns(5).DataBodyRange(i, 1)) .XValues ​​= ws.Range(tbl.HeaderRowRange(2), tbl.HeaderRowRange( 3)) .Nombre = "='" ws.Nombre "'!" tbl.ListColumns(1). _ DataBodyRange(i, 1).Address .ChartType = xlLine .Format.Line.ForeColor.RGB = standardColorCode .HasDataLabels = True .DataLabels.Format.TextFrame2.TextRange.Font.Fill.ForeColor. _ RGB = standardColorCode Termina con srs.Points(1) .DataLabel.Position = xlLabelPositionLeft .DataLabel.ShowSeriesName = True .DataLabel.ShowValue = True .DataLabel.Separator = " " Termina con srs.Points(2) .DataLabel. Position = xlLabelPositionRight .DataLabel.ShowSeriesName = False .DataLabel.ShowValue = True End WithNext i'Agregue la serie resaltada para cada fila en TableFor i = 1 To tbl.ListRows.Count Set srs = cht.SeriesCollection.NewSeries With srs .Values = ws.Range(tbl.ListColumns(6).DataBodyRange(i, 1), _ tbl.ListColumns(7).DataBodyRange(i, 1)) .XValues ​​= ws.Range(tbl.HeaderRowRange(4), tbl. HeaderRowRange(3)) .Name = "='" ws.Name "'!" tbl.ListColumns(1). _ DataBodyRange(i, 1).Address .ChartType = xlLine .Format.Line.ForeColor.RGB = resaltadoColorCode .HasDataLabels = True .DataLabels.Format.TextFrame2.TextRange.Font.Fill.ForeColor. _ RGB = resaltadoColorCode .DataLabels.Format.TextFrame2.TextRange.Font.Bold = msoTrue Terminar con Con srs.Points(1) .DataLabel.Position = xlLabelPositionLeft .DataLabel.ShowSeriesName = True .DataLabel.ShowValue = True .DataLabel.Separator = " " Terminar con Con srs.Points(2).DataLabel.

Conclusión

Como hemos visto, los gráficos de pendiente son un poco complicados de configurar, pero con una macro, podemos crearlos con bastante rapidez. Personalmente, creo que vale la pena el esfuerzo, ya que son hermosos en su simplicidad narrativa.

---