Varázslás az adatokkal a Djeeni Formula használatával

Végülis, az MS Excelben az adatok munkalapok celláiban találhatók. Ebben a fejezetben részletesen megtanulhatod, hogy hogyan

1. érheted el a forrás munkalap celláiban lévő adatokat;
2. dolgozhatod fel ezeket az adatokat, hogy megkapd a célértéket a cél munkalap egy cellájában;
3. készíthetsz szövegeket, amiket pl. fájlnevekben vagy a kommunikációban tudsz használni;
4. használhatsz Djeeni kódokat Djeeni formulákban.

Djeeniben (csakúgy mint MS Excelben) a legegyszerűbb elérése egy cellának az, hogy megadjuk a munkalapjának nevét, majd egy felkiáltójelet követően az oszlopának a betűjelét, végül a sorának a számát.

  wsERPRiport!F12

ahol a munkafüzet a Djeeni nevével van azonosítva (ahelyett, hogy a munkafüzet fizikai helyét, majd a munkalap saját nevét adnánk meg). Egy tartomány is pontosan ugyanaz, mint az MS Excelben: két cella ugyanazon a munkalapon, : jellel elválasztva.

  wsERPReport!B4:H5

De cellákat és tartományokat ritkán érünk el ezen a módon, ahol mindhárom rész fixen megadott. A fejezet további része megmutatja, hogy Djeeni különböző helyzetekben milyen lehetőségeket kínál a cellák és tartományok egyes részeinek a megadására.

Egy adatfeldolgozási folyamat sokszor több munkalapot is érint, amelyeket konszolidálni vagy létrehozni kell a folyamat során. Djeeni a munkafüzetlistát biztosítja ahhoz, hogy végig lehessen menni az összes munkalapon. A munkafüzetlistának van egy Djeeni neve, amit bárhol lehet használni, ahol munkafüzetre kell hivatkozni. Ha pl. a munkafüzetlista DJeeni neve wlAnalitikak, akkor

  wlAnalitikak!E35

az E35 cellát fogja jelölni a munkafüzetlista aktuális munkafüzetében.

Gyakori, hogy egy munkalapon nem lehet előre tudni, hogy hány sor van egy oszlopban, és ez minden egyes végrahajtásakor a folyamatnak változik. Az egyszerű #RowEnd jelölés használható az utolsó sor azonosítására egy oszlopban.

  wsLetszam!C#RowEnd

Önmagában a #RowEnd az A oszlop utolsó sorát jelöli. Ez megváltoztatható, ha egy | után megadjuk egy oszlop betűjelét:

  wsLetszam!C#RowEnd|F

Ez a cella a C oszlop azon sorában van, amelyik megegyezik az F oszlop utolsó sorával.

