Generieke formule
=MATCH(bigtext,range)
Samenvatting
Om de laatste relatieve positie (dwz laatste rij, laatste kolom) voor tekstgegevens (met of zonder lege cellen) te krijgen, kunt u de MATCH-functie gebruiken. In het getoonde voorbeeld is de formule in D5:
=MATCH(REPT("z",255),B4:B11)
Uitleg
Deze formule gebruikt de MATCH-functie in geschatte overeenkomstmodus om de laatste tekstwaarde in een bereik te lokaliseren. Geschatte overeenkomst ingeschakeld door het derde argument in VERGELIJKEN in te stellen op 1, of dit argument weg te laten, dat standaard 1 is.
De opzoekwaarde is een zogenaamde "grote tekst" (soms afgekort tot "bigtext") die opzettelijk een waarde is die "groter" is dan elke waarde die in het bereik zal verschijnen. Wanneer u met tekst werkt, waarbij alfabetisch wordt gesorteerd, betekent dit een tekstwaarde die altijd aan het einde van de alfabetische sorteervolgorde verschijnt.
Aangezien deze formule overeenkomt met tekst, is het de bedoeling om een opzoekwaarde te construeren die nooit in de werkelijke tekst zal voorkomen, maar altijd de laatste zal zijn. Om dat te doen, gebruiken we de REPT-functie om de letter "z" 255 keer te herhalen. Het getal 255 vertegenwoordigt het grootste aantal tekens dat MATCH toestaat in een opzoekwaarde.
Als MATCH deze waarde niet kan vinden, gaat het "terug" naar de laatste tekstwaarde in het bereik en retourneert de positie van die waarde.
Opmerking: deze benadering werkt prima met lege cellen in het bereik, maar is niet betrouwbaar met gemengde gegevens die zowel cijfers als tekst bevatten.
Laatste relatieve positie versus laatste rijnummer
Bij het bouwen van geavanceerde formules die dynamische bereiken creëren, is het vaak nodig om de laatste locatie van gegevens in een lijst te achterhalen. Afhankelijk van de gegevens kan dit de laatste rij met gegevens zijn, de laatste kolom met gegevens of de kruising van beide. Opmerking: we willen de laatste relatieve positie binnen een bepaald bereik, niet het rijnummer op het werkblad:
Dynamisch bereik
U kunt deze formule gebruiken om een dynamisch bereik te creëren met andere functies zoals INDEX en OFFSET. Zie onderstaande links voor voorbeelden en uitleg:
- Dynamisch bereik met INDEX en COUNTA
- Dynamisch bereik met OFFSET en COUNTA
De inspiratie voor dit artikel kwam uit het uitstekende boek Control + Shift + Enter van Mike Girvin, waar Mike het concept van "laatste relatieve positie" uitlegt.
