Verfolgen Sie den Fortschritt in Richtung eines Ziels - TechTV-Artikel

Mit dem bevorstehenden neuen Jahr möchten Sie vielleicht den Fortschritt in Richtung eines Ziels verfolgen. Dieses coole Diagramm in Excel enthält Elemente, die angeben, ob Sie auf dem richtigen Weg sind, um ein Ziel zu erreichen. Angenommen, Sie möchten in diesem Quartal 250 Meilen laufen.

Die schwarze Linie ist das Ziel
Die blaue Linie ist die "auf der Strecke" -Linie - Sie müssen 2,7 Meilen pro Tag laufen, um das Ziel zu erreichen
Die grüne Linie gibt die tatsächlichen Meilen an
Die rote Linie ist die Vorhersage, wo Sie das Quartal beenden werden, basierend auf Ihren bisherigen Fortschritten. Die rote Linie ist bei weitem der coolste Teil des Diagramms.

Hier erfahren Sie, wie Sie das Diagramm erstellen.

Geben Sie diese Überschriften oben in ein Arbeitsblatt ein. Geben Sie in Zelle A5 das erste Datum für das Diagramm ein.
Setzen Sie den Zellzeiger in A5. Sie werden feststellen, dass sich in der unteren rechten Ecke von A5 ein quadratischer Punkt befindet. Dies ist der Füllgriff. Fassen Sie mit der Maus den Füllgriff und ziehen Sie ihn nach unten. Sehen Sie sich den Tooltip an, bis Sie sehen, dass Sie weit genug gezogen haben, um das letzte Datum des Quartals anzugeben.
Die Formel für B2 lautet =COUNT(A5:A200). Dies zählt die Anzahl der Tage in Ihrem Bereich.
Die Formel für B3 lautet =B1/B2. Dies berechnet, wie weit Sie jeden Tag laufen müssen, um das Ziel zu erreichen.
Verwenden Sie Spalte B, um einzugeben, wie weit Sie jeden Tag gelaufen sind.
Obwohl Spalte B Ihre tatsächlichen Daten enthält, ist sie nicht zum Erstellen des Diagramms geeignet. In den Spalten C: F erstellen Sie einen völlig neuen Diagrammbereich, der die für das Diagramm erforderlichen Elemente enthält.
Spalte C enthält Datumsangaben. Wenn eine Achse Ihres Diagrammbereichs Zahlen oder Datumsangaben enthält, müssen Sie die obere linke Zelle des Diagrammbereichs leer lassen.
Dies ist ein seltsamer Fehler in Excel. Wenn Sie diese Zelle leer lassen, kann Intellisense von Excel den Diagrammbereich später erkennen. Lassen Sie also Zelle C5 leer und geben Sie in D5: F5 die Überschriften für Entfernung, Spur und Ziel ein.
Die Formel in C5 lautet =A5.
Wählen Sie Zelle C5 aus. Wählen Sie im Menü die Option Zellen formatieren. Wählen Sie auf der Registerkarte Numerisch die Kategorie Datum und dann das Format 3/14.
Wählen Sie Zelle C5 aus. Ziehen Sie den Füllpunkt nach unten, um diese Formel zu kopieren. Ziehen Sie nach unten, bis Sie Daten in Spalte A haben.
Für die Spalten D und E unterscheiden sich die Formeln in Zeile 5 von denen in den Zeilen 6 und darunter. Die Formeln in Zeile 5 bieten einen numerischen Start. Formeln in Zeile 6 und darunter erhöhen diese numerischen Werte in Zeile 5. Die Formel für D5 lautet =B5- dies kopiert einfach die Entfernung des ersten Tages in den Diagrammbereich.
In einem späteren Schritt fügt Excel eine Trendlinie hinzu, die auf den Werten in Spalte D basiert. Wenn Sie lediglich D5 nach unten kopieren, erhalten Sie für zukünftige Daten Nullen. Die Nullen machen die Trendlinie ungültig. Der Trick besteht darin, dass Sie zukünftige Daten mit # N / A-Fehlerwerten füllen müssen.
Auf Englisch heißt es in der Formel im Grunde: "Wenn wir heute gelaufen sind, addieren Sie den heutigen Wert zur gestrigen Summe. Andernfalls geben Sie ein N / A in diese Zelle ein.

