Categorie archief: Excel

WK-voetbal voorspeller (deel 3, incl VBA)

22 juni 2014Excel, VBA (Visual Basic for Applications)Aselect, Grootste, Index, SOM.ALS, VergelijkenGijs Verbruggen

Nog gauw even verder met de uitleg van het model uit de blog van 5 juni voordat het WK voorbij is!
Voor diegene die onderstaande uitleg willen volgen en het model niet (meer) paraat hebben: WK-voetbal voorspeller.

Werkblad Groepsfase

Vorige keer heb ik laten zien hoe we tot de puntenverdeling in de groepsfase komen.
We kijken nu verder naar de consequenties daarvan voor het vervolg van het toernooi.

Per land in kolom P (simpele verwijzingen naar kolom E en F) wordt gekeken hoeveel punten ze volgens het model hebben verdiend in de groepswedstrijden: in bijvoorbeeld cel Q4 moet het aantal punten komen, dat het land uit P4 heeft verzameld. Maar aangezien een land ‘uit en thuis’ kan spelen kunnen de verdiende punten in kolom M en in kolom N staan.
Om de relevante punten uit kolom M op te tellen kent Excel een mooie functie SOM.ALS. De functie kent de volgende vorm: =SOM.ALS(Bereik1;Voorwaarde;Bereik2)
Wat betekent: als een cel uit het Bereik1 gelijk is aan Voorwaarde, tel dan de overeenkomstige cel uit Bereik2 bij het totaal op.
In dit geval: =SOM.ALS($E$4:$E$51;P4;$M$4:$M$51)
Door de verwijzingen van Bereik1 en Bereik2 ‘absoluut’ te maken (dus met $-tekens voor de kolom-letter en het rij-nummer) en de Voorwaarde ‘relatief’ kan deze formule eenvoudig zonder aanpassingen naar beneden gekopieerd worden.

PS laat me weten als er behoefte is aan uitleg over het verschil tussen absolute en relatieve verwijzingen.

Maar ook zijn er punten te verdienen als ‘uitspelende’ partij, dus ook de punten uit kolom N moeten worden geteld.
De formule in Q4 wordt:
=SOM.ALS($E$4:$E$51;P4;$M$4:$M$51) + SOM.ALS($F$4:$F$51;P4;$N$4:$N$51)

Wat te doen als nummer 2 en 3 evenveel punten heeft; wie gaat dan door naar de achtste-finales?
Aangezien het hier maar een simpel model betreft en we niet hebben gemodelleerd hoeveel doelpunten ieder land maakt, moeten we een beetje truken: niet alleen krijgt een land de betreffende punten uit kolom M en N, maar we geven hem ook nog een willekeurig heel klein getal extra: + ASELECT()/10000.

Aangezien de functie ASELECT() een willekeurige getal tussen 0 en 1 genereert, krijgt een land dus een getal tussen 0 en 1/10.000 extra. Net genoeg om bij een ‘gelijke stand’ toch een verschil te hebben.

Volgorde binnen de groep
Nu we de resultaten van de groepen hebben opgezocht, moeten we nog bepalen wie de nummers 1 en 2 (en ook 3 en 4) zijn geworden.
In kolom U gaan we van iedere groep eerst het hoogst aantal punten ophalen met behulp van de functie =GROOTSTE(Bereik;Volgnummer); oftewel zoek in Bereik (gesorteerd van grootste naar kleinste) naar de met Volgnummer corresponderende waarde. Dus als Volgnummer 1 is, dan krijg je als resultaat de hoogste waarde; is het 2, dan de een na hoogste enzovoort.

Nu we de volgorde van punten per groep hebben gevonden, zoeken we in kolom T het bijbehorende land op met behulp van de functies INDEX en VERGELIJKEN (zie voor een verder uitleg hiervan het artikel Alternatief voor Vert.Zoeken).

Om in het volgende werkblad makkelijk verwijzingen naar de nummers 1 en 2 per groep te kunnen maken, geven we die cellen een naam (zie het artikel Namen ipv celverwijzingen): cel T4 krijgt de naam GA_1 (afkorting voor Groep A, nr 1), cel T5 de naam GA_2 (Groep A, nummer 2), T10 wordt GB_1 etc.

LET OP: je kunt NIET de naam GA1 gebruiken, omdat dit al een echte celverwijzing is; vandaar de _ (underscore).

