Tagarchief: Keuzelijst

Gegevensvalidatie

Excel, , , ,
Voorbeeldbestand: GegVal.xlsxDownload

In andere artikelen op deze site is al regelmatig gebruik gemaakt van Gegevensvalidatie. Meestal om er voor te zorgen dat er bij het invoeren van gegevens geen ‘vervuiling’ ontstaat (is de plaatsnaam nu Wanroy of Wanroij, moet de afdeling aangeduid worden met Marketing of met Verkoop, heet die medewerker nu Eric Jansen of Erik Janssen?)

Bij iedere Excel-sheet, waarin gegevens worden ingevoerd, is consistentie van groot belang om te zorgen dat bij analyses of rapporten de juiste zaken bij elkaar worden genomen.

In dit artikel kijken we naar de mogelijkheden van Gegevensvalidatie. Daarbij maken we gebruik van keuzelijsten. Niet echt ingewikkeld, maar het wordt wel leuk als we gebruik gaan maken van ‘meervoudige’ of afhankelijke keuzelijsten.

Gegevensvalidatie

In het Voorbeeldbestand op het tabblad Decl staat een ‘systeem’ waarmee binnen een bedrijf declaraties kunnen worden vastgelegd.

Niet alle invoer is toegestaan; aan alle 4 kolommen worden bepaalde eisen gesteld.

  1. Datum: in de cellen H2 en H3 (met de namen DtmVan en DtmTot) liggen een onder- en bovengrens vast.
    De ondergrens is een harde datum, de bovengrens is de datum van vandaag.
  1. Maand: dit is de maand waarop de declaratie betrekking heeft. Deze moet altijd kleiner of gelijk zijn aan de declaratiedatum
  2. voor Afd zijn maar 6 verschillende namen toegestaan
  3. het Bedrag moet altijd groter zijn dan 0 en kleiner dan 1000

Datum

  1. selecteer alle cellen uit de eerste kolom (behalve de kop natuurlijk)
  2. kies in de menutab Gegevens in het blok Hulpmiddelen voor gegevens de optie Gegevensvalidatie

  3. In de tab Instellingen zorgen we dat er alleen een datum is toegestaan.
    Bij Begindatum vullen we de naam van de betreffende cel in (druk op F3 en kies de naam), bij Einddatum verwijzen we uiteraard naar de betreffende cel.
    LET OP bij de validatie worden de twee grenzen als toegestaan gerekend
    NB1 vul je de naam handmatig in, vergeet dan het =-teken niet!
    NB2 in ons voorbeeld is het onderste vinkje niet nodig (we hebben alle betreffende cellen geselecteerd). Wil je later een wijziging aanbrengen, kies dan één cel en plaats het vinkje.
    NB3 de datum zal altijd ingevuld moeten worden; verwijder dus het vinkje bij Lege cellen negeren.
  1. Op de tweede tab kun je een Invoerbericht maken. Deze info wordt zichtbaar wanneer de betreffende cel is geselecteerd.
  1. Het is ook mogelijk om een eigen foutmelding te genereren die getoond wordt wanneer de invoer niet overeenkomt met de aangegeven grenzen.
    Wanneer je als Stijl de optie Stop kiest, zal Excel een juiste invoer afdwingen:

Maand

  1. selecteer alle cellen uit de tweede kolom
  2. kies in de menutab Gegevens in het blok Hulpmiddelen voor gegevens de optie Gegevensvalidatie
  1. Bij Toestaan kiezen we de optie Aangepast.
    Excel biedt dan de mogelijkheid om een formule in te tikken:
    =C3<=MAAND(B3)
    LET OP deze formule gaat er van uit dat een cel in regel 3 actief/geselecteerd is; anders een verwijzing naar de betreffende regel gebruiken.
  1. Ook voor deze kolom is een Invoerbericht aangemaakt.
  1. Een foutmelding geeft de gebruiker houvast wanneer de invoer niet juist is.
    Hier hebben we bij Stijl gekozen voor Waarschuwing; de gebruiker kan er dan altijd voor kiezen om toch door te gaan:

Afd

  1. selecteer alle cellen uit de derde kolom
  2. kies in de menutab Gegevens in het blok Hulpmiddelen voor gegevens de optie Gegevensvalidatie
  1. de toegestane afdelingsnamen staan in de cellen J3:J8.
    NB Zorg dat het vinkje aanstaat bij Vervolgkeuzelijst; de gebruiker kan dan kiezen uit de lijst en hoeft niets handmatig in te tikken.
  1. Op de tab Foutmelding is de Stijl Info gekozen. Dat betekent dat bij een ‘verkeerde’ invoer wel een waarschuwing op het scherm komt:

    maar door het klikken op OK, wordt de invoer toch geaccepteerd.

Bedrag

  1. selecteer alle cellen uit de vierde kolom
  2. kies in de menutab Gegevens in het blok Hulpmiddelen voor gegevens de optie Gegevensvalidatie
  1. voor het bedrag kiezen we bij Toestaan de optie Decimaal.
    Als grenzen wordt verwezen naar 2 cellen met de namen BedrVan en BedrTot.
    LET OP de waardes van de grenzen zijn allebei toegestaan.

Controle

Wil je achteraf weten of er toch nog fouten zitten in de invoer, kies dan in de menutab Gegevens in het blok Hulpmiddelen voor gegevens het vinkje onder de optie Gegevensvalidatie:

Excel omcirkelt alle foute invoer. De naam test2 hoort uiteraard daar niet thuis.

In de lege regel van de tabel tblDeclaraties wordt aangegeven, dat de Datum en de Afd verplicht zijn.

Wil je de ovalen rond de fouten weer verwijderen: kies de derde optie bij Gegevensvalidatie.

Afhankelijke keuzelijst

In het vorige voorbeeld hebben we gebruik gemaakt van Namen om te verwijzen naar bepaalde cellen. Bij de Afd bestaat de toegestane Lijst uit een bereik/range van cellen. Ook die hadden we met behulp van een naam kunnen aangeven.

Op het tabblad AfhKeuze van het Voorbeeldbestand is de kolom Afd van een gegevensvalidatie voorzien door als Bron te kiezen voor de naam Afd.
Dit is een verwijzing naar het bereik G2:I2

Maar ook de bereiken met persoonsnamen in de kolommen hebben een Naam gekregen; exact gelijk aan de omschrijvingen in regel 2.

NB dat is ook de reden dat er een underscore is gebruikt in plaats van een afbreek-streepje. In een Naam is dat laatste teken niet toegestaan. Maar ook een spatie, een /, een \ enzovoort kun je niet gebruiken in de definitie van een naam.

De cellen in de kolom Naam krijgen ook een gegevensvalidatie. Maar het zou natuurlijk mooi zijn als de reeks toegestane namen afhankelijk is van de invoer in de kolom Afd.

Uitgaande van cel D3 willen we dus alleen maar de namen zien die bij de afdeling Dir voorkomen.
Wanneer we bij de Bron van de gegevensvalidatie rechtstreeks zouden verwijzen naar cel C3, dan gaat het niet werken; dan is de naam Dir als enige toegestaan. Gebruiken we echter de functie Indirect dan zijn alle namen uit het bereik Dir toegestaan.

LET OP wanneer achteraf de afdeling wordt gewijzigd dan krijg je niet automatisch een foutmelding dat de persoonsnaam niet is toegestaan. Gebruik dus regelmatig de optie Ongeldige gegevens omcirkelen.
Met behulp van een klein beetje VBA is het makkelijk te regelen dat bij een wijziging van afdeling automatisch de persoon gewist wordt.

Afhankelijke keuzelijst 2

Er zijn (natuurlijk) nog wel meer manieren om een afhankelijke lijst te maken. In het tabblad Afh2 van het Voorbeeldbestand hebben we een meer dynamische methode geïmplementeerd.
Daar is een overzicht te vinden van de 50 gemeentes van Nederland met het grootste inwoner-aantal (op 1 jan 2021). In plaats van dat een gebruiker in de lijst moet gaan zoeken (dat is met 50 namen nog wel te doen, maar bij meer dan 300 is het wat lastiger) kan hij een keuze maken in cel F3, waarna in cel G3 het bijbehorende aantal inwoners wordt getoond.
De mogelijkheden voor de keuze in cel F3 zijn via Gegevensvalidatie beperkt; de toegestane lijst zijn alle namen uit kolom B.

Maar het is natuurlijk handiger om eerst de provincie te kiezen en afhankelijk daarvan pas de gemeentenaam; het aantal mogelijkheden is dan een stuk beperkter.

De validatie in cel G6 is recht toe recht aan.

Ook die van cel G7 komt bekend voor (zie hiernaast): we beperken de gemeentekeuze tot het bereik GemPerProv.

Maar in dat laatste zit nu net de crux: deze naam verwijst niet naar een bereik van cellen maar is via Formules/Namen beheren ingevoerd:
=VERSCHUIVING(
tblProvGem[[#Kopteksten];[Gem]];
VERGELIJKEN(ProvZoek;tblProvGem[Prov];0);
0;
AANTAL.ALS(tblProvGem[Prov];ProvZoek);
1)

In het tabblad Afh2 van het Voorbeeldbestand staat een Excel-tabel (met de naam tblProvGem) met alle betreffende provincies en bijbehorende gemeentes; gesorteerd eerst op Prov en dan op Gem.

Met behulp van de functie Verschuiving bepalen we de cellen met de gemeentes die bij een bepaalde provincie (in de cel met de naam ProvZoek) horen.

  • de functie Verschuiving start in de cel met de koptekst van de kolom Gem
  • maar het resultaat begint net zoveel regels lager als door de functie Vergelijken wordt gegenereerd
  • en 0 kolommen naar rechts of naar links, dus het resultaat staat in de kolom Gem
  • het resultaat bestaat uit zoveel regels als dat de gezochte provincie in de kolom Prov voor komt
  • en de breedte van het resultaat is 1 kolom

NB Wat een gedoe eigenlijk voor iets wat je met een draaitabel met een paar slicers in 1 minuut kunt bouwen. 😉 Zie het tabblad Afh2 in het Voorbeeldbestand.

Maar het gaat om het principe; op een ander moment is deze methode misschien dé oplossing voor uw probleem.

Alternatieve keuzelijsten

cel NIET geselecteerd

Een groot nadeel van gegevensvalidatie is dat je pas weet dat je een keuze kunt maken wanneer de betreffende cel is geselecteerd.

cel WEL gesecteerd

Gelukkig kent Excel nog andersoortige keuzelijsten (zie het tabblad KzLijst van het Voorbeeldbestand). Het kost wel wat meer moeite om dit te implementeren.

Keuzelijst met invoervak

We gaan eerst een Keuzelijst met invoervak maken:

  1. kies in de menutab Ontwikkelaars in het blok Besturingselementen de optie Invoegen
    NB ziet u de menutab niet: klik met de rechtermuisknop ergens in het lint, kies de optie Lint aanpassen en zet bij Hoofdtabbladen het vinkje aan bij Ontwikkelaars
  2. kies binnen de Formulierbesturingselementen de optie Keuzelijst met invoervak
    NB binnen de ActiveX-elementen vindt u een vergelijkbare keuzelijst. Deze is nog iets ingewikkelder te implementeren. Daar krijg je dan wel meer flexibiliteit voor terug. Bijvoorbeeld kan de inhoud qua lettertype en -grootte aangepast worden.
  3. ’teken met de muis’ waar je het invoervak wilt hebben en hoe groot deze moet zijn
  4. geef bij Invoerbereik de cellen op met de toegestane gegevens (in het voorbeeld de cellen B3:B14)
  5. kies bij Koppeling een lege cel (bijvoorbeeld J2)
  6. geef aan hoeveel regels er zichtbaar moeten worden wanneer het invoervak wordt geactiveerd

Maar we zijn nog niet klaar. Wanneer je een keuze maakt in de lijst wordt het overeenkomende volgnummer in de gekoppelde cel geplaatst (hier dus J2).
In het voorbeeld op het tabblad KzLijst hebben we de naam van de gekozen provincie nodig; die halen we in cel K2 op met de formule: =INDEX(tblProv2[Prov];J2)

In cel I3 halen we dan het totaal van het aantal inwoners van de gekozen provincie op:
=SOM.ALS(tblProvGem2[Prov];K2;tblProvGem2[InwAantal])

LET OP: het overzicht bevat alleen de 50 grootste steden, dus het totaal is niet echt het totaal van de provincie!

Keuzelijst

Als laatste kijken we nog naar een andere keuzelijst (zie tabblad KzLijst van het Voorbeeldbestand):

  1. kies in de menutab Ontwikkelaars in het blok Besturingselementen de optie Invoegen
  2. kies binnen de Formulierbesturingselementen de optie Keuzelijst
  3. ’teken met de muis’ waar je de lijst wilt hebben en hoe groot deze moet zijn
  4. geef bij Invoerbereik de cellen op met de toegestane gegevens (in het voorbeeld de cellen D3:D52)
  5. kies bij Koppeling een lege cel (bijvoorbeeld M8)
  6. zorg dat als Selectietype de optie Enkelvoudig is geselecteerd
    NB de andere 2 opties zijn alleen interessant als u deze keuzelijst met behulp van VBA wilt gebruiken

NB in het voorbeeld hebben we de gekoppelde cel ‘verstopt’ achter de keuzelijst.

In cel I8 zoeken we de gemeentenaam op die hoort bij het getal in cel M8.

Het ophalen van het inwoneraantal van die gemeente kan het handigst met de avz-methode (zie Zoeken: Index en vergelijken).

Om het resultaat van de keuze ook snel in de tabel te kunnen terugvinden hebben we daar een Voorwaardelijke opmaak aan toegevoegd.