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

Skapa budget jämfört med faktiskt avvikelsediagram i Excel

I Excel används ett diagram jämfört med faktiskt variansdiagram för att jämföra två uppsättningar seriedata och visa skillnaden eller variansen för de två dataserierna. Om skillnaderna är negativa värden visas en färgad stapel eller kolumn, om positiva värden visas en annan färgad stapel eller kolumn enligt nedanstående skärmdump. Den här artikeln kommer jag att prata om hur man skapar jämförelsevariansdiagram och ett mini i celljämförelsesvariansdiagram i Excel-arbetsbok.


Skapa budget kontra faktiskt variansdiagram i Excel

För att skapa budget kontra faktiskt variansdiagram gör du med följande steg:

1. Ange nedanstående formel i cell D2 för att beräkna skillnaden mellan budgeten och de faktiska värdena och dra sedan påfyllningshandtaget ner till cellerna du behöver, se skärmdump:

=C2-B2

2. Markera sedan data i kolumn A och kolumn D och klicka sedan på Insert > Infoga kolumn eller stapeldiagram > Klusterad kolumn, se skärmdump:

3. Och ett kolumndiagram infogas, så kan du ta bort de oönskade elementen i diagrammet som du behöver, till exempel ta bort diagramtitel, vertikal axel, rutnät, och du får resultatet enligt nedanstående skärmdumpar:

4. Högerklicka sedan på den horisontella axeln och välj Formatera axel alternativ från snabbmenyn, se skärmdump:

5. I det öppnade Formatera axel under rutan Axialternativ fliken, klicka Etiketter för att utöka detta avsnitt och välj sedan Låg från Etikettposition rullgardinsmeny, och den horisontella axeln placeras längst ner i diagrammet, se skärmdump:

6. Högerklicka sedan på någon av staplarna och välj Formatera dataserier alternativ, se skärmdump:

7. I det öppnade Formatera dataserier under rutan Fyll & rad fliken, från Fyll avsnittet, gör följande:

  • Välja Fast fyllning alternativ;
  • Kolla upp Invertera om det är negativt kryssruta;
  • Ange två färger för positiva och negativa värden separat från Färg falla ner.

8. När du har ställt in färgerna för staplarna ska du lägga till dataetiketterna. Klicka för att välja staplarna i diagrammet och klicka sedan på Diagramelement ikonen och kontrollera sedan Datataggar från Diagramelement listruta, se skärmdump:

9. Kolonnstängerna är så smala att de inte ser vackra ut, i det här fallet kan du justera stapelns bredd efter ditt behov, högerklicka på någon av staplarna och välj sedan Formatera dataserierI Formatera dataserier under rutan Seriealternativ ändra fliken Gap Bredd efter ditt behov, i det här fallet kommer jag att ändra det till 80%, se skärmdump:

tips:

När du har skapat diagrammet, kanske du vill visa dataetiketterna med olika färger, till exempel de positiva datatiketterna i den gröna färgen och de negativa datatiketterna i orange färg, gör så här:

Välj data i kolumnen Varians från den ursprungliga datatabellen och tryck sedan på Ctrl + 1 att öppna Formatera celler dialogrutan, i Formatera celler dialogrutan, klicka Motorcykel från Kategori och skriv sedan in koden nedan i Typ textruta:

[Color10]General;[Color53] -General

Klicka sedan på OK knappen, och du får resultatet efter behov, se skärmdump:

Anmärkningar: Om du behöver andra färger, se följande färgindextabell:


Skapa minibudget mot faktiskt variansdiagram i celler

Om du vill infoga en minibudget mot det faktiska variansdiagrammet i celler kan följande steg göra dig en tjänst:

1. Ange nedanstående formel i cell D2 för att beräkna skillnaden mellan budgeten och de faktiska värdena och dra sedan påfyllningshandtaget ner till cellerna du behöver, se skärmdump:

=C2-B2

2. Visa sedan varians negativa värden, använd följande formel i cell E2 och kopiera sedan formeln till andra celler du behöver, se skärmdump:

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

3. Visa de positiva värdena för variansen, skriv nedanstående formel i cell F2 och kopiera sedan formeln till andra celler du behöver, se skärmdump:

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

