Mësoni të zotëroni funksione si LOOKUP dhe INDEX në Excel për kërkime të fuqishme.

  • Funksionet e kërkimit në Excel ju lejojnë të gjeni shpejt dhe pa gabime të dhëna në tabela të mëdha.
  • INDEX dhe MATCH, të kombinuara, kapërcejnë kufizimet e VLOOKUP dhe HLOOKUP, duke lejuar kërkime dinamike në çdo drejtim.
  • Përdorimi i saktë i këtyre veçorive përmirëson efikasitetin, parandalon gabimet e zakonshme dhe e bën më të lehtë automatizimin e detyrave të përsëritura.
Pablo

Minuta 9

formulat bazë të Excel-it

Zotëroni funksionet e kërkim në Excel Excel është një nga ato superfuqi që bën gjithë ndryshimin kur dëshiron të punosh më shpejt, të shmangësh gabimet dhe të menaxhosh mirë sasi të mëdha të dhënash. Me siguri e ke gjetur veten me një spreadsheet të madh dhe ke menduar: si t'i gjej saktësisht të dhënat që po kërkoj pa u çmendur? Epo, për këtë shërbejnë funksione si LOOKUP, VLOOKUP, HLOOKUP, INDEX dhe MATCH - shpëtimtarë të vërtetë për këdo që përdor Excel rregullisht.

Në këtë artikull do të mësoni në detaje se si të përfitoni nga KËRKO, INDEKSI dhe formula të tjera kërkimi në Excel. Do t'jua shpjegojmë hap pas hapi në një mënyrë të thjeshtë, me shembuj që mund t'i zbatoni menjëherë në punën tuaj të përditshme. Gjithashtu do t'ju tregojmë se si t'i kombinoni këto funksione për të kryer kërkime komplekse, do t'ju japim këshilla për të fituar edhe më shumë fuqi dhe për të shmangur gabimet e zakonshme gjatë përdorimit të tyre.

Pse funksionet e kërkimit në Excel janë kaq të rëndësishme?

Në Excel, kur menaxhoni tabela të dhënash, bëhet thelbësore të keni mjete që ju lejojnë të gjeni informacione specifike. Imagjinoni që keni një listë punonjësish me numrat e tyre të zyrës dhe duhet të dini në cilën dhomë punon John Smith pa pasur nevojë të shikoni secilin prej tyre. Funksionet e kërkimit ato automatizohen këtë proces, duke ju kursyer shumë kohë dhe gabime manuale.

LOOKUP, VLOOKUP (që do të thotë "kërkim vertikal"), HLOOKUP (kërkim horizontal), INDEX dhe MATCH janë formulat klasike për këtë lloj detyre. Edhe pse secila ka veçoritë e veta, të gjitha kanë të përbashkët qëllimin e gjetjes efikase të të dhënave.

Funksioni VLOOKUP Mbetet më popullorja për kërkimin vertikal brenda tabelave, ndërsa INDEKSI dhe PËRPUTHJA Ato lejojnë kërkime shumë më fleksibile dhe të fuqishme, veçanërisht në tabela më komplekse ose të strukturuara dobët. Njohja e avantazheve të secilit funksion ju jep lirinë për të zgjedhur gjithmonë mjetin më të mirë për secilën situatë.

Dallimet kryesore midis VLOOKUP, HLOOKUP, INDEX dhe MATCH

Para se të futemi në detaje, le të sqarojmë ndryshimet dhe kur është më mirë të përdoret secila veçori:

  • VLOOKUPKërkon për një vlerë në kolonën e parë të një tabele dhe kthen rezultatin nga një kolonë specifike në të njëjtin rresht. Kërkon vetëm nga e majta në të djathtë.
  • HLOOKUPBën të njëjtën gjë si VLOOKUP, por kërkon në rreshtin e parë dhe kthen rezultate nga rreshtat e tjerë në të njëjtën kolonë. Kërkon nga lart poshtë.
  • INDEKSIKthen vlerën e një qelize të dhënë pozicionin e saj (rresht dhe kolonë) brenda një matrice ose diapazoni. Mund të kërkojë si vertikalisht ashtu edhe horizontalisht, duke e bërë më fleksibël.
  • KOINCIDKthen pozicionin relativ të një vlere brenda një diapazoni, ideal për t'u kombinuar me INDEX.

