Imisebenzi engama-30 ye-Excel ngezinsuku ezingama-30: HLOOKUP

Usuku lwe-10 lwemarathon 30 Excel isebenza ngezinsuku ezingama-30 sizonikela esifundweni somsebenzi HLOOKUP (GPR). Lesi sici sifana kakhulu ne I-VLOOKUP (I-VLOOKUP), isebenza kuphela nezici zohlu oluvundlile.

Umsebenzi oneshwa HLOOKUP (GLOW) ayidumile njengodadewabo, ngoba ezimweni eziningi idatha emathebula ihlelwa ngokuma iqonde phezulu. Khumbula isikhathi sokugcina lapho ufuna ukusesha iyunithi yezinhlamvu? Kuthiwani ngokubuyisela inani elisuka kukholomu efanayo, kodwa elitholakala komunye wemigqa engezansi?

Noma kunjalo, asinikeze izici HLOOKUP (GPR) isikhathi esisifaneleke kahle senkazimulo futhi ubhekisise ulwazi mayelana nalesi sici, kanye nezibonelo zokusetshenziswa kwaso. Khumbula, uma unemibono ethokozisayo noma izibonelo, sicela wabelane ngazo kumazwana.

Umsebenzi 10: HLOOKUP

Umsebenzi HLOOKUP (HLOOKUP) ibheka inani elikumugqa wokuqala wethebula futhi ibuyisela elinye inani elisuka kukholomu efanayo kuthebula.

Ngingawusebenzisa kanjani umsebenzi we-HLOOKUP (HLOOKUP)?

Kusukela umsebenzi HLOOKUP (HLOOKUP) ingathola inani eliqondile noma elilinganiselwe kuyunithi yezinhlamvu, bese ingakwazi:

  • Thola isamba sokuthengisa sesifunda esikhethiwe.
  • Thola inkomba ehambisana nedethi ekhethiwe.

I-HLOOKUP Syntax

Umsebenzi HLOOKUP (HLOOKUP) inala mazwi alandelayo:

HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)

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

  • ukubheka (lokup_value): Inani elizotholwa. Kungaba inani noma ireferensi yeseli.
  • itafula_ukuthi (ithebula): itafula lokubheka. Kungaba ireferensi yobubanzi noma ububanzi obuqanjwe igama obuqukethe imigqa emi-2 noma ngaphezulu.
  • inombolo_yenkomba_yomugqa (inombolo_yomugqa): Iyunithi yezinhlamvu equkethe inani elizobuyiswa umsebenzi. Imiswe ngenombolo yomugqa ngaphakathi kwethebula.
  • bheka_ububanzi (range_lookup): Sebenzisa AMANGA noma u-0 ukuze uthole okufanayo ngqo; ukuze uthole ukusesha okulinganiselwe, IQINISO (IQINISO) noma 1. Esimeni sakamuva, iyunithi yezinhlamvu okuseshwa kuyo umsebenzi kufanele ihlungwe ngohlelo olukhuphukayo.

Izicupho i-HLOOKUP (GPR)

Like I-VLOOKUP (VLOOKUP), umsebenzi HLOOKUP (HLOOKUP) inganensa, ikakhulukazi uma isesha okufanayo ncamashi kochungechunge lombhalo kuthebula elingahlungiwe. Noma nini lapho kunokwenzeka, sebenzisa ukusesha okulinganiselwe kuthebula elihlelwa ngomugqa wokuqala ngokulandelana okunyukayo. Ungaqala ukusebenzisa umsebenzi MATCH (KUDANWE KAKHULU) noma COUNTIF (COUNTIF) ukuze wenze isiqiniseko sokuthi inani olifunayo likhona ngisho kumugqa wokuqala.

Ezinye izici ezifana INDEX (INDEX) kanye MATCH (MATCH) ingasetshenziswa futhi ukubuyisa amanani kusuka kuthebula futhi isebenza kahle kakhulu. Sizozibheka ngokuhamba kwesikhathi kumjaho wethu webanga elide futhi sibone ukuthi zingaba namandla kangakanani futhi ziguquguquke kangakanani.

Isibonelo 1: Thola amanani okuthengisa esifunda esikhethiwe

Ake ngikukhumbuze futhi ukuthi umsebenzi HLOOKUP (HLOOKUP) ibheka kuphela inani elikumugqa ophezulu wethebula. Kulesi sibonelo, sizothola isamba sokuthengisa sesifunda esikhethiwe. Kubalulekile ukuthi sithole inani elilungile, ngakho-ke sisebenzisa izilungiselelo ezilandelayo:

  • Igama lesifunda lifakwe kuseli B7.
  • Ithebula lokubheka lesifunda linemigqa emibili futhi linwebeka kububanzi C2:F3.
  • Isamba sokuthengisa sikumugqa wesi-2 wetafula lethu.
  • I-agumenti yokugcina isethwe ukuze ithi FALSE ukuze kutholwe okufana ncamashi lapho kuseshwa.

Ifomula kuseli C7 ithi:

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

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

Imisebenzi engama-30 ye-Excel ngezinsuku ezingama-30: HLOOKUP

Uma igama lesifunda lingatholakali kumugqa wokuqala wethebula, umphumela womsebenzi HLOOKUP (GPR) izokwenza #AT (#N/A).

Imisebenzi engama-30 ye-Excel ngezinsuku ezingama-30: HLOOKUP

Isibonelo sesi-2: Thola isilinganiso sedethi ekhethiwe

Ngokuvamile uma usebenzisa umsebenzi HLOOKUP (HLOOKUP) idinga ukufana ncamashi, kodwa ngezinye izikhathi ukulinganiselwa okufanayo kufaneleka kakhulu. Isibonelo, uma izinkomba zishintsha ekuqaleni kwekota ngayinye, futhi izinsuku zokuqala zalawa makota zisetshenziswa njengezihloko zamakholomu (bheka umfanekiso ongezansi). Kulokhu, sebenzisa umsebenzi HLOOKUP (HLOOKUP) kanye nokulinganiselwa okufanayo, uzothola inkomba ehambisana nedethi ethile. Kulesi sibonelo:

  • Idethi ibhalwe kuseli C5.
  • Ithebula lokubheka inkomba linemigqa emibili futhi litholakala kububanzi C2:F3.
  • Ithebula lokubheka lihlelwa ngomugqa wedethi ngokulandelana okunyukayo.
  • Izinkomba zirekhodwa kulayini 2 wethebula lethu.
  • I-agumenti yokugcina yomsebenzi isethwe ukuze ithi TRUE ukuze kubhekwe okucishe kufane okufanayo.

Ifomula kuseli D5 ithi:

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

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

Uma idethi ingatholakali kumugqa wokuqala wethebula, umsebenzi HLOOKUP (HLOOKUP) izothola inani elikhulu eliseduze elingaphansi kwe-agumenti ukubheka (bheka_inani). Kulesi sibonelo, inani elifiswayo lithi Mashi 15. Alikho emugqeni wedethi, ngakho-ke ifomula izothatha inani 1 January bese ubuya 0,25.

Imisebenzi engama-30 ye-Excel ngezinsuku ezingama-30: HLOOKUP

shiya impendulo