Download het voorbeeldwerkboek
In deze zelfstudie leert u hoe u de laatste waarde in kolom of rij in Excel kunt opzoeken.
Laatste waarde in kolom
U kunt de LOOKUP-functie gebruiken om de laatste niet-lege cel in een kolom te vinden.
| 1 | =ZOEKEN(2,1/(B:B""),B:B) |
Laten we deze formule eens doornemen.
Het deel van de formule B:B”” retourneert een matrix met de waarden True en False: {FALSE, TRUE, TRUE,… }, waarbij wordt getest dat elke cel in kolom B leeg is (FALSE).
| 1 | =LOOKUP(2,1/({FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;… ),B:B) |
Deze Booleaanse waarden worden omgezet in 0 of 1 en worden gebruikt om 1.
| 1 | =ZOEKEN(2,{#DIV/0!;1;1;1;1;1;#DIV/0!;,B:B) |
Dit is de lookup_vector voor de LOOKUP-functie. In ons geval is de lookup_value 2, maar de grootste waarde in de lookup_vector is 1, dus de LOOKUP-functie komt overeen met de laatste 1 in de array en retourneert de overeenkomstige waarde in de result_vector.
Als u zeker weet dat u alleen numerieke waarden in uw kolom heeft, uw gegevens beginnen bij rij 1 en uw gegevensbereik doorlopend, kunt u een iets eenvoudigere formule gebruiken met de functies INDEX en COUNT.
| 1 | =INDEX(B:B,COUNT(B:B)) |
De COUNT-functie retourneert het aantal cellen gevuld met gegevens in het continue bereik (4) en de INDEX-functie geeft dus de waarde van de cel in deze overeenkomstige rij (4e).
Om mogelijke fouten te voorkomen wanneer uw gegevensbereik een combinatie van numerieke en niet-numerieke waarden bevat, of zelfs enkele lege cellen, kunt u de LOOKUP-functie samen met de ISBLANK- en NOT-functies gebruiken.
| 1 | =ZOEKEN(2,1/(NIET(ISBLANK(B:B))),B:B) |
De ISBLANK-functie retourneert een array met True en False-waarden, overeenkomend met enen en nullen. De NOT-functie verandert True (d.w.z. 1) in False en False (d.w.z. 0) in True. Als we deze resulterende array omkeren (wanneer we 1 delen door deze array), krijgen we een resultaatarray die opnieuw #DIV/0! fouten en enen, die kunnen worden gebruikt als opzoekarray (lookup_vector) in onze LOOKUP-functie. De functionaliteit van de LOOKUP-functie is dan hetzelfde als in ons eerste voorbeeld: het retourneert de waarde van de resultaatvector op de positie van de laatste 1 in de lookup-array.
Als u het rijnummer met het laatste item wilt retourneren, kunt u de formule die in ons eerste voorbeeld is gebruikt samen met de ROW-functie in uw result_vector wijzigen.
| 1 | =ZOEKEN(2,1/(B:B""),RIJ(B:B)) |
Laatste waarde in rij
Om de laatste niet-lege celwaarde in een rij gevuld met numerieke gegevens te krijgen, wilt u misschien een vergelijkbare benadering gebruiken, maar met verschillende functies: de OFFSET-functie samen met de MATCH- en MAX-functies.
| 1 | =OFFSET(Referentie, Rijen, Kolommen) |
| 1 | =OFFSET(B2,0,MATCH(MAX(B2:XFD2)+1,B2:XFD2,1)-1) |
Laten we eens kijken hoe deze formule werkt.
MATCH-functie
We gebruiken de MATCH-functie om te "tellen" hoeveel celwaarden lager zijn dan 1 + het maximum van alle waarden in rij 2 vanaf B2.
| 1 | =VERGELIJKEN(opzoekwaarde, zoekarray, [overeenkomst_type]) |
| 1 | =VERGELIJKEN(MAX(B2:XFD2)+1,B2:XFD2,1) |
De lookup_value van de MATCH-functie is het maximum van alle waarden in rij2 + 1. Aangezien deze waarde duidelijk niet bestaat in rij2 en het match_type is ingesteld op 1 (kleiner dan of gelijk aan lookup_value), retourneert de MATCH-functie de laatste "aangevinkte" celpositie in de array, dat wil zeggen, het aantal cellen gevuld met gegevens in het bereik van B2:XFD2 (XFD is de allerlaatste kolom in de nieuwere versies van Excel).
OFFSET-functie
Vervolgens gebruiken we de OFFSET-functie om de waarde van deze cel te krijgen, waarvan de positie werd geretourneerd door de MATCH-functie.
| 1 | =OFFSET(B2,0,C4-1) |
