Munka több munkalappal

Egy munkalapot elnevezni és használni egy nagyon hasznos koncepció ha kevés, és ismert munkalappal dolgozunk. De sok esetben több, vagy egyenesen sok, hasonló szerkezetű munkalappal kell egyszerre dolgozni. Tipikusan ilyen faladat az adatkonszolidáció és munkalap(ok) szétdarabolása.

A konszolidálandó forrás munkalapok már léteznek, és a fileneveik ismertek. A legjobb gyakorlat a WBook List Start és WBook List Next folyamatlépéseket (a Worksheet és If / Lists kategóriákban a Folyamat Eszköztáron) használni ugyanazon műveletek több munkalappal történő elvégzéséhez.

A 'Workbook List' paraméterben a forrás munkalapok munkafüzeteit megadhatja:

• a filenevekben ? és * dzsókerkarakterek használatával (e.g. c:\en\mappam\*.xlsx minden munkafüzethez az en\mappam mappában); és
• több mappából és filenévvel, amelyek pontosvesszővel (;) vannak elválasztva (pl. c:\2018\riport.xlsx;c:\2019\riport.xlsx)

Az első lépés a folyamat megadása: először egyetlen forrás munkalapra, mintha nem is lenne több. Használd a WSheet Use folyamatlépést a cél munkalap megadásához, majd egy másik WSheet Use lépést az egyetlen forrás munkalaphoz. Amikor a folyamat kész, kövesd ezeket a lépéseket:

1. cseréld le az egyetlen forrás munkalap WSheet Use folyamatlépését a WBook List Start folyamatlépésre az összes munkalaphoz a folyamat elején (NB: tartsd meg a WSheet Use lépést a cél munkalaphoz!);
2. add meg az összes forrás munkalapot a WBook LIst Start 'Workbook list' paraméterében;
3. add hozzá a folyamat végéhez a WBook List Next folyamatlépést;
4. használd a WBook List Start Djeeni nevét mint forrás munkalap hivatkozást minden folyamatlépésben az egyetlen munkalap WSheet Use lépésben megadott Djeeni neve helyett;
5. cseréld le a cél munkalapon a cellák és tartományok hivatkozásait, hogy
   • hozzáadhass sorokat;
   • beszúrhass sorokat; és
   • összegezhess értékeket ahelyett, hogy minden egyes munkalap felülírná ugyanazt a cellát újra és újra. Ezeket lásd lejjebb részletesen.

Kész!

Az egy munkalapos konszolidáció átmásolt néhány (feldolgozott) adatot a forrás munkalapról a cél munkalap bizonyos celláiba/ tartományaiba. A konszolidációs sablonban pedig van elég hely, hogy az összes forrás munkalap adatait bemásolhassuk. De hogyan találjuk meg az első üres sort/cellát? Az első üres sor - természtesen- egy sorral lejjebb/cellával jobbra van az utolsó sortól/cellától amelyikben adat van. Az utolsó, adatot tartalmazó, sort könnyű megadni az A oszlopban:

#RowEnd

és egy másik oszlopra (pl. F):

#RowEnd|F

Egy sorral lejjeb lépni (hogy megkapjuk az első üres sort):

