01. Automatizuotas duomenų surinkimas
Dirbant su periodinėmis ataskaitomis (pvz., vadybininkų sąnaudos kas mėnesį naujame lape), kritiškai svarbu sukurti dinaminę užklausą, kuri pati atpažintų naujus lapus.
Excel.CurrentWorkbook()
Naudojama, kai duomenys yra toje pačioje darbaknygėje. Leidžia apjungti visas lenteles ar vardinius diapazonus vienu metu.
Excel.Workbook(File.Contents)
Naudojama jungiantis prie išorinio failo. Galime pasiekti ne tik lenteles, bet ir visus darbalapius (Sheets).
02. Darbas su netvarkingomis lentelėmis
Vadybininkų išlaidų failai dažnai turi „žmogišką" struktūrą, kuri netinka analizei (tuščios eilutės, antraštės per kelis stulpelius, vardai surašyti virš sąnaudų blokų).
Vadybininko vardas nurodytas tik pirmoje eilutėje? Funkcija „Fill Down" užpildys tuščias ląsteles vardo reikšme iki kito vadybininko.
Pašalinami įmonės rekvizitai, ataskaitos pavadinimai ir kiti nereikalingi duomenys virš pagrindinės lentelės.
Išfiltruojamos tarpinės sumos, brūkšneliai ir tuščios eilutės, kurios atsiranda dėl vizualinio lentelės formatavimo.
03. Transformacijų strategijos
Merge vs Append
Append (Pridėti): Naudojame vadybininkų sąnaudų apjungimui iš skirtingų mėnesių (Sausis + Vasaris + Kovas).
Merge (Sujungti): Naudojame, kai prie sąnaudų reikia pridėti vadybininko departamentą ar regioną iš atskiro žinyno.
Pivot & Unpivot
Jei išlaidos (kuras, ryšiai, kanceliarinės) suvestos į atskirus stulpelius, naudojame Unpivot, kad gautume du stulpelius: „Išlaidų rūšis" ir „Suma".
Tai kritinis žingsnis norint vėliau duomenis naudoti Power Pivot modeliuose.
Praktinė užduotis: Vadybininkų išlaidų sutvarkymas
Užduoties situacija:
Turite failą su vadybininkų sąnaudomis, kur duomenys kiekvieną mėnesį pateikiami atskiruose lapuose. Lentelė prasideda tik nuo 7 eilutės, vadybininko vardas parašytas virš jo išlaidų sąrašo, o kai kuriuose stulpeliuose yra suvestos tarpinės sumos.
- Prijunkite failą naudodami
Excel.Workbookfunkciją. - Pašalinkite viršutines eilutes ir nustatykite teisingas antraštes.
- Sukurkite naują stulpelį „Vadybininkas", panaudodami sąlygas ir Fill Down techniką.
- Išfiltruokite tuščias eilutes ir tarpines sumas.
- Atlikite Unpivot transformaciją, jei išlaidų tipai pateikti stulpeliuose.
- Užtikrinkite, kad „Suma" stulpelio tipas būtų „Decimal Number".