Kokkuvõte
Selles üksikasjalikes artiklites kirjeldatakse, kuidas otsida andmeid tabelist (või lahtrivahemikust) Microsoft Exceli mitmesuguste valmisfunktsioonide abil. Sama tulemuse saamiseks saate kasutada erinevaid valemeid.
Näidistöölehe loomine
Selles artiklis kasutatakse Exceli valmisfunktsioonide illustreerimiseks näidistöölehte. Oletagem, et viitate nimele veerust A ja tagastate selle isiku vanuse veerust C. Selle töölehe loomiseks sisestage tühja Exceli töölehele järgmised andmed.Tippige väärtus, mida soovite leida lahtrisse E2. Saate tippida valemi sama töölehe mis tahes tühja lahtrisse.
|
A |
B |
C |
D |
E |
||
|
1 |
Name (Nimi) |
Osakonnas |
Vanus |
Väärtuse otsimine |
||
|
2 |
Henry |
501 |
28 |
Mary |
||
|
3 |
Stan |
201 |
19 |
|||
|
4 |
Mary |
101 |
22 |
|||
|
5 |
Larry |
301 |
29 |
Terminimääratlused
Selles artiklis kirjeldatakse Exceli valmisfunktsioonide kirjeldamiseks järgmisi termineid.
|
Mõiste |
Definitsioon |
Näide |
|
Tabelimassiiv |
Kogu otsingutabel |
A2:C5 |
|
Lookup_Value |
väärtus, mis leitakse Table_Array esimesest veerust. |
E2 |
|
Lookup_Array -või- Lookup_Vector |
Lahtrivahemik, mis sisaldab võimalikke otsinguväärtusi. |
A2:A5 |
|
Col_Index_Num |
Veerunumber Table_Array tagastatakse vastav väärtus. |
3 (Table_Array kolmas veerg) |
|
Result_Array -või- Result_Vector |
Vahemik, mis sisaldab ainult ühte rida või veergu. See peab olema sama suur kui Lookup_Array või Lookup_Vector. |
C2:C5 |
|
Range_Lookup |
Loogikaväärtus (TRUE või FALSE). Kui see argument on TRUE või puudub, tagastatakse ligikaudne vastavus. Kui see on FALSE, otsib see täpset vastet. |
FALSE |
|
Top_cell |
See on viide, millest soovite nihke aluse võtta. Top_Cell peab viitama lahtrile või külgnevate lahtrite vahemikule. Muul juhul tagastab funktsioon OFFSET #VALUE! #NUM!. |
|
|
Offset_Col |
See on veergude arv vasakule või paremale, millele tulemi ülemine vasakpoolne lahter peaks viitama. Näiteks "5" kui argument Offset_Col määrab, et viite ülemine vasakpoolne lahter on viitest viis veergu paremal. Offset_Col võib olla positiivne (st lähteviitest paremal) või negatiivne (st lähteviitest vasakul). |
Funktsioonid
LOOKUP()
Funktsioon LOOKUP leiab väärtuse ühest reast või veerust ja vastendab selle väärtusega, mis asub samas kohas teises reas või veerus.Järgmises näites on funktsiooni LOOKUP valemisüntaks:
=LOOKUP(Lookup_Value;Lookup_Vector;Result_Vector)
Järgmine valem leiab näidistöölehelt Maarja vanuse:
=LOOKUP(E2;A2:A5;C2:C5)
Valem kasutab lahtris E2 väärtust "Maarja" ja leiab otsinguvektoris (veerus A) väärtuse "Maarja". Seejärel vastab valem tulemivektori (veerg C) sama rea väärtusele. Kuna "Mary" on reas 4, tagastab funktsioon LOOKUP veeru C (22) rea 4 väärtuse.MÄRKUS: Funktsioon LOOKUP nõuab tabeli sortimist.
Funktsiooni LOOKUP kohta lisateabe saamiseks klõpsake Microsofti teabebaasis oleva artikli kuvamiseks järgmist artiklinumbrit.
VLOOKUP()
Funktsiooni VLOOKUP või Vertikaalne otsing kasutatakse siis, kui andmed on veergudes loetletud. See funktsioon otsib väärtust vasakpoolseimast veerust ja vastendab selle sama rea määratud veeru andmetega. Sortitud või sortimata tabelist andmete otsimiseks saate kasutada funktsiooni VLOOKUP. Järgmises näites kasutatakse sortimata andmetega tabelit.Järgnevalt on toodud näide funktsiooni VLOOKUP valemisüntaksi kohta.
=VLOOKUP(Lookup_Value;Table_Array;Col_Index_Num;Range_Lookup)
Järgmine valem leiab näidistöölehelt Maarja vanuse:
=VLOOKUP(E2;A2:C5;3;FALSE)
Valem kasutab lahtris E2 väärtust "Maarja" ja leiab kõige vasakpoolsemast veerust (veerust A) väärtuse "Maarja". Seejärel vastab valem Column_Index sama rea väärtusele. Selles näites kasutatakse Column_Index (veerg C) märki "3". Kuna "Maarja" on 4. reas, tagastab funktsioon VLOOKUP veeru C (22) 4. rea väärtuse.
Funktsiooni VLOOKUP kohta lisateabe saamiseks klõpsake Microsofti teabebaasis oleva artikli kuvamiseks järgmist artiklinumbrit.
INDEX() ja MATCH()
Funktsioone INDEX ja MATCH saate kasutada koos, et saada samad tulemid nagu funktsiooni LOOKUP või VLOOKUP kasutamisel.
Järgnevalt on toodud näide süntaksi kohta, mis ühendab funktsioonid INDEX ja MATCH , et anda eelmistes näidetes samad tulemid, mis funktsioonid LOOKUP ja VLOOKUP .
=INDEX(Table_Array;MATCH(Lookup_Value;Lookup_Array;0);Col_Index_Num)
Järgmine valem leiab näidistöölehelt Maarja vanuse:
=INDEX(A2:C5;MATCH(E2;A2:A5;0);3)
Valem kasutab lahtris E2 väärtust "Maarja" ja leiab veerust A väärtuse "Maarja". Seejärel vastab see veeru C samas reas olevale väärtusele. Kuna "Mary" on reas 4, tagastab valem veeru C (22) rea 4 väärtuse.
MÄRKUS. Kui ükski Lookup_Array lahtritest ei vasta Lookup_Value ("Mary"), tagastab see valem #N/A.Funktsiooni INDEX kohta lisateabe saamiseks klõpsake Microsofti teabebaasis oleva artikli kuvamiseks järgmist artiklinumbrit.
OFFSET() ja MATCH()
Saate kasutada funktsioone OFFSET ja MATCH koos, et anda eelmises näites olevate funktsioonidega samad tulemid.Järgmises näites on süntaks, mis ühendab funktsiooni OFFSET ja MATCH nii, et tulemid vastaksid funktsioonidele LOOKUP ja VLOOKUP.
=OFFSET(top_cell;MATCH(Lookup_Value;Lookup_Array;0);Offset_Col)
See valem leiab maarja vanuse näidistöölehelt:
=OFFSET(A1;MATCH(E2;A2:A5;0);2)
Valem kasutab lahtris E2 väärtust "Maarja" ja leiab veerust A väärtuse "Maarja". Seejärel vastab valem sama rea väärtusele, kuid kahe veeru paremale (veerg C). Kuna "Mary" asub veerus A, tagastab valem veeru C (22) rea 4 väärtuse.
Funktsiooni OFFSET kohta lisateabe saamiseks klõpsake Microsofti teabebaasis oleva artikli kuvamiseks järgmist artiklinumbrit.
