Excel-functies in Djeeni-processen

Djeeni is gemaakt voor mensen die ervaring en vaardigheid in MS Excel hebben opgebouwd door het regelmatig te gebruiken voor verschillende gegevensverwerkingstaken. We willen wel dat je je vaardigheid kunt behouden en benutten en daarom hebben we Djeeni gebouwd om naadloos samen te werken met MS Excel en de krachtige ingebouwde functies ervan. In de onderstaande paragrafen leggen we aan de hand van praktische voorbeelden uit hoe en voor welk doel u Excel-functies kunt inbedden bij verschillende Djeeni-processtappen.

De meeste processen zijn niet lineair: ze kunnen herhaalde stappen bevatten (zie lijsten) of voorwaardelijke/optionele stappen die alleen moeten worden uitgevoerd als aan bepaalde voorwaarden is voldaan. Djeeni biedt de processtap If om de voorwaarden te beschrijven.

Een van de meest voorkomende voorwaarden is om te controleren of een cel leeg is. De beste praktijk is om de ISBLANK() Excel-functie te gebruiken, omdat deze met alle mogelijke celtypen werkt:

   If Djeeni-naam: ifEmpty
             Voorwaarde: ISBLANK([$wsWorksheet!B3]) 'controleer of cel Werkblad!B3 leeg is

In MS Excel kunt u met behulp van een formule een dynamische waarde aan een cel instellen. Een dergelijke formule voert een aantal berekeningen uit met behulp van a) waarden van een of meer andere cellen; en b) ingebouwde of door de gebruiker gedefinieerde functies. De formule is vast aan de cel bevestigd.

In Djeeni kun je alle kennis en ervaring die je hebt met formules en Excel-functies behouden. Voorbeeld: Laten we aannemen dat we de waarde van cel B4 op het werkblad wsMonthEnd moeten instellen door enkele waarden in het bereik G2:G20 op het werkblad wsERPData samen te vatten. In Djeeni gebruik je de bekende SUM-functie met dezelfde bereikparameter als in Excel binnen een [+….] Djeeni-formule:

   Cell Set Cell: wsMonthEnd!B4 'NB: naar cellen wordt altijd verwezen met hun werkblad
             Waarde: [+SUM([:wsERPData!G2:G20])] 'Er is een bereik opgegeven in [:....]

Maar Djeeni kan meer. U kunt de cel ook dynamisch definiëren met behulp van een Djeeni-formule. Voorbeeld: de som die we verzamelen moet op B4 worden gezet als deze kleiner is dan 10.000; anders zou het naar C4 moeten gaan:

   Cell Set Cell: [+IF(SUM([:wsERPData!G2:G20])<10000,[$wsMonthEnd!B4],[$wsMonthEnd!C4])] 'Celadres is
             Waarde: [+SUM([:wsERPData!G2:G20])] 'verwezen door [$.....]
  

Deze Djeeni-functie is erg handig bij gebruik in een werkmap, rij- of kolommenlijst. Voorbeeld: een orderwerkblad (wsExport) wordt geëxporteerd vanuit een IT-systeem en bevat duizenden rijen met verschillende datums (kolom B) en productcodes (kolom K) in een maand. Op basis van de datum en de productcode moet elke bestelling in drie vooraf gedefinieerde kolommen per week worden vermeld (in totaal 15 doelkolommen voor de hele maand). De oplossing op papier:

    Doorloop de rijen van wsExport
        Stel de bestelwaarde in na de laatste rij in de doelkolom waar de doelkolom wordt berekend door:
             - het aantal weken vanaf het begin van de maand met behulp van de WEEKNUMMER-functie; plus
             - de kolom binnen een week volgens de productcategorie met behulp van de IFS-functie