Kombinimi i INDEX dhe MATCH Ai i kapërcen shumë kufizimet e VLOOKUP dhe HLOOKUP, pasi lejon kërkime si horizontale ashtu edhe vertikale, mund ta drejtojë kërkimin në çdo drejtim dhe nuk kërkon që vlera e kërkuar të jetë në kolonën ose rreshtin e parë.

Zbërthimi i VLOOKUP: funksioni klasik i kërkimit vertikal

VLOOKUP është funksioni më i përdorur për kërkimet vertikale në Excel. Sintaksa e tij është e thjeshtë:

=VLOOKUP(vlera_e_kërkimit, diapazoni_i_tabelës, indeksi_i_kolonës,)

Le të shohim se çfarë do të thotë secili argument:

  • kërkim_vleraTë dhënat që duam të gjejmë. Këto mund të jenë tekst, një numër ose një referencë qelize.
  • varg_tabeleZona e qelizave ku Excel do të kërkojë vlerën dhe nga ku do ta kthejë rezultatin.
  • indeksi_i_kolonësNumri i kolonës brenda diapazonit_tabelë nga i cili do të nxirret vlera.
  • përafërsisht_përputhjeOpsionale. Nëse e vendosni në TRUE ose e lini bosh, Excel do të kërkojë përputhje të përafërta; nëse e vendosni në FALSE, do të kërkojë vetëm përputhje të sakta.

Për shembull:

=VLOOKUP(B2, C2:E7, 3, E VËRTETË)

Në këtë rast, Excel do të kërkojë vlerën në qelizën B2 në kolonën e parë të diapazonit C2:E7 dhe do të kthejë vlerën përkatëse nga kolona e tretë e atij diapazoni. Nëse nuk ka përputhje të saktë dhe është vendosur TRUE, ai do të kthejë vlerën më të afërt si parazgjedhje.

Mashtrim: Nëse duhet të kërkoni në rreshta në vend të kolonave, përdorni HLOOKUP, e cila funksionon saktësisht njësoj, por horizontalisht. Për të mësuar më shumë se si të përfitoni nga këto veçori, mund të shikoni faqen tonë Udhëzues i plotë për VLOOKUP në Excel.

Shembuj të qartë të VLOOKUP dhe HLOOKUP

Për të sqaruar çdo konfuzion, do të shqyrtojmë shembuj praktikë të secilit funksion (mbani mend se mund të merrni fragmente të tabelave reale dhe t'i ngjisni formulat për t'i testuar ato):

Shembull bazë i VLOOKUP-it

Supozoni se keni një listë dendësish dhe viskozitetesh. Ju doni të gjeni vlerën e viskozitetit që korrespondon me një dendësi specifike prej 1:

  • =VLOOKUP(1, A2:C10, 2) – Gjen vlerën 1 në kolonën A dhe kthen vlerën nga kolona B në të njëjtin rresht (në këtë rast, 2,17).
  • =VLOOKUP(1, A2:C10, 3, E VËRTETË) – Kërko vlerën 1 në kolonën A dhe kthe vlerën nga kolona C në të njëjtin rresht (100).

Shembull bazë i HLOOKUP

  • =HLOOKUP("Akset", A1:C4, 2, TRUE) – Kërko për “Axes” në rreshtin 1 dhe kthe vlerën nga rreshti 2 në të njëjtën kolonë.
  • =HLOOKUP("Kushineta", A1:C4, 3, FALSE) – Kërko për “Kushinetat” në rreshtin 1 dhe kthe vlerën nga rreshti 3 në kolonën B.

