Excel caută o valoare după condiție. Căutați în Microsoft Excel. Crearea coloanelor suplimentare

Dacă căutați funcții wildcard, Google va arăta că CĂUTARE 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 vă oferă mai multă putere și flexibilitate în crearea foilor 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 masa 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 acea coloană, să completați numerele de personal folosind VLOOKUP, să le salvați ca valori și să ștergeți coloana temporară A.

Funcția INDEX().

Pentru a ne rezolva problema într-un singur pas, trebuie să folosim formulele INDEX și MATCH. Provocarea acestei abordări este că vă cere să aplicaț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 posibilitatea 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.

Să presupunem că trebuie să găsiți extensia de telefon a unui angajat folosind numărul său și, de asemenea, să estimați corect raportul comisionului pentru suma vânzărilor. Căutați date pentru a găsi rapid și eficient date specifice într-o listă și pentru a verifica automat dacă datele sunt utilizate corect. După ce vizualizați datele, puteți efectua calcule și afișa rezultatele, indicând valorile returnate. Există mai multe moduri de a căuta valori într-o listă de date și de a afișa rezultatele.

În acest articol

Găsiți valori într-o listă pe verticală după potrivirea exactă

Puteți utiliza funcția CĂUTARE V sau o combinație a funcțiilor INDEX și MATCH pentru a efectua această sarcină.

Exemple de utilizare a funcției CĂUTARE V

Funcția VLOOKUP.

Exemple de INDEX-uri și MATCHES

Ce înseamnă:

=INDEX(trebuie să returnați valoarea din C2:C10, care se va potrivi cu MATCH (prima valoare „Varză” din tabloul B2:B10))

Formula caută prima valoare în celula C2:C10 corespunzătoare varză(în B7) și returnează valoarea în C7 ( 100 ) - prima valoare corespunzătoare varză.

Pentru mai multe informații, consultați funcția INDEX și funcția MATCH.

Găsiți valori într-o listă pe verticală, după potrivire aproximativă

Pentru a face acest lucru, utilizați funcția VLOOKUP.

Important: Asigurați-vă că valorile din primul rând sunt sortate în ordine crescătoare.

În exemplul de mai sus, funcția VLOOKUP caută numele unui elev care are 6 întârzieri în intervalul A2:B7. Nu există nicio intrare în tabel pentru 6 întârzieri, astfel încât funcția CĂUTARE VOR caută următoarea potrivire cea mai mare sub 6 și găsește valoarea 5 asociată prenumelui Dave, și, prin urmare, se întoarce Dave.

Pentru mai multe informații, consultați funcția VLOOKUP.

Găsirea valorilor verticale într-o listă de dimensiuni necunoscute cu potrivire exactă

Pentru a efectua această sarcină, utilizați funcțiile OFFSET și MATCH.

Notă: Această abordare este utilizată dacă datele se află într-un interval de date extern pe care îl actualizați în fiecare zi. Știți că coloana B are Preț, dar nu știți câte rânduri de date returnează serverul, iar prima coloană nu este sortată alfabetic.

C1 este celula din stânga sus a intervalului (numită și celula de pornire).

Potrivire(„portocale”; C2: C7; 0) caută culoarea portocalie în intervalul C2:C7. Nu ar trebui să includeți celula de pornire în interval.

1 - numărul de coloane din dreapta celulei de pornire pentru care ar trebui returnată valoarea returnată. În exemplul nostru, valoarea returnată este în coloana D, Vânzări.

Găsiți valori într-o listă orizontal după potrivire exactă

Pentru a efectua această sarcină, este utilizată funcția GLOOKUP. Mai jos este un exemplu.

Funcția LOOKUP caută o coloană Vânzăriși returnează valoarea de la rândul 5 în intervalul specificat.

Pentru mai multe informații, consultați Funcțiile GLOOKUP.

Creați o formulă de căutare folosind Expertul de căutare (numai pentru Excel 2007)

