Tagarchief: Indirect

Hoog-Laag-Slot-grafiek

Excel, , , , ,

Binnenkort (nou ja, over een paar maanden) hebben we weer verkiezingen. In de aanloop daar naartoe zien we in de media steeds vaker de resultaten van diverse peilingen.

Op de site peilingwijzer.tomlouwerse.nl is het meest recente resultaat van een wetenschappelijk onderbouwde combinatie van 5 peilers (nee Maurice, geen pijlers!) te vinden.

Een grafiek op die site laat van 13 politieke partijen het verloop van de peilingen in de tijd zien; naast het gemiddelde van de 5 peilers zien we in de grafiek ook een 95%-onzekerheidsmarge (het licht gekleurde gebied).

Hieronder laat ik zien, hoe je in Excel een dergelijke grafiek kunt maken; we gebruiken daar een hoog-laag-slot-grafiek voor.
Daarbij moet wel aangetekend worden, dat de grafiek op de site van Tom Louwerse veel meer interactieve elementen bevat!

Basisgegevens

Zoals voor iedere grafiek hebben we basisgegevens nodig; deze zijn in dit geval op te halen vanaf de download-pagina van de Peilingwijzer.

In het Voorbeeldbestand heb ik op het tabblad Peilingwijzer deze gegevens geplaatst (op dit moment van 12 september 2012 tot en met 27 november 2016); zoals in eerdere voorbeelden ‘gieten’ we deze in de vorm van een tabel (Invoegen/Tabel, kopregels aanvinken) en geven die tabel de naam tblpeilingWijzer.

NB1 komen er nieuwe peiling-data beschikbaar, voeg deze dan onderaan de tabel als nieuwe regels toe.

NB2 komen er nieuwe partijen in het overzicht bij, voeg deze dan rechts aan de tabel als nieuwe kolommen toe.

Parameters

Hierna gaan we een grafiek maken van de peiling-resultaten. Hierbij kan echter maar één partij tegelijkertijd worden weergegeven. Om de invoer daarvan straks te vergemakkelijken leggen we de keuzemogelijkheden in een aparte tabel vast (zie tabblad Param in het Voorbeeldbestand).

NB wordt er in de peilingen een extra politieke partij meegenomen, dan moet deze onderaan toegevoegd worden; aangezien het ook hier een tabel betreft, zal Excel de nieuwe regel automatisch in tblPartijen meenemen.

Het is ook wel handig om te weten wat de eerste en laatste datum is, die in het peilingoverzicht voorkomt.
In cel E2 van het tabblad Param uit het Voorbeeldbestand bepalen we de eerste/kleinste datum door het minimum van die kolom op te zoeken.
Normaal gesproken zou dat kunnen met de formule =MIN(tblPeilingWijzer[Datum]), maar helaas levert dat de waarde 0 op. Dat komt omdat in de datum-kolom geen echte datum staat maar een tekst, dus het wordt wat ingewikkelder:
{=MIN(DATUM(
LINKS(tblPeilingWijzer[Datum];4);
DEEL(tblPeilingWijzer[Datum];6;2);
RECHTS(tblPeilingWijzer[Datum];2)))}

Met behulp van de functie LINKS zoeken we het jaar op, DEEL haalt 2 tekens op vanaf positie 6 (de maand dus) en RECHTS levert de dag. Deze 3 resultaten geven we door aan de functie DATUM, die er een datum van maakt. De functie MIN zoekt dan de kleinste datum op.

LET OP de 3 tekst-functies kijken eigenlijk alleen naar de datum uit de corresponderende rij (de 2e dus). Door na het intikken van de formule niet op Enter te drukken maar op Ctrl-Shift-Enter wordt het een zogenaamde array- of matrix-formule en worden de 3 tekst-functies op alle datums ‘losgelaten’. Vaak wordt dit ook CSE-invoer genoemd; Excel plaatst automatisch accolades om de formule (niet handmatig intypen!).

De formule in E3 mag dan geen verrassingen meer bevatten.

Voor het gemak hebben we de cellen E2 en E3 een naam gegeven MinDatum, resp. MaxDatum.

Grafiek met onzekerheidsmarge

Voordat we een dergelijke grafiek kunnen maken moeten we eerst een methode hebben om de gegevens van één partij uit de basis te destilleren.

Cel C3 (met de naam invPartij) in het tabblad Ovz van het Voorbeeldbestand gebruiken we als keuzevak:

  1. kies in de menutab Gegevens de optie Gegevensvalidatie
  2. in het pop-up-scherm kiezen we bij Toestaan: de optie Lijst
  3. bij Bron: zouden we alle keuzemogelijkheden kunnen intikken gescheiden door een ; (punt-komma), dus bijvoorbeeld VVD;PvdA;GL etc.
    Maar dat is niet zo handig. Die lijst kunnen we ook uit het tabblad Param halen: kolom Partijen in de tabel tblPartijen.
    Helaas kan gegevensvalidatie niet goed omgaan met de nieuwere tabellen (geïntroduceerd in versie 2007), dus de formule =tblPartijen([Partijen]) werkt niet, maar wel als we de functie INDIRECT gebruiken (zie ook het artikel Tabellen (deel 2); denk aan de aanhalingstekens!!).
  4. Eventueel nog een Invoerbericht en/of Foutmelding toevoegen (zie Voorbeeldbestand) en we zijn klaar: klik op OK.

De voorbereidingen zijn klaar, nu nog de gegevens ophalen van de gekozen partij:

  1. in rij 5 van het tabblad Ovz van het Voorbeeldbestand staat een kopregel
  2. vanaf B6 naar beneden staan alle datums, waarvoor er peilingen zijn
  3. in cel C6 moet de eerste peiling van de gekozen partij komen:
    =INDEX(
    INDIRECT(“tblPeilingWijzer[“&invPartij&”]”);
    VERGELIJKEN($B6;tblPeilingWijzer[Datum];0))
    Weet u niet (meer) hoe de functie INDEX werkt? Kijk in het artikel Zoeken: Index en Vergelijken; daar vindt u ook een truc hoe u de de functie makkelijk implementeert.
    Weer gebruiken we INDIRECT om de kolom, waarin gezocht moet worden, afhankelijk te maken van de cel C3 (met de naam invPartij).
  4. de formules in D6 en E6 zijn vergelijkbaar, alleen worden daar de partij-gegevens uit de low- en high-kolom opgehaald.
  5. de drie formules uit C6:E6 worden naar beneden gekopieerd, zodat bij iedere datum de gegevens tevoorschijn komen.
  6. via de menutab Invoegen en de optie Tabel maken we van dit overzicht een tabel (met de naam tblGrafBasis). Wanneer er nieuwe peilingen beschikbaar zijn is het dan voldoende om onderaan de datum toe te voegen. Alle formules worden dan automatisch door Excel ingevuld.

En nu de grafiek:

  1. plaats de cursor ergens in tblGrafBasis
  2. in de menutab Invoegen kiest u in het blok Grafieken de optie Overige grafieken.
    In het uitklapmenu nemen we de eerste grafieksoort in het blokje Hoog/Laag/Slot.
    Dit soort grafieken wordt vaak in de financiële wereld gebruikt om een overzicht van de dagkoersen weer te geven: per dag ziet u dan de hoogste, laagste en slotkoers.
    NB Excel beoordeelt automatisch in welke kolom de hoge, lage of slot-waarde (of in ons geval het gemiddelde) staat.
  3. Nog even wat opmaak regelen:
    * kies in de nieuwe menutab Hulpmiddelen voor grafieken het tabblad Indeling
    * kies in het blokje Huidige selectie de Reeks Gemiddeld 
    * daarna de optie Selectie opmaken
    * en kies bij Lijnkleur een gewenste kleur
    * kies in het blokje Huidige selectie de Hoog/laag-lijnen
    * daarna de optie Selectie opmaken
    * en kies bij Lijnkleur dezelfde kleur, maar met een Transparantie van 75%
    * kies in het blokje Huidige selectie de Reeks Hoog
    * daarna de optie Selectie opmaken
    * en kies bij Markeringsopties Geen
    * Grafiektitel laten verwijzen naar cel E3
    * aan de linker-as een Titel toevoegen, de notatie wijzigen in een percentage zonder decimalen en zorgen dat er het bereik altijd van 0% tot 30% loopt.

Wijzig cel C3 en bekijk het resultaat!

Tabellen (deel 2)

Excel, , ,

TabelIn mijn vorige artikel (Kunst en Excel) heb ik 11 voordelen van het gebruik van tabellen in Excel besproken.

Op een forum voor Excel-experts heb ik gevraagd of er ook nadelen zijn aan tabellen.

Daar zijn 2 reacties op gekomen, waarvan de tweede aanleiding is geweest voor dit artikel.

Nadelen Tabellen in Excel

De eerste reactie ging over de naam, die de tabel automatisch krijgt (Tabel1, de volgende Tabel2 etcetera). De naam moet dan nog aangepast worden om zodoende een zinvolle beschrijving te krijgen; tsja, hier zie ik zo gauw geen echt probleem (en ook geen oplossing).
Maar ook werd aangegeven, dat wanneer de tabel uit de werkmap wordt verwijderd de naam nog wel actief zou blijven. Zover ik kan nagaan, geldt dat wel voor cellen (of celbereiken) die een naam krijgen en daarna worden verwijderd, maar niet voor tabellen!

