Śledź postęp w kierunku celu - artykuły TechTV

Wraz ze zbliżającym się nowym rokiem możesz chcieć śledzić postępy w realizacji jakiegoś celu. Ten fajny wykres w programie Excel zawiera elementy, które wskazują, czy jesteś na dobrej drodze do osiągnięcia celu. Powiedz, że chcesz przebiec 250 mil w tym kwartale.

Czarna linia jest celem
Niebieska linia to linia „na torze” - aby osiągnąć cel, musisz biegać 2,7 mili dziennie
Zielona linia to faktyczne przejechane mile
Czerwona linia to prognoza, w której zakończysz kwartał, na podstawie Twoich dotychczasowych postępów. Czerwona linia to zdecydowanie najfajniejsza część wykresu.

Oto jak utworzyć wykres.

Wprowadź te nagłówki u góry arkusza. W komórce A5 wprowadź pierwszą datę wykresu.
Umieść celownik w A5. Zauważysz, że w prawym dolnym rogu A5 znajduje się kwadratowa kropka. To jest uchwyt napełniania. Za pomocą myszy chwyć uchwyt wypełniania i przeciągnij w dół. Oglądaj podpowiedź, aż zobaczysz, że przeciągnąłeś wystarczająco daleko, aby uwzględnić ostatni dzień kwartału.
Wzór na B2 to =COUNT(A5:A200). Spowoduje to policzenie dni w Twoim zakresie.
Wzór na B3 to =B1/B2. To oblicza, jak daleko musisz biec każdego dnia, aby osiągnąć cel.
Użyj kolumny B, aby wpisać, jak daleko biegałeś każdego dnia.
Chociaż kolumna B zawiera rzeczywiste dane, nie nadaje się do tworzenia wykresu. W rzeczywistości utworzysz zupełnie nowy zakres wykresu w kolumnach C: F, który będzie zawierał elementy potrzebne do wykresu.
Kolumna C będzie zawierać daty. Jeśli jedna oś zakresu wykresu zawiera liczby lub daty, pozostaw lewą górną komórkę zakresu wykresu pustą.
Jest to dziwny błąd w programie Excel - pozostawienie tej komórki pustej umożliwi programowi Intellisense programu Excel późniejsze rozpoznanie zakresu wykresu. Dlatego pozostaw komórkę C5 pustą i wprowadź nagłówki odległości, ścieżki i celu w D5: F5.
Formuła w C5 to =A5.
Wybierz komórkę C5. Z menu wybierz Formatuj komórki. Na karcie Liczby wybierz kategorię Data, a następnie format 3/14.
Wybierz komórkę C5. Przeciągnij uchwyt wypełniania w dół, aby skopiować tę formułę. Przeciągnij w dół tak daleko, jak masz dane w kolumnie A.
W przypadku kolumn D i E formuły w wierszu 5 będą inne niż w wierszach 6 i poniżej. Formuły w wierszu 5 zapewniają numeryczny początek. Formuły w wierszu 6 i poniżej zwiększą wartości liczbowe w wierszu 5. Wzór dla D5 jest następujący =B5- spowoduje to po prostu skopiowanie odległości z pierwszego dnia do zakresu wykresu.
W późniejszym etapie program Excel doda linię trendu na podstawie wartości z kolumny D. Jeśli tylko skopiowałeś D5 w dół, skończyłbyś z zerami dla przyszłych dat. Zera unieważnią linię trendu. Sztuczka polega na tym, że przyszłe daty należy wypełnić wartościami błędu # N / A.
W języku angielskim formuła zasadniczo mówi: „Jeśli uruchomiliśmy dzisiaj, dodaj dzisiejszą wartość do wczorajszej sumy. W przeciwnym razie wpisz N / A w tej komórce.

