Vizionați videoclipul - Căutați a doua, a treia sau a n-a valoare de potrivire
Când vine vorba de căutarea datelor în Excel, există două funcții uimitoare pe care le folosesc adesea - VLOOKUP și INDEX (mai ales împreună cu funcția MATCH).
Cu toate acestea, aceste formule sunt concepute pentru a găsi doar prima instanță a valorii de căutare.
Dar dacă doriți să căutați a doua, a treia, a patra sau a N-a valoare.
Ei bine, este posibil cu un pic de muncă suplimentară.
În acest tutorial, vă voi arăta diferite moduri (cu exemple) despre cum să căutați a doua sau a N-a valoare în Excel.
Căutați a doua, a treia sau a n-a valoare în Excel
În acest tutorial, voi acoperi două moduri de a căuta a doua sau a N-a valoare în Excel:
- Folosind o coloană de ajutor.
- Folosind formule matrice.
Să începem și să ne scufundăm chiar în.
Utilizarea coloanei de ajutor
Să presupunem că sunteți un coordonator de formare într-o organizație și că aveți un set de date așa cum se arată mai jos. Doriți să enumerați toate cursurile de formare în fața numelui unui angajat.
În setul de date de mai sus, angajaților li s-a oferit instruire cu privire la diferite instrumente Microsoft Office (Excel, PowerPoint și Word).
Acum, puteți utiliza funcția VLOOKUP sau combo-ul INDEX / MATCH pentru a găsi instruirea pe care un angajat a terminat-o. Cu toate acestea, va returna prima instanță potrivită.
De exemplu, în cazul lui John, a luat toate cele trei antrenamente, dar când îi caut numele cu VLOOKUP sau INDEX / MATCH, va reveni întotdeauna „Excel”, care este primul antrenament pentru numele său din listă .
Pentru a face acest lucru, putem folosi o coloană de ajutor și să creăm valori de căutare unice în ea.
Iată pașii:
- Introduceți o coloană înaintea coloanei care listează antrenamentul.
- În celula B2, introduceți următoarea formulă:
= A2 și COUNTIF ($ A $ 2: $ A2, A2)
- În celula F2, introduceți următoarea formulă și copiați-lipiți pentru toate celelalte celule:
= IFNA (VLOOKUP ($ E2 & COLUMNS ($ F $ 1: F1), $ B $ 2: $ C $ 14,2,0), "")
Formula de mai sus ar returna instruirea pentru fiecare angajat în ordinea în care apare pe listă. În cazul în care nu există o listă de instruire pentru un angajat, acesta returnează un gol.
Cum funcționează această formulă?
Formula COUNTIF din coloana asistentă face ca numele fiecărui angajat să fie unic prin adăugarea unui număr la acesta. De exemplu, prima instanță a lui Ioan devine Ioan1, a doua instanță devine Ioan2 și așa mai departe.
Formula VLOOKUP folosește acum aceste nume unice de angajați pentru a găsi pregătirea potrivită.
Rețineți că $ E2 & COLUMNS ($ F $ 1: F1) este valoarea de căutare din formulă. Aceasta ar adăuga un număr la numele angajatului pe baza numărului coloanei. De exemplu, când această formulă este utilizată în celula F2, valoarea de căutare devine „John1”. În celula G2, devine „John2” și așa mai departe.
Folosind formula Array
Dacă nu doriți să modificați setul de date original adăugând coloane de ajutor, puteți utiliza, de asemenea, o formulă matrice pentru a căuta a doua, a treia sau a n-a valoare.
Să presupunem că aveți același set de date ca în imaginea de mai jos:
Iată formula care va returna valoarea corectă de căutare:
= IFERROR (INDEX ($ B $ 2: $ B $ 14, SMALL (IF ($ A $ 2: $ A $ 14 = $ D2, ROW ($ A $ 2: $ A $ 14) -1, ""), COLUMNE : E1))), "")
Copiați această formulă și lipiți-o în celula E2.
Rețineți că aceasta este o formulă matrice și trebuie să utilizați Control + Shift + Enter (țineți apăsate tastele Control și Shift și apăsați tasta Enter), în loc să apăsați doar tasta Enter.
Faceți clic aici pentru a descărca fișierul de exemplu.
Cum funcționează această formulă?
Să împărțim această formulă în părți și să vedem cum funcționează.
$ A $ 2: $ A $ 14 = $ D2
Partea de mai sus a formulei compară fiecare celulă din A2: A14 cu valoarea din D2. În acest set de date, verifică dacă o celulă conține numele „John” sau nu.
Returnează o matrice de ADEVĂRAT de FALS. Dacă celula are numele „John”, ar fi adevărat, altfel ar fi fals.
Mai jos este matricea pe care o veți obține în acest exemplu:
{ADEVĂRAT; FALS; FALS; FALS; FALS; FALS; ADEVĂR; FALS; FALS; FALS; ADEVĂR; FALS; FALS}
Rețineți că are TRUE în pozițiile 1, 7 și 111, deoarece acolo apare numele John în setul de date.
IF ($ A $ 2: $ A $ 14 = $ D2, ROW ($ A $ 2: $ A $ 14) -1, ””)
Formula IF de mai sus folosește matricea TRUE și FALSE și înlocuiește TRUE cu poziția apariției sale în listă (dată de ROW ($ A $ 2: $ A $ 14) -1) și FALSE cu „” (spații libere). Următoarea este matricea rezultată pe care o obțineți cu această formulă IF:
{1;””;””;””;””;””;7;””;””;””;11;””;””}
Rețineți că 1, 7 și 11 sunt poziția apariției lui John în listă.
MICĂ (DACĂ ($ A $ 2: $ A $ 14 = $ D2, ROW ($ A $ 2: $ A $ 14) -1, ””), COLOANE ($ E $ 1: E1))
Funcția SMALL alege acum primul număr cel mai mic, al doilea cel mai mic, al treilea cel mai mic număr din această matrice. Rețineți că folosește funcția COLUMNS pentru a genera numărul coloanei. În celula E2, funcția COLUMNS returnează 1 și funcția SMALL returnează 1. În celula F2, funcția COLUMNS returnează 2 și funcția SMALL returnează 7.
INDEX ($ B $ 2: $ B $ 14, MICI (DACĂ ($ A $ 2: $ A $ 14 = $ D2, ROW ($ A $ 2: $ A $ 14) -1, ””), COLOANE ($ E $ 1: E1) )))
Funcția INDEX returnează acum valoarea din lista din Coloana B pe baza poziției returnate de funcția SMALL. Prin urmare, în celula E2, returnează „Excel”, care este primul element din B2: B14. În celula F2, returnează PowerPoint, care este al 7-lea element din listă.
Deoarece există cazuri în care există doar una sau două instruiri pentru unii angajați, funcția INDEX ar returna o eroare. Funcția IFERROR este utilizată pentru a returna un gol în locul erorii.
Rețineți că, în aceste exemple, am folosit referințe de gamă. Cu toate acestea, în exemple practice, este benefic să convertiți datele într-un tabel Excel. Prin conversie într-un tabel Excel, puteți utiliza referințe structurate, ceea ce face mai ușoară crearea formulelor. De asemenea, un tabel Excel poate contabiliza automat orice elemente de antrenament noi care sunt adăugate la listă (deci nu trebuie să ajustați formulele de fiecare dată).
Ce faci când trebuie să cauți a doua, a treia sau a N-a valoare? Sunt sigur că există mai multe modalități de a face acest lucru. Dacă utilizați ceva mai ușor decât cel enumerat aici, împărtășiți-ne tuturor în secțiunea de comentarii.
Faceți clic aici pentru a descărca fișierul de exemplu.
