Excel VLOOKUP funkcija tiek izmantota, lai atrastu un atgrieztu informāciju no datu tabulas, pamatojoties uz izvēlēto meklēšanas vērtību.
Parasti VLOOKUP prasa, lai meklēšanas vērtība būtu datu tabulas kreisajā stūrī, un funkcija atgriež citu datu lauku, kas atrodas tajā pašā rindā pa labi no šīs vērtības.
Tomēr, apvienojot VLOOKUP ar funkciju CHOOSE, var izveidot kreisās meklēšanas formulu, kas:
- Pieļauj, ka meklēšanas vērtība ir no jebkura datu tabulas kolonnas
- Atgriež informāciju, kas atrodas jebkurā slejā no meklēšanas vērtības pa kreisi
Piezīme: Šīs instrukcijas attiecas uz Excel versijām 2019, 2016, 2013, 2010 un Excel 365 Office.
01 no 05Apmācība: izmantojiet VLOOKUP un izvēlieties funkcijas kreisās meklēšanas formulas
Lai izveidotu kreisās meklēšanas formulu, kas redzama piemēra attēlā, izmantojiet formulu:
= VLOOKUP ($ D $ 2, IZVĒLIETIES ({1,2}, $ F: $ F, $ D: $ D), 2, FALSE)
Šajā piemērā šī formula ļauj atrast daļu, ko piegādā dažādie uzņēmumi, kas uzskaitīti datu tabulas 3. ailē.
Funkcija IZVĒLĒT funkciju formulā ir viltot VLOOKUP, uzskatot, ka kolonna 3 ir 1. sleja. Tā rezultātā uzņēmuma nosaukumu var izmantot kā meklēšanas vērtību, lai atrastu katra uzņēmuma piegādātās daļas nosaukumu.
Ievadiet apmācības datus
- Ievadiet virsrakstu Piegādātājs uz šūnu D1.
- Ievadiet virsrakstu Daļa šūnā E1.
- Ievadiet tabulā datus, kas redzami attēlā iepriekš, šūnās D4 līdz F9
- Rindas 2 un 3 tiek atstāti tukši, lai pielāgotos meklēšanas kritērijiem un kreisās meklēšanas formulu, kas izveidota šajā apmācībā
Atveriet VLOOKUP dialoglodziņu
Lai gan formu var tieši ievadīt darblapā šūnā F1, daudziem cilvēkiem ir grūtības ar formulas sintaksi.
Šajā gadījumā ir drošāk izmantot dialoglodziņu VLOOKUP. Gandrīz visās Excel funkcijās ir dialoglodziņš, kas ļauj ievadīt katru no funkciju argumentiem atsevišķā rindā.
- Noklikšķiniet uz šūnas E2 no darblapas. E2 ir vieta, kur tiks parādītas kreisās meklēšanas formulas rezultāti.
- Noklikšķiniet uz Formulas lentes cilne.
- Noklikšķiniet uz Meklēt un atsauce lentē, lai atvērtu nolaižamo sarakstu funkciju.
- Klikšķiniet uz VLOOKUP sarakstā, lai atvērtu funkciju dialoglodziņu.
Argumentu ievadīšana dialoglodziņā VLOOKUP
Funkcijas argumenti ir vērtības, ko funkcija izmanto, lai aprēķinātu rezultātu.
Funkcijas dialoglodziņā katra argumenta nosaukums ir atsevišķā rindā, kam seko lauka, kurā ievadīt vērtību.
Ievadiet šādas vērtības katram VLOOKUP argumentam pareizajā dialoglodziņā, kā parādīts pievienotajā attēlā.
Uzskaites vērtība
Uzmeklēšanas vērtība ir informācijas lauks, ko izmanto, lai meklētu tabulas masīvu. VLOOKUP atgriež citu datu lauku no vienas rindas kā meklēšanas vērtību.
Šajā piemērā tiek izmantota šūnu atsauce uz vietu, kurā uzņēmuma nosaukums tiks ievadīts darblapā. Šī priekšrocība ir tā, ka ir viegli nomainīt uzņēmuma nosaukumu, neizmainot formulu.
- Noklikšķiniet uz lookup_value dialoglodziņā.
- Noklikšķiniet uz šūnas D2 lai pievienotu šo šūnu atsauci uz lookup_value līnija
- Nospiediet F4 tastatūras taustiņš, lai padarītu šūnas atsauci absolūtu - $ D $ 2.
Absolūtās šūnu atsauces
Lai novērstu kļūdas, ja meklēšanas formulas tiek kopētas citās darblapas šūnās, meklēšanas lodziņu vērtībai un tabulas masīva argumentiem tiek izmantotas absolūto šūnu atsauces.
Ievadiet funkciju IZVĒLE
Tabulas masīvu arguments ir to blakus esošo datu bloks, no kuriem iegūst konkrētu informāciju.
Parasti VLOOKUP izskatās tikai pa labi no meklējamo vērtību argumenta, lai atrastu datus tabulas masīvā. Lai tas izskatās pa kreisi, VLOOKUP ir jāiegremdē, pārkārtoties kolonnas tabulas masīvā, izmantojot funkciju CHOOSE.
Šajā formā funkcija CHOUE veic divus uzdevumus:
- Tas izveido tabulas masīvu, kas ir tikai divu kolonnu plats (kolonnas D un F).
- Tas maina tabulas masīvu kolonnu labo vai kreiso secību, lai vispirms parādās aile F un kolonna D ir otra.
Funkciju ievadīšana
Ievadot funkcijas manuāli, katra funkcija ir atdalāma ar komatu.
- Dialoglodziņā VLOOKUP funkcija noklikšķiniet uz Table_array līnija
- Ievadiet sekojošo IZVĒLIETIES funkcija:IZVĒLIETIES ({1,2}, $ F: $ F, $ D: $ D)
Slejas indeksa numurs
Parasti kolonnu indeksa numurs norāda, kura tabulas masīva kolonna satur datus, pēc kuriem esat pēc tam. Tomēr šajā formulā tas attiecas uz kolonnu secību, kas noteikta, izvēloties funkciju IZVĒLE.
Funkcija IZVĒLES izveido tabulas masīvu, kas ir divu kolonnu plats ar pirmo sleju F un pirmo sekojošu kolonnu D. Tā kā pieprasītā informācija - daļas nosaukums ir D ailē, kolonnu indeksa argumentam jābūt iestatītam uz 2.
- Noklikšķiniet uz Col_index_num dialoglodziņā.
- Ievadiet a 2 šajā rindiņā.
Diapazona meklēšana
VLOOKUP Range_lookup arguments ir loģiska vērtība (tikai TRUE vai FALSE), kas norāda, vai VLOOKUP vēlaties atrast precīzu vai aptuvenu atbilstību meklēšanas vērtībai.
- Ja TRUE vai ja šis arguments ir izlaists, VLOOKUP atgriež vai nu precīzu atbilstību Lookup_value vai, ja precīzu atbilstību nav atrasts, VLOOKUP atgriež nākamo lielāko vērtību. Lai to izdarītu, formula Table_array pirmajā slejā ir jāsakārto augošā secībā.
- Ja FALSE, VLOOKUP izmanto precīzu atbilstību tikai Lookup_value. Ja Table_array pirmajā slejā ir divas vai vairākas vērtības, kas atbilst meklēšanas vērtībai, tiek izmantota pirmā atrasta vērtība. Ja precīzā atbilstība netiek atrasta, tiek atgriezta # N / A kļūda.
Šajā apmācībā, jo mēs meklējam konkrētu daļu nosaukumu, Range_lookup ir iestatīts uz Nepatiesa lai formula kļūst par precīzām precēm.
- Noklikšķiniet uz Range_lookup dialoglodziņā.
- Ierakstiet vārdu Nepatiesa šajā rindā, lai norādītu, ka mēs vēlamies, lai VLOOKUP atgrieztu precīzu atbilstību meklētajiem datiem.
- Klikšķis labi lai pabeigtu kreisās meklēšanas formulu un aizveriet dialoglodziņu.
- Tā kā mēs vēl neesam iekļāvuši uzņēmuma nosaukumu šūnā D2, šūnā E2 parādās # N / A kļūda.
Datu atgūšana ar kreisās meklēšanas formulu
Lai noskaidrotu, kuras kompānijas piegādā kādas detaļas, ierakstiet uzņēmuma nosaukumu šūnā D2 un nospiediet ENTER tastatūras taustiņš.
Daļas nosaukums tiek parādīts šūnā E2.
- Noklikšķiniet uz šūnas D2 savā darblapā.
- Tips Sīkrīki plus uz šūnu D2 un nospiediet ENTER tastatūras taustiņš.
- Teksts "Sīkrīki" - daļa, ko piegādā kompānija Gadgets Plus - jāiedala šūnā E2.
Tālāk pārbaudiet meklēšanas formulu, ierakstot citus uzņēmuma nosaukumus šūnā D2, un atbilstošā daļas nosaukums jāparādās šūnā E2.
Ja šūnā E2 parādās kļūdas ziņojums, piemēram # N / A, pārbaudiet pareizrakstības kļūdas šūnā D2.
04 no 05Divu sleju tabulas masīvu izveide
Funkcijas CHOOSE sintakse ir:
= IZVĒLIETIES (Index_number, Value1, Value2, … Value254)
Funkcija IZVĒLES parasti atgriež vienu vērtību no vērtību saraksta (no 1 vērtības uz 25 vērtību), pamatojoties uz ievadīto indeksa numuru.
Ja indeksa numurs ir 1, funkcija atgriež Value1 no saraksta; ja indeksa numurs ir 2, funkcija atgriež vērtību no saraksta un tā tālāk.
Ja tiek ievadīti vairāki indeksu numuri, funkcija atgriež vairākas vērtības jebkurā vēlamā secībā. Izvēloties IZVĒLE, lai atgrieztu vairākas vērtības, tiek izveidots masīvs.
Lai ievadītu masīvu, ieskauj ciparus, kas ievadīti ar cirtainiem balsteņiem vai kronšteiniem. Indeksam ir ievadīti divi numuri:{ 1,2 }.
Jāatzīmē, ka IZVĒLE ir ne tikai divu sleju tabulas izveidošana. Iekļaujot papildu skaitli masīvā - piemēram, {1,2,3} - un papildu diapazonu vērtības argumentā, CHOOSE izveido trīs kolonnu tabulu.
Papildu slejas ļauj jums atgriezt dažādu informāciju ar kreisās meklēšanas formulu, mainot VLOOKUP kolonnu indeksa numuru argumentu uz kolonnas numuru, kurā ir norādītā informācija.
05 no 05Kolonnu kārtības maiņa, izmantojot funkciju IZVĒLE
Šajā izvēles panelī IZVĒLĒT funkciju:
IZVĒLIETIES ({1,2}, $ F: $ F, $ D: $ D)
diapazons F slejai ir norādīts pirms kolonnas D.
Tā kā funkcija CHOUE iestata VLOOKUP tabulas masīvu (šīs funkcijas datu avotu), funkciju CHOOSE kolonnu maiņa tiek pārsūtīta gar VLOOKUP.
Ciktāl tas attiecas uz VLOOKUP, galda masīvs ir tikai divu kolonnu plats ar kolonnu F kreisajā pusē un kolonnu D pa labi. Tā kā slejā F ir uzņēmuma nosaukums, kuru mēs vēlamies meklēt, un tā kā slejā D ir daĜu nosaukumi, VLOOKUP var veikt regulārus meklēšanas pienākumus, lai atrastu datus, kas atrodas pa kreisi no meklēšanas vērtības.
Rezultātā VLOOKUP var izmantot uzņēmuma nosaukumu, lai atrastu to piegādāto daļu.