Aby osiągnąć ten wynik, wprowadź =IF(NOT(ISBLANK(B6)),D5+B6,NA())w komórce D6.
Wybierz komórkę D6. Kliknij dwukrotnie uchwyt wypełniania, aby skopiować tę formułę. Sztuczka z dwukrotnym kliknięciem działa, ponieważ kolumna po lewej stronie tej komórki zawiera wartości, które wskażą programowi Excel, jak daleko trzeba skopiować formułę.
Formuła w E5 musi kopiować dzienny z B3. Wejdź =B3na E5.
Formuła na pozostałą część E musi dodać B3 do poprzedniego dnia. Formuła dla E6 może wyglądać tak, =E5+B3ale ta formuła nie zadziała po skopiowaniu komórki do innych wierszy. Zawsze chcesz, aby formuła dodawała B3 do komórki powyżej.
Dlatego użyj =E5+$B$3w E6. Znaki dolara zapewniają, że kiedy kopiujesz formułę, zawsze będzie wskazywać na B3.
Przesuń wskaźnik komórki z powrotem do E6 i kliknij dwukrotnie uchwyt wypełniania, aby skopiować formułę.
Formuła dla całej kolumny F to =$B$1. Ten zakres będzie używany do rysowania na czarnej linii bramkowej na wykresie. Oto wskazówka dotycząca wprowadzania tego wzoru. Zacznij w F5. Wpisz znak równości. Używając myszy, dotknij B1. Następnie naciśnij klawisz F4, aby wstawić znaki dolara.
Naciśnij Ctrl + Enter, aby zaakceptować formułę i pozostać w bieżącej komórce. Możesz teraz kliknąć dwukrotnie uchwyt wypełniania, aby skopiować formułę.
Wreszcie jesteś gotowy do utworzenia wykresu. Wybierz zakres C4: F94. Użyj Wstaw - Wykres z menu lub kliknij ikonę Kreatora wykresów na standardowym pasku narzędzi.
W kroku 1 wybierz wykres liniowy.
Wykres będzie wymagał wielu dostosowań, ale nic z tego nie dzieje się w kreatorze, więc po prostu kliknij Zakończ, aby utworzyć ten okropnie wyglądający wykres.
Kliknij prawym przyciskiem myszy na szarym tle i wybierz opcję Formatuj obszar wydruku.
Wybierz efekty wypełnienia.
Skonfiguruj gradient dwukolorowy, jak pokazano tutaj.
Kliknij prawym przyciskiem myszy żółtą linię serii goli. Wybierz Formatuj serie danych.
Wybierz grubą niebieską linię dla serii bramkowej.
Powtórz kroki 23-24, aby zmienić linię ścieżki na cienką czarną linię.
Sformatowanie serii odległości jest trudne, ponieważ są tylko 2 punkty i jest prawie niemożliwe do zobaczenia. Jeśli trudno jest kliknąć prawym przyciskiem myszy element wykresu, możesz użyć listy rozwijanej na pasku narzędzi Wykresy.
Po wybraniu serii odległości użyj ikony Właściwości, aby otworzyć okno dialogowe Format dla wybranej serii.
Dodaj znacznik do tego okna dialogowego. Zmień kolor na ciemnoniebieski, a grubość linii na średnią.
Aby dodać linię trendu, musisz mieć możliwość kliknięcia prawym przyciskiem myszy serii odległości. W tej chwili jest to prawie niemożliwe. A więc - proponuję „oszukiwać”. Wprowadź fałszywy punkt danych o wartości 20 w komórce B7. Umożliwi to kliknięcie prawym przyciskiem myszy serii odległości. Wybierz Dodaj linię trendu.
W oknie dialogowym Linia trendu wybierz Liniową linię trendu.
Wybierz komórkę B7 i wpisz klawisz Delete, aby usunąć fałszywą wartość. Linia trendu zostanie teraz narysowana jako gruba czarna linia.
Kliknij prawym przyciskiem myszy linię trendu i wybierz Formatuj linię trendu.
Zmień linię trendu na przerywaną czerwoną linię o cienkiej wadze.
Na karcie Opcje w Formatuj linię trendu zmień nazwę serii na Predykcja

Masz już wypełniony wykres. Jeśli przegapisz dzień biegania, wprowadź zero w kolumnie B. Jeśli przegapisz kilka dni, czerwona linia prognozy pokaże, że nie jesteś na dobrej drodze do osiągnięcia celu.