Tagarchief: voorwaardelijke opmaak

Voetbal en gegevensvalidatie

27 februari 2015ExcelGegevensvalidatie, Grafiek, Grootste, Index, Indirect, Namen, Vergelijken, voorwaardelijke opmaakGijs Verbruggen

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.

Om 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):

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
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.
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
de 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,
* klik 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?

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
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
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.

selecteer cel J4 in het tabblad CompOvz2 van het Voorbeeldbestand
kies binnen de menutab Gegevens in het blok Hulpmiddelen voor gegevens de optie Gegevensvalidatie
er opent zich een keuzescherm:
bij Toestaan kiezen we de optie Lijst
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

Selecteer 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.
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):

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
bij Toestaan kiezen we opnieuw de optie Lijst
bij 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).
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

12 februari 2015ExcelCeleigenschappen, Opmaak, voorwaardelijke opmaakGijs Verbruggen

Het zuiden van Nederland maakt zich op voor Carnaval.

Waar 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

Bij 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:

selecteer alle getallen die een nieuwe opmaak moeten krijgen
kies Start in de menubalk en binnen het blok Getal, de optie Duizendtalnotatie (de 000)
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):

selecteer alle cellen die deze opmaak moeten krijgen
klik met de rechtermuisknop op de selectie en kies Celeigenschappen; dit kan ook sneller mbv Ctrl-1 (Ctrl vasthouden en de 1 indrukken)
kies het tabblad Getal en bij de Categorie weer Getal
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:
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:

selecteer alle cellen in kolom E die de opmaak moeten krijgen
kies Start in de menubalk en binnen het blok Stijlen de optie Voorwaardelijke opmaak
in het submenu op Nieuwe regel klikken
neem als type Alleen cellen opmaken met
en zorg dat onderstaande regel wordt aangemaakt
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:

selecteer een cel in kolom E
kies Start in de menubalk en binnen het blok Stijlen de optie Voorwaardelijke opmaak
in het submenu op Regels beheren klikken
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.
we voegen een regel toe via Nieuwe regel. Zorg dat de cel een rode achtergrond krijgt als de waarde kleiner is dan nul.
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.

In 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:

selecteer alle relevante cellen in kolom F (in dit geval F7:F75)
kies Start in de menubalk en binnen het blok Stijlen de optie Voorwaardelijke opmaak en dan weer Nieuwe regel
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.
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.
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
Een 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.

Aantallen turven

26 januari 2015Access, ExcelAantal, Aantal.als, Aantalarg, Array-formule, Besturingselement, Gekoppelde afbeelding, Herhaling, Integer, Kopiëren, Namen, Plakken speciaal, Rest, voorwaardelijke opmaakGijs Verbruggen

De afgelopen weken kreeg ik in diverse situaties vragen over het tellen van aantallen.

Wat simpel lijkt, wil in geautomatiseerde systemen nog wel eens ingewikkelder uitpakken.
Of het nu het tellen is van het aantal regels in een Excel-bestand, het aantal lege velden in een Excel-kolom, het aantal records in een Access-tabel of het aantal niet-lege namen in een Access-record: het blijft goed opletten wat en hoe er geteld moet worden.

Aantallen turven:
toen ik dit intikte, gingen mijn gedachten weer terug naar de vele gezellige avonden, die we lang geleden met een groepje hebben doorgebracht met het kaartspel Toepen. Bij dit spel worden de strafpunten op een bier- (of sinas-?)viltje geturfd door het zetten van streepjes.

Voor de liefhebbers volgt hieronder, na wat uitleg over het turven van aantallen in Excel en Access, een “geautomatiseerd bierviltje”.
Ik heb daarbij ook een weinig bekende manier van kopiëren gebruikt.

Excel

In het Voorbeeldbestand staat in het tabblad Aantal vanaf cel D15 een overzicht van de omzet per maand en regio van een kleine witgoedhandel (ze verkopen alleen stofzuigers, strijkijzers en broodroosters).

Uiteraard willen we de totale omzet weten (dat lossen we simpel op via een Som-formule); maar vandaag zijn we meer geïnteresseerd in Aantallen.

Excel kent diverse standaard-functies om aantallen te berekenen.
De meest simpele (en meest gebruikte) functie is Aantal. Maar als we die toepassen op het bereik D15:D158 (de maanden) dan krijgen we als resultaat 0; idem voor Regio en Product. Pas het turven van aantallen in de kolom Bedrag levert een aantal groter dan nul op, namelijk 142. De functie Aantal telt alleen maar de inhoud van numerieke cellen!

NB een datum is voor Excel ook een getal; dus de functie Aantal turft ook datums (zie ook de cellen L18:M21 in het Voorbeeldbestand).

De functie Aantalarg voldoet al beter aan de verwachtingen (zie het overzicht hierboven): deze functie telt hoeveel cellen er gevuld zijn, onafhankelijk van de inhoud.

Mbv de functie Aantal.lege.cellen (denk aan de puntjes!) kunnen we analyseren of en hoeveel lege cellen er in een cellenbereik voorkomen.

Voor de volledigheid heb ik ook nog enkele voorbeelden van de functies Aantal.als en Aantallen.als opgenomen; zie voor nadere uitleg het artikel Tellen met voorwaarden.

Turf-alternatief

Om snel te kunnen achterhalen in welke regel er gegevens ontbreken, heb ik achter iedere regel, in kolom H, mbv de functie Aantalarg geteld hoeveel velden er gevuld zijn.
Door cellen, die NIET 4 opleveren, via Voorwaardelijke opmaak te kleuren zien we direct in welke regels het fout gaat
(in één van de komende artikelen zal ik op Voorwaardelijke opmaak terugkomen).

Voor de liefhebbers nog een andere manier van tellen waarbij gebruik wordt gemaakt van zogenaamde Array- of Matrix-formules.
Wanneer we in Excel in een cel intypen =D15<>”” (we kijken dus of cel D15 NIET leeg is) dan levert dit de waarde WAAR op (als die cel gevuld is tenminste!). Maar intern vertaalt Excel dit naar een 1 (en ONWAAR naar een 0; het blijft een computerprogramma!). Daar kunnen we gebruik van maken:
typ in een cel =1*(D15<>””) en we krijgen als resultaat 1.
NB denk aan de haakjes rond de voorwaarde.

We zouden in cel I15 kunnen intikken =1*(D15<>””)+1*(E15<>””)+1*(F15<>””)+1*(G15<>””) om te achterhalen hoeveel cellen er niet leeg zijn, maar dat kan makkelijker:

tik in cel I15 in =Som(1*(
selecteer de cellen D15 t/m G15
en tik dan in (allemaal in cel I15) <>””))
niet op Enter drukken, maar Ctrl-Shift-Enter

Excel zal nu voor alle cellen in het bereik D15:G15 beoordelen of die leeg is; vermenigvuldigd met 1 levert dit een serie 0’en en 1’en op, die door de functie Som bij elkaar worden opgeteld.

Door de combinatie Ctrl-Shift-Enter wordt er een Array- of Matrix-formule ingevoerd; in de formulebalk wordt dit weergegeven door {} rond de formule.

NB alleen op bovenstaande manier kun je een array-formule invoeren; het plaatsen van accolades rond een formule werkt niet!

Kopieer de array-formule naar beneden (via de vulgreep rechtsonder in cel I15 natuurlijk; zie het artikel Doorvoeren en sorteren).

Access

Binnen dit programma moeten we het doen met veel minder (functie-)mogelijkheden. Voor het tellen van het aantal records in een tabel kunnen we een Totaal-query maken en dan per veld in de regel Totaal: aangeven dat we het Aantal willen zien.
Het resultaat hiervan komt overeen met het gebruik van de functie Aantalarg in Excel; dus het aantal niet-lege velden wordt geturfd voor ieder type veld.

Willen we het aantal niet-lege velden binnen een record weten (net als de berekening in de cellen H15 en I15 hiervoor in Excel) dan zullen we zelf een formule moeten intypen in de query.

Optie 1:
Gevuld: IIf([Maand]<>””;1;0) + IIf([Regio]<>””;1;0) + IIf([Product]<>””;1;0) + IIf([Bedrag]<>0;1;0)

De eerste als-voorwaarde (in Access Iif) levert een 1 als Maand niet leeg is, anders 0; de tweede voorwaarde hetzelfde voor Regio enz. Door de 4 resultaten van de Iif’s op te tellen zien we per record hoeveel velden er gevuld zijn.

NB Bedrag is in Access een numeriek veld; de inhoud kunnen we dan niet vergelijken met een lege tekst, dus we controleren of de waarde niet gelijk is aan 0.

Optie 2:
Gevuld2: IIf(Not IsNull([Maand]);1;0) + IIf(Not IsNull([Regio]);1;0) + IIf(Not IsNull([Product]);1;0) + IIf(Not IsNull([Bedrag]);1;0)

Vergelijkbaar met Optie 1, maar we controleren mbv een Access-functie IsNull of een veld leeg is.

Optie 3:
Gevuld3: -(Not IsNull([Maand])) – (Not IsNull([Regio])) – (Not IsNull([Product])) – (Not IsNull([Bedrag]))

Dit is iets korter dan optie 2, waarbij we gebruik maken van de eigenschap dat de waarde WAAR in Access door -1 wordt voorgesteld (en ONWAAR door 0; dit laatste is WEL hetzelfde als in Excel!?).

NB denk aan de juiste plaats en het aantal van de haakjes.

Toepen

Zoals beloofd een “Excel-bierviltje”; zie het Voorbeeldbestand.
De (straf)puntentelling bij het kaartspel Toepen gebeurt van oudsher door het zetten van streepjes, waarbij een vijftal door een dwarsstreep wordt aangegeven. Heb je 15 strafpunten, dan lig je er uit; 14 punten is ook een bijzondere situatie: de tegenstanders krijgen bij verlies 2 strafpunten (of ze passen voor 1 punt). In het Brabants wordt dit “Pulleke” genoemd, op het “scorebord” aangeduid door een P.

In het tabblad ToepBasis worden alle benodigdheden voor het scorebord klaar gezet:

in het blok B2:C4 staan de gebruikte symbolen, waarbij de cellen C2 t/m C4 een overeenkomende naam hebben gekregen (zie het Naamvak, linksboven)
in de cellen B8:D12 staan de spelersnamen en hun scores: de stand binnen het lopende ‘potje’ en het aantal gewonnen ‘potjes’.
F6:H12 is het scorebord/Excel-bierviltje.
In kolom F worden de deelnemers gecombineerd met hun resultaten door cellen en teksten met behulp van het &-teken te koppelen; in cel F8 staat bijvoorbeeld =B8&” (“&C8&”/”&D8&”)”
kolom G is de meest interessante: hier wordt de score uit kolom C (de cellen C8:C12 hebben samen de naam Score gekregen) omgezet in de juiste streepjes:
=ALS(Score>=15;Vijftal&Vijftal&Vijftal;ALS(Score=14;Vijftal&Vijftal&Paal;HERHALING(Vijftal;INTEGER(Score/5))&HERHALING(Streepje;REST(Score;5))))
* als de Score groter of gelijk aan 15 is, dan wordt 3x de tekst uit de cel met de naam Vijftal geplaatst
* bij een Score van 14, twee vijftallen en het teken voor het “Pulleke”
* anders via de functie HERHALING zoveel vijftallen als we krijgen wanneer we Score door 5 delen (INTEGER betekent geheel getal) en zoveel streepjes als de REST is bij het delen van de Score door 5.
kolom H bevat een simpelere vorm van de formule uit kolom G
nog wat Voorwaardelijke opmaak toevoegen (‘kapot’: rode achtergrond, ‘Pulleke’: oranje achtergrond, meer dan 9 punten: oranje letters) en het Scorebord is klaar!

LET OP: de naam Score bevat de cellen C8 t/m C12. Wanneer we in kolommen rechts hiervan in een formule verwijzen naar Score, dan neemt Excel de met de rij overeenkomende waarde uit deze reeks. Dus in cel G8 wijst Score naar de waarde in cel C8, in G9 naar cel C9 etc.

Het “Excel-bierviltje” vinden we terug op het tabblad ToepScore.
Om het turven nog makkelijker te maken bevat het tabblad diverse Besturingselementen, een 10-tal zogenaamde Kringvelden . Hiermee is het mogelijk om de scores snel te verhogen en te verlagen. De Kringvelden zijn gekoppeld aan de diverse scores in het vorige tabblad; klik met de rechtermuisknop op een Kringveld en bekijk de instellingen onder de optie Besturingselement opmaken …
Kringvelden (en andere besturingselementen) vinden we onder de menu-tab Ontwikkelaars.

Het scoreformulier zelf is GEEN serie cellen maar een Excel-afbeelding; wanneer je er op klikt, zie je in de hoeken en in het midden van de randen plaatsen waar je met de cursor de afbeelding kunt vergroten en verkleinen.
En niet zo maar een statisch plaatje; nee, een zogenaamde Gekoppelde afbeelding. De inhoud van de afbeelding verandert met de inhoud van de cellen, waaraan deze gekoppeld is! Mooier kunnen we het niet maken….

Hoe maken we een gekoppelde afbeelding?

selecteer de cellen, die in de afbeelding moeten worden weergegeven (voor ons scorebord de cellen F6:H12 in het tabblad ToepBasis)
klik rechts in de selectie en kies Kopiëren
ga naar de cel waar de afbeelding moet komen, klik rechts en kies de optie Plakken speciaal ….
In het submenu kies je onder Andere plakopties de 4e mogelijkheid, Gekoppelde afbeelding
pas via de grepen aan de zijkant van de afbeelding de grootte aan en/of verplaats het plaatje naar de gewenste locatie.

NB Gekoppelde afbeeldingen kunnen een belangrijke rol spelen in rapportages. Wanneer je daar meerdere overzichten onder elkaar wilt weergeven, zal het meestal zo zijn, dat de gewenste kolom-breedtes van die overzichten niet met elkaar overeenkomen; maar Excel kan standaard maar één breedte weergeven.
Zet de overzichten naast elkaar op een tabblad (of nog beter op verschillende tabbladen) en maak van deze overzichten Gekoppelde afbeeldingen; deze kun je dan onder elkaar zetten, zonder dat de kolombreedtes elkaar nog beïnvloeden.

LET OP Gekoppelde afbeeldingen vergen veel van Excel en van je PC! Sla je werk regelmatig op en gebruik niet teveel dynamische afbeeldingen in één werkmap.

Kalender 2015

19 januari 2015ExcelIndex, Indirect, Namen, Verschuiving, voorwaardelijke opmaakGijs Verbruggen

Ik dacht het nieuwe jaar te beginnen met het maken van een Excel-kalender en die te beschrijven in een G-Info-artikel.
Maar ‘helaas’ te laat: de Indiase Excel-guru Chandoo heeft vorige week een heel mooie kalender gepubliceerd; die wil ik u niet onthouden.
Ik heb deze omgezet in het Nederlands en de basiskalender iets anders ingericht, zodat de opbouw waarschijnlijk begrijpelijker is (hoewel de Chandoo-routine voor de liefhebbers echt kicken is!).

Deze keer geen uitleg van het Voorbeeldbestand; ‘kijk’ zelf rond en ontdek hoe allerlei handige opties van Excel (zoals Voorwaardelijke opmaak) gebruikt kunnen worden om een mooie Excel-toepassing te creëren.
Maar begrijp je een optie niet: schroom niet om G-Info om uitleg te vragen!

Ben je geïnteresseerd in de oorspronkelijke kalender (en meer van het werk van Chandoo) en zijn achtergrondartikelen: kijk dan op Chandoo.org.

De aangepaste, Nederlandstalige versie kun je hier downloaden.

G-Info en de functie G_Info()

15 september 2014Access, Excel, VBA (Visual Basic for Applications)Datum, eigen functie, Namen, Opmaak, Select, voorwaardelijke opmaakGijs Verbruggen

In mijn vorige artikel heb ik het gehad over de functies CEL() en INFO().
LET OP: in Excel en Access wordt een functie altijd gevolgd door 2 ronde haken. Bij de meeste functies worden tussen die haakjes argumenten meegegeven.

De naam van die tweede functie triggerde me om een eigen functie G-Info() te ontwikkelen. Maar wat zou die functie moeten doen?

Deze week kwam ik op een idee: wij moesten al nadenken wanneer we volgend voorjaar een korte vakantie zouden plannen; ergens met Hemelvaart of Pinksteren. Maar op welke data vallen die volgend jaar?

Ik wist nog wel, dat deze feestdagen afhankelijk zijn van Pasen, maar wanneer is het nu precies eerste Paasdag?

Pasen

Even “googlen” en al snel was ik een stap verder. Wikipedia:

Het concilie van Nicea bepaalde in 325 dat Pasen moet worden gevierd op de zondag na de eerste volle maan in de lente.

We hoeven dus alleen maar te weten wanneer de lente begint, wanneer het dan weer volle maan wordt en wat dan de eerstvolgende zondag is en we zijn klaar!!

Nog maar even verder op Google rondgeneusd en toen bleken er al diverse voorbeelden beschikbaar voor het bepalen van de datum van eerste Paasdag.

Dat is dus minder makkelijk dan ik gedacht had!
Met dank aan de bedenkers van deze routine gaan we dit gebruiken als bouwsteen voor onze nieuwe functie G-Info().

In het Voorbeeldbestand kunt u deze functie aan het werk zien:

wanneer in cel C2 een jaartal wordt ingetikt zal in cel D2 worden weergegeven, wanneer Pasen in dat jaar valt.

In cel D2 staat de formule =BepaalPasen(Jaar). Wanneer deze functie wordt ingetikt, zal in eerste instantie in D2 een getal komen. Door deze cel een datum-opmaak mee te geven zien we, dat Pasen in 2014 op 20 april is geweest. Ik heb de cel een aangepaste opmaak meegegeven: niet alleen de datum komt in cel D2, deze wordt vooraf gegaan door de tekst “Pasen valt op”.

LET OP: het Voorbeeldbestand bevat een viertal zelf-gedefinieerde namen, zodat de gebruikte formules veel beter leesbaar zijn. Cel C2 bijvoorbeeld heeft de naam Jaar gekregen.
Voor uitleg over het definiëren van namen, zie het artikel Namen ipv celverwijzingen.

G_Info()

De functie G_Info() vormt een uitbreiding op de functie BepaalPasen. Als parameter/argument wordt niet allen het jaar meegegeven, maar ook van welke feestdag de datum bepaald moet worden. Als derde parameter dient nog meegegeven te worden of de functie een datum moet retourneren (een getal dus) of een tekst.

Als voorbeeld: in C7 staat de formule =G_Info(Jaar;$B7;C$6).
Deze levert als resultaat de datum voor Pasen (cel B7) in het jaar 2014 en wel als echte datum (niet als tekst; cel C6)). Het resultaat is een getal, die een datumopmaak mee heeft gekregen.

PS1 let op de $-tekens in de formule; deze zijn zodanig gekozen, dat bij het kopiëren van de formule (naar rechts en naar beneden) de juiste verwijzingen blijven bestaan.
PS2 ik had de formule graag G-Info genoemd, maar het -streepje is in een functienaam niet toegestaan.

De formule in cel D7 geeft ook de Paasdatum weer, maar nu is het resultaat een tekst.

Naast de Paasdatum levert G_Info() ook de data voor Pinksteren, Carnaval, Hemelvaart, Kerstmis en Nieuwjaar (die laatste 2 liggen nogal voor de hand!). Zoals we hierna zullen zien, zijn uitbreidingen makkelijk door te voeren. Een verjaardag of huwelijksdag vergeten is er dus niet meer bij!

VBA

Eigen functies (in het Engels: User Definied Function, UDF) worden op dezelfde plaats gedefinieerd als waar macro’s worden geregistreerd: in de VBA-editor. Wanneer u daar nog nooit mee hebt gewerkt, bekijk dan het betreffende artikel.

Een korte uitleg van het programma:

Allereerst definiëren we de naam van de functie: G_Info.
NB zoals al eerder aangegeven is een – (streepje) niet toegestaan.
Daar achter, tussen haakjes, geven we aan welke parameters/argumenten/waarden aan de functie worden meegegeven: het Jaar, waarvoor de feestdagen bepaald moeten worden (moet een Integer=geheel getal zijn), voor welke Feestdag moet er informatie opgeleverd worden (een String=tekst) en een parameter TekstUitvoer (van het type Boolean; kan alleen de waarden WAAR of ONWAAR bevatten).
De groene tekst (achter het ‘-teken) is commentaar en wordt door het programma niet gebruikt.
In het vervolg van het programma willen we tusseninformatie ‘onthouden’ daarom definiëren we via Dim een variabele x.
Het programma moet nu afhankelijk van de inhoud van de parameter Feestdag de datum van Pasen of van Pinksteren of van Carnaval etcetera genereren.
Binnen VBA is daar een handige constructie voor:
Select Case ….
Case Voorwaarde1
Vervolg1
Case Voorwaarde2
Vervolg2
……
End Select
Wanneer in onze functie aan de parameter Feestdag de waarde Pasen wordt meegegeven, moet het programma de Paasdatum gaan bepalen. Afhankelijk van de inhoud van TekstUitvoer (WAAR of ONWAAR) levert het programma een tekst of een datum/getal op. Dit resultaat wordt in de hulpvariabele x bewaard.
De Select-optie wordt afgesloten met een Case Else: als er aan de parameter Feestdag een andere tekst wordt meegegeven dan waar in ons programma rekening mee is gehouden, zal dit programmagedeelte worden uitgevoerd.

Na afsluiting van de Select-constructie (dus na End Select) moeten we nog zorgen, dat de de functie in Excel het resultaat van het programma weergeeft. Aangezien we het resultaat in de variabele x hebben vastgelegd sluiten we de functie af met G_Info=x.

Het mag nu niet moeilijk meer zijn om de functie uit te breiden met voor u belangrijke feestdagen als verjaardag, huwelijksdag enzovoorts.

PS de twee in het Voorbeeldbestand gebruikte functies (BepaalPasen en G_Info) zijn zonder aanpassingen ook bruikbaar in Access. Kopieer de functies naar de VBA-omgeving (Module) van Access en u kunt ze direct in een query inzetten.

Kalender

In het voorbeeld is ook een jaaroverzicht opgenomen, waarin direct de diverse feestdagen worden geaccentueerd:
In dag 1 van januari staat de formule =Datum(Jaar;Maand;Dag) en deze formule is naar rechts en naar beneden gekopieerd. Deze Excel-functie genereert afhankelijk van het jaar, maand- en dagnummer de betreffende datum (in Excel-formaat, dus een getal; zie ook het betreffende artikel over Datums).
Jaar is de naam van cel C2, Maand de naam van het bereik met de cijfers 1 t/m 12 voor de maandomschrijvingen en Dag is het bereik met de cijfers 1 t/m 31.
Alle betreffende cellen hebben bij cel-eigenschappen het datumformaat “ddd” meegekregen: dus alleen de weekdag wordt afgekort getoond.

De blanco cellen op het einde van diverse maanden, de kleuren voor de weekenden en de kleuren voor de feestdagen worden m.b.v. Voorwaardelijke opmaak geregeld (via de menu-optie Start in het blok Stijlen).