In de tweede reactie werd aangegeven, dat tabellen niet bij een gegevensvalidatie kunnen worden gebruikt.
Ja, daar heeft Microsoft een steek laten vallen: standaard kan alleen een celbereik als keuzelijst worden opgegeven. Wel is het mogelijk om een ‘gewone’ naam als bron daarvoor op te geven, maar dan moet je in de tabel de gewenste reeks een aparte naam geven en er voor zorgen, dat bij uitbreiding van de lijst deze naam ook wordt aangepast.
Maar voor dit probleem hebben we gelukkig wel een oplossing!

Gegevensvalidatie

TabelIn het Voorbeeldbestand heb ik een tabblad Param opgenomen, waar 2 tabellen staan:

  1. in het eerste blok staan afkortingen en omschrijvingen voor alle afdelingen die kunnen worden gebruikt.
    De tabel heeft de naam tblAfd gekregen (hoe je een tabel maakt en de naam kunt aanpassen is in het artikel Kunst en Excel besproken).
  2. daarnaast staat een overzicht van de mogelijke kostensoorten; deze tabel heeft de naam tblKst

Deze twee tabellen gaan we gebruiken voor een gegevensvalidatie:

  1. Tabelde invoer in cel B3 van het tabblad GegVal moet beperkt worden tot die drie afkortingen voor de afdelingen, die in Param zijn opgenomen (dus D, C en O).
  2. kies de menuoptie Gegevens en dan binnen het blok Hulpmiddelen voor gegevens de optie Gegevensvalidatie:
    Tabel
  3. Tabelin het vervolgscherm kiezen we bij Toestaan de optie Lijst.
  4. dan komt de optie Bron tevoorschijn; klik op Tabel en selecteer dan de cellen B3 tot en met B5 in het tabblad Param.
  5. Klik OK. Aan cel B3 in GegVal is nu een keuzepijltje toegevoegd; als je daar op klikt kun je één van de drie mogelijkheden kiezen.

Op dezelfde manier kan ook de invoer in cel C3 beperkt worden tot de drie mogelijke kostensoorten uit Param (de cellen E3 t/m E5).

Door de cellen B3 en C3 te kopiëren kan de gegevensvalidatie uitgebreid worden naar andere cellen.

Een groot nadeel is, dat als er nieuwe opties bijkomen voor de afdelingen en/of de kostensoorten de betreffende gegevensvalidaties moeten worden aangepast.

Gegevensvalidatie met Tabellen

In plaats van een verwijzing naar de cellen B3 t/m B5 in het tabblad Param (als bron geven we dan op =Param!$B$3:$B$5) zouden we de tabelverwijzing =tblAfd[Afd] willen gebruiken (dus de kolom Afd in de tabel tblAfd).
Als we dat doen krijgen we een foutmelding, maar die kunnen we omzeilen door de functie INDIRECT te gebruiken.

Deze functie zet een tekst om in een Excel-verwijzing. Als voorbeeld: INDIRECT(“B”&A1) geeft een verwijzing naar de cel B1 als A1 de waarde 1 bevat, maar verwijst naar B2 als de waarde van A1 gelijk is aan 2 etc.

Door als bron voor de gegevensvalidatie in te voeren =INDIRECT(“tblAfd[Afd]”) krijgen we toch het gewenste resultaat.

TabelBekijk in het tabblad Boekingen1 de gegevensvalidatie van de cellen in kolom C en E. Deze maken gebruik van deze methode.

Voor de opvoer van boekingen in dit tabblad is ook gebruik gemaakt van een tabel. Een voordeel is dat bij de opvoer van een nieuwe regel (record) onderaan de gegevensvalidatie ook direct wordt overgenomen.
Hetzelfde geldt voor de formules in de kolommen D en F, waar met behulp van VERT.ZOEKEN de omschrijving van de gekozen afdeling of kostensoort wordt opgehaald.

In het tabblad Boekingen2 worden alleen de omschrijvingen van de afdelingen en de kostensoorten ingevoerd. Ook de invoer hiervan is via gegevensvalidatie ´beveiligd´.

Wanneer er nu een afdeling aan het tabblad Param wordt toegevoegd (bijvoorbeeld H met als omschrijving HRM) dan zullen alle gegevensvalidaties automatisch deze mogelijkheid meenemen. Uiteraard geldt dit ook voor de uitbreiding van het aantal kostensoorten.

