Ce conține al treilea argument din funcția eg? Condiție de căutare. Când să utilizați VLOOKUP

Funcția VLOOKUP (Căutare verticală) caută într-un tabel de date și, pe baza criteriilor de căutare, returnează valoarea corespunzătoare dintr-o coloană specifică. Foarte des este necesar să folosiți mai multe condiții simultan într-o interogare de căutare. Dar implicit această funcție nu poate face față mai mult de o condiție. Prin urmare, ar trebui să utilizați foarte formulă simplă, care va extinde capacitățile funcției LOOKUP pe mai multe coloane simultan.

Funcționarea funcției VLOOKUP după mai multe criterii

Pentru claritate, să analizăm formula VLOOKUP cu un exemplu de mai multe condiții. Ca exemplu, vom folosi un raport schematic cu privire la veniturile reprezentanților de vânzări pentru trimestrul:

În acest raport, trebuie să găsiți cifra de venit pentru un anumit reprezentant de vânzări la o anumită dată. Avand in vedere conditiile de cautare, cererea noastra trebuie sa contina 2 conditii:

– Data predării încasărilor către casierie.
– Numele reprezentantului de vânzări.

Pentru a rezolva această problemă, vom folosi funcția VLOOKUP în mai multe condiții și vom crea următoarea formulă:

În celula C1, introduceți prima valoare pentru primul criteriu interogare de căutare. De exemplu, data: 22.03.2017.
În celula C2, introduceți numele de familie al reprezentantului de vânzări (de exemplu, Novikov). Această valoare va fi folosită ca al doilea argument al interogării de căutare.
În celula C3 vom primi rezultatul căutării pentru a face acest lucru, introduceți formula acolo:

După introducerea formulei, apăsați combinația de taste rapide CTRL+SHIFT+Enter pentru a confirma, deoarece formula trebuie executată într-o matrice.

Rezultatul unei căutări în tabel folosind două condiții:

A fost găsită suma veniturilor unui anumit reprezentant de vânzări la o anumită dată.

Analiza principiului de funcționare a formulei pentru funcția CĂUTARE V cu mai multe condiții:

Primul argument al funcției =VLOOKUP() este prima condiție pentru căutarea unei valori în tabelul raportului de venituri reprezentanților de vânzări. Al doilea argument contine masă virtuală creat ca urmare a unui calcul masiv functie logica=IF(). Fiecare nume de familie din intervalul de celule B6:B12 este comparat cu valoarea din celula C2. Astfel, o matrice de date condiționată este creată în memorie cu elemente ale valorilor TRUE și FALSE.

Apoi, datorită formulei, în memoria programului fiecare element adevărat este înlocuit cu un set de date de 3 elemente:

element – Data.
element – Numele de familie.
element – Venituri.

Și fiecare element fals din memorie este înlocuit cu un set de 3 elemente de valori de text goale (""). Ca rezultat, este creat în memoria programului masa noua, cu care funcția VLOOKUP va funcționa deja. Ignoră toate seturile de date de elemente goale. Și elementele care nu sunt goale sunt potrivite cu valoarea celulei C1, care a fost folosită ca prim criteriu al interogării de căutare (Data). Pe scurt, tabelul din memorie este verificat de funcția VLOOKUP cu o condiție de căutare. Dacă rezultatul potrivirii este pozitiv, funcția returnează valoarea elementului din a treia coloană (venit) a tabelului condiționat. Acest lucru se întâmplă deoarece al treilea argument specifică numărul coloanei 3 din care sunt luate valorile. Este de remarcat faptul că, pentru vizualizare, întregul tabel este specificat în argumentele funcției (în al doilea argument), dar căutarea în sine trece întotdeauna prin prima coloană din tabelul specificat.

Și din ce coloană să ia valoarea returnată este indicată deja în al treilea argument.

Numărul 0 din ultimul argument al funcției indică faptul că potrivirea trebuie să fie absolut exactă.

