Kako narediti iskalno vrstico v excelu. Poiščite katero koli besedo s seznama v celici. Funkcija Searchpos v excelu - primer excelove funkcije searchpos z nadomestnimi znaki

Recimo, da morate poiskati telefonsko številko zaposlenega z njegovo številko in tudi pravilno oceniti razmerje provizije za znesek prodaje. Iščete podatke, da hitro in učinkovito najdete določene podatke na seznamu in samodejno preverite, ali se podatki pravilno uporabljajo. Ko si ogledate podatke, lahko izvedete izračune in prikažete rezultate z navedbo vrnjenih vrednosti. Obstaja več načinov za iskanje vrednosti na seznamu podatkov in prikaz rezultatov.

V tem članku

Poiščite vrednosti na seznamu navpično z natančnim ujemanjem

Za izvedbo te naloge lahko uporabite funkcijo VLOOKUP ali kombinacijo funkcij INDEX in MATCH.

Primeri uporabe funkcije VLOOKUP

funkcija VLOOKUP.

Primeri INDEXov in MATCHES

Kaj to pomeni:

=INDEX(vrniti morate vrednost iz C2:C10, ki se bo ujemala z MATCH(prva vrednost "Cabbage" v matriki B2:B10))

Formula išče prvo vrednost v celici C2:C10, ki ustreza zelje(v B7) in vrne vrednost v C7 ( 100 ) - prva ustrezna vrednost zelje.

Za več informacij glejte funkcijo INDEX in funkcijo MATCH.

Poiščite vrednosti na seznamu navpično po približnem ujemanju

Če želite to narediti, uporabite funkcijo VLOOKUP.

Pomembno:

V zgornjem primeru funkcija VLOOKUP išče ime študenta, ki ima 6 zamud v obsegu A2:B7. V tabeli ni vnosa za 6 zamude, zato funkcija VLOOKUP poišče naslednje največje ujemanje pod 6 in najde vrednost 5, povezano z imenom Dave, in se zato vrne Dave.

Za več informacij glejte funkcijo VLOOKUP.

Iskanje navpičnih vrednosti na seznamu neznane velikosti z natančnim ujemanjem

Za izvedbo te naloge uporabite funkciji OFFSET in MATCH.

Opomba: Ta pristop se uporablja, če so podatki v zunanjem obsegu podatkov, ki ga posodabljate vsak dan. Veste, da ima stolpec B ceno, vendar ne veste, koliko vrstic podatkov strežnik vrne, in prvi stolpec ni razvrščen po abecedi.

C1 je zgornja leva celica obsega (imenovana tudi začetna celica).

Match("pomaranče"; C2: C7; 0) išče oranžno barvo v območju C2:C7. Začetne celice ne smete vključiti v obseg.

1 - število stolpcev na desni strani začetne celice, za katere je treba vrniti vrnjeno vrednost. V našem primeru je vrnjena vrednost v stolpcu D, Prodaja.

Poiščite vrednosti na seznamu vodoravno z natančnim ujemanjem

Za izvedbo te naloge se uporablja funkcija GLOOKUP. Spodaj je primer.

Funkcija LOOKUP išče stolpec Prodaja in vrne vrednost iz vrstice 5 v podanem območju.

Za več informacij glejte funkcije LOOKUP.

Iskanje vrednosti na seznamu vodoravno z uporabo približnega ujemanja

Za izvedbo te naloge se uporablja funkcija GLOOKUP.

Pomembno: Prepričajte se, da so vrednosti v prvi vrstici razvrščene v naraščajočem vrstnem redu.

V zgornjem primeru funkcija GLOOKUP išče vrednost 11000 v vrstici 3 znotraj podanega obsega. Ne najde 11000 in zato išče naslednje najvišjo vrednost ki je manjši od 1100 in vrne število 10543.

Za več informacij glejte funkcije LOOKUP.

Ustvarite formulo za iskanje s čarovnikom za iskanje (samo Excel 2007)

Opomba: Dodatek Čarovnik za iskanje je bil ukinjen v Excelu 2010. Ti funkcionalnost nadomeščen s čarovnikom za funkcije in razpoložljivimi iskalnimi in referenčnimi (referenčnimi) funkcijami.

V Excelu 2007 čarovnik za iskanje ustvari formulo za iskanje na podlagi podatkov delovnega lista, ki ima naslove vrstic in stolpcev. Čarovnik za iskanje vam pomaga najti druge vrednosti v vrstici, ko poznate vrednost v enem stolpcu, in obratno. Čarovnik za iskanje uporablja indeks in MATCH v formulah, ki jih ustvari.

Datum: 3. januar 2016 Kategorija:

Iskanje znakov v nizu je najpomembnejša naloga, ki vam omogoča avtomatizacijo dela z besedilom. Na primer, olajša, ko položaj ciljne črte ni znan.

Orodja za iskanje Excel sem se dotaknil v , tukaj pa si bomo ogledali funkcije, ki iščejo besedilo v nizu in vrnejo njegov začetni položaj.

Za iskanje besedila v Microsoft Excel obstajata dve funkciji:

NAJTI() . Ta funkcija ima 2 zahtevana argumenta in enega neobveznega:
- Iskanje besedila– zaporedje znakov, ki jih je treba najti. To je obvezen argument
- Izvirno besedilo– niz, v katerem se izvaja iskanje. Potreben argument
- Začetni položaj– zaporedna številka znaka v izvornem besedilu, od katerega se začne iskanje. Izbirni argument. Če ni določen, se iskanje začne od prvega znaka

Funkcija FIND upošteva in ne podpira uporabe nadomestnih znakov(preberite več o njih).

Funkcija FIND v Excelu

ISKANJE(Iskanje besedila; Izvirno besedilo; [Začetni položaj]) ima podobno sintakso in argumente. V tem se razlikuje neobčutljivo na velike in male črke in podpira uporabo nadomestnih znakov.

Funkcija SEARCH v Excelu

Kot lahko vidite, je ta funkcija dala drugačen rezultat, ker iskanje ni razlikovalo med velikimi in malimi črkami.

Kaj so nadomestni znaki, se sprašujete? To so posebni znaki, ki vam omogočajo, da preskočite neznani del besedila pri iskanju:

? – kateri koli simbol
* – poljubno zaporedje znakov

Če je vprašaj ali zvezdica del iskalnega niza in ne nadomestni znak, pred njim postavite tildo (~).

Zapišimo naslednjo formulo: =ISKANJE(“??chn”,A1,1). Funkcija bo iskala niz poljubnih dveh znakov, ki jima sledita črki »chn«. Iskanje se ujema z delom besede "Tabular" v vzorčnem besedilu, tj. kombinacija znakov "osebno". Rezultat funkcije bo vrednost "4".

Nadomestni znaki v Excelu

Zdaj pa napišimo formulo =ISKANJE(“p*s”,A2,1) . Funkcija išče kombinacijo “p” + poljubno število poljubnih znakov + “s”. V primeru je to del besede "procesor". Rezultat funkcije je 11.

Nadomestni znak z zvezdico v Excelu

Če imate funkcije iskanja, lažje sledite položaju želenega besedila, zato ga je lažje zamenjati. Bodite prepričani, da vadite njihovo uporabo. Moja post-vadnica o besedilnih funkcijah vam bo pomagala pri tem.

In naslednji članek bo posvečen temu. Pomembna tema, ki jo je vsekakor treba preučiti!

Postavite vprašanja v komentarjih in se naročite na posodobitve, da ne zamudite ničesar!

Ta članek opisuje sintakso formule in uporabo funkcije ISKANJE in ISKANJE v programu Microsoft Excel.

Opis

Funkcije ISKANJE IN ISKANJE najti en besedilni niz znotraj drugega in vrniti začetni položaj prvega besedilnega niza (šteto od prvega znaka drugega besedilnega niza). Če želite na primer najti položaj črke "n" v besedi "tiskalnik", lahko uporabite naslednjo funkcijo:

SEARCH("n","tiskalnik")

Ta funkcija se vrne 4 , ker je "n" četrti znak v besedi "tiskalnik".

Besede lahko najdete tudi v drugih besedah. Na primer funkcija

SEARCH("baza","baza podatkov")

vrača 5 , saj se beseda "baza" začne s petim znakom besede "baza podatkov". Funkcije se lahko uporabljajo ISKANJE in ISKANJE za določitev položaja znaka ali besedilnega niza v drugem besedilnem nizu in nato vrnitev besedila s pomočjo funkcij PSTR in PSTRB ali ga nadomestite s funkcijami ZAMENJATI in ZAMENJATI. Te funkcije so prikazane v tem članku.

Pomembno:

Te funkcije morda niso na voljo v vseh jezikih.

Funkcija SEARCH šteje dva bajta na znak le, če je privzeti jezik jezik, ki podpira BDC. V nasprotnem primeru funkcija SEARCH deluje enako kot funkcija SEARCH in šteje en bajt na znak.

Jeziki, ki podpirajo BDCS, vključujejo japonščino, poenostavljeno kitajščino, tradicionalno kitajščino in korejščino.

Sintaksa

SEARCH(iskano_besedilo, ogledano_besedilo, [začetni_položaj])

SEARCHB(iskano_besedilo, ogledano_besedilo, [začetni_položaj])

Spodaj so opisani argumenti funkcij SEARCH in SEARCHB.

Search_text Obvezno. Besedilo, ki ga želite najti.

Ogledano_besedilo Obvezno. Besedilo, v katerem najdemo vrednost argumenta search_text .

Začetni_položaj Neobvezno. Številka znaka v argumentu ogledano_besedilo , kjer bi morali začeti iskanje.

Komentiraj

Funkcije ISKANJE in ISKANJE niso občutljivi na velike in male črke. Če morate upoštevati velike in male črke, uporabite funkcije NAJTI in NIGHTIB.

V argumentu search_text Uporabite lahko nadomestne znake: vprašaj ( ? ) in zvezdica ( * ). Vprašaj ustreza kateremu koli znaku, zvezdica pa kateremu koli zaporedju znakov. Če želite najti vprašaj ali zvezdico, vnesite tildo ( ~ ).

Če je vrednost argumenta search_text ni najden, #VALUE! vrnjena vrednost napake.

Če argument začetni_položaj je izpuščen, potem je nastavljen na 1.

če Začetni_položaj ne več kot 0 ali več kot dolžina argumenta ogledano_besedilo , #VREDNOST! vrnjena vrednost napake.

Prepir začetni_položaj lahko uporabite za preskok določenega števila znakov. Predpostavimo, da funkcija ISKANJE je treba uporabiti za delo z besedilnim nizom "MDS0093.Moška oblačila". Če želite najti prvo pojavitev "M" v opisnem delu besedilnega niza, nastavite argument na začetni_položaj vrednost 8, tako da se iskanje ne izvaja v tistem delu besedila, ki je serijska številka(v tem primeru - "MDS0093"). funkcija ISKANJE začne iskanje od osmega znaka, najde znak, naveden v argumentu search_text , na naslednjem mestu in vrne številko 9. Funkcija ISKANJE vedno vrne številko znaka, štetje od začetka besedila, ki si ga ogledujete , vključno z znaki, ki so preskočeni, če je vrednost argumenta začetni_položaj več kot 1.

Primeri

Kopirajte vzorčne podatke iz naslednje tabele in jih prilepite v celico A1 novega Excelovega delovnega lista. Če želite prikazati rezultate formul, jih izberite in pritisnite F2, nato pritisnite Enter. Po potrebi spremenite širino stolpcev, da vidite vse podatke.

podatki

Prihodek: marža

"Šef" tukaj.
Formula	Opis	Rezultat
SEARCH("in";A2;6)	Položaj prvega "in" v vrstici celice A2, ki se začne pri šestem znaku.
ISKANJE(A4;A3)	Začetni položaj vrstice »marža« (iskana vrstica je v celici A4) v vrstici »Prihodki: marža« (iskana celica je A3).
ZAMENJAJ(A3,ISK(A4,A3),6,"glasnost")	Zamenja besedo "rob" z besedo "volumen" tako, da poišče besedo "rob" v celici A3 in zamenja ta znak in naslednjih pet znakov z besedilnim nizom "volumen".	Prihodek: obseg
PSTR(A3;ISKANJE(" ";A3)+1,4)	Vrne prve štiri znake, ki sledijo prvemu presledku v vrstici Prihodek: Marža (celica A3).
ISKANJE("""";A5)	Prvi položaj dvojni narekovaji(") v celici A5.
PSTR(A5;ISKANJE("""";A5)+1;ISKANJE("""";A5;ISKANJE("""";A5)+1)-ISKANJE("""";A5)-1)	Vrne samo besedilo v dvojnih narekovajih iz celice A5.

V tem članku

Poiščite vrednosti na seznamu navpično z natančnim ujemanjem

Za izvedbo te naloge lahko uporabite funkcijo VLOOKUP ali kombinacijo funkcij INDEX in MATCH.

Primeri uporabe funkcije VLOOKUP

funkcija VLOOKUP.

Primeri INDEXov in MATCHES

Kaj to pomeni:

=INDEX(vrniti morate vrednost iz C2:C10, ki se bo ujemala z MATCH(prva vrednost "Cabbage" v matriki B2:B10))

Formula išče prvo vrednost v celici C2:C10, ki ustreza zelje(v B7) in vrne vrednost v C7 ( 100 ) - prva ustrezna vrednost zelje.

Za več informacij glejte funkcijo INDEX in funkcijo MATCH.

Poiščite vrednosti na seznamu navpično po približnem ujemanju

Če želite to narediti, uporabite funkcijo VLOOKUP.

Pomembno:

Za več informacij glejte funkcijo VLOOKUP.

Iskanje navpičnih vrednosti na seznamu neznane velikosti z natančnim ujemanjem

Za izvedbo te naloge uporabite funkciji OFFSET in MATCH.

C1 je zgornja leva celica obsega (imenovana tudi začetna celica).

Match("pomaranče"; C2: C7; 0) išče oranžno barvo v območju C2:C7. Začetne celice ne smete vključiti v obseg.

1 - število stolpcev na desni strani začetne celice, za katere je treba vrniti vrnjeno vrednost. V našem primeru je vrnjena vrednost v stolpcu D, Prodaja.

Poiščite vrednosti na seznamu vodoravno z natančnim ujemanjem

Za izvedbo te naloge se uporablja funkcija GLOOKUP. Spodaj je primer.

Funkcija LOOKUP išče stolpec Prodaja in vrne vrednost iz vrstice 5 v podanem območju.

Za več informacij glejte funkcije LOOKUP.

Iskanje vrednosti na seznamu vodoravno z uporabo približnega ujemanja

Za izvedbo te naloge se uporablja funkcija GLOOKUP.

Pomembno: Prepričajte se, da so vrednosti v prvi vrstici razvrščene v naraščajočem vrstnem redu.

V zgornjem primeru funkcija GLOOKUP išče vrednost 11000 v vrstici 3 znotraj podanega obsega. Ne najde 11000 in zato išče naslednjo največjo vrednost, ki je manjša od 1100, in vrne število 10543.

Za več informacij glejte funkcije LOOKUP.

Ustvarite formulo za iskanje s čarovnikom za iskanje (samo Excel 2007)

Opomba: Dodatek Čarovnik za iskanje je bil ukinjen v programu Excel 2010. To funkcionalnost je nadomestil čarovnik za funkcije ter razpoložljiva funkcija iskanja in sklicevanja (sklicevanja).

Vse najboljše prijateljice! In danes se boste naučili najti pravo besedo v Excelu. Delo z Excelom pogosto vključuje veliko količino informacij. V takih datotekah so razvite resnično ogromne tabele s tisočimi vrsticami, stolpci in tudi položaji. Iskanje kakršnih koli specifičnih vrednosti v tako velikem nizu je lahko včasih neverjetno težko.

To nalogo je mogoče poenostaviti. Če veste, kako najti zahtevano besedo, lahko preprosto krmarite po datoteki. Urejevalnik tabel ponuja več različic iskanja.

Kako najti pravo besedo po celicah v Excelu

Če želite prikazati naslove tistih celic, ki vsebujejo tisto, kar poskušate najti, sledite tem korakom:

Če ste uporabnik programa 2010, pojdite v meni, kliknite »Uredi« in nato »Najdi«.
Nato se odpre okno, v katerega morate vnesti iskalni izraz.
Program prejšnja različica ima ta gumb v meniju z imenom »Domov«, ki se nahaja na plošči za urejanje.
Podoben rezultat lahko dosežete v kateri koli različici s hkratno uporabo tipk Ctrl in F.
V polje vnesite frazo, iskane besede ali številke.
Če kliknete »Najdi vse«, boste sprožili iskanje po celotni datoteki. S klikom na “Naprej” jih bo program izbral po eno celico, ki se nahaja pod kazalcem celice datoteke.
Vredno je počakati, da se postopek zaključi. Poleg tega večji kot je dokument, več časa bo trajalo iskanje.
Prikaže se seznam rezultatov: imena in naslovi celic, ki vsebujejo ujemanja z določeno vrednostjo ali frazo.
S klikom na katerokoli vrstico bo ustrezna celica označena.
Za udobje lahko okno "raztegnete". Tako bo vidnih več vrstic.
Če želite razvrstiti podatke, morate klikniti imena stolpcev nad najdenimi rezultati. S klikom na “List” se vrstice razvrstijo po abecedi glede na ime lista, z izbiro “Vrednosti” pa se razvrstijo glede na vrednost. Mimogrede, te stolpce je mogoče tudi "raztegniti".

Iskalni parametri

Možno je postaviti svoje pogoje. Na primer, iskanje po več znakih. Tukaj je opisano, kako najti besedo, ki se je ne spomnite popolnoma:

Vnesete le del napisa. Uporabite lahko celo eno od črk - prikazana bodo vsa območja, kjer je na voljo.
Uporabite ikoni zvezdice in vprašaja. Sposobni so nadomestiti manjkajoče znake.
Vprašanje označuje eno manjkajočo pozicijo. Če na primer vnesete "Huh????", se bodo prikazale celice, ki vsebujejo besedo s petimi znaki, ki se začne z "A".
Zahvaljujoč zvezdici je mogoče zamenjati poljubno število znakov. Če želite najti vse vrednosti, ki vsebujejo koren "rast", morate začeti iskati po ključu "rast*".

Poleg tega lahko obiščete nastavitve:

V iskalnem polju kliknite »Možnosti«.
V razdelkih območja za ogled in iskanje boste morali natančno navesti, kje in na podlagi katerih kriterijev želite iskati ujemanja. Izberete lahko formule, različne opombe ali vrednosti.
Če želite razlikovati med malimi in velikimi črkami, morate potrditi polje »Match case«.
Če označite potrditveno polje »Celotna celica«, bodo rezultati prikazani v obliki celic, ki vsebujejo izključno določeno iskalni izraz, in nič drugega kot ona.

Nastavitve oblikovanja celic

Za iskanje vrednosti z določenim polnilom ali slogom uporabite nastavitve. Torej, če je beseda videti drugače kot ostalo besedilo, morate narediti naslednje:

V iskalnem polju kliknite na parametre in pritisnite gumb »Oblika«. Odpre se meni z več zavihki.
Določite lahko določeno pisavo, vrsto okvirja, barvo ozadja in obliko vhodnih podatkov. Sistem si bo ogledal tiste površine, ki ustrezajo podanim kriterijem.
Za prevzem informacij iz trenutne celice (označeno na ta trenutek), kliknite »Uporabi obliko te celice«. V tem primeru bo program našel vse vrednosti, ki imajo enako velikost in vrsto znakov, enako barvo, pa tudi enake meje itd.

Kako najti več besed v Excelu

V Excelovih datotekah lahko iščete celice s celotnimi izrazi. Vendar pa bo sistem z vnosom ključa, na primer "Red Ball", opravil delo posebej to zahtevo. Rezultati ne bodo prikazali vrednosti, ki bi izgledale kot "rdeča bleščeča krogla" ali "rdeča sijoča krogla".

Če želite iskati ne eno, ampak več besed hkrati, morate narediti naslednje:

Vnesite jih v iskalno vrstico.
Med njimi postavite zvezdice. Na ta način bodo najdene vrednosti, ki vsebujejo določen napis, ne glede na to, ali so med njimi določeni znaki ali ne.
S to metodo lahko določite ključ tudi z ločenimi črkami.

Uporaba filtra

Torej, če želite uporabiti filter, morate:

Izberite določeno celico, ki vsebuje podatke.
Kliknite glavno stran, nato - "Razvrščanje" in nato - "Filter".
V vrstici na vrhu bodo celice opremljene s puščicami. To je meni, ki ga je treba odpreti.
Zahtevo morate vnesti v besedilno polje in klikniti potrditev.
V stolpcu bodo prikazane samo celice. V katerem je prisoten iskalni izraz.
Če želite ponastaviti rezultate, na spustnem seznamu izberite »Izberi vse«.
Če želite onemogočiti filter, ga ponovno kliknite v razvrščanju.

Predstavljena metoda verjetno ne bo uporabna, če nimate informacij, v kateri vrstici se nahaja vrednost, ki jo iščete.

To je vse za danes, zdaj veste, kako najti pravo besedo v Excelu. Tako bi morali za iskanje določene fraze ali številke uporabiti zmožnosti, vgrajene v vmesnik. Možna je tudi izbira dodatne možnosti poiščite ali uporabite filter.