Valemid, avaldised ja sisefunktsioonid

Valemite struktuur ja põhielemendid

Valem on korraldus Excelile leida mingi väärtus ja salvestada see antud lahtris. See esitatakse kujul:

= avaldis

Võrdusmärk ( = ) on tunnuseks, et tegemist on valemiga. Suvalist sisendid, mis algab võrdusmärgiga, käsitleb Excel valemina. Kui see ei vasta valemite (avaldiste) esitamise reeglitele, siis väljastatakse veateade. Võrdusmärgi asemel võib olla ka pluss ( + ) või miinus ( - ). Viimast käsitletakse ühtlasi ka temale järgneva operandi märgina.

avaldis - määrab, millised tehted ja millises järjekorras peab täitma andmetega vajaliku väärtuse leidmiseks. Üldjuhul võib ta koosneda:

  • operandidest,

  • tehtemärkidest ja

  • ümarsulgudest

Operandideks võivad olla:

  • Konstandid

  • Viited lahtritele ja lahtriplokkidele.
    Lahtriviidad ehk muutujad osutavad lahtritele või lahtriplokkidele, kus asuvad valemi poolt kasutatavad andmed (väärtused). Kui neid väärtusi muudetakse, arvutab Excel valemi uue väärtuse. Viitamiseks lahtritele ja lahtriplokkidele võib valemites kasutada:
    aadresse ja nimesid.

  • Funktsiooniviited ehk lihtsalt funktsioonid.

Tehted jagunevad järgmistesse rühmadesse:

  • aritmeetikatehted : + , - , * , / , ^ , %

  • tekstitehe: & - sidurdamine või ühendamine

  • võrdlustehted : = , <> , < , <= , > , >=

Avaldiste liigid

Sõltuvalt kasutavatest tehetest ja neile vastavatest operandidest eristatakse sageli järgimisi avaldiste liike:

Üldjuhul võib olla aga tegemist nö segaavaldistega, kus esinevad koos erinevat liiki tehted ja andmed.

Ümarsulge kasutatakse tehete järjekorra määramiseks ja argumentide piiramiseks funktsiooniviidetes.

Excelis eristatakse ka skalaar- ja massiivavaldisi (valemeid). Skalaaravaldise tulemiks on üks väärtus, massiivavaldise tulemiks on vektor või maatriks.

Konstandid

Konstandi väärtus esitatakse vahetult valemis. Selle muutmiseks on vaja muuta valemit. Konstandi esitusviis sõltub andmetetüübist:

  • Arvkonstandid: 13 345,781 2,1e6 => 2,1•10 6 1e-8 => 10 -8

  • Tekstkonstandid: "N" "naine" "ei" "Peeter Kask"

Tekstkonstandid paigutatakse jutumärkide vahele. Jutumärgid ise konstandi väärtusesse ei kuulu .

  • Ajakonstandid : "26.10.91" "14:51:13"

Ajakonstandid paigutatakse samuti jutumärkide vahele .

  • Loogikakonstandid: on olemas ainult kaks väärtust - TRUE ja FALSE .

Konstantide kasutamise näiteid

= 3,14159 * ( 7,35 + 6,48 ) / 3,55

=SQRT( 13,4 ^ 2 + 26,8 ^ 4 )

= 2 * (a + b) = 2 * PI() * B6^ 2

=IF(palk <= 1700; 0; 0,22 * (palk - 1700 ))

=IF(LEFT(isikukood; 1 ) = " 3 "; "mees"; "naine")

=TODAY() - "21.03.89"

Aadressid

klipp: Aadressid

Excelis võib kasutada kahte tüüpi aadresse:

  • A1-tüüpi aadressid ja
  • R1C1-tüüpi aadressid.

A1-tüüpi aadress moodustatakse veerutähisest ja reanumbrist: A1, B13, AB21, B3:H53

R1C1-tüüpi aadressis kasutatakse nii rea kui ka veeru jaoks järjenumbreid, milledele eelnevad vastavalt tähed R ( R ow) ja C ( Column) : R1C1, R13C2, R21C28, R3C2:R53C8

Enamasti kasutatakse A1-tüüpi aadresse. Et kasutada R1C1-tüüpi aadresse, peab dialoogiboksi Options (kuvatakse korraldusega Tools/Options) vahelehel General lülitama sisse märkeruudu R1C1. Selle tulemusena, kuvatakse veerupäises tähtede asemel numbrid ja kõik A1-tüüpi aadressid valemites asendatakse R1C1-tüüpi aadressidega. Kuna R1C1-tüüpi aadresse kasutatakse väga harva, siis neid siin edaspidi ei käsitleta.

