Categorie archief: Excel

Groeperen in een draaitabel

Excel, ,

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:

Top2000 en draaitabellen

Excel, , , ,
Voorbeeld bijgewerkt t/m 2020: Top2000_2020Download

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

Hoog-Laag-Slot-grafiek

Excel, , , , ,

Binnenkort (nou ja, over een paar maanden) hebben we weer verkiezingen. In de aanloop daar naartoe zien we in de media steeds vaker de resultaten van diverse peilingen.

Op de site peilingwijzer.tomlouwerse.nl is het meest recente resultaat van een wetenschappelijk onderbouwde combinatie van 5 peilers (nee Maurice, geen pijlers!) te vinden.

Een grafiek op die site laat van 13 politieke partijen het verloop van de peilingen in de tijd zien; naast het gemiddelde van de 5 peilers zien we in de grafiek ook een 95%-onzekerheidsmarge (het licht gekleurde gebied).

Hieronder laat ik zien, hoe je in Excel een dergelijke grafiek kunt maken; we gebruiken daar een hoog-laag-slot-grafiek voor.
Daarbij moet wel aangetekend worden, dat de grafiek op de site van Tom Louwerse veel meer interactieve elementen bevat!

Basisgegevens

Zoals voor iedere grafiek hebben we basisgegevens nodig; deze zijn in dit geval op te halen vanaf de download-pagina van de Peilingwijzer.

In het Voorbeeldbestand heb ik op het tabblad Peilingwijzer deze gegevens geplaatst (op dit moment van 12 september 2012 tot en met 27 november 2016); zoals in eerdere voorbeelden ‘gieten’ we deze in de vorm van een tabel (Invoegen/Tabel, kopregels aanvinken) en geven die tabel de naam tblpeilingWijzer.

NB1 komen er nieuwe peiling-data beschikbaar, voeg deze dan onderaan de tabel als nieuwe regels toe.

NB2 komen er nieuwe partijen in het overzicht bij, voeg deze dan rechts aan de tabel als nieuwe kolommen toe.

Parameters

Hierna gaan we een grafiek maken van de peiling-resultaten. Hierbij kan echter maar één partij tegelijkertijd worden weergegeven. Om de invoer daarvan straks te vergemakkelijken leggen we de keuzemogelijkheden in een aparte tabel vast (zie tabblad Param in het Voorbeeldbestand).

NB wordt er in de peilingen een extra politieke partij meegenomen, dan moet deze onderaan toegevoegd worden; aangezien het ook hier een tabel betreft, zal Excel de nieuwe regel automatisch in tblPartijen meenemen.

Het is ook wel handig om te weten wat de eerste en laatste datum is, die in het peilingoverzicht voorkomt.
In cel E2 van het tabblad Param uit het Voorbeeldbestand bepalen we de eerste/kleinste datum door het minimum van die kolom op te zoeken.
Normaal gesproken zou dat kunnen met de formule =MIN(tblPeilingWijzer[Datum]), maar helaas levert dat de waarde 0 op. Dat komt omdat in de datum-kolom geen echte datum staat maar een tekst, dus het wordt wat ingewikkelder:
{=MIN(DATUM(
LINKS(tblPeilingWijzer[Datum];4);
DEEL(tblPeilingWijzer[Datum];6;2);
RECHTS(tblPeilingWijzer[Datum];2)))}

Met behulp van de functie LINKS zoeken we het jaar op, DEEL haalt 2 tekens op vanaf positie 6 (de maand dus) en RECHTS levert de dag. Deze 3 resultaten geven we door aan de functie DATUM, die er een datum van maakt. De functie MIN zoekt dan de kleinste datum op.

LET OP de 3 tekst-functies kijken eigenlijk alleen naar de datum uit de corresponderende rij (de 2e dus). Door na het intikken van de formule niet op Enter te drukken maar op Ctrl-Shift-Enter wordt het een zogenaamde array- of matrix-formule en worden de 3 tekst-functies op alle datums ‘losgelaten’. Vaak wordt dit ook CSE-invoer genoemd; Excel plaatst automatisch accolades om de formule (niet handmatig intypen!).

De formule in E3 mag dan geen verrassingen meer bevatten.

Voor het gemak hebben we de cellen E2 en E3 een naam gegeven MinDatum, resp. MaxDatum.

Grafiek met onzekerheidsmarge

