Crear un gráfico de varianza presupuesto frente a real en Excel

En Excel, un gráfico de varianza presupuesto frente a real permite comparar dos series de datos y visualizar claramente la diferencia —o varianza— entre ellas. Cuando las diferencias son negativas, se representan con barras o columnas de un color; si son positivas, se muestran en otro color, tal como se ilustra en la siguiente captura de pantalla. En este artículo te explicaré cómo crear tanto un gráfico de comparación de varianza como un minigráfico de comparación de varianza directamente dentro de las celdas de tu libro de Excel.

• Crear un gráfico de varianza presupuesto frente a real en Excel
• Crear un mini gráfico de varianza presupuesto frente a real dentro de celdas
• Crear un gráfico de varianza presupuesto frente a real con una función sorprendente
• Descargar archivo de ejemplo de gráfico de varianza presupuesto frente a real
• Vídeo: Crear un gráfico de varianza presupuesto frente a real en Excel

Crear un gráfico de varianza presupuesto frente a real en Excel

Para crear el gráfico de varianza presupuesto frente a real, siga estos pasos:

1. Introduzca la siguiente fórmula en la celda D2 para calcular la diferencia entre el presupuesto y el valor actual y, a continuación, arrastre el controlador de relleno hacia abajo hasta las celdas necesarias, tal como se muestra en la captura de pantalla:

2. A continuación, seleccione los datos de la columna A y la columna D y haga clic en Insertar > Insertar columna o gráfico de barras > Columna agrupada, tal como se muestra en la captura de pantalla:

3. Se insertará un gráfico de columnas; a continuación, podrá eliminar los elementos innecesarios del gráfico según sus necesidades, como el título del gráfico, el eje vertical y las líneas de división, hasta obtener el resultado que se muestra en las siguientes capturas de pantalla:

4. A continuación, haga clic con el botón derecho en el eje horizontal y seleccione la opción Formato del eje en el menú contextual, tal como se muestra en la captura de pantalla:

5. En el panel Formato del eje abierto, en la pestaña Opciones del eje, haga clic en Etiquetas para expandir esta sección, seleccione Baja en la lista desplegable Posición de etiqueta y el eje horizontal se colocará en la parte inferior del gráfico, tal como se muestra en la captura de pantalla:

6. A continuación, haz clic con el botón derecho en cualquiera de las barras y selecciona la opción Formato de serie de datos, tal como se muestra en la captura de pantalla:

7. En el panel Formato de serie de datos abierto, en la pestaña Relleno y línea, dentro de la sección Relleno, realice las siguientes operaciones:

• Seleccione la opción Relleno sólido;
• Marque la casilla Invertir si es negativoCasilla de Verificación;
• Especifique dos colores distintos para los valores positivos y negativos desde el menú desplegable Color.

8. Tras establecer los colores de las barras, debe agregar las Etiquetas de Datos. Para ello, haga clic para seleccionar las barras del gráfico y, a continuación, pulse el icono Elementos del gráfico. A continuación, marque la casilla Etiquetas de Datos en el cuadro de lista Elementos del gráfico, tal como se muestra en la captura de pantalla:

9. Las barras de las columnas son tan estrechas que no resultan estéticas. En este caso, puede ajustar su anchura según sus necesidades. Haga clic con el botón derecho en cualquiera de las barras y elija Formato de serie de datos. En el panel Formato de serie de datos, en la pestaña Opciones de serie, modifique el valor de Anchura del intervalo según sus preferencias. En este ejemplo, lo cambiaremos a 80 %, tal como se muestra en la captura de pantalla:

Nota: Si necesita otros colores, consulte la siguiente tabla de Código de color:

Crear un mini gráfico de varianza presupuesto frente a real dentro de celdas

Si desea insertar un mini gráfico de varianza presupuesto frente a real dentro de celdas, los siguientes pasos le serán de utilidad:

1. Introduzca la siguiente fórmula en la celda D2 para calcular la diferencia entre el presupuesto y Valor actual y arrastre el controlador de relleno hacia abajo hasta las celdas necesarias, tal como se muestra en la captura de pantalla:

2. Para mostrar los valores negativos de la varianza, aplique la siguiente fórmula en la celda E2 y cópiela en las demás celdas necesarias, tal como se muestra en la captura de pantalla:

3. Para mostrar los valores positivos de la varianza, introduce la siguiente fórmula en la celda F2 y cópiala en las demás celdas necesarias, tal como se muestra en la captura de pantalla:

4. A continuación, debe insertar un símbolo específico que servirá como barra del gráfico. Para ello, haga clic en Insertar > Símbolo. En el cuadro de diálogo Símbolo, seleccione Elementos de bloque —una opción disponible en casi cualquier tipo de letra— en la lista desplegable Subconjunto, haga clic en el símbolo Bloque completo y, a continuación, pulse Insertar para añadir el símbolo, tal como se muestra en la captura de pantalla:

5. Después de insertar el símbolo especial, aplique la siguiente fórmula en la celda G2, arrástrela al resto de las celdas y obtendrá el resultado que se muestra a continuación:

6. Aplique también la siguiente fórmula en la celda H2, arrástrela al resto de las celdas y obtendrá el resultado que se muestra a continuación:

7. A continuación, seleccione las celdas de la columna G y haga clic en Inicio > Alinear a la Derecha para alinear los datos a la derecha en las celdas, tal como se muestra en la captura de pantalla:

8. Por último, puede aplicar el color de fuente que desee a cada una de las columnas por separado y obtendrá el resultado que se muestra en la siguiente captura de pantalla:

Crear un gráfico de varianza presupuesto frente a real con una función sorprendente

Descargar archivo de ejemplo de gráfico de varianza presupuesto frente a real

Vídeo: Crear un gráfico de varianza presupuesto frente a real en Excel