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.

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