Aruanded ja kokkuvõtted keemialaboris MS Exceli baasil

1. Aruanded ja kokkuvõtted keemialaboris MS Exceli baasil Tartu Ülikool Katsekoda 25. jaanuar 2002 Koit Herodes koit@chem.ut.ee

2. Kursuse programm • Pivot’ tabel • definitsioon ja ülevaade • pivot’ tabeli loomine • pivot’ tabeli kasutamine/kujundamine • Baasandmed (listid) • listide projekteerimine • MS Exceli vahendid listide koostamiseks • mõningaid operatsioone listidega

3. Pivot’ tabel > Definitsioon ja ülevaade 1 • pivot – pöördtelg; piltl.tähtsaim punkt (J.Silvet, Inglise-eesti sõnaraamat) • pivot’ tabel on interaktiivne tabel listina esitatud andmete töötlemiseks (filtreerimiseks, summeerimiseks jne). • list – tulpade ja ridadena esitatud andmete kogum.

4. Pivot’ tabel > Definitsioon ja ülevaade 2

5. Pivot’ tabel > Definitsioon ja ülevaade 3 elemendid (items) väljad (fields) tulemuste piirkond (data area)

6. Pivot’ tabel > Pivot’ tabeli loomine • Aktiveerida suvaline lahter analüüsitavas listis. • Valida Data menüüst PivotTable Report. • Jätta valik “Microsoft Excel list …” [Next >] • Kui analüüsitav list ei ole automaatselt valitud, siis märgistada see hiirega (ka päis!) [Next >] • Pukseerida väljade nimetused lehe, rea, veeru ja tulemuste raamidele [Next >] • Valida tulevasele pivot’ tabelile koht avatud või uuel töölehel [Finish]

7. Pivot’ tabel > Pivot’ tabeli kasutamine 1 • Pivot tabeliga tegelemiseks on olemas • PivotTable tegumiriba • Data menüü • pivot’ tabeli hüpikmenüüd • Samaliigilisi välju võib olla mitu (nt. kaks tulemuste välja ja kaks reavälja) • Välju saab lisada ja kustutada • Wizardis pukseerides • hüpikmenüüst (ainult kustutada)

8. Pivot’ tabel > Pivot’ tabeli kasutamine 2 • välju võib nihutada, kas otse pivot’ tabelis või Wizardis • pivot’ tabeli lähteandmete muutudes, värskendada pivot’ tabelit: hüpikmenüüs Refresh Data • pivot’ tabelit võib kujundada “käsitsi” (font, jooned) või AutoFormat abil (Format menüüs)

9. Pivot’ tabel > Pivot’ tabeli kasutamine 3 Tulemuste väli • nime muutmine • lahtri sisu parandades • välja hüpikmenüüs Field...>Name • andmete summeerimisfunktsioonid välja hüpikmenüüs Field...>Summarize by • tulemuste numbrivorming hüpikmenüüs Field...>Number...

10. Pivot’ tabel > Pivot’ tabeli kasutamine 4 Rea/veeru väli • nime muutmine • lahtri sisu parandades • välja hüpikmenüüs Field...>Name • elementide järjestust saab muuta neid pukseerides • elemente saab automaatselt järjestada elementide endi või vastavate tulemuste järgi: hüpikmenüü Field...>Advanced...

11. Pivot’ tabel > Pivot’ tabeli kasutamine 5 Rea/veeru väli (järg) • elementide peitmine • välja hüpikmenüüst Field...>Hide Items • vastava elemendi hüpikmenüüst Delete • detailide peitmine/kuvamine (mitme rea/veeru välja korral) • topeltklõps vastaval elemendil peidab/kuvab detailid • elemendi hüpikmenüüst Group and Outline> Hide Detail või Show Detail

12. Pivot’ tabel > Pivot’ tabeli kasutamine 6 Rea/veeru väli (järg) • elementide “käsitsi” grupeerimine • märgista vajalikud elemendid ja hüpikmenüüst Group and Outline>Group... • grupeerimine numbriliste ja kuupäevaliste elementide korral • “käsitsi” • välja hüpikmenüüst Group and Outline> Group...

13. Pivot’ tabel > Pivot’ tabeli kasutamine 7 Rea/veeru väli (järg) • vahekokkuvõtted • välja hüpikmenüüst Field...>Subtotals • üldsummad (Grand Total) • pivot’ tabeli seadetest - hüpikmenüüst Options> Grand totals for columns või Grand totals for rows

14. Pivot’ tabel > Pivot’ tabeli kasutamine 8 Lehekülje väli • nime muutmine • lahtri sisu parandades • välja hüpikmenüüs Field...>Name • elementide peitmine • välja hüpikmenüüst Field...>Hide items

15. Pivot’ tabel > Pivot’ tabeli kasutamine 9 Lehekülje väli (järg) • elementide grupeerimine 1. lehkülje väli pukseerida rea või veeru väljaks 2. grupeerida vastav väli 3. pukseerida väli lehekülje väljale tagasi 4. vajadusel kustudada üleliigsed väljad

