INDIRECT
De INDIRECT-functie retourneert een verwijzing naar een bereik.
Je kunt deze functie gebruiken om een verwijzing te maken die niet verandert als rij of kolommen in het werkblad worden ingevoegd.
Of gebruik het om een verwijzing te maken uit letters en cijfers in andere cellen.
Hoe de INDIRECT functie werkt
De INDIRECT-functie is handig wanneer je een waarde wilt retourneren op basis van een tekst die je in een andere cel hebt staan.
Selecteer bijvoorbeeld een bereiknaam in een vervolgkeuzelijst en bereken het totale bedrag voor het geselecteerde bereik.
Kies in dit screenshot Heren of Dames in cel F2 en het totaal verschijnt in cel F3:
Je kunt dit eenvoudig samenstellen met een aantal functies in Excel:
Bereik van cellen een naam geven
De eerste stap is om deze gegevens zoals in het voorbeeld in te typen zodat je het voorbeeld na kan maken. Kolom A heb ik er als voorbeeld bijgezet om gemakkelijk te kunnen zien wat er gebeurt, maar heb je in wezen niet nodig.
Kolom B en C en hun waarden kun je zo overnemen.
Vervolgens kun je een bereik een naam geven zodat je dat straks kunt gebruiken in je INDIRECT functie. Selecteer de eerste twee cellen in kolom C en klik met de rechtermuis:
Kies vervolgens voor “Naam definiëren”. Vul als naam in Heren en klik op OK.
Doe hetzelfde voor cel C4 en C5 en kies nu voor de naam Dames.
Keuzelijst maken met gegevensvalidatie
Nu gaan we cel F2 opmaken zodat je daar kunt kiezen voor ofwel Heren, of Dames. Dit doen we met behulp van gegevensvalidatie.
Klik in cel F2 en ga in het lint naar “Gegevens”. Klik vervolgens op “Gegevensvalidatie” en vul onderstaande gegevens in:
Maak dus een Lijst met daarin de volgende twee opties:
- Heren
- Dames
Dit doe je door onder Bron Heren;Dames te typen, de “;” scheidt de twee opties van elkaar.
Nadat je op Ok klikt kun je nu in cel F2 gebruik maken van een menutje met de twee opties erin.
Indirect formule
De laatste stap is om de indirect formule van Excel toe te passen. Typ in cel F3:
=SOM(INDIRECT(F2))
Wat je hier hebt gedaan is je wilt de som optellen, van de getallen die staan in het bereik dat je eerder “Heren” of “Dames” hebt genoemd.
De INDIRECT functie kijkt namelijk naar wat er in cel F2 staat en neemt dat als zijn optelbereik.
INDIRECT functie argumenten
=INDIRECT(verw_tekst;[a1])
- Verw_tekst: Een verwijzing naar een cel die een A1-verwijzing, een R1K1-verwijzing, een naam gedefinieerd als een verwijzing of een verwijzing naar een cel als een tekenreeks bevat.
- A1: Een waarde waarbij je aangeeft of de A1 waarde of de R1K1 waarde gebruikt dient te worden.
WAAR:
ONWAAR:
Wanneer gebruik je INDIRECT?
- In de meeste gevallen werkt een celverwijzing of combinatie van tekstreeks en celverwijzing het beste met de INDIRECT-functie. Het bovenstaande voorbeeld Budget / Werkelijk toont deze techniek.
- Ik vind het ook nog wel handig als je een Excel maakt voor een andere gebruiker waarbij je de mogelijkheid kunt geven aan die gebruiker om te bepalen welke cel wordt berekend door bijvoorbeeld één invulveld te geven. Zij hoeven dan de formule zelf niet aan te passen
- Soms kun je INDIRECT ook gebruiken wanneer er vaak wijzigingen gemaakt worden in de kolommen. Dus vaak extra kolommen toegevoegd of verwijderd. Met INDIRECT blijft de verwijzing naar kolom X ook altijd kolom X, terwijl een normale verwijzing mee verandert wanneer het aantal kolommen wijzigt.
Tekst string – adres wordt in de formule getypt (niet flexibel)
- A1-stijl: = INDIRECT (“A4”) of = INDIRECT (“Demo! B3”) of = INDIRECT (“Budget”)
- R1C1-stijl: = INDIRECT (“R4C1”, ONWAAR)
Celverwijzing – verwijst naar een cel die een tekstreeks bevat
- A1-stijl: = INDIRECT (C6)
- R1C1-stijl: = INDIRECT (C8, ONWAAR)
Gecombineerd – Tekststring en celverwijzing
- A1-stijl: = SOM (INDIRECT (“A4: A” & C12))
Vergrendel een celverwijzing
De INDIRECT-functie kan een specifieke cel in een formule “vergrendelen”.
Als vervolgens rijen of kolommen boven of links van die cel worden ingevoegd of verwijderd, verandert de verwijzing niet.
Zonder INDIRECT zou de referentie automatisch worden aangepast.
Om te zien hoe dit werkt, volg je deze stappen om twee formules te maken – een met normale celverwijzingen en een met een INDIRECTE functie.
Typ in cellen C2: C7 een lijst met getallen
Kopieer de lijst naar E2: E7
Typ in cel C8 een SOM-formule: = SOM (C2: C7)
Typ deze formule in cel E8: = SOM (INDIRECT (“E2”): E7)
Om het verschil tussen de formules te zien, voeg je nu een lege rij in boven rij 2 en voer je 100 in voor cellen C2 en E2:
- Het totale bedrag verandert in kolom E, omdat de startcel is vergrendeld op E2
- Het totale bedrag verandert NIET in kolom C. De startcel verschuift naar cel C3 door het toevoegen van de rij
Flexibele verwijzing maken met INDIRECT
Een andere handige toepassing is om een gebruiker een flexibele verwijzing te bieden.
Zij kunnen dan bijvoorbeeld in een cel een waarde intypen om aan te geven welke cel zij zouden willen aanroepen met de formule waarin INDIRECT wordt gebruikt.
Als je een werkblad zoals onderstaande voorbeeld hebt:
Je vult vervolgens ergens de volgende formule in:
=INDIRECT(A2)
Dan verwijs je in eerste instantie naar cel A2. Deze verwijst vervolgens weer naar B2, hierdoor krijg je als antwoord 2. Je kunt nu kolom A gebruiken om aan te sturen waar de INDIRECT formule naar verwijst.
Verwijs naar een benoemd bereik
Naast celverwijzingen kun je verwijzen naar benoemde bereiken in een INDIRECT-formule. In het eerste voorbeeld in dit artikel heb ik de stappen laten zien om hier gebruik van te maken.
Dit is een van de meest voorkomende toepassingen van de INDIRECT functie.
Opmerking
- In Excel online worden externe verwijzingen niet ondersteund.
- De foutmelding #VERW! wordt weergegeven als er meer dan 1.048.576 rijen of 16.384 kolommen worden geselecteerd.