Patarimas
Pabandykite naudoti naujas XLOOKUP ir XMATCH funkcijas – patobulintas šiame straipsnyje aprašytas funkcijų versijas. Šios naujosios funkcijos veikia bet kuria kryptimi ir pateikia tikslius atitikmenis pagal numatytuosius parametrus, todėl jas lengviau ir patogiau naudoti nei ankstesnes.
Tarkime, kad turite statistinius biuro vietų numerius ir reikia sužinoti, kokie darbuotojai dirba skirtinguose kabinetuose. Skaičiuoklė milžiniška, todėl galite pagalvoti, kad tai sudėtinga užduotis. Iš tikrųjų tai gana lengva padaryti naudojant paieškos funkciją.
Funkcijos VLOOKUP ir HLOOKUP kartu su INDEX ir MATCH yra vienos naudingiausių "Excel" funkcijų.
Pastaba
Peržvalgos vedlio funkcijos programoje "Excel" nebėra.
Štai pavyzdys, kaip naudoti VLOOKUP.
=VLOOKUP(B2,C2:E7,3,TRUE)
Šiame pavyzdyje B2 yra pirmasis argumentas – duomenų elementas, kuris funkcijai turi veikti. Naudojant VLOOKUP, šis pirmas argumentas yra reikšmė, kurią norite rasti. Šis argumentas gali būti langelio nuoroda arba fiksuota reikšmė, pvz., "petraitis" arba 21 000. Antrasis argumentas yra langelių diapazonas C2-:E7, kuriame ieškoma reikšmės, kurią norite rasti. Trečiasis argumentas yra to diapazono stulpelis, kuriame yra jūsų ieškoma reikšmė.
Ketvirtas argumentas yra nebūtinas. Įveskite TRUE arba FALSE. Į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 sakant, ketvirtojo argumento neįrašius arba įvedus TRUE, paieška bus lankstesnė.
Šiame pavyzdyje rodoma, kaip funkcija veikia. Įvedus reikšmę į langelį B2 (pirmąjį argumentą), funkcija VLOOKUP atlieka paiešką langeliuose diapazone C2:E7 (2-as argumentas) ir pateikia apytikrius atitikmenis iš trečiojo stulpelio E (3-ias argumentas).
Ketvirtasis argumentas neįrašytas, tad funkcija pateikia apytikrius atitikmenis. Jei tai nevyktų, kad gautumėte rezultatus, turėtumėte įvesti bent vieną reikšmę iš stulpelių C arba D.
Kai įprasite naudotis funkcija VLOOKUP, taip pat paprasta naudotis ir funkcija HLOOKUP. Joje įvedami tie patys argumentai, bet ieškoma eilutėse, o ne stulpeliuose.
INDEX ir MATCH naudojimas vietoj VLOOKUP
Yra tam tikrų VLOOKUP naudojimo apribojimų – funkcija VLOOKUP gali peržiūrėti reikšmę tik iš kairės į dešinę. Tai reiškia, kad stulpelis, kuriame yra jūsų peržvalgos reikšmė, visada turi būti stulpelio, kuriame yra grąžinama reikšmė, kairėje pusėje. Jei jūsų 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.
Išbandykite
Jeigu norite išbandyti ieškos funkcijas prieš išbandydami jas su savo duomenimis, čia pateikiami duomenų pavyzdžiai.
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šymas | 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šymas | 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 siekiant grąžinti kiekvieno iš penkių miestų naujausios sąskaitos faktūros numerį ir atitinkamą datą. 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 |
Taip pat žr.
Sparčiųjų nuorodų kortelė: VLOOKUP atnaujinimo priemonė
Peržvalgos ir nuorodų funkcijos (rodyklė)