Kwa kutumia chaguo za kukokotoa za VLOOKUP ili kubadilisha thamani

Nani ni mvivu sana au hana wakati wa kusoma - tazama video. Maelezo na nuances ni katika maandishi hapa chini.

Uundaji wa shida

Kwa hivyo, tuna meza mbili - meza ya kuagiza и Orodha ya bei:

Kazi ni kubadilisha bei kutoka kwa orodha ya bei kwenye jedwali la maagizo kiatomati, ukizingatia jina la bidhaa ili baadaye uweze kuhesabu gharama.

Suluhisho

Katika seti ya kazi ya Excel, chini ya kategoria Marejeleo na safu (Kutafuta na kumbukumbu) kuna kazi VPR (VLOOKUP).Kazi hii inatafuta thamani fulani (kwa mfano wetu, hii ni neno "Apples") katika safu ya kushoto ya jedwali maalum (orodha ya bei) inayohamia kutoka juu hadi chini na, baada ya kuipata, inaonyesha yaliyomo kwenye seli iliyo karibu. (Rubles 23) .Kiratibu, utendakazi wa chaguo hili la kukokotoa unaweza kuwakilishwa Kwa hivyo:

Kwa urahisi wa matumizi zaidi ya chaguo la kukokotoa, fanya jambo moja mara moja - toa anuwai ya seli kwenye orodha ya bei jina lako mwenyewe. Ili kufanya hivyo, chagua seli zote za orodha ya bei isipokuwa "kichwa" (G3: H19), chagua kutoka kwenye menyu. Ingiza - Jina - Weka (Ingiza - Jina - Fafanua) au waandishi wa habari CTRL + F3 na ingiza jina lolote (hakuna nafasi) kama Bei… Sasa, katika siku zijazo, unaweza kutumia jina hili kuunganisha kwenye orodha ya bei.

Sasa tunatumia kazi VPR… Chagua kisanduku ambacho kitaingizwa (D3) na ufungue kichupo Fomula - Uingizaji wa Kazi (Mfumo - Ingiza Kazi)… Katika kategoria Marejeleo na safu (Tafuta na Marejeleo) pata kitendaji VPR (VLOOKUP) na vyombo vya habari OK… Dirisha la kuingiza hoja za chaguo za kukokotoa litaonekana:

Kwa kutumia chaguo za kukokotoa za VLOOKUP ili kubadilisha thamani

Tunawajaza kwa zamu:

  • Thamani inayotakiwa (Thamani ya Kutafuta) - jina la bidhaa ambayo chaguo la kukokotoa linapaswa kupata katika safu wima ya kushoto kabisa ya orodha ya bei. Kwa upande wetu, neno "Apples" kutoka kwa seli B3.
  • Meza (Safu ya Jedwali) - Jedwali ambalo maadili taka ya uXNUMXbuXNUMXbare yamechukuliwa, ambayo ni, orodha yetu ya bei. Kwa marejeleo, tunatumia jina letu wenyewe "Bei" tuliyopewa hapo awali. Ikiwa haukutoa jina, unaweza kuchagua meza tu, lakini usisahau kubonyeza kitufe F4kubandika kiungo kwa ishara za dola, kwa sababu vinginevyo, itateleza chini wakati wa kunakili fomula yetu hadi seli zingine kwenye safu wima D3:D30.
  • Nambari_ya_safu (Nambari ya faharasa ya safu wima) - nambari ya serial (sio barua!) Ya safu katika orodha ya bei ambayo tutachukua maadili ya bei. Safu ya kwanza ya orodha ya bei iliyo na majina imepewa nambari 1, kwa hivyo tunahitaji bei kutoka kwa safu iliyo na nambari 2.
  • muda_kuangalia (Utafutaji wa safu) - maadili mawili pekee yanaweza kuingizwa katika uwanja huu: FALSE au TRUE:
      • Ikiwa thamani imeingizwa 0 or KUSEMA UONGO (UONGO), basi kwa kweli hii ina maana kwamba utafutaji pekee unaruhusiwa mechi halisi, yaani, ikiwa chaguo la kukokotoa halitapata bidhaa isiyo ya kawaida iliyobainishwa kwenye jedwali la kuagiza katika orodha ya bei (kwa mfano, "Nazi" imeingizwa), itazalisha hitilafu ya #N/A (hakuna data).
      • Ikiwa thamani imeingizwa 1 or KWELI (KWELI), basi hii ina maana kwamba unaruhusu utafutaji si kwa ajili ya halisi, lakini takriban mechi, yaani katika kesi ya "nazi", kazi itajaribu kupata bidhaa yenye jina ambalo ni karibu iwezekanavyo na "nazi" na kurudi bei ya jina hili. Katika hali nyingi, ubadilishaji wa takriban kama huo unaweza kucheza hila kwa mtumiaji kwa kubadilisha thamani ya bidhaa isiyo sahihi ambayo ilikuwa kweli! Kwa hivyo kwa shida nyingi za biashara, utaftaji wa takriban ni bora kutoruhusu. Isipokuwa ni wakati tunatafuta nambari na sio maandishi - kwa mfano, wakati wa kuhesabu Punguzo la Hatua.

