Werken met veel werkbladen

Het benoemen en gebruiken van een werkblad is een zeer nuttig concept in het geval van enkele bekende werkbladen. Er zijn echter gevallen waarin meerdere of meer werkbladen met dezelfde structuur batchgewijs moeten worden verwerkt of gemaakt. Typische gevallen zijn consolidatie of het splitsen van taken.

De bronwerkbladen die moeten worden geconsolideerd, bestaan al en hebben hun bestandsnamen bij de hand. Daarom is het het beste om de processtappen WBook List Start en WBook List Next te gebruiken (onder de categorieën Werkblad en If / Lists op de ProcessToolbar) om dezelfde bewerkingen uit te voeren op meerdere bronwerkbladen.

Onder de werkmaplijstparameter kunnen de werkmappen van de bronwerkbladen worden opgegeven met behulp van:

• jokertekens? en * in bestandsnamen (bijvoorbeeld c:\mijn\map\*.xlsx voor alle bestanden in mijn\map); En
• meerdere mappen of bestanden gescheiden door ; (bijvoorbeeld c:\2018\rapport.xlsx;c:\2019\rapport.xlsx)

De eerste stap is het specificeren van het proces voor één bronwerkblad, net zoals dit wordt gedaan voor één werkbladcasus. Gebruik WSheet Use om het doelconsolidatiewerkblad op te geven en nog een WSheet Use voor het enige bronwerkblad. Zodra het proces actief is, volgt u deze stappen:

1. vervang WSheet Use voor het enige bronwerkblad door WBook List Start voor alle werkbladen aan het begin van het proces (NB: behoud WSheet Use voor het doel!);
2. geef alle werkbladen op in de parameter Werkmaplijst
3. voeg WBook List Next toe aan het einde van de bewerkingen;
4. gebruik de Djeeni name van WBook List Start als bronwerkbladreferentie in alle processtappen in plaats van de Djeeni-naam van WSheet Use voor het enige bronwerkblad
5. vervang de doelcel van cel- en bereikbewerkingen om dit mogelijk te maken
   • rijen toevoegen;
   • rijen invoegen; En
   • het verzamelen van waarden in plaats van dezelfde cel steeds opnieuw te overschrijven. Bekijk deze stappen gedetailleerd in de secties op deze pagina hieronder.

Klaar!

De versie met één werkblad van het consolidatieproces kopieerde enkele (verwerkte) gegevens van het bronwerkblad naar een bepaalde cel/bereik van het doelconsolidatiewerkblad. En in de consolidatiesjabloon is er lege ruimte om dit voor alle werkbladen te doen door dezelfde kopieerbewerking te gebruiken om gegevens toe te voegen. Maar hoe vind je de eerste lege rij/cel? De eerste lege rij/cel is uiteraard één rij/cel verder (onder of rechts) dan de laatste rij/cel met gegevens. De laatste rij met gegevens is eenvoudig te declareren voor kolom A:

#RijEnd

en voor een andere kolom (bijvoorbeeld F):

#Row End|F

Eén toevoegen aan het bovenstaande (om bij de eerste lege rij te komen) is:

[+[#Row End|F]+1]

Laten we aannemen dat de versie van één werkblad de gegevens heeft gekopieerd (via Cell Set of Range Set of Range Copy/Move) naar wsConsolidation!D2. Om de rijen eronder toe te voegen, moet dit worden gewijzigd in wsConsolidation!D[+[#RowEnd|D]+1].

Een andere manier om de consolidatielijst samen te stellen (als de sjabloon alleen een koptekst en geen vaste structuur heeft) is door de nieuwe rijen uit de bronwerkbladen vóór de bestaande in te voegen. Het is niet nodig om de eerste lege rij te berekenen.

Laten we aannemen dat de consolidatielijst begint op rij 3 van het wsConsolidation-werkblad. Dit zijn de wijzigingen in de gegevensverwerkingsstappen om de consolidatie voor meerdere werkbladen te laten werken:

• Als Range Copy/Move wordt gebruikt, is de enige verandering het instellen van de parameter Insert/Overwrite op Insert.
• Als Cell Set of Range Set wordt gebruikt, moet deze processtap worden voorafgegaan door Kolom/rij invoegen* * één rij invoegen vóór rij 4. ==== Waarden accumuleren ==== Consolidatie bestaat enerzijds uit het verzamelen van waarden in lijsten. Aan de andere kant verzamelt het waarden (optellen, middelen enz.) in een cel op het doelwerkblad. Bij de versie met één werkblad wordt dit bereikt door de doelcelwaarde in te stellen met behulp van Cell Set. En voila! Bij de versie met meerdere werkbladen blijft Cell Set behouden. De enige verandering is het gebruik van de vorige celwaarde en de benodigde berekening of Excel-functie om de nieuwe waarde te produceren. Voorbeeld 1: De som van de bronwaarden moet worden berekend. <code> 'Enige versie Cell Set Cell: wsTarget!C4 Waarde: [=wsSource!D5] 'Lijstversie Wbook List Start … Cell Set Cel: wsTarget!C4 Waarde: [+[=wsTarget!C4]+[=wsSource!D5]] … WBook List Next </code> Voorbeeld 2: Het gemiddelde van de bronwaarden moet worden berekend. Hier hebben we een ondersteunende cel nodig om het aantal te middelen waarden te tellen. Deze waarde stellen we vóór de Wbook List in op 0 en nadat de Wbook List gereed is, delen we de opgetelde waarde door het uiteindelijke getal. <code> 'Enige versie Cell Set Cell: wsTarget!C5 'aantal waarden Waarde: 1 Cell Set Cell: wsTarget!C4 'gemiddelde van de enige cel Waarde: [=wsSource!D5] 'Lijstversie Cell Set Cell: wsTarget!C5 'initialiseert Waarde: 0 WBoekenlijst starten … Cell Set Cell: wsTarget!C5 'toenemend aantal waarden Waarde: [+[=wsTarget!C5]+1] Cel set Cell: wsTarget!C4 'alleen optellen Waarde: [+[=wsTarget!C4]+[=wsSource!D5]] … WBook List Next Cell Set Cell: wsTarget!C4 'nu om het gemiddelde te berekenen Waarde: [+[=wsTarget!C4]/[=wsTarget!C5]] </code> ===== Gegevens splitsen van een bronwerkblad naar meerdere doelwerkbladen ===== Een typische taak is het splitsen van een MS Excel-bronwerkblad van een externe bron (dit kan bijvoorbeeld een door de leverancier verstrekte dataset of een IT-systeemrapport zijn) volgens bepaalde criteria. De vraag is: hoe wordt besloten welke doelwerkbladen moeten worden gemaakt? Ze bestaan niet vóór het proces, dus de bestandsnamen zijn niet bekend. Verder kan de set van de aan te maken bestanden elke keer anders zijn op basis van bron- of aanvullende informatie. Djeeni kan op drie manieren doelwerkbladen maken: - Als het aantal doelwerkbladen vast is, kunnen ze worden gespecificeerd in een WBook List; - Als de doelwerkbladen moeten worden aangemaakt met behulp van een aanvullende lijst (bijvoorbeeld een lijst met kostenplaatsen of afdelingen), dan kan deze aanvullende lijst worden gebruikt in een Rijenlijst of Kolompenlijst - Als de doelwerkbladen moeten worden gemaakt met behulp van de informatie uit het bronwerkblad zelf, kan de processtap WSheet Split worden gebruikt Laten we deze manieren in details bekijken. ==== Doelwerkbladen maken in een WBook List ==== Deze aanpak is vergelijkbaar met consolidatie. De doelwerkbladen (en -indien nodig- de bijbehorende werkmappen) worden één voor één gemaakt en verwerkt. Er kan naar het huidige werkblad worden verwezen met de Djeeni name van de WBook List. ==== Doelwerkbladen maken in een rijlijst ==== Vaak omvat de verwerking van een brondataset een aanvullende lijst met enkele masterdata. Het zijn meestal een lijst met kostenplaatsen, afdelingen, contacten en partners. Het proces moet voor elk element in deze aanvullende lijst het relevante deel uit het bronwerkblad halen. In dit geval wordt de verwerking van het bronwerkblad bepaald door de aanvullende lijst. Djeeni gebruikt de processtappen Row List Start … Row List Next om de elementen van de aanvullende lijst één voor één te nemen en de bijbehorende bewerkingen uit te voeren. De daadwerkelijke stappen zijn: - WSheet Use voor het bronwerkblad - WSheet Use voor de aanvullende lijst - Maak eventueel de doelwerkmap aan (met behulp van WBook New) voor het geval alle doelwerkbladen aan één werkmap worden toegevoegd - Row List Start op het aanvullende werkblad - Het maken van de volgende doelwerkmap (WBook New) voor het geval de doelwerkbladen aan verschillende werkmappen worden toegevoegd OF het volgende werkblad toevoegen aan de voordat de Rijenlijst doelwerkmap maakte; - het uitvoeren van de bewerkingen (meestal het filteren van het bronwerkblad en het extraheren van de gefilterde gegevens) met behulp van - de Djeeni name van de Row List om te verwijzen naar de informatie op de aanvullende lijst - de Djeeni-naam van het bronwerkblad om naar de brongegevensset te verwijzen - de Djeeni-naam van het gemaakte/toegevoegde werkblad om naar de doelgegevensset te verwijzen - Row List next om naar het volgende element van de aanvullende lijst te gaan Deze stappen vormen het volgende Djeeni-proces (voor verschillende werkmappen). Merk op hoe de doelwerkmappen een unieke naam krijgen met behulp van de aanvullende lijstinformatie in WBook New. <code> WSheet use Djeeni-naam: wsSourceData 'brongegevensset WSheet use Djeeni-naam: wsDepartments 'aanvullende lijst Row List Start Djeeni-naam: rlDepts Werkblad: wsAfdelingen Rij vanaf: 2 Rij naar: #RowEnd|C WBook Nieuwe Djeeni-naam: wsDepTarget Bestandsnaam: [=rlDepts!D#] 'dept. naam in kolom D van suppl. lijst Type: Doel Werkblad Excel-naam: Rapport 2020 juni … 'bewerkingen voor het huidige doelwerkblad waarnaar wsDepTarget verwijst Row List Next </code> ==== Doelwerkbladen maken met WSheet Split ==== ==== Bereiken in de bron selecteren door ==== te filteren Zodra de processtructuur voor het maken van de doelwerkbladen klaar is, moeten de overeenkomstige delen van de brongegevensset voor elk doelwerkblad worden geselecteerd. Als de selectie moet worden gedaan met behulp van een bekende waarde (bekende constante waarden of waarden afkomstig uit een aanvullende bron met behulp van Row List) in een kolom van het bronwerkblad, is filteren de beste praktijk. Djeeni implementeert het MS Excel-autofilter (bekend als de vervolgkeuzelijsten bij elke kolomkop) met behulp van de processtappen WSheet Filter Add en WSheet Filter Clear. Als meerdere WSheet Filter Add op een werkblad worden gebruikt, worden ze gecombineerd (als AND) en worden alleen de rijen geselecteerd die aan beide filtercriteria voldoen. Elke Range- en Cell-bewerking werkt met de gefilterde gegevensset. Voorbeeld 1: Fix (klein) aantal doelwerkmappen en bekende filterwaarden <code> WSheet Gebruik Djeeni-naam: wsSourceData WBook List Start Djeeni-naam: wlTarget Werkmaplijst: c:\wbook1.xlsx;c:\reports\wbook2.xlsx Als voorwaarde: [#wlTarget|NAME]=wbook1 WSheet-filter Werkblad toevoegen: wsSourceData Kolom: D Criterium 1: 1500 … 'bewerkingen voor het eerste doelwerkblad waarnaar wordt verwezen door wlTarget WBladfilter wissen Stop als Als voorwaarde: [#wlTarget|NAME]=wbook2 WSheet-filter Werkblad toevoegen: wsSourceData Kolom: E Criteria 1: VK … 'bewerkingen voor het tweede doelwerkblad waarnaar wordt verwezen door wlTarget WBladfilter wissen Stop als WBook List Next </code> Voorbeeld 2: Bron splitsen met behulp van een aanvullende lijst met afdelingen. Merk op hoe de doelwerkmappen een unieke naam krijgen met behulp van de aanvullende lijstinformatie in WBook New. <code> WSheet Use Djeeni-naam: wsSourceData 'brongegevensset WSheet Use Djeeni-naam: wsDepartments 'aanvullende lijst Row List Start Djeeni-naam: rlDepts Werkblad: wsAfdelingen Row vanaf: 2 Row naar: #RowEnd|C WBook Nieuwe Djeeni-naam: wsDepTarget Bestandsnaam: [=rlDepts!D#] 'dept. naam in kolom D van suppl. lijst Type: Doel Werkblad Excel-naam: Rapport 2020 juni WSheet-filter Werkblad toevoegen: wsSourceData Kolom: D'afd. ID in kolom D van de brongegevensset Criteria: [=rlDepts!F#] 'afd. ID in kolom F van suppl. lijst … 'bewerkingen voor het huidige doelwerkblad waarnaar wsDepTarget verwijst WBladfilter wissen Row List Next </code> ==== Bereiken in bron selecteren door opzoeken ==== Bepaalde brongegevenssets bevatten bereiken die worden geïdentificeerd door een startwaarde en een eindwaarde. Bijvoorbeeld een periode van één maand die begint op de eerste van de maand en loopt tot de eerste van de volgende maand. In dit geval kan het bereik worden geïdentificeerd aan de hand van de twee cellen die kunnen worden gevonden met behulp van Cell Lookup. De stappen zijn: - Zoek de eerste cel van het bereik met Cell Lookup - Zoek de laatste cel (of de cel na de laatste cel) van het bereik met behulp van Cell Lookup - Geef het bereik op met Range Use en de twee cellen <code> WSheet Use Djeeni-naam: wsData Cell Lookup Djeeni-naam: ceFirst Waarde: 1-11-2020 'eerste november Range: wsData!C1:C20000 Cel opzoeken Djeeni-naam: ceAfterLast Waarde: 1-12-2020 'eerste december Range wsData!C1:C20000 Range Use Djeeni-naam: rgNovember Range: wsData!C[#ceFirst|row]:C[+[#ceAfterLast|row]-1] </code> ===== Verwijzen naar het huidige werkblad in een werkmappenlijst ===== Werkmaplijsten hebben hun eigen Djeeni-namen. Deze Djeeni-namen gedragen zich als de Djeeni-naam van een werkblad dat is opgegeven in WSheet Use**. U kunt de twee Djeeni-namen waar nodig door elkaar gebruiken.

WSheet Gebruik Djeeni-naam: wsTarget
Celset Cel: wsTarget!C3

WBook List Start Djeeni-naam: wlTarget
Cell Set Cell: wlTarget!C3

Bij rijlijsten is er altijd een huidige rij waarnaar wordt verwezen met # in plaats van een feitelijk rijnummer. In het geval van geneste rijlijsten kan # worden uitgebreid met de Djeeni-naam van de rijlijst.

WSheet Use Djeeni-naam: wsOuter
WSheet Use Djeeni-naam: wsInner
Row List Start Djeeni-naam: rlOuter
                Werkblad: wsOuter
Row List Start Djeeni-naam: rlInner
                Werkblad: wsInner
Cell Set Cel: wsOuter!D[#|rlOuter] 'huidige rij van de rlOuter-Row List
Cell Set Cel: wsInner!D# 'de dichtstbijzijnde (binnenste) Row List