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