Przejdź do głównej zawartości

Utwórz wykres budżet vs rzeczywista wariancja w programie Excel

W programie Excel wykres Budżet a rzeczywista wariancja służy do porównywania dwóch zestawów danych serii i wyświetlania różnicy lub wariancji dwóch serii danych. Jeśli różnice są wartościami ujemnymi, wyświetlany jest jeden kolorowy słupek lub kolumny, jeśli wartości dodatnie, inne kolorowe paski lub kolumny są wyświetlane, jak pokazano poniżej. W tym artykule omówię, jak utworzyć wykres wariancji porównawczej i mini wykres wariancji porównania komórek w skoroszycie programu Excel.


Utwórz wykres wariancji budżetowej i rzeczywistej w programie Excel

Aby utworzyć wykres zależności budżetu od rzeczywistej wariancji, wykonaj następujące czynności:

1. Wprowadź poniższą formułę do komórki D2, aby obliczyć różnicę między budżetem a rzeczywistymi wartościami, a następnie przeciągnij uchwyt wypełniania w dół do potrzebnych komórek, patrz zrzut ekranu:

=C2-B2

2. Następnie wybierz dane w kolumnie A i kolumnie D, a następnie kliknij wstawka > Wstaw wykres kolumnowy lub słupkowy > Kolumna grupowanazobacz zrzut ekranu:

3. Po wstawieniu wykresu kolumnowego możesz usunąć niechciane elementy wykresu według potrzeb, takie jak usuń tytuł wykresu, oś pionową, linie siatki, a otrzymasz wynik, jak pokazano poniżej zrzuty ekranu:

4. Następnie kliknij prawym przyciskiem oś poziomą i wybierz Formatuj oś opcja z menu kontekstowego, zobacz zrzut ekranu:

5. W otwartym Formatuj oś okienko, pod Opcje osi tab, kliknij Etykiety aby rozwinąć tę sekcję, a następnie wybierz niski z Pozycja etykiety rozwijana lista, a oś pozioma zostanie umieszczona na dole wykresu, patrz zrzut ekranu:

6. Następnie kliknij prawym przyciskiem dowolny pasek i wybierz Formatuj serie danych opcja, patrz zrzut ekranu:

7. W otwartym Formatuj serie danych okienko, pod Wypełnij i wyrównaj karta, z Wypełniać sekcji, wykonaj następujące operacje:

  • Wybierz Pełne wypełnienie opcja;
  • Kontrola Odwróć, jeśli jest ujemny pole wyboru;
  • Określ dwa kolory dla wartości dodatnich i ujemnych oddzielnie od Kolor upuścić.

8. Po ustawieniu kolorów słupków należy dodać etykiety danych, kliknąć, aby zaznaczyć słupki na wykresie, a następnie kliknąć Elementy wykresu ikonę, a następnie sprawdź Etykiety danych z Elementy wykresu pole listy, zobacz zrzut ekranu:

9. Słupki kolumn są tak wąskie, że nie wyglądają pięknie, w takim przypadku możesz dostosować szerokość paska do swoich potrzeb, kliknij prawym przyciskiem myszy dowolny pasek, a następnie wybierz Formatuj serie danych, W Formatuj serie danych okienko, pod Opcje serii zmień Szerokość szczeliny do twoich potrzeb, w tym przypadku zmienię to na 80%zobacz zrzut ekranu:

Tips:

Czasami po utworzeniu wykresu możesz chcieć pokazać etykiety danych w różnych kolorach, na przykład etykiety danych dodatnich w kolorze zielonym i etykiety danych ujemnych w kolorze pomarańczowym, wykonaj następujące czynności:

Wybierz dane w kolumnie Odchylenie z oryginalnej tabeli danych, a następnie naciśnij Ctrl + 1 otworzyć Formatowanie komórek okno dialogowe w Formatowanie komórek okno dialogowe, kliknij Zamówienia Indywidualne z Kategoria pole listy, a następnie wpisz poniższy kod w Rodzaj Nieruchomości pole tekstowe:

[Color10]General;[Color53] -General

Następnie kliknij OK przycisk, a otrzymasz wynik, jakiego potrzebujesz, patrz zrzut ekranu:

Note: Jeśli potrzebujesz innych kolorów, zapoznaj się z poniższą tabelą indeksu kolorów:


Utwórz wykres mini-budżet a rzeczywiste odchylenie w komórkach

Jeśli chcesz wstawić mini budżet w porównaniu z rzeczywistym wykresem wariancji w komórkach, następujące kroki mogą wyświadczyć ci przysługę:

1. Wprowadź poniższą formułę do komórki D2, aby obliczyć różnicę między budżetem a rzeczywistymi wartościami, a następnie przeciągnij uchwyt wypełniania w dół do potrzebnych komórek, patrz zrzut ekranu:

=C2-B2

2. Następnie wyświetl ujemne wartości wariancji, zastosuj następującą formułę do komórki E2, a następnie skopiuj formułę do innych potrzebnych komórek, patrz zrzut ekranu:

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

3. Wyświetl dodatnie wartości wariancji, wpisz poniższą formułę w komórce F2, a następnie skopiuj formułę do innych potrzebnych komórek, zobacz zrzut ekranu:

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

4. Następnie należy wstawić określony symbol, który będzie używany jako słupki wykresu, kliknij wstawka > symbol, oraz w symbol okno dialogowe, wybierz Blokuj elementy które można znaleźć w prawie każdej czcionce z Podzbiór rozwijaną listę i kliknij Pełny blok symbol, a następnie kliknij wstawka aby wstawić symbol, zobacz zrzut ekranu:

5. Po wstawieniu specjalnego symbolu zastosuj poniższą formułę do komórki G2 i przeciągnij tę formułę do innych komórek, a otrzymasz poniższy wynik:

=IF(E2="","",E2&" "&REPT($A$16,ABS(E2)/100))
Note: W powyższym wzorze, E2 czy komórka zawiera ujemną wartość wariancji, A16 to komórka zawiera określony symbol, który został wstawiony, oraz liczbę 100 to zmienne dane, które są używane do obliczenia numeru symbolu na podstawie wartości wariancji, możesz ją zmienić na własną.

6. Kontynuuj stosowanie poniższej formuły do ​​komórki H2, a następnie przeciągnij tę formułę do innych komórek, a otrzymasz poniższy wynik:

=IF(F2="","",REPT($A$16,F2/100)&" "&F2)
Note: W powyższym wzorze, F2 czy komórka zawiera dodatnią wartość wariancji, A16 to komórka zawiera określony symbol, który został wstawiony, oraz liczbę 100 to zmienne dane, które są używane do obliczenia numeru symbolu na podstawie wartości wariancji, możesz ją zmienić na własną.

7. Następnie zaznacz komórki w kolumnie G, a następnie kliknij Strona główna > align Right aby wyrównać dane bezpośrednio na komórkach, zobacz zrzut ekranu:

8. W końcu możesz sformatować kolory czcionek dla dwóch kolumn osobno, jak potrzebujesz, a otrzymasz wynik jak na poniższym zrzucie ekranu:


Utwórz wykres wariancji budżetowej i rzeczywistej z niesamowitą funkcją

Kutools dla programu Excel udostępnia dziesiątki specjalnych typów wykresów, których nie ma w programie Excel, takich jak Wykres punktowy, Wykres docelowy i rzeczywisty, Wykres strzałek różnic i tak dalej. Dzięki poręcznemu narzędziu Wykres porównawczy różnic, możesz szybko i łatwo utworzyć wykres wariancji budżet vs rzeczywisty w arkuszu lub wykres minibudżet vs faktyczny odchylenie w komórkach. Kliknij, aby pobrać Kutools for Excel za bezpłatną wersję próbną!


Pobierz przykładowy plik wykresu wariancji budżet vs rzeczywista


Wideo: Tworzenie wykresu wariancji budżetowej i rzeczywistej w programie Excel


Najlepsze narzędzia biurowe

Kutools dla programu Excel - pomaga wyróżnić się z tłumu

🤖 Pomocnik AI Kutools: Zrewolucjonizuj analizę danych w oparciu o: Inteligentne wykonanie   |  Wygeneruj kod  |  Twórz niestandardowe formuły  |  Analizuj dane i generuj wykresy  |  Wywołaj funkcje Kutools...
Popularne funkcje: Znajdź, wyróżnij lub zidentyfikuj duplikaty  |  Usuń puste wiersze  |  Łącz kolumny lub komórki bez utraty danych  |  Okrągły bez wzoru ...
Super VLookup: Wiele kryteriów  |  Wiele wartości  |  W wielu arkuszach  |  Wyszukiwanie rozmyte...
Adw. Lista rozwijana: Łatwa lista rozwijana  |  Zależna lista rozwijana  |  Lista rozwijana wielokrotnego wyboru...
Menedżer kolumn: Dodaj określoną liczbę kolumn  |  Przesuń kolumny  |  Przełącz stan widoczności ukrytych kolumn  Porównaj kolumny z Wybierz Te same i różne komórki ...
Polecane funkcje: Fokus siatki  |  Widok projektu  |  Duży pasek formuły  |  Menedżer skoroszytów i arkuszy | Biblioteka zasobów (Automatyczny tekst)  |  Selektor dat  |  Połącz arkusze  |  Szyfruj/odszyfruj komórki  |  Wysyłaj e-maile według listy  |  Super filtr  |  Specjalny filtr (filtruj pogrubienie/kursywa/przekreślenie...) ...
15 najlepszych zestawów narzędzi12 Tekst Tools (Dodaj tekst, Usuń znaki ...)  |  50 + Wykres rodzaje (Wykres Gantta ...)  |  40+ Praktyczne Wzory (Oblicz wiek na podstawie urodzin ...)  |  19 Wprowadzenie Tools (Wstaw kod QR, Wstaw obraz ze ścieżki ...)  |  12 Konwersja Tools (Liczby na słowa, Przeliczanie walut ...)  |  7 Połącz i podziel Tools (Zaawansowane wiersze łączenia, Podziel komórki Excela ...)  |  ... i więcej

Kutools dla programu Excel oferuje ponad 300 funkcji, Pewność, że to, czego potrzebujesz, jest w zasięgu jednego kliknięcia...

Opis


Karta Office - Włącz czytanie i edycję na kartach w Microsoft Office (w tym Excel)

  • Jedna sekunda, aby przełączać się między dziesiątkami otwartych dokumentów!
  • Zmniejsz liczbę kliknięć myszą każdego dnia, pożegnaj się z dłonią myszy.
  • Zwiększa produktywność o 50% podczas przeglądania i edytowania wielu dokumentów.
  • Wprowadza wydajne karty do pakietu Office (w tym programu Excel), podobnie jak przeglądarki Chrome, Edge i Firefox.
Comments (1)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
bonjour,
Comment faire si je souhaite ajouter à chaque barre un petit trait pour montrer le dépassement ou non du budget ?
merci
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations