6 formule za iskanje v Excelu

Vsi poznamo slavno zvezdo Excelovih funkcij VLOOKUP. Običajno se uporablja za iskanje vrednosti z edinstvenim ID -jem. Vendar to ni edina funkcija, ki jo je mogoče uporabiti za iskanje vrednosti v Excelu. Obstaja veliko drugih funkcij in formul, ki jih je mogoče uporabiti za iskanje vrednosti. V tem članku vam bom predstavil vse te funkcije in formule iskanja v Excelu. Nekateri so celo boljši od funkcije VLOOKUP v Excelu. Torej, preberi do konca.

1. Funkcija Excel VLOOKUP

Prva funkcija iskanja v Excelu je seveda funkcija VLOOKUP. Ta funkcija je znana z razlogom. S to funkcijo lahko naredimo več kot le iskanje. Toda osnovna naloga te funkcije je iskanje vrednosti v tabeli od leve proti desni.

Sintaksa funkcije VLOOKUP:

= VLOOKUP (lookup_value, matrika_maza, col_index_number, [range_lookup])

Lookup_value: Vrednost, po kateri želite iskati v prvem stolpcu matrike tabel.

Matrika_maza: Tabela, v kateri želite poiskati/iskati

col_index_number: Številka stolpca v matriki tabel, iz katere želite pridobiti rezultate.

[range_lookup]: FALSE, če želite iskati natančno vrednost, TRUE, če želite približno ujemanje.

Prednosti VLOOKUP -a:

• Enostaven za uporabo.
• Hitro
• Večkratna uporaba
• Najboljše za iskanje vrednosti v navpičnem vrstnem redu.

Slabosti:

• Uporablja se samo za navpično iskanje
• Vrne samo prvo ujemajočo se vrednost.
• Statično do uporabe s funkcijo MATCH.
• Vrednosti ni mogoče poiskati z leve strani iskalne vrednosti.

Tukaj lahko podrobno preberete o tej formuli za iskanje v Excelu.

2. Funkcija Excel HLOOKUP

Funkcija HLOOKUP je manjkajoči del funkcije VLOOKUP. Funkcija HLOOKUP se uporablja za vodoravno iskanje vrednosti. Z drugimi besedami, če želite poiskati vrednost v Excelu z ujemanjem vrednosti v stolpcih in pridobiti vrednosti iz vrstic, potem uporabimo funkcijo HLOOKUP. To je ravno nasprotje funkcije VLOOKUP.

Sintaksa HLOOKUP

=HLOOKUP(iskalna vrednost, matrika tabel, številka indeksa vrstice, [obseg_ogled])

• iskalna vrednost: Vrednost, ki jo iščete.
• Niz tabel: Tabela, v kateri iščete vrednost.
• Številka indeksa vrstice: Številka vrstice v tabeli, iz katere želite pridobiti podatke.
• [range_lookup]: je vrsta ujemanja. 0 za natančno ujemanje in 1 za približno ujemanje.

Prednosti funkcije HLOOKUP:

• Vodoravno lahko išče vrednosti.
• Enostaven za uporabo.
• Večkratna uporaba
• Hitro

Slabosti:

• Uporablja se samo za vodoravno iskanje
• Vrne samo prvo ujemajočo se vrednost.
• Statično, dokler se ne uporabi s funkcijo MATCH.
• Vrednosti nad iskalnimi vrednostmi v tabeli ni mogoče poiskati.

Tukaj lahko izveste o tej funkciji iskanja v Excelu.

3. Formula iskanja INDEX-MATCH

Kjer VLOOKUP in HLOOKUP ne moreta doseči, lahko pride do te formule. To je najboljša iskalna formula v Excelu do Excela 2016 (XLOOKUP je na poti).

Splošna formula INDEX MATCH

= INDEX (Result_Range, MATCH (lookup_value, lookup range, 0))

Result_Range: To je obseg območja, od koder želite pridobiti vrednost.

Lookup_value: To je vrednost, ki ji želite ustrezati.

Lookup_Range:To je obseg, v katerem želite ujemati iskalno vrednost.

Prednosti iskalne formule INDEX-MATCH:

• Lahko išče v štirih smereh. Lahko poišče vrednosti levo in navzgor od iskalne vrednosti.
• Dinamično.
• Indeksa vrstice ali stolpca ni treba definirati.

Slabosti:

• Za nove uporabnike je lahko težko.
• Uporablja dve funkciji Excel v kombinaciji. Uporabniki morajo razumeti delovanje funkcij INDEX in MATCH.

Tu lahko izveste o tej formuli.

4: Formula iskanja Excel OFFSET-MATCH

To je še ena formula, ki jo je mogoče uporabiti za dinamično iskanje vrednosti. Ta iskalna formula Excel uporablja funkcijo OFFSET kot sidrno funkcijo in MATCH kot podajalno funkcijo. S to formulo lahko dinamično pridobimo vrednosti iz tabele tako, da poiščemo vrstice in stolpce.

Splošna formula,

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

StartCell:To je začetna celica iskalne tabele. Recimo, če želite poiskati v območju A2: A10, bo StartCell A1.

RowLookupValue: To je iskalna vrednost, ki jo želite najti v vrsticah podStartCell.

RowLookupRange:To je obseg, v katerem želite poiskati vrednost RowLookupValue. To je spodnji razpon StartCell (A2: A10).

ColLookupValue: To je iskalna vrednost, ki jo želite najti v stolpcih (glavi).

ColLookupRange:To je obseg, v katerem želite poiskati vrednost ColLookupValue. To je obseg na desni strani StartCell (na primer B1: D1).

Prednosti te tehnike iskanja v Excelu:

• Hitro
• Lahko išče vodoravno in navpično.
• Dinamično

Slabosti:

• Za nekatere ljudi je zapleteno.
• Razumeti morate delovanje funkcije OFFSET in funkcije MATCH.

Tukaj lahko podrobno izveste o tej formuli iskanja.

5: Več vrednosti formule Excel LOOKUP

Vse zgornje iskalne formule vrnejo prvo najdeno vrednost iz matrike. Če je več tekem, drugih tekem ne vrnejo. V tem primeru ta formula začne delovati, da reši dan. Ta formula vrne vse ujemajoče se vrednosti s seznama, namesto samo prvega ujemanja.

Ta formula uporablja funkcije INDEX, ROW in IF kot glavne funkcije. Funkcijo IFERROR lahko po želji uporabite za odpravljanje napak.

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 pritisnite CTRL+SHIFT+ENTER, da postane formula matrike.

Kot lahko vidite v gifu, vrne vsa ujemanja iz tabele Excel.

Prednosti:

• Vrne z več ujemajočimi se vrednostmi iz tabele Excel.
• Dinamično

Slabosti:

• Preveč je zapleteno, da bi ga novi uporabnik razumel.
• Uporablja formulo matrike
• Treba je opredeliti možno število izhodov in uporabiti to formulo kot formulo z več celicami (ne v Excelih 2019 in 365).
• Počasi.

Tukaj lahko podrobno izveste o tej formuli.

6: Formula za iskanje VLOOKUP-CHOOSE Iskanje Excel

Zato večina ljudi pravi, da ni mogoče poiskati vrednosti z leve strani iskalne vrednosti v Excelu s funkcijo VLOOKUP. No, žal mi je, da so, ampak motijo ​​se. Levo od iskalne vrednosti v Excelu lahko poiščemo s funkcijo VLOOKUP s pomočjo funkcije CHOOSE.

Splošna formula:

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

lookup_value : vrednost, ki jo je treba iskati

lookup_range : range, kje iskati lookup_value

req_range : obseg, kjer je potrebna ustrezna vrednost

2 : drugi stolpec, število predstavlja obseg req_range

0 : poiščite natančno ujemanje

V tej formuli v osnovi ustvarimo navidezno tabelo znotraj formule s funkcijo IZBERI. Funkcija IZBERI ustvari tabelo z dvema stolpcema. Prvi stolpec vsebuje obseg iskanja, drugi stolpec pa obseg rezultatov.

Prednosti iskalne formule VLOOKUP-CHOOSE:

• Lahko poiščete levo od vrednosti iskanja
• Hitro
• Enostavno

Slabosti:

• Funkcija IZBERI se redko uporablja. Uporabniki morajo razumeti njegovo delovanje.

Tu lahko izveste o tej formuli iskanja.

Torej, fantje, to so različne funkcije iskanja in formule. To še ni vse. V Excelu je lahko veliko več iskalnih formul, ki jih lahko ustvarite z različnimi kombinacijami Excelovih formul. Če imate kakšne posebne tehnike iskanja, jih delite v spodnjem razdelku za komentarje. To bomo vključili v naš članek z vašim imenom.

Upam, da je bilo koristno in poučno. Če dvomite o tem članku, me vprašajte v spodnjem razdelku s komentarji.

10+ novih funkcij v Excelih 2019 in 365: Čeprav so funkcije, ki so na voljo v Excelu 2016 in starejše, dovolj za izvajanje kakršnih koli izračunov in avtomatizacije, so včasih formule zapletene. Te vrste manjših, a pomembnih stvari so rešene v Excelih 2019 in 365.

17 stvari o Excelu VLOOKUP: VLOOKUP preprosto ni iskalna formula, je več kot to. VLOOKUP ima številne druge sposobnosti in se lahko uporablja za več namenov. V tem članku raziskujemo vse možne uporabe funkcije VLOOKUP.

10+ ustvarjalnih naprednih grafikonov Excel, ki bodo razširili vašo nadzorno ploščo: Excel je močno orodje za vizualizacijo podatkov, ki ga lahko uporabite za ustvarjanje osupljivih grafikonov v Excelu. Teh 15 naprednih lestvic excel lahko uporabite za očaranje svojih kolegov in šefov.

4 grafikoni dosežkov Creative Targe Vs in Excel: Lestvice ciljev in dosežkov so najosnovnejše in najpomembnejše lestvice v vsakem poslu. Zato jih je bolje postaviti na čim bolj ustvarjalen način. Ti 4 ustvarjalni grafikoni lahko vaše nadzorne plošče pretvorijo v predstavitev.

Priljubljeni članki:

50 bližnjic v Excelu za večjo produktivnost | Poskrbite, da bo vaša naloga hitrejša. S temi 50 bližnjicami boste še hitreje delali v Excelu.

Kako uporabljati funkcijo Excel VLOOKUP| To je ena najpogosteje uporabljenih in priljubljenih funkcij programa Excel, ki se uporablja za iskanje vrednosti iz različnih obsegov in listov.

Kako uporabljati Excel COUNTIF funkcija| Š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.

Kako uporabljati funkcijo SUMIF v Excelu | To je še ena bistvena funkcija armaturne plošče. To vam pomaga povzeti vrednosti za posebne pogoje.