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