Excel top5 formules: #4 Tijdseenheden

Time is tickin’

Time-is-tickingIn mijn functie als Webanalist maak ik (veel) Excel rapportages. Vanuit de business is vaak vraag naar zowel week- als maand- en soms zelfs kwartaalrapportages. Vaak zijn de gevraagde statistieken in deze rapportages nagenoeg hetzelfde. Om geen “data puker” of “reporting squirl” te worden (zoals Avinash dat zo mooi weet te verwoorden) en tijd over te hebben voor de echt leuke analyses, moet je hier handig mee omgaan. Excel kent hiervoor een aantal handige trucjes :-)! In deze post leg ik uit hoe je met één export op dagniveau je zowel een week-, maand- als kwartaalrapportage kunt samenstellen.

Het datumprobleem

Zelf werk ik vaak met de API van Google Analytics, waarbij ik rechtstreeks Analytics data in Excel kan laden. Dit werkt superfijn en efficiënt, alleen als je daarmee per datum data opvraagt is het format van de dag jjjjmmdd. Dit is een tekstveld en kan niet makkelijk geïnterpreteerd worden als een datumveld. Stel dat ik per datum, het aantal bezoeken, boekingen en omzet wil weten. Dan ziet de export er als volgt uit:

export

Om te kunnen rekenen met de kolom ‘date’ moeten we hier eerst een datumveld van maken. Dit kan met behulp van de functies =DATUM( )en =DEEL( ).

De functie =DATUM(jaar;maand;dag) retourneert het zogenaamde sequentiële seriële getal. Het resultaat van de formule =DATUM(2013;09;03) is bijvoorbeeld 41520. Dit is het seriële getal voor de datum 3-9-2013 (overigens kun je met behulp van de celeigenschappen dit getal weer een datumformat geven). Met dit getal kun je in Excel rekenen. Op basis van dit seriële getal kun je vrij gemakkelijk maanden en kwartalen maken. 

Aan het exportbestand gaan we dus een extra kolom toevoegen. We noemen deze kolom Datum. In deze cel typen we de volgende formule: =DATUM(DEEL(F2;1;4);DEEL(F2;5;2);DEEL(F2;7;2)). Hiermee zeggen we dus dat jaar hetzelfde is als de eerste 4 karakters uit cel F2 (2012), maand hetzelfde is als de 5e en 6e karakter (10) en dag hetzelfde is als de laatste 2 karakters uit cel F2 (01). We vragen dus op =DATUM(2012;10;01):

datum functie

MaDiWoDoVrZaZo

So far so good. Nu op naar de moeilijkste afgeleide: het weeknummer! In Excel zit een functie om het weeknummer op te vragen, maar dit zijn Amerikaanse weeknummers. Hierbij lopen de weken van zondag tot en met zaterdag. Europese bedrijven rapporteren niet op deze manier, maar op weken die lopen van maandag tot en met zondag.

Om het goede weeknummer te krijgen, maken we een extra kolom aan (links van datum) met de naam Week en typen we de volgende formule:

=INTEGER((E2-DATUM(JAAR(E2-WEEKDAG(E2-1)+4);1;3)+WEEKDAG(DATUM(JAAR(E2-WEEKDAG(E2-1)+4);1;3))+5)/7)

Deze formule ga ik niet uitleggen, want dat wordt toch écht te ingewikkeld (ook voor mij :-)). Ik zou zeggen kopiëren en plakken en zet hem in je favorietenlijstje!

weeknummer excel maandag tot zondag

Maand en kwartaal

De andere twee tijdseenheden maand en kwartaal zijn weer een stuk eenvoudiger. Voor maand gebruiken we simpelweg de functie =MAAND(E2). Om van deze maanden via een formule kwartalen te maken, gebruik je de volgende formule: =”Q”&INTEGER((C2-1)/3+1). Als je direct van een datumveld een kwartaal wilt maken, maak dan een geneste formule van de bovenste twee gecombineerd: =”Q”&INTEGER((MAAND(E2)-1)/3+1).

Als het goed is, het je nu de volgende output:

output

Met draaitabellen kun je nu heel gemakkelijk een rapportage opstellen:

kwartaal- en maandrapportage

Of maak in plaats van een kwartaal-/maandrapportage een weekrapportage. Vervang dan Kwartaal en Maand bij Rijlabels door Week:

weekrapportage

Make life even easier

De draaitabellen van Excel herbergen heel wat handigheidjes. Als je namelijk een kolom hebt met een (seriële) datum (kolom E), dan kun je met een draaitabel met Groeperen hier meteen jaren, maanden en kwartalen van maken. Zonder formules en extra kolommen! (kom je nú mee zul je denken ;-)). Alleen voor het juiste weeknummer zul je altijd de mooie bovenstaande excelformule moeten gebruiken.

kwartaal

jaar-kwartaal-maand

Hopelijk zorgen deze tips ervoor dat jouw reportingsquirlsymptomen nagenoeg verdwijnen en je tijd overhoudt voor echt leuke dingen :-D. Heb je vragen over de behandelde functies in Excel of andere rapportage vragen, neem dan gerust contact met me op of reageer op deze post!

One thought on “Excel top5 formules: #4 Tijdseenheden

  1. Hallo Anne,

    Ik moest vandaag diverse rapportages even klaar stomen voor het nieuwe jaar en moest hierbij weer even kijken hoe het ook al weer zat met de weeknummers. Ik gebruikte voorheen een soortgelijke formule als je hierboven beschrijft om een en ander te compenseren. Ik kwam er vandaag echter achter dat voor gebruik in het Europese systeem je tegenwoordig weeknummer(datum;2) of weeknummer(datum;21) kan gebruiken in plaats van de correctie zoals jij die toepast. Bij ;2 worden de eerste dagen van week 1 aan week 53 toebedeeld. bij ;21 worden deze dagen als week 1 gezien.
    Ik dacht er gelijk even een verhaaltje op mijn (verouderde maar nog steeds veel bezochte) website over te schrijven. Bij het kijken of Google mijn post al had opgepikt kwam ik jouw pagina tegen.
    Mooi vak hebben we toch!

    groetjes Michiel,

    Een mede Excel nerd

    1. Hi Michiel, aka mede Excel nerd :-),

      Bedankt voor je leuke reactie! Ik was al op de hoogte van de nieuwe formule in Excel waarbij je kan kiezen op welke dag de de week moet beginnen (zo of ma), maar was had de blogpost nog niet aangepast ;-). Ik heb jouw site ook even bekeken – ziet er ook goed uit! Wie weet kan ik daar ook nog wat trucjes op doen.

      Groetjes,
      Annemarie

Geef een reactie

Het e-mailadres wordt niet gepubliceerd. Vereiste velden zijn gemarkeerd met *