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

Opret Budget vs Faktisk afvigelsesdiagram i Excel

I Excel bruges et budget vs faktisk variansdiagram til at sammenligne to sæt seriedata og vise forskellen eller variansen for de to dataserier. Hvis forskellene er negative værdier, vises en farvet bjælke eller kolonner, hvis positive værdier vises en anden farvet bjælke eller kolonne som vist nedenstående skærmbillede. Denne artikel vil jeg tale om, hvordan man opretter sammenligningsvariansdiagrammet og et mini i cellesammenligningsvariansdiagram i Excel-projektmappe.


Opret budget vs faktisk variansdiagram i Excel

For at oprette budgettet versus det faktiske variansdiagram skal du gøre følgende:

1. Indtast nedenstående formel i celle D2 for at beregne forskellen mellem budgettet og de faktiske værdier, og træk derefter fyldhåndtaget ned til de celler, du har brug for, se skærmbillede:

=C2-B2

2. Vælg derefter dataene i kolonne A og kolonne D, og ​​klik derefter på indsatte > Indsæt søjle- eller søjlediagram > Klynget kolonne, se skærmbillede:

3. Og der indsættes et søjlediagram, så kan du slette de uønskede elementer i diagrammet, som du har brug for, såsom at slette diagrammetitel, lodret akse, gitterlinjer, og du får resultatet som vist nedenstående skærmbilleder:

4. Højreklik derefter på den vandrette akse, og vælg Formater akse mulighed fra genvejsmenuen, se skærmbillede:

5. I åbnet Formater akse under ruden Aksemuligheder fanebladet, klik på Etiketter for at udvide dette afsnit, og vælg derefter Lav fra Etiketposition rulleliste, og den vandrette akse placeres i bunden af ​​diagrammet, se skærmbillede:

6. Højreklik derefter på en af ​​bjælkerne, og vælg Formater dataserier mulighed, se skærmbillede:

7. I åbnet Formater dataserier under ruden Udfyld & linje fanen fra Fyld udfør følgende handlinger:

  • Type Fast fyld mulighed;
  • Check Inverter hvis negativ afkrydsningsfelt
  • Angiv to farver for de positive og negative værdier separat fra Farve falde ned.

8. Når du har indstillet farverne for bjælkerne, skal du tilføje dataetiketterne. Klik for at vælge bjælkerne i diagrammet, og klik derefter på Kortelementer ikon, og tjek derefter Datamærkater fra Kortelementer listefelt, se skærmbillede:

9. Søjlestængerne er så smalle, at de ikke ser smukke ud. I dette tilfælde kan du justere stangbredden efter dit behov. Højreklik på en af ​​bjælkerne, og vælg derefter Formater dataserierI Formater dataserier under ruden Serieindstillinger fanen, skift Gapbredde til dit behov, denne sag, vil jeg ændre det til 80 %, se skærmbillede:

Tips:

Når du har oprettet diagrammet, kan det være en god idé at vise dataetiketterne i forskellige farver, for eksempel de positive dataetiketter i den grønne farve og de negative dataetiketter i orange farve. Gør som følger:

Vælg dataene i kolonnen Variance fra den originale datatabel, og tryk derefter på Ctrl + 1 at åbne formater celler dialogboksen i formater celler dialogboksen, klik Tilpasset fra Boligtype listefelt, og skriv derefter nedenstående kode i Type tekstboks:

[Color10]General;[Color53] -General

Klik derefter på OK knappen, og du får resultatet efter behov, se skærmbillede:

Bemærk: Hvis du har brug for andre farver, skal du se følgende farveindeks-tabel:


Opret mini-budget vs faktisk variansdiagram i celler

Hvis du vil indsætte et mini-budget vs det faktiske variansdiagram i celler, kan følgende trin gøre dig en tjeneste:

1. Indtast nedenstående formel i celle D2 for at beregne forskellen mellem budgettet og de faktiske værdier, og træk derefter fyldhåndtaget ned til de celler, du har brug for, se skærmbillede:

=C2-B2

2. Vis derefter de negative værdier for variansen, anvend følgende formel i celle E2, og kopier derefter formlen til andre celler, du har brug for, se skærmbillede:

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

