Applies To„Excel“, skirta „Microsoft 365“ Internetinė „Excel“ „Excel 2024“ Excel 2021 Excel 2019 Excel 2016

Patarimas: Pabandykite naudoti naujas XLOOKUP ir XMATCH funkcijas, patobulintas šiame straipsnyje aprašytų funkcijų versijas. Šios naujos funkcijos veikia bet kuria kryptimi ir grąžina tikslius atitikmenis pagal numatytuosius nustatymus, todėl jas lengviau ir patogiau naudoti nei jų pirmtakai.

Tarkime, kad turite biuro vietų numerių sąrašą ir turite žinoti, kurie darbuotojai dirba kiekviename biure. Skaičiuoklė yra didžiulė, todėl galite manyti, kad užduotis yra sudėtinga. Iš tikrųjų tai gana lengva daryti su peržvalgos funkcija.

Funkcijos VLOOKUP ir HLOOKUP kartu su INDEX ir MATCH yra vienos naudingiausių "Excel" funkcijų.

Pastaba: Peržvalgos vediklio funkcija programoje "Excel" nebepasiekiama.

Štai pavyzdys, kaip naudoti VLOOKUP.

=VLOOKUP(B2,C2:E7,3,TRUE)

Šiame pavyzdyje B2 yra pirmasis argumentas – duomenų elementas, kurį funkcija turi veikti. VLOOKUP atveju šis pirmasis argumentas yra reikšmė, kurią norite rasti. Šis argumentas gali būti langelio nuoroda arba fiksuota reikšmė, pvz., "smith" arba 21 000. Antrasis argumentas yra langelių diapazonas C2-:E7, kuriame reikia ieškoti reikšmės, kurią norite rasti. Trečiasis argumentas yra to langelių diapazono stulpelis, kuriame yra ieškoma reikšmė.

Ketvirtas argumentas yra nebūtinas. Įveskite TRUE (teisinga) arba FALSE (klaidinga). Įvedus TRUE arba argumento neįrašius, funkcija nurodys į pirmajame argumente nurodytą reikšmę panašius rezultatus. Įvedus FALSE, funkcija ieškos pirmojo argumento reikšmės atitikmens. Kitaip tariant, ketvirtojo argumento neįrašę arba įvedę TRUE galėsite lanksčiau.

Šiame pavyzdyje rodoma, kaip funkcija veikia. Įvedus reikšmę į langelį B2 (pirmąjį argumentą), funkcija VLOOKUP ieško langelių diapazone C2:E7 (2-asis argumentas) ir pateikia artimiausią apytikslį atitikmenį iš trečiojo stulpelio diapazone E (3-ias argumentas).

Įprastinis funkcijos VLOOKUP naudojimas

Ketvirtasis argumentas tuščias, todėl funkcija pateikia apytikslį atitikmenį. Jei tai nevyktų, kad gautumėte rezultatus, turėtumėte įvesti bent vieną reikšmę iš stulpelių C arba D.

Kai įamžinsite VLOOKUP, funkciją HLOOKUP bus taip pat paprasta naudoti. Jūs įvedate tuos pačius argumentus, bet ieško eilutėse, o ne stulpeliuose.

INDEX ir MATCH naudojimas vietoj VLOOKUP

VLOOKUP naudojimas turi tam tikrų apribojimų– funkcija VLOOKUP gali ieškoti reikšmės tik iš kairės į dešinę. Tai reiškia, kad stulpelis, kuriame yra ieškoma reikšmė, visada turi būti į kairę nuo stulpelio, kuriame yra grąžinama reikšmė. Dabar, jei skaičiuoklė sukurta ne tokiu būdu, nenaudokite VLOOKUP. Vietoj to naudokite INDEX ir MATCH funkcijų derinį.

Šiame pavyzdyje pateiktas nedidelis sąrašas, kur norima ieškoti reikšmė, Čikaga, nėra kairiajame stulpelyje. Taigi, negalima naudoti VLOOKUP. Vietoj to, naudosime funkciją MATCH norėdami reikšmę Čikaga rasti diapazone B1:B11. Radome 4 eilutėje. Tada INDEX šią reikšmę naudoja kaip peržvalgos argumentą ir randa Čikagos populiaciją 4 stulpelyje (stulpelyje D). Naudojama formulė rodoma langelyje A14.

INDEX ir MATCH naudojimas ieškant reikšmės

Daugiau index ir MATCH naudojimo pavyzdžių, o ne VLOOKUP, ieškokite straipsnyje https://www.mrexcel.com/excel-tips/excel-vlookup-index-match/ Bill Jelen, Microsoft MVP.