Werkblad Finales

In kolom B nemen we de landen, die als eerste en tweede in een groep zijn geëindigd over. Eerst de winnaar van Groep A (in cel B3 tikken we in: =GA_1; een naamverwijzing dus), dan de 2e van Groep B (die spelen tegen elkaar in de achtste finales), winnaar Groep C, 2e van Groep D etc.

De rest van dit werkblad zou ondertussen voor zichzelf moeten spreken: we zoeken de sterktes van de landen weer op (kolommen C en D), bepalen aselect wie dan wint (kolom E) en zetten de winnaar in kolom F. Deze winnaars geven we weer een naam (AF_1, AF_2 etc. AF betekent dan Achtste Finale).
Het stramien is bij de kwart-, halve- en echte finale hetzelfde. Op die manier komen we uiteindelijk in cel Y18, de Winnaar.

Werkblad MC

Op de vorige werkbladen wordt iedere keer één Monte Carlo-run weergegeven: bij iedere wedstrijd wordt een ‘dobbelsteen’ gegooid en gekeken wie er wint. Al dit gedobbel levert dan uiteindelijk een winnaar op (meestal Brazilië, omdat we die bij Invoer het sterkste hebben ingeschat).

Door op F9 te drukken (of ergens iets te wijzigen op een werkblad) gaat Excel opnieuw met de dobbelstenen gooien en komen er overal andere resultaten tevoorschijn.
Door dit 100 keer te doen en te turven hoe vaak bijvoorbeeld Nederland winnaar wordt, weten we hoe groot de kans is dat Nederland Wereldkampioen wordt (op basis van de aannames in het werkblad Invoer en het gehanteerde model!!!).

Door het “drukken op F9” te automatiseren is het bepalen van deze kansen een stuk eenvoudiger geworden.

Allereerst maken we een tabel, waarin we kunnen turven hoe vaak een land wint:

tik in cel B4 in: =Landen
Dit is een verwijzing naar een reeks cellen in het werkblad Invoer; Excel neemt dan de waarde over die in die reeks in dezelfde rij staat als waar nu de verwijzing wordt getypt.
kopieer deze formule naar beneden tot en met cel B35.
kolom C moet door de PC worden gevuld; in C36 tellen we wel alvast het aantal runs op: =SOM(C4:C35)
in D4: =C4/$C$36; ofwel welk percentage van het totaal aantal runs komt ten gunste van dit land.
de kop boven de landen (cel B3) geven we ook een naam (LandWin); die kunnen we straks bij het turven goed gebruiken.

De grafiek is een weergave van deze tabel.

Nu nog even een klein programmaatje schrijven om de PC te laten ‘dobbelen’ en we zijn klaar!
Dat programma moet natuurlijk wel weten hoe vaak er een run gedraaid moet worden; dat zetten we in cel I5, die we de naam AantalRuns geven.

VBA-programma

Uitleg over gebruik van de VBA-editor kun je vinden in de blog van 5 mei 2014.
Hier zullen we volstaan met een korte uitleg van wat het programma doet:
1: definieer een programma (subroutine) met de naam MC
2, 3, 4: definieer variabelen, die we dadelijk gaan gebruiken
6: de programmavariabele iAantRuns krijgt de waarde van de Excel-cel met de naam AantalRuns
8: het tellertje i laten we lopen van 1 tot en met iAantRuns
9: we gaan dadelijk zoeken of we de winnaar ergens in ons lijstje tegenkomen (zou gek zijn als het niet zo was!!); we beginnen met zoeken in de regel 1, daarom geven we de variabele iRegel die waarde alvast
10: we hebben het land nog niet gevonden, dat leggen we ook even vast
12: wie is eigenlijk de winnaar van de run die net heeft gedraaid? Die staat in de Excel-cel met de naam Winnaar en dat stoppen we in de variabele strWin
13: zolang nog niet gevonden EN de cel iRegel’s onder de kop LandWin (Offset = Verschuiving) niet leeg is
14: als in die cel de winnaar van deze run staat dan
15: tel bij de waarde in de cel iRegel’s onder de kop en 1 kolom naar rechts (dus in kolom C) 1 op
16: gevonden!!!
17: een screenupdate binnen de applicatie zorgt er voor dat Excel alles doorrekent; in dit geval opnieuw gaat dobbelen bij alle wedstrijden
19: als we misschien nog verder moeten gaan zoeken, waar de winnaar staat, dan wel natuurlijk 1 regel lager
20: ga weer naar 13; als daar niet aan de voorwaarde wordt voldaan gaat het programma verder met 21
21: Next hoort bij de For van regel 8, volgende run dus
23: als het aantal runs doorlopen is, stopt het programma

Dit programma kun je laten runnen door op Alt-F8 te drukken, de macro MC te selecteren en OK te klikken, maar handiger is het om die met behulp van een button/knop te starten.
Zo’n knop maak je als volgt:

kies in de menu-balk de optie Ontwikkelaars
dan Invoegen
en klik op het eerste besturingselement: Knop
’teken’ met de cursor de omtrek voor de nieuwe knop
wijs de macro MC toe aan het object
en verander de tekst op de knop

Om ook de resultaten van één of meerdere runs te wissen, heb ik een tweede macro gemaakt (MC_Opnieuw) en die aan een knop toegewezen.
De werking van de macro zou met bovenstaande toelichting duidelijk moeten zijn.

WK-voetbal voorspeller (deel 2)

15 juni 2014ExcelAselect, Groeperen, Namen, Random, Verschuiving, ZoekenGijs Verbruggen

Spanje – Nederland: 1-5!

Wie had dat verwacht; niemand toch?
Mijn vrienden, met wie ik de wedstrijd samen bekeek, begonnen te twijfelen aan de WK-voetbal voorspeller van G-Info (zie mijn blog van 5 juni).
Ik kan alleen maar ter verdediging aanvoeren, dat in mijn vorige blog ook al stond aangegeven, dat de kwaliteit van ieder Excel-model afhangt van de input en van de gehanteerde systematiek. De systematiek hou ik nog even staande (hoewel er al mensen tips voor verbetering hebben aangeleverd); de input kan iedere gebruiker zelf aanpassen (ik heb een paar ‘kleine’ aanpassingen gedaan en zie hierboven het resultaat voor de verwachtingen voor Nederland!).

Maar ik had beloofd om deze week wat meer uitleg te geven over de opzet van het spreadsheet-model; dus voor diegene, die daar nu nog behoefte aan heeft ….

Werkblad Invoer

Hier valt weinig spannends te beleven; dit blad bevat 2 tabellen, die als basis dienen voor het vervolg.

Groepsindeling
Ik neem aan, dat de opbouw van deze tabel logisch genoeg is.
Zoals vorige keer al aangegeven, leggen we de sterkte van een land (qua voetbal!) vast via een ondergrens en een bovengrens.
Hoe kleiner het verschil tussen die twee grenzen, hoe stabieler de resultaten van het land zullen zijn en omgekeerd.

Het voorspeller-bestand is zodanig opgezet, dat deze voor een volgend toernooi opnieuw bruikbaar is. Dit alles onder de condities, dat er weer 8 groepen zijn met ieder 4 landen/ploegen en dat de 2 beste van de groepen verder gaan naar de achtste finales.
In dat geval hoeven alleen de namen van de deelnemende ploegen te worden gewijzigd in het werkblad Invoer. En uiteraard de grenzen voor de ploegen!

Groepschema

Met behulp van dit schema is vastgelegd in welke volgorde de groepswedstrijden worden gespeeld.
In dit geval speelt eerst ploeg 1 uit de groep tegen ploeg 2, dan 3 tegen 4, 1 tegen 3 etcetera.

Binnen dit werkblad zijn 4 namen voor groepen van cellen gedefinieerd:

Landen (cellen C4:C35)
LandKop (C3)
Sterkte (C4:E35)
GroepSchema (H3:I8)

In het vervolg van de spreadsheet wordt hier naar verwezen (meer over het gebruik van namen: zie het artikel van 31 mei 2014).

Werkblad Groepsfase

Nu begint het al wat leuker te worden! In dit blad zitten her en der wat aardige Excel-mogelijkheden ‘verstopt’.

Groeperen
Waarschijnlijk vallen de +-tekens aan de bovenkant en links meteen op. Hieraan is te zien, dat er (werk)-kolommen en -rijen zijn verborgen, zodat alleen de belangrijkste onderdelen van het overzicht getoond worden.

