Tagarchief: VERT.ZOEKEN

LET OP: na het downloaden de extensie wijzigen in xlsm

Vroeger…. toen was het allemaal anders (en beter?).
Je had toen speciale bureaus, die niets anders deden dan (standaard)berekeningen uitvoeren … “met de hand”.

Tegenwoordig wordt Excel vaak ingezet voor berekeningen. Maar in de praktijk zie ik daar vaak nog veel hand-arbeid aan te pas komen. Daar is op zich niets mis mee, maar het is wel veel foutgevoeliger dan wanneer dit soort processen worden geautomatiseerd.
Daarom deze keer aan de hand van een hypotheek-voorbeeld uitleg hoe met (een beetje) VBA bulk-processen binnen Excel kunnen worden uitgevoerd.

Hypotheek-berekening

De basis van dit artikel is een hypotheekberekening (zie het tabblad BerekHyp van het Voorbeeldbestand). Aan de hand van enkele input-gegevens wordt een overzicht van rente en aflossing gegenereerd; voor de overzichtelijkheid op jaar-basis.

Alle input-cellen (C2:C7) hebben een naam gekregen gelijk aan de tekst in de kolom links er van (via Formules/Namen/Maken obv selectie en dan de optie Linkerkolom).
Via Gegevens/Gegevensvalidatie zijn de invoer-mogelijkheden van enkele cellen ingeperkt.
Het schema van rente en aflossing is in een Excel-tabel weergegeven (met de naam tblBerek).

Cel B11 bevat een harde waarde, gelijk aan 1, cel C11 heeft als formule =Bedrag (het gewenste hypotheekbedrag dus).
In cel D11 wordt de verschuldigde rente voor dat jaar bepaald (voor een annuïteiten- of lineaire hypotheek is dat hetzelfde):
=[@BeginBedr]*Rente, dus het BeginBedr uit dezelfde regel vermenigvuldigd met het verschuldigde rentepercentage (cel C5).

De berekening van de aflossing het eerste jaar (cel E11) is iets ingewikkelder en afhankelijk of het een lineaire of annuïteiten-hypotheek betreft:
=ALS(Soort=”Lin”;
Bedrag/Looptijd;
-BET(Rente;Looptijd;Bedrag) – [@Rente])

Dus: als het een een lineaire hypotheek is dan is de aflossing gelijk aan het Bedrag gedeeld door de beoogde totale Looptijd. Anders (dus bij een annuïteit) is het gelijk aan het resultaat van de functie BET(Rente;Looptijd;Bedrag) minus de verschuldigde Rente van dat jaar.

NB1 de functie BET berekent aan de hand van de parameters rentepercentage, aantal termijnen en beginbedrag het verschuldigde termijnbedrag

NB2 als het beginbedrag in de functie BET positief is, dan is het resultaat van de functie negatief; vandaar het min-teken voor BET.

NB3 moeten de berekeningen exacter dan zul je het schema op maandbasis moeten doorrekenen; de Rente door 12 delen en de Looptijd en de rentevastperiode (RVP) met 12 vermenigvuldigen.

Het volgende Jaar-nummer (cel B12) is gelijk aan het vorige Jaar plus 1; het BeginBedr in cel C12 is gelijk aan het resultaat van cel F11. De formules in de overige cellen van rij 12 zijn gelijk aan die in de vorige regel. De formules uit rij 12 kunnen daarna allemaal naar beneden gekopieerd worden.

NB het schema mag alleen maar gevuld worden voor die jaren, die binnen de rentevastperiode (RVP) vallen. Dat is met behulp van de ALS-functie makkelijk op te lossen (zie Voorbeeldbestand).

In hetzelfde tabblad BerekHyp staat ook een samenvatting van de resultaten: de totale bedragen aan rente en aflossing en de restant-schuld op het einde van de RVP.

De eerste 2 berekeningen zijn rechttoe rechtaan: neem de som van de betreffende kolom in de tabel tblBerek. De bepaling van het eindbedrag gaat via de formule =MIN(tblBerek[EindBedr]), ofwel bepaal het minimum van de kolom EindBedr in de tabel tblBerek.

Bulk-invoer

Moet je nu 10 verschillende berekeningen uitvoeren, dan zullen per berekening de betreffende gegevens moeten worden gewijzigd, Excel berekent de resultaten en deze moeten overgenomen worden of geprint.
Maar wat te doen als er 100 berekeningen nodig zijn of misschien 2.000? Het overnemen van invoer-gegevens is behoorlijk foutgevoelig; de meest voorkomende fouten zijn:
* een 0 te veel of te weinig bij het bedrag
* een decimaal fout in het rentepercentage
* vergeten Ann te veranderen in Lin of andersom

Dus het is zaak om zo min mogelijk gegevens handmatig in te voeren.
Zorg bijvoorbeeld, dat gegevens door een ander systeem in Excel-formaat worden aangeleverd óf laat (als je zelf een overzicht in Excel hebt gevuld) iemand anders het overzicht aan de hand van brongegevens controleren.
Een voorbeeldbestand met 2.000 regels (met de naam tblInput) is opgenomen in het tabblad Input van het Voorbeeldbestand.

Bulk-berekeningen (handmatig)

Als de invoer-gegevens gecontroleerd zijn kunnen die verwerkt worden tot resultaat-gegevens.

In het tabblad Bereken van het Voorbeeldbestand staat een vergelijkbaar schema als in het tabblad BerekHyp.
Het enige verschil is, dat de invoergegevens met behulp van VERT.ZOEKEN-formules worden opgehaald uit de tabel tblInput op basis van het corresponderende Nr.

NB voor het verticaal zoeken hebben we een Hulp-kolom gebruikt om aan te geven uit welke kolom van tblInput het betreffende veld gelezen moet worden.

Op deze manier kunnen redelijk vlot alle berekening doorlopen worden. Maar wat doe je met de resultaten. Eén mogelijkheid is om deze te verwerken in een apart output-tabblad (zie Output1 van het Voorbeeldbestand):