Išbandykite

Jei norite išbandyti peržvalgos funkcijas prieš bandydami jas naudoti su savo duomenimis, pateikiame duomenų pavyzdžius.

VLOOKUP Example at work

Nukopijuokite šiuos duomenis į tuščią skaičiuoklę.

Patarimas: Prieš įklijuodami duomenis programoje „Excel“, nustatykite stulpelių pločius nuo stulpelio A iki stulpelio C, kad jie būtų 250 pikselių ir spustelėkite Kelti teksto eilutę (skirtukas Pagrindinis, grupė Lygiuotė).

Tankis

Klampumas

Temperatūra

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

Formulė

Aprašas

Rezultatas

=VLOOKUP(1,A2:C10,2)

Naudojant apytikslį atitikimą A stulpelyje ieškoma reikšmės 1, stulpelyje A randa didžiausią reikšmę, kuri yra mažesnė arba lygi vienetui, t. y. reikšmę 0,946, po to reikšmę iš stulpelio B grąžina į tą pačią eilutę.

2,17

=VLOOKUP(1,A2:C10,3,TRUE)

Naudojant apytikslį atitikimą A stulpelyje ieškoma reikšmės 1, randama stulpelyje A didžiausia mažesnė arba lygi vienetui reikšmė, t. y. reikšmę 0,946, po to iš stulpelio C reikšmė grąžinama į tą pačią eilutę.

100

=VLOOKUP(0.7,A2:C10,3,FALSE)

Naudojant apytikslį atitikimą A stulpelyje ieškoma reikšmės 0,7. Dėl to, kad A stulpelyje nėra tikslaus atitikimo, rodoma klaida.

#N/A

=VLOOKUP(0.1,A2:C10,2,TRUE)

Naudojant apytikslį atitikimą A stulpelyje ieškoma reikšmės 0,1. Dėl to, kad 0.1 yra mažiau nei mažiausia reikšmė A stulpelyje, rodoma klaida.

#N/A

=VLOOKUP(2,A2:C10,2,TRUE)

Naudojant apytikslį atitikimą A stulpelyje ieškoma reikšmės 2, stulpelyje A randama didžiausia reikšmė, kuri yra mažesnė už 2 arba lygi reikšmei 2, t. y. reikšmę 1,29, po to stulpelyje B esanti reikšmė grąžinama į tą pačią eilutę.

1,71

HLOOKUP pavyzdys

Nukopijuokite visus šios lentelės langelius ir įklijuokite juos į programos „Excel“ tuščios darbaknygės A1 langelį.

Patarimas: Prieš įklijuodami duomenis programoje „Excel“, nustatykite stulpelių pločius nuo stulpelio A iki stulpelio C, kad jie būtų 250 pikselių ir spustelėkite Kelti teksto eilutę (skirtukas Pagrindinis, grupė Lygiuotė).

Ašys

Guoliai

Varžtai

4

4

9

5

7

10

6

8

11

Formulė

Aprašas

Rezultatas

=HLOOKUP("Ašys", A1:C4, 2, TRUE)

Ieško „Ašys" eilutėje 1 ir grąžiną reikšmę iš to paties stulpelio (stulpelio A) eilutės 2.

4

=HLOOKUP("Guoliai", A1:C4, 3, FALSE)

Ieško „Guoliai" eilutėje 1 ir grąžiną reikšmę iš to paties stulpelio (stulpelio B) eilutės 3.

7

=HLOOKUP("G", A1:C4, 3, TRUE)

Ieško „G" eilutėje 1 ir grąžiną reikšmę iš to paties stulpelio eilutės 3. Kadangi tikslaus „B" atitikmens nerandama, rodoma didžiausia vertė iš eilutės 1 mažesnė už „B": „Ašys" iš stulpelio A.

5

=HLOOKUP("Varžtai", A1:C4, 4)

Ieško „Varžtai" eilutėje 1 ir grąžiną reikšmę iš to paties stulpelio (stulpelio C) eilutės 4.

11

=HLOOKUP(3, {1,2,3;"a","b","c";"d","e","f"}, 2, TRUE)

Trijų eilučių diapazono konstantoje ieško skaičiaus 3 ir grąžina reikšmę iš to paties stulpelio (šiuo atveju trečiojo) eilutės 2. Diapazono konstantoje yra trys eilutės reikšmių, kiekviena atskirta kabliataškiu (;). Kadangi „c" randama to paties stulpelio 3 eilutėje 2, „c" yra grąžinama.

c

INDEX ir MATCH pavyzdžiai

Paskutiniame pavyzdyje kartu naudojamos INDEX ir MATCH funkcijos, kad būtų pateiktas kiekvieno iš penkių miestų anksčiausios sąskaitos faktūros numeris ir atitinkama data. Kadangi data yra grąžinama kaip skaičius, naudojame TEXT funkciją, kad suformatuotume skaičių kaip datą. INDEX funkcija iš tikrųjų naudoja MATCH funkcijos rezultatą kaip argumentą. INDEX ir MATCH funkcijų kombinacija yra naudojama du kartus kiekvienoje formulėje – pirmiausia norint grąžinti sąskaitos faktūros numerį, o tada norint grąžinti datą.

Nukopijuokite visus šios lentelės langelius ir įklijuokite juos į programos „Excel“ tuščios darbaknygės A1 langelį.

Patarimas: Prieš įklijuodami duomenis programoje „Excel“, nustatykite stulpelių pločius nuo stulpelio A iki stulpelio D, kad jie būtų 250 pikselių ir spustelėkite Kelti teksto eilutę (skirtukas Pagrindinis, grupė Lygiuotė).

Sąskaita faktūra

Miestas

Sąskaitos faktūros data

Naujausia sąskaita faktūra pagal miestą su data

3115

Utena

2012 04 07

="Utena = "&INDEX($A$2:$C$33,MATCH("Utena",$B$2:$B$33,0),1)& ", Sąskaitos faktūros data: " & TEXT(INDEX($A$2:$C$33,MATCH("Utena",$B$2:$B$33,0),3),"m/d/yy")

3137

Utena

2012 04 09

="Alytus = "&INDEX($A$2:$C$33,MATCH("Alytus",$B$2:$B$33,0),1)& ", Sąskaitos faktūros data: " & TEXT(INDEX($A$2:$C$33,MATCH("Alytus",$B$2:$B$33,0),3),"m/d/yy")

3154

Utena

2012 04 11

="Biržai = "&INDEX($A$2:$C$33,MATCH("Biržai",$B$2:$B$33,0),1)& ", Sąskaitos faktūros data: " & TEXT(INDEX($A$2:$C$33,MATCH("Biržai",$B$2:$B$33,0),3),"m/d/yy")

3191

Utena

2012 04 21

="Naujoji Akmenė = "&INDEX($A$2:$C$33,MATCH("Naujoji Akmenė",$B$2:$B$33,0),1)& ", Sąskaitos faktūros data: " & TEXT(INDEX($A$2:$C$33,MATCH("Naujoji Akmenė",$B$2:$B$33,0),3),"m/d/yy")

3293

Utena

2012 04 25

="Kaunas = "&INDEX($A$2:$C$33,MATCH("Kaunas",$B$2:$B$33,0),1)& ", Sąskaitos faktūros data: " & TEXT(INDEX($A$2:$C$33,MATCH("Kaunas",$B$2:$B$33,0),3),"m/d/yy")

3331

Utena

2012 04 27

3350

Utena

2012 04 28

3390

Utena

2012 05 01

3441

Utena

2012 05 02

3517

Utena

2012 05 08

3124

Alytus

2012 04 09

3155

Alytus

2012 04 11

3177

Alytus

2012 04 19

3357

Alytus

2012 04 28

3492

Alytus

2012 05 06

3316

Biržai

2012 04 25

3346

Biržai

2012 04 28

3372

Biržai

2012 05 01

3414

Biržai

2012 05 01

3451

Biržai

2012 05 02

3467

Biržai

2012 05 02

3474

Biržai

2012 05 04

3490

Biržai

2012 05 05

3503

Biržai

2012 05 08

3151

Naujoji Akmenė

2012 04 09

3438

Naujoji Akmenė

2012 05 02

3471

Naujoji Akmenė

2012 05 04

3160

Kaunas

2012 04 18

3328

Kaunas

2012 04 26

3368

Kaunas

2012 04 29

3420

Kaunas

2012 05 01

3501

Kaunas

2012 05 06

Sparčiųjų nuorodų kortelė: VLOOKUP atnaujinimo priemonės

peržvalgos ir nuorodų funkcijos (nuoroda)

Funkcijos VLOOKUP argumento table_array naudojimas

Reikia daugiau pagalbos?

Norite daugiau parinkčių?

Sužinokite apie prenumeratos pranašumus, peržiūrėkite mokymo kursus, sužinokite, kaip apsaugoti savo įrenginį ir kt.

Bendruomenės padeda užduoti klausimus ir į juos atsakyti, pateikti atsiliepimų ir išgirsti iš ekspertų, turinčių daug žinių.