Aruanded ja kokkuvõtted keemialaboris MS Exceli baasil

Tartu Ülikool

Katsekoda

25. jaanuar 2002Koit Herodes

koit@chem.ut.ee

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

Pivot’ tabel > Definitsioon ja ülevaade

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.

1

Pivot’ tabel > Definitsioon ja ülevaade 2lab proov test tulem lab AA õun pH 4B pirn pH 5 Count of tulem testB ploom Ca 6 proov Ca pH Grand TotalA õun pH 3 kirss 1 1 2A pirn Ca 5 pirn 1 1A ploom pH 4 ploom 1 1B virsik pH 6 õun 2 2B virsik Ca 1 Grand Total 2 4 6A kirss pH 4A kirss Ca 5

Pivot’ tabel > Definitsioon ja ülevaade 3

lab A

Count of tulem testproov Ca pH Grand Totalkirss 1 1 2pirn 1 1ploom 1 1õun 2 2Grand Total 2 4 6

väljad (fields)

elemendid (items)

tulemuste piirkond (data area)

Pivot’ tabel > Pivot’ tabeli loomine

1. Aktiveerida suvaline lahter analüüsitavas listis.

2. Valida Data menüüst PivotTable Report.

3. Jätta valik “Microsoft Excel list …” [Next >]

4. Kui analüüsitav list ei ole automaatselt valitud, siis märgistada see hiirega (ka päis!) [Next >]

5. Pukseerida väljade nimetused lehe, rea, veeru ja tulemuste raamidele [Next >]

6. Valida tulevasele pivot’ tabelile koht avatud või uuel töölehel [Finish]

Pivot’ tabel > Pivot’ tabeli kasutamine

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)

1

Pivot’ tabel > Pivot’ tabeli kasutamine

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)

2

Pivot’ tabel > Pivot’ tabeli kasutamine

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...

3

Pivot’ tabel > Pivot’ tabeli kasutamine

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...

4

Pivot’ tabel > Pivot’ tabeli kasutamine

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

5

Pivot’ tabel > Pivot’ tabeli kasutamine

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...

6

Pivot’ tabel > Pivot’ tabeli kasutamine

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

7

Pivot’ tabel > Pivot’ tabeli kasutamine

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

8

Pivot’ tabel > Pivot’ tabeli kasutamine

Lehekülje väli (järg)

elementide grupeerimine1. lehkülje väli pukseerida rea või veeru väljaks2. grupeerida vastav väli3. pukseerida väli lehekülje väljale tagasi4. vajadusel kustudada üleliigsed väljad

9

Baasandmed > Listide projekteerimine

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

1lab proov test tulemA õun pH 4B pirn pH 5B ploom Ca 6A õun pH 3A pirn Ca 5A ploom pH 4B virsik pH 6B virsik Ca 1A kirss pH 4A kirss Ca 5

Baasandmed > Listide projekteerimine

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!

2

Baasandmed > Exceli vahendid

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...

1

Baasandmed > Exceli vahendid

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)

2

Baasandmed > Exceli vahendid

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

3

Baasandmed > Exceli vahendid

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

4

Baasandmed > Exceli vahendid

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

5

Baasandmed > Exceli vahendid

Et ei juhtuks äpardust ... lahtri sisu lukustamine

1. vali lahtrid, mis peavad avatuks jääma2. Format>Cells>Protection

Locked eest linnuke ära3. Tools>Protection>Protect Sheet

6

Baasandmed > Exceli vahendid

Mallid (template) - töölehed, mida kasutatakse teiste sarnaste töölehtede alusena

lihtsa malli loomine1. 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!

7

Baasandmed > Exceli vahendid

malli redigeerimine1. ava mall File>Open... menüüst või vastava

nupuga2. redigeeri malli sisu3. salvesta

Seega, malli KASUTAMISEKS ei tohi seda avada File>Open... (või Open nupu) abil!

8

Baasandmed > Exceli vahendid

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 asukoht4. määra andmebaasi (listi) tüüp ja asukoht5. määra lahtrid, kust võetakse andmed, ning nimed

moodustuva andmebaasi veergudele6. Finish

9

Baasandmed > Exceli vahendid

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

10

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 ...

Lisaküsimused

Tänan tähelepanu eest!