Download het voorbeeldwerkboek
Deze zelfstudie laat zien hoe u de Excel INDIRECTE Functie in Excel om een celverwijzing van tekst te maken.
INDIRECT Functieoverzicht
De INDIRECT-functie Maakt een celverwijzing van een tekstreeks.
(Let op hoe de formule-invoer verschijnt)
INDIRECT-functie Syntaxis en ingangen:
| 1 | =INDIRECT(ref_tekst,C1) |
ref_text - Een tekenreeks die een celverwijzing of bereikverwijzing vertegenwoordigt. De tekenreeks kan de indeling R1C1 of A1 hebben, of kan een benoemd bereik zijn.
a1 - OPTIONEEL: Geeft aan of de referentie in R1C1- of A1-formaat is. ONWAAR voor R1C1 of TRUE / weggelaten voor A1.
Wat is de INDIRECT-functie?
Met de INDIRECT-functie kunt u een tekststring geven en de computer die string als een werkelijke referentie laten interpreteren. Dit kan worden gebruikt om te verwijzen naar een bereik op hetzelfde blad, een ander blad of zelfs een andere werkmap.
LET OP: De INDIRECT-functie is een van de vluchtige functies. Wanneer u in uw spreadsheet werkt, berekent de computer meestal een formule alleen opnieuw als de invoer hun waarden heeft gewijzigd. Een vluchtige functie herberekent echter elk keer dat u een wijziging aanbrengt in een cel. Voorzichtigheid is geboden om ervoor te zorgen dat u geen grote herberekeningstijd veroorzaakt door overmatig gebruik van vluchtige functies of doordat veel cellen afhankelijk zijn van het resultaat van een vluchtige functie.
Een celverwijzing maken
Stel dat u de waarde van A2 wilt ophalen, maar dat u zeker wilt weten dat uw formule blijft op A2 ongeacht of er nieuwe rijen worden ingevoegd/verwijderd. Je zou een formule kunnen schrijven van
| 1 | =INDIRECT("A2") |
Merk op dat het argument in onze functie de tekstreeks "A2" is en geen celverwijzing. Omdat dit een tekenreeks is, is het ook niet nodig om een absolute referentie zoals $ A $ 2 aan te geven. De tekst zal nooit veranderen, en daarom zal deze formule altijd naar A2 wijzen, ongeacht waar deze naartoe wordt verplaatst.
INDIRECTE rijnummer
U kunt tekstreeksen en waarden uit cellen samenvoegen. In plaats van "A2" te schrijven zoals we eerder deden, kunnen we een numerieke waarde uit cel B2 halen en die in onze formule gebruiken. We zouden een formule uitschrijven zoals:
| 1 | =INDIRECT("A" & B2) |
Het "&"-symbool wordt hier gebruikt om de tekstreeks "A" samen te voegen met de waarde uit cel B2. Dus als de waarde van B2 momenteel 10 was, dan zou onze formule dit lezen als
| 123 | =INDIRECT("A" & 10)=INDIRECT("A10")=A10 |
INDIRECTE kolomwaarde
U kunt ook samenvoegen in de kolomverwijzing. Laten we deze keer zeggen dat we weten dat we een waarde uit rij 10 willen halen, maar dat we willen kunnen veranderen uit welke kolom we moeten halen. We plaatsen de gewenste kolomletter in cel B2. Onze formule zou eruit kunnen zien:
| 1 | =INDIRECT(B2 & "10") |
Als de waarde van B2 "G" is, dan evalueert onze formule als volgt
| 123 | =INDIRECT("G" & 10)=INDIRECT("G10")=G10 |
INDIRECTE r1c1 stijl
In ons vorige voorbeeld moesten we een letter gebruiken om kolomverwijzing aan te geven. Dit komt omdat we gebruikmaakten van wat bekend staat als A1-stijlreferentie. In A1-stijl worden kolommen gegeven door een letter en rijen door cijfers. Absolute referenties worden aangegeven met de "$" voor het item dat we absoluut willen houden.
In r1c1 worden zowel rijen als kolommen gestart met nummer. De absolute verwijzing naar a1 zou worden geschreven als
| 1 | =R1C1 |
U kunt dit lezen als “Rij 1, Kolom 1”. Relatieve verwijzingen worden gegeven door haakjes te gebruiken, maar het nummer geeft de positie aan ten opzichte van cel met formule. Dus als we een formule in cel A10 zouden schrijven en we moeten naar A1 verwijzen, zouden we de formule schrijven
| 1 | =R[-9]C |
U kunt dit lezen als "De cel 9 rijen omhoog, maar in dezelfde kolom.
De reden dat dit nuttig kan zijn, is dat INDIRECT het gebruik van de r1c1-notatie kan ondersteunen. Beschouw het vorige voorbeeld waarin we een waarde uit rij 10 ophaalden, maar de kolom wilden wijzigen. In plaats van een letter te geven, laten we zeggen dat we een getal in cel B2 plaatsen. Onze formule zou er dan als volgt uit kunnen zien:
| 1 | =INDIRECT("R10C" & B2, ONWAAR) |
We hebben de 2 . weggelatennd argumentatie tot nu toe. Als dit argument wordt weggelaten of True, wordt de functie geëvalueerd met behulp van de A1-stijl. Omdat het False is, wordt het geëvalueerd in r1c1. Laten we aannemen dat de waarde van B2 5 is. Onze formule zal dit zo evalueren
| 12 | =INDIRECT("R10C5", ONWAAR)=$E$10 |
INDIRECTE verschillen met A1 vs r1c1
Weet je nog dat we eerder hebben laten zien dat, aangezien de inhoud van deze formule een tekenreeks was, deze nooit is veranderd?
| 1 | =INDIRECT("A2") |
Deze formule kijkt altijd naar cel A2, ongeacht waar u de formule naartoe verplaatst. In r1c1 blijft deze regel niet consistent, aangezien je de relatieve positie kunt aangeven met haakjes. Als u deze formule in cel B2 . plaatst
| 1 | =INDIRECT("RC[-1]") |
Het kijkt naar cel A2 (aangezien kolom A zich links van kolom B bevindt). Als u deze formule naar cel B3 kopieert, blijft de tekst erin hetzelfde, maar de INDIRECT kijkt nu naar cel A3.
INDIRECT met bladnaam
U kunt ook een bladnaam combineren in uw INDIRECTE referenties. Een belangrijke regel om te onthouden is dat u enkele aanhalingstekens rond de namen moet plaatsen en dat u de bladnaam van de celverwijzing moet scheiden met een uitroepteken.
Laten we zeggen dat we deze opstelling hadden, waarbij we onze bladnaam, rij en kolom vermelden.
Onze formule om al deze in een referentie te combineren ziet er als volgt uit:
| 1 | =INDIRECT("'" & A2 & "'!" & B2 & C2) |
Onze formule wordt dan als volgt geëvalueerd:
| 123 | =INDIRECT("'" & "Blad2" & "'!" & "B" & "5")=INDIRECT("'"Blad2'!B5")='Blad2'!B5 |
Technisch gezien, aangezien het woord "Blad2" geen spaties bevat, hebben we geen nodig hebben de enkele aanhalingstekens. Het is volkomen geldig om iets te schrijven als
| 1 | =Blad2!A2 |
Het kan echter geen kwaad om de aanhalingstekens te plaatsen als u ze niet nodig hebt. Het is een goede gewoonte om ze op te nemen, zodat uw formule de instantie kan verwerken waar ze nodig kunnen zijn.
INDIRECT naar een andere werkmap
We zullen ook vermelden dat INDIRECT een verwijzing naar een andere werkmap kan maken. De beperking is dat INDIRECT geen waarden ophaalt uit een gesloten werkmap, dus dit specifieke gebruik is beperkt praktisch. Als de werkmap waarnaar INDIRECT verwijst ongeopend is, zal de functie een "#REF!" fout.
De syntaxis bij het schrijven van de naam van de werkmap is dat deze tussen vierkante haken moet staan. Laten we deze opstelling gebruiken en proberen een waarde op te halen uit cel C7.
Onze formule zou zijn:
| 1 | =INDIRECT("'[" & A2 & "]" & B2 & "'!C7") |
Nogmaals, let op de plaatsing van de enkele aanhalingstekens, haakjes en uitroeptekens. Onze formule wordt dan als volgt geëvalueerd:
| 123 | =INDIRECT("'[" & "Voorbeeld.xlsx" & "]" & "Samenvatting" & "'!C7")=INDIRECT("'[Voorbeeld.xslx]Samenvatting'!C7")='[Voorbeeld.xlsx]Samenvatting'!C7 |
INDIRECT om dynamisch bereik op te bouwen
Wanneer u een grote dataset heeft, is het belangrijk om de formules te proberen en te optimaliseren, zodat ze niet meer werk doen dan nodig is. In plaats van te verwijzen naar de hele kolom A, willen we misschien alleen verwijzen naar het exacte aantal cellen in onze lijst. Denk aan de volgende indeling:
In cel B2 hebben we de formule geplaatst
| 1 | =AANTALLEN(A:A) |
De COUNTA-functie is heel gemakkelijk voor de computer om te berekenen, omdat deze eenvoudig controleert hoeveel cellen in kolom A een bepaalde waarde hebben, in plaats van logische controles of wiskundige bewerkingen uit te voeren.
Laten we nu onze formule bouwen die de waarden in kolom A optelt, maar we willen er zeker van zijn dat deze alleen kijkt naar het exacte bereik met waarden (A2:A5). We zullen onze formule schrijven als
| 1 | =SOM(INDIRECT("A2:A" & B2)) |
Onze INDIRECT pakt het nummer 5 uit cel B2 en maakt een verwijzing naar het bereik A2:A5. De SOM kan dit bereik dan gebruiken voor zijn berekening. Als we een andere waarde toevoegen aan cel A6, wordt het getal in B2 bijgewerkt en wordt onze SOM-formule ook automatisch bijgewerkt om deze nieuwe waarde op te nemen.
LET OP: Met de introductie van tabellen in Office 2007 is het veel efficiënter om uw gegevens in een tabel op te slaan en een structurele referentie te gebruiken in plaats van de formule te bouwen die we in dit voorbeeld hebben gebruikt vanwege de vluchtige aard van INDIRECT. Het kunnen echter gevallen zijn waarin u een lijst met items moet maken en geen tabel kunt gebruiken.
Dynamische grafieken met INDIRECT
Laten we het vorige voorbeeld nemen en nog een stap verder gaan. In plaats van een formule te schrijven om ons de som van de waarden te geven, maken we een benoemd bereik. We zouden dit bereik "MyData" kunnen noemen en laten verwijzen naar:
| 1 | =INDIRECT("A2:A" & COUNTA($A:$A)) |
Merk op dat, aangezien we dit in een Named Range plaatsen, we de verwijzing naar B2 hebben verwisseld en in plaats daarvan de COUNTA-functie daar rechtstreeks hebben geplaatst.
Nu we dit genoemde bereik hebben, kunnen we het in een grafiek gebruiken. We maken een blanco lijndiagram en voegen vervolgens een gegevensreeks toe. Voor de reekswaarden zou je iets kunnen schrijven als
| 1 | =Blad1!Mijn gegevens |
De grafiek gaat deze verwijzing nu gebruiken om waarden te plotten. Naarmate er meer waarden aan kolom A worden toegevoegd, verwijst de INDIRECT naar een groter en groter bereik en blijft onze grafiek up-to-date met alle nieuw toegevoegde waarden.
Dynamische gegevensvalidatie met INDIRECT
Bij het verzamelen van input van gebruikers is het soms nodig om de keuzemogelijkheden van een keuze afhankelijk te maken van een eerdere keuze. Overweeg deze lay-out, waar onze eerste kolom de gebruiker laat kiezen tussen fruit, groenten en vlees.
in de 2nd kolom, we willen geen grote lijst met alle mogelijke keuzes, omdat we de zaken al een beetje hebben beperkt. We hebben dus nog 3 andere lijsten gemaakt die er als volgt uitzien:
Vervolgens zullen we elk van toewijzen deze lijsten naar een benoemd bereik. D.w.z. alle vruchten bevinden zich in een bereik genaamd "Fruit", en groenten in "Groenten", enz.
Terug in onze tabel zijn we klaar om de gegevensvalidatie in te stellen in de 2nd kolom. We zullen een validatie van het lijsttype maken, met een invoer van:
| 1 | =INDIRECT(A2) |
De INDIRECT gaat de gemaakte keuze in col A inlezen en de naam van een categorie zien. We hebben bereiken met deze namen gedefinieerd, dus de INDIRECT neemt die naam dan aan en maakt een verwijzing naar het gewenste bereik.
extra notities
Gebruik de INDIRECT-functie om een celverwijzing van tekst te maken.
Maak eerst de tekstreeks die een celverwijzing vertegenwoordigt. De string moet ofwel in de gebruikelijke A1-stijl kolomletter & rijnummer (M37) of in R1C1-stijl (R37C13) zijn. U kunt de verwijzing rechtstreeks typen, maar meestal verwijst u naar cellen die de rijen en kolommen definiëren. Voer als laatste in welke celverwijzingsindeling u kiest. TRUE of weggelaten voor referentie in A1-stijl of FALSE voor R1C1-stijl.
Als u met INDIRECTE formules werkt, wilt u misschien de RIJ-functie om het rijnummer van een referentie te krijgen of de KOLOM Functie om het kolomnummer (geen letter) van een verwijzing te krijgen.
Keer terug naar de lijst met alle functies in Excel
INDIRECTE in Google Spreadsheets
De INDIRECT-functie werkt in Google Spreadsheets precies hetzelfde als in Excel:
