Maghanap ng Maramihang Mga Patlang ng Data sa Excel VLOOKUP

Sa pamamagitan ng pagsasama ng ExcelVLOOKUP gumana saCOLUMN function na maaari naming lumikha ng lookup formula na nagbibigay-daan sa iyo upang ibalik ang maramihang mga halaga mula sa isang solong hilera ng isang database o talaan ng data.

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 lookup formula na ang COLUMN function ay nested 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 Ang pagpapaandar ay ipapasok bilang index ng haligi numero argumento 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.

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 sa G1.
• Ipasok ang ikalawang hanay sa mga cell D4 sa G10.

Ang pamantayan sa paghahanap at ang formula ng lookup na nilikha sa panahon ng tutorial na ito ay ipapasok hilera 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.

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.

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

1. I-highlight mga cell D5 sa G10 sa worksheet upang piliin ang mga ito.
2. Mag-click sa Pangalan ng Kahon na matatagpuan sa itaas haligi A.
3. Uri Table sa Pangalan ng Kahon.
4. pindutin ang Ipasok susi sa keyboard.
5. Mga Cell D5 sa G10 ngayon ay may hanay ng pangalan ng Table. Gagamitin namin ang pangalan para sa VLOOKUP table array argumento 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 VLOOKUP dialog box. 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 ipapakita ang mga resulta ng dalawang-dimensional na lookup formula.
2. Mag-click sa Tab ng formula ng laso.
3. Mag-click sa Lookup & Reference pagpipilian sa laso upang buksan ang drop-down na function.
4. Mag-click sa VLOOKUP sa listahan upang buksan ang dialog box ng function.

05 ng 10

Pagpasok sa Lookup Value Argument

Karaniwan, ang lookup halaga tumutugma sa larangan ng data sa unang hanay ng talahanayan ng data. Sa aming halimbawa, ang lookup halaga ay tumutukoy sa pangalan ng bahagi ng hardware kung saan nais naming makahanap ng impormasyon. Ang pinapahintulutang uri ng data para sa lookup halaga ay ang data ng teksto, mga lohikal na halaga, numero, at mga reference sa cell.

Mga Gabay sa Absolute Cell

Karaniwan, kapag ang mga formula ay kinopya sa Excel, nagbabago ang mga reference sa cell upang mapakita ang kanilang bagong lokasyon. Kung mangyari ito, D2, ang cell reference para sa lookup halaga, magbabago kapag ang kopya ay nakopya sa paglikha ng mga error sa mga cell F2 at G2.

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

Upang maiwasan ang mga pagkakamali, i-convert namin ang reference ng cell D2 sa isang absolute reference ng cell. Ang mga ganap na sanggunian ng cell ay nilikha sa pamamagitan ng pagpindot sa F4 susi sa keyboard. Ang paggawa nito ay nagdaragdag ng mga palatandaan ng dolyar sa palibot ng sanggunian ng cell tulad ng $ D $ 2.

1. Mag-click sa lookup_value linya sa dialog box.
2. Mag-click sa cell D2 upang idagdag ang reference na ito ng cell sa lookup_value linya - 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 pagpapasok ng punto, pindutin ang F4 susi sa keyboard upang i-convert D2 sa absolute reference ng cell $ D $ 2.
4. Iwanan ang VLOOKUP function dialog box na 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 hanay ay naglalaman ng argumento ng lookup value (nakaraang hakbang sa tutorial), habang ang pangalawang haligi ay hahanapin ng lookup formula upang mahanap ang impormasyon na tinukoy namin.

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.

1. Mag-click sa table_array linya sa dialog box.
2. Uri Table upang ipasok ang pangalan ng range para sa argument na ito.
3. Iwanan ang VLOOKUP function dialog box na bukas para sa susunod na hakbang sa tutorial.

07 ng 10

Pag-angkop sa COLUMN Function

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

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 walang pag-edit ng aming lookup formula - ito ay kung saan ang COLUMN Ang pagpapaandar ay nasa.

Sa pagpasok nito bilang numero ng index ng haligi argumento, ito ay magbabago habang ang lookup formula ay kinopya mula sa cell D2 sa mga cell E2 at F2 mamaya sa tutorial.

Nesting Function

Ang COLUMN function, samakatuwid, ay gumaganap bilang VLOOKUP's numero ng index ng haligi argumento; ito ay natapos sa pamamagitan ng nesting ang COLUMN function sa loob ng VLOOKUP nasa Col_index_num linya ng dialog box.

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. Ang COLUMN Ang function, samakatuwid, ay dapat na manu-manong ipinasok sa Col_index_num linya. Ang COLUMN Ang pag-andar ay may isang argument lamang - ang Sanggunian argumento na isang reference ng cell.

Pagpili ng Sangguniang Pagtuturo ng COLUMN Function

Ang COLUMN Ang gawain ng pag-andar ay upang ibalik ang bilang ng haligi na ibinigay bilang Sanggunian argumento. Sa madaling salita, binago nito ang haligi ng hanay sa isang numero na may haligi A bilang unang haligi, haligi B ang pangalawa at iba pa.

Dahil ang unang larangan ng data, kung ano ang nais naming ibalik ay ang presyo ng item, na nasa column na dalawang ng talahanayan ng data. Maaari naming piliin ang sanggunian ng cell para sa anumang cell haligi B bilang ang Sanggunian Argumento upang makuha ang numero 2 para sa Col_index_num argumento.

1. Nasa VLOOKUP function dialog box, mag-click sa Col_index_num linya.
2. I-type ang pangalan ng function haligi sinusundan ng isang bukas na round bracket.
3. Mag-click sa cell B1 sa worksheet upang ipasok ang sangguniang cell na iyon bilang Sanggunian argumento.
4. Mag-type ng pagsasara ng round bracket upang makumpleto ang COLUMN function
5. Iwanan ang VLOOKUP function dialog box na bukas para sa susunod na hakbang sa tutorial.

08 ng 10

Pagpasok sa VLOOKUP Range Lookup Argument

VLOOKUP's Range_lookup Ang argumento ay isang lohikal na halaga (TRUE o Mali) na nagpapahiwatig kung gusto mo VLOOKUP upang mahanap ang isang eksaktong o isang tinatayang tugma sa Lookup_value.

• Kung TRUE o kung ang argumentong ito ay tinanggal: VLOOKUP nagbabalik ng eksaktong tugma sa Lookup_valueO, kung ang eksaktong tugma ay hindi natagpuan, VLOOKUP nagbabalik ang susunod na pinakamalaking halaga. Para sa formula na gawin ito, ang data sa unang haligi ng Table_array Dapat na pinagsunod-sunod sa pataas na pagkakasunud-sunod.
• Kung Mali: VLOOKUP gagamitin lamang ang 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, a # N / A bumalik ang error.

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

1. Mag-click sa Range_lookup linya sa dialog box.
2. I-type ang salita Mali sa linyang ito upang ipahiwatig na gusto natin VLOOKUP upang maibalik ang eksaktong tugma para sa data na hinahanap namin.
3. Mag-click OK upang makumpleto ang lookup formula at malapit na dialog box.
4. Dahil hindi pa namin ipinasok ang pamantayan sa lookup sa cell D2 isang # N / A error ay naroroon 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 Lookup Formula

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 haligi 2, 3, at 4 ng talahanayan ng data - iyon ay ang presyo, ang numero ng bahagi, at ang pangalan ng tagapagtustos kapag nagpasok kami ng isang bahagyang 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.

Bilang ang formula ay kinopya, i-update ng Excel ang kamag-anak na sanggunian ng cell sa COLUMN function (cell B1) upang mapakita ang bagong lokasyon ng formula.

Gayundin, ang Excel ay hindi nagbabago ng ganap na reference ng cell $ D $ 2 at ang pinangalanang hanay Table 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 ang Handle.

1. Mag-click sa cell E2, kung saan matatagpuan ang lookup formula, upang gawin itong aktibong cell.
2. Ilagay ang pointer ng mouse sa ibabaw ng itim na parisukat sa ibabang kanang sulok - ang pointer ay magbabago sa isang plus sign upang ipakilala ang hawakan ng punan.
3. I-click ang kaliwang pindutan ng mouse at i-drag ang hawakan ng punan sa kabuuan cell G2.
4. Paglabas ang pindutan ng mouse at cell F3 dapat maglaman ng dalawang-dimensional na lookup formula.
5. Kung tama ang ginagawa, mga cell F2 at G2 dapat na ngayon ay naglalaman din ng # N / A error na naroroon 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 nais mong makuha sa Lookup_value cell (D2) at pindutin ang Ipasok susi 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.

1. Mag-click sa cell D2 sa worksheet.
2. Uri Widget sa cell D2 at pindutin ang Ipasok susi sa keyboard.
3. Ang sumusunod na impormasyon ay dapat na ipapakita sa mga cell E2 sa G2.
   1. E2: $ 14.76 - ang presyo ng isang widget
   2. F2: PN-98769 - ang numero ng bahagi para sa isang widget
   3. G2: Mga Widget Inc. - ang pangalan ng supplier para sa mga widget
4. Subukan ang VLOOKUP array formula sa pamamagitan ng pag-type ng pangalan ng iba pang mga bahagi sa cell D2 at pagmamasid sa mga resulta sa mga cell E2 sa G2