Funkcija VLOOKUP - Ievads
Funkcija VLOOKUP ir etalons.
Jūs zināt kaut ko programmā Excel, ja zināt, kā izmantot funkciju VLOOKUP.
Ja jums tā nav, labāk neuzskaitiet Excel kā vienu no savām spēcīgajām jomām savā CV.
Esmu piedalījusies paneļu intervijās, kurās, tiklīdz kandidāts kā savu kompetences jomu minēja Excel, pirmā lieta, kas tika uzdota, bija - jūs to sapratāt - funkcija VLOOKUP.
Tagad, kad mēs zinām, cik svarīga ir šī Excel funkcija, ir jēga to pilnībā izskaust, lai varētu lepni teikt - “Es Excel zinu vienu vai divas lietas”.
Šī būs milzīga VLOOKUP apmācība (pēc maniem standartiem).
Es apskatīšu visu, kas par to jāzina, un pēc tam parādīšu noderīgus un praktiskus VLOOKUP piemērus.
Tātad piesprādzējiet.
Ir pienācis laiks pacelšanās brīdim.
Kad programmā Excel izmantot funkciju VLOOKUP?
Funkcija VLOOKUP ir vislabāk piemērota situācijām, kad kolonnā meklējat atbilstošu datu punktu un, kad tiek atrasts atbilstošs datu punkts, dodaties pa labi šajā rindā un iegūstat vērtību no šūnas, kas ir noteikts skaits kolonnas pa labi.
Apskatīsim vienkāršu piemēru, lai saprastu, kad programmā Excel izmantot Vlookup.
Atcerieties, kad eksāmenu rezultātu saraksts bija izlikts un pielīmēts pie ziņojumu dēļa, un visi mēdza kļūt traki, atrodot savus vārdus un punktu skaitu (vismaz tā tas bija, kad es mācījos skolā).
Lūk, kā tas darbojās:
- Jūs dodaties uz ziņojumu dēļa un sākat meklēt savu vārdu vai reģistrācijas numuru (ar pirkstu nolaižot sarakstu no augšas uz leju).
- Tiklīdz pamanāt savu vārdu, jūs pārvietojat acis pa labi no vārda/reģistrācijas numura, lai redzētu savus rezultātus.
Un tieši to jūsu labā dara Excel VLOOKUP funkcija (izmantojiet šo piemēru nākamajā intervijā).
Funkcija VLOOKUP slejā meklē noteiktu vērtību (iepriekš minētajā piemērā tas bija jūsu vārds), un, atrodot norādīto atbilstību, tā atgriež vērtību tajā pašā rindā (iegūtās atzīmes).
Sintakse
= MEKLĒŠANA (uzmeklēšanas_vērtība, tabulas_masīvs, kolonnas_indeksa_numurs, [diapazona_meklējums])
Ievades argumenti
- lookup_value - šī ir uzmeklēšanas vērtība, kuru mēģināt atrast tabulas kreisajā slejā. Tā var būt vērtība, šūnas atsauce vai teksta virkne. Rezultātu lapas piemērā tas būtu jūsu vārds.
- table_array - tas ir tabulas masīvs, kurā jūs meklējat vērtību. Tas varētu būt atsauce uz šūnu diapazonu vai nosauktu diapazonu. Rezultātu lapas piemērā šī būtu visa tabula, kurā ir rādītājs visiem par katru priekšmetu
- col_index - tas ir kolonnas indeksa numurs, no kura vēlaties iegūt atbilstošo vērtību. Rezultātu lapas piemērā, ja vēlaties iegūt matemātikas rādītājus (kas ir tabulas pirmā sleja, kurā ir rādītāji), skatieties 1. slejā. Ja vēlaties rādītājus par fiziku, skatiet sleju. 2.
- [range_lookup] - šeit jūs norādāt, vai vēlaties precīzu atbilstību vai aptuvenu atbilstību. Ja tas tiek izlaists, tā noklusējuma vērtība ir TRUE - aptuvena atbilstība (skatīt papildu piezīmes zemāk).
Papildu piezīmes (garlaicīgi, bet svarīgi zināt)
- Atbilstība var būt precīza (FALSE vai 0 in range_lookup) vai aptuvena (TRUE vai 1).
- Aptuvenajā meklēšanā pārliecinieties, vai saraksts ir sakārtots augošā secībā (no augšas uz leju), pretējā gadījumā rezultāts var būt neprecīzs.
- Ja range_lookup ir TRUE (aptuvens uzmeklējums) un dati ir sakārtoti augošā secībā:
- Ja funkcija VLOOKUP nevar atrast vērtību, tā atgriež lielāko vērtību, kas ir mazāka par lookup_value.
- Tas atgriež kļūdu #N/A, ja uzmeklēšanas_vērtība ir mazāka par mazāko vērtību.
- Ja lookup_value ir teksts, var izmantot aizstājējzīmes (skatiet piemēru zemāk).
Tagad, cerot, ka jums ir pamatzināšanas par to, ko var izmantot funkcija VLOOKUP, nomizosim šo sīpolu un apskatīsim dažus praktiskus VLOOKUP funkcijas piemērus.
10 Excel VLOOKUP piemēri (pamata un papildu)
Šeit ir 10 noderīgi Excel Vlookup izmantošanas piemēri, kas parādīs, kā to izmantot ikdienas darbā.
1. piemērs - Breda matemātiskā rezultāta atrašana
Tālāk redzamajā VLOOKUP piemērā man ir saraksts ar studentu vārdiem kreisajā kreisajā slejā un atzīmes dažādos priekšmetos no B līdz E slejai.
Tagad ķersimies pie darba un izmantosim funkciju VLOOKUP, lai tā darbotos vislabāk. No iepriekš minētajiem datiem man jāzina, cik daudz Breds ieguva Matemātikā.
No iepriekš minētajiem datiem man jāzina, cik daudz Breds ieguva Matemātikā.
Šeit ir VLOOKUP formula, kas atgriezīs Breda matemātikas rezultātu:
= VLOOKUP ("Brad", $ A $ 3: $ E $ 10,2,0)
Iepriekšminētajai formulai ir četri argumenti:
- “Breds: - tā ir uzmeklēšanas vērtība.
- $ A $ 3: $ E $ 10 - tas ir šūnu diapazons, kurā mēs meklējam. Atcerieties, ka Excel meklē uzmeklēšanas vērtību kreisajā slejā. Šajā piemērā tas meklētu nosaukumu Brad A3: A10 (kas ir norādītā masīva kreisākā sleja).
- 2 - Kad funkcija pamanīs Breda vārdu, tā nonāks masīva otrajā kolonnā un atdos vērtību tajā pašā rindā kā Breda vērtība. Vērtība 2 šeit norādīja, ka mēs meklējam rezultātu no norādītā masīva otrās kolonnas.
- 0 - tas liek funkcijai VLOOKUP meklēt tikai precīzas atbilstības.
Lūk, kā darbojas VLOOKUP formula iepriekš minētajā piemērā.
Pirmkārt, slejā kreisajā pusē tiek meklēta vērtība Brad. Tas iet no augšas uz leju un atrod vērtību šūnā A6.
Tiklīdz tā atrod vērtību, tā iet pa labi otrajā kolonnā un iegūst vērtību tajā.
Jūs varat izmantot to pašu formulas konstrukciju, lai iegūtu atzīmes ikvienam no priekšmetiem.
Piemēram, lai atrastu Marijas atzīmes ķīmijā, izmantojiet šādu VLOOKUP formulu:
= VLOOKUP ("Marija", 3 ASV dolāri: 10,4 ASV dolāri)
Iepriekš minētajā piemērā uzmeklēšanas vērtība (studenta vārds) tiek ievadīta pēdiņās. Varat arī izmantot šūnu atsauci, kurā ir uzmeklēšanas vērtība.
Šūnu atsauces izmantošanas priekšrocība ir tā, ka tā padara formulu dinamisku.
Piemēram, ja jums ir šūna ar studenta vārdu un jūs iegūstat matemātikas rezultātu, rezultāts tiek automātiski atjaunināts, mainot studenta vārdu (kā parādīts zemāk):
Ja ievadāt uzmeklēšanas vērtību, kas nav atrodama kreisajā slejā, tā atgriež kļūdu #N/A.
2. piemērs - divvirzienu uzmeklēšana
Iepriekš minētajā 1. piemērā mēs stingri kodējām kolonnas vērtību. Tādējādi formula vienmēr atdotu matemātikas rezultātu, jo kolonnas indeksa numuru esam izmantojuši 2.
Bet ko darīt, ja vēlaties padarīt VLOOKUP vērtību un kolonnu indeksa numuru dinamisku. Piemēram, kā parādīts zemāk, varat mainīt vai nu studenta vārdu, vai mācību priekšmeta nosaukumu, un VLOOKUP formula iegūst pareizo rezultātu. Šis ir divvirzienu VLOOKUP formulas piemērs.
Šis ir divvirzienu VLOOKUP funkcijas piemērs.
Lai izveidotu šo divvirzienu uzmeklēšanas formulu, kolonna ir jāpadara dinamiska. Tātad, kad lietotājs maina tēmu, formula automātiski izvēlas pareizo kolonnu (2 matemātikas gadījumā, 3 fizikas gadījumā utt.).
Lai to izdarītu, kā kolonnas arguments jāizmanto funkcija MATCH.
Šeit ir VLOOKUP formula, kas to darīs:
= VLOOKUP (G4, $ A $ 3: $ E $ 10, MATCH (H3, $ A $ 2: $ E $ 2,0), 0)
Iepriekšminētā formula izmanto kolonnas numuru MATCH (H3, $ A $ 2: $ E $ 2,0). Funkcija MATCH ņem objekta nosaukumu kā uzmeklēšanas vērtību (H3) un atgriež savu pozīciju A2: E2. Tādējādi, ja izmantojat matemātiku, tā atgriezīs 2, jo matemātika ir atrodama B2 (kas ir otrā šūna norādītajā masīva diapazonā).
3. piemērs. Nolaižamo sarakstu izmantošana kā uzmeklēšanas vērtības
Iepriekš minētajā piemērā mums dati ir jāievada manuāli. Tas varētu aizņemt daudz laika un radīt kļūdas, it īpaši, ja jums ir milzīgs uzmeklēšanas vērtību saraksts.
Šādos gadījumos laba ideja ir izveidot uzmeklēšanas vērtību nolaižamo sarakstu (šajā gadījumā tas varētu būt studentu vārdi un priekšmeti) un pēc tam vienkārši izvēlēties no saraksta.
Pamatojoties uz atlasi, formula automātiski atjauninātu rezultātu.
Kaut kas, kā parādīts zemāk:
Tas ir labs informācijas paneļa komponents, jo jums var būt milzīga datu kopa ar simtiem studentu aizmugurē, bet gala lietotājs (teiksim, skolotājs) var ātri iegūt skolēna atzīmes mācību priekšmetā, vienkārši veicot atlasi no nolaižamais.
Kā to izdarīt:
Šajā gadījumā izmantotā VLOOKUP formula ir tāda pati kā 2. piemērā.
= VLOOKUP (G4, $ A $ 3: $ E $ 10, MATCH (H3, $ A $ 2: $ E $ 2,0), 0)
Uzmeklēšanas vērtības ir pārveidotas nolaižamajos sarakstos.
Tālāk ir norādītas nolaižamā saraksta izveides darbības.
- Atlasiet šūnu, kurā vēlaties nolaižamo sarakstu. Šajā piemērā G4 mēs vēlamies studentu vārdus.
- Dodieties uz Dati -> Datu rīki -> Datu validācija.
- Lodziņā Datu validācijas dialoglodziņš iestatījumu cilnē nolaižamajā izvēlnē Atļaut atlasiet Saraksts.
- Avotā atlasiet $ A $ 3: $ A $ 10
- Noklikšķiniet uz Labi.
Tagad šūnā G4 būs nolaižamais saraksts. Līdzīgi H3 varat izveidot vienu priekšmetiem.
4. piemērs. Trīsceļu uzmeklēšana
Kas ir trīsvirzienu uzmeklēšana?
2. piemērā esam izmantojuši vienu uzmeklēšanas tabulu ar rādītājiem skolēniem dažādos priekšmetos. Šis ir divvirzienu uzmeklēšanas piemērs, jo mēs iegūstam divus mainīgos, lai iegūtu rezultātu (studenta vārdu un priekšmeta nosaukumu).
Pieņemsim, ka pēc gada studentam ir trīs dažādi eksāmenu līmeņi - vienības tests, vidusposma eksāmens un gala eksāmens (tas bija man, kad es biju students).
Trīs virzienu uzmeklēšana būtu iespēja iegūt studenta atzīmes par noteiktu priekšmetu no noteiktā eksāmena līmeņa.
Kaut kas, kā parādīts zemāk:
Iepriekš minētajā piemērā funkciju VLOOKUP var meklēt trīs dažādās tabulās (vienības pārbaude, vidusposma eksāmens un gala eksāmens) un atdot norādītā priekšmeta studenta rezultātu.
Šeit ir šūnā H4 izmantotā formula:
= VLOOKUP (G4, CHOOSE (IF (H2 = "Unit Test", 1, IF (H2 = "Midterm", 2,3))), $ A $ 3: $ E $ 7, $ A $ 11: $ E $ 15, $ A $ 19: $ E $ 23), MATCH (H3, $ A $ 2: $ E $ 2,0), 0)
Šī formula izmanto funkciju IZVĒLĒT, lai pārliecinātos, ka ir atsauce uz pareizo tabulu. Analizēsim formulas IZVĒLES daļu:
IZVĒLIES (IF (H2 = ”Unit Test”, 1, IF (H2 = ”Midterm”, 2,3))), $ A $ 3: $ E $ 7, $ A $ 11: $ E $ 15, $ A $ 19: $ E $ 23 )
Pirmais formulas arguments ir IF (H2 = ”vienības tests”, 1, IF (H2 = ”vidusposms”, 2,3)), kas pārbauda šūnu H2 un redz, uz kādu eksāmena līmeni tiek norādīts. Ja tas ir vienības tests, tas atgriež USD 3 USD: USD 7 USD, kam ir vienības testa rezultāti. Ja tas ir vidusposms, tas atgriež USD 11 USD: USD 15 USD, pretējā gadījumā atgriež USD 19 USD: USD 23 USD.
To darot, VLOOKUP tabulas masīvs kļūst dinamisks un līdz ar to tiek veikts trīsvirzienu uzmeklējums.
5. piemērs. Pēdējās vērtības iegūšana no saraksta
Varat izveidot VLOOKUP formulu, lai no saraksta iegūtu pēdējo skaitlisko vērtību.
Lielākais pozitīvais skaitlis, ko varat izmantot programmā Excel 9,99999999999999E+307. Tas arī nozīmē, ka lielākais uzmeklēšanas numurs VLOOKUP numurā ir vienāds.
Es nedomāju, ka jums kādreiz būtu nepieciešami nekādi aprēķini, kas saistīti ar tik lielu skaitu. Un tieši to mēs varam izmantot, lai iegūtu pēdējo numuru sarakstā.
Pieņemsim, ka jums ir datu kopa (A1: A14) kā parādīts zemāk, un jūs vēlaties iegūt pēdējo numuru sarakstā.
Šeit ir formula, kuru varat izmantot:
= VLOOKUP (9,99999999999999E+307, 1 ASV dolārs: 14 ASV dolāri,PATIESA)
Ņemiet vērā, ka iepriekšminētajā formulā tiek izmantota aptuvena atbilstība VLOOKUP (Formulas beigās atzīmējiet TRUE, nevis FALSE vai 0). Ņemiet vērā arī to, ka saraksts nav jāšķiro, lai šī VLOOKUP formula darbotos.
Lūk, kā darbojas aptuvenā VLOOKUP funkcija. Tas skenē lielāko kreiso kolonnu no augšas uz leju.
- Ja tas atrod precīzu atbilstību, tas atgriež šo vērtību.
- Ja tā atrod vērtību, kas ir augstāka par uzmeklēšanas vērtību, tā atgriež vērtību virs tās esošajā šūnā.
- Ja uzmeklēšanas vērtība ir lielāka par visām saraksta vērtībām, tā atgriež pēdējo vērtību.
Iepriekš minētajā piemērā trešais scenārijs darbojas.
Kopš 9,99999999999999E+307 ir lielākais skaitlis, ko var izmantot programmā Excel, ja to izmanto kā uzmeklēšanas vērtību, tas atgriež pēdējo skaitli no saraksta.
Tādā pašā veidā varat to izmantot arī, lai atgrieztu pēdējo teksta vienumu no saraksta. Šeit ir formula, kas to var izdarīt:
= VLOOKUP ("zzz", $ A $ 1: $ A $ 8,1,PATIESA)
Tāda pati loģika seko. Programma Excel izskata visus nosaukumus, un, tā kā zzz tiek uzskatīts par lielāku par jebkuru nosaukumu/tekstu, kas sākas ar alfabētu pirms zzz, tas atgriezīs pēdējo vienumu no saraksta.
6. piemērs - Daļēja uzmeklēšana, izmantojot aizstājējzīmes un VLOOKUP
Excel aizstājējzīmes var būt patiešām noderīgas daudzās situācijās.
Tā ir burvju mikstūra, kas piešķir jūsu formulām superspējas.
Daļēja uzmeklēšana ir nepieciešama, ja sarakstā ir jāmeklē vērtība un nav precīzas atbilstības.
Piemēram, pieņemsim, ka jums ir datu kopa, kā parādīts zemāk, un vēlaties sarakstā atrast uzņēmumu ABC, bet sarakstā ir ABC Ltd, nevis ABC.
Jūs nevarat izmantot ABC kā uzmeklēšanas vērtību, jo A slejā nav precīzas atbilstības. Aptuvenā atbilstība arī noved pie kļūdainiem rezultātiem, un tas prasa sarakstu sakārtot augošā secībā.
Tomēr, lai iegūtu atbilstību, VLOOKUP funkcijā varat izmantot aizstājējzīmi.
Ievadiet šādu formulu šūnā D2 un velciet to uz citām šūnām:
= MEKLĒŠANA ("*" & C2 & "*", $ A $ 2: $ A $ 8,1, FALSE)
Kā šī formula darbojas?
Iepriekš minētajā formulā tā vietā, lai izmantotu uzmeklēšanas vērtību, tā abās pusēs ir aizstāta ar aizstājējzīmes rakstzīmi zvaigznīte (*) - “*” Un C2 & ”*”
Zvaigznīte ir aizstājējzīme programmā Excel un var attēlot neierobežotu rakstzīmju skaitu.
Izmantojot zvaigznīti abās uzmeklēšanas vērtības pusēs, programmai Excel tiek norādīts, ka tai ir jāmeklē jebkurš teksts, kas satur vārdu C2. Tam var būt neierobežots rakstzīmju skaits pirms vai pēc teksta C2.
Piemēram, šūnai C2 ir ABC, tāpēc funkcija VLOOKUP meklē nosaukumus A2: A8 un meklē ABC. Tas atrod atbilstību šūnā A2, jo tajā ir ABC SIA. Nav nozīmes tam, vai ABC kreisajā vai labajā pusē ir rakstzīmes. Kamēr teksta virknē nav ABC, tas tiks uzskatīts par atbilstību.
Piezīme. Funkcija VLOOKUP vienmēr atgriež pirmo atbilstošo vērtību un pārtrauc skatīties tālāk. Tātad, ja jums ir ABC SIA, un ABC Corporation sarakstā, tas atgriezīs pirmo un ignorēs pārējo.
7. piemērs - VLOOKUP Atgriež kļūdu, neskatoties uz meklēšanas vērtības atbilstību
Tas var padarīt jūs traku, ja redzat, ka ir atbilstoša uzmeklēšanas vērtība un funkcija VLOOKUP atgriež kļūdu.
Piemēram, zemāk minētajā gadījumā ir atbilstība (Matt), bet funkcija VLOOKUP joprojām atgriež kļūdu.
Tagad, kamēr mēs redzam, ka ir sērkociņš, mēs ar neapbruņotu aci nevaram redzēt, ka var būt priekšējās vai beigu vietas. Ja pirms, pēc vai starp meklēšanas vērtībām ir šīs papildu atstarpes, tā NAV precīza atbilstība.
Tas bieži notiek, importējot datus no datu bāzes vai iegūstot tos no kāda cita. Šīm vadošajām/aizmugurējām telpām ir tendence ielīst.
Risinājums šeit ir funkcija TRIM. Tas noņem visas sākuma vai beigu atstarpes vai papildu atstarpes starp vārdiem.
Šeit ir formula, kas sniegs jums pareizo rezultātu.
= VLOOKUP ("Matt", TRIM ($ A $ 2: $ A $ 9), 1,0)
Tā kā šī ir masīva formula, izmantojiet taustiņu kombināciju Control + Shift + Enter, nevis tikai Enter.
Vēl viens veids varētu būt vispirms apstrādāt savu uzmeklēšanas masīvu ar funkciju TRIM, lai pārliecinātos, ka visas papildu vietas nav, un pēc tam izmantot funkciju VLOOKUP kā parasti.
8. piemērs. Veicot reģistrjutīgu meklēšanu
Pēc noklusējuma funkcijas VLOOKUP uzmeklēšanas vērtība nav reģistrjutīga. Piemēram, ja jūsu meklēšanas vērtība ir MATT, matēta vai matēta, funkcijai VLOOKUP tā ir vienāda. Neatkarīgi no gadījuma tiks atgriezta pirmā atbilstošā vērtība.
Bet, ja vēlaties veikt reģistrjutīgu meklēšanu, jums ir jāizmanto funkcija EXACT kopā ar funkciju VLOOKUP.
Šeit ir piemērs:
Kā redzat, ir trīs šūnas ar tādu pašu nosaukumu (A2, A4 un A5), bet ar atšķirīgu alfabēta burtu. Labajā pusē mums ir trīs vārdi (Matt, MATT un matt) kopā ar viņu rezultātiem matemātikā.
Tagad funkcija VLOOKUP nav aprīkota, lai apstrādātu reģistrjutīgās uzmeklēšanas vērtības. Šajā piemērā tas vienmēr atgriezīs 38, kas ir rezultāts Matam A2.
Lai padarītu to reģistrjutīgu, mums jāizmanto palīga kolonna (kā parādīts zemāk):
Lai iegūtu vērtības palīga kolonnā, izmantojiet funkciju = ROW (). Tas vienkārši iegūs rindas numuru šūnā.
Kad esat izveidojis palīga kolonnu, šeit ir formula, kas sniegs reģistrjutīgo meklēšanas rezultātu.
= VLOOKUP (MAKS.
Tagad sadalīsimies un sapratīsim, ko tas nozīmē:
- PRECĪZS (E2, $ A $ 2: $ A $ 9) - šajā daļā tiktu salīdzināta uzmeklēšanas vērtība E2 ar visām A2: A9 vērtībām. Tas atgriež masīvu TRUE/FALSE, kur tiek atgriezta TRUE, ja ir precīza atbilstība. Šādā gadījumā tas atgrieztu šādu masīvu: {TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE}.
- PRECĪZI (E2, $ A $ 2: $ A $ 9)*(ROW ($ A $ 2: $ A $ 9) - šī daļa reizina TRUE/FALSE masīvu ar rindas numuru. Ja ir TRUE, tas norāda rindas numuru , citādi tas dod 0. Šādā gadījumā tas atgrieztu {2; 0; 0; 0; 0; 0; 0; 0}.
- MAX (EXACT (E2, $ A $ 2: $ A $ 9)*(ROW ($ A $ 2: $ A $ 9)))) - šī daļa atgriež maksimālo vērtību no skaitļu masīva. Šajā gadījumā tas atgriezīs 2 (kas ir rindas numurs, kur ir precīza atbilstība).
- Tagad mēs vienkārši izmantojam šo skaitli kā uzmeklēšanas vērtību un izmantojam uzmeklēšanas masīvu kā B2: C9
Piezīme. Tā kā šī ir masīva formula, vienkārši ievadīšanas vietā izmantojiet Control + Shift + Enter.
9. piemērs - VLOOKUP izmantošana ar vairākiem kritērijiem
Funkcija Excel VLOOKUP pamatformā var meklēt vienu uzmeklēšanas vērtību un atdot atbilstošo vērtību no norādītās rindas.
Bet bieži vien programmā Excel ir jāizmanto VLOOKUP ar vairākiem kritērijiem.
Pieņemsim, ka jums ir dati ar studentu vārdu, eksāmena veidu un matemātikas punktu skaitu (kā parādīts zemāk):
Funkcijas VLOOKUP izmantošana, lai iegūtu matemātikas rezultātu katram studentam par attiecīgajiem eksāmenu līmeņiem, varētu būt izaicinājums.
Piemēram, ja mēģināt izmantot VLOOKUP ar Matu kā uzmeklēšanas vērtību, tas vienmēr atgriež 91, kas ir rādītājs par pirmo Matt parādīšanos sarakstā. Lai iegūtu Matta rezultātu katram eksāmena veidam (vienības pārbaude, vidusposms un fināls), jums jāizveido unikāla uzmeklēšanas vērtība.
To var izdarīt, izmantojot palīga kolonnu. Pirmais solis ir ievietot palīga kolonnu pa kreisi no rādītājiem.
Tagad, lai katram nosaukuma gadījumam izveidotu unikālu kvalifikatoru, izmantojiet šādu formulu C2: = A2 & ”|” & B2
Kopējiet šo formulu visās palīga kolonnas šūnās. Tādējādi tiks izveidotas unikālas uzmeklēšanas vērtības katram vārda gadījumam (kā parādīts zemāk):
Tagad, kamēr bija vārdu atkārtojumi, nav atkārtojumu, ja vārds tiek apvienots ar eksāmena līmeni.
Tas atvieglo, jo tagad kā uzmeklēšanas vērtības varat izmantot palīga kolonnas vērtības.
Šeit ir formula, kas sniegs jums rezultātu G3: I8.
= MEKLĒŠANA ($ F3 & "|" & G $ 2, $ C $ 2: $ D $ 19,2,0)
Šeit mēs esam apvienojuši studenta vārdu un pārbaudes līmeni, lai iegūtu uzmeklēšanas vērtību, un mēs izmantojam šo uzmeklēšanas vērtību un pārbaudām to palīga slejā, lai iegūtu atbilstošo ierakstu.
Piezīme. Iepriekš minētajā piemērā mēs izmantojām | kā atdalītāju, pievienojot tekstu palīga kolonnā. Dažos ārkārtīgi retos (bet iespējamos) apstākļos jums var būt divi kritēriji, kas ir atšķirīgi, bet kopā dod tādu pašu rezultātu. Šeit ir piemērs:
Ņemiet vērā: lai gan A2 un A3 ir atšķirīgi, bet B2 un B3 ir atšķirīgi, kombinācijas galu galā ir vienādas. Bet, ja jūs izmantojat atdalītāju, tad pat kombinācija būtu atšķirīga (D2 un D3).
Šeit ir apmācība par to, kā izmantot VLOOKUP ar vairākiem kritērijiem, neizmantojot palīgu kolonnas. Šeit varat arī noskatīties manu video pamācību.
10. piemērs. Kļūdu apstrāde, izmantojot funkciju VLOOKUP
Funkcija Excel VLOOKUP atgriež kļūdu, ja tā nevar atrast norādīto uzmeklēšanas vērtību. Iespējams, nevēlaties, lai neglītā kļūdas vērtība traucētu jūsu datu estētiku, ja VLOOKUP nevar atrast vērtību.
Jūs varat viegli noņemt kļūdas vērtības ar jebkādu nozīmi pilnu tekstu, piemēram, “Nav pieejams” vai “Nav atrasts”.
Piemēram, zemāk redzamajā piemērā, mēģinot sarakstā atrast Breda rezultātu, tiek parādīta kļūda, jo Breda vārda sarakstā nav.
Lai novērstu šo kļūdu un aizstātu to ar kaut ko nozīmīgu, iesaiņojiet VLOOKUP funkciju IFERROR funkcijā.
Šeit ir formula:
= IFERROR (MEKLĒJUMS (D2, $ A $ 2: $ B $ 7,2,0), "Nav atrasts")
Funkcija IFERROR pārbauda, vai pirmā argumenta (kas šajā gadījumā ir funkcija VLOOKUP) atgrieztā vērtība ir kļūda. Ja tā nav kļūda, tā atgriež vērtību, izmantojot funkciju VLOOKUP, pretējā gadījumā atgriež vērtību Nav atrasts.
Funkcija IFERROR ir pieejama, sākot no Excel 2007. Ja pirms tam izmantojat versijas, izmantojiet šādu funkciju:
= JA (ISERROR (VLOOKUP (D2, $ A $ 2: $ B $ 7,2,0)), "Not Found", VLOOKUP (D2, $ A $ 2: $ B $ 7,2,0))
Skatīt arī: Kā rīkoties ar VLOOKUP kļūdām programmā Excel.
Tas ir šajā VLOOKUP apmācībā.
Esmu mēģinājis aprakstīt galvenos Vlookup funkcijas izmantošanas piemērus programmā Excel. Ja vēlaties, lai šim sarakstam tiktu pievienoti vēl citi piemēri, informējiet mani komentāru sadaļā.
Piezīme: Esmu mēģinājis visu iespējamo, lai koriģētu šo apmācību, bet, ja atrodat kādas kļūdas vai pareizrakstības kļūdas, lūdzu, informējiet mani 🙂
Izmantojot Funkcija VLOOKUP programmā Excel - video
- Excel HLOOKUP funkcija.
- Excel XLOOKUP funkcija
- Excel INDEX funkcija.
- Excel netiešā funkcija.
- Excel MATCH funkcija.
- Excel OFFSET funkcija.
