Maandelijks archief: april 2015

Indirecte verwijzing

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.


Wil je op de hoogte gebracht worden, wanneer er weer een nieuw item aan Tips & trucs is toegevoegd:

Dynamische grafieken

Niet iedereen zal meteen weten, wat hiermee bedoeld wordt.
De term dynamische grafiek wordt in de Excel-omgeving gebruikt, wanneer een grafiek zich direct aanpast aan wijzigingen in de bron-spreadsheet zonder dat de gebruikte bereiken voor de x- en y-waarden hoeven worden aangepast.
Het bekendste voorbeeld: er komen omzet-gegevens van een nieuwe maand beschikbaar; deze worden onderaan een reeks toegevoegd. Dan zou het fijn zijn als de bijbehorende grafiek dit automatisch zou overnemen.

Eenvoudige oplossing

Op allerlei manieren is er een bepaalde vorm van dynamiek te implementeren. DynGraf1

Laten we eerst een eenvoudige implementatie bekijken.
In het Voorbeeldbestand op het tabblad DynGraf1 zijn bedragen (kolom C) voor iedere maand (kolom A) opgenomen; in kolom B hebben de maanden een volgnummer gekregen.
Stel dat we alleen de cijfers tot en met juni willen weergeven:

  1.  cel D1 krijgt dan de waarde 6
  2. in D4 staat de formule: =ALS(B4<=$D$1;A4;””)
    Dus als het volgnummer kleiner of gelijk is aan de waarde in cel D1, dan wordt de waarde uit A4 (de maand) overgenomen en anders komt er niets (er staan alleen maar 2 aanhalingstekens)
  3. idem voor cel E4: =ALS(B4<=$D$1;C4;””)
  4. D4 en E4 kopiëren we naar beneden
  5. dan maken we een grafiek, die voor de x-waarden (horizontale as) “kijkt” naar de cellen D4 t/m D15 en voor de y-waarden naar E4 t/m E15
  6. de titel van de grafiek maken we dynamisch door een verwijzing te maken naar cel B17; dit gaat als volgt:
    * geef de grafiek een willekeurige titel
    * klik in de titel, dan in de formulebalk en voer daar het =-teken in, gevolgd door een verwijzing naar de gewenste cel: DynGraf2 en druk op Enter
  7. in cel B17 zorgen we dat de tekst aangepast wordt voor de maandkeuze:
    =”Bedragen t/m “&VERT.ZOEKEN($D$1;B4:D15;3;ONWAAR)
    Hier worden 2 teksten gekoppeld (zie het artikel Teksten samenvoegen): de “harde” tekst Bedragen t/m en de betreffende maand, die met de functie verticaal zoeken wordt opgehaald (zoek in de eerste kolom van het blok B4:D15 naar de waarde uit cel D1; geef de corresponderende waarde uit de 3e kolom van dat blok terug)

Een groot nadeel van de bovenstaande methode is, dat er voor de ontbrekende maanden een lege ruimte in de grafiek overblijft.
Dat kunnen we voorkomen door de grafiek dynamisch te maken met behulp van de functie Verschuiving.

Verschuiving

Voordat we deze functie voor grafieken gaan gebruiken eerst wat uitleg:
overal waar je in Excel een verwijzing naar een cel of een groep cellen (een cel-bereik of -range) gebruikt kun je ook VERSCHUIVING gebruiken.
De verwijzing zal dan niet naar de opgegeven cel zijn, maar naar 1 of meerdere cellen verschoven. DynGraf3Een voorbeeld:

  1. kies in het Voorbeeldbestand het tabblad DynGraf1
  2.  in cel H4 zetten we de formule =VERSCHUIVING(C4;2;0)
  3. het resultaat is 120; de functie geeft de inhoud van de cel 2 rijen onder en 0 kolommen rechts van C4 (dus C6)
  4. zo geeft de formule =VERSCHUIVING(C4;1;2) de waarde 110 (namelijk de inhoud van cel E5)
  5.  =VERSCHUIVING(E3;3;-2) werkt ook!! Resultaat is 120; de inhoud van cel C6.
  6. =VERSCHUIVING(D3;D1;0) en =VERSCHUIVING(D3;D1;1) leveren de maand en het bedrag afhankelijk van de waarde in cel D1
  7. Maar deze functie kent nog meer parameters (optioneel; zijn dus niet verplicht). We kunnen ook nog aangeven hoeveel rijen en kolommen het bereik vanuit de nieuwe positie moet hebben:
    =VERSCHUIVING(D3;1;1;3;1) geeft als resultaat de inhoud van de cellen E4:E6; namelijk, vanuit cel D3 startend, 1 rij naar beneden en 1 kolom naar rechts (dus E4) nemen we een bereik van 3 rijen hoog en 1 kolom breed.
    Wanneer we deze formule intikken in Excel, levert dit het resultaat 100; Excel laat de waarde van de eerste cel (E4 dus) zien.
    NB wil je weten wat Excel intern als resultaat van de functie vasthoudt:
    * klik in de formulebalk achter de betreffende functie en druk op F9
    * in plaats van de formule komt nu het resultaat: DynGraf4; dus wel degelijk de inhoud van drie cellen E4, E5 en E6
    * druk daarna niet op Enter maar op Esc
  8. maar als het resultaat van de vorige functie de inhoud van 3 cellen is, dan kunnen we natuurlijk ook nog het volgende doen:
    =SOM(VERSCHUIVING(D3;1;1;D1;1))
    Het resultaat is de som van de bedragen van de maanden, waarbij cel D1 bepaalt hoeveel maanden er worden meegenomen.

 Complexere (maar mooiere) oplossing

