Doelzoeken en invoer controleren in Excel

Marco den Teuling
0

Inhoudsopgave

Excel doelzoeken gegevensvalidatie fout opsporen voorkomen waarde zoekenDe functie doelzoeken in Excel bespaart je veel rekenwerk. Binnen een handomdraai kom je te weten vanaf welke verkoopprijs een product winst oplevert. Via de gegevensvalidatie voorkom je dat er ongeldige waarden in je spreadsheets belanden. En verder nog: handige methoden om fouten op te sporen.

Veel formules in Excel voeren zeer complexe berekeningen door. Andere ontlasten je door je het arbeidsintensieve trial-and-error-werk uit handen te nemen. Doelzoeken is zo’n handige optie. Deze tool lost bij benadering vergelijkingen op en rekent net zo lang door met variabele gegevens totdat hij een oplossing vindt voor de opgegeven doelwaarde, of erachter komt dat er geen oplossing mogelijk is.

Een voorbeeld: je hebt nog 1575 euro nodig om een auto te kunnen kopen. Dit gat wil je dichten met winst uit aandelen. Via Doelzoeken kun je laten berekenen welke koers je nodig hebt om je aandelen te verkopen en het gewenste bedrag te behalen. In een spreadsheet voer je de aankoopprijs, orderprovisie en fees in. Klik dan in de cel voor de winst en start het Doelzoeken via ‘Gegevens / Wat-als-analyse / Doelzoeken’. Voer als doelwaarde 1575 in en bij ‘door wijzigen van cel’ het adres van de verkoopkoers. Na een klik op OK wordt berekend welke koers nodig is om het gewenste eindbedrag te behalen.

Excel-doelzoeken-gegevensvalidatie-waarde-zoeken-bepalen

De totale kosten van een bedrijf zijn opgebouwd uit het totaal van vaste en variabele kosten. Vaste kosten zoals de huur en afschrijvingen zijn onafhankelijk van de hoeveelheid productie en blijven constant. De variabele kosten hangen echter af van de hoeveelheid geproduceerde producten. Deze bestaan uit kostenposten zoals materiaal, provisie en werkzaamheden door derden. Voor een commerciële rekening zet je de omzet en kosten in een spreadsheet tegenover elkaar. De winst bepaal je door alle kosten van de omzet af te trekken.

Het uitgangspunt van de berekening is een bepaalde te verwachten winst. Als je daarvan uitgaat kun je de minimale verkoopprijs bepalen. Maar is deze verkoopprijs wel competitief? Biedt een concurrent misschien een veel lagere verkoopprijs? In zo’n situatie is het handig om het breakevenpoint te bepalen. Dit is het punt waar de omzet hoger is dan de kosten oftewel waar je winst maakt. Stel een cel in voor de winst met als doelwaarde 0 en kies bij ‘door wijzigen van cel’ de cel met de verkoopprijs.

Excel doelzoeken waarde bepalen nul

Fouten voorkomen

Als je spreadsheets bouwt die later door andere gebruikers ingevuld worden, kun je foutieve invoer helpen voorkomen. Een gebruiksvriendelijke spreadsheet geeft een gebruiker al bij het invoeren van data aan dat de invoer niet geldig is. Hiervoor gebruik je de Excel-functie ‘gegevensvalidatie’. Deze checkt de invoer op vooraf gespecificeerde data of waarden en laat alleen geldig verklaarde waarden toe.

Start de gegevensvalidatie via ‘Gegevens / Hulpmiddelen voor gegevens’ en klik dan op het icoontje Gegevensvalidatie. Kies in het venster op het eerste tabblad (Instellingen) voor de optie ‘aangepast’. Op deze manier kun je met behulp van formules bepaalde invoer toestaan of juist niet. Via AANTAL.ALS(Bereik;Criterium) kun je bijvoorbeeld dubbele entry’s in een lijst voorkomen door als criterium ‘<=1‘ in te voeren. Met WEEKDAG(A1;2)<6 sta je bij het invoeren van een datum alleen werkdagen toe. Op het tabblad Foutmelding kun je een verklarend stukje tekst meegeven. Bij het invoeren van een foutieve input verschijnt deze tekst in een waarschuwingsvenster op het scherm. Een duidelijke uitleg van de fout helpt een gebruiker beter op weg dan de cryptische foutmeldingen van Excel zelf.