Acest articol este dedicat Funcțiile CĂUTARE V. Se va lua în considerare instrucțiuni pas cu pas Funcțiile CĂUTARE V, numită „”. În acest articol vom arunca o privire detaliată asupra descrierii, sintaxei și Exemple de funcție CĂUTARE V din Excel. De asemenea, vom analiza cum să-l folosim și vom analiza principalele greșeli și de ce. Funcția VLOOKUP nu funcționează.

Sintaxa și descrierea funcției VLOOKUP în Excel

Deci, deoarece al doilea titlu al acestui articol este „ Funcția VLOOKUP în Excel pentru manechine„, să începem prin a afla ce este și ce face funcția VLOOKUP? Funcția VLOOKUP în engleză CĂUTARE V, caută valoarea specificată și returnează valoarea corespunzătoare dintr-o altă coloană.

Cum funcționează funcția VLOOKUP? Funcția CĂUTARE V din Excel caută în listele dvs. de date pe baza identificator unicși vă oferă o informație asociată cu acel identificator unic.

Litera „V” în VPR înseamnă „verticală”. Este folosit pentru a diferenția Funcțiile GLOOKUP și GLOOKUP, care caută valoarea în linia de sus matrice („H” înseamnă „orizontal”).

Funcția VLOOKUP este disponibilă în toate versiuni Excel 2016, Excel 2013, Excel 2010, Excel 2007, Excel 2003.

Sintaxă Funcțiile CĂUTARE V arata asa:

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

După cum puteți vedea, Funcția VLOOKUP are 4 parametri sau argumente. Primii trei parametri sunt necesari, ultimul este optional.

valoare_căutare este valoarea de căutare.

Aceasta poate fi fie o valoare (număr, dată sau text), fie referință de celulă(o referință la celula care conține valoarea de căutare) sau valoarea returnată de altcineva Funcția Excel. De exemplu:

Căutați un număr: =VLOOKUP(40; A2:B15; 2) - formula va căuta numărul 40.
Căutare text: =VLOOKUP(„mere”, A2:B15, 2) - formula va căuta textul „mere”. Vă rugăm să rețineți că includeți întotdeauna valorile textuluiîn „ghilimele duble”.
Căutarea unei valori dintr-o altă celulă: =VLOOKUP(C2, A2:B15, 2) - formula va căuta valoarea în celula C2.

un tabel este format din două sau mai multe coloane de date.

Ține minte asta Funcția VLOOKUP caută întotdeauna valoarea dorită în prima coloană a tabelului. Tabelul dvs. poate conține diverse valori, cum ar fi text, data, numere sau valori booleene. Valori insensibil la majuscule, ceea ce înseamnă că majuscule și litere mici sunt considerate identice.

Deci formula noastră =CĂUTAREV(40, A2:B15, 2) va căuta „40” în celulele A2 la A15, deoarece A este prima coloană a tabelului A2:B15.

column_number - numărul coloanei din tabel din care trebuie returnată valoarea din rândul corespunzător.

Coloana din stânga din tabelul specificat este 1, a doua coloană este 2, a treia este 3 etc.

4. interval_view determină dacă căutați o potrivire exactă (FALSE) sau o potrivire aproximativă (adevărat sau omis). Acest ultim parametru este opțional, dar foarte important.

Funcția VLOOKUP în exemplele Excel

Acum să ne uităm la câteva cazuri de utilizare Funcțiile CĂUTARE V pentru date reale.

Funcția VLOOKUP pe diferite foi

În practică formule VLOOKUP rar folosit pentru a căuta date pe o singură foaie. De cele mai multe ori, va trebui să căutați și să extrageți datele relevante dintr-o altă foaie.

La utilizați funcția CĂUTARE V dintr-o altă foaie Excel, trebuie să introduceți numele foii de lucru și semnul exclamăriiîn argumentul tabelului înainte de intervalul de celule, de exemplu, =CĂUTAREV(40;Sheet2!A2:B15;2). Formula indică faptul că intervalul de căutare A2:B15 se află în Sheet2.

Desigur, nu trebuie să introduceți manual numele foii. Doar începeți să tastați formula și, când vine vorba de argumentul tabelului, treceți la foaia de lucru de căutare și selectați intervalul cu mouse-ul.