Sidusa lahtriploki ( piikonna ) aadress esitatakse kujul:

aadress1 : aadress2,

kus aadress1 on tavaliselt ülemise vasakpoolse lahtri aadress, aadress2 -  alumise parempoolse latri aadress: B2:D5, H1:CA5000, F2:F5 (tulp), D8:D8 (rivi), A1:IV65536

Ridadele ja veergudele võib viidata kahel erineval viisil: 13:13 = A13:IV256, 5:12 = A5:IV12, E:E = E1:E65536

Mittesidusa lahtriploki aadress esitatakse piirkondade aadresside loeteluna: B2:D6; F2:F5; B8:D8; F6; 11:12; H:I

Teise töölehe lahtri või lahtriploki aadress antud lehel esitatakse kujul:

lehenimi ! lahtriaadress,

kus lahtriaadress on lahtri või lahtriploki aadress:
Sheet3!B13, palk!H36, maksud!B5:E300, '1 . kvartali koond '!D13
NB! Kui lehenimi sisaldab tühikuid, peab ta esitama ülakomade vahel!

Teise töövihiku lahtri või lahtriploki aadress esitatakse kujul:

[ töövihiku_nimi ] lehenimi ! lahtriaadress,

kus töövihiku nimele võib eelneda ka tee: [palk.xls]juuni!E6:E50, [C:\aruanded\materjalid.xls]puit!D13

3-D -lahtriploki aadress esitatakse kujul:

lehenimi1 : lehenimi2 ! lahtriaadress

Näiteks: jaanuar:juuni!B5:H35
3-D -lahtriplokk kujutab endast kolmemõõtmelist andmekogumit (massiivi), mis paikneb mitmel järjestikul töölehel.

Suht-, absoluut­ ja sega-aadressid

Käitumise järgi valemite kopeerimisel eristatakse suht-, absoluut­ ja sega - aadresse.

Suhtaadress esitatakse tavalisel viisil, näiteks B5, E3:K35. Taolises aadressis võib kopeerimisel muutuda nii reanumber kui ka veerutähis, sõltuvalt kopeerimise sihtkohast.

Absoluutaadressis on veerutähise ja reanumbri ees dollari märk, näiteks $B$5, $E$3:$K$35. Taoline aadress kopeerimisel ei muutu.

Sega-aadressis on dollari märk ainult reanumbri või veerutähise ees, näiteks B$5 $B5. Taolises aadressis võib kopeerimisel muutuda ainult see osa, mille ees ei ole märki $.

Valemite kopeerimist ja erinevat liiki aadessite käitumist kopeerimisel vaadeldakse jaotises Tabelid. Kui valemit ei kopeerita, siis aadressi esitusviis ei oma tähtsust ning tavaliselt kasutatakse suhtaadresse.

Nimed

Nimesid võib kasutada viitamiseks lahtritele ja lahtriplokkidele aadresside asemel. Eriti otstarbekas on nimede kasutamine, kui rakenduses on tegemist pikkade ja keeruliste valemitega. Nime peab lahtrile või lahtriplokile eelnevalt määrama.

Põhireeglid nimede esitamiseks

  • Nimi võib koosneda ühest tähest või tähtede, numbrite, allkriipsude ja punktide jadast, mis peab algama tähega.
    Nimede näiteid: a, x, pikkus, palk, sum3, pk2st3, x_1, x.1 a_3, hind1, hind_1
  • Suur- ja väiketähti nimedes ei eristata: a = A, x_1 = X_1
  • Nimedeks ei saa olla üksikult tähed c ja r. Nende asemel võib kasutada näiteks taolisi nimesid: c_ ja r_. Erand on seotud R1C1-tüüpi aadresside erijuhtude kasutamisega.
  • Nimes ei tohi olla tühikuid. Näiteks ei saa kasutada taolisi nimesid: x 1, a 3, hind 2.
  • Nimi ei tohi langeda kokku lahtri aadressiga. Näiteks, ei saa kasutada taolisi nimesid: a1, x13. Nende asemel võiks olla: a_1, x_13.
  • Sama nimi ei saa olla töölehe mitmel lahtril või lahtriplokil. Erinevatel töölehtedel võib teatud piirangutega kasutada ühesuguseid nimesid (vt allpool globaalsete ja lokaalsete nimede kasutamist).
  • Ühel lahtril või lahtriplokil võib olla mitu nime.

Globaalsed ja lokaalsed nimed. Nimede skoop

