Excel Lookup Formula na may Maramihang Pamantayan

Sa pamamagitan ng paggamit ng array formula sa Excel maaari kaming lumikha ng lookup formula na gumagamit ng maramihang pamantayan upang makahanap ng impormasyon sa isang database o talaan ng data.

Ang array formula ay nagsasangkot ng paghuhugas ng function ng MATCH sa loob ng function na INDEX .

Kasama sa tutorial na ito ang isang hakbang-hakbang na halimbawa ng paglikha ng lookup formula na gumagamit ng maramihang pamantayan upang makahanap ng isang tagapagtustos ng Mga Widget na titan sa isang sample na database.

Ang pagsunod sa mga hakbang sa mga paksa ng tutorial sa ibaba ay lumalakad sa iyo sa paglikha at paggamit ng formula na nakikita sa larawan sa itaas.

01 ng 09

Pagpasok sa 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 F2
• Ipasok ang ikalawang hanay sa mga cell D5 hanggang F11

Ang mga hilera 3 at 4 ay iniwang blangko upang mapaunlakan ang array formula na nilikha sa panahon ng tutorial na ito.

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 pagpipilian sa pag-format na katulad ng nakikita sa itaas ay magagamit sa Tutorial sa Formatting Basic Excel na ito.

02 ng 09

Simula sa INDEX Function

Ang INDEX function ay isa sa ilang sa Excel na may maraming mga form. Ang function ay may isang Array Form at isang Reference Form .

Ang Array Form ay nagbabalik ng aktwal na data mula sa isang database o talaan ng data, habang binibigyan ka ng Reference Form ng cell reference o lokasyon ng data sa talahanayan.

Sa tutorial na ito gagamitin namin ang Form ng Array dahil gusto naming malaman ang pangalan ng isang supplier para sa titanium widgets kaysa sa cell reference sa supplier na ito sa aming database.

Ang bawat form ay may iba't ibang listahan ng mga argumento na dapat piliin bago simulan ang pag-andar.

Mga Hakbang sa Tutorial

1. Mag-click sa cell F3 upang gawin itong aktibong cell . Ito ay kung saan namin ipasok ang nested function.
2. Mag-click sa tab na Formula ng laso menu.
3. Piliin ang Lookup at Sanggunian mula sa laso upang buksan ang drop down na listahan ng function.
4. Mag-click sa INDEX sa listahan upang ilabas ang kahon ng dialogo ng Piliin Argument .
5. Piliin ang array, row_num, col_num na opsyon sa dialog box.
6. I-click ang OK upang buksan ang kahon ng dialog ng function INDEX.

03 ng 09

Pagpasok sa INDEX Function Array Argument

Ang unang argument na kinakailangan ay ang Array argument. Tinutukoy ng argumentong ito ang hanay ng mga selula upang maghanap para sa ninanais na data.

Para sa tutorial na ito, ang argument na ito ay ang aming sample na database .

Mga Hakbang sa Tutorial

1. Sa dialog box ng function ng INDEX, mag-click sa linya ng Array .
2. I-highlight ang mga cell D6 hanggang F11 sa worksheet upang makapasok sa hanay sa dialog box.

04 ng 09

Simula sa Nested MATCH Function

Kapag nesting isang function sa loob ng isa pang ito ay hindi posible na buksan ang dialog box ng pangalawang o nakapugad na function na upang ilagay ang mga kinakailangang mga argumento .

Ang nested na function ay dapat na ma-type sa bilang isa sa mga argumento ng unang pag-andar.

Sa tutorial na ito, ang nested na pag-andar ng MATCH at ang mga argumento nito ay ipapasok sa pangalawang linya ng kahon sa dialog ng INDEX function - ang linya ng Row_num .

Mahalagang tandaan na, kapag pinapasok ang mga pag-andar nang manu-mano, ang mga argumento ng pag-andar ay pinaghihiwalay mula sa bawat isa sa pamamagitan ng isang kuwit "," .

Pagpasok sa Lookup_value Argument ng Function ng MATCH

Ang unang hakbang sa pagpasok ng nested function MATCH ay upang ipasok ang Lookup_value argument.

Ang Lookup_value ay ang lokasyon o reference ng cell para sa terminong ginamit sa paghahanap na gusto naming tumugma sa database.

Karaniwan ang Lookup_value ay tumatanggap lamang ng isang pamantayan o term sa paghahanap. Upang maghanap ng maraming pamantayan, dapat naming pahabain ang Lookup_value .

Ginagawa ito sa pamamagitan ng pag- concatenate o pagsali sa dalawa o higit pang mga sanggunian ng cell na magkasama gamit ang ampersand na simbolo " & ".

