Aruanded ja kokkuvõtted keemialaboris MS Exceli baasil
description
Transcript of Aruanded ja kokkuvõtted keemialaboris MS Exceli baasil
Aruanded ja kokkuvõtted keemialaboris MS Exceli baasil
Tartu Ülikool
Katsekoda
25. jaanuar 2002Koit Herodes
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!