Tagarchief: AselectTussen

Frequentietabel en histogram



Overal kom je ze tegen, histogrammen: een grafische weergave van een frequentietabel.

Iedereen die met Excel werkt, heeft wel eens zoiets gemaakt.

In dit artikel laten we diverse mogelijkheden de revue passeren om zo’n overzicht te maken. We zoomen daarbij vooral in op verschillende methoden om een frequentietabel te maken. Volgende keer komen diverse alternatieven voor het histogram aan bod.

Voor het maken van een frequentietabel is de functie INTERVAL heel erg handig. Maar ook enkele ‘vreemde’ eigenschappen daarvan komen aan bod (zoals beloofd in Excel en kaarten 2).

Basis

Zoals gezegd: je komt ze bijna dagelijks tegen, histogrammen (en dus ook frequentietabellen). Ieder krantenartikel, waarin aantallen voorkomen, wordt wel verduidelijkt (?) met een grafiekje. Maar ook HR-medewerkers zijn er verzot op (leeftijds-, functie- en salarisopbouw kunnen daar goed mee worden geïllustreerd) en ook leerkrachten in het onderwijs gebruiken het veel (om de verdeling van proefwerkpunten te bepalen bijvoorbeeld).

In het Voorbeeldbestand op het tabblad Basis staat een fictief overzicht van de resultaten van 5 proefwerken van 10 leerlingen.
Met behulp van de functie ASELECTTUSSEN worden (bij een wijziging in Excel of het drukken op F9) iedere keer nieuwe resultaten tussen 2 en 10 gegeneerd (inclusief die 2 grenzen).

Op hetzelfde tabblad staat een vergelijkbaar overzicht maar daar krijgen de proefwerkresultaten 1 decimaal. De gebuikte formule wordt dan: =ASELECTTUSSEN(20;100)/10

Deze overzichten gebruiken we in het vervolg van het artikel als voorbeeld-input.

Frequentietabel 1

We beginnen met een proefwerkoverzicht zonder decimalen. Daarvan gaan we een frequentietabel maken: hoe vaak komt ieder cijfer voor.

NB direct is te zien dat het voorbeeld niet echt reëel is: het cijfer 10 komt wel heel vaak voor! Maar hoe de verdeling van de overige cijfers is, is zelfs met dit kleine aantal moeilijker te beoordelen.

Op het tabblad FreqTabel1 van het Voorbeeldbestand staat ons eerste resultaat. In kolom J wordt de absolute frequentie van ieder mogelijk cijfer geturfd:
=AANTAL.ALS($C$3:$G$12;I3)

In cel J3 tellen we het aantal keren, dat de waarde uit I3 voorkomt in het bereik C3:G12.

NB ter controle tellen we onderaan in kolom J de frequenties op, zodat we zeker weten dat alle cijfers zijn meegenomen.
Selecteer de cel onder de frequenties en druk op Alt-= (dus de Alt-toets vasthouden en op = drukken). Deze sneltoets werkt ook als je getallen in een rij wilt optellen.

In kolom K bepalen we de cumulatieve absolute frequentie door bij het vorige resultaat in die kolom het resultaat uit kolom J op te tellen (in cel K4 staat de formule =K3+J4).

De formules in de kolommen L en M, waarin de relatieve en cumulatieve relatieve frequenties worden bepaald, spreken voor zich.

Absolute frequentie 2

Een alternatieve methode voor het bepalen van de absolute frequentie is het gebruik van de Excel-functie Interval (zie het tabblad FreqTabel1 van het Voorbeeldbestand).

Allereerst de volgende waarschuwing: Interval is een ‘vreemde’ functie. Niet alleen de input wordt gevormd door een gebied/bereik van cellen (net als bij Som bijvoorbeeld) ook het resultaat bestaat uit meer dan één getal. Het is een zogenaamde matrix-functie.

  1. selecteer eerst de cellen waar het resultaat moet komen. Interval levert de frequenties op, horend bij een gewenst interval. In dit geval bij de cijfers 1 t/m 10. Dus het resultaat moet uit 10 cellen bestaan.
    LET OP het resultaat van de functie Interval moet altijd in een kolom komen, dus selecteer cellen ONDER ELKAAR. In het voorbeeld de cellen N3:N12.
  2. tik in =interval(
  3. voer dan de eerste parameter van de functie in, de Gegevensmatrix. Dit zijn de brongegevens, in dit geval het bereik C3:G12.
  4. tik in ;
  5. dan komt de tweede parameter, de Interval_verw. Dit moeten cellen zijn die de gewenste intervallen aangeven; in het voorbeeld de cellen I3:I12.
  6. tik in )
  7. sluit de functie NIET af door op Enter te drukken, maar tegelijkertijd op Ctrl-Shift-Enter. Op deze manier wordt een matrix-formule ingevoerd, ook wel een CSE-formule genoemd.
    In de 10 cellen staat nu overal exact dezelfde formule:
    {=INTERVAL(C3:G12;I3:I12)}
    LET OP de accolades hebben we niet zelf ingevoerd; dit is een indicatie dat hier een matrix-formule staat. Onderdelen van een matrix-formule kunnen niet worden gewijzigd, er kunnen geen regels tussengevoegd worden etc. Wil je een wijziging doorvoeren: selecteer alle bij elkaar horende cellen, klik in de Formulebalk en druk op Ctrl-Enter. Nu wordt in alle cellen dezelfde formule ingevoerd, maar niet als matrix-formule en dus horen ze niet meer bij elkaar.
    NB in de Engelstalige Excel-versies heet deze functie Frequency; dit geeft beter de bedoeling van de functie aan dan Interval.

NB1 het is in een matrix-formule niet nodig om de bereiken absoluut (met $-tekens) in te voeren.

