Recimo da trebate pronaći lokal zaposlenog koristeći njegov broj i također ispravno procijeniti omjer provizije za iznos prodaje. Pretražujete podatke kako biste brzo i efikasno pronašli određene podatke na listi i automatski provjerili da li se podaci ispravno koriste. Nakon što pregledate podatke, možete izvršiti proračune i prikazati rezultate, navodeći povratne vrijednosti. Postoji nekoliko načina za traženje vrijednosti na listi podataka i prikaz rezultata.

U ovom članku

Pronađite vrijednosti na listi okomito prema tačnom podudaranju

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

Šta 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 kojoj 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 listi okomito prema približnom podudaranju

Da biste to učinili, koristite funkciju VLOOKUP.

Bitan:

U gornjem primjeru, funkcija VLOOKUP traži ime učenika koji ima 6 kašnjenja u rasponu A2:B7. U tabeli 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 funkcija VLOOKUP.

Pronalaženje vertikalnih vrijednosti na listi nepoznate veličine s tačnim podudaranjem

Da biste izvršili ovaj zadatak, koristite funkcije OFFSET i MATCH.

Bilješka: Ovaj pristup se koristi ako se podaci nalaze u rasponu vanjskih podataka koji ažurirate svaki dan. Znate da kolona B ima cijenu, ali ne znate koliko redova podataka server vraća, a prva kolona nije sortirana po abecednom redu.

C1 je gornja lijeva ćelija raspona (također se naziva početna ćelija).

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

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

Pronađi vrijednosti na listi horizontalno prema tačnom podudaranju

Za obavljanje ovog zadatka koristi se funkcija GLOOKUP. Ispod je primjer.

Funkcija LOOKUP pretražuje kolonu Prodaja i vraća vrijednost iz reda 5 u navedenom rasponu.

Za više informacija pogledajte funkcije LOOKUP.

Pronalaženje vrijednosti na listi horizontalno pomoću približnog podudaranja

Za obavljanje ovog zadatka koristi se funkcija GLOOKUP.

Bitan: Uvjerite se da su vrijednosti u prvom redu sortirane uzlaznim redoslijedom.

U gornjem primjeru, funkcija GLOOKUP traži vrijednost 11000 u redu 3 unutar navedenog raspona. Ne nalazi 11000 i stoga traži sljedeće najveća vrijednost koji je manji od 1100 i vraća broj 10543.

Za više informacija pogledajte funkcije LOOKUP.

Kreirajte formulu za traženje pomoću čarobnjaka za traženje (samo Excel 2007)

Bilješka: Dodatak Čarobnjak za traženje je ukinut u programu Excel 2010. Ovi funkcionalnost zamijenjen čarobnjakom za funkcije i dostupnim funkcijama pretraživanja i referenci (reference).

U programu Excel 2007, čarobnjak za traženje kreira formulu za traženje zasnovanu na podacima radnog lista koji imaju naslove redova i kolona. Čarobnjak za traženje vam pomaže da pronađete druge vrijednosti u nizu kada znate vrijednost u jednoj koloni i obrnuto. Čarobnjak za traženje koristi indeks i MATCH u formulama koje kreira.

Datum: 3. januara 2016. Kategorija:

Traženje znakova u nizu najvažniji je zadatak koji vam omogućava automatizaciju rada s tekstom. Na primjer, olakšava kada je pozicija ciljne linije nepoznata.

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:

1. PRONAĐI() . Ova funkcija ima 2 obavezna argumenta i jedan opcioni:
   • Pretraži tekst– niz znakova koji treba pronaći. Ovo je obavezan argument
   • Originalni tekst– niz u kojem se vrši pretraga. Potreban argument
   • Početna pozicija– serijski broj znaka u izvornom tekstu od kojeg počinje pretraga. Opcioni argument. Ako nije navedeno, pretraga počinje od prvog znaka

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

Funkcija FIND u Excelu

1. PRETRAŽI(Pretraži tekst; Originalni tekst; [Početni položaj]) ima sličnu sintaksu i argumente. Po tome se razlikuje neosjetljivo na velika i mala slova I podržava upotrebu zamjenskih znakova.

Funkcija SEARCH u Excelu

Kao što vidite, ova funkcija je dala drugačiji rezultat jer pretraga nije bila osjetljiva na velika i mala slova.

Šta su zamjenski znakovi, pitate se? Ovo su posebni znakovi koji vam omogućavaju da preskočite nepoznati dio teksta radi pretraživanja:

• ? – bilo koji simbol
• * – bilo koji niz znakova

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

Zapišimo sljedeću formulu: =SEARCH(“??chn”,A1,1). Funkcija će tražiti niz od bilo koja dva znaka praćenog slovima “chn”. Pretraga se poklapa sa dijelom riječi “Tabular” u tekstu primjera, tj. kombinacija znakova "osobna". Rezultat funkcije će biti vrijednost "4".

Zamjenski znakovi u Excelu

Sada napišimo formulu =SEARCH(“p*s”,A2,1) . Funkcija traži kombinaciju “p” + bilo koji broj bilo kojeg karaktera + “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. Svakako vježbajte njihovu upotrebu. Moj post-tutorial o tekstualnim funkcijama će vam pomoći u tome.

I sljedeći članak će 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 funkcija TRAŽI I TRAŽI u programu Microsoft Excel.

Opis

Funkcije TRAŽI I TRAŽI pronađite jedan tekstualni niz u drugom i vratite početnu poziciju prvog tekstualnog niza (računajući od prvog znaka drugog tekstualnog niza). Na primjer, da biste pronašli poziciju slova "n" u riječi "printer", možete koristiti sljedeću funkciju:

SEARCH("n","printer")

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

Možete pronaći riječi i drugim riječima. Na primjer, funkcija

