Näpunäide.
Proovige kasutada uusi funktsioone XLOOKUP ja XMATCH , selles artiklis kirjeldatud funktsioonide täiustatud versioone. Need uued funktsioonid töötavad mis tahes suunas ja tagastavad vaikimisi täpsed vasted, mis muudavad nende kasutamise lihtsamaks ja mugavamaks kui nende eelkäijad.
Oletagem, et teil on loend kontori asukohanumbritest ja teil on vaja teada, millised töötajad igas kontoris asuvad. Arvutustabel on väga suur, nii et võib-olla arvate, et see on keeruline ülesanne. Tegelikult on seda otsingufunktsiooniga üsna lihtne teha.
Funktsioonid VLOOKUP ja HLOOKUP koos funktsioonidega INDEX ja MATCH on Excelis mõned kõige kasulikumad funktsioonid.
Märkus.
Otsinguviisardi funktsioon pole enam Excelis saadaval.
Siin on näide funktsiooni VLOOKUP kasutamise kohta.
=VLOOKUP(B2;C2:E7;3;TRUE)
Selles näites on B2 esimene argument – andmeelement, mida funktsioon vajab töötamiseks. Funktsiooni VLOOKUP puhul on see esimene argument väärtus, mida soovite leida. See argument võib olla lahtriviide või fikseeritud väärtus (nt "soo" või 21 000). Teine argument on lahtrivahemik C2–:E7, kust otsida soovitud väärtust. Kolmas argument on selle lahtrivahemiku veerg, mis sisaldab otsitavat väärtust.
Neljas argument pole kohustuslik. Sisestage kas TRUE või FALSE. Kui sisestate väärtuse TRUE või jätate selle argumendi ära, tagastab funktsioon esimeses argumendis määratud väärtuse ligikaudse vaste. Kui sisestate väärtuse FALSE, vastab funktsioon esimese argumendi esitatud väärtusele. Teisisõnu annab neljanda argumendi tühjaks jätmine või väärtuse TRUE sisestamine teile rohkem paindlikkust.
See näide illustreerib funktsiooni tööd. Kui sisestate väärtuse lahtrisse B2 (esimene argument), otsib funktsioon VLOOKUP vahemikus C2:E7 (2. argument) olevatest lahtritest ja tagastab lähima ligikaudse vaste vahemiku kolmandast veerust ehk veerust E (kolmas argument).
Neljas argument on tühi, seega tagastab funktsioon ligikaudse vaste. Vastasel juhul peaksite sisestama ühe väärtustest veerus C või D, et üldse tulemust saada.
Kui olete funktsiooni VLOOKUP kasutamisega tuttav, on funktsiooni HLOOKUP sama lihtne kasutada. Sisestage samad argumendid, kuid see otsib veergude asemel ridadest.
Funktsioonide INDEX ja MATCH kasutamine funktsiooni VLOOKUP asemel
Funktsiooni VLOOKUP kasutamisel on teatud piirangud – funktsioon VLOOKUP saab otsida ainult vasakult paremale jääva väärtuse. See tähendab, et otsitavat väärtust sisaldav veerg peab olema alati tagastusväärtust sisaldavast veerust vasakul. Kui teie arvutustabel pole nii loodud, ärge kasutage funktsiooni VLOOKUP. Kasutage selle asemel funktsioonide INDEX ja MATCH kombinatsiooni.
Selles näites on väike loend, kus väärtus, mille järgi me otsime (Chicago), pole vasakpoolseimas veerus. Seega ei saa funktsiooni VLOOKUP kasutada. Selle asemel kasutame funktsiooni MATCH, et leida Vahemikust B1:B11 Chicago. See asub 4. reas. Seejärel kasutab funktsioon INDEX seda väärtust otsinguargumendina ja leiab 4. veerus (veerus D) Chicago populatsiooni. Kasutatav valem kuvatakse lahtris A14.
Proovige järele!
Kui soovite katsetada otsingufunktsioone enne, kui proovite neid oma andmetega, leiate siit mõned näidisandmed.
Funktsiooni VLOOKUP näide tööl
Kopeerige järgmised andmed tühja arvutustabelisse.
Näpunäide.
Enne andmete Excelisse kleepimist määrake veergude A–C laiuseks 250 pikslit ja klõpsake käsku Murra teksti ridu (menüü Avaleht jaotis Joondus ).
| Tihedus | Viskoossus | Temperatuur |
|---|---|---|
| 0,457 | 3,55 | 500 |
| 0,525 | 3,25 | 400 |
| 0,606 | 2,93 | 300 |
| 0,675 | 2,75 | 250 |
| 0,746 | 2,57 | 200 |
| 0,835 | 2,38 | 150 |
| 0,946 | 2,17 | 100 |
| 1,09 | 1,95 | 50 |
| 1,29 | 1,71 | 0 |
| Valem | Kirjeldus | Tulem |
| =VLOOKUP(1;A2:C10;2) | Otsib veerust A ligikaudset vastet väärtusele 1, leiab veerust A suurima väärtuse, mis on väiksem või võrdne väärtusega 1 (see on 0,946), ja tagastab väärtuse sama rea veerust B. | 2,17 |
| =VLOOKUP(1;A2:C10;3;TRUE) | Otsib veerust A ligikaudset vastet väärtusele 1, leiab veerust A suurima väärtuse, mis on väiksem või võrdne väärtusega 1 (see on 0,946), ja tagastab väärtuse sama rea veerust C. | 100 |
| =VLOOKUP(0;7;A2:C10;3;FALSE) | Otsib veerust A täpset vastet väärtusele 0,7. Kuna veerus A täpset vastet ei leidu, tagastatakse veaväärtus. | #N/A |
| =VLOOKUP(0;1;A2:C10;2;TRUE) | Otsib veerust A ligikaudset vastet väärtusele 0,1. Kuna 0,1 on väiksem kui veeru A kõige väiksem väärtus, tagastatakse veaväärtus. | #N/A |
| =VLOOKUP(2;A2:C10;2;TRUE) | Otsib veerust A ligikaudset vastet väärtusele 2, leiab veerust A suurima väärtuse, mis on väiksem või võrdne väärtusega 2 (see on 1,29), ja tagastab väärtuse sama rea veerust B. | 1,71 |
HLOOKUP Example
Kopeerige selle tabeli kõik lahtrid ja kleepige need tühja Exceli töövihiku lahtrisse A1.
Näpunäide.
Enne andmete Excelisse kleepimist määrake veergude A–C laiuseks 250 pikslit ja klõpsake käsku Murra teksti ridu (menüü Avaleht jaotis Joondus ).
| Teljed | Kuullaagrid | Poldid |
|---|---|---|
| 4 | 4 | 9 |
| 5 | 7 | 10 |
| 6 | 8 | 11 |
| Valem | Kirjeldus | Tulem |
| =HLOOKUP("Teljed"; A1:C4; 2; FALSE) | Otsib 1. reast väärtust "Teljed" ja tagastab väärtuse sama veeru (veeru A) 2. reast. | 4 |
| =HLOOKUP("Kuullaagrid"; A1:C4; 3; FALSE) | Otsib 1. reast väärtust "Kuullaagrid" ja tagastab väärtuse sama veeru (veeru B) 3. reast. | 7 |
| =HLOOKUP("P"; A1:C4; 3; TRUE) | Otsib 1. reast väärtust B ja tagastab väärtuse sama veeru 3. reast. Kuna "B" täpset vastet ei leita, kasutatakse esimese rea suurimat väärtust, mis on väiksem kui "B": "Teljed", veerus A. | 5 |
| =HLOOKUP("Poldid"; A1:C4; 4) | Otsib 1. reast väärtust "Poldid" ja tagastab väärtuse sama veeru (veeru C) 4. reast. | 11 |
| =HLOOKUP(3;{1\2\3;"a"\"b"\"c";"d"\"e"\"f"};2;TRUE) | Otsib kolmerealisest massiivikonstandist arvu 3 ja tagastab väärtuse sama (antud juhul kolmanda) veeru 2. reast. Massiivikonstandis on kolm väärtuste rida, iga rida on eraldatud semikooloniga (;). Kuna "c" asub reas 2 ja samas veerus kui 3, tagastatakse "c". | c |
FUNKTSIOONIDE INDEX ja MATCH näited
Selles viimases näites kasutatakse funktsioone INDEX ja MATCH koos, et tagastada varaseim arvenumber ja sellele vastav kuupäev iga viie linna kohta. Kuna kuupäev tagastatakse arvuna, kasutame selle kuupäevana vormindamiseks funktsiooni TEXT. Funktsioon INDEX kasutab ühe argumendina funktsiooni MATCH tulemit. Funktsioonide INDEX ja MATCH kombinatsiooni kasutatakse igas valemis kaks korda – esiteks arvenumbri tagastamiseks ja teiseks kuupäeva tagastamiseks.
Kopeerige selle tabeli kõik lahtrid ja kleepige need tühja Exceli töövihiku lahtrisse A1.
Näpunäide.
Enne andmete Excelisse kleepimist määrake veergude A–D laiuseks 250 pikslit ja klõpsake nuppu Murra teksti ridu (menüü Avaleht jaotis Joondus ).
| Arve | Linn | Arve kuupäev | Linna varaseim arve ja selle kuupäev |
|---|---|---|---|
| 3115 | Tartu | 07.04.12 | ="Tartu = "&INDEX($A$2:$C$33;MATCH("Tartu";$B$2:$B$33;0);1)& "; Arve kuupäev: " & TEXT(INDEX($A$2:$C$33;MATCH("Tartu";$B$2:$B$33;0);3);"dd.mm.yyyy") |
| 3137 | Tartu | 09.04.12 | ="Tallinn = "&INDEX($A$2:$C$33;MATCH("Tallinn";$B$2:$B$33;0);1)& "; Arve kuupäev: " & TEXT(INDEX($A$2:$C$33;MATCH("Tallinn";$B$2:$B$33;0);3);"dd.mm.yyyy") |
| 3154 | Tartu | 11.04.12 | ="Pärnu = "&INDEX($A$2:$C$33;MATCH("Pärnu";$B$2:$B$33;0);1)& "; Arve kuupäev: " & TEXT(INDEX($A$2:$C$33;MATCH("Pärnu";$B$2:$B$33;0);3);"dd.mm.yyyy") |
| 3191 | Tartu | 21.04.12 | ="Valga = "&INDEX($A$2:$C$33;MATCH("Valga";$B$2:$B$33;0);1)& "; Arve kuupäev: " & TEXT(INDEX($A$2:$C$33;MATCH("Valga";$B$2:$B$33;0);3);"dd.mm.yyyy") |
| 3293 | Tartu | 25.04.12 | ="Haapsalu = "&INDEX($A$2:$C$33;MATCH("Haapsalu";$B$2:$B$33;0);1)& "; Arve kuupäev: " & TEXT(INDEX($A$2:$C$33;MATCH("Haapsalu";$B$2:$B$33;0);3);"dd.mm.yyyy") |
| 3331 | Tartu | 27.04.12 | |
| 3350 | Tartu | 28.04.12 | |
| 3390 | Tartu | 01.05.12 | |
| 3441 | Tartu | 02.05.12 | |
| 3517 | Tartu | 08.05.12 | |
| 3124 | Tallinn | 09.04.12 | |
| 3155 | Tallinn | 11.04.12 | |
| 3177 | Tallinn | 19.04.12 | |
| 3357 | Tallinn | 28.04.12 | |
| 3492 | Tallinn | 06.05.12 | |
| 3316 | Pärnu | 25.04.12 | |
| 3346 | Pärnu | 28.04.12 | |
| 3372 | Pärnu | 01.05.12 | |
| 3414 | Pärnu | 01.05.12 | |
| 3451 | Pärnu | 02.05.12 | |
| 3467 | Pärnu | 02.05.12 | |
| 3474 | Pärnu | 04.05.12 | |
| 3490 | Pärnu | 05.05.12 | |
| 3503 | Pärnu | 08.05.12 | |
| 3151 | Valga | 09.04.12 | |
| 3438 | Valga | 02.05.12 | |
| 3471 | Valga | 04.05.12 | |
| 3160 | Haapsalu | 18.04.12 | |
| 3328 | Haapsalu | 26.04.12 | |
| 3368 | Haapsalu | 29.04.12 | |
| 3420 | Haapsalu | 01.05.12 | |
| 3501 | Haapsalu | 06.05.12 |
Lisateave
Kiirülevaate kaart: funktsiooni VLOOKUP näpunäited
Otsingu- ja viitamisfunktsioonid (teatmematerjalid)