Erinevatel töölehtedel asuvatel lahtriplokkidel võib olla sama nimi. Neist üks võib olla globaalne, kõik teised peavad olema lokaalsed.
Globaalne nimi võimaldab viidata temaga seotud lahtriplokile antud töövihiku suvaliselt töölehelt, kus ei ole määratletud sama tähistusega lokaalne nime.
Lokaalne nimi on mõeldud kasutamiseks eeskätt sellel lehel, kus asub temale vastav lahtriplokk. Viitamisel lokaalse nimega lahtriplokile teiselt lehelt peab lahtriploki nime ees näitame ka lehenime: Sheet2!a, juuni!palk.
Kas nimi on lokaalne või globaalne saab valida selle määramisel.

Nimede määramiseks, kasutamiseks ja haldamiseks on mitmeid erinevaid võimalusi ja vahendeid. Need sõltuvad teatud määral versioonist, kuid erinevused ei ole põhimõtelised.

  • Nimeboks. Võimaldab märata nimesid, näha olemasolevaid nimesid ja kasutada neid liikumiseks töövihikus. Kasutamine toimub ühtemoodi mõlemas versioonis.
  • Nimede defineerimine ja haldamine. Nimede loomine, eemaldamine ja muutmine. Info saamine nimede kohta. Sisuliselt sama mõlemas versioonis, kuid mõned tehnilised erinevused.
    Excel 2003. Insert-menüü käsk Name/Define
    Excel 2007. Vahekaardi Formulas käsud Define Name ja Name Manager
  • Nimede loomine. Saab korraga luua nimed mitme lahtri või või vektori (tabeli tulp või rivi) jaoks. Praktiliselt sama mõlemas versioonis. Erinevus käskude nimedes
    Excel 2003. Insert-menüü käsk Name/Create
    Excel 2007. Vahekaardi Formulas käsk Create from Selection
  • Nimede lisamine valemitesse ja korraldustesse
    Osutamine. Sama mõlemas versioonis
    Klahv F3. Kuvab boksi nimedega, kust saab valida vajaliku nime. Sama mõlemas versioonis.
    Käsk Use in Formulas. Ainult 2007. Praktiliselt sama nagu F3.

Nimeboksi kasutamine. Klipp Nimed 1

Nimeboksis, mis kujutab endast ripploendit, on loetelu kõikidest nimedest, mis on kasutatavad aktiivsel lehel: globaalsed nimed ja antud lehe lokaalsed nimed. Kui klõpsata mingit nime, muudab Excel aktiivseks (valib välja) nimele vastava lahtri või lahtriploki. Nimeboksi saab kasutada ka nimede määramiseks.

  • Valida välja lahtriplokk ja klõpsata nimeboksi. See muutub aktiivseks.
  • Tippida nimeboksi nimi ja vajutada klahvile Enter.

NB! Taoliselt saab määrata ainult globaalseid nimesid. Kui antud lehel on juba olemas sama tähistusega lokaalne või globaalne nimi või teisel lehel on sama tähistusega globaalne nimi, siis peale nime sisestamist nimeboksi, muudab Excel aktiivseks (valib välja) vastava lahtriploki.

Käsk Name/Define ja dialoogiboks Define Name (2003) Klipp Nimed 2

define
  • Märgistada lahtriplokk ning valida Insert-menüüst käsk Name/Define
  • Ilmuva dialoogiboksi Define Name väljas Names in workbook tippida vajalik nimi ja klõpsata nuppu OK. Lokaalse nime määramisel, peab lahtri nime ees näitama lehe nime, näiteks Variant_2!pikkus

Dialoogiboksist Define Name saab informatsiooni kõikide aktiivselt lehelt kättesaadavate nimede kohta. Boksi keskmises suures väljas on kättesaadavate nimede loetelu. Lokaalsete nimede jaoks on paremas servas lehe nimi. Näiteks siin on lokaalsed nimed: a, b, c_, pindala, ruumala (leht Variant_2). Väljas Names in workbook saab sisestada nime definatsiooni. Kui klõpsata nime loetelus, ilmub see antud väljas. Alumises väljas: Refers to, on nimele vastava lahtri või lahtribloki aadress (koos lehega).

Boksi saab kasutada ka nimede ja/või neile vastavate lahtriplokkide aadresside muutmiseks ning nimede eemaldamiseks.

Käsk Define Name ja dialoogiboks New Name (2007)

uus nimi

