În acest articol, vom învăța cum să căutați valoarea între două numere în Excel.
Scenariu:
Este ușor să căutați o valoare cu un criteriu într-un tabel. Putem folosi pur și simplu VLOOKUP. Dar ce am putea face dacă avem aceleași criterii de coloane pentru a se potrivi în datele dvs. și trebuie să căutați în mai multe coloane pentru a se potrivi cu o valoare. Să învățăm cum poate fi rezolvată această problemă folosind diferite versiuni de căutare ale Excel
CĂUTARE valoare între două numere
Folosind formula VLOOKUP
În potrivirea aproximativă VLOOKUP, dacă nu se găsește o valoare, atunci se potrivește ultima valoare mai mică decât valoarea de căutare și se returnează valoarea din indexul coloanei date.
Formula generică pentru a căuta valoarea între două numere:
| = VLOOKUP (valoare, tabel, lookup_col, 1) |
Și încă un lucru despre Vlookup este că arată valoarea din coloană și dacă nu găsește valoarea în matricea de coloane, atunci se potrivește și returnează valoarea care este mai mică decât acea valoare din matricea de tabel.
Notă: VLOOKUP implicit face o potrivire aproximativă, dacă omitem variabila de căutare a intervalului. O potrivire aproximativă este utilă atunci când doriți să faceți o potrivire aproximativă și când aveți matricea de tabele sortată în ordine crescătoare.
Folosind formula INDEX și MATCH
Formula INDEX și MATCH face același lucru ca mai sus, dar cu sintaxă diferită. Dar dacă ar trebui să o alegeți doar pe cea cu care funcții vă simțiți confortabil
Formula generică pentru a căuta valoarea dintre două numere
| = INDEX (gama_cercetării, MATCH (1, INDEX ((criterii1 = interval1)*(criterii2 = interval2),0,1),0)) |
interval de căutare: intervalul din care doriți să recuperați valoarea.
Criterii 1, Criterii 2, Criterii N: Acestea sunt criteriile pe care doriți să le potriviți în intervalul 1, intervalul 2 și intervalul N. Puteți avea până la 270 de criterii - perechi de intervale.
Range1, range2, rangeN: Acestea sunt intervalele în care veți corespunde criteriilor dvs. respective.
Exemplu:
Toate acestea ar putea fi confuze pentru a înțelege. Să înțelegem cum să folosim funcția folosind un exemplu. Aici avem ID-ul de student și notele respective într-un test. Acum a trebuit să obținem notele pentru fiecare elev prin căutarea în tabelul sistemului de note.
Pentru a face acest lucru, vom folosi un atribut al funcției VLOOKUP, care este în cazul în care funcția VLOOKUP nu găsește potrivirea exactă pe care o caută doar pentru potrivirea aproximativă din tabel și numai dacă ultimul argument al funcției este fie ADEVĂRAT sau 1.
Mărcile / tabelul de scor trebuie să fie în ordine crescătoare
Folosiți formula:
| = VLOOKUP (B2, tabel, 2, 1) |
Cum functioneazã?
Funcția Vlookup caută valoarea 40 în coloana Marcaje și returnează valoarea corespunzătoare, dacă este potrivită. Dacă nu se potrivește, funcția caută valoarea mai mică decât valoarea de căutare (adică 40) și returnează rezultatul.
Aici table_array este denumit interval ca masa în formulă și B2 este dat ca referință de celulă.
După cum puteți vedea, am obținut nota pentru primul elev. Acum, pentru a obține toate celelalte note, vom folosi o comandă rapidă Ctrl + D sau utilizați opțiunea glisați în celulă în Excel.
Iată toate notele clasei care utilizează funcția VLOOKUP.
CĂUTARE valoare între două numere de pe tabelul angajaților
Avem un tabel care conține detaliile tuturor angajaților dintr-o organizație pe o foaie separată. Prima coloană conține ID-ul acestor angajați. Am numit acest tabel drept emp_data.
Acum, foaia mea de căutare trebuie să preia informațiile angajatului al căror cod este scris în celula B3. Am numit B3 ca ID.
Pentru ușurință de înțelegere, toate anteturile coloanei sunt exact în aceeași ordine ca și tabelul emp_data.
Acum scrieți formula de mai jos în celula C3 pentru a recupera zona ID-ului angajatului scris în B3.
| = VLOOKUP (ID, Emp_Data, 2,0) |
Aceasta va returna zona angajatului Id 1-1830456593 deoarece coloana numărul 2 din baza de date conține zona angajaților.
Copiați această formulă în restul celulelor și modificați numărul coloanei din formulă pentru a obține toate informațiile angajaților.
Puteți vedea toate informațiile legate de ID-ul menționat în celula B3. Indiferent de codul pe care îl scrieți în celula B3, toate informațiile vor fi preluate fără a face nicio modificare în formulă.
Cum funcționează asta?
Nu este nimic complicat. Pur și simplu folosim funcția VLOOKUP pentru a căuta ID-ul și apoi a prelua coloana menționată. Exersați VLOOKUP folosind astfel de date pentru a înțelege mai mult VLOOKUP.
Preluarea datelor despre angajați folosind titluri
În exemplul de mai sus, am avut toate coloanele organizate în aceeași ordine, dar vor exista momente când veți avea o bază de date care va avea sute de coloane. În astfel de cazuri, această metodă de recuperare a informațiilor despre angajați nu va fi bună. Va fi mai bine dacă formula poate privi antetul coloanei și prelua date din coloana respectivă din tabelul angajaților.
Deci, pentru a extrage valoarea din tabel folosind titlul coloanei, vom folosi o metodă de căutare bidirecțională sau vom spune căutarea dinamică a VLOOKUP.
Utilizați această formulă în celula C3 și copiați în celulele restante. Nu este nevoie să schimbați nimic în formulă, totul va fi preluat din thd emp_data.
| = VLOOKUP (ID, Emp_Data, MATCH (C2, Emp_Data_Headers, 0), 0) |
Această formulă preia pur și simplu toate informațiile din coloanele potrivite. Puteți amesteca anteturile în raport, acest lucru nu va face nicio diferență. Indiferent de titlul scris în celula de mai sus, datele respective conțin.
Cum funcționează asta?
Acesta este pur și simplu un VLOOKUP dinamic. Puteți citi despre asta aici. Dacă o voi explica aici, va deveni un articol prea mare.
Recuperați ID-ul angajatului cu potrivire parțială
Se poate întâmpla să nu vă amintiți întregul ID al unui angajat, dar doriți totuși să preluați informațiile unui ID. În astfel de cazuri potrivirea parțială VLOOKUP este cea mai bună soluție.
De exemplu, dacă știu că un ID conține 2345, dar nu știu întregul ID. Dacă introduc acest număr în celula C3, ieșirea va fi ca.
Nu primim nimic. Deoarece nimic nu se potrivește cu 2345 din tabel. Modificați formula de mai sus astfel.
| =CĂUTARE("*"&ID&"*", Emp_Data,MECI(C2, Emp_Data_Headers, 0), 0) |
Copiați acest lucru pe întregul rând. Și acum aveți informațiile primului angajat care conține acest număr.
Vă rugăm să rețineți că vom primi primul ID care conține numărul potrivit în coloana Emp Id. Dacă orice alt ID conține același număr, această formulă nu va prelua informațiile angajatului respectiv.
Dacă doriți să obțineți toate ID-urile angajaților care conțin același număr, utilizați formula care caută toate valorile potrivite.
Folosind formula INDEX și MATCH
Aici avem un tabel de date. Vreau să extrag Numele clientului folosind Data de rezervare, Constructorul și Zona. Așadar, aici am trei criterii și un interval de căutare.
Scrieți această formulă în celula I4 apăsați Enter.
| = INDEX (E2: E16, MATCH (1, INDEX ((I1 = A2: A16) * (I2 = B2: B16) * (I3 = C2: C16), 0,1), 0)) |
Cum functioneaza:
Știm deja cum funcționează funcția INDEX și MATCH în EXCEL, așa că nu voi explica asta aici. Vom vorbi despre trucul pe care l-am folosit aici.
(I1 = A2: A16) * (I2 = B2: B16) * (I3 = C2: C16): partea principală este aceasta. Fiecare parte a acestei afirmații returnează o serie de false false.
Când valorile booleene sunt înmulțite, ele returnează o matrice de 0 și 1. Multiplicarea funcționează ca un operator AND. Intensificați atunci când toate valorile sunt adevărate numai atunci returnează încă 1
(I1 = A2: A16) * (I2 = B2: B16) * (I3 = C2: C16) Acest lucru va reveni
| {FALS; FALS; FALS; FALS; FALS; FALS; ADEVĂR; ADEVĂR; FALS; FALS; FALS; FALS; FALS; FALS; FALS} *
{FALSE; FALSE; FALSE; ADEVĂRATE; ADEVATE; ADEVATE; ADEVATE; ADEVARE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; ADEVĂRATE} * {FALSE; FALSE; FALSE; ADEVĂRATE; FALSE; FALSE; FALSE; ADEVARE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE} |
Ceea ce se va traduce în
| {0;0;0;0;0;0;0;1;0;0;0;0;0;0;0} |
INDEX ((I1 = A2: A16) * (I2 = B2: B16) * (I3 = C2: C16), 0,1): Funcția INDEX va returna aceeași matrice ({0; 0; 0; 0; 0; 0; 0; 0; 1; 0; 0; 0; 0; 0; 0; 0}) la funcția MATCH ca matrice de căutare.
MATCH (1, INDEX ((I1 = A2: A16) * (I2 = B2: B16) * (I3 = C2: C16), 0,1): funcția MATCH va căuta 1 în matricea {0; 0; 0; 0; 0; 0; 0; 1; 0; 0; 0; 0; 0; 0; 0} Și va returna numărul index al primului 1 găsit în matrice. Care este 8 aici.
INDEX (E2: E16, MATCH (1, INDEX ((I1 = A2: A16) * (I2 = B2: B16) * (I3 = C2: C16), 0,1), 0)): În cele din urmă, INDEX va reveni valoarea din intervalul dat (E2: E16) la indexul găsit (8).
Iată toate notele de observație folosind formula din Excel
Note:
- Coloana tabelului de note / scoruri trebuie să fie în ordine crescătoare sau altfel funcția poate da rezultatul greșit.
- Funcția VLOOKUP caută valoarea din primul rând din Table_array și extrage valorile corespunzătoare numai în dreapta intervalului de căutare.
- Ultimul argument al funcției VLOOKUP trebuie setat la TRUE sau 1 pentru a obține potrivirea aproximativă.
- Funcția VLOOKUP returnează o eroare dacă adresa registrului de lucru este nevalidă sau greșită.
- Funcția VLOOKUP returnează o eroare dacă valoarea nu este potrivită.
Sper că acest articol despre Cum să căutați valoarea dintre două numere în Excel este explicativ. Găsiți mai multe articole despre calcularea valorilor și formulele Excel conexe aici. Dacă ți-au plăcut blogurile noastre, împărtășește-le prietenilor tăi de pe Facebook. Și, de asemenea, ne puteți urmări pe Twitter și Facebook. Ne-ar plăcea să aflăm de la dvs., să ne anunțați cum putem îmbunătăți, completa sau inova munca noastră și să o îmbunătățim. Scrieți-ne pe site-ul de e-mail.
Mod de a utiliza funcția Vlookup în validarea datelor : Restricționați utilizatorii să permită valorile din tabelul de căutare utilizând caseta Formula validării datelor din Excel. Caseta Formula din validarea datelor vă permite să alegeți tipul de restricție necesar.
Cum se recuperează cel mai recent preț în Excel : Este obișnuit să actualizați prețurile în orice afacere și să utilizați cele mai recente prețuri pentru orice achiziție sau vânzare. Pentru a prelua cel mai recent preț dintr-o listă din Excel, folosim funcția CĂUTARE. Funcția de căutare obține cel mai recent preț.
Funcția VLOOKUP pentru a calcula nota în Excel : Pentru a calcula notele IF și IFS nu sunt singurele funcții pe care le puteți utiliza. VLOOKUP este mai eficient și mai dinamic pentru astfel de calcule condiționale. Pentru a calcula notele folosind VLOOKUP putem folosi această formulă.
17 lucruri despre Excel VLOOKUP : VLOOKUP este cel mai frecvent utilizat pentru recuperarea valorilor potrivite, dar VLOOKUP poate face mult mai mult decât atât. Iată 17 lucruri despre VLOOKUP pe care ar trebui să le știți să le utilizați eficient.
Căutați primul text dintr-o listă în Excel : Funcția VLOOKUP funcționează bine cu caractere wildcard. Putem folosi acest lucru pentru a extrage prima valoare text dintr-o listă dată în Excel. Iată formula generică.
CĂUTARE dată cu ultima valoare din listă : Pentru a prelua data care conține ultima valoare folosim funcția LOOKUP. Această funcție verifică celula care conține ultima valoare într-un vector și apoi folosește acea referință pentru a returna data.
Articole populare:
50 de comenzi rapide Excel pentru a vă crește productivitatea : Obțineți mai rapid sarcinile dvs. în Excel. Aceste comenzi rapide vă vor ajuta să vă sporiți eficiența de lucru în Excel.
Cum se utilizează funcția VLOOKUP în Excel : Aceasta este una dintre cele mai utilizate și populare funcții ale Excel, care este utilizată pentru a căuta valori din diferite intervale și foi.
Cum se utilizează funcția IF în Excel : Instrucțiunea IF din Excel verifică starea și returnează o valoare specifică dacă condiția este ADEVĂRATĂ sau returnează o altă valoare specifică dacă este FALSĂ.
Cum se utilizează funcția SUMIF în Excel : Aceasta este o altă funcție esențială a tabloului de bord. Acest lucru vă ajută să rezumați valori în condiții specifice.
Cum se utilizează funcția COUNTIF în Excel : Numărați valorile cu condiții folosind această funcție uimitoare. Nu trebuie să vă filtrați datele pentru a număra anumite valori. Funcția Countif este esențială pentru a vă pregăti tabloul de bord.