Kako napraviti traku za pretraživanje u excelu. Pronađite bilo koju riječ s popisa u ćeliji. Searchpos funkcija u excelu - primjer excel searchpos funkcije sa zamjenskim znakovima

Recimo da trebate pronaći telefonski broj zaposlenika koristeći njegov broj i također ispravno procijeniti omjer provizije za iznos prodaje. Pretražujete podatke kako biste brzo i učinkovito pronašli određene podatke na popisu i automatski provjerili koriste li se podaci ispravno. Nakon što pogledate podatke, možete izvršiti izračune i prikazati rezultate, navodeći povratne vrijednosti. Postoji nekoliko načina za pretraživanje vrijednosti u popisu podataka i prikaz rezultata.

U ovom članku

Pronađite vrijednosti na popisu okomito točnim podudaranjem

Za izvođenje ovog zadatka možete koristiti funkciju VLOOKUP ili kombinaciju funkcija INDEX i MATCH.

Primjeri korištenja funkcije VLOOKUP

VLOOKUP funkcija.

Primjeri INDEX-a i MATCHES-a

Što to znači:

=INDEX(morate vratiti vrijednost iz C2:C10, koja će odgovarati MATCH(prva vrijednost "Cabbage" u nizu B2:B10))

Formula traži prvu vrijednost u ćeliji C2:C10 koja odgovara kupus(u B7) i vraća vrijednost u C7 ( 100 ) - prva odgovarajuća vrijednost kupus.

Za više informacija pogledajte funkciju INDEX i funkciju MATCH.

Pronađite vrijednosti na popisu okomito po približnom podudaranju

Da biste to učinili, koristite funkciju VLOOKUP.

Važno:

U gornjem primjeru, funkcija VLOOKUP traži ime učenika koji ima 6 kašnjenja u rasponu A2:B7. U tablici nema unosa za 6 kasni, pa funkcija VLOOKUP traži sljedeće najviše podudaranje ispod 6 i pronalazi vrijednost 5 povezanu s imenom Dave, i stoga se vraća Dave.

Za više informacija pogledajte funkciju VLOOKUP.

Pronalaženje okomitih vrijednosti na popisu nepoznate veličine s točnim podudaranjem

Za izvođenje ovog zadatka koristite funkcije OFFSET i MATCH.

Bilješka: Ovaj se pristup koristi ako su podaci u vanjskom rasponu podataka koji ažurirate svaki dan. Znate da stupac B ima cijenu, ali ne znate koliko redaka podataka poslužitelj vraća, a prvi stupac nije sortiran po abecedi.

C1 je gornja lijeva ćelija raspona (koja se naziva i početna ćelija).

Podudaranje("naranče"; C2: C7; 0) traži narančastu boju u rasponu C2:C7. Ne biste trebali uključiti početnu ćeliju u raspon.

1 - broj stupaca desno od početne ćelije za koje treba vratiti povratnu vrijednost. U našem primjeru, povratna vrijednost je u koloni D, Prodajni.

Pronađite vrijednosti na popisu vodoravno točnim podudaranjem

Za izvođenje ovog zadatka koristi se funkcija GLOOKUP. Ispod je primjer.

Funkcija LOOKUP pretražuje stupac Prodajni i vraća vrijednost iz linije 5 u navedenom rasponu.

Za više informacija pogledajte funkcije LOOKUP.

Pronalaženje vrijednosti na popisu vodoravno pomoću približnog podudaranja

Za izvođenje ovog zadatka koristi se funkcija GLOOKUP.

Važno: Provjerite jesu li vrijednosti u prvom redu poredane uzlaznim redoslijedom.

U gornjem primjeru funkcija GLOOKUP traži vrijednost 11000 u retku 3 unutar navedenog raspona. Ne pronalazi 11000 i stoga traži sljedeće najveća vrijednostšto je manje od 1100 i vraća broj 10543.

Za više informacija pogledajte funkcije LOOKUP.

Stvorite formulu pretraživanja pomoću čarobnjaka za traženje (samo Excel 2007)

Bilješka: Dodatak Lookup Wizard ukinut je u programu Excel 2010. Ovi funkcionalnost zamijenjen čarobnjakom za funkcije i dostupnim funkcijama za pretraživanje i referencu (referenca).

U programu Excel 2007 čarobnjak za pretraživanje stvara formulu za pretraživanje na temelju podataka radnog lista koji ima naslove redaka i stupaca. Čarobnjak za traženje pomaže vam pronaći druge vrijednosti u retku kada znate vrijednost u jednom stupcu i obrnuto. Čarobnjak za pretraživanje koristi indeks i MATCH u formulama koje stvara.

