Bekijk video - Invullen in Excel
Vaak kunt u een gegevensset tegenkomen waarbij slechts één cel is gevuld met gegevens en de cellen eronder leeg zijn tot de volgende waarde.
Iets zoals hieronder weergegeven:
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.
En dit heeft een gemakkelijke oplossing!
In deze tutorial laat ik je zien hoe je snel cellen in Excel invullen tot de volgende gevulde waarde.
U kunt dit eenvoudig doen met behulp van een eenvoudige Go-To speciale dialoogvenstertechniek, VBA of Power Query.
Dus laten we beginnen!
Methode 1 - Invullen met Ga naar speciaal + formule
Stel je hebt een dataset zoals hieronder weergegeven en je wilt gegevens invullen in kolom A en kolom B.
In kolom B is het de bedoeling om 'Printer' te vullen tot de laatste lege cel eronder, en wanneer 'Scanner' start, vul dan 'Scanner' in de onderstaande cellen totdat de cellen leeg zijn.
Ga onder de te gebruiken stappen naar speciaal om alle lege cellen te selecteren en vul deze in met een eenvoudige formule:
- Selecteer de gegevens die u wilt invullen (A1:D21 in ons voorbeeld)
- Ga naar het tabblad 'Home'
- Klik in de groep Bewerken op het pictogram 'Zoeken en selecteren' (dit toont wat meer opties in de vervolgkeuzelijst)
- Klik op de optie 'Ga naar'
- 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 Blanks
- Klik OK
Met de bovenstaande stappen worden alle lege cellen in de gegevensset geselecteerd (zoals hieronder weergegeven).
In de lege cellen die zijn geselecteerd, zou je merken dat één cel lichter is dan de rest van de selectie. Deze cel is de actieve cel waar we een formule gaan invoeren.
Maak je geen zorgen over waar de cel zich in de selectie bevindt, want onze methode zou in elk geval werken.
Hieronder vindt u de stappen om de gegevens in de geselecteerde lege cellen in te vullen:
- Druk op de gelijk-aan (=) toets op uw toetsenbord. Dit zal een gelijk-naar-teken 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 de Enter-toets
De bovenstaande stappen zouden automatisch alle lege cellen invoegen met de waarde erboven.
Hoewel dit misschien te veel stappen lijken, kunt u, als u deze methode eenmaal onder de knie hebt, binnen enkele seconden snel gegevens in Excel invullen.
Nu zijn er nog twee dingen waar u op moet letten bij het gebruik van deze methode.
Formules converteren naar waarden
De eerste is om ervoor te zorgen dat je formules naar waarden converteert (zodat je statische waarden hebt en dingen niet in de war raken als je in de toekomst gegevens wijzigt).
Hieronder vindt u een video die u laat zien hoe u formules snel kunt converteren naar waarden in Excel.
De datumnotatie wijzigen
Als u datums in uw gegevens gebruikt (zoals ik in de voorbeeldgegevens ben), zou u merken dat de ingevulde waarden in de datumkolom getallen zijn en geen datums.
Als de uitvoer in de datumkolom de gewenste datumnotatie heeft, zit je goed en hoef je verder niets te doen.
Maar als deze niet in het juiste datumformaat staan, moet u een kleine aanpassing maken. Hoewel u de juiste waarden hebt, hoeft u alleen het formaat te wijzigen zodat het als een datum in de cel wordt weergegeven.
Hieronder staan de stappen om dit te doen:
- Selecteer de kolom met de datums
- Klik op het tabblad Start
- Klik in de groep Getal op de vervolgkeuzelijst Formaat en selecteer vervolgens de datumnotatie.
Als je deze fill-down af en toe moet doen, raad ik je aan om deze speciale Go-To- en formuletechniek te gebruiken.
Hoewel het een paar stappen heeft, is het eenvoudig en krijgt u het resultaat direct in uw dataset.
Maar als u dit vaak moet doen, raad ik u aan naar de VBA- en de Power Query-methoden te kijken die hierna worden behandeld
Methode 2 - Invullen met een eenvoudige VBA-code
U kunt een heel eenvoudige VBA-macrocode gebruiken om cellen in Excel snel te vullen tot de laatste lege waarde.
U hoeft de VBA-code maar één keer aan het bestand toe te voegen en vervolgens kunt u de code eenvoudig meerdere keren hergebruiken in dezelfde werkmap of zelfs meerdere werkmappen op uw systeem.
Hieronder staat de VBA-code die door elke cel in de selectie gaat en alle lege cellen vult:
'Deze VBA-code is gemaakt door Sumit Bansal van TrumpExcel.com Sub FillDown() For Each cell In Selection If cell = "" Then cell.FillDown End If Next End Sub
De bovenstaande code gebruikt een For-lus om door elke cel in de selectie te gaan.
Binnen de For-lus heb ik een If-Then-voorwaarde gebruikt die controleert of de cel leeg is of niet.
Als de cel leeg is, wordt deze gevuld met de waarde uit de cel erboven, en als deze niet leeg is, negeert de for-lus de cel en gaat naar de volgende.
Nu je de VBA-code hebt, wil ik je laten zien waar je deze code in Excel kunt plaatsen:
- Selecteer de gegevens waar u wilt invullen
- Klik op het tabblad 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, zou je de Project Explorer hebben. Als je het niet ziet, klik dan op de optie 'Bekijken' in het menu en klik vervolgens op Projectverkenner
- Als u meerdere Excel-werkmappen hebt geopend, toont Project Explorer u alle namen van de werkmappen. Zoek de naam van de werkmap waar u de gegevens hebt.
- Klik met de rechtermuisknop op een van de objecten in de werkmap, ga naar Invoegen en klik vervolgens op Module. Hiermee wordt een nieuwe module voor die werkmap ingevoegd
- Dubbelklik op de 'Module' die u zojuist in de bovenstaande stap 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 vervolgens de macrocode uit door op de groene knop in de werkbalk te klikken of door de sneltoets F5 te gebruiken
De bovenstaande stappen zouden de VBA-code uitvoeren en uw gegevens zouden 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 hebt toegang tot deze macro met een enkele klik (in de werkmap waar u de code in de backend hebt).
Dus de volgende keer dat u gegevens heeft die u moet invullen, hoeft u alleen maar de selectie te maken en vervolgens op de macroknop in de werkbalk Snelle toegang te klikken.
U kunt deze macro ook toevoegen aan de persoonlijke macrowerkmap en deze vervolgens in elke werkmap op uw systeem gebruiken.
Klik hier om meer te lezen over persoonlijke macrowerkmappen en hoe u er code aan kunt toevoegen.
Methode 3 - Opvullen met Power Query
Power Query heeft een ingebouwde functionaliteit waarmee u met een enkele klik kunt invullen.
Het gebruik van Power Query wordt aanbevolen wanneer u het toch gebruikt om uw gegevens te transformeren of gegevens uit meerdere werkbladen of meerdere werkmappen te combineren.
Als onderdeel van uw bestaande workflow kunt u de invuloptie in Power Query gebruiken om snel de lege cellen te vullen.
Als u Power Query wilt gebruiken, is het raadzaam dat uw gegevens een Excel-tabelindeling hebben. Als u uw 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 de dataset die ik al heb omgezet naar een Excel-tabel. U kunt dat doen door de dataset te selecteren, naar het tabblad Invoegen te gaan en vervolgens op het pictogram Tabel te klikken.
Hieronder de stappen om Power Query te gebruiken om 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 blad'. Hiermee wordt de Power Query-editor geopend. Merk op dat de lege cellen de waarde 'null' zouden tonen in Power Query
- Selecteer in de Power Query-editor de kolommen die u wilt invullen. Houd hiervoor de Control-toets ingedrukt en klik vervolgens op de kop van de kolommen die u wilt selecteren. In ons voorbeeld zijn dit de kolommen Datum en Product.
- Klik met de rechtermuisknop op een van de geselecteerde koppen
- Ga naar de optie 'Vullen' en klik vervolgens op 'Omlaag'. Dit zou de gegevens in alle lege cellen vullen
- Klik op het tabblad Bestand en klik vervolgens op 'Sluiten & laden'. Hiermee wordt een nieuw werkblad in de werkmap ingevoegd met de resulterende tabel
Hoewel deze methode misschien een beetje overdreven klinkt, is een groot voordeel van het gebruik van Power Query dat u de resulterende gegevens snel kunt bijwerken voor het geval uw oorspronkelijke gegevens veranderen.
Als u bijvoorbeeld meer records aan de oorspronkelijke gegevens toevoegt, hoeft u het bovenstaande niet opnieuw te doen. U kunt eenvoudig met de rechtermuisknop op de resulterende tabel klikken en vervolgens 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 alleen een dataset hebt waarin je de lege cellen wilt vullen, dan is het handiger om de speciale methode Ga naar of de VBA-methode (hierboven beschreven) te gebruiken.
Dit zijn dus drie eenvoudige manieren die u kunt gebruiken om vul lege cellen in tot de volgende waarde in Excel.
Ik hoop dat je deze tutorial nuttig vond.
