VLOOKUP funtzioa Excel-en - Hasiberrientzako gida: sintaxia eta adibideak

Gaur Excel-en ezaugarririk erabilgarrienetako bat deskribatzen duten artikulu sorta bat hasten ari gara VPR (BILAKETA). Funtzio hau, aldi berean, konplexuenetakoa eta gutxien ulertzen denetakoa da.

Tutorial honetan VPR Saiatuko naiz oinarriak ahalik eta errazen azaltzen, esperientziarik gabeko erabiltzaileentzat ikaskuntza prozesua ahalik eta argiena izan dadin. Horrez gain, Excel formulekin hainbat adibide aztertuko ditugu, funtzioaren erabilera kasurik ohikoenak erakutsiko dituztenak VPR.

  • Deskribapen orokorra eta sintaxia
  • VLOOKUP Adibideak
    • Nola erabili VLOOKUP beste Excel orri batean bilatzeko
    • Bilatu beste lan-koaderno batean VLOOKUP-ekin
    • Nola erabili izendun barruti edo taula bat VLOOKUP-ekin formuletan
    • Komodinak erabiltzea VLOOKUP formuletan
  • Bat-etortze zehatza edo gutxi gorabeherakoa VLOOKUP funtzioan
  • VLOOKUP Excel-en - hau gogoratu behar duzu!

VLOOKUP funtzioa Excel-en - deskribapen orokorra eta sintaxia

Beraz, zer da VPR? Beno, lehenik eta behin, Excel funtzio bat da. Zer egiten du? Zehazten duzun balioa bilatzen du eta dagokion balioa itzultzen du beste zutabetik. Teknikoki hitz eginez, VPR Emandako barrutiaren lehen zutabeko balioa bilatzen du eta errenkada bereko beste zutabe bateko emaitza itzultzen du.

Aplikazio ohikoenean, funtzioa VPR datu-basean identifikatzaile esklusibo jakin bat bilatzen du eta horri lotutako informazio batzuk ateratzen ditu datu-basetik.

Funtzioaren izenaren lehen letra VPR (BILAKETA) esan nahi du Вbertikala (Vbertikala). Horren bidez bereiz dezakezu VPR ra GPR (HLOOKUP), barruti baten goiko errenkadan balio bat bilatzen duena - Гhorizontala (Hhorizontalean).

Funtzio VPR eskuragarri Excel 2013, Excel 2010, Excel 2007, Excel 2003, Excel XP eta Excel 2000.

VLOOKUP funtzioaren sintaxia

Funtzio VPR (VLOOKUP) sintaxi hau du:

VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])

ВПР(искомое_значение;таблица;номер_столбца;[интервальный_просмотр])

Ikusten duzun bezala, funtzio bat VPR Microsoft Excel-en 4 aukera (edo argumentu) ditu. Lehenengo hirurak derrigorrezkoak dira, azkenak aukerakoak.

  • bilatu_balioa (lookup_value) - Bilatu beharreko balioa. Hau balio bat (zenbakia, data, testua) edo gelaxka-erreferentzia bat (bilaketa-balioa duena) edo Excel-eko beste funtzio batek itzultzen duen balio bat izan daiteke. Adibidez, formula honek balioa bilatuko du 40:

    =VLOOKUP(40,A2:B15,2)

    =ВПР(40;A2:B15;2)

Bilaketa-balioa bilatzen ari den barrutiaren lehen zutabeko balio txikiena baino txikiagoa bada, funtzioa VPR errore baten berri emango du #AT (#N/A).

  • table_array (taula) - bi datu-zutabe edo gehiago. Gogoratu, funtzioa VPR argumentuan emandako barrutiaren lehen zutabean balioa bilatzen du beti table_array (mahai). Ikus daitekeen barrutiak hainbat datu izan ditzake, hala nola testua, datak, zenbakiak, boolearrak. Funtzioak maiuskulak eta minuskulak bereizten ditu, hau da, letra larriak eta minuskulak berdintzat hartzen dira. Beraz, gure formulak balioa bilatuko du 40 tik zeluletan A2 to A15, A argumentuan emandako A2:B15 barrutiaren lehen zutabea delako table_array (taula):

    =VLOOKUP(40,A2:B15,2)

    =ВПР(40;A2:B15;2)

  • col_index_num (zutabe_zenbakia) aurkitutako errenkadan balioa itzuliko den emandako barrutian dagoen zutabearen zenbakia da. Emandako barrutiaren ezkerreko zutabea da 1, bigarren zutabea da 2, hirugarren zutabea da 3 eta abar. Orain formula osoa irakur dezakezu:

    =VLOOKUP(40,A2:B15,2)

    =ВПР(40;A2:B15;2)

    Balioaren bila formula 40 barrutian A2: A15 eta B zutabetik dagokion balioa itzultzen du (B A2:B15 barrutiko bigarren zutabea delako).

Argudioaren balioa bada col_index_num (zutabe_zenbakia) baino txikiagoa 1ondoren VPR errore baten berri emango du #VALUE! (#BALIOA!). Eta barrutiko zutabe kopurua baino gehiago bada table_array (taula), funtzioak errore bat itzuliko du #REF! (#LINK!).

  • barruti_bilaketa (range_lookup) - zer bilatu behar den zehazten du:
    • parekatze zehatza, argumentuak berdina izan behar du FALSE (GEZURRA);
    • gutxi gorabeherako partida, argumentua berdin EGIA KODEA (EGIA) edo batere zehaztu gabe.

    Parametro hau hautazkoa da, baina oso garrantzitsua. Geroago tutorial honetan VPR Bat-etortze zehatzak eta gutxi gorabeherakoak aurkitzeko formulak nola idatzi azaltzen duten adibide batzuk erakutsiko dizkizut.

VLOOKUP Adibideak

Funtzioa espero dut VPR apur bat argitu zaitez. Orain ikus ditzagun erabilera kasu batzuk VPR datu errealak dituzten formuletan.

Nola erabili VLOOKUP beste Excel orri batean bilatzeko

Praktikan, funtzio bat duten formulak VPR oso gutxitan erabiltzen dira lan-orri berean datuak bilatzeko. Gehienetan, beste orri batetik dagozkion balioak bilatu eta berreskuratuko dituzu.

Ordena erabiltzea ere VPR, bilatu beste Microsoft Excel orri batean, Argumentuan behar duzu table_array (taula) zehaztu orriaren izena harridura-ikur batekin eta ondoren gelaxka sorta batekin. Adibidez, hurrengo formulak erakusten du barrutia A2: B15 izeneko orri batean dago Sheet2.

=VLOOKUP(40,Sheet2!A2:B15,2)

=ВПР(40;Sheet2!A2:B15;2)

Jakina, orriaren izena ez da eskuz sartu behar. Hasi besterik ez formula idazten, eta argumentuari dagokionez table_array (taula), aldatu nahi duzun orrira eta hautatu nahi duzun gelaxka sorta saguarekin.

Beheko pantaila-argazkian agertzen den formulak "1 produktua" testua bilatzen du A zutabean (A1: B2 barrutiaren 9. zutabea da) lan-orri batean Prezioak.

=VLOOKUP("Product 1",Prices!$A$2:$B$9,2,FALSE)

=ВПР("Product 1";Prices!$A$2:$B$9;2;ЛОЖЬ)

Gogoratu testu-balio bat bilatzean, komatxoen artean ("") sartu behar duzula, Excel-eko formuletan egin ohi den bezala.

Argudiorako table_array (taula) komeni da erreferentzia absolutuak beti erabiltzea ($ zeinuarekin). Kasu honetan, bilaketa-barrutia aldatu gabe geratuko da formula beste gelaxka batzuetan kopiatzean.

Bilatu beste lan-koaderno batean VLOOKUP-ekin

Funtzionatzeko VPR Excel bi lan-libururen artean lan eginda, lan-liburuaren izena zehaztu behar duzu kortxete artean orri-izena baino lehen.

Adibidez, behean balioa bilatzen duen formula bat dago 40 fitxan Sheet2 liburuan Zenbakiak.xlsx:

=VLOOKUP(40,[Numbers.xlsx]Sheet2!A2:B15,2)

=ВПР(40;[Numbers.xlsx]Sheet2!A2:B15;2)

Hona hemen Excel-en formula bat sortzeko modurik errazena VPRbeste lan-koaderno batekin lotzen duena:

  1. Ireki bi liburuak. Ez da beharrezkoa, baina errazagoa da formula bat sortzea horrela. Ez duzu lan-liburuaren izena eskuz sartu nahi, ezta? Gainera, ustekabeko akatsetatik babestuko zaitu.
  2. Hasi funtzio bat idazten VPReta argudioari dagokionez table_array (taula), aldatu beste lan-liburu batera eta hautatu bertan beharrezko bilaketa-barrutia.

Beheko pantaila-argazkiak bilaketa lan-liburuko barruti batean ezarrita duen formula erakusten du PriceList.xlsx fitxan Prezioak.

Funtzio VPR Bilatutako lan-liburua ixten duzunean ere funtzionatuko du eta lan-liburuaren fitxategiaren bide osoa formula-barran agertzen da, behean erakusten den moduan:

Lan-koadernoaren edo orriaren izenak zuriuneak baditu, apostrofoen artean sartu behar da:

=VLOOKUP(40,'[Numbers.xlsx]Sheet2'!A2:B15,2)

=ВПР(40;'[Numbers.xlsx]Sheet2'!A2:B15;2)

Nola erabili izendun barruti edo taula bat VLOOKUP-ekin formuletan

Bilaketa-barruti bera funtzio anitzetan erabiltzeko asmoa baduzu VPR, sorta izendatu bat sor dezakezu eta bere izena formulan sartu argumentu gisa table_array (mahai).

Izeneko barruti bat sortzeko, besterik gabe, hautatu gelaxkak eta idatzi izen egokia eremuan Lehenengoa izena, formula-barraren ezkerraldean.

Orain idatz dezakezu honako formula hau produktu baten prezioa aurkitzeko Produktua 1:

=VLOOKUP("Product 1",Products,2)

=ВПР("Product 1";Products;2)

Barruti-izen gehienek Excel lan-liburu osorako funtzionatzen dute, beraz, ez dago argumenturako orri-izena zehaztu beharrik table_array (taula), formula eta bilaketa-barrutia lan-orri ezberdinetan egon arren. Lan-liburu desberdinetan badaude, barrutiaren izenaren aurretik lan-liburuaren izena zehaztu behar duzu, adibidez, honela:

=VLOOKUP("Product 1",PriceList.xlsx!Products,2)

=ВПР("Product 1";PriceList.xlsx!Products;2)

Beraz, formula askoz argiagoa dirudi, ados? Gainera, izen-barrutiak erabiltzea erreferentzia absolutuetarako alternatiba ona da, izen-barrutia ez baita aldatzen formula beste gelaxka batzuetara kopiatzen duzunean. Horrek esan nahi du ziur egon zaitezkeela formulako bilaketa-barrutia beti zuzena izango dela.

Gelaxka sorta bat Excel-eko kalkulu-orri oso batean bihurtzen baduzu komandoa erabiliz Taula (Taula) fitxa Gizarteratze (Txertatu), gero saguarekin barruti bat hautatzen duzunean, Microsoft Excel-ek automatikoki gehituko dizkio zutabeen izenak (edo taularen izena taula osoa hautatzen baduzu) formulari.

Amaitutako formulak honelako itxura izango du:

=VLOOKUP("Product 1",Table46[[Product]:[Price]],2)

=ВПР("Product 1";Table46[[Product]:[Price]];2)

Edo agian honela:

=VLOOKUP("Product 1",Table46,2)

=ВПР("Product 1";Table46;2)

Izendatutako barrutiak erabiltzean, estekek gelaxka berdinetara zuzenduko dute funtzioa kopiatzen duzun edozein lekutan VPR lan-koadernoaren barruan.

Komodinak erabiltzea VLOOKUP formuletan

Beste funtzio askotan bezala, VPR Karaktere komodin hauek erabil ditzakezu:

  • Galdera ikurra (?) - edozein karaktere bakarra ordezkatzen du.
  • Asteriskoa (*): edozein karaktere-segida ordezkatzen du.

Komodinak Funtzioetan erabiltzea VPR erabilgarria izan daiteke kasu askotan, adibidez:

  • Aurkitu behar duzun testua zehazki gogoratzen ez duzunean.
  • Gelaxka baten edukiaren parte den hitz bat aurkitu nahi duzunean. Jakin hori VPR gelaxkako edukiaren arabera bilatzen du bere osotasunean, aukera gaituta egongo balitz bezala Lotu gelaxka osoko edukia (Zelula osoa) Excel bilaketa estandarrean.
  • Gelaxka batek edukiaren hasieran edo amaieran espazio gehigarriak dituenean. Egoera horretan, burmuinak luzaroan txertatu ditzakezu, formulak zergatik ez duen funtzionatzen jakin nahian.

1. adibidea: zenbait karaktererekin hasten edo amaitzen den testuaren bila

Demagun behean agertzen den datu-basean bezero zehatz bat bilatu nahi duzula. Ez duzu bere abizena gogoratzen, baina badakizu “ack”-arekin hasten dela. Hona hemen lana ondo egingo duen formula:

=VLOOKUP("ack*",$A$2:$C$11,1,FALSE)

=ВПР("ack*";$A$2:$C$11;1;ЛОЖЬ)

Izen zuzena aurkitu duzula ziur zaudenean, formula bera erabil dezakezu bezero honek ordaindutako zenbatekoa aurkitzeko. Horretarako, aldatu besterik ez dago funtzioaren hirugarren argumentua VPR nahi duzun zutabe-zenbakira. Gure kasuan, hau da C zutabea (barrutiko hirugarrena):

=VLOOKUP("ack*",$A$2:$C$11,3,FALSE)

=ВПР("ack*";$A$2:$C$11;3;ЛОЖЬ)

Hona hemen komodinak dituzten adibide gehiago:

~ Bilatu "gizon" amaierako izen bat:

=VLOOKUP("*man",$A$2:$C$11,1,FALSE)

=ВПР("*man";$A$2:$C$11;1;ЛОЖЬ)

~ Bilatu "ad"-z hasi eta "semea"-z amaitzen den izena:

=VLOOKUP("ad*son",$A$2:$C$11,1,FALSE)

=ВПР("ad*son";$A$2:$C$11;1;ЛОЖЬ)

~ Zerrendan lehen izena aurkituko dugu, 5 karakterez osatua:

=VLOOKUP("?????",$A$2:$C$11,1,FALSE)

=ВПР("?????";$A$2:$C$11;1;ЛОЖЬ)

Funtzionatzeko VPR komodinekin behar bezala funtzionatu zuen, beti erabili behar duzun laugarren argumentua bezala FALSE (GEZURRA). Bilaketa-barrutiak komodinekin bilaketa-terminoekin bat datozen balio bat baino gehiago baditu, aurkitutako lehen balioa itzuliko da.

2. adibidea: Konbinatu komodinak eta gelaxka-erreferentziak VLOOKUP formuletan

Ikus dezagun orain funtzioa erabiliz nola bilatu jakiteko adibide apur bat konplexuagoa VPR gelaxka bateko balioaren arabera. Imajinatu A zutabea lizentzia-gakoen zerrenda dela eta B zutabea lizentzia baten jabe diren izenen zerrenda dela. Horrez gain, C1 gelaxkan lizentzia-gako baten zati bat (hainbat karaktere) duzu eta jabearen izena aurkitu nahi duzu.

Hau formula hau erabiliz egin daiteke:

=VLOOKUP("*"&C1&"*",$A$2:$B$12,2,FALSE)

=ВПР("*"&C1&"*";$A$2:$B$12;2;FALSE)

Formula honek C1 gelaxkaren balioa bilatzen du emandako barrutian eta B zutabetik dagokion balioa itzultzen du. Kontuan izan lehenengo argumentuan ampersand (&) karaktere bat erabiltzen dugula gelaxka-erreferentziaren aurretik eta ondoren testu-katea lotzeko.

Beheko irudian ikus dezakezun bezala, funtzioa VPR "Jeremy Hill" itzultzen du bere lizentzia-gakoak C1 gelaxkako karaktere-sekuentzia duelako.

Kontuan izan argumentua table_array (taula) goiko pantaila-argazkiak taularen izena dauka (7. taula) gelaxka sorta bat zehaztu beharrean. Hau da aurreko adibidean egin genuena.

Bat-etortze zehatza edo gutxi gorabeherakoa VLOOKUP funtzioan

Eta azkenik, ikus ditzagun funtziorako zehaztutako azken argumentuari VPR - barruti_bilaketa (tarte_ikuspegia). Ikasgaiaren hasieran esan bezala, argudio hau oso garrantzitsua da. Formula berdinean emaitza guztiz desberdinak lor ditzakezu bere balioarekin EGIA KODEA (EGIA) edo FALSE (GEZURRA).

Lehenik eta behin, jakin dezagun zer esan nahi duen Microsoft Excel-ek partida zehatzak eta gutxi gorabeherakoak.

  • Argudioa bada barruti_bilaketa (range_lookup) berdina da FALSE (FALSE), formulak bat-etortze zehatza bilatzen du, hau da, argumentuan emandako balio bera bilatu_balioa (bilaketa_balioa). t barrutiaren lehen zutabean badagai_array (taula) argumentuarekin bat datozen bi balio edo gehiago topatzen ditu bilatu_balioa (bilaketa_balioa), orduan lehenengoa hautatuko da. Bat-etortzerik aurkitzen ez bada, funtzioak errore baten berri emango du #AT (#N/A). Adibidez, hurrengo formulak errore baten berri emango du #AT (#N/A) A2:A15 barrutian baliorik ez badago 4:

    =VLOOKUP(4,A2:B15,2,FALSE)

    =ВПР(4;A2:B15;2;ЛОЖЬ)

  • Argudioa bada barruti_bilaketa (range_lookup) berdina da EGIA KODEA (EGIA), formulak gutxi gorabeherako bat etortzea bilatzen du. Zehatzago esanda, lehenengo funtzioa VPR bat-etortze zehatz bat bilatzen du, eta bat ez bada aurkitzen, gutxi gorabeherako bat hautatzen du. Gutxi gorabeherako bat etortzea argumentuan zehaztutako balioa gainditzen ez duen baliorik handiena da. bilatu_balioa (bilaketa_balioa).

Argudioa bada barruti_bilaketa (range_lookup) berdina da EGIA KODEA (EGIA) edo zehaztu gabe, barrutiaren lehen zutabeko balioak goranzko ordenan ordenatu behar dira, hau da, txikienetik handienera. Bestela, funtzioa VPR emaitza okerra itzul daiteke.

Aukerak duen garrantzia hobeto ulertzeko EGIA KODEA (EGIA) edo FALSE (GEZURRA), ikus ditzagun funtzioarekin formula batzuk gehiago VPR eta begiratu emaitzak.

1. adibidea: VLOOKUP-ekin bat-etortze zehatza aurkitzea

Gogoratzen duzun bezala, bat-etortze zehatz bat bilatzeko, funtzioaren laugarren argumentua VPR axola beharko luke FALSE (GEZURRA).

Itzul gaitezen taulara lehen adibidetik eta jakin dezagun zein animalia abiaduran mugi daitekeen 50 kilometro orduko. Uste dut formula honek ez dizula inolako zailtasunik sortuko:

=VLOOKUP(50,$A$2:$B$15,2,FALSE)

=ВПР(50;$A$2:$B$15;2;ЛОЖЬ)

Kontuan izan gure bilaketa-barrutiak (A zutabeak) bi balio dituela 50 – zeluletan A5 и A6. Formulak gelaxkako balioa itzultzen du B5. Zergatik? Zeren partida zehatz bat bilatzean, funtzioa VPR bilatzen ari denarekin bat datorren lehen balioa erabiltzen du.

2. adibidea: VLOOKUP erabiltzea gutxi gorabeherako bat etortzea aurkitzeko

