Djeeni-formules gebruiken

Uiteindelijk zitten de MS Excel-gegevens in de cellen van werkbladen. In dit hoofdstuk leest u gedetailleerd hoe u dit kunt doen

1. toegang krijgen tot de gegevens van een broncel;
2. verwerken/berekenen dat het moet worden gebruikt als resultaatwaarde op een doellocatie (d.w.z. een cel op een doelwerkblad);
3. maak tekstuele waarden die voor andere doeleinden worden gebruikt, zoals het maken van bestandsnamen of communiceren;
4. gebruik Djeeni-codes in Djeeni-formules.

De basistoegang van een cel in Djeeni (net als in MS Excel) is het opgeven van de werkbladnaam gevolgd door het uitroepteken, de kolomletter en het rijnummer:

   wsERPRapport!F12

waarbij het werkblad wordt geïdentificeerd door de Djeeni name in plaats van de fysieke bestandsnaam en werkbladnaam in MS Excel. Een bereik is ook hetzelfde als in MS Excel: twee cellen op hetzelfde werkblad die gescheiden zijn door een dubbele punt.

   wsERPRapport!B4:H5

Maar cellen en bereiken worden zelden benaderd via deze basismethode waarbij alle onderdelen vastliggen. In de rest van dit hoofdstuk wordt de flexibiliteit uitgelegd die Djeeni in verschillende situaties biedt om de delen van een cel of bereik te definiëren.

Vaak omvat een gegevensproces veel werkbladen die tijdens het proces moeten worden geconsolideerd of gemaakt. Djeeni gebruikt werkmaplijsten om de set werkbladen te doorlopen. Een werkmappenlijst heeft een Djeeni name die op elke plek gebruikt kan worden waar een werkblad gespecificeerd moet worden. Als de werkmappenlijst de Djeeni-naam wlSubLedgers heeft, dan

   wlSubLedgers!E35

is de cel E35 op het huidige werkblad in de werkmappenlijst.

Het is heel gebruikelijk dat het aantal rijen op een werkblad niet vooraf bekend is en varieert elke keer dat het proces wordt gebruikt. De eenvoudige waarde #RowEnd kan worden gebruikt om de laatste rij met gegevens in een kolom te identificeren.

   wsHeadcount!C#RowEnd

Zelf zoekt #RowEnd naar de laatste rij in kolom A. Dit gedrag kan worden gewijzigd door de kolomnaam toe te voegen na |:

   wsHeadcount!C#RowEnd|F

De bovenstaande cel is de cel in kolom C met een rijnummer gelijk aan de laatste rij in kolom F.

Net als #RowEnd kan #ColumnEnd worden gebruikt om naar de laatste kolom in een rij te verwijzen:

   wsSupplies!#ColumnEnd9 'laatste kolom in rij 1
   wsSupplies![#ColumnEnd|5]9 'laatste kolom in rij 5

Merk op dat […] wordt gebruikt om de 5 (modifier voor #ColumnEnd) te scheiden van 9 (rijnummer van de cel).

U hebt dus toegang tot elke cel of elk bereik op elk werkblad. Meestal worden de gegevens in de broncel verwerkt voordat deze naar een andere doelcel worden geschreven. Dit zijn de verwerkingsopties van Djeeni.

Om gegevens in MS Excel zelf te verwerken kunnen formules gebruikt worden; en een dominant deel van de formules maakt gebruik van ingebouwde MS Excel-functies. Een MS Excel-formule kan bijvoorbeeld de waarden van andere cellen/bereiken optellen:

   =SOM(A4:C5)

In Djeeni kunt u de MS Excel-functies combineren met Djeeni-formules:

   =SUM([:wsReport!A4:C5]) 'Djeeni-formule binnen een MS Excel-formule

waarbij [….] het Djeeni-formulegedeelte aangeeft. Als een MS Excel-functie meerdere parameters heeft, kunnen ze allemaal een waarde krijgen met behulp van Djeeni-formules:

   =IF([$wsMaster!B#]="C";[$wsMaster!C#];[$wsSource!A2])

En als u een MS Excel-formule binnen een procesparameterwaarde moet gebruiken, kunt u [+…] gebruiken:

   WSheet Gebruik bestandsnaam: Rapport-[+TEXT(TODAY(),"jjjjmmdd")].xlsx

Verschillende soorten Djeeni-formules kunnen worden gecombineerd met MS Excel-formules:

   [$.....] 'een MS Excel-celreferentie
   [=.....] 'de werkelijke waarde van de cel
   [:.....] 'een bereik
   [+.....] 'een berekening
   [#.....] 'speciale Djeeni-codes

Opmerking: het verschil tussen [$…] en [=…] is erg belangrijk:

• [$….] wordt door Djeeni vertaald naar de MS Excel-syntaxis en aan MS Excel overgelaten om de werkelijke waarde te lezen: [$wsReport!A4] zal zijn (laten we zeggen dat wsReport zich bevindt op c:\myReport. xlsx, ter aanduiding van werkblad Exporteren), vertaald naar '[c:\mijnrapport.xlsx]Exporteren'!A4. Deze referentie kan verder worden gebruikt in andere MS Excel-formules.
• [=…] wordt uitgevoerd door Djeeni die de werkelijke waarde van de cel leest: als [$wsReport!A4] de waarde 24 bevat, is het resultaat van deze formule 24 en kan worden gebruikt voor berekeningen of als tekstwaarde. Voor datumwaarden leest [=…] de onderliggende getalswaarde.

Voor geavanceerde tips over het gebruik van Excel-functies in verschillende processtappen, zie deze pagina.

Naast functies biedt MS Excel de mogelijkheid om berekeningen uit te voeren in een MS Excel-formule:

   =A5+B4

In Djeeni kun je nog steeds dit formaat gebruiken (stel dat cel A5 het getal 2 bevat en B4 6):

   =[$wsInput!A5]+[$wsInput!B4] 'MS Excel berekent =A5+B4
   =[=wsInput!A5]+[=wsInput!B4] 'MS Excel berekent =2+6

of laat Djeeni zelf de berekening maken met [+…]:

[+[=wsInput!A5]+[=wsInput!B4]]

NB: Elk type berekening wordt aangegeven met [+…], maar binnen de formule kunt u alle + - * / operatoren gebruiken.

Werken met datums in MS Excel is de meest complexe taak die de meeste hoofdpijn veroorzaakt. Zeker, de meesten van ons zijn deze situatie tegengekomen: er wordt een werkmap ontvangen met samengevoegde werkbladen die een datumkolom bevatten. Verrassend genoeg worden sommige datums weergegeven als getallen, terwijl andere datums niet worden herkend door MS Excel en niet kunnen worden gebruikt voor verdere verwerking. Bovendien veranderen sommige datums simpelweg: het was 10 maart 2022 bij de afzender, en bij de ontvanger wordt het gewoon weergegeven als 3 oktober 2022 (maart = 3; oktober = 10).

De (bijna) gemene deler is het feit dat alle datums intern in MS Excel als getal worden opgeslagen. Djeeni gebruikt deze getalswaarde wanneer deze beschikbaar is voor het lezen van celwaarden en berekeningen. Het gevolg hiervan is dat de doelcellen vooraf moeten worden opgemaakt naar korte of lange datums, anders worden de getalswaarden gezien in plaats van de datumreeksen.

Datums worden ook hier besproken.

In sommige gevallen is het rijnummer en/of de kolomletter van de cel niet vooraf bekend:

• het wordt berekend met #RowEnd of #ColumnEnd
• resultaat van een Cell Lookup processtap
• huidige rij (#) in een rijlijst

en het is nodig om enkele berekeningen uit te voeren met deze rijnummers/kolomletters.

Het meest typische voorbeeld is het vinden van de eerste lege rij onder de laatste gegevensrij op een werkblad. #RowEnd geeft het rijnummer van de laatste gegevensrij en er moet 1 worden toegevoegd. Deze berekende waarde moet dan in een andere bewerking worden gebruikt. We moeten bijvoorbeeld het woord 'Totaal' schrijven onder de laatste gegevensrij in kolom A op werkblad wsSummary:

   Celset Cel: wsSummary!A[+#RowEnd+1]
               Waarde: Totaal

Het rijnummer en de kolomletter van een cel gevonden door Cell Lookup (met Djeeni-naam ceDay) zijn:

   [#ceDay|row] 'rijnummer van gevonden cel
   [#ceDay|column] 'kolomnummer van gevonden cel

en met een eenvoudige berekening krijgt u toegang tot elke cel die hiermee verband houdt. Deze functie van Djeeni is vergelijkbaar met de 'tweede helft' van de VLOOKUP MS Excel-functiefamilie, maar biedt veel meer flexibiliteit:

   wsA!C[+[#ceDay|row]-2] 'cel 2 rijen boven de eerder gevonden cel; in kolom C
                             'de eerder gevonden cel hoeft NIET in kolom C te staan
                             'nog verder: het hoeft NIET op hetzelfde werkblad te staan
   wsA![+[#ceDay|column]-1]1 'de kolom links naar de kolom van de gevonden cel; in rij 1

Let op: voor de duur van berekeningen zet Djeeni op magische wijze kolomletters om in cijfers en omgekeerd.

De huidige rij van een rijlijst wordt aangegeven met #. Het kan net zo flexibel worden gebruikt als het rijnummer van een gevonden cel door Cell Lookup: elke combinatie met werkbladen en kolommen is mogelijk.

Djeeni-processen kunnen dat wel

• manipuleer veel werkbladen met verschillende bestandsnamen en werkbladnamen met behulp van werkmappenlijsten;
• communiceer met veel gebruikers met verschillende e-mailadressen;
• en stel voor elke rij verschillende celwaarden in rijlijsten in.

Het veel voorkomende probleem is dat de waarde (bestandsnaam, e-mailadres enz.) uit verschillende delen bestaat: deels letterlijke waarden, deels dynamische waarden uit cellen. MS Excel biedt de functie CONCATENATE om een dergelijke waarde samen te stellen, maar in Djeeni is het veel eenvoudiger. Als de Djeeni-formule [=….] wordt gebruikt om de werkelijke celwaarde van een cel te lezen, volstaat het eenvoudigweg de delen achter elkaar te schrijven (zonder een MS Excel-formule te gebruiken die begint met = ).

Voorbeeld: Stel dat we enkele maandnamen hebben in kolom C op het werkblad wsMonths. Het Djeeni-proces doorloopt de rijen wsMonths en creëert voor elke maand een nieuw werkblad in een werkmap die is samengesteld uit het letterlijke 'Rapport-' en de naam van de maand.

   WSheet Gebruik Excel-naam: Report-[=wsMonths!C#]

De [=…] Djeeni-formule kan ook in alle parameters van een e-mail (aan, onderwerp, hoofdtekst) worden gebruikt om gepersonaliseerde en met actuele gegevens gevulde e-mails te verzenden.

Een andere veel voorkomende situatie is wanneer de bestandsnaam een datum bevat die dynamisch is maar niet in een cel is opgeslagen. We moeten MS Excel-formules gebruiken om de waarden te berekenen. Voorbeeld: Laten we aannemen dat we elke dag toegang moeten hebben tot een bestand dat een bestandsnaam heeft die (onder andere) de datum van gisteren bevat. De MS Excel-functies TEXT en TODAY kunnen binnen de [+…] Djeeni-formule worden gebruikt om de datumreeks elke dag dynamisch te genereren:

   WSheet Gebruik bestandsnaam: Rapport-[+TEXT(TODAY()-1,"mmddyy")].xlsx

Bij bestandsnamen komt het voor dat elk deel van de fysieke locatie van een gebruikt werkblad (map, bestandsnaam en extensie van de bevattende werkmap; de MS Excel-naam van het werkblad) opnieuw moet worden gebruikt. In Djeeni is deze informatie toegankelijk (laten we zeggen dat de Djeeni name van het werkblad wsInput is) met behulp van:

   [#wsInput|FOLDER] 'het mapgedeelte van de locatie van de bevattende werkmap
   [#wsInput|FILE] 'het bestandsnaamgedeelte van de locatie van de bevattende werkmap
   [#wsInput|EXTENSION] 'het uitbreidingsgedeelte van de locatie van de bevattende werkmap
   [#wsInput|NAME] 'de MS Excel-werkbladnaam van het werkblad

Enige informatie met betrekking tot cellen; bestandsnamen en werkbladnamen; rijlijsten zijn toegankelijk met behulp van Djeeni-codes. Ze werden al besproken tijdens voorbeelden in de gebruikershandleiding elders. Deze sectie geeft een overzicht ervan op één plek.

Djeeni-codes beginnen met # gevolgd door een naam en optioneel een modifier na |. Naast constante Djeeni-codenamen kan Djeeni-namen ook als Djeeni-code worden gebruikt.

#RowEnd geeft het rijnummer van de laatste niet-lege cel in een kolom aan. Standaard kijkt het in kolom A, maar kan worden aangepast om in elke kolom te kijken: #RowEnd|D

#ColumnEnd geeft de kolomletter aan van de laatste niet-lege cel in een rij. Standaard ziet het er in rij 1 uit, maar kan worden aangepast om het in elke rij te laten lijken: #RowEnd|12

# (lege naam) verwijst naar het huidige rijnummer in een rijlijst. Als er meerdere rijlijsten zijn ingesloten, verwijst # naar de binnenste rijlijst. Kan worden gewijzigd door elke rijlijstnaam om te verwijzen naar het huidige rijnummer van die specifieke rijlijst. Voorbeeld: *|rlJaren

#wsDName (waarbij wsDName de Djeeni-naam is van een gebruikt werkblad) met modifiers kunnen worden gebruikt om toegang te krijgen tot verschillende delen van de fysieke locatie van dat werkblad:

   [#wsDName|FOLDER] 'het mapgedeelte van de locatie van de bevattende werkmap
   [#wsDName|FILE] 'het bestandsnaamgedeelte van de locatie van de bevattende werkmap
   [#wsDName|EXTENSION] 'het extensiegedeelte van de locatie van de bevattende werkmap
   [#wsDName|NAME] 'de MS Excel-werkbladnaam van het werkblad

#ceDName (waarbij ceDName de Djeeni-naam is van een gevonden cel door Cel opzoeken) met modifiers kan worden gebruikt om toegang te krijgen tot het rijnummer, de kolomletter en de waarde van de cel:

   [#ceDName|CELL] 'de MS Excel-verwijzing naar de cel
   [#ceDName|ROW] 'het rijnummer van de gevonden cel
   [#ceDName|COLUMN] 'de kolomletter van de gevonden cel