Šajā datu vadītajā pasaulē datu pārvaldībai ir nepieciešami dažādi rīki. Dati reāllaikā ir milzīgi, un detaļu iegūšana par kādu konkrētu datu gabalu noteikti būtu nogurdinošs uzdevums, bet ar VLOOKUP Excel , šo uzdevumu var sasniegt ar vienu komandrindu. Šajā rakstā jūs uzzināsiet par vienu no svarīgākajiem Excel funkcijas t.i., funkcija VLOOKUP.
Pirms turpināt, apskatīsim visas tēmas, kas šeit tiek apspriestas:
- Kas ir VLOOKUP programmā Excel?
- Kā tas darbojas?
- Precīza atbilstība
- Aptuvenā spēle
- Pirmais mačs
- Reģistrjutība
- Kļūdas
- Divvirzienu meklēšana
- Izmantojot aizstājējzīmes
- Vairākas uzmeklēšanas tabulas
Kas ir VLOOKUP programmā Excel?
Programmā Excel VLOOKUP ir a iebūvēta funkcija kas tiek izmantots, lai meklētu un ielādētu konkrētus datus no Excel lapas. V apzīmē vertikāli, un, lai programmā Excel izmantotu funkciju VLOOKUP, datiem jābūt sakārtotiem vertikāli. Šī funkcija ir ļoti noderīga, ja jums ir milzīgs datu apjoms, un manuāli meklēt dažus konkrētus datus būtu praktiski neiespējami.
Kā tas darbojas?
Funkcija VLOOKUP ņem vērtību, ti, uzmeklēšanas vērtību, un sāk to meklēt kreisajā kolonnā. Kad tiek atrasts pirmais uzmeklēšanas vērtības gadījums, tas sāk virzīties pa labi šajā rindā un atgriež vērtību no jūsu norādītās kolonnas. Šo funkciju var izmantot, lai atgrieztu gan precīzas, gan aptuvenas atbilstības (noklusējuma atbilstība ir aptuvena atbilstība).
Sintakse:
Šīs funkcijas sintakse ir šāda:
VLOOKUP (uzmeklēšanas_vērtība, tabulas_zīmējums, kol_indeksa_numurs, [diapazona_meklējums])
kur,
- uzmeklēšanas_vērtība ir vērtība, uz kuru jāpievērš uzmanība dotās tabulas pirmajā kolonnā
- table_index ir tabula, no kuras jāiegūst dati
- col_index_num ir kolonna, no kuras jāiegūst vērtība
- range_lookup ir loģiska vērtība, kas nosaka, vai uzmeklēšanas vērtībai ir jābūt ideālai vai aptuvenai atbilstībai ( PATIESA atradīs vistuvāko spēli FALSE pārbauda precīzu atbilstību)
Precīza atbilstība:
Kad vēlaties, lai funkcija VLOOKUP meklētu precīzu uzmeklēšanas vērtības atbilstību, jums būs jāiestata range_lookup vērtība FALSE. Apskatiet šo piemēru, kas ir tabula, kurā ir detalizēta informācija par darbiniekiem:
Ja vēlaties meklēt kādu no šiem darbiniekiem, varat rīkoties šādi:
- Atlasiet šūnu, kurā vēlaties parādīt izvadi, un pēc tam ierakstiet “=” zīmi
- Izmantojiet funkciju VLOOKUP un piegādājiet uzmeklēšanas_vērtība (Šeit tas būs darbinieka ID)
- Pēc tam ievadiet citus parametrus, t.i. tabula_mugurs , col_index_num un iestatiet range_lookup vērtība FALSE
- Tāpēc funkcija un tās parametri būs: = VLOOKUP (104, A1: D8, 3, FALSE)
Funkcija VLOOKUP sāk meklēt darbinieka ID 104 un pēc tam virzās uz labo pusi rindā, kur ir atrasta vērtība. Tas turpinās līdz col_index_num un atgriež vērtību, kas atrodas šajā pozīcijā.
Aptuvenā spēle:
Šī funkcijas VLOOKUP funkcija ļauj iegūt vērtības pat tad, ja jums nav precīzas atbilstības loopup_value. Kā minēts iepriekš, lai liktu VLOOKUP meklēt aptuvenu spēli, jums būs jāiestata range_lookup vērtība uz TRUE. Apskatiet nākamo piemēru, kur zīmes tiek kartētas kopā ar to atzīmēm un klasi, kurai tās pieder.
- Tāpat kā to, kā veicāt precīzu spēli, veiciet tās pašas darbības
- Vērtības range_lookup vietā FALSE vietā izmantojiet TRUE
- Tāpēc funkcija kopā ar tās parametriem būs: = VLOOKUP (55, A12: C15, 3, TRUE)
Tabulā, kas sakārtota augošā secībā, VLOOKUP sāk meklēt aptuveno atbilstību un apstājas pie nākamās lielākās vērtības, kas ir mazāka par ievadīto uzmeklēšanas vērtību. Pēc tam tas pārvietojas pa labi šajā rindā un atgriež vērtību no norādītās kolonnas. Iepriekš minētajā piemērā uzmeklēšanas vērtība ir 55 un nākamā lielākā uzmeklēšanas vērtība pirmajā kolonnā ir 40. Tāpēc izeja ir otrā klase.
Pirmā spēle:
Ja jums ir tabula, kas sastāv no vairākām uzmeklēšanas vērtībām, VLOOKUP apstājas pie tās pirmās atbilstības un izgūst vērtību no šīs rindas norādītajā kolonnā.
Apskatiet zemāk redzamo attēlu:
ID 105 tiek atkārtots, un, ja uzmeklēšanas vērtība ir norādīta kā 105, VLOOKUP ir atgriezis vērtību no rindas, kurai ir pirmā uzmeklēšanas vērtība.
Reģistrjutība:
VLOOKUP funkcija nav reģistrjutīga. Ja jums ir uzmeklēšanas vērtība, kas lielajiem burtiem un tabulā esošā vērtība ir maza, VLOOKUP joprojām iegūs vērtību no rindas, kurā atrodas šī vērtība. Apskatiet zemāk redzamo attēlu:
Kā redzat, vērtība, kuru esmu norādījis kā parametru, ir “RAFA”, savukārt tabulā esošā vērtība ir “Rafa”, bet VLOOKUP joprojām ir atgriezusi norādīto vērtību. Ja jums ir precīza atbilstība pat ar lietu, VLOOKUP joprojām atgriezīs pirmo uzmeklēšanas vērtības atbilstību neatkarīgi no izmantotā gadījuma. Apskatiet zemāk redzamo attēlu:
ko visi var nagios uzraudzīt
Kļūdas:
Ir dabiski sastapties ar kļūdām ikreiz, kad izmantojam funkcijas. Tāpat, izmantojot VLOOKUP funkciju, var rasties kļūdas, un dažas no izplatītākajām kļūdām ir šādas:
- #NAME
- # N / A
- # REF
- # VĒRTĪBA
#NAME kļūda:
Šīs kļūdas pamatā ir informēt jūs, ka sintaksē esat pieļāvis kļūdu. Lai izvairītos no sintaktiskām kļūdām, katrai funkcijai labāk izmantot Excel piedāvāto funkciju vedni. Funkciju vednis palīdz jums iegūt informāciju par katru parametru un ievadāmo vērtību veidu. Apskatiet zemāk redzamo attēlu:
Kā redzat, funkciju vednis informē, ka parametra lookup_value vietā jāievada jebkura veida vērtība, kā arī sniedz to īsu aprakstu. Līdzīgi, atlasot citus parametrus, redzēsiet arī informāciju par tiem.
# N / A kļūda:
Šī kļūda tiek atgriezta, ja norādītajai uzmeklēšanas vērtībai nav atrasta atbilstība. Piemēram, ja “RAFA” vietā ievadīšu “AFA”, saņemšu kļūdu # N / A.
Lai noteiktu iepriekš minēto divu kļūdu kļūdas ziņojumu, varat izmantot IFNA funkciju. Piemēram:
#REF kļūda:
Šī kļūda rodas, sniedzot atsauci uz kolonnu, kas tabulā nav pieejama.
#VALUE kļūda:
Šī kļūda rodas, ja parametriem ievietojat nepareizas vērtības vai izlaižat dažus obligātos parametrus.
Divvirzienu meklēšana:
Divvirzienu meklēšana attiecas uz vērtības iegūšanu no divdimensiju tabulas no jebkuras atsauces tabulas šūnas. Lai veiktu divvirzienu meklēšanu, izmantojot VLOOKUP, jums kopā ar to būs jāizmanto funkcija MATCH.
MATCH sintakse ir šāda:
MATCH (uzmeklēšanas_vērtība, uzmeklēšanas_zīmējums, atbilstības tips)
- uzmeklēšanas_vērtība ir vērtība, kas jāmeklē
- look_array ir šūnu diapazons, kas sastāv no uzmeklēšanas vērtībām
- match_type var būt skaitlis, t.i., vai nu 0, 1 vai -1, kas apzīmē precīzu atbilstību, attiecīgi mazāks par un lielāks par
Tā vietā, lai ar VLOOKUP izmantotu stingri kodētas vērtības, varat to padarīt dinamiski apietu šūnu atsaucēs. Apsveriet šādu piemēru:
Kā redzat augšējā attēlā, funkcija VLOOKUP uzmeklēšanas vērtībai norāda šūnu atsauci kā F6, un kolonnas indeksa vērtību nosaka funkcija MATCH. Veicot izmaiņas kādā no šīm vērtībām, attiecīgi mainīsies arī izeja. Apskatiet zemāk redzamo attēlu, kur es esmu mainījis F6 vērtību no Chris uz Leo, un attiecīgi tiek atjaunināta arī izeja:
Gadījumā, ja es mainu G5 vērtību, vai nu F6, gan G5, šī formula attiecīgi darbosies, parādot attiecīgos rezultātus.
Varat arī izveidot nolaižamos sarakstus, lai vērtību maiņa būtu ļoti ērta. Iepriekš minētajā piemērā tas jādara ar F6 un G5. Lūk, kā jūs varat izveidot nolaižamos sarakstus:
- Lentes cilnē atlasiet Dati
- Datu rīku grupā atlasiet Datu pārbaude
- Atveriet iestatījumu rūti un sadaļā Atļaut atlasiet Saraksts
- Norādiet avotu saraksta masīvu
Kad esat izveidojis nolaižamo sarakstu, tas izskatās šādi:
Izmantojot aizstājējzīmes:
Ja nezināt precīzu uzmeklēšanas vērtību, bet tikai daļu no tās, varat izmantot aizstājējzīmes. Programmā Excel simbols “*” apzīmē aizstājējzīmi. Šis simbols informē Excel, ka ir jāmeklē secība, kas nāk pirms, pēc vai starp tām, un pirms vai pēc tām var būt jebkurš rakstzīmju skaits. Piemēram, manis izveidotajā tabulā ievadot “erg” kopā ar aizstājējzīmēm abās pusēs, VLOOKUP atgriezīs “Sergio” izvadi, kā parādīts zemāk:
Vairākas uzmeklēšanas tabulas:
Ja jums ir vairākas uzmeklēšanas tabulas, varat izmantot IF funkciju kopā ar to, lai izpētītu kādu no tabulām, pamatojoties uz kādu konkrētu nosacījumu. Piemēram, ja ir tabula ar divu lielveikalu datiem un jums jānoskaidro katra no tiem gūtā peļņa, pamatojoties uz pārdošanas apjomu, varat rīkoties šādi:
Izveidojiet galveno tabulu šādi:
Pēc tam izveidojiet divas tabulas, no kurām jāiegūst peļņa.
Kad tas ir izdarīts, izveidojiet katrai no jaunizveidotajām tabulām nosaukto diapazonu. Lai izveidotu nosaukto diapazonu, rīkojieties šādi:
- Atlasiet visu tabulu, kurai vēlaties piešķirt nosaukumu
- Cilnes lentē atlasiet Formulas un pēc tam grupā Definētie vārdi atlasiet Definēt vārdu
- Tiks parādīts šāds dialoglodziņš
- Piešķiriet jebkuru vārdu pēc izvēles
- Noklikšķiniet uz Labi
Kad tas ir izdarīts abām tabulām, šos nosauktos diapazonus IF funkcijā varat izmantot šādi:
Kā redzat, VLOOKUP ir atgriezis atbilstošās vērtības, lai aizpildītu kolonnu Peļņa atbilstoši tam, kādam lielveikalam tie pieder. Tā vietā, lai rakstītu formulu katrā kolonnas Peļņa šūnā, man ir tikai nokopēja formulu lai ietaupītu laiku un enerģiju.
Tādējādi mēs nonākam līdz šī raksta beigām par VLOOKUP programmā Excel. Es ceru, ka jums ir skaidrs viss, kas ar jums dalīts. Pārliecinieties, ka pēc iespējas vairāk praktizējat un atgriezieties pie pieredzes.
Vai mums ir jautājums? Lūdzu, pieminējiet to šī emuāra “VLOOKUP in Excel” komentāru sadaļā, un mēs pēc iespējas ātrāk sazināsimies ar jums.
Lai iegūtu padziļinātas zināšanas par visām populārākajām tehnoloģijām un to dažādajām lietojumprogrammām, varat reģistrēties tiešraidē ar diennakts atbalstu un piekļuvi mūža garumā.