Applies ToMicrosoft 365 rakendus Excel Exceli veebirakendus Excel 2024 Excel 2021 Excel 2019 Excel 2016

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.

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).

Funktsiooni VLOOKUP tüüpiline kasutusviis

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.

Funktsioonide INDEX ja MATCH kasutamine väärtuse otsimiseks

Lisateavet funktsioonide VLOOKUP asemel funktsioonide INDEX ja MATCH kasutamise kohta leiate artiklist Https://www.mrexcel.com/excel-tips/excel-vlookup-index-match/ , mille on koostanud Bill Jelen, Microsoft MVP.

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.

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.

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.

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

Kiirülevaate kaart: funktsiooni VLOOKUP värskendamise

otsingu- ja viitefunktsioonid (teatmematerjalid)

Funktsioonis VLOOKUP argumendi table_array kasutamine

Kas vajate veel abi?

Kas soovite rohkem valikuvariante?

Siin saate tutvuda tellimusega kaasnevate eelistega, sirvida koolituskursusi, õppida seadet kaitsma ja teha veel palju muud.

Kogukonnad aitavad teil küsimusi esitada ja neile vastuseid saada, anda tagasisidet ja saada nõu rikkalike teadmistega asjatundjatelt.