Excel-en VLOOKUP funtzioa konbinatzen duen COLUMN funtzioarekin datu-base edo datu- taula baten errenkada bakar batetik itzultzeko aukera ematen duen bilaketa formula bat sortu dezakegu.
Goiko irudian agertzen den adibidean, bilaketa formula esker, balore guztiak itzultzeko erraza da, adibidez, prezioaren, zatikien kopurua eta hornitzailea.
10eko 10
Itzuli VLOOKUP Excelreko balio anitzak
Behean zerrendatutako urratsak jarraituz, goiko irudian ikusten den bilaketa-formula sortzen du, datu anitzetatik datozen balio anitzak itzultzeko.
Bilaketa-formula VLOOKUP barruan COLUMN funtzioa habiaratu behar da.
Funtzio bat egiteak bigarren funtzioa sartzen du lehen funtzioaren argumentu gisa.
Tutorial honetan, COLUMN funtzioa VLOOKUP zutabeen indizearen zenbakien argumentu gisa sartuko da.
Tutorialaren azken urratsa bilaketa formula formula zutabe osagarrien kopiatzea da, hautatutako zatiaren balio osagarriak berreskuratzeko.
Tutorialen edukiak
- Tutorial datuak sartu
- Datuen taularako izendatutako eramailea sortzea
- VLOOKUP Funtzioa abiarazten
- Bilaketa-balioaren argumentua zelula absolutuen erreferentziak erabiliz sartuz
- Taularen Array Argumentua sartuz
- COLUMN funtzio habiaratua sartuz
- VLOOKUP Funtzioa osatzea
- Begiratu Formula kopiatzea Bete heldulekuarekin
- Datuen bilaketa bilaketa formula batekin
10/10
Sartu Tutorial datuak
Tutorialaren lehen urratsa datuak Excel formatuan sartzea da.
Tutorialaren urratsak jarraitzeko, goiko irudian agertzen diren datuak sartu hurrengo gelaxketan .
- Idatzi goiko datu- sorta zelulak gelaxkatan D1-tik G1-ra
- Sartu bigarren gelaxka gelaxkak D4-tik G10-ra
Tutorial honetan sortutako bilaketa-irizpideak eta bilaketa formula lanaren 2. errenkadan sartuko dira.
Tutorialak ez du irudian ikusten den formatua barne hartzen, baina horrek ez du eragingo bilaketa formula nola funtzionatzen duen.
Aurretik ikusitakoen antzeko formatu-aukeren informazioa Formatua Oinarrizko Formatu Tutorial honetan eskuragarri dago.
Tutorial Urratsak
- Idatzi datuak goiko irudian ikusten den bezala D1 eta G10 artean
10/03
Datuen taularako izendatutako eramailea sortzea
Izeneko barrutia formula batean datu sorta bat aurkitzeko modu erraza da. Datuen gelaxken erreferentziak idazten hasi beharrean, barraren izena idatzi dezakezu.
Izeneko barruti bat erabiltzerakoan bigarren abantaila da barruti honetako gelaxka-erreferentziak ez direla inoiz aldatu, nahiz eta formula formula beste orri batera kopiatu.
Range izenak, beraz, gelaxka erreferentzial absolutuak erabiltzea dira, formulak kopiatzean akatsak saihesteko.
Oharra: barrutiaren izena ez da datuen izenburuak edo eremu izenak barne hartzen (errenkadan 4), baina datu berak bakarrik.
Tutorial Urratsak
- Nabarmendu gelaxkak D5-tik G10-ko lan-orrian hautatzeko
- Egin klik A zutabearen goiko zatian dagoen izen-kutxa
- Idatzi "Taula" (komatxorik gabe) Izena Kutxan
- Sakatu ENTER tekla teklatuan
- Gelaxkak D5-tik G10-ra dagoeneko "Taula" izenaren barrutia dute. VLOOKUP taula array argumentuaren izena erabiliko dugu geroago tutorialan
04 de 10
VLOOKUP elkarrizketa-koadroa irekitzea
Izan ere, gure bilaketa-formula zuzenean zuzenean zelula batean sartu ahal izateko, lan-orri batean, jende askok zailtzen du sintaxia zuzen mantentzeko, batez ere formula konplexu baterako, esaterako, tutorial honetan erabiltzen ari garen moduan.
Beste aukera bat, kasu honetan, VLOOKUP elkarrizketa koadroa erabiltzea da. Excel-en funtzio guztiak funtzioaren argumentu bakoitza beste lerro batean sartzeko aukera ematen duen elkarrizketa-koadro bat dauka.
Tutorial Urratsak
- Egin klik kalkulu orriko E2 gelaxkan - bi dimentsioko bilaketa formatuaren emaitzak bistaratuko diren tokian
- Egin klik Zinta botoian Formula fitxan
- Egin klik zutabeen Lookup & Reference aukera zerrendan goitibeherako zerrenda irekitzeko
- Egin klik VLOOKUP zerrendan funtzioaren elkarrizketa-koadroa irekitzeko
10/10
Bilaketa-balioaren argumentua zelula absolutuen erreferentziak erabiliz sartuz
Normalean, bilaketa-balioaren datuen eremua datu-taulako lehen zutabean dator .
Gure adibidean, bilaketa-balioa informazioa aurkitu nahi dugun hardware partearen izena da.
Begiratze- balioaren datu mota baikorrak hauek dira:
- testuaren datuak
- balio logikoa (TRUE edo FALSE bakarra)
- zenbaki bat
- lan- orriko balioa duen gelaxka erreferentzia
Adibide honetan, zelula erreferentzia sartuko dugu parte izenaren kokalekua non dagoen jakiteko: zelula D2.
Zelula absolutuen erreferentziak
Tutorialaren ondorengo pausuan, lookup zelula E2 zelula F2 eta G2 zelula kopiatuko dugu.
Normalean, formulak Excel-en kopiatzean, zelula erreferentziak aldatu egiten dira kokapen berria islatzeko.
Hori gertatzen bada, D2 - bilaketa-balioaren zelula erreferentzia - formula aldatuko da, F2 eta G2 zelulak akatsak sortuz.
Akatsak saihesteko, gelaxka erreferentzia D2 bihurtuko dugu zelula erreferentzia absolutu batean.
Gelaxken erreferentzia absolutuak ez dira aldatzen formulak kopiatzen direnean.
Gelaxken erreferentziak absolutuak F4 tekla teklatua sakatzean sortzen dira. Horrela, dolarraren seinaleak gehitzen ditu zelula erreferentziaren inguruan, adibidez, $ D $ 2
Tutorial Urratsak
- Egin klik elkarrizketa-koadroko lookup_value lerroan
- Egin klik gelaxka D2 gelaxka erreferentziari lookup_value lerroari gehitzeko. Hau da, informazio bila nabilen partearen izena idatziko dugu
- Txertatze-puntua mugitu gabe, sakatu F4 tekla teklatuan D2 bihurtzeko gelaxka-erreferentzia absolutuan $ D $ 2
- Utzi VLOOKUP funtzioaren elkarrizketa-koadroa irekita tutorearen hurrengo urratsean
10eko 10
Taularen Array Argumentua sartuz
Taula-array bilaketa-formula bilatzen duen datuen taula da, nahi dugun informazioa bilatzeko.
Taula arrayek gutxienez bi datu zutabe eduki behar ditu.
- lehen zutabean bilaketa-balioaren argumentua (tutorearen aurreko urratsa) dauka.
- Bigarrena, eta zutabe osagarri gehiago, bilaketa formula erabiliko dugu zehaztutako informazioa bilatzeko.
Taularen matrizearen argumentua datu-taulan edo barruti-izen gisa duen gelaxka-erreferentziak dituzten barruti bat izan behar da.
Adibide honetan, tutorearen 3. urratsean sortutako sorta izena erabiliko dugu.
Tutorial Urratsak
- Egin klik taula gainean lerroan elkarrizketa-koadroan
- Idatzi "Taula" (komatxorik gabe) argumentu honen barrutiaren izena sartzeko
- Utzi VLOOKUP funtzioaren elkarrizketa-koadroa irekita tutorearen hurrengo urratsean
07 de 10
COLUMN Funtzioa habiaratuz
Normalean VLOOKUP datuak datu taula batetik zutabe batetik datuak bakarrik itzultzen ditu eta zutabe hau zutabeen indizearen zenbakiaren argumentuak zehazten du.
Adibide honetan, ordea, hiru zutabe ditugu, datuak itzuli nahi ditugu, beraz, zutabeen aurkibide zenbakia erraz aldatzeko modu bat behar dugu, gure kontsulta formula editatu gabe.
Kolore-funtzioaren funtzioan sartuko da COLUMN funtzioa. Zutabe-indizeen argumentu gisa sartuz gero, bilaketa-formula D2 zelularretik kopiatuko da, E2 eta F2 gelaxkak geroago tutorialan.
Igeriketa funtzioak
COLUMN funtzioa, beraz, VLOOKUP zutabeen indizearen zenbaki argumentua da .
Horrela, COLUMN funtzioa VLOOKUP barruan biltzen da elkarrizketa-koadroan Col_index_num lerroan.
COLUMN funtzioa eskuz sartu
Habiaratze funtzioak egitean, Excelek ez du aukera ematen bigarren funtzioaren elkarrizketa - koadroa irekitzeko, bere argumentuak idazteko.
COLUMN funtzioa, beraz, eskuz sartu behar da Col_index_num lerroan.
COLUMN funtzioak argumentu bakarra du - erreferentzia argumentua zelula erreferentzia bat da.
COLUMN funtzioaren Erreferentzia Argumentua aukeratuz
COLUMN funtzioaren lana Erreferentzia argumentu gisa emandako zutabe kopurua itzultzeko da.
Beste era batera esanda, zutabe-gutuna zutabea zutabe bat bihurtzen du A lehenengo zutabea izanik, zutabe B bigarrena eta abar.
Datuak itzuli nahi ditugun lehen eremua elementuaren prezioa da. Datu-taulan bi zutabetan dagoenez, gelaxka erreferentea aukeratu ahal izango dugu zutabe B edozein erreferentzia argumentu gisa, zenbaki 2 lortzeko. Col_index_num argumentua.
Tutorial Urratsak
- VLOOKUP funtzioaren elkarrizketa-koadroan, sakatu Col_index_num linean
- Idatzi funtzioaren izena zutabe bat irekita dagoen biribila " ( "
- Egin klik gelaxka B1 fitxan, gelaxka erreferentzia erreferentzia argumentu gisa sartzeko
- Idatzi tarte itxiera " ) " COLUMN funtzioa osatzeko
- Utzi VLOOKUP funtzioaren elkarrizketa-koadroa irekita tutorearen hurrengo urratsean
08 de 10
VLOOKUP Array Lookup Argumentua sartu
VLOOKUP-en Range_lookup argumentua balio logikoa da (TRUE edo FALSE bakarra), VLOOKUP-k Lookup_value bati zehatz edo gutxi gorabeherako bat aurkitzea nahi duzun ala ez adierazten du.
- TRUE (EGIA) bada edo argumentu hau baliogabea bada, VLOOKUP-k Lookup_valuearekin datorren parekatze zuzena itzultzen du edo, partida zehatza ez bada, VLOOKUP-k hurrengo balio handiena itzultzen du. Horretarako formula egiteko, table_array lehen zutabean dauden datuak ordena gorakorrean ordenatu behar dira.
- FALSE baldin badago, VLOOKUP Lookup_valuearekin bat datorren partida bat bakarrik erabiliko du. Bi balio edo gehiago badituzu, balioaren balioarekin bat datorren Table_array lehen zutabean badago, lehenengo balioaren erabilpena erabiltzen da. Partidu zehatza ez bada aurkitzen, # N / A akatsa itzuliko da.
Tutorial honetan, hardware elementu jakin bati buruzko informazio zehatza bilatzen ari garenez, Range_lookup False berdina ezarriko dugu.
Tutorial Urratsak
- Egin klik Range_lookup lerroan elkarrizketa-koadroan
- Idatzi hitz faltsua lerro honetan, VLOOKUP nahi duguna aurkitzeko datuak zehaztea itzultzea nahi dugula adierazteko
- Egin klik Ados bilaketa-formula osatzeko eta itxi elkarrizketa-koadroa
- Bilaketa-irizpideak zelan sartu ez direnez gero, D2 # N / A errore bat agertuko da E2 gelaxkan
- Errore hau zuzenduko da bilaketa-irizpideak tutorearen azken urratsean gehituko dugunean
10/09
Begiratu Formula kopiatzea Bete heldulekuarekin
Bilaketa-formula datu-taula zutabe anitzetatik datuak aldi berean berreskuratzeko asmoa da.
Horretarako, bilaketa-formula behar da, informazio hori nahi dugun eremu guztietan.
Tutorial honetan datuen zutabeetako 2, 3 eta 4 datuen datuak berreskuratu nahi ditugu - prezioa, parten zenbakia eta hornitzailearen izena partiturak Lookup_valua gisa sartzen direnean.
Datuak fitxan eredu erregular batean ezartzen direnez gero, lookup formula kopiatu ahal izango dugu E2 zelularra F2 eta G2 zeluletan.
Formula kopiatzen den bezala, Excel-ek zelulen erreferentzia erlatiboa eguneratuko du COLUMN funtzioan (B1) formularen kokapen berria islatzeko.
Horrez gain, Excel-ek ez du aldatzen gelaxka-erreferentzia absolutuaren $ D $ 2 eta barrutiaren izendatutako taula formula kopiatzen denean.
Excel datu datuak kopiatzeko modu bat baino gehiago dago, baina seguru asko modurik errazena Betegarria erabiltzea da.
Tutorial Urratsak
- Egin klik E2 gelaxkan - lookup formula kokatuta dagoenean - gelaxka aktiboa egiteko
- Jarri saguaren erakuslea beheko eskuineko izkinan dagoen lauki beltzaren gainean. Erakuslea plus ikurrarekin aldatuko da " + " hau: betegarria da
- Egin klik saguaren ezkerreko botoian eta arrastatu helduleku betegarria G2 gelaxkara
- Askatu saguaren botoia eta F3 zelularrak bi dimentsioko bilaketa formula izan behar du
- Behar bezala egin bada, gelaxkak F2 eta G2-k orain ere N2 / E akatsa izan dezakete E2 gelaxkan
10tik 10
Bilaketa-irizpideak sartzea
Beharrezko zelulak kopiatzeko formula bete ondoren datu-taulatik informazioa berreskuratzeko erabil daiteke.
Horretarako, idatzi Lookup_value zelula (D2) sartu nahi duzun elementuaren izena eta sakatu Sartu tekla teklatuan.
Egin ondoren, bilaketa-formula duen gelaxka bakoitzak bilaketa-tresnaren inguruko datuen zati bat eduki beharko luke.
Tutorial Urratsak
- Egin klik D2 gelaxkaren fitxan
- Idatzi widgeta D2 gelaxkan eta sakatu SARTZE tekla teklatuan
- Hurrengo informazioa zelula E2 eta G2 artean bistaratu behar da:
- E2 - $ 14.76 - widget baten prezioa
- F2 - PN-98769 - widget baten zati kopurua
- G2 - Widgets Inc. - widgetentzako hornitzailearen izena
- Probatu VLOOKUP array formula gehiago, beste zati batzuen izenak D2 zelulan sartu eta emaitzak E2 eta G2 zelulak behatuz.
Errore mezu bat, adibidez #REF! E2, F2 edo G2 zeluletan agertzen da, VLOOKUP errore-mezuen zerrenda honek arazoa non dagoen jakiteko lagun zaitzake.