Um dieses Ergebnis zu erzielen, geben Sie =IF(NOT(ISBLANK(B6)),D5+B6,NA())in Zelle D6 ein.
Wählen Sie Zelle D6 aus. Doppelklicken Sie auf den Füllpunkt, um diese Formel nach unten zu kopieren. Der Doppelklick-Trick funktioniert, da die Spalte links neben dieser Zelle Werte enthält, die Excel zeigen, wie weit die Formel kopiert werden muss.
Die Formel in E5 muss die pro Tag von B3 kopieren. Geben Sie =B3in E5 ein.
Die Formel für den Rest von E muss B3 zum vorherigen Tag hinzufügen. Die Formel für E6 könnte lauten, =E5+B3aber diese Formel funktioniert nicht, wenn Sie die Zelle in die anderen Zeilen kopieren. Sie möchten immer, dass die Formel B3 zur obigen Zelle hinzufügt.
Verwenden Sie daher =E5+$B$3in E6. Die Dollarzeichen stellen sicher, dass beim Kopieren der Formel immer auf B3 verwiesen wird.
Bewegen Sie den Zellzeiger zurück zu E6 und doppelklicken Sie auf den Füllpunkt, um die Formel nach unten zu kopieren.
Die Formel für die gesamte Spalte F lautet =$B$1. Dieser Bereich wird verwendet, um die schwarze Torlinie in der Karte zu zeichnen. Hier ist ein Tipp zur Eingabe dieser Formel. Beginnen Sie in F5. Geben Sie das Gleichheitszeichen ein. Berühren Sie mit der Maus B1. Geben Sie dann die Taste F4 ein, um die Dollarzeichen einzugeben.
Drücken Sie Strg + Eingabetaste, um die Formel zu akzeptieren und in der aktuellen Zelle zu bleiben. Sie können jetzt auf den Füllpunkt doppelklicken, um die Formel nach unten zu kopieren.
Schließlich können Sie das Diagramm erstellen. Wählen Sie den Bereich von C4: F94. Verwenden Sie entweder Einfügen - Diagramm aus dem Menü oder klicken Sie in der Standardsymbolleiste auf das Symbol Diagrammassistent.
Wählen Sie in Schritt 1 ein Liniendiagramm.
Das Diagramm erfordert viele Anpassungen, aber nichts davon geschieht im Assistenten. Klicken Sie einfach auf Fertig stellen, um dieses schrecklich aussehende Diagramm zu erstellen.
Klicken Sie mit der rechten Maustaste auf den grauen Hintergrund und wählen Sie Plotbereich formatieren.
Wählen Sie Fülleffekte.
Richten Sie wie hier gezeigt einen 2-Farben-Farbverlauf ein.
Klicken Sie mit der rechten Maustaste auf die gelbe Linie der Zielserie. Wählen Sie Datenreihen formatieren.
Wählen Sie eine dicke blaue Linie für die Zielserie.
Wiederholen Sie die Schritte 23 bis 24, um die Spurlinie in eine dünne schwarze Linie zu ändern.
Es ist schwierig, die Distanzserie zu formatieren, da es nur 2 Punkte gibt und es fast unmöglich ist, sie zu sehen. Wenn es schwierig ist, mit der rechten Maustaste auf ein Element des Diagramms zu klicken, können Sie das Dropdown-Menü in der Diagrammsymbolleiste verwenden.
Sobald die Entfernungsreihe ausgewählt ist, öffnen Sie mit dem Symbol Eigenschaften das Dialogfeld Format für die ausgewählte Reihe.
Fügen Sie diesem Dialogfeld einen Marker hinzu. Ändern Sie die Farbe in Dunkelblau und die Linienstärke in Mittel.
Um die Trendlinie hinzuzufügen, müssen Sie in der Lage sein, mit der rechten Maustaste auf die Entfernungsreihe zu klicken. Dies ist zu diesem Zeitpunkt nahezu unmöglich. Also - ich schlage vor, dass Sie "betrügen". Geben Sie in Zelle B7 einen gefälschten Datenpunkt von 20 ein. Auf diese Weise können Sie mit der rechten Maustaste auf die Entfernungsreihe klicken. Wählen Sie Trendlinie hinzufügen.
Wählen Sie im Dialogfeld "Trendlinie" die Option "Lineare Trendlinie".
Wählen Sie Zelle B7 aus und geben Sie die Entf-Taste ein, um den falschen Wert dort zu löschen. Sie haben jetzt die Trendlinie als dicke schwarze Linie eingezeichnet.
Klicken Sie mit der rechten Maustaste auf die Trendlinie und wählen Sie Trendlinie formatieren.
Ändern Sie die Trendlinie in eine unterbrochene rote Linie mit einem dünnen Gewicht.
Ändern Sie auf der Registerkarte Optionen der Format-Trendlinie den Seriennamen in Vorhersage

Sie haben jetzt ein fertiges Diagramm. Wenn Sie einen Lauftag verpassen, geben Sie in Spalte B eine Null ein. Wenn Sie einige Tage verpassen, zeigt die rote Vorhersagelinie an, dass Sie nicht auf dem richtigen Weg sind, um das Ziel zu erreichen.