Klik op een plus-teken en je kunt zien wat er ‘ achter’ zit; klik dan weer op het min-teken en de zaak wordt weer verborgen.
LET OP: ik heb niet de optie Kolommen of Rijen verbergen gebruikt; daarbij valt het te weinig op, dat er iets verborgen is. Nee alles is uitgevoerd met behulp van de optie Groeperen: kies aaneengesloten rijen of kolommen, die je tijdelijk niet wilt zien, door de betreffende koppen te selecteren en ga naar de menu-optie Gegevens. In het onderdeel Overzicht zie je de optie Groeperen.
Maar makkelijker: heb je de kolommen of rijen geselecteerd, druk dan de toetscombinatie Shift-Alt-Rechts in. Degroeperen gaat op een vergelijkbare manier (Shift-ALt-Links). Met Rechts en Links bedoel ik de cursortoets naar rechts cq. links.

Verschuiving
Nu gaan we naar wat steviger kost; kijk eens naar cel F13:

Om deze spreadsheet nog vaker te kunnen gebruiken, moet het systeem zelf afleiden welk land op die plaats moet komen staan: het moet Land2 zijn (info in cel F1) in de 4e wedstrijd (cel C13) van de 2e groep (cel B13).

Uit het groepschema weten we, dat de 2e ploeg in de 4e wedstrijd in iedere groep het 2e land uit die groep is.
In Excel vinden we dat met de formule: INDEX(GroepSchema;WedNr;F1)
(zoek in Groepschema de rij op die overeenkomt met WedNr en daarbinnen de kolom, zoals vermeld in cel F1; in dit geval rij 4, kolom 2; voor meer informatie over zoeken via INDEX, zie ook het artikel Alternatief zoeken).
Ik maar hier gebruik van een heel handige eigenschap van het gebruik van Namen binnen Excel: WedNr is een reeks cellen, die loopt van C4 tot en met C51; wanneer je ergens in een cel daarnaast naar deze range verwijst via de naam (in dit voorbeeld vanuit cel F13), dan haalt Excel alleen de corresponderende cel in dezelfde rij op (dus hier C13, die de waarde 4 heeft), dus niet de hele reeks!

In het werkblad Invoer staan alle landen onder elkaar: eerst Groep A (de eerste groep), dan Groep B etc.
Om het land te vinden, dat in cel F13 moet komen, is het dus niet voldoende om te weten dat het het 2e land is maar moeten we ook nog weten in welke groep.
Het 2e land in de eerste groep staat in de landenkolom op de 2e plaats, het 2e land uit de 2e groep op 6, uit groep 3 op 10 etc.; telkens 4 verder dus.
Vandaar dat bij het hiervoor gevonden landnummer nog (GroepNr-1)*4 opgeteld moet worden om op de juiste plaats in de landenkolom terecht te komen (in dit geval gaat het om groep 2, dus komt er 4 bij en wordt het landnummer 6).

Met behulp van de functie INDEX zouden we nu in de reeks met de naam Landen kunnen zoeken, want we weten in welke rij we moeten zijn.
Ik heb deze keer voor een alternatieve manier gekozen: de functie VERSCHUIVING.

Met VERSCHUIVING geef je naast een verwijzing naar een bepaalde cel ook aan, dat je wat verder naar beneden (of naar boven!) en/of naar rechts of links wilt uitkomen.
In dit geval willen we dus vanuit de LandKop gerekend x rijen naar beneden en 0 kolommen naar links of rechts zoeken, ofwel
=VERSCHUIVING (LandKop; x; 0)

Maar de x kenden we ook al; die hebben we hiervoor afgeleid, dus de functie die we nodig hebben is:

=VERSCHUIVING(LandKop;(GroepNr-1)*4+INDEX(GroepSchema;WedNr;E$1);0)

Nu we deze formule eenmaal hebben, kan die ook naar de overige cellen in E en F gekopieerd worden.

Wie wint een wedstrijd?
In de kolommen G en H staan de grenzen voor het eerste land uit de corresponderende rij (opgezocht m.b.v. INDEX en VERGELIJKEN; zie het artikel Alternatief zoeken), in de kolommen J en K idem voor het tweede land.

Als we kijken naar cel I4, dan zien we de formule =ASELECTTUSSEN(G4;H4).
In dit geval wordt er voor Brazilië willekeurig een getal gekozen tussen zijn onder- en bovengrens (deze keer leverde dat 40 op; druk je op de functietoets F9 dan zal er hoogstwaarschijnlijk een ander getal komen).

