Volg de voortgang naar een doel - TechTV-artikelen

Nu het nieuwe jaar eraan komt, wil je misschien de voortgang naar een bepaald doel bijhouden. Deze coole grafiek in Excel bevat elementen die aangeven of je op schema ligt om een doel te bereiken. Stel dat u dit kwartaal 400 kilometer wilt hardlopen.

De zwarte lijn is het doel
De blauwe lijn is de lijn "on track" - je moet 2,7 mijl per dag rennen om het doel te bereiken
De groene lijn is de werkelijk afgelegde kilometers
De rode lijn is de voorspelling van waar je het kwartaal zult eindigen, op basis van je voortgang tot dusver. De rode lijn is verreweg het coolste deel van de kaart.

Hier ziet u hoe u de grafiek maakt.

Typ deze koppen bovenaan een werkblad. Voer in cel A5 de eerste datum voor het diagram in.
Zet de celpointer in A5. U zult zien dat er een vierkante stip in de rechter benedenhoek van A5 staat. Dit is de vulgreep. Pak met de muis de vulgreep vast en sleep naar beneden. Bekijk de knopinfo totdat u ziet dat u ver genoeg hebt gesleept om de laatste datum van het kwartaal op te nemen.
De formule voor B2 is =COUNT(A5:A200). Dit telt het aantal dagen in uw bereik.
De formule voor B3 is =B1/B2. Dit berekent hoe ver je elke dag moet rennen om het doel te bereiken.
Gebruik kolom B om in te voeren hoe ver u elke dag hebt gelopen.
Hoewel kolom B uw werkelijke gegevens bevat, is deze niet geschikt voor het maken van de grafiek. U zult in feite een volledig nieuw diagrambereik bouwen in de kolommen C: F dat de elementen bevat die nodig zijn voor het diagram.
Kolom C bevat datums. Als een as van uw diagrambereik getallen of datums bevat, moet u de cel linksboven van het diagrambereik leeg laten.
Dit is een vreemde fout in Excel - als u die cel leeg laat, kan Excel's Intellisense het diagrambereik later herkennen. Dus laat cel C5 leeg en voer koppen in voor Afstand, Track en Doel in D5: F5.
De formule in C5 is =A5.
Selecteer cel C5. Selecteer Cellen opmaken in het menu. Kies op het tabblad Numeriek de categorie Datum en vervolgens het formaat 3/14.
Selecteer cel C5. Sleep de vulgreep omlaag om deze formule te kopiëren. Sleep zo ver als je gegevens hebt in kolom A.
Voor de kolommen D en E zijn de formules in rij 5 anders dan die in rijen 6 en lager. De formules in rij 5 zorgen voor een numerieke start. Formules in rij 6 en lager zullen die numerieke waarden in rij 5 verhogen. De formule voor D5 is =B5- dit kopieert eenvoudig de afstand van de eerste dag naar het kaartbereik.
In een latere stap voegt Excel een trendlijn toe op basis van de waarden in kolom D. Als u D5 alleen maar naar beneden kopieert, krijgt u nullen voor toekomstige datums. De nullen maken de trendlijn ongeldig. De truc is dat je toekomstige datums moet vullen met # N / A-foutwaarden.
In het Engels zegt de formule dus in feite: "Als we vandaag zouden draaien, tel dan de waarde van vandaag op bij het totaal van gisteren. Zet anders een n.v.t. in deze cel.

Om dit resultaat te bereiken, voert u =IF(NOT(ISBLANK(B6)),D5+B6,NA())cel D6 in.
Selecteer cel D6. Dubbelklik op de vulgreep om deze formule naar beneden te kopiëren. De dubbelklik-truc werkt omdat de kolom aan de linkerkant van deze cel waarden bevat die Excel zullen begeleiden hoe ver de formule naar beneden moet worden gekopieerd.
De formule in E5 moet de dag van B3 kopiëren. Voer =B3E5 in.
De formule voor de rest van E moet B3 toevoegen aan de vorige dag. De formule voor E6 zou kunnen zijn, =E5+B3maar deze formule zou niet werken als u de cel naar de andere rijen kopieert. U wilt altijd dat de formule B3 toevoegt aan de bovenstaande cel.
Gebruik dus =E5+$B$3in E6. De dollartekens zorgen ervoor dat wanneer u de formule kopieert, deze altijd naar B3 wijst.
Verplaats de celwijzer terug naar E6 en dubbelklik op de vulgreep om de formule naar beneden te kopiëren.
De formule voor kolom F is =$B$1. Dit bereik wordt gebruikt om de zwarte doellijn op de kaart te tekenen. Hier is een tip voor het invoeren van deze formule. Begin in F5. Typ het gelijkteken. Raak met de muis B1 aan. Typ vervolgens de F4-toets om de dollartekens in te voeren.
Druk op Ctrl + Enter om de formule te accepteren en in de huidige cel te blijven. U kunt nu dubbelklikken op de vulgreep om de formule naar beneden te kopiëren.
Eindelijk bent u klaar om de grafiek te maken. Selecteer het bereik van C4: F94. Gebruik Invoegen - Grafiek in het menu of klik op het pictogram Wizard Grafiek in de standaardwerkbalk.
Kies in stap 1 een lijndiagram.
De grafiek vereist veel aanpassingen, maar niets gebeurt in de wizard, dus klik gewoon op Voltooien om deze verschrikkelijk uitziende grafiek te maken.
Klik met de rechtermuisknop op de grijze achtergrond en kies Plotgebied opmaken.
Kies Opvuleffecten.
Stel een tweekleurig verloop in zoals hier wordt weergegeven.
Klik met de rechtermuisknop op de gele lijn Doelseries. Kies Gegevensreeks opmaken.
Kies een dikke blauwe lijn voor de doelserie.
Herhaal stap 23-24 om de tracklijn te veranderen in een dunne zwarte lijn.
Het is moeilijk om de Distance-serie te formatteren, aangezien er maar 2 punten zijn en het bijna niet te zien is. Als het moeilijk is om met de rechtermuisknop op een element van het diagram te klikken, kunt u de vervolgkeuzelijst in de diagramwerkbalk gebruiken.
Zodra de afstandsreeks is geselecteerd, gebruikt u het pictogram Eigenschappen om het dialoogvenster Opmaak voor de geselecteerde reeks te openen.
Voeg een markering toe aan dit dialoogvenster. Verander de kleur in donkerblauw en de lijndikte in medium.
Om de trendlijn toe te voegen, moet u met de rechtermuisknop op de afstandsreeks kunnen klikken. Dit is op dit moment bijna onmogelijk. Dus - ik stel voor dat je "vals speelt". Voer een nepgegevenspunt van 20 in cel B7 in. Hierdoor kunt u met de rechtermuisknop op de afstandsreeks klikken. Kies Trendlijn toevoegen.
Kies in het dialoogvenster Trendlijn de lineaire trendlijn.
Selecteer cel B7 en typ de Delete-toets om de nepwaarde daar te wissen. Je hebt nu de trendlijn getekend als een dikke zwarte lijn.
Klik met de rechtermuisknop op de trendlijn en kies Trendlijn opmaken.
Verander de trendlijn in een onderbroken rode lijn met een dun gewicht.
Wijzig op het tabblad Opties van de Opmaaktrendlijn de naam van de reeks in Voorspelling

Je hebt nu een ingevulde grafiek. Als je een dag hardlopen mist, vul dan een nul in in kolom B. Als je een paar dagen mist, zal de rode voorspellingslijn aangeven dat je niet op schema ligt om het doel te halen.