Vul in cel D2 van het tabblad Bereken een nummer in
Kopieer de cellen D2:D7 van het tabblad Bereken
Kies Plakken Speciaal/Waarden en Transponeren in een nieuwe regel in het tabblad Output1
Kopieer de cellen H2:H4
Plak die op een vergelijkbare manier achter de vorige gegevens
herhaal bovenstaande voor alle nummers uit Input

Bulk-berekeningen (met VBA)

Maar we kunnen het hele proces natuurlijk nog verder automatiseren; daar komt dan wat VBA (programmeerwerk) om de hoek kijken.
In het tabblad Bereken staan enkele buttons; de eerste met de titel Vullen 1 start de subroutine OutputVullen1 (rechtsklikken op de button en dan kiezen Macro toewijzen):

Een korte toelichting:

Allereerst worden met behulp van het commando Dim de variabelen gedeclareerd, die we binnen de routine gaan gebruiken.
Na de declaraties wordt de variabele MaxHyp gevuld met de waarde van de Range met de naam AantHyp (cel I2 in het tabblad Input)
Dan een vreemd blok (met de #-tekens): dit is een stuk met zogenaamde Conditional Compilation. De regel, die begint met If MaxHyp> 500, wordt alleen uitgevoerd op een MAC-computer. Omdat een MAC veel langzamer is dan een Windows-machine, beperken we het aantal uit te voeren berekeningen tot maximaal 500.
De regel na het #-blok plaatst een pop-up op het scherm, die aangeeft hoeveel berekeningen er uitgevoerd zullen gaan worden. Wordt binnen deze pop-up op Cancel (of Annuleren) gedrukt dan wordt de subroutine afgebroken.
Om straks te kunnen bepalen hoeveel tijd de routine heeft gekost, wordt de starttijd vastgelegd in de variabele StartTijd (beter gezegd de datum en tijd worden bewaard).
Dan wordt een andere subroutine (OutputLeegMaken1) uitgevoerd (zie hieronder).
Het belangrijkste gedeelte van de subroutine wordt gevormd door een zogenaamde For-Next-loop. Het stukje programma tussen For en Next wordt een bepaald aantal keren uitgevoerd, afhankelijk van de inhoud van (in dit geval) MaxHyp. De teller i houdt bij welke hypotheek-berekening wordt uitgevoerd.
Binnen de loop wordt allereerst de waarde van de teller i in de cel met de naam Nr geplaatst (zoals we hiervoor handmatig deden).
Excel herberekent op dat moment direct alle relevante cellen.
Het volgende gedeelte van het programma plaatst dan alle relevante waardes in de resultaat-tabel tblOutput1. Dit wordt gedaan door telkens, geredeneerd vanuit de tabelkop (Header) met de naam Nr, een aantal regels i naar beneden en 0, 1 et cetera kolommen naar rechts te gaan.
De overige regels hebben geen functionele bijdrage.

In bovenstaand programma wordt de routine OutputLeegMaken1 aangeroepen:

Via de Range-opdracht wordt de uitvoer-tabel leeg gemaakt. Als deze toevalligerwijs al leeg zou zijn, zou het programma stoppen met een foutmelding. De regels er omheen zorgen er voor, dat bij een eventuele fout het programma gewoon doorgaat.

Op mijn oude laptop heeft Excel ruim een minuut nodig om de berekeningen uit te voeren en de resultaten weg te schrijven.
Op Windows-computers is goed te zien, dat Excel aan het werk is; cellen veranderen continu van inhoud.

In de praktijk blijkt dat het bijwerken van het scherm het merendeel van de benodigde tijd in beslag neemt.
In de routine OutputVullen1b is daarom een regel toegevoegd: Application.ScreenUpdating = False

Via de button Vullen 1b start u deze routine; nu blijkt er nog maar 13 seconden nodig te zijn voor de verwerking van de 2.000 hypotheken!

LET OP vergeet niet in de VBA-routine de schermverversing weer aan te zetten. Onderaan komt dan Application.ScreenUpdating = True

Bulk-berekeningen (met VBA) vervolg

Maar het kan nog sneller. Wanneer we de For-Next-Loop vervangen door:

dan duurt het totale proces nog maar een paar seconden (klik op de button Vullen 2). De bijbehorende VBA-routine heeft de naam OutputVullen2.

Om dit mogelijk te maken is in het tabblad Output2 van het Voorbeeldbestand een aantal extra cellen toegevoegd, die samen de naam ResultRng hebben gekregen. De inhoud van deze cellen wordt in één keer in de Range vanaf (i,0) tot (i,8) geplaatst.

De cellen van ResultRng bevatten twee matrix-formules TRANSPONEREN, waarmee de benodigde gegevens uit het tabblad Bereken worden opgehaald (zie het artikel Transponeren).
Matrix-formules worden ingevoerd door in plaats van af te sluiten met Enter, de toetscombinatie Control-Shift-Enter te gebruiken (de CSE-methode). Excel plaatst dan automatisch accolades rond de formule.

Voetbalcompetities

30 augustus 2018Excel, VBA (Visual Basic for Applications)Aantal.als, Index, Rang, Tekst, Vergelijken, VERT.ZOEKENGijs Verbruggen

Voorbeeldbestand: VoetbalComp.xlsm Download

Je hoeft niet per se van voetballen te houden om dit artikel te kunnen waarderen.
Ik kreeg van Nico van der Meer een mooi Excel-systeem toegestuurd, dat hij graag met anderen wil delen: het verwerken van voetbaluitslagen tot overzichten en standen, inclusief consequenties voor nacompetities, Champions League etcetera.

In eerste instantie dacht ik: deze sheet bevat wel veel formules, daar zullen we snel wat fouten of mogelijke problemen uit kunnen halen.
Maar niets was minder waar; dus ik geef de werkmap door met maar een paar kleine aanpassingen (verbeteringen?).

Met het systeem van Nico kun je de hele competitie 2018-2019 plezier hebben; alles is al voorbereid, inclusief speeldata en zelfs tijden!
Maar, zoals uit het plaatje hierboven mag blijken, zijn dit soort competities behoorlijk onderhevig aan wijzigingen, dus voor volgend jaar zul je wel weer flink wat aanpassingen moeten doen.

Hieronder zal ik de werking van het systeem kort toelichten en ook een handreiking geven over de aanpassingen voor een volgende competitie-ronde.
Uiteraard zal ik ook de meest gebruikte Excel-functies en de gebruikte VBA-routines toelichten.

Voetbal-competitie

Voordat er zo’n mooi overzicht ontstaat moet er natuurlijk wat voorwerk verricht worden.
In het tabblad Info van het Voorbeeldbestand heeft Nico al diverse aanwijzingen gegeven.

Het systeem omvat 3 competities, Ere-, Eerste – en Tweede Divisie (de EersteDivisie heeft nu de naam KeukenKampioen).
In het tabblad Data liggen de gegevens van de competitieronde en van de teams per divisie vast. Ook kan daar aangegeven worden of de beveiliging (ter voorkoming van het overschrijven van formules) wel of niet moet worden aangezet.

Het tabblad Speelschema bevat per divisie een overzicht van alle rondes en de wedstrijden die daarbinnen gespeeld moeten worden; dit alles inclusief de betreffende datum en tijd. Deze gegevens zijn op internet terug te vinden; via de hyperlink boven iedere divisie zijn de relevante zaken te benaderen.

Klik rechts op een Hyperlink en dan Hyperlink bewerken om te zien hoe deze opgebouwd is.

LET OP om een hyperlink te bekijken moet de beveiliging van het tabblad verwijderd zijn. Dit kan via rechtsklikken op de tab onderaan of door in het tabblad Data aan te geven dat alle beveiligingen verwijderd moeten worden.

Wilt u met dit systeem de resultaten van een competitie volgen dan moeten op dit tabblad Speelschema de uitslagen handmatig ingevoerd worden (voor de Eredivisie in de kolommen C en D).

Resultaten en stand Eredivisie

In het tabblad Eredivisie van het Voorbeeldbestand worden de ingevulde wedstrijdresultaten automatisch verwerkt.

In cel G6 wordt bijvoorbeeld het aantal doelpunten opgehaald voor de thuisclub van de wedstrijd PSV – AZ Alkmaar mbv de formule
=ALS(ISLEEG(VERT.ZOEKEN(TEKST($B6;)&” – ” & TEKST($G$5;);SchemaED;2;0)); “”;VERT.ZOEKEN(TEKST($B6;)&” – “&TEKST($G$5;);SchemaED;2;0))

Via Verticaal Zoeken wordt de wedstrijd (B6, dus PSV, tegen G5, dus AZ) opgezocht in het bereik met de naam SchemaED. De inhoud van de 2e kolom wordt als resultaat teruggegeven. De derde parameter (een 0, nul) geeft aan dat een exacte zoekactie naar de wedstrijdnaam moet worden uitgevoerd.
Als de zoekactie niets oplevert (er is nog niets ingevuld) dan komt er in dit schema ook niets te staan (de dubbele aanhalingstekens), anders wordt het resultaat van de zoekactie in de cel geplaatst.

NB1 de namen van de teams worden door een spatie, een min-teken en opnieuw een spatie van elkaar gescheiden. Bij de opvoer in Speelschema moet dit ook exact zo zijn gedaan.

NB2 Nico heeft de team-namen niet rechtstreeks aan elkaar gekoppeld, maar heeft de functie Tekst gebruikt. Waarschijnlijk om er zeker van te zijn dat Excel deze betreffende cellen zeker als tekst zal zien. Naar mijn idee overbodig.

De overige formules onder en naast het schema wijzen zichzelf.

Ook de standentabel wordt automatisch ingevuld.
Via Vergelijken wordt gekeken op welke positie het betreffende team in de vorige tabel staat en dan wordt via Index het gewenste aantal winst-punten opgehaald (uit en thuis opgeteld).

De formules in de overige kolommen zijn vergelijkbaar of ‘rechttoe-rechtaan’.

In kolom BW staat een code, die gebruikt wordt om de teams in de juiste volgorde te kunnen sorteren en de Plaats in de stand te bepalen.
Wanneer namelijk nieuwe resultaten worden toegevoegd in Speelschema, dan worden wel alle berekeningen direct uitgevoerd en ook de Plaats wordt bepaald, maar de volgorde in de standenlijst verandert niet. Wil je ook de volgorde juist hebben, klik dan op de knop Standen bijwerken. De betreffende VBA-routine (zie hierna) zal dan de sortering uitvoeren.

In cel BW6 staat de formule =WAARDE( TEKST(BS6;”000″)&TEKST(100+BT6;”000″)&TEKST(BU6;”000″)&TEKST(100-BN6;”00″)&TEKST(AANTAL.ALS($BM$6:$BM$23;”>=”&BM6);”00″))

Ook hier wordt de functie Tekst gebruikt; in dit geval om zeker te weten dat het betreffende getal altijd met exact hetzelfde aantal cijfers wordt weergegeven.
Met de functie Waarde wordt van de resulterende tekst weer een getal gemaakt.

Nico heeft in een opmerking bij Code netjes vermeld hoe de code is opgebouwd.

NB bij de Eredivisie zijn na 3 speelrondes al geen gelijke standen meer; bij de KK-divisie na 2 speelrondes nog wel.

In kolom BL wordt de plaats van het team in de stand bepaald: =RANG(BW6;$BW$6:$BW$23)
Met behulp van Voorwaardelijke opmaak krijgen bepaalde Plaatsen ook nog een kleurcode; dit is dus onafhankelijk van de sortering.

Overige divisies en nacompetitie

Bij de KK-divisie heeft Nico ook nog overzichten gemaakt ten behoeve van de bepaling van de periode-kampioenen. Zover ik nu heb kunnen zien heeft hij alle lastige afwijkingen en problemen daarmee uitstekend opgelost.

Wat de nacompetitie betreft: de maker van het systeem is nog niet helemaal zeker over de juistheid daarvan.

“De nacompetitie is voor mij nog experimenteel gezien de complexe regels.
Ik had niet verwacht, dat ik tot iets zou kunnen komen, maar al werkende daaraan ontstond er toch iets”.

Nieuwe competitieronde

Wilt u dit systeem ook een volgend seizoen gebruiken, dat moet er nogal wat aangepast worden:

in het tabblad Data van het Voorbeeldbestand het seizoen aanpassen
ook in Data de teams in de drie divisies aanpassen (hoeven niet per se gesorteerd te zijn)
op het tabblad Data de teams van de Eredivisie selecteren en Ctrl-C (kopiëren) drukken; in het tabblad Eredivisie op de cel BM6 rechts klikken en Plakken speciaal/Waarden kiezen
dezelfde werkwijze hanteren voor de teams van de eerste en tweede divisie
LET OP bij de eerste divisie moeten de teams vijf keer geplakt worden.
in het tabblad Speelschema alle uitslagen wissen en de wedstrijden die gespeeld zullen gaan worden overschrijven.
LET OP zorg dat de namen van de clubs exact gelijk zijn aan die in het tabblad Data en dat de 2 ploegen gescheiden worden door een spatie, min-teken en nog een spatie

VBA

Met behulp van de knoppen Standen bijwerken worden bijbehorende VBA-routines uitgevoerd.
Voor de Eredivisie ziet die er als volgt uit:
Het bereik met de naam StandEredivisie wordt gesorteerd op kolom BW en wel in Aflopende volgorde (grootste getal eerst) en het bereik heeft geen Kop.

Alle andere sorteringen gaan op dezelfde manier. Bij de KK-divisie worden 5 sorteringen achter elkaar uitgevoerd; dus voor alle schema’s van het betreffende tabblad.

In het Excel-object ThisWorkbook staat de volgende routine:

De For-Next-loop loopt alle tabbladen langs en afhankelijk van de inhoud van de cel met de naam BevJN (deze staat in het tabblad Data) wordt de beveiliging aan of uit gezet (zonder wachtwoord).

Application.ScreenUpdating zorgt er voor, dat het scherm niet gaat ‘flikkeren’ bij het selecteren van een tabblad.

Zoeken: Index en Vergelijken

19 oktober 2016Excelavz, CSE, Index, Vergelijken, VERT.ZOEKENGijs Verbruggen

Op ieder Excel-forum zie je regelmatig de opmerking, dat het gebruik van de standaard-functie VERT.ZOEKEN (verticaal zoeken) niet flexibel is en foutgevoelig (en dat geldt ook voor de horizontale variant). Meestal wordt er dan als alternatief verwezen naar het gebruik van INDEX, gecombineerd met VERGELIJKEN.
Ik heb daar al eens eerder een artikel aan gewijd (Alternatief voor verticaal zoeken).

In de praktijk zie ik, dat er toch vaak met VERT.ZOEKEN wordt gewerkt, zo nodig met behulp van allerlei hulpkolommen. Bijna altijd met het argument: “Ik weet niet meer hoe ik die 2 functies moet combineren.”

Ik kwam pas een handige truc tegen, waardoor het invoeren van deze gecombineerde functies werkelijk een fluitje van een cent wordt.
Hierna zal ik deze tip toelichten, maar eerst laat ik (nogmaals) zien dat de combinatie van INDEX en VERGELIJKEN echt beter is dan VERT.ZOEKEN.

Voorbeeld van VERT.ZOEKEN

Hiernaast zie je een simpel voorbeeld van een werknemersadministratie: naast een personeelsnummer staat de naam en de afdeling.

Wanneer we een personeelsnummer kennen zien we in één oogopslag welke persoon daar bij hoort. Zou het bestand veel groter zijn (en niet gesorteerd op nummer), dan kan het zoeken wat lastiger worden. Daar komen de Excel-zoekfuncties om de hoek kijken. In het Voorbeeldbestand is in het tabblad VZoeken het bestandje opgenomen.

In cel F3 staat het personeelsnummer waarvan we de naam en afdeling willen weten. In G3 staat de formule
=VERT.ZOEKEN(F3;B3:D7;2;ONWAAR)
Ofwel: zoek de waarde van F3 op in de eerste kolom (VERTicaal) van het bereik B3:D7 en geef de corresponderende waarde uit de 2e kolom van dat bereik terug. De zoekwaarde moet exact overeenkomen vandaar dat bij de laatste parameter/argument van de functie (Benaderen) ONWAAR is ingevuld.

Wijzig de waarde in F3 en de corresponderende naam wordt opgehaald; net als de betreffende afdeling in cel H3, waar de gegevens uit de 3e kolom worden uitgelezen.

NB1 als het nummer niet in het bestand voorkomt zal de functie #N/B (niet beschikbaar) opleveren.

NB2 de Excel zoek-functie zoekt de waarde van het eerste argument altijd in de eerste kolom van het bereik, dat opgegeven is in het tweede argument. Vandaar dat je niet ‘naar links’ kunt zoeken. Dus als je een naam weet kun je op deze manier niet het nummer opzoeken.

NB3 voeg tussen de kolommen C en D een nieuwe kolom in en kijk wat er met het resultaat van het zoeken gebeurt!

NB4 voeg een nieuwe werknemer onderaan toe, plaats het personeelsnummer in cel F3. Wat gebeurt er?

Index en Vergelijken (deel 1)

In het tabblad IndVerg van het Voorbeeldbestand is gebruik gemaakt van INDEX en VERGELIJKEN.

Hoe werkt dit?

allereerst moet de zoekwaarde (in dit geval cel F3) vergeleken worden met de kolom met personeelsnummers (B3:B7). Het resultaat is de positie van de zoekwaarde in het bereik (in dit geval 1; zie cel J3).
Met de derde parameter van de functie VERGELIJKEN kunnen we aangeven welk soort resultaat er moet worden opgeleverd; met 0 geven we aan, dat er een exacte match moet zijn.
de zo gevonden positie wordt gebruikt in INDEX om een resultaat op te halen in de kolom met namen (C3:C7).

Op deze manier is het probleem van de NB3 van hierboven in ieder geval opgelost.

Ook de NB2 kunnen we hiermee omzeilen: in cel G7 zoeken we het nummer op in B3:B7, dat hoort bij de naam in cel F7 door die waarde te vergelijken met de inhoud van C3:C7.

NB5 de functie VERGELIJKEN levert altijd maar 1 resultaat terug; de eerste die aan de voorwaarde voldoet. Een oplossing hiervoor wordt verderop uitgewerkt.

Index en Vergelijken (deel 2)

Ook de NB3 moeten we nog oplossen: als er werknemers bij komen moeten de formules blijven werken.

Het eerste wat we doen is van het personeelsbestand een tabel maken:

klik op één van de cellen in het bestand
kies in de menutab Invoegen de optie Tabel; zorg dat het vinkje aan staat bij De tabel bevat kopteksten.
de tabel wordt automatisch van een ‘zebra’-opmaak voorzien. In het Voorbeeldbestand heeft de tabel op het tabblad IndVerg2 de naam tblWerknemers gekregen (klik op Ontwerpen in de nieuwe menutab Hulpmiddelen voor tabellen en wijzig de standaardnaam).

In cel G3 gaan we opnieuw de alternatieve opzoek-formule maken om de naam bij een nummer te vinden:

tik in: =index(
maar in plaats van het intikken van C3:C7 (het bereik met namen) wijzen we met de cursor de bovenrand van cel C2 aan; de cursor wordt dan een zwarte pijl naar beneden. Op dat moment 1 keer klikken en de formule wordt aangevuld met tblWerknemers[Naam] (ofwel de kolom Naam uit de tabel tblWerknemers).
de rest mag dan duidelijk zijn: tik in ;vergelijken( en klik op cel F3
tik in ; en klik op de bovenrand van cel B2; de formule wordt aangevuld met tblWerknemers[Nr]
tik in ;0))
druk op Enter

Voeg een nieuwe werknemer toe en kijk of de formules nog werken.

Index en Vergelijken (deel 3)

Het blijkt niet zo makkelijk om te onthouden hoe de combinatie van deze functies moet worden ingevoerd. Maar pas kwam ik een handige truc tegen (zie Wyn Hopkins op LinkedIn): gebruik de auto-correctie-mogelijkheden van Office! En dit kan natuurlijk met iedere complexe of anderszins moeilijk te onthouden functie(-combinatie).

Kies in Excel de menutab Bestand
klik op Opties en dan op Controle.
klik op de button AutoCorrectie-opties
in het nieuwe scherm vullen we onder Vervangen: een afkorting in die we hierna zullen gebruiken, bijvoorbeeld avz (Alternatief Voor Zoeken).
NB werkt u bij of voor de firma Aluminium Verwerking Zuid dan zult u een andere, niet gebruikte afkorting moeten nemen.
onder Door: komt de formule
=INDEX(Stap3_Waar_is_het_resultaat?;VERGELIJKEN(Stap1_Wat_zoeken?;Stap2_Waar_zoeken?;0))
Kies Toevoegen en OK

Nu gaan we deze afkorting gebruiken:

open het tabblad IndVerg3 in het Voorbeeldbestand
klik in cel G3; de formule die daar staat gaan we opnieuw opbouwen
tik in avz (of een andere afkorting die bij Vervangen: is ingevuld) en voeg nog een spatie toe
de auto-correctie doet zijn werk en in plaats van avz staat er nu onze index/vergelijken-formule!
dubbelklik op Stap1 (door de underscores wordt alles tot aan de ; geselecteerd) en klik op cel F3
dubbelklik op Stap2 en klik op de bovenrand van cel B2
dubbelklik op Stap3 en klik op de bovenrand van cel C2

Makkelijk toch? Er is nu geen enkele reden meer om VERT.ZOEKEN (of HORIZ.ZOEKEN) te blijven gebruiken.

Index en Vergelijken (multi-criteria)

In ons voorbeeldbestand komen twee mensen voor met de naam Jansen. Excel levert ons bij het zoeken altijd de eerste (zie cel I3 in het tabblad IndVergMulti van het Voorbeeldbestand).
Willen we zeker weten dat we de juiste persoon ophalen dan zullen we nog meer eigenschappen er bij moeten betrekken om zo de zoekopdracht uniek te maken, bijvoorbeeld de geboortedatum.

NB voordat u in Excel een zoek-optie implementeert, moet u zeker weten op welke manier u iemand of iets uniek kunt identificeren; misschien is naam en geboortedatum niet genoeg.

Voordat we de multi-criteria-formule gaan implementeren moeten we eerst kijken naar een alternatieve implementatie van VERGELIJKEN.
In het tabblad IndVergMulti in cel L4 plaatsen we de volgende formule:

=VERGELIJKEN(WAAR;tblWerknemers3[Naam]=G4;0)

In plaats van het vergelijken van een zoekwaarde (G4) met een bepaald bereik (tblWerknemers3[Naam]) vergelijken we hier de zoekwaarde WAAR met een reeks uitkomsten, die ontstaat door te controleren of de cellen in de kolom Naam gelijk zijn aan G4.

NB wilt u weten hoe een formule intern de tussenstappen berekent, kies dan in de menutab Formules de optie Formule evalueren.

Excel moet wel weten dat G4 met ALLE cellen uit de kolom Naam moet worden vergeleken; daarom moet deze formule als een zogenaamde array-formule worden ingevoerd door niet gewoon op Enter te drukken maar op Ctrl-Shift-Enter (soms wordt dit dan ook de CSE-methode genoemd).
Excel plaatst automatisch accolades om de formule bij CSE; dit is niet hetzelfde als wanneer er handmatig accolades geplaatst worden!!

Maar nu multicriteria: de vergelijking van G4 met de cellen van de kolom Naam levert een reeks op met daarin WAAR of ONWAAR; intern Excel is dit een 1 , respectievelijk 0.
Door deze reeks WAAR/ONWAAR (ofwel 1 en 0) te vermenigvuldigen met een andere reeks, die ontstaat door H4 (een datum) te vergelijken met de kolom GebDtm, krijgen we een nieuwe reeks met een 1 als aan alle twee de voorwaarden is voldaan, anders een 0.

In cel M4 staat dan ook de formule
=VERGELIJKEN(1;(tblWerknemers3[Naam]=G4)*(tblWerknemers3[GebDtm]=H4);0)

LET OP ook deze formule moet met CSE worden ingevoerd.

LET OP plaats bij het vermenigvuldigen van dit soort reeksen er haakjes omheen.

NB aangezien de vermenigvuldiging altijd een 1 of 0 oplevert (en niet WAAR of ONWAAR) moet als zoekwaarde een 1 worden opgegeven (bekijk de tussenstappen met Formule evalueren).

Om het juiste resultaat op te halen op basis van de 2 criteria staat in I4 de formule
=INDEX(tblWerknemers3[Nr];VERGELIJKEN(1;(tblWerknemers3[Naam]=G4)*(tblWerknemers3[GebDtm]=H4);0))

LET OP ook deze formule moet met CSE worden ingevoerd!

Cadeau: in de cellen I7 en J7 staat een aangepaste formule, die er voor zorgt, dat bij een onbekende werknemer er geen #N/B als resultaat komt.

Gegevenstabel

3 oktober 2016ExcelAfronden, Gegevenstabel, Index, NORM.VERD, VERT.ZOEKENGijs Verbruggen

Ik had al lang op mijn lijstje staan om iets te schrijven over Gegevens-tabellen in Excel. Een handig hulpmiddel bij scenario-analyses.
Een artikel op de site van Chandoo was aanleiding om daar daadwerkelijk invulling aan te geven!

Even verwarring wegnemen.
In Excel kennen we twee soorten tabellen: Tabellen en Gegevenstabellen. Helder? Vóór versie 2010 werden deze tabellen Lijsten, respectievelijk Tabellen genoemd.
Laten we de nieuwere terminologie maar hanteren: in Excel is een Tabel een verzameling bij elkaar horende cellen, die door de gebruiker worden gevuld (zie ook de artikelen Kunst en Excel en Tabellen deel 2); een Gegevenstabel daarentegen wordt door Excel automatisch ingevuld.
Cryptisch? Lees hieronder verder.

Gegevenstabel met 1 variabele

Om de werking van een Gegevenstabel uit te kunnen leggen hebben we een voorbeeld nodig. In het Voorbeeldbestand heb ik een aangepaste versie van het Chandoo-voorbeeld gebruikt:

Sara wil een kopieer-bedrijfje beginnen en maakt een bedrijfsanalyse in Excel
Ze kan copiers leasen met een capaciteit van 100.000 kopieën per jaar voor een bedrag van € 5.000 per maand.
Een ruimte heeft ze ook al op het oog voor € 1.000 per maand.
De kostprijs van een kopie is 2 cent.
Marktonderzoek heeft uitgewezen, dat klanten 11 cent voor een kopie over hebben.
Hoeveel kopietjes moeten er dagelijks gemaakt worden om break-even te spelen; uitgaande van 50 werkweken van 5 dagen?

In het Voorbeeldbestand op het tabblad WA1 zijn deze aannames in het blok Input verwerkt.

In datzelfde tabblad staat ook een blok Berekeningen:

eerst het aantal kopieën: in cel G4 staat de formule =C8*C9
dan moeten we weten hoeveel copiers Sara daarvoor moet leasen: het aantal kopieën gedeeld door de capaciteit van een copier. Dit moet een heel getal zijn dus in cel G5 komt de formule =AFRONDEN.NAAR.BOVEN(G4/C5;0).
De tweede parameter (0) geeft aan dat het resultaat geen decimalen mag hebben.
De formules voor de kosten, opbrengsten en de winst mogen verder geen verrassingen meer met zich mee brengen.
De 3 cellen in regel 16 hebben een voorwaardelijke opmaak gekregen, zodat direct duidelijk is of er sprake is van winst of verlies. 500 kopieën per dag zijn duidelijk niet voldoende om onder de gehanteerde aannames winst te maken.

Door nu het aantal kopieën per dag in cel C9 te wijzigen kunnen we proberen het break-even-point voor dit bedrijfje te vinden.

En juist dit is het moment, dat een Gegevenstabel zijn kracht kan bewijzen:

plaats ergens in een lege kolom alle aantallen, die doorgerekend moeten worden (bv 100 t/m 1000)
één regel hoger in de kolom rechts daarnaast plaatsen we een verwijzing naar de cel met de winst/verliescijfers (of rechtstreeks de formule =G14-G11)
selecteer dan alle cellen met aantallen, inclusief de lege cel daarboven en alle cellen daarnaast (in het voorbeeld hiernaast dus de cellen J7 tot en met K26.
Tip selecteer cel J7 en druk op Ctrl-A
kies binnen de menu-tab Gegevens in het blok Hulpmiddelen voor gegevens de optie Wat-als-analyse en daarbinnen de optie Gegevenstabel
we hebben een 1-dimensionale gegevenstabel: alleen in de kolom staan waardes die door Excel moeten worden doorgerekend. In het vervolgscherm vullen we dan ook alleen de kolominvoer in; we verwijzen naar de cel met het aantal kopieën (cel C9).
Klik OK en Excel vult alle cellen in kolom K met de winst, die bij het betreffende aantal hoort!

NB1 als je een waarde in kolom J wijzigt, zal ook het resultaat in kolom K worden aangepast

NB2 we hebben nu alleen een formule in kolom K gezet; in een Gegevenstabel met 1 variabele kunnen echter meerdere resultaten naast elkaar berekend worden.

NB3 Excel heeft een heel speciale matrix-formule in de sheet gezet (let op de {} rond de formules in kolom K). Een consequentie daarvan is, dat je niet zomaar meer cellen/regels kunt weghalen of toevoegen. Zorg dus bij het maken van een gegevenstabel dat je ruim voldoende invoercellen hebt. Moet je later iets toevoegen dan zul je de bestaande gegevenstabel moeten weghalen en een nieuwe tabel creëren.

Tot en met 1000 exemplaren per dag wordt dit bedrijf niet winstgevend. Tot en met 400 kopietjes neemt het verlies af maar daarna schiet die weer omhoog (er moet een 2e copier geleased worden). Tussen 400 en 800 loopt het verlies weer terug; na de hik bij 800 geldt hetzelfde verhaal.

Om het overzicht wat flexibeler te maken zijn in het Voorbeeldbestand 2 cellen opgenomen (K4 en K5) waarmee het startaantal en de stapgrootte aangepast kunnen worden.

Vullen we daar 500 resp. 100 in, dan krijgen we direct het overzicht zoals hiernaast.
Bij 1.200 kopietjes per dag speelt Sara quitte, maar daarboven gaat het weer mis (er moet nog een copier bij!). Pas rond 2.000 exemplaren begint er een stabiele winst te ontstaan.

Gegevenstabel met 2 variabelen

Maar het model sluit niet goed aan bij de realiteit: wanneer het aantal kopietjes per dag ineens groter wordt kan Sara natuurlijk niet (direct) over meer of nieuwe copiers beschikken; met de leverancier moeten van tevoren goede afspraken gemaakt worden. Voor het aantal gewenste copiers moet een inschatting gemaakt worden. Die raming nemen we in de input op.

In het tabblad WA2 van het Voorbeeldbestand is die wijziging doorgevoerd.
Ook is bij de berekening daar de formule voor het aantal kopieën aangepast; hier komt het minimum van (het aantal dagen * aantal kopieën) en (aantal copiers * capaciteit): =MIN(C9*C10;C6*C5)

Ons winst-model is nu afhankelijk van 2 variabelen: het aantal copiers en het aantal kopieën per dag.
Dus willen we een scenario-analyse doen dan zal onze gegevenstabel ook 2-dimensionaal moeten zijn:

plaats ergens in een lege kolom alle aantallen kopieën, die doorgerekend moeten worden (bv 100 t/m 1000)
één regel hoger in de kolommen rechts daarnaast plaatsen we de aantallen copiers, die we willen doorrekenen
in de cel links van de aantallen copiers (en dus net boven het aantal kopieën) komt weer een verwijzing naar de cel met de winst/verliescijfers
selecteer dan de cel met de formule en het hele blok cellen met links en boven de aantallen (in het voorbeeld hiernaast dus de cellen K8 tot en met R27.
kies binnen de menu-tab Gegevens in het blok Hulpmiddelen voor gegevens de optie Wat-als-analyse en daarbinnen de optie Gegevenstabel
we hebben nu een 2-dimensionale gegevenstabel: in de rij staan de waardes voor het aantal copiers en in de kolom het aantal kopieën, waarvoor de resultaten door Excel moeten worden doorgerekend. In het vervolgscherm vullen we dan ook allebei de invoercellen in
Klik OK en Excel vult alle cellen in de gegevenstabel in.

Uit de tabel volgt dat het bedrijfje pas levensvatbaar gaat worden bij 4 kopiers en dat Sara moet proberen dan 1600 kopieën per dag te produceren.

Risicoanalyse mbv Gegevenstabel

Ambitieus als Sara is, gaat ze voor 5 copiers: “Het lukt me wel om gemiddeld 2000 kopietjes per dag aan de man te brengen! Mijn winst verdubbelt dan tov 4 copiers.”

Een vriend zegt haar, dat ze dan wel een risico-analyse moet uitvoeren. Exact 2000 kopieën per dag is niet reëel: “Ik denk dat het aantal normaal verdeeld is met een gemiddelde van 2000 en een standaard-deviatie van 100.”

In het tabblad WA3 van het Voorbeeldbestand is het model uitgebreid met een kans dat het betreffende aantal kopieën, op basis van het gemiddelde en SD , gehaald zal worden. Daarvoor gebruiken we in cel J11 de formule
=NORM.VERD.N(K11;$K$4;$K$5;ONWAAR)

Ofwel hoe groot is de kans dat er 1550 (cel K11) kopieën worden gemaakt onder de aanname dat de verdeling van het aantal Normaal Verdeeld is met een gemiddelde van 2000 (cel K4) en een standaard-deviatie van 100 (cel K5)? De echte kans, niet een cumulatieve (dus de laatste parameter is Onwaar).

Cel K10 bevat nu geen verwijzing naar de Winst maar naar de Winst * Kans.

Onder de gegevenstabel tellen we de, met de kansen, gewogen winsten per kolom op. Die delen we door de som van de gebruikte kansen en krijgen dan de gemiddelde winst per kolom. De verwachte winst voor Sara wordt dan ongeveer 10% lager.

Multi-variabele Gegevenstabel

Sara heeft het idee, dat ook de huurprijs nog bespreekbaar is en wil in de analyse hier rekening mee houden. Het model krijgt dan een derde input-variabele. Maar de Excel-gegevenstabel kan maar 2 dimensies aan.

Geen nood:

zorg dat alle combinaties van de variabelen (dat kunnen er dus ook 10 zijn!), die doorgerekend moeten worden in een scenario-tabel zijn opgenomen
wijzig de input-tabel zodanig dat de input-variabelen afhankelijk zijn van het scenarionummer. Dat kan met Verticaal Zoeken in de scenariotabel (in het voorbeeld op het tabblad WA4 is dat B25:F37).
maak een 1-dimensionale Gegevenstabel, waarbij in de eerste kolom de scenarionummers staan. In de kolomkoppen daarnaast maken we dan verwijzingen naar de scenario-tabel (zie hieronder de formule in cel K5) en naar de bijbehorende winst.
het is niet fraai, dat het resultaat van die verwijzingen altijd zichtbaar is: wijzig de opmaak van die kopjes zodanig, dat de tekstkleur gelijk is aan de achtergrondkleur.

Bel-grafiek of -diagram

14 september 2016Excel, VBA (Visual Basic for Applications)Beldiagram, Belgrafiek, Kleurcodes, RGB, Tabel, VERT.ZOEKENGijs Verbruggen

In het artikel Eerbetoon aan Rosling is gebruik gemaakt van zogenaamde bel-diagrammen, ook vaak bel-grafiek genoemd.
Met behulp van dit soort grafieken is het mogelijk om 3 dimensies weer te geven: via de x- en y-as en door de grootte van de bellen.

Het artikel was aanleiding voor een vraag, die er op neer kwam of er ook nog een vierde dimensie mogelijk was door de kleur van de bellen te variëren.
Met de hand is dit mogelijk maar er is geen standaard-optie, die bij wijzigingen in de sheet de kleuren automatisch zal aanpassen (wat wel gebeurt met de grootte van de bellen).

Kleuren in Excel

Voordat we verder gaan met een mogelijke oplossing voor het probleem van Sander moeten we eerst iets meer weten over de codering van de kleuren in Excel.

Alle kleuren worden opgebouwd door een combinatie van Rood, Groen en Blauw (de zogenaamde RGB-codering). Ieder van die 3 basis-kleuren kan in 256 stappen worden toegevoegd (lopend van 0 tot 255).
Zwart is dan opgebouwd uit 0 delen van iedere kleur, terwijl wit ontstaat door het mengen van alle drie de kleuren met een intensiteit van 255. Door alleen Rood en Groen te mengen (met een intensiteit van 255) ontstaat geel.

Door de blauw-code met 65536 te vermenigvuldigen, de groen-code met 256 en deze 2 dan op te tellen met de rood-code krijgen we een decimale kleurcode.
In het schema hierboven en op het tabblad Param van het Voorbeeldbestand staan enkele voorbeelden.

De decimale codes zullen we hierna gaan gebruiken.

Probleem

Als je een bellengrafiek maakt krijgen de bellen standaard allemaal dezelfde kleur. Deze kunnen gewijzigd worden door één voor één de bellen te selecteren (klik op een bel, dan wordt de hele serie geselecteerd; klik nogmaals op de bel dan wordt de selectie beperkt tot de betreffende bel) en dan via rechts-klikken de opmaak aan te passen.

Maar de vraag is: kan dit geautomatiseerd, waarbij de kleur afhankelijk is van de inhoud van bepaalde Excel-cellen?

Hieronder volgt een oplossing waarbij gebruik wordt gemaakt van VBA; een zelf-geschreven routine controleert van iedere bel wat de corresponderende kleurcode moet zijn en past die aan. Wel moet de routine na iedere wijziging handmatig aangeroepen worden; daar hebben we dan weer een button voor gecreëerd.

Oplossing

Zoals voor ieder bel-diagram hebben we naast waarden voor de x- en y-as ook waarden nodig die de grootte van de bellen zullen bepalen.

NB1 een bel-grafiek is een speciale vorm van een spreiding- of xy-grafiek en kan dus op de assen alleen maar met getallen werken, geen teksten.

NB2 we kunnen niet exact de grootte van de bellen bepalen; Excel bepaalt zelf de grootte relatief ten opzichte van elkaar.

In het tabblad Data van het Voorbeeldbestand is dit tabelletje opgenomen en is een bel-grafiek gemaakt.

Maar nu komt de vraag van Sander: kunnen de bellen een eigen kleur krijgen afhankelijk van het aantal keren, dat een combinatie van x en y voorkomt. Hij zou graag zien, dat een bel rood is als het aantal 1 tot 5 is, geel bij 5 tot 10 en groen bij 10 of meer.

Daarom eerst even een hulptabel opgezet (zie tabblad Param in het Voorbeeldbestand):

een kolom voor de ondergrens voor een bepaalde kleur
een kolom met de decimale code voor de gewenste kleur (zie hierboven)
de 8 cellen zijn in een Excel-tabel geplaatst (via Invoegen/Tabel) en deze tabel heeft de naam tbKleur gekregen.

Aan de gegevens heb ik een vierde kolom toegevoegd met een fictief aantal en nog een kolom die uit Param afleidt welke kleurcode de bel moet krijgen. De gegevens zijn in een Excel-tabel opgenomen met de naam tbData.

De formule in de kolom Kleur ziet er als volgt uit:
=VERT.ZOEKEN([@Aantal];tbKleur;2;WAAR)

Ofwel: neem de waarde uit de corresponderende kolom Aantal, zoek in de Excel-tabel tbKleur deze waarde op en geeft de waarde uit de 2e kolom terug (de kleurcode). We zoeken geen exacte match, maar een ‘benadering’ (de grootste waarde die nog voldoet); vandaar WAAR als laatste parameter in de functie VERT.ZOEKEN.

Alles staat nu klaar om de bellen de juiste kleurcodes te geven.

VBA-routine

Zoals hiervoor aangegeven, kent Excel geen automatische koppeling tussen de kleur van de bellen en de waarde in bepaalde cellen. Dus zullen we het zelf moeten doen:

in de eerste regel geven we ons programma (subroutine) een naam: BelKleur
in de 2e en 3e regel zorgen we er voor dat de variabelen pts en x netjes worden gedefinieerd (mbv Dim): in de eerste variabele gaan we punten bewaren, in de tweede gehele getallen.
in de 4e regel vullen we de variabele pts met de punten (Points) uit de eerste serie (SeriesCollection(1)) van de grafiek (Chart) met de naam grBel (ChartObjects(“grBel”)) in het actieve Excel-tabblad (ActiveSheet)
dan komt er een For-Next-loop waarbij x loopt van 1 tot het aantal bellen in de grafiek (pts.Count). Alles tussen For en Next wordt dus net zo vaak herhaald als er bellen zijn.
regel 6 en 7 horen bij elkaar (door de Underscore en een spatie op het einde van regel 6).
De opmaak (Format) van punt x (pts(x)) wordt ingesteld; de bel wordt gevuld (Fill) met een kleurcode (ForeColor.RGB) gelijk aan de inhoud van de cel x+1 (de kop telt ook mee, vandaar +1) uit de kolom Kleur van de tabel tbData uit de actieve sheet (ActiveSheet)

Bellen kleuren

Iedere keer als er iets aan de gegevens wordt gewijzigd (nieuwe regels toegevoegd, aantallen aangepast) zal bovenstaande routine moeten worden uitgevoerd.

Dat kan op verschillende manieren:

klik op de button Bellen kleuren op het tabblad Data van het Voorbeeldbestand
kies op de menu-tab Ontwikkelaars de optie Macro’s, selecteer de routine BelKleur en klik op Uitvoeren
Druk op Alt-F8, selecteer de routine BelKleur en klik op Uitvoeren

G-Info

van gegevens naar informatie