Formula pe care o vedeți în imaginea de mai jos caută text în celula A2 ("Produsul 3") în coloana A (prima coloană a intervalului de căutare A2:B9) din foaia de lucru "Prețuri":

CĂUTARE V (A2, prețuri! 2 USD: 8 USD, 2 USD, FALSE)

Funcția VLOOKUP în Excel - Funcția VLOOKUP pe diferite foi

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

Dacă intenționați să utilizați același interval de căutare în mai multe formule CĂUTARE V, puteți crea și introduce un nume pentru acesta direct în argumentul de tabel al formulei CĂUTARE V.

Pentru a crea un interval denumit, pur și simplu selectați celulele și introduceți orice nume în caseta Nume din stânga panoului Formulă.

Funcția VLOOKUP în Excel - Denumirea unui interval

Acum puteți scrie următoarea formulă CUTARE V pentru a obține prețul produsului 1:

CĂUTARE V ("Produs 1";Produse;2)

Funcția CĂUTARE V în Excel - Exemplu de funcție CĂUTARE V cu un nume de interval

Majoritatea numelor de intervale din Excel se aplică întregului registru de lucru, astfel încât nu trebuie să specificați un nume de foaie de lucru, chiar dacă intervalul dvs. de căutare se află pe o altă foaie de lucru. Astfel de formule sunt mult mai de înțeles. De asemenea, utilizarea intervalelor denumite poate fi o alternativă bună la celule. Deoarece intervalul numit nu se modifică atunci când formula este copiată în alte celule, puteți fi sigur că intervalul dvs. de căutare va rămâne întotdeauna corect.

Dacă ați convertit o serie de celule la funcționalitate completă Foaie de calcul Excel(Inserare filă --> Tabel), puteți selecta intervalul de căutare folosind mouse-ul și Microsoft Excel va adăuga automat nume de coloane sau nume de tabel la formulă:

Funcția LOOKUP în Excel - Exemplu de funcție CUTARE V cu nume de tabel

Formula completă ar putea arăta cam așa:

CĂUTARE V(„Produsul 1”;Tabel6[[Produs]:[Preț]];2)

sau chiar =VLOOKUP(„Produsul 1”;Tabel6;2).

La fel ca intervalele denumite, numele coloanelor sunt constante, iar referințele la celule nu se vor schimba indiferent de locul în care este copiată formula CĂUTARE V.

Funcția VLOOKUP cu mai multe condiții

Să luăm în considerare exemplu de funcție VLOOKUP cu mai multe condiții. Avem următoarele date de intrare:

Funcția VLOOKUP în Excel - Tabel de date sursă

Să presupunem că trebuie să folosim Funcția VLOOKUP cu mai multe condiții. De exemplu, pentru a căuta prețul unui produs folosind două criterii: numele produsului și tipul acestuia.

Pentru a utiliza Funcția VLOOKUP cu mai multe condiții este necesara introducerea unei coloane suplimentare la inceput care va stoca informatii cu denumirea si tipul produsului.

Deci, în foaia „Prețuri”, introduceți o coloană și în celula A2 introduceți următoarea formulă:

Folosind această formulă, obținem valoarea coloanelor „Produs” și „Tip”. Completați toate celulele.

Acum tabelul de căutare arată astfel:

Funcția VLOOKUP în Excel - Adăugarea unei coloane de ajutor

Acum, în celula C2 de pe foaia „Vânzări”, vom scrie următoarea formulă de CĂUTARE V:

CĂUTARE V(A2Prețuri! $A$1:$D$8,4,FALSE)

Completam celulele rămase și, ca rezultat, obținem prețuri pentru fiecare produs în funcție de tipul:

Funcția CĂUTARE V din Excel - Exemplu de CĂUTARE V cu mai multe conditii

Acum să ne uităm la asta Erori ale funcției CĂUTARE V.

De ce nu funcționează funcția VLOOKUP?

În această parte a articolului vom vedea de ce Funcția VLOOKUP nu funcționează si posibil Erori ale funcției CĂUTARE V.

