In dit artikel over Excel komen weer verschillende functies en formules aan de orde. Het streven is dat zowel de beginnende als de iets gevorderde gebruiker hiermee uit de voeten kan. Aan de hand van het voorbeeld kunnen we met heel wat functies aan de slag.

WAAR of ONWAAR

In het bereik E2:E12 staan de waarden WAAR en ONWAAR. In E2 staat de formule =D2<10 (minder dan 10) en dat geeft de waarde ONWAAR, want de waarde in D2 is immers 10. Als de formule eenmaal naar beneden gekopieerd is, zie je inderdaad dat de uitkomst van deze formule klopt.Het lijkt zinloos om zoiets in het bereik van kolom E te doen, maar het kan je in sommige gevallen helpen om een fout te traceren. Wat in kolom E gedaan is om de waarde te testen uit kolom D kan natuurlijk ook voor de andere kolommen gelden.

Nu willen we weten hoe vaak WAAR voorkomt in het bereik. Dat doen we met de formule =AANTAL.ALS(E2:E12;WAAR). Let op dat WAAR niet tussen aanhalingstekens staat. Immers als je een tekst in de formule wilt verwerken dan moet die wel tussen aanhalingstekens staan. Hier gaat het niet over tekst, maar over de uitkomst van de formule. Wil je weten hoeveel keer John in kolom A voorkomt gebruik je de formule =AANTAL.ALS(A2:A12;"John"). Dus John wel tussen aanhalingstekens.

In kolom B staan cijfers en getallen, de cijfers zijn links uitgelijnd en is dus tekst. Ik noem het cijfers omdat je met tekst niet kunt rekenen. Om te achterhalen hoe vaak er getallen in het bereik voorkomen geeft de formule =AANTAL(B2:B12) uitkomst. Te zien is dat in kolom C elf getallen zitten en in kolom B vijf. De links uitgelijnde cijfers zijn dus inderdaad geen getallen.

Vervangen van tekens

Er zijn een aantal opties om tekens te vervangen voorhanden in Excel. In kolom B staan zes cijferreeksen waar een punt in voorkomt. Met andere woorden: tekst waar niet mee gerekend kan worden. Daar willen we verandering in brengen, want er moet wel mee gerekend worden.

Excel kent de optie Vervangen en deze is zeer geschikt om de punten in komma’s te veranderen. Ik noem het een optie omdat Excel ook de functie VERVANGEN() kent, maar daar kom ik straks op terug. Om de optie Vervangen te gebruiken ga je als volgt te werk: Tab start > Bewerken Zoeken en selecteren Vervangen. Klik op Vervangen. Overigens geven de toetsen Ctrl+H hetzelfde effect.

In het tekstveld achter Zoeken naar zet je een punt, achter Vervangen door een komma. Als je het bereik van tevoren hebt geselecteerd klik je op Alles vervangen en klaar zijn we. Excel lijnt de getallen de ene keer wel links uit, de andere keer niet, feit is dat de cijfers nu getallen geworden zijn. Zo je wilt kun je handmatig de getallen rechts uitlijnen.

SUBSTITUEREN en VERVANGEN

In B12 moet de punt vervangen worden door een komma. Zoals beloofd komt nu de functie VERVANGEN() aan bod. De formule is: =VERVANGEN(B12;3;1;","). 

Oud_tekst is B12, begin_getal is het derde teken dus 3, aantal_tekens die vervangen moeten worden is 1, nieuw_tekst is ",". De formule werkt. Tegelijk lopen we tegen een nadeel op van deze functie.

 

Als de formule naar rij 9 is gekopieerd krijgen we een rare uitkomst te zien. Aangezien in de formule is aangegeven dat het derde teken moet worden vervangen vervalt de tweede nul, wordt er wel een komma geplaatst maar de punt blijft staan. Met andere woorden: de formule moet aangepast worden als er meer dan twee cijfers voor de punt staan. Niet zo handig toch?

Nee, dan kun je beter de functie SUBSTITUEREN() gebruiken.

Ook binnen deze functie moet als eerste de oud_tekst worden opgegeven, gevolgd door nieuw_tekst, en als optionele mogelijkheid de rang_getal. Van de laatste optie is in deze formule geen gebruik gemaakt, aangezien er maar één punt te vervangen valt. De formule ziet er dan zo uit: =SUBSTITUEREN(B2;".";",")*1. Waarom *1 (keer 1)? Omdat de functie er anders geen werkbaar getal van maakt.

In het geval je bijvoorbeeld de punt voor duizendtallen wilt laten staan gebruik je onderstaande formule. Nu is wel de rang_getal opgegeven, dus wordt de tweede punt vervangen door een komma. Wel is het zo dat de uitkomst gewoon tekst blijft en er dan ook niet mee gerekend kan worden. Vul je de formule aan met *1 dan is het een getal waar mee te rekenen valt. De punt van duizendtallen zal dan echter wel verdwijnen.

De functie SUBSTITUEREN() vervangt slechts een teken. Wil je meer tekens vervangen dan zul je de functie moeten nesten. Zoals in het voorbeeld te zien is.

Kopiëren en plakken als waarde

Uitgaande van ons voorbeeld kun je het beste de optie Vervangen gebruiken, dat zal duidelijk zijn. Immers heb je voor de besproken functies een extra kolom nodig. Als het niet anders kan dan een functie gebruiken, dan kun je verkregen waarden plakken in de kolom waarin de oorspronkelijke gegevens staan. 

In dit geval staan de verkregen waarden in kolom C. Selecteer het bereik waar de formules in staan. Druk op de toetsen ctrl+C om het bereik te kopiëren. Selecteer B2 en klik op het pijltje onder Plakken. Kies de optie Waarden (123) en de waarden uit kolom C staan nu in kolom B, dus zonder formules. Kolom C kun je nu verwijderen. Klik met rechts op de kolomletter en kies voor Verwijderen.

MAX.ALS.VOORWAARDEN

In de vorige nieuwsbrief is de functie MAX() aan de orde geweest. Sinds versie 2016 van Excel is de functie MAX.ALS.VOORWAARDEN() beschikbaar.

 

In de E2 geef je het niveau op en in F2 de klasse.

Het maximumbereik is A2:A7, het criteriabereik1 is B2:B7 en criteria1 is F1. Het tweede criteriumbereik is D2:D7, criteria is E2. De uitkomst van de formule is 210. Meer uitleg over deze functie is denk ik niet nodig. Je kunt in plaats van cel referenties ook tekst en een waarde gebruiken. Zie daarvoor de formule in rij 11.