In de kolommen M en N verdelen we de punten per wedstrijd via een dubbele ALS-formule; in cel M4 is dat =ALS(I4>L4;3;ALS(I4=L4;1;0)).
Als de sterkte van Brazilië groter is dan die van Kroatië krijgen ze 3 punten, als de sterktes gelijk zijn dan 1 punt, anders 0 punten.

De volgende keer ga ik verder met de uitleg van de overige gebruikte functies en de VBA.

Voortschrijdend inzicht

Nu de eerste wedstrijden zijn gespeeld, is er misschien iets meer duidelijkheid gekomen over de sterkte van de landen. In ieder geval kennen we al wat uitslagen en kent het model dus iets minder onzekerheden.

Wat kun je nu doen:

download eventueel nog een keer de WK-voetbal voorspeller
als je wilt kun je op het werkblad Invoer nog wat sterktes aanpassen (Nederland toch maar gelijk maken aan Brazilië??, de sterkte van Spanje flink verlagen??)
vul op het werkblad Groepsfase de bekende uitslagen in:
Brazilië – Kroatië: 3-1; in cel I4 komt een 3 en in cel L4 een 1
Spanje – Nederland: 1-5; in cel I10 een 1 en in cel L10 een 5
vul ook de andere uitslagen in
wis op het werkblad MC de vorige run(s) en laat Monte Carlo ongeveer 500 keer zijn werk doen; dat is voldoende om een goed beeld te krijgen van de mogelijke resultaten (volgens het model is de kans, dat Nederland wereldkampioen wordt door de uitslagen tot nu toe, verdubbeld!).
sla het spreadsheet onder een andere naam op: WK2014 Uitslagen.xlsm

WK-voetbal voorspeller

5 juni 2014Excel, VBA (Visual Basic for Applications)Aselect, RandomGijs Verbruggen

Het is bijna zover: Nederland wordt wereldkampioen voetbal 2014! Of toch niet?

Heel (?) Nederland is in de ban van Koning Voetbal en menigeen wil zijn kennis van het spelletje te gelde maken door een gokje te wagen in één van de vele poules die georganiseerd worden.

Maar niet voor iedereen is zo’n poule invullen een fluitje van een cent; daarom geeft G-Info je deze keer een handig hulpmiddel cadeau: je hoeft alleen maar op te geven hoe jij de sterkte van de diverse landen inschat en de WK-voorspeller laat zien wat de uitslagen van de diverse wedstrijden zullen zijn en dus ook wie er wereldkampioen wordt!

WK-voetbal en Monte Carlo

Je zou zeggen: deze twee hebben weinig met elkaar te maken. Qua voetbal klopt dat misschien, maar als het op gokken aankomt….

In de bijgaande WK-voorspeller gebruik ik namelijk de zogenaamde Monte Carlo-methode om met behulp van Excel voorspellingen te doen.
In het kort komt het er op neer, dat we Excel per wedstrijd per land willekeurig (random, aselect) een sterkte laten kiezen tussen een onder- en bovengrens. Die twee grenzen moet je zelf opgeven; ik ben benieuwd wat voetbalkenners van mijn poging vinden.
Per wedstrijd wordt de sterkte van de 2 landen vergeleken en de sterkste van de 2 krijgt de 3 punten (even sterk, dan ieder 1 punt).
Laat je één run uitvoeren, dan heb je het resultaat van een heleboel toevalligheden bij elkaar; laat je je PC echter 100 runs draaien (of 1.000 of 10.000!), dan kun je turven hoe vaak Nederland kampioen wordt (of zullen we toch maar Brazilië nemen).
Dat aantal, gedeeld door het aantal runs, geeft de kans van het land op het wereldkampioenschap.
Het is een soort veredeld dobbelsteen gooien, een gokspel, vandaar de naam Monte Carlo.

Ik kan me voorstellen, dat er nu mensen zijn, die zeggen, dat dit wel een heel simpele benadering van het complexe voetbalspel is.
En helaas moet ik dat meteen toegeven, maar als verdediging kan ik aanvoeren, dat het alleen bedoeld is om weer eens een alternatief gebruik van Excel toe te lichten.

Werking programma

Wat moet je doen voordat je je poule gaat invullen?

uiteraard eerst de Excel-sheet downloaden en in Excel openen.
vul op het werkblad Invoer bij alle landen een onder- en bovengrens voor hun sterkte in.
LET OP: als de bovengrens bij een land kleiner is dan de ondergrens van een ander land, dan kan het eerste land dus nooit winnen van het tweede.
ga naar het werkblad Groepsfase en druk op F9 (Excel gaat dan alles opnieuw doorrekenen, inclusief alle willekeurig gekozen getallen) en ziedaar de resultaten van alle groepswedstrijden en dus ook wie er naar de achtste-finales mogen.
Bevalt een uitslag niet: druk opnieuw op F9! Excel zal een nieuwe reeks doorrekenen; doe dat 100 keer en kijk in Groep B hoe vaak Nederland verder gaat (dit aantal gedeeld door 100 is de kans op overleven).
Het is natuurlijk nauwkeuriger als je dit 1.000 of 10.000 keer doet ….
op het werkblad Finales zie je hoe het verder gaat met de eerste en tweede per groep en wie uiteindelijk wereldkampioen zal worden.

Als je al had gedacht , dat je met dit programma goud in handen had gekregen; je zult ondertussen de betrekkelijkheid van onze ‘voorspeller’ hebben doorgrond.
De kwaliteit van zo’n Excel-model zit in de betrouwbaarheid van de aannames (in dit geval de grenzen voor de sterkte per land) en in het benaderen van de werkelijkheid met Excel-formules (het winnen van een wedstrijd hangt niet alleen af van een toevallige steekproef van de sterktes van de 2 partijen).
Toch kan Excel op deze manier, zeker wanneer de aannames beter worden onderbouwd en de formules beter de wekelijkheid nabootsen, een belangrijk hulpmiddel zijn om zogenaamde stochastische processen na te bootsen en met een zekere nauwkeurigheid resultaten te voorspellen.

Monte Carlo-runs

Het ontbreekt me nu aan tijd om het programma verder toe te lichten (even voetbal kijken: Nederland- Wales), maar ik beloof dat ik in mijn volgende blog de wat complexere (en dus leukere!) zaken nog eens de revue zal laten passeren.

Als troost heb ik nog een werkblad MC toegevoegd: hiermee kun je een serie Monte Carlo-runs achter elkaar draaien en kijken welk land de grootste kans heeft om wereldkampioen te worden.

Klik op de button Monte Carlo draaien en bekijk het resultaat.
Je kunt ook het aantal runs per serie aanpassen (dus is het toch simpel om 10.000 runs uit te voeren).
Wil je opnieuw beginnen: druk op de button Vorige run(s) wissen.

Namen ipv celverwijzingen

31 mei 2014ExcelNamen, VerwijzingenGijs Verbruggen

Bij sommige spreadsheets krijg ik het gevoel, dat de opsteller alles in het werk stelt om het zo ingewikkeld te maken, dat niemand behalve hijzelf er nog iets van begrijpt.
Verwijzingen van de ene kant van een werkblad naar de andere kant en ook nog eens over de werkbladen heen. Je blijft maar zoeken waar alle gegevens vandaan gehaald worden.
(Om eerlijk te zijn: de aanleiding voor dit artikeltje is een spreadsheet van mezelf van vorig jaar, waar ik niet zo makkelijk meer uitkwam!).

Namen definiëren

Los van verdere structurering van je spreadsheet (waar ik in cursussen een ochtend aan besteed) is het een goede gewoonte om cellen of reeksen van cellen, die vaste waarden bevatten (zogenaamde constanten) een naam te geven.
Het grote voordeel hiervan is, dat verwijzingen veel beter leesbaar worden (voor ons menselijke wezens in ieder geval):
in plaats van een verwijzing als =Blad7!B5 * (1+Blad3!D3) zien we dan =Bedrag * (1+BTW)

Wanneer je een cel (of reeks van cellen) hebt geselecteerd, kun je op een paar verschillende manieren deze cel of het bereik een naam geven:

zoek de menu-optie Formules, het blok Gedefinieerde namen en kies Naam definiëren, tik bij Naam een naam in (klinkt logisch!)
iets makkelijker: klik in het Naamvak (waar nu nog de naam van (eerste) gekozen cel staat) en tik de gewenste naam in
nog makkelijker: rechts klikken en Naam definiëren kiezen

Reeks namen definiëren

Soms heb je een serie cellen, die je allemaal een naam wilt geven. Dat kan dan een klus zijn, maar gelukkig heeft Bill Gates daar een mooie oplossing voor bedacht.
Wanneer je nu de cellen vanaf Doel t/m 30 selecteert, dan menu-optie Formules, het blok Gedefinieerde namen en Maken obv selectie kiest, krijg je een nieuw keuzescherm.
De bedoeling is, dat de cellen in de tweede kolom een naam krijgen zoals in de eerste kolom staat aangegeven, dus alleen het vinkje voor Linkerkolom mag aan staan.
Kies dan OK en je zult zien, dar alle cellen in de rechterkolom een naam hebben (“wandel” er overheen met de cursor en kijk in het Naamvak).
Even experimenteren met de mogelijkheden!

Namen gebruiken

Maar wat is nu het nut van Namen? Nou, bekijk onderstaand voorbeeld
(of open het Voorbeeldbestand):

Als je weet, dat het hier een annuïteitenberekening betreft, kun je misschien nog achterhalen wat de verwijzingen allemaal doen, maar of ze naar de juiste cellen verwijzen?
Dan vind ik de formule in D8 in het volgende voorbeeld toch een stuk leesbaarder (en betrouwbaarder!):

Hoe krijg je Namen in formules?

Wanneer je de diverse constanten, die je gebruikt, al namen geeft voordat je formules gaat invoeren scheelt dat een heleboel werk.

