Maghanap ng Maramihang Mga Patlang ng Data sa Excel VLOOKUP

Sa pamamagitan ng pagsasama ng function na VLOOKUP ng Excel sa pag-andar ng COLUMN maaari kaming lumikha ng lookup formula na nagbibigay-daan sa iyo upang makabalik ng maramihang mga halaga mula sa isang solong hilera ng isang database o talaan ng data.

Sa halimbawang ipinakita sa imahe sa itaas, ang lookup formula ay ginagawang madali upang ibalik ang lahat ng mga halaga - tulad ng presyo, numero ng bahagi, at supplier - na may kaugnayan sa iba't ibang mga piraso ng hardware.

01 ng 10

Bumalik ng Maramihang Mga Halaga na may Excel VLOOKUP

Ang pagsunod sa mga hakbang na nakalista sa ibaba ay lumilikha ng lookup formula na nakikita sa imahe sa itaas na magbabalik ng maramihang mga halaga mula sa isang solong talaan ng data.

Kinakailangan ng formula ng lookup na ang function ng COLUMN ay nakapaso sa loob ng VLOOKUP.

Ang pag-angkop ng isang function ay nagsasangkot ng pagpasok ng ikalawang function bilang isa sa mga argumento para sa unang pag-andar.

Sa tutorial na ito, ang COLUMN function ay ipapasok bilang argumento bilang index ng hanay para sa VLOOKUP.

Ang huling hakbang sa tutorial ay nagsasangkot sa pagkopya ng lookup formula sa mga karagdagang column upang makuha ang karagdagang mga halaga para sa napiling bahagi.

Nilalaman ng Tutorial

• Pagpasok sa Data ng Tutorial
• Paglikha ng Named Range para sa Data Table
• Simula sa VLOOKUP Function
• Pagpasok sa Lookup Value Argument gamit ang Absolute Cell References
• Pagpasok sa Table Array Argument
• Pagpasok sa Nested COLUMN Function
• Pagkumpleto ng VLOOKUP Function
• Kinokopya ang Formula ng Lookup gamit ang Handle ng Punan
• Pagkuha ng Data sa Formula ng Lookup

02 ng 10

Ipasok ang Data ng Tutorial

Ang unang hakbang sa tutorial ay upang ipasok ang data sa isang worksheet ng Excel.

Upang masunod ang mga hakbang sa tutorial, ipasok ang data na ipinapakita sa imahe sa itaas sa mga sumusunod na cell .

• Ipasok ang pinakamataas na hanay ng data sa mga cell D1 hanggang G1
• Ipasok ang ikalawang hanay sa mga cell D4 hanggang G10

Ang pamantayan sa paghahanap at ang lookup formula na nilikha sa panahon ng tutorial na ito ay ipapasok sa row 2 ng worksheet.

Ang tutorial ay hindi kasama ang pag-format na nakikita sa larawan, ngunit hindi ito makakaapekto kung paano gumagana ang lookup formula.

Ang impormasyon sa mga opsyon sa pag-format na katulad ng nakikita sa itaas ay magagamit sa Tutorial sa Formatting Basic Excel na ito .

Mga Hakbang sa Tutorial

1. Ipasok ang data na nakikita sa imahe sa itaas sa mga cell D1 hanggang G10

03 ng 10

Paglikha ng Named Range para sa Data Table

Ang pinangalanang hanay ay isang madaling paraan upang sumangguni sa isang hanay ng data sa isang formula. Sa halip na mag-type sa mga sanggunian ng cell para sa data, maaari mo lamang i-type ang pangalan ng range.

Ang pangalawang bentahe sa paggamit ng isang pinangalanang hanay ay ang mga sanggunian ng cell para sa saklaw na ito ay hindi kailanman nagbabago kahit na ang formula ay nakopya sa iba pang mga cell sa worksheet.

Ang mga pangalan ng saklaw ay, samakatuwid, isang alternatibo sa paggamit ng ganap na sanggunian ng cell upang maiwasan ang mga error kapag kinopya ang mga formula.

Tandaan: Hindi kasama sa hanay ng pangalan ang mga pamagat o pangalan ng patlang para sa data (hilera 4) ngunit lamang ang data mismo.

Mga Hakbang sa Tutorial

1. I-highlight ang mga cell D5 hanggang G10 sa worksheet upang piliin ang mga ito
2. Mag-click sa Name Box na nasa itaas ng haligi A
3. I-type ang "Table" (walang mga quote) sa Name Box
4. Pindutin ang ENTER key sa keyboard
5. Ang mga cell D5 hanggang G10 ay mayroon na ngayong pangalan ng hanay na "Table". Gagamitin namin ang pangalan para sa argumento ng array ng VLOOKUP table mamaya sa tutorial

04 ng 10

Pagbubukas ng VLOOKUP Dialog Box

Kahit na posible na i-type lamang ang aming lookup formula nang direkta sa isang cell sa isang worksheet, maraming mga tao ang nahihirapang manatiling tuwid ang syntax - lalo na para sa isang kumplikadong formula tulad ng isa na ginagamit namin sa tutorial na ito.

Ang isang alternatibo, sa kasong ito, ay gamitin ang kahon ng dialogo ng VLOOKUP. Halos lahat ng mga function ng Excel ay may dialog box na nagbibigay-daan sa iyo upang ipasok ang bawat argumento ng function sa isang hiwalay na linya.

Mga Hakbang sa Tutorial

1. Mag-click sa cell E2 ng worksheet - ang lokasyon kung saan makikita ang mga resulta ng dalawang dimensional na lookup formula
2. Mag-click sa tab na Formula ng laso
3. Mag-click sa pagpipiliang Lookup at Sanggunian sa laso upang buksan ang drop down na listahan ng function
4. Mag-click sa VLOOKUP sa listahan upang buksan ang dialog box ng function

05 ng 10

Pagpasok sa Lookup Value Argument gamit ang Absolute Cell References

Karaniwan, ang halaga ng lookup ay tumutugma sa larangan ng data sa unang hanay ng talahanayan ng data.

Sa aming halimbawa, ang halaga ng paghahanap ay tumutukoy sa pangalan ng bahagi ng hardware kung saan nais naming makahanap ng impormasyon.

Ang mga pinapahintulutang uri ng data para sa halaga ng lookup ay:

• data ng teksto
• isang lohikal na halaga (TRUE o FALSE lamang)
• isang numero
• isang reference sa cell sa isang halaga sa worksheet

Sa halimbawang ito, ipapasok namin ang cell reference sa kung saan matatagpuan ang pangalan ng bahagi - cell D2.

Mga Gabay sa Absolute Cell

Sa susunod na hakbang sa tutorial, kopyahin namin ang lookup formula sa cell E2 sa mga cell F2 at G2.

Karaniwan, kapag ang mga formula ay kinopya sa Excel, nagbabago ang mga reference sa cell upang mapakita ang kanilang bagong lokasyon.

Kung nangyari ito, ang D2 - ang reference sa cell para sa halaga ng paghahanap - ay magbabago habang ang formula ay nakopya sa paglikha ng mga error sa mga cell F2 at G2.

Upang maiwasan ang mga error, i-convert namin ang cell reference D2 sa isang absolute reference ng cell .

Ang mga absolute reference ng cell ay hindi nagbabago kapag ang mga formula ay kinopya.

Ang mga ganap na sanggunian ng cell ay nilikha sa pamamagitan ng pagpindot sa F4 key sa keyboard. Ang paggawa nito ay nagdaragdag ng mga palatandaan ng dollar sa paligid ng reference ng cell tulad ng $ D $ 2

Mga Hakbang sa Tutorial

1. Mag-click sa line lookup_value sa dialog box
2. Mag-click sa cell D2 upang idagdag ang reference ng cell na ito sa linya ng lookup_value . Ito ang cell kung saan namin i-type ang pangalan ng bahagi tungkol sa kung saan kami ay naghahanap ng impormasyon
3. Nang walang paglipat sa insertion point, pindutin ang F4 key sa keyboard upang i-convert ang D2 sa absolute reference ng cell $ D $ 2
4. Iwanan ang dialog box ng function ng VLOOKUP bukas para sa susunod na hakbang sa tutorial

06 ng 10

Pagpasok sa Table Array Argument

Ang hanay ng talahanayan ay ang talahanayan ng data na hinahanap ng lookup formula upang mahanap ang impormasyong gusto namin.

Ang hanay ng talahanayan ay dapat maglaman ng hindi bababa sa dalawang haligi ng data .

• ang unang haligi ay naglalaman ng argumento ng lookup na halaga (nakaraang hakbang sa tutorial)
• ang pangalawang, at anumang karagdagang mga haligi, ay hahanapin ng lookup formula upang mahanap ang impormasyon na aming tinukoy.

Ang talahanayan ng hanay ng table ay dapat maipasok bilang alinman sa hanay na naglalaman ng mga reference sa cell para sa talahanayan ng data o bilang isang pangalan ng saklaw .