NB2 hiervoor hebben we net gedaan of de Interval-functie de frequenties van de diverse cijfers heeft geturfd, maar eigenlijk zijn de waardes in het bereik Interval_verw de te hanteren bovengrenzen van de diverse intervallen. Maar aangezien de proefwerkpunten geen decimalen bevatten, zijn de bovengrenzen ook de enige cijfers die in dat interval voorkomen.

Cumulatieve absolute frequentie 2

In cel O3 van het tabblad FreqTabel1 van het Voorbeeldbestand hebben we de formule
=INTERVAL($C$3:$G$12;I3)
geplaatst.
Er is maar 1 bovengrens van een interval, dus ook maar 1 resultaat. De formule hoeft dan ook niet met CSE afgesloten te worden, een Enter volstaat.

Deze formule turft het aantal keren, dat een getal uit het bereik van de eerste parameter kleiner of gelijk is aan de waarde in cel I3. In dit geval dus <=1; dat komt in het voorbeeld niet voor.
Wanneer deze formule naar beneden gekopieerd wordt krijgen we de gewenste cumulatieve resultaten.

Histogram

Bij een frequentietabel hoort ook de grafische weergave, een histogram.

Maak een grafiek met daarin de absolute frequentie en de cumulatie daarvan, zorg dat op de x-as de intervalgrenzen komen (dus de cijfers 1 t/m 10) en zorg dat het een combinatiegrafiek wordt met de cumulatieve waarden uitgezet op de secundaire as.

LET OP kies bij het gebruik van een secundaire as de maximale waarden van de assen zodanig dat de horizontale rasterlijnen aan allebei de kanten bij weergegeven getallen uitkomen. In dit geval is het maximum rechts 4x groter dan links.

In een volgend artikel zullen we diverse andere methoden voor het maken van een histogram de revue laten passeren met daarbij de voor- en nadelen van de diverse alternatieven.

NB wanneer de brongegevens in de loop van de tijd nog uitgebreid worden, plaats deze dan in een Excel-tabel. Na uitbreiding hiervan wordt automatisch de frequentietabel geactualiseerd (zie het tweede blok op het tabblad FreqTabel1 van het Voorbeeldbestand).
In het voorbeeld was het dan wel logischer geweest als ik de proefwerken in de rijen had geplaatst en de leerlingen in de kolommen!
Maar in mijn-tijd-als-docent was dit wel de indeling in de lerarenagenda:

Frequentietabel 2

We gebruiken hetzelfde soort proefwerkoverzicht als in het vorige hoofdstuk, maar nu met 1 decimaal (zie het tabblad FreqTabel2 van het Voorbeeldbestand).
Dankzij de voorwaardelijke opmaak van Excel zien we snel waar de hoge en lage punten zitten.

Hadden we dat vroeger in de lerarenagenda ook maar zo makkelijk gehad. Toen gebruikten we gekleurde pennen (exacter uitgedrukt: pennen met gekleurde inkt) met alle problemen van dien, als een cijfer achteraf nog aangepast moest worden.

Om de voorwaardelijke opmaak te kunnen toepassen gebruiken we een hulpkolom I; in de cellen I3:I6 staan de cijfers 4, 6, 8 en 10. De regels voor de voorwaardelijke opmaak zien er als volgt uit:

De vorige opmaak leverde wel een bonte kermis op. Het mag wel wat subtieler.
Maar daardoor is het iets moelijker om te zien in welke categorie een cijfer valt.

De gebruikte regels:

Hé, wat vreemd. Alle cijfers krijgen de goede kleur maar zijn allemaal cursief en vet, terwijl dat in de opmaakregels alleen voor de laagste en hoogste punten is ingesteld.
Als deskundige Excel-ler hebt u natuurlijk al gezien hoe dat komt. We nemen als voorbeeld het cijfer 5,6 : dit voldoet niet aan de eerste regel maar wel aan de tweede, dus krijgt het een oranje opmaak.
Maar hij voldoet ook aan de derde regel! Dus zou het cijfer een licht-groene opmaak moeten krijgen, maar dat kan Excel niet: én oranje én licht-groen. Het cijfer krijgt de eerste kleur.
5,6 voldoet ook aan regel 4. Donker-groen maken kan Excel niet meer, maar wel cursief en vet.

We moeten ook de laatste kolom binnen de opmaak-regels gebruiken (Stoppen indien Waar).
Als een cijfer aan een regel voldoet dan worden de volgende regels niet meer uitgevoerd.

NB1 Het laatste vinkje hoeft niet meer; de opmaak-routine stopt toch al

NB2 bij het opmaken van het ‘kermis-overzicht’ is de stop-optie niet nodig omdat ook hier geldt dat Excel maar één opvulkleur aan een cel kan toewijzen.

Even genoeg over de opmaak; dit artikel gaat over frequentietabellen.
Om de proefwerkresultaten met decimalen in de juiste categorie onder te brengen gebruiken we weer de functie Aantal.Als.

Maar wel iets ingewikkelder dan hierboven: in cel K3 staat de formule
=AANTAL.ALS($C$3:$G$12;”<=”&I3)
Turf het aantal getallen in het bereik C3:G12, die voldoen aan de voorwaarde dat ze kleiner of gelijk zijn aan de waarde in cel I3.

LET OP de voorwaarde moet een tekst-vorm hebben: dus de tekst <= (zie de “-tekens) wordt met behulp van & samengevoegd met de inhoud van cel I3.

Deze is formule is naar beneden gekopieerd. Helaas, nu hebben we niet de frequenties maar de cumulatieven.
Geen nood, in kolom L gaan we de frequenties bepalen. De eerste berekening is oké. Dus cel L3 is hetzelfde als K3.
In cel L4 plaatsen we de formule =AANTAL.ALS($C$3:$G$12;”<=”&I4)-L3. Maar die voldoet niet als we die “naar beneden kopiëren”: we hadden =AANTAL.ALS($C$3:$G$12;”<=”&I5)-SOM($L$3:L3) moeten gebruiken.