Funtzioa erabiltzen duzunean VPR gutxi gorabeherako bat etortzea, hau da, argumentua denean barruti_bilaketa (range_lookup) berdina da EGIA KODEA (EGIA) edo baztertuta, egin behar duzun lehenengo gauza barrutia lehenengo zutabearen arabera ordenatzea da, goranzko ordenan.

Hau oso garrantzitsua da funtzioa delako VPR Emandakoaren ondotik hurrengo baliorik handiena itzultzen du eta, ondoren, bilaketa gelditzen da. Ordenaketa zuzena alde batera uzten baduzu, emaitza oso arraroak edo errore-mezu bat izango dituzu. #AT (#N/A).

Orain formula hauetako bat erabil dezakezu:

=VLOOKUP(69,$A$2:$B$15,2,TRUE) or =VLOOKUP(69,$A$2:$B$15,2)

=ВПР(69;$A$2:$B$15;2;ИСТИНА) or =ВПР(69;$A$2:$B$15;2)

Ikusten duzuenez, animalietatik zeinek duen abiadura hurbilen jakin nahi dut 69 kilometro orduko. Eta hona hemen funtzioak itzuli zidan emaitza VPR:

Ikus dezakezunez, formulak emaitza bat itzuli zuen Antelope (Antilopea), zeinaren abiadura 61 orduko kilometroak, zerrendan ere jasota dagoen arren Cheetah (Gepardoa) abiaduran dabilena 70 kilometro orduko, eta 70 hurbilago dago 69tik 61etik baino, ezta? Zergatik gertatzen da hau? Funtzioa delako VPR gutxi gorabeherako bat-etortze bat bilatzean, bilatzen ari dena baino handiagoa ez den baliorik handiena itzultzen du.

Adibide hauek funtzioarekin lan egiteko argi pixka bat ematea espero dut VPR Excel-en, eta jada ez duzu kanpotar bat bezala ikusten. Orain ez du minik laburki errepikatzea aztertu dugun materialaren gakoak memorian hobeto finkatzeko.

VLOOKUP Excel-en - hau gogoratu behar duzu!

  1. Funtzio VPR Excel-ek ezin du ezkerrera begiratu. Argumentuak emandako barrutiaren ezkerreko zutabean dagoen balioa bilatzen du beti table_array (mahai).
  2. Funtzioan VPR balio guztiak maiuskulak eta minuskulak bereizten dituzte, hau da, letra txikiak eta handiak baliokideak dira.
  3. Bilatzen ari zaren balioa bilatzen ari den barrutiaren lehen zutabeko balio minimoa baino txikiagoa bada, funtzioa VPR errore baten berri emango du #AT (#N/A).
  4. 3. argumentua bada col_index_num (zutabe_zenbakia) baino txikiagoa 1funtzioa VPR errore baten berri emango du #VALUE! (#BALIOA!). Barrutiaren zutabe kopurua baino handiagoa bada table_array (taula), funtzioak errore baten berri emango du #REF! (#LINK!).
  5. Erabili gelaxka-erreferentzia absolutuak argumentuan table_array (taula), formula kopiatzerakoan bilaketa-barruti zuzena gorde dadin. Saiatu izendun barrutiak edo taulak Excel-en erabiltzen alternatiba gisa.
  6. Gutxi gorabeherako partida-bilaketa bat egiterakoan, gogoratu bilatzen ari zaren barrutiaren lehenengo zutabea goranzko ordenan ordenatu behar dela.
  7. Azkenik, gogoratu laugarren argumentuaren garrantzia. Erabili balioak EGIA KODEA (EGIA) edo FALSE (GEZURRA) nahita eta buruhauste asko kenduko dituzu.

Gure funtzio-tutorialaren hurrengo artikuluetan VPR Excel-en, adibide aurreratuagoak ikasiko ditugu, hala nola, hainbat kalkulu erabiliz VPR, hainbat zutabetatik balioak ateraz eta gehiago. Eskerrik asko tutorial hau irakurtzeagatik eta datorren astean berriro elkartzea espero dut!

Utzi erantzun bat