Para sa halimbawang ito, gagamitin namin ang range name na nilikha sa hakbang 3 ng tutorial.

Mga Hakbang sa Tutorial

1. Mag-click sa linya ng table_array sa dialog box
2. I-type ang "Table" (walang quote) upang ipasok ang pangalan ng range para sa argument na ito
3. Iwanan ang dialog box ng function ng VLOOKUP bukas para sa susunod na hakbang sa tutorial

07 ng 10

Pag-angkop sa COLUMN Function

Karaniwan ay nagbabalik lamang ang VLOOKUP ng data mula sa isang haligi ng isang talahanayan ng data at ang hanay na ito ay itinakda ng argumento ng numero ng hanay ng index .

Gayunpaman, sa halimbawang ito, mayroon kaming tatlong hanay na nais naming ibalik ang data mula sa gayon kailangan namin ng isang paraan upang madaling baguhin ang numero ng index ng haligi nang hindi ine-edit ang aming lookup formula.

Ito ay kung saan ang function ng COLUMN ay dumating sa pamamagitan ng pagpasok nito bilang argumento bilang index ng hanay , ito ay magbabago habang ang lookup formula ay kinopya mula sa cell D2 papunta sa mga cell E2 at F2 mamaya sa tutorial.

Nesting Function

Ang function ng COLUMN, samakatuwid, ay gumaganap bilang argumento ng hanay ng index ng hanay ng VLOOKUP.

Ito ay natapos sa pamamagitan ng nesting ang COLUMN function sa loob ng VLOOKUP sa Col_index_num linya ng kahon ng dialogo.

Pumasok ang COLUMN Function Manu-manong

Kapag naglalagay ng mga function ng nesting, hindi pinapayagan kami ng Excel na buksan ang dialog box ng pangalawang function upang ipasok ang mga argumento nito.

Kung gayon, ang function ng COLUMN ay dapat manu-manong ipinasok sa linya ng Col_index_num .

Ang COLUMN function ay may isang argument lamang - ang argumento ng Sanggunian na isang reference ng cell.

Pagpili ng Sangguniang Pagtuturo ng COLUMN Function

Ang trabaho ng COLUMN function ay upang ibalik ang bilang ng haligi na ibinigay bilang Reference argument.

Sa ibang salita, binago nito ang haligi ng hanay sa isang numero na may haligi A na ang unang haligi, haligi B ang pangalawang at iba pa.

Dahil ang unang larangan ng data na nais naming ibalik ay ang presyo ng item - na nasa haligi ng dalawang talahanayan ng data - maaari naming piliin ang cell reference para sa anumang cell sa haligi B bilang Reference Argument upang makuha ang numero 2 para sa ang argumento ng Col_index_num .

Mga Hakbang sa Tutorial

1. Sa kahon ng dialogo ng VLOOKUP function, mag-click sa linya ng Col_index_num
2. I-type ang haligi ng pangalan ng function na sinusundan ng isang bukas na round bracket " ( "
3. Mag-click sa cell B1 sa worksheet upang mapasok ang cell reference na iyon bilang Reference argument
4. Mag-type ng pagsasara ng round bracket " ) " upang makumpleto ang function na COLUMN
5. Iwanan ang dialog box ng function ng VLOOKUP bukas para sa susunod na hakbang sa tutorial

08 ng 10

Pagpasok sa VLOOKUP Range Lookup Argument

Ang Range_lookup argument ng VLOOKUP ay isang lohikal na halaga (TRUE o FALSE only) na nagpapahiwatig kung nais mong VLOOKUP upang makahanap ng isang eksaktong o isang tinatayang tugma sa Lookup_value.

• Kung TRUE o kung ang argument na ito ay tinanggal, ang VLOOKUP ay nagbabalik ng isang eksaktong tugma sa Lookup_value, o, kung hindi eksaktong tugma, ang VLOOKUP ay nagbabalik sa susunod na pinakamalaking halaga. Para sa formula na gawin ito, ang data sa unang haligi ng Table_array ay dapat na pinagsunod - sunod sa pataas na pagkakasunud-sunod .
• Kung ang FALSE, ang VLOOKUP ay gagamit lamang ng eksaktong tugma sa Lookup_value. Kung mayroong dalawa o higit pang mga halaga sa unang hanay ng Table_array na tumutugma sa halaga ng paghahanap, ang unang halaga na natagpuan ay ginagamit. Kung ang isang eksaktong tugma ay hindi natagpuan, isang # N / A error ay ibinalik.

