Je vindt me ook op

Looptijden en graphics in Excel

Snelheid uitgedrukt in minuten:seconden per kilometer, of in kilometer/uur: een omrekenaar in Excel. En cellen laten oplichten via voorwaardelijke opmaak. En een grafische weergave.

Looptijden en graphics in Excel

In deze tutorial:

Running apps, waaronder Nike+ GPS, hanteren vaak de aanduiding minuten:seconden per kilometer en niet kilometer per uur.

  • Snelheid uitgedrukt in min:sec / km, of in km/uur: een omrekenaar in Excel.
  • Je runs met de hoogste gemiddelde snelheid laten oplichten via voorwaardelijke opmaak.
  • En een grafische weergave van afstanden en gemiddelde snelheden gecombineerd in één grafiek

Een minpuntje van de voor het overige geweldige app Nike+ GPS is dat zowel de snelheid (en hoogste, laagste) op een bepaald moment, als de gemiddelde snelheid (van een kilometer, mijl, 5 of 10 km of van de totale afstand) uitgedrukt worden in aantal minuten:seconden per kilometer. Soms wil je misschien liever een gemiddelde snelheid uitgedrukt zien in km/uur.

Als je gemiddelde snelheid bijvoorbeeld een keer 5 min. 33 sec. / km bedroeg, hoeveel sneller was je dan een volgende keer toen de gemiddelde snelheid 5 min. 16 sec. / km bedroeg?
Antwoord: de eerste keer liep je met een gem. snelheid van 10,8 km/uur, daarna met 11,4 km/uur.
In plaats van dat uit je hoofd om te rekenen kun je ook een hulpmiddel gebruiken: Excel.

Running apps, waaronder Nike+ GPS, hanteren vaak de aanduiding minuten:seconden per kilometer en niet kilometer per uur.

Scherm bij start van lopen

Route summary

Route info met hoogste en laagste snelheid

Twitterbericht met gem. snelheid omgerekend naar km/uur

Hoe reken je 6 min. 6sec. per km. om in km. per uur?
  • 6 min. + 6 sec. = 366 sec. per km.
  • 366 sec. gaat 3600/366 keer in 1 uur = 9,836 km/uur

Of via de totale tijd en afstand:

Hoe reken je 11,45 km. in 1:09’48” om in km. per uur?

Reken totale tijd om naar seconden: 1 uur + 9 minuten + 48 seconden = 3600 + 540 + 48 = 4188 seconden;

  • Voor de totale afstand van 11,45 km heb ik nodig gehad 4188 sec.
  • Dus in 1 uur zou zijn afgelegd: 3600/4188 * 11,45 = 9,842 km

Deze tweede berekening is door het veel grotere aantal seconden en kilometers nauwkeuriger dan de eerste berekening.

Excel doet het rekenwerk

Als je voor deze tweede methode Excel het werk wil laten doen, moet je de gegevens invoeren zoals in de figuur hieronder:

  • cel A2: totale afstand
  • cel B2: de totale tijd; eigenschap van cel: datum/tijd, aangepaste tijdnotatie; deze notatie heb je alleen nodig als je wil kunnen terugrekenen naar min.:sec per km. (de Nike-weergave), in dit geval opgenomen in cel G2
  • cellen C2:E2: de totale tijd, geknipt in uren (in C2), min. (in D2), sec. (in E2)
  • cel F2: de functie: =3600/((C2*3600)+(D2*60)+E2)*A2
    hiermee wordt het aantal sec. van een uur (3600) gedeeld door het totale aantal gelopen seconden (C2*3600)+(D2*60)+E2); deze breuk wordt vermenigvuldigd met het totale aantal km. (cel A2)
  • cel G2: eigenschap van cel: datum/tijd, aangepaste tijdnotatie: de functie: =B2/A2 totale tijd gedeeld door totale aantal km. 1:09:48 / 11,45