Kila kitu! Inabakia kushinikiza OK na unakili kitendakazi kilichoingizwa kwenye safu nzima.

# N / A makosa na ukandamizaji wao

kazi VPR (VLOOKUP) hurejesha hitilafu ya #N/A (#N/A) kama a:

  • Utafutaji halisi umewezeshwa (hoja Mtazamo wa muda = 0) na jina linalohitajika halipo Meza.
  • Utafutaji mbaya umejumuishwa (Mtazamo wa muda = 1), lakini Meza, ambamo utafutaji unafanyika haujapangwa kwa mpangilio wa kupanda wa majina.
  • Umbizo la seli ambapo thamani inayotakiwa ya jina inatoka (kwa mfano, B3 kwa upande wetu) na muundo wa seli za safu wima ya kwanza (F3: F19) ya jedwali ni tofauti (kwa mfano, nambari na maandishi. ) Kesi hii ni ya kawaida wakati wa kutumia nambari za nambari (nambari za akaunti, vitambulisho, tarehe, n.k.) badala ya majina ya maandishi. Katika kesi hii, unaweza kutumia kazi Ч и TEXT kubadilisha muundo wa data. Itaonekana kitu kama hiki:

    =VLOOKUP(TEXT(B3),bei,0)

    Unaweza kusoma zaidi kuhusu hili hapa.

  • Chaguo za kukokotoa haziwezi kupata thamani inayohitajika kwa sababu msimbo una nafasi au vibambo visivyoweza kuchapishwa (vipumziko vya mstari, n.k.). Katika kesi hii, unaweza kutumia kazi za maandishi TRIM (TRIM) и Magazeti(SAFI) kuwaondoa:

    =VLOOKUP(TRIMSPACES(CLEAN(B3)),bei,0)

    =VLOOKUP(TRIM(CLEAN(B3)));bei;0)

Ili kukandamiza ujumbe wa makosa # N / A (#N/A) katika hali ambapo kazi haiwezi kupata mechi halisi, unaweza kutumia kazi IFERRO (IFERROR)… Kwa hivyo, kwa mfano, ujenzi huu unakumbatia hitilafu zozote zinazozalishwa na VLOOKUP na kuzibadilisha na sufuri:

= IFERROR (VLOOKUP (B3, bei, 2, 0), 0)

= IFERROR (VLOOKUP (B3; bei; 2; 0); 0)

PS

Ikiwa unahitaji kutoa sio thamani moja, lakini seti nzima mara moja (ikiwa kuna tofauti kadhaa), basi utakuwa na shamanize na fomula ya safu. au utumie kipengele kipya cha XLOOKUP kutoka Office 365.

 

  • Toleo lililoboreshwa la chaguo za kukokotoa za VLOOKUP (VLOOKUP 2).
  • Uhesabuji wa haraka wa punguzo la hatua (masafa) kwa kutumia chaguo la kukokotoa la VLOOKUP.
  • Jinsi ya kutengeneza "VLOOKUP ya kushoto" kwa kutumia vitendaji vya INDEX na MATCH
  • Jinsi ya kutumia kitendakazi cha VLOOKUP kujaza fomu na data kutoka kwenye orodha
  • Jinsi ya kutoa sio ya kwanza, lakini maadili yote kutoka kwa meza mara moja
  • VLOOKUP2 na VLOOKUP3 vitendaji kutoka kwa programu jalizi ya PLEX

 

Acha Reply