30 Excel imagwira ntchito m'masiku 30: LOOKUP

Dzulo pa marathon 30 Excel imagwira ntchito m'masiku 30 tinasangalala ndi ntchitoyi REP (REPEAT) popanga ma chart mkati mwa cell ndikuigwiritsa ntchito powerengera mosavuta. Ndi Lolemba, ndipo nthawi inanso ndi nthawi yoti tivale zipewa zathu zoganiza bwino.

Pa tsiku la 16 la marathon, tidzaphunzira ntchitoyi YANG'ANANI (KUONA). Uyu ndi bwenzi lapamtima VLOOKUP (VLOOKUP) ndi HLOOKUP (GPR), koma imagwira ntchito mosiyana.

Choncho, tiyeni tiphunzire chiphunzitsocho ndikuyesa ntchitoyo mwakuchita YANG'ANANI (KUONA). Ngati muli ndi zambiri kapena zitsanzo za momwe mungagwiritsire ntchito izi, chonde gawanani nawo mu ndemanga.

Ntchito 16: LOOKUP

ntchito YANG'ANANI (LOOKUP) imabweretsa mtengo kuchokera pamzere umodzi, mzere umodzi, kapena kuchokera pamndandanda.

Kodi ndingagwiritse ntchito bwanji ntchito ya LOOKUP?

ntchito YANG'ANANI (LOOKUP) imabweretsa zotsatira, kutengera mtengo womwe mukuyang'ana. Ndi chithandizo chake mudzatha:

  • Pezani mtengo womaliza pamndandanda.
  • Pezani mwezi watha ndi malonda olakwika.
  • Sinthani kupambana kwa ophunzira kuchokera kuperesenti kupita ku magiredi a zilembo.

Syntax LOOKUP

ntchito YANG'ANANI (LOOKUP) ili ndi mitundu iwiri ya syntactic - vector ndi array. Mu mawonekedwe a vector, ntchitoyi imayang'ana mtengo mumzere kapena mzere womwe wapatsidwa, ndipo mumndandanda, imayang'ana mtengo mumzere woyamba kapena mzere woyamba.

Fomu ya vector ili ndi mawu awa:

LOOKUP(lookup_value,lookup_vector,result_vector)

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

  • kumachika (lookup_value) - Itha kukhala mawu, nambala, boolean, dzina, kapena ulalo.
  • lookup_vector (lookup_vector) - Gulu lokhala ndi mzere umodzi kapena gawo limodzi.
  • result_vector (zotsatira_vector) - mndandanda wokhala ndi mzere umodzi kapena gawo limodzi.
  • mikangano ranges lookup_vector (lookup_vector) ndi result_vector (result_vector) iyenera kukhala yofanana.

Mafomu osiyanasiyana ali ndi mawu awa:

LOOKUP(lookup_value,array)

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

  • kumachika (lookup_value) - Itha kukhala mawu, nambala, boolean, dzina, kapena ulalo.
  • kufufuza kumachitika molingana ndi kukula kwa gululo:
    • ngati gululi lili ndi zipilala zambiri kuposa mizere, ndiye kuti kusaka kumachitika pamzere woyamba;
    • ngati chiwerengero cha mizere ndi zipilala zili zofanana kapena pali mizere yambiri, ndiye kuti kufufuza kumapezeka muchigawo choyamba.
  • ntchitoyo imabweza mtengo womaliza kuchokera pamzere wopezeka/gawo.

Misampha LOOKUP (VIEW)

  • Kugwira ntchito YANG'ANANI (BROWSE) palibe njira yofufuzira yofanana ndendende, yomwe ili mkati VLOOKUP (VLOOKUP) ndi mu HLOOKUP (GPR). Ngati palibe mtengo wofufuzira, ndiye kuti ntchitoyi idzabwezera mtengo wapamwamba wosapitirira mtengo wofufuzira.
  • Gulu kapena vekitala yomwe ikufufuzidwa iyenera kusanjidwa mokwera, apo ayi ntchitoyo ikhoza kubweretsa zotsatira zolakwika.
  • Ngati mtengo woyamba mu gulu / vekitala yomwe ikuyang'aniridwa ndi yayikulu kuposa mtengo wowonera, ndiye kuti ntchitoyi itulutsa uthenga wolakwika. #AT (#N / A).

