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

Izolo kumarathon 30 Excel isebenza ngezinsuku ezingama-30 sijabule ngomsebenzi REPT (PHINDA) ngokwakha amashadi ngaphakathi kweseli futhi uyisebenzisele ukubala okulula. KungoMsombuluko, futhi sekuyisikhathi sokuthi siphinde sigqoke izigqoko zethu zokucabanga.

Ngosuku lwama-16 lwemarathon, sizofunda umsebenzi BHEKA (BUKA). Umngane oseduze lo I-VLOOKUP (VLOOKUP) kanye HLOOKUP (GPR), kodwa isebenza ngokuhlukile kancane.

Ngakho-ke, ake sitadishe ithiyori futhi sihlole umsebenzi ngokusebenza BHEKA (BUKA). Uma unolwazi olwengeziwe noma izibonelo zokusebenzisa lesi sici, sicela wabelane ngazo kumazwana.

Umsebenzi 16: LOOKUP

Umsebenzi BHEKA (LOOKUP) ibuyisela inani elisuka kumugqa owodwa, ikholomu eyodwa, noma kumalungu afanayo.

Ngingawusebenzisa kanjani umsebenzi we-LOOKUP?

Umsebenzi BHEKA (LOOKUP) ibuyisela umphumela, kuye ngevelu oyifunayo. Ngosizo lwayo uzokwazi uku:

  • Thola inani lokugcina kukholomu.
  • Thola inyanga yokugcina ngokuthengisa okungekuhle.
  • Guqula impumelelo yomfundi isuke kumaphesenti iye kumabanga ezinhlamvu.

I-syntax LOOKUP

Umsebenzi BHEKA (I-LOOKUP) inamafomu amabili e-syntactic - i-vector kanye nohlelo. Ngefomu le-vector, umsebenzi ubheka inani kukholomu noma umugqa onikeziwe, futhi ngendlela yamalungu afanayo, ubheka inani elikumugqa wokuqala noma ikholomu yamalungu afanayo.

Ifomu le-vector line-syntax elandelayo:

LOOKUP(lookup_value,lookup_vector,result_vector)

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

  • ukubheka (lokup_value) - Kungaba umbhalo, inombolo, i-boolean, igama, noma isixhumanisi.
  • lookup_vector (lokup_vector) – Ububanzi obuhlanganisa umugqa owodwa noma ikholomu eyodwa.
  • umphumela_vector (result_vector) – ububanzi obuhlanganisa umugqa owodwa noma ikholomu eyodwa.
  • amabanga wokungqubuzana lookup_vector (lokup_vector) kanye umphumela_vector (result_vector) kufanele kube usayizi ofanayo.

Ifomu lamalungu afanayo line-syntax elandelayo:

LOOKUP(lookup_value,array)

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

  • ukubheka (lokup_value) - Kungaba umbhalo, inombolo, i-boolean, igama, noma isixhumanisi.
  • usesho lwenziwa ngokobukhulu belungu elifanayo:
    • uma uhlu lunamakholomu amaningi kunemigqa, khona-ke ukusesha kwenzeka kumugqa wokuqala;
    • uma inani lemigqa namakholomu lifana noma kunemigqa eyengeziwe, ukusesha kwenzeka kukholamu yokuqala.
  • umsebenzi ubuyisela inani lokugcina ukusuka kumugqa/ikholomu etholiwe.

Izicupho LOOKUP (BUKA)

  • Kuyasebenza BHEKA (BHEKA) ayikho inketho yokusesha okufana ncamashi, okungaphakathi I-VLOOKUP (VLOOKUP) kanye ne-in HLOOKUP (GPR). Uma lingekho inani lokusesha, umsebenzi uzobuyisela inani eliphezulu elingadluli inani lokusesha.
  • Amalungu afanayo noma i-vector eseshwayo kufanele ihlungwe ngohlelo olukhuphukayo, ngaphandle kwalokho umsebenzi ungase ubuyisele umphumela ongalungile.
  • Uma inani lokuqala ohlwini/ivekhtha ebhekwayo likhulu kunevelu yokubheka, umsebenzi uzokhiqiza umlayezo wephutha. #AT (#N/A).

