Formula Lookup Formula da Multiple Criteria

Ta hanyar amfani da matakan tsafta a cikin Excel za mu iya ƙirƙirar daftarin hanyar da take amfani da sharuɗɗan ma'auni don neman bayani a cikin wani tashar bayanai ko tebur na bayanan.

Tsarin lissafi ya haɗa da yin aikin MATCH a cikin aikin INDEX .

Wannan koyaswar ya haɗa da mataki na gaba misali na ƙirƙirar wata hanyar bincike wanda yayi amfani da ma'aunin ma'auni don neman mai sayen titanium Widgets a cikin samfurin samfurin.

Biye matakai a cikin darussan koyawa da ke ƙasa ke tafiya ta hanyar samarwa da yin amfani da samfurin da aka gani a cikin hoto a sama.

01 na 09

Shigar da Bayanan Tutorial

Mataki na farko a cikin koyawa shine shigar da bayanai a cikin takardar aikin Excel.

Don bi matakai a cikin koyawa shigar da bayanai da aka nuna a hoton da ke sama zuwa cikin wadannan kwayoyin .

• Shigar da kewayon bayanai a cikin sel D1 zuwa F2
• Shigar da jeri na biyu a cikin sel D5 zuwa F11

Rukunai na 3 da 4 an bar blank domin yada tsarin da aka tsara a wannan tutorial.

Koyarwar ba ta haɗa da tsarin da aka gani a cikin hoton ba, amma wannan ba zai tasiri yadda tsarin binciken yake aiki ba.

Za'a iya samun ƙarin bayani game da tsarin tsarawa kamar waɗanda aka gani a sama a wannan Basic Tutel Tutorial.

02 na 09

Fara aikin INDEX

Ayyukan INDEX ɗaya ne daga cikin 'yan kaɗan a Excel wanda ke da siffofin da yawa. Ayyukan suna da Formar Array da takardar Magana .

Fom ɗin Array ya sake dawo da ainihin bayanan daga bayanan bayanai ko tebur na bayanan, yayin da takardar Magana ya ba ku tantancewar tantanin halitta ko wuri na bayanai a cikin tebur.

A cikin wannan koyo za mu yi amfani da Array Form tun muna so mu san sunan mai sayarwa ga titanium widgets maimakon ma'anar tantancewar salula ga wannan mai samarwa a cikin database.

Kowace nau'i yana da jerin jayayya daban-daban waɗanda dole ne a zaba kafin su fara aiki.

Tutorial Steps

1. Danna kan salula F3 don sanya shi tantanin aiki . Wannan shi ne inda za mu shiga aikin da aka saka.
2. Danna maɓallin Formulas na shafin rubutun .
3. Zabi Binciken da Magana daga ribbon don bude jerin jerin ayyuka.
4. Danna kan INDEX cikin jerin don kawo akwatin maganganu na Zaɓaɓɓun Magana .
5. Zaɓi tsararren, row_num, zaɓi na col_num a cikin akwatin maganganu.
6. Danna Ya yi don buɗe akwatin maganganun INDEX.

03 na 09

Shigar da Sakamakon Jirgin Ƙungiyar INDEX

Bayani na farko da aka buƙata shi ne hujjar Array. Wannan hujja ta ƙayyade kewayon kwayoyin don a bincika bayanai da ake so.

Don wannan koyaswar wannan jayayya za ta kasance bayanan samfurinmu.

Tutorial Steps

1. A cikin akwatin maganganun INDEX, danna kan Array line.
2. Sanya sassa D6 zuwa F11 a cikin takarda don shigar da kewayon cikin akwatin maganganu.

04 of 09

Fara Ayyukan MATCH Nested

A lokacin da aikin nesting ɗaya a cikin wani ba zai yiwu a buɗe akwatin zane na biyu ko aikin da aka saka ba don shigar da muhawarar da ake bukata.

Dole ne a danna aikin da aka gwada a matsayin daya daga cikin muhawarar aikin farko.

A cikin wannan koyo, aikin MATCH da aka haɓaka da kuma muhawarar za a shiga cikin na biyu na akwatin maganin INDEX - Row_num line.

Yana da muhimmanci a lura cewa, lokacin da ka shiga ayyuka tare da hannu, za a rabu da muhawarar aiki ta juna ta hanyar takaddama "," .

Shigar da Magana na Lookup_value na MATCH

Mataki na farko a shigar da aikin MATCH da aka haɓaka ita ce shigar da dubawar Lookup_value .