A #RowEnd jelöléshez hasonlóan a #ColumnEnd jelölés használható egy sor utolsó oszlopára hivatkozáshoz.

  wsAnyagok!#ColumnEnd9                  'az utolsó oszlop az első sorban
  wsAnyagok![#ColumnEnd|5]9              'az utolsó oszlop az ötödik sorban

Figyeld meg, hogy a […] elválasztja egymástól az 5 számot (ami az utolsó oszlop módosítója) a 9 számtól (ami a cella sorszáma)

Tehát, el tudjuk érni bármely munkalap bármely celláját. De legtöbbször a forrás cellában található adatot fel kell dolgozni mielőtt beírásra kerül egy másik cél cellában. Úgyhogy következzenek Djeeni adatfeldolgozási lehetőségei.

To process data in MS Excel itself, formulas can be used; and a dominant part of formulas use built-in MS Excel functions. For example, an MS Excel formula can sum the values of other cells / ranges:

  =SUM(A4:C5)

In Djeeni you can combine the MS Excel functions with Djeeni formulas:

  =SUM([:wsReport!A4:C5])

where [….] denotes the Djeeni formula part. If an MS Excel function has multiple parameters, all of them can get a value using Djeeni formulas:

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

Several different types of Djeeni formulas can be combined with MS Excel formulas:

  [$.....]        'an MS Excel cell reference
  [=.....]        'the actual value of the cell
  [:.....]        'a range
  [+.....]        'a calculation
  [#.....]        'special Djeeni codes

Next to functions, MS Excel provides the opportunity to perform calculations in an MS Excel formula:

  =A5+B4

In Djeeni you can still use this format (suppose that cell A5 contains the number 2 and B4 contains 6):

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

or let Djeeni itself make the calculation using [+…]:

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

NB: Any type of calculations will be denoted by [+…] but inside you can use + - * / as well.

At some cases, the row number and/or column letter of the cell are not known upfront:

• it is calculated by #RowEnd or #ColumnEnd
• result of a Cell Lookup process step
• current row (#) in a row list

and it is needed to perform some calculations with these row numbers/column letters.

The most typical example is to find the first empty row below the last data row on a worksheet. #RowEnd gives the row number of last data row and 1 should be added. This calculated value should be used then in another operation. For example we need to write the word 'Total' below the last data row in column A on worksheet wsSummary:

  Cell Set    Cell: wsSummary!A[+#RowEnd+1]
              Value: Total

The row number and column letter of a cell found by Cell Lookup (with Djeeni name ceDay) are:

  [#ceDay|row]       'row number of found cell
  [#ceDay|column]    'column number of found cell

and a simple calculation let you access any cell related to this one. This feature of Djeeni is similar to the 'second half' of the VLOOKUP MS Excel function family but provides much more flexibility:

  wsA!C[+[#ceDay|row]-2]    'cell 2 rows above the previously found cell; in column C
                            'the previously found cell need NOT be in column C
                            'even further: it need NOT be on the same worksheet
  wsA![+[#ceDay|column]-1]1 'the column left to the found cell's column; in row 1

NB: for the duration of calculations Djeeni magically converts column letters into numbers and back.

The current row of a row list is denoted by #. It can be used just as flexible as the row number of a found cell by Cell Lookup: any combination with worksheets and columns are possible.

Djeeni processes can

• manipulate many worksheets with different filenames and worksheet names using workbook lists;
• communicate with many users with different email addresses;
• and set different cell values in row lists for each row.

The common problem is that the value (filename, email address etc.) is composed of several parts: partly literal values, partly dynamic values from cells. MS Excel provides the CONCATENATE function to compose such a value but in Djeeni it is much simpler. If the Djeeni formula [=….] is used to read the actual cell value of a cell then writing simply the parts after each other (without using an MS Excel formula that starts with = ) does the job.

Example: Suppose that we have some month names in column C on the worksheet wsMonths. The Djeeni process iterates through the rows of wsMonths and creates a new worksheet for each month in a workbook that is composed of the literal 'Report-' and the month name.

  WSheet Add  Excel name: Report-[=wsMonths!C#]

The Djeeni formula [=…..] can be used also in all parameters of an email (to, subject, body) to send personalized and with actual data filled emails.

With filenames it happens that any part of the physical location of a used worksheet (folder, filename and extension of the containing workbook; the MS Excel name of the worksheet) should be reused. In Djeeni this information can be accessed (let's say the Djeeni name of the worksheet is wsInput) using:

  [#wsInput|FOLDER]    'the folder part of the location of the containing workbook
  [#wsInput|FILE]      'the filename part of the location of the containing workbook
  [#wsInput|EXTENSION] 'the extension part of the location of the containing workbook
  [#wsInput|NAME]      'the MS Excel worksheet name of the worksheet

Some information related to cells; filenames and worksheet names; row lists can be accessed using Djeeni codes. They were already discussed during examples in the user guide elsewhere. This section gives an overview of them in one place.

Djeeni codes start with # followed by a name and optionally a modifier after |. Next to constant Djeeni code names, Djeeni names can be also used as a Djeeni code.

#RowEnd denotes the row number of the last non-empty cell in a column. By default, it looks in column A but can be modified to look in any column: #RowEnd|D

#ColumnEnd denotes the column letter of the last non-empty cell in a row. By default, it looks in row 1 but can be modified to look in any row: #RowEnd|12

# (empty name) refers to the current row number in a row list. In case there are multiple row lists embedded, # refers to the innermost row list. Can be modified by any row list name to refer to the current row number of that particular row list. Example: *|rlYears

#wsDName (where wsDName is the Djeeni name of a used worksheet) with modifiers can be used to access different parts of the physical location of that worksheet:

  [#wsDName|FOLDER]    'the folder part of the location of the containing workbook
  [#wsDName|FILE]      'the filename part of the location of the containing workbook
  [#wsDName|EXTENSION] 'the extension part of the location of the containing workbook
  [#wsDName|NAME]      'the MS Excel worksheet name of the worksheet

#ceDName (where ceDName is the Djeeni name of a found cell by Cell Lookup) with modifiers can be used to access the row number, column letter and value of the cell:

  [#ceDName|CELL]    'the MS Excel reference to the cell
  [#ceDName|ROW]   'the row number of the found cell
  [#ceDName|COLUMN]  'the column letter of the found cell