30 Excel imagwira ntchito m'masiku 30: HLOOKUP

Tsiku la 10 la marathon 30 Excel imagwira ntchito m'masiku 30 tidzadzipereka ku phunziro la ntchitoyi HLOOKUP (GPR). Izi ndizofanana kwambiri ndi VLOOKUP (VLOOKUP), imangogwira ntchito ndi mndandanda wopingasa.

ntchito mwatsoka HLOOKUP (GLOW) sichidziwika ngati mlongo wake, chifukwa nthawi zambiri deta yomwe ili pamatebulo imakonzedwa molunjika. Mukukumbukira nthawi yomaliza yomwe mumafuna kusaka chingwe? Nanga bwanji kubweza mtengo kuchokera pamndandanda womwewo, koma womwe uli mumzere umodzi m'munsimu?

Komabe, tiyeni tipereke mawonekedwe HLOOKUP (GPR) mphindi yoyenera yaulemerero ndikuyang'anitsitsa zambiri za izi, komanso zitsanzo za ntchito yake. Kumbukirani, ngati muli ndi malingaliro osangalatsa kapena zitsanzo, chonde gawanani nawo mu ndemanga.

Ntchito 10: HLOOKUP

ntchito HLOOKUP (HLOOKUP) imayang'ana mtengo mumzere woyamba wa tebulo ndikubwezeretsanso mtengo wina kuchokera pagawo lomwelo patebulo.

Kodi ndingagwiritsire ntchito bwanji HLOOKUP (HLOOKUP)?

Popeza ntchito HLOOKUP (HLOOKUP) ikhoza kupeza mtengo weniweni kapena woyerekeza mu chingwe, ndiye imatha:

  • Pezani kuchuluka kwa malonda a dera lomwe mwasankha.
  • Pezani chizindikiro chomwe chili choyenera pa tsiku losankhidwa.

HLOOKUP Syntax

ntchito HLOOKUP (HLOOKUP) ili ndi mawu awa:

HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)

ГПР(искомое_значение;таблица;номер_строки;интервальный_просмотр)

  • kumachika (lookup_value): Mtengo wopezeka. Itha kukhala mtengo kapena ma cell reference.
  • tebulo_tayira (tebulo): tebulo loyang'ana. Atha kukhala renji kapena masinthidwe omwe ali ndi mizere iwiri kapena kuposerapo.
  • mzere_index_nambala (line_nambala): Chingwe chomwe chili ndi mtengo woti ubwezedwe ndi ntchitoyi. Khazikitsani ndi nambala ya mzere mkati mwa tebulo.
  • range_kuyang'ana (range_lookup): Gwiritsani ntchito FALSE kapena 0 kuti mupeze zofanana zenizeni; pakufufuza pafupifupi, CHOONADI (CHOONA) kapena 1. Potsirizira pake, chingwe chomwe ntchitoyo ikufufuzidwa iyenera kusanjidwa mwadongosolo lokwera.

Misampha HLOOKUP (GPR)

ngati VLOOKUP (VLOOKUP), ntchito HLOOKUP (HLOOKUP) ikhoza kuchedwa, makamaka pofufuza zofanana ndendende ndi zingwe patebulo losasankhidwa. Ngati n'kotheka, gwiritsani ntchito kufufuza moyerekeza mutebulo losanjidwa ndi mzere woyamba mokwera. Mukhoza kugwiritsa ntchito poyamba GANIZANI (Zavumbulutsidwa ZAMBIRI) kapena COUNTIF (COUNTIF) kuti muwonetsetse kuti mtengo womwe mukuyang'ana uliponso pamzere woyamba.

Zina monga INDEX (INDEX) ndi GANIZANI (MATCH) itha kugwiritsidwanso ntchito kubweza mitengo patebulo ndipo ndiyothandiza kwambiri. Tidzawayang'ana pambuyo pake mu mpikisano wathu wa marathon ndikuwona momwe angakhalire amphamvu komanso osinthika.

Chitsanzo 1: Pezani zotsatsa zadera lomwe mwasankha

Ndiroleni ndikukumbutseninso kuti ntchitoyo HLOOKUP (HLOOKUP) amangoyang'ana mtengo womwe uli mumzere wapamwamba wa tebulo. Muchitsanzo ichi, tipeza kuchuluka kwa malonda a dera lomwe lasankhidwa. Ndikofunikira kuti tipeze mtengo wolondola, chifukwa chake timagwiritsa ntchito makonda awa:

  • Dzina lachigawo limalowetsedwa mu cell B7.
  • Gome loyang'ana dera lili ndi mizere iwiri ndipo limatambasula C2:F3.
  • Zogulitsa zonse zili mumzere 2 wa tebulo lathu.
  • Mtsutso womaliza wakhazikitsidwa kuti FALSE kuti mupeze kufanana kwenikweni mukakusaka.

Fomula mu cell C7 ndi:

=HLOOKUP(B7,C2:F3,2,FALSE)

=ГПР(B7;C2:F3;2;ЛОЖЬ)

30 Excel imagwira ntchito m'masiku 30: HLOOKUP

Ngati dzina la dera silipezeka pamzere woyamba wa tebulo, zotsatira za ntchitoyo HLOOKUP (GPR) adzatero #AT (#N / A).

30 Excel imagwira ntchito m'masiku 30: HLOOKUP

Chitsanzo 2: Pezani muyeso wa tsiku losankhidwa

Nthawi zambiri mukamagwiritsa ntchito HLOOKUP (HLOOKUP) imafuna kufanana kwenikweni, koma nthawi zina pafupifupi machesi ndi oyenera. Mwachitsanzo, ngati zizindikiro zasintha kumayambiriro kwa kotala iliyonse, ndipo masiku oyambirira a magawowa amagwiritsidwa ntchito ngati mitu yazambiri (onani chithunzi pansipa). Pankhaniyi, kugwiritsa ntchito HLOOKUP (HLOOKUP) ndi machesi pafupifupi, mupeza chizindikiro chomwe chili choyenera pa tsiku lomwe laperekedwa. Mu chitsanzo ichi:

  • Tsikuli limalembedwa mu cell C5.
  • Gome loyang'ana chizindikiro lili ndi mizere iwiri ndipo lili mumitundu C2: F3.
  • Tebulo loyang'ana limasanjidwa ndi mzere wa deti mokwerera.
  • Zizindikiro zalembedwa mu mzere 2 wa tebulo lathu.
  • Mtsutso womaliza wa ntchitoyi wakhazikitsidwa kuti TRUE kuyang'ana pafupifupi kufanana kwake.

Fomula mu cell D5 ndi:

=HLOOKUP(C5,C2:F3,2,TRUE)

=ГПР(C5;C2:F3;2;ИСТИНА)

Ngati tsikulo silipezeka pamzere woyamba wa tebulo, ntchitoyo HLOOKUP (HLOOKUP) adzapeza mtengo wapafupi kwambiri womwe uli wocheperako kumachika (kuyang'ana_mtengo). Mu chitsanzo ichi, mtengo wofunidwa ndi March 15. Sili mu mzere wa deti, kotero fomulayo itenga mtengo wake 1 January ndi kubwerera 0,25.

30 Excel imagwira ntchito m'masiku 30: HLOOKUP

Siyani Mumakonda