Paano Gumawa ng Excel Lookup Formula na May Maramihang Pamantayan

2024 May -akda: Abigail Brown | [email protected]. Huling binago: 2024-01-07 19:07

Ano ang Dapat Malaman

• Una, gumawa ng INDEX function, pagkatapos ay simulan ang nested MATCH function sa pamamagitan ng paglalagay ng Lookup_value argument.
• Susunod, idagdag ang Lookup_array argument na sinusundan ng Match_type argument, pagkatapos ay tukuyin ang hanay ng column.
• Pagkatapos, gawing array formula ang nested function sa pamamagitan ng pagpindot sa Ctrl+ Shift+ Enter. Panghuli, idagdag ang mga termino para sa paghahanap sa worksheet.

Ipinapaliwanag ng artikulong ito kung paano gumawa ng lookup formula na gumagamit ng maraming pamantayan sa Excel upang maghanap ng impormasyon sa isang database o talahanayan ng data sa pamamagitan ng paggamit ng array formula. Kasama sa array formula ang paglalagay ng MATCH function sa loob ng INDEX function. Saklaw ng impormasyon ang Excel para sa Microsoft 365, Excel 2019, Excel 2016, Excel 2013, Excel 2010, at Excel para sa Mac.

Sundan Kasama ang Tutorial

Upang sundin ang mga hakbang sa tutorial na ito, ilagay ang sample na data sa mga sumusunod na cell, tulad ng ipinapakita sa larawan sa ibaba. Ang mga hilera 3 at 4 ay iniwang blangko upang i-accommodate ang array formula na ginawa sa panahon ng tutorial na ito. (Tandaan na hindi kasama sa tutorial na ito ang pag-format na makikita sa larawan.)

• Ilagay ang nangungunang hanay ng data sa mga cell D1 hanggang F2.
• Ilagay ang pangalawang hanay sa mga cell D5 hanggang F11.

Gumawa ng INDEX Function sa Excel

Ang INDEX function ay isa sa ilang mga function sa Excel na may maraming mga form. Ang function ay may Array Form at Reference Form. Ibinabalik ng Array Form ang data mula sa isang database o talahanayan ng data. Ang Reference Form ay nagbibigay ng cell reference o lokasyon ng data sa talahanayan.

Sa tutorial na ito, ang Array Form ay ginagamit upang mahanap ang pangalan ng supplier para sa mga titanium widget, sa halip na ang cell reference sa supplier na ito sa database.

Sundin ang mga hakbang na ito para gawin ang INDEX function:

1. Piliin ang cell F3 upang gawin itong aktibong cell. Ang cell na ito ay kung saan ilalagay ang nested function.

2. Pumunta sa Formulas.

   

3. Piliin ang Lookup & Reference upang buksan ang drop-down list ng function.
4. Piliin ang INDEX para buksan ang Select Arguments dialog box.
5. Pumili ng array, row_num, column_num.
6. Piliin ang OK upang buksan ang Mga Argumento ng Paggana dialog box. Sa Excel para sa Mac, bubukas ang Formula Builder.
7. Ilagay ang cursor sa Array text box.

8. I-highlight ang mga cell D6 hanggang F11 sa worksheet upang ipasok ang range sa dialog box.

   Iwanang bukas ang dialog box ng Mga Pangangatwiran ng Function. Hindi pa tapos ang formula. Kukumpletuhin mo ang formula sa mga tagubilin sa ibaba.

   

Simulan ang Nested MATCH Function

Kapag nesting ang isang function sa loob ng isa pa, hindi posibleng buksan ang pangalawa, o nested, formula builder ng function para ilagay ang mga kinakailangang argumento. Dapat ilagay ang nested function bilang isa sa mga argumento ng unang function.

Kapag manu-mano ang paglalagay ng mga function, ang mga argumento ng function ay pinaghihiwalay sa isa't isa ng kuwit.