Chitsanzo 1: Kupeza mtengo womaliza pamndandanda

Mu mawonekedwe a gulu ntchito YANG'ANANI (LOOKUP) angagwiritsidwe ntchito kupeza mtengo womaliza pamndandanda.

Mtengo wothandiza wa Excel 9,99999999999999E + 307 monga nambala yayikulu kwambiri yomwe ingalembedwe mu cell. Muchilinganizo chathu, chidzakhazikitsidwa ngati mtengo womwe tikufuna. Zimaganiziridwa kuti chiwerengero chachikulu chotero sichidzapezeka, kotero ntchitoyo idzabwezera mtengo wotsiriza mu gawo la D.

Muchitsanzo ichi, manambala omwe ali mugawo la D amaloledwa kuti asasinthidwe, kuwonjezera apo, zolemba zimatha kubwera.

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

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

30 Excel imagwira ntchito m'masiku 30: LOOKUP

Chitsanzo 2: Pezani mwezi watha wokhala ndi mtengo woipa

Mu chitsanzo ichi, tigwiritsa ntchito mawonekedwe a vector YANG'ANANI (KUONA). Mzere D uli ndi mtengo wamalonda, ndipo gawo E lili ndi mayina a miyezi. M’miyezi ina, zinthu sizinali bwino, ndipo manambala olakwika ankawonekera m’maselo okhala ndi mtengo wamalonda.

Kuti mupeze mwezi watha wokhala ndi nambala yolakwika, chilinganizocho ndi YANG'ANANI (LOOKUP) idzayang'ana mtengo uliwonse wogulitsa kuti ndi wocheperapo 0 (kusagwirizana mu formula). Kenako, timagawanitsa 1 pazotsatira zake, timamaliza kapena 1, kapena uthenga wolakwika #DIV/0 (#GAWO/0).

Popeza mtengo wofunidwa ndi 2 sichipezeka, ntchitoyi idzasankha zomwe zapezeka 1, ndi kubweza mtengo wolingana ndi gawo E.

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

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

30 Excel imagwira ntchito m'masiku 30: LOOKUP

Kufotokozera: Mu chilinganizo ichi, m'malo mwa mkangano lookup_vector (lookup_vector) mawu osinthidwa 1/(D2:D8<0), yomwe imapanga mndandanda mu RAM ya kompyuta, yopangidwa ndi 1 ndi zolakwika #DIV/0 (#GAWO/0). 1 zikuwonetsa kuti selo lolingana mumitundu D2:D8 lili ndi mtengo wocheperako 0, ndi cholakwika #DIV/0 (#DIV/0) - chachikulu kuposa kapena chofanana ndi chiyani 0. Zotsatira zake, ntchito yathu ndikupeza womaliza 1 m'magulu omwe adapangidwa, ndipo kutengera izi, bweretsani dzina la mwezi kuchokera pamitundu E2:E8.

Chitsanzo 3: Kusintha kupambana kwa ophunzira kuchokera kuperesenti kupita ku magiredi

M'mbuyomu, tathetsa kale vuto lofananalo pogwiritsa ntchito ntchitoyi VLOOKUP (VPR). Lero tigwiritsa ntchito YANG'ANANI (VIEW) mu mawonekedwe a vector kuti mutembenuzire kupambana kwa ophunzira kuchokera kuperesenti kupita ku magiredi a zilembo. Mosiyana VLOOKUP (VLOOKUP) ya ntchito YANG'ANANI (KUONA) Ziribe kanthu ngati maperesenti ali mgawo loyamba la tebulo. Mutha kusankha mzati uliwonse.

Muchitsanzo chotsatirachi, zigoli zili mu ndime D, zosanjidwa mwadongosolo lokwera, ndipo zilembo zofananirazo zili mugawo C, kumanzere kwa gawo lomwe likufufuzidwa.

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

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

30 Excel imagwira ntchito m'masiku 30: LOOKUP

Siyani Mumakonda