Datum: 3. siječnja 2016. Kategorija:

Traženje znakova u nizu je najvažniji zadatak koji vam omogućuje automatizaciju rada s tekstom. Na primjer, olakšava kada je položaj ciljane linije nepoznat.

Dotaknuo sam se Excel alata za pretraživanje u , ali ovdje ćemo pogledati funkcije koje traže tekst u nizu i vraćaju njegovu početnu poziciju.

Za traženje teksta u Microsoft Excel postoje dvije funkcije:

PRONAĆI() . Ova funkcija ima 2 obavezna argumenta i jedan izborni:
- Traži tekst– niz znakova koje treba pronaći. Ovo je potreban argument
- Izvorni tekst– niz u kojem se vrši pretraga. Potreban argument
- Početna pozicija– redni broj znaka u izvornom tekstu od kojeg počinje pretraživanje. Neobavezni argument. Ako nije navedeno, pretraživanje počinje od prvog znaka

FIND funkcija uzima u obzir I ne podržava korištenje zamjenskih znakova(o njima pročitajte dalje).

FIND funkcija u Excelu

TRAŽI(Traži tekst; Izvorni tekst; [Početni položaj]) ima sličnu sintaksu i argumente. Razlikuje se u tome neosjetljivo na velika i mala slova I podržava korištenje zamjenskih znakova.

SEARCH funkcija u Excelu

Kao što vidite, ova je funkcija dala drugačiji rezultat jer pretraživanje nije razlikovalo velika i mala slova.

Što su zamjenski znakovi, pitate se? Ovo su posebni znakovi koji vam omogućuju da preskočite nepoznati dio teksta za pretraživanje:

? – bilo koji simbol
* – bilo koji niz znakova

Ako su upitnik ili zvjezdica dio niza za pretraživanje, a ne zamjenski znak, ispred njih stavite tildu (~).

Zapišimo sljedeću formulu: =SEARCH(“??chn”,A1,1). Funkcija će tražiti niz od bilo koja dva znaka iza kojih slijede slova "chn". Pretraživanje odgovara dijelu riječi “Tabular” u primjeru teksta, tj. kombinacija znakova "osobno". Rezultat funkcije bit će vrijednost "4".

Zamjenski znakovi u Excelu

Sada napišimo formulu =SEARCH(“p*s”,A2,1) . Funkcija traži kombinaciju “p” + bilo koji broj bilo kojih znakova + “s”. U primjeru, ovo je dio riječi "procesor". Rezultat funkcije je 11.

Zamjenski znak zvjezdice u Excelu

Funkcije pretraživanja olakšavaju vam praćenje položaja željenog teksta, pa ga je lakše zamijeniti. Obavezno vježbajte njihovu upotrebu. Moj post-tutorial o tekstualnim funkcijama pomoći će vam u tome.

I sljedeći će članak biti posvećen. Važna tema koju svakako treba proučiti!

Postavljajte pitanja u komentarima i pretplatite se na ažuriranja kako ništa ne biste propustili!

Ovaj članak opisuje sintaksu formule i korištenje funkcije TRAŽI I TRAŽI u programu Microsoft Excel.

Opis

Funkcije TRAŽI I TRAŽI pronaći jedan tekstualni niz unutar drugog i vratiti početnu poziciju prvog tekstualnog niza (brojeći od prvog znaka drugog tekstualnog niza). Na primjer, da biste pronašli položaj slova "n" u riječi "pisač", možete koristiti sljedeću funkciju:

PRETRAŽIVANJE("n","pisač")

Ova funkcija se vraća 4 , jer je "n" četvrti znak u riječi "printer".

Također možete pronaći riječi u drugim riječima. Na primjer, funkcija

SEARCH("baza","baza podataka")

vraća 5 , budući da riječ "baza" počinje petim znakom riječi "baza podataka". Funkcije se mogu koristiti TRAŽI I TRAŽI za određivanje položaja znaka ili tekstualnog niza u drugom tekstualnom nizu i zatim vraćanje teksta pomoću funkcija PSTR I PSTRB ili ga zamijeniti funkcijama ZAMIJENITI I ZAMIJENITI. Te su funkcije prikazane u ovom članku.

Važno:

Ove značajke možda neće biti dostupne na svim jezicima.

Funkcija SEARCH broji dva bajta po znaku samo ako je zadani jezik jezik s omogućenim BDC-om. U suprotnom, funkcija SEARCH radi isto kao funkcija SEARCH i broji jedan bajt po znaku.