Ang unang hakbang para ipasok ang nested MATCH function ay ilagay ang Lookup_value argument. Ang Lookup_value ay ang lokasyon o cell reference para sa termino para sa paghahanap na itugma sa database.

Ang Lookup_value ay tumatanggap lamang ng isang pamantayan o termino sa paghahanap. Upang maghanap ng maraming pamantayan, palawigin ang Lookup_value sa pamamagitan ng pagsasama-sama, o pagsali, ng dalawa o higit pang cell reference gamit ang simbolo ng ampersand (&).

1. Sa Function Arguments dialog box, ilagay ang cursor sa Row_num text box.
2. Enter MATCH(.
3. Piliin ang cell D3 upang ipasok ang cell reference na iyon sa dialog box.
4. Ilagay ang & (ang ampersand) pagkatapos ng cell reference D3 upang magdagdag ng pangalawang cell reference.
5. Piliin ang cell E3 upang ipasok ang pangalawang cell reference.

6. Enter , (isang kuwit) pagkatapos ng cell reference E3 para kumpletuhin ang entry ng Lookup_value argument ng MATCH function.

   

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

Kumpletuhin ang Nested MATCH Function

Ang hakbang na ito ay sumasaklaw sa pagdaragdag ng Lookup_array argument para sa nested MATCH function. Ang Lookup_array ay ang hanay ng mga cell na hinahanap ng MATCH function upang mahanap ang Lookup_value argument na idinagdag sa nakaraang hakbang ng tutorial.

Dahil dalawang field ng paghahanap ang natukoy sa Lookup_array argument, dapat ding gawin ito para sa Lookup_array. Ang MATCH function ay naghahanap lamang ng isang array para sa bawat terminong tinukoy. Para maglagay ng maraming array, gamitin ang ampersand para pagsama-samahin ang mga array.

1. Ilagay ang cursor sa dulo ng data sa Row_num text box. Lumilitaw ang cursor pagkatapos ng kuwit sa dulo ng kasalukuyang entry.
2. I-highlight ang mga cell D6 hanggang D11 sa worksheet para makapasok sa range. Ang hanay na ito ay ang unang array na hinahanap ng function.
3. Ilagay ang & (isang ampersand) pagkatapos ng mga cell reference D6:D11. Ang simbolo na ito ay nagdudulot sa function na maghanap ng dalawang array.
4. I-highlight ang mga cell E6 hanggang E11 sa worksheet para makapasok sa range. Ang hanay na ito ay ang pangalawang array na hinahanap ng function.

5. Enter , (isang kuwit) pagkatapos ng cell reference E3 upang kumpletuhin ang entry ng Lookup_array argument ng MATCH function.

   

6. Iwanang bukas ang dialog box para sa susunod na hakbang sa tutorial.

Idagdag ang MATCH Type Argument

Ang pangatlo at huling argumento ng MATCH function ay ang Match_type argument. Sinasabi ng argumentong ito sa Excel kung paano itugma ang Lookup_value sa mga value sa Lookup_array. Ang mga available na pagpipilian ay 1, 0, o -1.

Ang argumentong ito ay opsyonal. Kung aalisin ito, gagamitin ng function ang default na value na 1.

• Kung Match_type=1 o inalis, hahanapin ng MATCH ang pinakamalaking value na mas mababa sa o katumbas ng Lookup_value. Ang data ng Lookup_array ay dapat ayusin sa pataas na pagkakasunud-sunod.
• Kung Match_type=0, hahanapin ng MATCH ang unang value na katumbas ng Lookup_value. Maaaring pag-uri-uriin ang data ng Lookup_array sa anumang pagkakasunud-sunod.
• Kung Match_type=-1, hahanapin ng MATCH ang pinakamaliit na value na mas malaki sa o katumbas ng Lookup_value. Ang data ng Lookup_array ay dapat ayusin sa pababang pagkakasunud-sunod.

Ilagay ang mga hakbang na ito pagkatapos na ilagay ang kuwit sa nakaraang hakbang sa Row_num na linya sa INDEX function:

1. Enter 0 (a zero) pagkatapos ng kuwit sa Row_num text box. Ang numerong ito ay nagiging sanhi ng nested function na ibalik ang mga eksaktong tugma sa mga terminong inilagay sa mga cell D3 at E3.

2. Enter ) (isang closing round bracket) para makumpleto ang MATCH function.

   

