Tabelarvutus ja andmete analüüs algajast edasijõudnuks (26 tunnine koolitus)

Kasutatav tarkvara: MS Excel (Koolitus sobib tarkvarade Excel 2019 - Office 365)

Sihtgrupp: Kasutaja, kellel on olemas MS Excel töötamise kogemus põhioskuse tasemel ja kes soovib omandada oskused professionaalsel tasemel igapäevaseks tööks Exceliga. 

Praktiline Exceli koolitus MS Exceli kasutajale, kes soovib õppida juurde uusi võimalusi ja rakendusi, et muuta tabelarvutusprogrammiga töötamine efektiivsemaks, dünaamilisemaks. Koolituse põhiteemadeks on funktsioonide kasutamine, kombineerimine. Mahukate andmetabelite kokkuvõtted, analüüsimine ja optimeerimine. Andmete importimine, PowerQuery tööriista kasutamine tabelite vaheliste seoste ja arvutusreeglite loomiseks.  

​

Eelteadmised: Tabelarvutusprogrammi kasutamise kogemus on vajalik põhioskuse tasemel või võrdväärsed teadmised järgneval koolitusel (vt link): Tabelarvutuse baaskoolitus MS Excel baasil

Koolitus koosneb kahest moodulist:

I moodul: Tabelarvutus ja andmete analüüs edasijõudnu oskuste omandamine (2 päeva, mõlemal päeval 6,5 akadeemilist tundi)

II moodul: Kokkuvõtete sooritamine ja töötamine imporditud andmetega (2 päeva, mõlemal päeval 6,5 akadeemilist tundi)

​

Koolituse programm

​Tabelarvutus ja andmete analüüs MS Excelis edasijõudnu oskuste omandamine

I moodul:

Tulemus (I moodul): Koolituse läbinul on olemas põhjalikud oskused töötamiseks tabelarvutusprogrammiga. Koostada erinevaid arvutavaid tabeleid. Sooritada vastavalt vajadusele analüüse, kasutades selleks tabelarvutusprogrammi funktsioone ja analüüsivõimalusi

Koolituse programm

Töötamine tabelarvutusprogrammis MS Excel:

• Tabeli ülesehituse loogika ja tabeli ettevalmistus arvutusteks, analüüsimisteks. Ülesehitusvigade kõrvaldamise võimalused Exceli tööriistadega

Töötamine andmetega:

• Erinevad andmetüübid (%, neg arv, kuupäev, kell, valuuta, tekst, nr) ja nende mõju arvutamisele
• Sisestatud andmete vormindamine ja nende mõju arvutustele
• Vormingute eemaldamine
• Andmete paigutamine erinevate töölehtede ja failide vahel, ülesehituse loogika

Arvutused ja funktsioonid Excelis:

• Tehete järjekord ja sulgude kasutamine
• Nimelised piirkonnad andmetabelites ja nende kaudu töö lihtsustamine
• Põhifunktsioonide kasutamine (summa- sum, loendus- count, keskmine- average, suurim- max, väiksem- min)
• Ümardusfunktsioonid (round, roundup, rounddown, mround, floor, ceiling)
• Tekstifunktsioonid andmete parandamiseks ja muutmiseks (clean, trim, upper, proper, lower, text, substitute)
• Andmete tükeldamise ja ühendamise funktsioonid (left, right, mid, concatenate)
• Kuupäevafunktsioonid (weeknum, networkdays, workdays, day, month, year)
• Andmebaasifunktsioonid kokkuvõtete tegemiseks (dsum, dcount, daverage)
• Tingimusfunktsioonid (if, and, or, true, false, sumif, countif, iferror)
• Otsingufunktsioonid tabelites (vlookup, hlookup, match, xlookup, vstack)
• Filtreerimisfunktsioonid (filter, sort, choosecols jt)
• Erinevate funktsioonide kombineerimine omavahel (nt mitme IF funktsiooni kombineerimine, veateadete kõrvaldamine jt)
• Absoluutne ja suhteline aadress valemites
• Arvutamine mitme tabeli ja erinevate töölehtede vahel
• Valemirea kasutamine, funktsioonide jälitamine

Mahukate andmetabelite kasutamine:

• Suurte andmemahtudega tabelite kasutamine (külmutamine, jaotamine, andmete otsimine) (Freeze, Split, Side by Side)
• Andmete sorteerimine, sorteerimise seaded (kuupäevade järgi sorteerimine, sorteerimistingimuste loomine)
• Andmete grupeerimine (Group)
• Andmete filtreerimine, laiendatud filtri kasutamine, unikaalsete andmete kuvamine (Filter, Advanced Filter)
• Vahekokkuvõtted gruppide kaupa (Subtotal)
• Konsolideerimise kasutamine kokkuvõttes, mitme tabeli ja faili koond (Consolidate)
• Eesmärgistatud otsing (Goal Seek) (õige lähtearvu leidmine tabelis)
• Liigendtabeli koostamine (PivotTable), lugemine ja muutmine. Funktsioonid risttabelis. Lõpparuandes andmete esitamine % ja nr väärtustes. Kuupäevaliste aruannete koostamine (kuude, kvartalite aastate lõikes). Arvutamine väljadega (arvutuste lisamine risttabelisse), diagrammid
• Stsenaariumite kasutamine prognoosimisel (Scenario Manager)
• Rippmenüüde kasutamine tabelite täitmisel ja Indirect funktsioon

Tabelite kujundamine:

• Tingimusega kujundamine, tingimusliku kujunduse lisamine läbi funktsiooni (Conditional Formatting)

Diagrammid:

• Diagrammi koostamine tabelis olevatest andmetest
• Mitmest tabelist diagrammi koostamine
• Diagrammile andmete lisamine ja eemaldamine
• Diagrammi kujundamine, töö skaalaga

Salvestamine ja väljatrükk:

• Salvestusformaadid ja salvestamine
• Lk seaded väljatrükiks (lk nr, päis jalus, äärised, prindiala)
• Tabelite, graafikute väljatrüki häälestamine

Tabelite kaitsmine ja ühine koostöö:

• Tabeli kaitsmise võimalused (faili kaitsmine parooliga, lahtrite kaitsmine parooliga, osaline kaitsmine)
• Tööraamatu jagamine arvutivõrku ja ühise tööraamatu kasutamine
• Muudatuste jälitamine ja andmete taastamise võimalused
• Sisestuspiirangute lisamine tabelitele (teksti lubamine, numbri lubamine, tühiku kontroll jt)

Andmete importimine:

• Andmete importimine Excelisse välistest andmeallikatest. CSV, Internet jt

Kokkuvõtete sooritamine ja töötamine imporditud andmetega

II moodul

Tulemus (II moodul): Koolituse tulemusena oskavad kasutajad muuta valemite ja funktsioonide abil Exceli tabeleid dünaamilisemaks, optimeerida tabeleid funktsioonide kombineerimise kaudu. Kasutada tabeli lahtrites ripploendeid, funktsioone ning nuppe makrode käivitamiseks, saada aru makrode põhimõttest. Importida, korrastada andmeid, teha kokkuvõtteid Exceli tabelitest (PowerQuery, PivotTable).

​

Koolituse teemad:

Mida teha, kui andmeid hoitakse mujal ja andmeid on vaja Exceli kasutada? Andmete importimine PowerQuery abil: Andmete importimine ja töötamine imporditud andmetega (teksti fail, andmebaas, Internet), importimise seaded (properties). Imporditud andmete parandamine (probleemne tühik, punkt ja koma nr eraldajana), andmete jaotamine veergude vahel (erinevate probleemide lahendamine andmete jaotamisel). Meeskonnatöö Exceli failidega. Andmete pärimine kolleegi tabelist.

​

Kuidas puhastada andmeid ebasobivatest sissekannetest? Char kooditabeli kasutamine andmete jaotamisel veergude vahel (Substitute, Char, Find, Search, Left, Right, Mid, Trim, Concatenate, IF funktsioonide kombineerimine andmete lõikamisel või jaotamisel veergude vahel). Andmetabelite optimeerimine, mida arvestada mahukate andmetabelite kasutamisel.

​

Erinevad tingimuslaused ja kuidas tingimuslauseid kombineerida? Tingimusarvutused Excelis (IF ja mitme IF funktsiooni kasutamine, AND, OR tingimused, DCOUNT, DSUM, SUMPRODUCT jt)

​

Andmete võrdlemine ja otsimine erinevate tabelite vahel.

Kuidas luua seoseid 3-4 tabeli vahel? Kuidas viia andmeid kokku, kui kahe tabeli vahel erineb otsingutunnus teineteisest? Andmete otsimine, võrdlemine ja kokku viimine Vlookup ja Match, Index funktsioonide abil. Mitme VLOOKUP funktsiooni kasutamine Vlookup valemis (otsingud läbi mitme andmetabeli). Erineva kirjapildiga andmete kokku viimine (Fuzzy Lookup). PowerQuery abil andmete ühendamine, PowerQuery Vlookup asemel. Erinevad kokkuühendamise võimalused ja variandid (seosed üks ühele, üks mitmele jt). Uus võimalus tabelitevaheliste seoste loomiseks XLOOKUP funktsiooni abil. VSTACK, FILTER, SORT, CHOOSECOLS funktsioonide kombineerimine koos näidetega.