Jezici koji podržavaju BDCS uključuju japanski, pojednostavljeni kineski, tradicionalni kineski i korejski.

Sintaksa

PRETRAŽIVANJE(tekst_za_pretraživanje, pregledani_tekst, [početna_položaj])

SEARCHB(tekst_za_pretraživanje, pregledani_tekst, [početna_položaj])

Argumenti za funkcije SEARCH i SEARCHB opisani su u nastavku.

Search_text Potreban. Tekst koji želite pronaći.

Pregledani_tekst Potreban. Tekst u kojem se traži vrijednost argumenta search_text .

Početna_položaj Neobavezno. Broj znaka u argumentu pogledani_tekst , gdje biste trebali započeti svoju pretragu.

Komentar

Funkcije TRAŽI I TRAŽI nisu osjetljivi na velika i mala slova. Ako trebate razlikovati velika i mala slova, koristite funkcije PRONAĆI I NIGHTIB.

U raspravi search_text Možete koristiti zamjenske znakove: upitnik ( ? ) i zvjezdica ( * ). Upitnik odgovara bilo kojem znaku, a zvjezdica odgovara bilo kojem nizu znakova. Ako želite pronaći upitnik ili zvjezdicu, upišite tildu ( ~ ).

Ako vrijednost argumenta search_text nije pronađeno, #VALUE! vraćena vrijednost pogreške.

Ako argument početna_pozicija je izostavljen, tada je postavljen na 1.

Ako Početna_položaj ne više od 0 ili veće od duljine argumenta pogledani_tekst , #VRIJEDNOST! vraćena vrijednost pogreške.

Argument početna_pozicija može se koristiti za preskakanje određenog broja znakova. Pretpostavimo da funkcija TRAŽI treba koristiti za rad s tekstualnim nizom "MDS0093.Muška odjeća". Da biste pronašli prvo pojavljivanje "M" u opisnom dijelu tekstualnog niza, postavite argument na početna_pozicija vrijednost 8 tako da se pretraga ne vrši u onom dijelu teksta koji je serijski broj(u ovom slučaju - "MDS0093"). Funkcija TRAŽI započinje pretragu od osmog znaka, pronalazi znak naveden u argumentu search_text , na sljedećoj poziciji i vraća broj 9. Funkcija TRAŽI uvijek vraća broj znaka, računajući od početka teksta koji se gleda , uključujući znakove koji se preskaču ako je vrijednost argumenta početna_pozicija više od 1.

Primjeri

Kopirajte uzorke podataka iz sljedeće tablice i zalijepite ih u ćeliju A1 novog radnog lista programa Excel. Za prikaz rezultata formula, odaberite ih i pritisnite F2, zatim pritisnite Enter. Ako je potrebno, promijenite širinu stupaca kako biste vidjeli sve podatke.

Podaci

Prihod: marža