De oplossing in Djeeni (eerst de berekening van de doelkolom tijdelijk opslaan in cel wsTarget!ZZ1):

   Row List Start Row: 2
            End Row: #RowEnd
   Cell Set Cell: wsTarget!ZZ1 'Tijdelijke cel om het berekeningsresultaat van de doelkolom op te slaan
            Waarde: [+ WEEKNUM([$wsExport!B#],2)–WEEKNUM(DATE(YEAR([$wsExport!B#]),MONTH([$wsExport!B#]),1),2)+1 +
                      IFS([=wsExport!K#]<5000,0,[=wsExport!K#]<20000,1,[=wsExport!K#]>19999,2) ]
   Cell Set Cell: wsTarget![=wsTarget!ZZ1][#RowEnd|[=wsTarget!ZZ1]]
            Waarde: wsExport!C#
   Row List Next

Als u bekend bent met draaitabellen in MS Excel, dan is deze functie van Djeeni een uitbreiding van de draaitabellen voor complexere gevallen.

Werken met datums is een van de moeilijkste taken in MS Excel op drie niveaus:

• Er zijn drie belangrijke datumformaten: Maand/Dag/Jaar; Jaar maand dag; Dag-Jaar-Maand (hier hebben we het over het schrijven van jaren met 4 cijfers - de jaarnotatie van 2 cijfers maakt het nog moeilijker). Alleen het jaar-maand-dag-formaat is eenvoudig voor MS Excel.
• MS Excel gedraagt zich anders afhankelijk van de regionale instellingen van het besturingssysteem. U opent dezelfde werkmap (of CSV-bestand) op twee machines en de ene begrijpt de datums, de andere niet, of niet correct. Je kunt er niet op vertrouwen dat je de date hebt
• Wanneer datums in formules worden ingevoerd, worden deze opnieuw verwerkt in MS Excel; soms met als gevolg dat de datum opnieuw verkeerd wordt begrepen.

MS Excel doet veel ongedocumenteerde moeite om verschillende waarden te vinden en in stilte om te zetten in datums. Het maakt veel fouten: kan geen geldige datums vinden (vanwege de regionale instellingen); herkent data waarop er geen zijn; of interpreteert datums eenvoudigweg verkeerd (bijvoorbeeld tussen 5 maart en 3 mei), wat problemen veroorzaakt.

Op basis van deze problemen raden we u aan datums te gebruiken in Djeeni-processen volgens deze richtlijnen:

1. Bewaar elke datum indien mogelijk in drie afzonderlijke cellen: jaar; maand; dag
2. Combineer de drie waarden (laten we aannemen dat jaar A1 is; maand is B1; dag is C1 op werkblad wsSheet) in een datum op [+DATE([=wsSheet!A1],[=wsSheet!B1],[=wsSheet!C1 ])] (met behulp van de standaardvolgorde jaar-maand-datum). Djeeni gebruikt de onderliggende getalswaarde van de datum en zorgt voor consistentie op alle platforms en locaties.
3. Formatteer uw doelcellen vooraf naar een korte datum of een lange datum om de juiste datumreeksen weer te geven (in plaats van getalswaarden).

Er is een verborgen concept in MS Excel over wat het betekent om naar een cel te verwijzen. Wanneer naar een cel (bijvoorbeeld B3) wordt verwezen in een formule of binnen een functie, leest MS Excel de waarde van de cel en gebruikt deze waarde verder. Het lezen van de waarde betekent ook dat u moet beslissen of het een getal, een tekst of een datumwaarde is (het type van de waarde). Bepaalde functieparameters kunnen alleen tekst-, getal- of datumwaarden zijn en MS Excel geeft een foutmelding als de opgegeven waarde niet het juiste type heeft. Daarom is het belangrijk om te begrijpen hoe Djeeni-formules met cellen en celwaarden omgaan wanneer ze worden gecombineerd met Excel-functies.

In Djeeni hebben celverwijzingen altijd de vorm wsDjeeniName!ColumnRow, die voor MS Excel in twee vormen kan worden omgezet:

• De Djeeni-celreferentie wordt omgezet in een Excel-celreferentie. Voorbeeld: als we een werkblad1 hebben in de werkmap c:\finance\export.xlsx met de Djeeni-naam wsExport, dan wordt wsExport!A2 doorgegeven aan Excel als '[c:\finance\export.xlsx]sheet1'!A2. Om dit te bereiken moet de Djeeni-formule [$ … ] worden gebruikt.
• De Djeeni-celreferentie wordt door Djeeni verwerkt door de celwaarde te lezen. Voorbeeld: als bovenstaande A2-cel 'Speelgoedschip' bevat, krijgt MS Excel de waarde 'Speelgoedschip' om mee te werken. Om dit te bereiken moet de Djeeni-formule [= … ] worden gebruikt. Djeeni controleert niet of de waarde een getal of tekst of een datum is; het wordt in ieder geval doorgegeven als tekst zonder tussen “” te staan (en het is erg handig bij het genereren van bijvoorbeeld het dynamische deel van een variabele werkmapnaam)

Aan de andere kant verwacht MS Excel “” rond tekstwaarden in functies. Voorbeeld: Als de waarde van de bovenstaande A2-cel moet worden vergeleken met 'Speelgoedboot', schrijft men in Djeeni: IF([=wsExport!A2] = “Speelgoedboot”,1,2); maar dit resulteert in een fout. Na verwerking van de Djeeni-formule zou MS Excel het volgende zien: IF(Speelgoedschip = “Speelgoedboot”,1,2).

Er zijn twee oplossingen:

• schrijf: IF([$wsExport!A2] = “Speelgoedboot”,1,2) dat wordt doorgegeven aan MS Excel als IF('[c:\finance\export.xlsx]sheet1'!A2 = “Speelgoedboot ”,1,2)
• schrijf: IF(“[=wsExport!A2]” = “Speelgoedboot”,1,2) dat wordt doorgegeven aan MS Excel als IF(“Speelgoedschip” = “Speelgoedboot”,1,2)

Controleer altijd welke oplossing in de f past