Notă: Complementul Lookup Wizard a fost întrerupt în Excel 2010. Această funcționalitate a fost înlocuită de Function Wizard și funcțiile de căutare și referință disponibile (referință).

În Excel 2007, Expertul de căutare creează o formulă de căutare bazată pe datele din foaia de lucru care au titluri de rând și coloană. Asistentul de căutare vă ajută să găsiți alte valori într-un rând atunci când cunoașteți valoarea dintr-o coloană și invers. Expertul de căutare folosește index și MATCH în formulele pe care le creează.

Avem un tabel care înregistrează volumele de vânzări ale anumitor produse în luni diferite. Trebuie să găsiți date în tabel, iar criteriile de căutare vor fi titlurile de rând și de coloană. Dar căutarea trebuie făcută separat după intervalul de rând sau coloană. Adică va fi folosit doar unul dintre criterii. Prin urmare, funcția INDEX nu poate fi folosită aici, dar este nevoie de o formulă specială.

Găsirea valorilor într-un tabel Excel

Pentru a rezolva această problemă, ilustrăm un exemplu pe un tabel schematic care corespunde condițiilor descrise mai sus.

Foaie cu un tabel pentru căutarea valorilor pe verticală și pe orizontală:

Deasupra tabelului în sine există o linie cu rezultatele. În celula B1 introducem criteriul pentru interogarea de căutare, adică titlul coloanei sau numele rândului. Și în celula D1, formula de căutare ar trebui să returneze rezultatul calculării valorii corespunzătoare. După aceea, a doua formulă va funcționa în celula F1, care va folosi deja valorile celulelor B1 și D1 ca criterii pentru căutarea lunii corespunzătoare.

Găsirea unei valori într-un șir Excel

Acum aflăm care este volumul maxim și în ce lună a fost vânzarea maximă a Produsului 4.

Pentru a căuta după coloane:

S-a găsit în ce lună și care a fost cea mai mare vânzare a produsului 4 în două trimestre.

Principiul formulei pentru găsirea unei valori într-un rând Excel:

Primul argument al funcției VLOOKUP (Vizualizare verticală) specifică o legătură către celula în care se află criteriul de căutare. Al doilea argument specifică intervalul de celule de vizualizat în timpul căutării. Al treilea argument al funcției CĂUTARE VL trebuie să indice numărul coloanei din care ar trebui luată valoarea față de rândul numit Produs 4. Dar, deoarece nu cunoaștem acest număr dinainte, folosim funcția COLUMN pentru a crea o matrice de coloane. numere pentru intervalul B4:G15.

Acest lucru permite funcției VLOOKUP să colecteze o întreagă matrice de valori. Ca urmare, toate valorile corespunzătoare pentru fiecare coloană din rândul Produs 4 sunt stocate în memorie (și anume: 360; 958; 201; 605; 462; 832). După aceea, funcția MAX poate lua numai numărul maxim din această matrice și îl poate returna ca valoare pentru celula D1, ca urmare a calculării formulei.

După cum puteți vedea, designul formulei este simplu și concis. Pe baza acestuia, puteți găsi și alți indicatori pentru un anumit produs într-un mod similar. De exemplu, valoarea minimă sau medie a volumului vânzărilor utilizând funcțiile MIN sau MEDIE. Nimic nu te împiedică să aplici această formulă schelet folosind funcții mai complexe pentru a implementa cea mai confortabilă analiză a raportului de vânzări.

Cum să obțineți antetele de coloană bazate pe începutul unei celule?

