Note: The other languages of the website are Google-translated. Back to English

Crear gráfico de variación de presupuesto vs real en Excel

En Excel, se utiliza un gráfico de varianza de presupuesto frente a real para comparar dos conjuntos de datos de series y mostrar la diferencia o varianza de las dos series de datos. Si las diferencias son valores negativos, se muestran barras o columnas de un color, si hay valores positivos, se muestran otras barras o columnas de colores como se muestra a continuación. En este artículo, hablaré sobre cómo crear el gráfico de varianza de comparación y un mini gráfico de varianza de comparación de celda en el libro de Excel.


Cree un gráfico de variación de presupuesto vs real en Excel

Para crear el gráfico de variación de presupuesto frente a real, siga los siguientes pasos:

1. Ingrese la fórmula a continuación en la celda D2 para calcular la diferencia entre el presupuesto y los valores reales, y luego arrastre el controlador de relleno hacia las celdas que necesita, vea la captura de pantalla:

=C2-B2

2. Luego, seleccione los datos en la columna A y la columna D, y luego haga clic en recuadro > Insertar gráfico de columnas o barras > Columna agrupada, ver captura de pantalla:

3. Y se inserta un gráfico de columnas, luego puede eliminar los elementos no deseados del gráfico que necesite, como eliminar el título del gráfico, el eje vertical, las líneas de cuadrícula, y obtendrá el resultado como se muestra a continuación:

4. Luego, haga clic derecho en el eje horizontal y elija Formato de eje opción del menú contextual, ver captura de pantalla:

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

6. Y luego, haga clic derecho en cualquiera de las barras y elija Formato serie de datos opción, ver captura de pantalla:

7. En el abierto Formato serie de datos panel, debajo del Rellenar línea pestaña, desde la Llene sección, realice las siguientes operaciones:

  • Seleccione Relleno sólido opción;
  • Revise Invertir si es negativo casilla de verificación;
  • Especifique dos colores para los valores positivos y negativos por separado del Color desplegable.

8. Después de configurar los colores para las barras, debe agregar las etiquetas de datos, haga clic para seleccionar las barras en el gráfico y luego haga clic en Elementos del gráfico icono, luego verifique Etiquetas de datos de Elementos del gráfico cuadro de lista, ver captura de pantalla:

9. Las barras de la columna son tan estrechas que no se ven hermosas, en este caso, puede ajustar el ancho de la barra a sus necesidades, haga clic derecho en cualquiera de las barras y luego elija Formato serie de datos, En la Formato serie de datos panel, debajo del Opciones de serie pestaña, cambie el Anchura de rendija a tu necesidad, este caso, lo cambiaré a 80 %, ver captura de pantalla:

Consejos:

Después de crear el gráfico, a veces, es posible que desee mostrar las etiquetas de datos con diferentes colores, por ejemplo, las etiquetas de datos positivos en color verde y las etiquetas de datos negativos en color naranja, haga lo siguiente:

Seleccione los datos en la columna Varianza de la tabla de datos original y luego presione Ctrl + 1 para abrir el Formato de celdas cuadro de diálogo, en el Formato de celdas cuadro de diálogo, haga clic en Personalizado de Categoría cuadro de lista, y luego escriba el siguiente código en el Tipo caja de texto:

[Color10]General;[Color53] -General

A continuación, haga clic en OK botón, y obtendrá el resultado que necesite, vea la captura de pantalla:

Nota:: Si necesita otros colores, consulte la siguiente tabla de índice de colores:


Crear un mini presupuesto frente a un gráfico de variación real en las celdas

Si desea insertar un cuadro de variación de mini presupuesto frente a real en las celdas, los siguientes pasos pueden hacerle un favor:

1. Ingrese la fórmula a continuación en la celda D2 para calcular la diferencia entre el presupuesto y los valores reales, y luego arrastre el controlador de relleno hacia las celdas que necesita, vea la captura de pantalla:

=C2-B2

2. Luego, muestre los valores negativos de la varianza, aplique la siguiente fórmula en la celda E2 y luego copie la fórmula en otras celdas que necesite, vea la captura de pantalla:

=IF(D2<0,D2,"")

3. Muestre los valores positivos de la varianza, escriba la fórmula a continuación en la celda F2 y luego copie la fórmula en otras celdas que necesita, vea la captura de pantalla:

=IF(D2>0,D2,"")

4. Y luego, debe insertar un símbolo específico que se utilizará como las barras del gráfico, haga clic en recuadro > Símbolo, Y en el Símbolo cuadro de diálogo, seleccione Elementos de bloque que puedes encontrar en casi todas las fuentes del Subconjunto lista desplegable y haga clic en el Bloque completo símbolo, luego haga clic en recuadro para insertar el símbolo, vea la captura de pantalla:

5. Después de insertar el símbolo especial, aplique la fórmula a continuación en la celda G2 y arrastre esta fórmula a otras celdas, y obtendrá el siguiente resultado:

=IF(E2="","",E2&" "&REPT($A$16,ABS(E2)/100))
Nota:: En la fórmula anterior, E2 es la celda que contiene el valor de varianza negativo, A16 es la celda que contiene el símbolo específico que está insertado, y el número 100 es un dato variable que se utiliza para calcular el número del símbolo en función del valor de variación, puede cambiarlo por el suyo.

6. Continúe aplicando la fórmula a continuación en la celda H2, y luego arrastre esta fórmula a otras celdas, y obtendrá el siguiente resultado:

=IF(F2="","",REPT($A$16,F2/100)&" "&F2)
Nota:: En la fórmula anterior, F2 es la celda que contiene el valor de varianza positivo, A16 es la celda que contiene el símbolo específico que está insertado, y el número 100 es un dato variable que se utiliza para calcular el número del símbolo en función del valor de variación, puede cambiarlo por el suyo.

7. Y luego, seleccione las celdas en la columna G, y luego haga clic en Inicio > alinear a la derecha para alinear los datos directamente en las celdas, vea la captura de pantalla:

8. Por último, puede formatear los colores de fuente para las dos columnas por separado según lo necesite, y obtendrá el resultado como se muestra a continuación:


Cree un gráfico de variación de presupuesto frente a real con una característica sorprendente

Kutools for Excel proporciona docenas de tipos especiales de gráficos que Excel no tiene, como Gráfico de viñetas, Gráfico objetivo y real, Gráfico de flechas de diferencia y así. Con su práctica herramienta Gráfico de comparación de diferencias, puede crear un gráfico de variación de presupuesto frente a real en la hoja de trabajo o un gráfico de variación de mini presupuesto frente a real dentro de las celdas de forma rápida y sencilla. ¡Haga clic para descargar Kutools for Excel para una prueba gratuita!


Descargar el archivo de muestra del gráfico de variación de presupuesto vs real


Video: Cree un gráfico de variación de presupuesto vs real en Excel


Las mejores herramientas de productividad de oficina

Kutools para Excel: te ayuda a destacar entre la multitud

¿Le gustaría completar su trabajo diario de forma rápida y perfecta? Kutools for Excel ofrece 300 potentes funciones avanzadas (combinar libros de trabajo, sumar por color, dividir contenido de celdas, convertir fecha, etc.) y ahorrarle un 80% de tiempo.

  • Diseñado para 1500 escenarios de trabajo, te ayuda a resolver el 80% de problemas de Excel.
  • Reduzca miles de clics de teclado y mouse todos los días, alivie sus ojos y manos cansados.
  • Conviértete en un experto en Excel en 3 minutos. Ya no es necesario recordar fórmulas dolorosas y códigos VBA.
  • Prueba gratuita ilimitada de 30 días. Garantía de devolución de dinero de 60 días. Actualización y soporte gratuitos durante 2 años.
Cinta de Excel (con Kutools para Excel instalado)

Ficha Office: habilite la lectura y edición con pestañas en Microsoft Office (incluya Excel)

  • ¡Un segundo para cambiar entre docenas de documentos abiertos!
  • Reduzca cientos de clics del mouse para usted todos los días, diga adiós a la mano del mouse.
  • Aumenta su productividad en un 50% al ver y editar varios documentos.
  • Trae pestañas eficientes a Office (incluye Excel), al igual que Chrome, Firefox y el nuevo Internet Explorer.
Captura de pantalla de Excel (con la pestaña Office instalada)
Comentarios (1)
Aún no hay calificaciones. ¡Sé el primero en calificar!
Este comentario fue minimizado por el moderador en el sitio
hola,
Comentario faire si je souhaite ajouter à chaque barre un petit trait pour montrer le dépassement ou non du budget ?
gracias
No hay comentarios publicados aquí todavía
Deje sus comentarios
Publicar como invitado
×
Califica esta publicación:
0   Personajes
Ubicaciones sugeridas

Seguinos

Copyright © 2009 - www.extendoffice.com. | Reservados todos los derechos. Energizado por ExtendOffice, | Mapa del Sitio
Microsoft y el logotipo de Office son marcas comerciales o marcas comerciales registradas de Microsoft Corporation en los Estados Unidos y / o en otros países.
Protegido por Sectigo SSL