Excel 2010 – Otsingu- ja viitefunktsioonid (HARJUTUS 14)

Otsingu- ja viitefunktsioonid on mõeldud näiteks kiiresti suurtest tabelistest andmete leidmiseks ja luua omavahelisi seoseid. Kõige selle teostamiseks on mitu erinevat funktsiooni, mis põhimõtteliselt võivad teha sama asja ning erinevus seisneb teostuses.

INDEX()

INDEX() funktsioon leiab massiivist reanumbri järgi lahtri sisu. Tegemist on rea ja veeru ristumiskohaga. Võtame näiteks nimede massiivi ja palume väljastada kolmanda rea sisu.

397.jpg

Kui andmete massiiv on mitmemõõtmeline ehk on rohkem veerge, siis on võimalik ära määrata ka veerg. Palume nüüd funktsioonil väljastada massiivist kolmanda rea ja teise veeru sisu.

398.jpg

Kuna otsingufunktsioonid tunduvad keskmisest raskemad, siis vaatame veel mõnda näidet. Võtame näiteks korterite nimekirja.

399.jpg

Eesmärk: kui olen sisestanud korteri numbri, siis INDEX() funktsioon väljastab omaniku nime. Selleks sisestame järgmise valemi:

=INDEX(A2:D9;F3;2)

Jah, korteri numbri peab ise sisestama. Aga mis siis saab, kui korteri number mida andmebaasis pole? Siis peaks kasutajat veast teavitama.

=IFERROR(INDEX(A2:D9;F3;2);”Viga korteri numbris!”)

400.jpg

MATCH()

MATCH() funktsiooni abil leiame otsitava väärtuse järjekorra numbri massiivis. Lisaks massiivile ja otsitavale väärtusele tuleb määrata otsitava väärtuse täpsus:

  • 1 või puudub – otsimassiivist otsitakse väiksemat väärtust, mis ei ületaks otsiväärtust
  • 0 – etteantud ja otsitav väärtus peavad täpselt ühtima.
  • -1 – otsitakse suurimat väärtust, mis ei oleks väiksem otsiväärtuset.

Vaatame uuesti nimede tabelit – soovin teada saada mitmes nimekirjas on “kalle” ning otsin täpset vastet.

401.jpg

Juhul kui tabel ei eelda täpseid väärtusi ja antud on vahemikud,  tuleb kasutada 1 või -1 täpsust. Kui võrdluse tüübiks on 1, peavad otsimassiivi elemendid olema kasvavas, tüübi -1 puhul kahanevas järjestuses. Alljärgnevas tabelis vahemikud 0, 100, 250 ja 500. Kui otsitav on 200, siis soovin saada teist tulemust, sest see ei ületa järgmist piiri. Selleks kasutan täpsust 1.

402.jpg

INDEX() ja MATCH()

Sagedamini kasutatakse INDEX() ja MATCH() funktsiooni pesastatuna. INDEX() vastutab ikka lahtri sisu eest ja MATCH() leiab INDEX() jaoks reanumbri. Keeruline? Vaatame näidet:

403.jpg

Tegemist on pisikese sõnaraamatuga. Kui mina sisestan tõlkelahtrisse ingliskeelse sõna, siis Excel “ütleb” mulle selle eestikeelse vaste.

=INDEX(A3:A6; MATCH(D2; B3:B6; 0))

404.jpg

Sõnaraamatus kasutasime ainult rea järgi otsimist. Aga vaatame tabelit, kus võtame kasutusele nii read kui veerud. Võtame näitena kümne aasta poiste ja tüdrukute sünnid.

405.jpg

Eesmärgiks on leida Soo ja Aasta sisestamisel sündinud laste arv. Valem tundub võibolla pikk ja lohisev aga kõik on loogiline. Nimelt nii read kui veerud leiame MATCH() abil.

406.jpg

VLOOKUP() ja HLOOKUP()

VLOOKUP() ja HLOOKUP() on olemuselt sarnased otsingufunktsioonid. VLOOKUP() otsib vertikaalses tabelis ning HLOOKUP() horisontaalses tabelis.

Valime õppimiseks tabeli, kus meil on tehtud klientidele tehtud Sooduka tabel.

407.jpg

Kui klient tellib üle 500€, saab ta 2% soodukat jne. Vaikimisi kasutab VLOOKUP() ja HLOOKUP() ligikaudset vastust. Massiivina tuleb ära märgistada kõik andmed ning veeru arv, kust vastus tuleb leida.

408.jpg

TÄPSE vastuse leidmiseks tuleb lisada funktsioonile neljas argument – FLASE.

LOOKUP()

LOOKUP() on  väga sarnane eelmistele ja isegi lihtsam. Selle ainuke puudus on, et otsitav massiiv peab olema järjestatud kasvavalt!

Kõige lihtsam on määrata otsitav ja massiiv kust soovid seda leida.

409.jpg

Juhul kui väärtused on erikohtades, siis on oluline jälgida, et andete arv oleks ikka sama.

410.jpg

Kõik otsingu- ja viitefunktsioonid

FunktsioonKirjeldus
ADDRESSAnnab vastuseks tekstiviite töölehe ühele lahtrile.
AREASAnnab vastuseks viites olevate alade arvu.
CHOOSEAnnab vastuseks ühe väärtuse väärtuste loendist.
COLUMNAnnab vastuseks viite veerunumbri.
COLUMNSAnnab vastuseks viites olevate veergude arvu.
HLOOKUPOtsib väärtust massiivi esimesest reast ja annab vastuseks näidatud lahtri väärtuse.
HYPERLINKLoob otsetee, mis avab võrguserveris, sisevõrgus või Internetis talletatud dokumendi.
INDEXOtsib indeksi abil väärtuse viitest või massiivist.
INDIRECTAnnab vastuseks tekstiväärtuse poolt viidatud viite.
LOOKUPOtsib väärtusi vektorist või massiivist.
MATCHOtsib väärtusi viitest või massiivist.
OFFSETAnnab vastuseks viite kauguse antud viitest.
ROWAnnab vastuseks viite reanumbri.
ROWSAnnab vastuseks viites olevate ridade arvu.
RTDLaadib reaalajas alla andmed programmist, mis toetab COM-automatiseerimist
TRANSPOSEAnnab vastuseks transponeeritud massiivi.
VLOOKUPOtsib massiivi esimesest veerust näidatud väärtusega lahtri ja annab vastuseks lahtri väärtuse, liikudes üle rea.

Harjutus 14

Lae alla fail harjutus14.xlsx

432.jpg

 

SEE ALLAdd a note
YOU
Add your Comment

Viimased postitused

  • iot

    Asjade Internet

    Mis on Asjade Internet Asjade Internet (Internet of Things, IoT) on interneti...

  • Microsoft Teams

    Mis on Microsoft Teams? Microsoft Teams on vestluspõhine tööruum, kus saab...

  • Microsoft Forms

    Microsoft Forms on Office 365 paketiga kaasa tulev rakendus, mille abil saad...

Meist

metshein.com on pakkunud juba üle kümne aasta tasuta eestikeelseid infotehnoloogiaga seotud kursusi. Sama kurssi püütakse järgida ka edaspidi. Eesmärk muuta arvutiõpe võimalikult lihtsaks!

metshein.com: parim eestikeelne koolitusportaal

Kontakt: kliki siia

Hetkel õpivad:

There are no users currently online

Kontrolli tunnistust

Tööribale