Meniul
AcasăJocuri

Ce face funcția de exemplu? Folosind o coloană suplimentară. Index și căutare combinate cu iferror în Excel

Tabelele create în Excel nu se caracterizează întotdeauna prin faptul că denumirile categoriilor de date trebuie definite doar în titlurile coloanelor. Uneori, atunci când analizăm datele din tabel, avem posibilitatea de a folosi atât titlurile de coloană, cât și numele rândurilor care se află în prima coloană.

Exemplu de formulă cu VLOOKUP și MATCH

Un exemplu de tabel cu raporturi bonus este prezentat mai jos în figură:

Scopul acestui tabel este de a găsi valorile premium adecvate în intervalul B5:K11 pe baza unei anumite sume de venituri și a magazinelor cu minim sau dimensiuni maxime plăți bonus. Dificultatea apare atunci când detecție automată mărimea bonusului pe care se poate baza un angajat atunci când depășește o anumită limită de venit. Deoarece nu există o valoare de plată a primei definită în mod clar pentru fiecare sumă probabilă de venit. Există doar limite inferioare și superioare ale sumelor premium pentru fiecare magazin.

De exemplu, avem nevoie ca programul să determine automat eventuala primă minimă pentru un vânzător din al 3-lea magazin al cărui venit a depășit nivelul de 370.000.

Pentru aceasta:

  1. În celula B14, introduceți suma veniturilor: 370.000.
  2. În celula B15, introduceți numărul magazinului: 3.
  3. În celula B16, introduceți următoarea formulă:

Ca urmare, s-a stabilit linia de jos bonusuri pentru magazinul nr. 3 cu venituri mai mari de >370.000, dar mai puține<400 000.



Prefață necesară

Dacă nu ați mai lucrat cu funcția înainte CĂUTARE V, atunci ai pierdut multe, recomand cu căldură să o citești mai întâi.

Problemă

După cum mulți oameni știu, funcția CĂUTARE V poate returna ca rezultat valori care se afla strict in dreapta coloanei in care se efectueaza cautarea. O, ce frumos ar fi dacă al treilea argument al acestei funcții (numărul coloanei din care sunt returnate valorile) ar putea fi setat la negativ, dar nu.

În practică, adesea apar situații când trebuie să căutați date într-o coloană care se află în dreapta, și nu în stânga, a coloanei cu rezultate, de exemplu:

Este ușor să găsiți costul după codul de comandă - o CĂUTARE V obișnuită vă va ajuta aici o dată sau de două ori. Dar cum să găsesc numele produsului după cod? La antrenamente, aud cel mai adesea această întrebare în formula „cum se face o CĂUTARE VL la stânga”?

Să ne uităm la câteva moduri.

Metoda 1. Atacul frontal

Dacă urmați principiul lui Occam și nu îl complicați în mod inutil, puteți pur și simplu să copiați coloana dorită în dreapta (sau să o faceți linkuri) și să utilizați coloana obișnuită. CĂUTARE V:


Ieftin și vesel, dar necesită finisarea manuală a mesei. În plus, există adesea cazuri în care un tabel nu poate fi schimbat: este protejat prin parolă, este un șablon corporativ, un tabel partajat etc. Atunci este nevoie de o abordare diferită.

Metoda 2. Rearanjarea virtuală a coloanelor folosind funcția SELECT

Dacă este imposibil să rearanjați coloanele pe foaie, atunci acest lucru se poate face virtual, adică. „din zbor” chiar în formula în sine. Pentru aceasta avem nevoie de o funcție ALEGE. Scopul său principal este de a selecta elementul dorit din listă cu un anumit număr. De exemplu, poate fi folosit pentru a înlocui numărul zilei săptămânii cu omologul său text:


Nimic supranatural la prima vedere, dar există câteva momente dificile aici.

in primul rand, în locul numelor text ale elementelor de listă selectate („Luni”, „Marți”, etc.), puteți utiliza adrese de interval. Și apoi funcția va returna un link către intervalul selectat. Deci, de exemplu, formula:

SELECTARE(2; A1:A10; D1:D10; B1:B10)

În al doilea rând, în loc de un simplu număr unic al elementului care trebuie preluat în primul argument al funcției ALEGERE Puteți specifica o matrice de constante în acolade, de exemplu, astfel:

ALEGERE( {1;2} ; A1:A10; D1:D10; B1:B10)

Apoi, la ieșire, vom obține primele două intervale (A1:A10 și D1:D10), lipite împreună într-un singur întreg.

Și acum toate acestea pot fi puse în interiorul nostru CĂUTARE V pentru a implementa „căutarea stângă”:


Diferă de „Căutare V clasică”, după cum puteți vedea, doar prin faptul că intervalul este specificat prin lipirea a două coloane Codul de comandaȘi Produs folosind funcția ALEGERE. În rest, totul este ca de obicei.

Dezavantajele acestei metode sunt viteza (de aproximativ 5-7 ori mai lentă decât un VLOOKUP obișnuit) și o oarecare nefamiliaritate pentru colegi (și poate că acesta este chiar un plus!)

Metoda 3. Conectarea funcțiilor INDEX și CĂUTARE

Dacă nu rămâneți la funcția VLOOKUP, puteți utiliza analogul mai puternic - o combinație de două funcții foarte utile INDEXȘi MECI:


Funcţie CĂUTARE caută o valoare dată (C2, adică codul comenzii de care avem nevoie) într-un interval unidimensional (coloana de coduri din tabelul C10:C25) și dă ca rezultat numărul de serie al celulei în care a găsit ceea ce am găsit. căutați - în cazul nostru va fi numărul 4, adică To. Codul de comandă de care avem nevoie este al patrulea în tabel.

Și apoi intră în joc funcția INDEX, care poate extrage date dintr-o matrice de coloane verticale (numele produselor în B10:B25) după numărul de serie (pe care l-a găsit anterior CĂUTARE). Prin urmare, INDEX ne va oferi conținutul celei de-a patra celule din coloană Produs, care este ceea ce s-a cerut.

În comparație cu metoda anterioară, această opțiune este recalculată mult mai rapid (aproape la fel de rapid ca o CĂUTARE V obișnuită), ceea ce este important pentru mesele mari.

Am analizat un exemplu similar (cu video) mai devreme. Și despre funcție INDEX poti vorbi :)

Metoda 4. Funcția SUMIF(MN).

Dacă trebuie să extrageți exact un număr dintr-un tabel (de exemplu, volumul în litri), atunci uneori este mai ușor să utilizați funcția de însumare selectivă pentru a implementa „Căutare V din stânga” SUMIF sau sora ei mai mare - funcția SUMIFS:


Dezavantajele acestei abordări sunt evidente - funcționează doar pentru numere și, cu condiția să nu existe valori duplicate în coloană. Dacă există duplicate (mai multe comenzi cu același cod), atunci această funcție va adăuga toate volumele și nu va returna primul, așa cum ar face VLOOKUP. Ei bine, viteza acestei metode nu este, de asemenea, foarte bună - de aproximativ 3-4 ori mai lentă decât o căutare VLOOKUP obișnuită.

Articol mare despre funcțiile de numărare selectivă bazate pe una sau mai multe condiții.

Metoda 5: Funcția macro gata făcută de la PLEX

Dacă nu vă este frică să utilizați macrocomenzi, atunci puteți utiliza o funcție gata creată, definită de utilizator în Visual Basic, care este inclusă în Microsoft Excel. În comparație cu o CĂUTARE V obișnuită, poate:

  • caută în mai multe coloane simultan (până la 3)
  • produce rezultate din orice coloană (la stânga sau la dreapta - nu contează)
  • scoateți nu numai prima valoare întâlnită, ci și cea cerută în ordine
  • puteți specifica ce să afișați dacă nu se găsește nimic în loc de eroarea #N/A


Această metodă are două dezavantaje: trebuie să salvați un fișier cu suport macro (XLSM) și viteza oricărei funcții macro nu este foarte mare - pe tabele mari poate încetini semnificativ.

Dar, ca una dintre opțiuni, va funcționa :)

Linkuri conexe

Metodă grozavă. Mulțumesc.
Învățați: Cum puteți utiliza căutarea prin index și poziție pentru a selecta valori din prima coloană atunci când căutați în a doua coloană, dar, în același timp, trebuie să găsiți valori în a doua coloană cu o dată maximă în Al patrulea?
Îmi vine în minte cea mai mare funcție, dar unde să o inserez....))) iată un tabel

traseueidprefixplată
vest2251458 BR7652I16.01.2017
Kashira1252412 BR7652I17.01.2017
S5-1253016 BR7272I18.01.2017
S3-1254392 BR7652I19.01.2017
Kashira1255031 BR7249I20.01.2017
Kashira1257321 BR7759I21.01.2017
SV1-1257569 BR7761I22.01.2017
Pușkino1259373 BR7647I23.01.2017
Kolomna1259591 BR7315I24.01.2017
Kashira1260300 BR7544I25.01.2017
asta caut
prefixeid
BR7652IINDEX(A2:D11,POTRIVIRE(D20,C2:C11,0),Potrivire(E19,A1:D1,0))
dar am nevoie de rezultatul cu data maxima de plata si nu de primul in ordine

Astăzi începem o serie de articole care descriu una dintre cele mai utile funcții ale Excel - CĂUTARE V(CĂUTARE V). Această funcție este, în același timp, una dintre cele mai complexe și mai puțin înțelese.

În acest tutorial pe CĂUTARE V Voi încerca să prezint elementele de bază într-un limbaj cât mai simplu posibil pentru a face procesul de învățare cât mai clar posibil pentru utilizatorii fără experiență. În plus, vom studia câteva exemple cu formule Excel care vor demonstra cele mai frecvente utilizări ale funcției CĂUTARE V.

Funcția VLOOKUP în Excel - descriere generală și sintaxă

Deci ce este CĂUTARE V? Ei bine, în primul rând, este o funcție Excel. Ce face ea? Acesta caută valoarea pe care o specificați și returnează valoarea corespunzătoare dintr-o altă coloană. Tehnic vorbind, CĂUTARE V caută valoarea din prima coloană a unui interval dat și returnează rezultatul dintr-o altă coloană din același rând.

În cea mai comună aplicație, funcția CĂUTARE V caută în baza de date un anumit identificator unic și preia unele informații legate de acesta din baza de date.

Prima literă din numele funcției CĂUTARE V(CĂUTARE V) înseamnă ÎN vertical ( V vertical). După el poți distinge CĂUTARE V din GPR(HLOOKUP), care caută valoarea din linia de sus a intervalului - G orizontal ( H orizontală).

Funcţie CĂUTARE V Disponibil în Excel 2013, Excel 2010, Excel 2007, Excel 2003, Excel XP și Excel 2000.

Sintaxa funcției CĂUTARE V

Funcţie CĂUTARE V(CĂUTARE V) are următoarea sintaxă:

CĂUTARE V(valoare_căutare, matrice_tabelă, număr_index_col,)
CĂUTARE V(valoare_căutare, tabel, număr_coloană, [căutare_interval])

După cum puteți vedea, funcția CĂUTARE Vîn Microsoft Excel are 4 parametri (sau argumente). Primele trei sunt obligatorii, ultima opțională.

  • valoare_căutare(lookup_value) – valoarea de căutat Aceasta poate fi o valoare (număr, dată, text) sau o referință de celulă (conținând valoarea de căutare) sau o valoare returnată de o altă funcție Excel. De exemplu, această formulă va căuta valoarea 40 :

    CĂUTARE V(40,A2:B15,2)
    =CĂUTARE V(40;A2:B15;2)

Dacă valoarea pe care o căutați este mai mică decât cea mai mică valoare din prima coloană a intervalului pe care îl căutați, funcția CĂUTARE V va raporta o eroare #N / A(#N / A).

  • table_array(tabel) – două sau mai multe coloane de date Rețineți, funcție CĂUTARE V caută întotdeauna valoarea din prima coloană a intervalului dat în argument table_array(masa). Intervalul care este vizualizat poate conține diverse date, de exemplu, text, date, numere, valori booleene. Funcția nu ține seama de majuscule, ceea ce înseamnă că caracterele majuscule și mici sunt considerate la fel. Deci, formula noastră va căuta valoarea 40 în celule din A2 inainte de A15 deoarece A este prima coloană a intervalului A2:B15 dat în argument table_array(masa):

    CĂUTARE V(40,A2:B15,2)
    =CĂUTARE V(40;A2:B15;2)

Dacă valoarea argumentului col_index_num(număr_coloană) mai puțin 1 , Acea CĂUTARE V va raporta o eroare #VALOARE!(#VALOARE!). Și dacă este mai mare decât numărul de coloane din interval table_array(tabel), funcția va returna o eroare #REF!(#LEGĂTURĂ!).

  • range_lookup(interval_view) – definește ce să cauți:
    • potrivire exactă, argumentul trebuie să fie egal cu FALS(MINCIUNĂ);
    • potrivire aproximativă, argumentul este egal cu ADEVĂRAT(ADEVĂRAT) sau nu este specificat deloc.

    Acest parametru este opțional, dar foarte important. Mai târziu în acest tutorial CĂUTARE V Vă voi arăta câteva exemple care explică cum să compuneți corect formule pentru a găsi potriviri exacte și aproximative.

Exemple cu funcția VLOOKUP

Sper ca functia CĂUTARE V a devenit puțin mai clar pentru tine. Acum să ne uităm la câteva cazuri de utilizare CĂUTARE Vîn formule cu date reale.

Cum să utilizați VLOOKUP pentru a efectua o căutare pe o altă foaie Excel

În practică, formule cu funcție CĂUTARE V rar folosit pentru a căuta date pe aceeași foaie. De cele mai multe ori, veți căuta și recupera valorile corespunzătoare dintr-o altă foaie.

Pentru, folosind CĂUTARE V, efectuați o căutare într-o altă foaie Microsoft Excel, trebuie să în argument table_array(tabel) specificați numele foii cu un semn de exclamare, urmat de un interval de celule. De exemplu, următoarea formulă arată că intervalul A2:B15 este pe foaia numită Foaia 2.

CĂUTARE V(40,Foaie2!A2:B15,2)
=CĂUTAREV(40,Foaie2!A2:B15,2)

Desigur, numele foii nu trebuie introdus manual. Începeți doar să introduceți formula și când vine vorba de argument table_array(tabel), comutați la foaia dorită și selectați intervalul necesar de celule cu mouse-ul.

Formula prezentată în captura de ecran de mai jos caută textul „Produs 1” în coloana A (aceasta este prima coloană a intervalului A2:B9) în foaia de lucru Preturi.

CĂUTARE V ("Produsul 1", Prețuri! 2$A$:$B$9,2,FALSE)
=CĂUTAREV(„Produsul 1”;Prețuri!$A$2:$B$9,2;FALSE)

Vă rugăm să rețineți că atunci când căutați o valoare de text, trebuie să o includeți între ghilimele (""), așa cum se face de obicei în formulele Excel.

Pentru argumentare table_array(tabel) Este recomandabil să folosiți întotdeauna legături absolute (cu semnul $). În acest caz, intervalul de căutare va rămâne neschimbat atunci când copiați formula în alte celule.

Căutați într-un alt registru de lucru folosind VLOOKUP

A functiona CĂUTARE V a lucrat între două registre de lucru Excel, trebuie să indicați numele registrului de lucru între paranteze drepte înainte de numele foii.

De exemplu, mai jos este o formulă care caută valoarea 40 pe o foaie Foaia 2 in carte Numere.xlsx:

CĂUTARE V(40,Foaie2!A2:B15,2)
=CĂUTAREV(40,Foaie2!A2:B15,2)

Iată cel mai simplu mod de a crea o formulă în Excel cu CĂUTARE V care trimite la un alt registru de lucru:

  1. Deschide ambele cărți. Acest lucru nu este necesar, dar facilitează crearea formulei. Nu doriți să introduceți manual numele registrului de lucru, nu-i așa? În plus, acest lucru vă va proteja de greșeli accidentale.
  2. Începeți să tastați funcția CĂUTARE V, iar când vine vorba de argument table_array(tabel), comutați la alt registru de lucru și selectați intervalul de căutare dorit în acesta.

Captura de ecran afișată mai jos arată formula în care căutarea este setată la un interval din registrul de lucru PriceList.xlsx pe o foaie Preturi.

Funcţie CĂUTARE V va funcționa chiar și atunci când închideți registrul de lucru în care căutați, iar calea completă către fișierul registrului de lucru apare în bara de formule, după cum se arată mai jos:

Dacă numele unui registru de lucru sau al unei foi conține spații, atunci acesta trebuie să fie inclus în apostrofe:

CĂUTARE V(40,„Foaie2”!A2:B15,2)
=CĂUTAREV(40,„Foaie2”!A2:B15,2)

Cum să utilizați un interval sau un tabel denumit în formulele CĂUTARE V

Dacă intenționați să utilizați un interval de căutare în mai multe funcții CĂUTARE V, puteți crea un interval denumit și puteți introduce numele acestuia în formulă ca argument table_array(masa).

Pentru a crea un interval denumit, pur și simplu selectați celulele și introduceți un nume potrivit în câmp Nume, în stânga barei de formule.

Acum puteți nota această formulă pentru a afla prețul unui produs Produsul 1:

CĂUTARE V ("Produs 1", Produse, 2)
=CĂUTARE(„Produsul 1”;Produse;2)

Majoritatea numelor de intervale funcționează pentru întregul registru de lucru Excel, deci nu este nevoie să specificați numele foii pentru argument table_array(tabel), chiar dacă formula și intervalul de căutare sunt pe foi diferite ale registrului de lucru. Dacă se află în cărți diferite, atunci înainte de numele intervalului trebuie să indicați numele registrului de lucru, de exemplu, astfel:

CĂUTARE V ("Produsul 1",ListaPreț.xlsx!Produse,2)
=CĂUTAREV(„Produsul 1”;ListaPreț.xlsx!Produse;2)

Acest lucru face ca formula să pară mult mai clară, nu ești de acord? În plus, utilizarea intervalelor denumite este o alternativă bună la referințele absolute, deoarece intervalul denumit nu se modifică atunci când copiați formula în alte celule. Aceasta înseamnă că puteți fi sigur că intervalul de căutare din formulă va rămâne întotdeauna corect.

Dacă convertiți o serie de celule într-un tabel Excel complet folosind comanda Masa fila (Tabel). Introduce(Inserați), apoi când selectați un interval cu mouse-ul, Microsoft Excel va adăuga automat nume de coloane (sau nume de tabel dacă selectați întregul tabel) la formulă.

Formula finală va arăta cam așa:

CĂUTARE V ("Produs 1", Tabel46[:],2)
=CĂUTAREV(„Produsul 1”;Tabel46[:];2)

Sau poate chiar asa:

CĂUTARE V ("Produs 1", Tabel46,2)
=CĂUTARE(„Produsul 1”;Tabel46;2)

Când utilizați intervale denumite, legăturile vor indica aceleași celule, indiferent unde copiați funcția CĂUTARE Vîn registrul de lucru.

Utilizarea metacaracterelor în formulele CĂUTARE V

Ca și în cazul multor alte funcții, CĂUTARE V Puteți utiliza următoarele metacaractere:

  • Semnul întrebării (?) – înlocuiește orice caracter.
  • Asterisc (*) – înlocuiește orice secvență de caractere.

Utilizarea wildcard-urilor în funcții CĂUTARE V 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 unei celule. Să știi asta CĂUTARE V caută în întregul conținut al celulei, ca și cum opțiunea ar fi activată Potriviți întregul conținut al celulei(întreaga celulă) în căutarea standard Excel.
  • Când o celulă conține spații suplimentare la începutul sau la sfârșitul conținutului. Într-o astfel de situație, s-ar putea să-ți strângi creierul pentru o lungă perioadă de timp, încercând să înțelegi de ce formula nu funcționează.

Exemplul 1: Căutați 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 vă amintiți numele lui de familie, dar știți că începe cu „ack”. Această formulă va face treaba perfect:

CĂUTARE V ("ack*", $A$2:$C$11,1,FALSE)
=CUTARE V ("ack*", $A$2:$C$11,1,FALSE)

Acum că sunteți sigur că ați găsit numele corect, puteți utiliza aceeași formulă pentru a afla suma plătită de acel client. Pentru a face acest lucru, trebuie doar să schimbați al treilea argument al funcției CĂUTARE V la numărul coloanei dorite. În cazul nostru, aceasta este coloana C (a treia în interval):

CĂUTARE V ("ack*", $A$2:$C$11,3,FALSE)
=CUTARE V ("ack*", $A$2:$C$11,3,FALSE)

Iată mai multe exemple cu metacaractere:

~ Găsiți un nume care se termină cu „om”:

CĂUTARE V ("*om", $A$2:$C$11,1,FALSE)
=CĂUTARE(„*om”;$A$2:$C$11,1;FALSE)

~ Găsiți un nume care începe cu „anunț” și se termină cu „fiu”:

CĂUTARE V („anunț*fiu”, $A$2:$C$11,1,FALSE)
=CĂUTAREV(„anunț*fiu”;$A$2:$C$11,1;FALSE)

~ Găsiți prenumele în listă, format din 5 caractere:

CĂUTARE V ("?????",$A$2:$C$11,1,FALSE)
=CĂUTAREV("?????",$A$2:$C$11,1,FALSE)

A functiona CĂUTARE V a funcționat corect cu metacaracterele, ar trebui să utilizați întotdeauna FALS(MINCIUNĂ). Dacă intervalul de căutare conține mai mult de o valoare care se potrivește cu criteriile de căutare cu metacaractere, va fi returnată prima valoare găsită.

Exemplul 2: combinați metacaracterele și referințele de celule în formulele CĂUTARE V

Acum să ne uităm la un exemplu puțin mai complex despre cum să căutați folosind funcția CĂUTARE V după valoare într-o celulă. Imaginează-ți 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 o parte (mai multe caractere) a unei chei de licență în celula C1 și doriți să găsiți numele proprietarului.

Acest lucru se poate face folosind această formulă:

CĂUTARE V("*"&C1&"*",$A$2:$B$12,2,FALSE)
=CĂUTAREV("*"&C1&"*";$A$2:$B$12,2;FALSE)

Această formulă caută valoarea din celula C1 într-un interval dat și returnează valoarea corespunzătoare din coloana B. Rețineți că în primul argument, folosim caracterul ampersand (&) înainte și după referința celulei pentru a asocia șirul de text.

După cum puteți vedea în figura de mai jos, funcția CĂUTARE V returnează valoarea „Jeremy Hill” deoarece cheia de licență conține secvența de caractere din celula C1.

Rețineți că argumentul table_array(tabelul) din captura de ecran de mai sus conține numele tabelului (Tabelul 7) în loc să specifice un interval de celule. Aceasta este ceea ce am făcut în exemplul anterior.

Potrivire exactă sau aproximativă în funcția CĂUTARE V

Și, în sfârșit, să aruncăm o privire mai atentă la ultimul argument care este specificat pentru funcție CĂUTARE V – range_lookup(time-lapse_view). După cum am menționat la începutul lecției, acest argument este foarte important. Puteți obține rezultate complet diferite în aceeași formulă cu valoarea ei ADEVĂRAT(ADEVĂRAT) sau FALS(MINCIUNĂ).

Mai întâi, să aflăm ce înseamnă Microsoft Excel prin potrivire exactă și aproximativă.

  • Dacă argumentul range_lookup FALS(FALSE), formula caută o potrivire exactă, de ex. exact aceeași valoare ca cea specificată în argument valoare_căutare(search_value). Dacă în prima coloană a intervalului t able_array(tabel) există două sau mai multe valori care se potrivesc cu argumentul valoare_căutare(search_value), apoi va fi selectat primul. Dacă nu se găsesc potriviri, funcția va raporta o eroare #N / A(#N/A).De exemplu, următoarea formulă va raporta o eroare #N / A(#N/A) dacă nu există nicio valoare în intervalul A2:A15 4 :

    CĂUTARE V(4,A2:B15,2,FALSE)
    =CĂUTAREV(4,A2:B15,2,FALSE)

  • Dacă argumentul range_lookup(timelapse) este egal cu ADEVĂRAT(ADEVĂRAT), formula caută o potrivire aproximativă. Mai exact, mai întâi funcția CĂUTARE V caută o potrivire exactă și, dacă nu este găsită, selectează una aproximativă. O potrivire aproximativă este cea mai mare valoare care nu depășește valoarea specificată în argument valoare_căutare(search_value).

Dacă argumentul range_lookup(timelapse) este egal cu ADEVĂRAT(ADEVĂRAT) sau nespecificate, atunci valorile din prima coloană a intervalului trebuie sortate în ordine crescătoare, adică de la cel mai mic la cel mai mare. Altfel functioneaza CĂUTARE V poate returna un rezultat eronat.

Pentru a înțelege mai bine importanța alegerii ADEVĂRAT(ADEVĂRAT) sau FALS(FALSE), să ne uităm la câteva formule cu funcția CĂUTARE V si uita-te la rezultate.

Exemplul 1: Găsirea unei potriviri exacte utilizând CĂUTARE V

După cum vă amintiți, pentru a găsi o potrivire exactă, al patrulea argument al funcției CĂUTARE V ar trebui să conteze FALS(MINCIUNĂ).

Să ne uităm din nou la tabelul din primul exemplu și să aflăm ce animal se poate mișca cu viteza 50 mile pe oră. Cred că această formulă nu vă va cauza dificultăți:

CĂUTARE V (50, $A$2:$B$15,2,FALSE)
=CĂUTAREV(50,$A$2:$B$15,2,FALSE)

Rețineți că intervalul nostru de căutare (coloana A) conține două valori 50 – în celule A5Și A6. Formula returnează valoarea din celulă B5. De ce? Pentru că atunci când căutați o potrivire exactă, funcția CĂUTARE V folosește prima valoare găsită care se potrivește cu valoarea căutată.

Exemplul 2: Utilizarea VLOOKUP pentru a găsi o potrivire aproximativă

Când utilizați funcția CĂUTARE V pentru a găsi o potrivire aproximativă, adică când argumentează range_lookup(timelapse) este egal cu ADEVĂRAT(ADEVĂRAT) sau lipsește, primul lucru pe care ar trebui să-l faceți este să sortați intervalul după prima coloană în ordine crescătoare.

Acest lucru este foarte important deoarece funcția CĂUTARE V returnează următoarea valoare cea mai mare după cea dată și apoi căutarea se oprește. Dacă neglijezi să sortezi corect, vei ajunge cu rezultate foarte ciudate sau cu un mesaj de eroare. #N / A(#N / A).

Acum puteți utiliza una dintre următoarele formule:

CĂUTARE V (69, $ A$ 2: $ B $ 15,2, TRUE) sau = CĂUTARE V (69, $ A $ 2: $ B $ 15,2)
=CĂUTAREV(69,$A$2:$B$15,2,TRUE) sau =CĂUTAREV(69,$A$2:$B$15,2)

După cum puteți vedea, vreau să aflu care animal are viteza cea mai apropiată 69 mile pe oră. Și acesta este rezultatul pe care mi l-a returnat funcția CĂUTARE V:

După cum puteți vedea, formula a returnat rezultatul Antilope(Antilope), a cărei viteză 61 mile pe oră, deși lista include și ghepard(Ghepard) care aleargă cu viteză 70 mile pe oră, iar 70 este mai aproape de 69 decât 61, nu? De ce se întâmplă asta? Pentru că funcția CĂUTARE V când se caută o potrivire aproximativă, returnează cea mai mare valoare care nu este mai mare decât valoarea căutată.

Sper că aceste exemple ar arunca puțină lumină în lucrul cu această funcție CĂUTARE Vîn Excel și nu o mai privești ca pe o străină. Acum nu va strica să repetăm ​​pe scurt punctele cheie ale materialului pe care l-am studiat pentru a-l consolida mai bine în memorie.

VLOOKUP în Excel - trebuie să vă amintiți acest lucru!

  1. Funcţie CĂUTARE V Excel nu poate privi spre stânga. Acesta caută întotdeauna valoarea în coloana din stânga a intervalului specificat de argument table_array(masa).
  2. În funcțiune CĂUTARE V Toate valorile sunt insensibile la majuscule, ceea ce înseamnă că literele mici și majuscule sunt echivalente.
  3. Dacă valoarea pe care o căutați este mai mică decât valoarea minimă din prima coloană a intervalului pe care îl căutați, funcția CĂUTARE V va raporta o eroare #N / A(#N / A).
  4. Dacă al 3-lea argument col_index_num(număr_coloană) mai puțin 1 , funcție CĂUTARE V va raporta o eroare #VALOARE!(#VALOARE!). Dacă este mai mare decât numărul de coloane din interval table_array(tabel), funcția va raporta o eroare #REF!(#LEGĂTURĂ!).
  5. Utilizați referințe absolute de celule în argument table_array(tabel) astfel încât atunci când copiați formula, intervalul de căutare corect să fie păstrat. Încercați să utilizați intervale sau tabele denumite în Excel ca alternativă.
  6. Când efectuați o căutare de potrivire aproximativă, rețineți că prima coloană din intervalul căutat trebuie sortată în ordine crescătoare.
  7. În cele din urmă, amintiți-vă importanța celui de-al patrulea argument. Utilizați valori ADEVĂRAT(ADEVĂRAT) sau FALS(FALS) gânditor și te vei scuti de multe batai de cap.

În următoarele articole din tutorialul nostru despre funcție CĂUTARE Vîn Excel vom învăța exemple mai avansate, cum ar fi efectuarea diferitelor calcule folosind CĂUTARE V, extragerea valorilor din mai multe coloane și altele. Vă mulțumesc că ați citit acest tutorial și sper să ne revedem săptămâna viitoare!

Bună prieteni. Cât de des trebuie să căutați o potrivire într-o foaie de calcul Excel pentru o valoare? De exemplu, trebuie să găsiți adresa unei persoane într-un director sau prețul unui produs într-o listă de prețuri. Dacă apar astfel de sarcini, această postare este doar pentru tine!

Eu efectuez proceduri similare în fiecare zi și fără funcțiile descrise mai jos mi-ar fi cu adevărat greu. Ia notă și aplică-le în munca ta!

Căutați în funcțiile tabel Excel, CĂUTARE V și CĂUTARE

Rolul acestor funcții în viața unui utilizator obișnuit este greu de supraestimat. Acum puteți găsi cu ușurință o intrare potrivită în tabelul de date și puteți returna valoarea corespunzătoare.

Sintaxa funcției CĂUTARE V este: =CĂUTAREV( valoare_căutare; tabel_căutare; număr_coloană_ieșire; [mapping_type]). Să luăm în considerare argumentele:

  • Valoarea de căutare– valoarea pe care o vom căuta. Acesta este un argument necesar;
  • Căutați în tabel– matricea de celule în care va avea loc căutarea. Coloana cu valorile căutate trebuie să fie prima din această matrice. Acesta este, de asemenea, un argument necesar;
  • Numărul coloanei de afișat– numărul de serie al coloanei (începând de la prima din matrice), din care funcția va afișa date dacă se potrivesc valorile cerute. Argument necesar;
  • Tip de potrivire– selectați „1” (sau „TRUE”) pentru o potrivire slabă, „0” (“FALSE”) pentru o potrivire completă. Argumentul este opțional dacă este omis, se va efectua o căutare potrivire non-strict.

Găsirea unei potriviri exacte folosind VLOOKUP

Să ne uităm la un exemplu despre cum funcționează funcția CĂUTARE VL când tipul de potrivire este FALSE, căutând o potrivire exactă. Matricea B5:E10 indică activele fixe ale unei anumite companii, valoarea lor contabilă, numărul de inventar și locația. Celula B2 conține numele pentru care trebuie să găsiți numărul de inventar în tabel și să îl plasați în celula C2.

Funcția VLOOKUP în Excel

Să scriem formula: =VLOOKUP(B2,B5:E10,3,FALSE) .

Aici primul argument indică faptul că în tabel trebuie să căutați valoarea din celula B2, adică. cuvântul „Fax”. Al doilea argument spune că tabelul de căutat este în intervalul B5:E10 și trebuie să căutați cuvântul „Fax” în prima coloană, adică. în matricea B5:B10. Al treilea argument îi spune programului că rezultatul calculului este conținut în a treia coloană a tabloului, adică. D5:D10. Al patrulea argument este FALS, adică. este necesară o potrivire completă.

Și astfel, funcția va primi șirul „Fax” din celula B2 și îl va căuta în matricea B5:B10 de sus în jos. Odată ce este găsită o potrivire (linia 8), funcția va returna valoarea corespunzătoare din coloana D, adică. Conținutul D8. Este exact ceea ce aveam nevoie, problema este rezolvată.

Dacă valoarea căutată nu este găsită, funcția va returna .

Găsirea unei potriviri imprecise folosind VLOOKUP

Datorită acestei opțiuni din VLOOKUP, putem evita formulele complexe pentru a găsi rezultatul dorit.

Matricea B5:C12 arată ratele dobânzii la împrumuturi în funcție de valoarea împrumutului. În celula B2 indicăm suma împrumutului și dorim să primim rata pentru o astfel de tranzacție în C2. Sarcina este dificilă, deoarece cantitatea poate fi orice și este puțin probabil să coincidă cu cele specificate în matrice, căutarea unei potriviri exacte nu este potrivită:

Apoi scriem formula pentru o căutare nestrictă: =CĂUTARE V(B2;B5:C12;2;ADEVĂRAT). Acum, din toate datele prezentate în coloana B, programul îl va căuta pe cel mai apropiat cel mai mic. Adică pentru suma de 8.000 se va selecta valoarea de 5.000 și se va afișa procentul corespunzător.


Căutare non-strict VLOOKUP în Excel

Pentru ca funcția să funcționeze corect, trebuie să sortați prima coloană a tabelului în ordine crescătoare. În caz contrar, poate da rezultate eronate.

Funcția GLOOKUP are aceeași sintaxă ca VLOOKUP, dar caută rezultatul în rânduri și nu în coloane. Adică scanează tabelele nu de sus în jos, ci de la stânga la dreapta și afișează numărul de rând specificat, nu coloana.

Căutarea datelor utilizând funcția VIEW

Funcția LOOKUP funcționează similar cu VLOOKUP, dar are o sintaxă diferită. Îl folosesc atunci când tabelul de date conține câteva zeci de coloane și pentru a folosi VLOOKUP trebuie să calculați suplimentar numărul coloanei de ieșire. În astfel de cazuri, funcția VIEW facilitează sarcina. Și astfel, sintaxa: =VEDERE( valoare_căutare; Array_to_search; Array_to_display) :

  • Valoarea de căutare– date sau un link către datele de căutat;
  • Matrice pentru a căuta– un rând sau coloană în care căutăm o valoare similară. Trebuie să sortăm această matrice în ordine crescătoare;
  • Matrice de afișat– un interval care conține date pentru afișarea rezultatelor. Desigur, trebuie să aibă aceeași dimensiune ca și matricea de căutare.

Când scrieți în acest fel, dați o referință non-relativă la tabloul de rezultate. Și arăți direct spre el, adică. nu este nevoie să calculați mai întâi numărul coloanei de ieșire. Folosim funcția VIEW în primul exemplu pentru funcția VLOOKUP (micuri fixe, numere de inventar): =VIZUALIZARE(B2;B5:B10;D5:D10). Problema a fost rezolvată cu succes!


Funcția VIEW în Microsoft Excel

Căutare după coordonatele relative. Funcțiile MATCH și INDEX

O altă modalitate de a căuta date este să combinați funcțiile MATCH și INDEX.

Prima dintre ele este folosită pentru a căuta o valoare într-o matrice și a obține numărul de serie al acesteia: SEARCH( valoare_căutare; matrice_vizualizate; [Tip de potrivire] ). Argumente ale funcției:

  • Valoarea de căutare– argument necesar
  • Matrice de vizualizat– un rând sau coloană în care căutăm o potrivire. Argument necesar
  • Tip de potrivire– specificați „0” pentru a căuta o potrivire exactă, „1” pentru cea mai apropiată potrivire mai mică, „-1” pentru cea mai apropiată potrivire mai mare. Deoarece funcția caută de la începutul listei până la sfârșit, atunci când îl căutați pe cel mai apropiat cel mai mic, sortați coloana de căutare în ordine descrescătoare. Și când căutați mai multe, sortați-o în ordine crescătoare.

Poziția valorii necesare a fost găsită, acum o puteți afișa pe ecran folosind funcția INDEX( Matrice; Numărul de linie; [Column_number]) :

  • Matrice– argumentul specifică din ce matrice de celule trebuie selectată valoarea
  • Numărul de linie– indicați numărul de serie al liniei (începând cu prima celulă a matricei) pe care doriți să o afișați. Aici puteți scrie valoarea manual sau puteți utiliza rezultatul unui calcul al unei alte funcții. De exemplu, CĂUTARE.
  • Numărul coloanei– argument opțional, specificat dacă tabloul este format din mai multe coloane. Dacă un argument este omis, formula utilizează prima coloană a tabelului.

Acum să combinăm aceste funcții pentru a obține rezultatul:


Funcțiile MATCH și INDEX în Excel

Acestea sunt metodele de căutare și afișare a datelor care există în Excel. Mai mult, le puteți folosi în calcule, le puteți folosi în prezentări, efectuați operații cu ele, le puteți specifica ca argumente pentru alte funcții etc.

Simți cum cunoștințele și abilitățile tale cresc și se consolidează? Atunci nu te opri, continuă să citești! În următoarea postare ne vom uita la: va fi dificil și interesant!

Microsoft Excel este un instrument excelent pentru lucrul cu date. Posibilitățile sunt enorme, dar potențialul a fost, de fapt, puțin studiat, deoarece regulile de scriere a comenzilor provoacă dificultăți chiar și pentru profesioniști. Dar dacă muncești din greu, se dovedește că aici poți face lucruri incredibile. Funcția Excel VLOOKUP este unul dintre instrumentele de prelucrare a datelor și despre asta vom vorbi acum.

Ce este funcția VLOOKUP în Excel - domeniu

La procesarea mai multor tabele, adesea situate în cărți diferite, este necesar să transferați date de la unul la altul, asigurându-vă în același timp că valorile nu își pierd sensul și procesul este automatizat. Mecanismul din Excel ar trebui să funcționeze simplu și rapid.

De exemplu, avem o întreprindere. Ivan Ivanovici lucrează acolo. Pe o foaie este stocată valoarea salariului său, pe cealaltă suma de bani pe care departamentul de contabilitate o reține din salariu pentru amendă. Trebuie să combinați toate valorile într-un singur document. Un alt exemplu, există două tabele: prețuri și depozit. Unul indică costul batistelor, celălalt numărul acestora. Este necesar să se consolideze cantitatea și prețul într-un singur loc.

În cazurile în care există doar doi sau trei angajați ai întreprinderii sau până la o duzină de bunuri, puteți face totul manual. Cu grija cuvenită, o persoană va lucra fără erori. Dar dacă există, de exemplu, o mie de valori de procesat, este necesară automatizarea muncii. În acest scop, Excel are VLOOKUP.

Exemple pentru claritate: în tabelele 1,2 - datele inițiale, tabelul 3 - ce ar trebui să se întâmple.

Tabelul de date inițial 1

Tabelul de date combinat 3

NUMELE COMPLET. Z.P. Amenda
Ivanov 20.000 ₽ 38.000 ₽
Petrov 19.000 ₽ 12.000 ₽
Sidorov 21.000 ₽ 200 ₽

Funcția VLOOKUP în Excel - cum se utilizează

Pentru ca Tabelul 1 să ajungă la forma sa finală, introducem un titlu de coloană în el, de exemplu „Fine”. De fapt, acest lucru nu este necesar, puteți scrie orice text sau îl puteți lăsa necompletat. Funcția va funcționa și făcând clic cu mouse-ul în câmpul în care ar trebui să apară valoarea găsită într-un alt tabel.

Acum trebuie să apelăm funcția. Acest lucru se poate face în diferite moduri:

Este necesar să completați valorile pentru funcția VLOOKUP

Rezultatul este evident - în tabelul 3 (vezi mai sus).

VLOOKUP – instrucțiuni pentru lucrul cu două condiții

În situațiile în care este necesar să se stabilească criteriile de căutare nu după o condiție, ci pe mai multe sau două coloane simultan, funcția CĂUTARE V poate fi folosită și pentru lucru. Pentru a face acest lucru, datele inițiale trebuie modificate.

De exemplu, trebuie să introduceți prețul din tabelul 5 în tabelul 4.

Tabelul cu caracteristicile telefonului 4

Exemplul ales este pe telefoane, dar este clar că datele pot fi absolut orice. După cum puteți vedea din tabele, mărcile de telefoane nu diferă, dar RAM și Camera diferă. Pentru a crea date rezumative, trebuie să selectăm telefoanele după marcă și RAM. Pentru a utiliza funcția CĂUTARE V pentru mai multe condiții, trebuie să combinați coloanele cu condiții.

Adăugați coloana din stânga. De exemplu, o numim „Unificare”. În prima celulă de valori, avem B 2, scriem construcția „= B 2& C 2”. Reproducem folosind mouse-ul. Rezultă ca în tabelul 6.

Tabelul cu caracteristicile telefonului 6

O asociere Nume RAM Preț
ZTE 0.5 ZTE 0,5 1.990 RUR
ZTE 1 ZTE 1 3.099 RUR
DNS1 DNS 1 3 100 ₽
DNS 0.5 DNS 0,5 2.240 RUB
Alcatel 1 Alcatel 1 4.500 ₽
Alcatel 256 Alcatel 256 450 ₽

Procesăm Tabelul 5 exact în același mod. Apoi folosim funcția VLOOKUP pentru a căuta după o condiție. Condiția este datele din coloanele unite. Nu uitați că numărul coloanei de unde provin datele din funcția CĂUTARE VD se va modifica. După aplicarea funcției, se va obține o selecție pe baza a două condiții. Puteți îmbina nu coloanele adiacente, ci coloanele cu marca telefonului și camera foto.

Urmărește tutorialul video despre cum să folosești funcția CĂUTARE V din Excel pentru manechini:

Funcția VLOOKUP din Excel nu este greu de utilizat, iar posibilitățile de prelucrare a datelor sunt enorme.