Mga Hakbang sa Tutorial

1. Sa dialog box ng function na INDEX, mag-click sa linya ng Row_num .
2. I-type ang pagtutugma ng pangalan ng function na sinusundan ng isang bukas na round bracket " ( "
3. Mag-click sa cell D3 upang mapasok ang cell na reference sa dialog box.
4. Mag-type ng isang ampersand " & " pagkatapos ng cell reference D3 upang magdagdag ng pangalawang cell reference.
5. Mag-click sa cell E3 upang ipasok ang pangalawang cell reference sa dialog box.
6. Mag-type ng kuwit "," pagkatapos ng cell reference E3 upang makumpleto ang pagpasok ng Lookup_value argument ng MATCH function.
7. Iwanan ang dialog box ng INDEX function bukas para sa susunod na hakbang sa tutorial.

Sa huling hakbang ng tutorial, ang Lookup_values ​​ay ipapasok sa mga cell D3 at E3 ng worksheet.

05 ng 09

Pagdaragdag ng Lookup_array para sa Function ng MATCH

Sinasaklaw ng hakbang na ito ang pagdaragdag ng argumento ng Lookup_array para sa nested function na MATCH.

Ang Lookup_array ay ang hanay ng mga cell na ang MATCH function ay maghanap upang mahanap ang Lookup_value argument idinagdag sa nakaraang hakbang ng tutorial.

Dahil nakilala namin ang dalawang mga patlang ng paghahanap sa Lookup_array argument na dapat naming gawin ang parehong para sa Lookup_array . Ang pag-andar ng MATCH ay naghahanap lamang ng isang array para sa bawat termino na tinukoy.

Upang magpasok ng maramihang mga arrays muli naming gagamitin ang ampersand " & " upang i- concatenate ang mga arrays nang sama-sama.

Mga Hakbang sa Tutorial

Ang mga hakbang na ito ay ipasok pagkatapos ng kuwit na ipinasok sa nakaraang hakbang sa linya ng Row_num sa dialog box ng INDEX function.

1. Mag-click sa Row_num line pagkatapos ng kuwit upang ilagay ang insertion point sa dulo ng kasalukuyang entry.
2. I-highlight ang mga cell D6 hanggang D11 sa worksheet upang makapasok sa range. Ito ang unang array na ang function ay upang maghanap.
3. Mag-type ng isang ampersand " & " pagkatapos ng mga reference sa cell D6: D11 dahil nais namin ang function upang maghanap ng dalawang arrays.
4. I-highlight ang mga cell E6 hanggang E11 sa worksheet upang makapasok sa range. Ito ang pangalawang array na ang function ay upang maghanap.
5. Mag-type ng kuwit "," pagkatapos ng cell reference E3 upang makumpleto ang entry ng argumento ng Lookup MATCH ng MATCH.
6. Iwanan ang dialog box ng INDEX function bukas para sa susunod na hakbang sa tutorial.

06 ng 09

Pagdaragdag ng Uri ng Pagtutugma at Pagkumpleto ng MATCH Function

Ang pangatlong at panghuling argument ng function na MATCH ay ang Match_type argument.

Ang argument na ito ay nagsasabi sa Excel kung paano itugma ang Lookup_value na may mga halaga sa Lookup_array. Ang mga pagpipilian ay: 1, 0, o -1.

Ang argument na ito ay opsyonal. Kung ito ay tinanggal na ang function ay gumagamit ng default na halaga ng 1.

• kung ang Match_type = 1 o ay tinanggal: MATCH ay hinahanap ang pinakamalaking halaga na mas mababa sa o pantay sa Lookup_value. Ang Lookup_array data ay dapat na pinagsunod - sunod sa pataas na pagkakasunud-sunod.
• kung ang match_type = 0: hinahanap ng MATCH ang unang halaga na eksaktong katumbas ng Lookup_value. Ang Lookup_array data ay maaaring pinagsunod-sunod sa anumang pagkakasunud-sunod.
• kung ang Match_type = -1: hinahanap ng MATCH ang pinakamaliit na halaga na mas malaki o katumbas sa Lookup_value. Ang Lookup_array data ay dapat na pinagsunod-sunod sa pababang pagkakasunud-sunod.

Mga Hakbang sa Tutorial

Ang mga hakbang na ito ay ipasok pagkatapos ng kuwit na ipinasok sa nakaraang hakbang sa linya ng Row_num sa dialog box ng INDEX function.

