6 Formula za pretraživanje u Excelu

Svi znamo slavnu zvijezdu Excel funkcija VLOOKUP. Obično se koristi za traženje vrijednosti s jedinstvenim ID -om. No ovo nije jedina funkcija koja se može koristiti za traženje vrijednosti u Excelu. Postoje mnoge druge funkcije i formule koje se mogu koristiti za traženje vrijednosti. U ovom članku upoznat ću vas sa svim ovim funkcijama i formulama pretraživanja programa Excel. Neki su čak i bolji od funkcije VLOOKUP u Excelu. Dakle, pročitajte do kraja.

1. Excel VLOOKUP funkcija

Prva excelova funkcija pretraživanja je naravno funkcija VLOOKUP. Ova je funkcija poznata s razlogom. Ovu funkciju možemo koristiti za više od pretraživanja. No, osnovni zadatak ove funkcije je traženje vrijednosti u tablici, slijeva nadesno.

Sintaksa funkcije VLOOKUP:

= VLOOKUP (lookup_value, stol_pored, broj_indeksa_broj, [raspon_lookup])

Lookup_value: Vrijednost po kojoj želite pretraživati ​​u prvom stupcu polja tablice.

Polje_tablice: Tablica u kojoj želite tražiti/pretraživati

broj_indeksa_broj: Broj stupca u nizu tablica iz kojeg želite dohvatiti rezultate.

[range_lookup]: FALSE ako želite tražiti točnu vrijednost, TRUE ako želite približno podudaranje.

Prednosti VLOOKUP -a:

• Jednostavan za korištenje.
• Brzo
• Višestruka upotreba
• Najbolje za traženje vrijednosti u okomitom redoslijedu.

Nedostaci:

• Koristi se samo za vertikalno pretraživanje
• Vraća samo prvu usklađenu vrijednost.
• Statičan do upotrebe s funkcijom MATCH.
• Nije moguće pronaći vrijednosti s lijeve strane vrijednosti traženja.

Ovdje možete detaljno pročitati o ovoj formuli za pretraživanje programa Excel.

2. Excel HLOOKUP funkcija

Funkcija HLOOKUP je dio funkcije VLOOKUP koji nedostaje. Funkcija HLOOKUP koristi se za horizontalno traženje vrijednosti. Drugim riječima, kada želite pronaći vrijednost u Excelu podudaranjem vrijednosti u stupcima i dobiti vrijednosti iz redaka, tada koristimo funkciju HLOOKUP. To je upravo suprotnost funkcije VLOOKUP.

Sintaksa HLOOKUP -a

=HLOOKUP(vrijednost pretraživanja, niz tablice, broj indeksa retka, [raspon_lookup])

• tražilica vrijednost: Vrijednost koju tražite.
• Niz tablica: Tablica u kojoj tražite vrijednost.
• Broj indeksa retka: Broj retka u tablici iz kojeg želite dohvatiti podatke.
• [range_lookup]: vrsta podudaranja. 0 za točno podudaranje i 1 za približno podudaranje.

Prednosti funkcije HLOOKUP:

• Može vodoravno tražiti vrijednosti.
• Jednostavan za korištenje.
• Višestruka upotreba
• Brzo

Nedostaci:

• Koristi se samo za vodoravno pretraživanje
• Vraća samo prvu usklađenu vrijednost.
• Statičan do upotrebe s funkcijom MATCH.
• Nije moguće pronaći vrijednosti iznad vrijednosti traženja u tablici.

Ovdje možete saznati o ovoj funkciji pretraživanja programa Excel.

3. Formula za pretraživanje INDEX-MATCH

Tamo gdje VLOOKUP i HLOOKUP ne mogu doći, ova formula može doseći. Ovo je najbolja formula za pretraživanje u Excelu do Excela 2016 (XLOOKUP je na putu).

Opća formula INDEX MATCH -a

= INDEX (Rezultat_Raspon, MATCH (traženje_vrijednost, raspon pretraživanja, 0))

Raspon_sultata: To je raspon raspona odakle želite dohvatiti vrijednost.

Lookup_value: To je vrijednost kojoj želite odgovarati.

Raspon_potraživanja:To je raspon u kojem želite uskladiti vrijednost pretraživanja.

Prednosti formule za pretraživanje INDEX-MATCH:

• Može tražiti u četiri smjera. Može tražiti vrijednosti lijevo i gore od tražene vrijednosti.
• Dinamičan.
• Nema potrebe za definiranjem indeksa retka ili stupca.

Nedostaci:

• Novim korisnicima može biti teško.
• Koristi dvije Excel funkcije u kombinaciji. Korisnici trebaju razumjeti rad funkcije INDEX i MATCH.

Ovdje možete saznati o ovoj formuli.

4: Formula za pretraživanje Excel OFFSET-MATCH

Ovo je još jedna formula koja se može koristiti za dinamičko traženje vrijednosti. Ova formula za pretraživanje programa Excel koristi funkciju OFFSET kao funkciju sidrenja i MATCH kao funkciju uvlačenja. Pomoću ove formule možemo dinamički dohvatiti vrijednosti iz tablice traženjem u redovima i stupcima.

Opća formula,

= OFFSET (StartCell, MATCH (RowLookupValue, RowLookupRange, 0), MATCH (ColLookupValue, ColLookupRange, 0))

StartCell:Ovo je početna ćelija tablice za pretraživanje. Recimo, ako želite tražiti u rasponu A2: A10, tada će StartCell biti A1.

RowLookupValue: Ovo je vrijednost pretraživanja koju želite pronaći u retcima ispodStartCell.

RowLookupRange:Ovo je raspon u kojem želite pronaći RowLookupValue. To je raspon u nastavku StartCell (A2: A10).

ColLookupValue: Ovo je vrijednost pretraživanja koju želite pronaći u stupcima (zaglavlja).

Raspon ColLookup:Ovo je raspon u kojem želite pronaći ColLookupValue. To je raspon s desne strane StartCell -a (poput B1: D1).

Prednosti ove tehnike pretraživanja programa Excel:

• Brzo
• Može tražiti vodoravno i okomito.
• Dinamičan

Nedostaci:

• Složen nekim ljudima.
• Morate razumjeti rad OFFSET funkcije i MATCH funkcije.

Ovdje možete detaljno naučiti o ovoj formuli za pretraživanje.

5: Excel LOOKUP Formula Više vrijednosti

Sve gore navedene formule za pretraživanje vraćaju prvu pronađenu vrijednost iz niza. Ako ima više utakmica, neće vratiti ostale utakmice. U tom slučaju ova formula stupa na snagu kako bi spasila stvar. Ova formula vraća sve podudarne vrijednosti s popisa, umjesto samo prvog podudaranja.

Ova formula koristi funkcije INDEX, ROW i IF kao glavne funkcije. IFERROR funkcija može se opcionalno koristiti za rješavanje pogrešaka.

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 pritisnite CTRL+SHIFT+ENTER kako biste je pretvorili u formulu niza.

Kao što možete vidjeti na gifu, vraća sva podudaranja iz tablice programa Excel.

Prednosti:

• Vraća s više podudaranih vrijednosti iz tablice Excel.
• Dinamičan

Nedostaci:

• To je previše složeno da bi ga novi korisnik razumio.
• Koristi formulu polja
• Potrebno je definirati mogući broj izlaza i primijeniti ovu formulu kao formulu polja s više ćelija (nije u Excel 2019 i 365).
• Usporiti.

Ovdje možete detaljno naučiti o ovoj formuli.

6: VLOOKUP-CHOOSE Potraži Excel formulu

Dakle, većina ljudi kaže da nije moguće pronaći vrijednosti s lijeve strane tražene vrijednosti u Excelu pomoću funkcije VLOOKUP. Pa, žao mi je što to moram reći, ali griješe. Možemo pretraživati ​​lijevo od vrijednosti traženja u Excelu pomoću funkcije VLOOKUP uz pomoć funkcije IZABERI.

Opća formula:

= VLOOKUP (lookup_value, CHOOSE ({1, 2}, lookup_range, req_range), 2, 0)

lookup_value : vrijednost koju treba tražiti

opseg_potraživanja : range, gdje tražiti lookup_value

req_range : raspon, gdje je potrebna odgovarajuća vrijednost

2 : drugi stupac, broj koji predstavlja req_range

0 : potražite točno podudaranje

U ovoj formuli u osnovi stvaramo virtualnu tablicu unutar formule pomoću funkcije CHOOSE. Funkcija IZABERI stvara tablicu od dva stupca. Prvi stupac sadrži raspon pretraživanja, a drugi stupac sadrži raspon rezultata.

Prednosti formule za pretraživanje VLOOKUP-CHOOSE:

• Možete tražiti lijevo od vrijednosti traženja
• Brzo
• Lako

Nedostaci:

• Funkcija IZBOR rijetko se koristi. Korisnici moraju razumjeti njegov rad.

Ovdje možete saznati o ovoj formuli za pretraživanje.

Pa da, dečki, ovo su različite funkcije pretraživanja i formule. To nije sve. U Excelu može postojati još mnogo formula za pretraživanje koje se mogu stvoriti pomoću različitih kombinacija Excelovih formula. Ako imate neke posebne tehnike pretraživanja, podijelite ih u odjeljku komentara u nastavku. Uključit ćemo ga u naš članak s vašim imenom.

Nadam se da je bilo korisno i informativno. Ako imate bilo kakvih nedoumica u vezi s ovim člankom, pitajte me u donjem odjeljku komentara.

10+ novih funkcija u Excelu 2019 i 365: Iako su funkcije dostupne u Excelu 2016 i starije dovoljne za razradu bilo koje vrste izračuna i automatizacije, formule ponekad postaju zeznute. Ovakve manje, ali važne stvari riješene su u Excelima 2019 i 365.

17 stvari o Excelu VLOOKUP: VLOOKUP jednostavno nije formula za pretraživanje, to je više od toga. VLOOKUP ima mnoge druge sposobnosti i može se koristiti u više svrha. U ovom članku istražujemo sve moguće uporabe funkcije VLOOKUP.

10+ kreativnih naprednih Excelovih tablica koje će uzdrmati vašu nadzornu ploču: Excel je moćan alat za vizualizaciju podataka koji se može koristiti za stvaranje zadivljujućih grafikona u Excelu. Ovih 15 naprednih Excel tablica može se koristiti za očaranje vaših kolega i šefova.

4 grafikona postignuća Creative Target Vs postignuća u Excelu: Grafikoni cilja i postignuća najosnovniji su i najvažniji grafikoni u bilo kojem poslu. Zato ih je bolje postaviti na najkreativniji mogući način. Ove 4 kreativne ljestvice mogu učiniti da vaše nadzorne ploče uzdrmaju prezentaciju.

Popularni članci:

50 Excel prečaca za povećanje vaše produktivnosti | Budite brži u izvršavanju svojih zadataka. Ovih 50 prečaca učinit će da radite još brže na Excelu.

Kako koristiti Excel VLOOKUP funkciju| 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 Excel COUNTIF funkcija| 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.

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.