16. Baasandmed > Listide projekteerimine 1 • list - ridadesse ja veergudesse organiseeri-tud andmete kogum, kus veerg määrab andmete iseloomu ja tüübi, ning read väljendavad konkreetseid üksikjuhte • väli - rea ja veeru ristumiskoht

17. Baasandmed > Listide projekteerimine 2 • listide projekteerimisel tuleb arvestada • andmete struktuuri • edasise töötlemise vahendeid ja võimalusi • listide koostamise reeglid • kõik väljad olgu täidetud • minimaalselt korduvat infot • korrektsed andmed!

18. Baasandmed > Exceli vahendid 1 • fraasi automaatne lõpetamine -AutoComplete (Tools>Options>Edit>Enable AutoComplete for cell values) • valikumenüü - lahtri hüpikmenüüs Pick From List • otsing ja asendamine märgistatud piirkonnas või kogu töölehel - Edit>Replace...

19. Baasandmed > Exceli vahendid 2 Arvutused listis st. kopeerimisega • isegi =3*3 on võimalik! :-) • tehted veergudega nt. =A3*B3 , =sin(A3) • suhtelised ja absoluutsed aadressid • tehe veeru ja konstandiga nt. =$A$1/A3 • funktsioone vektoritega nt. =sum(A3:A10) • veerunime kasutamine valemis nt. =10^pH (Tools>Options>Calculation>Accept labels in formulas)

20. Baasandmed > Exceli vahendid 3 Väärtuste otsimine eraldiseisvast tabelist identifikaatori järgi • =VLOOKUP(id;otsitabel;veerg;otsimisviis) • id - otsitav väärtus • otsitabel - viide tabelile, mille esimeses veerus on otsitavad väärtused • veerg - veeru number, kust tagastatakse väärtus • otsimisviis - meie juhul FALSE

21. Baasandmed > Exceli vahendid 4 • Loogilised avaldised • tõesuse kontrolliks • tingimuslikuks arvutamiseks (vt. =IF()) • =IF(kontroll;tulem_tõene;tulem_väär) • kontroll - loogiline avaldis • tulem_tõene - väärtus, kui [kontroll]=TRUE • tulem_väär - väärtus, kui [kontroll]=FALSE

22. Baasandmed > Exceli vahendid 5 • Andmete sisestamiseks/kontrolliks • sisestusvorm - aktiivne lahter listis ja siis Data>Form • sisestuskontroll (piirang) - märgista veerg ja Data>Validation • olemasoleva listi kontroll (vastavalt sisestuskontrolli piirangutele) - Tools> Auditing>Show Auditing Toolbar>Circle invalid data

23. Baasandmed > Exceli vahendid 6 Et ei juhtuks äpardust ... • lahtri sisu lukustamine 1. vali lahtrid, mis peavad avatuks jääma 2. Format>Cells>ProtectionLocked eest linnuke ära 3. Tools>Protection>Protect Sheet

24. Baasandmed > Exceli vahendid 7 Mallid (template) - töölehed, mida kasutatakse teiste sarnaste töölehtede alusena • lihtsa malli loomine 1. vormista tööleht, millel on vajalikud valemid, piirangud (andmekontroll), vormistus jmt, kuid puuduvad andmed 2. salvesta mall - File>Save As>Save as type: Template (*.xlt) 3. malli kasutamiseks topeltklõps malli nimel NB! Jälgi järgmist punkti!

25. Baasandmed > Exceli vahendid 8 • malli redigeerimine 1. ava mall File>Open... menüüst või vastava nupuga 2. redigeeri malli sisu 3. salvesta Seega, malli KASUTAMISEKS ei tohi seda avada File>Open... (või Open nupu) abil!

26. Baasandmed > Exceli vahendid 9 Andmekaardid(?) (Template Wizard) 1. vormista tööleht, millel on vajalikud valemid, piirangud (andmekontroll), vormistus jmt, kuid puuduvad andmed 2. käivita mallitark(?) - Data>Template Wizard ... 3. määra malli nimi ja asukoht 4. määra andmebaasi (listi) tüüp ja asukoht 5. määra lahtrid, kust võetakse andmed, ning nimed moodustuva andmebaasi veergudele 6. Finish

27. Baasandmed > Exceli vahendid 10 Andmekaardid - kasutamine • andmekaarti kasutatakse nagu tavalist malli • malli avanemisel küsitakse, kas makrosid võib kasutada - vastata Enable macros • andmekaardi alusel koostatud tabel tuleb kindlasti salvestada • salvestamisel küsitakse, kas andmeid on vaja andmebaasi salvestada - täpp jätta Create new record ette ja siis OK

28. Baasandmed > Valik operatsioone • sorteerimine • Data>Sort • vastavad nupud tegumiribal • filtreerimine • Data>Filter>AutoFilter • filtreeritud andmete kopeerimine • märgista ainult nähtavad read - Edit>Go To> Special>Visible cells only • edasi kopeeri nagu tavaliselt ...

29. Lisaküsimused

30. Tänan tähelepanu eest!