A Lookup_value zai zama wuri ko tantancewar salula don kalmar bincike da muke son daidaita a cikin database.

Kullum al'amuran Lookup_value sun yarda da ka'idoji ɗaya kawai ko lokaci. Domin bincika ma'auni masu yawa, dole ne mu mika Lookup_value .

Anyi wannan ta hanyar yin amfani da mahimmanci ko shiga cikin haɗin tantanin halitta biyu ko fiye tare ta amfani da alamar ampersand " & ".

Tutorial Steps

1. A cikin akwatin maganganun INDEX, danna kan Row_num line.
2. Rubuta nau'ikan aikin wasan da aka biye da takalmin bude " ( "
3. Danna kan tantanin halitta D3 don shigar da wannan tantanin halitta a cikin akwatin maganganu.
4. Rubuta ampersand " & " bayan bayanan tantanin halitta D3 don ƙara ƙirar ta biyu.
5. Danna kan tantanin halitta E3 don shigar da wannan tantanin halitta ta biyu a cikin akwatin maganganu.
6. Rubuta takaddama "," bayan bayanan tantancewa na E3 don kammala aikin shiga MATCH na binciken Lookup_value .
7. Ka bar akwatin maganganun INDEX bude don mataki na gaba a cikin koyawa.

A mataki na karshe na koyawa da dubawa a cikin ɗakunan D3 da E3 na takardun aiki.

05 na 09

Ƙara Lookup_array don aikin MATCH

Wannan mataki ya hada da ƙara ƙirar Lookup_array don aikin MATCH da aka yi.

The Lookup_array ne kewayon kwayoyin da aikin MATCH zai bincika don gano shawara na Lookup_value a cikin mataki na gaba na koyawa.

Tun da mun gano matakan bincike guda biyu a cikin gardama na Lookup_array dole ne muyi haka don Lookup_array . Ayyukan MATCH ne kawai ke bincika wata tsararre don kowane lokaci da aka ƙayyade.

Don shigar da bayanan da yawa zamu sake amfani da ampersand " & " don su hada baki tare.

Tutorial Steps

Wajibi ne a shigar da waɗannan matakai bayan ƙirar da aka shigo a mataki na baya akan jerin Row_num a cikin akwatin maganganun INDEX.

1. Danna kan layin Row_num bayan bayanan don saka wurin sakawa a ƙarshen shigarwa a yanzu.
2. Sanya sassa D6 zuwa D11 a cikin takardar aiki don shigar da kewayon. Wannan shine farkon aikin tsararren aiki don bincika.
3. Rubuta ampersand " & " bayan bayanan salula D6: D11 saboda muna son aikin don bincika samfurori guda biyu.
4. Sanya siffofin E6 zuwa E11 a cikin takarda don shigar da kewayon. Wannan shi ne na biyu jigilar aikin shine don bincika.
5. Rubuta takaddama "," bayan bayanan tantanin halitta E3 don kammala shigar da aikin MATCH na Lookup_array .
6. Ka bar akwatin maganganun INDEX bude don mataki na gaba a cikin koyawa.

06 na 09

Ƙara nau'in matsala da kuma kammala aikin MATCH

Sakamakon na uku da na karshe na MATCH shine matsala Match_type.

Wannan hujja ta nuna Excel yadda za a daidaita da Lookup_value tare da dabi'u a Lookup_array. Zaɓuɓɓuka sune: 1, 0, ko -1.

Wannan jayayya na da zaɓi. Idan an cire aikin yana amfani da ƙimar tsofin 1.

• idan Match_type = 1 ko kuma an bar shi: MATCH ta sami mafi girma da ƙimar da ta kasa da ko daidai da Lookup_value. Dole ne a yi la'akari da bayanan Lookup_array cikin tsari mai girma.
• idan wasa_type = 0: MATCH ta sami darajar farko da daidai daidai da Lookup_value. Ana iya rarraba bayanai na Lookup_array cikin kowane umurni.
• idan Match_type = -1: MATCH ya sami mafi girman ƙimar da ya fi ko kuma daidai da Lookup_value. Dole ne a yi la'akari da bayanan Lookup_array cikin tsari mai saukowa.

Tutorial Steps

Wajibi ne a shigar da waɗannan matakai bayan ƙirar da aka shigo a mataki na baya akan jerin Row_num a cikin akwatin maganganun INDEX.

