Batchverwerking: hoe u een bestaand Excel spreadsheet op een groep gegevens kunt toepassen

Een artikel door Niek Otten, Excel MVP.

Inleiding

Het komt soms voor dat u een bestaand Excel spreadsheet op een groep gegevens wilt toepassen in plaats van op slechts één geval (waarvoor het gemaakt was). Bijvoorbeeld: u ontwikkelde een spreadsheet waarin u uw pensioen berekent. Het heeft elf invoervariabelen nodig en produceert acht uitvoer berekeningen. Elke keer dat mij gevraagd werd zoiets te doen kostte het me nogal wat tijd om uit te zoeken hoe dat ook al weer ging. Daarom heb ik dit receptje ontwikkeld. Ik kan het toepassen zonder na te denken over het hoe en waarom; ik weet dat het recept werkt. Niet onbelangrijk: neemt het de beperkingen in aantal invoervariabelen en gebruikte werkbladen geheel weg.Een sterk vereenvoudigd voorbeeld dat we in dit artikeltje verder zullen gebruiken vindt u in Figuur 1. In dit geval staan de berekeningen op één blad, BerekeningsBlad genoemd. U kunt het bestand hier downloaden, 1.300  keer gedownload.

Een pensioenberekening

Figuur 1

Nu wordt u plotseling gevraagd om die berekening uit te voeren voor 500 werknemers! De invoergegevens zijn gedownload uit een database in een Excel werkblad (Figuur 2), op het blad GegevensBlad. We laten het nu even bij vijf regels, geen 500! Deze twee werkbladen vindt u in het bestand "Batchverwerking Met Tabel functie.xlsx".

 Een pensioenberekening

Figuur 2

U zou natuurlijk VBA code (Excel’s programmeertaal) kunnen (laten) schrijven om dat te doen. Maar veel mensen hebben nogal wat aarzelingen bij het ontwikkelen in VBA en soms is het gewoon niet toegestaan, bijvoorbeeld op een bedrijfs-PC. Gelukkig is er een oplossing die geen VBA nodig heeft.

Helaas spreekt Excel’s ingebouwde mogelijkheid om dat te doen, het Data|Tabel commando, niet echt voor zichzelf en wordt het in de Help-teksten niet erg helder beschreven. Ook kent het een aantal beperkingen; alles moet zich op één werkblad afspelen en het maximale aantal invoervariabelen is slechts twee(!).

Hoe werkt het

Het lijken misschien heel wat stappen, maar het is niet moeilijk, u kunt het in minder dan tien minuten uitvoeren.

Stap 1.

Maak een kopie van uw originele Excel Map en gebruik die om mee te werken in de volgende stappen.

Stap 2.

Stel vast hoe de regels van uw tabel er uit moeten zien. Alle gegevens voor één database record moeten straks op één rij van het werkblad staan. Waarschijnlijk zult u enkele beschrijvende gegevens uit de database over willen nemen (personeelnummer etc.), maar u hebt in ieder geval alle invoervariabelen nodig. En natuurlijk de uitvoergegevens. Maak een lijstje van die gegevens. In ons geval de invoer gegevens::

  • Employeenummer
  • Naam
  • Geboortedatum
  • Sexe
  • Burgerlijke staat
  • Datum indiensttreding
  • Datum deelname pensioenfonds
  • Parttime percentage
  • Franchise
  • Salaris

En de uitvoer gegevens:

  • Pensioensalaris
  • Pensioengrondslag
  • Ouderdomspensioen
  • Partnerpensioen
  • Wezenpensioen
  • Pensioendatum
  • Opgebouwde duur
  • Toekomstige duur

Stap 3.

Definieer namen voor de invoerkolommen in uw database tabel door middel van het Invoegen|Naam|Definiëren commando. Als de kolommen kolomkoppen hebben (met een naam of beschrijving van de kolom), neem die dan niet in de definitie op. Dus de eerste regel die verwerkt moet worden is de eerste regel van uw gedefinieerde naam.

Stap 4.

Voeg een nieuw blad in in uw Map. In het voorbeeld hebben we dat blad BatchVerwerkingsBlad genoemd. Vul kolomkoppen in voor uw invoer en uitvoer (zoals gedefinieerd in stap 2), te beginnen in cel B1. Voer het getal 1 in in cel A2. Voer de volgende formule in in cel B2: =INDEX(Employeenummer;A2). Hier is Employeenummer een voorbeeld van een naam die u in stap 3 gecreëerd hebt. Gebruik dus uw zelf gedefinieerde namen. In cel C2 komt weer een formule: =INDEX(Naam;A2), etc. Doe dit op regel twee voor elk van de invoervariabelen, in ons voorbeeld dus t/m kolom R, Toekomstige duur.

Stap 5.

Laat al uw oorspronkelijke invoer velden verwijzen naar regel 2 van uw zojuist gecreëerde werkblad. In ons voorbeeld krijgt dus cel B6 op het blad BerekeningsBlad de formule: =BatchVerwerkingsBlad!B2 in plaats van een ingevulde waarde. Cel B7 krijgt de formule =BatchVerwerkingsBlad!C2, enzovoort. Doe dit voor alle invoer variabelen.

Stap 6.

Laat de uitvoer gegevens in uw tabel verwijzen naar de oorspronkelijke uitvoer gegevens, ergens in uw Map. Dus de cel K2 op het blad BatchVerwerkingsblad krijgt de formule: =BerekeningsBlad!E6, L6: =BerekeningsBlad!E7, etc. Doe dit voor alle uitvoer gegevens in uw tabel.

