Reikšmių paieška naudojant VLOOKUP, INDEX arba MATCH

Patarimas: Pabandykite naudoti naujas xlookup ir xmatch funkcijas, geresnes šiame straipsnyje aprašytų funkcijų versijas. Šios naujos funkcijos veikia bet kuria kryptimi ir pagal numatytąją reikšmę grąžina tikslius atitikmenis, kad būtų lengviau ir patogiau naudoti nei jų pirmtakai.

Tarkime, kad turite "Office" vietos numerių sąrašą, o jūs turite žinoti, kurie darbuotojai yra kiekviename biure. Skaičiuoklė yra didžiulė, todėl jūs manote, kad tai sudėtinga užduotis. Tai tikrai gana paprasta atlikti naudojant peržvalgos funkciją.

Funkcijos VLOOKUP ir HLOOKUP kartu su indeksu ir atitikmeniuyra kai kurios naudingiausios "Excel" funkcijos.

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

Štai pavyzdys, kaip naudoti VLOOKUP.

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

Šiame pavyzdyje B2 yra pirmasis argumentas– duomenų elementas, kurį turi veikti funkcija. VLOOKUP pirmasis argumentas yra reikšmė, kurią norite surasti. Šis argumentas gali būti langelio nuoroda arba fiksuota reikšmė, pvz., "Smith" arba "21 000". Antras argumentas yra langelių, C2-: E7 langelių, kuriuose ieškosite norimos rasti reikšmės, intervalas. Trečias argumentas yra stulpelis, esantis tame langelių, kuriame yra jūsų ieškoma reikšmė, intervalas.

Ketvirtas argumentas yra nebūtinas. Įvesk TRUE arba klaidingas. Į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, Palikus ketvirtą argumentą tuščią arba įvedant TRUE, suteikiama daugiau lankstumo.

Šiame pavyzdyje rodoma, kaip funkcija veikia. Kai įvedate reikšmę langelyje B2 (pirmasis argumentas), VLOOKUP ieško langelių diapazono C2: E7 (2 argumentas) ir grąžina artimiausio apytikslio atitikmens, esančio trečiajame diapazono stulpelyje, stulpelyje E (3 argumentas).

Įprastinis funkcijos VLOOKUP naudojimas

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

Kai esate patenkintas VLOOKUP, funkcija HLOOKUP yra vienodai paprasta naudoti. Įvedate tuos pačius argumentus, tačiau ji ieško eilutėse vietoj stulpelių.

RODYKLĖS ir atitikties naudojimas vietoj VLOOKUP

Yra tam tikrų apribojimų naudojant VLOOKUP – funkcija VLOOKUP gali ieškoti reikšmės iš kairės į dešinę. Tai reiškia, kad stulpelis, kuriame yra reikšmė, visada turėtų būti įtrauktas į kairiąją stulpelį, kuriame yra grąžinama reikšmė. Dabar, jei jūsų skaičiuoklė nebus sukurta taip, nenaudokite VLOOKUP. Naudokite funkcijų INDEX ir MATCH 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 pavyzdžių, kaip naudoti INDEX ir MATCH vietoj VLOOKUP, ieškokite straipsnyje https://www.MrExcel.com/Excel-Tips/Excel-VLOOKUP-index-Match/ by Bill Jelen, Microsoft MVP.

Išbandykite

Jei norite išbandyti peržvalgos funkcijas prieš juos išbandydami su savo duomenimis, pateikiame keletą duomenų pavyzdžių.

VLOOKUP pavyzdys darbe

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

Šis paskutinis pavyzdys naudoja funkcijas INDEX ir MATCH kartu, kad grąžintų anksčiausią sąskaitos faktūros numerį ir atitinkamą datą kiekvienam iš penkių miestų. 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

Dallas

2012 04 25

3346

Dallas

2012 04 28

3372

Dallas

2012 05 01

3414

Dallas

2012 05 01

3451

Biržai

2012 05 02

3467

Dallas

2012 05 02

3474

Dallas

2012 05 04

3490

Dallas

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

Tampa

2012 04 18

3328

Tampa

2012 04 26

3368

Tampa

2012 04 29

3420

Tampa

2012 05 01

3501

Kaunas

2012 05 06

Taip pat žr.

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

peržvalgos ir nuorodų funkcijos (nuoroda)

naudokite table_array argumentą VLOOKUP funkcijoje

Reikia daugiau pagalbos?

Tobulinkite savo „Office“ įgūdžius
Ieškoti mokymo
Pirmiausia gaukite naujų funkcijų
Prisijunkite prie „Office Insider“ dalyvių

Ar ši informacija buvo naudinga?

Dėkojame už jūsų atsiliepimus!

Dėkojame už jūsų atsiliepimą! Panašu, kad gali būti naudinga jus sujungti su vienu iš mūsų „Office“ palaikymo agentų.

×