Duke kombinuar funksionin VLOOKUP të Excel me funksionin COLUMN ne mund të krijojmë një formulë të lookup që ju lejon të ktheni vlera të shumta nga një rresht i vetëm i një baze të të dhënave ose tabelë të të dhënave.
Në shembullin e treguar në imazhin e mësipërm, formula e lookup bën të lehtë kthimin e të gjitha vlerave - të tilla si çmimi, numri i pjesës dhe furnizuesi - të lidhura me pjesë të ndryshme të pajisjeve.
01 nga 10
Kthimi i vlerave të shumëfishta me Excel VLOOKUP
Duke ndjekur hapat e listuara më poshtë krijon formulën e shikimit të parë në imazhin e mësipërm që do të kthejë vlera të shumta nga një rekord i vetëm i të dhënave.
Formulimi i kërkimeve kërkon që funksioni COLUMN të mbivendoset brenda VLOOKUP.
Fletimi i një funksioni përfshin hyrjen në funksionin e dytë si një nga argumentet për funksionin e parë.
Në këtë tutorial, funksioni COLUMN do të 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ë gjetjes në kolona shtesë në mënyrë që të marrin vlera shtesë për pjesën e zgjedhur.
Përmbajtja e Tutorial
- Hyrja e të Dhënave të Tutorial
- Krijimi i një Range Named për Tabelën e të Dhënave
- Fillimi i funksionit VLOOKUP
- Futja e Argumentit të Vlerës së Kërkesës duke përdorur Referencat e Celularëve Absolute
- Hyrja e Argumentit të Tabelës së Tabelës
- Hyrja e Funksionit COLUMN të mbivendosur
- Përfundimi i funksionit VLOOKUP
- Kopjimi i Formularit të Kërkimit me Dorëzën e Plotësimit
- Marrja e të Dhënave me Formulën e Kërkimit
02 nga 10
Shkruani të dhënat e tutorialit
Hapi i parë në tutorial është të futni të dhënat në një fletë pune në Excel.
Në mënyrë që të ndiqni hapat në tutorial futni të dhënat e paraqitura në imazhin e mësipërm në qelizat e mëposhtme.
- Shkruani gamën më të lartë të të dhënave në qelizat D1 deri në G1
- Futni vargun e dytë në qeliza D4 deri në G10
Kriteret e kerkimit dhe formulari i kerkimit i krijuar gjate ketij udhezimi do te futen ne rreshtin 2 te tabeles.
Tutorial nuk përfshin formatimin e parë në imazh, por kjo nuk do të ndikojë në funksionimin e formulës së lookup.
Informacioni mbi opsionet e formatimit të ngjashme me ato të paraqitura më sipër është i disponueshëm në këtë Udhëzues të Formulimit Bazë të Excel-it .
Hapat e Tutorial
- Futni të dhënat siç shihet në imazhin e mësipërm në qelizat D1 deri në G10
03 nga 10
Krijimi i një Range Named për Tabelën e të Dhënave
Një varg i emëruar është një mënyrë e thjeshtë për t'iu referuar një sërë të dhënash në një formulë. Në vend që të shtypni referencat e qelizave për të dhënat, mund të shkruani vetëm emrin e vargut.
Një avantazh i dytë për të përdorur një varg të emëruar është se referencat e qelizave për këtë fushë nuk ndryshojnë as kur formulari kopjohet në qeliza të tjera në fletë pune.
Emrat e fushës janë, pra, një alternativë për përdorimin e referencave të qelizave absolute për të parandaluar gabimet kur kopjohen formula.
Shënim: Emri i vargut nuk përfshin titujt ose emrat e fushave për të dhënat (rreshti 4), por vetëm të dhënat vetë.
Hapat e Tutorial
- Dallojini qelizat D5 në G10 në fletën e punës për t'i përzgjedhur ato
- Kliko në kutinë e emrit të vendosur mbi kolonën A
- Lloji "Tabela" (pa citate) në Kutinë e Emrit
- Shtypni butonin ENTER në tastierë
- Qelizat D5 deri në G10 tani kanë emrin e vargut të "Tabelës". Ne do të përdorim emrin për argumentin e tabelave të tabelave VLOOKUP më vonë në tutorial
04 nga 10
Hapja e kutisë së dialogut VLOOKUP
Megjithëse është e mundur të shkruash direkt formulën e lookup direkt në një qelizë në një fletë pune, shumë njerëz e kanë të vështirë ta mbajnë sintakin drejt - veçanërisht për një formulë komplekse siç është ajo që ne po përdorim në këtë tutorial.
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 - vendin ku do të shfaqen rezultatet e formulës së kërkimit dy dimensionale
- 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ë hapur kutinë e dialogut të funksionit
05 nga 10
Futja e Argumentit të Vlerës së Kërkesës duke përdorur Referencat e Celularëve Absolute
Normalisht, vlera e kërkimit përputhet me një fushë të të dhënave në kolonën e parë të tabelës së të dhënave.
Në shembullin tonë, vlera e kërkimit i referohet emrit të pjesës hardware për të cilën dëshirojmë të gjejmë informacion.
Llojet e lejueshme të të dhënave për vlerën e kërkimit janë:
- të dhënat e tekstit
- një vlerë logjike (vetëm TRUE ose FALSE)
- një numër
- një referencë qelizore ndaj një vlere në fletën e punës
Në këtë shembull, ne do të hyjmë në referencën e qelizës ku do të gjendet emri i pjesës - qeliza D2.
Referencat absolute të qelizave
Në një hap të mëvonshëm në tutorial, ne do të kopjojmë formulën e lookup në qelizën E2 te qelizat F2 dhe G2.
Normalisht, kur formulat kopjohen në Excel, referencat e qelizave ndryshojnë për të pasqyruar vendndodhjen e tyre të re.
Nëse kjo ndodh, D2 - referenca e qelizës për vlerën e kërkimit - do të ndryshojë pasi formulari kopjohet duke krijuar gabime në qelizat F2 dhe G2.
Për të parandaluar gabimet, ne do të konvertojmë referencën qelizore D2 në një referencë qelizore absolute .
Referencat e qelizave absolute nuk ndryshojnë kur formula kopjohet.
Referencat e qelizave absolute krijohen duke shtypur butonin F4 në tastierë. Duke bërë kështu shton shenja të dollarit rreth referencës së qelizës, si $ D $ 2
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 . Kjo është qeliza ku do të shkruajmë emrin e pjesës për të cilën po kërkojmë informacion
- Pa lëvizur pikën e futjes, shtypni butonin F4 në tastierë për të kthyer D2 në referencën qelizore absolute $ D $ 2
- Lini kutinë e dialogut të funksionit VLOOKUP hapur për hapin e ardhshëm në tutorial
06 nga 10
Hyrja e Argumentit të Tabelës së Tabelës
Tabela e tabelës është tabela e të dhënave që kërkimet kërkojnë formula për të gjetur informacionin që duam.
Grupi i tabelës duhet të përmbajë të paktën dy kolona të të dhënave .
- kolona e parë përmban argumentin e vlerës së kërkimit (hapi i mëparshëm në tutorial)
- e dyta, dhe çdo kolonë shtesë, do të kontrollohen nga formula e lookup për të gjetur informacionin që ne i specifikojmë.
Argumenti i tabelës së tabelës duhet të futet si një varg që përmban referencat e qelizave për tabelën e të dhënave ose si një emër distanca .
Për këtë shembull, do të përdorim emrin e vargut të krijuar në hapin 3 të tutorialit.
Hapat e Tutorial
- Klikoni në tabelën e tabelave në kutinë e dialogut
- Lloji "Tabela" (pa citate) për të futur emrin e vargut për këtë argument
- Lini kutinë e dialogut të funksionit VLOOKUP hapur për hapin e ardhshëm në tutorial
07 nga 10
Foleja e funksionit COLUMN
Normalisht, VLOOKUP kthen vetëm të dhënat nga një kolonë e një tabele të të dhënave dhe kjo kolonë vendoset nga argumenti i numrit të indeksit të kolonës .
Në këtë shembull, megjithatë, kemi tre kolona që ne dëshirojmë të kthejmë të dhënat kështu që ne kemi nevojë për një mënyrë për të ndryshuar lehtësisht numrin e indeksit të kolonës pa editing formulën tonë të lookup.
Kjo është ajo ku futet funksioni COLUMN. Duke futur atë si argumenti i numrit të indeksit të kolonës , ajo do të ndryshojë pasi formula e lookup kopjohet nga qeliza D2 në qelizat E2 dhe F2 më vonë në tutorial.
Funksionet e Nesting
Funksioni COLUMN, pra, vepron si argument i numrit të indeksit të kolonës VLOOKUP.
Kjo arrihet duke fole funksionin COLUMN brenda VLOOKUP në linjën Col_index_num të kutisë së dialogut.
Hyrja me funksionin COLUMN manualisht
Kur funksionon foleja, Excel nuk na lejon të hapim kutinë e dialogut të funksionit të dytë për të futur argumentet e tij.
Funksioni COLUMN, pra, duhet të futet manualisht në linjën Col_index_num .
Funksioni COLUMN ka vetëm një argument - argumentin e Referencës i cili është një referencë qelizore.
Zgjedhja e Argumentit të Referimit të Funksionit të COLUMN
Puna e funksionit COLUMN është të kthejë numrin e kolonës së dhënë si argument i Referencës .
Me fjalë të tjera, ai konverton letrën e kolonës në një numër me kolonën A duke qenë kolona e parë, kolona B e dytë dhe kështu me radhë.
Meqenëse fusha e parë e të dhënave që duam të kthehemi është çmimi i artikullit - që është në kolonën e dytë të tabelës së të dhënave - ne mund të zgjedhim referencën e qelizës për çdo qelizë në kolonën B si Argument Referimi për të marrë numrin 2 për argumenti Col_index_num .
Hapat e Tutorial
- Në kutinë e dialogut të funksionit VLOOKUP, klikoni në vijën Col_index_num
- Shkruani kolonën e emrit të funksionit të ndjekur nga një kllapë e hapur raundi " ( "
- Klikoni në qelizën B1 në fletën e punës për të hyrë në atë referencë qelizore si argument referimi
- Shtypni një radhë të mbylljes radhazi " ) " për të përfunduar funksionin COLUMN
- Lini kutinë e dialogut të funksionit VLOOKUP hapur për hapin e ardhshëm në tutorial
08 nga 10
Hyrja në argumentin e VLOOKUP Range Lookup
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 Lookup_value.
- 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, meqenëse ne po kërkojmë informata specifike për një artikull hardware të veçantë, ne do të vendosim Range_lookup të barabartë me False .
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 kërkimit dhe kutinë e dialogut të ngushtë
- Pasi që ne ende nuk kemi hyrë në kriterin e kërkimit në qelizën D2 një gabim # N / A do të jetë i pranishëm në qelizën E2
- Ky gabim do të korrigjohet kur do të shtojmë kriterin e kërkimit në hapin e fundit të tutorialit
09 nga 10
Kopjimi i Formularit të Kërkimit me Dorëzën e Plotësimit
Formula e kërkimit synon të rifitojë të dhënat nga kolonat e shumta të tabelës së të dhënave në një kohë.
Për ta bërë këtë, formula e lookup duhet të banojë në të gjitha fushat nga të cilat duam informacion.
Në këtë tutorial ne duam që ajo të rifitojë të dhënat nga kolonat 2, 3 dhe 4 të tabelës së të dhënave - që është çmimi, numri i pjesës dhe emri i furnizuesit kur futim një emër të pjesës si "Lookup_value".
Meqenëse të dhënat janë paraqitur në një model të rregullt në fletën e punës , ne mund të kopjojmë formulën e lookup në qelizën E2 te qelizat F2 dhe G2.
Me kopjimin e formulës, Excel do të përditësojë referencën e qelizave relative në funksionin COLUMN (B1) për të pasqyruar vendndodhjen e re të formulës.
Po ashtu, Excel nuk ndryshon referencën qelizore absolute $ D $ 2 dhe tabelën e emrit të quajtur si formula është kopjuar.
Ka më shumë se një mënyrë për të kopjuar të dhëna në Excel, por ndoshta mënyra më e lehtë është duke përdorur Dorëzën e Plotësimit .
Hapat e Tutorial
- Klikoni në qelizën E2 - ku gjendet formulari i kërkimit - për ta bërë qelinë aktive
- Vendosni treguesin e miut mbi sheshin e zi në këndin e poshtëm djathtas. Treguesi do të ndryshojë në një shenjë plus " + " - kjo është dora e mbushur
- Klikoni butonin e majtë të miut dhe tërhiqni trajtimin e mbushjes përgjatë qelizës G2
- Lëshoni butonin e miut dhe qeliza F3 duhet të përmbajë formulën e kërkimit dy dimensional
- Nëse bëhet si duhet, qelizat F2 dhe G2 tani duhet të përmbajnë edhe gabimin # N / A që është i pranishëm në qelizën E2
10 nga 10
Hyrja në Kriteret e Kërkimit
Sapo formulari i kërkimit të jetë kopjuar në qelizat e kërkuara , mund të përdoret për të marrë informacion nga tabela e të dhënave.
Për ta bërë këtë, shtypni emrin e artikullit që dëshironi të rifitoni në qelizën Lookup_value (D2) dhe shtypni butonin ENTER në tastierë.
Pasi të bëhet, çdo qelizë që përmban formulën e lookup duhet të përmbajë një pjesë të ndryshme të të dhënave në lidhje me artikullin e pajisjeve që po kërkoni.
Hapat e Tutorial
- Klikoni në qelizën D2 në fletën e punës
- Lloji Widget në qelizë D2 dhe shtypni butonin ENTER në tastierë
- Informacioni i mëposhtëm duhet të shfaqet në qelizat E2 deri në G2:
- E2 - $ 14.76 - çmimi i një widget
- F2 - PN-98769 - numri i pjesës për një widget
- G2 - Widgets Inc. - emri i furnizuesit për Widgets
- Testoni formulën e grupit VLOOKUP më tej duke shtypur emrin e pjesëve të tjera në qelizën D2 dhe duke vëzhguar rezultatet në qelizat E2 deri në G2
Nese nje mesazh gabimi si #REF! shfaqet në qelizat E2, F2 ose G2, kjo listë e mesazheve të gabimit VLOOKUP mund t'ju ndihmojë të përcaktoni se ku qëndron problemi.