Käsu alusel kuvatakse dialoogiboks, mis võimaldab määrata lahtri või lahtriploki nime. See sisestakse välja Name.
Vaikimisi on tegemist globaalse nimega: väljas Scope (skoop -määramise piirkond) on Workbook. Lokaalse nime määramiseks valida väljas Scope vajaliku lehe nimi. Praegu valitud Variant_2.
Väljas Refers to on lahtri või lahtriploki aadress (koos lehe nimega). Seda saab muuta

Käsk Name Manager ja dialoogiboks Name Manager (2007)

Name Manager

Käsu alusel kuvatav dialoogiboks võimaldab saada infot kõikide töövihiku nimede kohta.
Iga nime jaoks on näidatud lahtris või lahtriplokis olevad väärtused, aadress koos lehenimega ja skoop: globaalne - Workbook, lokaalne - lehe nimi.

Boksi abil saab määratleda uusi nimesid: nupp New... Selle alusel kuvatakse boks New Name (vt eespool).
Nupuga Edit... saab kuvada dialoogiboksi Edit Name, kus saab muuta nii nime kui ka sellele vastava aadressi
Nupuga Delete saab kustutada valitud nime

 

Nimede loomine käsuga Insert/Name/Create (2003. Klipp Nimed 3) ja käsuga Create from Selection (2007)

Tegemist on praktiliselt sama käsuga. Seda kasutatakse tavaliselt nimede määramiseks korraga mitmele järjestikku paiknevale lahtrile, tulbale või rivile, kasutades ära lahtrite kohal või kõrval olevaid tähistusi (tekste):

  • Valida välja piirkond, mis sisaldab tähistusi ja lahtreid, milledele määratakse nimed.

Create name

  • Valida käsk Insert/Name/Create või Create from Selection
  • Ilmuvas dialoogiboksis Create Name näidata ära nimedeks mõeldud tähistuste (tekstide) asukoht: Top row (ülalpool), Left column (vasemal), Bottom row (allpool), Right column (paremal).
    boks

NB! Kui teisel lehel on juba olemas sama tähistusega globaalsed nimed, luuakse antud lehel automaatselt lokaalsed nimed.

Sisefunktsioonid

Klipp Funktsioonid

Exceli koosseisu kuulub üle 350 sisefunktsiooni. Enamasti funktsioon leiab ja tagastab ühe väärtuse. Kuid on ka funktsioone, mis täidavad operatsioone massiividega (andmekogumitega) ja mille tulemused kujutavad samuti massiive (maatriksite korrutamine, pöördmaatriksi leidmine jm).

Pöördumine funktsiooni poole esitatakse funktsiooniviida abil:

nimi ( argument1 ; argument2 ; … )

nimi - funktsiooni nimi: SIN, COS, SQRT, SUM, VLOOKUP , ...

argument1 ; argument2 ; … argumendid - määravad väärtused, mille alusel leitakse funktsiooni väärtus. Argumendid peavad olema alati sulgudes. Tühjad sulud peavad olema funktsiooni nime järel ka siis, kui argumente pole üldse. Argumentideks võivad olla erijuhul konstandid, aadressid ja/või nimed, üldjuhul aga avaldised:

SIN(1,3), TAN(A3), LN(b), PI() * SQRT(a ^ 2 + b ^ 2)

Argumentide arv, tüüp ja järjekord sõltub funktsioonist ja on kindlalt määratud. Mõnedel funktsioonidel võivad argumendid puududa, sageli on neid ainult üks.

Funktsioonide kasutamise näiteid

PI() - pi, TODAY() - jooksev kuupäev, RAND() - juhuslik arv: 0 <= arv < 1

LOG10(m), ASIN(B3/B5), SUM(E5:E25)

ROUND(tariif * tunde; 2), LEFT(eesnimi; 1)

IF(x < 0 ; 3 * SIN(x / 2) + 5 ; COS(2 * x) - 3)

SQRT(SIN(2 * x)^2 + COS(PI() * x / 4)^4)) + TAN(PI() * x / (x^2+3))

fun tark
Informatsiooni Exceli 2003 sisefunktsioonide kohta saab abiinfosüsteemist ja dialoogiboksist Insert Function, mille saab kuvada vastava tööriistariba nupuga. Funktsioonitark pakub ka abi funktsioonide sisestamiseks.
Valiku hõlbustamiseks, on funktsioonid jagatud rühmadesse: matemaatika (Math & Trig), loogika (Logical), kuupäevad ja kellaajad (Date & Time), tekst (Text), statistika (Statistical), otsimine ja viitamine (Lookup & Reference) jne.

Excel 2007-s on analoogilised võimalused realiseeritud lindi vahekaardi Formulas rühmas Function Library asuvate menüüde abil.

fun_2007