MENU
 
Home
Nieuwsbrief
GC in de media
SiteMap
 
 
Tips en Tricks
Excel
Word
Photoshop
PowerPoint
Surf Tips
Gmail
Android
iPad
Excel ClipTips
 
cursus Office 2016
Access
Excel
Outlook
PowerPoint
Word
 
cursus Office 2013
Excel
PowerPoint
Word
Nieuw in Excel 2013
Nieuw in Word 2013
Nieuw in PowerPoint 2013
 
cursus Office 2010
Access
Nieuw in Excel 2010
Excel automatiseren
Excel
OneNote
Outlook
PowerPoint
Word
 
cursus Office 2007
Access
Excel
Outlook
PowerPoint
Publisher
Visio
Word
 
cursus Office
2000-2002-2003-XP
Access
Excel
PowerPoint
Outlook
Word
 
cursus Photoshop
CC -3D
Animatie
CS6
CS5
CS4
CS2 - CS3
Lightroom 3
Elements 6
Mask Pro
Nik Collection
 
cursus Dreamweaver
CS3
 
cursus Illustrator
CS4
 
cursus Flash
CS4
 
cursus Fireworks
CS4
 
cursus Paint Shop Pro
X en X2
 
cursus Premiere
Elements 7-8
 
cursus Joomla
Joomla 1.5
 
Sociale Netwerk sites
Facebook
LinkedIn
Twitter
 
iPad
Apps
 
Diversen
Celtx
CSS
DropBox
Firefox 3.6
GIMP
Internet Explorer 9
LIME
Linux
OpenSUZE
PREZI
ProShow Producer
YouTube
 
cursus Google
Agenda
Analytics
Gmail
Zoeken
Picasa 3
SketchUp
Chrome
Street View
 
cursus Windows
Live Movie Maker
Windows 10
Windows 8
Windows 7
XP
Vista
 
cursus Office '97
Word
Excel
 
 

 

Tips Excel   go to : Index
       
Tip 29    
Deze Tip werd geschreven door Valentin, waarvoor mijn dank.
 
Formules gebruiken in gegevensvalidatie.

In Cursus Excel 2007 Lessen 066, 067 en 068 en Cursus Excel 2010 lessen 70, 71 en 72 wordt het gebruik van enkele opties voor gegevensvalidatie besproken. In deze tip bespreken we enkele andere opties.

 
Waarde van de geselecteerde cel moet hoger zijn dan bovenliggende cel.

Als eerste voorbeeld nemen we een  bestand(1) waar we  bv. wekelijks(2) ons gasverbruik noteren. Om te voorkomen dat we een foute meterstand ingeven, de meterstanden moeten immers oplopend zijn, gaan we gebruik maken van een formule.

 
Vul in cel B3 een beginmeterstand in, klik in cel B4, druk de Shift-toets in, klik in cel B55.
Ga op het lint naar “Gegevens” en klik op “Gegevensvalidatie”.
 
Op het tabblad “Instellingen”(1) van het venster “Gegevensvalidatie” kiezen we bij “Toestaan:” de optie “Aangepast”(2), in het invulvak  “Formule:” typen we het isgelijkteken =, we klikken in cel B4, we typen de vergelijkings operator > (is groter dan) en we klikken in cel B3, (de formule is nu =B4>B3), we vullen eventueel de nodige gegevens in op de tabbladen “Invoerbericht”(3) en “Foutmelding”(4) en we klikken OK.
 

De gegevensvalidatie wordt automatisch aangepast voor de resterende geselecteerde cellen, we kunnen dit testen door een willekeurige cel te selecteren waarop we de validatie hebben toegepast bv. cel B20, het venster “Gegevensvalidatie” terug op te roepen  waar we de aangepaste formule zien voor de cel B20, nl =B20>B19.

 

De formule die we ingeven voor gegevensvalidatie moet geformuleerd worden voor de eerst geselecteerde cel, de eerst geselecteerde cel in een geselecteerd gebied is nl. lichter ingekleurd(1).

 

In de nu volgende voorbeelden gaan we er van uit dat de we de cellen A1 tot en met A10 selecteren en dat de cel A1 de eerst geselecteerde cel is, en we gaan op het lint naar “Gegevens” en klikken op “Gegevensvalidatie”, en op het tabblad “Instellingen” van het venster “Gegevensvalidatie” kiezen we bij “Toestaan:” de optie “Aangepast”.

 
Geen dubbele waarden toegelaten.
We gebruiken daarvoor de functie AANTAL.ALS.
De functie AANTAL.ALS telt het aantal cellen in een bereik (parameter 1) dat voldoet aan een  criterium (parameter 2)dat men opgeeft.
Formule is =AANTAL.ALS($A$1:$A$10;A1)=1 en druk OK. We maken de cellen A1:A10 absoluut voor de parameter bereik zodat deze zich niet aanpast voor de cellen A2 tot en met A10.
Nu kunnen we gelijk welke waarde maar één keer ingeven in de cellen A1 tot en met A10.
 
Geen weekenddagen toegelaten.

Als we datums willen ingeven van werkdagen (geen zat. en zondagen)
We gebruiken daarvoor de functies EN en WEEKDAG.
De functie EN wordt vaak gebruikt om andere functies die logische tests uitvoeren uit te breiden.
De functie WEEKDAG geeft als resultaat de dag van de week voor een datum. De dag wordt standaard weergegeven als een geheel getal van 1 (zondag) tot en met 7 (zaterdag).
Formule is =EN(WEEKDAG(A1)<>1;WEEKDAG(A1)<>7) en druk OK.

 
Totaal bedrag limiteren

Als een bepaald bedrag niet mag overschreden worden bv. 1000
Formule is =SOM($A$1:$A$10)<=1000 en druk OK. . We maken de cellen A1 :A10 absoluut voor de parameter bereik zodat deze zich niet aanpast voor de cellen A2 tot en met A10.
Nu mag het totaal bedrag voor de cellen A1 tot en met A10 de 1000 niet overschrijden.

 
Alleen tekst toegelaten

Als het alleen toegelaten is om tekst in te geven.
We gebruiken de functie: ISTEKST
Met de functie ISTEKST wordt gecontroleerd of de inhoud van de cel tekst is.
Formule is =ISTEKST(A1),en druk OK.

 
Beginletter

Als de tekst met een bepaalde letter moet beginnen bv. X (Let op, dit is niet hoofdlettergevoelig, dus zowel kleine letters als hoofdletters zijn toegestaan)
We gebruiken de functie: LINKS
De functie LINKS geeft het eerste teken of de eerste tekens in een tekenreeks als resultaat, op basis van het aantal tekens dat men opgeeft, de functie LINKS heeft 2 parameters nl.
tekst  is vereist. De tekenreeks met de tekens die u wilt ophalen .
aantal-tekens  is optioneel. Het aantal tekens dat men met LINKS wilt ophalen.
aantal-tekens moet groter zijn dan of gelijk zijn aan nul.
als aantal-tekens groter is dan de lengte van tekst, geeft LINKS alle tekst als resultaat.
als men aantal-tekens weglaat, wordt uitgegaan van de waarde 1.
vb. =LINKS(A1) geeft als resultaat het eerste teken van cel A1.
Formule is =LINKS(A1)="X" en druk OK.

 
Beginletters + lengte tekst

Als de tekst met bepaalde letters moet beginnen bv.XYZ, gevolgd door een koppelteken, en exact 9 tekens lang mag zijn.
We gebruiken de functie: AANTAL.ALS
Formule is =AANTAL.ALS(A1;"XYZ-?????")=1 en druk OK. (Let op, dit is niet hoofdlettergevoelig, dus zowel kleine letters als hoofdletters zijn toegestaan, bv. XYZ-ABCDE, xyz-abcde, en op de plaats van de vraagtekens zijn letters én cijfers toegestaan dus ook XYZ-12ABC.)

 
Cijfer en hoofdlettercombinatie
Als de celinhoud een combinatie moet zijn van cijfers en hoofdletters en een koppelteken,
 bv. XYZ-123-ABC.
We gebruiken daarvoor de functies EN, GELIJK, HOOFDLETT, LINKS, MIDDEN, RECHTS, GETAL en LENGTE.

Opgelet! in Excel 97, 2003 en 2007 worden sommige functies anders genoemd.

 

De functie GELIJK vergelijkt twee tekenreeksen en geeft als resultaat WAAR als de reeksen identiek zijn en ONWAAR als dit niet het geval is. Er wordt verschil gemaakt tussen hoofdletters en kleine letters. Verschillen in opmaak worden echter genegeerd. Gebruik GELIJK om tekst te controleren die in een document wordt ingevoerd, deze functie bestaat uit 2 parameters nl.
tekst1  is vereist. De eerste tekenreeks.
tekst2  is vereist. De tweede tekenreeks.

 

De functie HOOFDLETT zet tekst om in hoofdletters en heeft 1 parameter nl.
tekst  is vereist. De tekst die men wilt omzetten in hoofdletters. tekst kan een verwijzing of een tekenreeks zijn

 

De functie LINKS: zie voorbeeld Beginletter

 

De functie MIDDEN geeft als resultaat een bepaald aantal tekens van een tekenreeks, gerekend vanaf de opgegeven positie en op basis van het aantal opgegeven teken en bestaat uit 3 parameters nl.
tekst   is vereist. De tekenreeks met de tekens die u wilt ophalen.
begin_getal  is vereist. De positie van het eerste teken dat u uit tekst wilt ophalen. Het eerste teken in tekst heeft de waarde 1 als begin_getal, enzovoort.
aantal-tekens  is vereist. Het aantal tekens dat men met MIDDEN uit tekst wilt ophalen.
vb. =MIDDEN(A1;4;2) geeft als resultaat 2 tekens vanaf teken 4 uit cel A1, dus teken 4 en 5 van cel A1.

 

De functie RECHTS geeft het laatste teken of de laatste tekens in een tekenreeks als resultaat, op basis van het aantal tekens dat men opgeeft en heeft  2 parameters nl.
tekst  is vereist. De tekenreeks met de tekens die u wilt ophalen.
aantal_tekens is optioneel. Het aantal tekens dat u met RECHTS wilt ophalen.
vb. =RECHTS(A1;3) geeft als resultaat de 3 laatste tekens van cel A1.

 

De functie GETAL converteert een tekenreeks die overeenkomt met een getal naar een getal en heeft 1 parameter nl.
tekst  is vereist. De tekst tussen dubbele aanhalingstekens of een verwijzing naar een cel met de tekst die men wilt converteren.

 

De functie LENGTE geeft als resultaat het aantal tekens in een tekenreeks.

 
Om zo een lange formule samen te stellen is het gemakkelijker om deze eerst samen te stellen in een cel, we kunnen dan gebruik maken van de formulehulp(1) die we niet ter beschikking hebben bij het opmaken van een formule in het venster “Gegevensvalidatie”. We nemen bv. cel B1.
 

Omdat er aan verschillende voorwaarden moet worden voldaan beginnen we met de functie EN
We typen =EN( vervolgens nemen we de functie GELIJK die 2 waarden vergelijkt,  parameter 1 is cel A1 en parameter 2 is HOOFDLETT(A1) we typen het sluitende haakje van de functie GELIJK

De formule tot nu is
=EN(GELIJK(A1;HOOFDLETT(A1)) ,elke voorwaarde voor de functie EN wordt gescheiden door puntkomma, dus typen we de puntkomma ; de volgende voorwaarde is dat de eerste 3 tekens letters moeten zijn groter of gelijk aan A, hier nemen we de functie LINKS(A1;3)>=”A” we typen puntkomma ; voor de volgende voorwaarde dat de eerste 3 tekens letters moeten zijn kleiner of gelijk aan Z  LINKS(A1;3)<=”Z”

De formule tot nu is
=EN(GELIJK(A1;HOOFDLETT(A1)); LINKS(A1;3)>=”A”;LINKS(A1;3)<=”Z”
We typen weer een puntkomma om naar de volgende voorwaarde te gaan ; hier moeten we bepalen dat het vierde teken een koppelteken moet zijn, we nemen de functie MIDDEN MIDDEN(A1;4;1)=”-“

De formule tot nu is
=EN(GELIJK(A1;HOOFDLETT(A1));LINKS(A1;3)>=”A”;LINKS(A1;3)<=”Z”;
MIDDEN(A1;4;1)=”-“
we typen weer een puntkomma als scheiding voor de volgende voorwaarde ;
Nu bepalen we dat vanaf het vijfde teken drie cijfers moeten komen die groter zijn dan 001, we nemen de functies GETAL en MIDDEN GETAL(MIDDEN(A1;5;3))>=1 we typen weer een puntkomma voor de volgende voorwaarde ; vanaf het vijfde teken moeten drie cijfers komen die kleiner zijn dan 999
GETAL(MIDDEN(A1;5;3))<=999

De formule tot nu is
=EN(GELIJK(A1;HOOFDLETT(A1));LINKS(A1;3)>=”A”;LINKS(A1;3)<=”Z”; MIDDEN(A1;4;1)=”-“;GETAL(MIDDEN(A1;5;3))>=1;GETAL(MIDDEN(A1;5;3))<=999
We typen weer een puntkomma als scheiding voor de volgend voorwaarde ;  dat moet weer een koppelteken zijn op de achtste plaats MIDDEN(A1;8;1)=”-“

De formule tot nu is
 =EN(GELIJK(A1;HOOFDLETT(A1));LINKS(A1;3)>=”A”;LINKS(A1;3)<=”Z”; MIDDEN(A1;4;1)=”-“;
GETAL(MIDDEN(A1;5;3))>=1;GETAL(MIDDEN(A1;5;3))<=999;MIDDEN(A1;8;1)=”-“
We typen weer een puntkomma als scheiding voor de volgend voorwaarde ;
De laatste drie tekens moeten drie letters zijn groter of gelijk aan A, we nemen de functie RECHTS  RECHTS(A1;3)>=”A” we typen weer  puntkomma ; voor de volgende voorwaarde dat de laatste  3 tekens letters moeten zijn kleiner of gelijk aan Z  RECHTS(A1;3)<=”Z”

De formule is nu =EN(GELIJK(A6;HOOFDLETT(A6));LINKS(A6;3)>="A";LINKS(A6;3)<="Z";MIDDEN(A6;4;1)="-";
GETAL(MIDDEN(A6;5;3))>=1;GETAL(MIDDEN(A6;5;3))<=999;MIDDEN(A6;8;1)="-";
RECHTS(A6;3)>="A";RECHTS(A6;3)<="Z"
We typen weer een puntkomma ; en om de lengte te bepalen nemen we de functie LENGTE, we  typen  LENGTE(A1)=11 en we typen het sluitende haakje van de functie EN ) en we drukken Ctrl+Enter zo blijft de cel met de formule geselecteerd.

 

De formule is nu alles achter elkaar getypt (voor Excel 2010):
EN(
GELIJK(A1;HOOFDLETT(A1));
LINKS(A1;3)>="A";
LINKS(A1;3)<="Z";
MIDDEN(A1;4;1)="-";
GETAL(MIDDEN(A1;5;3))>=1;
GETAL(MIDDEN(A1;5;3))<=999;
MIDDEN(A1;8;1)="-";
RECHTS(A1;3)>="A";
RECHTS(A1;3)<="Z";
LENGTE(A1)=11)

 
De formule is nu alles achter elkaar getypt (voor Excel 97, 2003 en 2007):
=EN(
GELIJK(A1;HOOFDLETTERS(A1));
LINKS(A1;3)>="A";
LINKS(A1;3)<="Z";
DEEL(A1;4;1)="-";
WAARDE(DEEL(A1;5;3))>=1;
WAARDE(DEEL(A1;5;3))<=999;
DEEL(A1;8;1)="-";
RECHTS(A1;3)>="A";
RECHTS(A1;3)<="Z"
LENGTE(A1)=11)
 

Om te weten als de formule naar behoren werkt typen we in cel A1 een juiste letter en cijfercombinatie dit moet dan als resultaat geven “WAAR”, en vervolgens  een foute combinatie die  als geeft resultaat “ONWAAR”.
We wissen eerst de inhoud van cel A1, doen we dit niet en er staat een foute combinatie in de cel dan zal deze fout na het ingeven van de gegevensvalidatie niet herkent worden als foute combinatie, vervolgens selecteren en knippen we de formule van cel B1 vanaf de formulebalk, we selecteren een aantal cellen bv. A1 tot en met A10 we gaan op het lint naar “Gegevens” en klik op “Gegevensvalidatie”, op het tabblad “Instellingen” van het venster “Gegevensvalidatie” kiezen we bij “Toestaan:” de optie “Aangepast”, en we plakken de formule in het invulvak  bij “Formule:” en klikken OK.

 

Zo zie je, met de optie “Aangepast” voor gegevensvalidatie zijn de mogelijkheden praktisch onbeperkt.

 
Heb je vragen betreffende deze tip, mail me Valentin
 
Index

All courses now available in English:
www.swotster.com

copyright © 2012 - Swotster Ltd - Hong Kong - China