Tip de eroare	Cauza	Soluţie
	Locația coloanei căutate este incorectă	Coloana tabelului în care se efectuează căutarea TREBUIE să fie cea din stânga. Mutați coloana pe care o căutați în extrema stângă a tabelului. Sau creați o coloană duplicat auxiliară în partea stângă a tabelului.
	Intervalul de masă nu este fix	Dacă prima valoare a fost afișată corect și după extinderea formulei CĂUTARE V, eroarea #N/A este întâlnită în unele celule, atunci intervalul tabelului nu este fix. Utilizați ($) pentru a bloca un interval de tabel, astfel încât formula să folosească același interval atunci când este populată. Sau folosiți
	Nu s-a putut găsi o potrivire exactă (dacă în vizualizare time-lapse căutați valoarea exactă selectată (0)
	ÎN vizualizare time-lapse se efectuează căutarea celei mai apropiate valori (1), iar tabelul după care se efectuează căutarea nu este sortat.	Sortați prima coloană a tabelului în ordine crescătoare a numelor.
		Utilizați funcțiile TYPE sau SPACE.
	Valoarea numărului coloanei depășește numărul de coloane din tabel	Verificați numărul coloanei care conține valoarea returnată.
	În formulă lipsesc ghilimele	Dacă utilizați ca valoarea dorită nu un link de celulă, ci text, atunci acesta trebuie inclus între ghilimele. De exemplu: CĂUTARE V ("Produsul 1"; Prețuri! $A$2:$B$8,2,0)

Sper că chiar și acum pentru manechin, funcția VLOOKUP în Excel va fi clar.

Orice utilizator care lucrează cu funcția LOOKUP realizează după un timp că nu este foarte convenabil să lucreze cu o funcție care caută doar în dreapta coloanei în care se află valoarea căutată. Funcțiile MATCH și INDEX pot rezolva această problemă, dar formula generală va fi mult mai lungă și mai complexă decât o simplă CĂUTARE V.

Supliment personalizat r CĂUTARE funcționează ca VLOOKUP, dar vă permite să efectuați o căutare inversă. Funcția folosește pur și simplu metodele de indexare și potrivire în VBA. Este prevăzut în Add-in Excel pentru ușurință în utilizare.

Descărcați suplimentul

Salvați fișierul pe computer și instalați-l în Excel selectând Fișier -> Opțiuni -> Suplimente. Faceți clic pe butonul Merge..., în fereastra care se deschide, selectați Recenzie. Selectați fișierul salvat și faceți clic pe OK pentru a finaliza instalarea. După instalare, mesajul „Reverse_Lookup” va apărea în lista de suplimente disponibile și funcția rLOOKUP va deveni disponibilă în toate cărțile.

Descrierea funcției

Argumentele pentru funcția rLOOKUP sunt aceleași ca și pentru funcția VLOOKUP, vezi capturile de ecran de mai jos. Pentru a efectua o căutare inversă, pur și simplu introduceți număr negativ la argumentul „Col_Index_Num” (vezi exemplele de mai jos). Formula va căuta spre stânga.

Una dintre diferențele dintre aceste două funcții este că dacă Time-lapse_view omisă în CĂUTARE V, cu siguranță nu va exista niciun rezultat. Cu toate acestea, opusul este adevărat pentru rLOOKUP, implicit această funcție revine valoarea exacta. Din nou, acest lucru se datorează faptului că, de cele mai multe ori, utilizatorii doresc să obțină valoarea din rezultatul căutării. Valorile de eroare returnate de funcția rLOOKUP sunt similare cu cele returnate de VLOOKUP.

Exemplu de funcție

Următorul date Excel afișează adresele birourilor, numele angajaților și funcția.

Pentru a răspunde la această întrebare „Cine lucrează în biroul B43”, putem folosi funcția CĂUTARE V:

CĂUTARE V ("B43"; A1:C6;2; FALSE) returnează "Dilbert".

Cu toate acestea, dacă vrem să știm, „Unde este biroul lui Dilbert, atunci CĂUTARE V nu va funcționa”. Aici este utilă funcția rLOOKUP:

rCĂUTARE(„Dilbert”; A1:B6, -2).

Vă rugăm să rețineți că în argumente numărul coloanei este specificat ca un număr negativ -2, ceea ce vă permite să căutați în coloana din stânga.

În general, funcția funcționează în ambele direcții:

rLOOKUP("B43";A1:C6;2) returnează "Dilbert" și

rCĂUTARE(„Dilbert”; A1:B6; -2) returnează „B43”.

Alte exemple sunt date mai jos:

Nu uitați să instalați mai întâi suplimentul, altfel va fi returnată o eroare #NUME?.

Cum funcționează funcția

Funcția folosește funcții MATCH și INDEX pentru a efectua o căutare inversă.

Consultați codul sursă pentru funcția de mai jos.

Funcția rLOOKUP(Lookup_value, Table_array As Range, Col_index_num As Integer, Optional Range_lookup As Boolean)
"
„Funcție personalizată care permite căutări verticale înainte și inversă
„Funcționează ca VLookup pentru Col_index_num pozitiv și face căutări inverse pentru Col_index_num negativ
„Spre deosebire de VLOOKUP, caută valoarea exactă în mod implicit
"
„Nitin Mehta, www.Engineers-Excel.com
"
"
Dim Source_Col ca interval, Dest_Col_num ca întreg

„Coloana sursă este coloana în care se află Lookup_value. Este coloana din stânga dacă
„Col_index_number este pozitiv. Dacă Col_index_number este negativ, este coloana din dreapta
Setați Source_Col = Table_array.Columns(IIf(Col_index_num > 0, 1, Table_array.Columns.Count))

„Dest_col_num este numărul coloanei în cadrul Gama Table_array de unde trebuie să ridicăm intrarea față de Lookup_value
Dest_Col_num = IIf(Col_index_num > 0, Col_index_num, Table_array.Columns.Count + Col_index_num + 1)

„Utilizați funcțiile MATCH și INDEX pentru căutare.
„Utilizarea CVErr(xlErrRef) sugerată de Brian Canes
rCĂUTARE = IIf(Dest_Col_num< 1, CVErr(xlErrRef), Application.Index(Table_array, Application.Match(Lookup_value, Source_Col, Range_lookup), Dest_Col_num))

Funcția de sfârșit

Lasă comentariul tău!

Salutare 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ță utilizator obișnuit greu de supraestimat. Acum puteți găsi cu ușurință în tabelul de date intrare potrivităși returnează valoarea corespunzătoare.

Sintaxa funcției CĂUTARE V este: =CĂUTAREV( valoare_căutare; tabel_căutare; număr_coloană_ieșire; [tip_mapping]). Să ne uităm la argumente:

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 VLOOKUP 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 se află î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 în Lucru VPR, putem evita formulele complexe pentru a găsi rezultatul dorit.

În matrice sunt indicate B5:C12 ratele dobânzilor pentru î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ă prin faptul că suma 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ĂUTAREV(B2;B5:C12;2;ADEVĂRAT). Acum, din toate datele prezentate în coloana B, programul o va căuta pe cea mai mică. Adică pentru suma de 8.000 se va selecta valoarea de 5.000 și se va afișa procentul corespunzător.

Lax Căutare VLOOKUPîn Excel

Pentru funcţionare corectă Funcția trebuie să sorteze prima coloană a tabelului în ordine crescătoare. În caz contrar, poate da un rezultat eronat.

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ă. Această matrice Asigurați-vă că sortați î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.

Cu o astfel de intrare, nu dai legătură relativă serie 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 pentru 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-l î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ăr_linie; [Column_number]) :

