În acest articol, vom afla cum să căutați mai multe instanțe ale unei valori în Excel.
Căutați valori folosind opțiunea derulantă?
Aici înțelegem cum putem căuta rezultate diferite folosind formula matricei de funcții INDEX. Doar selectați valoarea din listă și rezultatul corespunzător va fi acolo.
Formula generică
| {= INDEX (matrice, SMALL (IF (lookup_value = lookup_value_range, ROW (lookup_value_range) -ROW (prima celulă de lookup_value_range) +1), ROW (1: 1)))} |
Matrice: Intervalul de unde doriți să preluați date.
valoare_cercare: Valoarea dvs. de căutare pe care doriți să o filtrați.
interval_valor_cercare: Intervalul în care doriți să filtrați lookup_value.
Prima celulă din intervalul lookup_value: dacă intervalul dvs. lookup_value este de $ A $ 5: $ A $ 100 $, atunci este de $ A $ 5.
Important: Totul ar trebui să fie referențiat absolut. valoare_cercare poate fi relativă în funcție de cerință.
Introduceți-l ca o formulă matrice. După scrierea formulei, utilizați CTRL + SHIFT + ENTER pentru a face din aceasta o formulă matrice.
Exemplu de căutare pentru rezultate multiple
Am aceste date despre elevi în raza de acțiune A2: E14. În celula G1, am o listă verticală a valorilor regiunii, de ex. Central, est, nord, sud și vest. Acum vreau, indiferent de regiunea pe care o am în G1, o listă a tuturor studenților din acea regiune ar trebui să fie afișată în coloana H.
Pentru a căuta mai multe valori în Excel, să identificăm variabilele noastre.
Matrice: $ C $ 2: $ C $ 14
valoare_cercare: $ G $ 1
interval_valor_cercare: $ A $ 2: $ A $ 14
Prima celulă din intervalul lookup_value: $ A $ 2
Conform datelor de mai sus, formula noastră pentru a recupera mai multe valori în Excel va fi:
| {= IFERROR (INDEX ($ C $ 2: $ C $ 14, SMALL (IF ($ G $ 1 = $ A $ 2: $ A $ 14, ROW ($ A $ 2: $ A $ 14)) -ROW ($ A $ 2) +1) , ROW (1: 1))), "Nu mai există valoare")} |
Aceasta este o formulă matrice. Nu utilizați doar Enter după ce ați introdus formula. Folosiți CTRL + SHIFT + ENTER împreună.
Acum să înțelegem CUM FUNCȚIONEAZĂ.
Chiar dacă formula poate părea complexă, dar ideea este simplă. Trebuie să obținem numărul de index al fiecărei apariții a valorii, apoi să recuperăm valorile folosind funcția INDEX a Excel.
Prin urmare, provocarea principală este de a obține o serie de numere index ale valoare_cercare. Pentru a obține numere index, am folosit funcțiile IF și ROW. Formula este într-adevăr complexă, să o descompunem.
Vrem să obținem valori din coloana student, deci matricea noastră pentru funcția INDEX este $ C $ 2: $ C $ 14.
Acum trebuie să dăm numere de rând de la $ A $ 2: $ A $ 14 (valoare de căutare) în care există valoarea G1 (să spunem, deocamdată) G1 are o centrală în ea).
IF ($ G $ 1 = $ A $ 2: $ A $ 14, ROW ($ A $ 2: $ A $ 14): Acum, această parte returnează numărul rândului dacă valoarea G1 a unui contain de celulă (central) în raza de acțiune $ A $ 2: $ A $ 14 altfel se întoarce FALS. În acest exemplu, va reveni
{2; FALS; FALS; FALS; FALS; 7; 8; FALS; FALS; 11; FALS; FALS; FALS}.
Acum, deoarece matricea de mai sus conține numere de rând din primul rând (1: 1) și avem nevoie de rânduri care încep de la matrice (A2: A14). Pentru a face acest lucru, folosim -ROW ($ A $ 2) +1 în formula IF. Aceasta va returna un număr de rânduri înainte de a începe matricea noastră.
Pentru acest exemplu, este -1. Dacă începea de la A3, ar reveni -2 și așa mai departe. Acest număr va fi scăzut din fiecare număr din matricea returnată de IF. Deci, în sfârșit IF ($ G $ 1 = $ A $ 2: $ A $ 14, ROW ($ A $ 2: $ A $ 14) -ROW ($ A $ 2) +1) acest lucru se va traduce în {1; FALS; FALS; FALS; FALS;6;7; FALS; FALS;10; FALS; FALS; FALS}.
Apoi, această matrice este înconjurată de funcția SMALL. Această funcție returnează cea de-a N-a cea mai mică valoare din matricea dată. Acum, avem MICI ({2; FALS; FALS; FALS; FALS;7;8; FALS; FALS;11; FALSE; FALSE; FALSE}, ROW (1: 1)). ROW (1: 1) va reveni 1. Prin urmare, funcția de mai sus va returna prima cea mai mică valoare din matrice, care este 2.
Când copiați această formulă în celulele de mai jos ROW (1: 1) va deveni ROW (2: 2) și va returna a 2-a cea mai mică valoare din matrice, care este 7 și așa mai departe. Aceasta permite funcției să returneze mai întâi prima valoare găsită. Dar dacă doriți să obțineți ultima valoare găsită mai întâi, atunci utilizați funcția LARGE în loc de funcția SMALL.
Acum, folosind valorile returnate deasupra funcțiilor, funcția INDEX returnează cu ușurință fiecare valoare potrivită dintr-un interval.
De asemenea, puteți efectua căutări potrivite exacte utilizând funcțiile INDEX și MATCH în Excel. Aflați mai multe despre cum se face căutarea sensibilă la majuscule și minuscule folosind funcția INDEX & MATCH în Excel. De asemenea, puteți căuta potrivirile parțiale folosind metacaracterele din Excel.
Sper că acest articol despre Cum să căutați mai multe instanțe ale unei valori î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.
Utilizați VLOOKUP din două sau mai multe tabele de căutare | Pentru a căuta din mai multe tabele putem lua o abordare IFERROR. Pentru a căuta din mai multe tabele, este nevoie de eroare ca un comutator pentru tabelul următor. O altă metodă poate fi abordarea If.
Cum se face o căutare sensibilă la majuscule și minuscule în Excel | funcția VLOOKUP a excelului nu este sensibilă la majuscule și minuscule și va returna prima valoare potrivită din listă. INDEX-MATCH nu face excepție, dar poate fi modificat pentru a face sensibil la majuscule. Să vedem cum …
Căutați text care apare frecvent cu criterii în Excel | Căutarea apare cel mai frecvent în text într-un interval pe care îl folosim funcția INDEX-MATCH cu MOD. Iată metoda.
Articole populare:
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 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 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.