Door slimme combinaties van IS-functies met andere functies kun je het invoeren van bepaalde tekens voorkomen. Zo wijst de formule =ISFOUT2 (VIND.SPEC(" ";cel)) invoer af die ongewenste spaties bevat. Dit voorkomt het invoeren van ’12 3′. Excel accepteert deze waarde wel, maar kan er niet mee rekenen. De formule zoekt via de VIND.SPEC-functie naar spaties en geeft de plek in de tekst weer. Hiervoor kun je ook de vrijwel identieke functie VIND.ALLES gebruiken. Deze houdt ook rekening met hoofdletters en kleine letters, maar dat speelt geen rol bij het zoeken naar spaties. Zodra de zoekfunctie een spatie vindt en een waarde groter dan nul teruggeeft, geeft ISFOUT2 de waarde WAAR terug. Zo weet de gegevensvalidatie dat het om niet toegestane invoer gaat.

Excel gegevensvalidatie ongeldige invoer spatie

IS-functies combineren

Door het gebruik van IS-functies kun je cryptische foutmeldingen onderdrukken en vervangen door duidelijkere varianten. Zo levert ISFOUT2(WAARDE) de waarde WAAR als de volgende foutcodes worden getoond: #WAARDE!, #VERW!, #DEEL/0!, #GETAL!, #NAAM? en #LEEG!. Bij de functie ISFOUT(waarde) komt daar nog de foutmelding #N/B bij. In combinatie met de ALS-functie kun je met de formule =ALS(ISFOUT(A1);"Fout – controleer je invoer!";A1*1,19) een tekst aangeven. Bij een foutieve invoer (WAAR) wordt de tekst getoond, anders wordt de als andere uitvoer opgegeven berekening uitgevoerd.

Fouten duidelijk aangeven

In een werkblad dat meerdere fouten bevat, kun je de fouten snel en overzichtelijk laten weergeven. Open het ‘Ga naar’- venster via F5 of Control+G en klik via de optie Speciaal op Formules. Deactiveer hier Getallen, Tekst en Logische waarden, zodat alleen nog Fouten blijft staan. Zodra je op OK klikt, krijg je alleen de cellen te zien die een foutmelding triggeren.

Formulecombinaties zijn ook samen met voorwaardelijke opmaak te gebruiken. Met behulp van de functie ISFOUT(waarde) kun je de foutmeldingen gekleurd laten weergeven. Kies via ‘Start / Voorwaardelijke opmaak’ de menuoptie ‘Nieuwe regel’ en selecteer in de lijst het item ‘Een formule gebruiken om te bepalen welke cellen worden opgemaakt’. Als formule geef je de functie ISFOUT() met de cellenreeks aan. Via de knop Opmaak kun je op het tabblad Opvulling een kleur kiezen. Met de formule =ALS(ISFOUT(A1);"Fout";"") toont Excel in plaats van de cryptische standaardfoutmelding het woord “Fout” en laat anders de cel leeg.

Je kunt naar wens ook tekens uit het lettertype Wingdings gebruiken voor een visuele foutmelding. De tekens 74, 75 en 76 van dit lettertype leveren een lachende, neutrale of droevige smiley op, de tekens 67 en 68 een duim omhoog of omlaag. Zo levert de formule =ALS(ISFOUT(C7);TEKEN( 76);TEKEN(74)) bij een fout in cel C7 een bedroefde smiley op, anders een lachende. Rond het invoeren van de formule af met de toetsencombinatie Ctrl+Enter en stel vervolgens Wingdings in als lettertype.

Foutwaarden benutten

Bij twee van elkaar onafhankelijke lijsten is de functie VERT.ZOEKEN(zoekwaarde;zoekbereik;kolom) erg praktisch. Maar als die een waarde niet vindt, toont Excel normaliter de foutmelding ‘#NB’. Door dit te ondervangen met ISNB kun je de standaardmelding vervangen door een eigen tekst, waar de gebruiker meer aan heeft. De volledige formule wordt dan bijvoorbeeld =ALS(ISNB(VERT.ZOEKEN(C4;bonustabel;2));"geen omzetcijfers" ; VERT.ZOEKEN(C4;bonustabel;2)). Het is vaak zinvol om op bepaalde fouten te reageren. Met de functie TYPE.FOUT(foutwaarde) kun je het type fout bepalen en die met behulp van andere functies op verschillende manieren afhandelen. Deze functie levert voor elk fouttype een ander getal terug: #LEEG! = 1, #DEEL/0! = 2, #WAARDE! = 3, #VERW! = 4, #NAAM? = 5, #GETAL! = 6, #N/B = 7; al het andere levert #N/B.

Excel eigen foutmelding ISNB melding

