Gjeni fusha të shumëfishta të të dhënave me Excel VLOOKUP

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

Kthimi i vlerave të shumëfishta me Excel VLOOKUP. © Ted French

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

02 nga 10

Shkruani të dhënat e tutorialit

Hyrja e të Dhënave të Tutorial. © Ted French

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.

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

  1. 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

Klikoni mbi imazhin për të parë madhësinë e plotë. © Ted French

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

  1. Dallojini qelizat D5 në G10 në fletën e punës për t'i përzgjedhur ato
  2. Kliko në kutinë e emrit të vendosur mbi kolonën A
  3. Lloji "Tabela" (pa citate) në Kutinë e Emrit
  4. Shtypni butonin ENTER në tastierë
  5. 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

Klikoni mbi imazhin për të parë madhësinë e plotë. © Ted French

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

  1. Klikoni në qelizën E2 të fletëspunës - vendin ku do të shfaqen rezultatet e formulës së kërkimit dy dimensionale
  2. Klikoni në tab Formulat e shiritit
  3. Klikoni në opsionin Lookup & Reference në shiritin për të hapur drop down listën e funksionit
  4. 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

Klikoni mbi imazhin për të parë madhësinë e plotë. © Ted French

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ë:

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

  1. Klikoni në vijën e lookup_valuekutinë e dialogut
  2. 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
  3. Pa lëvizur pikën e futjes, shtypni butonin F4 në tastierë për të kthyer D2 në referencën qelizore absolute $ D $ 2
  4. 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

Klikoni mbi imazhin për të parë madhësinë e plotë. © Ted French

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 .

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

  1. Klikoni në tabelën e tabelavekutinë e dialogut
  2. Lloji "Tabela" (pa citate) për të futur emrin e vargut për këtë argument
  3. Lini kutinë e dialogut të funksionit VLOOKUP hapur për hapin e ardhshëm në tutorial

07 nga 10

Foleja e funksionit COLUMN

Klikoni mbi imazhin për të parë madhësinë e plotë. © Ted French

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_numkutisë 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

  1. Në kutinë e dialogut të funksionit VLOOKUP, klikoni në vijën Col_index_num
  2. Shkruani kolonën e emrit të funksionit të ndjekur nga një kllapë e hapur raundi " ( "
  3. Klikoni në qelizën B1fletën e punës për të hyrë në atë referencë qelizore si argument referimi
  4. Shtypni një radhë të mbylljes radhazi " ) " për të përfunduar funksionin COLUMN
  5. 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

Klikoni mbi imazhin për të parë madhësinë e plotë. © Ted French

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ë 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

  1. Klikoni në vijën Range_lookupkutinë e dialogut
  2. 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ë
  3. Kliko OK për të përfunduar formulën e kërkimit dhe kutinë e dialogut të ngushtë
  4. 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
  5. 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

Klikoni mbi imazhin për të parë madhësinë e plotë. © Ted French

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 emritquajtur 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

  1. Klikoni në qelizën E2 - ku gjendet formulari i kërkimit - për ta bërë qelinë aktive
  2. 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
  3. Klikoni butonin e majtë të miut dhe tërhiqni trajtimin e mbushjes përgjatë qelizës G2
  4. Lëshoni butonin e miut dhe qeliza F3 duhet të përmbajë formulën e kërkimit dy dimensional
  5. 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

Marrja e të Dhënave me Formulën e Kërkimit. © Ted French

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

  1. Klikoni në qelizën D2 në fletën e punës
  2. Lloji Widget në qelizë D2 dhe shtypni butonin ENTER në tastierë
  3. 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
  4. 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.