V tem članku se bomo naučili, kako poiskati več primerkov vrednosti v Excelu.
Iskanje vrednosti z uporabo spustnega menija?
Tu razumemo, kako lahko poiščemo različne rezultate s formulo matrike funkcij INDEX. Samo izberite vrednost s seznama in tam bo ustrezen rezultat.
Splošna formula
| {= INDEX (matrika, SMALL (IF (lookup_value = lookup_value_range, ROW (lookup_value_range) -ROW (prva celica lookup_value_range) +1), ROW (1: 1)))} |
Niz: Obseg, od koder želite pridobiti podatke.
lookup_value: Vaša lookup_value, ki jo želite filtrirati.
lookup_value_range: Obseg, v katerem želite filtrirati lookup_value.
Prva celica v obsegu lookup_value: če je vaš obseg lookup_value $ A $ 5: $ A $ 100, potem je to $ A $ 5.
Pomembno: Vse bi moralo biti absolutno referenčno. lookup_value je lahko relativno glede na zahtevo.
Vnesite ga kot formulo matrike. Po pisanju formule uporabite CTRL+SHIFT+ENTER, da postane formula matrike.
Primer iskanja za več rezultatov
Imam te študentske podatke v dosegu A2: E14. V celici G1 imam spustni meni vrednosti regij, npr. Srednje, vzhodno, severno, južno in zahodno. Zdaj želim, ne glede na to, katero regijo imam v G1, v stolpcu H prikazati seznam vseh študentov iz te regije.
Če želimo poiskati več vrednosti v Excelu, prepoznajmo naše spremenljivke.
Niz: $ C $ 2: $ C $ 14
lookup_value: $ G $ 1
lookup_value_range: $ A $ 2: $ A $ 14
Prva celica obsega lookup_value: $ 2 $
Glede na zgornje podatke bo naša formula za pridobivanje več vrednosti v Excelu naslednja:
| {= IFERROR (INDEKS ($ C $ 2: $ C $ 14, MALO (ČE ($ G $ 1 = $ A $ 2: $ A $ 14, ROW ($ A $ 2: $ A $ 14 USD) -ROW ($ A $ 2 $ +1) +1) , ROW (1: 1))), "Ni več vrednosti")} |
To je formula matrike. Po vnosu formule ne uporabljajte samo Enter. Uporabite CTRL + SHIFT + ENTER skupaj.
Zdaj pa razumejmo, kako deluje.
Čeprav je formula morda videti zapletena, je ideja preprosta. Za vsak pojav vrednosti moramo pridobiti indeksno številko, nato pa pridobiti vrednosti s pomočjo funkcije INDEX v Excelu.
Zato je glavni izziv pridobiti niz indeksnih številk lookup_value. Za pridobitev indeksnih številk smo uporabili funkciji IF in ROW. Formula je res zapletena, razčlenimo jo.
Vrednosti želimo pridobiti iz stolpca študent, zato je naša matrika za funkcijo INDEX naslednja $ C $ 2: $ C $ 14.
Zdaj moramo podati številke vrstic od $ A $ 2: $ A $ 14 (iskalna vrednost), v kateri obstaja vrednost G1 (zaenkrat recimo G1 ima v njem centralno).
ČE ($ G $ 1 = $ A $ 2: $ A $ 14, ROW ($ A $ 2: $ A $ 14): Zdaj ta del vrne številko vrstice, če je vrednost števca celice G1 (osrednji) v dosegu $ A $ 2: $ A $ 14 drugače se vrne LAŽNO. V tem primeru se bo vrnilo
{2; FALSE; FALSE; FALSE; FALSE; 7; 8; FALSE; FALSE; 11; FALSE; FALSE; FALSE}.
Zdaj, ker zgornja matrika vsebuje številke vrstic iz prve vrstice (1: 1) in potrebujemo vrstice, ki se začnejo iz naše matrike (A2: A14). Za to uporabimo -ROW ($ A $ 2) +1 v formuli IF. To bo vrnilo nekaj vrstic pred zagonom našega niza.
V tem primeru je -1. Če bi štartal od A3, bi vrnil -2 in tako naprej. Ta številka bo odšteta od vsake številke v matriki, ki jo vrne IF. Tako Končno ČE ($ G $ 1 = $ A $ 2: $ A $ 14, ROW ($ A $ 2: $ A $ 14) -ROW ($ A $ 2) +1) to se bo prevedlo v {1; FALSE; FALSE; FALSE; FALSE;6;7; FALSE; FALSE;10; FALSE; FALSE; FALSE}.
Nato je ta niz obdan s funkcijo SMALL. Ta funkcija vrne najmanjšo N vrednost v danem nizu. Zdaj imamo MALO ({2; FALSE; FALSE; FALSE; FALSE;7;8; FALSE; FALSE;11; FALSE; FALSE; FALSE}, ROW (1: 1)). ROW (1: 1) vrne 1. Zato bo zgornja funkcija vrnila prvo najmanjšo vrednost v matriki, ki je 2.
Ko kopirate to formulo v spodnje celice, bo ROW (1: 1) postal ROW (2: 2) in vrnil bo drugo najmanjšo vrednost v matriki, ki je 7 itd. To omogoča, da funkcija najprej vrne prvo najdeno vrednost. Če pa želite najprej dobiti zadnjo najdeno vrednost, uporabite funkcijo LARGE namesto funkcije SMALL.
Zdaj z uporabo vrnjenih vrednosti nad funkcijami funkcija INDEX zlahka vrne vsako ujemajočo se vrednost iz območja.
Poiščete lahko tudi natančna ujemanja s funkcijami INDEX in MATCH v Excelu. Preberite več o tem, kako v Excelu izvedete iskanje velikih in malih črk s funkcijo INDEX & MATCH. Delna ujemanja lahko poiščete tudi z nadomestnimi znaki v Excelu.
Upam, da je ta članek o tem, kako poiskati več primerkov vrednosti v Excelu, razložljiv. Več člankov o izračunu vrednosti in sorodnih formulah Excel najdete tukaj. Če so vam bili naši blogi všeč, jih delite s prijatelji na Facebooku. Prav tako nas lahko spremljate na Twitterju in Facebooku. Radi bi slišali od vas, nam sporočite, kako lahko izboljšamo, dopolnimo ali inoviramo svoje delo in ga izboljšamo. Pišite nam na spletnem mestu e -pošte.
Uporabite VLOOKUP iz dveh ali več iskalnih tabel | Za iskanje iz več tabel lahko uporabimo pristop IFERROR. Za iskanje iz več tabel napaka jemlje kot stikalo za naslednjo tabelo. Druga metoda je lahko pristop If.
Kako v Excelu poiskati velike in male črke | Excelova funkcija VLOOKUP ne razlikuje velikih in malih črk in bo vrnila prvo ujemajočo se vrednost s seznama. INDEX-MATCH ni izjema, vendar ga je mogoče spremeniti tako, da je občutljiv na velike in male črke. Poglejmo, kako…
Iskanje pogosto pojavljajočega se besedila z merili v Excelu | Iskanje se najpogosteje pojavi v besedilu v obsegu, ki ga uporabljamo INDEX-MATCH with MODE. Tukaj je metoda.
Priljubljeni članki:
Kako uporabljati funkcijo IF v Excelu : Stavek IF v Excelu preveri pogoj in vrne določeno vrednost, če je pogoj TRUE, ali vrne drugo posebno vrednost, če je FALSE.
Kako uporabljati funkcijo VLOOKUP v Excelu : To je ena najpogosteje uporabljenih in priljubljenih funkcij programa Excel, ki se uporablja za iskanje vrednosti iz različnih obsegov in listov.
Kako uporabljati funkcijo SUMIF v Excelu : To je še ena bistvena funkcija nadzorne plošče. To vam pomaga povzeti vrednosti za posebne pogoje.
Kako uporabljati funkcijo COUNTIF v Excelu : Štejte vrednosti s pogoji s to neverjetno funkcijo. Za štetje določenih vrednosti vam ni treba filtrirati podatkov. Funkcija Countif je bistvena za pripravo vaše armaturne plošče.