Duke kombinuar funksionin VLOOKUP të Excel-it me funksionin COLUMN, mund të krijoni një formulë kërkimi që kthen vlera të shumta nga një rresht i vetëm i një baze të dhënash ose tabele të dhënash. Mësoni se si të krijoni një formulë kërkimi që kthen vlera të shumta nga një regjistrim i vetëm i të dhënave.
Udhëzimet në këtë artikull zbatohen për Excel 2019, 2016, 2013, 2010; dhe Excel për Microsoft 365.
Përfundim
Formula e kërkimit kërkon që funksioni COLUMN të vendoset brenda VLOOKUP. Vendosja e një funksioni përfshin futjen e funksionit të dytë si një nga argumentet për funksionin e parë.
Fut të dhënat e tutorialit
Në këtë tutorial, funksioni COLUMN futet si argumenti i numrit të indeksit të kolonës për VLOOKUP. Hapi i fundit në tutorial përfshin kopjimin e formulës së kërkimit në kolona shtesë për të marrë vlera shtesë për pjesën e zgjedhur.
Hapi i parë në këtë tutorial është futja e të dhënave në një fletë pune në Excel. Për të ndjekur hapat në këtë tutorial, futni të dhënat e treguara në imazhin më poshtë në qelizat e mëposhtme:
- Fut gamën kryesore të të dhënave në qelizat D1 deri në G1.
- Fut gamën e dytë në qelizat D4 deri në G10.
Kriteret e kërkimit dhe formula e kërkimit të krijuar në këtë tutorial futen në rreshtin 2 të fletës së punës.
Ky tutorial nuk përfshin formatimin bazë të Excel-it të treguar në imazh, por kjo nuk ndikon në mënyrën se si funksionon formula e kërkimit.
Krijoni një gamë të emërtuar për tabelën e të dhënave
Një varg me emër është një mënyrë e thjeshtë për t'iu referuar një gamë të dhënash në një formulë. Në vend që të shkruani referencat e qelizave për të dhëna, shkruani emrin e diapazonit.
Një avantazh i dytë i përdorimit të një diapazoni të emërtuar është se referencat e qelizave për këtë varg nuk ndryshojnë kurrë edhe kur formula kopjohet në qeliza të tjera në fletën e punës. Emrat e diapazonit janë një alternativë ndaj përdorimit të referencave absolute të qelizave për të parandaluar gabimet gjatë kopjimit të formulave.
Emri i diapazonit nuk përfshin titujt ose emrat e fushave për të dhënat (siç tregohet në rreshtin 4), vetëm të dhënat.
-
Theksoni qelizat D5 deri në G10 në fletën e punës.
Image -
Vendosni kursorin në Kutinë e Emrit që ndodhet sipër kolonës A, shkruani Tabela, më pas shtypni Enter. Qelizat D5 deri në G10 kanë emrin e diapazonit të tabelës.
Image - Emri i diapazonit për argumentin e grupit të tabelës VLOOKUP përdoret më vonë në këtë tutorial.
Hap kutinë e dialogut VLOOKUP
Megjithëse është e mundur të shtypni formulën e kërkimit direkt në një qelizë në një fletë pune, shumë njerëz e kanë të vështirë ta mbajnë sintaksën drejt - veçanërisht për një formulë komplekse si ajo e përdorur në këtë tutorial.
Si alternativë, përdorni kutinë e dialogut Argumentet e Funksionit VLOOKUP. Pothuajse të gjitha funksionet e Excel-it kanë një kuti dialogu ku secili prej argumenteve të funksionit futet në një rresht të veçantë.
-
Zgjidhni qelizën E2 të fletës së punës. Ky është vendi ku do të shfaqen rezultatet e formulës së kërkimit dydimensional.
Image -
Në shirit, shko te skeda Formulat dhe zgjidh Kërkimi & Referenca.
Image -
Zgjidh VLOOKUP për të hapur kutinë e dialogut Function Arguments.
Image - Kutia e dialogut Argumentet e Funksionit është vendi ku futen parametrat e funksionit VLOOKUP.
Fut argumentin e vlerës së kërkimit
Normalisht, vlera e kërkimit përputhet me një fushë të dhënash në kolonën e parë të tabelës së të dhënave. Në këtë shembull, vlera e kërkimit i referohet emrit të pjesës që dëshironi të gjeni informacion. Llojet e lejueshme të të dhënave për vlerën e kërkimit janë të dhënat e tekstit, vlerat logjike, numrat dhe referencat e qelizave.
Referencat absolute të qelizave
Kur formulat kopjohen në Excel, referencat e qelizave ndryshojnë për të pasqyruar vendndodhjen e re. Nëse kjo ndodh, D2, referenca e qelizës për vlerën e kërkimit, ndryshon dhe krijon gabime në qelizat F2 dhe G2.
Referencat absolute të qelizave nuk ndryshojnë kur formulat kopjohen.
Për të parandaluar gabimet, konvertoni referencën e qelizës D2 në një referencë absolute të qelizës. Për të krijuar një referencë absolute të qelizës, shtypni tastin F4. Kjo shton shenja dollari rreth referencës së qelizës si p.sh. $D$2.
-
Në kutinë e dialogut Argumentet e Funksionit, vendoseni kursorin në kutinë e tekstit lookup_value. Më pas, në fletën e punës, zgjidhni qeliza D2 për të shtuar këtë referencë qelize në vlera_lookup. Qeliza D2 është vendi ku do të futet emri i pjesës.
Image -
Pa lëvizur pikën e futjes, shtypni tastin F4 për të kthyer D2 në referencën absolute të qelizës $D$2.
Image - Lëre të hapur kutinë e dialogut të funksionit VLOOKUP për hapin tjetër në tutorial.
Fut argumentin e grupit të tabelës
Një grup tabelash është tabela e të dhënave që kërkon formula e kërkimit për të gjetur informacionin që dëshironi. Grupimi i tabelës duhet të përmbajë të paktën dy kolona të dhënash.
Kollona e parë përmban argumentin e vlerës së kërkimit (i cili u konfigurua në seksionin e mëparshëm), ndërsa kolona e dytë kërkohet nga formula e kërkimit për të gjetur informacionin që specifikoni.
Argumenti i grupit të tabelës duhet të futet ose si një diapazon që përmban referencat e qelizave për tabelën e të dhënave ose si një emër vargu.
Për të shtuar tabelën e të dhënave në funksionin VLOOKUP, vendoseni kursorin në kutinë e tekstit table_array në kutinë e dialogut dhe shkruani Tabelapër të futur emrin e intervalit për këtë argument.
Foleja e funksionit COLUMN
Normalisht, VLOOKUP kthen të dhëna vetëm nga një kolonë e një tabele të dhënash. Kjo kolonë vendoset nga argumenti i numrit të indeksit të kolonës. Në këtë shembull, megjithatë, ka tre kolona dhe numri i indeksit të kolonës duhet të ndryshohet pa modifikuar formulën e kërkimit. Për ta arritur këtë, futni funksionin COLUMN brenda funksionit VLOOKUP si argumentin Col_index_num.
Kur futen funksione, Excel nuk e hap kutinë e dialogut të funksionit të dytë për të futur argumentet e tij. Funksioni COLUMN duhet të futet manualisht. Funksioni COLUMN ka vetëm një argument, argumentin e referencës, i cili është një referencë qelize.
Funksioni COLUMN kthen numrin e kolonës së dhënë si argument Referencë. Shndërron shkronjën e kolonës në numër.
Për të gjetur çmimin e një artikulli, përdorni të dhënat në kolonën 2 të tabelës së të dhënave. Ky shembull përdor kolonën B si referencë për të futur 2 në argumentin Col_index_num.
-
Në kutinë e dialogut Function Arguments, vendoseni kursorin në kutinë e tekstit Col_index_num dhe shkruani COLUMN(. (Sigurohuni që të përfshini kllapin e hapur të rrumbullakët.)
Image -
Në fletën e punës, zgjidhni qeliza B1 për të futur atë referencë qelize si argument Referencë.
Image - Shkruani një kllapë mbyllëse të rrumbullakët për të përfunduar funksionin COLUMN.
Hyni Argumentin e Kërkimit të Range VLOOKUP
Argumenti Range_lookup i VLOOKUP është një vlerë logjike (TRUE ose FALSE) që tregon nëse VLOOKUP duhet të gjejë një përputhje të saktë ose të përafërt me vlerën e kërkimit.
- E VËRTETË ose e hequr: VLOOKUP kthen një përputhje të ngushtë me vlerën e kërkimit. Nëse nuk gjendet një përputhje e saktë, VLOOKUP kthen vlerën tjetër më të madhe. Të dhënat në kolonën e parë të Table_array duhet të renditen në rend rritës.
- FALSE: VLOOKUP përdor një përputhje të saktë me vlerën e kërkimit. Nëse ka dy ose më shumë vlera në kolonën e parë të Table_array që përputhen me vlerën e kërkimit, përdoret vlera e parë e gjetur. Nëse nuk gjendet një përputhje e saktë, kthehet një gabim N/A.
Në këtë tutorial, do të kërkohet informacion specifik për një artikull të veçantë harduer, kështu që kërkimi_Range është vendosur në FALSE.
Në kutinë e dialogut Funksioni Argumentet, vendoseni kursorin në kutinë e tekstit Range_lookup dhe shkruani False për t'i thënë VLOOKUP të kthejë një përputhje të saktë për të dhënat.
Zgjidhni OK për të plotësuar formulën e kërkimit dhe për të mbyllur kutinë e dialogut. Qeliza E2 do të përmbajë një gabim N/A sepse kriteret e kërkimit nuk janë futur në qelizën D2. Ky gabim është i përkohshëm. Do të korrigjohet kur kriteret e kërkimit të shtohen në hapin e fundit të këtij udhëzuesi.
Kopjo formulën e kërkimit dhe fut kriteret
Formula e kërkimit merr të dhëna nga kolona të shumta të tabelës së të dhënave në të njëjtën kohë. Për ta bërë këtë, formula e kërkimit duhet të qëndrojë në të gjitha fushat nga të cilat dëshironi informacion.
Për të marrë të dhëna nga kolonat 2, 3 dhe 4 të tabelës së të dhënave (çmimi, numri i pjesës dhe emri i furnizuesit), vendosni një emër të pjesshëm si vlerën e kërkimit.
Meqenëse të dhënat janë paraqitur në një model të rregullt në fletën e punës, kopjoni formulën e kërkimit në qeliza E2 në qelizat F2 dhe G2 Ndërsa formula kopjohet, Excel përditëson referencën relative të qelizave në funksionin COLUMN (qeliza B1) për të pasqyruar vendndodhjen e re të formulës. Excel nuk e ndryshon referencën absolute të qelizës (si p.sh. $D$2) dhe diapazonin e emërtuar (Tabela) pasi formula kopjohet.
Ka më shumë se një mënyrë për të kopjuar të dhënat në Excel, por mënyra më e lehtë është të përdorni dorezën e mbushjes.
-
Zgjidh qelizën E2, ku ndodhet formula e kërkimit, për ta bërë atë qelizën aktive.
Image -
Zvarritni dorezën e mbushjes te qeliza G2. Qelizat F2 dhe G2 shfaqin gabimin N/A që është i pranishëm në qelizën E2.
Image -
Për të përdorur formulat e kërkimit për të marrë informacion nga tabela e të dhënave, në fletën e punës zgjidhni qeliza D2, shkruani Widget dhe shtypni Enter.
Image Informacioni i mëposhtëm shfaqet në qelizat E2 deri në G2.
- E2: 14,76 $ - çmimi i një widget
- F2: PN-98769 - numri i pjesës për një miniaplikacion
- G2: Widgets Inc. - emri i furnizuesit për miniaplikacionet
-
Për të testuar formulën e grupit VLOOKUP, shkruani emrin e pjesëve të tjera në qelizën D2 dhe vëzhgoni rezultatet në qelizat E2 deri në G2.
Image - Çdo qelizë që përmban formulën e kërkimit përmban një pjesë të ndryshme të të dhënave në lidhje me artikullin e harduerit që keni kërkuar.
Funksioni VLOOKUP me funksione të ndërlidhura si COLUMN ofron një metodë të fuqishme për të kërkuar të dhëna brenda një tabele, duke përdorur të dhëna të tjera si referencë kërkimi.