Komt op een werkblad een deling door nul voor, dan kun je die melding vervangen door een tekst als ‘geen gegevens’. Dat is bijvoorbeeld handig als je een formule doorvoert naar andere cellen, waarbij sommige geen volledige brongegevens hebben. Dat wordt op deze manier netjes aangeduid. Delen door nul wordt gedetecteerd als fouttype 2. In combinatie met de functies ALS() en ISNB kun je ongeldige gegevens als volgt aangeven: =ALS(ISNB(TYPE.FOUT(B11/ C11));B11/C11;ALS(TYPE.FOUT(B11/ C11)=2;"nul filialen")). Daarbij voert =ALS(ISNB(TYPE.FOUT(B11/C11));B11/C11) de berekening normaal uit als er geen fout is. Als er wel een fout is en die is van het type 2 dan schrijft ALS(TYPE.FOUT(B11/C11)=2;"nul filialen") de tekst “nul filialen” in de cel.

Excel eigen foutmelding TYPE.FOUT welke fout melding functie

Via de formulebalk krijg je vaak een beter beeld van langere formules. Je kunt daarin klikken op een willekeurige gebruikte functie in de formule. Gebruik vervolgens het knopje ‘Functie invoegen’ (fx), dan verschijnt het venster ‘Functieargumenten’. Daarin zie je behalve een toelichting op de functie ook het tussenresultaat voor de huidige cel. Klik op andere delen van de formule voor andere tussenresultaten. Sluit af met de Esc-toets om de formule ongewijzigd te laten.

Conversie door Excel

Met de functie CONVERTEREN(cel;meeteenheid;meeteenheid) rekent Excel om tussen meeteenheden. Hij vereist drie argumenten: de te converteren waarde, de bijbehorende meeteenheid en de meeteenheid waarnaar geconverteerd moet worden. De meeteenheden (zie afbeelding voor voorbeelden) moeten tussen aanhalingstekens geplaatst worden. Zo zet de formule =CONVERTEREN(H13;"km";"mi") de afstandswaarde in cel H13 om van kilometers naar mijlen.

Excel converteren omrekenen conversie meeteenheid

Andere functies converteren waarden tussen twee verschillende getalsystemen. De functie DECIMAAL(tekst;grondtal) kan bijvoorbeeld het binaire getal “0000010” omzetten in het decimale getal 2. Omgekeerd kan BASIS (getal;grondtal,minimumlengte) een decimaal getal converteren naar een binair getal. Geef je voor grondtal 16 op in plaats van 2, dan converteer je tussen decimale en hexadecimale getallen. De parameter minimumlengte is niet verplicht, als je die weglaat krijgt het resultaat geen voorloopnullen. De functies HEX.N.BIN(getal), HEX.N.DEC(getal;posities) en HEX.N.OCT (getal;posities) zetten hexadecimale getallen om in binaire, decimale en octale getallen. DEC.N.OCT (getal;posities) converteert decimale naar octale getallen.

Wil je Arabische naar Romeinse getallen omzetten, dan komt ROMEINS(getal,type) van pas. Het getal mag daarbij niet negatief zijn of groter dan 3999. Via de optionele parameter ‘type’ geef je met het cijfer 0, 1, 2, 3 of 4 de schrijfwijze aan voor het resultaat. Type 0 is de standaard Romeinse schrijfwijze voor getallen, elk hoger cijfer staat voor een beknoptere variant (bijvoorbeeld XDIX in plaats van het klassieke CDXCIX voor 499). Omgekeerd kun je met ARABISCH(tekst) Romeinse getallen omzetten naar Arabische.

(Rainer Schwabe / Alieke van Sommeren / Marco den Teuling, c’t magazine 4/2018)

Nog meer tips voor Excel nodig? Ontdek Excel-functies voor snel converteren, opmaken en meer of lees alle informatie in c’t magazine.

Deel dit artikel

Marco den Teuling
Marco den TeulingHad als eerste eigen computer ooit een 16-bit systeem, waar van de 48 kilobyte toch echt niet ‘genoeg voor iedereen’ was. Sleutelt graag aan pc’s, van de hardware tot het uitpluizen van de BIOS-instellingen. Vindt ‘Software as a Service’ een onbedoeld ironische naamgeving.

Lees ook

Raspberry Pi als NAS-systeem gebruiken? Dit moet je weten!

Wist je dat je een Raspberry Pi als Network Attached Storage (NAS) kunt gebruiken? Zo voldoet de Raspberry Pi 4 aan de belangrijkste eisen om dit te k...

Je Raspberry Pi op afstand bedienen? Zo krijg je het voor elkaar met SSH!

De Raspberry Pi op afstand bedienen is handig en kan op verschillende manieren. Via SSH is het makkelijkste op te zetten, we laten zien hoe.

0 Praat mee
avatar
  Abonneer  
Laat het mij weten wanneer er