In dit artikel zullen we leren hoe u celverwijzingen kunt krijgen met behulp van de INDIRECT-functie in Excel.
Scenario :
Werken met datasets in verschillende bladen, Soms moeten we zoeken naar een reeks waarden van een ander blad met behulp van een INDIRECT-functie. Bijvoorbeeld het extraheren van vitale informatie in verschillende data-arrays van de verschillende bladen naar het hoofdblad. Dus hiervoor gebruikt u mogelijk de kopieer- en plakwaarde van het gegevensblad naar het hoofdblad. Maar de geretourneerde waarde wordt niet bijgewerkt omdat de waarde wordt bijgewerkt in het gegevensblad. De hieronder uitgelegde formule bespaart je herhaalde werk om elk element één voor één uit een ander blad te kiezen.
Hoe het probleem op te lossen?
Voor dit probleem zullen we nadenken over de manier waarop we elementen uit hetzelfde blad kiezen, en vervolgens zullen we de formule gebruiken om een lijst met waarden uit verschillende bladen te extraheren. Excel INDIRECT & CELL-functie zal worden gebruikt om een formule te maken om dit te doen, zoals hieronder wordt uitgelegd.
Generieke formule:
| =INDIRECT ("bladnaam" & "!" & CEL ("adres", cel_ref) |
bladnaam : bladnaam
cell_ref : celverwijzing zonder bladnaam.
Voorbeeld :
Al deze dingen kunnen verwarrend zijn om te begrijpen. Laten we een voorbeeld nemen om te begrijpen hoe de formule kan worden gebruikt om een reeks waarden uit de verschillende bladen te extraheren. Hier hebben we een werkblad met waarden in verschillende bladen. We moeten een lijst met waarden extraheren met behulp van de formule
Gebruik de formule:
| =INDIRECT ($C$3 & "!" & CEL("adres",B1)) |
Uitleg:
- Bij het extraheren van een waarde uit het andere blad. De Excel accepteert een syntaxis die Blad1!B1 is. (Hier is Blad1 de bladnaam en B1 is de celverwijzing).
- De functie CEL retourneert de celverwijzing en voert de verwijzing naar de INDIRECT-functie.
- & operator voegt de twee of meer argumenten samen of voegt ze samen.
- De functie INDIRECT retourneert de waarde in de opgegeven celverwijzing.
Hier is B1 een celverwijzing van hetzelfde blad, maar wordt de waarde uit het gegevensblad gehaald.
De waarde in de gegevensblad B1-cel is 5800. Kopieer de formule om de lijst met waarden te krijgen, beginnend bij de waarde die is geëxtraheerd uit het eerste resultaat.
Hier hebben we de lijst met waarden die beginnen bij de B1 tot B7-cel van het gegevensblad.
Excel verhoogt de celverwijzing van een ander blad met behulp van celverwijzing.
We weten hoe we de celverwijzingsverhoging van hetzelfde blad kunnen krijgen. Laten we een formule gebruiken om waarde uit hetzelfde blad te halen. Generieke formule is =cell_ref.
Wat deze formule doet, is dat plakken de waarde kiest uit het adres dat de celverwijzing is. Wanneer een celverwijzing wordt gebruikt binnen een formule. Het kan voor verschillende taken worden gebruikt en de waarde wordt bijgewerkt naarmate de waarde vanuit de cel wordt bijgewerkt. Breid de formule uit naar onderstaande cellen om een lijst met waarden te krijgen.
Zoals je hier kunt zien hebben we het benodigde aantal. Nu moeten we deze waarden naar het hoofdblad krijgen. Excel accepteert de syntaxis bij het extraheren van waarden uit een ander blad, namelijk =Referentie!A1. (Hier is Reference de bladnaam en A1 is de celverwijzing).
Kopieer de formule naar de resterende cellen met behulp van Ctrl + D of sleep naar beneden vanaf de rechteronderkant van de cel.
Zoals u kunt zien, hebben we hier alle vereiste waarden uit het referentieblad. Er is nog een methode om de waarden uit verschillende bladen te halen.
Extraheer lijst met waarden uit verschillende bladen met behulp van benoemd bereik.
We kunnen een methode gebruiken om de hele array rechtstreeks aan te roepen met behulp van het genoemde bereik van verschillende bladen. Hiervoor zullen we gewoon de array of tabel selecteren en het geselecteerde onderdeel een naam geven in het naamvak en het bij de naam noemen waar nodig in het werkblad.
Selecteer hier de hele tabel en noem deze Data. Het naamvak bevindt zich in de linkerbovenhoek van het blad, zoals gemarkeerd in het bovenstaande blad.
We extraheren de genoemde tabel in het nieuwe blad. Ga naar het nieuwe blad en selecteer hetzelfde aantal cellen en typ de onderstaande formule.
Gebruik de formule:
| { =Gegevens } |
Let op: gebruik Ctrl + Shift + Enter in plaats van Enter naar de formule die voor arrays werkt. Gebruik accolades niet handmatig.
Zoals we kunnen zien, werkt de formule prima. Elk van deze methoden kan worden gebruikt waar nodig.
Hier zijn alle observatienotities met betrekking tot het gebruik van de formule.
Opmerkingen:
- Deze formules extraheren alleen waarden, niet het formaat. U kunt dezelfde opmaak toepassen met behulp van de opmaaktool op alle vellen.
- Waarde wordt bijgewerkt als we de waarde in de celverwijzing wijzigen.
- We kunnen de waarden in de formule bewerken met behulp van verschillende operators, zoals de wiskundige operator voor getallen, de "&" -operator voor tekstwaarden.
Ik hoop dat dit artikel over het verhogen van celverwijzingen met de INDIRECT-functie verklarend is. Vind hier meer artikelen over het extraheren van waarden met formules. Als je onze blogs leuk vond, deel deze dan met je vrienden op Facebook. En je kunt ons ook volgen op Twitter en Facebook. We horen graag van je, laat ons weten hoe we ons werk kunnen verbeteren, aanvullen of vernieuwen en het voor jou beter kunnen maken. Schrijf ons op de e-mailsite
Alles over Excel benoemde bereiken: Excel-bereiken die zijn getagd met namen zijn gemakkelijk te gebruiken in Excel-formules. Leer er hier alles over.
Het naamvak in Excel : Excel Name Box is niets anders dan een klein weergavegebied linksboven op het Excel-blad dat de naam van actieve cellen of bereiken in Excel toont. U kunt een cel of array hernoemen voor verwijzingen.
Hoe bladnaam van werkblad in Excel te krijgen : CELL-functie in Excel geeft u de informatie over elk werkblad, zoals col, inhoud, bestandsnaam, … enz. Leer hier hoe u de bladnaam kunt krijgen met behulp van de CELL-functie.
Hoe een sequentieel rijnummer in Excel te krijgen: Soms moeten we een opeenvolgend rijnummer in een tabel krijgen, dit kan voor een serienummer of iets anders zijn. In dit artikel leren we hoe u rijen in Excel kunt nummeren vanaf het begin van de gegevens.
Verhoog een getal in een tekenreeks in Excel: Als je een grote lijst met items hebt en je moet het laatste nummer van de tekst van de oude tekst in Excel vergroten, dan heb je hulp nodig van de twee TEKST- en RECHTS-functies.
Populaire artikels:
Hoe de IF-functie in Excel te gebruiken? : De IF-instructie in Excel controleert de voorwaarde en retourneert een specifieke waarde als de voorwaarde WAAR is of retourneert een andere specifieke waarde als ONWAAR.
Hoe de VERT.ZOEKEN-functie in Excel te gebruiken? : Dit is een van de meest gebruikte en populaire functies van Excel die wordt gebruikt om waarde op te zoeken uit verschillende bereiken en bladen.
Hoe de AANTAL.ALS-functie in Excel te gebruiken? : Tel waarden met voorwaarden met behulp van deze geweldige functie. U hoeft uw gegevens niet te filteren om specifieke waarden te tellen. Countif-functie is essentieel om uw dashboard voor te bereiden.
Hoe de SUMIF-functie in Excel te gebruiken? : Dit is een andere essentiële functie van het dashboard. Dit helpt u bij het optellen van waarden voor specifieke voorwaarden.