Hoe vindt en retourneert u de een-na-laatste waarde in een specifieke rij of kolom in Excel?
Bij het werken met grote Excel-werkmappen is het vaak nodig om niet alleen de laatste waarde in een rij of kolom te identificeren, maar specifiek de een-na-laatste waarde. Zoals in de onderstaande afbeelding wordt getoond, moet u bij het tabelbereik A1:E16 bijvoorbeeld de een-na-laatste waarde in de 6e rij of in kolom B ophalen. Deze behoefte komt veel voor bij het bijhouden van recente wijzigingen, het monitoren van tijdreeksgegevens of het analyseren van de meest recente, maar niet huidige, gegevens in een dataset. In vergelijking met het eenvoudigweg vinden van de laatste waarde, kan het vinden van de een-na-laatste waarde minder eenvoudig zijn, vooral wanneer de gegevens Lege cellen bevatten of regelmatig worden bijgewerkt. Dit artikel biedt duidelijke, stapsgewijze oplossingen voor deze taak om uw workflow efficiënter te maken en veelvoorkomende valkuilen te vermijden.
- Zoek en retourneer de een-na-laatste waarde in een bepaalde rij of kolom met formules
- VBA-code – Gebruik een macro om de een-na-laatste waarde in een opgegeven rij of kolom te vinden
- Andere ingebouwde Excel-methoden – Filter alle lege cellen en identificeer handmatig de een-na-laatste waarde
Zoek en retourneer de een-na-laatste waarde in een bepaalde rij of kolom met formules
Zoals in de bovenstaande afbeelding te zien is, bieden Excel-formules een dynamische en efficiënte oplossing wanneer u de een-na-laatste waarde in de 6e rij of kolom B binnen het bereik A1:E16 moet vinden en retourneren. Formules zijn ideaal voor scenario’s waarin uw gegevens regelmatig worden bijgewerkt of waarbij de positie van de een-na-laatste waarde kan verschuiven door toevoegingen of verwijderingen. In tegenstelling tot handmatige methodes zorgt een formule ervoor dat uw resultaat automatisch meebeweegt met de nieuwste gegevens.
Zoek en retourneer de een-na-laatste waarde in kolom B
1. Selecteer een lege cel waarin u de een-na-laatste waarde wilt weergeven. Voer de volgende matrixformule in de formulebalk in en druk vervolgens op Ctrl + Shift + Enter (voor oudere versies van Excel) om deze als matrixformule te bevestigen. In Excel 365 of Excel 2021 hoeft u alleen op Enter te drukken, omdat matrixformules daar automatisch worden verwerkt.
=INDEX(B:B,LARGE(IF(B:B<>"",ROW(B:B)),2))
Opmerking: In deze formule verwijst B:B naar kolom B. Als u de een-na-laatste waarde in een andere kolom nodig hebt, vervangt u eenvoudig B:Bdoor de gewenste kolomverwijzing (bijvoorbeeld)C:C voor kolom C). Deze methode werkt voor een bereik met of zonder lege cellen, maar als er verborgen waarden of uitgefilterde gegevens aanwezig zijn, kunt u afwijkingen tegenkomen. Controleer daarom altijd uw gegevensbereik als u de verwachte waarde niet ziet.
Zoek en retourneer de een-na-laatste waarde in rij 6
Selecteer een lege cel om het resultaat voor de 6e rij weer te geven. Voer vervolgens de volgende formule in de formulebalk in en druk op Enter om te bevestigen.
=OFFSET($A$6,0, COUNTA(6:6)-2,1,1)
Opmerking: In de bovenstaande formule is $A$6 de eerste cel van rij 6, en 6:6geeft de gehele rij 6 aan. Pas deze verwijzingen aan om andere rijen als doelwit te nemen. De formule telt dynamisch het aantal niet-lege cellen in rij 6 en verschuift vervolgens vanaf de begincel om de een-na-laatste gevulde cel te lokaliseren. Als uw rij formules bevat die lege tekenreeksen retourneren ()""), kan COUNTA deze nog steeds als niet-leeg tellen, wat het resultaat zou kunnen beïnvloeden. Controleer bij bereiken met een combinatie van constanten en formules de uitkomsten om fouten te voorkomen.
Tips voor het gebruik van formules:
- Bij grote datasets kan het gebruik van volledige kolomverwijzingen (bijv.)
B:B) de prestaties nadelig beïnvloeden. Beperk het bereik daarom, indien mogelijk, tot alleen het benodigde gebied (bijv.B1:B100). - Als uw gegevens verborgen rijen bevatten of gefilterd zijn, worden verborgen en uitgefilterde cellen nog steeds meegerekend in formules. Overweeg bij gefilterde gegevens speciale SUBTOTAAL-functies of hulpkolommen te gebruiken.
- Als alle waarden in een doelrij of -kolom leeg zijn, kunnen deze formules een foutmelding of onverwacht resultaat geven. Zorg er daarom voor dat uw gegevens minimaal twee niet-lege waarden bevatten.
VBA-code – Gebruik een macro om de een-na-laatste waarde in een opgegeven rij of kolom te vinden
Voor gebruikers die regelmatig de een-na-laatste waarde in wisselende of grote bereiken moeten opsporen, bespaart het automatiseren van dit proces met een VBA-macro aanzienlijk tijd – zeker bij dynamische of complexe tabellen. Macro’s zijn ideaal als uw dataset van omvang verandert en u formules niet steeds opnieuw hoeft aan te passen, of wanneer u met dezelfde aanpak waarden uit meerdere verschillende posities wilt ophalen. Hieronder vindt u een VBA-codeoplossing die u vraagt uw doelrij of -kolom op te geven en vervolgens automatisch de een-na-laatste waarde retourneert, zodat handmatig tellen of aanpassen overbodig wordt.
1. Ga naar Ontwikkelaarshulpmiddelen in het Excel-lint, klik op Visual Basic om de VBA-editor te openen. Klik in de editor op Invoegen > Module. Plak vervolgens de volgende VBA-code in de nieuw aangemaakte module:
Sub GetSecondToLastValue()
Dim rng As Range
Dim arr As Variant
Dim values As Collection
Dim i As Long
Dim secondLast As Variant
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set rng = Application.InputBox("Select the row or column range to analyze", xTitleId, Selection.Address, Type:=8)
If rng Is Nothing Then Exit Sub
arr = rng.Value
Set values = New Collection
If rng.Rows.Count = 1 Then
For i = 1 To rng.Columns.Count
If arr(1, i) <> "" Then
values.Add arr(1, i)
End If
Next i
ElseIf rng.Columns.Count = 1 Then
For i = 1 To rng.Rows.Count
If arr(i, 1) <> "" Then
values.Add arr(i, 1)
End If
Next i
Else
MsgBox "Please select a single row or single column range.", vbExclamation
Exit Sub
End If
If values.Count < 2 Then
MsgBox "There are less than two non-blank values in the selected range.", vbInformation
Exit Sub
End If
secondLast = values(values.Count - 1)
MsgBox "The second-to-last value is: " & secondLast, vbInformation
End Sub2. Nadat u de code hebt ingevoerd, keert u terug naar Excel en voert u de macro uit via de knop 
Uitvoeren, of door op Alt + F8 te drukken en GetSecondToLastValue te selecteren in de lijst. Er verschijnt een dialoogvenster waarin u wordt gevraagd een bereik van één rij of kolom te selecteren (bijvoorbeeld B1:B16 voor een kolom of A6:E6 voor een rij). Zodra u uw selectie bevestigt, toont de macro de een-na-laatste waarde in een nieuw dialoogvenster.
Opmerkingen:
- Deze macro telt automatisch alleen de niet-lege cellen en slaat lege cellen binnen de geselecteerde rij of kolom over.
- Als uw selectie minder dan twee niet-lege waarden bevat, ontvangt u een waarschuwingsbericht en wordt er geen waarde geretourneerd.
- De macro is bedoeld voor selecties van één rij of één kolom. Als u tegelijkertijd meerdere rijen én kolommen selecteert, wordt u gevraagd uw selectie aan te passen.
- Voor automatisering kunt u de code verder aanpassen, zodat het resultaat niet in een berichtvenster verschijnt, maar direct naar een specifieke cel op een werkblad wordt gekopieerd.
Deze VBA-oplossing is vooral ideaal voor gebruikers die werken met dynamische tabellen of vaak veranderende datasets, omdat ze handmatige fouten en herhalende taken effectief minimaliseert.
Andere ingebouwde Excel-methoden – Filter alle lege cellen en identificeer handmatig de een-na-laatste waarde
Hoewel formules en macro’s geautomatiseerde manieren bieden om de een-na-laatste waarde te vinden, kiest u soms liever voor een snelle, visuele aanpak—vooral bij onregelmatige of niet-aaneengesloten gegevens, of wanneer u slechts af en toe een waarde wilt controleren. Met de ingebouwde filterfuncties van Excel verbergt u tijdelijk lege of irrelevante cellen, zodat u de gewenste invoer direct ziet zonder formules te hoeven schrijven of aanpassen.
Filtermethode:
- Selecteer het gegevensbereik voor uw kolom of rij (voor kolommen: cellen B1:B16; voor rijen: A6:E6 in uw werkblad).
- Klik op het Excel-lint op Gegevens > Filter om filterpijltjes in te schakelen. Klik vervolgens in de kolomkop of naast uw geselecteerde bereik op het filterkeuzemenu.
- Schakel (Lege cellen) uit om lege cellen te verbergen. De gegevensweergave toont nu alleen niet-lege waarden.
- Bij een kolom scrolt u naar het einde van de gefilterde lijst en bekijkt u de een-na-laatste waarde. Bij een rij telt u, na filtering indien mogelijk, vanaf rechts om de een-na-laatste niet-lege invoer te identificeren.
Opmerkingen en tips:
- Filteren werkt het snelst bij kleine tot middelgrote lijsten of wanneer u visueel de positie van waarden wilt controleren.
- Bij zeer grote datasets kan filteren de prestaties vertragen en is het minder geschikt voor automatisering of herhaald gebruik.
- Zorg ervoor dat u bij toegepaste filters altijd de juiste context in ogenschouw neemt—gefilterde lijsten kunnen namelijk verborgen rijen of kolommen overslaan.
- Om filters te verwijderen, klikt u op Wissen op het tabblad Gegevens.
Deze methode is minder geschikt voor continue dynamische analyse of grote datasets, maar biedt wel transparante, handmatige controle over de een-na-laatste niet-lege invoer—ideaal voor snel probleemoplossen of verificatie.
Gerelateerde artikelen:
- Hoe vindt u de positie van het eerste of laatste cijfer in een tekstreeks in Excel?
- Hoe vindt u de eerste of laatste vrijdag van elke maand in Excel?
- Hoe zoekt u met VLOOKUP de eerste, tweede of n-de overeenkomende waarde in Excel?
- Hoe vindt u in Excel de waarde met de hoogste frequentie binnen een bereik?
Beste Office-productiviteitshulpmiddelen
Geef uw Excel-vaardigheden een boost met Kutools voor Excel en ervaar efficiëntie zoals nooit tevoren.Kutools voor Excel biedt meer dan 300 geavanceerde functies om de productiviteit te verhogen en Tijd besparen.Klik hier om de functie te krijgen die u het meest nodig heeft...
Office Tab brengt een tabbladinterface naar Office en maakt uw werk veel eenvoudiger
- Schakel tabbladbewerking en -lezen in voor Word, Excel, PowerPoint, Publisher, Access, Visio en Project.
- Open en maak meerdere documenten aan in nieuwe tabbladen binnen hetzelfde venster, in plaats van in afzonderlijke vensters.
- Verhoogt uw productiviteit met 50 % en bespaart u dagelijks honderden muisklikken!
Alle Kutools-add-ins in één installatieprogramma.
Kutools for Office bundelt add-ins voor Excel, Word, Outlook en PowerPoint, plus Office Tab Pro—ideaal voor teams die met meerdere Office-apps werken.
- Alles-in-één suite— add-ins voor Excel, Word, Outlook & PowerPoint plus Office Tab Pro
- Één installatieprogramma, één licentie— binnen enkele minuten klaar (MSI-geschikt)
- Werkt beter samen— gestroomlijnde productiviteit in alle Office-apps
- 30 dagen volledig functionele proefversie— geen registratie, geen creditcard
- Beste prijs-kwaliteitverhouding— bespaar ten opzichte van het afzonderlijk kopen van add-ins