De exemplu, cât de eficient am afișat luna în care a existat o vânzare maximă folosind a doua formulă. Nu este greu de observat că în a doua formulă am folosit scheletul primei formule fără funcția MAX. Structura principală a formulei este: VLOOKUP(B1,A5:G14,COLUMN(B5:G14),0). Am înlocuit funcția MAX cu MATCH, care folosește ca prim argument valoarea obținută prin formula anterioară. Acum acționează ca un criteriu pentru căutarea lunii. Iar ca urmare, funcția SEARCH ne returnează numărul coloanei 2, unde se află valoarea maximă a volumului vânzărilor pentru produsul 4 După care se include în lucrare funcția INDEX, care returnează valoarea după dată și numărul coloanei din intervalul specificat în argumentele sale. Deoarece avem un număr de coloană de 2, iar numărul rândului în intervalul în care sunt stocate numele lunilor va fi în orice caz 1, atunci trebuie doar să folosim funcția INDEX pentru a obține valoarea corespunzătoare din intervalul B4: G4 - februarie (a doua lună).

Găsirea unei valori într-o coloană Excel

A doua versiune a sarcinii ar fi să căutați în tabel folosind numele lunii ca criteriu. În astfel de cazuri, trebuie să schimbăm scheletul formulei noastre: înlocuiți funcția VLOOKUP cu HLOOKUP și înlocuiți funcția COLUMN cu ROW.

Acest lucru ne va permite să aflăm ce volum și ce produs au fost vânzările maxime într-o anumită lună.

Pentru a afla ce produs a avut volumul maxim de vânzări într-o anumită lună, ar trebui să:

Principiul formulei pentru găsirea unei valori într-o coloană Excel:

În primul argument al funcției HLOO (Horizontal View), indicăm un link către celula cu criteriul de căutare. Al doilea argument specifică o referință la intervalul de tabel care este vizualizat. Al treilea argument este generat de funcția ROW, care creează o matrice de 10 elemente de numere de linii în memorie. Deoarece avem 10 rânduri în partea tabulară.

Apoi, funcția GLOOKUP, folosind pe rând fiecare număr de linie, creează o serie de valori de vânzări corespunzătoare din tabel pentru o anumită lună (iunie). În continuare, funcția MAX trebuie doar să selecteze valoarea maximă din această matrice.

ATENŢIE! Când utilizați formula schelet pentru alte probleme, acordați întotdeauna atenție celui de-al doilea și al treilea argument al funcției de căutare GLOOKUP. Numărul de rânduri acoperite în intervalul specificat în argument trebuie să se potrivească cu numărul de rânduri din tabel. Și, de asemenea, numerotarea ar trebui să înceapă din a doua linie!

Unul dintre cei mai populari operatori printre utilizatorii Excel este funcția CĂUTARE. Sarcinile sale includ determinarea numărului de poziție al unui element dintr-o matrice de date dată. Aduce cel mai mare beneficiu atunci când este utilizat în combinație cu alți operatori. Să ne dăm seama care este funcția CĂUTARE, și cum poate fi folosit în practică.

Operator CĂUTARE aparține categoriei de funcții „Legături și matrice”. Acesta caută un element dat în matricea specificată și afișează numărul poziției sale în acest interval într-o celulă separată. De fapt, chiar și numele său indică acest lucru. De asemenea, această funcție, atunci când este utilizată în combinație cu alți operatori, îi informează asupra numărului de poziție al unui anumit element pentru prelucrarea ulterioară a acestor date.

Sintaxa operatorului CĂUTARE arata asa:

MATCH(valoare_căutare, matrice_căutare, [tip_potrivire])

Acum să ne uităm la fiecare dintre aceste trei argumente separat.

„Valoare de căutare”- acesta este elementul care ar trebui găsit. Poate avea un text, o formă numerică sau poate lua o valoare logică. Acest argument poate fi, de asemenea, o referință la o celulă care conține oricare dintre valorile de mai sus.

„Matrice de navigare”– aceasta este adresa domeniului în care se află valoarea dorită. Operatorul trebuie să o determine poziția acestui element în această matrice CĂUTARE.