PRETRAŽI("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 da odredite poziciju znaka ili tekstualnog niza u drugom tekstualnom nizu, a zatim vratite tekst pomoću funkcija PSTR I PSTRB ili ga zamijenite funkcijama ZAMIJENITE I ZAMIJENITE. Ove funkcije su prikazane u ovom članku.

Bitan:

Ove funkcije možda nisu dostupne na svim jezicima.

Funkcija SEARCH broji dva bajta po karakteru samo ako je zadani jezik jezik koji podržava BDC. Inače, funkcija SEARCH radi isto kao i funkcija SEARCH i broji jedan bajt po karakteru.

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

Sintaksa

SEARCH(tekst_pretraživanja, pregledani_tekst, [početna_pozicija])

SEARCHB(tekst_pretraživanja, pregledani_tekst, [početna_pozicija])

Argumenti funkcija SEARCH i SEARCHB opisani su u nastavku.

Search_text Obavezno. Tekst koji želite pronaći.

Pregledano_tekst Obavezno. Tekst u kojem treba pronaći vrijednost argumenta search_text .

Start_position Opciono. Broj znakova u argumentu viewed_text , gdje biste trebali započeti pretragu.

Komentar

Funkcije TRAŽI I TRAŽI ne razlikuju velika i mala slova. Ako trebate biti osjetljivi na velika i mala slova, koristite funkcije FIND I NIGHTIB.

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

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

Ako je argument start_position je izostavljen, tada se postavlja na 1.

Ako Start_position ne više od 0 ili veće od dužine argumenta viewed_text , #VRIJEDNOST! vraćena vrijednost greške.

Argument start_position može se koristiti za preskakanje određenog broja znakova. Pretpostavimo da je funkcija TRAŽI treba koristiti za rad sa tekstualnim nizom "MDS0093.Muška odjeća". Da biste pronašli prvo pojavljivanje "M" u opisnom dijelu tekstualnog niza, postavite argument na start_position 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 poč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 karaktera, računajući od početka teksta koji se pregledava , uključujući znakove koji se preskaču ako je vrijednost argumenta start_position više od 1.

Primjeri

Kopirajte uzorke podataka iz sljedeće tablice i zalijepite ih u ćeliju A1 novog Excel radnog lista. Da biste prikazali rezultate formula, odaberite ih i pritisnite F2, a zatim pritisnite Enter. Ako je potrebno, promijenite širinu kolona da vidite sve podatke.

Podaci
Prihod: marža
"Šef" ovde.
Formula	Opis	Rezultat
PRETRAŽI("i";A2;6)	Položaj prvog "i" u redu ćelije A2, počevši od šestog znaka.
TRAŽI(A4;A3)	Početna pozicija reda "margina" (traženi red je u ćeliji A4) u redu "Prihodi: marža" (pretražena ćelija je A3).
ZAMJENA(A3,TRAŽI(A4,A3),6,"volumen")	Zamjenjuje riječ "margina" riječju "volume" tako što locira riječ "margin" u ćeliju A3 i zamjenjuje taj znak i sljedećih pet znakova s ​​tekstualnim nizom "volume".	Prihod: obim
PSTR(A3;TRAŽI(" ";A3)+1,4)	Vraća prva četiri znaka koja slijede prvi razmak u redu Prihod: marža (ćelija A3).
PRETRAŽI("""";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 zatvoren u dvostrukim navodnicima iz ćelije A5.

Recimo da trebate pronaći lokal zaposlenog koristeći njegov broj i također ispravno procijeniti omjer provizije za iznos prodaje. Pretražujete podatke kako biste brzo i efikasno pronašli određene podatke na listi i automatski provjerili da li se podaci ispravno koriste. Nakon što pregledate podatke, možete izvršiti proračune i prikazati rezultate, navodeći povratne vrijednosti. Postoji nekoliko načina za traženje vrijednosti na listi podataka i prikaz rezultata.

U ovom članku

Pronađite vrijednosti na listi okomito prema tačnom podudaranju

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

Šta 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 kojoj 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 listi okomito prema približnom podudaranju

Da biste to učinili, koristite funkciju VLOOKUP.

Bitan:

U gornjem primjeru, funkcija VLOOKUP traži ime učenika koji ima 6 kašnjenja u rasponu A2:B7. U tabeli 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 funkcija VLOOKUP.

Pronalaženje vertikalnih vrijednosti na listi nepoznate veličine s tačnim podudaranjem

Da biste izvršili ovaj zadatak, koristite funkcije OFFSET i MATCH.

Bilješka: Ovaj pristup se koristi ako se podaci nalaze u rasponu vanjskih podataka koji ažurirate svaki dan. Znate da kolona B ima cijenu, ali ne znate koliko redova podataka server vraća, a prva kolona nije sortirana po abecednom redu.

C1 je gornja lijeva ćelija raspona (također se naziva početna ćelija).

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

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

Pronađi vrijednosti na listi horizontalno prema tačnom podudaranju

Za obavljanje ovog zadatka koristi se funkcija GLOOKUP. Ispod je primjer.

Funkcija LOOKUP pretražuje kolonu Prodaja i vraća vrijednost iz reda 5 u navedenom rasponu.

Za više informacija pogledajte funkcije LOOKUP.

Pronalaženje vrijednosti na listi horizontalno pomoću približnog podudaranja

Za obavljanje ovog zadatka koristi se funkcija GLOOKUP.

Bitan: Uvjerite se da su vrijednosti u prvom redu sortirane uzlaznim redoslijedom.

U gornjem primjeru, funkcija GLOOKUP traži vrijednost 11000 u redu 3 unutar navedenog raspona. Ne nalazi 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.

Kreirajte formulu za traženje pomoću čarobnjaka za traženje (samo Excel 2007)

Bilješka: Dodatak Čarobnjak za traženje je ukinut u programu Excel 2010. Ovu funkciju zamijenili su Čarobnjak za funkcije i dostupne funkcije pretraživanja i reference (reference).

U programu Excel 2007, čarobnjak za traženje kreira formulu za traženje zasnovanu na podacima radnog lista koji imaju naslove redova i kolona. Čarobnjak za traženje vam pomaže da pronađete druge vrijednosti u nizu kada znate vrijednost u jednoj koloni i obrnuto. Čarobnjak za traženje koristi indeks i MATCH u formulama koje kreira.

Svi najbolji prijatelji! A danas ćete naučiti kako pronaći pravu riječ u Excelu. Često rad sa Excelom uključuje značajne količine informacija. U takvim fajlovima su razvijene zaista ogromne tabele sa hiljadama redova, kolona, ​​ali i pozicija. Pronalaženje bilo koje specifične vrijednosti u tako velikom nizu ponekad može biti nevjerovatno teško.

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

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

Da biste prikazali adrese onih ćelija koje sadrže ono što pokušavate pronaći, slijedite ove korake:

1. Ako ste korisnik programa 2010, trebali biste otići u meni, zatim kliknuti na “Uredi”, a zatim “Pronađi”.
2. Zatim će se otvoriti prozor u koji morate upisati frazu za pretraživanje.
3. Program prethodna verzija ima ovo dugme u meniju pod nazivom “Home”, koji se nalazi na panelu za uređivanje.
4. Sličan rezultat se može postići u bilo kojoj od verzija istovremenom upotrebom tipki Ctrl i F.
5. U polje treba upisati frazu, tražene riječi ili brojeve.
6. Klikom na „Pronađi sve“, pokrenut ćete pretragu po cijeloj datoteci. Klikom na „Dalje“, program će ih odabrati jednu po jednu ćeliju, koja se nalazi ispod kursora ćelije datoteke.
7. Vrijedi pričekati dok se proces ne završi. Štaviše, što je dokument veći, to će više vremena biti potrebno za pretragu.
8. Pojavit će se lista rezultata: imena i adrese ćelija koje sadrže podudaranja sa navedenom vrijednošću ili frazom.
9. Klikom na bilo koju liniju, odgovarajuća ćelija će biti označena.
10. Za praktičnost, možete "razvući" prozor. Na ovaj način će biti vidljivo više linija.
11. Da biste sortirali podatke, potrebno je da kliknete na nazive kolona iznad pronađenih rezultata. Klikom na “Sheet” redovi će biti raspoređeni po abecednom redu u zavisnosti od naziva lista, a odabirom “Vrijednosti” će se rasporediti ovisno o vrijednosti. Usput, ove kolone se mogu i "razvući".

Parametri pretrage

Moguće je postaviti sopstvene uslove. Na primjer, pretraživanje na osnovu nekoliko znakova. Evo kako pronaći riječ koju se ne sjećate u potpunosti:

1. Treba upisati samo dio natpisa. Možete čak koristiti i jedno od slova - bit će označena sva područja u kojima je dostupna.
2. Koristite ikone zvjezdice i upitnika. Oni mogu zamijeniti znakove koji nedostaju.
3. Pitanje ukazuje na jednu poziciju koja nedostaje. Ako, na primjer, upišete "Huh????", prikazat će se ćelije koje sadrže riječ od pet znakova koja počinje s "A".
4. Zahvaljujući zvjezdici, bilo koji broj znakova se može zamijeniti. 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 polju za pretragu kliknite na „Opcije“.
• U odjeljcima područja za pregled i pretragu, morat ćete naznačiti tačno gdje i na osnovu kojih kriterija želite tražiti podudaranja. Možete odabrati formule, razne napomene ili vrijednosti.
• Da biste razlikovali mala i velika slova, morate označiti okvir pod nazivom "Upari velika slova".
• Označavanjem potvrdnog okvira “Complete cell”, rezultati će biti prikazani u obliku ćelija koje sadrže isključivo navedene fraza za pretragu, i ništa osim nje.

Postavke formatiranja ćelije

Da biste tražili vrijednosti sa određenom ispunom ili stilom, trebate primijeniti postavke. Dakle, ako riječ izgleda drugačije od ostatka teksta, trebate učiniti sljedeće:

1. U polju za pretragu kliknite na parametre i pritisnite dugme „Format“. Otvoriće se meni koji sadrži nekoliko kartica.
2. Možete odrediti određeni font, tip okvira, boju pozadine, kao i format ulaznih podataka. Sistem će vidjeti one oblasti koje ispunjavaju navedene kriterije.
3. Za preuzimanje informacija iz trenutne ćelije (označeno uključeno ovog trenutka), kliknite na "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 koristeći čitave izraze. Međutim, unošenjem ključa, na primjer „Red Ball“, sistem će obaviti rad posebno na njemu ovaj zahtjev. Rezultati neće prikazati vrijednosti koje izgledaju kao "Crvena svjetlucava lopta" ili "Crvena sjajna lopta".

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.
• Postavite zvijezde između njih. Na taj način će se pronaći vrijednosti koje sadrže navedeni natpis, bez obzira na to postoje li određeni znakovi između njih ili ne.
• Koristeći ovu metodu, možete odrediti ključ čak i sa zasebnim slovima.

Primjena filtera

Dakle, da biste koristili filter, trebate:

1. Odaberite određenu ćeliju koja sadrži podatke.
2. Kliknite na glavnu stranicu, zatim – “Sortiranje”, a zatim – “Filter”.
3. U liniji na vrhu ćelije će biti opremljene strelicama. Ovo je meni koji treba otvoriti.
4. Potrebno je da unesete zahtev u tekstualno polje i kliknete na potvrdu.
5. U koloni će biti prikazane samo ćelije. U kojoj je prisutna fraza za pretraživanje.
6. Da poništite rezultate, odaberite "Odaberi sve" na padajućoj listi.
7. Da biste onemogućili filter, ponovo kliknite na njega u sortiranju.

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

To je sve za danas, sada znate kako pronaći pravu riječ u Excelu. Stoga, da biste tražili određenu frazu ili broj, trebali biste koristiti mogućnosti ugrađene u sučelje. Takođe je moguć izbor dodatne opcije pretražite ili koristite filter.