Wanneer je (in bovenstaand voorbeeld) ergens intikt =-BET( en dan met de cursor op C7 in het werkblad Parameters klikt, zal automatisch de naam Rente worden ingevoegd.
PS met de formule BET bepaal je voor een annuïteit de periodieke betaling, die hoort bij een te lenen bedrag, een aantal termijnen en een afgesproken rente (nou ja, afgesproken? Eenzijdig bepaald!). Voor onze rekenmachine is een betaling een negatief bedrag, maar dat vind ik niet mooi, dus daarom zet ik er een – (minteken) voor.
Een andere methode is: tik weer in
=-BET(
Je ziet dan dat de formule een rente verwacht, dus we beginnen te tikken Re en ziedaar onze eigen naam Rente staat er al bij! Even dubbelklikken (of met de cursor er naartoe en dan op de Tab-toets drukken).
Tik een ; (punt-komma) en tik in lo etc.
je kunt ook op F3 drukken wanneer je een eigen gedefinieerde Naam wilt invoeren.

Achteraf namen gebruiken in plaats van de celverwijzingen? Dat kan nog altijd:
We gaan weer via de menu-optie Formules naar het blok Gedefinieerde namen en kiezen dan NAAST Naam definiëren het vinkje en krijgen dan de mogelijkheid om Namen te gebruiken. Je kunt dan nog aangeven welke namen je in je spreadsheet wilt laten gebruiken.
Helaas heb ik (maar ook Microsoft zelf) moeten constateren, dat het op deze manier vervangen van verwijzingen door namen niet altijd lukt. Dan zul je de formules opnieuw moeten invoeren.

Andere toepassing van Namen

Wanneer je een flinke spreadsheet hebt ontwikkeld met veel bladen en veel informatie op de diverse werkbladen dan is het handig om Namen ook als een soort bladwijzers te gebruiken.

Klik achter het Naamvak op het uitklap-symbool, kies een eigen gedefinieerde naam en … de cursor staat ineens op een ander tabblad op een andere plaats!!

Nog even over je hypotheek

Het Voorbeeldbestand bevat een annuïteitenberekening. Deze is als voorbeeld bedoeld voor het gebruik van Namen (kijk zeker even op het werkblad Rek2).
Maar natuurlijk kun je die ook gebruiken om een indicatie te krijgen over de kosten van een hypotheek. Wanneer je gegevens op het werkblad Parameters wijzigt, zie je de resultaten in de andere werkbladen.
Kun je achterhalen bij welk rentepercentage je evenveel rente als aflossing betaalt, over de hele looptijd gerekend?
Misschien nog een idee voor een volgende Tip.

Tekst-cellen in Excel

25 mei 2014ExcelKopiëren, Opmaak, Plakken speciaalGijs Verbruggen

Haalt u ook wel eens gegevens uit SAP of een ander systeem over naar Excel?
Hoe vaak gebeurt het dan, dat totalen ineens niet meer aansluiten?

10 tegen 1 hebben de twee systemen elkaar niet helemaal begrepen en is Excel enigszins de kluts kwijt geraakt!

Niet alleen tellen de bedragen niet goed op, ook de datums zijn niet goed overgekomen.

Vervangen

Het eerste wat we gaan doen, is in ieder geval de opmaak aanpassen:

selecteer alle cellen van kolom B door op de betreffende letter boven de kolom te klikken
ga naar de menu-optie Start, kies Zoeken en selecteren en dan Vervangen (of iets korter: druk Ctrl-h in)
bij Zoeken naar: vullen we . (een punt dus) in en bij Vervangen door: – ( een streepje)
kies Alles vervangen (zie je het streepje onder de s? Dat betekent dat je ook Alt-s kunt indrukken voor hetzelfde resultaat)
selecteer alle cellen van kolom D
Ctrl-h en vervang de . (punt) door een , (komma)

Misschien is alles goed gegaan en ziet het overzicht er als volgt uit:

De datums zijn ineens rechts uitgelijnd (een teken dat Excel de inhoud van de cel als een getal ziet; zie de blog over Datums) en ook alle bedragen.
En de optelling klopt!

Helaas wil het nog wel eens voorkomen, dat Excel zich niet zo makkelijk op het goede spoor laat brengen en ziet het overzicht er nog niet helemaal goed uit.

Waar het op neer komt: Excel blijft hardnekkig denken dat de cellen D6 en D7 teksten bevatten.

Tekst-cellen

En helaas: Excel blijft volharden!
Enkele veel gehoorde ‘oplossingen’:

de cellen rechts uitlijnen
via celeigenschappen (Ctrl-1) de opmaak op Getal zetten

Nee, dit is allemaal louter opmaak: Excel blijft de cellen als tekst zien en zal deze bij de optelling niet meenemen. Probeer het maar eens in het Voorbeeldbestand.

Maar gelukkig: met een extra zetje laat Excel zich wel vermurwen!
Wanneer je met zo’n cel gaat rekenen (er bijvoorbeeld iets bij optellen) dan probeert Excel de tekst te vertalen in een getal.
Dus we plaatsen in E6 de formule = D6 + 0 en we krijgen het gewenste getal te zien.
Even E6 kopiëren naar E7 en ook het andere getal hebben we.

Maar de getallen moeten in kolom D komen:

selecteer de cellen E6 en E7
kies kopiëren (Ctrl-c)
ga naar cel D6
klik rechts en kies bij Plakopties het tweede symbooltje (Waarden)
Excel kopieert dan niet de formules uit E6 en E7, maar de ‘harde’ waarden (klinkt logisch, toch?)
verwijder cellen E6 en E7

Snelle oplossing

We gaan hetzelfde idee toepassen, maar dan zonder alle tussenstappen:

1. 1. zet ergens in een lege cel de waarde 0
  2. kopieer deze cel
  3. selecteer de cellen D6 en D7
  4. rechts klikken en kies dan Speciaal Plakken…
  5. in het volgende scherm
    zet je (onder Bewerking) de optie Optellen aan
  6. klik OK

Vergeet niet de cel met de 0 weer leeg te maken!

LET OP: op deze manier wordt ook de lay-out van de cel met de 0 meegenomen. Wil je dat niet, zet dan in stap 5 ook de optie Waarden aan.

Twee alternatieven:

1. 1. ipv een cel met de waarde 0 (nul) kun je ook een lege cel kopiëren!
  2. kopieer een cel met de waarde 1 en kies in stap 5 niet Optellen, maar Vermenigvuldigen

Plakken speciaal/Vermenigvuldigen

Uiteraard is de optie Plakken speciaal/Vermenigvuldigen niet ontwikkeld met bovenstaande toepassing in het achterhoofd.
Maar heb je een keer een serie bedragen waar de 21% BTW nog bij moet: kopieer het getal 1,21 en vermenigvuldig op deze manier de gewenste cellen.
Of de budgetten moeten allemaal 5% omhoog (of naar beneden) of ….

Voorbeeldbestand

Probeer in het voorbeeldbestand de mogelijkheden uit.