Sa tutorial na ito, dahil hinahanap namin ang tiyak na impormasyon tungkol sa isang partikular na item sa hardware, itatakda namin ang Range_lookup na katumbas ng Mali .

Mga Hakbang sa Tutorial

1. Mag-click sa hanay ng Range_lookup sa dialog box
2. I-type ang salitang Mali sa linya na ito upang ipahiwatig na nais namin ang VLOOKUP upang makabalik ng eksaktong tugma para sa data na aming hinahanap
3. I-click ang OK upang makumpleto ang lookup formula at isara ang dialog box
4. Dahil hindi pa namin ipinasok ang pamantayan sa lookup sa cell D2 ang isang # N / A error ay makikita sa cell E2
5. Ang error na ito ay itatama kapag idaragdag namin ang pamantayan sa lookup sa huling hakbang ng tutorial

09 ng 10

Kinokopya ang Formula ng Lookup gamit ang Handle ng Punan

Ang formula sa lookup ay inilaan upang mabawi ang data mula sa maraming mga hanay ng talahanayan ng data sa isang pagkakataon.

Upang gawin ito, ang formula ng lookup ay dapat manirahan sa lahat ng larangan mula sa kung saan nais namin ang impormasyon.

Sa tutorial na ito gusto naming makuha nito ang data mula sa mga haligi 2, 3, at 4 ng talahanayan ng data - na ang presyo, ang numero ng bahagi, at ang pangalan ng tagapagtustos kapag nagpasok kami ng isang bahagi na pangalan bilang Lookup_value.

Dahil ang data ay inilatag sa isang regular na pattern sa worksheet , maaari naming kopyahin ang lookup formula sa cell E2 sa mga cell F2 at G2.

Habang nakopya ang formula, i-update ng Excel ang reference ng cell ng kamag - anak sa function na COLUMN (B1) upang mapakita ang bagong lokasyon ng formula.

Gayundin, hindi binabago ng Excel ang absolute reference ng cell $ D $ 2 at ang pinangalanang Table ng hanay habang ang formula ay kinopya.

Mayroong higit sa isang paraan upang kopyahin ang data sa Excel, ngunit marahil ang pinakamadaling paraan ay ang paggamit ng Punan ng Punan .

Mga Hakbang sa Tutorial

1. Mag-click sa cell E2 - kung saan matatagpuan ang lookup formula - upang gawin itong aktibong cell
2. Ilagay ang mouse pointer sa ibabaw ng black square sa kanang sulok sa ibaba. Ang pointer ay magbabago sa isang plus sign " + " - ito ay ang hawakan punan
3. I-click ang kaliwang pindutan ng mouse at i-drag ang hawakan ng punan patungo sa cell G2
4. Bitawan ang pindutan ng mouse at ang cell F3 ay dapat maglaman ng dalawang dimensional na lookup formula
5. Kung tapos na nang tama, ang mga cell F2 at G2 ay dapat na naglalaman din ng # N / A error na nasa cell E2

10 ng 10

Pagpasok sa Lookup Criteria

Sa sandaling ang kopya ng lookup ay kinopya sa mga kinakailangang cell na magagamit nito upang makuha ang impormasyon mula sa talahanayan ng data.

Upang gawin ito, i-type ang pangalan ng item na nais mong kunin sa Lookup_value cell (D2) at pindutin ang ENTER key sa keyboard.

Sa sandaling tapos na, ang bawat cell na naglalaman ng lookup formula ay dapat maglaman ng ibang piraso ng data tungkol sa item ng hardware na iyong hinahanap.

Mga Hakbang sa Tutorial

1. Mag-click sa cell D2 sa worksheet
2. I-type ang Widget sa cell D2 at pindutin ang ENTER key sa keyboard
3. Ang sumusunod na impormasyon ay dapat na ipinapakita sa mga cell E2 hanggang G2:
   • E2 - $ 14.76 - ang presyo ng isang widget
   • F2 - PN-98769 - ang numero ng bahagi para sa isang widget
   • G2 - Mga Widget Inc. - ang pangalan ng supplier para sa mga widget
4. Subukan ang karagdagang formula ng VLOOKUP sa pamamagitan ng pag-type ng pangalan ng ibang mga bahagi sa cell D2 at pagmamasid sa mga resulta sa mga cell E2 hanggang G2

Kung ang isang mensahe ng error tulad ng #REF! Lumilitaw sa mga cell E2, F2, o G2, ang listahang ito ng mga mensahe ng error sa VLOOKUP ay maaaring makatulong sa iyo na matukoy kung nasaan ang problema.