01 nga 03
Gjeni të dhëna në të majtë
Përmbledhja e formulës së Excel Lookup Left
Funksioni VLOOKUP i Excel përdoret për të gjetur dhe kthyer informacionin nga një tabelë e të dhënave bazuar në një vlerë kërkimi që ju zgjidhni.
Normalisht, VLOOKUP kërkon që vlera e kërkimit të jetë në kolonën më të majtë të tabelës së të dhënave dhe funksioni të kthejë një fushë tjetër të të dhënave të vendosur në të njëjtën rresht në të djathtë të kësaj vlere.
Duke kombinuar VLOOKUP me funksionin CHOOSE ; megjithatë, mund të krijohet një formulë e gjetjes së majtë që do të:
- lejon që vlera e kërkimit të zgjidhet nga çdo kolonë në tabelën e të dhënave
- kthimin e informacionit të vendosur në çdo kolonë në të majtë të vlerës së kërkimit
Shembull: Përdorimi i funksioneve VLOOKUP dhe ZGJIDHJA në një Formulë Kërkimi Majtas
Hapat e detajuar më poshtë krijojnë formulën e gjetjes së majtë të parë në imazhin e mësipërm.
Formula
= VLOOKUP ($ D $ 2, të zgjedhur ({1,2} $ F: $ F, $ D: $ D), 2, FALSE)bën të mundur gjetjen e pjesës së furnizuar nga kompanitë e ndryshme të renditura në kolonën 3 të tabelës së të dhënave.
Puna e funksionit CHOOSE në formulë është mashtrimi i VLOOKUP në besimin se kolona 3 është në të vërtetë kolona 1. Si rezultat, emri i kompanisë mund të përdoret si vlerë e kërkimit për të gjetur emrin e pjesës së furnizuar nga secila kompani.
Hapat e Tutorial - Hyrja e të Dhënave të Tutorial
- Futni titujt e mëposhtëm në qelizat e shënuara: D1 - Furnizuesi E1 - Pjesa
- Shkruani tabelën e të dhënave të shikuara në imazhin e mësipërm në qelizat D4 deri në F9
- Rreshtat 2 dhe 3 janë lënë bosh për të akomoduar kriteret e kërkimit dhe formulën e gjetjes së majtë të krijuar gjatë këtij udhezimi
Fillimi i Formulës së Kërkimit të Majtë - Hapja e kutisë së dialogut VLOOKUP
Megjithëse është e mundur që vetëm të shkruani formulën e mësipërme direkt në qelizën F1 në fletën e punës, shumë njerëz kanë vështirësi me sintaksën e formulës.
Një alternativë, në këtë rast, është përdorimi i kutisë së dialogut VLOOKUP. Pothuajse të gjitha funksionet e Excel-it kanë një kuti dialogu që ju lejon të futni secilën prej argumenteve të funksionit në një vijë të veçantë.
Hapat e Tutorial
- Klikoni në qelizën E2 të fletës së punës - vendndodhjen ku do të shfaqen rezultatet e formulës së gjetjes së majtë
- Klikoni në tab Formulat e shiritit
- Klikoni në opsionin Lookup & Reference në shiritin për të hapur drop down listën e funksionit
- Klikoni në VLOOKUP në listë për të shfaqur kutinë e dialogut të funksionit
02 nga 03
Futja e Argumenteve në kutinë e dialogut VLOOKUP - Kliko për të parë imazhin më të madh
Argumentet e VLOOKUP
Argumentet e një funksioni janë vlerat e përdorura nga funksioni për të llogaritur një rezultat.
Në kutinë e dialogut të një funksioni, emri i secilit argument gjendet në një vijë të veçantë të ndjekur nga një fushë në të cilën të futni një vlerë.
Futni vlerat vijuese për secilën prej argumenteve të VLOOKUP në vijën e saktë të kutisë së dialogut siç tregohet në imazhin e mësipërm.
Vlera e Kërkimit
Vlera e kërkimit është fusha e informacionit që përdoret për të kërkuar tabelën e tabelave. VLOOKUP kthen një fushë tjetër të të dhënave nga rreshti i njëjtë si vlera e kërkimit.
Ky shembull përdor një referencë të qelizës në vendin ku emri i kompanisë do të futet në fletën e punës. Avantazhi i kësaj është se ai e bën të lehtë për të ndryshuar emrin e kompanisë pa editing formulën.
Hapat e Tutorial
- Klikoni në vijën e lookup_value në kutinë e dialogut
- Klikoni në qelizën D2 për të shtuar këtë referencë të qelizës në linjë lookup_value
- Shtypni butonin F4 në tastierë për të bërë referencën e qelizës absolute - $ D $ 2
Shënim: Referencat e qelizave absolute përdoren për vlerat e kërkimit dhe argumentet e grupeve të tabelave për të parandaluar gabimet nëse formula e lookup kopjohet në qeliza të tjera në fletën e punës.
Tabela e tabelës: Hyrja në funksionin CHOOSE
Argumenti i grupit të tabelës është blloku i të dhënave të afërt nga i cili kërkohet informacioni specifik.
Normalisht, VLOOKUP duket vetëm në të djathtën e argumentit të vlerës së kërkimit për të gjetur të dhëna në tabelën e tabelës. Për të marrë atë për të parë majtas, VLOOKUP duhet të mashtrohet duke riorganizuar kolonat në tabelën e tabelës duke përdorur funksionin ZGJIDHJA.
Në këtë formulë, funksioni CHOOSE kryen dy detyra:
- krijon një grup tabelash që është vetëm dy kolona të gjera - kolonat D dhe F
- ai ndryshon të drejtën në rendin e majtë të kolonave në tabelën e tabelës në mënyrë që kolona F të vijë së pari dhe kolona D është e dyta
Detajet se si funksioni CHOOSE kryen këto detyra mund të gjenden në faqen 3 të tutorialit .
Hapat e Tutorial
Shënim: Kur futni funksione me dorë, secili nga argumentet e funksionit duhet të ndahet me një presje "," .
- Në kutinë e dialogut të funksionit VLOOKUP, klikoni në vijën Table_array
- Futni funksionin e mëposhtëm ZGJIDHNI
- ZGJIDHNI ({1,2} $ F: $ F, $ D: $ D)
Numri i indeksit të kolonës
Normalisht, numri i indeksit të kolonës tregon se cila kolonë e tabelës së tabelës përmban të dhënat që ju jeni pas. Në këtë formulë; megjithatë, ai i referohet rendit të shtyllave të vendosura nga funksioni CHOOSE.
Funksioni CHOOSE krijon një grup tryezash që është dy kolona të gjerë me kolonën F parë pasuar nga kolona D. Meqenëse informacioni i kërkuar - emri i pjesës - është në kolonën D, vlera e argumentit të indeksit të kolonës duhet të vendoset në 2.
Hapat e Tutorial
- Klikoni në vijën Col_index_num në kutinë e dialogut
- Shkruani një 2 në këtë rresht
Kërkimi i zonës
Argumenti VLOOKUP i Range_lookup është një vlerë logjike (vetëm TRUE ose FALSE) që tregon nëse doni që VLOOKUP të gjejë një ndeshje të saktë ose të përafërt me vlerën e kërkimit.
- Nëse TRUE ose nëse ky argument hiqet, VLOOKUP kthen ose një ndeshje të saktë me Lookup_value ose, në qoftë se një ndeshje e saktë nuk gjendet, VLOOKUP kthen vlerën tjetër më të madhe. Për formulën për ta bërë këtë, të dhënat në kolonën e parë të tabelës së tabelave duhet të renditen sipas rendit rritës .
- Nëse FALSE, VLOOKUP do të përdorë vetëm një ndeshje të saktë me Lookup_value. Nëse ka dy ose më shumë vlera në kolonën e parë të tabelës së tabelës që përputhet me vlerën e kërkimit, përdoret vlera e parë e gjetur. Nëse nuk gjendet një ndeshje e saktë, kthehet një gabim #N / A.
Në këtë tutorial, pasi ne jemi duke kërkuar për një emër të veçantë pjesë, Range_lookup do të vendoset në False kështu që vetëm ndeshjet e sakta do të kthehen nga formula.
Hapat e Tutorial
- Klikoni në vijën Range_lookup në kutinë e dialogut
- Shtypni fjalën False në këtë vijë për të treguar që ne duam që VLOOKUP të kthejë një ndeshje të saktë për të dhënat që po kërkojmë
- Kliko OK për të përfunduar formulën e gjetjes së majtë dhe kutinë e dialogut të ngushtë
- Pasi që ne ende nuk kemi hyrë në emrin e kompanisë në qelizën D2, një gabim # N / A duhet të jetë i pranishëm në qelizën E2
03 nga 03
Testimi i Formulës së Kërkimit të Majtë
Kthimi i të dhënave me formulën e kërkimit të majtë
Për të gjetur se cilat kompani furnizojnë cilat pjesë, shtypni emrin e kompanisë në qelizën D2 dhe shtypni butonin ENTER në tastierë.
Emri i pjesës do të shfaqet në qelizën E2.
Hapat e Tutorial
- Klikoni në qelizën D2 në fletën tuaj të punës
- Lloji Gadgets Plus në qelizën D2 dhe shtypni butonin ENTER në tastierë
- Gadgets teksti - pjesa e furnizuar nga kompania Gadgets Plus - duhet të shfaqet në qelizën E2
- Testoni formulën e kërkimit më tej duke shtypur emra të tjerë të kompanisë në qelizën D2 dhe emri përkatës i pjesës duhet të shfaqet në qelizën E2
Mesazhet e gabimit të VLOOKUP
Nëse një mesazh gabimi siç është # N / A shfaqet në qelizën E2, së pari kontrolloni gabimet e drejtshkrimit në qelizën D2.
Nëse nuk është problemi i drejtshkrimit, kjo listë e mesazheve të gabimit VLOOKUP mund t'ju ndihmojë të përcaktoni se ku qëndron problemi.
Thyerja e punës së funksionit të zgjedhjes
Siç u përmend, në këtë formulë, funksioni CHOOSE ka dy punë:
- krijon një grup tabelash që është vetëm dy kolona të gjera - kolonat D dhe F
- ai ndryshon të drejtën në rendin e majtë të kolonave në tabelën e tabelës në mënyrë që kolona F të vijë së pari dhe kolona D është e dyta
Krijimi i një tabele të dy tabelave të kolonës
Sintaksa për funksionin CHOOSE është:
= ZGJIDHNI (Index_number, Value1, Value2, ... Value254)
Funksioni CHOOSE normalisht kthen një vlerë nga lista e vlerave (Value1 to Value254) bazuar në numrin e indeksit të futur.
Nëse numri i indeksit është 1, funksioni kthen Value1 nga lista; nëse numri i indeksit është 2, funksioni i kthen Value2 nga lista dhe kështu me radhë.
Duke futur numra të shumtë indeksish; megjithatë, funksioni do të kthejë vlera të shumta në çdo mënyrë që dëshironi. Marrja e zgjedhjes për kthimin e vlerave të shumëfishta bëhet duke krijuar një grup .
Futja e një koleksioni realizohet duke rrethuar numrat e futur me mbërthimet e kapelave ose kllapa. Dy numra janë futur për numrin e indeksit: {1,2} .
Duhet të theksohet se ZGJIDHIMI nuk kufizohet vetëm në krijimin e një tabele me dy kolona. Duke përfshirë një numër shtesë në grup - siç është {1,2,3} - dhe një varg shtesë në argumentin e vlerës, mund të krijohet një tabelë me tre kolona.
Kolona shtesë do t'ju lejojnë të ktheni informacion të ndryshëm me formulën e kërkimit të majtë thjesht duke ndryshuar argumentin e numrit të indeksit të kolonës VLOOKUP në numrin e kolonës që përmban informacionin e dëshiruar.
Ndryshimi i rendit të kolonave me Funksionin ZGJIDH
Në funksionin CHOOSE që përdoret në këtë formulë: ZGJIDH ({1,2}, $ F: $ F, $ D: $ D) , vargu i kolonës F është renditur përpara kolonës D.
Meqë funksioni CHOOSE përcakton tabelën e tabelave të VLOOKUP-it - burimi i të dhënave për atë funksion - ndërrimi i rendit të kolonave në funksionin CHOOSE kalon përgjatë VLOOKUP.
Tani, sa i përket VLOOKUP është i shqetësuar, tabela e tabelës është vetëm dy kolona të gjera me kolonën F në të majtën dhe kolonën D në të djathtë. Meqë kolona F përmban emrin e kompanisë që ne dëshirojmë të kërkojmë, dhe që nga kolona D përmban emrat e pjesëve, VLOOKUP do të jetë në gjendje të kryejë detyrat e zakonshme të kërkimit në gjetjen e të dhënave që ndodhen në të majtë të vlerës së lookup.
Si rezultat, VLOOKUP është në gjendje të përdorë emrin e kompanisë për të gjetur pjesën që furnizojnë.