Vaak kunt u een Excel-gegevensset tegenkomen waarin slechts één cel is gevuld met gegevens en de cellen eronder leeg zijn tot de volgende waarde.
Zoals hieronder getoond:
Hoewel dit formaat voor sommige mensen werkt, is het probleem met dit soort gegevens dat je het niet kunt gebruiken om draaitabellen te maken of in berekeningen te gebruiken.
Het is gemakkelijk te repareren!
In deze tutorial laat ik je zien hoe:Vul snel cellen tot de volgende vulwaarde in Excel.
U kunt dit eenvoudig doen met behulp van eenvoudige Go-To speciale dialoogtechnieken, VBA of Power Query.
Dus laten we beginnen!
Inhoud
Methode 1 – Vul in met de Ga naar speciaal + formule
Stel je hebt een dataset zoals hieronder weergegeven, en je wilt de gegevens in de kolommen A en B invullen.
In kolom B is het doel om de "printer" te vullen tot de laatste lege cel eronder, dan wanneer de "scanner" start, vul dan de "scanner" in de cel eronder in totdat de cel leeg is.
De onderstaande stappen gebruiken ga naar speciaal om alle lege cellen te selecteren en in te vullen met een eenvoudige formule:
- Selecteer de gegevens die u wilt invullen (A1:D21 in ons voorbeeld)
- Ga naar het tabblad "Home"
- Klik in de bewerkingsgroep op het pictogram Zoeken en selecteren (hierdoor worden meer opties weergegeven in het vervolgkeuzemenu)
- Klik op de "Go" optie
- Klik in het dialoogvenster Ga naar dat wordt geopend op de knop Speciaal.Hierdoor wordt het dialoogvenster "Ga naar speciaal" geopend
- Klik in het dialoogvenster Ga naar speciaal op Leeg
- Klik OK
Met de bovenstaande stappen worden alle lege cellen in de gegevensset geselecteerd (hieronder weergegeven).
Van de geselecteerde lege cellen zult u merken dat één cel helderder is dan de rest van de selectie.Deze cel is de actieve cel waarin we de formule willen invoeren.
Maak je geen zorgen over de positie van de cel in de selectie, onze methode zal in ieder geval werken.
Dit zijn nu de stappen om de gegevens in de geselecteerde lege cellen in te vullen:
- Klik op het gelijkteken (=) op uw toetsenbord.Dit zal een gelijkteken invoegen in de actieve cel
- Druk op de pijl omhoog.Hiermee wordt de celverwijzing van de cel boven de actieve cel ingevoegd.In ons geval is B3 bijvoorbeeld de actieve cel en wanneer we deze twee stappen uitvoeren, wordt =B2 in de cel ingevoerd
- Houd de Control-toets ingedrukt en druk op Enter
De bovenstaande stappen zullen automatisch alle lege cellen invoegen met de bovenstaande waarden.
Hoewel dit misschien te veel stappen lijken, kunt u, als u het eenmaal onder de knie heeft, in enkele seconden snel gegevens in Excel invullen.
Nu zijn er nog twee dingen waar u op moet letten bij het gebruik van deze methode.
Formule naar waarde converteren
De eerste is om ervoor te zorgen dat je formules naar waarden converteert (zodat je statische waarden hebt en het niet verknoeit wanneer je de gegevens in de toekomst wijzigt).
Datumnotatie wijzigen
Als u datums in uw gegevens gebruikt (zoals ik deed in mijn voorbeeldgegevens), zult u merken dat de ingevulde waarden in de datumkolom getallen zijn in plaats van datums.
Als de uitvoer in de datumkolom de gewenste datumnotatie heeft, dan zit je goed en hoef je verder niets te doen.
Maar als die datums niet in het juiste formaat staan, is finetuning vereist.Hoewel u de juiste waarde hebt, hoeft u alleen de opmaak te wijzigen zodat deze als een datum in de cel wordt weergegeven.
Hier zijn de stappen om dit te doen:
- Selecteer kolommen met datums
- Klik op het tabblad Start
- Klik in de groep Getallen op het vervolgkeuzemenu Opmaak en selecteer een datumnotatie.
Als je deze opvulling van tijd tot tijd moet doen, raad ik deze Go-To speciale en formuletechniek aan.
Hoewel het een paar stappen heeft, is het eenvoudig en krijgt u resultaten in de dataset.
Maar als je dit vaak moet doen, raad ik je aan om te kijken naar VBA en de Power Query-methode die hierna wordt beschreven
Methode 2 - Vul in met eenvoudige VBA-code
U kunt een zeer eenvoudige VBA-macrocode gebruiken om cellen in Excel snel te vullen tot de laatste lege waarde.
U hoeft slechts één keer VBA-code aan een bestand toe te voegen, dan kunt u die code gemakkelijk meerdere keren hergebruiken in dezelfde werkmap of zelfs in meerdere werkmappen op uw systeem.
Hier is de VBA-code die door elke cel in de selectie wordt herhaald en lege cellen invult:
Sub FillDown() Voor elke cel In selectie If cell = "" Dan cel.FillDown End If Next End Sub
De bovenstaande code gebruikt een For-lus om door elke cel in de selectie te lopen.
In de For-lus gebruik ik een If-Then-voorwaarde om te controleren of de cel leeg is.
Als de cel leeg is, wordt deze gevuld met de waarde uit de cel erboven, zo niet, dan negeert de for-lus de cel en gaat naar de volgende cel.
Nu je de VBA-code hebt, wil ik je laten zien waar je deze code in Excel kunt plaatsen:
- Selecteer de gegevens om in te vullen
- Klik op het tabblad voor ontwikkelaars
- Klik op het Visual Basic-pictogram (of u kunt de sneltoets ALT+F11 gebruiken).Dit opent de Visual Basic-editor in Excel
- In de Visual Basic-editor aan de linkerkant heb je de Projectverkenner.Als u het niet ziet, klikt u op de optie Weergeven in het menu en vervolgens op Projectverkenner
- Als u meerdere Excel-werkmappen hebt geopend, geeft Project Explorer alle namen van de werkmappen weer.Zoek de naam van de werkmap waarin u de gegevens hebt.
- Klik met de rechtermuisknop op een object in de werkmap, ga naar Invoegen en klik op Module.Hiermee wordt een nieuwe module voor deze werkmap ingevoegd
- Dubbelklik op de "Module" die u zojuist in de bovenstaande stappen hebt ingevoegd.Het opent het codevenster voor die module
- Kopieer en plak de VBA-code in het codevenster
- Plaats de cursor ergens in de code en voer de macrocode uit door op de groene knop in de werkbalk te klikken of door de sneltoets F5 te gebruiken
De bovenstaande stappen voeren de VBA-code uit en uw gegevens worden ingevuld.
Als u deze VBA-code in de toekomst opnieuw wilt gebruiken, moet u dit bestand opslaan als een Excel-werkmap met macro's (met de extensie .XLSM)
Een ander ding dat u kunt doen, is deze macro toevoegen aan uw werkbalk Snelle toegang, die altijd zichtbaar is en u kunt deze macro met slechts één klik openen (in de werkmap met de code in de backend).
Dus de volgende keer dat u gegevens heeft die u moet invullen, kunt u gewoon een selectie maken en op de macroknop in de werkbalk Snelle toegang klikken.
U kunt deze macro ook aan uw persoonlijke macrowerkmap toevoegen en deze vervolgens in elke werkmap op uw systeem gebruiken.
Klik hier om meer te lezen over uw persoonlijke macrowerkmap en hoe u er code aan kunt toevoegen.
Methode 3 - Invullen met Power Query
Power QueryHeeft ingebouwde functionaliteit waarmee u het met een enkele klik kunt invullen.
Het wordt aanbevolen wanneer u Power Query nog steeds gebruikt om gegevens te transformeren of gegevens uit meerdere werkbladen of meerdere werkmappen te combineren.
Als onderdeel van een bestaande workflow kunt u snel lege cellen vullen met de optie Vullen in Power Query.
Als u Power Query wilt gebruiken, wordt aanbevolen dat uw gegevens in Excel-tabelindeling zijn.Als u de gegevens niet naar een Excel-tabel kunt converteren, moet u een benoemd bereik voor de gegevens maken en dat benoemde bereik vervolgens gebruiken in Power Query.
Hieronder heb ik een dataset die is geconverteerd naar een Excel-tabel.U kunt dit doen door de dataset te selecteren, naar het tabblad Invoegen te gaan en op het pictogram Tabel te klikken.
Dit zijn de stappen om Power Query te gebruiken om de gegevens in te vullen tot de volgende waarde:
- Selecteer een cel in de dataset
- Klik op het tabblad Gegevens
- Klik in de groep Gegevens ophalen en transformeren op Van werkblad.Hiermee wordt de Power Query-editor geopend.Merk op dat lege cellen de waarde "null" in Power Query weergeven
- Selecteer in de Power Query-editor de kolom die u wilt invullen.Houd hiervoor de Control-toets ingedrukt en klik op de kop van de kolom die u wilt selecteren.In ons voorbeeld zijn dit de datum- en productkolommen.
- Klik met de rechtermuisknop op een geselecteerde titel
- Ga naar de optie Vullen en klik op Omlaag.Hiermee worden alle lege cellen gevuld met gegevens
- Klik op het tabblad Bestand en klik vervolgens op Sluiten en laden.Hiermee wordt een nieuw werkblad met de resultatentabel in de werkmap ingevoegd
Hoewel deze benadering misschien overdreven klinkt, is een van de grote voordelen van het gebruik van Power Query dat u de resulterende gegevens snel kunt bijwerken wanneer de oorspronkelijke gegevens worden gewijzigd.
Als u bijvoorbeeld meer records aan de oorspronkelijke gegevens toevoegt, hoeft u niet al het bovenstaande opnieuw te doen.U kunt gewoon met de rechtermuisknop op de resulterende tabel klikken en op vernieuwen klikken.
Hoewel de Power Query-methode goed werkt, kunt u deze het beste gebruiken als u Power Query al in uw workflow gebruikt.
Als je maar één dataset hebt met lege cellen om in te vullen, is het handiger om de speciale methode Ga naar of de VBA-methode (hierboven beschreven) te gebruiken.
U kunt deze drie eenvoudige methoden dus gebruiken om:Vul lege cellen in tot de volgende waarde in Excel.
Ik hoop dat je deze tutorial nuttig vond.
