Spor fremgang mot et mål - TechTV-artikler

Med det nye året som kommer, vil du kanskje spore fremgangen mot et slags mål. Dette kule diagrammet i Excel inneholder elementer som indikerer om du er på vei til å nå et mål. Si at du vil løpe 250 miles dette kvartalet.

Den svarte linjen er målet
Den blå linjen er "on track" -linjen - du må løpe 4,7 miles per dag for å nå målet
Den grønne linjen er den faktiske kilometerløpet
Den røde linjen er spådommen for hvor du vil fullføre kvartalet, basert på fremgangen din så langt. Den røde linjen er uten tvil den kuleste delen av diagrammet.

Slik lager du diagrammet.

Skriv inn disse overskriftene øverst på et regneark. I celle A5 skriver du inn den første datoen for diagrammet.
Sett cellepekeren i A5. Du vil merke at det er en firkantet prikk i nedre høyre hjørne av A5. Dette er fyllhåndtaket. Ta tak i fyllhåndtaket med musen og dra nedover. Se på verktøytipsen til du ser at du har dratt langt nok til å inkludere den siste datoen i kvartalet.
Formelen for B2 er =COUNT(A5:A200). Dette vil telle antall dager i området ditt.
Formelen for B3 er =B1/B2. Dette beregner hvor langt du må løpe hver dag for å nå målet.
Bruk kolonne B for å angi hvor langt du løp hver dag.
Selv om kolonne B inneholder dine faktiske data, er det ikke egnet for å lage diagrammet. Du vil faktisk bygge et helt nytt diagramområde i kolonnene C: F som inneholder elementene som trengs for diagrammet.
Kolonne C vil inneholde datoer. Hvis en akse i kartområdet inneholder tall eller datoer, må du la den øverste venstre cellen i kartområdet være tom.
Dette er en merkelig feil i Excel - å la den cellen være tom vil tillate Excel Intellisense å gjenkjenne kartområdet senere. Så la celle C5 være tom, og skriv inn overskrifter for Avstand, Spor og Mål i D5: F5.
Formelen i C5 er =A5.
Velg celle C5. Velg Format celler fra menyen. Velg kategorien Dato og deretter formatet 3/14 i kategorien Numerisk.
Velg celle C5. Dra fyllhåndtaket ned for å kopiere denne formelen. Dra så langt du har data i kolonne A.
For kolonnene D & E vil formlene i rad 5 være forskjellige enn de i rad 6 og under. Formlene i rad 5 vil gi en numerisk start. Formler i rad 6 og nedenfor vil øke de numeriske verdiene i rad 5. Formelen for D5 er =B5- dette vil bare kopiere den første dags avstand til kartområdet.
I et senere trinn vil Excel legge til en trendlinje basert på verdiene i kolonne D. Hvis du bare kopierte D5 ned, vil du ende opp med nuller for fremtidige datoer. Nullene vil ugyldiggjøre trendlinjen. Trikset er at du må fylle fremtidige datoer med # N / A feilverdier.
På engelsk sier formelen i utgangspunktet: "Hvis vi løp i dag, så legg dagens verdi til gårsdagens total. Ellers legger du N / A i denne cellen.

For å oppnå dette resultatet, skriv =IF(NOT(ISBLANK(B6)),D5+B6,NA())inn celle D6.
Velg celle D6. Dobbeltklikk påfyllingshåndtaket for å kopiere ned denne formelen. Dobbeltklikk-trikset fungerer fordi kolonnen til venstre for denne cellen inneholder verdier som vil lede Excel hvor langt ned formelen må kopieres.
Formelen i E5 må kopiere per dag fra B3. Skriv =B3inn E5.
Formelen for resten av E må legge til B3 til forrige dag. Formelen for E6 kan være, =E5+B3men denne formelen fungerer ikke når du kopierer cellen ned til de andre radene. Du vil alltid at formelen skal legge til B3 i cellen ovenfor.
Dermed bruk =E5+$B$3i E6. Dollartegnene sørger for at når du kopierer formelen, vil den alltid peke på B3.
Flytt cellepekeren tilbake til E6, og dobbeltklikk på fyllhåndtaket for å kopiere formelen.
Formelen for hele kolonne F er =$B$1. Dette området vil bli brukt til å tegne den svarte mållinjen på diagrammet. Her er et tips for å skrive inn denne formelen. Start i F5. Skriv likhetstegnet. Trykk på B1 ved hjelp av musen. Skriv deretter inn F4-tasten for å sette dollartegnene inn.
Trykk Ctrl + Enter for å godta formelen og forbli i gjeldende celle. Du kan nå dobbeltklikke på fyllhåndtaket for å kopiere formelen ned.
Til slutt er du klar til å lage diagrammet. Velg området C4: F94. Bruk enten Sett inn - Kart fra menyen, eller klikk på Kartveiviser-ikonet i standardverktøylinjen.
I trinn 1 velger du et linjediagram.
Diagrammet vil kreve mye tilpasning, men ingenting skjer i veiviseren, så bare klikk Fullfør for å lage dette fryktelige utseendet.
Høyreklikk på den grå bakgrunnen og velg Format tomtområde.
Velg Fill Effects.
Sett opp en 2-farget gradient som vist her.
Høyreklikk på den gule linjen for målserien. Velg Format Data Series.
Velg en tykk blå linje for målserien.
Gjenta trinn 23-24 for å endre sporlinjen til en tynn svart linje.
Det er vanskelig å formatere Distance-serien, da det bare er to poeng, og det er nesten umulig å se. Hvis det er vanskelig å høyreklikke på et element i diagrammet, kan du bruke rullegardinmenyen i verktøylinjen Kartlegging.
Når avstandsserien er valgt, bruk Egenskaper-ikonet for å åpne Format-dialogboksen for den valgte serien.
Legg til en markør i denne dialogen. Endre fargen til mørkeblå og linjetykkelsen til middels.
For å legge til trendlinjen, må du kunne høyreklikke på avstandsserien. Dette er nesten umulig å gjøre på dette tidspunktet. Så - jeg foreslår at du "jukser". Skriv inn et falskt datapunkt på 20 i celle B7. Dette lar deg høyreklikke på avstandsserien. Velg Legg til trendlinje.
Velg Lineær trendlinje i Trendline-dialogen.
Velg celle B7 og skriv Delete-tasten for å slette den falske verdien der. Du vil nå få trendlinjen tegnet inn som en tykk svart linje.
Høyreklikk på trendlinjen og velg Format Trendline.
Endre Trendline til en ødelagt rød linje med tynn vekt.
I kategorien Alternativer i Format Trendline endrer du serienavnet til Prediction

Du har nå et fullført diagram. Hvis du savner en løpedag, skriv inn et null i kolonne B. Hvis du savner et par dager, vil den røde prediksjonslinjen vise at du ikke er på vei til å nå målet.