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.
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.
Kuna otsingufunktsioonid tunduvad keskmisest raskemad, siis vaatame veel mõnda näidet. Võtame näiteks korterite nimekirja.
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!”)
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.
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.
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:
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))
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.
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.
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.
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.
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.
Juhul kui väärtused on erikohtades, siis on oluline jälgida, et andete arv oleks ikka sama.
Kõik otsingu- ja viitefunktsioonid
| Funktsioon | Kirjeldus |
| ADDRESS | Annab vastuseks tekstiviite töölehe ühele lahtrile. |
| AREAS | Annab vastuseks viites olevate alade arvu. |
| CHOOSE | Annab vastuseks ühe väärtuse väärtuste loendist. |
| COLUMN | Annab vastuseks viite veerunumbri. |
| COLUMNS | Annab vastuseks viites olevate veergude arvu. |
| HLOOKUP | Otsib väärtust massiivi esimesest reast ja annab vastuseks näidatud lahtri väärtuse. |
| HYPERLINK | Loob otsetee, mis avab võrguserveris, sisevõrgus või Internetis talletatud dokumendi. |
| INDEX | Otsib indeksi abil väärtuse viitest või massiivist. |
| INDIRECT | Annab vastuseks tekstiväärtuse poolt viidatud viite. |
| LOOKUP | Otsib väärtusi vektorist või massiivist. |
| MATCH | Otsib väärtusi viitest või massiivist. |
| OFFSET | Annab vastuseks viite kauguse antud viitest. |
| ROW | Annab vastuseks viite reanumbri. |
| ROWS | Annab vastuseks viites olevate ridade arvu. |
| RTD | Laadib reaalajas alla andmed programmist, mis toetab COM-automatiseerimist |
| TRANSPOSE | Annab vastuseks transponeeritud massiivi. |
| VLOOKUP | Otsib massiivi esimesest veerust näidatud väärtusega lahtri ja annab vastuseks lahtri väärtuse, liikudes üle rea. |