Hoe krijg je altijd de waarde van de cel erboven wanneer je een rij invoegt of verwijdert in Excel?
In Excel is het refereren aan de waarde van de cel direct hierboven een veelvoorkomende vereiste voor het maken van lopende totalen, maand-op-maandvergelijkingen of gewoon om gegevensconsistentie te behouden terwijl je werkblad evolueert. Normaal gesproken kun je de cel hierboven refereren met een eenvoudige formule zoals =D5, maar deze oplossing heeft beperkingen: als je rijen invoegt of verwijdert, zal je formule mogelijk niet blijven verwijzen naar de nieuwe 'bovenliggende' cel zoals je zou verwachten. Zoals hieronder geïllustreerd, kan het invoegen van een nieuwe rij bovenaan de continuïteit verbreken of de referentie gekoppeld blijven aan de originele cel in plaats van zich aan te passen aan de nieuwe positie.
Om dit probleem op te lossen en ervoor te zorgen dat je formule altijd de waarde uit de directe cel hierboven ophaalt, zelfs na het invoegen of verwijderen van rijen, zijn er verschillende methoden die je kunt gebruiken. Elke methode heeft verschillende afwegingen, afhankelijk van de complexiteit van je werkblad, of je een automatische update of een handmatige formule wilt, en of je bekend bent met VBA/macros.
Inhoudsopgave:
- Altijd de waarde uit de cel hierboven ophalen bij het invoegen of verwijderen van rijen met een formule
- Automatisch de celwaarde bijwerken vanuit de cel hierboven met behulp van een VBA-gebeurtenisgedreven macro (altijd dynamisch)
Altijd de waarde uit de cel hierboven ophalen bij het invoegen of verwijderen van rijen met een formule
Om dit probleem op een eenvoudige manier aan te pakken die geen macros of complexe instellingen vereist, kun je een formule gebruiken die dynamisch verwijst naar de cel hierboven, ongeacht hoe je rijen veranderen. De formule gebruikt Excels INDIRECT en ADRES-functies, zodat verwijzingen altijd de cel hierboven 'volgen', zelfs als de rijen verschuiven door invoegingen of verwijderingen. Dit maakt het vooral geschikt voor werkbladen waar je vaak de rijstructuur wijzigt, zoals het toevoegen van nieuwe gegevens bovenaan of in het midden van een lijst.
Voer de volgende formule rechtstreeks in de cel in waar je altijd de waarde uit de cel hierboven wilt ophalen (bijvoorbeeld in cel B6 als je B5 wilt refereren):
=INDIRECT(ADDRESS(ROW()-1,COLUMN()))Druk op Enter nadat je de formule hebt getypt. De huidige cel toont onmiddellijk de waarde van de cel direct hierboven, zoals hieronder weergegeven:
Als je nu een nieuwe rij ergens boven de referentieformulecel invoegt, zal de formule herberekenen en altijd de waarde uit de nieuwe cel hierboven tonen. Op deze manier blijven je formules altijd up-to-date, ongeacht rijinvoegingen of -verwijderingen. Bekijk de volgende schermafbeelding ter referentie:
Uitleg van parameters en tips:
- Deze formule haalt de waarde van de cel direct hierboven de huidige formulecel op – dus het gebruik ervan in B6 zal altijd B5 weerspiegelen, zelfs als rijen boven worden ingevoegd of verwijderd.
- Als je deze formule gebruikt in de eerste rij van je gegevens (zoals A1), kan het proberen om gegevens uit een niet-bestaande rij te halen en een
#VERW!-fout retourneren. Je kunt dit voorkomen door foutafhandeling toe te voegen, bijvoorbeeld met=ALS(RIJ()=1;"";INDIRECT(ADRES(RIJ()-1;KOLOM())))om blanco weer te geven in de eerste rij. - Houd er rekening mee dat INDIRECT een vluchtige functie is, dus in extreem grote werkbladen kan overmatig gebruik de berekeningen vertragen.
- Het gebruik van deze formule werkt goed wanneer je een strikte relatie wilt behouden met de rijlocatie, ongeacht hoe je bladstructuur verandert.
Problemen oplossen en samenvattingsuggesties:
Als je formule niet zoals verwacht wordt bijgewerkt na het invoegen of verwijderen van rijen, controleer dan of deze in de bedoelde cel is ingevoerd. Zorg er ook voor dat je geen absolute celverwijzingen gebruikt (zoals $A$1), die statisch zijn. Als je #REF! fouten tegenkomt in de eerste rij, overweeg dan het gebruik van een voorwaardelijke formule zoals eerder vermeld. Voor geavanceerde automatisering, of als je de waarde wilt kopiëren en niet alleen refereren, zie de VBA-gebeurtenisgedreven macro-oplossing hieronder voor een dynamische, op code gebaseerde aanpak.
Automatisch de celwaarde bijwerken vanuit de cel hierboven met behulp van een VBA-gebeurtenisgedreven macro (altijd dynamisch)
Als je een oplossing nodig hebt waarbij de celwaarde altijd automatisch wordt bijgewerkt om overeen te komen met de cel hierboven, ongeacht het invoegen of verwijderen van rijen – en je wilt dit gedrag beheren zonder formules te kopiëren – is het gebruik van een gebeurtenisgedreven VBA-macro effectief. Deze methode is ideaal voor gebruikers die bekend zijn met het inschakelen van macros en biedt dynamische updates: telkens wanneer je een specifieke cel of bereik selecteert, of wanneer je een waarde wijzigt, kan de macro onmiddellijk de doelcel instellen om altijd gelijk te zijn aan de waarde hierboven, waardoor gegevensinvoer consistent blijft, ongeacht hoe je werkblad verandert. Dit is vooral handig in sjablonen waar je een vast patroon of standaardwaarde-instelling wilt hebben in elke nieuwe rij.
Hier is hoe je dit instelt met behulp van de Worksheet_Change-gebeurtenis:
1. Klik met de rechtermuisknop op het tabblad van het werkblad waar je deze functionaliteit wilt hebben en selecteer Code Weergeven. De Microsoft Visual Basic for Applications-editor zal openen naar de juiste werkbladmodule.
2. Kopieer en plak de volgende VBA-code in het venster van de werkbladmodule:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim WatchRange As Range
On Error Resume Next
' Set the range you want to monitor (for example, B2:B100)
Set WatchRange = Intersect(Target, Me.Range("B2:B100"))
If Not WatchRange Is Nothing Then
Application.EnableEvents = False
Dim cell As Range
For Each cell In WatchRange
' Avoid the first row, or adjust as needed
If cell.Row > 1 Then
cell.Value = Me.Cells(cell.Row - 1, cell.Column).Value
End If
Next cell
Application.EnableEvents = True
End If
End SubParameteropmerkingen: Vervang "B2:B100" in Me.Bereik("B2:B100") door het daadwerkelijke bereik waar je dit gedrag wilt (je kunt het instellen op een hele kolom, bijv. "B:B", maar het verkleinen van het bereik verbetert de prestaties en voorkomt onbedoelde overschrijvingen).
3. Sluit de VBA-editor. Nu zal Excel, wanneer een cel wordt gewijzigd, ingevoegd of het werkblad wordt bijgewerkt binnen je gespecificeerde bereik, automatisch die cellen bijwerken om de waarde hierboven weer te geven. Bijvoorbeeld, als je een rij invoegt op rij 5, zullen alle bewaakte cellen in kolom B vanaf dat punt de waarde uit de cel direct hierboven hun nieuwe positie ophalen.
- Wees voorzichtig: deze code zal handmatig ingevoerde waarden binnen het bewaakte bereik overschrijven. Gebruik met zorg als je formulecellen hebt of als je originele invoer wilt behouden.
- Als je deze VBA-macro in de toekomst wilt blijven gebruiken in de werkmap, moet je het bestand opslaan als een macro-ondersteunde werkmap (.xlsm).
- Gebeurteniscode werkt alleen op het werkbladmodule waar de macro is geplakt (niet op alle werkbladen tenzij code wordt toegevoegd aan elke werkbladmodule).
- Als je wilt dat de update plaatsvindt bij het selecteren van een cel in plaats van bij het wijzigen van een waarde, kun je Worksheet_SelectionChange gebruiken en een vergelijkbare logica.
Problemen oplossen en samenvattingsuggesties:
Als de VBA-script na het kopiëren niet lijkt te werken, controleer dan of macros zijn ingeschakeld in je werkmap en dat je de code in de juiste bladmodule hebt geplakt (niet in een standaardmodule). Als je fouten krijgt of Excel vastloopt, controleer dan dubbel of Application.EnableEvents is ingesteld op False voordat je automatische celwijzigingen uitvoert en terug op True wordt gezet om recursieve looping te voorkomen. Voor andere geavanceerde functies of fijnere controle, overweeg aangepaste scripts op basis van je gegevensstructuur.
Beste productiviteitstools voor Office
Verbeter je Excel-vaardigheden met Kutools voor Excel en ervaar ongeëvenaarde efficiëntie. Kutools voor Excel biedt meer dan300 geavanceerde functies om je productiviteit te verhogen en tijd te besparen. Klik hier om de functie te kiezen die je het meest nodig hebt...
Office Tab brengt een tabbladinterface naar Office en maakt je werk veel eenvoudiger
- Activeer tabbladbewerking en -lezen in Word, Excel, PowerPoint, Publisher, Access, Visio en Project.
- Open en maak meerdere documenten in nieuwe tabbladen van hetzelfde venster, in plaats van in nieuwe vensters.
- Verhoog je productiviteit met50% en bespaar dagelijks honderden muisklikken!
Alle Kutools-invoegtoepassingen. Eén installatieprogramma
Kutools for Office-suite bundelt invoegtoepassingen voor Excel, Word, Outlook & PowerPoint plus Office Tab Pro, ideaal voor teams die werken met Office-toepassingen.
- Alles-in-één suite — invoegtoepassingen voor Excel, Word, Outlook & PowerPoint + Office Tab Pro
- Eén installatieprogramma, één licentie — in enkele minuten geïnstalleerd (MSI-ready)
- Werkt beter samen — gestroomlijnde productiviteit over meerdere Office-toepassingen
- 30 dagen volledige proef — geen registratie, geen creditcard nodig
- Beste prijs — bespaar ten opzichte van losse aanschaf van invoegtoepassingen