Hanyoyi guda biyu na Excel Duba Yin amfani da VLOOKUP Sashe na 2

01 na 06

Fara Ayyukan MATCH Nested

Komawa zuwa Sashe na 1

Shigar da aikin MATCH a matsayin Maɓallin Lissafin Ƙididdigar Shafin

Bisa ga al'ada VLOOKUP kawai ya dawo da bayanai daga wani shafi na lissafin bayanai kuma wannan shafi an saita shi ta hanyar ƙididdiga ƙididdiga na index .

Duk da haka, a cikin wannan misali muna da ginshiƙai guda uku da muke so mu sami bayanai don haka muna buƙatar hanyar da za mu sauya sauƙin rubutun harafi ba tare da gyara madadin bincikenmu ba.

Wannan shine inda aikin MATCH ya shiga wasa. Zai ba mu damar daidaita lamba a cikin sunan filin - ko Janairu, Fabrairu, ko Maris - mu rubuta cikin tantanin halitta E2 na takardar aiki.

Ayyukan Nesting

Sabili da aikin MATCH, sabili da haka, yana aiki kamar yadda ƙididdiga ƙididdigar lissafin index na VLOOKUP.

An kammala wannan ta hanyar yin aikin MATCH a ciki na VLOOKUP a cikin layin Col_index_num na akwatin maganganu.

Shigar da aikin MATCH da hannu

A lokacin da ayyuka na nesting, Excel ba ya ƙyale mu mu buɗe akwatin maganganun na biyu don shigar da muhawararsu.

Saboda haka, aikin MATCH, dole ne a shigar da hannu a hannu a Col_index_num line.

Lokacin shigar da ayyuka tare da hannu, dole ne a raba raƙuman muhawarar ta hanyar takaddama "," .

Tutorial Steps

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.

1. A cikin akwatin maganganun VLOOKUP, danna kan Col_index_num line.
2. Rubuta nau'ikan aikin wasan da aka biye da takalmin bude " ( "
3. Danna kan tantanin halitta E2 don shigar da wannan tantanin halitta a cikin akwatin maganganu.
4. Rubuta takaddama "," bayan bayanan tantancewa na E3 don kammala aikin shiga MATCH na binciken Lookup_value .
5. Ka bar akwatin maganganun VLOOKUP don bude mataki na gaba a cikin koyawa.

A mataki na karshe na tutorial da za a shiga Lookup_values ​​a cikin sassan D2 da E2 na takardun aiki .

02 na 06

Ƙara Lookup_array don aikin MATCH

Ƙ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.

A cikin wannan misali, muna son aikin MATCH don bincika kwayoyin D5 zuwa G5 don wasa tare da sunan watan da za a shiga cikin cell E2.

Tutorial Steps

Dole ne a shigar da waɗannan matakai bayan ƙirar da aka shigo a mataki na baya a kan layin Col_index_num a cikin akwatin maganganun VLOOKUP.

1. Idan ya cancanta, danna kan layin Col_index_num bayan ƙirar don sanya wurin sakawa a ƙarshen shigarwa a yanzu.
2. Sanya sassa D5 zuwa G5 a cikin takardun aiki don shigar da waɗannan maƙallan cell kamar yadda kewayin aikin shine don bincika.
3. Latsa maɓalli F4 a kan keyboard don canza wannan kewayawa zuwa cikakkun bayanai . Yin haka zai sa ya yiwu a kwafe tsarin binciken da aka kammala zuwa wasu wurare a cikin takardun aiki a cikin mataki na karshe na koyawa
4. Rubuta takaddama "," bayan bayanan tantanin halitta E3 don kammala shigar da aikin MATCH na Lookup_array .

03 na 06

Ƙara nau'in matsala da kuma kammala aikin MATCH

Ƙ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. Idan an zaɓa wannan darajar, dole ne a tsara jigilar Lookup_array cikin tsari mai girma.
• idan Match_type = 0: MATCH ya sami darajar farko wadda take 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. Idan an zaɓa wannan darajar, dole ne a yi la'akari da bayanan Lookup_array cikin tsari mai saukowa.

Tutorial Steps

Dole ne a shigar da waɗannan matakai bayan bayanan da aka shigo a mataki na baya akan jerin Row_num a cikin akwatin maganganun VLOOKUP.

1. Biyewar karo na biyu a kan layin Col_index_num , rubuta wani zero " 0 " tun da muna son aikin da aka gwada ya dawo daidai daidai zuwa watan shiga cikin cell E2.
2. Rubuta takalmin rufewa " ) " don kammala aikin MATCH.
3. Ka bar akwatin maganganun VLOOKUP don bude mataki na gaba a cikin koyawa.

04 na 06

Shigar da Bincike na VLOOKUP a Kalli Shawara

Tambayar Gudun Range

Magana ta VLOOKUP ta Range_lookup tana da ma'ana daidai (TRUE ko FALSE kawai) wanda ya nuna ko kuna so VLOOKUP don neman ainihin ko kusa da wasan zuwa Lookup_value.

• Idan TRUE ko kuma idan an cire wannan hujja, VLOOKUP ya dawo ko dai daidai daidai da Lookup_value, ko, idan ba'a samo daidai ba, VLOOKUP ya sake dawowa mafi girma. Domin dabarun da za a yi haka, dole ne a ware jigilar bayanai a cikin shafi na farko na Table_array a tsari mai girma .
• Idan FALSE, VLOOKUP kawai zai yi amfani da ainihin daidai zuwa Lookup_value. Idan akwai dabi'u biyu ko fiye a cikin shafin farko na Table_array wanda yayi daidai da darajar binciken, ana amfani da darajar farko. Idan ba a samo daidai ba, ana mayar da kuskuren N / A.

A cikin wannan koyo, tun da muna neman tallan tallace-tallace na wata, za mu saita Range_lookup daidai da Ƙarya .

Tutorial Steps

1. Danna kan Range_lookup line a cikin akwatin maganganu
2. Rubuta kalma ƙarya a cikin wannan layi don nuna cewa muna so VLOOKUP ya dawo daidai daidai don bayanan da muke nema
3. Danna Ya yi don kammala tsarin bincike na biyu da rufe akwatin maganganu
4. Tun da ba mu riga muka shiga jerin binciken D2 da E2 ba, kuskuren N / A zai kasance a cell F2
5. Wannan kuskure za a gyara a matakai na gaba a cikin koyawa lokacin da za mu kara tsarin bincike a mataki na gaba na koyawa.

05 na 06

Gwada Hanya Hanya na Biyu

Gwada Hanya Hanya na Biyu

Don amfani da hanyar neman hanyar biyu don samun bayanan tallace-tallace na kowane wata don kukis daban-daban da aka lakafta a cikin tashar tebur, rubuta sunan kuki a cikin cell D2, watan zuwa cikin cell E2 kuma danna maballin ENTER akan keyboard.

Bayanan tallace-tallace za a nuna su a cell F2.

Tutorial Steps

1. Danna kan tantanin halitta D2 a cikin takardar aikinku
2. Rubuta Oatmeal zuwa cikin cell D2 kuma danna maballin ENTER akan keyboard
3. Danna kan tantanin halitta E2
4. Rubuta Fabrairu zuwa cell E2 kuma danna maballin ENTER akan keyboard
5. Darajar $ 1,345 - adadin tallace-tallace don kukis Oatmeal a watan Fabrairu - ya kamata a nuna shi a cikin salula F2
6. A wannan lokaci, zanenku ya dace da misali a shafi na 1 na wannan koyawa
7. Gwada matakan binciken da kara ta hanyar buga kowane nau'in nau'in kuki da kuma watanni da ke cikin Table_array kuma ana nuna adadin tallace-tallace a cell F2
8. Ƙarshe na ƙarshe a cikin koyaswar kwafi na kwafin tsarin da aka yi amfani da shi ta amfani da Fill Handle .

Idan sakon kuskure kamar #REF! ya bayyana a cell F2, wannan jerin jerin saƙonnin kuskure na VLOOKUP zai taimake ka ka gane inda matsalar ta kasance.

06 na 06

Yin kwaskwarima tsarin ƙira guda biyu tare da cika cika

Yin kwaskwarima tsarin ƙira guda biyu tare da cika cika

Don sauƙaƙa kwatanta bayanai don watanni daban-daban ko daban-daban kukis, ana iya kwafin tsarin da aka bincika zuwa wasu kwayoyin domin ana iya nuna yawan yawa a lokaci guda.

Tun lokacin da aka ƙaddamar da bayanan a cikin tsarin aiki na yau da kullum, zamu iya kwafi tsarin binciken a cell F2 zuwa cell F3.

Yayinda aka kofe ma'anar, Excel za ta sabunta hankulan dangin dan adam don yin la'akari da sabon tsarin. A wannan yanayin D2 ya zama D3 da E2 ya zama E3,

Bugu da ƙari, Excel tana kiyaye cikakkiyar tantancewar kwayar halitta kamar haka cikakken farashi $ D $ 5: $ G $ 5 ya kasance daidai lokacin da aka kofe wannan maƙala.

Akwai hanya fiye da ɗaya don kwafe bayanai a Excel, amma mai yiwuwa hanya mafi sauki ita ce ta amfani da Fill Handle.

Tutorial Steps

1. Danna kan tantanin halitta D3 a cikin aikinku
2. Rubuta Oatmeal zuwa cikin cell D3 kuma danna maballin ENTER akan keyboard
3. Danna kan salula E3
4. Rubuta Maris zuwa cikin cell E3 kuma latsa maballin ENTER akan keyboard
5. Danna kan F2 F2 don yin sautin mai aiki
6. Sanya ma'anar linzamin kwamfuta a kan kusurwar baki a cikin kusurwar dama. Maɗin zai canza zuwa alamar da aka sanya "+" - wannan shi ne Fill Handle
7. Danna maballin hagu na hagu kuma jawo mai cikawa zuwa tantanin halitta F3
8. Saki da maballin linzamin kwamfuta kuma tantanin halitta F3 ya kamata ya ƙunshi nauyin binciken abu biyu
9. Darajar $ 1,287 - adadin tallace-tallace na kukis Oatmeal a watan Maris - ya kamata a nuna shi a cikin salula F3