Voordat we een dergelijke grafiek kunnen maken moeten we eerst een methode hebben om de gegevens van één partij uit de basis te destilleren.

Cel C3 (met de naam invPartij) in het tabblad Ovz van het Voorbeeldbestand gebruiken we als keuzevak:

  1. kies in de menutab Gegevens de optie Gegevensvalidatie
  2. in het pop-up-scherm kiezen we bij Toestaan: de optie Lijst
  3. bij Bron: zouden we alle keuzemogelijkheden kunnen intikken gescheiden door een ; (punt-komma), dus bijvoorbeeld VVD;PvdA;GL etc.
    Maar dat is niet zo handig. Die lijst kunnen we ook uit het tabblad Param halen: kolom Partijen in de tabel tblPartijen.
    Helaas kan gegevensvalidatie niet goed omgaan met de nieuwere tabellen (geïntroduceerd in versie 2007), dus de formule =tblPartijen([Partijen]) werkt niet, maar wel als we de functie INDIRECT gebruiken (zie ook het artikel Tabellen (deel 2); denk aan de aanhalingstekens!!).
  4. Eventueel nog een Invoerbericht en/of Foutmelding toevoegen (zie Voorbeeldbestand) en we zijn klaar: klik op OK.

De voorbereidingen zijn klaar, nu nog de gegevens ophalen van de gekozen partij:

  1. in rij 5 van het tabblad Ovz van het Voorbeeldbestand staat een kopregel
  2. vanaf B6 naar beneden staan alle datums, waarvoor er peilingen zijn
  3. in cel C6 moet de eerste peiling van de gekozen partij komen:
    =INDEX(
    INDIRECT(“tblPeilingWijzer[“&invPartij&”]”);
    VERGELIJKEN($B6;tblPeilingWijzer[Datum];0))
    Weet u niet (meer) hoe de functie INDEX werkt? Kijk in het artikel Zoeken: Index en Vergelijken; daar vindt u ook een truc hoe u de de functie makkelijk implementeert.
    Weer gebruiken we INDIRECT om de kolom, waarin gezocht moet worden, afhankelijk te maken van de cel C3 (met de naam invPartij).
  4. de formules in D6 en E6 zijn vergelijkbaar, alleen worden daar de partij-gegevens uit de low- en high-kolom opgehaald.
  5. de drie formules uit C6:E6 worden naar beneden gekopieerd, zodat bij iedere datum de gegevens tevoorschijn komen.
  6. via de menutab Invoegen en de optie Tabel maken we van dit overzicht een tabel (met de naam tblGrafBasis). Wanneer er nieuwe peilingen beschikbaar zijn is het dan voldoende om onderaan de datum toe te voegen. Alle formules worden dan automatisch door Excel ingevuld.

En nu de grafiek:

  1. plaats de cursor ergens in tblGrafBasis
  2. in de menutab Invoegen kiest u in het blok Grafieken de optie Overige grafieken.
    In het uitklapmenu nemen we de eerste grafieksoort in het blokje Hoog/Laag/Slot.
    Dit soort grafieken wordt vaak in de financiële wereld gebruikt om een overzicht van de dagkoersen weer te geven: per dag ziet u dan de hoogste, laagste en slotkoers.
    NB Excel beoordeelt automatisch in welke kolom de hoge, lage of slot-waarde (of in ons geval het gemiddelde) staat.
  3. Nog even wat opmaak regelen:
    * kies in de nieuwe menutab Hulpmiddelen voor grafieken het tabblad Indeling
    * kies in het blokje Huidige selectie de Reeks Gemiddeld 
    * daarna de optie Selectie opmaken
    * en kies bij Lijnkleur een gewenste kleur
    * kies in het blokje Huidige selectie de Hoog/laag-lijnen
    * daarna de optie Selectie opmaken
    * en kies bij Lijnkleur dezelfde kleur, maar met een Transparantie van 75%
    * kies in het blokje Huidige selectie de Reeks Hoog
    * daarna de optie Selectie opmaken
    * en kies bij Markeringsopties Geen
    * Grafiektitel laten verwijzen naar cel E3
    * aan de linker-as een Titel toevoegen, de notatie wijzigen in een percentage zonder decimalen en zorgen dat er het bereik altijd van 0% tot 30% loopt.

Wijzig cel C3 en bekijk het resultaat!

Grafiek zonder nullen

Excel,

wegen-naar-romeIk kreeg deze week van Hasan de volgende vraag in mijn mailbox:

Ik ben bezig met een lijngrafiek waarbij de waarde nul zichtbaar is in mijn grafiek. Dat wil ik niet. Hoe kan ik dat oplossen?

Meestal leiden er meerdere wegen naar Rome, maar hiervoor kan ik toch maar één oplossing bedenken. Kent iemand nog een alternatief?

Aan de hand van het (vereenvoudigde) voorbeeld van Hasan zal ik laten zien hoe de oplossing werkt.

Prognose versus realisatie

grafnulHasan heeft een overzicht per week (en maand en kwartaal) van budgetcijfers, prognoses en realisaties. Ik zal verder werken met alleen prognoses en realisaties (zie het tabblad Basis in het Voorbeeldbestand).

Zoals te zien is, kennen we de realisatie tot en met week 7; maar zit deze nou boven of onder de prognose?
grafnul2
Misschien dat een grafische weergave meer inzicht geeft?

Tsja, dat helpt nog niet echt veel. Nee, we ontkomen er niet aan om naast bovenstaande cijfers ook met cumulatieven vanaf week 1 te gaan werken.

Prognose versus realisatie, incl. cumulatieven

grafnul3Twee extra kolommen nemen de cumulatieve cijfers voor hun rekening (zie tabblad Cum1 in het Voorbeeldbestand):

  1. in cel E3 komt dezelfde waarde als in C3
  2. zoals te zien is, staat in E4 de formule
    =E3+C4
    ofwel: neem de vorige cumulatieve prognose (E3) en tel daar de prognose van de betreffende week bij op (C4)
  3. deze formule wordt naar beneden gekopieerd
  4. hetzelfde gebeurt in kolom F voor de realisatie

grafnul4Deze 2 kolommen aan de grafiek toevoegen; duidelijk (?) is te zien dat de totale realisatie (de paarse lijn) achter blijft bij de cumulatieve prognose (blauwe lijn).

Maar we kennen de realisatie t/m week 7 al; waarom dan nog met de prognose van die eerste weken rekenen om tot een cumulatieve prognose te komen? Zo is ook de cumulatieve realisatie na week 7 niet interessant.

Prognose versus realisatie, incl. cumulatieven (deel 2)

grafnul5Daarom een nieuwe opzet gemaakt voor de cumulatieve kolommen (zie tabblad Cum2 in het Voorbeeldbestand):

  1. allereerst maken we het ons gemakkelijk en plaatsen we in cel D2 het laatste weeknummer waarvan we de realisatie kennen
  2. in cel E5 komt de eerste ‘cumulatieve’ prognose; deze is  afhankelijk van D2. Als er nog geen realisatie is  (D2<B5) dan is de cumulatieve prognose gelijk aan de prognose van de eerste week (C5); is de eerste week afgesloten (D2=B5) dan is de cumulatieve prognose gelijk aan de (cumulatieve) realisatie (F5; is gelijk aan D5) en anders doet de cum. prognose er niet toe en maken we die gelijk aan 0.
  3. de cumulatieve prognose van de tweede week komt in cel E6:
    =ALS(B6=$D$2;F6;ALS(B6>$D$2;E5+C6;0))
    Als het de laatst gerealiseerde week betreft (B6=$D$2) dan komt hier de waarde van de cumulatieve realisatie (F6). Als het weeknummer groter is dan de afgesloten week (B6>$D$2)dan is de cumulatieve prognose gelijk aan de vorige (E5) plus de prognose van deze week (C6) en anders doet de prognose er niet meer toe (dus 0).
  4. de formule in E6 kan naar beneden gekopieerd worden (vandaar dat D2 ‘absoluut’ is gemaakt mbv de $-tekens).
  5. nu nog de cumulatieve realisatie: in cel F5 komt de formule
    =ALS($D$2>=B5;D5;0)
    Dus als de gerealiseerde week (D2) groter of gelijk is aan de eerste week (B5) dan wordt de eerste cumulatieve realisatie gelijk aan de eerste realisatie (D5) en anders 0.
  6. in F6 komt de tweede cumulatieve realisatie:
    =ALS($D$2>=B6;F5+D6;0)
    Als de gerealiseerde week (D2) groter of gelijk is aan de betreffende week (B6) dan wordt de cumulatieve realisatie gelijk aan de vorige cumulatiieve realisatie (F5) plus de realisatie van deze week (D6) en anders 0.
  7. ook deze formule kan naar beneden worden gekopieerd.

grafnul6De bijbehorende grafiek laat het resultaat zien, wanneer de realisatie van week 7 bekend is (de weekcijfers horen bij de linker (primaire) as, de cumulatieven bij de rechter (secundaire) as).

Hier wordt het probleem van Hasan zichtbaar: door de nullen in de diverse formules zien we een vreemde overgang van week 6 naar 7 (bij de cumulatieve prognose) en van week 7 naar 8 (bij de realisatie). Ook het wijzigen van de 0 in de formules in dubbele aanhalingstekens levert geen oplossing; Excel interpreteert dit in de grafiek als nul-waarden.

Prognose versus realisatie, grafiek zonder nullen

De enige oplossing (die ik kan bedenken) is om de nullen niet te vervangen door iets leegs (de dubbele aanhalingstekens) maar door de functie NB(). Deze geeft aan dat de waarde Niet Bekend is en deze worden in de grafiek niet weergegeven (zie tabblad Cum3 in het Voorbeeldbestand).

grafnul7

Dat ziet er beter uit:

  1. De cumulatieve lijn loopt netjes door.
  2. De grafiek heeft een dynamische titel; gekoppeld aan cel F2.
  3. Kleuren zijn aan elkaar aangepast.
  4. De secundaire as heeft een zodanige vaste verdeling gekregen (een maximum van 2500), dat de horizontale lijnen én links én rechts toegepast kunnen worden.

NB Is niet alleen de grafiek van belang maar wordt ook de tabel gebruikt in rapportages, zorg dan dat de #N/B’s niet zichtbaar zijn; dat kan eenvoudig met een voorwaardelijke opmaak.

Zoeken: Index en Vergelijken

Excel, , , ,

vertzoekenOp ieder Excel-forum zie je regelmatig de opmerking, dat het gebruik van de standaard-functie VERT.ZOEKEN (verticaal zoeken) niet flexibel is en foutgevoelig (en dat geldt ook voor de horizontale variant). Meestal wordt er dan als alternatief verwezen naar het gebruik van INDEX, gecombineerd met VERGELIJKEN.
Ik heb daar al eens eerder een artikel aan gewijd (Alternatief voor verticaal zoeken).

In de praktijk zie ik, dat er toch vaak met VERT.ZOEKEN wordt gewerkt, zo nodig met behulp van allerlei hulpkolommen. Bijna altijd met het argument: “Ik weet niet meer hoe ik die 2 functies moet combineren.”

Ik kwam pas een handige truc tegen, waardoor het invoeren van deze gecombineerde functies werkelijk een fluitje van een cent wordt.
Hierna zal ik deze tip toelichten, maar eerst laat ik (nogmaals) zien dat de combinatie van INDEX en VERGELIJKEN echt beter is dan VERT.ZOEKEN.

Voorbeeld van VERT.ZOEKEN

vertzoeken1Hiernaast zie je een simpel voorbeeld van een werknemersadministratie: naast een personeelsnummer staat de naam en de afdeling.

Wanneer we een personeelsnummer kennen zien we in één oogopslag welke persoon daar bij hoort. Zou het bestand veel groter zijn (en niet gesorteerd op nummer), dan kan het zoeken wat lastiger worden. Daar komen de Excel-zoekfuncties om de hoek kijken. In het Voorbeeldbestand is in het tabblad VZoeken het bestandje opgenomen.

vertzoeken2In cel F3 staat het personeelsnummer waarvan we de naam en afdeling willen weten. In G3 staat de formule
=VERT.ZOEKEN(F3;B3:D7;2;ONWAAR)
Ofwel: zoek de waarde van F3 op in de eerste kolom (VERTicaal) van het bereik B3:D7 en geef de corresponderende waarde uit de 2e kolom van dat bereik terug. De zoekwaarde moet exact overeenkomen vandaar dat bij de laatste parameter/argument van de functie (Benaderen) ONWAAR is ingevuld.

Wijzig de waarde in F3 en de corresponderende naam wordt opgehaald; net als de betreffende afdeling in cel H3, waar de gegevens uit de 3e kolom worden uitgelezen.

NB1 als het nummer niet in het bestand voorkomt zal de functie #N/B (niet beschikbaar) opleveren.