LET OP: in het tabblad GegVal wordt HRM niet als keuzemogelijkheid getoond, omdat daar de gegevensvalidatie ‘hard’ aan de cellen B3 t/m B5 van Param  is gekoppeld.

Cel.Lezen

Excel, ,

Een paar weken geleden kreeg ik via de website ginfo.nl een vraag over de Excel-functie Cel.Lezen.

Ik begon aan mijn geheugen te twijfelen, want ik (her)kende de functie niet.
Dus maar even gegoogeled; wat bleek: Cel.Lezen is een restant uit een ver verleden, toen VBA nog niet geïmplementeerd was, een zogenaamde Excel4Macro.
cellezenCel.Lezen is één van de vele functies, die niet meer gedocumenteerd zijn, maar nog wel gebruikt kunnen worden; niet direct, als een standaard-functie, maar via een omweg (het gebruik van de optie Namen beheren).
Steeds meer mogelijkheden van Cel.Lezen zijn (of worden) in nieuwere versies van Excel ingebouwd, maar soms kan deze functie toch nog goed bruikbaar zijn.
En los daarvan: het is nooit weg om nog eens met het onderdeel Namen van Excel te stoeien!

Gebruik van Cel.Lezen

Wat kun je doen met Cel.Lezen?
Deze macro/functie levert 66 (!) verschillende soorten informatie over de inhoud of de opmaak van een cel. CelLezenHierbij valt te denken aan de celverwijzing (welke rij, welke kolom) maar ook de inhoud, of de cel een formule bevat, welke kleuren gebruikt zijn etc.
In het Voorbeeldbestand is in het tabblad InfoType een totaal-overzicht opgenomen.

De functie Cel.Lezen heeft 2 parameters nodig: de eerste is het InfoTypeNummer en de tweede is de cel, waarvan de informatie opgehaald moet worden.

CelLezenZoals hiervoor al aangegeven, kan Cel.Lezen niet rechtstreeks aangeroepen worden; dit kan wel via een eigen formule/naam geregeld worden.

Een voorbeeld (zie tabblad VB in het Voorbeeldbestand):
het is altijd goed om te weten of en waar in een kolom (bijvoorbeeld C) formules staan.
We gaan dat in kolom D als volgt aangeven:

  1. plaats de cursor in cel D3
  2. kies binnen de menu-tab Formules de optie Namen beheren en dan de button Nieuw
  3. CelLezenvul het scherm in:
    * kies als naam BevatFormule
    * het bereik, waar deze naam geldig is, beperken we tot het betreffende tabblad (zie hieronder), dus Vb
    * en we verwijzen niet, zoals gebruikelijk is voor een Naam, naar een cel(bereik), maar naar een formule, namelijk
    =CEL.LEZEN(48;C3)
    (InfoTypeNummer 48 geeft als resultaat WAAR als de betreffende cel een formule bevat)
    * klik OK
  4. in het overzichtsscherm Namen beheren zien we onze nieuwe naam
  5. tik in cel D3 de formule =BevatFormule
  6. kopieer de formule in D3 naar beneden tot en met cel D15

NB1 als je goed kijkt in het overzichtsscherm Namen beheren, dan zul je zien dat onze formule iets is aangepast: aan cel C3 is ook de bladnaam Vb toegevoegd; dit is niet te voorkomen. Dat is ook de reden, dat we hiervoor de naam alleen maar geldig maken op het tabblad Vb

NB2 we hebben een relatieve verwijzing naar C3 gebruikt (zonder $-tekens); dit zorgt er voor, dat de formule in D4 naar C4 ‘kijkt’ etc.
Het effect hiervan zie je ook in Namen beheren: de formule bij Verwijst naar: is afhankelijk van de plaats van de cursor in het tabblad.

BevatFormule (vervolg)

Hiervoor is met behulp van de zelf-gedefinieerde naam BevatFormule een mogelijkheid gecreëerd om zichtbaar te maken of cellen al dan niet een formule bevatten.

In het tabblad Vb2 van het Voorbeeldbestand is een alternatieve methode gebruikt:
op dezelfde manier als hiervoor is, met cel D3 geselecteerd, een nieuwe naam gemaakt, BevatFormule2.
Deze naam is alleen geldig in Vb2 en heeft als verwijzing
=CEL.LEZEN(48;D3)

LET OP deze formule verwijst dus naar de cel zelf. Wanneer we ergens in het tabblad dan ook intikken =BevatFormule2, dan zal het resultaat altijd WAAR zijn!

Deze naam is in het voorbeeld gebruikt om via Voorwaardelijke opmaak zichtbaar te maken waar ergens in kolom C een formule wordt gebruikt.