1. Kasunod ng comma sa linya Row_num , mag-type ng zero " 0 " dahil nais namin ang nested function na ibalik ang eksaktong mga katugma sa mga term na ipinasok namin sa mga cell D3 at E3.
2. Mag-type ng pagsasara ng round bracket " ) " upang makumpleto ang pag-andar ng MATCH.
3. Iwanan ang dialog box ng INDEX function bukas para sa susunod na hakbang sa tutorial.

07 ng 09

Bumalik sa INDEX Function

Ngayon na ang MATCH function ay tapos na kami ay lumipat sa ikatlong linya ng bukas na kahon ng dialogo at ipasok ang huling argument para sa INDEX function.

Ang pangatlong at pangwakas na argument ay ang argumento ng Column_num na nagsasabi sa Excel ang numero ng haligi sa saklaw ng D6 hanggang F11 kung saan makikita ang impormasyon na gusto naming ibalik ng function. Sa kasong ito, isang tagapagtustos para sa mga widget ng titan .

Mga Hakbang sa Tutorial

1. Mag-click sa linya ng Column_num sa dialog box.
2. Ipasok ang numero ng tatlong " 3 " (walang mga panipi) sa linyang ito dahil hinahanap namin ang data sa ikatlong haligi ng hanay D6 hanggang F11.
3. Huwag I-click ang OK o isara ang dialog box ng function na INDEX. Dapat itong manatiling bukas para sa susunod na hakbang sa tutorial - ang paglikha ng array formula .

08 ng 09

Paglikha ng Formula ng Array

Bago isara ang dialog box na kailangan namin upang i-on ang aming nested function sa isang array formula .

Ang array formula ay kung ano ang nagbibigay-daan ito sa paghahanap para sa maraming mga termino sa talahanayan ng data. Sa tutorial na ito kami ay naghahanap upang tumugma sa dalawang termino: Mga Widget mula sa haligi 1 at titan mula sa haligi 2.

Ang paglikha ng isang array formula sa Excel ay ginagawa sa pamamagitan ng pagpindot sa CTRL , SHIFT , at ENTER na mga key sa keyboard sa parehong oras.

Ang epekto ng pagpindot sa mga susi na ito ay magkasama upang palibutan ang pag-andar na may mga kulot na brace: {} na nagpapahiwatig na ito ay isang array na formula.

Mga Hakbang sa Tutorial

1. Sa nakumpletong dialog box na bukas pa rin mula sa nakaraang hakbang ng tutorial na ito, pindutin nang matagal ang CTRL at SHIFT key sa keyboard at pagkatapos ay pindutin at bitawan ang ENTER key.
2. Kung tapos na nang tama, ang kahon ng dialogo ay isasara at isang # N / A error ay lilitaw sa cell F3 - ang cell kung saan kami pumasok sa function.
3. Ang # N / A error ay lilitaw sa cell F3 dahil ang mga cell D3 at E3 ay blangko. D3 at E3 ay ang mga cell kung saan sinabi namin ang function upang mahanap ang Lookup_values ​​sa hakbang 5 ng tutorial. Kapag ang data ay idinagdag sa dalawang mga cell na ito, ang error ay papalitan ng impormasyon mula sa database .

09 ng 09

Pagdagdag ng Search Criteria

Ang huling hakbang sa tutorial ay upang idagdag ang mga termino para sa paghahanap sa aming worksheet.

Tulad ng nabanggit sa nakaraang hakbang, hinahanap namin upang tumugma sa mga termino Mga Widget mula sa haligi 1 at Titan mula sa haligi 2.

Kung, at kung lamang, nakikita ng aming formula ang isang tugma para sa parehong mga termino sa naaangkop na mga haligi sa database, ibabalik nito ang halaga mula sa ikatlong haligi.

Mga Hakbang sa Tutorial

1. Mag-click sa cell D3.
2. I-type ang Mga Widget at pindutin ang Enter key sa keyboard.
3. Mag-click sa cell E3.
4. I-type ang Titan at pindutin ang Enter key sa keyboard.
5. Ang pangalan ng tagapagtustos Widgets Inc. ay dapat na lumitaw sa cell F3 - ang lokasyon ng function dahil ito ay ang tanging tagapagtustos na nakalista na nagbebenta ng Mga Widget na Titanium.
6. Kapag nag-click ka sa cell F3 ang kumpletong pag-andar
   {= INDEX (D6: F11, MATCH (D3 & E3, D6: D11 & E6: E11, 0), 3)}
   Lumilitaw sa formula bar sa itaas ng worksheet .

Tandaan: Sa aming halimbawa mayroong isang tagapagtustos lamang para sa mga widget ng titan. Kung mayroong higit sa isang tagapagtustos, ang tagapagtustos na unang nakalista sa database ay ibinalik ng function.