30 Excel inoshanda mumazuva makumi matatu: LOOKUP

Nezuro mumarathon 30 Excel inoshanda mumazuva makumi matatu takatandara nebasa racho REPT (DZORERA) nekugadzira machati mukati mesero uye nekuishandisa kuverenga kuri nyore. Muvhuro, uye zvakare yave nguva yekuti tipfeke nguwani dzedu dzekufunga.

Pazuva rechi16 remarathon, tichadzidza basa racho TARISA MUDENGA (ONA). Iyi ishamwari yepedyo VLOOKUP (VLOOKUP) uye HLOOKUP (GPR), asi inoshanda zvishoma zvakasiyana.

Saka, ngatidzidzei dzidziso uye tiedze basa racho mukuita TARISA MUDENGA (ONA). Kana iwe uine rumwe ruzivo kana mienzaniso yemashandisirwo echinhu ichi, ndapota ugovane mumhinduro.

Basa rechi16: LOOKUP

basa TARISA MUDENGA (LOOKUP) inodzosa kukosha kubva mumutsara mumwe, koramu imwe, kana kubva mutsetse.

Ndingashandisa sei basa reLOOKUP?

basa TARISA MUDENGA (LOOKUP) inodzosa mhedzisiro, zvichienderana nehuwandu hwauri kutsvaga. Nekubatsira kwayo iwe uchakwanisa:

  • Wana kukosha kwekupedzisira mukoramu.
  • Tsvaga mwedzi wekupedzisira nekutengesa kusina kunaka.
  • Shandura kubudirira kwemudzidzi kubva muzana kuenda kumagiredhi emavara.

Syntax LOOKUP

basa TARISA MUDENGA (LOOKUP) ine mafomu maviri esyntactic - vector uye array. Mune vector fomu, basa rinotarisa kukosha mumutsara wakapihwa kana mutsara, uye mune array fomu, inotarisa kukosha mumutsara wekutanga kana koramu yehurongwa.

Iyo vector fomu ine inotevera syntax:

LOOKUP(lookup_value,lookup_vector,result_vector)

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

  • lookup_value (lookup_value) - Inogona kuve mameseji, nhamba, boolean, zita, kana chinongedzo.
  • lookup_vector (lookup_vector) - Range rine mutsara mumwe kana koramu imwe.
  • result_vector (result_vector) - renji rine mutsara mumwe kana koramu imwe.
  • nharo ranges lookup_vector (lookup_vector) uye result_vector (result_vector) inofanira kunge yakafanana.

Iyo array fomu ine inotevera syntax:

LOOKUP(lookup_value,array)

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

  • lookup_value (lookup_value) - Inogona kuve mameseji, nhamba, boolean, zita, kana chinongedzo.
  • kutsvaga kunoitwa zvinoenderana nechiyero cheiyo array:
    • kana dhizaini iine makoramu akawanda kupfuura mitsara, saka kutsvaga kunoitika mumutsara wekutanga;
    • kana nhamba yemitsara nemakoramu akafanana kana kune mamwe mitsara, saka kutsvaga kunowanikwa mumutsara wekutanga.
  • basa rinodzosa kukosha kwekupedzisira kubva kumutsara wakawanikwa/column.

Misungo LOOKUP (ONA)

  • Mukushanda TARISA MUDENGA (BROWSE) hapana sarudzo yekutsvaga chaiyo match, iri mukati VLOOKUP (VLOOKUP) uye mukati HLOOKUP (GPR). Kana pasina kukosha kwekutsvaga, ipapo basa racho richadzorera chiyero chepamusoro chisingapfuuri kukosha kwekutsvaga.
  • The array kana vector irikutsvagwa inofanira kurongwa muhurongwa hwekukwira, zvikasadaro basa racho rinogona kudzorera mhedzisiro isiriyo.
  • Kana kukosha kwekutanga mune array/vector iri kutariswa yakakura kudarika kukosha kwekutarisa, ipapo basa racho rinoburitsa meseji yekukanganisa. #AT (#N/A).

Muenzaniso 1: Kutsvaga kukosha kwekupedzisira mukoramu

Muchimiro chechirongwa chebasa TARISA MUDENGA (LOOKUP) inogona kushandiswa kutsvaga kukosha kwekupedzisira mukoramu.

Excel kubatsira makotesheni kukosha 9,99999999999999E + 307 senhamba huru kwazvo inogona kunyorwa musero. Mune fomula yedu, ichaiswa seyakakosha kukosha. Zvinofungidzirwa kuti nhamba yakakura kudaro haizowaniki, saka basa rinodzosa kukosha kwekupedzisira mukoramu D.

Mumuenzaniso uyu, nhamba dziri mukoramu D dzinotenderwa kusarongeka, nekuwedzera, mameseji maitiro anogona kuuya.

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

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

30 Excel inoshanda mumazuva makumi matatu: LOOKUP

Muenzaniso 2: Tsvaga mwedzi wapfuura uine kukosha kwakashata

Mumuenzaniso uyu, isu tichashandisa iyo vector chimiro TARISA MUDENGA (ONA). Column D ine mutengo wekutengesa, uye chikamu E chine mazita emwedzi. Mune mimwe mwedzi, zvinhu hazvina kufamba zvakanaka, uye nhamba dzisina kunaka dzakaonekwa mumasero ane mutengo wekutengesa.

Kuti uwane mwedzi wekupedzisira uine nhamba isina kunaka, fomula ine TARISA MUDENGA (LOOKUP) inotarisa kune yega yega kukosha kwekutengesa kuti ishoma pane 0 (kusaenzana mune fomula). Zvadaro, tinoparadzanisa 1 pamugumisiro, tinopedzisira tawana chero 1, kana meseji yezvikanganiso #DIV/0 (#CHIKAMU/0).

Sezvo kukosha kwaidiwa kuri 2 haina kuwanikwa, basa richasarudza yekupedzisira kuwanikwa 1, uye dzorera kukosha kunoenderana kubva mukoramu E.

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

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

30 Excel inoshanda mumazuva makumi matatu: LOOKUP

Tsanangudzo: Mune iyi fomula, pachinzvimbo chekupokana lookup_vector (lookup_vector) kutaura kunotsiviwa 1/(D2:D8<0), iyo inoumba hurongwa mu RAM yekombuta, inosanganisira 1 uye kukanganisa kukosha #DIV/0 (#CHIKAMU/0). 1 inoratidza kuti sero rinoenderana mumhando D2: D8 ine kukosha kushoma pane 0, uye kukanganisa #DIV/0 (#DIV/0) - chikuru pane kana kuenzana nacho 0. Nekuda kweizvozvo, basa redu nderekutsvaga wekupedzisira 1 mune yakasikwa virtual array, uye zvichibva pane izvi, dzorera zita remwedzi kubva pachiyero E2:E8.

Muenzaniso 3: Kushandura kubudirira kwevadzidzi kubva muzana kusvika kune mavara magiredhi

Kare, takatogadzirisa dambudziko rakafanana tichishandisa basa racho VLOOKUP (VPR). Nhasi tichashandisa basa TARISA MUDENGA (VIEW) mune vector fomu yekushandura kubudirira kwemudzidzi kubva muzana kusvika kune mavara mamakisi. Kusiyana VLOOKUP (VLOOKUP) yebasa TARISA MUDENGA (Maonero) Hazvina basa kana zvikamu zviri muchikamu chekutanga chetafura. Unogona kusarudza zvachose chero column.

Mumuenzaniso unotevera, zvibodzwa zviri mukoramu D, zvakarongwa muhurongwa hwekukwira, uye mavara anoenderana nawo ari muchikamu C, kuruboshwe kwechikamu chiri kutsvagwa.

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

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

30 Excel inoshanda mumazuva makumi matatu: LOOKUP

Leave a Reply