NB2 de Excel zoek-functie zoekt de waarde van het eerste argument altijd in de eerste kolom van het bereik, dat opgegeven is in het tweede argument. Vandaar dat je niet ‘naar links’ kunt zoeken. Dus als je een naam weet kun je op deze manier niet het nummer opzoeken.

NB3 voeg tussen de kolommen C en D een nieuwe kolom in en kijk wat er met het resultaat van het zoeken gebeurt!

NB4 voeg een nieuwe werknemer onderaan toe, plaats het personeelsnummer in cel F3. Wat gebeurt er?

Index en Vergelijken (deel 1)

indverg1In het tabblad IndVerg van het Voorbeeldbestand is gebruik gemaakt van INDEX en VERGELIJKEN.

Hoe werkt dit?

  1. allereerst moet de zoekwaarde (in dit geval cel F3) vergeleken worden met de kolom met personeelsnummers (B3:B7).  Het resultaat is de positie van de zoekwaarde in het bereik (in dit geval 1; zie cel J3).
    Met de derde parameter van de functie VERGELIJKEN kunnen we aangeven welk soort resultaat er moet worden opgeleverd; met 0 geven we aan, dat er een exacte match moet zijn.
  2. de zo gevonden positie wordt gebruikt in INDEX om een resultaat op te halen in de kolom met namen (C3:C7).

Op deze manier is het probleem van de NB3 van hierboven in ieder geval opgelost.

indverg2Ook de NB2 kunnen we hiermee omzeilen: in cel G7 zoeken we het nummer op in B3:B7, dat hoort bij de naam  in cel F7 door die waarde te vergelijken met de inhoud van C3:C7.

NB5 de functie VERGELIJKEN levert altijd maar 1 resultaat terug; de eerste die aan de voorwaarde voldoet. Een oplossing hiervoor wordt verderop uitgewerkt.

Index en Vergelijken (deel 2)

Ook de NB3 moeten we nog oplossen: als er werknemers bij komen moeten de formules blijven werken.

indverg3Het eerste wat we doen is van het personeelsbestand een tabel maken:

  1. klik op één van de cellen in het bestand
  2. kies in de menutab Invoegen de optie Tabel; zorg dat het vinkje aan staat bij De tabel bevat kopteksten.
  3. de tabel wordt automatisch van een ‘zebra’-opmaak voorzien. indverg4In het Voorbeeldbestand heeft de tabel op het tabblad IndVerg2 de naam tblWerknemers gekregen (klik op Ontwerpen in de nieuwe menutab Hulpmiddelen voor tabellen en wijzig de standaardnaam).

indverg5In cel G3 gaan we opnieuw de alternatieve opzoek-formule maken om de naam bij een nummer te vinden:

  1. tik in: =index(
  2. maar in plaats van het intikken van C3:C7 (het bereik met namen) wijzen we met de cursor de bovenrand van cel C2 aan; de cursor wordt dan een zwarte pijl naar beneden. Op dat moment 1 keer klikken en de formule wordt aangevuld met tblWerknemers[Naam] (ofwel de kolom Naam uit de tabel tblWerknemers).
  3. de rest mag dan duidelijk zijn: tik in ;vergelijken( en klik op cel F3
  4. tik in ; en klik op de bovenrand van cel B2; de formule wordt aangevuld met tblWerknemers[Nr]
  5. tik in ;0))
  6. druk op Enter

Voeg een nieuwe werknemer toe en kijk of de formules nog werken.

Index en Vergelijken (deel 3)

Het blijkt niet zo makkelijk om te onthouden hoe de combinatie van deze functies moet worden ingevoerd. Maar pas kwam ik een handige truc tegen (zie Wyn Hopkins op LinkedIn): gebruik de auto-correctie-mogelijkheden van Office! En dit kan natuurlijk met iedere complexe of anderszins moeilijk te onthouden functie(-combinatie).

  1. indverg6Kies in Excel de menutab Bestand
  2. klik op Opties en dan op Controle.
  3. klik op de button AutoCorrectie-opties
  4. in het nieuwe scherm vullen we onder Vervangen: een afkorting in die we hierna zullen gebruiken, bijvoorbeeld avz (Alternatief Voor Zoeken).
    NB werkt u bij of voor de firma Aluminium Verwerking Zuid dan zult u een andere, niet gebruikte afkorting moeten nemen.
  5. onder Door: komt de formule
    =INDEX(Stap3_Waar_is_het_resultaat?;VERGELIJKEN(Stap1_Wat_zoeken?;Stap2_Waar_zoeken?;0))
  6. Kies Toevoegen en OK