NB de naam BevatFormule2 kan ook voor de gehele werkmap werkend worden gemaakt: wijzig de verwijzing naar cel D3 in INDIRECT(“RK”;Onwaar)

Overzicht

CelLezenOm de werking van Cel.Lezen verder te verduidelijken is in het Voorbeeldbestand het tabblad Ovz opgenomen. Hierbij is gebruik gemaakt van de naam CelInfo met als formule
=CEL.LEZEN($C$2;B5)
LET OP de eerste parameter is absoluut, de tweede relatief: waar de formule ook gebruikt wordt, het InfoType is altijd de inhoud van cel C2

Door in cel C2 een nummer tussen 1 en 66 in te voeren wordt in C5:C9 de informatie over de overeenkomende cellen in kolom B weergegeven.

Met behulp van InfoTypeNummer=1 zien we de (absolute) celverwijzingen etc.

Herberekenen

Eén van de nadelen van dit soort implementatie van functies is, dat Excel het resultaat niet altijd bijwerkt.
Excel probeert namelijk zo efficiënt met de rekentijd om te gaan en beoordeelt na het aanpassen van een cel welke andere cellen allemaal opnieuw berekend moeten worden.
CelLezenMaar, wanneer je bijvoorbeeld de achtergrondkleur van een cel wijzigt, zal Excel geen enkele cel spontaan gaan herberekenen. En helaas ook niet wanneer we Excel daartoe dwingen door op F9 (Handmatig herberekenen) te drukken.

Wijzig in het tabblad Kleur van het Voorbeeldbestand bijvoorbeeld de achtergrondkleur van cel B3 in grijs. Wat gebeurt er met C3? En als je op F9 drukt? Niets dus.
Het enige wat helpt, is om cel C3 te selecteren, op F2 te drukken (inhoud cel wijzigen) en dan op Enter.
Voor de code-kolom is als Naam de volgende formule gebruikt:
=CEL.LEZEN(38;B3).

Maar voor alles (?) is een oplossing. We moeten gewoon zorgen dat onze formule een element bevat, dat bij een herberekening altijd een nieuwe waarde krijgt. Een voorbeeld van zoiets is de functie Nu(), die bij iedere wijziging in Excel opnieuw wordt bepaald.
Dus nogmaals een Naam vastleggen (CelKleur2):
=CEL.LEZEN(38+Nu()*0;B3).
Wijzig één van de kleuren in kolom E en druk op F9: et voilà!

NB1 de functie Nu() levert een getal op; dat zou de inhoud van de eerste parameter veranderen, dat is natuurlijk ook weer niet de bedoeling. Vandaar het vermenigvuldigen met 0.

NB2 de truc om te zorgen dat Excel wat ‘actiever’ is bij het herberekenen heb ik ooit op de site van Jan Karel Pieterse gevonden: jkp-ads.com.

Waarschuwingen

Waarschuwing1: aangezien Cel.Lezen voor Excel een soort macro is, moet een bestand, dat gebruik maakt van deze formule, opgeslagen worden met de extensie xlsm.

Waarschuwing2: bij het googelen kwam ik enkele keren tegen, dat het kopieren van cellen met verwijzingen naar Cel.Lezen naar andere tabbladen, kan zorgen voor een crash van Excel. OEPS!

Waarschuwing3: of Microsoft de  Excel4Macro’s in een volgende versie nog zal blijven ondersteunen is maar de vraag.

Indirecte verwijzing

Excel, , ,

shakespeareDirect or Indirect, that’s the question!

Heb je je dit ook al ooit afgevraagd?
Als ik deze vraag beperk tot het gebruik van Excel, dan denk ik dat het antwoord meestal ontkennend zal zijn.
Het gebruik van de functie INDIRECT is niet wijdverbreid; maar misschien is de uitdrukking Onbekend maakt onbemind wel van toepassing.

Ik kreeg vorige week een vraag van iemand van een bridgeclub, die de wekelijkse resultaten automatisch wilde laten verwerken in een totaal-sheet. Ze dacht, dat daarbij VBA/programmeren aan te pas zou moeten komen, maar hier bleek de functie INDIRECT uitkomst te bieden.

Directe en indirecte verwijzingen

Indirect1Laten we eerst eens even kijken wat dat Indirect nu precies is, voordat we in een (simpel) voorbeeld enkele mogelijkheden gaan onderzoeken.

In het Voorbeeldbestand heb ik in het tabblad Vb1a een maandoverzicht met bedragen staan (kolommen B en C).
Daarnaast staat een compacter overzicht waar maar enkele maanden zijn opgenomen (kolom E). Wanneer we de betreffende bedragen daarnaast willen hebben, dan kunnen we in cel F4 intikken =C3.
Dit wordt een verwijzing genoemd en in dit geval een directe verwijzing: we nemen direct de waarde uit C3 over in cel F4.
In cel G4 zie je hetzelfde resultaat, maar daar staat als formule een indirecte verwijzing
=INDIRECT(“C3”)