"Šef" ovdje.
Formula	Opis	Proizlaziti
PRETRAŽIVANJE("i";A2;6)	Položaj prvog "i" u retku ćelije A2, počevši od šestog znaka.
TRAŽI(A4;A3)	Početna pozicija retka "marža" (pretraživani red je u ćeliji A4) u retku "Prihod: marža" (pretraživana ćelija je A3).
ZAMIJENI(A3,TRAŽI(A4,A3),6,"volumen")	Zamjenjuje riječ "margina" riječju "volumen" lociranjem riječi "margina" u ćeliji A3 i zamjenom tog znaka i sljedećih pet znakova tekstualnim nizom "volumen".	Prihod: volumen
PSTR(A3;TRAŽI(" ";A3)+1,4)	Vraća prva četiri znaka koja slijede nakon prvog razmaka u retku Prihod: Marža (ćelija A3).
PRETRAŽIVANJE("""";A5)	Pozicija prva dvostruki navodnici(") u ćeliji A5.
PSTR(A5;SEARCH("""";A5)+1;SEARCH("""";A5;SEARCH("""";A5)+1)-SEARCH("""";A5)-1)	Vraća samo tekst u dvostrukim navodnicima iz ćelije A5.

U ovom članku

Pronađite vrijednosti na popisu okomito točnim podudaranjem

Za izvođenje ovog zadatka možete koristiti funkciju VLOOKUP ili kombinaciju funkcija INDEX i MATCH.

Primjeri korištenja funkcije VLOOKUP

VLOOKUP funkcija.

Primjeri INDEX-a i MATCHES-a

Što to znači:

=INDEX(morate vratiti vrijednost iz C2:C10, koja će odgovarati MATCH(prva vrijednost "Cabbage" u nizu B2:B10))

Formula traži prvu vrijednost u ćeliji C2:C10 koja odgovara kupus(u B7) i vraća vrijednost u C7 ( 100 ) - prva odgovarajuća vrijednost kupus.

Za više informacija pogledajte funkciju INDEX i funkciju MATCH.

Pronađite vrijednosti na popisu okomito po približnom podudaranju

Da biste to učinili, koristite funkciju VLOOKUP.

Važno:

Za više informacija pogledajte funkciju VLOOKUP.

Pronalaženje okomitih vrijednosti na popisu nepoznate veličine s točnim podudaranjem

Za izvođenje ovog zadatka koristite funkcije OFFSET i MATCH.

C1 je gornja lijeva ćelija raspona (koja se naziva i početna ćelija).

Podudaranje("naranče"; C2: C7; 0) traži narančastu boju u rasponu C2:C7. Ne biste trebali uključiti početnu ćeliju u raspon.

1 - broj stupaca desno od početne ćelije za koje treba vratiti povratnu vrijednost. U našem primjeru, povratna vrijednost je u koloni D, Prodajni.

Pronađite vrijednosti na popisu vodoravno točnim podudaranjem

Za izvođenje ovog zadatka koristi se funkcija GLOOKUP. Ispod je primjer.

Funkcija LOOKUP pretražuje stupac Prodajni i vraća vrijednost iz linije 5 u navedenom rasponu.

Za više informacija pogledajte funkcije LOOKUP.

Pronalaženje vrijednosti na popisu vodoravno pomoću približnog podudaranja

Za izvođenje ovog zadatka koristi se funkcija GLOOKUP.

Važno: Provjerite jesu li vrijednosti u prvom redu poredane uzlaznim redoslijedom.

U gornjem primjeru funkcija GLOOKUP traži vrijednost 11000 u retku 3 unutar navedenog raspona. Ne pronalazi 11000 i stoga traži sljedeću najveću vrijednost koja je manja od 1100 i vraća broj 10543.

Za više informacija pogledajte funkcije LOOKUP.

Stvorite formulu pretraživanja pomoću čarobnjaka za traženje (samo Excel 2007)

Bilješka: Dodatak Lookup Wizard ukinut je u Excelu 2010. Ova je funkcionalnost zamijenjena Function Wizardom i dostupnom funkcijom pretraživanja i reference (referenca).

Sve najbolje prijateljice! A danas ćete naučiti kako pronaći pravu riječ u Excelu. Rad s Excelom često uključuje značajne količine informacija. U takvim datotekama razvijene su uistinu ogromne tablice s tisućama redaka, stupaca, a također i pozicija. Pronalaženje bilo koje specifične vrijednosti u tako velikom nizu ponekad može biti nevjerojatno teško.

Ovaj se zadatak može pojednostaviti. Znajući kako pronaći potrebnu riječ, možete lako kretati datotekom. Uređivač tablica nudi nekoliko varijanti pretraživanja.

Kako pronaći pravu riječ po ćelijama u Excelu

Za prikaz adresa onih ćelija koje sadrže ono što pokušavate pronaći, trebate slijediti ove korake:

Ako ste korisnik programa 2010, trebali biste otići na izbornik, zatim kliknuti na “Uredi”, a zatim “Pronađi”.
Zatim će se otvoriti prozor u koji morate upisati traženi izraz.
Program prethodna verzija ima ovaj gumb u izborniku pod nazivom "Početna", koji se nalazi na ploči za uređivanje.
Sličan rezultat može se postići u bilo kojoj verziji istovremenim korištenjem tipki Ctrl i F.
U polje treba upisati izraz, tražene riječi ili brojeve.
Klikom na “Pronađi sve” pokrenut ćete pretragu cijele datoteke. Klikom na “Dalje”, program će ih odabrati jednu po jednu ćeliju, koja se nalazi ispod kursora ćelije datoteke.
Vrijedno je pričekati dok se proces ne završi. Štoviše, što je veći dokument, potrebno je više vremena za pretraživanje.
Pojavit će se popis rezultata: imena i adrese ćelija koje sadrže podudaranja s navedenom vrijednošću ili frazom.
Klikom na bilo koji redak bit će označena odgovarajuća ćelija.
Radi praktičnosti, možete "rastegnuti" prozor. Na taj način će biti vidljivo više linija.
Za sortiranje podataka potrebno je kliknuti na nazive stupaca iznad pronađenih rezultata. Klikom na “Sheet” linije će se poredati po abecedi ovisno o nazivu lista, a odabirom “Values” će se poredati ovisno o vrijednosti. Usput, ovi se stupci također mogu "razvući".

Parametri pretraživanja

Moguće je postaviti vlastite uvjete. Na primjer, pretraživanje na temelju nekoliko znakova. Evo kako pronaći riječ koje se u potpunosti ne sjećate:

Treba unijeti samo dio natpisa. Možete čak upotrijebiti jedno od slova - bit će naznačena sva područja u kojima je dostupno.
Koristite ikone zvjezdice i upitnika. Oni mogu zamijeniti znakove koji nedostaju.
Pitanje označava jednu poziciju koja nedostaje. Ako, primjerice, upišete “Ha????”, prikazat će se ćelije koje sadrže riječ od pet znakova koja počinje s “A”.
Zahvaljujući zvjezdici, može se zamijeniti bilo koji broj znakova. Da biste pronašli sve vrijednosti koje sadrže korijen "rast", trebali biste započeti pretraživanje prema ključu "rast*".

Osim toga, možete posjetiti postavke:

U okviru za pretraživanje kliknite "Opcije".
U odjeljcima područja za gledanje i pretraživanje morat ćete točno naznačiti gdje i na temelju kojih kriterija želite tražiti podudaranja. Možete odabrati formule, razne bilješke ili vrijednosti.
Da biste razlikovali mala i velika slova, morate potvrditi okvir pod nazivom "Podudaranje velikih i malih slova".
Označavanjem potvrdnog okvira “Complete cell” rezultati će biti prikazani u obliku ćelija koje sadrže isključivo navedene izraz za pretraživanje, i ništa osim nje.

Postavke oblikovanja ćelija

Za traženje vrijednosti s određenim ispunom ili stilom, trebali biste primijeniti postavke. Dakle, ako riječ izgleda drugačije od ostatka teksta, morate učiniti sljedeće:

U okviru za pretraživanje kliknite na parametre i pritisnite gumb "Format". Otvorit će se izbornik koji sadrži nekoliko kartica.
Možete odrediti određeni font, vrstu okvira, boju pozadine, kao i format ulaznih podataka. Sustav će pregledati ona područja koja zadovoljavaju navedene kriterije.
Za preuzimanje informacija iz trenutne ćelije (označeno na ovaj trenutak), kliknite "Koristi format ove ćelije". U tom slučaju, program će pronaći sve vrijednosti koje imaju istu veličinu i vrstu znakova, istu boju, kao i iste granice, itd.

Kako pronaći više riječi u Excelu

U Excel datotekama možete pretraživati ćelije pomoću cijelih izraza. Međutim, unošenjem ključa, na primjer "Crvena lopta", sustav će raditi posebno na ovaj zahtjev. Rezultati neće prikazivati vrijednosti koje izgledaju kao "Crvena svjetlucava kugla" ili "Crvena sjajna kugla".

Da biste tražili ne jednu, već nekoliko riječi u isto vrijeme, morate učiniti sljedeće:

Unesite ih u traku za pretraživanje.
Stavite zvijezde između njih. Na taj način će se pronaći vrijednosti koje sadrže navedeni natpis, bez obzira na to postoje li između njih određeni znakovi ili ne.
Pomoću ove metode možete odrediti ključ čak i s odvojenim slovima.

Primjena filtra

Dakle, da biste koristili filtar, trebate:

Odaberite određenu ćeliju koja sadrži podatke.
Kliknite na glavnu stranicu, zatim - "Sortiraj", a zatim - "Filtriraj".
U retku na vrhu ćelije će biti opremljene strelicama. Ovo je izbornik koji treba otvoriti.
Potrebno je upisati zahtjev u tekstualno polje i kliknuti na potvrdu.
U stupcu će biti prikazane samo ćelije. U kojem je prisutan izraz za pretraživanje.
Za poništavanje rezultata odaberite "Odaberi sve" na padajućem popisu.
Da biste onemogućili filtar, ponovno kliknite na njega u sortiranju.

Predstavljena metoda vjerojatno neće biti korisna ako nemate informaciju u kojem se retku nalazi vrijednost koju tražite.

To je sve za danas, sada znate kako pronaći pravu riječ u Excelu. Dakle, za traženje određene fraze ili broja, trebali biste koristiti mogućnosti ugrađene u sučelje. Također je moguće birati dodatne opcije pretražite ili koristite filtar.