Data-invoer in Excel
- 27/12/2022
 | Voorbeeldbestanden bij dit artikel: |
Hier is na een tijdje oponthoud nog eens een nieuw blog artikel. Fijne Kersttijd voor iedereen!
Inleiding
In Excel gaat er onvermijdelijk data-invoer plaats vinden, soms meer, soms minder. Dit kan gepaard gaan met zowel tijdrovende manuele acties, als de kans op fouten zoals schrijffouten. Wat zijn onze opties en hoe doen we best data-invoer in Excel ? Hieronder zet ik een aantal opties uit, van traditioneel tot de laatste nieuwe Excel functies.
Het idee is dat voor (een gedeelte van) de data-invoer we ofwel moeten kiezen uit een lijst, dan wel nieuwe invoer toelaten. De casus is als volgt. Iemand gaat in de natuur observaties doen van vogelsoorten. Men houdt de datum bij en ook de naam van de gespotte vogelsoort:
Als het om vele waarnemingen gaat is dat nogal lastig, tijdsintensief en foutgevoelig. Zeker als het om de data-invoer van een heel team aan observatoren gaat. Gelukkig bestaan er lijsten waaruit men reeds kan kiezen (zoals bij Wikipedia):
Dan kan je die lijst gebruiken in Excel om te selecteren en de data-invoer te versnellen en vergemakkelijken. Je kopieert de lijst naar Excel, kuist die wat op en past data validatie toe. Je selecteert best eerst alle 1.009 cellen/vogelsoorten en geeft het bereik een naam zoals "vogelsoorten". Vervolgens pas je die naam toe in de data validatie:
Kende je deze optie reeds ? Zelfs zonder een data validatie lijst kan je selecteren uit andere invoer, op voorwaarde dat de invoer gebeurde in dezelfde kolom en er geen lege rijen zijn:
Je krijgt dan een dropdown lijst met enkel de opties die je reeds invoerde. De sneltoetscombinatie is Alt + pijl omlaag, wat heus niet zo slecht gevonden is; de lijst klapt zich als het ware naar onder uit en dat wordt gesymboliseerd door de pijl naar beneden. Merk op dat Excel de titel "Waarneming" niet in de lijst voorziet: slim ! Merk ook op dat de lijst alfabetisch gerangschikte opties toont: ook handig.
Alt + pijl omlaag werkt ook prima als er lege rijen zijn (wat ik trouwens afraad !) en er data validatie in de cel gedefinieerd staat. In dat geval gaat de lijst van de data validatie getoond worden, en niet de "unieke waarden uit de omliggende rijen in dezelfde kolom".
Zelf ben ik echter niet zo een fan van die dropdown lijstjes. Vaak grijp je dan instinctief naar de muis om een selectie te maken (behalve bij Alt + pijl omlaag) maar dan nog. Het vertraagt het werk bij grotere hoeveelheden data-invoer.
Daarom ben ik gewoon geneigd om te typen, Excel zal wel aanvullen als hij herkent wat er getypt wordt - als het reeds eerder voorkwam in de tabel. Zo typ ik in cel B6 simpelweg sc en Excel vult aan voor mij. Ik kan dan bevestigen of gewoon verder typen als ik de Scharrelaar niet wens te gebruiken:
Een truc
Als je met andere woorden simpelweg Excel wil laten aanvullen en zelf enkel de eerste letters wil typen, dan kan je ook het volgende doen. Je voegt een duizendtal rijen in boven de tabel en plakt de vogelsoorten:
Op die manier is de data-invoer snel en handig want alles is al eens voorgekomen in de tabel. Klopt, we spelen een beetje vals, maar het doel heiligt de middelen hier! Die hulpcellen aan de bovenkant van de tabel ga je verbergen en geen haan kraait ernaar. Heel belangrijk: zorg dat er geen lege cellen/rijen zitten tussen de bovenste tabel en de echte invoertabel. Anders stokt het mechanisme.
Let wel, dit mechanisme is beperkt tot pakweg 1.000 rijen. Voor de hele lange keuzelijsten zal dit geen oplossing bieden. Tevens werkt dit mechanism niet wanneer de data-invoer gebeurt in een tabel (ListObject). Het aanvullen van de invoer pikt de items boven de tabel niet op. Heel jammer !
Hiermee heb ik echter nog niks nieuws gezegd, deze truc is bekend in Excel middens. Wat als er toch lege cellen zouden zijn ? Vul ze gewoon op met een weglatingsteken en een spatie:
En kijk, je hebt weer een aaneensluitend bereik ! Nog een stapje verder. Excel MVP Randy Austin ontdekte dit mechanisme: de cellen B1011:B1013 zijn effectief leeg. Maar er loopt een ononderbroken spoor van niet-lege cellen richting de invoertabel. Dat is voldoende voor Excel ! Een bijzonder fenomeen maar het werkt wel.
Andere invoer - nieuwe technieken
Tijd om nu zelf ook wat nieuws toe te voegen aan de Excel community, want het bovenstaande kennen we ondertussen. Wat als de lijst met vogelsoorten niet bestaat en je wil toch eerdere invoer hergebruiken om nieuwe invoer te vergemakkelijken ? De eerdere invoer bestaat onder deze vorm: niet in een tabel maar komma-gescheiden waarden op datum. Kunnen we hier ook mee aan de slag ? Ja !
Je kan (nog niet zo heel lang) in Excel:
- dynamische matrixformules gebruiken
- celinhouden splitsen op karakter(s).
- variabelen gebruiken in formules.
- Lambda functies gebruiken.
- bereiken groter of kleiner maken met een formule.
- enzovoort.
Een eerste formule is deze (om één en ander chronologisch op te bouwen):
=LET( data; Rapport!B2:B4; sep; G2; output; ALS.FOUT( SORTEREN( UNIEK( SPATIES.WISSEN( TEKST.SPLITSEN( TEKST.COMBINEREN( sep; ; data ); ; sep )))); G3 ); output )
De LET functie laat toe om variabelen te definiëren. Hier gaat het om "data", "sep" en "output". "data" en "sep" zijn simpele verwijzingen naar andere cellen, "output" berekent ook effectief iets. "output" gaat nl. de celinhouden uit B2:B4 splitsen op de separator (", "). Vervolgens nemen we alle unieke items, zonder overtollige spaties, sorteren we die lijst en zetten we dat in cel N1. Het resultaat, voor de gegeven invoer op het andere tabblad, is cellen N1:N10. Merk op hoe dat lijstje van zelf uitbreidt en inkrimpt met het aantal unieke vogelsoorten in de broncellen. Er verschijnt een blauwe rand rondom het bereik N1:N10 om aan te geven dat het een dynamische matrixformule is. De cel N1 bevat de formule, de cellen N2:N10 bevatten geen formule maar krijgen wel de "gespilde" resultaten uit bovenliggende formule mee. Cel G3 is leeg en komt dadelijk aan bod. Na het definiëren van 3 variabelen in de LET functie is "output" als laatste argument binnen de functie hetgeen het resultaat van de formule wordt en in de cellen komt.
Dat is al heel mooi ! Maar we willen verder gaan op het thema van dit artikel: data-invoer vlotter en gebruiksvriendelijker laten verlopen. We willen namelijk het lijstje van (nu) 10 verschillende vogelsoorten aanvullen in dezelfde kolom met nieuwe data-invoer. Maar we weten dat we geen lege rijen mogen gebruiken of het mechanisme om aan te vullen vervalt. Anderzijds willen we ook niet regelmatig extra nieuwe rijen toevoegen om het gegenereerde lijstje meer plaats te gunnen.
Mijn oplossing
Wat denk je van het lijstje ZELF door Excel formules te laten uitbreiden ? Om het concreet te maken: data-invoer gebeurt vanaf rij 25. M.a.w. een titelrij in rij 24 zorgt ervoor dat we 23 unieke vogelsoorten reeds kunnen oplijsten. We hebben er momenteel 10, dan resten er nog 13 lege cellen. Laten we die lege cellen opvullen met iets zodat ze niet meer leeg zijn, en het automatisch aanvullen van ingetypte tekst weer kan gebeuren.
Dat kan je hierboven zien. De dynamische matrixformule loopt nu door tot en met rij 24, dat hebben zo ingesteld in cel G3, de variabele "padding_char". Kies gerust eigen variabelen naar keuze ! De formule is dan als volgt:
=LET( data; Rapport!B2:B4; sep_char; G1; starting_row; G3; padding_char; G2; uniques; ALS.FOUT( SORTEREN( UNIEK( SPATIES.WISSEN( TEKST.SPLITSEN( TEKST.COMBINEREN( sep_char; ; data ); ; sep_char )))); padding_char ); output; ALS( AANTALARG( uniques ) >= starting_row; "Verplaats aub de start_rij " & starting_row & " naar rij " & AANTALARG( uniques ) + 1 & " of lager"; UITBREIDEN( uniques; starting_row - 1; ; padding_char )); output )
De aandachtige lezer merkt op dat ik rekening houd met de situatie waarbij de start_rij (24) "te hoog" is, anders gezegd, te weinig plaats voor het aantal unieke vogelsoorten. In dit geval is er een melding in de cel dat die startrij omlaag verplaatst moet worden. De unieke vogelsoorten worden dan niet getoond, wel de melding aan de gebruiker.
Lambda formules
De laatste stap in het rijtje, om het allemaal af te maken, is een heuse Lambda functie. Daarbij definiëren we een nieuwe functie zodat die overal in het Excel bestand toegepast kan worden. Alsof die nieuwe functie altijd reeds in Excel bestaan heeft. De functie kan argumenten hebben (inclusief optionele argumenten) en gelijkt sterk op wat we reeds hadden met de LET functie:
Kijk zeker in het voorbeeldbestand over hoe dit alles in zijn werk gaat. Dit is zeer veelzijdig en vervangt zowel VBA code als ingebouwde, lastige, formules met hulpcellen/hulpkolommen. Lambda functies kunnen ook recursief zijn.
Data validatie lijst
De functies die unieke items teruggeven (zonder aanvulling) zou je in een cel kunnen huizen. De resultaten zullen doorlopen in de cellen eronder. Dit dynamisch bereik zou je een naam kunnen geven, en die naam kan gebruikt worden in een simpele Data validatie lijst. Bijvoorbeeld: =Broncellen wat dan verwijst naar: =T1# Hier is de hash tag # de zogenaamde spill rnage operator. T1# verwijst naar het dynamische bereik wat start in cel T1.
Ik liet VBA vallen. Inderdaad, met VBA kan je de data-invoer ook vergemakkelijken en versnellen. Denk maar aan een zogenaamd Worksheet_SelectionChange event. Of een gewone macro procedure. Hiermee kan je ook ver gaan en het kost niet eens veel code en inspanning. VBA laat toe om heel uitgebreide gebruikersformulieren op te zetten met dito programmering. PowerQuery kan ook gebruikt worden in plaats van de Lambda functie, als je dat wil. Vergeet dan wel niet om regelmatig de output te verversen.
Tot slot vermeld ik nog de FILTER functie. Deze functie laat je toe om een dynamische matrix te creëren met alle vogelsoorten waarvan de naam matcht met *gele*. Dit lijstje kan de gebruiker dan helpen voor gele vogelsoorten.
Nuttige sneltoetscombinaties
Wat betreft data-invoer gebruik ik zelf heel vaak:
- Alt + pijl omlaag: kies een item uit de lijst
- Ctrl + D: kopieer de bovenliggende cel(len) naar de geselecteerde cel(len). De kopieeractie doet alle celeigenschappen. De richting is omlaag (D van Down).
- Ctrl + ": kopieer de celwaarde van de bovenliggende cel naar de geselecteerde cel. De kopieeractie transfereert enkel de celtekst (of het resultaat van de formule).
- Ctrl + R: gelijkaardig aan de variant met D maar de richting is Rechts.
- Ctrl + Enter: selecteer meerdere cellen, voer een waarde of formule in, en druk dan deze sneltoetscombinatie om Excel ineens alle cellen te laten vullen.
