Cum să găsiți date într-un tabel excel. Funcții de indexare și căutare în excel - cea mai bună alternativă pentru vpr

Este cu siguranță mai ușor să căutați într-un singur tabel, deși mare, întreg sau în intervale adiacente de celule, decât prin mai multe tabele împărțite în părți împrăștiate în diferite intervale neadiacente sau chiar în foi separate. Chiar dacă o faci căutare automată simultan pe mai multe tabele, pot apărea obstacole semnificative. Dar organizarea tuturor datelor într-un singur tabel este dificilă, uneori aproape imposibilă. Pe exemplu concret Vom demonstra soluția corectă pentru căutarea simultană în mai multe tabele în Excel.

Căutare simultană în mai multe intervale

Pentru un exemplu vizual, să creăm trei tabele separate simple situate în intervale neadiacente ale unei singure foi:

Ar trebui să căutați cantitatea necesară pentru a produce 20 de bucăți de produse. Din păcate, aceste date sunt în diferite coloane și rânduri. Prin urmare, în primul rând, trebuie să verificați cât timp va dura producerea acestor produse (primul tabel).

Pe baza datelor obținute, trebuie să treceți imediat la o căutare într-un alt tabel și să aflați câți muncitori ar trebui să fie implicați într-un anumit volum de producție. Rezultatul obținut trebuie comparat cu datele din cel de-al treilea tabel. Astfel, într-o singură operațiune de căutare în trei tabele vom determina imediat costurile (suma) necesare.

Utilizatorul mediu de Excel ar căuta o soluție folosind funcții bazate pe formule, cum ar fi CĂUTARE V. Și ar efectua o căutare în 3 etape (separat pentru fiecare tabel). Se pare că puteți obține imediat un rezultat gata făcut prin efectuarea unei căutări într-o singură etapă folosind o formulă specială. Pentru a face acest lucru:

1. În celula E6, introduceți valoarea 20, care este condiția pentru interogare de căutare.
2. În celula E7, introduceți următoarea formulă:

Cost de producție pentru 20 buc. un anumit produs.

Cum funcționează formula cu VLOOKUP în mai multe tabele:

Principiul de funcționare al acestei formule se bazează pe căutarea secvențială a tuturor argumentelor pentru funcția principală CĂUTARE V (prima). Mai întâi, a treia funcție CĂUTARE VL caută în primul tabel cantitatea de timp necesară pentru a produce 20 de bucăți din produs specificată ca valoare pentru celula E6 (care poate fi apoi modificată dacă este necesar). Apoi, a doua funcție CĂUTARE VĂ caută valoarea pentru primul argument al funcției principale.

Ca rezultat al căutării celei de-a treia funcție, obținem valoarea 125, care este primul argument pentru a doua funcție. După ce a primit toți parametrii, a doua funcție caută în al doilea tabel numărul de muncitori necesari pentru producție. Ca rezultat, este returnată valoarea 5, care va fi apoi utilizată de funcția principală. Pe baza tuturor datelor primite, formula returnează rezultatul final al calculului. Și anume, suma de 1.750 USD necesară pentru a produce 20 de bucăți dintr-un anumit produs.

Folosind acest principiu, puteți folosi formule pentru funcția CĂUTARE V din mai multe foi.

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

Nota: 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 trebuie 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.

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

Nota: Complementul Lookup Wizard a fost întrerupt în Excel 2010. Acestea funcţionalitateînlocuit cu un asistent de funcții și cu funcții de căutare și referință (referință) disponibile.

Î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 în ce volum maxim și în ce lună a existat o vânzare maximă a Produsului 4.

Pentru a căuta după coloane:

Am găsit în ce lună și ce tip a fost cea mai mare vânzare Produsul 4 timp de două sferturi.

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 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. 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 din 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 opțiune pentru sarcină 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 linie î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!

Am o cantitate imensă de date și de multe ori trebuie să caut, de exemplu, după numele produsului, pentru a vedea anumite caracteristici. Utilizarea unei căutări obișnuite este incomod, deoarece tabelul are multe coloane și adesea trebuie verificat de două ori. Este posibil să automatizezi acest lucru cumva?

Acest lucru se poate face folosind VLOOKUP sau GLOOKUP. CĂUTARE V este utilizată dacă valoarea necesară este situată în coloană. GPR - dacă într-o linie. Dar ordinea și principiul de funcționare al funcțiilor sunt aceleași. Aceste funcții pot fi utilizate dacă datele sunt unice (adică nu se repetă).

Deoarece se presupune că există o mulțime de date, puteți face o casetă combinată pentru a simplifica procesul de introducere a datelor necesare și pentru a evita erorile. Pentru a face acest lucru, plasați cursorul într-o celulă care va fi o casetă combinată (puteți face acest lucru pe o foaie goală care nu conține date, pe care o vom folosi în viitor pentru a afișa rezultatele căutării). Pe fila Date grupuri Post cu datele click pe buton Verificarea datelor selectați elementul din lista verticală Verificarea datelor...

În caseta de dialog Examinare pe filă Opțiuniîn câmp Tip de date alege Listă. În câmpul care apare Sursă Indicăm un link către gama de celule care conține numele mărfurilor. Faceți clic pe OK.

Ar trebui să ajungeți cu o listă derulantă ca aceasta:

Acum să trecem direct la crearea unei funcții pentru a căuta valorile necesare. Tabelul în care va avea loc căutarea trebuie mai întâi sortat după valoarea dorită în ordine crescătoare. În exemplul nostru, după marca de telefon.

Plasați cursorul mouse-ului pe linia de lângă lista derulantă creată sau într-un alt loc unde doriți să fie afișate rezultatele căutării. Folosind fila sau butonul Formule Funcția de inserare pe Bare de formule selectați funcția VLOOKUP.

Masă- selectați tabelul cu datele astfel încât argumentul necesar să fie în prima coloană și apăsați butonul F4 sau introduceți manual semnele $ pentru ca legătura să devină absolută. Selectați tabelul fără antet, doar date.

Număr_coloană- indicați numărul de serie al coloanei ale cărei date dorim să le vedem ca rezultat al căutării.

Time-lapse_view- FALSE - pentru că avem nevoie de o potrivire exactă a datelor specificate în căutare.

Faceți clic pe OK.

Schimbarea Numărul coloaneiÎn funcția VLOOKUP creată, puteți selecta mai multe valori din tabelul sursă care vor îndeplini condițiile dvs. de căutare.

Este posibil să se efectueze o căutare similară cu o căutare folosind funcția CĂUTARE V, dar fără a sorta datele? VLOOKUP produce eroarea 9 în acest caz.

Puteți găsi orice valoare într-un rând dacă cunoașteți valoarea care este într-o coloană folosind două funcții CĂUTARE și INDEX.

Ca exemplu, să luăm un tabel care conține numele mărcilor de telefoane și prețurile acestora.

Pentru a simplifica căutarea valorilor necesare, puteți face o casetă combinată, care va consta din datele pe care le specificăm pentru căutare (de exemplu, numele produsului). Pentru a face acest lucru, plasând cursorul într-o celulă liberă, accesați fila Date grup Lucrul cu dateși apăsați butonul Verificarea datelor, din lista derulantă selectați articolul Verificarea datelor...În caseta de dialog Validarea valorilor introduse pe filă Opțiuniîn câmp Tip de date alege Listăși indicați intervalul care conține datele de căutat.

Apoi, plasați cursorul într-o celulă liberă folosind fila Formule sau butoane Funcția de inserare pe Bare de formule selectați funcția CĂUTARE.

Search_value- indicăm celula care conține caseta combinată pe care am creat-o la început.

View_array- selectați gama de celule care conțin celulele pe care le căutați. În exemplul nostru, există o coloană cu mărci de telefoane, dar trebuie să o evidențiați fără antet.

Tip_mapping- specificați „0” (zero) - acest lucru va permite funcției să găsească primul argument care se potrivește cu valoarea dorită.

Faceți clic pe OK.

Funcția MATCH indică ce linie conține valoarea dorită. Prin plasarea cursorului pe celula care conține formula, veți vedea sintaxa funcției în bara de formule, selectați-o până la semnul „=" și copiați-o (apăsând Ctrl+C) și apăsați Enter pentru a ieși din celulă.

Apoi, plasăm cursorul într-o celulă liberă în care vom afișa rezultatele, și folosind fila Formule sau butoane Funcția de inserare pe Bare de formule selectați funcția INDEX. Această funcție conține două tipuri de argumente, despre care veți fi imediat informat, avem nevoie de „număr_linie”;

În caseta de dialog Argumente ale funcției indica urmatoarele valori:

Matrice- selectați întreaga matrice de date (fără antetul tabelului), care conține atât valorile dorite, cât și datele pe care dorim să fie afișate ulterior. În exemplul nostru, acestea sunt coloanele care conțin marca și prețul telefonului.

Număr_date- aici inserăm funcția MATCH apăsând Ctrl+V.

Număr_coloană- aici trebuie să specificați numărul coloanei din matrice, a cărei valoare va fi afișată ca rezultat al căutării.

Faceți clic pe OK.