Ievads funkcijā VLOOKUP
Funkcija VLOOKUP programmā Excel, bez šaubām, ir visbiežāk izmantotā un runātā funkcija. Jebkurā darba intervijā, kurā nepieciešamas Excel prasmes, šis ir visbiežāk uzdotais jautājums, ja zināt, kā izmantot funkciju VLOOKUP.
Funkcija VLOOKUP ietilpst Excel funkciju meklēšanas kategorijā. V VLOOKUP nozīmē vertikāli. Funkcija tiek izmantota vertikāli strukturētu datu meklēšanai. Horizontālajai meklēšanai ir funkcija HLOOKUP.
Funkcija Excel VLOOKUP datu tabulā meklē dotās vērtības, ko sauc par uzmeklēšanas vērtību, pirmo atbilstību un atgriež vērtību no dotā kolonnu indeksa. Atbilstība var būt aptuvena vai precīza.
Lai gan VLOOKUP ir viegli lietojams, tam ir savi ierobežojumi un fons. Mēs apspriedīsim visu, ieskaitot funkcijas stiprās un vājās puses.
Sintakse:
= MEKLĒŠANA (uzmeklēšanas_vērtība, tabulas_masīvs, kolonnas_indeksa_numurs, [diapazona_meklējums])
Kas ir uzmeklēšanas vērtība
Uzmeklēšanas_vērtība: Tā ir vērtība, kuru vēlaties meklēt tabulu masīvā.
Piemēram, ja jūs meklējat ruļļa Nr. 110 studenta galdā, tad tā 110 ir uzmeklēšanas vērtība.
Tabulas_masīvs: Tabula, no kuras vēlaties meklēt uzmeklēšanas vērtību.
Piemēram, ja tabulas masīvā meklējat 110 B5: E17. Tad B5: E17 ir jūsu galda masīvs.
col_index_number: Tabulas masīva slejas numurs, no kura vēlaties izgūt rezultātus.
Piemēram, ja tabulas masīvā B5: E17 vēlaties iegūt vērtību no kolonnas D tad jūsu kolonnu indekss būs 3 (skaitot no B līdz D (B, C, D)).
[range_lookup]: FALSE, ja vēlaties meklēt precīzu vērtību, TRUE, ja vēlaties aptuvenu atbilstību.
Funkcijas VLOOKUP izmantošana
Ja jums ir strukturēti dati un vēlaties meklēt datus šajos datos, risinājums ir VLOOKUP.
VLOOKUP atbilstības režīmi
VLOOKUP ir divi atbilstības režīmi - aptuvena atbilstība un precīza atbilstība. Pēc noklusējuma VLOOKUP veic aptuvenu atbilstību. Bet, ja vēlaties piespiest VLOOKUP funkciju veikt precīzu atbilstību, varat arī to izdarīt. Lielākoties es un es ļoti ticam, ka arī citi cenšas panākt precīzu atbilstību, izmantojot funkciju VLOOKUP. Es nesaprotu, kāpēc Excel izstrādātāji domā, ka lietotāji lielākoties vēlētos izmantot aptuveno atbilstību.
1. VLOOKUP aptuvenā atbilstība
= MEKLĒŠANA (uzmeklēšanas_vērtība, tabulas_masīvs, kolonnas_indeksa_numurs, 1)
Ja vērtība VLOOKUP aptuvenā atbilstība netiek atrasta, tad tiek saskaņota pēdējā vērtība, kas ir mazāka par uzmeklēšanas vērtību, un tiek atgriezta vērtība no norādītā kolonnas indeksa.
VLOOKUP pēc noklusējuma atbilst aptuvenai, ja mēs izlaižam diapazona uzmeklēšanas mainīgo. Aptuvenā atbilstība ir noderīga, ja vēlaties veikt aptuvenu atbilstību un kad tabulas masīvs ir sakārtots augošā secībā.
Vlookup meklē vērtību, un, ja tā neatrod vērtību tabulas masīvā, tā atbilst vērtībai, kas ir mazāka par šo vērtību tabulas masīvā. Ļaujiet mums to saprast ar piemēru.
1. piemērs
Šeit mums ir studentu saraksts. Katrs students pārbaudījumā ir ieguvis dažas atzīmes. Tagad es gribu dot atzīmi pēc viņu atzīmēm.
Ikvienam studentam, kurš ieguva mazāk par 40, jābūt atzīmei F, studentam, kurš atzīmējās no 40 līdz 60, jābūt atzīmei C un tā tālāk, kā parādīts attēlā.
Mēs uzrakstītu šo VLOOKUP formulu E2 un velciet to uz leju.
= MEKLĒŠANA (D2, $ H $ 2: I $ 6,2, TRUE)
Kā tas strādā?
Iepriekš minētajā piemērā mūsu uzmeklēšanas vērtības ir D slejā, sākot no D2. Tabulas masīvs ir H2: I6 (ņemiet vērā, ka tas ir sakārtots augošā secībā un absolūti). Kolonna, no kuras iegūstam datus, ir 2. Un atbilstības veids, ko mēs esam definējuši aptuvenu, nokārtojot TRUE (nekas un 1 nozīmēs to pašu).
Tātad, pārbaudīsim pirmās meklēšanas vērtību D2, kas satur 40.
- VLOOKUP meklē 40 tabulas masīva H2: I6 pirmajā slejā (H).
- Tas atrod 40 otrajā vietā šūnā H3.
- Tagad tas pāriet uz otro kolonnu no H3 uz I3 un atgriež D.
Šajā gadījumā vlookup atrada precīzu atbilstību. Apskatīsim nākamo gadījumu.
Otrās uzmeklēšanas vērtības D3, kas satur 36.
- VLOOKUP tabulas masīva H2: I6 pirmajā slejā meklē 36.
- VLOOKUP pirmajā kolonnā nekur nevarēja atrast 36.
- Tā kā VLOOKUP nevarēja atrast 36, tas atbilst pēdējai atrastajai vērtībai, kas ir mazāka par uzmeklēšanas vērtību.
- Pirmā vērtība, kas ir mazāka par 36, ir 0, tāpēc tā atgriež F, kas ir saistīta ar 0.
Šeit tas notiek katrā gadījumā. 92, pēdējā vērtība, kas ir mazāka par 92, ir 90. Tādējādi A tiek atgriezta aptuvenai atbilstībai.
2. VLOOKUP precīza atbilstība
Šī visbiežāk izmantotā VLOOKUP forma un var būt arī visnoderīgākā. Ja vēlaties tabulas masīva pirmajā slejā meklēt precīzu vērtību, izmantojiet precīzo atbilstību. Piemēram, ja veicat uzmeklēšanu, veidojot ID, tad jūs visvairāk vēlētos precīzu atbilstību, nevis vērtību, kas ir mazāka par šo vērtību.
Lai piespiestu VLOOKUP precīzai atbilstībai programmā Excel, mēs kā parametru range_lookup norādām 0 vai FALSE.
= UZSKATĪŠANA (lookup_value, table_array, col_index_number, 0)
Ja vērtība nav atrodama tabulas masīva pirmajā kolonnā, formula atgriež kļūdu #N/A.
Apskatīsim piemēru.
2. piemērs
Šajā piemērā es vēlos A2 rakstīt tikai studenta sarakstu, bet B2 - es gribu izgūt studenta vārds un iekšā C2 viņa Novērtējums. Vienkārši. Galda masīvs ir B5: D17. Kāpēc?
Lai to izdarītu, ierakstiet šīs divas formulas šūnā attiecīgi šūnā B2 un C2.
= MEKLĒŠANA (A2, $ B $ 5: $ E $ 17,2,0)
= MEKLĒŠANA (A2, $ B $ 5: $ E $ 17,3,0)
Neatkarīgi no tā, kuru ID jūs ierakstīsit šūnā A2, funkcija VLOOKUP meklēs precīzu atbilstību kolonnā B un atgriezīs vērtību no dotā kol_indeksa.
Kā darbojas precīza atbilstība VLOOKUP?
Exact Match VLOOKUP programmā Excel ir vienkāršs. Tā meklē uzmeklēšanas vērtību dotā table_array pirmajā kolonnā un atgriež vērtību no vērtības no dotā column_index numura. Ja vērtība netiek atrasta, VLOOKUP atgriež kļūdu #N/A.
VLOOKUP funkcijas labākās izmantošanas iespējas
Vlookup ir daudz lietojumu. Es apspriedīšu dažus visnoderīgākos lietošanas gadījumus.
3. Novērtēšanai izmantojiet Excel VLOOKUP, nevis sarežģītu ligzdotu IF funkciju
Izmantojiet VLOOKUP aptuveno atbilstību, lai aizstātu ligzdoto IF funkciju. 1. piemērā mēs redzējām, kā mēs varam izmantot vlookup, lai skolēnus vērtētu pēc viņu atzīmēm.
VLOOKUP aptuvenā atbilstība ir ātrāka nekā ligzdota, ja tā izmanto bināro meklēšanu iekšēji.
4. Izmantojiet VLOOKUP, lai iegūtu datus
Šis visizplatītākais uzdevums, ko var viegli paveikt, izmantojot funkciju VLOOKUP programmā Excel. 2. piemērā mēs to darījām, lai izgūtu skolēnu datus, izmantojot viņu sarakstu Nr.
5. Pārbaudiet, vai vērtība ir ierakstā, izmantojot Vlookup.
VLOOKUP var izmantot, lai pārbaudītu, vai vērtība ir kādā sarakstā vai nav.
2. piemērā, rakstot 152, tiek atgriezta kļūda #N/A. Ja VLOOKUP atgriež #N/A, tas nozīmē, ka tā nekur nav atradusi doto vērtību.
Esmu sagatavojis īsu rakstu par to, to varat izlasīt šeit.
Izmantojot VLOOKUP formulu, lai pārbaudītu, vai vērtība pastāv
Automātiska kolonnas indeksa iegūšana
Es piederu tai cilvēku grupai, kuri nevēlas atkārtot vienu un to pašu uzdevumu atkal un atkal. Tātad kol_index numura mainīšana atkal un atkal mani kaitina, un es vienmēr no tā izvairos. Patiesībā es izvairos no jebkāda veida rediģēšanas savās formulās, kad esmu to uzrakstījis, ja vien un kamēr nav citas iespējas.
Kolonnu indeksu programmā Excel var aprēķināt automātiski. Ir vairāki veidi, kā to izdarīt. Apskatīsim dažus no tiem šeit.
6. Izmantojiet funkciju VLOOKUP ar funkciju MATCH
Tātad, kā mēs zinām, funkcija MATCH atgriež atrasto atbilstošās vērtības indeksu.
Tātad, ja mums meklēšanas vietā ir tieši tādi paši virsraksti kā avota datiem, mēs varam tos izmantot, lai iegūtu kol_index. Kā? Ļaujiet mums redzēt…
Zemāk esošajos datos mēs vēlamies vienkārši ierakstīt ruļļa numuru G2 un iegūt studenta vārdu, atzīmes un pakāpi H2, neatkarīgi no tā, kuru virsrakstu mēs tur rakstām.
Ierakstiet šo formulu šūnā H2
= MEKLĒŠANA (G2, B2: E14, MATCH (H1, B1: E1,0), 0)
Tagad, kad mainīsit virsrakstu H1, šī vērtība H2 tiks parādīta no šīs slejas.
Varat izmantot nolaižamo izvēlni, izmantojot datu validāciju, kurā ir viss virsraksts no tabulas, lai padarītu to lietotājam draudzīgāku.
Šeit VLOOKUP veic precīzu atbilstību. Tagad VLOOKUP veic ierastās darbības, iegūstot datus. Burvju veic MATCH funkcija kol_index pozīcijā.
Šeit VLOOKUP redz jebkuru vērtību G2 tabulas masīva B2: E14 pirmajā kolonnā. Tagad col_index vietā mums ir SASTĀVS (H1, B1: E1,0).
Funkcija MATCH meklē jebkuru vērtību diapazonā H1 B1: E1 un atgriež atbilstības vērtības indeksu.
Piemēram, rakstot students H1 tas meklē to diapazonā B1: E1. Tas ir atrodams C2. līdz ar to atgriež 2. Ja rakstām Novērtējums tas atgriezīs skolēna pakāpi.
Ņemiet vērā, ka, ierakstot reģionu, tas atgriež #N/A. Tā kā tas neietilpst tabulas darbības jomā. Par to mēs runāsim pēdējā rakstā.
Tas ir labākais veids, kā padarīt VLOOKUP automātisku. Ir arī citas metodes, taču tās nav tik elastīgas.
7. Izmantojiet funkciju VLOOKUP ar funkciju SLEJA
Kolonnas funkcija atgriež piegādātās atsauces kolonnas numuru. Un kas mums ir nepieciešams, lai iegūtu datus, izmantojot VLOOKUP? Kolonna_indekss Tātad, izmantojot funkciju SLEJA, mēs, protams, varam iegūt datus no jebkuras datu kopas.
Ņemsim iepriekš minēto piemēru. Bet tagad mums ir jāiegūst veseli dati. Ne tikai viena kolonna.
Uzrakstiet šo formulu H2 un kopējiet I2 un J2.
= MEKLĒŠANA ($ G2, $ B $ 2: $ E $ 14, SLEJA (B1), 0)
Tas dos jums dinamisku rezultātu. Kā? Paskatīsimies.
VLOOKUP darbojas kā parasti. Mēs esam rakstījuši kolonnā_indekss SLEJA (B1), kas tiks tulkots uz 2, jo B slejas 2 kolonnā ir studenta vārds, tas atgriež studenta vārdu.
Svarīgs: Tas darbojas, jo mūsu tabula sākas no B slejas. Ja jūsu tabula atrodas lapas vidū, jums ir jāatņem vai jāpievieno daži skaitļi, lai tie atbilstu jūsu prasībām.
Piemēram, ja iepriekšējā tabula sākās no C1 tādā pašā struktūrā, tad visas zemāk esošās formulas darbojās labi.
= MEKLĒŠANA ($ G2, $ C $ 2: $ F $ 14, SLEJA (B1), 0)
= MEKLĒŠANA ($ G2, $ C $ 2: $ F $ 14, SLEJA (C1) -1,0)
= MEKLĒŠANA ($ G2, $ C $ 2: $ F $ 14, SLEJA (A1) +1,0)
8. VLOOKUP, lai apvienotu datus
Lielākajā daļā datu bāzu ir viens vaicājums, kas apvienojas tabulās, pārsvarā to sauc par pievienošanās vaicājumu, bet MS Excel nav datu bāzē, tāpēc tam nav pievienošanās vaicājuma. Bet tas nenozīmē, ka mēs nevaram pievienoties divām tabulām, ja divās tabulās ir dažas kopīgas kolonnas.
Tātad, iegūstot datus no diviem viena uzņēmuma departamentiem, jūs vēlaties tos apvienot, lai analizētu šos datus.
Piemēram, ja no skolotājiem saņemat skolēnu atzīmes un pēc tam apmeklējuma ierakstu no administratīvās nodaļas, jūs vēlaties redzēt, kurš students ieguva kādu atzīmi un kāds ir viņa apmeklējuma procents.
Mums tie ir jāapvieno zemāk esošajā tabulā.
Marks, mums ir jāiegūst no atzīmju tabulas un apmeklētības no apmeklētības datiem.
Tālāk rakstiet formulas šūnā D19, lai iegūtu atzīmes un velciet uz leju.
= MEKLĒŠANA (B19, $ B $ 2: $ D $ 15,3,0)
Ierakstiet zem formulas šūnā E19
= MEKLĒŠANA (B19, $ G $ 3: I $ 15,3,0)
Un mēs esam apvienojuši divas tabulas vienā tabulā. Protams, varat pievienot vairāk nekā divas tabulas. Jums vienkārši jāiegūst dati no visām vajadzīgajām tabulām vienuviet, izmantojot VLOOKUP.
VLOOKUP paraugprakse
Tātad visos iepriekš minētajos piemēros mēs vienkārši izmantojām vlookup ar neapstrādātiem datiem. Visos iepriekš minētajos piemēros mēs bijām uzmanīgi attiecībā uz izmantotajiem datiem. Mums bija jābūt uzmanīgiem, vienlaikus norādot tabulu masīvu atsauces. Bet ir daži veidi, kas var ievērojami samazināt šīs pūles.
9. Izmantojiet Absolūtās atsauces ar VLOOKUP
Jūs, iespējams, pamanījāt, ka dažos rakstos esmu izmantojis absolūtos diapazonus (diapazona $ zīme). Absolūti dusmas tiek izmantotas, ja nevēlamies mainīt diapazonu, kopējot formulas šūnu uz citām šūnām.
Piemēram, ja man ir = VLOOKUP (G2,B2: E14, 2,0), 0) šūnā H2, un, ja es to nokopēju vai velku uz leju šūnā H3, formula mainīsies uz = VLOOKUP (G3,B3: E15, 2,0). Visas atsauces tiek salīdzinoši mainītas. Šeit mēs varētu vēlēties mainīt uzmeklēšanas vērtības atsauci, bet ne tabulas masīvu. Tā kā B2 iziet vai ir diapazons, un mēs varam vēlēties, lai tas vienmēr būtu mūsu tabulu masīvos.
Tāpēc, lai novērstu to, mēs izmantojam absolūtas atsauces. Mēs padarām absolūtus tos diapazonus, kurus nevēlamies mainīt, piemēram, tabulas masīvs H2, = VLOOKUP (G2,$ B $ 2: $ E $ 14, 2,0), 0). Kopējot H2 H3, formula būs = VLOOKUP (G3,$ B $ 2: $ E $ 14, 2,0), 0). Ņemiet vērā, ka tiek mainīta uzmeklēšanas vērtība, bet ne tabulas_masīvs.
10. Izmantojiet nosauktos diapazonus ar VLOOKUP
Iepriekš minētajā piemērā tabulas_masīva labošanai izmantojām absolūtu atsauci. Tas izskatās diezgan izplūdis un grūti lasāms. Kā būtu, ja varētu vienkārši uzrakstīt "Dati" vietā $ B $ 2: $ E $ 14 un tas būtu atsauce uz $ B $ 2: $ E $ 14 vienmēr. Tas vēl vairāk atvieglos formulas lasīšanu un rakstīšanu.
Tāpēc vienkārši atlasiet diapazonu un nosauciet to par “datiem”.
Lai nosauktu diapazonu, atlasiet diapazonu. Ar peles labo pogu noklikšķiniet uz Definēt vārdu un nosauciet vajadzīgo diapazonu. Tagad tikai = VLOOKUP (G3,dati,2,0), 0) formula darbosies tāpat kā iepriekš.
Jūs varat lasīt vairāk par nosauktajiem diapazoniem programmā Excel. Šeit es esmu izskaidrojis katru nosauktā diapazona aspektu.
11. Aizstājējzīmes daļējai atbilstībai, izmantojot VLOOKUP
VLOOKUP ļauj daļēji saskaņot. Jā, veicot precīzu VLOOKUP atbilstību, jūs varat izmantot aizstājējzīmju operatorus, lai veiktu daļēju atbilstību. Piemēram, ja vēlaties meklēt vērtību, kas sākas ar “Gil”, varat izmantot aizstājējzīmju operatoru * ar “Gil” kā “Gil *”. Apskatīsim piemēru.
Šeit es vēlos iegūt atzīmes par studentu, kura vārds sākas ar Gilu. Lai to iegūtu, es uzrakstīšu zemāk esošo formulu.
= VLOOKUP ("*Gill", C2: D14,2,0)
Programmā Excel ir pieejamas divas aizstājējzīmes, kuras var izmantot kopā ar funkciju VLOOKUP.
Zvaigznīte (*) aizstājējzīme. To izmanto, ja lietotājs nezina tur esošo rakstzīmju skaitu un zina tikai dažas atbilstības rakstzīmes. Piemēram, ja lietotājs zina vārdu, kas sākas ar “Sm”, viņš rakstīs “Sm*”. Ja lietotājs zina, ka uzmeklēšanas vērtība, kas beidzas ar “123”, viņš rakstīs “*123”. (aizstājējzīme darbojas tikai ar tekstiem). Un viņš zina, ka tekstā ir “se”, tad viņš rakstīs “*se*”.
Jautājuma zīme ("?"). To izmanto, ja lietotājs zina trūkstošo rakstzīmju skaitu. Piemēram, ja es vēlos atrast vērtību, kurā ir 4 rakstzīmes, bet es nezinu, kas tās ir, es vienkārši uzrakstīšu “????” uzmeklēšanas vērtības vietā.
Vlookup atgriež pirmo atrasto vērtību, kurā ir tieši četras rakstzīmes.
Svarīgs: Aizstājējzīme darbojas tikai ar teksta vērtībām. Pārveidojiet ciparus par tekstu, ja vēlaties ar tiem izmantot aizstājējzīmi.
Funkcijas VLOOKUP vājās vietas
VLOOKUP patiešām ir spēcīga un vienkārša funkcija vērtību iegūšanai, taču ir daudzas jomas, kurās VLOOKUP vienkārši nav tik noderīgs. Ja strādājat pie Excel, jums arī jāzina viņi un kā veikt uzdevumu, ko VLOOKUP nevar paveikt.
12. Nevar iegūt vērtību no tabulas_masīva kreisās puses
Funkcijas VLOOKUP lielākais trūkums ir tas, ka tā nevar iegūt vērtību no tabulas masīva kreisās puses. VLOOKUP meklē tikai no meklēšanas vērtības.
Tas notiek tāpēc, ka VLOOKUP meklē doto uzmeklēšanas vērtība dotajā pirmajā slejā galda masīvs. Tas nekad neatgriezīs vērtību no tabulas ārpuses. Un, ja mēģināt tabulas masīvā saglabāt kreiso kolonnu, tā kļūst par tabulas masīva pirmo kolonnu, tāpēc meklēšanas vērtība tiks meklēta šajā diapazonā. Kas, iespējams, radīs kļūdu.
Lai meklētu vērtības no meklēšanas vērtības kreisās puses, mēs izmantojam INDEX-MATCH. Funkcija INDEX-MATCH var meklēt vērtības no jebkuras lapas kolonnas. Faktiski uzmeklēšanas diapazons un diapazons, no kura vēlaties meklēt vērtības, ir pilnīgi neatkarīgi, kas padara indeksa atbilstību ļoti pielāgojamu.
VLOOKUP atgriež #N/A pat tad, ja pastāv uzmeklēšanas vērtība.
#N/A funkcija VLOOKUP atgriež kļūdu, ja uzmeklēšanas vērtība netiek atrasta. Var šķist nepatīkami, ja diapazonā pastāv vērtība, bet VLOOKUP atgriež kļūdu #N/A.
14. Kad skaitļi tiek veidoti kā teksts
Tas galvenokārt notiek, ja skaitļi tiek noformēti kā teksts. Ja atrodat, izmantojot komandu CTRL+F, Excel to atradīs, bet, mēģinot izmantot VLOOKUP, tas atgriež #N/A. Šis uzdevums lielākajā daļā interviju tiek dots kā viltīgi jautājumi.
Iepriekš redzamajā attēlā varat redzēt, ka 101 ir tieši tur, bet formula atgriež kļūdu. Kā, vai tu tieki galā? Nu, ir divas metodes.
1. Pārvērtiet tekstu par skaitli datos
Zaļo tagu varat redzēt rollno kolonnas augšējā kreisajā stūrī. Tie norāda, ka šūnas vērtībā kaut kas ir neparasts. Atlasot šūnu, tas parādīs, ka numurs ir formatēts kā teksts.
Noklikšķinot uz izsaukuma zīmes (!), Tiks parādīta opcija Pārvērst par skaitli. Atlasiet visas šūnas un noklikšķiniet uz šīs opcijas. Visas vērtības tiks pārveidotas par tekstu.
2. Pārveidojiet savu uzmeklēšanas numuru tekstā formulā
Iepriekš minētajā piemērā mēs mutējām sākotnējos datus. Iespējams, nevēlaties neko mainīt sākotnējos datos. Tātad jūs vēlaties to darīt formulā. Lai mainītu skaitli tekstā VLOOKUP formulā, uzrakstiet šo.
= MEKLĒŠANA (TEKSTS (G2, "0"), B2: D14,2,0)
Šeit formula dinamiski maina skaitli tekstā un pēc tam saskaņo to ar doto diapazonu.
15. Teksts ar vadošajām un pēdējām atstarpēm
Daži datu ievades operatoru sniegtie dati un dati no interneta nav tīri. Tajos var būt atstarpes vai dažas nedrukājamas rakstzīmes. Veicot meklēšanu, izmantojot šīs vērtības, iespējams, redzēsit kādu kļūdu #N/A. Lai no tā izvairītos, vispirms notīriet savus datus. Lai noņemtu atstarpes, izmantojiet TRIM funkciju. TRIM no teksta noņem jebkādu skaitu sākuma vai beigu atstarpes.
Tāpēc es iesaku jums pievienot jaunu kolonnu un iegūt tur notīrītos datus. Pēc tam ielīmējiet vērtību. Tagad, ja vēlaties, varat atbrīvoties no sākotnējās kolonnas.
= TRIM (CLEAN (teksts))
VLOOKUP kļūdu apstrāde
Kā jūs zināt, VLOOKUP neizdodas atrast vērtību, tā atgriež kļūdu #N/A. Labi, ja tiek parādīta kļūda #N/A, varbūt jūs dažreiz to vēlaties iegūt, piemēram, ja vēlaties pārbaudīt, vai vērtība jau pastāv sarakstā vai ne. Tur #N/A nozīmē, ka vērtības nav.
Bet #N/A izskatās neglīti. Kā iegūt lietotājam draudzīgu izvadi, piemēram, “Id nav atrasts” vai “Klients nav atrasts”. Mēs varam izmantot IFERROR ar funkciju VLOOKUP, lai izvairītos no #N/A.
Izmantojiet zemāk esošo formulu, lai meklētu kļūdu VLOOKUP.
= IFERROR (VLOOKUP (150, B2: E14,2,0), "Roll no not found")
16. MEKLĒŠANA ar vairākiem kritērijiem
VLOOKUP nevar strādāt ar vairākiem kritērijiem.Jā, vērtību meklēšanai var izmantot tikai vienu kritēriju, izmantojot VLOOKUP formulu.
Piemēram, ja jums ir vārds un uzvārds divās atsevišķās kolonnās.
Un tagad, ja vēlaties meklēt vērtību, kuras vārds ir Jānis un uzvārds Deivs, tad to nevar izdarīt normāli.
Bet tam ir risinājums. Varat izveidot atsevišķu kolonnu, apvienojot vārdu un uzvārdu, un pēc tam uzmeklēt šo sleju vārds un uzvārds.
Ir INDEX-MATCH alternatīva, kas var meklēt vairākus kritērijus bez palīdzības kolonnas.
17. VLOOKUP izgūst tikai pirmo atrasto vērtību
Iedomājieties, ka jums ir daži dati centrālajā reģionā. Tagad jūs vēlaties iegūt pirmos 5 centrālā reģiona datu ierakstus. VLOOKUP atdos tikai pirmo centrālo vērtību visos piecos ierakstos. Tas ir nozīmīgs fons.
Bet tas nenozīmē, ka mēs to nevaram sasniegt. Mēs varam izmantot sarežģītu masīvu formulu, kā norādīts zemāk.
= INDEKSS ($ C $ 2: $ C $ 14, SMALL (IF (G2 = $ A $ 2: $ A $ 14, ROW ($ A $ 2: $ A $ 14) -ROW ($ A $ 2) +1), ROW (1: 1)))
Uzrakstiet šo formulu un nospiediet CTRL+SHIFT+ENTER.
Un tas ir izdarīts.
Es to esmu paskaidrojis rakstā, kā MEKLĒT vairākas vērtības programmā Excel.
Tātad, jā, puiši, šajā rakstā es esmu aptvēris visus iespējamos VLOOKUP funkcijas aspektus atbilstoši manām zināšanām. Ja jūs domājat, ka esmu kaut ko palaidis garām, lūdzu, informējiet mani tālāk komentāru sadaļā.
Vlookup 5 populārākās vērtības ar dublētām vērtībām, izmantojot programmu INDEX-MATCH programmā Excel
VLOOKUP vairākas vērtības
VLOOKUP ar Dynamic Col Index
Daļēja atbilstība funkcijai VLOOKUP
Izmantojiet VLOOKUP no divām vai vairākām uzmeklēšanas tabulām
Vlookup pēc datuma programmā Excel
Izmantojot VLOOKUP formulu, lai pārbaudītu, vai vērtība pastāv
Kā VLOOKUP no dažādām Excel lapām
VLOOKUP ar cipariem un tekstu
IF, ISNA un VLOOKUP funkcija
ISNA un VLOOKUP funkcija
IFERROR un VLOOKUP funkcija