Behalve, dat dit nou geen echt zinvol gebruik van deze functie is, kan ik daarmee wel de kracht van de functie uitleggen: Indirect vertaalt inwendig een tekst (let op de aanhalingstekens!) naar een directe celverwijzing.
Uiteraard moet die tekst dan wel een geldige verwijzing voorstellen (dus bijvoorbeeld geen spatie tussen de kolomletter en het rijnummer).

Maar de kracht van deze vertaalmogelijkheid komt pas tot uiting in cel G5. Hier moet het bedrag (in kolom C) van de 3e maand (cel E5) opgehaald worden:
=INDIRECT(“C”&E5)

Door het &-teken worden twee teksten gekoppeld: de letter C en de inhoud van cel E5 (zie het artikel Teksten samenvoegen).

Ai, jammer: dit klopt niet helemaal. Op deze manier wordt een verwijzing naar cel C3 gemaakt; maar maand 3 staat in rij 5, maand 4 in 6 etc. Dus altijd 2 lager dan het maandnummer aangeeft.
Oplossing:
=INDIRECT(“C”&(E5+2))
De letter C wordt aan de inhoud van cel E5 plus 2 gekoppeld; in dit geval levert dat als verwijzing C5 op.

NB de haakjes rond de optelling staan er alleen voor de duidelijkheid; zonder haakjes zou Excel hetzelfde resultaat opleveren.

De formule in cel G5 kan nu naar beneden gekopieerd worden naar G6 t/m G8 en in die cellen verschijnt het bedrag van de betreffende maanden.
Wanneer in de cellen E5 t/m E8 andere maandnummers worden ingevoerd, zullen de overeenkomende bedragen direct in kolom G getoond worden.
Het overzicht is daardoor flexibeler/dynamischer geworden, vergeleken met directe verwijzingen.

NB overal waar je in Excel een directe verwijzing zet (of dit in een formule direct achter het =-teken is, als parameter in een formule, in een rekenkundige bewerking) kan ook een indirecte verwijzing worden geplaatst.

Verwijzingstype

Indirect2In het tabblad Vb2 van het Voorbeeldbestand staat een zelfde soort maandoverzicht, maar nu ‘horizontaal’.

Nu wordt het opzoeken van het bedrag, dat bij de 3e maand (cel B8) hoort, wat lastiger:
alle bedragen staan in rij 3; dus het laatste gedeelte van de verwijzing is altijd gelijk aan 3. Nu de juiste kolom vinden: de 3e maand staat in de 5e kolom etc.; dus ook hier moeten we 2 optellen bij het maandnummer. Maar dat is niet voldoende: het kolomnummer moet omgezet worden naar een kolomletter. In Excel kan dat met de functie TEKEN.
Als we dan ook nog weten, dat de code 65 de letter A voorstelt, 66 de B etc. dan zou de formule in cel D8 te doorgronden moeten zijn:
=INDIRECT(TEKEN(64+B8+2)&”3″)

Het omzetten van een kolomnummer naar een letter is echter niet persé nodig: de functie Indirect kent nog een optionele parameter (die mag maar hoeft dus niet ingevuld te worden). Via deze parameter kunnen we aangeven, dat de verwijzing al dan niet de vorm RxKy (rij x, kolom y) heeft.
Het bedrag van de 3e maand (B8 bevat de waarde 3) ophalen kan dan ook als volgt:
=INDIRECT(“R3K”&(B8+2);ONWAAR)

De parameter ONWAAR geeft aan, dat het verwijzingstype de vorm RxKy heeft; wanneer als parameter WAAR wordt meegegeven (of wanneer de parameter wordt weggelaten zoals hiervoor) dan heeft de verwijzing de standaardvorm A1 (kolomletter, gevolgd door rijnummer).

Verwijzing naar een andere werkmap of werkblad

Vaak worden samenvattingsoverzichten op een ander werkblad (of in een andere werkmap) geplaatst; niet bij de basisgegevens zelf.

Indirect3In het Voorbeeldbestand, tabblad VB3, willen we de gegevens van het tabblad Vb1a overnemen.
In cel C3 staat een directe verwijzing naar cel C3 van het tabblad Vb1a; de naam van het tabblad (Vb1a) wordt gescheiden van de celnaam door een uitroepteken.
De indirecte verwijzing in cel D4 wordt daarom:
=INDIRECT(“Vb1a!C”&(B4+2))

