Maandelijks archief: januari 2017

Groeperen in een draaitabel

Hoewel steeds meer Excel-gebruikers weten, dat draaitabellen ontzettend handig zijn bij de analyse van gegevens, het maken van overzichten etc. en deze mogelijkheden ook in hun dagelijkse werk gebruiken, is het groeperen binnen draaitabellen nog minder bekend: deze keer dus de focus op Groeperen in draaitabellen.

Oplettende lezers hebben ongetwijfeld gezien, dat ik dit in mijn vorige artikel (Top-2000 en draaitabellen) heb gebruikt zonder verdere uitleg.

Basis

Om een draaitabel te kunnen maken hebben we wat basisgegevens nodig. Ik heb in het Voorbeeldbestand op het tabblad Basis de fictieve productie van een simpele winkel gezet: een datum, het artikel en het verkochte aantal (soms dubbel).
Uiteraard (zie Kunst en Excel; 10 voordelen van Tabellen) zijn die gegevens in de vorm van een Tabel opgeslagen (met als naam tblVerkopen).
Ze waren eerst niet gesorteerd, maar om gemakkelijk de resultaten van de draaitabellen te beoordelen, heb ik dat nu wel even gedaan (klik op het vinkje achter Datum).

Overzicht per dag

Laten we snel even een overzicht maken van deze gegevens:

  1. Plaats de cursor ergens in de tabel tblVerkopen
  2. kies in de menutab Invoegen in het blok Tabellen de optie Draaitabel
  3. in het vervolgscherm staat alles goed (Excel heeft automatisch de hele tabel als bron gekozen en het resultaat moet op een nieuw werkblad komen), dus klik op OK
  4. op het nieuwe werkblad krijgt u aan de rechterkant de mogelijkheid om de draaitabel in te delen zoals u wilt: sleep Datum naar de Rijlabels, Art naar de Kolomlabels en Aantal naar het waardengebied
  5. eigenlijk is de draaitabel klaar, maar ik vind de standaard-layout niet handig, dus heb ik in de menutab Hulpmiddelen voor draaitabellen de tab Ontwerpen gekozen en bij Rapportindeling de optie Tabelweergave.

Het resultaat staat op het tabblad Draai van het Voorbeeldbestand: een overzicht van de totale verkoopaantallen per dag en gesplitst naar artikel.

Overzicht per maand

Vaak willen we in rapportages niet alle details zien, maar bijvoorbeeld in plaats van dag-resultaten de verkopen per maand.

In dit geval zou je de basisgegevens daartoe kunnen uitbreiden met een zogenaamde hulp-kolom waarin met behulp van de functie MAAND de maand wordt vastgelegd. Als we ook nog het jaar willen weten, moeten we nog een extra hulp-kolom toevoegen (met daarin de functie JAAR).

Maar die hulpkolommen zijn niet nodig:

  1. klik met de rechtermuisknop op één van de datums in de hiervoor gemaakte draaitabel
  2. het zogenaamde context-gevoelige submenu komt dan tevoorschijn. Kies daar de optie Groeperen
  3. Excel heeft de kolom, waarin geklikt is, geanalyseerd en heeft gezien dat dit allemaal datums zijn.
    We willen een maand-overzicht dus het voorstel van Excel kunnen we overnemen; klik op OK

LET OP als in de basisgegevens ergens in de kolom Datum een cel niet is gevuld of gevuld met een niet-bestaande datum of een getal of een tekst, dan zal het Groeperen-scherm er anders uitzien. De datum-opties zijn dan niet beschikbaar.
Nog erger: ook al corrigeert u de basisgegevens, Excel zal deze kolom nooit meer als een datum ‘zien’. Er zit dan niets anders op dan het werkblad met de draaitabel te verwijderen en het overzicht opnieuw te maken!

NB In het blok Lijst met draaitabelvelden aan de rechterkant van het scherm staat bij de te kiezen velden nog steeds Datum, maar Excel zal in de draaitabel alleen nog maar de maanden laten zien.

Overzicht per maand en jaar

