Getallen afronden

Voorbeeldbestanden bij dit artikel:
  • Getallen afronden
  • 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 naamEngelstalige naam
    AFRONDEN.N.VEELVOUDMROUND
    AFRONDEN.BENEDEN.WISKFLOOR.MATH
    AFRONDEN.BOVEN.WISKCEILING.MATH
    AFRONDENROUND
    AFRONDEN.NAAR.BOVENROUNDUP
    AFRONDEN.NAAR.BENEDENROUNDDOWN
    INTEGERINT
    GEHEELTRUNC
    RESTMOD




    Homepage

    Rubriek onderdelen

    Over Wim

    Wim Gielis is Business Intelligence consultant en Excel expert

    Andere links