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.

Egy forrás adathalmaz feldolgozásakor sokszor kell használnunk valamilyen egyéb törzsadatlistát. Legtöbbször cost centerek, részlegek, kapcsolatok, partnerek listáját. A Djeeni folyamatnak a forrás munkalap adatait a törzsadatlista egyes elemeihez tartozóan kell szétválogatnia; azaz a folyamatot a törzsadatlista vezérli. Djeeni a Row List Start…Row List Next folyamatlépéseket használja a törzsadatlista elemeinek egyesével történő eléréséhez és a kapcsolódó műveletek elvégzéséhez. A folyamat konkrét lépései a következők:

1. WSheet Use a forrás munkalap használatba vételéhez
2. WSheet Use a törzsadatmunkalap használatba vételéhez
3. Opcionálisan egy cél munkafüzet létrehozása (a WBook New folyamatlépéssel), amennyiben minden cél munkalap egyetlen munkafüzetbe kerül
4. Row List Start a törzsadatmunkalapon
5. VAGY Elkészíteni a következő munkafüzetet (WBook New), amennyiben minden cél munakalap különböző munkafüzetekbe kerül; VAGY Hozzáadni a következő munkalapot a Row List előtt létrehozott munkafüzethez;
6. Elvégezni a szükséges műveleteket (legtöbbször szűrni az adatokat majd a leszűrt adatokat a cél munkalapra másolni):
   1. a Row List Djeeni nevével hivatkozva a törzsadatmunkalap adataira;
   2. a forrás munkalap Djeeni nevével hivatkozva a forrás munkalap adataira;
   3. a létrehozott/hozzáadott munkalap Djeeni nevével hivatkozva a cél munkalap adataira;
7. Row List Next a törzsadatmunkalap következő sorára lépéshez

Ezekből a lépésekből a következő Djeeni folyamatot lehet készíteni (különböző munkafüzetekkel). Figyeld meg, hogy hogyan kap minden munkafüzet egyedi nevet a törzsadatlista információi alapján a WBook New folyamatlépésben.

  WSheet Use         Djeeni name: wsSourceData   'forrás adathalmaz
  WSheet Use         Djeeni name: wsDepartments  'törzsadatlista
  Row List Start     Djeeni name: rlDepts
                     Worksheet: wsDepartments
                     Row from: 2
                     Row to: #RowEnd|C
  WBook New          Djeeni name: wsDepTarget
                     Filename: [=rlDepts!D#] 'a részleg neve a törzsadat D oszlopból
                     Type: Target
                     Worksheet Excel name: Report 2020 June
  ...   'az aktuális cél munkafüzet műveletei a wsDepTarget Djeeni név alatt
  Row List Next                              

Hamarosan!

Ha a cél munkalapok elkeszítésének struktúrája kész, akkor a forrás adathalmaz megfelelő részeit kell kiválasztani az egyes cél munkalapokhoz. Ha a kiválasztás a forrás munkafüzet egy oszlopában lévő valamely ismert adat (vagy egy fix adat vagy egy törzsadatforrásból a RowList folyamatlépés használatával származó törzsadat) alapján történik, akkor a szűrés alkalmazása a legjobb gyakorlat. Djeeni az MS Excel Autofilter funkcióját (ami az oszlopok fejlécében lévő lenyíló dobozokként ismert) kínálja a WSheet Filter Add és a WSheet Filter Clear folyamatlépések formájában.

Ha több WSheet Filter Add szűrést is használunk egy munkalapon, akkor azok összeadódnak (mint egy ÉS kapcsolat) és csak azok a sorok kerülnek kiválasztásra, amelyek mindkét szűrési feltételnek megfelelnek. Minden Range és Cell folyamatlépés a szűrt adatokkal dolgozik.

Példa 1: Fix (kevés) számú cél munkafüzet és ismert szűrési adatok.

  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
  ...   'a wlTarget Djeeni névvel használt első cél munkafüzet műveletei
  WSheet Filter Clear
  End If
  If                 Condition: [#wlTarget|NAME]=wbook2
  WSheet Filter Add  Worksheet: wsSourceData
                     Column: E
                     Criteria 1:  UK
  ...   'a wlTarget Djeeni névvel használt második cél munkafüzet műveletei
  WSheet Filter Clear
  End If
  WBook List Next

Példa 2: A forrás szétválogatása a törzsadatokban lévő részlegek nevei alapján. Figyeld meg, hogy hogyan kap minden munkafüzet egyedi nevet a törzsadatlista információi alapján a WBook New folyamatlépésben.

  WSheet Use         Djeeni name: wsSourceData   'forrás adathalmaz
  WSheet Use         Djeeni name: wsDepartments  'törzsadatlista
  Row List Start     Djeeni name: rlDepts
                     Worksheet: wsDepartments
                     Row from: 2
                     Row to: #RowEnd|C
  WBook New          Djeeni name: wsDepTarget
                     Filename: [=rlDepts!D#] 'részleg név a törzsadatok D oszlopában
                     Type: Target
                     Worksheet Excel name: Report 2020 June
  WSheet Filter Add  Worksheet: wsSourceData
                     Column: D   'részleg ID a forrás D oszlopában
                     Criteria: [=rlDepts!F#]  'részleg ID a törzsadatok F oszlopában
  ...   'a wsDepTarget Djeeni név alatt az aktuális cél munkafüzet műveletei
  WSheet Filter Clear
  Row List Next                              

Néhány forrás adathalmaz olyan tartományokat tartalmaz, amelyek a kezdő és végértékeikkel azonosítottak. Például egy hónap az első napjával kezdődik és addig tart, amíg a következő hónap első napja el nem jön. Ebben az esetben a tartmány a két cellával azonosítható, ameleyeket a Cell Lookup folyamatlépésben kereshetünk ki. A folyamat lépései:

1. Keressük ki a tartomány első celláját a Cell Lookup folyamatlépéssel;
2. Keressük ki a tartomány utolsó (vagy az utolsó utáni) celláját egy újabb Cell Lookup folyamatlépéssel;
3. Adjuk meg a tartományt a Range Use folyamatlépésben a két megtalált cella segítségével.

WSheet Use    Djeeni name: wsData
Cell Lookup   Djeeni name: ceElso
              Value: 2020-11-1     'november elseje
              Range: wsData!C1:C20000
Cell Lookup   Djeeni name: ceUtolsoUtan                
              Value: 2020-12-1     'december elseje
              Range wsData!C1:C20000
Range Use     Djeeni name: rgNovember
              Range: wsData!C[#ceElso|row]:C[+[#ceUtolsoUtan|row]-1]

A munkafüzetlistáknak saját Djeeni nevük van. Ezek a Djeeni nevek ugyanúgy viselkednek, mint a WSheet Use folyamatlépésben egy munkafüzetnek megadott Djeeni név. Mindkétfajta Djeeni nevet használhatod egy munkalapra hivatkozáshoz a megfelelő helyen.

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

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

A sorlistáknak mindig van egy aktuális sora, amelyre a sor száma heleyett a # jellel lehet hivatkozni. Amennyiben több sorlista van egymásba ágyazva, a # jel kiegészíthető a megfelelő sorlista Djeeni nevével.

WSheet Use     Djeeni name: wsKulso
WSheet Use     Djeeni name: wsBelso
Row List Start Djeeni name: rlKulso
               Worksheet: wsKulso
Row List Start Djeeni name: rlBelso
               Worksheet: wsBelso
Cell Set       Cell: wsKulso!D[#|rlKulso]  'az rlKulso sorlista aktuális sora
Cell Set       Cell: wsBelso!D#   'a legközelebbi (legbelső) sorlista aktuális sora