LET OP: na het downloaden de extensie van het bestand wijzigen in xlsb
De Excel-functie Indirect heb ik de afgelopen tijd weer in menig artikel en project gebruikt.
In de praktijk blijkt het lang niet voor iedereen (direct) helder wat de functie doet en waar die dus voor ingezet kan worden.
Aanleiding voor een artikel; inclusief wat VBA-programmering om het voorbeeld uit het vorige artikel gebruikersvriendelijker te maken.
In 2015 heb ik al eens over deze functie geschreven. Deze keer kiezen we een andere invalshoek voor de uitleg en komen andere facetten van de functie aan bod. Zoals het spreekwoord luidt: dubbel genaaid houdt beter!
Indirect (versie 1)
Op het tabblad Cel van het Voorbeeldbestand is cel B2 gevuld met een tekst die aangeeft welke dag het vandaag is:
=”Vandaag is het “&TEKST(VANDAAG();”d-mm-jjjj”)
Cel B4 bevat de tekst B2 en in cel D4 staat de verwijzing naar cel B4:
=B4
Het resultaat in cel D4 is uiteraard de inhoud van cel B4, dus de tekst B2.
In cel D5 staat de formule =INDIRECT(B4)
Het resultaat is geen directe verwijzing naar cel B4, maar naar de inhoud van cel B4, dus naar cel B2. Een Indirecte verwijzing dus, vandaar de naam van de functie.
Simpel toch!
Nog een (iets ‘intelligenter’) voorbeeld:
in kolom B staan 5 namen. In cel E11 staat een indirecte verwijzing naar cel B12, waardoor de derde naam als resultaat wordt weergegeven.
De tussenstap (cel E10) is enigszins dynamisch gemaakt:
=”B”&D11+9
Dus aan de tekst B wordt het resultaat van de optelling van de inhoud van cel D11 en het getal 9, gekoppeld.
Wijzigen we nu cel D11 dan zal ook het resultaat in cel E11 veranderen.
Maar echt fool-proof is deze constructie niet: voeg maar eens een regel in boven regel 10.
In cel F10 is het statische getal 9 vervangen door het dynamische RIJ(B10)-1.
NB voeg je vóór kolom B een kolom in, dan zal deze formule ook niet meer goed werken. Wil je dit voorkomen dan moet de tekst B in de formule ook dynamisch gemaakt worden.
Indirect (versie 2)
De tussenstap in het vorige voorbeeld (in cel E10 wordt een verwijzing naar een andere cel geconstrueerd) kunnen we overslaan: de parameter die aan de functie Indirect wordt meegegeven kan ook een tekst zijn.
In het tabblad Tekst van het Voorbeeldbestand bevat cel D6 de formule =INDIRECT(“B4”). Het resultaat is de inhoud van cel B4.
De functie zorgt nu voor een indirecte verwijzing door een tekst om te zetten naar een bereik/range en daar de inhoud van op te halen.
De tekst moet het A1-formaat hebben, één of twee letters voor de kolom- en een getal voor de rij-aanduiding.
Deze tekst-versie kent nog een andere variant: cel D7 bevat de formule =INDIRECT(“R4K2”;ONWAAR) en geeft hetzelfde resultaat als de formule in D6. Hierbij wordt het zogenaamde R1K1-formaat voor de tekst gebruikt: eerst een getal voor de rij en dan een getal voor de kolom. Wel moet aan de functie een tweede parameter meegegeven worden met de waarde ONWAAR.
NB de tweede parameter is niet verplicht als je het A1-formaat gebruikt. Maar deze mag wel altijd ingevuld worden; heeft de tekst het A1-formaat dan moet deze tweede parameter de waarde WAAR hebben.
LET OP wanneer deze spreadsheet in een andere taal-versie van Excel wordt ingeladen dan kan het zijn dat de formule niet goed werkt. Om het bijvoorbeeld in de Engelstalige versie te laten werken moet de formule in cel D7 gewijzigd worden in =INDIRECT(“R4C2”;ONWAAR)
In de cellen G3 en G11 worden de twee varianten nog eens in een ander voorbeeld gebruikt:
De kracht van de tekst-versie van de Indirect-functie ligt er uiteraard in, dat de tekst op allerlei manieren geconstrueerd kan worden (zoals in de vorige voorbeelden in eenvoudige vorm wordt getoond).
Cel G12 bevat de formule =INDIRECT(TEKST(G9*10+G10;”R0K0″);ONWAAR). Op deze manier verwijzen we ook naar cel F4. Daarbij is gebruik gemaakt van de functie Tekst waarmee we de weergave van een getal kunnen bepalen. Door de rij-waarde te vermenigvuldigen met 10 en daar de kolomwaarde bij op te tellen krijgen we (in dit geval) de waarde 46. Met de notatie-aanduiding R0K0 geven we aan dat het laatste cijfer van het getal 46 achter de K moet komen en de overige cijfers tussen de R en de K .
Dit gaat echter fout, als we een verwijzing maken naar een kolomwaarde groter dan 9 (probeer maar eens door in cel G10 de waarde 10 in te voeren).
In cel G13 is dit opgelost: =INDIRECT(TEKST(G9*100+G10;”R0K00″);ONWAAR)
Het gebruik van een harde tekst (dus geen samengestelde tekst zoals hiervoor) als parameter kan bijvoorbeeld handig zijn om te testen of een bepaalde cel niet is verplaatst (al dan niet door het invoegen of verwijderen van rijen of kolommen).
In cel G15 wordt getest of cel F17 wel of niet de waarde Maand bevat.
Zo niet dan krijg je een waarschuwing.
Uiteraard hebben we dat met voorwaardelijke opmaak nog wat duidelijker gemaakt (groen is de standaard-kleur, rood als de inhoud afwijkt van de waarde “Ok”).
Gegevensvalidatie 1
In het Voorbeeldbestand vindt u in het tabblad GegVal1 een ‘systeem’ waarmee we de maximumsnelheid van een vervoermiddel kunnen ophalen uit een tabel. Daarbij is gebruik gemaakt van een zogenaamde afhankelijke Gegevensvalidatie (zie het vorige artikel). De keuzelijst in cel F3 is afhankelijk van de keuze in cel F2.
In cel F4 wordt de maximale snelheid van het gekozen merk opgehaald met de functie Vert.Zoeken. Een (flexibeler) alternatief staat in cel F5: =INDEX(C9:C15;VERGELIJKEN(F3;B9:B15;0))
Maar dat ophalen kan ook met behulp van de functie Indirect. Zie cel F6:
=INDIRECT(“C”&RIJ(B8)+VERGELIJKEN(F3;B9:B15;0))
NB1 door niet hard te verwijzen naar rij 8, maar de functie RIJ(B8) te gebruiken werkt de formule ook nog als er regels worden toegevoegd of verwijderd.
NB2 als je rijen toevoegt of verwijdert (of kolommen) dan krijg je een VBA-foutmelding. Kies de optie Beëindigen. De reden van deze foutmelding zullen we hierna uitleggen (en wat daar aan te doen!)
Zoals in het vorige artikel aangegeven zou het mooi zijn als het systeem automatisch het Merk leeg maakt als je het type Vervoermiddel wijzigt.
Daarom hebben we aan het tabblad GegVal1 een VBA-routine gekoppeld (klik rechts op het betreffende tabje onderaan en kies de optie Programmacode weergeven):
Iedere keer, dat er iets is gewijzigd in dit tabblad, wordt deze routine automatisch aangeroepen (Worksheet_Change). Welke cel of cellen er zijn gewijzigd wordt door Excel meegegeven in de parameter Target. We willen alleen maar iets doen (namelijk het merk leeg maken) als het type vervoermiddel wijzigt (cel F2). Daarom wordt in de eerste regel van de sub-routine gecontroleerd of Target daar gelijk aan is. Als dat niet zo is, gaat de routine verder met de opdracht na de laatste End If (de subroutine beëindigen dus).
De variabele VervMiddel bevat de inhoud van cel F2, zoals die was toen de routine vorige keer is aangeroepen. Daarom kijken we of die veranderd is. Is de huidige inhoud niet gelijk aan VervMiddel dan wordt cel F3 leeg gemaakt en maken we de variabele VervMiddel gelijk aan de inhoud van F2.
NB1 ik weet het: de routine is niet helemaal netjes geprogrammeerd. Wanneer je de eerste keer cel F2 wijzigt weet het ‘systeem’ helemaal niet wat er vorige keer in F2 stond. Dus F3 zal dan altijd leeg gemaakt worden.
NB2 de declaratie van de variabele VervMiddel gebeurt buiten de routine. Zouden we dat niet doen dan wordt deze variabele bij iedere Change opnieuw gedeclareerd en is dan dus altijd leeg.
NB3 deze Excel-werkmap bevat VBA. Bij het opslaan van het bestand moet je dan kiezen voor de extensie xlsm of xlsb.
NB4 verschuiven de cellen F2 en F3 dan moet de VBA aangepast worden. In cel I2 is daarom een controle met een waarschuwing ingebouwd (voeg maar eens een kolom in vóór kolom B).
Maar wat gebeurt er als je een rij of kolom toevoegt of verwijdert? Uiteraard wordt de routine Worksheet_Change aangeroepen, maar de parameter Target bevat helemaal niets. VBA wil dit ‘niets’ vergelijken met een Range en dat kan niet; vandaar dat we een foutmelding krijgen. Dat moeten we natuurlijk wel oplossen.
Gegevensvalidatie 2
In het tabblad GegVal2 van het Voorbeeldbestand staat een simpelere versie van het vorige voorbeeld. In de VBA-routine zit een belangrijk verschil:
In de eerste regel wordt via de VBA-functie Intersect gekeken wat de overlap tussen Target en Range(“F2”) is. Als er geen overlap is (ook als Target leeg is) dan wordt de rest van de routine overgeslagen. Nu krijgen we die vervelende foutmelding van VBA niet meer.
Indirect en Namen
Bij de voorbeelden met gegevensvalidatie is al gebruik gemaakt van Excel-namen. Overal waar je verijst naar een cel of naar een bereik van cellen kun je ook namen gebruiken, dus ook bij de functie Indirect.
Hiernaast staat een voorbeeld (zie het tabblad Namen in het Voorbeeldbestand).
De cellen C3:C12 hebben de naam Een en de cellen C13:C22 de naam Twee.
In cel F2 kun je (met behulp van gegevensvalidatie) kiezen uit deze twee namen.
De formule =SOM(INDIRECT(F2)) in cel F3 telt die cellen bij elkaar op, die door de naam in cel F2 worden bepaald.
LET OP Deze formule werkt alleen als de gegevens netjes met blokken bij elkaar staan (dus soort Een bij elkaar en ook soort Twee). De functie Indirect kan alleen een bereik van cellen verwerken als die cellen op elkaar aansluiten.
In de cellen F4 t/m F7 staan formules die ook het juiste resultaat opleveren, wanneer de gegevens niet netjes gesorteerd zijn.
Voorbeeld van het R1K1-formaat
In cel I4 van het tabblad R1K1 van het Voorbeeldbestand staat een formule die gegevens uit een tabel ophaalt, wanneer de gewenste rij en kolom zijn opgegeven.
Daaronder staan twee varianten; bij eentje wordt de Excel-functie Adres gebruikt.
In het blokje daaronder kunt u zelf ‘spelen’ met de mogelijkheden van deze Adres-functie.
Unieke gegevens selecteren
Hiernaast ziet u een overzicht van 3 lijstjes met daarop verschillende soorten fruit (zie het tabblad Uniek in het Voorbeeldbestand).
Als we willen weten welke soorten voorkomen zoeken we dus de unieke elementen van dit overzicht. Dat is een behoorlijke uitdaging. Maar ook hier komt de functie Indirect van pas.
In cel F3 staat de formule:
=INDIRECT(
TEKST(
MIN(
ALS(
($B$3:$D$10<>””)*AANTAL.ALS($F$2:F2;$B$3:$D$10)=0);
RIJ($3:$10)*100+KOLOM($B:$D);7^8)
);”R0K00″);
ONWAAR)
&””
Wil je ‘zien’ hoe de formule werkt, gebruik dan in de menutab Formules de optie Formule evalueren.
Aangezien het hier een zogenaamde matrix- of array-formule betreft moet deze worden afgesloten door op Ctrl-Shift-Enter te drukken.
Kopieer de formule nu net zo lang naar beneden totdat een of meer lege cellen het resultaat is.
Indirect en bereik-verwijzing
Nog een laatste voorbeeld van het gebruik van de functie Indirect; nu om een bereik van cellen aan te geven (zie het tabblad Range van het Voorbeeldbestand).
In cel F3 halen we vanuit een maand-overzicht het totaal tot en met een bepaalde maand op. Deze maand kan in cel F2 ingevoerd worden.
NB1 cel F2 heeft een gegevensvalidatie, zodat er een keuzelijst ontstaat.
Ook al lijkt deze toegestane lijst absoluut ($B$3:$B$14), wanneer je een rij toevoegt aan de tabel dan wordt deze lijst ook automatisch uitgebreid.
NB2 in cel F3 wordt voor het vergelijken gemakshalve naar de hele kolom B gekeken; wanneer je een complex en/of groot systeem hebt gebouwd kan dit vertragend werken. Het is beter om alleen maar in de cellen te zoeken die relevant zijn. In cel F4 staat de formule
=SOM(INDIRECT(“C3:C”&VERGELIJKEN(F2;Tabel3[Maand];0)+RIJ(Tabel3[[#Kopteksten];[Maand]])))
Een variant daarop in cel F5:
=SOM(C3:INDIRECT(“C”&VERGELIJKEN(F2;Tabel3[Maand];0)+RIJ(Tabel3[[#Kopteksten];[Maand]])))
In cel I4 wordt het totaal van een groepje te kiezen maanden bepaald.
De gegevensvalidatie van de twee grens-maanden is anders dan bij het vorige voorbeeld.
Hier gebruiken we de functie Indirect om te verwijzen naar de kolom Maand van de Excel-tabel met de naam Tabel3. Zonder Indirect is dit niet mogelijk.
LET OP gebruik aanhalingstekens rond de kolom-aanduiding.
Beste
Welke functie kan ik gebruiken om waarden welke via een query in excel binnengehaald werden te gebruiken om mee te rekenen? Kan de functie indirect hierbij helpen?
Alvast bedankt.
Waarschijnlijk begrijp ik je niet goed, Leen.
Waar gegevens vandaan komen doet er voor Excel niet toe, dus alle Excel-functies zijn bruikbaar.
Als je iets anders bedoelt: probeer de vraag wat te preciseren.
Mvg
Gijs Verbruggen