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

Vytvořte graf rozpočtu vs. skutečné odchylky v aplikaci Excel

V aplikaci Excel se graf Budget vs. Skutečná odchylka používá k porovnání dvou sad dat řady a zobrazení rozdílu nebo rozptylu dvou datových řad. Pokud jsou rozdíly záporné hodnoty, zobrazí se jeden barevný pruh nebo sloupec, v případě kladných hodnot další barevný pruh nebo sloupec, jak je znázorněno níže. V tomto článku budu hovořit o tom, jak vytvořit graf odchylek porovnání a graf odchylek mini v porovnání buněk v sešitu aplikace Excel.


Vytvořte graf rozpočtu vs. skutečný rozptyl v aplikaci Excel

Chcete-li vytvořit graf rozptylu rozpočtu a skutečných odchylek, proveďte následující kroky:

1. Zadejte následující vzorec do buňky D2 pro výpočet rozdílu mezi rozpočtem a skutečnými hodnotami a poté přetáhněte popisovač výplně dolů do buněk, které potřebujete, viz screenshot:

=C2-B2

2. Poté vyberte data ve sloupci A a sloupci D a poté klikněte Vložit > Vložte sloupcový nebo sloupcový graf > Seskupený sloup, viz screenshot:

3. A je vložen sloupcový graf, můžete podle potřeby odstranit nechtěné prvky grafu, například odstranit nadpis grafu, svislou osu, mřížku a výsledek získáte, jak je uvedeno níže:

4. Poté klikněte pravým tlačítkem na vodorovnou osu a vyberte Formát osy možnost z kontextového menu, viz screenshot:

5. V otevřeném Formát osy podokno pod Možnosti osy klepněte na kartu Štítky rozbalte tuto část a poté vyberte Nízký z Pozice štítku rozevírací seznam a vodorovná osa bude umístěna ve spodní části grafu, viz screenshot:

6. Poté klepněte pravým tlačítkem na některý z pruhů a vyberte Formátovat datové řady možnost, viz screenshot:

7. V otevřeném Formátovat datové řady podokno pod Vyplnit a řádek na kartě z Vyplnit v části proveďte následující operace:

  • vybrat Plná výplň volba;
  • Check Invertovat, pokud je negativní zaškrtávací políčko;
  • Zadejte dvě barvy pro kladné a záporné hodnoty odděleně od Barva drop down.

8. Po nastavení barev pruhů byste měli přidat popisky dat. Klepnutím vyberte pruhy v grafu a poté klepněte na Prvky grafu ikonu, pak zkontrolujte Datové štítky z Prvky grafu seznam, viz screenshot:

9. Sloupové pruhy jsou tak úzké, že nevypadají krásně, v tomto případě můžete upravit šířku pruhu podle svých potřeb, klikněte pravým tlačítkem na kterýkoli z pruhů a poté vyberte Formátovat datové řady, V roce Formátovat datové řady podokno pod Možnosti řady kartu, změňte Šířka mezery na vaši potřebu, v tomto případě to změním na 80%, viz screenshot:

Tipy:

Po vytvoření grafu můžete někdy chtít zobrazit datové štítky s různými barvami, například pozitivní datové štítky v zelené barvě a negativní datové štítky v oranžové barvě, postupujte takto:

Vyberte data ve sloupci Odchylka z původní tabulky dat a stiskněte Ctrl + 1 k otevření Formát buněk v dialogovém okně v Formát buněk , klepněte na tlačítko OK Zvyk z Kategorie seznamu a potom zadejte následující kód do typ Textové pole:

[Color10]General;[Color53] -General

Pak klikněte na tlačítko OK tlačítko a výsledek získáte podle potřeby, viz screenshot:

Poznámka: Pokud potřebujete další barvy, přečtěte si následující tabulku indexů barev:


Vytvořte v buňkách graf minimálního rozpočtu vs. skutečný rozptyl

Pokud chcete do buněk vložit miniaturní graf vs. graf skutečné odchylky, mohou vám udělat následující kroky:

1. Zadejte následující vzorec do buňky D2 pro výpočet rozdílu mezi rozpočtem a skutečnými hodnotami a poté přetáhněte popisovač výplně dolů do buněk, které potřebujete, viz screenshot:

=C2-B2

2. Poté zobrazte záporné hodnoty odchylky, použijte následující vzorec do buňky E2 a poté zkopírujte vzorec do dalších buněk, které potřebujete, viz screenshot:

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

3. Zobrazte kladné hodnoty odchylky, zadejte následující vzorec do buňky F2 a poté zkopírujte vzorec do dalších buněk, které potřebujete, viz screenshot:

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

4. A pak byste měli vložit konkrétní symbol, který bude použit jako pruhy grafu, klikněte prosím Vložit > Symbol, a v Symbol dialogové okno vyberte Blokové prvky že najdete téměř v každém písmu z Podmnožina rozevírací seznam a klikněte na ikonu Plný blok symbol, poté klikněte na Vložit pro vložení symbolu viz screenshot:

5. Po vložení speciálního symbolu použijte následující vzorec do buňky G2 a přetáhněte tento vzorec do dalších buněk a získáte níže uvedený výsledek:

=IF(E2="","",E2&" "&REPT($A$16,ABS(E2)/100))
Poznámka: Ve výše uvedeném vzorci, E2 je buňka obsahuje zápornou hodnotu rozptylu, A16 je buňka obsahuje konkrétní symbol, který jste vložili, a číslo 100 jsou data proměnné, která se používají k výpočtu počtu symbolů na základě hodnoty rozptylu, můžete je změnit na vlastní.

6. Pokračujte v aplikaci níže uvedeného vzorce do buňky H2 a poté přetáhněte tento vzorec do jiných buněk a získáte níže uvedený výsledek:

=IF(F2="","",REPT($A$16,F2/100)&" "&F2)
Poznámka: Ve výše uvedeném vzorci, F2 je buňka obsahuje hodnotu kladné odchylky, A16 je buňka obsahuje konkrétní symbol, který jste vložili, a číslo 100 jsou data proměnné, která se používají k výpočtu počtu symbolů na základě hodnoty rozptylu, můžete je změnit na vlastní.

7. Potom vyberte buňky ve sloupci G a poté klikněte Home > Zarovnejte správně zarovnat data přímo na buňky, viz screenshot:

8. Nakonec můžete barvy písma pro dva sloupce naformátovat samostatně, jak potřebujete, a výsledek získáte, jak je ukázáno níže:


Vytvořte graf rozptylu rozpočtu a skutečného rozptylu s úžasnou funkcí

Kutools pro Excel poskytuje desítky speciálních typů grafů, které Excel nemá, například Odrážkový graf, Cílový a skutečný graf, Graf šipky rozdílu a tak dále. Se svým praktickým nástrojem Graf porovnání rozdílů, můžete rychle a snadno vytvořit tabulku rozpočtu proti skutečné odchylce v listu nebo mini rozpočet proti skutečné odchylce v buňkách. Klikněte a stáhněte si zdarma Kutools pro Excel!


Stáhněte si ukázkový soubor Budget vs Actual Variance Chart


Video: Vytvořte graf rozpočtu vs. skutečný rozptyl v aplikaci Excel


Nejlepší kancelářské nástroje produktivity

Kutools pro Excel - pomůže vám vyniknout před davem

Chcete dokončit svou každodenní práci rychle a dokonale? Kutools pro Excel přináší 300 výkonných pokročilých funkcí (kombinovat sešity, součet podle barvy, rozdělit obsah buněk, převést datum atd.) A ušetřit vám 80% času.

  • Navrženo pro 1500 80 pracovních scénářů, pomůže vám vyřešit XNUMX% problémů s Excelem.
  • Omezte každý den tisíce kliknutí na klávesnici a myš a ulehčete svým unaveným očím a rukám.
  • Staňte se odborníkem na Excel za 3 minuty. Už si nemusíte pamatovat žádné bolestivé vzorce a kódy VBA.
  • 30denní neomezená bezplatná zkušební verze. 60denní záruka vrácení peněz. Bezplatný upgrade a podpora po dobu 2 let.
Pás karet aplikace Excel (s nainstalovaným programem Kutools pro Excel)

Záložka Office - Povolte čtení a úpravy na záložkách v Microsoft Office (včetně Excelu)

  • Jednu sekundu přepnete mezi desítkami otevřených dokumentů!
  • Snižte stovky kliknutí myší každý den, sbohem s myší rukou.
  • Zvyšuje vaši produktivitu o 50% při prohlížení a úpravách více dokumentů.
  • Přináší efektivní karty do Office (včetně Excelu), stejně jako Chrome, Firefox a Nový Internet Explorer.
Snímek obrazovky aplikace Excel (s nainstalovanou kartou Office)
Komentáře (1)
Zatím žádné hodnocení. Buďte první, kdo ohodnotí!
Tento komentář byl moderátorem webu minimalizován
Bonjour,
Komentář faire si je souhaite ajouter à chaque barre un petit trait pour montrer le dépassement ou non du budget?
merci
Zatím zde nejsou žádné komentáře
Zanechat své připomínky
Odesílání jako host
×
Ohodnoťte tento příspěvek:
0   Postavy
Doporučená umístění