In kolom M hebben we nog een alternatieve berekening voor de frequenties: M3 is weer gelijk aan K3, in M4 staat de formule =AANTALLEN.ALS($C$3:$G$12;”<=”&I4;$C$3:$G$12;”>”&I3)
Dus een Als met 2 voorwaarden, dat kan alleen met de functie AantalLEN.als.
Deze formule kan naar beneden gekopieerd worden.

NB wil je toch Aantal.Als gebruiken: in cel M4 had ook de formule
=AANTAL.ALS($C$3:$G$12;EN(“<=”&I4;”>”&I3))
kunnen staan. Hierbij zijn met behulp van de functie EN 2 voorwaarden gecombineerd tot 1.

Zoals uit de vorige alinea’s mag blijken is het maken van een frequentietabel met behulp van Aantal.Als niet altijd even makkelijk. We mogen blij zijn dat Excel de functie Interval kent:

  1. selecteer de cellen N3:N6
  2. tik in de de formulebalk in: =INTERVAL(C3:G12;I3:I6)
  3. druk op Ctrl-Shift-Enter
  4. klaar!

NB1 niet tevreden met de grenzen van de intervallen? Is alles boven 5,5 een voldoende? Wijzig de inhoud van cel I4 in 5,5 en de hele sheet wordt automatisch aangepast. Niet alleen de frequentietabellen maar ook de voorwaardelijke opmaak.

Maar wat gebeurt er als je de 10 in cel I6 wijzigt in 9? Dan worden niet alle punten in de frequentietabel meegeteld; dat is niet de bedoeling. Gelukkig heeft Microsoft daar rekening mee gehouden.

Ter verduidelijking bevat het tabblad FreqTabel2 nog een ander overzicht:

  1. selecteer de cellen J10:J22
  2. tik in de de formulebalk in: =INTERVAL(C3:G12;I10:I19)
  3. druk op Ctrl-Shift-Enter

Bewust is het resultaatbereik groter gekozen dan we gewend zijn; de eerste 10 regels zijn logisch: eerst wordt het aantal proefwerkresultaten kleiner of gelijk aan 1 geturfd (0 dus), dan hoeveel groter dan 1 en kleiner of gelijk aan 2 etc.
Maar dan komt er nog een resultaat; in dit geval 0. Excel levert via de Interval-functie altijd één waarde meer dan het aantal gedefinieerde intervallen. Hier wordt geteld hoeveel brongegevens groter zijn dan de hoogst gedefnieerde intervalgrens.
Wijzig de 10 in bijvoorbeeld 9,5 en je ziet het volgende resultaat:

De laatste 2 regels laten zien, dat de Interval-functie geen resultaat meer oplevert, dus we hadden hiervoor bij punt 1 beter de cellen J10:J20 kunnen kiezen.

Interval-functie

Laten we de Interval-functie nog eens even verder onder de loep nemen.
In het tabblad Interval van het Voorbeeldbestand ziet u weer dezelfde brongegevens staan. Op basis van de 10 intervalgrenzen in kolom I zijn de bijbehorende frequenties in kolom J met behulp van de Interval-functie bepaald, inclusief 1 extra cel (en 2 niet-nuttige cellen).

De frequentietabel in de kolommen L en M laat zien dat de Interval-functie ook werkt wanneer de grenzen in de volgorde hoog-laag staan (in de kolom M staat de formule .

En nog mooier: de intervalgrenzen mogen ook willekeurig door elkaar staan (zie de kolommen O en P)! Maar wanneer zou je zoiets nu doen?

De kolommen R en S laten nog een andere eigenschap van de functie zien: wanneer een interval een tweede keer voorkomt dan wordt de bijbehorende frequentie 0, zodat we geen dubbeltelling krijgen (dit geldt ook als zo’n grens nog vaker voorkomt). Consequentie is wel dat de frequenties van andere intervallen veranderen. En dat is natuurlijk terecht omdat de getallen in kolom R de bovengrens van een interval aanduiden.

Interval-functie 2

Hiervoor hebben we al regelmatig de functie Interval gebruikt, waarbij de werking (na wat oefening) ‘normaal’ begint aan te voelen.

We oefenen nog even met een nieuw voorbeeld (zie het tabblad Interval2 van het Voorbeeldbestand): van enkele leerlingen hebben we scores verzameld. De data zijn vastgelegd in een Excel-tabel met de naam tblLLscore.
Het aantal records staat onder de kolom LLnr, daarnaast is het totaal van de scores bepaald met de formule .
De twee totalen zijn (bijna) automatisch gegenereerd door in de menutab Hulpmiddelen voor tabellen de Totaalrij te activeren:

Een overzicht van de verdeling van de scores is nu snel gemaakt:

  1. plaats de scores 0 tot en met 10 in een kolom (hier kolom E)
  2. selecteer daarnaast de cellen in kolom F, tik in de formule =INTERVAL(tblLLscore[Score];E3:E13) en druk op Ctrl-Shift-Enter
  3. in kolom G sommeren we de scores als de score overeenkomt met cel E3, E4 etc.
  4. onderaan sommeren we de resultaten van de kolommen F en G

LET OP de resultaat-tabel in de kolommen E:G kan niet de vorm van een Excel-tabel krijgen; Excel staat een combinatie van zo’n tabel en een matrix-formule niet toe!

We weten nu hoe de verdeling over de verschillende scores is, maar hoe is de verdeling over de leerlingen?
In het voorbeeld is het aantal leerlingen beperkt; we zien in één oogopslag dat alleen de nummers 1 tot en met 5 voorkomen. Maar hoe weet je dat wanneer de bron veel uitgebreider is? Een oplossing is het gebruik van Filter:

  1. kies in de menutab Gegevens in het blok Sorteren en filteren de optie Geavanceerd
  2. vul het scherm van het Uitgebreid filter in zoals hiernaast
  3. klik op OK

Het klopt, er zijn 5 verschillende leerlingennummers.
We hebben de kolom I aangevuld met J en K en daar de Excel-tabel tblLLresult van gemaakt.
In kolom J staat de formule =AANTAL.ALS(tblLLscore[LLnr];[@LLnr]) en in K: =SOM.ALS(tblLLscore[LLnr];[@LLnr];tblLLscore[Score])

En de tabel heeft ook een Totaalrij gekregen.

Een andere methode om vanuit de basis tot een leerlingen-overzicht te komen is door het gebruik van Power Query.
In de kolommen M:O staat het resultaat daarvan.

Wat gebeurt er als er nieuwe scores bij komen?

  1. selecteer met de cursor cel C16 (de laatste score)
  2. druk op de Tab-toets
  3. de Totaalrij van de tabel verschuift automatisch naar beneden
  4. voeg nu nieuwe gegevens toe: leerling 3 heeft een 7 gescoord.

Alle overzichten zijn overeenkomstig aangepast. Het resultaat van Power Query hebben we wel moeten Vernieuwen (rechts klikken op één van de cellen in M:O).

Voeg op dezelfde manier voor leerlingnummer 6 de score 8 toe:

Helaas: ons leerlingenoverzicht is niet meer compleet. We zullen handmatig het leerlingnummer 6 moeten toevoegen om alles weer kloppend te maken. Fijn dat we controle-totalen hebben toegevoegd!

NB misschien toch handiger om voor dit soort overzichten draaitabellen te gebruiken?

Interval-functie-3a

Maar de functie heeft een speciale eigenschap, die goed gebruikt kan worden bij het turven van het aantal unieke gegevens (en dat was uiteindelijk de aanleiding voor dit artikel).

Op het tabblad Uniek van het Voorbeeldbestand hebben we de kolom met leerlingnummers gekopieerd naar kolom B.

Daarnaast hebben we met behulp van de Interval-functie een frequentieoverzicht voor deze leerlingen gemaakt door de Gegevensmatrix en Interval_verw gelijk te maken.

Leerling 1 komt 4 keer voor, de volgende 3 keer etc.
Komt het leerlingnummer nogmaals voor dan is de bijbehorende frequentie 0; we krijgen dus geen dubbeltellingen!

Door alle frequenties in kolom C op te tellen weten we dus ook het totaal aantal vastgelegde scores.

Maar hoe bepalen we nu het aantal unieke leerlingnummers? Met de volgende formule is dit eenvoudig:
LET OP om Excel te ‘dwingen’ om de ALS uit te voeren voor alle cellen in C3:C16 moet de formule afgesloten worden met Ctrl-Shift-Enter.

We hebben de frequenties van kolom C niet per se nodig. In cel G3 staat de formule =SOM(ALS(INTERVAL(B3:B16;B3:B16)>0;1;0)) die ook het juiste aantal unieke leerlingen oplevert.
NB deze formule kan met Enter afgesloten worden; blijkbaar snapt Excel door het gebruik van Interval dat hij/zij de ALS vaker moet uitvoeren.

Nog korter (in cel H3): =SOM(1*(INTERVAL(B3:B16;B3:B16)>0))

NB1 denk aan de extra haakjes na 1*.

NB2 wil je kijken hoe deze formules werken? Gebruik de optie Formules/Formules evalueren.

Interval-functie-3b

Maar de weg van een Excel-ler is niet altijd even geplaveid, dat weet u ongetwijfeld.
Wanneer we niet te maken hebben met leerlingnummers maar met namen (zoals in het tabblad Uniek2 van het Voorbeeldbestand) dan werkt de Interval-functie niet meer!

Gelukkig is daar wat op te vinden. Ter verduidelijking hebben we in kolom E een formule staan met de functie Vergelijken: vergelijk de naam van de leerling in dezelfde regel (zie de @) met ALLE namen in de Leerling-kolom. Als die te vinden is (en dat is hier natuurlijk altijd zo!) dan levert de functie de positie op van deze naam; komt de naam vaker voor dan wordt telkens dezelfde positie opgeleverd.
Op deze manier vertalen we de namen dus naar getallen, waarbij dezelfde namen dezelfde getallen opleveren. U voelt het al aankomen: op deze hulpkolom kunnen we wel de Interval-functie loslaten!

We hebben de hulpkolom E niet nodig: zoals te zien is levert de formule in cel J3 direct de juiste informatie.


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


Snelle analyse



Op de site van G-Info hebben we het al heel vaak gehad over allerlei methoden om met Excel gegevens te analyseren.

Uit alle vragen die we krijgen blijkt dat de meeste mensen daarbij worstelen met Voorwaardelijke opmaak, grafieken en draaitabellen. Daar hebben we dan ook al diverse keren aandacht aan besteed.

Waar we het nog niet over hebben gehad, is dat je Excel vaak automatisch het (voor)werk kunt laten doen!
Microsoft heeft (vanaf versie 2013) een optie ingebouwd, die ze Snelle Analyse genoemd hebben. Naar mijn idee zijn ze daarbij eigenlijk te bescheiden: ik zou dat eerder Zeer snelle analyse met heel erg veel mogelijkheden hebben genoemd, maar dat is natuurlijk wat lang in een menu 😉

De diverse resultaten van Snelle analyse kunnen nog handmatig naar wens worden aangepast; ook kan het een goede start zijn om de diverse onderdelen van Excel die daarbij gebruikt worden, beter te begrijpen.

Basis-gegevens

In het Voorbeeldbestand staat op het tabblad Data een blokje gegevens dat we in dit artikel zullen gebruiken om te laten zien hoe snel (en goed!) Snelle Analyse werkt.

De getallen worden door de functie ASELECTTUSSEN bij iedere wijziging in de werkmap (of na het drukken op F9) opnieuw gegenereerd. Het effect daarvan zie je dan direct in de verschillende analyses terug.

Snelle analyse starten

Selecteer je in Excel meer dan één cel tegelijk dan zie je rechtsonder bij die selectie de button Snelle analyse tevoorschijn komen. Zoals je kunt zien kun je ook de toetscombinatie Ctrl-Q gebruiken.

NB de button komt alleen tevoorschijn als je aaneengesloten cellen selecteert.

Klik je op de button dan verschijnt het Snelle analyse-menu:

Je kunt dus op 5 manieren je gegevens analyseren: met Opmaak (beter gezegd Voorwaardelijke opmaak), door middel van Grafieken, door automatisch Totalen (en andere statistieken) toe te laten voegen, door het gebruik van Tabellen (inclusief draaitabellen) of met Sparklines.

De mogelijkheden binnen deze opties kunnen afhankelijk zijn van de geselecteerde cellen.

Opmaak

De (voorwaardelijke) opmaak willen we alleen toepassen op de getallen in het overzicht. Dus selecteer eerst de cellen met die getallen en klik dan op de Analyse-button (of druk op Ctrl-Q). De Opmaak-optie is al geselecteerd. Wanneer je nu met de muis over de 6 verschillende menu-keuzes gaat zie je direct het resultaat daarvan in de brongegevens.

Wanneer je de opmaak daadwerkelijk aan je gegevens wilt toevoegen dan moet je op de betreffende keuze klikken (zie het tabblad Opmaak van het Voorbeeldbestand).
Ook kun je een combinatie van voorwaardelijke opmaak toevoegen door meerdere keuzes achter elkaar te maken.
Wil je de opmaak verwijderen kies dan de laatste optie in het Opmaak-menu.

Gegevensbalk

Via de eerste keuzemogelijkheid worden Gegevensbalken aan de geselecteerde cellen toegevoegd. Hierdoor krijg je snel inzicht in hoe de getallen zich ten opzichte van elkaar verhouden.

NB Excel zal om de lengte van de gegevensbalken te maken alle getallen uit de geselecteerde cellen vergelijken. Wil je dat Excel per kolom (of rij) de waardes vergelijkt dan moet je de opmaak voor iedere kolom (of rij) apart instellen.

Kleurschalen en Pictogrammen

Ook via de opties Kleurschalen en Pictogrammen kun je de onderlinge verhoudingen van de geselecteerde getallen zichtbaar maken.

NB de voorwaardelijke opmaak kan nog naar wens worden aangepast:

  1. selecteer één van de cellen met opmaak
  2. kies in de menutab Start in het blok Stijlen de optie Voorwaardelijke opmaak
  3. kies Regels beheren….
  4. kies Regel bewerken
  5. pas de diverse opties naar wens aan

Groter dan

Door middel van de vierde opmaak-optie kun je getallen die groter zijn dan een bepaalde waarde een opmaak meegeven.

Kies je deze mogelijkheid dan moet je een grenswaarde opgeven; in het voorbeeld verwijzen we daarbij naar cel D32. Door deze cel te wijzigen zal de opmaak zich automatisch aanpassen.

Bovenste 10%

Deze optie spreekt voor zich. De cellen die waarden bevatten die bij de hoogste 10% horen worden gemarkeerd.

Door de opmaak-regel aan te passen kun je ook een heel ander gedeelte van de getallen een opmaak geven:

Opmaak wissen

Met de 6e keuzemogelijkheid kun je bestaande opmaak wissen.

NB op het tabblad Opmaak van het Voorbeeldbestand staan ‘gewone’ Excel-overzichten. Wanneer je deze uitbreidt met nieuwe gegevens moet je de opmaak zelf nog aan die nieuwe gegevens toevoegen.
In het tabblad Opmaak2 staan dezelfde overzichten, maar dan in de vorm van een Excel-tabel. Als je daar gegevens toevoegt, krijgen de nieuwe gegevens automatisch de bijbehorende opmaak.

Grafieken

Grafieken worden veel gebruikt als rapportagetool, maar kunnen ook een goed analyse-hulpmiddel zijn.

Met Snelle analyse is een grafiek maken een fluitje van een cent:

  1. voor een grafiek hebben we niet alleen de getallen nodig maar ook de omschrijvingen daar omheen.
  2. selecteer alle benodigde cellen en klik op de button Snelle analyse
    of
    selecteer één van de cellen met getallen en druk op Ctrl-Q
  1. klik in het submenu op de optie Grafieken en ga met de muis over de voorgestelde grafieken. Welke dit zijn is afhankelijk van de brongegevens.
  2. Klik op een van de grafieken, et voilà! Zie het tabblad Grafieken van het Voorbeeldbestand.

Komen er nieuwe gegevens bij, dan zul je óf de de brongegevens van de grafiek moeten aanpassen (rechtsklikken op de grafiek en Gegevens selecteren kiezen) óf de grafiek op bovenstaande manier opnieuw moeten maken.

Dat kun je ondervangen door de brongegevens als Excel-tabel vast te leggen (zie het tabblad Grafieken2 van het Voorbeeldbestand).
Helaas, dan werkt het selecteren van de gegevens met Ctrl-Q niet goed. Doe dan het volgende:

  1. selecteer één van de cellen met getallen en druk op Ctrl-A
  2. maar we hebben ook de kopregel nodig; druk nogmaals op Ctrl-A
  3. druk dan Ctrl-Q of kies de Snelle analyse-button.

Totalen

  1. we willen natuurlijk alleen van de getallen in het binnenblok de totalen berekenen (en bijvoorbeeld niet van de datums ook al zijn dat voor Excel ook getallen); dus selecteer de cellen C4:F15 (zie het tabblad Totalen van het Voorbeeldbestand)
  2. druk op Ctrl-Q of klik op de analyse-button
  3. kies de optie Totalen en klik op de gewenste functie (Som, Gemiddelde, Aantal, Totaal % of Voorlopig …)
  4. Excel plaatst formules onder het geselecteerde blok getallen en voert daarmee de gewenste berekening uit.

NB1 zijn de cellen onder de selectie niet leeg, dan krijg je een waarschuwing of je deze wilt overschrijven. Wil je deze gegevens bewaren maak dan eerst ruimte door een lege regel in te voegen voordat je bovenstaande handelingen uitvoert.

NB2 bij de eerste 5 opties is een regel blauw gekleurd; dit betekent dat Excel de formules onderaan in een regel plaatst (en totalen per kolom bepaalt). De 6e optie (en verder, klik op het pijltje aan de rechterkant) laten een gekleurde kolom zien. Als je die optie gebruikt zal Excel formules aan de rechterkant plaatsen en dus totaalberekeningen over de rij uitvoeren.

NB3 Excel plaatst formules in het tabblad. Deze zijn zodanig opgezet dat bij het toevoegen van nieuwe gegevens de berekeningen met een paar kleine aanpassingen weer kloppen.

Lopend totaal

In rij 19 staat een formule die het lopend totaal bepaalt (in Excel wordt deze analyse-optie met Voorlopig … aangeduid). In cel C19 staat het totaal van de regio Noord, in D19 het totaal van Noord én Oost etc.

In kolom J staat een lopend totaal over alle regio’s. Wil je per regio een lopend totaal dan moet je eerst tussen C en D een nieuwe kolom invoegen, de gegevens van kolom C selecteren en dan het lopend totaal invoegen. Doe dat ook voor de andere kolommen; zie het tabblad Totalen:

NB plaats je de gegevens in een Excel-tabel dan zien de ingevoegde formules er heel anders uit (zie het tabblad Totalen2 van het Voorbeeldbestand). Bij het toevoegen van nieuwe gegevens hoeft dan niets (of veel minder) aan de formules gewijzigd te worden.

Tabellen

Met de eerste keuze binnen de analyse-optie Tabellen wordt een gewoon bereik van cellen omgezet naar een Excel-tabel. Maar ik gebruik die (bijna) nooit; die optie kennen we al via Invoegen of door Ctrl-L te tikken.

In het tabblad Tabellen van het Voorbeeldbestand is het gebruikte bronbestand omgezet naar een database-vorm; dit om de mogelijkheden van een draaitabel makkelijker te benutten.

Per combinatie van kenmerken (Regio, Maand en Product) wordt het Aantal vastgelegd.

  1. selecteer één van de cellen in de Excel-tabel en druk op Ctrl-Q
  2. kies de analyse-optie Tabellen
  3. op basis van deze brongegevens stelt Excel 2 draaitabellen voor: Som van Aantal per regio en per product
  4. klik op één van de 2 mogelijkheden (of de 3e om zelf een draaitabel te maken)

NB de 2 draaitabellen zijn gebaseerd op dezelfde brongegevens maar laten ieder een ander totaal zien!
Dit is een inconsistentie binnen Excel. Maak je zelf verschillende draaitabellen op basis van dezelfde brongegevens dan worden alle draaitabellen tegelijkertijd vernieuwd. Zijn de draaitabellen via Snelle analyse aangemaakt dan moet iedere draaitabel afzonderlijk vernieuwd worden. Maar dan worden de brongegevens tussentijds aangepast door de Aselect-formule.

Op het tabblad Tabellen staat ook een draai-grafiek; deze is gemaakt op basis van de gegevens in de eerste kolommen door in de analyse-optie een grafiek te kiezen met het -teken.

Sparklines

Niets nieuws meer onder zon: selecteer alle cellen met getallen (dus niet de maanden en de kopregels), Ctrl-Q, kies de optie Sparklines en klik op één van de drie mogelijkheden (zie het tabblad Sparklines in het Voorbeeldbestand).

Het eerste type sparkline laat het globale verloop als lijngrafiek zien, de tweede globaal het verloop als kolomgrafiek en de derde (Winst/verlies) laat alleen maar zien of het resultaat positief of negatief is.

NB1 het uiterlijk van de sparklines kan makkelijk aangepast worden: klik op een cel die een sparkline bevat en kies bij Hulpmiddelen voor sparklines de optie Ontwerpen.

NB2 of je nu met een bereik van cellen werkt of met een Excel-tabel (zie het tabblad Sparklines2 van het Voorbeeldbestand), bij het uitbreiden van gegevens zul je de sparklines ook moeten aanpassen (of opnieuw maken). Het aanpassen gaat simpel door de vulgreep rechtsonder in de onderste cel met een sparkline naar beneden te trekken.


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


Treemap en Waterval



Zolang als Excel al bestaat, wordt het niet alleen als rekentool gebruikt maar ook voor rapportage-doeleinden.
Waar in de beginperiode meestal door middel van cijfers werd gerapporteerd, zijn daar later ook grafieken bij gekomen.

Microsoft heeft ons in de afgelopen jaren met diverse soorten grafieken verblijd.
In iedere nieuwe versie van Excel verschijnen er weer nieuwe; in dit artikel aandacht voor de Treemap en de waterval-grafiek.

NB in oudere versies van Excel en in menige MAC-versie werken de voorbeelden niet.

Basisgegevens

Om een grafiek te kunnen maken hebben we natuurlijk basisgegevens nodig.
In het Voorbeeldbestand staat in het tabblad DataTree een overzicht van Bedragen uitgesplitst naar Maand en Soort.

NB door middel van de functie Aselecttussen worden de data door Excel willekeurig gekozen; bij iedere wijziging in de werkmap zullen dus nieuwe gegevens gegenereerd worden.
Op deze manier kun je snel de impact op de grafieken zien.

De gegevens zijn opgeslagen in een Excel-tabel met de naam tblDataTree. Uitbreidingen aan deze tabel (of verwijderingen) zullen daardoor automatisch doorwerken in de overzichten en grafieken die daar op gebaseerd zijn.

Draaitabel en -grafiek

De gegevens uit het tabblad DataTree zijn in een draaitabel samengevat op het tabblad OvzTree. In dit geval is de Soort in het Filter-veld geplaatst, de Maand in de Rijen en het Bedrag in het Waarden-gebied.
Deze draaitabel kan direct vertaald worden naar een grafiek:

  1. klik ergens in de draaitabel
  2. op dat moment komt er een nieuwe menu-tab bij, Hulpmiddelen voor Draaitabellen
  3. klik daarbinnen op de menutab Analyseren en dan in het blok Extra op Draaigrafiek
  4. na enkele cosmetische aanpassingen ontstaat bovenstaande grafiek

NB de grafiektitel is dynamisch: wanneer in het Filter een andere Soort wordt gekozen past de titel zich automatisch aan.
In cel C19 wordt de basis daarvoor gelegd. U wijzigt een grafiektitel als volgt: klik in de bestaande titel, daarna in de formulebalk, voer het =-teken in, klik dan op cel C19 en druk op Enter. In de formulebalk verschijnt =OvzTree!$C$19.

Niet altijd is in deze grafiek duidelijk welke maanden in welke mate bijdragen tot het totaal (klik rechts in de Draaitabel en kies Vernieuwen). Aangezien de basisgegevens telkens opnieuw worden gegenereerd zal de Draaitabel (en dus ook de Draaigrafiek) dienovereenkomstig worden bijgewerkt.
Door de maanden anders te rangschikken kunnen de onderlinge verhoudingen duidelijker worden gemaakt:

  1. klik op het keuzevinkje achter Rijlabels
  2. kies Meer sorteeropties
  3. klik op het keuzerondje vóór Aflopend
  4. kies dan daaronder als sorteervolgorde niet voor Maand maar voor Som van Bedrag
  5. klik op OK

Bekijk het effect als je de draaitabel vernieuwd.

Treemap

Maar niet iedereen vind het lezen/interpreteren van bovenstaande grafiek makkelijk.
Waarschijnlijk zijn deze mensen meer gebaat bij een zogenaamde Treemap, bedoeld om hiërarchie in resultaten te verduidelijken.

Helaas is de Treemap niet beschikbaar als de bron een draaitabel is. Daarom is in het tabblad OvzTree van het Voorbeeldbestand een hulptabel gecreëerd, die de gegevens van de draaitabel repliceert.

In cel C27 wordt, afhankelijk van de corresponderende waarde in kolom B, het Bedrag opgehaald in de draaitabel rond cel B4.

Het maken van een Treemap is dan een peuleschil:

  1. klik op één van de cellen in het brongebied, bijvoorbeeld cel C27
  2. kies in de menutab Invoegen in het blok Grafieken voor de optie Hiërarchiegrafiek
  3. kies daar de optie Treemap
  4. uiteraard zijn diverse eigenschappen, zoals legenda nog aanpasbaar

NB Blijkbaar komt dit grafiektype van een andere software-maker; niet alle eigenschappen van een grafiek zijn beschikbaar.
De mooie ronde hoeken bijvoorbeeld zijn niet meer terug te vinden.
Ook een dynamische grafiektitel is niet op dezelfde manier, als hiervoor aangegeven, in te voeren. In dit geval (zie het tabblad OvzTree van het Voorbeeldbestand) heb ik er voor gekozen om een Tekstblok in te voegen en daar dan de verwijzing naar cel C19 te plaatsen.

Om het instellen van keuzemogelijkheden te vereenvoudigen zijn ook 2 Slicers toegevoegd: eentje voor het instellen van de Soort en een andere om bepaalde Maanden te kunnen selecteren.

Waterval-grafiek

Is niet zozeer de onderlinge verhouding van belang, maar wil je weten hoe ieder onderdeel bijdraagt aan het geheel, dan is een ander type grafiek meer voor de hand liggend, de Waterval-grafiek.

NB Ook deze grafiek in niet beschikbaar als een draaitabel de brongegevens bevat.

In het tabblad Waterval van het Voorbeeldbestand is daarom een nieuwe bron-tabel (met de naam tblDataWater) ingevoerd. Per Maand wordt hier een willekeurig Bedrag gegenereerd tussen -100 en +200.

Ook het maken van een Watervalgrafiek is dan ‘kinderspel’:

  1. klik op één van de cellen in het brongebied, bijvoorbeeld cel C3
  2. kies in de menutab Invoegen in het blok Grafieken voor de optie Waterval-, trechter-, ….
  3. kies daar de optie Waterval
  4. uiteraard zijn diverse eigenschappen, zoals legenda nog aanpasbaar

NB ook voor dit type grafiek geldt het voorbehoud, dat niet alle (standaard-)instellingen beschikbaar zijn.


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


Loterij

loterij2Vorige week kreeg ik de vraag, hoe je met Excel het makkelijkst de winnaars van een wedstrijd zou kunnen selecteren.
De bedoeling was om uit een (grote) hoeveelheid goede inzenders willekeurig drie personen er uit te lichten.

Een poosje geleden heb ik al iets geschreven over steekproeven; dezelfde systematiek is ook voor dit probleem toepasbaar.
Maar deze keer een iets andere benadering.

Loterij1

loterijIn het Voorbeeldbestand heb ik in het tabblad Loterij een overzicht opgenomen van alle verkochte loten, inclusief de corresponderende naam (in dit geval de lotnummers 1 t/m 100 en een fictieve naam, die daarvan is afgeleid; bijvoorbeeld bij het eerste lot hoort Naam 1).
NB in plaats van lotnummers kan dit overzicht ook alle goede inzenders van een wedstrijd voorstellen

loterij2Op het tabblad Uitslag worden 3 winnaars geselecteerd:

  1. Allereerst tellen we het aantal verkochte loten (of dus het aantal goede inzenders). In cel C2 staat daartoe de formule:
    =AANTAL(Loterij!B:B)
    Ofwel tel het aantal getallen in kolom B van het tabblad Loterij.
    LET OP zijn in kolom B geen nummers opgenomen maar teksten (bijvoorbeeld A1, A2, B1 etc) dan moet u de formule AANTALARG gebruiken en van het resultaat 1 aftrekken, omdat dan ook het woord LotNr in cel B2 wordt meegeteld
  2. uit het aantal verkochte loten worden dan 3 willekeurige getallen getrokken (cellen C5, C6 en C7):
    =ASELECTTUSSEN(1;$C$2)
    Dus neem een willekeurig getal tussen 1 en de waarde in C2 (in het voorbeeld dus 100); de grenzen doen ook mee.
  3. in cel D5 zoeken we dan de corresponderende naam op:
    =VERT.ZOEKEN(C5;Loterij!B:C;2)
    Deze formule zoekt de waarde uit cel C5 op in kolom B van Loterij en geeft als resultaat de corresponderende cel uit kolom C.
    LET OP bovenstaande formule werkt alleen goed, als de lotnummers in volgorde in kolom B staan en er geen “gaten” zijn. Beter is om de formule
    =VERT.ZOEKEN(C5;Loterij!B:C;2;ONWAAR) te gebruiken; de laatste parameter zorgt er voor, dat Excel naar een exacte match gaat zoeken.
  4. de formule in D5 kan naar beneden gekopieerd worden
  5. bij iedere wijziging in de werkmap worden de Aselect-formules opnieuw berekend; dus iedere keer zullen er andere winnaars tevoorschijn komen. Dit gebeurt ook door op de functietoets F9 te drukken: herberekenen.
    Belangrijk is om van tevoren duidelijk met de “notaris” af te spreken hoe vaak er herberekend zal worden, voordat de definitieve uitslag wordt bepaald.

LET OP met bovenstaande methode is het mogelijk dat prijswinnaar 2 en/of 3 gelijk is aan prijswinnaar 1. Dat is natuurlijk niet de bedoeling. Druk dan nog een keer op F9.

NB worden er aan de lijst in het tabblad Loterij nummers en namen toegevoegd of worden er verwijderd, dan zullen de resultaten in Uitslag direct daaraan worden aangepast; we kijken immers naar alle rijen in de kolommen B en C.

Loterij2

Het Voorbeeldbestand bevat ook een tabblad Loterij2; deze is vergelijkbaar met de eerste, maar is in de vorm van een Excel-tabel opgevoerd. Een groot voordeel hiervan is dat we niet alle cellen uit de kolommen B en C hoeven mee te nemen in de formules: wanneer de tabel wordt uitgebreid of verkleind dan zullen alle corresponderende formules zich automatisch daaraan aanpassen.

loterij3De formule in C2 ziet er anders uit:
=AANTAL(LoterijOvz2[LotNr])
Tel het aantal nummers in de kolom met als kopje LotNr uit de tabel LoterijOvz2 (de tabel uit het tabblad Loterij2).

De rest van kolom C is hetzelfde. Maar in kolom D gebruiken we geen VERT.ZOEKEN maar de functie INDEX. In cel D5 komt dan de formule =INDEX(LoterijOvz2[Naam];C5).
Haal in de kolom met als kopje Naam uit de tabel LoterijOvz2 de waarde op in die regel, die overeenkomt  met de waarde in C5.

NB aanpassingen aan de tabel in Loterij2 worden automatisch meegenomen in de resultaten.

LET OP ook hier kan het nodig zijn om een keer extra op F9 te drukken om geen dubbele prijswinnaars te krijgen.

Loterij3

loterij4In het tabblad Loterij3 van het Voorbeeldbestand is in de Excel-tabel een extra kolom opgenomen, waarin iedere regel van een willekeurig getal tussen 0 en 1 wordt voorzien dmv de formule =ASELECT().
De kans, dat hier dubbele getallen in voorkomen, is heel erg klein.

NB deze functie kent geen parameters, maar, zoals achter iedere functie, dienen er wel 2 haakjes te staan (openen en sluiten).

loterij5De bepaling van de winnaars gaat nu iets anders: in cel C3 wordt de eerste winnaar bepaald door het grootste getal (MAX) in de kolom Aselect van de tabel LoterijOvz3 op te zoeken. In D3 wordt met VERT.ZOEKEN het corresponderende lotnummer gevonden en in E3 met INDEX de naam (zoals uit de formules van kolom F blijkt kunnen we dat laatste ook met VERT.ZOEKEN in de hele tabel LoterijOvz3).

Maar hoe vinden we nu de 2e- en 3e- prijswinnaars? Dan kunnen we niet meer MAX gebruiken.
In cel C4 staat dan ook een andere formule: =GROOTSTE(LoterijOvz3[Aselect];2)
ofwel zoek de tweede in grootte in  de kolom Aselect van de tabel LoterijOvz3.
U begrijpt: in plaats van MAX in cel C3 hadden we ook de functie GROOTSTE met een parameter 1 kunnen gebruiken!

NB uiteraard hadden we in dit geval de winnaars ook kunnen selecteren met de functie KLEINSTE.


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