„Tip de potrivire” indică o potrivire exactă de căutat sau una inexactă. Acest argument poate avea trei semnificații: "1", «0» Și „-1”. Când valoare «0» operatorul caută doar o potrivire exactă. Dacă este specificată o valoare "1", apoi în lipsa unei potriviri exacte CĂUTARE returnează elementul cel mai apropiat de acesta în ordine descrescătoare. Dacă este specificată o valoare „-1”, atunci dacă nu se găsește o potrivire exactă, funcția returnează elementul cel mai apropiat de acesta, în ordine crescătoare. Este important, dacă nu căutați o valoare exactă, ci una aproximativă, ca matricea vizualizată să fie ordonată în ordine crescătoare (tip de potrivire "1") sau descendent (tip de potrivire „-1”).

Argument „Tip de potrivire” este opțională. Poate fi omis dacă nu este necesar. În acest caz, valoarea sa implicită este "1". Aplica argumentul „Tip de potrivire”, în primul rând, are sens doar la procesarea valorilor numerice, nu a celor text.

Dacă CĂUTARE cu setările date nu poate găsi elementul necesar, operatorul afișează o eroare în celulă "#N / A".

Atunci când efectuează o căutare, operatorul nu face distincție între cazurile de caractere. Dacă există mai multe potriviri exacte în matrice, atunci CĂUTARE afișează poziția primului dintre ei în celulă.

Metoda 1: Afișarea locului unui element într-un interval de date text

Să ne uităm la cel mai simplu caz ca exemplu, când folosim CĂUTARE puteți determina locația elementului specificat într-o matrice de date text. Să aflăm ce poziție în gama în care se află denumirile produselor ocupă cuvântul "Zahăr".

Metoda 2: Automatizarea utilizării operatorului MATCH

Mai sus ne-am uitat la cel mai primitiv caz de utilizare a operatorului CĂUTARE, dar chiar și asta poate fi automatizat.

Metoda 3: Utilizarea operatorului MATCH pentru expresii numerice

Acum haideți să vedem cum puteți utiliza CĂUTARE pentru lucrul cu expresii numerice.

Sarcina este de a găsi un produs cu o valoare de vânzare de 400 de ruble sau cel mai apropiat în ordine crescătoare de această sumă.

Într-un mod similar, puteți căuta cea mai apropiată poziție de "400" Descendentă. Doar pentru aceasta trebuie să filtrați datele în ordine crescătoare și în câmp „Tip de potrivire” argumentele funcției setează valoare "1".

Metoda 4: Utilizați în combinație cu alți operatori

Cel mai eficient este să utilizați această funcție cu alți operatori ca parte a unei formule complexe. Cel mai adesea este folosit împreună cu funcția INDEX. Acest argument afișează conținutul intervalului specificat de numărul rândului sau al coloanei din celula specificată. Mai mult, numerotarea, ca și în raport cu operatorul CĂUTARE, nu se realizează relativ la întreaga foaie, ci doar în intervalul. Sintaxa pentru această funcție este următoarea:

INDEX(matrice, row_number, column_number)

În plus, dacă matricea este unidimensională, atunci poate fi folosit doar unul dintre cele două argumente: "Numărul de linie" sau „Numărul coloanei”.

Caracteristica unui grup de funcții INDEXȘi CĂUTARE este că acesta din urmă poate fi folosit ca argument pentru primul, adică indică poziția unui rând sau a unei coloane.

Să aruncăm o privire la cum se poate face acest lucru în practică folosind același tabel. Sarcina noastră este să afișăm într-un câmp suplimentar al foii "Produs" numele produsului, suma totală a veniturilor din care este egală cu 350 de ruble sau cea mai apropiată valoare de această valoare în ordine descrescătoare. Acest argument este specificat în câmp „Suma aproximativă a veniturilor pe foaie”.

După cum vedem, operatorul CĂUTARE este o funcție foarte convenabilă pentru determinarea numărului de serie al unui element specificat dintr-o matrice de date. Dar beneficiile sale cresc semnificativ dacă este folosit în formule complexe.