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 sunud-sunod na halimbawa ng paglikha ng lookup formula na gumagamit ng maramihang pamantayan upang makahanap ng isang supplier ngMga Widget na titan sa isang sample na database. Sundan ka upang malaman kung gaano kadali ang lumikha ng iyong sariling mga custom na lookup formula.
Ihanda ang Iyong Workbook sa Excel sa Data
Upang masunod ang mga hakbang sa tutorial na ito, kakailanganin mong magsimula sa pamamagitan ng pagpasok ng sample na data sa mga sumusunod na cell, tulad ng ipinapakita sa imahe sa itaas. Ang mga hilera 3 at 4 ay iniwang blangko upang mapaunlakan ang array formula na nilikha sa panahon ng tutorial na ito.
- Ipasok ang pinakamataas na hanay ng data sa mga cell D1 sa F2
- Ipasok ang ikalawang hanay sa mga cell D5 sa F11
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 08Gumawa ng INDEX Function sa Excel
Ang INDEX Ang pag-andar ay isa sa ilang sa Excel na may maraming mga form. Ang function ay may Form ng Array at isang Form ng Sanggunian. 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 supplier para sa mga widget ng titan kaysa sa cell reference sa supplier na ito sa aming database. Sundin ang mga hakbang na ito upang likhain ang iyong INDEX Function:
- Mag-click sa cell F3 upang gawin itong aktibong cell - kung saan ipapasok namin ang nested function.
- Mag-click sa Formula tab ng menu ng laso.
- Pumili Paghahanap at Sanggunian mula sa laso bar upang buksan ang function drop down.
- Mag-click sa INDEX sa listahan upang ilabas ang Piliin ang Mga Argumento dialog box.
- Piliin ang array, row_num, col_num opsyon sa dialog box.
- Mag-click OK upang buksan ang kahon ng kahon ng INDEX function; bubuksan nito ang Formula Builder sa Excel.
- Nasa Formula Builder, mag-click sa Array linya.
- I-highlight ang mga cell D6 sa F11, sa pamamagitan ng pag-click at pag-drag, sa worksheet upang makapasok sa range sa tagabuo.
Sa mas lumang mga bersyon ng Excel ang Formula Builder ay papalitan ng Mga Pangangatwiran ng Function window. Ilapat ang parehong mga hakbang tulad ng nabanggit sa tutorial na ito sa window.
03 ng 08Simulan ang Nested MATCH Function
Kapag nesting isang function sa loob ng isa pang ito ay hindi posible na buksan ang tagabuo ng pangalawang o nakapugad na function ng nested o ipasok ang mga kinakailangang mga argumento. Ang nested function ay dapat na ma-type sa bilang isa sa mga pinagsunod-sunod sa unang pag-andar.
Kapag nang manu-manong pagpasok ng mga function, ang mga argumento ng pag-andar ay pinaghihiwalay mula sa bawat isa sa pamamagitan ng isang kuwit.
Ang unang hakbang sa pagpasok ng nested function na MATCH ay upang ipasok ang Lookup_value argumento. 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; tang kanyang ginawa sa pamamagitan ng pag-concatenate o pagsali sa dalawa o higit pang mga sanggunian ng cell na magkasama gamit ang simbolong ampersand - &.
- Nasa Formula Builder, mag-click sa Row_num linya.
- I-type ang pangalan ng function MATCH na sinusundan ng isang bukas na round bracket.
- Mag-click sa cell D3 upang ipasok ang sangguniang cell na iyon sa dialog box.
- Mag-type ng isang ampersand pagkatapos ng cell reference D3 upang magdagdag ng pangalawang reference ng cell.
- Mag-click sa cell E3 upang ipasok ang pangalawang cell reference na ito.
- Mag-type ng kuwit pagkatapos ng reference ng cell E3 upang makumpleto ang pagpasok ng function ng MATCH Lookup_value argumento.
Sa huling hakbang ng tutorial, ang aming Lookup_values ay papasok sa mga cell D3 at E3 ng worksheet.
04 ng 08Kumpletuhin ang Nested MATCH Function
Sinasaklaw ng hakbang na ito ang pagdaragdag ng Lookup_array argumento para sa nested MATCH function. 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 paghahanap ng MATCH ay naghahanap lamang ng isang array para sa bawat termino na tinukoy, sa gayon ay pumasok sa maraming arrays na muli naming ginagamit ang ampersand upang pagsamahin ang mga arrays nang sama-sama.
Ang mga hakbang na ito ay ipasok pagkatapos ng kuwit na ipinasok sa nakaraang hakbang sa Row_num linya sa INDEX function.
- Mag-click sa Row_num line pagkatapos ng kuwit upang ilagay ang punto ng pagpapasok sa dulo ng kasalukuyang entry.
- I-highlight ang mga cell D6 sa D11 sa worksheet upang makapasok sa range - ito ang unang array na ang function ay upang maghanap.
- Mag-type ng ampersand pagkatapos ng mga reference sa cell D6: D11 dahil nais namin ang pag-andar upang maghanap ng dalawang arrays.
- I-highlight ang mga cell E6 sa E11 sa worksheet upang makapasok sa hanay - ito ang ikalawang hanay na ang pag-andar ay upang maghanap.
- Mag-type ng kuwit pagkatapos ng reference ng cell E3 upang makumpleto ang pagpasok ng MATCH function na Lookup_array argumento.
- Iwanan ang INDEX function dialog box na bukas para sa susunod na hakbang sa tutorial.
Idagdag ang MATCH Type Argument
Ang pangatlong at pangwakas na argument ng function na MATCH ay ang Match_type argumento; Sinasabi nito sa Excel kung paano itugma ang Lookup_value na may mga halaga sa Lookup_array - ang magagamit na 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 tinanggal: MATCH ay nahanap 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: Natuklasan 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: Nakikita 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.
Ang mga hakbang na ito ay ipasok pagkatapos ng kuwit na ipinasok sa nakaraang hakbang sa Row_num linya sa INDEX function.
- Kasunod ng kuwit sa Row_num linya, i-type ang zero - 0 - dahil gusto namin ang nested function upang ibalik ang eksaktong mga tugma sa mga tuntunin na ipinasok namin sa mga cell D3 at E3.
- Mag-type ng isang pagsasara ng round bracket - ) - upang makumpleto ang MATCH function.
- Iwanan ang Formula Builder buksan para sa susunod na hakbang sa tutorial.
Tinatapos ang INDEX Function
Ngayon na ang MATCH ang pag-andar ay tapos na namin ilipat sa ikatlong linya ng Formula Builder at ipasok ang huling argumento para sa INDEX function. Ang pangatlong at pangwakas na argumento ay ang Column_num argumento na nagsasabi sa Excel ang numero ng hanay sa hanay D6 sa F11; ito ay kung saan makikita ang impormasyon na gusto naming ibalik sa pamamagitan ng pag-andar. Sa kasong ito, isang supplier para sa titan widgets.
- Mag-click sa Column_num linya.
- Ipasok ang numero ng tatlo - 3 - sa linyang ito dahil hinahanap namin ang data sa ikatlong haligi ng saklaw D6 sa F11.
- Muli, iwan ang Formula Builder buksan para sa susunod na hakbang sa tutorial.
Paglikha ng Formula ng Array
Bago isara ang Formula Builder, kailangan naming i-on ang aming nested function sa isang array formula; ito ay kung ano ang nagbibigay-daan sa aming pag-andar upang maghanap ng maraming termino sa talaan ng data. Sa tutorial na ito, hinahanap namin 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 mga key sa keyboard nang sabay. Sa sandaling pinindot, ang function ay napapalibutan ng mga kulot na brace, na nagpapahiwatig na ang pag-andar ay isang array na ngayon.
- Isara ang Formula Builder sa pamamagitan ng pag-click sa Tapos na na pindutan.
- Susunod, piliin ang cell F4 at pindutin ang Ipasok susi upang tingnan ang formula.
- Upang i-convert ang Formula sa isang array, sabay-sabay pindutin CTRL + SHIFT + ENTER sa iyong keyboard.
- Kung tama ang ginagawa a # N / A ang error ay lilitaw sa cell F3 - ang cell kung saan namin ipinasok ang function.
- Ang # N / A lumilitaw ang error sa cell F3 dahil ang mga cell D3 at E3 ay blangko. Ang D3 at E3 ay ang mga cell kung saan sinabi namin ang function upang mahanap ang Lookup_values. Kapag ang data ay idinagdag sa dalawang mga cell na ito, ang error ay papalitan ng impormasyon mula sa database.
Pagdagdag ng Search Criteria
Sa wakas, idaragdag namin ang mga termino para sa paghahanap sa aming worksheet. Tulad ng nabanggit sa nakaraang hakbang, kami ay naghahanap upang tumugma sa mga tuntunin 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.
- Mag-click sa cell D3.
- Uri Mga Widget at pindutin ang Ipasok susi sa keyboard.
- Mag-click sa cell E3.
- Uri Titan at pindutin ang Ipasok susi sa keyboard.
- Ang pangalan ng supplier Mga Widget Inc. ay dapat na lumitaw sa cell F3 - ang lokasyon ng function dahil ito ay ang tanging supplier na nakalista na nagbebenta Titanium Widgets.
- Kapag nag-click ka sa cell F3 ang kumpletong function ay lilitaw sa formula bar sa itaas ng worksheet.
{= INDEX (D6: F11, MATCH (D3 & E3, D6: D11 & E6: E11, 0), 3)}
Sa aming halimbawa, mayroon lamang isang supplier para sa titanium widgets. Kung mayroong higit sa isang tagapagtustos, ang tagapagtustos na unang nakalista sa database ay ibinalik ng function.
