01 nga 03
Gjeni pikat e përafërta me të dhënat me VLOOKUP të Excel
Si punon Funksioni VLOOKUP
Funksioni VLOOKUP i Excel, i cili qëndron për lookup vertikal , mund të përdoret për të kërkuar informata specifike të vendosura në një tabelë të të dhënave ose bazës së të dhënave.
VLOOKUP zakonisht kthen një fushë të vetme të të dhënave si rezultat i saj. Si e bën këtë është:
- Ju jepni një emër ose lookup_value që tregon VLOOKUP në cilën rresht ose rekord të tabelës së të dhënave për të kërkuar të dhënat e dëshiruara
- Ju furnizoni numrin e kolonës - të njohur si col_index_num - të të dhënave që kërkoni
- Funksioni shikon lookup_value në kolonën e parë të tabelës së të dhënave
- VLOOKUP pastaj lokalizon dhe kthen informacionin që kërkoni nga një fushë tjetër e të njëjtit rekord duke përdorur numrin e kolonës së dhënë
Renditja e të dhënave së pari
Megjithëse nuk kërkohet gjithmonë, zakonisht është më së miri për të renditur së pari gamën e të dhënave që VLOOKUP po kërkon në rendin ngjitës duke përdorur kolonën e parë të vargut për çelësin e rendit.
Nëse të dhënat nuk janë të renditura, VLOOKUP mund të kthejë një rezultat të pasaktë.
Sintaksa dhe Argumentet e Funksionit VLOOKUP
Sintaksa e një funksioni i referohet paraqitjes së funksionit dhe përfshin emrin, kllapa dhe argumentet e funksionit .
Sintaksa për funksionin VLOOKUP është:
= VLOOKUP (lookup_value, table_array, col_index_num, range_lookup)
lookup _value - (kërkohet) vlera për të kërkuar - siç është sasia e shitur në imazhin e mësipërm
table_array - (e nevojshme) Kjo është tabela e të dhënave që VLOOKUP kërkon për të gjetur informacionin që ju po pasoni .
- Tabela duhet të përmbajë të paktën dy kolona të të dhënave
- Kolona e parë zakonisht përmban lookup_value
col_index_num - (kërkohet) numri i kolonës së vlerës që doni të gjeni.
- Numërimi fillon me kolonën search_key si kolonë 1
- Nëse col_index_num është vendosur në një numër më të madh se numri i kolonave të zgjedhur në argumentin table_array a #REF! gabimi kthehet nga funksioni
range_lookup - (opsional) tregon nëse vargu është klasifikuar sipas rendit ngjitës.
- Të dhënat në kolonën e parë përdoren si çelësi i rendit
- Vlera Boolean - TRUE ose FALSE janë vlerat e vetme të pranueshme
- Nëse mungon, vlera është vendosur në TRUE sipas parazgjedhjes
- Nëse vendoset në TRUE ose mungon dhe kolona e parë e diapazonit nuk renditet sipas renditjes ngjitëse, mund të ndodhë një rezultat i pasaktë
- Nëse vendoset në TRUE ose mungon dhe një ndeshje e saktë për vlerën e kërkimit nuk është gjetur, ndeshja më e afërt që është më e vogël në madhësi ose vlerë përdoret si çelësi i kërkimit
- Nëse vendoset në FALSE, VLOOKUP pranon vetëm një ndeshje të saktë për vlerën e kërkimit . Nëse ka vlera të shumëfishta përputhëse, vlera e parë e përputhjes kthehet
- Nëse vendoset në FALSE dhe nuk gjendet asnjë vlerë e përputhshme për search_key , një gabim #N / A kthehet nga funksioni
Shembull: Gjeni normën e zbritjes për sasinë e blerë
Shembulli në imazhin e mësipërm përdor funksionin VLOOKUP për të gjetur normën e skontimit që ndryshon varësisht nga sasia e artikujve të blerë.
Shembulli tregon se zbritja për blerjen e 19 artikujve është 2%. Kjo është për shkak se kolona Sasia përmban shkallë vlerash. Si rezultat, VLOOKUP nuk mund të gjejë një ndeshje të saktë. Në vend të kësaj, duhet të gjendet një ndeshje e përafërt në mënyrë që të kthehet norma korrekte e skontimit.
Për të gjetur ndeshje të përafërta:
- llojin e të dhënave në tabelën e tabelës në rendin në ngjitje;
- vendos argumentin range_lookup në TRUE
Në shembull, formula e mëposhtme që përmban funksionin VLOOKUP përdoret për të gjetur zbritjen për sasitë e mallrave të blera.
= VLOOKUP (C2, $ C $ 5: $ D $ 8,2, e vërtetë)
Edhe pse kjo formulë mund të shtypet vetëm në një qelizë pune, një tjetër opsion, siç përdoret me hapat e shënuar më poshtë, është të përdorësh kutinë e dialogut të funksionit për të futur argumentet e tij.
- Përdorimi i kutisë së dialogut shpesh e bën më të lehtë për të futur në mënyrë të saktë argumentet e një funksioni.
Hapja e kutisë së dialogut VLOOKUP
Hapat që përdoren për të hyrë në funksionin VLOOKUP të treguar në imazhin e mësipërm në qelizën B2 janë:
- Klikoni në qelizën B2 për ta bërë atë qelizën aktive - vendndodhjen ku shfaqen rezultatet e funksionit VLOOKUP
- Klikoni në tab Formulat .
- Zgjidhni Kërkimin dhe Referencën nga shiriti për të hapur listën e lëshimit të funksionit
- Klikoni në VLOOKUP në listë për të shfaqur kutinë e dialogut të funksionit
02 nga 03
Hyrja e Argumenteve të Funksionit VLOOKUP të Excel
Duke treguar Referencat e Celës
Argumentet për funksionin VLOOKUP futen në linja të ndara të kutisë së dialogut siç tregohet në imazhin e mësipërm.
Referencat e qelizave që përdoren si argumente mund të shtypen në vijën e saktë, ose, siç bëhet në hapat e mëposhtëm, duke treguar, që përfshin nxjerrjen në pah të intervalit të dëshiruar të qelizave me treguesin e miut, mund të përdoret për të futur ato në kutinë e dialogut .
Përparësitë e përdorimit të shenjës përfshijnë:
- Është më shpejt se të shkruash;
- Janë bërë më pak gabime duke hyrë në referencat e saktë të qelizave.
Përdorimi i referencave të qelizave relative dhe absolute me argumente
Nuk është e pazakontë të përdorësh kopje të shumta të VLOOKUP për t'u kthyer informacione të ndryshme nga tabela e njëjtë e të dhënave. Për ta bërë më të lehtë për ta bërë këtë, shpesh VLOOKUP mund të kopjohet nga një qelizë në tjetrën. Kur funksionet kopjohen në qeliza të tjera, duhet të keni kujdes për të siguruar që referencat e qelizave që rezultojnë janë të sakta, duke marrë parasysh vendndodhjen e re të funksionit.
Në imazhin e mësipërm, shenjat e dollarit ( $ ) rrethojnë referencat e qelizës për argumentin table_array që tregon se ato janë referenca qelizore absolute , që do të thotë se ato nuk do të ndryshojnë nëse funksioni kopjohet në një qelizë tjetër. Kjo është e dëshirueshme pasi që kopjet e shumta të VLOOKUP do të referohen të gjithë tabelën e të dhënave të njëjta si burimi i informacionit.
Referenca e qelizës e përdorur për lookup_value, nga ana tjetër , nuk është e rrethuar nga shenja të dollarit, gjë që e bën atë një referencë relative qelizore. Referencat relative të qelizave ndryshojnë kur kopjohen për të pasqyruar vendndodhjen e tyre të re në lidhje me pozicionin e të dhënave për të cilat i referohen.
Hyrja e Argumenteve të Funksionit
- Klikoni në rreshtin Shqyrtim _value në kutinë e dialogut VLOOKUP
- Klikoni në qelizën C2 në fletën e punës për të hyrë në këtë referencë qelizore si argumenti search_key
- Klikoni në vijën e Tabelës së tabelës së dialogut
- Shënoni qelizat C5 deri në D8 në fletën e punës për të hyrë në këtë varg si argument Table_array - titujt e tabelës nuk janë të përfshira
- Shtypni butonin F4 në tastierë për të ndryshuar vargun në referencat absolute të qelizave
- Klikoni në vijën Col_index_num të kutisë së dialogut
- Lloji një 2 në këtë rresht si argumenti Col_index_num , meqë normat e skontimit gjenden në kolonën 2 të argumentit Table_array
- Klikoni në vijën Range_lookup të kutisë së dialogut
- Shkruani fjalën True si argumenti Range_lookup
- Shtypni butonin Enter në tastierë për të mbyllur kutinë e dialogut dhe për t'u kthyer në fletën e punës
- Përgjigja 2% (norma e zbritjes për sasinë e blerë) duhet të paraqitet në qelizën D2 të fletës së punës
- Kur klikoni në qelizën D2, funksioni i plotë = VLOOKUP (C2, $ C $ 5: $ D $ 8,2, TRUE) shfaqet në shiritin e formulës mbi fletën e punës
Pse VLOOKUP u kthye 2% si rezultat
- Në shembull, kolona Sasia nuk përmban një ndeshje të saktë për vlerën e kërkimit_kombë prej 19.
- Meqenëse argumenti is_sorted është vendosur në TRUE, VLOOKUP do të gjejë një ndeshje të përafërt me vlerën search_key .
- Vlera më e afërt në madhësi që është akoma më e vogël se vlera e search_key e 19 është 11.
- VLOOKUP, pra, kërkon përqindjen e zbritjes në rreshtin që përmban 11, dhe, si rezultat, kthen një normë skontimi prej 2%.
03 nga 03
Excel VLOOKUP nuk punon: # N / A dhe #REF Gabimet
Mesazhet e gabimit të VLOOKUP
Mesazhet e gabimit në vijim lidhen me VLOOKUP.
A # N / A ("vlera nuk është e disponueshme") Gabim shfaqet nëse:
- Shqyrtimi i vlerës nuk gjendet në kolonën e parë të argumentit të vargut
- Argumenti Table_array është i pasaktë. Për shembull, argumenti mund të përfshijë kolona bosh në anën e majtë të vargut
- Argumenti Range_lookup është vendosur në FALSE dhe një ndeshje e saktë për argumentin search_key nuk mund të gjendet në kolonën e parë të intervalit
- Argumenti Range_lookup është vendosur në TRUE dhe të gjitha vlerat në kolonën e parë të rangut janë më të mëdha se çelësi i kërkimit
Një #REF! ("referencë jashtë rrezes") Gabim shfaqet nëse:
- Argumenti Col_index_num është më i madh se numri i kolonave në Table_array