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.412  keer gedownload.

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".

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.

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.

Figuur 4.

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

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!