Manipulacje w wykresach – iluzja w erze informacji
23 października 2011
Wykresy w komórkach Excela – profesjonalny wygląd tabel
12 listopada 2011
W projektach szkoleniowych często operujemy harmonogramem zajęć lub szkoleń. Harmonogram można przekazać jako prosty tekst lub utworzyć ładny i czytelny wykres Gantta – ten sam, który używa się w zarządzaniu projektami. Co ciekawe – można to zrobić w Excel.
Najprościej to zrobić tabelkę z kolejnymi dniami i ręcznie kolorować poszczególne kratki, żeby otrzymać zamierzony efekt. Ale przy zmianach w harmonogramie trzeba by znowu ręcznie… Przecież Excel jest po to, żeby nie trzeba było ręcznie!
Przygotowałem dla was arkusz kalkulacyjny, który pozwala utworzyć wykres Gantta wpisując po prostu nazwy szkoleń, daty początku i końca i określić datę od jakiej ma być pokazywany wykres.
Wykres jest w wersji pokazującej 31 dni lub 14 dni. Musiałem się zdecydować na jakieś okresy, żeby sformatować wydruk.
Jak używać?
Otwórz arkusz w MS Excel i zrób co następuje (w dowolnej kolejności):
- wpisz tytuł wykresu w niebieskim polu („Tytuł”)
- wpisz datę, od której ma się pokazać wykres w niebieskim polu („Początek wykresu”)
- wpisz nazwy szkoleń oraz daty początku i końca w lewej części wykresu
I tyle – jak uruchomisz drukowanie, to otrzymasz wykres na drukarce. Drukuje się tylko jedna strona – bez niebieskich części arkusza.
Daty trzeba wpisać w takim formacie, żeby Excel zinterpretował to jako datę – w polskich wersjach Excela powinno zadziałać tak jak w przykładzie.
Jak chcesz wykres na inny okres czasu, to po prostu zmień datę „początek wykresu”.
Jak to działa?
Ten fragment nie jest obowiązkowy. Ale może niektórzy potraktują to jako materiał poglądowy do nauki Excela.
Generalnie wykorzystujemy dwie rzeczy – operacje na datach i warunkowe kolorowanie komórek w zależności od zawartości. Każda komóreczka w obszarze pokazującym paski Gantta zawiera formułę, która sprawdza czy data reprezentowana przez tę komórkę (jest zapisana na górze kolumny) mieści się pomiędzy datą początku i końca kursu (po lewej stronie) – jeśli tak, to przyjmuje wartość 1.
Formatowanie warunkowe nadane komórkom wykresu sprawdza czy jest wartość 1 w komóreczce i jeśli tak, to barwi na czerwono.
Jeśli uważnie czytasz, to widzisz, że efektem ubocznym tej zabawy jest fakt, że każda komórka zaczerwieniona ma wartość 1. Czyli możesz na przykład zsumować kolumnę wykresu Gantta, żeby zobaczyć ile jednocześnie jest kursów w danym dniu.
Kilka uwag
Możesz ukryć część wierszy, jeśli chcesz na wykresie mniej kursów. Możesz też zmieniać wysokoć wierszy, ewentualnie skopiować ostatni wiersz jeśli ci brakuje szkoleń.
Dla każdego projektu możesz zapisać projekt jako osobny arkusz Excela – wtedy w razie zmian w harmonogramie unikniesz ponownego wpisywania danych.
Można oczywiście używać tego arkusza do wpisywania zadań (zarządzanie projektami). Dla osób nie mających żadnego oprogramowania wspierającego zarządzanie projektami typu MS Project to może być dobre wyjście.
Dozwolony użytek
Możesz używać do projektów komercyjnych i niekomercyjnych. Nie wolno ci sprzedawać tego arkusza. Możesz się nim dzielić na zasadach uznania autorstwa – to znaczy nie udawaj, że to wymyśliłeś.
Będę wdzięczny jeśli przy okazji zapromujesz mojego bloga.
https://praktykatrenera.pl/wp-content/uploads/2011/11/Harmonogram_Gantt.xlsx
W odpowiedzi na komentarz Darka jeszcze dodatkowy plik, w którym działamy odwrotnie – wpisujemy jedynki w kratki, a daty się wyliczają same – przeczytaj komentarz.
Harmonogram_Gantt_reverse (https://praktykatrenera.pl/wp-content/uploads/2011/11/Harmonogram_Gantt_reverse.xlsx)
29 Comments
Witam,
Mam kilka pytań:
1. Używając Twojego szablonu chciałbym zakreślić okres wyświetlanego kalendarza
tzn jeżeli wprowadzę okres od 1.07.2012 do (+40dni) 10.08.2012 to chciałbym żeby taki okres kalendarza graficznego się wyświetlał – Jak to zrobić?
2. Czy zakreślając okres (wartość 1) na kalendarzu graficznym jest możliwość aby daty (rrrr-mm-dd) uzupełniały się wedle zakreślonego okresu na przemian.
tzn zakreślam okres w graficznym kalendarzu 1-3.07.2012 – przedział koloruje się na np czerwono a w kolumnach początek i koniec przypisują się daty rozpoczęcia i końca tego zadania ( 1.07.2012 / 3.07.2012)
Z góry dzięki za informacje
Nie wiem czy dobrze rozumiem pytania, ale postaram się pomóc. Jeśli potrzebujesz, żeby kalendarz zadziałał na 40 dni, to po prostu trzeba „rozciągnąć” ostatnią komórkę na następne 10.
W obszarze nagłówkowym – tam gdzie jest napisane od kiedy do kiedy jest harmonogram trzeba zmienić formułę, żeby dodawała do daty początkowej 40 dni a nie 30.
Natomiast jeśli chcesz odwrotnie działać – to znaczy narysować okres na wykresie i spowodować, żeby daty się odpowiednio ustawiły, to częściowo jest to możliwe.
Jednak w ten sposób, że będziesz musiał wpisać liczbę 1 w odpowiednie kratki (na tym polega ich kolorowanie). To niewielki wysiłek – wstawiasz jedną jedynkę i potem rozciągasz na odpowiednie kratki.
Oczywiście wtedy trzeba wpisać odpowiednie funkcje wyliczające odpowiednie daty. Są to funkcje PODAJ.POZYCJĘ i INDEKS – nie za łatwo to wyjaśnić na przestrzeni komentarza, więc na końcu postu umieszczam dodatkowy plik w Excelu, w którym w arkuszu reverse jest właśnie to zrobione. Funkcje nie są skomplikowane, choć pewnie niezbyt powszechnie znane.
Witam,
Harmonogram świetny. Mam tylko jedno pytanie. Jak zmienić kolor zakreślenia z czerwonego na inny wybrany?
Kolor zakreślenia jest uzyskiwany przez formatowanie warunkowe. Mam akurat włączonego Excela 2007, więc na jego przykładzie, ale zasada taka sama w Excel 2010.
Trzeba zaznaczyć te zakreślane komórki (czyli cały obszar z wąskimi kratkami). Dla tych komórek zdefiniowane jest formatowanie warunkowe, które chcemy zmienić.
Wybieramy więc Formatowanie warunkowe na wstędze i po rozwinięciu dalej „zarządzaj regułami”. Pokaże się okienko z listą reguł, których używamy – w naszym przypadku będzie to jedna reguła. Klikamy na nią i wybieramy przycisk „Edytuj regułę”. W kolejnym okienku jest przycisk formatuj, który pozwoli zmienić kolor.
Witam,
Wielkie dzięki za ten arkusz, bardzo pomocny. Pytanie mam następujące: Czy jest mozliwość zastosowania formuł które, będą wyświetlały tygodnie wg ich numeracji w kalendarzu? Mówiąc wprost, aby okres szkoleń nie był liczony w dniach tylko w tygodniach?
W Excelu jest dostępna funkcja WEEKNUM, która podaje numer tygodnia określonej daty. Wydaje się więc, że po przetworzeniu formuł, żeby porównywały numery tygodni, a nie numery dni powinno zadziałać również dla tygodni.
Witam,
trochę czasu już minęło od publikacji artykułu ale teraz się na niego natknąłem, mam pytanie odnośnie tego arkusza, a dokładnie np. tej formuły:
=JEŻELI(E$10>=$B12;JEŻELI(E$10<=$C12;1;0);0)
jak analogicznie stosuję ją w swoim arkuszu po wpisaniu dat w zdeklarowanych komórkach pokazują się same zera a nie jedynki nie mogę dojść dlaczego? jęsli można prosić o jakąś podpowiedź będę bardzo wdzięczny 🙂
Bardzo ważne jest, żeby dobrze ustawić komórkę B3, czyli datę początku wykresu. Czy może się Pan upewnić, że jest ustawiona na odpowiednią datę (czyli datę, od której się ma zacząć wykres)?
Witam. Jak zrobić ten wykres żeby był na cały rok?? A konkretnie od marca 2013 do marca 2014. Z góry dziękuję za odpowiedź 🙂
Kopiując ostatnią kolumnę tabelki wyświetlającej wykres Gantta uzyskamy przestrzeń na kolejne dni.
Jednak w takim przypadku ten wykres będzie bardzo szeroki.
Trzeba by również zadbać o wyświetlanie miesięcy (na przykład przy każdym pierwszym dniu miesiąca).
Czy o taki efekt ci chodzi?
To będzie z 8 stron wydruku – pasek na szerokość ze 2 metry.
Witam,
czy jest sposób, aby pomijać dni wolne np soboty i niedziele w taki sposób żeby dni z jednego szkolenia przechodziły na kolejne od nowego tygodnia? Np coś rozpoczyna się w piątek i trwa 3 dni, tak więc powinny się zaświetlić komórki piątku, poniedziałku i środy z pominięciem soboty i niedzieli.
Wydaje mi się, że klasyczny wykres Gantta ma pokazywać raczej jeden nieprzerwany pasek.
Żeby uzyskać efekt, o który pytasz, trzeba by dodać jeszcze jeden warunek w formule, która jest na komórkach arkusza – tych, które zmieniają kolor.
Musiałby to być warunek sprawdzający, czy wskazany dzień, to nie sobota i nie niedziela.
W tej chwili warunek sprawdza czy data reprezentowana przez komórkę (odczytuje ją z wiersza B i odpowiedniej kolumny) jest pomiędzy datami w komórkach zawartych w kolumnach B i C tego samego wiersza. W takim przypadku daje wartość 1.
Po modyfikacji musiałby sprawdzić jeszcze czy dzień reprezentowany przez komórkę to nie sobota lub niedziela.
Nie za bardzo rozumiem pytanie, bo w takim przypadku jak opisujesz szkolenie rozpoczęte w piątek wyświetliło by też się w poniedziałek i wtorek (dlaczego środa?).
Możesz uściślić lub precyzyjniej opisać?
Witam. Dziekuje za inspiracje do wykonania swojego wykresu Gantta 🙂 Niestety moj wykres bazuje na tygodniach w roku I problem pojawia sie, gdy zadanie rozpoczyna sie w tyg 52 a konczy w tyg 2 nastepnego roku. Funkcja if wyswietla wtedy 0 (wiadomo, warunkie nie sa spelnione aby wyswietlila 1). Zastanawiam sie jak zmodyfikowac funkcje if w excelu aby zadzialala poprawnie. Dodalam warunek, ze jezeli tygodnie sa z przedzialu 1-10 wtedy wyswietla 1, ale szczerze mowiac moze sie to w kazdej chwili nieauwazenie posypac. Ma Pan moze jakis pomysl? 🙂
Z gory Dziekuje I pozdrawiam,
Kasia
No tak, wykorzystywanie tylko numeru tygodnia powoduje taki efekt.
Myślę, że trzeba by rozbudować formułę o warunki dotyczące roku. To trochę zabawy logicznej, ale wydaje się do zrobienia.
Witam. Mam pytanie czy można doszczególowić datę o godzinę (rr-mm-dd gg:min), oraz czy jest takie narzędzie obliczające ilość godzin i minut pomiedzy datą początkową (rr-mm-dd gg:min) i datą końcową (rr-mm-dd gg:min).
Dziękuję i Pozdrawiam
Witam,
mam pytanie: czy można zakładkę z wykresem skopiować tak, aby w jednym pliku były w kolejnych zakładkach kolejne m-ce roku (od stycznia do grudnia)?
Pozdrawiam,
Anna
Witam
Czy można zmienić kolor pasków zajętości zadania ? Jeżeli tak to w jaki sposób ?
Pozdrawiam,
Kolory pasków są zdefiniowane w formatowaniu warunkowych przyjętym dla komórek wykresu. Trzeba zaznaczyć cały obszar na którym mają się wyświetlać paski i wejść w opcję „Formatowanie warunkowe”. Tam w „Zarządzaj regułami” znajdzie się zastosowana reguła, którą można przeredagować – na przykład zmieniając kolor.
Witam,
chciałam się zapytać o możliwość oznaczenia kolorami różnych zadań w zależności od tego jaki jest ich status tzn.
– jeśli mamy zadanie nadrzędne, które składa się z 10 pod zadań i 3 z nich są wykonane, 2 są w trakcie, a reszta do wykonania, chciałabym móc oznaczyć to 3 kolorami: wykonane – zielonym, w trakcie – żółtym, do zrobienia – czerwonym
Próbuję zmieniać kolory według wcześniejszych komentarzy w Formatowaniu warunkowym w regule, ale zmieniają mi się wszystkie komórki na jeden kolor.
Witam,
czy jest możliwość stworzenia zadania cyklicznie powtarzanego (czynności powtarzanej co tydzień przez okres 2 lat)?
pozdrawiam
W tym arkuszu Excela nie ma takiej możliwości. Myślę, że zbudowanie takiego Excela, w którym byłoby to możliwe jest dość złożone – szczególnie, jeśli by ta powtarzalność miała być zmienna (nie zawsze co tydzień).
Witam,
proszę o pomoc w zmianie koloru, a konkretnie chciałabym wybrać inne kolory dla różnych kursów/szkoleń, gdy zmieniam kolor wg Pana opisu to zmienia kolor wszystkich kursów.
Przede wszystkim trzeba zdefiniować nową regułę formatowania warunkowego – mam wrażenie, że Pani próbuje poprawiać tę która jest.
Trzeba zaznaczyć obszar z tym drugim szkoleniem i dodać regułę, że jak wartość jest większa niż 0,9 to tło w innym kolorze (i napis też, żeby nie było widać).
Po tej operacji powinny być dwie reguły, z tym, że każda dotyczy innego obszaru.
Mam nadzieję, że pomogłem.
Super, bardzo dziękuję za pomoc
Trafiłam na ten wykres w momencie jak mam stworzyć harmonogram na doradztwo/szkoleniowe. Dziękuję bardo, chyba warto jednak podszkolić się w funkcjach Excela, pozwolę sobie pisać w razie pytań.
W jaki sposób rozwiązać sytuację, w której harmonogram jest dłuższy niż rok i daty/numery tygodni zaczynają się powtarzać? Można to jakoś obejść?
Nie wiem, czy dobrze rozumiem – czy może Pani podesłać plik Excela, żebym się przyjrzał problemowi?
Widzę, że dawno nie było komentarzy, ale artykuł jak widać wciąż aktualny. Ogólnie korzystałam z samego Gantta, natomiast w Excellu projekt wygląda świetnie. Bardzo dziękuję za udostępnienie. Dobry materiał.
Dziękuję za słowa wsparcia.