[+[#RowEnd|F]+1]

Tegyük fel, hogy az egy munkalapos verzió átmásolt (a Cell Set vagy Range Set vagy Range Copy/Move folyamatlépések valamelyikével) egy adatot a wsConsolidation!D2 cellába. Hozzáadásnál (azaz azért, hogy elkerüljük a már bent lévő adat felülírását) ezt meg kell változtatni erre: wsConsolidation!D[+[#RowEnd|D]+1].

Egy másik módja a konszolidáció elkészítésének (amennyiben a cél munkalap sablonja csak egy fejlécet tartalmaz fix struktúra nélkül), hogy a forrás munkalapról származó új sorokat a cél munkalapon a már ottlévő sorok elé szúrjuk be. Ekkor nincs szükség a munkalap alján lévő első üres sor kiszámolására.

Tegyük fel, hogy a konszolidációs lista a wsConsolidation munkalap harmadik sorában kezdődik. Ahhoz, hogy a konszolidáció több munkalappal is működjön, ezeket a változtatásokat kell elvégezni az adatfeldolgozó lépésekben:

• Ha Range Copy/Move lépést használsz, akkor az egyetlen változtatás az Insert / Overwrite parameter Insert értékre állítása.
• Ha Cell Set vagy Range Set lépést használsz, akkor ezt a lépést meg kell, hogy előzze egy Insert Column/Row lépés, amelyik beszúr egy új, üres sort a negyedik sor elé.

A konszolidáció egyrészről adatok összegyűjtése listákba. Másrészről viszont adatok összegzése (összeadása, átlagolása stb.) több helyről a cél munkalap egy cellájában. Egy munkalapnál (azaz egyetlen forrás adatnál) egy egyszerű Cell Set lépés elvégzi az adat másolását. És több forrás munkalapnál? Voila! A több forrás munkalapos verzió is a Cell Set lépést használja. Az egyetlen változtatás a már a cellában lévő érték felhasználása egy MS Excel függvény segítségével, amivel előállítható az új célérték.

Példa 1: A forrás adatok összegét kell kiszámolni.

'Egyetlen forrás munkalap
  Cell Set    Cell: wsTarget!C4
              Value: [=wsSource!D5]
 
'Listás verzió
  Wbook List Start
  ...
  Cell Set    Cell: wsTarget!C4
              Value: [+[=wsTarget!C4]+[=wsSource!D5]]
  ...
  WBook List Next

Példa 2: A forrás adatok átlagát kell kiszámolni. Ehhez szükségünk van egy segédcellára, amelyik számolja az átlagolandó adatok számát. A Wbook List lépés előtt beállítjuk 0-ra, majd a Wbook List lépés után az összegzett értéket elosztva megkapjuk a keresett átlagot.

'Egyetlen forrás munkalap
  Cell Set    Cell: wsTarget!C5    'az adatok száma
              Value: 1
  Cell Set    Cell: wsTarget!C4    'az egyetlen adat átlaga
              Value: [=wsSource!D5]
 
'Listás verzió
  Cell Set    Cell: wsTarget!C5    'kezdőérték
              Value: 0
  WBook List Start 
  ...
  Cell Set    Cell: wsTarget!C5    'az adatszámlálót megnöveljük eggyel
              Value: [+[=wsTarget!C5]+1]
  Cell Set    Cell: wsTarget!C4    'csak összeadunk
              Value: [+[=wsTarget!C4]+[=wsSource!D5]]
  ...
  WBook List Next
  Cell Set    Cell: wsTarget!C4    'itt számoljuk az átlagot
              Value: [+[=wsTarget!C4]/[=wsTarget!C5]]

Egy tipikus feladat egy kívülről származó forrás MS Excel munkalap adatainak (pl. egy beszállító cég által megadott adathalmaz vagy egy IT rendszer riportja) szétválogatása. A fő kérdés, hogy el kell dönteni, hogy hány és milyen cél munkalapot kell egyáltalán elkészíteni? A folyamat elején a cél munkalapok még nem léteznek, így a fileok nevei sem ismertek. Továbbá, az elkészítendő cél fielok halmaza is minden futtatáskor különböző lehet a forrás adatai vagy egyéb információk alapján. Mindezek fényében Djeeni három módon tud cél munkalapokat létrehozni:

1. Ha a cél munkalapok száma rögzített, akkor azokat meg lehet adni a WBook List lépésben;
2. Ha a cél munkalapokat valamilyen egyéb információ (például a cost centerek vagy a cégen belüli részlegek listája) alapján kell létrehozni, akkor ez a lista használható a Row List vagy Column List lépésekben.
3. Ha a cél munkalapokat a forrás munkalap saját információi alapján kell létrehozni, akkor a WSheet Split folyamatlépés használható

Lássuk az egyes módokat részleteiben!

Ez a mód hasonló az konszolidációhoz. A cél munkalapok (és -ha szükséges- a kapcsolódó munkafüzetek) egyesével lesznek létrehozva és feldolgozva. Az éppen aktuális munkalapra a WBook List folyamatlépés Djeeni nevével hivatkohatsz.

Often, processing of a source data set involves a supplementary list of some master data. They are mostly list of cost centers, departments, contacts, partners. The process should extract the relevant part from the source worksheet for each element in this supplementary list. In this case, the processing of the source worksheet is governed by the supplementary list. Djeeni uses the Row List Start … Row List Next process steps to take the elements of the supplementary list one by one and perform the corresponding operations. The actual steps are:

1. WSheet Use for the source worksheet
2. WSheet Use for the supplementary list
3. Optionally create the target workbook (using WBook New) in case all the target worksheets will be added into one workbook
4. Row List Start on the supplementary worksheet
5. Creating the next target workbook (WBook New) in case the target worksheets will be added to different workbooks OR adding the next worksheet to the before the Row List created target workbook;
6. performing the operations (mostly filtering the source worksheet and extracting the filtered data) using
   1. the Djeeni name of the Row List to refer to the information on the supplementary list
   2. the Djeeni name of the source worksheet to refer to the source data set
   3. the Djeeni name of the created/added worksheet to refer to the target data set
7. Row List Next to get to the next element of the supplementary list

These steps make the following Djeeni process (for different workbooks). Note how the target workbooks are uniquely named using the supplementary list information in WBook New.

  WSheet Use         Djeeni name: wsSourceData   'source data set
  WSheet Use         Djeeni name: wsDepartments  'supplementary list
  Row List Start     Djeeni name: rlDepts
                     Worksheet: wsDepartments
                     Row from: 2
                     Row to: #RowEnd|C
  WBook New          Djeeni name: wsDepTarget
                     Filename: [=rlDepts!D#] 'dept. name in column D of suppl. list
                     Type: Target
                     Worksheet Excel name: Report 2020 June
  ...   'operations for the current target worksheet referenced by wsDepTarget
  Row List Next                              

Once the process structure to create the target worksheets is ready the corresponding parts of the source data set should be selected for each target worksheet. In case the selection should be done using a known value (known constant values or values coming from a supplementary source using Row List) in a column of the source worksheet, filtering is the best practice. Djeeni implements the MS Excel autofilter (known as the dropdown boxes at every column header) using the WSheet Filter Add and WSheet Filter Clear process steps.

If multiple WSheet Filter Add is used on a worksheet then they are combined (as AND) and only the rows that match both filter criteria will be selected. Every Range and Cell operation works with the filtered data set.

Example 1: Fix (small) number of target workbooks and known filtering values

  WSheet Use         Djeeni name: wsSourceData
  WBook List Start   Djeeni name: wlTarget
                     Workbook list: c:\wbook1.xlsx;c:\reports\wbook2.xlsx
  If                 Condition: [#wlTarget|NAME]=wbook1
  WSheet Filter Add  Worksheet: wsSourceData
                     Column: D
                     Criteria 1:  1500
  ...   'operations for the first target worksheet referenced by wlTarget
  WSheet Filter Clear
  End If
  If                 Condition: [#wlTarget|NAME]=wbook2
  WSheet Filter Add  Worksheet: wsSourceData
                     Column: E
                     Criteria 1:  UK
  ...   'operations for the second target worksheet referenced by wlTarget
  WSheet Filter Clear
  End If
  WBook List Next

Example 2: Split source using a supplementary list of departments. Note how the target workbooks are uniquely named using the supplementary list information in WBook New.

  WSheet Use         Djeeni name: wsSourceData   'source data set
  WSheet Use         Djeeni name: wsDepartments  'supplementary list
  Row List Start     Djeeni name: rlDepts
                     Worksheet: wsDepartments
                     Row from: 2
                     Row to: #RowEnd|C
  WBook New          Djeeni name: wsDepTarget
                     Filename: [=rlDepts!D#] 'dept. name in column D of suppl. list
                     Type: Target
                     Worksheet Excel name: Report 2020 June
  WSheet Filter Add  Worksheet: wsSourceData
                     Column: D   'dept. ID in column D of the source data set
                     Criteria: [=rlDepts!F#]  'dept. ID in column F of suppl. list
  ...   'operations for the current target worksheet referenced by wsDepTarget
  WSheet Filter Clear
  Row List Next                              

Certain source data sets contain ranges that are identified by a start value and an end value. For example, a range of one month that starts on the first of the month and lasts till the first of the next month. In this case, the range can be identified by the two cells that can be found using Cell Lookup. The steps are:

1. Find the first cell of the range using Cell Lookup
2. Find the last cell (or the cell after the last cell) of the range using Cell Lookup
3. Specify the range using Range Use and the two cells

WSheet Use    Djeeni name: wsData
Cell Lookup   Djeeni name: ceFirst
              Value: 1-11-2020     'first of November
              Range: wsData!C1:C20000
Cell Lookup   Djeeni name: ceAfterLast                
              Value: 1-12-2020     'first of December
              Range wsData!C1:C20000
Range Use     Djeeni name: rgNovember
              Range: wsData!C[#ceFirst|row]:C[+[#ceAfterLast|row]-1]

Workbook lists have their own Djeeni names. These Djeeni names behave like the Djeeni name of a worksheet specified in WSheet Use. You can use the two Djeeni names interchangeably where appropriate.

WSheet Use       Djeeni name: wsTarget
Cell Set         Cell: wsTarget!C3

WBook List Start Djeeni name: wlTarget
Cell Set         Cell: wlTarget!C3

At row lists, there is always a current row that is referred by # instead of an actual row number. In case of nested row lists, # can be extended by the Djeeni name of the Row List.

WSheet Use     Djeeni name: wsOuter
WSheet Use     Djeeni name: wsInner
Row List Start Djeeni name: rlOuter
               Worksheet: wsOuter
Row List Start Djeeni name: rlInner
               Worksheet: wsInner
Cell Set       Cell: wsOuter!D[#|rlOuter]  'current row of the rlOuter row list
Cell Set       Cell: wsInner!D#   'the nearest (innermost) row list