En ook deze formule kan nu zonder verdere aanpassingen naar beneden gekopieerd worden.

LET OP wanneer de naam van een tabblad uit meerdere woorden bestaat, gescheiden door een spatie of een ander leesteken, niet zijnde een underscore (_), dan moeten rond deze naam rechte, enkele aanhalingstekens (‘) geplaatst worden.

Wanneer de brongegevens in een andere werkmap staan, maak dan eerst een directe verwijzing naar een cel in dat bronbestand (door in een cel het =-teken te plaatsen en dan met de muis ergens in het bronbestand te klikken). Binnen de functie Indirect zal dan eenzelfde verwijzing als tekst moeten worden opgebouwd.

Nog een voorbeeld

In het Voorbeeldbestand is een tabblad Vb1b opgenomen, dat qua opzet gelijk is aan het tabblad Vb1a.

Indirect4Het resultaat in de kolommen G en H van het tabblad Vb3 is via de functie Indirect tot stand gekomen.
Bijvoorbeeld in cel G3 staat de formule:
=INDIRECT(“‘”&G$2&”‘!C”&($F3+2))

Let op de plaats van de enkele aanhalingstekens en de gedeeltelijk absolute en gedeeltelijk relatieve verwijzingen naar G$2 en $F3.

De formule in G3 is naar rechts en naar beneden gekopieerd.

Voetbal en gegevensvalidatie

Excel, , , , , , ,

Hoewel de titel van dit artikel misschien anders doet vermoeden (het gaat dus NIET over het checken van persoonsbewijzen bij een voetbalwedstrijd), gaan we het hebben over het valideren van de invoer van gegevens in een cel in Excel.

De aanleiding hiervoor was een discussie op een internet-forum over “Lege velden in keuzelijst met invoervak“.
Al snel kwam daar de opmerking langs, dat het onderliggende probleem waarschijnlijk makkelijker en beter met gegevensvalidatie kon worden opgelost; dan was er ook geen VBA nodig.
Het probleem in het kort: de toegestane invoer in een cel is afhankelijk van de keuze, die in een andere cel is gemaakt.

voetbalcompetitieOm de daar geschetste oplossing in de praktijk te kunnen laten zien, heb ik de stand van de twee Nederlandse profvoetbalcompetities genomen (Bron: www.voetbaluitslagen.nl).
NB Het valt me nu pas op hoe goed de stad Eindhoven het doet!

Stand voetbalcompetities

Dus eerst maar eens een voorbeeld opzetten (zie Voorbeeldbestand):voetbalcompetitie

  1. eerst de standen van de twee competities onder elkaar gezet, gesorteerd op clubnaam, waarbij de kolom met de punten (Pnt.) voor ons het belangrijkste is
  2. dan een schema opzetten van de beste 3 per competitie (zie hierboven).
    Daarbij maak ik gebruik van de functie Grootste:
    =GROOTSTE(H15:H32;1)
    Dit levert het maximum op van de Eredivisiepunten; in het Voorbeeldbestand, tabblad CompOvz1, heb ik deze reeks een naam gegeven, ErePnt, zodat de bedoeling van de formule duidelijker is.
    Door de 1 te veranderen in 2 vinden we het één na hoogste puntentotaal enz.
    Via Index en Vergelijken (zie het artikel over Verticaal zoeken) vinden we de bijbehorende clubs.
    NB zoals uit de stand hierboven mag blijken gaat het bij een gelijk aantal punten niet altijd goed; er wordt geen rekening gehouden met doelsaldo.
  3. dan nog wat Voorwaardelijke opmaak ‘strooien’ over het overzicht (zie mijn vorige blog) en we krijgen wat beter inzicht in de voetbal-verhoudingen in Nederland.
    NB we zien nu wel dat AZ en Feyenoord een gelijk aantal punten hebben
  4. voetbalcompetitiede verhoudingen tussen de clubs kunnen ook grafisch worden weergegeven.
    Op de x-as staan de gegevens van de kolommen B en C; Excel zorgt zelf voor een duidelijke lay-out wat betreft de indeling van de twee competities.
    De titel is dynamisch, wat in dit geval inhoudt dat deze verandert wanneer de datum in cel B2 wordt gewijzigd:
    * maak een willekeurig titel aan,
    * kvoetbalcompetitielik ergens in de titel,
    * kies de formulebalk en tik een verwijzing naar de cel met de gewenste titel-tekst in, inclusief de naam van het tabblad en een !
    In het voorbeeld staat de titel-tekst in cel N27, waar met behulp van de functie Teken een scheiding tussen de twee elementen van de titel is gemaakt.