Matrice– argumentul specifică din ce matrice de celule trebuie selectată valoarea
Numărul liniei– indicați numărul de serie al liniei (începând de la 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, puteți efectua 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 vom lua în considerare: va fi dificil și interesant!

Dacă căutați funcții wildcard, Google va arăta că CUTARE V este mult mai populară decât funcția INDEX. Acest lucru este de înțeles, deoarece pentru a oferi funcției INDEX aceeași funcționalitate ca și CUTARE V, trebuie să utilizați o altă formulă - CĂUTARE. Pentru mine, a fost întotdeauna o provocare să încerc să stăpânesc două funcții noi în același timp. Dar oferă mai multe oportunități și flexibilitate în creare foi de calcul. Dar mai întâi lucrurile.

Funcția VLOOKUP().

Să presupunem că aveți un tabel cu datele angajaților. Prima coloană stochează numărul de personal al angajatului, restul - alte date (nume complet, departament etc.). Dacă aveți un număr de personal, puteți utiliza funcția CĂUTARE V pentru a returna anumite informații despre angajat. Sintaxa formulei =CĂUTAREV(valoare_căutare, tabel, număr_coloană, [căutare_interval]). Ea îi spune lui Excel: „Găsiți masă un rând a cărui primă celulă se potrivește search_value,și returnează valoarea celulei cu numărul de serie număr_coloană».

Dar sunt situații când ai numele unui angajat și trebuie să returnezi numărul de personal. În figura din celula A10 este numele angajatului și trebuie să determinați numărul de personal din celula B10.

Când câmpul cheie este în dreapta datelor pe care le doriți, VLOOKUP nu va ajuta. Dacă, desigur, ar fi posibil să întreb număr_coloană-1, atunci nu ar fi probleme. O soluție comună este să adăugați o nouă coloană A, să copiați numele angajaților în această coloană, să completați numerele de personal cu folosind VLOOKUP, salvați-le ca valori și ștergeți coloana temporară A.

Funcția INDEX().

Pentru a ne rezolva problema într-un singur pas, trebuie să folosim formulele INDEX și MATCH. Complexitate această abordare este că trebuie să utilizați două funcții pe care poate nu le-ați mai folosit până acum. Pentru a simplifica înțelegerea, vom rezolva această problemă în două etape.

Să începem cu funcția INDEX. Un nume groaznic. Când cineva spune „index”, în capul meu nu apare o singură asociere despre ceea ce face această funcție. Și necesită trei argumente: =INDEX(matrice, număr_rând, [număr_coloană]).

În termeni simpli, Excel intră într-o matrice de date și returnează valoarea la intersecția unui rând și a unei coloane specificate. Pare simplu. Astfel, formula =INDEX($A$2:$C$6,4,2) va returna valoarea găsită în celula B5.

În raport cu problema noastră, pentru a returna numărul de personal al angajatului, formula ar trebui să arate astfel =INDEX($A$2:$A$6;?;1). Pare o prostie, dar dacă înlocuim semnul întrebării cu formula MATCH, avem o soluție.

Funcția MATCH().

Sintaxa pentru această funcție este: =MATCH(valoare_căutare, matrice_căutare, [tip_potrivire]).

Ea îi spune lui Excel: „Găsiți valoare_căutareîn matricea de date și returnează numărul de rând al matricei în care apare această valoare." Astfel, pentru a afla ce linie se află numele angajatului în celula A10, trebuie să scrieți formula =MATCH(A10, $B$2:$B$6, 0). Dacă numele „Colin Farel” este în celula A10, atunci MATCH va returna al 5-lea rând al matricei B2:B6.

Ei bine, practic, asta este. Funcția MATCH îi spune funcției INDEX ce rând să caute o valoare. Înlocuiți semnul întrebării din formula INDEX cu formula CĂUTARE și obțineți echivalentul unei CĂUTARE V cu capacitatea de a căuta date situate în stânga coloanei cheie. Se va dovedi astfel:

INDEX($A$2:$B$6,MATCH(A10,$B$2:$B$6,0),1)

La început a fost neobișnuit, a existat o tentație uriașă de a introduce o altă coloană și de a lucra ca întotdeauna. Dar cu timpul, folosirea funcției INDEX a devenit un obicei. S-a dovedit a fi mai rapid și necesită mai puțină manipulare. Deci data viitoare când doriți să setați un număr de coloană la un număr negativ într-o CĂUTARE V, utilizați o combinație de două funcții ciudate, INDEX și MATCH, pentru a vă rezolva problema.