Stap 7.

Test dit goed uit. Vul een getal in in cel A2 (dit getal geeft een regel in de tabel aan die behandeld gaat worden) en controleer of alle invoergegevens van die regel correct worden overgenomen en of alle uitvoergegevens netjes in die regel terechtkomen. Het blad ziet er nu uit als in Figuur 3.

De juiste opzet voor de Tabel functie

Figuur 3.

Stap 8.

Te beginnen in cel A2, vul oplopende getallen (1, 2, 3…) in in kolom A, evenveel getallen als er records te behandelen zijn (het aantal gegevensregels op het blad GegevensBlad) zodat naast elke regel een getal (recordnummer) staat. Gebuik daarvoor geen formule: gebruik de vulgreep of het Bewerken|Doorvoeren|Reeks commando of, als het er weinig zijn, typ de nummers handmatig. Voor grote series kunt u eerst een formule gebruiken en dan de getallen met Kopiëren en Plakken speciaal, waarden vervangen door de resultaten van die formule. In ieder geval mag er uiteindelijk geen formule in kolom A staan!

Stap 9.

Selecteer de tabel op het BatchVerwerkingsBlad (Cel A2 t/m de laatste regel en laatste kolom van uw tabel). Kies het commando Data|Tabel. In het dialoogvenster, laat Rij invoercel leeg en vul A2 in in het vak Kolominvoercel; zie Figuur 4.

Het instellen van de tabel

Figuur 4.

Klik op OK en zie hoe uw tabel zich vult met invoergegevens en berekende resultaten, zie Figuur 5.

De resulterende tabel

Figuur 5.

Stap 10.

Zoals u ziet moet u de uitvoerkolommen nog even de juiste opmaak geven; Valuta-opmaak voor de bedragen en Datum-opmaak voor de Pensioendatum. Dat kunt u eenvoudig doen door de eerste regel correct op te maken en die opmaak te kopiëren naar de rest van de tabel; Kopiëren, Plakken speciaal, Opmaak.

Samenvatting.

Voor Batchverwerking hoeft u geen VBA te gebruiken. U hoeft ook uw spreadsheet er niet speciaal voor te ontwerpen. Elk bestaand spreadsheet kan met dit recept omgebouwd worden tot een batchverwerkend systeem. Met de huidige hardware capaciteit kunnen grote bestanden in zeer acceptabele tijden verwerkt worden. Het is even een nauwkeurig werkje, maar.. gewoon het recept volgen en het werkt altijd!


Vragen, suggesties en opmerkingen

Al het commentaar over deze pagina:


Commentaar van: toby wissel (3-8-2009 08:40:45) deeplink naar dit commentaar

is het mogelijk dat als je op blad 1 gegevens zet enkele van die gegevens permanent worden opgeslagen op blad 2 een soort database ?
ik ben hier al maanden me bezig maar ik kom er niet uit, hebben jullie een suggestie

toby wissel


Commentaar van: Jan Karel Pieterse (7-9-2009 08:55:01) deeplink naar dit commentaar

Hallo Toby,

Alles kan met Excel, vooral als je wat VBA gaat inzetten.
Kan je wat meer details geven?


Commentaar van: nicky (19-3-2015 21:57:08) deeplink naar dit commentaar

Ik heb het verhaal nu ongeveer 12 keer doorgenomen en ben een uur verder maar ben geen cm verder. Het woord table woord vaak gebruikt zonder de vermelding naar welk tabblad.
STap 3, 5 , 6 en 7 geven niet aan in welke tablad dit gedaan moet worden.


Commentaar van: Jan Karel Pieterse (20-3-2015 13:43:28) deeplink naar dit commentaar

Hoi Nicky,

Jammer dat het niet lukt. Ik zal Niek Otten vragen om naar je vraag te kijken.


Commentaar van: Celine (28-9-2016 16:26:46) deeplink naar dit commentaar

Hallo,
Dit ziet er wel interessant uit en zou het wel eens willen uittesten. Er wordt hier verwezen naar een excel doc WEKAPensioen.xls maar nergens een link? Het zou nochtans handig zijn om dit erbij te kunnen nemen om stap voor stap te overlopen.

En wat de data table in excel betreft, ik vind die zeer mooi uitgelegd in "Excel in depth" van Bill Jelen. Zijn boeken zijn echte aanraders.

Alvast bedankt
Céline


Commentaar van: Jan Karel Pieterse (28-9-2016 17:53:24) deeplink naar dit commentaar

Hoi Celine,

Goed punt, ik ga het bestand eens opvragen.


Commentaar van: P. Schuitema (4-11-2020 15:21:00) deeplink naar dit commentaar

Als men de eerste 3 of 4 rijen in een excelbestand wil blijven zien tijdens het scrollen wat moet men dan doen om dit mogelijk te maken?


Commentaar van: Jan Karel Pieterse (4-11-2020 16:16:00) deeplink naar dit commentaar

Hoi,

Selecteer dan een cel op rij 4 en kies Beeld, Titels blokkeren. Alle rijen boven die cel en alle kolommen links van die cel staan nu "vast"


Heeft u vragen, suggesties of opmerkingen? Gebruik dan dit formulier.

Mocht uw vraag niet direct relevant zijn voor deze pagina, maar een algemene Excel vraag betreffen, dan adviseer ik om deze hier te stellen: excelexperts.nl/forum/index.php.




Als u VBA code in uw commentaar plaatst, gebruik dan [VB] tags: [VB]Uw code[/VB].