Combinatie van grafieken

De grafiek kan wel wat duidelijker: een betere scheiding tussen de twee competities, wie staan bovenaan, hoe staat mijn favoriete club er voor?voetbalcompetitie

  1. in het Voorbeeldbestand, op het tabblad CompOvz2, is het onderscheid tussen de competities geregeld door de behaalde punten in 2 verschillende kolommen te plaatsen, een extra kolomgrafiek toe te voegen, de overlap van de grafieken op 100% te zetten en de kleuren aan te passen
  2. daarna is er een label toegevoegd aan de nummers 1 tot en met 3: achter iedere club (in kolom F) staat een formule, die kijkt of de club bij de eerste 3 hoort:
    =ALS(D15=Ere_1;1;ALS(D15=Ere_2;2;ALS(D15=Ere_3;3;””)))
    Ere_1 is de naam van de cel, die het aantal punten van de aanvoerder van de ranglijst bevat etc.
    Het bereik F15:F52 is als een nieuwe grafiek toegevoegd; bij Opmaak is gekozen voor Geen opvulling maar wel zijn Gegevenslabels toegevoegd
  3. om onze favoriete club er te laten uitspringen, voegen we een nieuwe grafiek toe, die alleen de punten van deze club bevat; zie kolom G:
    =ALS(C15=Voorkeur;D15+E15;””)
    Geef deze grafiek een afwijkende kleur.

Gegevensvalidatie

In het vorige voorbeeld staat de naam van de favoriete club in cel J4, die de naam Voorkeur heeft gekregen.
De (groene) kolom in de grafiek bij de favoriet wordt natuurlijk alleen maar zichtbaar, wanneer die cel een bestaande clubnaam bevat. In Excel dwingen we dat af via Gegevensvalidatie.

  1. voetbalcompetitieselecteer cel J4 in het tabblad CompOvz2 van het Voorbeeldbestand
  2. kies binnen de menutab Gegevens in het blok Hulpmiddelen voor gegevens de optie Gegevensvalidatie
  3. er opent zich een keuzescherm:
    voetbalcompetitie
  4. bij Toestaan kiezen we de optie Lijst
  5. en als Bron geven we het bereik op, waar alle clubnamen staan; in het voorbeeld heeft dat bereik de naam Teams.
    LET OP: denk aan het =-teken

voetbalcompetitieSelecteer J4 en tik een clubnaam in; komt deze niet (precies) in de clublijst voor dan krijgt u een foutmelding. Het is daarom handiger om het ‘vinkje’ achter de cel te gebruiken, zodat u de keuzelijst kunt gebruiken.

‘Meervoudige’ keuzelijst

In het vorige voorbeeld bestaat de lijst, waaruit een favoriete club gekozen kan worden, uit 38 teams.
Via scrollen in de zijbalk van de keuzelijst is de gewenste club nog vrij snel te vinden. Bij langere lijsten is dat vaak onhandig.

Als voorbeeld zou het in dit geval makkelijker zijn om eerst een competitie te kiezen (Eredivisie of Jupiler) en daarna pas een club uit de gekozen competitie.voetbalcompetitie
Dat is wat ik bedoel met ‘meervoudige’ keuzelijst: de inhoud van de tweede wordt bepaald door de keuze in de eerste.

Op de volgende manier is dit in te regelen (zie het Voorbeeldbestand, tabblad CompOvz3):

  1. voor de competitie-keuze voegen we weer een gegevensvalidatie toe (aan cel J3): kies binnen de menutab Gegevens in het blok Hulpmiddelen voor gegevens de optie Gegevensvalidatie
  2. bij Toestaan kiezen we opnieuw de optie Lijst
  3. voetbalcompetitiebij Bron wordt meestal een celbereik van toegestane elementen opgegeven.
    In dit geval hebben we maar 2 mogelijkheden: Eredivisie of Jupiler. De namen van de betreffende clubs liggen vast in de celbereiken EreTeams, respectievelijk JupTeams. Deze 2 namen geven we als bron van de lijst op, gescheiden door een ; (punt-komma).
  4. aan cel J4 geven we weer een gegevensvalidatie.
    Bij Toestaan kiezen we weer Lijst; bij Bron zijn we geneigd om de cel J3 mee te geven, maar helaas: Excel zal dan alleen de letterlijke tekst uit die cel aan de keuzelijst meegeven (dus EreTeams of JupTeams).
    Nee, bij Bron moeten we invullen
    =INDIRECT(J3)
    De functie Indirect zorgt er voor, dat niet de inhoud van J3 zelf wordt gebruikt, maar de inhoud van het bereik waar J3 naar verwijst (EreTeams of JupTeams dus).