U ovom ćemo članku naučiti Kako potražiti više primjeraka vrijednosti u Excelu.
Tražiti vrijednosti pomoću padajuće opcije?
Ovdje razumijemo kako možemo tražiti različite rezultate koristeći formulu niza funkcija INDEX. Samo odaberite vrijednost s popisa i bit će tamo odgovarajući rezultat.
Opća formula
| {= INDEX (niz, SMALL (IF (lookup_value = lookup_value_range, ROW (lookup_value_range) -ROW (prva ćelija opsega_opremne_vrijednosti) +1), ROW (1: 1)))} |
Niz: Raspon od mjesta gdje želite dohvatiti podatke.
lookup_value: Vaša lookup_value koju želite filtrirati.
raspon_vrijednosti_pogledavanja: Raspon u kojem želite filtrirati lookup_value.
Prva ćelija u rasponu lookup_value: ako je vaš raspon lookup_value $ A $ 5: $ A $ 100 onda je to $ A $ 5.
Važno: Sve bi trebalo biti apsolutno referencirano. lookup_value može biti relativno prema zahtjevu.
Unesite ga kao formulu niza. Nakon pisanja formule upotrijebite CTRL+SHIFT+ENTER kako biste je učinili formulom niza.
Primjer pretraživanja za više rezultata
Imam podatke o studentima u dometu A2: E14. U ćeliji G1 imam padajući popis vrijednosti regije, npr. Središnji, istočni, sjeverni, južni i zapadni. Sada želim, bez obzira na to koju regiju imam u G1, popis svih učenika iz te regije trebao bi biti prikazan u stupcu H.
Da bismo potražili više vrijednosti u Excelu, identificirajmo naše varijable.
Niz: $ C $ 2: $ C $ 14
lookup_value: $ G $ 1
raspon_vrijednosti_pogledavanja: $ A $ 2: $ A $ 14
Prva ćelija raspona lookup_value: $ 2 USD
Prema gornjim podacima, naša formula za dohvaćanje više vrijednosti u excelu bit će:
| {= IFERROR (INDEKS ($ C $ 2: $ C $ 14, SMALL (AKO ($ G $ 1 = $ A $ 2: $ A $ 14, ROW ($ A $ 2: $ A $ 14)) -ROW ($ A $ 2) +1) , ROW (1: 1))), "Nema više vrijednosti")} |
Ovo je formula niza. Nemojte koristiti samo Enter nakon upisivanja formule. Koristite CTRL + SHIFT + ENTER zajedno.
Sada shvatimo KAKO RADI.
Iako formula može izgledati složeno, ideja je jednostavna. Moramo dobiti indeksni broj svake pojave vrijednosti, a zatim dohvatiti vrijednosti pomoću funkcije INDEX u Excelu.
Stoga je glavni izazov dobiti niz indeksnih brojeva lookup_value. Za dobivanje indeksnih brojeva koristili smo IF i ROW funkcije. Formula je doista potpuno složena, razložimo je.
Želimo dobiti vrijednosti iz studenskog stupca, pa je naš niz za funkciju INDEX takav $ C $ 2: $ C $ 14.
Sada moramo dati brojeve redaka iz $ A $ 2: $ A $ 14 (lookup value) u kojoj postoji vrijednost G1 (za sada, recimo G1 u sebi ima centralnu).
AKO ($ G $ 1 = $ A $ 2: $ A $ 14, ROW ($ A $ 2: $ A $ 14): Sada ovaj dio vraća broj retka ako vrijednost ćelije sadrži G1 (središnji) u dometu $ A $ 2: $ A $ 14 inače se vraća LAŽNO. U ovom primjeru će se vratiti
{2; FALSE; FALSE; FALSE; FALSE; 7; 8; FALSE; FALSE; 11; FALSE; FALSE; FALSE}.
Sada, budući da gornji niz sadrži brojeve redaka iz 1. reda (1: 1) i potrebni su nam redovi koji počinju od našeg niza (A2: A14). Da bismo to učinili, koristimo -ROW ($ A $ 2) +1 u formuli IF. Ovo će vratiti određeni broj redaka prije pokretanja našeg niza.
U ovom primjeru je -1. Da je krenuo od A3, vratio bi -2 i tako dalje. Taj će se broj oduzeti od svakog broja u nizu koji vrati IF. Tako Konačno AKO ($ G $ 1 = $ A $ 2: $ A $ 14, ROW ($ A $ 2: $ A $ 14) -ROW ($ A $ 2) +1) to će se prevesti na {1; FALSE; FALSE; FALSE; FALSE;6;7; FALSE; FALSE;10; FALSE; FALSE; FALSE}.
Zatim je ovaj niz okružen SMALL funkcijom. Ova funkcija vraća N -tu najmanju vrijednost u danom nizu. Sada imamo MALO ({2; FALSE; FALSE; FALSE; FALSE;7;8; FALSE; FALSE;11; FALSE; FALSE; FALSE}, ROW (1: 1)). ROW (1: 1) će vratiti 1. Dakle, gornja funkcija će vratiti prvu najmanju vrijednost u nizu, koja je 2.
Kada kopirate ovu formulu u donje ćelije, ROW (1: 1) će postati ROW (2: 2) i vratit će drugu najmanju vrijednost u nizu, koja je 7 i tako dalje. To omogućuje funkciji da prvo vrati prvu pronađenu vrijednost. Ali ako želite prvo dobiti zadnju pronađenu vrijednost, upotrijebite LARGE funkciju umjesto SMALL funkcije.
Sada koristeći vrijednosti vraćene iznad funkcija, funkcija INDEX lako vraća svaku odgovarajuću vrijednost iz raspona.
Također možete izvršiti traženje točnih podudaranja pomoću funkcija INDEX i MATCH u Excelu. Saznajte više o tome kako napraviti pretraživanje osjetljivo na velika i mala slova pomoću funkcije INDEKS & MATCH u Excelu. Djelomična podudaranja možete potražiti i pomoću zamjenskih znakova u Excelu.
Nadamo se da je ovaj članak o tome kako pronaći više instanci vrijednosti u Excelu objašnjen. Ovdje pronađite više članaka o izračunavanju vrijednosti i srodnih Excel formula. Ako vam se sviđaju naši blogovi, podijelite ih sa svojim prijateljima na Facebooku. Također nas možete pratiti na Twitteru i Facebooku. Voljeli bismo čuti vaše mišljenje, javite nam kako možemo poboljšati, nadopuniti ili inovirati svoj rad i učiniti ga boljim za vas. Pišite nam na web stranici e -pošte.
Koristite VLOOKUP iz dvije ili više tablica za pretraživanje | Za pretraživanje iz više tablica možemo uzeti IFERROR pristup. Za pretraživanje iz više tablica pogreška se uzima kao prekidač za sljedeću tablicu. Druga metoda može biti pristup If.
Kako izvršiti pretraživanje osjetljivo na velika i mala slova u Excelu | excelova funkcija VLOOKUP ne razlikuje velika i mala slova i vratit će prvu usklađenu vrijednost s popisa. INDEX-MATCH nije iznimka, ali se može izmijeniti kako bi bila osjetljiva na velika i mala slova. Da vidimo kako…
Potražite često pojavljujući tekst s kriterijima u Excelu | Pretraživanje se najčešće pojavljuje u tekstu u rasponu koji koristimo INDEX-MATCH s MODE funkcijom. Evo metode.
Popularni članci:
Kako koristiti IF funkciju u Excelu : IF izraz u Excelu provjerava uvjet i vraća određenu vrijednost ako je uvjet TRUE ili vraća drugu određenu vrijednost ako je FALSE.
Kako koristiti funkciju VLOOKUP u Excelu : Ovo je jedna od najčešće korištenih i popularnih funkcija programa Excel koja se koristi za traženje vrijednosti iz različitih raspona i listova.
Kako koristiti funkciju SUMIF u Excelu : Ovo je još jedna bitna funkcija nadzorne ploče. To vam pomaže pri zbrajanju vrijednosti u posebnim uvjetima.
Kako koristiti funkciju COUNTIF u Excelu : Brojte vrijednosti s uvjetima pomoću ove nevjerojatne funkcije. Ne morate filtrirati svoje podatke da biste brojali određene vrijednosti. Funkcija Countif bitna je za pripremu vaše nadzorne ploče.