Mukati
10th zuva remarathon 30 Excel inoshanda mumazuva makumi matatu isu tichazvipira pakudzidza kwebasa racho HLOOKUP (GPR). Ichi chimiro chakafanana chaizvo ne VLOOKUP (VLOOKUP), chete inoshanda nezvinhu zverunyorwa rwakachinjika.
Zvinosuwisa basa HLOOKUP (GLOW) haina kufarirwa sehanzvadzi yayo, nekuti kazhinji data iri mumatafura inorongwa yakatwasuka. Rangarira nguva yekupedzisira pawaida kutsvaga tambo? Zvakadini nekudzorera kukosha kubva pachikamu chimwe chete, asi chiri mune imwe yemitsara iri pasi?
Zvakadaro, ngatipe maitiro HLOOKUP (GPR) nguva yakanyatsokodzera yekubwinya uye nyatsotarisa ruzivo pamusoro pechinhu ichi, pamwe nemienzaniso yekushandiswa kwayo. Rangarira, kana uine mazano anonakidza kana mienzaniso, ndapota ugovane mumhinduro.
Basa rechigumi: HLOOKUP
basa HLOOKUP (HLOOKUP) anotarisa kumusoro kukosha mumutsara wekutanga wetafura uye anodzosera imwe kukosha kubva mukoramu imwechete mutafura.
Ndingashandisa sei basa reHLOOKUP (HLOOKUP)?
Sezvo basa HLOOKUP (HLOOKUP) inogona kuwana chaiyo kana kukosha kukosha mutambo, uye inogona:
- Tsvaga zvikamu zvekutengesa zvedunhu rakasarudzwa.
- Tsvaga chiratidzo chinokodzera zuva rakasarudzwa.
HLOOKUP Syntax
basa HLOOKUP (HLOOKUP) ine chirevo chinotevera:
HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)
ГПР(искомое_значение;таблица;номер_строки;интервальный_просмотр)
- lookup_value (lookup_value): Kukosha kuwanikwa. Inogona kuva kukosha kana sereferensi.
- tafura_array (tafura): tafura yekutarisa. Inogona kuva referensi yerenji kana zita renji rine mitsetse miviri kana kudarika.
- row_index_num (line_nhamba): Tambo ine kukosha kunodzoserwa nebasa. Ronga nenhamba yemutsara mukati metafura.
- range_lookup (range_lookup): Shandisa FALSE kana 0 kuti uwane chaiwo match; pakutsvaga kutsvaga, CHOKWADI (CHOKWADI) kana 1. Muchiitiko chekupedzisira, tambo iyo basa iri kutsvaga inofanira kurongwa mukukwira.
Traps HLOOKUP (GPR)
se VLOOKUP (VLOOKUP), basa HLOOKUP (HLOOKUP) inogona kunonoka, kunyanya kana uchitsvaga mutsara chaiwo wetambo yemavara patafura isina kurongwa. Pese pazvinogoneka, shandisa fungidziro yekutsvaga mutafura yakarongwa nemutsara wekutanga mukukwira. Unogona kutanga kushandisa basa MATCH (ZVIMWE ZVINOFUNDURWA) kana COUNTIF (COUNTIF) kuve nechokwadi kuti kukosha kwauri kutsvaga kuripo mumutsara wekutanga.
Zvimwe zvinhu zvakadai INDEX (INDEX) uye MATCH (MATCH) inogona zvakare kushandiswa kutora kukosha kubva patafura uye inoshanda zvakanyanya. Tichavatarisa gare gare mumakwikwi edu emarathon toona kuti vangave vane simba uye vanochinjika sei.
Muenzaniso 1: Tsvaga mutengo wekutengesa wedunhu rakasarudzwa
Rega ndikuyeuchidze zvakare kuti basa racho HLOOKUP (HLOOKUP) inongotarisa kukosha mumutsara wepamusoro wetafura. Mumuenzaniso uyu, tichawana huwandu hwekutengesa kwenharaunda yakasarudzwa. Izvo zvakakosha kuti isu tiwane kukosha kwayo, saka isu tinoshandisa anotevera marongero:
- Zita redunhu rinoiswa muchitokisi B7.
- Tafura yedunhu rekutarisa ine mitsara miviri uye inotambanudzira huwandu hweC2:F3.
- Huwandu hwekutengesa huri mumutsara 2 wetafura yedu.
- Kupokana kwekupedzisira kunoiswa kuFALSE kuti uwane inowirirana kana uchitsvaga.
Iyo formula musero C7 ndeiyi:
=HLOOKUP(B7,C2:F3,2,FALSE)
=ГПР(B7;C2:F3;2;ЛОЖЬ)
Kana zita redunhu risingawaniki mumutsara wekutanga wetafura, mhedzisiro yebasa HLOOKUP (GPR) ichaita #AT (#N/A).
Muenzaniso 2: Tsvaga chiyero chezuva rakasarudzwa
Kazhinji kana uchishandisa basa HLOOKUP (HLOOKUP) inoda mutambo chaiwo, asi dzimwe nguva kuenzanisa kwekuenzanisa kunonyanya kukodzera. Semuenzaniso, kana zviratidzo zvikachinja pakutanga kwekota yega yega, uye mazuva ekutanga ezvikamu izvi anoshandiswa semisoro yekoromo (ona mufananidzo uri pazasi). Muchiitiko ichi, kushandisa basa HLOOKUP (HLOOKUP) uye fungidziro yemutambo, iwe unowana chiratidzo chinoenderana nezuva rakapihwa. Mumuenzaniso uyu:
- Zuva rakanyorwa muchitokisi C5.
- Iyo chiratidzo chekutarisa tafura ine mitsara miviri uye iri mumhando C2: F3.
- Tafura yekutarisa inorongwa nemutsara wemazuva muhurongwa hwekukwira.
- Zviratidzo zvakanyorwa mumutsara 2 wetafura yedu.
- Mapoka ekupedzisira ebasa akaiswa kuti TRUE kutsvaga fungidziro yekufananidza.
Iyo formula musero D5 ndeye:
=HLOOKUP(C5,C2:F3,2,TRUE)
=ГПР(C5;C2:F3;2;ИСТИНА)
Kana zuva racho risina kuwanikwa mumutsara wekutanga wetafura, basa racho HLOOKUP (HLOOKUP) ichawana kukosha kwepedyo kwakakura kuri kushoma pane gakava lookup_value (kutarisa_kukosha). Mumuenzaniso uyu, kukosha kunodiwa ndiko March 15. Haisi mumutsara wemazuva, saka fomula ichatora kukosha 1 January uye dzokai 0,25.
