Spåra framsteg mot ett mål - TechTV-artiklar

Med det nya året som kommer kan du spåra framsteg mot något slags mål. Det här coola diagrammet i Excel innehåller element som indikerar om du är på rätt spår för att nå ett mål. Säg att du vill springa 250 mil i kvartalet.

Den svarta linjen är målet
Den blå linjen är "on track" -linjen - du måste springa 4,7 miles per dag för att nå målet
Den gröna linjen är den faktiska milkörningen
Den röda linjen är förutsägelsen för var du kommer att avsluta kvartalet, baserat på dina framsteg hittills. Den röda linjen är den absolut coolaste delen av diagrammet.

Så här skapar du diagrammet.

Ange dessa rubriker högst upp på ett kalkylblad. Ange det första datumet för diagrammet i cell A5.
Sätt cellpekaren i A5. Du kommer att märka att det finns en fyrkantig punkt i det nedre högra hörnet av A5. Detta är påfyllningshandtaget. Ta tag i fyllningshandtaget med musen och dra nedåt. Titta på verktygstipsen tills du ser att du har dragit tillräckligt långt för att inkludera det sista datumet i kvartalet.
Formeln för B2 är =COUNT(A5:A200). Detta räknar antalet dagar i ditt intervall.
Formeln för B3 är =B1/B2. Detta beräknar hur långt du måste springa varje dag för att nå målet.
Använd kolumn B för att ange hur långt du sprang varje dag.
Även om kolumn B innehåller dina faktiska data är det inte lämpligt för att skapa diagrammet. Du kommer faktiskt att bygga ett helt nytt diagramintervall i kolumner C: F som innehåller de element som behövs för diagrammet.
Kolumn C innehåller datum. Om en axel i ditt diagramintervall innehåller siffror eller datum måste du lämna den övre vänstra cellen i diagramområdet tomt.
Det här är ett konstigt fel i Excel - om den cellen lämnas tom kan Excels Intellisense känna igen kartområdet senare. Så lämna cell C5 tom och ange rubriker för avstånd, spår och mål i D5: F5.
Formeln i C5 är =A5.
Välj cell C5. Välj Formatera celler på menyn. På fliken Numerik väljer du Datumkategori och sedan formatet 3/14.
Välj cell C5. Dra påfyllningshandtaget för att kopiera den här formeln. Dra ner så långt du har data i kolumn A.
För kolumnerna D & E kommer formlerna i rad 5 att skilja sig från dem i raderna 6 och nedan. Formlerna i rad 5 ger en numerisk start. Formler i rad 6 & nedan kommer att öka dessa numeriska värden i rad 5. Formeln för D5 är =B5- detta kopierar helt enkelt den första dagens avstånd till diagramområdet.
I ett senare steg kommer Excel att lägga till en trendlinje baserad på värdena i kolumn D. Om du bara kopierade D5 ner skulle du få nollor för framtida datum. Nollor ogiltigförklarar trendlinjen. Tricket är att du måste fylla framtida datum med # N / A-felvärden.
På engelska säger formeln i princip: "Om vi sprang idag, lägg sedan dagens värde till gårdagens totala värde. Annars sätter du ett N / A i den här cellen.

För att uppnå detta resultat, ange =IF(NOT(ISBLANK(B6)),D5+B6,NA())i cell D6.
Välj cell D6. Dubbelklicka på fyllningshandtaget för att kopiera ner den här formeln. Dubbelklickstricket fungerar eftersom kolumnen till vänster om den här cellen innehåller värden som styr Excel hur långt ner formeln behöver kopieras.
Formeln i E5 måste kopiera per dag från B3. Ange =B3E5.
Formeln för resten av E måste lägga till B3 till föregående dag. Formeln för E6 kan vara =E5+B3men den här formeln fungerar inte när du kopierar cellen ner till de andra raderna. Du vill alltid att formeln ska lägga till B3 i cellen ovan.
Använd sålunda =E5+$B$3i E6. Dollartecknen ser till att när du kopierar formeln kommer den alltid att peka på B3.
Flytta cellpekaren tillbaka till E6 och dubbelklicka på fyllningshandtaget för att kopiera formeln nedåt.
Formeln för hela kolumn F är =$B$1. Detta intervall kommer att användas för att rita i den svarta mållinjen i diagrammet. Här är ett tips för att ange denna formel. Börja i F5. Skriv likhetstecknet. Tryck på B1 med musen. Skriv sedan F4-tangenten för att sätta in dollartecken.
Tryck på Ctrl + Enter för att acceptera formeln och stanna kvar i den aktuella cellen. Du kan nu dubbelklicka på fyllningshandtaget för att kopiera formeln neråt.
Slutligen är du redo att skapa diagrammet. Välj intervallet för C4: F94. Använd antingen Infoga - diagram från menyn eller klicka på ikonen för diagramguiden i standardverktygsfältet.
I steg 1 väljer du ett linjediagram.
Diagrammet kräver mycket anpassning, men inget av det händer i guiden, så klicka bara på Slutför för att skapa detta hemskt snygga diagram.
Högerklicka på den grå bakgrunden och välj Formatera tomtområde.
Välj Fyllningseffekter.
Ställ in en tvåfärgad lutning som visas här.
Högerklicka på den gula målserieraden. Välj Format Data Series.
Välj en tjock blå linje för målserien.
Upprepa steg 23-24 för att ändra spårlinjen till en tunn svart linje.
Det är svårt att formatera distansserien, eftersom det bara finns två poäng och det är nästan omöjligt att se. Om det är svårt att högerklicka på ett element i diagrammet kan du använda rullgardinsmenyn i verktygsfältet Kartläggning.
När avståndsserien har valts använder du ikonen Egenskaper för att öppna dialogrutan Format för den valda serien.
Lägg till en markör i denna dialog. Ändra färgen till mörkblå och linjetjockleken till medium.
För att lägga till trendlinjen måste du kunna högerklicka på distansserien. Detta är nästan omöjligt att göra vid denna tidpunkt. Så - jag föreslår att du "fuskar". Ange en falsk datapunkt på 20 i cell B7. Detta gör att du kan högerklicka på distansserien. Välj Lägg till trendlinje.
I dialogrutan Trendline väljer du Linjär trendlinje.
Välj cell B7 och skriv Delete-tangenten för att radera det falska värdet där. Nu kommer trendlinjen att dras in som en tjock svart linje.
Högerklicka på trendraden och välj Format Trendline.
Ändra Trendline till en trasig röd linje med en tunn vikt.
Ändra seriens namn till förutsägelse på fliken Alternativ i Format Trendline

Du har nu ett färdigt diagram. Om du missar en löpedag anger du en noll i kolumn B. Om du missar ett par dagar kommer den röda förutsägelseslinjen att visa att du inte är på väg att nå målet.