Note: The other languages of the website are Google-translated. Back to English
Zaloguj Się  \/ 
x
or
x
Rejestruję się  \/ 
x

or

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;
  • Czek 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:

Wskazówki:

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 zwyczaj z Wszystkie produkty pole listy, a następnie wpisz poniższy kod w Rodzaj pole tekstowe:

[Color10]General;[Color53] -General

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

Uwagi: 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))
Uwagi: 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)
Uwagi: 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

Chcesz szybko i perfekcyjnie zakończyć codzienną pracę? Kutools dla programu Excel oferuje zaawansowane zaawansowane funkcje 300 (łącz skoroszyty, sumuj według koloru, dziel zawartość komórek, konwertuj datę itd.) i oszczędzaj 80% czasu.

  • Zaprojektowany dla 1500 scenariuszy pracy, pomaga rozwiązać problemy 80% Excel.
  • Zmniejsz tysiące kliknięć klawiatury i myszy każdego dnia, odciąż zmęczone oczy i dłonie.
  • Zostań ekspertem Excela w 3 minuty. Nie musisz już pamiętać żadnych bolesnych formuł i kodów VBA.
  • 30-dniowy nieograniczony bezpłatny okres próbny. 60-dniowa gwarancja zwrotu pieniędzy. Bezpłatna aktualizacja i wsparcie przez 2 lata.
Wstążka programu Excel (z zainstalowanym Kutools dla programu Excel)

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 wydajność o 50% podczas przeglądania i edycji wielu dokumentów.
  • Wprowadza wydajne zakładki do pakietu Office (w tym Excel), podobnie jak Chrome, Firefox i nowy Internet Explorer.
Zrzut ekranu programu Excel (z zainstalowaną kartą Office)
Say something here...
symbols left.
You are guest
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    matteodu69 · 1 months ago
    bonjour,

    Comment faire si je souhaite ajouter à chaque barre un petit trait pour montrer le dépassement ou non du budget ?

    merci