Astăzi începem o serie de articole care descriu una dintre cele mai utile funcții Excel - VLOOKUP. Această funcție, în același timp, este una dintre cele mai complexe și mai puțin înțelese.
În acest manual al VPR, voi încerca să pun la punct noțiunile de bază în limbajul cel mai simplu posibil, pentru a face procesul de învățare pentru utilizatorii neexperimentați cât mai clară posibil. În plus, vom studia câteva exemple cu formule Excel care arată cele mai uzuale utilizări pentru funcția VLOOK.
Funcția VLOOKUP din Excel este o descriere generală și o sintaxă
Deci, ce este VPR? În primul rând, este o funcție Excel. Ce face ea? Se caută valoarea pe care o specificați și returnează valoarea corespunzătoare dintr-o altă coloană. În termeni tehnici, VLOOK caută valoarea din prima coloană a intervalului specificat și returnează rezultatul dintr-o altă coloană din același rând.
În cea mai obișnuită utilizare, funcția VLOOKUP caută baza de date pentru un identificator unic dat și preia unele informații conexe din baza de date.
Prima literă din numele funcției VLOOKUP înseamnă Vertical. Pe acesta puteți distinge între VLR și HLOOKUP, care caută valoarea din linia superioară a intervalului - orizontul H (H orizontal).
Sintaxa funcției VLOOKUP
Funcția VLOOKUP are această sintaxă:
După cum puteți vedea, funcția VLOOKUP din Microsoft Excel are 4 parametri (sau argument). Primele trei - obligatorii, ultima - dacă este necesar.
- lookup_value (search_value) - valoarea căutată.
Dacă valoarea dorită este mai mică decât cea mai mică valoare din prima coloană a intervalului scanat, funcția VLOOKUP raportează # N / A (# N / A).
- table_array (table) - două sau mai multe coloane cu date.
Rețineți că funcția VLOOKUP caută întotdeauna valoarea din prima coloană a intervalului specificat în argumentul table_array (tabel). În intervalul vizualizat pot exista diverse date, de exemplu, text, date, numere, valori logice. Registrul de caractere nu este considerat de funcția respectivă, adică se consideră că literele superioare și minuscule sunt aceleași.
Astfel, formula noastră va căuta o valoare de 40 în celulele de la A2 la A15. deoarece A este prima coloană din intervalul A2: B15, specificată în argumentul table_array (tabel):
- col_index_num (număr_pagină) este numărul coloanei din intervalul specificat, din care va fi returnată valoarea găsită în șirul găsit.
Coloana din stânga din intervalul dat este 1. a doua coloană este 2. a treia coloană este 3 și așa mai departe. Acum puteți citi întreaga formulă:
Formula urmărește o valoare de 40 în intervalul A2: A15 și returnează valoarea corespunzătoare din coloana B (deoarece B este a doua coloană din domeniul A2: B15).
Acest parametru este opțional, dar foarte important. Mai departe, în acest manual VPR vă voi arăta câteva exemple care explică modul în care să formulezi corect formulele pentru a găsi coincidență exactă și aproximativă.
Exemple cu funcția VLF
Sper că funcția VLOOK a devenit un pic mai ușor de înțeles pentru dvs. Acum, să aruncăm o privire asupra câtorva exemple de utilizare a VLR în formule cu date reale.
Cum, utilizând VLOOK, efectuați o căutare pe o altă foaie Excel
În practică, formulele cu funcția VLF sunt rareori utilizate pentru a găsi date pe aceeași foaie. Cel mai adesea veți căuta și extrageți valorile corespunzătoare dintr-o altă foaie.
Pentru a utiliza VPR. efectuați o căutare pe o altă foaie de Microsoft Excel, trebuie să specificați numele foii cu un semn de exclamare în argumentul table_array (tabel) și apoi domeniul de celule. De exemplu, următoarea formulă arată că domeniul A2: B15 se află pe o foaie numită Sheet2.
Desigur, numele foii nu trebuie introdus manual. Trebuie doar să începeți să tastați formula, iar atunci când este vorba de table_array argument (a se vedea tabelul), comutați la pagina dorită și trageți mouse-ul în intervalul dorit de celule.
Formula prezentată în imaginea de mai jos, caută textul „Produs 1“ în coloana A (aceasta este prima gamă coloană A2: B9) pe foaia de prețuri.
= VLOOKUP ("Produs 1", Prețuri $ A $ 2: $ B $ 9,2, FALSE)
= VPR ("Produs 1"; Prețuri $ A $ 2: $ B $ 9; 2; LIE)
Rețineți că atunci când căutați o valoare de text, trebuie să o înscrieți în ghilimele (""), așa cum se face de obicei în formulele Excel.
Pentru argumentul table_array (tabel), este întotdeauna de dorit să se utilizeze referințe absolute (cu semnul $). În acest caz, intervalul de căutare va rămâne neschimbat când formula este copiată în alte celule.
Căutați în alt registru de lucru utilizând VLF
Pentru ca funcția VLOOKUP să funcționeze între două registre de lucru Excel, trebuie să specificați numele cărții în paranteze drepte înainte de numele foii.
De exemplu, mai jos este o formulă care caută o valoare de 40 pe foaia Sheet2 în Numbers.xlsx:
Iată modul cel mai simplu de a crea o formulă Excel în Excel. care se referă la un alt registru de lucru:
- Deschideți ambele cărți. Acest lucru nu este necesar, dar este mai ușor să creați o formulă. Nu doriți să introduceți manual numele cărții? În plus, acest lucru vă va proteja de greșelile accidentale.
- Începeți să introduceți funcția VLOOKUP. și când vine vorba de argumentul table_array (tabel), treceți la un alt registru de lucru și selectați intervalul de căutare dorit în el.
Imaginea de mai jos a ecranului arată formula în care este specificat intervalul din registrul de lucru PriceList.xlsx din foaia de lucru Preturi.
Funcția VLOOKUP va funcționa chiar și atunci când închideți registrul de lucru în care se efectuează căutarea, iar calea completă la fișierul din registrul de lucru apare în bara de formule, după cum se arată mai jos:
Dacă numele din registrul de lucru sau foaia de lucru conține spații, trebuie să îl includeți în apostrofuri:
Cum se utilizează un interval sau un tabel numit în formule cu VLR
Dacă intenționați să utilizați o gamă de căutare în mai multe funcții VLOOK. puteți crea un interval numit și introduceți numele acestuia în formula ca argument table_array (tabel).
Pentru a crea un interval numit, pur și simplu selectați celulele și introduceți un nume adecvat în câmpul Nume. la stânga liniei formulei.
Acum puteți scrie această formulă pentru a căuta prețul produsului Produs 1:
= VLOOKUP ("Produsul 1", Produse, 2)
= VLOOK ("Produs 1"; Produse; 2)Cele mai multe dintre numele pentru toate lucrările variază Excel registru de lucru, astfel încât nu este necesar să se specifice numele foii pentru argumentul table_array (a se vedea tabelul), chiar dacă formula și intervalul de căutare sunt pe diferite pagini ale cărții. În cazul în care acestea sunt în diferite cărți, trebuie să specificați numele registrului de lucru înainte de numele gamei, de exemplu, ca aceasta:
= VLOOKUP ("Produs 1", produse PriceList.xlsx !, 2)
= VLP ("Produsul 1"; Produse PriceList.xlsx; 2)Dacă convertiți o serie de celule într-o foaie de calcul complet Excel, folosind tab-ul de comandă (tabel) Insert (Inserare), apoi alocarea gamei mouse-ului, Microsoft Excel adaugă automat numele formula coloana (sau numele tabelei dacă selectați întregul tabel).
Formula finita va arata cam asa:
= VLOOKUP ("Produsul 1", tabelul 46 [[Produs]: [Pret]], 2)
= VPR ("Produs 1"; Tabel 46 [[Produs]: [Pret]]; 2)Și poate chiar așa:
= VLOOKUP ("Produsul 1", Tabelul 46.2)
= VLOOK ("Produsul 1"; Tabelul 46; 2)Dacă utilizați intervale numite, legăturile vor conduce la aceleași celule, indiferent unde copiați funcția VLOOKUP din registrul de lucru.
Folosind metacaractere în formule cu VLOOKING
Ca și în multe alte funcții, pe VLOOK puteți folosi următoarele caractere cu machete:
- Semnul de întrebare (?) - înlocuiește un singur caracter.
- Un asterisc (*) - înlocuiește orice secvență de caractere.
Folosirea caracterelor wildcard în funcțiile VLOOK poate fi utilă în multe cazuri, de exemplu:
- Când nu vă amintiți exact textul pe care trebuie să-l găsiți.
- Când doriți să găsiți un cuvânt care face parte din conținutul celulei. Fiți conștienți de faptul că VLOO caută întregul conținut al celulei, la fel ca în cazul opțiunii "Match entire content cell" în căutarea standard în Excel.
- Când o celulă conține spații suplimentare la începutul sau la sfârșitul conținutului. În această situație, puteți să faceți puzzle mult timp, încercând să înțelegeți de ce formula nu funcționează.
Exemplul 1: Căutarea unui text care începe sau se termină cu anumite caractere
Să presupunem că doriți să găsiți un anumit client în baza de date prezentată mai jos. Nu-ți amintești numele de familie, dar știi că începe cu "ack". O astfel de formulă se va descurca perfect cu această sarcină:
Acum că sunteți sigur că ați găsit numele corect, puteți utiliza aceeași formulă pentru a găsi suma plătită de acest client. Pentru a face acest lucru, este suficient să modificați al treilea argument al funcției VLOOKUP la numărul coloanei dorite. În cazul nostru, aceasta este coloana C (a treia în interval):
Mai jos sunt câteva exemple cu metacaractere:
Căutăm numele care se termină cu "om":
Găsim un nume care începe cu "anunț" și se termină cu "fiu":
Gasim primul nume in lista, format din 5 caractere:
= VLOOKUP (".", $ A $ 2: $ C $ 11.1, FALSE)
= VPR (".", $ A $ 2: $ C $ 11; 1; FALSE)Pentru ca funcția VLOOK să funcționeze corect cu caracterele "wildcard", FALSE (FALSE) ar trebui să fie întotdeauna folosit ca al patrulea argument. Dacă intervalul de căutare conține mai mult de o valoare care corespunde condițiilor de căutare cu caractere cu metacaractere, prima valoare găsită va fi returnată.
Exemplul 2: Combinăm caracterele și referințele celulare în formulele cu VLOOK
Și acum să luăm un exemplu puțin mai complicat de a efectua o căutare folosind funcția VLOOK prin valoare într-o celulă. Imaginați-vă că coloana A conține o listă de chei de licență, iar coloana B conține o listă de nume care dețin licența. În plus, aveți parte (mai multe caractere) a unei anumite chei de licență în celula C1 și doriți să găsiți numele proprietarului.
Acest lucru se poate face folosind următoarea formulă:
Această formulă caută valoare în celula C1 într-un interval predeterminat, și returnează valoarea corespunzătoare a coloanei B. De notat că primul argument, folosim caracterul ampersand () înainte și după trimiterea celulei pentru a lega șir text.
După cum puteți vedea în figura de mai jos, funcția VLOOK returnează valoarea "Jeremy Hill" deoarece cheia de licență conține o secvență de caractere din celula C1.
Rețineți că argumentul table_array (table) din captura de ecran de mai sus conține numele tabelului (Table7) în loc de a specifica intervalul de celule. Așa am făcut în exemplul anterior.
Coincidență exactă sau aproximativă în funcția VLR
Mai întâi, să aflăm ce înseamnă Microsoft Excel prin coincidență exactă și aproximativă.
De exemplu, următoarea formulă va raporta # N / A (# N / A) dacă nu există valoare în intervalul A2: A15 de 4:
Pentru a înțelege mai bine importanța alegerii TRUE sau FALSE, să examinăm mai multe formule cu funcția VLOOK și să examinăm rezultatele.
Exemplul 1: Găsirea potrivirii corecte folosind VLF
După cum vă amintiți, pentru a găsi potrivirea exactă, al patrulea argument al funcției VLOOK trebuie să fie FALSE.
Să ne uităm din nou la masă din primul exemplu și să aflăm care animal poate călători cu o viteză de 50 de mile pe oră. Cred că o astfel de formulă nu vă va face dificultăți:
Rețineți că gama de căutare (coloana A) conține două valori de 50 - în celulele A5 și A6. Formula returnează o valoare din celula B5. De ce? Deoarece când căutați o potrivire exactă, funcția VLOOKUP utilizează prima valoare găsită care se potrivește cu cea pe care o căutați.
Exemplul 2: Utilizați VLOOK pentru a găsi o potrivire aproximativă
Acest lucru este foarte important deoarece funcția VLOOK returnează următoarea valoare mai mare după valoarea specificată și apoi căutarea se oprește. Dacă neglijezi sortarea corectă, problema se va termina prin faptul că vei obține rezultate foarte ciudate sau un mesaj de eroare # N / A (# N / A).
Acum puteți utiliza una dintre următoarele formule:
= VLOOKUP (69, $ A $ 2: $ B $ 15,2, TRUE) sau = VLOOKUP (69, $ A $ 2: $ B $ 15,2)
VPR (69; $ A $ 2: $ B $ 15; 2; TRUE) sau = VPR (69; $ A $ 2: $ B $ 15; 2)După cum puteți vedea, vreau să aflu care animal are viteza cea mai apropiată de 69 de mile pe oră. Și aici rezultatul mi-a revenit de funcția VLR:
După cum puteți vedea, formula returnează rezultatul Antelope (Antelope), viteza este de 61 de mile pe oră, deși lista este, de asemenea, Gepard (Cheetah), care se desfășoară la o viteză de 70 de mile pe oră, și 70 este mai aproape de 69 decât 61, nu? De ce se întâmplă acest lucru? Deoarece funcția VLOOKUP, atunci când căutați o potrivire aproximativă, returnează cea mai mare valoare care nu depășește cea pe care o căutați.
Sper că aceste exemple aruncă o lumină asupra lucrului cu funcția VLF din Excel și nu te mai uiți la ea ca pe un outsider. Acum nu durează scurt să repetăm punctele-cheie ale materialului studiat de noi, pentru ao consolida mai bine în memorie.
VPR în Excel - trebuie amintit!
Articole similare
Trimiteți-le prietenilor: