Maandelijks archief: februari 2015

Voetbal en gegevensvalidatie

Excel, , , , , , ,

Hoewel de titel van dit artikel misschien anders doet vermoeden (het gaat dus NIET over het checken van persoonsbewijzen bij een voetbalwedstrijd), gaan we het hebben over het valideren van de invoer van gegevens in een cel in Excel.

De aanleiding hiervoor was een discussie op een internet-forum over “Lege velden in keuzelijst met invoervak“.
Al snel kwam daar de opmerking langs, dat het onderliggende probleem waarschijnlijk makkelijker en beter met gegevensvalidatie kon worden opgelost; dan was er ook geen VBA nodig.
Het probleem in het kort: de toegestane invoer in een cel is afhankelijk van de keuze, die in een andere cel is gemaakt.

voetbalcompetitieOm de daar geschetste oplossing in de praktijk te kunnen laten zien, heb ik de stand van de twee Nederlandse profvoetbalcompetities genomen (Bron: www.voetbaluitslagen.nl).
NB Het valt me nu pas op hoe goed de stad Eindhoven het doet!

Stand voetbalcompetities

Dus eerst maar eens een voorbeeld opzetten (zie Voorbeeldbestand):voetbalcompetitie

  1. eerst de standen van de twee competities onder elkaar gezet, gesorteerd op clubnaam, waarbij de kolom met de punten (Pnt.) voor ons het belangrijkste is
  2. dan een schema opzetten van de beste 3 per competitie (zie hierboven).
    Daarbij maak ik gebruik van de functie Grootste:
    =GROOTSTE(H15:H32;1)
    Dit levert het maximum op van de Eredivisiepunten; in het Voorbeeldbestand, tabblad CompOvz1, heb ik deze reeks een naam gegeven, ErePnt, zodat de bedoeling van de formule duidelijker is.
    Door de 1 te veranderen in 2 vinden we het één na hoogste puntentotaal enz.
    Via Index en Vergelijken (zie het artikel over Verticaal zoeken) vinden we de bijbehorende clubs.
    NB zoals uit de stand hierboven mag blijken gaat het bij een gelijk aantal punten niet altijd goed; er wordt geen rekening gehouden met doelsaldo.
  3. dan nog wat Voorwaardelijke opmaak ‘strooien’ over het overzicht (zie mijn vorige blog) en we krijgen wat beter inzicht in de voetbal-verhoudingen in Nederland.
    NB we zien nu wel dat AZ en Feyenoord een gelijk aantal punten hebben
  4. voetbalcompetitiede verhoudingen tussen de clubs kunnen ook grafisch worden weergegeven.
    Op de x-as staan de gegevens van de kolommen B en C; Excel zorgt zelf voor een duidelijke lay-out wat betreft de indeling van de twee competities.
    De titel is dynamisch, wat in dit geval inhoudt dat deze verandert wanneer de datum in cel B2 wordt gewijzigd:
    * maak een willekeurig titel aan,
    * kvoetbalcompetitielik ergens in de titel,
    * kies de formulebalk en tik een verwijzing naar de cel met de gewenste titel-tekst in, inclusief de naam van het tabblad en een !
    In het voorbeeld staat de titel-tekst in cel N27, waar met behulp van de functie Teken een scheiding tussen de twee elementen van de titel is gemaakt.

Combinatie van grafieken

De grafiek kan wel wat duidelijker: een betere scheiding tussen de twee competities, wie staan bovenaan, hoe staat mijn favoriete club er voor?voetbalcompetitie

  1. in het Voorbeeldbestand, op het tabblad CompOvz2, is het onderscheid tussen de competities geregeld door de behaalde punten in 2 verschillende kolommen te plaatsen, een extra kolomgrafiek toe te voegen, de overlap van de grafieken op 100% te zetten en de kleuren aan te passen
  2. daarna is er een label toegevoegd aan de nummers 1 tot en met 3: achter iedere club (in kolom F) staat een formule, die kijkt of de club bij de eerste 3 hoort:
    =ALS(D15=Ere_1;1;ALS(D15=Ere_2;2;ALS(D15=Ere_3;3;””)))
    Ere_1 is de naam van de cel, die het aantal punten van de aanvoerder van de ranglijst bevat etc.
    Het bereik F15:F52 is als een nieuwe grafiek toegevoegd; bij Opmaak is gekozen voor Geen opvulling maar wel zijn Gegevenslabels toegevoegd
  3. om onze favoriete club er te laten uitspringen, voegen we een nieuwe grafiek toe, die alleen de punten van deze club bevat; zie kolom G:
    =ALS(C15=Voorkeur;D15+E15;””)
    Geef deze grafiek een afwijkende kleur.

Gegevensvalidatie

In het vorige voorbeeld staat de naam van de favoriete club in cel J4, die de naam Voorkeur heeft gekregen.
De (groene) kolom in de grafiek bij de favoriet wordt natuurlijk alleen maar zichtbaar, wanneer die cel een bestaande clubnaam bevat. In Excel dwingen we dat af via Gegevensvalidatie.

  1. voetbalcompetitieselecteer cel J4 in het tabblad CompOvz2 van het Voorbeeldbestand
  2. kies binnen de menutab Gegevens in het blok Hulpmiddelen voor gegevens de optie Gegevensvalidatie
  3. er opent zich een keuzescherm:
    voetbalcompetitie
  4. bij Toestaan kiezen we de optie Lijst
  5. en als Bron geven we het bereik op, waar alle clubnamen staan; in het voorbeeld heeft dat bereik de naam Teams.
    LET OP: denk aan het =-teken

voetbalcompetitieSelecteer J4 en tik een clubnaam in; komt deze niet (precies) in de clublijst voor dan krijgt u een foutmelding. Het is daarom handiger om het ‘vinkje’ achter de cel te gebruiken, zodat u de keuzelijst kunt gebruiken.

‘Meervoudige’ keuzelijst

In het vorige voorbeeld bestaat de lijst, waaruit een favoriete club gekozen kan worden, uit 38 teams.
Via scrollen in de zijbalk van de keuzelijst is de gewenste club nog vrij snel te vinden. Bij langere lijsten is dat vaak onhandig.

Als voorbeeld zou het in dit geval makkelijker zijn om eerst een competitie te kiezen (Eredivisie of Jupiler) en daarna pas een club uit de gekozen competitie.voetbalcompetitie
Dat is wat ik bedoel met ‘meervoudige’ keuzelijst: de inhoud van de tweede wordt bepaald door de keuze in de eerste.

Op de volgende manier is dit in te regelen (zie het Voorbeeldbestand, tabblad CompOvz3):

  1. voor de competitie-keuze voegen we weer een gegevensvalidatie toe (aan cel J3): kies binnen de menutab Gegevens in het blok Hulpmiddelen voor gegevens de optie Gegevensvalidatie
  2. bij Toestaan kiezen we opnieuw de optie Lijst
  3. voetbalcompetitiebij Bron wordt meestal een celbereik van toegestane elementen opgegeven.
    In dit geval hebben we maar 2 mogelijkheden: Eredivisie of Jupiler. De namen van de betreffende clubs liggen vast in de celbereiken EreTeams, respectievelijk JupTeams. Deze 2 namen geven we als bron van de lijst op, gescheiden door een ; (punt-komma).
  4. aan cel J4 geven we weer een gegevensvalidatie.
    Bij Toestaan kiezen we weer Lijst; bij Bron zijn we geneigd om de cel J3 mee te geven, maar helaas: Excel zal dan alleen de letterlijke tekst uit die cel aan de keuzelijst meegeven (dus EreTeams of JupTeams).
    Nee, bij Bron moeten we invullen
    =INDIRECT(J3)
    De functie Indirect zorgt er voor, dat niet de inhoud van J3 zelf wordt gebruikt, maar de inhoud van het bereik waar J3 naar verwijst (EreTeams of JupTeams dus).

Voorwaardelijke opmaak

Excel, ,

Het zuiden van Nederland maakt zich op voor Carnaval.

CarnavalWaar de schmink bij Carnaval meer bedoeld is om het echte gezicht te verbergen of te versieren, is de opmaak bij Excel bedoeld om zaken te benadrukken, duidelijker te maken.

Zoals in eerdere artikelen beloofd, gaan we het deze keer hebben over Voorwaardelijke opmaak.
Iedereen maakt er wel eens gebruik van; misschien zonder het zelf te weten!
Er zijn allerlei vormen: van het simpel tussenvoegen van een punt, wanneer getallen groter zijn dan 1.000 tot het tonen van grafische tekens in een cel afhankelijk van andere gegevens in de Excel-werkmap.

Celeigenschappen/opmaak

opmaakBij celeigenschappen vinden we de meest gebruikte opmaak-opties van Excel. We beperken ons deze keer tot de opmaak van cellen met numerieke waarden.

Hebt u dat ook met grote getallen: hebben we het nou over tonnen of over miljoenen?
Zie Voorbeeldbestand

Excel biedt ons een snelle mogelijkheid om deze getallen beter leesbaar te maken:

  1. opmaakselecteer alle getallen die een nieuwe opmaak moeten krijgen
  2. kies Start in de menubalk en binnen het blok Getal, de optie Duizendtalnotatie (de 000)
  3. met de 2 knoppen rechts daarvan kun je meer of minder decimalen weergeven

NB de inhoud van de cellen verandert niet, slechts de weergave.

Moeten de negatieve getallen een andere kleur krijgen (meestal kiezen we daarvoor rood):

  1. opmaakselecteer alle cellen die deze opmaak moeten krijgen
  2. klik met de rechtermuisknop op de selectie en kies Celeigenschappen; dit kan ook sneller mbv Ctrl-1 (Ctrl vasthouden en de 1 indrukken)
  3. kies het tabblad Getal en bij de Categorie weer Getal
  4. kies geen decimalen, wel scheiding voor duizendtallen en kleur

Naast de standaard-opmaak die in Excel is ingebouwd, kunnen we de opmaak ook nog voor een groot gedeelte zelf bepalen.
Daartoe moeten we weer even terug naar de Celeigenschappen, het tabblad Getal. Wanneer we bij Categorie de optie Aangepast kiezen, laat Excel bij Type de code zien die gebruikt is voor de opmaak, bijvoorbeeld: opmaak
In dit geval bestaat de code uit 2 secties (gescheiden door een punt-komma); het eerste geeft aan hoe positieve getallen moeten worden weergegeven, de tweede geldt voor negatieve getallen. Zonder op alle details in te gaan, lijkt me de hoofdstrekking van de diverse codes duidelijk.

NB pas de opmaak via een van de standaardopties aan en bekijk bij Aangepast welke codes daarvoor gebruikt worden.

Excel kent in principe 4 opmaak-secties: voor positieve getallen, negatieve, voor de nul-waarde en voor tekst. Maar we mogen ook eigen secties maken; bijvoorbeeld als we alle getallen groter dan 100 blauw willen maken, gebruiken we in een sectie [Blauw][>100]#.##0

Deze laatste handigheid kan mooi toegepast worden bij grafieken, aangezien Celopmaak ook gebruikt kan worden bij het opmaken van de assen. Op deze manier kunnen bepaalde gebieden op een as een aparte kleur krijgen.

NB om nog meer mogelijkheden te ontdekken: bekijk alle opmaak in het tabblad CelOpmaak van het Voorbeeldbestand.

Opmaak afhankelijk van inhoud cel

Heel vaak kan het handig zijn om de opmaak van een cel afhankelijk te maken van de inhoud daarvan: onderscheid maken tussen positieve en negatieve waarden, analyse van nauwelijks zichtbare verschillen (of andersom wanneer zijn 2 waardes gelijk), controlegetal moet 0 (nul) zijn etc.

Dit is met Celeigenschappen niet of slechts moeizaam te regelen, waarbij de opmaak beperkt blijft tot het kleuren van de tekst.
Met de optie Voorwaardelijke opmaak komen we een stuk verder.

Laten we als voorbeeld de aan- en afmeldingen eens bekijken van een sportschool (zie Voorbeeldbestand, tabblad CelVoorw). Hiernaast is te zien, dat er in januari meer leden zijn vertrokken dan er zijn aangemeld. Om snel inzicht te krijgen in het maandresultaat hebben de cellen in kolom E een opmaak gekregen, die afhangt van de inhoud: voorwaardelijke opmaak dus.
Hoe doen we dat:

  1. opmaakselecteer alle cellen in kolom E die de opmaak moeten krijgen
  2. kies Start in de menubalk en binnen het blok Stijlen de optie Voorwaardelijke opmaak
  3. in het submenu op Nieuwe regel klikken
  4. neem als type Alleen cellen opmaken met
  5. en zorg dat onderstaande regel wordt aangemaakt
    opmaak
  6. kies een daarbij passende opmaak, bijvoorbeeld groene opvulling

Aanpassen voorwaardelijke opmaak
We zouden op dezelfde manier ook een rode kleur kunnen toevoegen aan cellen met een negatieve waarde, maar dat gaan we anders doen:

  1. selecteer een cel in kolom E
  2. kies Start in de menubalk en binnen het blok Stijlen de optie Voorwaardelijke opmaakopmaak
  3. in het submenu op Regels beheren klikken
  4. Excel is zo slim om te zien, dat we de huidige selectie willen beheren. Duidelijk is te zien wat onze eerste regel doet en op welke cellen deze betrekking heeft. De voorwaarde en de opmaak zijn te veranderen door Regel bewerken te kiezen.
  5. we voegen een regel toe via Nieuwe regel. Zorg dat de cel een rode achtergrond krijgt als de waarde kleiner is dan nul.
  6. vergeet niet deze regel van toepassing te laten zijn op alle relevante cellen in kolom E

NB druk op de functietoets F9 om de sheet opnieuw door te rekenen; aangezien de aan- en afmeldingen aselect gekozen worden, zal het resultaat ook iedere keer anders zijn.

Andere voorbeelden
In de kolommen G t/m J worden twee resultaten vergeleken. Als de waarde in kolom H gelijk is aan de waarde in I, dan zorgt de formule in kolom J ( in J3 staat bijvoorbeeld =H3=I3) er voor, dat het resultaat WAAR verschijnt (zo niet, dan ONWAAR). Via voorwaardelijke opmaak is in één oogopslag te zien waar de kolommen gelijke waardes bevatten (eventueel even F9 drukken voor nieuwe gegevens).

In cel O3 is een verbandcontrole ingebouwd: is het resultaat nul, dan kleurt de cel groen, anders rood (probeer maar eens uit als ergens in kolom O een resultaat wordt gewist of, wat in de praktijk nog wel eens wil gebeuren, als de formule in O door een harde waarde wordt overschreven.

Opmaak afhankelijk van inhoud van een andere cel

“Maar, wat als ….”
Een bekende frase in automatiseringsland: iedere keer moeten er weer grenzen verlegd worden.

opmaakIn het Voorbeeldbestand heb ik een tabblad Energie opgenomen, waarop per dag de meterstanden worden genoteerd. Excel berekent voor ons dan het dagverbruik.
Bovenaan berekenen we het lopende gemiddelde voor het verbruik van stroom en gas, zodat we in ieder geval kunnen zien of we onder of boven het gemiddelde scoren.
Met behulp van Voorwaardelijke opmaak willen we dat natuurlijk direct laten zien; eerst voor de stroom:

  1. selecteer alle relevante cellen in kolom F (in dit geval F7:F75)
  2. kies Start in de menubalk en binnen het blok Stijlen de optie Voorwaardelijke opmaak en dan weer Nieuwe regel
  3. als type kunnen we niet meer de tweede optie gebruiken; de voorwaarde hangt ook af van de inhoud van een andere cel. We moeten een formule gaan gebruiken, dus kiezen de laatste optie.
  4. tik dan als formule in: =F7<$F$3, kies een bijpassende Opmaak (tekst groen bijvoorbeeld; we zitten onder het gemiddelde) en klik OK
    NB een formule moet altijd met het =-teken beginnen, daarachter volgt de voorwaarde: F7<$F$3.
    F7 is een relatieve verwijzing, $F$3 een absolute; dat betekent dat als Excel deze voorwaarde één cel lager toepast hij deze interpreteert als F8<$F$3 etc.
  5. op dezelfde manier kunnen we een kleur geven aan het verbruik dat hoger uitkomt dan gemiddeld

Kijk in het Voorbeeldbestand hoe de overige opmaak is geregeld door Regels beheren op te roepen.

Nog een ander voorbeeld
opmaakEen klas heeft 3 proefwerken gehad en we zien direct wie gemiddeld lager dan 5,5 staat.

Ook hier is gebruik gemaakt van voorwaardelijke opmaak: op de cellen B4:F11 is de volgende formule als Nieuwe Regel ingevoerd:
=$F4<5,5
In de formule is de kolomnaam F absoluut, want voor alle cellen in B4:F11 kijken we voor de voorwaarde naar kolom F, maar de rijnaam 4 is relatief; dus in de cellen B5:F5 zal Excel beoordelen of F5 kleiner is dan 5,5 etc.