Skatieties video - meklējiet otro, trešo vai devīto atbilstošo vērtību
Runājot par datu meklēšanu programmā Excel, es bieži izmantoju divas pārsteidzošas funkcijas - VLOOKUP un INDEX (pārsvarā kopā ar funkciju MATCH).
Tomēr šīs formulas ir paredzētas, lai atrastu tikai pirmo uzmeklēšanas vērtības gadījumu.
Bet ko darīt, ja vēlaties meklēt otro, trešo, ceturto vai N vērtību.
Nu, tas ir izdarāms ar nelielu papildu darbu.
Šajā apmācībā es parādīšu dažādus veidus (ar piemēriem), kā programmā Excel meklēt otro vai N. vērtību.
Atrodiet otro, trešo vai n -to vērtību programmā Excel
Šajā apmācībā es apskatīšu divus veidus, kā Excel meklēt otro vai N vērtību:
- Izmantojot palīgu kolonnu.
- Izmantojot masīva formulas.
Sāksim un ienirsim iekšā.
Palīdzības kolonnas izmantošana
Pieņemsim, ka esat apmācību koordinators organizācijā un jums ir datu kopa, kā parādīts zemāk. Jūs vēlaties uzskaitīt visas apmācības darbinieka vārda priekšā.
Iepriekš minētajā datu kopā darbinieki ir apmācīti par dažādiem Microsoft Office rīkiem (Excel, PowerPoint un Word).
Tagad varat izmantot funkciju VLOOKUP vai kombināciju INDEX/MATCH, lai atrastu apmācību, ko darbinieks ir pabeidzis. Tomēr tas atgriezīs tikai pirmo atbilstošo gadījumu.
Piemēram, Jāņa gadījumā viņš ir izgājis visus trīs treniņus, bet, kad es uzmeklēšu viņa vārdu ar VLOOKUP vai INDEX/MATCH, tas vienmēr atgriezīs 'Excel', kas ir pirmais viņa vārda treniņš sarakstā .
Lai to paveiktu, mēs varam izmantot palīgu kolonnu un izveidot tajā unikālas uzmeklēšanas vērtības.
Tālāk ir norādītas darbības.
- Ievietojiet kolonnu pirms kolonnas, kurā uzskaitīti treniņi.
- Šūnā B2 ievadiet šādu formulu:
= A2 un COUNTIF ($ A $ 2: $ A2, A2)
- Šūnā F2 ievadiet šādu formulu un kopējiet un ielīmējiet visas pārējās šūnas:
= IFNA (VLOOKUP ($ E2 & SLEJAS ($ F $ 1: F1), $ B $ 2: $ C $ 14,2,0), "")
Iepriekš minētā formula atgriezīs apmācību katram darbiniekam tādā secībā, kādā tā parādās sarakstā. Ja darbiniekam nav norādītas apmācības, tas atgriež tukšu.
Kā šī formula darbojas?
COUNTIF formula palīga slejā padara katra darbinieka vārdu unikālu, pievienojot tam numuru. Piemēram, pirmais Jāņa gadījums kļūst par Jāni1, otrais - Džons2 un tā tālāk.
VLOOKUP formula tagad izmanto šos unikālos darbinieku vārdus, lai atrastu atbilstošo apmācību.
Ņemiet vērā, ka $ E2 & COLUMNS ($ F $ 1: F1) ir uzmeklēšanas vērtība formulā. Tādējādi darbinieka vārdam tiktu pievienots numurs, pamatojoties uz kolonnas numuru. Piemēram, ja šo formulu izmanto šūnā F2, uzmeklēšanas vērtība kļūst par “Jānis1”. Šūnā G2 tas kļūst par “John2” un tā tālāk.
Izmantojot masīva formulu
Ja nevēlaties mainīt sākotnējo datu kopu, pievienojot palīgkolonnas, varat izmantot arī masīva formulu, lai atrastu otro, trešo vai n -to vērtību.
Pieņemsim, ka jums ir tāda pati datu kopa, kā parādīts zemāk:
Šeit ir formula, kas atgriezīs pareizo uzmeklēšanas vērtību:
= IFERROR (INDEX ($ B $ 2: $ B $ 14, SMALL (IF ($ A $ 2: $ A $ 14 = $ D2, ROW ($ A $ 2: $ A $ 14) -1, "")), SLEJAS ($ E $ 1 : E1))), "")
Kopējiet šo formulu un ielīmējiet to šūnā E2.
Ņemiet vērā, ka tas ir masīvs formula, un jums ir nepieciešams, lai izmantotu Control + Shift + Enter (turiet kontroles un Shift taustiņu un nospiediet taustiņu Enter), nevis hitting tikai Enter taustiņu.
Noklikšķiniet šeit, lai lejupielādētu parauga failu.
Kā šī formula darbojas?
Sadalīsim šo formulu daļās un redzēsim, kā tā darbojas.
$ A $ 2: $ A $ 14 = $ D2
Iepriekšējā formulas daļa salīdzina katru šūnu A2: A14 ar vērtību D2. Šajā datu kopā tiek pārbaudīts, vai šūnā ir vārds “Jānis” vai nē.
Tas atgriež masīvu TRUE of FALSE. Ja šūnai ir vārds “Jānis”, tā būtu patiesa, pretējā gadījumā tā būtu nepatiesa.
Zemāk ir masīvs, ko jūs iegūtu šajā piemērā:
{True; false; false; false; false; false; true; false; false; false; true; false; FALSE}
Ņemiet vērā, ka 1., 7. un 111. pozīcijā tam ir PATIESA, jo tur datu kopā parādās vārds Jānis.
IF ($ A $ 2: $ A $ 14 = $ D2, ROW ($ A $ 2: $ A $ 14) -1, ””)
Iepriekš minētā IF formula izmanto masīvu TRUE un FALSE un aizstāj TRUE ar tā parādīšanās vietu sarakstā (norādīta ROW ($ A $ 2: $ A $ 14) -1) un FALSE ar “” (tukšas vietas). Tālāk ir iegūts masīvs, ko iegūstat, izmantojot šo IF formulu:
{1;””;””;””;””;””;7;””;””;””;11;””;””}
Ņemiet vērā, ka 1., 7. un 11. ir Jāņa parādīšanās vieta sarakstā.
SMALL (IF ($ A $ 2: $ A $ 14 = $ D2, ROW ($ A $ 2: $ A $ 14) -1, ""), kolonnas ($ E $ 1: E1))
Funkcija SMALL no šī masīva izvēlas pirmo mazāko, otro mazāko, trešo mazāko skaitli. Ņemiet vērā, ka kolonnas numura ģenerēšanai tā izmanto funkciju SLEJAS. Šūnā E2 funkcija SLEJAS atgriež 1 un funkcija MAZA atgriež 1. Šūnā F2 funkcija SLEJAS atgriež 2 un funkcija MAZA atgriež 7.
INDEKSS ($ B $ 2: $ B $ 14, SMALL (IF ($ A $ 2: $ A $ 14 = $ D2, ROW ($ A $ 2: $ A $ 14) -1, ””), SLEJAS ($ E $ 1: E1) ))
Funkcija INDEX tagad atgriež vērtību no B slejas saraksta, pamatojoties uz funkcijas SMALL atgriezto pozīciju. Tādējādi šūnā E2 tas atgriež “Excel”, kas ir pirmais vienums B2: B14. Šūnā F2 tas atgriež PowerPoint, kas ir 7. vienums sarakstā.
Tā kā ir gadījumi, kad dažiem darbiniekiem ir tikai viena vai divas apmācības, funkcija INDEX atgriezīs kļūdu. Funkciju IFERROR izmanto, lai kļūdas vietā atgrieztu tukšu.
Ņemiet vērā, ka šajos piemēros esmu izmantojis diapazona atsauces. Tomēr praktiskos piemēros ir lietderīgi datus pārvērst Excel tabulā. Pārvēršot Excel tabulā, varat izmantot strukturētas atsauces, kas atvieglo formulu izveidi. Turklāt Excel tabula var automātiski uzskaitīt visus jaunos mācību priekšmetus, kas tiek pievienoti sarakstam (tāpēc jums nav katru reizi jāpielāgo formulas).
Ko jūs darāt, kad jāmeklē otrā, trešā vai N vērtība? Esmu pārliecināts, ka ir vairāk veidu, kā to izdarīt. Ja izmantojat kaut ko vieglāku par šeit uzskaitīto, kopīgojiet ar mums visus komentāru sadaļā.
Noklikšķiniet šeit, lai lejupielādētu parauga failu.
