Most Valuable Professional


View Jan Karel Pieterse's profile on LinkedIn subscribe to rss feed
Subscribe in a reader

Subscribe to our mailing list

* indicates required

Audit !!!

Probeer onze RefTreeAnalyser
de beste Excel formule auditing tool.

Cursussen

Excel VBA Masterclass (Engels)
Excel VBA voor Financials

Third party tools

Speed up your file

FastExcel
The best tool to optimise your Excel model!

Repair your file

Stellar Phoenix Excel Repair
Best tool to repair corrupt Excel sheets and objects
Home > Nederlandse site > Artikelen > Excel data tabel recept
This page in English

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, 102 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::

En de uitvoer gegevens:

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!


Vragen, suggesties en opmerkingen

Al het commentaar over deze pagina:


Comment by: toby wissel (8/3/2009 8:40:45 AM)

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

 


Comment by: Jan Karel Pieterse (9/7/2009 8:55:01 AM)

Hallo Toby,

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

 


Comment by: nicky (3/19/2015 9:57:08 PM)

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.

 


Comment by: Jan Karel Pieterse (3/20/2015 1:43:28 PM)

Hoi Nicky,

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

 


Comment by: Celine (9/28/2016 4:26:46 PM)

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

 


Comment by: Jan Karel Pieterse (9/28/2016 5:53:24 PM)

Hoi Celine,

Goed punt, ik ga het bestand eens opvragen.

 


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: www.eileenslounge.com.

Uw naam (verplicht veld):

Uw e-mail adres (Niet verplicht, dit adres wordt niet getoond)

Uw verzoek of commentaar:

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