Të gjitha sekretet e funksionit Excel VLOOKUP për gjetjen e të dhënave në një tabelë dhe nxjerrjen e tyre në një tjetër

2024 Autor: Malcolm Clapton | [email protected]. E modifikuara e fundit: 2023-12-17 04:10

Pas leximit të artikullit, jo vetëm që do të mësoni se si të gjeni të dhëna në një spreadsheet Excel dhe t'i nxjerrni ato në një tjetër, por edhe teknika që mund të përdoren në lidhje me funksionin VLOOKUP.

Kur punoni në Excel, shumë shpesh lind nevoja për të gjetur të dhëna në një tabelë dhe për t'i nxjerrë ato në një tjetër. Nëse ende nuk dini si ta bëni këtë, atëherë pasi të keni lexuar artikullin, jo vetëm që do të mësoni se si ta bëni këtë, por gjithashtu do të zbuloni se në cilat kushte mund të shtrydhni performancën maksimale nga sistemi. Shumica e teknikave shumë efektive që duhet të përdoren në lidhje me funksionin VLOOKUP janë konsideruar.

Edhe nëse keni vite që përdorni funksionin VLOOKUP, atëherë me një shkallë të lartë probabiliteti ky artikull do t'ju jetë i dobishëm dhe nuk do t'ju lërë indiferentë. Për shembull, duke qenë specialist i IT-së dhe më pas drejtues në IT, kam 15 vjet që përdor VLOOKUP, por arrita të merrem me të gjitha nuancat vetëm tani, kur fillova t'u mësoj njerëzve Excel në baza profesionale.

VLOOKUP është një shkurtim për vvertikale NSinspektimit. Po kështu, VLOOKUP - Vertical LOOKUP. Vetë emri i funksionit na lë të kuptohet se ai kërkon në rreshtat e tabelës (vertikalisht - duke përsëritur mbi rreshta dhe duke rregulluar kolonën), dhe jo në kolona (horizontalisht - duke përsëritur mbi kolona dhe duke rregulluar rreshtin). Duhet të theksohet se VLOOKUP ka një motër - një rosë të shëmtuar që nuk do të bëhet kurrë një mjellmë - ky është funksioni HLOOKUP. HLOOKUP, ndryshe nga VLOOKUP, kryen kërkime horizontale, por koncepti i Excel (dhe në të vërtetë koncepti i organizimit të të dhënave) nënkupton që tabelat tuaja kanë më pak kolona dhe shumë më shumë rreshta. Kjo është arsyeja pse ne duhet të kërkojmë sipas rreshtave shumë herë më shpesh sesa sipas kolonave. Nëse e përdorni funksionin HLO shumë shpesh në Excel, atëherë ka të ngjarë që nuk keni kuptuar diçka në këtë jetë.

Sintaksë

Funksioni VLOOKUP ka katër parametra:

= VLOOKUP (;; [;]), këtu:

- vlera e dëshiruar (rrallë) ose një referencë për një qelizë që përmban vlerën e dëshiruar (shumica dërrmuese e rasteve);

- referencë për një varg qelizash (varg dy-dimensionale), në kolonën FIRST (!) ku do të kërkohet vlera e parametrit;

- numri i kolonës në intervalin nga i cili do të kthehet vlera;

- ky është një parametër shumë i rëndësishëm që i përgjigjet pyetjes nëse kolona e parë e diapazonit është renditur në rend rritës. Nëse grupi është i renditur, ne specifikojmë vlerën TRUE ose 1, përndryshe - FALSE ose 0. Nëse ky parametër hiqet, ai vendoset në 1.

Vë bast se shumë nga ata që e njohin funksionin VLOOKUP si flaky, pasi lexojnë përshkrimin e parametrit të katërt, mund të ndihen të pakëndshëm, pasi janë mësuar ta shohin atë në një formë pak më ndryshe: zakonisht ata flasin për një përputhje të saktë kur kërkimi (FALSE ose 0) ose një skanim i diapazonit (TRUE ose 1).

Tani duhet të tendoseni dhe të lexoni disa herë paragrafin tjetër derisa të ndjeni kuptimin e asaj që u tha deri në fund. Çdo fjalë është e rëndësishme atje. Shembujt do t'ju ndihmojnë ta kuptoni.

Si funksionon saktësisht formula VLOOKUP?

• Formula e tipit I. Nëse parametri i fundit hiqet ose specifikohet i barabartë me 1, atëherë VLOOKUP supozon se kolona e parë është renditur në rend rritës, kështu që kërkimi ndalon në rreshtin që menjëherë i paraprin rreshtit që përmban vlerën më të madhe se ajo e dëshiruar … Nëse nuk gjendet një varg i tillë, rreshti i fundit i diapazonit kthehet.

  

• Formula II. Nëse parametri i fundit specifikohet si 0, atëherë VLOOKUP skanon kolonën e parë të grupit në mënyrë sekuenciale dhe menjëherë ndalon kërkimin kur gjendet përputhja e parë e saktë me parametrin, përndryshe kodi i gabimit # N / A (# N / A) është kthyer.

  

Formulon flukset e punës

VPR lloji I

VPR tipi II

Pasojat për formulat e formës I

1. Formulat mund të përdoren për të shpërndarë vlerat nëpër diapazon.
2. Nëse kolona e parë përmban vlera të kopjuara dhe renditet saktë, atëherë do të kthehet i fundit nga rreshtat me vlera të kopjuara.
3. Nëse kërkoni për një vlerë që është dukshëm më e madhe se sa mund të përmbajë kolona e parë, atëherë mund të gjeni lehtësisht rreshtin e fundit të tabelës, i cili mund të jetë mjaft i vlefshëm.
4. Kjo pamje do të kthejë gabimin # N / A vetëm nëse nuk gjen një vlerë më të vogël ose të barabartë me atë të dëshiruar.
5. Është mjaft e vështirë të kuptohet që formula kthen vlerat e gabuara nëse grupi juaj nuk është i renditur.

Pasojat për formulat e tipit II

Nëse vlera e dëshiruar ndodh disa herë në kolonën e parë të grupit, atëherë formula do të zgjedhë rreshtin e parë për marrjen e mëvonshme të të dhënave.

Performanca e VLOOKUP

Ju keni arritur kulmin e artikullit. Do të duket, mirë, cili është ndryshimi nëse specifikoj zero ose një si parametrin e fundit? Në thelb, të gjithë tregojnë, natyrisht, zero, pasi kjo është mjaft praktike: nuk keni nevojë të shqetësoheni për renditjen e kolonës së parë të grupit, mund të shihni menjëherë nëse vlera u gjet apo jo. Por nëse keni disa mijëra formula VLOOKUP në fletën tuaj, do të vini re se VLOOKUP II është i ngadalshëm. Në të njëjtën kohë, zakonisht të gjithë fillojnë të mendojnë:

• Më duhet një kompjuter më i fuqishëm;
• Më duhet një formulë më e shpejtë, për shembull, shumë njerëz dinë për INDEX + MATCH, i cili supozohet se është më i shpejtë nga 5-10%.

Dhe pak njerëz mendojnë se sapo të filloni të përdorni VLOOKUP të tipit I dhe të siguroheni që kolona e parë të renditet me çdo mjet, shpejtësia e VLOOKUP do të rritet 57 herë. Po shkruaj me fjalë - PESËDHJETE E SHTATË HERË! Jo 57%, por 5700%. Unë e kontrollova këtë fakt mjaft të besueshëm.

Sekreti i një pune kaq të shpejtë qëndron në faktin se një algoritëm kërkimi jashtëzakonisht efikas mund të zbatohet në një grup të renditur, i cili quhet kërkim binar (metoda e përgjysmimit, metoda e dikotomisë). Pra, VLOOKUP i tipit I e aplikon atë, dhe VLOOKUP i tipit II kërkon pa asnjë optimizim fare. E njëjta gjë vlen edhe për funksionin MATCH, i cili përfshin një parametër të ngjashëm, dhe funksionin LOOKUP, i cili funksionon vetëm në grupe të renditura dhe përfshihet në Excel për pajtueshmërinë me Lotus 1-2-3.

Disavantazhet e formulës

Disavantazhet e VLOOKUP janë të dukshme: së pari, ai kërkon vetëm në kolonën e parë të grupit të specifikuar, dhe së dyti, vetëm në të djathtë të kësaj kolone. Dhe siç e kuptoni, mund të ndodhë që kolona që përmban informacionin e nevojshëm të jetë në të majtë të kolonës në të cilën do të kërkojmë. Kombinimi tashmë i përmendur i formulave INDEX + MATCH nuk ka këtë pengesë, gjë që e bën atë zgjidhjen më fleksibile për nxjerrjen e të dhënave nga tabelat në krahasim me VLOOKUP (VLOOKUP).

Disa aspekte të zbatimit të formulës në jetën reale

Kërkimi i diapazonit

Një ilustrim klasik i një kërkimi të gamës është detyra e përcaktimit të një zbritjeje sipas madhësisë së porosisë.

Kërkoni për vargjet e tekstit

Sigurisht, VLOOKUP nuk duket vetëm për numra, por edhe për tekst. Duhet të kihet parasysh se formula nuk bën dallim midis rastit të personazheve. Nëse përdorni shkronja të egra, mund të organizoni një kërkim të paqartë. Ka dy karaktere të egra: "?" - zëvendëson çdo karakter në një varg teksti, "*" - zëvendëson çdo numër të çdo karakteri.

Hapësirat luftarake

Shpesh shtrohet pyetja se si të zgjidhet problemi i hapësirave shtesë gjatë kërkimit. Nëse tabela e kërkimit ende mund të pastrohet prej tyre, atëherë parametri i parë i formulës VLOOKUP nuk varet gjithmonë nga ju. Prandaj, nëse ekziston rreziku i bllokimit të qelizave me hapësira shtesë, atëherë mund të përdorni funksionin TRIM për ta pastruar atë.

Format të ndryshëm të të dhënave

Nëse parametri i parë i funksionit VLOOKUP i referohet një qelize që përmban një numër, por që ruhet në qeli si tekst, dhe kolona e parë e grupit përmban numra në formatin e duhur, atëherë kërkimi do të dështojë. Situata e kundërt është gjithashtu e mundur. Problemi mund të zgjidhet lehtësisht duke përkthyer parametrin 1 në formatin e kërkuar:

= VLOOKUP (−− D7; Produktet! $ A $ 2: $ C $ 5; 3; 0) - nëse D7 përmban tekst dhe tabela përmban numra;

= VLOOKUP (D7 & ""); Produktet $ A $ 2: $ C $ 5; 3; 0) - dhe anasjelltas.

Nga rruga, ju mund ta përktheni tekstin në një numër në disa mënyra menjëherë, zgjidhni:

• Mohimi i dyfishtë -D7.
• Shumëzimi me një D7 * 1.
• Mbledhja zero D7 + 0.
• Ngritja në fuqinë e parë D7 ^ 1.

Konvertimi i një numri në tekst bëhet përmes lidhjes me një varg bosh, i cili detyron Excel të konvertojë llojin e të dhënave.

Si të shtypni # N / A

Kjo është shumë e përshtatshme për të bërë me funksionin IFERROR.

Për shembull: = IFERROR (VLOOKUP (D7; Produkte! $ A $ 2: $ C $ 5; 3; 0); "").

Nëse VLOOKUP kthen kodin e gabimit # N / A, atëherë IFERROR do ta ndërpresë atë dhe do të zëvendësojë parametrin 2 (në këtë rast, një varg bosh), dhe nëse nuk ndodh asnjë gabim, atëherë ky funksion do të pretendojë se nuk ekziston fare, por ka vetëm VLOOKUP që ka kthyer rezultatin normal.

Array

Shpesh ata harrojnë të bëjnë referencën e grupit absolut, dhe kur shtrihen grupi "noton". Mos harroni të përdorni $ A $ 2: $ C $ 5 në vend të A2: C5.

Është një ide e mirë që ta vendosni grupin e referencës në një fletë të veçantë të librit të punës. Nuk bie nën këmbë dhe do të jetë më e sigurt.

Një ide edhe më e mirë do të ishte deklarimi i këtij grupi si një varg me emër.

Shumë përdorues, kur specifikojnë një grup, përdorin një ndërtim si A: C, duke specifikuar kolona të tëra. Kjo qasje ka të drejtë të ekzistojë, pasi ju jeni të shpëtuar nga detyrimi për të mbajtur gjurmët e faktit që grupi juaj përfshin të gjitha vargjet e kërkuara. Nëse shtoni rreshta në fletë me grupin origjinal, atëherë diapazoni i specifikuar si A: C nuk ka nevojë të rregullohet. Natyrisht, ky konstrukt sintaksor e detyron Excel-in të bëjë pak më shumë punë sesa të specifikojë saktësisht diapazonin, por kjo shpenzim mund të neglizhohet. Po flasim për të qindtat e sekondës.

Epo, në prag të gjeniut - të rregulloni grupin në formë.

Përdorimi i funksionit COLUMN për të specifikuar kolonën për të nxjerrë

Nëse tabela në të cilën po merrni të dhëna duke përdorur VLOOKUP ka të njëjtën strukturë si tabela e kërkimit, por thjesht përmban më pak rreshta, atëherë mund të përdorni funksionin COLUMN () në VLOOKUP për të llogaritur automatikisht numrat e kolonave që do të merren. Në këtë rast, të gjitha formulat VLOOKUP do të jenë të njëjta (të rregulluara për parametrin e parë, i cili ndryshon automatikisht)! Vini re se parametri i parë ka një koordinatë absolute të kolonës.

Krijimi i një çelësi të përbërë me & "|" &

Nëse bëhet e nevojshme të kërkohet nga disa kolona në të njëjtën kohë, atëherë është e nevojshme të bëhet një çelës i përbërë për kërkimin. Nëse vlera e kthyer nuk do të ishte tekstuale (siç është rasti me fushën "Kodi"), por numerike, atëherë formula më e përshtatshme SUMIFS do të ishte e përshtatshme për këtë dhe çelësi i kolonës së përbërë nuk do të kërkohej fare.

Ky është artikulli im i parë për një Lifehacker. Nëse ju pëlqeu, ju ftoj ta vizitoni, dhe gjithashtu lexoni me kënaqësi në komente për sekretet tuaja të përdorimit të funksionit VLOOKUP dhe të ngjashme. Faleminderit.:)