3. Iwanang bukas ang dialog box para sa susunod na hakbang sa tutorial.

Tapusin ang INDEX Function

Ang MATCH function ay tapos na. Oras na para lumipat sa Column_num text box ng dialog box at ilagay ang huling argument para sa INDEX function. Sinasabi ng argumentong ito sa Excel na ang numero ng column ay nasa hanay na D6 hanggang F11. Ang hanay na ito ay kung saan nahahanap nito ang impormasyong ibinalik ng function. Sa kasong ito, isang supplier para sa titanium widgets.

1. Ilagay ang cursor sa Column_num text box.

2. Ilagay ang 3 (ang numerong tatlo). Sinasabi ng numerong ito sa formula na maghanap ng data sa ikatlong column ng hanay na D6 hanggang F11.

   

3. Iwanang bukas ang dialog box para sa susunod na hakbang sa tutorial.

Gumawa ng Array Formula

Bago isara ang dialog box, gawing array formula ang nested function. Binibigyang-daan ng array na ito ang function na maghanap ng maraming termino sa talahanayan ng data. Sa tutorial na ito, dalawang termino ang itinutugma: Mga Widget mula sa column 1 at Titanium mula sa column 2.

Upang gumawa ng array formula sa Excel, pindutin ang CTRL, SHIFT, at ENTERna susi nang sabay-sabay. Kapag pinindot, ang function ay napapalibutan ng mga kulot na brace, na nagpapahiwatig na ang function ay isang array na ngayon.

1. Piliin ang OK upang isara ang dialog box. Sa Excel para sa Mac, piliin ang Done.
2. Piliin ang cell F3 upang tingnan ang formula, pagkatapos ay ilagay ang cursor sa dulo ng formula sa Formula Bar.
3. Para i-convert ang formula sa array, pindutin ang CTRL+ SHIFT+ ENTER.
4. Lumilitaw ang A N/A error sa cell F3. Ito ang cell kung saan ipinasok ang function.

5. Lalabas ang N/A error sa cell F3 dahil blangko ang mga cell D3 at E3. Ang D3 at E3 ay ang mga cell kung saan tinitingnan ng function ang Lookup_value. Pagkatapos maidagdag ang data sa dalawang cell na ito, ang error ay papalitan ng impormasyon mula sa database.

   

Idagdag ang Pamantayan sa Paghahanap

Ang huling hakbang ay idagdag ang mga termino para sa paghahanap sa worksheet. Ang hakbang na ito ay tumutugma sa mga terminong Mga Widget mula sa column 1 at Titanium mula sa column 2.

Kung makakita ang formula ng tugma para sa parehong termino sa naaangkop na mga column sa database, ibinabalik nito ang value mula sa ikatlong column.

1. Pumili ng cell D3.
2. Enter Widgets.
3. Pumili ng cell E3.
4. Type Titanium, at pindutin ang Enter.
5. Ang pangalan ng supplier, ang Widgets Inc., ay lumalabas sa cell F3. Ito lang ang nakalistang supplier na nagbebenta ng Titanium Widgets.

6. Pumili ng cell F3. Lumilitaw ang function sa formula bar sa itaas ng worksheet.

   {=INDEX(D6:F11, MATCH(D3&E3, D6:D11&E6:E11, 0), 3)}
   Sa halimbawang ito, isa lang ang supplier para sa mga titanium widget. Kung mayroong higit sa isang supplier, ang supplier na unang nakalista sa database ay ibabalik ng function.