Het overzicht per maand is in dit geval prima omdat Basis alleen maar datums uit 2016 bevat. Voegen we echter onderaan (of tussenin) de volgende regel toe:
1-1-2017, Servetten, 10
dan komen die in het Overzicht per maand uiteraard ook in de januari-regel tevoorschijn (wel even de draaitabel Vernieuwen door ergens rechts te klikken!).

We moeten zorgen dat de draaitabel ook per jaar groepeert:

  1. klik met de rechtermuisknop op één van de maanden in de datum-kolom van de draaitabel
  2. kies opnieuw Groeperen
  3. en klik nu ook de Jaren aan en OK

NB1 in de Lijst met draaitabelvelden is een er een nieuw veld bijgekomen, Jaren. Dit veld kunt u op dezelfde manier in de draaitabel plaatsen als andere velden. Bijvoorbeeld in het Rapportfilter zodat u overzichten per jaar kunt maken.

NB2 wanneer u een draaitabel maakt, dan legt Excel de brongegevens apart op een eigen manier in het geheugen vast. Zo ook de consequenties van groeperingen. Maakt u nu met dezelfde bron (in dit geval tblVerkopen) een andere draaitabel, dan gebruikt Excel, om geheugenruimte te sparen, dezelfde interne bron. Dus ook eventuele groeperingen zijn in de tweede draaitabel direct zichtbaar.

NB3 hebt u bij een analyse/overzicht in dezelfde draaitabel (of een andere; zie NB2) de datums zelf nodig, klik dan bij het Groeperen ook de Dagen aan.
Hebt u de dagen in het huidige overzicht niet (meer) nodig, verwijder dan het vinkje voor de Datum in Lijst met draaitabelvelden.

Overzicht per kwartaal

Ongetwijfeld hebt u ondertussen ook gezien dat u op deze manier heel gemakkelijk kwartaaloverzichten kunt maken.
In het overzicht hiernaast is de groepering per kwartaal aangezet (zoals te zien is heb ik ook de 2017-regel toegevoegd en de draaitabel vernieuwd).
Nog een paar kleine aanpassingen:

  1. met de rechtermuisknop klikken op een jaar en dan Subtotaal Jaren aanvinken
  2. met de rechtermuisknop klikken op een kwartaal en dan Subtotaal Kwartalen aanvinken
  3. de eerste drie kwartalen ‘dichtklappen’ door op het min-teken vóór het betreffende kwartaal te klikken
  4. grafiek nodig? Klik ergens in de draaitabel, kies het tabblad Opties in de menutab Hulpmiddelen voor draaitabellen en binnen het blok Extra de optie Draaigrafiek. We willen een Kolomgrafiek, dus het klikken op OK volstaat.

 

Artikelen groeperen

Voordat we verder gaan is het het makkelijkst om de draaitabel op het tabblad Draai van het Voorbeeldbestand eerst aan te passen:

  1. klik het vinkje voor Datum weg in de Lijst met draaitabelvelden
  2. verplaats Art van Kolomlabels naar Rijlabels

Zoals we gezien hebben weet Excel bij datums hoe hij kan groeperen; bij teksten is dat natuurlijk een stuk lastiger.
Daarom gaan we als volgt te werk:

  1. selecteer met de muis, die cellen die u wilt groeperen. In dit geval liggen die niet tegen elkaar, dus moeten we de Ctrl-toets gebruiken.
    Klik op Broodrooster
  2. hou Ctrl ingedrukt en klik op Strijkijzer
  3. klik met de rechtermuisknop op één van die 2 artikelen en kies Groeperen
  4. doe hetzelfde met Servetten en Tafelkleed
  5. verander de nieuwe Groep1 in Hard door de tekst ‘er overheen’ te tikken
  6. Groep2 wordt op die manier Soft
  7. de nieuwe kolomkop Art2 wordt ArtGroep
  8. zet nog de subtotalen voor ArtGroep aan

Nu weer een kwartaaloverzicht maken:

  1. klik het vinkje voor Art weg
  2. verplaats ArtGroep van Rijlabels naar Kolomlabels
  3. plaats Kwartalen en Jaren in de juiste volgorde in Rijlabels

Getallen groeperen

Om dit toe te lichten maken we een nieuwe draaitabel:

  1. plaats de cursor ergens in de tabel tblVerkopen
  2. kies in de menutab Invoegen in het blok Tabellen de optie Draaitabel
  3. in het vervolgscherm staat alles goed (Excel heeft automatisch de hele tabel als bron gekozen en het resultaat moet op een nieuw werkblad komen), dus klik op OK
  4. op het nieuwe werkblad krijgt u aan de rechterkant de mogelijkheid om de draaitabel in te delen zoals u wilt: sleep Aantal naar de Rijlabels, Art naar de Kolomlabels en Datum naar het waardengebied

We zien op die manier het aantal keren, dat een bepaald aantal verkopen voorkomt, ook nog gesplitst naar artikel.

LET OP Aantal van Datum is wat misleidend: we turven niet het aantal dagen, maar het aantal keer dat het veld Datum gevuld is. We hadden ook Aantal van Art of zelfs Aantal van Aantal kunnen kiezen.

NB de basis-gegevens zijn random gevuld. Je zou dus overal exact 50 verwachten. Als ik 100.000 records had aangemaakt zou de spreiding ook minder groot zijn geweest!

Maar nu groeperen:

  1. klik met de rechtermuisknop op één van de cijfers 2, 3, 4, 5 of 6 en kies Groeperen
  2. we krijgen nu een heel ander groepeer-scherm; we kunnen een eigen indeling maken.
    Verander de 2 in 1 en de 1 achter Op: in 3.
    Ofetwel begin bij 1 en groepeer in blokken van 3.
  3. klik op OK.

Als we ook nog de artikelen groeperen naar Hard en Soft dan krijgen we het volgende overzicht:


Wil je op de hoogte gebracht worden, wanneer er weer een nieuw item aan Tips & trucs is toegevoegd:

Top2000 en draaitabellen

Eind 2016 stond voor menigeen, op muziekgebied, weer in het teken van de jaarlijkse Top-2000; één van de (Nederlandse) tradities naast Kerstmis, Oud en Nieuw, oliebollen etc.

Bovenaan de ranglijst is het normaal gesproken niet echt spannend; de top-3 ligt wel ongeveer vast. De vraag was of de vele in 2016 gestorven pop-helden daar verandering in zou brengen. Niet dus.

Tijd om wat overzichten te maken; uiteraard gebruiken we daar draaitabellen voor!

Basis-materiaal

De Top-2000 wordt uitgezonden door Radio-2; op de website daarvan is dan ook  altijd de rangschikking van het laatste jaar te vinden. Door nog wat verder te googelen heb ik ook de standen vanaf het begin van de Top-2000 kunnen vinden; de eerste uitzending was eind december 1999.

Wat heb ik vastgelegd? Per nummer het jaar van uitzenden, de notering/positie in dat jaar, de naam van de artiest/groep, de titel van het nummer en het jaar dat het nummer gepubliceerd/uitgebracht is (zie het tabblad Basis in het Voorbeeldbestand).

Alle gegevens heb ik in de vorm van een Excel-tabel vastgelegd met de naam tblTop2000 (heb je een aaneengesloten gebied met gevulde cellen, plaats de cursor ergens in dat gebied en kies in de menutab Invoegen de optie Tabel; kies dan in de nieuwe menutab Hulpmiddelen voor tabellen de optie Ontwerpen en wijzig de naam).
Eind 2017 zal de traditie wel worden voortgezet: kopieer de nieuwe gegevens en voeg deze onderaan de tabel toe. Nog even sorteren en alles staat klaar:

  1. plaats de cursor ergens in de tabel
  2. kies in de menutab Gegevens in de groep Sorteren en filteren de optie Sorteren
  3. in het tussenscherm staan de juiste instellingen al klaar:
    Klik dus op OK.

