Ayyukan Excel 30 a cikin kwanaki 30: LOOKUP

Jiya a gudun marathon 30 Excel yana aiki a cikin kwanaki 30 mun ji daɗi da aikin REP (Mai maimaita) ta hanyar ƙirƙirar ginshiƙi a cikin tantanin halitta da amfani da shi don ƙididdigewa mai sauƙi. Litinin ne, kuma lokaci ya yi da za mu sa huluna masu tunani.

A ranar 16th na marathon, za mu yi nazarin aikin DUBA SAMA (DUBI). Wannan aboki na kurkusa ne KYAUTA (VLOOKUP) da HLOOKUP (GPR), amma yana aiki da ɗan bambanta.

Don haka, bari mu yi nazarin ka'idar kuma mu gwada aikin a aikace DUBA SAMA (DUBI). Idan kuna da ƙarin bayani ko misalai kan yadda ake amfani da wannan fasalin, da fatan za a raba su a cikin sharhi.

Aiki 16: DUBA

aiki DUBA SAMA (LOOKUP) yana dawo da ƙima daga jere ɗaya, shafi ɗaya, ko daga tsararru.

Ta yaya zan iya amfani da aikin LOOKUP?

aiki DUBA SAMA (LOOKUP) yana mayar da sakamakon, ya danganta da ƙimar da kuke nema. Tare da taimakonsa za ku iya:

  • Nemo ƙima ta ƙarshe a cikin shafi.
  • Nemo watan da ya gabata tare da tallace-tallace mara kyau.
  • Maida nasarar ɗalibi daga kashi-kashi zuwa maki haruffa.

SANTAX LOOKUP

aiki DUBA SAMA (LOOKUP) yana da nau'i biyu na syntactic - vector da tsararru. A cikin nau'i na vector, aikin yana neman ƙima a cikin ginshiƙi ko jere, kuma a cikin tsari, yana neman ƙimar a jere na farko ko ginshiƙi na tsararru.

Siffar vector tana da syntax mai zuwa:

LOOKUP(lookup_value,lookup_vector,result_vector)

ПРОСМОТР(искомое_значение;просматриваемый_вектор;вектор_результатов)

  • dubawa (lookup_value) - Zai iya zama rubutu, lamba, boolean, suna, ko hanyar haɗi.
  • duba_vector (lookup_vector) - kewayon da ya ƙunshi jere ɗaya ko shafi ɗaya.
  • sakamako_vector (result_vector) - kewayon da ya ƙunshi jere ɗaya ko shafi ɗaya.
  • jayayya duba_vector (lookup_vector) da sakamako_vector (result_vector) dole ne ya zama girman iri ɗaya.

Tsarin tsararru yana da ma'ana mai zuwa:

LOOKUP(lookup_value,array)

ПРОСМОТР(искомое_значение;массив)

  • dubawa (lookup_value) - Zai iya zama rubutu, lamba, boolean, suna, ko hanyar haɗi.
  • Ana yin binciken bisa ga girman jeri:
    • idan tsararru tana da ginshiƙai fiye da layuka, to binciken yana faruwa a jere na farko;
    • idan adadin layuka da ginshiƙai iri ɗaya ne ko kuma akwai ƙarin layuka, to binciken yana faruwa a ginshiƙi na farko.
  • aikin yana dawo da ƙimar ƙarshe daga layin da aka samo.

KALLON tarko (DUBI)

  • A cikin aiki DUBA SAMA (BROWSE) babu wani zaɓi don nemo ainihin wasa, wanda ke ciki KYAUTA (VLOOKUP) kuma a ciki HLOOKUP (GPR). Idan babu ƙimar bincike, to aikin zai dawo da matsakaicin ƙimar da bai wuce ƙimar bincike ba.
  • Dole ne a jera jeri ko vector da ake nema bisa tsari mai hawa, in ba haka ba aikin na iya dawo da sakamakon da ba daidai ba.
  • Idan ƙimar farko a cikin tsararru/vector da ake duba sama ta fi ƙimar nema, to aikin zai haifar da saƙon kuskure. #AT (#N/A).

Misali 1: Nemo ƙima ta ƙarshe a cikin shafi

A cikin sigar aikin tsararru DUBA SAMA Ana iya amfani da (LOOKUP) don nemo ƙima ta ƙarshe a cikin shafi.

Taimakon Excel yana faɗi darajar 9,99999999999999E + 307 a matsayin lambar mafi girma da za a iya rubutawa a cikin tantanin halitta. A cikin tsarin mu, za a saita shi azaman ƙimar da ake so. An ɗauka cewa ba za a sami irin wannan adadi mai yawa ba, don haka aikin zai dawo da ƙimar ƙarshe a shafi na D.

A cikin wannan misali, lambobin da ke cikin ginshiƙi na D ba a ba su izinin a jera su ba, ƙari, ƙimar rubutu na iya zuwa.

=LOOKUP(9.99999999999999E+307,D:D)

=ПРОСМОТР(9,99999999999999E+307;D:D)

Ayyukan Excel 30 a cikin kwanaki 30: LOOKUP

Misali 2: Nemo watan da ya gabata tare da ƙima mara kyau

A cikin wannan misali, za mu yi amfani da siffar vector DUBA SAMA (DUBI). Rukunin D ya ƙunshi ƙimar tallace-tallace, kuma shafi E ya ƙunshi sunayen watanni. A cikin wasu watanni, abubuwa ba su yi kyau ba, kuma lambobi mara kyau sun bayyana a cikin sel masu ƙimar tallace-tallace.

Don nemo watan da ya gabata tare da lambar mara kyau, dabarar tare da DUBA SAMA (LOOKUP) zai bincika kowane ƙimar tallace-tallace cewa bai kai ba 0 (rashin daidaituwa a cikin tsari). Na gaba, muna rarraba 1 a sakamakon, mun ƙare da ko dai 1, ko saƙon kuskure #DIV/0 (#SASHE/0).

Tunda darajar da ake so shine 2 ba a samu ba, aikin zai zaɓi na ƙarshe da aka samu 1, kuma dawo da madaidaicin ƙimar daga shafi E.

=LOOKUP(2,1/(D2:D8<0),E2:E8)

=ПРОСМОТР(2;1/(D2:D8<0);E2:E8)

Ayyukan Excel 30 a cikin kwanaki 30: LOOKUP

Ƙarin bayani: A cikin wannan tsari, maimakon jayayya duba_vector (lookup_vector) an maye gurbin magana 1/(D2:D8<0), wanda ke samar da jeri a cikin RAM ɗin kwamfutar, wanda ya ƙunshi 1 da ƙimar kuskure #DIV/0 (#SASHE/0). 1 yana nuna cewa madaidaicin tantanin halitta a cikin kewayon D2:D8 ya ƙunshi ƙimar ƙasa da 0, da kuskure #DIV/0 (#DIV/0) - abin da ya fi ko daidai da 0. Sakamakon haka, aikinmu shine nemo na ƙarshe 1 a cikin tsararrun ƙirƙira, kuma bisa ga wannan, dawo da sunan watan daga kewayon E2:E8.

Misali 3: Maida nasarar ɗalibi daga kashi-kashi zuwa maki haruffa

A baya can, mun riga mun warware irin wannan matsala ta amfani da aikin KYAUTA (VPR). Yau za mu yi amfani da aikin DUBA SAMA (VIEW) a cikin nau'in vector don canza nasarar ɗalibi daga kashi-kashi zuwa maki haruffa. Sabanin KYAUTA (VLOOKUP) don aiki DUBA SAMA (DUBI) Ba kome ba idan adadin yana cikin ginshiƙin farko na tebur. Kuna iya zaɓar kowane shafi.

A cikin misali mai zuwa, makin suna cikin ginshiƙin D, an jera su bisa tsari masu hawa sama, kuma haruffan da suka dace suna cikin shafi na C, zuwa hagu na shafi da ake nema.

=LOOKUP(C10,D4:D8,C4:C8)

=ПРОСМОТР(C10;D4:D8;C4:C8)

Ayyukan Excel 30 a cikin kwanaki 30: LOOKUP

Leave a Reply