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 verificați 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:

Î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 VL 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 LOOKUP.

Găsirea valorilor într-o listă pe orizontală folosind potrivirea aproximativă

Pentru a efectua această sarcină, este utilizată funcția GLOOKUP.

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

În exemplul de mai sus, funcția GLOOKUP caută valoarea 11000 în rândul 3 în intervalul specificat. Nu găsește 11000 și, prin urmare, caută următoarea valoare cea mai mare care este mai mică de 1100 și returnează numărul 10543.

Pentru mai multe informații, consultați funcțiile LOOKUP.

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

Notă: Suplimentul Lookup Wizard a fost întrerupt în Excel 2010. Această funcționalitate a fost înlocuită de Function Wizard și de caracteristicile disponibile de căutare și referință (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ă.

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ă 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 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 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 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 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.

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 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ă. 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 (active 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ă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 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!

Data: 3 ianuarie 2016 Categorie:

Căutarea caracterelor dintr-un șir este cea mai importantă sarcină care vă permite să automatizați lucrul cu text. De exemplu, ușurează atunci când poziția liniei țintă este necunoscută.

Am atins instrumentul de căutare Excel în , dar aici ne vom uita la funcțiile care caută text într-un șir și returnează poziția sa de pornire.

Există două funcții pentru căutarea textului în Microsoft Excel:

1. GĂSI() . Această funcție are 2 argumente obligatorii și unul opțional:
   • Căutați text– succesiunea de caractere care trebuie găsită. Acesta este un argument necesar
   • Text original– șirul în care se efectuează căutarea. Argument necesar
   • Poziția de pornire– numărul de serie al personajului din textul sursă de la care începe căutarea. Argument opțional. Dacă nu este specificat, căutarea începe de la primul caracter

Funcția FIND ia în considerareȘi nu acceptă utilizarea wildcard-urilor(citiți mai departe despre ele).

Funcția FIND în Excel

1. CĂUTARE(Căutare text; Text original; [Poziția de pornire]) are sintaxă și argumente similare. Diferă prin aceea insensibil la majusculeȘi acceptă utilizarea metacaracterilor.

Funcția CĂUTARE în Excel

După cum puteți vedea, această funcție a dat un rezultat diferit, deoarece căutarea nu a fost sensibilă la majuscule și minuscule.

Ce sunt metacaracterele, te întrebi? Acestea sunt caractere speciale care vă permit să săriți peste o parte necunoscută a textului pentru căutare:

• ? – orice simbol
• * – orice succesiune de caractere

Dacă semnul de întrebare sau asteriscul face parte din șirul de căutare și nu un caracter joker, precedați-l cu un tilde (~).

Să notăm următoarea formulă: =SEARCH(“??chn”,A1,1). Funcția va căuta un șir format din oricare două caractere urmate de literele „chn”. Căutarea se potrivește cu o parte a cuvântului „Tabular” din textul exemplu, de exemplu. combinație de caractere „personal”. Rezultatul funcției va fi valoarea „4”.

Wildcards în Excel

Acum să scriem formula =SEARCH(“p*s”,A2,1) . Funcția caută combinația „p” + orice număr de orice caractere + „s”. În exemplu, aceasta face parte din cuvântul „procesor”. Rezultatul funcției este 11.

Wildcard asterisc în Excel

Având funcțiile de căutare, vă este mai ușor să urmăriți poziția textului dorit, astfel încât este mai ușor să îl înlocuiți. Asigurați-vă că exersați folosirea lor. Post-tutorialul meu despre funcțiile text vă va ajuta în acest sens.

Și următorul articol va fi dedicat. Un subiect important care trebuie studiat cu siguranță!

Pune întrebări în comentarii și abonează-te la actualizări pentru a nu pierde nimic!

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 cu numele Produs 4. Dar, deoarece nu cunoaștem acest număr dinainte, folosim funcția COLUMN pentru a crea o matrice de numere de coloane 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 rezultat al 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. Și 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 este inclusă î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 din intervalul în care sunt stocate numele lunilor va fi în orice caz 1. Atunci nu trebuie decât 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!

Printre mii de rânduri și zeci de coloane de date, este aproape imposibil să găsești manual ceva într-un tabel Excel. Singura opțiune este să folosiți un fel de funcție de căutare, iar în continuare ne vom uita la modul de căutare într-un tabel Excel.

Pentru a căuta date într-un tabel Excel, trebuie să utilizați elementul de meniu „Găsiți și selectați” pe filă "Acasă", în care trebuie să selectați o opțiune "Găsi" sau utilizați combinația de taste pentru a apela „Ctrl + F”.

De exemplu, să încercăm să găsim numărul necesar printre datele din tabelul nostru, deoarece atunci când căutăm numere trebuie să luăm în considerare unele subtilități ale căutării. Să căutăm un număr în tabelul Excel "10".

După selectarea elementului de meniu necesar, introduceți valoarea dorită în caseta de căutare care apare. Avem două opțiuni pentru căutarea valorilor într-un tabel Excel, aceasta este pentru a găsi toate potrivirile simultan făcând clic pe butonul "Găsiți toate" sau vizualizați imediat fiecare celulă găsită apăsând butonul de fiecare dată "Găsește următorul". Când utilizați butonul "Găsește următorul" ar trebui să țineți cont și de locația curentă a celulei active, deoarece căutarea va începe din această poziție.

Să încercăm să găsim toate valorile simultan și tot ce a fost găsit va fi listat în fereastra sub setările de căutare. Dacă lăsăm toate setările implicite, rezultatul căutării nu va fi exact ceea ce ne așteptam.

Pentru a căuta corect date într-un tabel Excel, faceți clic pe butonul "Opțiuni"și configurați zona de căutare. Acum valoarea dorită este căutată chiar și în formulele utilizate în celulele pentru calcule. Trebuie să specificăm căutarea numai în valori și, dacă se dorește, putem specifica și formatul datelor căutate.

Atunci când căutați cuvinte într-un tabel Excel, ar trebui să țineți cont și de toate aceste subtilități și, de exemplu, puteți lua în considerare chiar și cazul literelor.

Și, în sfârșit, să ne uităm la cum să căutați date în Excel numai în zona necesară a foii. După cum se poate vedea din exemplul nostru, valoarea dorită "10" apare în toate coloanele de date simultan. Dacă trebuie să găsiți această valoare, de exemplu, doar în prima coloană, trebuie să selectați această coloană sau orice zonă de valori în care trebuie să căutați, apoi să începeți căutarea.