Isibonelo 1: Ukuthola inani lokugcina kukholomu

Ngendlela yomsebenzi wamalungu afanayo BHEKA (LOOKUP) ingasetshenziswa ukuze kutholwe inani lokugcina kukholomu.

I-Excel usizo lokucaphuna inani 9,99999999999999E + 307 njengenombolo enkulu kakhulu engabhalwa kuseli. Kufomula yethu, izosethwa njengenani elifiswayo. Kucatshangwa ukuthi inani elikhulu kangaka ngeke litholakale, ngakho umsebenzi uzobuyisela inani lokugcina kukholomu D.

Kulesi sibonelo, izinombolo ezikukholomu D zivunyelwe ukuthi zingahlungwa, ngaphezu kwalokho, amanani ombhalo angase ahlangane.

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

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

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

Isibonelo sesi-2: Thola inyanga yokugcina enevelu engeyinhle

Kulesi sibonelo, sizosebenzisa umumo we-vector BHEKA (BUKA). Ikholomu D iqukethe amanani entengo, futhi ikholomu E iqukethe amagama ezinyanga. Ezinyangeni ezithile, izinto azihambanga kahle, futhi kwavela izinombolo ezingezinhle kumaseli anamanani okuthengisa.

Ukuze uthole inyanga yokugcina enenombolo enegethivu, ifomula ethi BHEKA (I-LOOKUP) izohlola ivelu ngayinye yokuthengisa ukuthi ingaphansi kwalokhu 0 (ukungalingani kufomula). Okulandelayo, sihlukanisa 1 kumphumela, sigcina ngokunye 1, noma umlayezo wephutha #DIV/0 (#ISIGABA/0).

Njengoba inani oyifunayo 2 ayitholakali, umsebenzi uzokhetha okokugcina okutholakele 1, bese ubuyisela inani elihambisanayo kukholomu E.

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

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

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

Incazelo: Kule fomula, esikhundleni sempikiswano lookup_vector (lokup_vector) isisho sishintshiwe 1/(D2:D8<0), okwenza amalungu afanayo ku-RAM yekhompyutha, ehlanganisa 1 kanye namanani amaphutha #DIV/0 (#ISIGABA/0). 1 ibonisa ukuthi iseli elihambisanayo kububanzi D2:D8 iqukethe inani elingaphansi 0, kanye nephutha #DIV/0 (#DIV/0) – yini enkulu noma elingana nayo 0. Ngenxa yalokho, umsebenzi wethu ukuthola owokugcina 1 ohlwini olubonakalayo oludaliwe, futhi ngokusekelwe kulokhu, buyisela igama lenyanga ebangeni elingu-E2:E8.

Isibonelo sesi-3: Ukuguqula impumelelo yomfundi isuke kumaphesenti iye kumamaki ezinhlamvu

Ngaphambilini, sesivele siyixazulule inkinga efanayo sisebenzisa umsebenzi I-VLOOKUP (VPR). Namuhla sizosebenzisa umsebenzi BHEKA (BUKA) ngendlela ye-vector ukuze uguqule impumelelo yomfundi isuke kumaphesenti iye kumamaki ezinhlamvu. Ngokungafani I-VLOOKUP (VLOOKUP) ngomsebenzi BHEKA (BUKA) Akunandaba ukuthi amaphesenti akukholomu yokuqala yethebula. Ungakhetha ngokuphelele noma iyiphi ikholomu.

Esibonelweni esilandelayo, amaphuzu akukholomu D, ahlelwe ngokulandelana okunyukayo, futhi izinhlamvu zawo ezihambisanayo zikukholomu C, kwesokunxele sekholomu eseshwayo.

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

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

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

shiya impendulo