4. Och sedan ska du infoga en specifik symbol som kommer att användas som diagramfält, klicka Insert > Symbol, och i Symbol dialogrutan väljer du Blockelement som du kan hitta i nästan alla teckensnitt från Delmängd rullgardinsmenyn och klicka på Fullt block och klicka sedan på Insert för att infoga symbolen, se skärmdump:

5. När du har infogat specialsymbolen, använd sedan formeln nedan i cell G2 och dra denna formel till andra celler, så får du resultatet nedan:

=IF(E2="","",E2&" "&REPT($A$16,ABS(E2)/100))
Anmärkningar: I ovanstående formel, E2 är cellen innehåller det negativa variansvärdet, A16 är cellen innehåller den specifika symbolen du har infogat och numret 100 är en variabel data som används för att beräkna symbolens nummer baserat på variansvärdet, kan du ändra det till ditt eget.

6. Fortsätt att använda nedanstående formel i cell H2 och dra sedan denna formel till andra celler, så får du resultatet nedan:

=IF(F2="","",REPT($A$16,F2/100)&" "&F2)
Anmärkningar: I ovanstående formel, F2 är cellen innehåller det positiva variansvärdet, A16 är cellen innehåller den specifika symbolen du har infogat och numret 100 är en variabel data som används för att beräkna symbolens nummer baserat på variansvärdet, kan du ändra det till ditt eget.

7. Välj sedan cellerna i kolumn G och klicka sedan på Hem > Justera höger för att justera data direkt på cellerna, se skärmdump:

8. Äntligen kan du formatera teckensnittsfärgerna för de två kolumnerna separat efter behov och du får resultatet enligt nedanstående skärmdump:


Skapa budget mot faktiskt variansdiagram med en fantastisk funktion

Kutools för Excel ger dussintals speciella typer av diagram som Excel inte har, till exempel Bullet Chart, Mål och faktiskt diagram, Skillnad pilschema och så vidare. Med sitt praktiska verktyg- Skillnad Jämförelsediagram, kan du snabbt och enkelt skapa ett budget kontra faktiskt variansdiagram i kalkylblad eller minibudget mot faktiskt variansdiagram. Klicka för att ladda ner Kutools för Excel för en gratis provperiod!


Ladda ner exempel på budget vs faktiskt variansdiagram


Video: Skapa budget kontra faktiskt variansdiagram i Excel


De bästa Office-produktivitetsverktygen

Kutools för Excel - Hjälper dig att sticka ut från mängden

Vill du slutföra ditt dagliga arbete snabbt och perfekt? Kutools för Excel ger 300 kraftfulla avancerade funktioner (kombinera arbetsböcker, summera efter färg, dela cellinnehåll, konvertera datum och så vidare ...) och spara 80% tid åt dig.

  • Designad för 1500 arbetsscenarier, hjälper dig att lösa 80% Excel-problem.
  • Minska tusentals tangentbord och musklick varje dag, lindra dina trötta ögon och händer.
  • Bli en expert på 3 minuter. Behöver inte längre komma ihåg några smärtsamma formler och VBA-koder.
  • 30 dagars obegränsad gratis provperiod. 60-dagars pengarna tillbaka-garanti. Gratis uppgradering och support i 2 år.
Ribbon of Excel (med Kutools för Excel installerat)

Fliken Office - Aktivera läsning och redigering av flikar i Microsoft Office (inkluderar Excel)

  • En sekund att växla mellan dussintals öppna dokument!
  • Minska hundratals musklick åt dig varje dag, säg adjö till mushanden.
  • Ökar din produktivitet med 50% när du tittar på och redigerar flera dokument.
  • Ger effektiva flikar till Office (inkluderar Excel), precis som Chrome, Firefox och ny Internet Explorer.
Skärmdump av Excel (med Office-fliken installerad)
Sortera kommentarer efter
Kommentarer (1)
Inga betyg än. Bli först med att betygsätta!
Denna kommentar minimerades av moderatoren på webbplatsen
Hej,
Comment faire si je souhaite ajouter à chaque barre un petit trait pour montrer le dépassement ou non du budget ?
tack
Det finns inga kommentarer här ännu
Lämna dina kommentarer
Postar som gäst
×
Betygsätt detta inlägg:
0   Tecken
Föreslagna platser