DynGraf5Nu we met Verschuiving overweg kunnen, kunnen we die voor onze dynamische grafiek gebruiken:

  1. kies opnieuw in het Voorbeeldbestand het tabblad DynGraf1
  2. scrol naar beneden zodat rij 70 in beeld is
  3. hier staat nog een grafiek die afhankelijk is van de waarde in cel D1; maar deze heeft geen “lege” maanden
  4. voordat je een dergelijke grafiek kunt maken, moeten binnen Excel enkele nieuwe namen gedefinieerd worden, die voor de x- en y-waarden worden gebruikt:
    * ga naar de menu-tab Formules
    * kies binnen het blok Gedefinieerde namen de optie Namen beheren
    * kies de optie NieuwDynGraf6, bij Naam tikken we in Maand en Verwijst naar wordt =verschuiving($A$49;0;0;$D$1;1)
    * klik OK; in het overzicht van namen is onze nieuwe naam Maand opgenomen, waarbij Excel aan de cellen A49 en D1 automatisch de naam van het betreffende tabblad en een ! heeft toegevoegd.
    LET OP maak verwijzingen naar cellen ALTIJD absoluut (met de $-tekens) anders zal de naam relatief zijn t.o.v. de cel, die op het moment van aanmaken geselecteerd is
  5. op dezelfde manier maken we een nieuwe naam Bedrag: =verschuiving($B$49;0;0;$D$1;1) of
    =verschuiving($A$49;0;1;$D$1;1)
  6. bij het maken van de grafiek geven we als Aslabelbereik =DynGraf1!Maand op en als Reekswaarden de naam =DynGraf1!Bedrag.
    LET OP begin met het =-teken en laat de hierboven gedefinieerde naam voorafgaan door de naam van het tabblad en een !
  7. Verander de waarde in D1 en bekijk het resultaat

NB wat gebeurt er als je in D1 bijvoorbeeld 15 intikt?

NB2 zet in D1 de formule =AANTAL(B49:B60) en maak cel B60 leeg

Nog complexere (en nog mooiere) oplossingen

DynGraf7In het Voorbeeldbestand heb ik nog 2 alternatieve oplossingen opgenomen.

In het eerste voorbeeld kun je ook de beginmaand opnemen, zodat de grafiek niet altijd met januari hoeft te beginnen, maar je bijvoorbeeld alleen de resultaten van het lopende kwartaal kunt laten zien.

Open het tabblad DynGraf2 en bekijk de opbouw van de sheet en de grafiek.
In dit tabblad worden de namen Maand2, Bedrag2 en Totaal gebruikt. De laatste is toegewezen aan het tekstvak, dat over de grafiek ligt.

DynGraf8In het laatste voorbeeld (tabblad DynGraf3) kunnen de grenzen met behulp van zogenaamde Schuifbalken worden ingesteld.

Om deze in een sheet te plaatsen kies je in de menu-tab Ontwikkelaars in het blok Besturingselementen de optie Invoegen. Klik dan binnen het blok Formulierbesturingselementen de optie Schuifbalk; geef met de cursor in de sheet aan waar de balk moet komen. DynGraf9Daarna kan na rechtsklikken op de schuifbalk het besturingselement opgemaakt worden.

Zoals te zien is kunnen hier de minimale en maximale waarden worden vastgelegd.  Van groot belang is de optie Koppeling met cel.


Wil je op de hoogte gebracht worden, wanneer er weer een nieuw item aan Tips & trucs is toegevoegd: