Anders tellen

Excellerend
Kwartaaltip 2016-3
Excellerend
Heemraadweg 21
2741 NC Waddinxveen
06 – 5115 97 46
[email protected]
BTW: NL1876.58.316 B.01
BANK: NL72ABNA053.68.25.491
KVK: 24389967
Anders tellen
Toen ik begin jaren negentig computerles kreeg werd mij uitgelegd dat een bit een één kan zijn
of een nul, en dat een byte 8 bits was. Omdat iedere bit twee waarden kon hebben, was het
maximaal aantal waarden 2^8 (2 tot de macht 8, ofwel 2x2x2x2x2x2x2x2). Maar omdat je begint
met tellen bij 0, is de maximale waarde 255.
Jaren daarvoor had ik al iets soortgelijks met wiskunde gehad. Een digitaal getal bestaat uit 7
balkjes die aan of uit zijn, voorbeelden:
De precieze vraag weet ik niet meer, maar het was een instinker. Hoeveel combinaties zijn er
mogelijk? Dat is 2^7. Maar omdat de combinatie waarbij alle balkjes uit zijn niet telde, was het
antwoord 127 (2^7 - 1).
Binair
Computers slaan alles binair op. Eén byte kan daarom maximaal de decimale waarde van 255
voorstellen. Worden de getallen groter, dan zijn er meer bytes nodig.
Wat heeft dit nu met Excellerend te maken? Een aantal jaar ben ik actief geweest op de website
www.beterrekenen.nl, een website waar je per werkdag vier sommetjes kunt krijgen om je
scherp te houden. Best leuk, ook voor kinderen. Eén van de regelmatig terugkerende sommetjes
betrof het binaire stelsel. Dan moest je twee binaire getallen optellen en een antwoord kiezen
uit drie of vier binaire getallen. Ik had daarvoor een lijst met 255 rijen gemaakt en had die vraag
altijd goed.  Maar ja, een uitdaging was het niet meer. Dus zocht ik die in Excel.
Ik zocht dus een formule die een binair getal kon omzetten in een decimaal getal. Zoiets is vast
op Internet te vinden, maar zelf puzzelen is veel leuker! De lijst met mogelijkheden had ik al in
Excel staan. In kolom A het binaire getal en in kolom C het decimale getal:
Pagina 1 van 5
www.excellerend.nl
Excellerend
Kwartaaltip 2016-3
Een ander belangrijk lijstje is de volgende met machten van 2:
Het binaire getal is opgebouwd uit 8 enen en nullen en we rekenen met machten van 2. Het
meest rechtse cijfer (de 8e) kan een 0 en een 1 betekenen. (Zie bovenste plaatje) De 7e kan een 2
(zie regel 3 bovenste plaatje) en een 0 betekenen. De 6e kan een 4 (regel 5) en een 0 betekenen
etcetera. Maar hoe krijg je dat in een Excelformule?
Als het binaire getal in cel A1 staat, kan het 8e cijfer als volgt achterhaald worden:
= RECHTS(A1;1)*2^0
In woorden: neem vanaf rechts gezien van het getal in cel A1 één cijfer en vermenigvuldig dat
met 2 tot de macht 0. 2^0 is gelijk aan 1. En het cijfers kan een 1 of en 0 zijn. De uitkomst is dan 1
of 0.
De waarde van het zevende cijfer is als volgt te bepalen:
= DEEL(A1;7;1)*2^1
In woorden: Neem van het getal in cel A1 het 7e cijfer en vermenigvuldig dat met 2 tot de macht
1. De vermenigvuldiging kan dan zijn 1 x 2 of 0 x 2. De uitkomst is dan 2 of 0.
Het 6e cijfer:
=DEEL(A1;6;1)*2^2
Dit wordt dan: 1 x 4 of 0 x 4.
En de anderen:
=DEEL(A1;5;1)*2^3. Dit kan zijn 1 x 8 of 0 x 8.
=DEEL(A1;4;1)*2^4. Dit kan zijn 1 X 16 of 0 x 16.
=DEEL(A1;3;1)*2^5. Dit kan zijn 1 X 32 of 0 x 32.
=DEEL(A1;2;1)*2^6. Dit kan zijn 1 X 64 of 0 x 64.
=LINKS(A1;1)*2^7. Dit kan zijn 1 X 128 of 0 x 128.
Zoals in het eerste rijtje te zien is, is het binaire getal 00000011 gelijk aan het decimale getal 3.
Controle: het 7e cijfer is gelijk aan 1 * 2 en het 8e cijfer is gelijk aan 1 x 1: 2 + 1 = 3.
Wat is dan het getal 00011011?
De vierde, vijfde, zevende en achtste cijfers zijn een 1. De rest levert toch nul op.
1 x 2^4 + 1 x 2^3 + 1 x 2^1 + 1 x 2^0 = 16 + 8 + 2 + 1 = 27. En dat klopt ook!
Pagina 2 van 5
www.excellerend.nl
Excellerend
Kwartaaltip 2016-3
De uiteindelijke Excelformule wordt dan de optelling van alle bovengenoemde formules:
=LINKS(A1;1)*2^7+DEEL(A1;2;1)*2^6+DEEL(A1;3;1)*2^5+DEEL(A1;4;1)*2^4+DEEL(A1;5;1)*2^3+D
EEL(A1;6;1)*2^2+DEEL(A1;7;1)*2^1+RECHTS(A1;1)*2^0
N.B. Omdat ik met de functie LINKS, DEEL en RECHTS werk, is het resultaat tekst, geen getal.
Optellen
Maar kun je dan in Excel ook binaire getallen optellen met een formule? Want dat was de opgave
op de website.
Vooraf kun je bedenken dat de getallen uit enen en nullen bestaan. De optelling van cijfers op
dezelfde positie is dan 0, 1 of 2. Mmmm, die 2 betekent dat op die positie een 0 moet komen te
staan, maar ook dat de som van de positie links ervan met ééntje opgehoogd moet worden.
Of anders gezegd, het achtste getal is een 1 als de optelling 1 is, en anders wordt het een nul:
=ALS(RECHTS(A2;1)+RECHTS(A3;1)=1;1;0)
Het cijfer op de 7e positie kan een 0, een 1 of een 2 zijn, maar het kan ook een drie worden als de
optelling van de twee cijfers op de achtste positie en 2 is! Mmmm, bij een oneven resultaat
wordt het een 1, en anders een 0.
=ALS(IS.ONEVEN(DEEL(A2;7;1)+DEEL(A3;7;1)+ALS(RECHTS(A2;1)+RECHTS(A3;1)=2;1;0));1;0)
In woorden wordt het al een heel verhaal. We tellen het zevende cijfer van de twee getallen bij
elkaar op èn tellen daarbij op een 1 (als de som van de twee achtste cijfers een 2 is) of een 0 (in
de andere gevallen).
Positie 6 wordt weer langer:
=ALS(DEEL(A2;6;1)+DEEL(A3;6;1)+ALS(DEEL(A2;7;1)+DEEL(A3;7;1)+ALS(RECHTS(A2;1)+RECHTS(A3
;1)=2;1;0)=2;1;0)=1;1;0)
Mocht je hier afhaken, dan snap ik dit. Ik vond de uitdaging ook niet leuk meer. Het aantal
mogelijkheden wordt namelijk te groot en de formule niet meer te behappen.
Excelformules
Hoe zou Microsoft dit opgelost hebben? In Excel zelf zijn verschillende formules om binaire
getallen om te zetten naar decimale getallen.
Typ in een cel =BIN en Excel vult het al aan tot:
De functie BIN.N.DEC converteert een binair getal naar een decimaal getal.
Maakt het nou veel uit of je ‘mijn’ formule gebruikt of de functie van Excel? Yep. Als ik met:
=ASELECTTUSSEN(0;1)&ASELECTTUSSEN(0;1)&ASELECTTUSSEN(0;1)&ASELECTTUSSEN(0;1)&ASEL
ECTTUSSEN(0;1)&ASELECTTUSSEN(0;1)&ASELECTTUSSEN(0;1)&ASELECTTUSSEN(0;1)
Pagina 3 van 5
www.excellerend.nl
Excellerend
Kwartaaltip 2016-3
geheel at random 8 enen en nullen achter elkaar plaats en dat in een kolom over alle 1.048.576
rijen van een werkblad, dan doet mijn formule er bijna 16 seconden over om de decimale
getallen te berekenen. De functie =BIN.N.DEC redt het in minder dan 4 seconden.
Maar kun je dan met Excel makkelijk binaire getallen optellen? Ja dat kan, maar met een omweg.
Je moet eerst van de binaire getallen decimale getallen maken, die optellen en dan weer
omzetten naar een binair getal.
In de cellen B2 en B3 staan binaire getallen en in D2 en D3 de functie =BIN.N.DEC() om er
decimale getallen van te maken. In cel D4 staat de optelling ervan met =SOM(D2:D3) en in B4
staat de functie =DEC.N.BIN(D4) welke een decimaal getal omzet naar een binair getal.
Maar ik vind het niet mooi want het getal bestaat niet uit 8 enen en nullen! Dat kan op twee
manieren aangepast worden.
Manier 1 is 8 nullen vastplakken voor het resultaat, en dan vanaf rechts 8 karakters pakken:
1) =RECHTS(”0000000”&DEC.N.BIN(D4);8)
Manier 2 is meer cosmetisch. Je kunt namelijk via de opmaak van een cel bepalen hoeveel cijfers
voor de komma je wilt zien. Maar dan moet het resultaat van de functie eerst als een decimaal
getal worden, want dat is het nu niet. De truc hiervoor is het te vermenigvuldigen met 1.
=DEC.N.BIN(D4)*1 zorgt ervoor dat het een getal wordt. Getallen zijn standaard rechts uitgelijnd,
terwijl tekst standaard links uitgelijnd is.
De bovenste cel bevat alleen de formule =DEC.N.BIN(D4). De onderste is: =DEC.N.BIN(D4)*1
Als de uitkomst een getal geworden is, ga je naar de celeigenschappen (Ctrl+1, rechter muisknop
> Celeigenschappen of via het pijltje linksonder in de groepering Getal op de tab Start.
.
Klik op de groep Aangepast en typ 8 nullen in het vak rechts:
Pagina 4 van 5
www.excellerend.nl
Excellerend
Kwartaaltip 2016-3
Klik dan op OK en het resultaat is:
Tip: Speel ook eens met de functie =ROMEINS()
Einde kwartaaltip 3-2016
De kwartaaltip(s) zijn als PDF te downloaden via: www.excellerend.nl\kwartaaltips.html
Wilt u een op maat gemaakte cursus Excel voor uw organisatie regelen? Zoekt u ondersteuning
in het bouwen van rekenmodellen, controles, of denkt u dat iets mogelijk is in Excel maar u weet
niet hoe? Neem dan contact op met Richard Meijles: 06 – 5115 97 46 of via e-mail:
[email protected].
Wilt u geen kwartaaltips meer ontvangen? Klik dan op: [email protected]
Hebt u een verzoek voor een kwartaaltip? Klik dan op: [email protected] en vermeld tevens
uw Excelversie.
Richard Meijles
Pagina 5 van 5
www.excellerend.nl