3. Vis de positive værdier for variansen, skriv venligst nedenstående formel i celle F2, og kopier derefter formlen til andre celler, du har brug for, se skærmbillede:

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

4. Og derefter skal du indsætte et specifikt symbol, der vil blive brugt som diagramstænger. Klik venligst indsatte > Symbol, og i Symbol dialogboksen, vælg Bloker elementer som du kan finde i næsten alle skrifttyper fra Subset rullelisten, og klik på Fuld blokering og klik derefter på indsatte for at indsætte symbolet, se skærmbillede:

5. Når du har indsat det specielle symbol, skal du anvende nedenstående formel i celle G2 og trække denne formel til andre celler, så får du nedenstående resultat:

=IF(E2="","",E2&" "&REPT($A$16,ABS(E2)/100))
Bemærk: I ovenstående formel E2 er cellen indeholder den negative variansværdi, A16 er cellen indeholder det specifikke symbol, du er indsat, og nummeret 100 er en variabel data, der bruges til at beregne nummeret på symbolet baseret på variansværdien, kan du ændre det til dit eget.

6. Fortsæt med at anvende nedenstående formel i celle H2, og træk derefter denne formel til andre celler, og du får nedenstående resultat:

=IF(F2="","",REPT($A$16,F2/100)&" "&F2)
Bemærk: I ovenstående formel F2 er cellen indeholder den positive variansværdi, A16 er cellen indeholder det specifikke symbol, du er indsat, og nummeret 100 er en variabel data, der bruges til at beregne nummeret på symbolet baseret på variansværdien, kan du ændre det til dit eget.

7. Vælg derefter cellerne i kolonne G, og klik derefter på Hjem > Juster Right for at justere dataene lige på cellerne, se skærmbillede:

8. Endelig kan du formatere skrifttypefarverne for de to kolonner hver for sig, og du får resultatet som vist nedenstående skærmbillede:


Opret budget vs faktisk variansdiagram med en fantastisk funktion

Kutools til Excel indeholder snesevis af specielle typer diagrammer, som Excel ikke har, f.eks Punktdiagram, Mål og faktisk diagram, Forskel pil diagram og så videre. Med sit praktiske værktøj- Forskel Sammenligningsdiagram, kan du oprette et budget vs faktisk variansdiagram i regneark eller mini-budget vs faktisk variansdiagram i celler hurtigt og nemt. Klik for at downloade Kutools til Excel til gratis prøveperiode!


Download eksempel på budget vs faktisk variansdiagram


Video: Opret budget versus faktisk variansdiagram i Excel


De bedste Office-produktivitetsværktøjer

Kutools til Excel - Hjælper dig med at skille dig ud fra mængden

Vil du gerne fuldføre dit daglige arbejde hurtigt og perfekt? Kutools til Excel bringer 300 kraftfulde avancerede funktioner (Kombiner projektmapper, sum efter farve, del celleindhold, konverter dato osv.) Og spar 80% tid til dig.

  • Designet til 1500 arbejdsscenarier, hjælper dig med at løse 80% Excel-problemer.
  • Reducer tusindvis af tastatur- og museklik hver dag, lindre dine trætte øjne og hænder.
  • Bliv en Excel-ekspert på 3 minutter. Behøver ikke længere huske smertefulde formler og VBA-koder.
  • 30-dages ubegrænset gratis prøveperiode. 60-dages pengene tilbage garanti. Gratis opgradering og support i 2 år.
Ribbon of Excel (med Kutools til Excel installeret)

Fanen Office - Aktiver læsning og redigering af faner i Microsoft Office (inkluderer Excel)

  • Et sekund til at skifte mellem snesevis af åbne dokumenter!
  • Reducer hundreder af museklik for dig hver dag, og farvel med musens hånd.
  • Øger din produktivitet med 50%, når du ser og redigerer flere dokumenter.
  • Bringer effektive faner til Office (inkluderer Excel), Ligesom Chrome, Firefox og ny Internet Explorer.
Skærmbillede af Excel (med Office-fanen installeret)