Šajā rakstā mēs uzzināsim, kā programmā Excel meklēt vairākus vērtības gadījumus.
Meklēt vērtības, izmantojot nolaižamo opciju?
Šeit mēs saprotam, kā mēs varam meklēt dažādus rezultātus, izmantojot funkciju INDEX masīva formulu. Vienkārši atlasiet vērtību no saraksta, un atbilstošais rezultāts būs tur.
Vispārīgā formula
| {= INDEKSS (masīvs, mazs |
Masīvs: Diapazons, no kura vēlaties iegūt datus.
lookup_value: Jūsu meklēšanas_vērtība, kuru vēlaties filtrēt.
lookup_value_range: Diapazons, kurā vēlaties filtrēt lookup_value.
Pirmā šūna lookup_value diapazonā: ja jūsu lookup_value diapazons ir $ A $ 5: $ A $ 100, tad tā ir $ A $ 5.
Svarīgs: Visam vajadzētu būt absolūti atsaucās. lookup_value var būt relatīvs atbilstoši prasībām.
Ievadiet to kā masīva formulu. Pēc formulas uzrakstīšanas izmantojiet CTRL+SHIFT+ENTER taustiņu, lai padarītu to par masīva formulu.
Vairāku rezultātu meklēšanas piemērs
Šie studentu dati ir diapazonā A2: E14. Šūnā G1 man ir reģionu vērtību nolaižamā izvēlne, piem. Centrālā, austrumu, ziemeļu, dienvidu un rietumu daļa. Tagad es vēlos, lai neatkarīgi no G1 reģiona, H slejā tiktu parādīts visu šī reģiona studentu saraksts.
Lai programmā Excel meklētu vairākas vērtības, identificēsim mūsu mainīgos.
Masīvs: 2 ASV dolāri: 14 ASV dolāri
lookup_value: $ G $ 1
lookup_value_range: 2 USD: 14 USD
Lookup_value diapazona pirmā šūna: 2 USD
Saskaņā ar iepriekš minētajiem datiem mūsu formula, lai iegūtu vairākas Excel vērtības, būs šāda:
| {= 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))), "Vairs nav vērtības")} |
Šī ir masīva formula. Pēc formulas ievadīšanas nelietojiet tikai Enter. Izmantojiet CTRL + SHIFT + ENTER kopā.
Tagad sapratīsim, kā tas darbojas.
Pat ja formula var šķist sarežģīta, ideja ir vienkārša. Mums jāiegūst katras vērtības parādīšanās indeksa numurs, pēc tam jāiegūst vērtības, izmantojot Excel funkciju INDEX.
Tādējādi galvenais izaicinājums ir iegūt indeksa numuru masīvu lookup_value. Lai iegūtu indeksa numurus, mēs izmantojām IF un ROW funkcijas. Formula patiešām ir sarežģīta, sadalīsim to.
Mēs vēlamies iegūt vērtības no studentu slejas, tāpēc mūsu funkcija INDEX ir 2 ASV dolāri: 14 ASV dolāri.
Tagad mums jāsniedz rindu numuri no 2 USD: 14 USD (uzmeklēšanas vērtība), kurā pastāv G1 vērtība (teiksim, pagaidām) G1 tajā ir centrālais punkts).
IF ($ G $ 1 = $ A $ 2: $ A $ 14, ROW ($ A $ 2: $ A $ 14): Tagad šī daļa atgriež rindas numuru, ja šūnas skaitītāja vērtība ir G1 (centrālais) diapazonā 2 USD: 14 USD cits atgriežas FALSE. Šajā piemērā tas atgriezīsies
{2; FALSE; FALSE; FALSE; FALSE; 7; 8; FALSE; FALSE; 11; FALSE; FALSE; FALSE}.
Tagad, tā kā iepriekšminētais masīvs satur rindu numurus no 1. rindas (1: 1), un mums ir vajadzīgas rindas, sākot no mūsu masīva (A2: A14). Lai to izdarītu, IF formulā mēs izmantojam -ROW ($ A $ 2) +1. Tas atgriezīs vairākas rindas pirms mūsu masīva sākšanas.
Šajā piemērā tas ir -1. Ja tas sāktos no A3, tas atgrieztos -2 un tā tālāk. Šis skaitlis tiks atņemts no katra IF masas numura. Tātad Visbeidzot IF ($ G $ 1 = $ A $ 2: $ A $ 14, ROW ($ A $ 2: $ A $ 14) -ROW ($ A $ 2) +1) tas tiks tulkots uz {1; FALSE; FALSE; FALSE; FALSE;6;7; FALSE; FALSE;10; FALSE; FALSE; FALSE}.
Tālāk šo masīvu ieskauj funkcija SMALL. Šī funkcija atgriež N -to mazāko vērtību dotajā masīvā. Tagad mums ir MAZS ({2; FALSE; FALSE; FALSE; FALSE;7;8; FALSE; FALSE;11; FALSE; FALSE; FALSE}, ROW (1: 1)). Rinda (1: 1) atgriezīs 1. Līdz ar to iepriekš minētā funkcija atgriezīs masīvā pirmo mazāko vērtību, kas ir 2.
Kopējot šo formulu zemāk esošajās šūnās, ROW (1: 1) kļūs par ROW (2: 2) un tā atdos masīvā otro mazāko vērtību, kas ir 7 un tā tālāk. Tas ļauj funkcijai vispirms atgriezt pirmo atrasto vērtību. Bet, ja vispirms vēlaties iegūt pēdējo atrasto vērtību, tad izmantojiet funkciju LARGE, nevis funkciju SMALL.
Tagad, izmantojot vērtības, kas atgrieztas iepriekš, funkcija INDEX viegli atgriež katru atbilstošo vērtību no diapazona.
Varat arī veikt uzmeklēšanas precīzās atbilstības, izmantojot funkciju INDEX un MATCH programmā Excel. Uzziniet vairāk par to, kā veikt reģistrjutīgu meklēšanu, izmantojot funkciju INDEX & MATCH programmā Excel. Daļējās atbilstības varat arī meklēt, izmantojot Excel aizstājējzīmes.
Ceru, ka šis raksts par to, kā programmā Excel meklēt vairākus vērtības gadījumus, ir paskaidrojošs. Vairāk rakstu par vērtību un saistīto Excel formulu aprēķināšanu meklējiet šeit. Ja jums patika mūsu emuāri, kopīgojiet to ar saviem draugiem Facebook. Un arī jūs varat sekot mums Twitter un Facebook. Mēs labprāt uzklausītu jūsu viedokli, dariet mums zināmu, kā mēs varam uzlabot, papildināt vai ieviest jauninājumus mūsu darbā un uzlabot to jūsu labā. Rakstiet mums e -pasta vietnē.
Izmantojiet VLOOKUP no divām vai vairākām uzmeklēšanas tabulām | Lai meklētu no vairākām tabulām, mēs varam izmantot IFERROR pieeju. Lai meklētu no vairākām tabulām, kļūda tiek uzskatīta par nākamās tabulas slēdzi. Vēl viena metode var būt If pieeja.
Kā veikt reģistrjutīgu meklēšanu programmā Excel | Excel VLOOKUP funkcija nav reģistrjutīga, un tā atgriezīs pirmo atbilstošo vērtību no saraksta. INDEX-MATCH nav izņēmums, taču to var mainīt, lai padarītu to reģistrjutīgu. Redzēsim, kā…
Meklējiet bieži parādīto tekstu ar kritērijiem programmā Excel | Meklēšana visbiežāk parādās tekstā diapazonā, kurā izmantojam funkciju INDEX-MATCH with MODE. Lūk, metode.
Populāri raksti:
Kā lietot funkciju IF programmā Excel : IF priekšraksts programmā Excel pārbauda nosacījumu un atgriež noteiktu vērtību, ja nosacījums ir TRUE, vai atgriež citu noteiktu vērtību, ja FALSE.
Kā lietot funkciju VLOOKUP programmā Excel : Šī ir viena no visbiežāk izmantotajām un populārākajām Excel funkcijām, kas tiek izmantota, lai meklētu vērtību no dažādiem diapazoniem un lapām.
Kā lietot funkciju SUMIF programmā Excel : Šī ir vēl viena būtiska informācijas paneļa funkcija. Tas palīdz apkopot vērtības noteiktos apstākļos.
Kā lietot funkciju COUNTIF programmā Excel : Saskaitiet vērtības ar nosacījumiem, izmantojot šo apbrīnojamo funkciju. Jums nav jāfiltrē dati, lai saskaitītu noteiktas vērtības. Skaitītāja funkcija ir būtiska, lai sagatavotu informācijas paneli.