Bij het gebruik van opzoekformules in Excel (zoals VERT.ZOEKEN, XLZOEKEN of INDEX/VERGELIJKEN), is het de bedoeling om de overeenkomende waarde te vinden en die waarde (of een overeenkomstige waarde in dezelfde rij/kolom) als resultaat te krijgen.
Maar in sommige gevallen wilt u misschien dat de formule het celadres van de waarde retourneert in plaats van de waarde op te halen.
Dit kan met name handig zijn als u een grote gegevensset hebt en u de exacte positie van het resultaat van de opzoekformule wilt weten.
Er zijn enkele functies in Excel die zijn ontworpen om precies dit te doen.
In deze tutorial laat ik je zien hoe je dat kunt zoek en retourneer het celadres in plaats van de waarde in Excel met behulp van eenvoudige formules.
Zoek en retourneer celadres met behulp van de ADRES-functie
De ADRES-functie in Excel is precies hiervoor bedoeld.
Het neemt het rij- en het kolomnummer en geeft u het celadres van die specifieke cel.
Hieronder staat de syntaxis van de ADRES-functie:
=ADRES(rij_getal, kolom_getal, [abs_getal], [a1], [bladtekst])
waar:
- row_num: Rijnummer van de cel waarvan u het celadres wilt hebben
- column_num: Kolomnummer van de cel waarvan u het adres wilt
- [abs_num]: optioneel argument waarmee u kunt specificeren of de celverwijzing absoluut, relatief of gemengd moet zijn.
- [a1]: Optioneel argument waar u kunt specificeren of u de verwijzing in de R1C1-stijl of A1-stijl wilt
- [sheet_text]: optioneel argument waar u kunt specificeren of u de bladnaam samen met het celadres wilt toevoegen of niet
Laten we nu een voorbeeld nemen en kijken hoe dit werkt.
Stel dat er een dataset is zoals hieronder getoond, waar ik de werknemer-ID, hun naam en hun afdeling heb, en ik wil snel het celadres weten dat de afdeling voor werknemer-ID KR256 bevat.
Hieronder is de formule die dit zal doen:
=ADRES(OVEREENKOMST("KR256",A1:A20,0),3)
In de bovenstaande formule heb ik de MATCH-functie gebruikt om het rijnummer te achterhalen dat de gegeven werknemers-ID bevat.
En aangezien de afdeling in kolom C staat, heb ik 3 als tweede argument gebruikt.
Deze formule werkt prima, maar heeft één nadeel: hij werkt niet als je de rij boven de dataset of een kolom links van de dataset toevoegt.
Dit komt omdat wanneer ik het tweede argument (het kolomnummer) als 3 specificeer, het hard gecodeerd is en niet zal veranderen.
Als ik een kolom aan de linkerkant van de dataset toevoeg, zou de formule 3 kolommen tellen vanaf het begin van het werkblad en niet vanaf het begin van de dataset.
Dus als je een vaste dataset hebt en een simpele formule nodig hebt, dan werkt dit prima.
Maar als je dit nodig hebt om onfeilbaarder te zijn, gebruik dan degene die in het volgende gedeelte wordt behandeld.
Zoek en retourneer het celadres met behulp van de CELL-functie
Hoewel de ADDRESS-functie speciaal is gemaakt om u de celverwijzing van het opgegeven rij- en kolomnummer te geven, is er een andere functie die dit ook doet.
Het wordt de CEL-functie genoemd (en het kan u veel meer informatie over de cel geven dan de ADRES-functie).
Hieronder staat de syntaxis van de CELL-functie:
=CEL(info_type, [referentie])
waar:
- info_type: de informatie over de gewenste cel. Dit kan het adres zijn, het kolomnummer, de bestandsnaam, enz.
- [referentie]: optioneel argument waar u de celverwijzing kunt opgeven waarvoor u de celinformatie nodig hebt.
Laten we nu een voorbeeld bekijken waarin u deze functie kunt gebruiken om de celverwijzing op te zoeken en op te halen.
Stel dat u een dataset heeft zoals hieronder weergegeven, en u wilt snel het celadres weten dat de afdeling voor werknemer-ID KR256 bevat.
Hieronder is de formule die dit zal doen:
=CEL("adres", INDEX($A$1:$D$20,MATCH("KR256",$A$1:$A$20,0),3))
De bovenstaande formule is vrij eenvoudig.
Ik heb de INDEX-formule gebruikt als het tweede argument om de afdeling voor de werknemer-ID KR256 te krijgen.
En dan gewoon verpakt in de CEL-functie en gevraagd om het celadres van deze waarde die ik krijg van de INDEX-formule terug te geven.
Nu is hier de geheim waarom het werkt - de INDEX-formule retourneert de opzoekwaarde wanneer u deze alle benodigde argumenten geeft. Maar tegelijkertijd zou het ook de celverwijzing van die resulterende cel retourneren.
In ons voorbeeld retourneert de INDEX-formule "Verkoop" als de resulterende waarde, maar tegelijkertijd kunt u deze ook gebruiken om u de celverwijzing van die waarde te geven in plaats van de waarde zelf.
Normaal gesproken, wanneer u de INDEX-formule in een cel invoert, wordt de waarde geretourneerd omdat dat is wat er van wordt verwacht. Maar in scenario's waarin een celverwijzing vereist is, geeft de INDEX-formule u de celverwijzing.
In dit voorbeeld is dat precies wat het doet.
En het beste van het gebruik van deze formule is dat deze niet is gekoppeld aan de eerste cel in het werkblad. Dit betekent dat u elke dataset kunt selecteren (die zich overal in het werkblad kan bevinden), de INDEX-formule kunt gebruiken om regelmatig op te zoeken en u krijgt nog steeds het juiste adres.
En als u een extra rij of kolom invoegt, wordt de formule dienovereenkomstig aangepast om u het juiste celadres te geven.
Dit zijn dus twee eenvoudige formules die u kunt gebruiken om op te zoeken en zoek en retourneer het celadres in plaats van de waarde in Excel.
Ik hoop dat je deze tutorial nuttig vond.