1. Biye da takaddama akan Row_num line, rubuta wani zero " 0 " tun da muna so aikin da aka gwada ya dawo daidai matches da kalmomin da muka shiga a cikin kwayoyin D3 da E3.
2. Rubuta takalmin rufewa " ) " don kammala aikin MATCH.
3. Ka bar akwatin maganganun INDEX bude don mataki na gaba a cikin koyawa.

07 na 09

Komawa zuwa aikin INDEX

Yanzu cewa aikin MATCH ya aikata zamu motsa zuwa sashin na uku na akwatin maganganun buɗewa kuma shigar da hujja na ƙarshe don aikin INDEX.

Wannan hujja ta uku da na ƙarshe ita ce hujjar Column_num wadda ke nuna Excel lambar mahaɗin a cikin kewayon D6 zuwa F11 inda zai sami bayanin da muke son dawowa ta wurin aikin. A wannan yanayin, mai sayarwa ga kayan aikin widget .

Tutorial Steps

1. Danna kan layin Column_num cikin akwatin maganganu.
2. Shigar da lambar uku " 3 " (ba a faɗi) a kan wannan layi ba tun muna neman bayanai a cikin sashin na uku na kewayon D6 zuwa F11.
3. Kada ka danna OK ko rufe akwatin maganganun INDEX. Dole ne ya kasance a bude don mataki na gaba a cikin koyawa - ƙirƙirar lissafi .

08 na 09

Ƙirƙirar takarda

Kafin rufe akwatin maganganun muna buƙatar kunna aikinmu wanda aka gwada a cikin tsari mai tsari .

Tsarin lissafi shine abin da ya ba shi damar bincika kalmomin da yawa a cikin tebur na bayanai. A wannan darasi muna kallon daidaitawa guda biyu: Widgets daga shafi na 1 da kuma titanium daga shafi na 2.

Samar da samfurin tsari a Excel anyi ta latsa maɓallin CTRL , SHIFT , da kuma ENTER a kan maɓallin keyboard a lokaci guda.

Sakamakon latsa maɓallin maɓalli tare shi ne kewaye da aikin tare da takalmin gyare-gyare: {} yana nuna cewa yanzu ya zama tsari mai tsabta.

Tutorial Steps

1. Tare da maganganun maganganun kammala har yanzu suna buɗe daga mataki na gaba na wannan koyawa, latsa ka riƙe ƙasa da maɓallin CTRL da SHIFT a kan keyboard sannan latsa kuma saki maɓallin ENTER .
2. Idan an yi daidai, akwatin maganganu zai rufe kuma kuskuren N / A zai bayyana a cell F3 - tantanin halitta inda muka shiga aikin.
3. Kuskuren N / A ya bayyana a cikin F cell F3 saboda sel D3 da E3 sune blank. D3 da E3 sune sel a inda muka fada aikin don gano Lookup_values ​​a mataki na 5 na koyawa. Da zarar an ƙara bayanai zuwa waɗannan kwayoyin halitta biyu, kuskure za a maye gurbinsu bayanan bayanai daga database .

09 na 09

Ƙara Matakan Bincike

Ƙarshen mataki a cikin koyawa shine don ƙara maƙallin binciken zuwa aikin mu.

Kamar yadda aka ambata a cikin mataki na baya, muna kallon daidaita ka'idodin Widgets daga shafi na 1 da Titanium daga shafi na 2.

Idan, kuma idan kawai, hanyarmu ta samo matsala don kalmomin biyu a ginshiƙai masu dacewa a cikin bayanai, zai dawo da darajar daga shafi na uku.

Tutorial Steps

1. Danna kan tantanin D3.
2. Rubuta Widgets kuma danna maɓallin shigarwa akan keyboard.
3. Danna kan salula E3.
4. Nau'ikan iri kuma danna maɓallin shigarwa akan keyboard.
5. Sunan mai suna Widgets Inc. ya kamata ya bayyana a tantanin salula F3 - wurin wurin aikin saboda shi ne kawai mai siyar da aka jera wadanda ke sayar da Titanium Widgets.
6. Lokacin da ka danna kan tantanin F3 da cikakken aikin
   {= INDEX (D6: F11, MATCH (D3 & E3, D6: D11 & E6: E11, 0), 3)}
   ya bayyana a cikin maɓallin tsari a sama da takardun aiki .

Lura: A cikin misalin mu akwai guda ɗaya don samar da widget din titanium. Idan akwai masu sayarwa fiye da ɗaya, za'a mayar da mai sayen da aka jera a cikin database ta hanyar aikin.