Op deze manier moet je de tijd dus twee keer invoeren. Maar ’t kan makkelijker.
Je hoeft de tijd van cel B2 niet eigenhandig op te knippen, want er is een Excel-functie die dit voor je kan doen:

  • in cel C2 de functie: =UUR(B2) (deze functie haalt uit de genoemde cel (B2) het aantal uren dat daarin voorkomt);
  • in cel D2 de functie: =MINUUT(B2) (deze functie haalt uit de genoemde cel (B2) het aantal minuten dat daarin voorkomt);
  • in cel E2 de functie: =SECONDE(B2) (deze functie haalt uit de genoemde cel (B2) het aantal seconden dat daarin voorkomt);

Als je de genoemde formules één keer ingevoerd hebt hoef je voortaan alleen nog maar totale afstand en totale tijd in te voeren (cellen A2 en B2).
De uren, minuten en seconden in respectievelijk de cellen C2, D2 en E2 worden automatisch ingevuld!

De inhoud van de kolommen C, D en E van het werkblad hierboven kun je een stuk naar rechts verplaatsen zodat ze buiten beeld blijven; ze zijn immers alleen maar ‘in de achtergrond’ nodig voor de berekening.

De omrekenaar in iPhone

de omrekenaar in Excel

Bewaar het Excel-bestandje in de iPhone en je hebt je eigen omrekenmachientje bij de hand.
Om zo efficiënt mogelijk met de beperkte ruimte om te gaan is de lay-out geoptimaliseerd: Je vult de C- en E-cel in, het resultaat verschijnt in de D-cel daaronder.
(de drie overbodige cellen met de gesplitste uren – minuten – seconden zijn er wel maar blijven buiten beeld)

de omrekenaar op iPhone, in DocsToGo, Excel

Als je de formule per kolom kopieert naar bijvoorbeeld 25 cellen in dezelfde kolom heb je een gegevensbestand zoals hieronder:

De run met de hoogste gemiddelde snelheid

De cel met groene achtergrond krijg je door voor de hele kolom als voorwaardelijke opmaak voor de cellen te kiezen: in menu: [Opmaak] -> [Voorwaardelijke opmaak…] -> ‘alleen hoogste of laagste’ -> hoogste -> aantal: 1, en voor die regel de gekozen opmaak kiezen: achtergrond…

Met die voorwaardelijke opmaak kun je nog meer doen, bijvoorbeeld direct zien wat tot nu toe de vijf hoogste gemiddelde snelheden waren, maar tegelijkertijd wat de hoogste gemiddelde snelheid tot nu toe was.
Dat kan door via hetzelfde menu: [Opmaak] -> [Voorwaardelijke opmaak…] -> niet één maar twee regels op te nemen (klik voor vergroting):

Regel 1 en 2 voor de voorwaardelijke opmaak

Regel 2: de vijf snelste tijden hebben een lichtgroene achtergrond, maar van die vijf wordt de opmaak van de cel met de snelste tijd overruled door:
Regel 1: geef de snelste tijd van allemaal een donkergroene achtergond met witte cijfers (voorgrond).

Het resultaat in je tabel ziet er dan zo uit:

resultaat voorwaardelijke opmaak

En Excel maakt een grafische weergave

En als je dan toch zo’n databestandje hebt dat zich wekelijks uitbreidt is het ook wel leuk om in een grafiek per run de afstand weer te geven (blauwe balken, blauwe getallen op primaire y-as) en de gemiddelde snelheid in km/uur (de door de rode lijn verbonden punten, rode getallen op secundaire y-as).

Het is bijvoorbeeld leuk om te zien hoe de gemiddelde snelheid lager ligt bij een relatief grote toename van de gelopen afstand. En als je ziet hoe de rode lijn ook bij een lange afstand dicht in de buurt komt van de hoogste gemiddelde snelheid is er sprake van een motivatie-trigger: de rode lijn zal nog moeten stijgen, het liefst ongeacht de gelopen afstand.

(klik voor vergroting)

De Nike+ Dashboard site doet iets dergelijks ook wel, maar in de staafgrafiek zit niet de gemiddelde snelheid van elke run, althans niet in een overzicht door de grafiek heen.

'My runs' in Nike+ Dashboard

grafische weergave van runs, met mouse-over effect

Per run is er wel een mouse-over effect dat gegevens toont van de betreffende run, inclusief de gemiddelde snelheid… in aantal minuten per km, niet in km/uur…

Je kan naar dit artikel verwijzen op sociale netwerken:
About Author

