Cómo hacer comparaciones visuales de las ventas reales con las metas objetivo en un gráfico de Excel

Cuando la relación entre las entidades del gráfico es menos importante que las entidades individuales, intente utilizar estas dos técnicas de gráficos para resaltar detalles importantes en Microsoft Excel.

Imagen: iStock / muchomor

Los gráficos son herramientas impresionantes cuando cuentan una historia, y lo hacen resaltando detalles que de otro modo podrían perderse en el ruido del gráfico. Cuanto más silencioso es un gráfico, en mi opinión, más eficaz es. Esto se representa fácilmente aclarando los detalles en un gráfico que compara las ventas reales del personal con el objetivo de cada persona. Un buen gráfico contará la historia de los datos con poco o ningún esfuerzo por parte del espectador para encontrar las pistas ocultas, los detalles que importan. En este artículo, le mostraré dos gráficos que representan las ventas y los objetivos en Excel. Comenzaremos con los gráficos predeterminados y cambiaremos el formato para lograr un gráfico limpio que exponga los detalles que la administración seguramente querrá saber.

VER: 83 consejos de Excel que todo usuario debería dominar (TechRepublic)

Estoy usando Microsoft 365 en un sistema Windows 10 de 64 bits, pero puede usar versiones anteriores. Excel Online mostrará los gráficos, pero no puede crearlos en línea. Para su comodidad, puede descargar los archivos de demostración .xlsx y .xls.

Prepara tus datos

Para obtener los resultados esperados, la forma en que estructura sus datos es importante. A veces, puede cambiar los valores de eje y serie una vez que existe el gráfico, pero en su mayor parte, debe comenzar con un diseño de estructura estable, y eso no es tan difícil como parece. Además, recuerde que no tiene que graficar todo el conjunto de datos; puede representar gráficamente las columnas y filas que desee, incluso si no son contiguas. Esta vez no nos ocuparemos de ese tipo de gráfico, pero es bueno saberlo.

VER: Cómo usar XLOOKUP () para encontrar puntos de referencia de comisiones en Excel (TechRepublic)

Para nuestros propósitos, realizaremos un seguimiento de los montos de ventas reales y la cuota o el objetivo objetivo de cada persona. Mucha gente en ventas trabaja con expectativas específicas. Las fórmulas que determinan comisiones y bonificaciones suelen evaluar las diferencias entre las ventas reales y los objetivos; La comisión y el monto del bono probablemente usan un porcentaje más alto cuando los objetivos se alcanzan o exceden de manera rutinaria.

Figura A muestra un conjunto de datos simple sin problemas estructurales reales. El gráfico de la derecha es un gráfico de columnas 2D que agrupa las ventas reales y el monto objetivo de cada persona. (Ingresé un nuevo título de gráfico, pero no es necesario que lo haga). Observe que los datos seleccionados incluyen el texto del encabezado y no incluyen la columna de Comisión.

Figura A

El gráfico predeterminado agrupa los valores de ventas objetivo y reales para cada persona.

Tal como está, el gráfico está bien, pero es un poco difícil distinguir quién está cumpliendo su objetivo y quién no. Tienes que mirar cada conjunto de barras para discernir algo significativo, ¡y no es así como se supone que funcionan los gráficos!

Si no sabe cómo insertar un gráfico, haga lo siguiente:

Seleccione B2: D8: los datos que desea en el gráfico. Incluya el texto del encabezado para que Excel pueda generar una leyenda automáticamente. Haga clic en Insertar y luego en Insertar columna o gráfico de barras en el grupo Gráficos. Elija la primera opción en la primera línea. ¡Eso es todo!

Crear este gráfico fue fácil, pero creo que todos podemos estar de acuerdo en que no es tan efectivo como podría ser. Los valores, tanto objetivo como reales, están demasiado cerca entre sí para resaltar realmente lo que está sucediendo (o no). Por ejemplo, es posible que se pierda por completo que James no cumplió con su objetivo y que Luke, el chico nuevo, ya está superando su objetivo inicial. James necesita una charla de ánimo; Luke se ha ganado una palmada en la espalda.

Cómo superponer dos series en Excel

La superposición de las dos series es una forma de aclarar un poco las cosas. En otras palabras, movamos la serie de destino para que esté encima de la serie de valor real. La diferencia entre el objetivo de cada persona y las ventas reales será más evidente, o al menos, creemos que deberían serlo. A veces funciona; a veces no es así.

Para generar la serie superpuesta, haga lo siguiente:

Haga clic en cualquiera de las barras de la serie real (las barras azules) para seleccionar la serie completa. Haga clic con el botón derecho en la sección y elija Dar formato a serie de datos en el submenú resultante, que abrirá el panel Dar formato a serie de datos (a la derecha). Cambie la opción Plot Series On a Secondary Axis, como se muestra en Figura B. Al hacerlo, se agrega un nuevo eje al lado derecho. Es fácil ver que James no ha cumplido su objetivo, pero ahora June y Luke son un misterio. Han superado sus objetivos, pero ¿en cuánto? Regrese al gráfico y seleccione la serie de destino (las columnas naranjas). Cambie el ancho del espacio a 100. Como puede ver en Figura C, todo es más aparente, pero tenemos que ocuparnos de ese eje secundario (vertical). El eje secundario nos permitió formatear las dos series de manera diferente, pero ya no lo necesitamos y, francamente, agrega confusión. Seleccione el eje secundario a la derecha y presione Eliminar. Figura D muestra la serie superpuesta resultante, ¡y tiene sentido!

Figura B

Agregue un eje secundario; hacerlo nos permitirá formatear las dos series de manera diferente.

Figura C

Cambie el ancho del espacio para la serie de destino.

Figura D

Después de eliminar el eje secundario, las barras superpuestas presentan una imagen significativa.

No sé ustedes, pero los colores predeterminados me parecen un poco chillones. No es necesario para la técnica, pero es posible que desee cambiar el color de una o ambas series. Puede elegir los colores que desee, pero voy a cambiar las columnas de destino, el color naranja a un azul más claro eligiendo un tema diferente. Para hacerlo, haga clic en el icono Formato en la esquina superior derecha del gráfico, es el pincel. En el panel resultante, haga clic en Color (en la parte superior). Elija un tema más agradable. Elegí el penúltimo, que son tonos de azul. Siéntase libre de elegir cualquier tema que desee. Alternativamente, puede hacer clic con el botón derecho en la serie, elegir Dar formato a serie de datos y hacer clic en la opción Rellenar para cambiar solo una serie.

VER: Office 365: una guía para líderes tecnológicos y empresariales (PDF gratuito) (TechRepublic)

Las columnas superpuestas resaltan mejor los detalles que el gráfico predeterminado, pero no es la única forma de representar estos valores. De hecho, debido a que los valores están tan cerca, el fracaso de James en alcanzar el objetivo todavía es fácil de pasar por alto; no es tan fácil, pero creo que aún podemos hacerlo mejor.

Cómo hacer un gráfico combinado en Excel

El problema con el que nos hemos ocupado desde el principio es que todos los valores son similares, por lo que no hay mucho contraste, contraste que llamaría nuestra atención y haría que algunos detalles fueran más obvios. Esta vez representaremos los valores objetivo como una línea en lugar de una columna mediante un gráfico combinado.

Para comenzar, seleccione los mismos datos, B2: D8 y luego elija Insertar gráfico combinado en el grupo Gráficos en la pestaña Insertar. Elija un gráfico de líneas de columnas agrupadas. Figura E muestra el nuevo gráfico (usando el mismo azul y naranja predeterminados). Sin hacer nada, está claro dónde están todos. Sin embargo, puede cambiar el tema de color como hicimos en el primer gráfico. Como antes, no es necesario cumplir con los requisitos técnicos; es más fácil para los ojos.

Figura E

En este punto, puede detenerse, pero le mostraré un cambio que podría gustarle. Específicamente, eliminaremos las líneas de conexión y cambiaremos el formato de los marcadores. El resultado es un gráfico más limpio, pero pierde el flujo de las líneas de conexión que pueden enfatizar las diferencias entre el personal. Pero en este caso, comparar el personal puede no ser de interés.

Nuestra línea no tiene marcadores, así que ese es el primer paso. Si ya tiene marcadores, puede omitir la siguiente sección. Para mostrar marcadores, seleccione la línea y luego haga clic en la pestaña contextual Diseño de gráfico. En el grupo Tipo, haga clic en Cambiar tipo de gráfico. En el cuadro de diálogo resultante, cambie la configuración de destino de Línea a Línea apilada con marcadores, como se muestra en Figura Fy luego haga clic en Aceptar.

Figura F

Agregue marcadores a su línea, si es necesario.

Para eliminar las líneas de conexión, haga doble clic en él para abrir el panel Formato de serie de datos, haga clic en la opción Rellenar y línea y, a continuación, haga clic en Línea en la parte superior (si es necesario). Si no le gustan los marcadores redondos, cámbielos de la siguiente manera:

Haga clic en Marcador (en la parte superior). En la sección Opciones de marcador, haga clic en Integrado. Elija la línea horizontal del menú desplegable Tipo y aumente su tamaño; Lo cambié a 15.

Como puedes ver en Figura F, eliminar las líneas de conexión produce un gráfico más limpio, pero la verdad es que este cambio es preferencial. Un vistazo rápido es todo lo que necesita para ver que James es la única persona que no alcanza el objetivo. Una segunda observación rápida es que ninguno de los miembros del personal está produciendo muy por encima de su objetivo. Además, podría ser el momento de aumentar el objetivo de Luke, lo que probablemente signifique también aumentar su porcentaje de comisión. Él estará feliz de escuchar eso.

Figura F

James no ha cumplido su objetivo.

Hay otras formas de lograr resultados visuales que resaltan los detalles de los datos. Puede usar minigráficos o incluso formato condicional si no desea crear un gráfico tradicional. Puede utilizar cualquiera de estas técnicas para cualquier tipo de comparación individual en la que la relación entre las entidades registradas no sea tan importante como las personas.

Ver también