E shihni, këto janë formula të lehta për t’u mbajtur mend! Por çfarë ndodh nëse kolona që dëshironi të kërkoni nuk është në të majtë, ose nëse keni nevojë për një kërkim më të gjithanshëm?

INDEKSI dhe PËRPUTHJA: Çifti perfekt për kërkime të avancuara

Kjo është ajo ku INDEKSI y KOINCID mund t'ju japë superfuqi. Kur kufizimet e VLOOKUP ju lënë të bllokuar, veçanërisht nëse duhet të kërkoni majtas, kombinoni INDEKSI y KOINCID hap një gamë të gjerë mundësish për ju.

Le të fillojmë me një shpjegim të qartë të secilit funksion:

Si funksionon funksioni INDEX?

INDEKSI ju lejon të nxirrni vlerën e një qelize saktësisht aty ku dëshironi, duke specifikuar rreshtin dhe kolonën brenda një diapazoni ose matrice. Sintaksa e saj është:

=INDEX(matricë, numri_i_rreshtit,)

Për shembull, nëse keni një tabelë shitjesh dhe doni të dini se cila vlerë është në rreshtin 4 dhe kolonën 2, thjesht duhet të vendosni:

=INDEX(A1:C10, 4, 2)

Kjo kthen vlerën në qelizën B4. Një detaj i rëndësishëm: INDEKSI Mund të kërkoni vertikalisht ose horizontalisht, dhe është shumë i gjithanshëm për formulat dinamike.

Për çfarë përdoret COINCIDIR?

COINCIDIR, nga ana tjetër, tregon pozicioni relativ i një vlere brenda një diapazoniDomethënë, ju tregon se në cilin rresht ose kolonë ndodhen të dhënat që po kërkoni. Sintaksa e saj është:

=MATCH(vlera_e_kërkimit, vargu_i_kërkimit,)

  • kërkim_vlera: Të dhënat që dëshironi të gjeni.
  • grup_kërkuesishDiapazoni ku e kërkoni.
  • lloji_i_përputhjesZakonisht 0 për përputhje të saktë (rekomandohet për shumicën e rasteve).

Për shembull, për të gjetur pozicionin e "Gjonit" në një listë:

=MATCH(«Gjoni», A2:A20, 0)

Kjo kthen pozicionin (numrin e rreshtit brenda diapazonit) ku është "John".

Kombinimi i INDEX dhe MATCH për kërkime të fuqishme

Magjia e vërtetë është në përdor INDEX dhe MATCH së bashkuNë këtë mënyrë, mund të gjeni vlerën e kryqëzimit të një rreshti dhe kolone që ndryshon dinamikisht me kriteret e kërkimit tuaj.

Për shembull, keni një tabelë me emrat e shitësve në rreshta dhe vitet në kolona, ​​dhe doni të dini se sa faturoi "Laura" në vitin 2011. Emrat nuk janë në kolonën e parë, kështu që VLOOKUP Nuk funksionon, por mund të përdorni:

=INDEX(diapazoni_i_shitjeve, KOINCID("Laura", diapazoni_i_emrit, 0), KOINCID(2011, vite_diapazoni, 0))

Në këtë mënyrë, nëse ndryshoni emrin ose vitin, formula automatikisht kthen të dhënat e sakta. Çelësi është:

  1. MATCH lokalizon pozicionin e "Laura" në vargun e emrave.
  2. MATCH lokalizon pozicionin e vitit në matricën e kokës.
  3. INDEX kryqëzon ato pozicione për të aksesuar qelizën e saktë.

Kjo lejon pyetje dinamike, krahasim, filtrim dhe referencim të të dhënave pa kufizime.

Funksioni INDEX në formë referimi

Përveç kësaj, INDEKSI Ka një variant të avancuar në formën e një reference, e cila mund të kthejë zona të shumta njëkohësisht. Në këtë modalitet, Mund të punoni me tabela të shumta dhe të kërkoni në të gjitha ato me një formulë të vetme..

Sintaksa e saj është:

=INDEX((zona1; zona2), numri_i_rreshtit, numri_i_kolonës, numri_i_zonës)