Rob le PairDocent - onderzoeker Radboud Universiteit Nijmegen; favoriete thema's op deze site: Persuasive communication, Eindhoven-Philipsdorp, WordPress-design, gebruik van sociale media, running experiences, gardening.View all posts by Rob le Pair →

  1. Van der Borgt Luc
    Van der Borgt Luc20 augustus 2012

    Kan ik ergens een gelijkaardig gratis excel programmatje downloaden om te gebruiken op mijn pc, dit om mijn fietstijden kan bijhouden en te vergelijken over een langere periode?
    graag help want ik ben zelf geen krak om zoiets zelf te gaan maken.

  2. Rob le Pair
    Rob le Pair20 augustus 2012

    Dag Luc,
    Ik kan wel een Excel-bestand voor je maken met wat instructies erbij; zal ik daar de kolommen bij doen voor automatische omrekening van uren:minuten:seconden per kilometer naar gemiddelde snelheid in kilometer per uur?
    Ik kan je dan over een paar dagen wel zo’n bestand toesturen.

    • Van der Borgt Luc
      Van der Borgt Luc21 augustus 2012

      hallo Rob,
      dit zou fantastisch zijn. Het voorbeeld in excel met de tabellen voor datum-km-tijd-uur-min-sec-en gemiddelde km/u zou al heel goed zijn + misschien ook de weergave van die snelste tijden in een kleurtje, je ziet maar wat kan.
      alvast bedankt voor alle moeite

  3. Peter Stals
    Peter Stals23 juli 2013

    juist wat ik zocht om km/u te berekenen,

    Ik heb de kolommen met uur, min en sec weggelaten door onmiddellijk de aparte formules in de hoofdformule te zetten:
    =3600/((UUR(B3)*3600)+(MINUUT(B3)*60)+SECONDE(B3))*G3

    nu mij eens wagen aan die grafiek.

    • Rob le Pair
      Rob le Pair14 november 2014

      Bedankt, goede tip, dat is nog eenvoudiger

  4. Niek
    Niek13 november 2014

    Bedankt voor je erg goede tips. Ik ben zelf bezig aan een eigen spreadsheet voor mijn hardlooptijden en heb dus veel aan je blog gehad. Bedankt.

    • Rob le Pair
      Rob le Pair14 november 2014

      Graag gedaan, en succes met lopen en registreren!

  5. Frits
    Frits21 oktober 2015

    De formule met uur seconde en minuut kende ik nog niet. Bedankt voor de duidelijken uitleg.
    Wat ik nu nog wil maken is een rekenvel met de eindafstand. ik geef dan in een cel aan hoeveel secondes ik over 1 ronde van 400 meter doe. de eind tijd wordt 12 minuten (coopertest).

  6. Paul
    Paul22 mei 2016

    hi Rob,
    ziet er goed uit en is precies wat ik zoek.
    Heb je toevallig ook een download bestand of zou je het bestandje mij toe willen mailen in het kader van niet hetzelfde wiel uitvinden?
    Hartelijke groet,

    Paul

  7. Henk
    Henk14 oktober 2016

    Hoi Rob,

    Ik stoei er ook mee heb je voor mij ook een bestandje en dan met graffieken erin. Alvast bedankt.
    h.heide38@upcmail.nl

    Groeten Henk

  8. R de Rooy
    R de Rooy18 november 2016

    Hoi Rob,

    Via Google op jouw site terecht gekomen.
    Ik ben een MILA looptrainer.
    Ik wil in excel een schema maken van 400m ronde tijden.
    Vb 400m in 1.00, is een 800m in 2.00, is een 1000m in 2.30, een 1200m in 3.00, een 1500m 3.45 etc
    Ik sta regelmatig op de atletiek baan te coachen op de 1500m-3-5-10km.
    Ik zie op uw blog dat u helpt bij het maken van oplossingen in excel.
    Met getallen kan ik wel werken in excel, maar het moet omgezet worden in uren, minuten, seconden. Dat lukt mij tot op heden niet.
    Kunt u mij de juiste formules hiervoor geven?

    Met vriendelijke groet

    Ronald de Rooy
    rderooy@kpnmail.nl

Leave a Reply