NB hebt u nieuwe data toegevoegd, vergeet dan niet de draaitabellen te Vernieuwen, anders worden de nieuwe gegevens niet meegenomen in de overzichten. Eén draaitabel vernieuwen is voldoende; aangezien alle overzichten aan dezelfde basis zijn gekoppeld worden dan alle draaitabellen bijgewerkt.

Filteren

Om de resultaten van een artiest te bekijken kunnen we de filter-eigenschappen van Excel gebruiken.
Aangezien het basis-materiaal als een tabel is opgeslagen krijgen we filter-opties er ‘gratis’ bij (zie de  achter iedere kolom-kop).
Wanneer we bijvoorbeeld alle noteringen van de Eagles willen weten:

  1. klik op het  achter Artiest
  2. zoals u ziet kunt u daar ook een sortering aanbrengen, maar we zijn nu meer geïnteresseerd in de filtermogelijkheden.
    Excel heeft ontdekt dat de kolom tekst bevat, dus we krijgen tekstfilters aangeboden.
    Onder het invulvak staan standaard alle mogelijkheden waaruit u kunt kiezen; u zou dus naar de Eagles kunnen scrollen (of eerst een E intikken en dan verder scrollen), maar dat is nogal tijdrovend. In plaats daarvan tikken we in het invulvak eagles (het zoeken is niet hoofdletter-gevoelig) en Excel geeft ons alleen die opties, die ergens het woord eagles bevatten.
  3. Ik bedoelde niet de Eagles of Death Metal, dus die vinken we uit en klikken op OK
  4. en we hebben het gewenste overzicht:

    Eventueel nog sorteren op Titel om het verloop in de jaren per nummer makkelijk te kunnen volgen.

Overzicht per artiest

We gaan eens analyseren hoe de diverse artiesten het in de loop van de tijd hebben gedaan:

  1. klik ergens in de tabel tblTop2000 in het tabblad Basis van het Voorbeeldbestand
  2. kies in de menutab Invoegen de optie Draaitabel en klik op OK
  3. sleep de velden UitzendJr, Artiest en Titel naar de gewenste plaats in de tabel zoals hiernaast weergegeven.
    In de rijen zal Excel nu alle (unieke!) artiesten tonen en in de kolommen komen de (12) verschillende uitzendjaren.
    In het waarden-gebied hebben we de Titel geplaatst; Excel weet dat dit een tekst is, dus zal direct het aantal voorkomens van een artiest in een jaar turven.
    NB Hadden we een numeriek-veld in het waarden-gebied geplaatst dan bepaalt Excel standaard de som van die waarden.
  4. kies in de nieuwe menutab Hulpmiddelen voor draaitabellen het tabblad Ontwerpen en dan bij Rapportindeling de optie Tabelweergave
  5. nog even een sortering aanpassen: kies het ‘vinkje’ achter UitzendJr en sorteer van hoog naar laag

Het resultaat is terug te vinden in het tabblad ovzArtiest van het Voorbeeldbestand:

Wil je weten welke 3 nummers van 10CC er in 2016 in de lijst stonden en op welke plaats: dubbelklik op de 3 in cel B5 en er verschijnt een nieuw tabblad met de drie betreffende records.
Dubbelklikken op de 101 zal alle noteringen van 10CC in de loop der jaren laten zien.

Welke artiest/groep heeft de meeste noteringen?

  1. klik ergens in de 2016-kolom, bijvoorbeeld cel B5
  2. kies in de menutab Gegevens in het blok Sorteren en filteren de optie .
  3. wil je de stand in een ander jaar weten? Herhaal de procedure met de cursor in een andere kolom.
  4. Hetzelfde als u de stand over alle jaren wilt weten: plaats de cursor in de laatste kolom en sorteer.

Overzicht per artiest en titel

Met nog een paar aanpassingen aan de draaitabel ontstaat een totaaloverzicht van de hele Top-2000 over alle jaren:

  1. Voeg nogmaals de Titel toe aan de Lijst met draaitabelvelden; deze keer in het Rijlabels-gebied.
    Let op zorg dat de titel ONDER de artiest komt! Probeer maar uit wat het verschil is.
  2. in het waarden-gebied halen we de Titel weg (versleep buiten het keuzegebied) en plaatsen daar de Notering.
    Aangezien dit een numeriek veld is stelt Excel voor om dan de Som daarvan te pakken. Aangezien iedere artiest/titel-combinatie in ieder uitzendjaar maar één keer voorkomt, zal Excel dus ‘gewoon’ de notering laten zien.