Për shembull, nëse i keni të dyja zonat (njëra për ngjyrat dhe tjetra për frutat), mund ta përdorni këtë metodë për t'i kërkuar të dyja bazuar në kriteret tuaja.

mbaj mendKur vendosni zona të shumëfishta, përdorni kllapa të dyfishta për të shmangur gabimet.

Shembull praktik i kërkimit me INDEX dhe MATCH

Supozoni se menaxhoni një tabelë faturash me numër, qytet dhe datë, dhe doni të merrni faturën e parë për secilin qytet me numrin dhe datën e tij. Formula do të ishte:

= "Qyteti: " & INDEX(diapazoni_i_faturës, KOINCID("Qyteti", diapazoni_i_qyteteve, 0), 1) & «, Data: » & TEXT(INDEX(diapazoni_i_faturave, KOINCID("Qyteti", diapazoni_i_qyteteve, 0), 3), "d/m/vv")

Në këtë mënyrë, për secilin qytet, ju merrni të dhënat e nevojshme në një qelizë të vetme, të formatuar në mënyrë të përshtatshme.

Këshilla thelbësore për të shmangur gabimet në funksionet e kërkimit

  • Gjithmonë përdorni 0 en KOINCID për të siguruar përputhje të sakta.
  • En VLOOKUP, nëse modifikoni strukturën, indeksi_i_kolonës mund të dështojë; prandaj, INDEKSI + PËRPUTHJA është më rezistente.
  • Vendosni referencat me F4 kur kopjohen formulat, në mënyrë që diapazonet të mos ndryshojnë aksidentalisht.
  • Kontrolloni që vlerat e kërkuara ekzistojnë, diapazonet janë të sakta dhe argumentet e funksionit janë të përcaktuara mirë për të shmangur gabime të tilla si # N / A o # REF!.

Avantazhet e INDEX+MATCH mbi VLOOKUP dhe HLOOKUP

Edhe pse VLOOKUP përdoret gjerësisht për thjeshtësinë e tij, INDEKSI+PËRPUTHJE ka disa përparësi:

  • Ju lejon të kërkoni në çdo drejtim: nga e majta në të djathtë dhe anasjelltas.
  • Reziston më mirë ndaj ndryshimeve në tabelëNëse futni kolona, ​​formulat mbeten të sakta.
  • Lejon kërkime dy-dimensionale: i fiksuar në rreshta dhe kolona, ​​ideal për të dhëna komplekse ose dinamike.
  • Funksionon me diapazone ose zona të shumëfishta: i dobishëm në fletëllogaritëset e përparuara.

Ushtrime praktike për të konsoliduar funksionet e kërkimit në Excel

Praktikoni këto funksione me të dhënat tuaja ose me shembuj të gatshëm. Ja disa ide:

  1. Gjeni çmimin e një produktiMe një listë produktesh dhe çmimesh, përdorni VLOOKUP ose për të marrë koston duke futur emrin.
  2. Gjeni vendndodhjen e një punonjësiNë një tabelë me emra dhe zyra, gjeni në cilën zyrë punon një punonjës, pavarësisht nga vendosja e kolonës.
  3. Nxirrni datën e shitjes së parë në një qytetDuke kombinuar INDEX dhe MATCH, ju merrni të dhënat e dëshiruara shpejt dhe me efikasitet.
searchV excel
Artikulli i lidhur:
Një udhëzues i plotë për funksionin VLOOKUP në Excel: si ta përdorni, shembuj dhe këshilla të avancuara.

Funksione kryesore si LOOKUP, VLOOKUP, HLOOKUP, INDEX dhe MATCH në Excel Ju bën më efikas dhe ju lejon të zgjidhni probleme komplekse, të kryeni analiza të avancuara dhe të automatizoni proceset. Praktika e vazhdueshme do t'ju bëjë ekspert, duke ju mundësuar të punoni më saktë dhe shpejt në fletëllogaritjet tuaja.