Hoe blanco rijen invoegen wanneer de waarde verandert in Excel?

Stel dat je een reeks gegevens hebt, en nu wil je blanco rijen invoegen tussen de gegevens wanneer de waarde verandert, zodat je opeenvolgende dezelfde waarden in één kolom kunt scheiden zoals te zien is in de volgende schermafbeeldingen. In dit artikel zal ik het hebben over enkele trucs om dit probleem op te lossen.

Blanco rijen invoegen wanneer de waarde verandert met de functie Subtotaal

Specifiek aantal blanco rijen invoegen wanneer de waarde verandert met een krachtige functie

Blanco rijen invoegen wanneer de waarde verandert met hulptabellen

Blanco rijen invoegen wanneer de waarde verandert met VBA-code

Blanco rijen invoegen wanneer de waarde verandert met de functie Subtotaal

Met de functie Subtotaal kun je blanco rijen invoegen tussen de gegevens wanneer de waarde verandert volgens de volgende stappen:

1. Selecteer het gegevensbereik dat je wilt gebruiken.

2. Klik op Gegevens > Subtotaal om het dialoogvenster Subtotaal te openen, en in het dialoogvenster Subtotaal voer je de volgende opties uit:

1: Selecteer de kolomnaam waarop je blanco rijen wilt invoegen wanneer de waarde verandert onder de sectie Bij elke wijziging in;

2: Kies Tellen uit de vervolgkeuzelijst Functie gebruiken;

3: Vink de kolomnaam aan waarin je de subtotalen wilt invoegen in het vak Subtotalen toevoegen aan

3. Klik dan op OK, de subtotaallijnen zijn ingevoegd tussen de verschillende producten, en de overzichtssymbolen worden weergegeven aan de linkerkant van de tabel, zie screenshot:

4. Klik vervolgens op het nummer 2 bovenaan het overzichtssymbool om alleen de subtotaallijnen weer te geven.

5. Selecteer vervolgens het subtotaalbereik van de gegevens en druk op Alt+; sneltoetsen om alleen de zichtbare rijen te selecteren, zie screenshot:

6. Nadat je alleen de zichtbare rijen hebt geselecteerd, druk dan op de Delete-toets op het toetsenbord, en alle subtotaalrijen zijn verwijderd.

7. Klik dan op een andere cel en ga terug naar Gegevens > Groeperen > Overzicht wissen om de overzichten te verwijderen, zie screenshot:

8. De overzichtssymbolen zijn meteen gewist, en je kunt zien dat er blanco rijen zijn ingevoegd tussen de gegevens wanneer de waarde verandert, zie screenshot:

9. Tot slot kun je kolom A verwijderen indien nodig.

Specifiek aantal blanco rijen invoegen wanneer de waarde verandert met een krachtige functie

Als je moe bent van de bovenstaande lastige methoden, hier introduceer ik een handig hulpmiddel, Kutools voor Excel's Markeer wijzigingen in kolom kan je helpen om paginabreuken, blanco rijen, onderste rand of vulkleur in te voegen wanneer de celwaarde snel en gemakkelijk verandert.

Nadat je Kutools voor Excel hebt geïnstalleerd, doe dan het volgende:

1. Klik op Kutools > Opmaak > Markeer wijzigingen in kolom, zie screenshot:

2. In het dialoogvenster Markeer wijzigingen in kolom , voer je de volgende bewerkingen uit:

• Selecteer het gegevensbereik dat je wilt gebruiken, en kies dan de sleutelkolom waarop je blanco rijen wilt invoegen;
• Vink vervolgens de optie Blanco rij aan in de sectie Opties, en voer het aantal blanco rijen in dat je wilt invoegen.

3. Klik dan op de knop Ok, en het specifieke aantal blanco rijen is ingevoegd in de gegevens als de celwaarde verandert, zie screenshots:

Klik om Kutools voor Excel te downloaden en gratis uit te proberen!

Blanco rijen invoegen wanneer de waarde verandert met hulptabellen

Met de hulptabellen kun je eerst een formule invoeren, en vervolgens de functie Zoeken en vervangen toepassen, en tot slot de blanco rijen tussen de veranderende waarden invoegen. Doe het volgende:

1. Voer in een lege cel C3 deze formule in =A3=A2, en in cel D4 voer je deze formule in =A4=A3, zie screenshot:

2. Selecteer dan C3:D4, en sleep de vulgreep over het bereik waarop je deze formules wilt toepassen, en je krijgt Waar of Onwaar in de cellen, zie screenshot:

3. Druk dan op Ctrl + F om het dialoogvenster Zoeken en vervangen te openen, in het pop-upvenster voer je ONWAAR in het tekstvak Zoeken naar onder het tabblad Zoeken, en klik op de knop Opties om dit dialoogvenster uit te vouwen, en kies Waarden uit de vervolgkeuzelijst Zoeken in, zie screenshot:

4. Klik op de knop Alles zoeken, en druk dan op Ctrl + A om alle zoekresultaten te selecteren, alle ONWAAR-cellen zijn meteen geselecteerd, zie screenshot:

6. Sluit het dialoogvenster Zoeken en vervangen, in de volgende stap kun je blanco rijen invoegen door te klikken op Start > Invoegen > Bladrijen invoegen, en blanco rijen zijn ingevoegd in de gegevens wanneer de waarde verandert op basis van kolom A, zie screenshots:

7. Ten slotte kun je de hulptabellen C en D verwijderen indien nodig.

Blanco rijen invoegen wanneer de waarde verandert met VBA-code

Als je moe bent van het gebruik van de bovenstaande methoden, hier is ook een code die je kan helpen om blanco rijen tussen de gewijzigde waarden in één keer in te voegen.

1. Houd de toetsen ALT + F11 ingedrukt om het venster Microsoft Visual Basic for Applications te openen.

2. Klik op Invoegen > Module, en plak de volgende code in het Modulevenster.

VBA-code: Blanco rijen invoegen wanneer de waarde verandert

Sub InsertRowsAtValueChange()
'Update by Extendoffice
Dim Rng As Range
Dim WorkRng As Range
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
Application.ScreenUpdating = False
For i = WorkRng.Rows.Count To 2 Step -1
    If WorkRng.Cells(i, 1).Value <> WorkRng.Cells(i - 1, 1).Value Then
        WorkRng.Cells(i, 1).EntireRow.Insert
    End If
Next
Application.ScreenUpdating = True
End Sub

3. Druk dan op de toets F5 om deze code uit te voeren, en er verschijnt een promptvenster waarin je één kolom gegevens selecteert waarvoor je blanco rijen wilt invoegen wanneer de waarde verandert waarop je baseert, zie screenshot:

4. Klik dan op OK, de blanco rijen zijn ingevoegd tussen de gegevens wanneer de waarde verandert op basis van kolom A.