Nu gaan we deze afkorting gebruiken:

  1. open het tabblad IndVerg3 in het Voorbeeldbestand
  2. klik in cel G3; de formule die daar staat gaan we opnieuw opbouwen
  3. tik in avz (of een andere afkorting die bij Vervangen: is ingevuld) en voeg nog een spatie toe
  4. de auto-correctie doet zijn werk en in plaats van avz staat er nu onze index/vergelijken-formule!
  5. dubbelklik op Stap1 (door de underscores wordt alles tot aan de ; geselecteerd) en klik op cel F3
  6. dubbelklik op Stap2 en klik op de bovenrand van cel B2
  7. dubbelklik op Stap3 en klik op de bovenrand van cel C2

Makkelijk toch? Er is nu geen enkele reden meer om VERT.ZOEKEN (of HORIZ.ZOEKEN) te blijven gebruiken.

Index en Vergelijken (multi-criteria)

In ons voorbeeldbestand komen twee mensen voor met de naam Jansen. Excel levert ons bij het zoeken altijd de eerste (zie cel I3 in het tabblad IndVergMulti van het Voorbeeldbestand).
Willen we zeker weten dat we de juiste persoon ophalen dan zullen we nog meer eigenschappen er bij moeten betrekken om zo de zoekopdracht uniek te maken, bijvoorbeeld de geboortedatum.

NB voordat u in Excel een zoek-optie implementeert, moet u zeker weten op welke manier u iemand of iets uniek kunt identificeren; misschien is naam en geboortedatum niet genoeg.

Voordat we de multi-criteria-formule gaan implementeren moeten we eerst kijken naar een alternatieve implementatie van VERGELIJKEN.
In het tabblad IndVergMulti in cel L4 plaatsen we de volgende formule:

=VERGELIJKEN(WAAR;tblWerknemers3[Naam]=G4;0)

In plaats van het vergelijken van een zoekwaarde (G4) met een bepaald bereik (tblWerknemers3[Naam]) vergelijken we hier de zoekwaarde WAAR met een reeks uitkomsten, die ontstaat door te controleren of de cellen in de kolom Naam gelijk zijn aan G4.

NB wilt u weten hoe een formule intern de tussenstappen berekent, kies dan in de menutab Formules de optie Formule evalueren.

Excel moet wel weten dat G4 met ALLE cellen uit de kolom Naam moet worden vergeleken; daarom moet deze formule als een zogenaamde array-formule worden ingevoerd door niet gewoon op Enter te drukken maar op Ctrl-Shift-Enter (soms wordt dit dan ook de CSE-methode genoemd).
Excel plaatst automatisch accolades om de formule bij CSE; dit is niet hetzelfde als wanneer er handmatig accolades geplaatst worden!!

Maar nu multicriteria: de vergelijking van G4 met de cellen van de kolom Naam levert een reeks op met daarin WAAR of ONWAAR; intern Excel is dit een 1 , respectievelijk 0.
Door deze reeks WAAR/ONWAAR (ofwel 1 en 0) te vermenigvuldigen met een andere reeks, die ontstaat door H4 (een datum) te vergelijken met de kolom GebDtm, krijgen we een nieuwe reeks met een 1 als aan alle twee de voorwaarden is voldaan, anders een 0.

In cel M4 staat dan ook de formule
=VERGELIJKEN(1;(tblWerknemers3[Naam]=G4)*(tblWerknemers3[GebDtm]=H4);0)

LET OP ook deze formule moet met CSE worden ingevoerd.

LET OP plaats bij het vermenigvuldigen van dit soort reeksen er haakjes omheen.

NB aangezien de vermenigvuldiging altijd een 1 of 0 oplevert (en niet WAAR of ONWAAR) moet als zoekwaarde een 1 worden opgegeven (bekijk de tussenstappen met Formule evalueren).

Om het juiste resultaat op te halen op basis van de 2 criteria staat in I4 de formule
=INDEX(tblWerknemers3[Nr];VERGELIJKEN(1;(tblWerknemers3[Naam]=G4)*(tblWerknemers3[GebDtm]=H4);0))

LET OP ook deze formule moet met CSE worden ingevoerd!

Cadeau: in de cellen I7 en J7 staat een aangepaste formule, die er voor zorgt, dat bij een onbekende werknemer er geen #N/B als resultaat komt.