Getallen afronden
- 15/01/2016
 | Voorbeeldbestanden bij dit artikel: |
Inleiding
Bij mijn weten heb ik op mijn website in al die jaren nog nooit echt iets gedaan over het afronden van getallen. Ik heb wel onlangs geschreven over het opmaken van euro-bedragen, maar er is een verschil: bij opmaak van getallen wijzig je het onderliggende getal niet en toon je de gebruiker enkel een andere voorstelling. Bij het afronden van getallen ga je wel het onderliggende getal wijzigen.
Bij het nemen van beslissingen gaan we doorgaans niet vergelijken en beslissen op basis van niet-afgeronde getallen. Vandaar de noodzaak aan het makkelijk kunnen voorstellen van afgeronde getallen.
Hierboven toon ik een simpele opzet van hoe je op een flexibele manier kan afronden. In cel B2 heb je 4 mogelijkheden ten aanzien van het afronden
(zie de keuzemogelijkheden onderaan links). In cel B3 geef je aan tot op welk veelvoud je wil afronden. Meestal is 1 of 10 voor de hand liggend,
maar dat hoeft absoluut niet jouw keuze te zijn. De formule voor het afronden van het ruwe getal in B6 zien we dan in B8:
=ALS.FOUT(
ALS(B2 = "Niet afronden"; B6;
ALS(B2 = "Dichtstbijzijnde veelvoud"; AFRONDEN.N.VEELVOUD(B6;B3);
ALS(B2 = "Naar beneden afronden"; AFRONDEN.BENEDEN.WISK(B6;B3);
ALS(B2 = "Naar boven afronden"; AFRONDEN.BOVEN.WISK(B6;B3);
0))))
;0)
Je ziet hierboven de uitsplitsing voor de berekening van elk van de 4 componenten. Moet je meer dan 1 getal afronden, dan heb je misschien te maken met een tabel:
De functie KIEZEN is een handige: op basis van de keuze van de gebruiker pik je het juiste resultaat uit een lijst van 4 mogelijke resultaten:
=KIEZEN($B$17; F3; AFRONDEN.N.VEELVOUD(F3; $B$3); AFRONDEN.BENEDEN.WISK(F3; $B$3); AFRONDEN.BOVEN.WISK(F3; $B$3))
Relatieve formules
Het nadeel aan deze oplossing vind ik dat er nogal veel hardgecodeerde tekst staat in elk van de cellen in de tabel. Dit is niet optimaal. Tot slot van dit artikel doe ik jullie nog een zeer handige truc van de hand. Je kan formules ook relatief schrijven, als je gebruik maakt van een Naam in Excel:
=Afronding
is uiteraard geen bestaande functie in Excel, en al zeker niet in de Engelse versie van Excel die ik bezit ;-)
Is het dan misschien een functie die je programmeert in VBA ? Neen, ook niet, want het bestand dat jullie downloaden bovenaan deze pagina heeft extensie *.xlsx
en dat laat geen macro's of VBA-code toe. Bij de gedefinieerde namen hebben we dit:
De actieve cel staat in cel M3, en dat is een heel belangrijke notie. Als je dan bij de gedefinieerde namen een formule koppelt aan een naam, waarbij je cel F3 gebruikt (zonder dollartekens, dus een relatieve celverwijzing) dan past Excel telkens diezelfde verschuiving toe bij gebruik van "Afronding". Met de verschuiving die Excel toepast bedoel ik dat Excel, vertrekkende in de actieve cel, 7 kolommen naar links opschuift, 0 rijen naar boven/onder, en het getal in die cel ophaalt. In dit voorbeeld is dat dus de 3.449,5 € uit cel F3. Maar voor cel N4 wordt dat dan G4 als basisreferentie. Excel pas op het getal dat het ophaalt uit de andere cel de formule uit de naam toe, zoals in ons geval 1 van de 4 afrondingsprincipes. Leuk en handig om te weten ! Het voordeel is elke cel dezelfde formule heeft (=Afronding) en dat de hardgecodeerde elementen slechts op 1 plaats staan - in de gedefinieerde namen. Uiteraard moet je oppassen met relatieve celverwijzingen of je belandt vroeger of later in de verkeerde cel !
Andere afrondingsformules in Excel
De functies hierboven zijn niet de enige die je kan gebruiken in Excel om getallen af te ronden. Hier is een overzichtje:
| Nederlandstalige naam | Engelstalige naam |
| AFRONDEN.N.VEELVOUD | MROUND |
| AFRONDEN.BENEDEN.WISK | FLOOR.MATH |
| AFRONDEN.BOVEN.WISK | CEILING.MATH |
| AFRONDEN | ROUND |
| AFRONDEN.NAAR.BOVEN | ROUNDUP |
| AFRONDEN.NAAR.BENEDEN | ROUNDDOWN |
| INTEGER | INT |
| GEHEEL | TRUNC |
| REST | MOD |
