Si të përdorni funksionin XLOOKUP në Excel 2016

Excel 2016 për abonentët e Office 365 në Windows dhe Mac tani mbështet një funksion të ri XLOOKUP, i shpallur si një zëvendësim shumë më i thjeshtë dhe më i gjithanshëm për funksionin e kërkimit vertikal shumë të popullarizuar (megjithatë të keqdashësuar), VLOOKUP (nuk e di se çfarë është X në XLOOKUP qëndron për; e gjerë, ndoshta?).

Për ata prej jush që nuk janë njohur ende me VLOOKUP (i konsideruar si funksioni i tretë më i përdorur menjëherë pas SUM dhe AVERAGE), ky funksion kërkon vertikalisht sipas rreshtit në kolonën më të majtë të një tabele kërkimi të caktuar nga lart poshtë derisa të gjejë një vlerë në një kolona e kërkimit e caktuar nga një numër i kompensuar që përputhet ose tejkalon atë që kërkoni. Megjithëse është jashtëzakonisht i dobishëm për gjetjen e artikujve të veçantë në një listë të gjatë ose kolonë të një tabele të dhënash në fletën tuaj të punës, funksioni VLOOKUP ka disa kufizime që nuk ndahen nga ky funksion i ri i kërkimit, si XLOOKUP:

• Parazgjedhjet për të gjetur përputhje të sakta për vlerën tuaj të kërkimit në diapazonin e kërkimit
• Mund të kërkojë si vertikalisht (sipas rreshtit) ashtu edhe horizontalisht (sipas kolonës) në një tabelë, duke zëvendësuar kështu nevojën për të përdorur funksionin HLOOKUP kur kërkoni horizontalisht sipas kolonës
• Mund të kërkojë majtas ose djathtas në mënyrë që diapazoni i kërkimit në tabelën tuaj të kërkimit të mos duhet të vendoset në një kolonë në të majtë të asaj të caktuar si diapazoni i kthimit në mënyrë që funksioni të funksionojë
• Kur përdoret standardi i saktë i përputhjes, funksionon edhe kur vlerat në diapazonin e kërkimit nuk janë renditur në një rend të caktuar
• Mund të kërkojë nga rreshti i poshtëm në krye në diapazonin e grupit të kërkimit, duke përdorur një argument opsional të modalitetit të kërkimit

Funksioni XLOOKUP ka pesë argumente të mundshme, tre të parët prej të cilëve kërkohen dhe dy të fundit janë opsionalë, duke përdorur sintaksën e mëposhtme:

XLOOKUP ( lookup_value , lookup_array , return_array [ match_mode ], [ search_mode ])

Argumenti i kërkuar lookup_value përcakton vlerën ose artikullin për të cilin po kërkoni. Argumenti i kërkuar i grupit look_up përcakton gamën e qelizave që do të kërkohen për këtë vlerë kërkimi dhe argumenti return_array përcakton gamën e qelizave që përmbajnë vlerën që dëshironi të kthehet kur Excel gjen një përputhje të saktë.

* Mbani parasysh kur caktoni argumentet e grupit lookup_array dhe kthimit në funksionin tuaj XLOOKUP, të dy vargjet duhet të jenë me gjatësi të barabartë, përndryshe Excel do të kthejë #VALUE! gabim në formulën tuaj. Kjo është edhe më shumë arsyeja që ju të përdorni emrat e diapazonit ose emrat e kolonave të një tabele të caktuar të dhënash kur përcaktoni këto argumente në vend që t'i vini në dukje ose të shkruani referencat e tyre të qelizave .

Argumenti opsional match_mode mund të përmbajë cilëndo nga katër vlerat e mëposhtme:

• 0 për një përputhje të saktë (e paracaktuar, njësoj si kur nuk është caktuar asnjë argument match_mode )
• -1 për përputhjen e saktë ose vlerën tjetër më të vogël
• 1 për përputhjen e saktë ose vlerën tjetër më të madhe
• 2 për përputhje të pjesshme duke përdorur karaktere të shkreta të bashkuara me referencën e qelizës në argumentin lookup_value

Argumenti opsional search_mode mund të përmbajë cilëndo nga katër vlerat e mëposhtme:

• 1 për të kërkuar nga i pari tek i fundit, domethënë nga lart poshtë (e paracaktuar, njësoj si kur nuk është caktuar asnjë argument search_mode )
• -1 për të kërkuar nga fundi në të parë, domethënë nga poshtë lart
• 2 për një kërkim binar në rend rritës
• -2 për kërkimin binar në rend zbritës

Mënyra më e mirë për të kuptuar fuqinë dhe shkathtësinë e funksionit të ri XLOOKUP është ta shihni atë në veprim në një fletë pune në Excel. Në figurën e mëposhtme, unë kam një fletë pune me një tabelë të thjeshtë të të dhënave të shitjeve 2019 të rregulluar sipas vendit. Për të përdorur XLOOKUP për të kthyer shitjet totale nga kjo tabelë në qelizën E4 bazuar në shtetin që futni në qelizën D4 të fletës së punës, ndërmerrni këto hapa:

Vendosni kursorin e qelizës në qelizën E4 të fletës së punës

Klikoni opsionin Kërko & Referenca në skedën Formulat e ndjekur nga XLOOKUP afër fundit të menysë rënëse për të hapur kutinë e dialogut të Argumenteve të Funksionit.

Klikoni në qelizën D4 në fletën e punës për të futur referencën e qelizës së saj në kutinë e tekstit të argumentit Lookup_value.

Shtypni Tab për të zgjedhur kutinë e tekstit të argumentit Lookup_array, më pas klikoni në qelizën A4 dhe mbani shtypur Shift ndërsa shtypni shigjetën Ctrl-poshtë për të zgjedhur A4:A8 si diapazonin për të kërkuar (sepse diapazoni A3:B8 është përcaktuar si një tabelë e të dhënave në Excel, Tabela1[Shteti] shfaqet në kutinë e tekstit në vend të diapazonit A4:A8).

Shtypni Tab për të zgjedhur kutinë e tekstit të argumentit Return_array, më pas klikoni në qelizën B4 dhe mbani shtypur Shift ndërsa shtypni shigjetën Ctrl-poshtë për të zgjedhur B4:B8 si diapazonin që përmban vlerat që do të kthehen bazuar në rezultatet e kërkimit (që duket si Tabela 1[Shitjet totale] në kutinë e tekstit).

Klikoni OK për të futur formulën XLOOKUP në qelizën E4.

Krijimi i një formule me XLOOKUP në qelizën E4 që kthen shitjet bazuar në shtetin e futur në qelizën D4.

Excel fut formulën XLOOKUP në qelizën E4 të fletës së punës dhe kthen 4900 si rezultat, sepse Kosta Rika aktualisht është futur në qelizën e kërkimit D4 dhe siç mund ta shihni në tabelën e shitjeve 2019, kjo është me të vërtetë shitjet totale të bëra për këtë vend.

Për shkak se XLOOKUP funksionon nga e djathta në të majtë po aq mirë sa nga e majta në të djathtë, ju mund ta përdorni këtë funksion po aq mirë për të kthyer vendin nga kjo tabelë shitjesh bazuar në një shifër të veçantë shitjesh. Figura e mëposhtme ju tregon se si e bëni këtë. Këtë herë, ju krijoni formulën XLOOKUP në qelizën D4 dhe caktoni vlerën e futur në qelizën E4 (11,000, në këtë rast) si argumenti lookup_value.

Përveç kësaj, ju futni -1 si argumentin match_mode për të anashkaluar parazgjedhjen e saktë të përputhjes së funksionit, në mënyrë që Excel ta kthejë vendin me një përputhje të saktë me vlerën e shitjeve të futur në qelizën e kërkimit E4 ose atë me shitjet totale të ardhshme më të ulëta (Meksikë me 10,000 dollarë në këtë rast pasi nuk ka asnjë vend në këtë tabelë me 11,000 dollarë të shitjeve totale). Pa caktuar një argument përputhje_mode për këtë formulë, Excel do të kthente #NA si rezultat, sepse nuk ka përputhje të saktë me 11,000 dollarë në këtë tabelë shitjesh.

Krijimi i një formule me XLOOKUP në qelizën D4 që kthen vendin bazuar në shitjet e futura në qelizën E4

Për shkak se funksioni XLOOKUP është po aq i rehatshëm kur kërkon horizontalisht sipas kolonës ashtu siç është duke kërkuar vertikalisht sipas rreshtit, mund ta përdorni për të krijuar një formulë që kryen një kërkim të dyanshëm (duke zëvendësuar nevojën për të krijuar një formulë që kombinon funksionet INDEX dhe MATCH si ne te shkuaren). Figura e mëposhtme, që përmban tabelën e orarit të prodhimit 2019 për numrat e pjesëve, AB-100 deri në AB-103 për muajt prill deri në dhjetor, ju tregon se si bëhet kjo.

Krijimi i një formule me funksione të ndërlidhura XLOOKUP për të kthyer numrin e njësive të prodhuara për një pjesë në një muaj të caktuar

Në qelizën B12, krijova formulën e mëposhtme:

=XLOOKUP(kontrolli i_pjesës,$A$3:$A$6,XLOOKUP(kërkimi_data,$B$2:$J$2,$B$3:$J$6))

Kjo formulë fillon duke përcaktuar një funksion XLOOKUP që kërkon vertikalisht sipas rreshtit për një përputhje të saktë me hyrjen e pjesës së bërë në qelizën e quajtur part_lookup (qeliza B10, në këtë rast) në diapazonin e qelizave $A$3:$A$6 të tabelës së prodhimit . Vini re, megjithatë, se argumenti return_array për këtë funksion origjinal LOOKUP është në vetvete një funksion i dytë XLOOKUP.

Ky funksion i dytë XLOOKUP i ndërlidhur kërkon horizontalisht sipas kolonës vargun e qelizave $B$2:$J$2 për një përputhje të saktë me hyrjen e datës së bërë në qelizën e quajtur date_lookup (qeliza B11, në këtë rast). Argumenti return_array për këtë funksion të dytë XLOOKUP të mbivendosur është $B$3:$J$6, diapazoni i qelizave të të gjitha vlerave të prodhimit në tabelë.

Mënyra se si funksionon kjo formulë është që Excel fillimisht llogarit rezultatin e funksionit të dytë, të mbivendosur XLOOKUP duke kryer një kërkim horizontal që, në këtë rast, kthen grupin në diapazonin e qelizave D3: D6 të kolonës Jun-19 (me vlerat: 438, 153, 306 dhe 779) si rezultat i tij. Ky rezultat, nga ana tjetër, bëhet argumenti return_array për funksionin origjinal XLOOKUP që kryen një kërkim vertikal sipas rreshtit për një përputhje të saktë me hyrjen e numrit të pjesës të bërë në qelizën B11 (e quajtur part_lookup). Për shkak se, në këtë shembull, kjo qelizë part_lookup përmban AB-102, formula kthen vetëm vlerën e prodhimit Jun-19, 306, nga rezultati i funksionit të dytë, të radhës XLOOKUP.

Ja ku e keni! Një vështrim i parë në XLOOKUP, një funksion i ri i kërkimit i fuqishëm, i gjithanshëm dhe mjaft i lehtë për t'u përdorur, i cili jo vetëm që mund të bëjë kërkimet me një vlerë të vetme të kryera nga funksionet VLOOKUP dhe HLOOKUP, por edhe kërkimet e vlerave të dyanshme të kryera duke kombinuar Funksionet INDEX dhe MATCH gjithashtu.

* Fatkeqësisht, funksioni XLOOKUP nuk është i pajtueshëm me versionet e mëparshme të Microsoft Excel që mbështesin vetëm funksionet VLOOKUP dhe HLOOKUP ose i pajtueshëm me versionet aktuale që nuk e përfshijnë ende atë si një nga funksionet e tyre të kërkimit, si Excel 2019 dhe Excel Online . Kjo do të thotë që nëse ndani një libër pune që përmban formula XLOOKUP me bashkëpunëtorët ose klientët që përdorin një version të Excel që nuk përfshin këtë funksion të ri kërkimi, të gjitha këto formula do të kthejnë #NAME? vlerat e gabimit kur hapin fletën e tij të punës.