Een voorbeeld kunt u vinden in het tabblad ovzArtTitel van het Voorbeeldbestand.

NB uit het voorbeeld hier rechts mag blijken dat in het basis-materiaal de titels niet consistent gevuld zijn. Ook bij de artiesten was dit het geval; deze heb ik handmatig zo goed mogelijk ‘opgeschoond’.

Noteringen in de loop van de tijd

In het tabblad ovzJrVerloop van het Voorbeeldbestand ziet u een andere mogelijkheid om het verloop in de tijd te volgen:
De Artiest hebben we in het Rapportfilter geplaatst.

De titel heeft een ‘vreemde’ sortering gekregen:

  1. plaats de cursor in één van de titels
  2. klik in die cel met de rechter-muisknop en kies binnen de optie Sorteren het item Meer sorteeropties
  3. in het vervolgscherm kiezen we dan een oplopende sortering; maar niet op Titel maar op Som van Notering

NB1 omdat Excel naar het totaal over alle jaren heen kijkt, is de sortering niet vlekkeloos.

NB2 Ook door de inconsistente vulling van de database levert dit niet altijd het verwachte/gewenste resultaat op; kijk bijvoorbeeld wat er gebeurt als je ABBA kiest.

Grafiek

Wanneer we van de draaitabel een grafiek maken levert dit nog meer inzicht op in het verloop van de noteringen in de tijd (zie tabblad ovzJrVerloopGraf):

  1. klik ergens in de draaitabel
  2. kies in de menutab Hulpmiddelen voor draaitabellen de tab Opties
  3. in het blokje Extra vindt u de Draaigrafiek
  4. na de keuze van het gewenste type komt de grafiek op hetzelfde tabblad waar ook de draaitabel staat; kies  en zet de grafiek op een Nieuw blad
  5. aangezien de grafiek aan de draaitabel gekoppeld is kun je hier direct een andere artiest-keuze maken. Deze keuze wordt dan direct ook in de gekoppelde draaitabel toegepast, inclusief sorteringen.

Publicatiejaar

Ik was ook geïnteresseerd naar het ‘nostalgische’ karakter van de Top2000; ofwel uit welke jaren komen de meeste nummers?

Uit de grafiek over het uitzendjaar 1999 blijkt dat toen het zwaartepunt lag bij muziek uit de eind-60- en eind-70-jaren.
Wanneer we dit vergelijken met de grafiek over 2016 dan zien we dat er een veel gelijkmatigere verdeling over de tijd is! De Top-2000 is niet alleen meer voor ‘ouwe knarren’?

 

Hoe is deze grafiek gemaakt?

  1. de basis is weer een draaitabel.
    In het Rapportfilter is het UitzendJr geplaatst, waarmee we de hoofdselectie maken.
    Het PublJr plaatsen we in de rijen en we turven het aantal door de Titel in het waarden-gebied te laten ‘vallen’.
  2. daarna is van deze draaitabel een draaigrafiek gemaakt; klaar!

In het tabblad ovzPublJr van het Voorbeeldbestand is het resultaat terug te vinden.

Slicers

Om de keuze van het uitzendjaar makkelijker te kunnen maken is een slicer toegevoegd aan de draaitabel:

  1. klik ergens in de draaitabel
  2. kies in de menutab Hulpmiddelen voor draaitabellen de tab Opties
  3. kies in het blokje Sorteren en filteren de optie Slicer invoegen
  4. in het vervolgscherm kiezen we in dit geval de optie UitzendJr
  5. regel nog wat opmaak via de Opties in Hulpmiddelen voor Slicers

Wil je op de hoogte gebracht worden, wanneer er weer een nieuw item aan Tips & trucs is toegevoegd: