agwebrunner.com

Ang VLOOKUP ay isa sa mga pinaka kapaki-pakinabang na pag-andar ng Excel, at ito rin ay isa sa hindi gaanong nauunawaan. Sa artikulong ito, tinitiyak namin ang VLOOKUP sa pamamagitan ng isang halimbawa ng totoong buhay. Lilikha kami ng isang magagamit Template ng Invoice para sa isang kathang-isip na kumpanya.

Ang VLOOKUP ay isang Excel pagpapaandar. Ipagpapalagay ng artikulong ito na ang mambabasa ay mayroon nang pagdaan na pag-unawa sa mga pagpapaandar ng Excel, at maaaring gumamit ng mga pangunahing pag-andar tulad ng SUM, AVERAGE, at NGAYON. Sa pinaka-karaniwang paggamit nito, ang VLOOKUP ay isang database pagpapaandar, nangangahulugang gumagana ito sa mga talahanayan ng database - o higit pa nang simple, mga listahan ng mga bagay sa isang worksheet ng Excel. Anong uri ng mga bagay? Eh kahit ano uri ng bagay. Maaari kang magkaroon ng isang worksheet na naglalaman ng isang listahan ng mga empleyado, o mga produkto, o mga customer, o mga CD sa iyong koleksyon ng CD, o mga bituin sa kalangitan sa gabi. Hindi talaga mahalaga.

Narito ang isang halimbawa ng isang listahan, o database. Sa kasong ito ito ay isang listahan ng mga produkto na ipinagbibili ng aming fictitious na kumpanya:

Karaniwan ang mga listahan tulad nito ay may ilang uri ng natatanging identifier para sa bawat item sa listahan. Sa kasong ito, ang natatanging identifier ay nasa hanay na "Item Code". Tandaan: Upang gumana ang pagpapaandar ng VLOOKUP sa isang database / listahan, ang listahang iyon ay dapat magkaroon ng isang haligi na naglalaman ng natatanging pagkakakilanlan (o "key", o "ID"), at ang haligi na iyon ay dapat na ang unang haligi sa talahanayan. Ang aming sample na database sa itaas ay nasiyahan ang pamantayan na ito.

Ang pinakamahirap na bahagi ng paggamit ng VLOOKUP ay pag-unawa nang eksakto kung para saan ito. Tingnan natin kung maaari nating makuha ang linaw na iyon muna:

Kinukuha ng VLOOKUP ang impormasyon mula sa isang database / listahan batay sa isang ibinigay na halimbawa ng natatanging pagkakakilanlan.

Sa halimbawa sa itaas, ipapasok mo ang pagpapaandar ng VLOOKUP sa isa pang spreadsheet na may isang code ng item, at ibabalik sa iyo ang alinman sa paglalarawan ng kaukulang item, ang presyo nito, o ang pagkakaroon nito (ang dami ng "In stock") na inilarawan sa iyong orihinal listahan Alin sa mga impormasyon na ito ang ibabalik sa iyo nito? Kaya, mapagpasyahan mo ito kapag lumilikha ka ng formula.

Kung ang kailangan mo lang ay isang piraso ng impormasyon mula sa database, magiging maraming problema na puntahan upang bumuo ng isang formula na may function na VLOOKUP dito. Karaniwan gagamitin mo ang ganitong uri ng pag-andar sa isang magagamit muli na spreadsheet, tulad ng isang template. Sa tuwing may nagpasok ng isang wastong code ng item, kukuha ng system ang lahat ng kinakailangang impormasyon tungkol sa kaukulang item.

Lumikha tayo ng isang halimbawa nito: An Template ng Invoice na maaari nating magamit nang paulit-ulit sa aming kathang-isip na kumpanya.

Sinimulan muna namin ang Excel, at nilikha namin ang aming sarili ng isang blangkong invoice:

Ganito ito gagana: Ang taong gumagamit ng template ng invoice ay punan ang isang serye ng mga code ng item sa haligi na "A", at kukunin ng system ang paglalarawan at presyo ng bawat item mula sa aming database ng produkto. Ang impormasyong iyon ay gagamitin upang makalkula ang kabuuang linya para sa bawat item (sa pag-aakalang ipinasok namin ang isang wastong dami).

Para sa mga layunin ng pagpapanatili ng halimbawang ito na simple, mahahanap namin ang database ng produkto sa isang hiwalay na sheet sa parehong workbook:

Sa katotohanan, mas malamang na ang database ng produkto ay matatagpuan sa isang hiwalay na workbook. Gumagawa ito ng kaunting pagkakaiba sa pagpapaandar ng VLOOKUP, na hindi talaga alintana kung ang database ay matatagpuan sa parehong sheet, ibang sheet, o isang ganap na magkakaibang workbook.

Kaya, nilikha namin ang aming database ng produkto, na ganito ang hitsura:

Upang masubukan ang formula ng VLOOKUP na isusulat na namin, una kaming naglagay ng wastong code ng item sa cell A11 ng aming blangko na invoice:

Susunod, inililipat namin ang aktibong cell sa cell kung saan nais naming maimbak ang impormasyon mula sa database ng VLOOKUP. Kapansin-pansin, ito ang hakbang na nagkakamali ang karamihan sa mga tao. Upang maipaliwanag pa: Malilikha na namin ang isang formula na VLOOKUP na kukunin ang paglalarawan na tumutugma sa code ng item sa cell A11. Saan natin nais ilagay ang paglalarawan na ito kapag nakuha natin ito? Sa cell B11, syempre. Kaya't doon namin isinusulat ang pormula ng VLOOKUP: sa cell B11. Piliin ang cell B11 ngayon.

Kailangan naming hanapin ang listahan ng lahat ng mga magagamit na pag-andar na inaalok ng Excel, upang maaari kaming pumili ng VLOOKUP at makakuha ng tulong sa pagkumpleto ng formula. Ito ay matatagpuan sa pamamagitan ng unang pag-click sa Mga pormula tab, at pagkatapos ay pag-click Ipasok ang Pag-andar:

Lumilitaw ang isang kahon na nagbibigay-daan sa amin upang pumili ng alinman sa mga pagpapaandar na magagamit sa Excel.

Upang mahanap ang hinahanap namin, maaari kaming mag-type ng isang termino para sa paghahanap tulad ng "lookup" (dahil ang pagpapaandar na interesado kami ay a paghanap pagpapaandar). Ibabalik sa amin ng system ang isang listahan ng lahat ng mga pagpapaandar na nauugnay sa paghahanap sa Excel. VLOOKUP ang pangalawa sa listahan. Piliin ito ng isang pag-click OK lang.

Ang Pag-andar ng Mga Argumento lilitaw ang kahon, na hinihimok kami para sa lahat ng mga pagtatalo (o mga parameter) kinakailangan upang makumpleto ang pagpapaandar ng VLOOKUP. Maaari mong isipin ang kahong ito bilang pagpapaandar na nagtatanong sa amin ng mga sumusunod na katanungan:

1. Anong natatanging identifier ang tinitingnan mo sa database?
2. Nasaan ang database?
3. Aling piraso ng impormasyon mula sa database, na nauugnay sa natatanging identifier, nais mong makuha para sa iyo?

Ang unang tatlong mga argumento ay ipinapakita sa naka-bold, na nagpapahiwatig na sila ay sapilitan mga argumento (ang pagpapaandar ng VLOOKUP ay hindi kumpleto nang wala ang mga ito at hindi magbabalik ng wastong halaga). Ang ikaapat na argumento ay hindi naka-bold, nangangahulugang opsyonal ito:

Kami ay makukumpleto ang mga argumento sa pagkakasunud-sunod, itaas hanggang sa ibaba.

Ang unang argumentong kailangan nating kumpletuhin ay ang Lookup_value pagtatalo Kailangan ng pagpapaandar sa amin upang sabihin dito kung saan hanapin ang natatanging pagkakakilanlan (ang code ng item sa kasong ito) na dapat itong ibalik ang paglalarawan ng. Dapat nating piliin ang item code na ipinasok namin nang mas maaga (sa A11).

Mag-click sa icon ng tagapili sa kanan ng unang argument:

Pagkatapos mag-click nang isang beses sa cell na naglalaman ng item code (A11), at pindutin Pasok:

Ang halaga ng "A11" ay ipinasok sa unang argumento.

Ngayon kailangan naming maglagay ng isang halaga para sa Table_array pagtatalo Sa madaling salita, kailangan nating sabihin sa VLOOKUP kung saan hahanapin ang database / listahan. Mag-click sa icon ng tagapili sa tabi ng pangalawang argument:

Ngayon hanapin ang database / listahan at piliin ang buong listahan - hindi kasama ang linya ng header. Sa aming halimbawa, ang database ay matatagpuan sa isang hiwalay na worksheet, kaya unang nag-click kami sa tab na worksheet:

Susunod na pipiliin namin ang buong database, hindi kasama ang linya ng header:

... at pindutin Pasok. Ang saklaw ng mga cell na kumakatawan sa database (sa kasong ito na "'Product Database'! A2: D7") ay awtomatikong naipasok para sa amin sa pangalawang pagtatalo.

Ngayon kailangan nating ipasok ang pangatlong argumento, Col_index_num. Ginagamit namin ang argument na ito upang tukuyin sa VLOOKUP kung aling piraso ng impormasyon mula sa database, na naiugnay sa aming code ng item sa A11, nais naming bumalik sa amin. Sa partikular na halimbawang ito, nais naming magkaroon ng item paglalarawan bumalik sa amin. Kung titingnan mo ang worksheet ng database, mapapansin mo na ang haligi na "Paglalarawan" ay ang pangalawa haligi sa database. Nangangahulugan ito na dapat kaming magpasok ng isang halaga ng "2" sa Col_index_num kahon:

Mahalagang tandaan na hindi kami pumapasok sa isang "2" dito dahil ang haligi na "Paglalarawan" ay nasa B haligi sa worksheet na iyon. Kung ang database ay nangyari upang magsimula sa haligi K ng worksheet, papasok pa rin kami ng isang "2" sa larangang ito dahil ang haligi na "Paglalarawan" ay ang pangalawang haligi sa hanay ng mga cell na pinili namin kapag tinukoy ang "Table_array".

Panghuli, kailangan nating magpasya kung maglalagay ng isang halaga sa pangwakas na argumento ng VLOOKUP, Saklaw_lookup. Ang argument na ito ay nangangailangan ng alinman sa a totoo o hindi totoo halaga, o dapat itong iwanang blangko. Kapag gumagamit ng VLOOKUP sa mga database (tulad ng totoong 90% ng oras), ang paraan upang magpasya kung ano ang ilalagay sa argument na ito ay maaaring isipin tulad ng sumusunod:

Kung ang unang haligi ng database (ang haligi na naglalaman ng mga natatanging pagkakakilanlan) ay pinagsunod-sunod ayon sa alpabeto / bilang ayon sa pataas na pagkakasunud-sunod, posible na magpasok ng isang halaga ng totoo sa argument na ito, o iwanang blangko ito.

Kung ang unang haligi ng database ay hindi pinagsunod-sunod, o pinagsunod-sunod ito sa pababang pagkakasunud-sunod, pagkatapos ikaw dapat maglagay ng halaga ng hindi totoo sa pagtatalo na ito

Tulad ng unang haligi ng aming database ay hindi pinagsunod-sunod, pumapasok kami hindi totoo sa argument na ito:

Ayan yun! Naipasok namin ang lahat ng impormasyong kinakailangan para maibalik ng VLOOKUP ang halagang kailangan namin. I-click ang OK lang pindutan at pansinin na ang paglalarawan na naaayon sa item code na "R99245" ay naipasok nang tama sa cell B11:

Ganito ang pormula na nilikha para sa amin:

Kung papasok tayo a iba code ng item sa cell A11, magsisimula kaming makita ang lakas ng pagpapaandar ng VLOOKUP: Nagbabago ang paglalarawan ng cell upang tumugma sa bagong code ng item:

Maaari kaming magsagawa ng isang katulad na hanay ng mga hakbang upang makuha ang item presyo bumalik sa cell E11. Tandaan na ang bagong formula ay dapat likhain sa cell E11. Magiging ganito ang magiging resulta:

... at ang formula ay magiging ganito:

Tandaan na ang pagkakaiba lamang sa pagitan ng dalawang formula ay ang pangatlong argumento (Col_index_num) ay nagbago mula sa isang "2" patungo sa isang "3" (dahil nais naming makuha ang data mula sa ika-3 haligi sa database).

Kung nagpasya kaming bumili ng 2 sa mga item na ito, papasok kami ng isang "2" sa cell D11. Ipapasok namin pagkatapos ang isang simpleng pormula sa cell F11 upang makuha ang kabuuang linya:

= D11 * E1

... na ganito ang hitsura ...

Pagkumpleto sa Template ng Invoice

Marami kaming natutunan tungkol sa VLOOKUP sa ngayon. Sa katunayan, natutunan namin ang lahat ng matututunan natin sa artikulong ito. Mahalagang tandaan na ang VLOOKUP ay maaaring magamit sa iba pang mga pangyayari bukod sa mga database. Ito ay hindi gaanong karaniwan, at maaaring saklaw sa hinaharap na artikulo ng How-To Geek.

Ang aming template ng invoice ay hindi pa kumpleto. Upang makumpleto ito, gagawin namin ang sumusunod:

1. Aalisin namin ang sample na code ng item mula sa cell A11 at ang "2" mula sa cell D11. Magiging sanhi ito ng aming bagong nilikha na mga formula ng VLOOKUP na magpakita ng mga mensahe ng error:

   
   

   
   Maaari nating malunasan ito sa pamamagitan ng matalinong paggamit ng Excel KUNG () at ISBLANK () pagpapaandar Binabago namin ang aming pormula mula dito ... = VLOOKUP (A11, 'Product Database'! A2: D7,2, FALSE)…Sa ganito…= KUNG (ISBLANK (A11), "", VLOOKUP (A11, ’Product Database’! A2: D7,2, FALSE))

   
   

2. Kopyahin namin ang mga formula sa mga cell B11, E11 at F11 pababa sa natitirang mga item row ng invoice. Tandaan na kung gagawin natin ito, ang mga nagresultang formula ay hindi na magre-refer nang tama sa talahanayan ng database. Maaari naming ayusin ito sa pamamagitan ng pagbabago ng mga sanggunian ng cell para sa database sa ganap mga sanggunian sa cell. Bilang kahalili - at mas mabuti pa - maaari kaming lumikha ng isang pangalan ng saklaw para sa buong database ng produkto (tulad ng "Mga Produkto"), at gamitin ang pangalan ng saklaw na ito sa halip na mga sanggunian ng cell. Magbabago ang formula mula rito… = KUNG (ISBLANK (A11), "", VLOOKUP (A11, ’Product Database’! A2: D7,2, FALSE))…Sa ganito… = KUNG (ISBLANK (A11), ””, VLOOKUP (A11, Mga Produkto, 2, MALI))... at tapos kopyahin ang mga formula hanggang sa natitirang mga hilera ng item ng invoice.
3. Marahil ay "maia-lock" natin ang mga cell na naglalaman ng aming mga formula (o sa halip i-unlock ang iba pa cells), at pagkatapos ay protektahan ang worksheet, upang matiyak na ang aming maingat na binuo na mga formula ay hindi sinasadyang ma-overlap kapag may dumating upang punan ang invoice.
4. Sine-save namin ang file bilang a template, upang magamit ito muli ng lahat sa aming kumpanya

Kung nararamdaman natin Talaga matalino, gagawa kami ng isang database ng lahat ng aming mga customer sa isa pang worksheet, at pagkatapos ay gagamitin ang customer ID na ipinasok sa cell F5 upang awtomatikong punan ang pangalan at address ng customer sa mga cell B6, B7 at B8.

Kung nais mong magsanay sa VLOOKUP, o simpleng makita ang aming nagresultang Template ng Invoice, maaari itong mai-download mula rito.