În acest articol, vom învăța cum să preluați un preț dintr-o listă care se potrivește atât cu criteriile de categorie, cât și cu articolele din Excel.
Scenariu:
Este ușor să găsești o valoare cu un criteriu într-un tabel, pentru asta Am putea folosi pur și simplu VLOOKUP. Însă, atunci când nu aveți un singur criteriu de coloană în datele dvs. și trebuie să găsiți mai multe criterii de coloane pentru a se potrivi cu o valoare, VLOOKUP nu vă ajută.
Formula generică atât pentru categorie, cât și pentru articol
| = INDEX (interval de căutare, MATCH (1, INDEX ((item1 = item_range) * (category2 = category_range), 0,1), 0)) |
lookup_range: este 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 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)) |
Știm deja cum funcționează funcția INDEX și MATCH în EXCEL, deci nu vom 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
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).
Dacă puteți apăsa CTRL + SHIFT + ENTER în consecință, atunci puteți elimina funcția interioară INDEX. Scrieți doar această formulă și apăsați CTRL + SHIFT + ENTER.
Formulă
| = INDEX (E2: E16, MATCH (1, (I1 = A2: A16) * (I2 = B2: B16) * (I3 = C2: C16), 0)) |
Formula de matrice generică pentru căutarea mai multor criterii
| = INDEX (interval de căutare, MATCH (1, (criterii1 = interval1) * (criterii2 = interval2) * (criteriiN = intervalN), 0)) |
Iată toate notele de observație folosind formula din Excel
Note:
- Utilizați Vlookup dacă aveți un criteriu pe măsură, este o practică obișnuită.
- Puteți adăuga mai multe rânduri și coloane în matricea de căutare.
- Argumentele text trebuie date între ghilimele ("").
- Tabelul VLOOKUP trebuie să aibă lookup_array în coloana din stânga sau prima.
- Indexul col nu poate fi 1 deoarece este matricea de căutare
- Argumentul 0 este utilizat pentru valoarea potrivirii exacte. Utilizați 1 pentru valoarea aproximativă de potrivire.
- Funcția returnează o eroare # N / A, dacă valoarea de căutare nu este găsită în matricea de căutare. Deci, prindeți eroarea, dacă este necesar.
Sper că acest articol despre Cum să preiați un preț dintr-o listă care se potrivește atât cu criteriile de categorie, cât și cu criteriile de articol î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.
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 valoarea 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.