Funkcijas VLOOKUP izmantošana vērtību aizstāšanai

Kam ir par slinku vai nav laika lasīt – skatieties video. Sīkāka informācija un nianses ir zemāk esošajā tekstā.

Problēmas formulēšana

Tātad, mums ir divas tabulas - pasūtījumu galds и cenrādis:

Uzdevums ir automātiski aizstāt cenas no cenrāža pasūtījumu tabulā, koncentrējoties uz preces nosaukumu, lai vēlāk varētu aprēķināt izmaksas.

Šķīdums

Excel funkciju komplektā zem kategorijas Atsauces un masīvi (Uzmeklēšana un atsauce) ir funkcija VPR (MEKLĒT).Šī funkcija meklē norādīto vērtību (mūsu piemērā tas ir vārds "Āboli") norādītās tabulas (cenrāža) pašā kreisajā kolonnā, virzoties no augšas uz leju un, to atradusi, parāda blakus esošās šūnas saturu. (23 rubļi) .Shēmiski šīs funkcijas darbību var attēlot Tātad:

Lai atvieglotu funkcijas turpmāko lietošanu, veiciet vienu lietu uzreiz – piešķiriet cenrāža šūnu diapazonam savu nosaukumu. Lai to izdarītu, atlasiet visas cenrāža šūnas, izņemot “galveni” (G3: H19), atlasiet no izvēlnes Ievietot - Nosaukums - Piešķirt (Ievietot — Nosaukums — Definēt) vai nospiediet CTRL + F3 un ievadiet jebkuru nosaukumu (bez atstarpēm), piemēram Сena… Tagad turpmāk varat izmantot šo nosaukumu, lai izveidotu saiti uz cenrādi.

Tagad mēs izmantojam funkciju VPR… Atlasiet šūnu, kurā tā tiks ievadīta (D3) un atveriet cilni Formulas – Funkcijas ievietošana (Formulas — Ievietot funkciju)… Kategorijā Atsauces un masīvi (Uzmeklēšana un atsauce) atrast funkciju VPR (MEKLĒT) un nospiediet OK... Parādīsies logs funkcijas argumentu ievadīšanai:

Funkcijas VLOOKUP izmantošana vērtību aizstāšanai

Mēs tos aizpildām pēc kārtas:

  • Vēlamā vērtība (Uzmeklēšanas vērtība) – preces nosaukums, kas funkcijai jāatrod cenrāža kreisajā malā. Mūsu gadījumā vārds “Āboli” no šūnas B3.
  • Tabula (Tabulu masīvs) - tabula, no kuras tika ņemtas vēlamās vērtības uXNUMXbuXNUMX, tas ir, mūsu cenrādis. Uzziņai mēs izmantojam savu iepriekš norādīto nosaukumu “Cena”. Ja nenorādījāt nosaukumu, varat vienkārši atlasīt tabulu, bet neaizmirstiet nospiest pogu F4lai piespraustu saiti ar dolāra zīmēm, jo pretējā gadījumā tā noslīdēs uz leju, kopējot mūsu formulu uz pārējām šūnām kolonnā D3:D30.
  • Kolonnas_numurs (Slejas indeksa numurs) – sērijas numurs (nevis burts!) Cenrādī tās ailes, no kuras ņemsim cenu vērtības. Cenrāža pirmā kolonna ar nosaukumiem ir numurēta ar 1, tāpēc mums ir nepieciešama cena no kolonnas ar numuru 2.
  • intervāla_uzmeklēšana (Diapazona meklēšana) – šajā laukā var ievadīt tikai divas vērtības: FALSE vai TRUE:
      • Ja ir ievadīta vērtība 0 or MELO (FALSE), tad patiesībā tas nozīmē, ka ir atļauta tikai meklēšana precīza atbilstība, proti, ja funkcija neatrod cenrādī pasūtījuma tabulā norādīto nestandarta preci (ja ir ievadīts, piemēram, “Kokosrieksts”), tā ģenerēs kļūdu #N/A (nav datu).
      • Ja ir ievadīta vērtība 1 or TRUE (PATIESA), tad tas nozīmē, ka atļaujat meklēt nevis precīzu, bet aptuvenā atbilstība, proti, “kokosriekstu” gadījumā funkcija mēģinās atrast preci ar nosaukumu, kas pēc iespējas tuvāks “kokosriekstam”, un atgriezīs cenu par šo nosaukumu. Vairumā gadījumu šāda aptuvenā aizstāšana var apmānīt lietotāju, aizstājot ar nepareizā produkta vērtību, kas patiesībā bija tur! Tāpēc lielākajai daļai reālu biznesa problēmu aptuveno meklēšanu labāk neatļaut. Izņēmums ir gadījumi, kad mēs meklējam skaitļus, nevis tekstu – piemēram, aprēķinot soļu atlaides.

Viss! Atliek nospiest OK un kopējiet ievadīto funkciju visā kolonnā.

# N / A kļūdas un to apspiešana

funkcija VPR (MEKLĒT) atgriež #N/A kļūdu (#N/A) ja:

  • Precīzā meklēšana ir iespējota (arguments Intervāla skats = 0) un vēlamais vārds nav norādīts Tabula.
  • Iekļauta rupja meklēšana (Intervāla skats = 1), bet Tabula, kurā notiek meklēšana, nav sakārtots nosaukumu augošā secībā.
  • Tās šūnas formāts, no kuras nāk vajadzīgā nosaukuma vērtība (mūsu gadījumā, piemēram, B3), un tabulas pirmās kolonnas šūnu formāts (F3: F19) atšķiras (piemēram, ciparu un teksta). ). Šis gadījums ir īpaši raksturīgs, ja teksta nosaukumu vietā izmanto ciparu kodus (konta numurus, identifikatorus, datumus utt.). Šajā gadījumā varat izmantot funkcijas Ч и TEKSTS lai konvertētu datu formātus. Tas izskatīsies apmēram šādi:

    =VLOOKUP(TEKSTS(B3),cena,0)

    Vairāk par to varat lasīt šeit.

  • Funkcija nevar atrast vajadzīgo vērtību, jo kods satur atstarpes vai neredzamas, nedrukājamas rakstzīmes (rindiņu pārtraukumus utt.). Šajā gadījumā varat izmantot teksta funkcijas TRIM (TRIM) и PRINT(TĪRS) lai tos noņemtu:

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

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

Lai izslēgtu kļūdas ziņojumu # N / A (#N/A) gadījumos, kad funkcija nevar atrast precīzu atbilstību, varat izmantot funkciju IFERROR (IFERROR)… Tātad, piemēram, šī konstrukcija pārtver visas kļūdas, ko rada VLOOKUP, un aizstāj tās ar nullēm:

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

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

PS

Ja jums ir nepieciešams iegūt nevis vienu vērtību, bet visu komplektu uzreiz (ja ir vairākas dažādas), tad jums būs jāšamanizē ar masīva formulu. vai izmantojiet jauno XLOOKUP līdzekli no Office 365.

 

  • Uzlabota funkcijas VLOOKUP versija (VLOOKUP 2).
  • Ātrs soļu (diapazona) atlaižu aprēķins, izmantojot funkciju VLOOKUP.
  • Kā izveidot “kreiso VLOOKUP”, izmantojot funkcijas INDEX un MATCH
  • Kā izmantot funkciju VLOOKUP, lai aizpildītu veidlapas ar datiem no saraksta
  • Kā no tabulas uzreiz izvilkt ne pirmo, bet visas vērtības
  • VLOOKUP2 un VLOOKUP3 funkcijas no PLEX papildinājuma

 

Atstāj atbildi