​

Mida teha, kui suurtes tabelites on Vlookup väga aeglane? Appi tuleb PowerQuery: Mis on PowerQuery ja kuidas seda käivitada, kasutada? Andmetabelite kokku viimine, päringute loomine. Andmete importimine PowerQuery abil. Päringute haldamine, kohandamine, seoste loomine. Valemite kasutamine PowerQuery aknas. Reeglite loomine ja nende abil andmete automaatne kohandamine.

PowerQuery abil kataloogis olevate kõikide Exceli failide sisude importimine Exceli töölehele suurde andmetabelisse From Folder Improt (kõikide Exceli failide Import kataloogidest).

Kuidas importida andmeid Excelisse PDF failidest ja fotodelt? PowerQuery kasutamine fotodelt-, PDF failidest andmete importimisel. Erinevate lehekülgede kokku koondamise ühtsesse andmetabelisse.

​

Ripploendid ja tabelipõhjad

Kuidas luua ripploendeid tabelis, kus ripploendi sisu muutub automaatselt algväärtuste muutmisel. Dünaamilise sisuga ripploendid: Ripploendite kasutamine (DataValidation ja Indirect funktsioon). Nimeliste piirkondade kasutamine viitamistel, registri loomine ja sellest andmete pärimine. Ripploendi kaudu Filter funktsiooni kasutamine, uus võimalus Exceli tabelist andmete väljavõtmiseks funktsioonide abil mõnele teisele töölehele.

Mis on makro ja kuidas seda kasutada tegevuste optimeerimisel: Sissejuhatus makrodesse, makrode lindistamine. Nuppude kaudu makrode käivitamine, makrode lindistus keerukamate filtrite rakendamiseks ja andmetabelist väljavõtete sooritamiseks (Advanced filter, Record macro)

Aruanded PivotTable abil (mitmest tabelist kokkuvõte ühiste tunnuste järgi- Relationships ja Pivoti ehitamine läbi andmemudeli (Workbook DataModel). Pivoti konverteerimine Cube valemiks ja aruande kasutamine edasisteks arvutamisteks, aruanded kuupäevadega ja TEXT funktsiooni kasutamine. Pivoti kasutamine varem loodud dokumendi põhjale (template), andmekuubi liigutamine, seoste säilitamine algandmetega. Kuidas Pivoti aruandes valemeid kasutada, kuidas kasutada Pivot aruandes andmemudelit, mis võimaldab kasutada ühes aruandes mitut erinevat andmetabelit?

​

Kuidas valemite abil seadistada tingimusvorminguid (kujundusi) ja seadistada valemitega sisestuspiiranguid? Tingimusvormingud (Conditional Formatting) läbi funktsioonide. Sisestuspiirangute kasutamine ja sisestuste kontrollimine läbi funktsioonide (nt järgmise lahtri lubatud täitmine, kui eelnev on täidetud, tühja lahtri keelamine jt sisestuskontrollid). Blanketid ja vormid Excelis. Sisestuspiirangud kasutajatele.

​

Analüüsi tööriistade valikus pakub Excel Data table tööriista. Mis see on ja milleks see on hea, kus kasutada?

Lisaks käiakse koolitusel praktilise töö käigus läbi hulgaliselt erinevaid nippe ja lahendusi, kuidas muuta rutiinseid tegevusi ja tülikaid tabeleid selliseks, et neid oleks edaspidi hea kasutada.

​Õppemeetodid: Õppijad omandavad teadmisi läbi praktiliste tegevuse tarkvara kasutades

Hindamismeetod: Praktilise töö (harjutuste) sooritamine

Hindamiskriteerium: Õpiväljundite saavutamist kontrollitakse läbi praktiliste harjutuste

Täiendkoolituses osalenule väljastatakse tunnistus või tõend:

Tunnistus täiendkoolituse läbimise kohta väljastatakse, kui koolituse käigus hinnatud õpiväljundid on saavutatud. Tõend täienduskoolituses osalemise või selle läbimise kohta väljastatakse juhul, kui koolituse käigus ei saavutatud kõiki õppekava lõpetamiseks nõutud õpiväljundeid.

​

Päevi: 4

Tunde: 26 akadeemilist tundi jaotatuna 4 päevale

I moodul: 2 x 6,5 akadeemilist tundi

II moodul: 2 x 6,5 akadeemilist tundi

Toimumise koht: Akadeemia tee 21-1 2 korrus, Tallinn, Eesti, Koolitusruum Ceres

Koolitaja: Kristjan Sakk

Investeering kokku kõikidele moodulitele: 660 € +22% km