Funkcija TRAŽI u Excel-u se koristi za određivanje položaja teksta unutar bilo kojeg teksta i označavanje njegove tačne pozicije.
Šta funkcija vraća
Funkcija vraća numeričku vrijednost koja označava početnu poziciju teksta za pretraživanje unutar drugog teksta. Pozicija označava redni broj znaka od kojeg počinje tekst za pretragu.
Sintaksa
= PRETRAGA (pronađi_tekst, unutar_teksta,)- Engleska verzija
= PRETRAGA (tekst_potraži; tekst_potraži; [početna_pozicija])- ruska verzija
Argumenti funkcije
- pronađi_tekst (pretraži_tekst)- tekst ili tekstualni niz koji želite da pronađete;
- unutar_teksta (vidljivi_tekst)- tekst unutar kojeg pretražujete;
- ([početna_pozicija])- numerička vrijednost koja označava poziciju s koje želite započeti pretragu. Ako ne navedete ovaj argument, funkcija će početi tražiti od početka teksta.
Dodatne informacije
- Ako početna pozicija pretrage nije specificirana, onda se pretraživanje teksta izvodi prvo od teksta;
- Funkcija ne razlikuje velika i mala slova. Ako vam je potrebna osjetljivost na velika i mala slova, upotrijebite funkciju;
- Funkcija može rukovati zamjenskim znakovima. U Excelu postoje tri zamjenska znaka -?, *, ~.
- znak "?" - odgovara bilo kojem pojedinačnom znaku;
- "*" - odgovara svim dodatnim znakovima;
- znak “~” se koristi kada treba da pronađete sam znak pitanja ili zvjezdicu.
- Funkcija vraća grešku ako tekst pretraživanja nije pronađen.
Primjeri korištenja funkcije SEARCH u Excelu
Primjer 1. Traženje riječi unutar tekstualnog niza (od početka)
U gornjem primjeru možete vidjeti da kada tražimo riječ "dobro" u tekstu "Dobro jutro", funkcija vraća vrijednost "1", koja odgovara poziciji riječi "dobro" u tekstu " Dobro jutro".
Budući da funkcija nije osjetljiva na velika i mala slova, nije bitno kako ćemo specificirati željenu riječ "dobro", bilo da je "VRSTA", "Vrsta", "vrsta" itd. funkcija će vratiti istu vrijednost.
Ako trebate izvršiti pretragu koja razlikuje velika i mala slova - koristite funkciju u Excelu.
Primjer 2. Tražimo riječ unutar tekstualnog niza (koji označava početnu poziciju pretraživanja)
Treći argument funkcije označava redni položaj u tekstu, s kojeg će se izvršiti pretraga. U gornjem primjeru, funkcija vraća “1” kada traži “dobro” u tekstu “Dobro jutro”, počevši od prve pozicije.
Istovremeno, ako funkciji naznačimo da pretraga treba početi od drugog znaka teksta "Dobro jutro", odnosno funkcija u ovom slučaju vidi tekst kao "dobro jutro" i traži riječ " dobro", onda će rezultat biti greška.
Ako ne navedete početnu poziciju za pretragu kao argument, funkcija će automatski započeti pretragu od početka teksta.
Primjer 3. Potražite riječ u prisustvu nekoliko podudaranja u tekstu
Funkcija počinje tražiti tekst od početne pozicije, koju možemo navesti kao argument, ili će automatski početi tražiti od početka teksta. U gornjem primjeru tražimo riječ “dobro” u tekstu “Dobro jutro” sa početnom pozicijom za pretragu “1”. U ovom slučaju, funkcija vraća “1”, budući da prva pronađena riječ “Kind” počinje prvim znakom teksta.
Ako funkciji naznačimo početak pretraživanja, na primjer, od drugog znaka, tada će rezultat izračuna funkcije biti "8".
Primjer 4. Upotreba zamjenskih znakova u funkciji SEARCH u Excelu
Funkcija uzima u obzir zamjenske znakove prilikom pretraživanja. U gornjem primjeru tražimo tekst “c * l”. Prisustvo zamjenskog znaka “*” u ovom upitu znači da tražimo bilo koju riječ koja počinje slovom “c” i završava se slovom “l”, a šta se nalazi između ova dva slova nije važno. Kao rezultat toga, funkcija vraća vrijednost "3", jer je u riječi "Excel" koja se nalazi u ćeliji A2, slovo "c" na trećoj poziciji.
Recimo da vaš izvještaj sadrži tabelu s puno podataka i mnogo kolona. Izuzetno je teško izvršiti vizuelnu analizu ovakvih tabela. A jedan od zadataka za rad sa izvještajem je - analiza podataka o zaglavljima redova i kolona koji se odnose na određeni mjesec. Na prvi pogled, ovo je vrlo jednostavan zadatak, ali se ne može riješiti jednom standardnom funkcijom. Da, naravno možete koristiti alat: "HOME" - "Uređivanje" - "Pronađi" CTRL + F da pozovete prozor za pretragu vrijednosti u Excel listu. Ili kreirajte pravilo uvjetnog oblikovanja za tablicu. Ali tada neće biti moguće izvršiti daljnja izračunavanja s dobivenim rezultatima. Stoga je potrebno kreirati i pravilno primijeniti odgovarajuću formulu.
Pronalaženje vrijednosti u Excel nizu
Šema za rješavanje problema izgleda otprilike ovako:
- u ćeliju B1 upisujemo podatke koji nas zanimaju;
- ćelija B2 će prikazati zaglavlje kolone koje sadrži vrijednost ćelije B1
- ćelija B3 će prikazati ime reda koji sadrži vrijednost ćelije B1.
U stvari, morate tražiti koordinate u Excelu. čemu služi? Vrlo često moramo dobiti koordinate tabele po vrijednosti. Pomalo kao inverzna analiza matrice. Konkretan primjer ukratko izgleda otprilike ovako. Cilj u brojevima je početna vrijednost, potrebno je odrediti ko je i kada najbliži ovom cilju. Kao primjer, koristit ćemo jednostavnu matricu podataka koja izvještava o broju prodatih artikala za tri kvartala, kao što je prikazano na donjoj slici. Važno je da svi brojevi budu konzistentni. Ako ne želite ručno kreirati i popunjavati Excel tablicu od nule, onda na kraju članka možete preuzeti gotov primjer.
Uzastopno ćemo razmotriti rješenja različite složenosti, a na kraju članka - konačni rezultat.
Pronalaženje vrijednosti u Excel koloni
Prvo, naučimo kako dobiti naslove stupaca tablice prema vrijednosti. Da biste to učinili, slijedite ove korake:
- U ćeliju B1 unesite vrijednost preuzetu iz tabele 5277 i označite njenu pozadinu plavom bojom radi čitljivosti polja za unos (dalje ćemo unijeti druge brojeve u ćeliju B1 kako bismo eksperimentirali s novim vrijednostima).
- U ćeliju C2 unesite formulu da dobijete zaglavlje stupca tabele koji sadrži ovu vrijednost:
- Nakon unosa formule za potvrdu, pritisnite kombinaciju prečaca CTRL + SHIFT + Enter, jer se formula mora izvršiti u nizu. Ako je sve urađeno ispravno, vitičaste zagrade () će se pojaviti na rubovima trake formule.
Pronalaženje vrijednosti u Excel stringu
Sada dobijamo broj reda za istu vrijednost (5277). Da biste to učinili, unesite sljedeću formulu u ćeliju C3:
Nakon unosa formule za potvrdu, ponovo pritisnite kombinaciju tipki CTRL + SHIFT + Enter i dobijete rezultat:
Formula je vratila broj 9 - pronašla je naslov reda lista prema odgovarajućoj vrijednosti u tabeli. Kao rezultat, imamo punu adresu vrijednosti D9.
Kako dobiti zaglavlje kolone i ime reda tabele
Sada ćemo naučiti kako dobiti koordinate ne cijelog lista, već trenutne tablice po vrijednosti. Jednom riječju, trebamo pronaći zaglavlja po vrijednosti 5277 umjesto D9:
- za kolonu tabele - mart;
- za liniju - Item4.
Da bismo riješili ovaj problem, koristit ćemo formulu s već dobivenim vrijednostima u ćelijama C2 i C3. Da bismo to uradili, radimo ovo:
Kao rezultat, interne koordinate tabele se dobijaju po vrednosti - mart; Stavka 4:
Pronalaženje duplih vrijednosti u Excel rasponu
Za kontrolu prisutnosti duplikata među vrijednostima tablice, kreirat ćemo formulu koja nas može obavijestiti o prisutnosti duplikata i prebrojati njihov broj. Da biste to učinili, unesite formulu u ćeliju E2:
Štoviše, za raspon tabelarnog odjeljka kreirat ćemo pravilo uvjetnog oblikovanja:
Kao što vidite, u prisustvu duplikata, formula za naslove uzima naslov od prvog duplikata horizontalno (s lijeva na desno). A formula za dobijanje imena (broja) reda uzima broj od prvog duplikata vertikalno (od vrha do dna). Postoje 2 načina da popravite ovo rješenje:
U ovom slučaju mijenjamo formule ili jednu ili drugu, ali ne dvije odjednom. Vrijedi podsjetiti da bi stara formula trebala ostati u ćeliji C3:
Ovdje su koordinate prvog duplikata prikazane okomito (od vrha do dna) - I7 za list i kolovoz; Proizvod2 za sto. Ostavimo ovu opciju za sljedeći konačni primjer.
Pronađite najbližu vrijednost u Excel rasponu
Ova tabela još uvek nije savršena. Uostalom, kada analizirate, morate tačno znati sve njegove vrijednosti. Ako formula ne pronađe uneseni broj u ćeliji B1 u tabeli, onda se vraća greška - #VRIJEDNOST! Bilo bi idealno kada bi formula, u nedostatku početnog broja u tabeli, sama izabrala najbližu vrijednost koju tabela sadrži. Da biste kreirali takav program za analizu tabela u ćeliji F1, unesite novu formulu:
Zatim slijedi u svim ostalim formulama promijenite vezu umjesto B1 treba da bude F1! Također morate promijeniti vezu u uvjetnom formatu. Odaberite: HOME - Stilovi - Uvjetno oblikovanje - Upravljanje pravilima - Uredi pravilo. I ovdje u parametrima navedite F1 umjesto B1. Da biste provjerili rad programa, unesite u ćeliju B1 broj koji nije u tabeli, na primjer: 8000. Ovo će dovesti do konačnog rezultata:
Sada možete unijeti bilo koju početnu vrijednost, a program će sam odabrati najbliži broj koji tabela sadrži. Zatim prikazuje naslov kolone i naziv reda za trenutnu vrijednost. Na primjer, ako unesete broj 5000, dobićemo novi rezultat:
Naš program u Excelu pronašao je najbližu vrijednost 4965 za početnu vrijednost - 5000. Takav program može biti koristan za automatsko rješavanje različitih analitičkih problema u poslovnom planiranju, postavljanju ciljeva, pronalaženju racionalnog rješenja itd. A rezultirajući redovi i kolone vam omogućavaju da dodatno proširite računske mogućnosti ove vrste izvještaja koristeći nove Excel formule.
Često, kada radite s podacima u Excelu, morate selektivno brisati ili sakrivati neke informacije, najčešće su to redovi ili stupci koji sadrže ili ne sadrže određene riječi, slova, brojeve, simbole ili njihove kombinacije. Standardni Excel alati kao što su pretraga, filter i napredni filter pomažu u tome. Ako ovi alati nisu dovoljni za rješavanje problema, VBA priskače u pomoć.
Kako pronaći određene riječi u Excel redovima? Pronađite riječi, slova, brojeve i simbole u Excelu
Najjednostavniji način pretraživanja zadate riječi, slova, broja, simbola ili njihove kombinacije je standardna pretraga. Svi parametri pretrage unose se u dijalog Pronađi i zameni, koji se može pozvati iz glavnog menija, ili kombinacijom prečice Ctrl + f (gde je f prvo slovo engleske reči find - find). Pored redovnog pretraživanja, možete izvršiti i zamjenu pretraživanja.
Korištenje filtera za pronalaženje određene riječi, slova, broja ili simbola u Excelu
Možete koristiti filtriranje da odaberete redove koji sadrže informacije koje definira korisnik. U programu Excel 2007 i novijim, na primjer, pored uobičajenog filtera teksta, postoji filter po boji ispune ćelije i boji fonta. Filter teksta vam omogućava da koristite uslove kao što su "jednako ...", "nije jednako ...", "počinje sa ...", "završava sa ...", "sadrži ...", "ne sadrži...". Nakon što su sve potrebne linije filtrirane, možete izvršiti sve radnje s njima, uključujući brisanje linija.
Kako programski pronaći i izbrisati određene redove u Excelu koristeći VBA Like operator?
Iz raznih razloga, standardni Excel alati nisu uvijek prikladni za rješavanje određenih zadataka. Ispod je programski kod makronaredbe koji vam omogućava da pronađete tekst definiran šablonom u ćelijama korištenog raspona i izbrišete cijeli red aktivnog radnog lista koji sadrži ćeliju sa navedenim tekstom. Željeni tekst se dodjeljuje varijabli "Shablon" pomoću uzorak koji odgovara posebnim znakovima .
Sub Udalenie_Strok_Po_Shablonu () Dim r As Long, FirstRow As Long, LastRow As Long Dim Region As Range, iRow As Range, Cell As Range Dim Shablon As String Shablon = "Ovdje unesite tekst za pretragu" Postavite Region = ActiveSheet.UsedRange FirstRow = Region Red LastRow = Region.Row - 1 + Region.Rows.Count Za r = LastRow To FirstRow Korak -1 Postavite iRow = Region.Rows (r - FirstRow + 1) Za svaku ćeliju u iRow.Ćelije Ako je ćelija poput Shablon onda redovi ( r) Izbriši kraj ako sljedeća ćelija sljedeća r Kraj pod
Ovaj makro koristi fuzzy pretragu i VBA Like operator za poređenje da pronađe fraze koje su mu potrebne da uporedi nizove sa šablonom. Prilikom upoređivanja stringova, ovaj operator razlikuje velika i mala slova, a rezultat poređenja ovisi o instrukciji Opcija Usporedi .
Funkcije Instr i Find VBA također se mogu koristiti za rješavanje takvih problema.
Slične radnje obavljaju Excel dodatak, čija upotreba vam omogućava da unesete tekst za pretragu bez posebnih znakova u dijaloški okvir i postavite različita područja pretraživanja.
Uz veliku količinu podataka na Excel listu, prije ili kasnije morat ćete potražiti preraspodijeljenu vrijednost. Jednostavna pretraga se izvodi pomoću komande "Pronađi i izaberi" koja se nalazi u grupi "Uređivanje" na kartici "Početna".
Pogledajte i video verziju članka.
Također možete pozvati dijaloški okvir za pretragu koristeći prečicu na tastaturi " Ctrl + F«.
U stvari, dijaloški okvir se zove " Pronađite i zamijenite"Zato što rješava ova dva povezana problema i ako se poziva kroz vruću kombinaciju" Ctrl + H", Tada će sve biti isto, samo će se otvoriti prozor sa aktiviranom karticom " Zamijeni ".
Ovdje je, na prvi pogled, sve jednostavno: unio sam riječ i pronašao potrebnu ćeliju na Excel listu. Naravno, ne može se isključiti ovakva razvojna opcija kada će se pretraga obaviti brzo, međutim, vjerojatnija je situacija kada unesete željenu vrijednost, ali je procesor proračunskih tablica ne traži ili morate izvršiti labavu traži kada se željeni tekst približno zapamti, što se, inače, često dešava kada se traže osobe po prezimenu.
U ovom materijalu razmotrit ćemo nijanse i tajne okvira za pretraživanje koje većini korisnika nisu poznate.
Neke postavke koje mogu pomoći u rješavanju problema skrivene su iza dugmeta "Opcije".
Razmotrite situaciju kada trebate pronaći zaposlenika Komarova Aleksandra Ivanoviča, ali postoji niz faktora koji uzrokuju poteškoće:
- ne znamo kako je napisano, sa potpunim dešifriranjem imena i patronima, ili samo inicijala;
- postoji određena nesigurnost u pogledu toga koje je drugo slovo "a" ili "o";
- nije jasno kako se tačno prvo evidentira zaposlenik, prezime, pa ime i patronim, ili obrnuto.
Komarov Aleksandar Ivanovič se može pisati kao: A. Komarov, Komarov Aleksandar, K a marov Aleksandar I. itd. opcija je dovoljna.
Da biste izvršili olabavu pretragu, trebali biste koristiti zamjenske simbole, oni se također nazivaju divljim simbolima. Prozor za pretragu u Excel-u podržava dva takva znaka: "*" i "?":
- "*" Odgovara bilo kojem broju znakova;
- "?" odgovara bilo kojem pojedinačnom znaku.
Ovdje se odmah postavlja pitanje, što ako trebate potražiti znak pitanja ili zvjezdicu (poznat kao znak množenja)? Sve je jednostavno, samo stavite tildu "~" ispred željenih znakova, odnosno, ako trebate tražiti tildu, onda morate staviti dvije tilde.
Dakle, da biste izvršili netačnu pretragu za pomenutim prezimenom, u polje za pretragu upišite "K? Mars" i kliknite na dugme "Pronađi sve", u tom slučaju će svi zapisi koji ispunjavaju zadati uslov biti prikazani u prozor za pretragu, ali samo ako nije označen parametarom "Ćelija kao cjelina".
Parametar "Ćelija kao cjelina" znači traženje strogo tekstualnog niza unesenog u polje za pronalaženje i ništa drugo, drugim riječima, ako osim prezimena postoje i inicijali, zapis neće biti pronađen.
Istovremeno, ako malo ponovo izgradite upit "* K? Mars *", tada će zapisi biti pronađeni bez obzira da li je parametar "Cijela ćelija" aktivan.
Postoji nekoliko važnih pravila koje treba imati na umu kada pretražujete u Excelu:
- Pretraživanje se vrši u odabranom području, a ako nema odabira, onda na cijelom listu. Stoga se ne biste trebali iznenaditi ako slučajno odaberete par praznih ćelija, pokrenete okvir za pretraživanje i nećete moći ništa pronaći.
- Pretraživanje ne uzima u obzir formatiranje, stoga ne biste trebali dodavati nikakve simbole valute.
- Kada radite sa datumima, bolje ih je tražiti u zadanom formatu za određeni sistem, u ovom slučaju Excel će pronaći sve datume koji ispunjavaju uslov. Na primjer, ako sistem koristi format d / m / y, tada će upit za pretragu * / 12/2015 prikazati sve datume za decembar 2015. godine, bez obzira na to kako su formatirani (28.12.2015., 28.12.2015. ili 28. decembra 2015. itd.).
U Microsoft Excel dokumentima, koji se sastoje od velikog broja polja, često je potrebno pronaći određene podatke, naziv reda itd. Vrlo je nezgodno kada morate pregledavati ogroman broj redova da biste pronašli željenu riječ ili izraz. Ugrađena Microsoft Excel pretraga pomoći će uštedjeti vrijeme i živce. Pogledajmo kako to funkcionira i kako ga koristiti.
Funkcija pretraživanja u Microsoft Excel-u nudi mogućnost pronalaženja željenog teksta ili numeričkih vrijednosti kroz prozor Find and Replace. Osim toga, aplikacija ima mogućnost napredne pretrage podataka.
Metoda 1: jednostavno pretraživanje
Jednostavna pretraga podataka u programu Excel omogućava vam da pronađete sve ćelije koje sadrže skup znakova (slova, brojeve, riječi, itd.) unesenih u prozor za pretraživanje, bez obzira na velika i mala slova.
Metoda 2: pretraživanje po određenom rasponu ćelija
Ako imate prilično veliku tablicu, tada u ovom slučaju nije uvijek zgodno pretraživati cijeli list, jer rezultati pretraživanja mogu sadržavati ogroman broj rezultata koji nisu potrebni u određenom slučaju. Postoji način da ograničite prostor za pretragu samo na određeni raspon ćelija.
Metoda 3: Napredno pretraživanje
Kao što je gore spomenuto, tokom normalnog pretraživanja, apsolutno sve ćelije koje sadrže sekvencijalni skup znakova pretraživanja u bilo kojem obliku, bez obzira na velika i mala slova, uključene su u rezultate pretraživanja.
Osim toga, u rezultate može ući ne samo sadržaj određene ćelije, već i adresa elementa na koji se ona odnosi. Na primjer, ćelija E2 sadrži formulu koja je zbir ćelija A4 i C3. Ovaj iznos je 10, a ovo je broj koji se pojavljuje u ćeliji E2. Ali, ako u pretrazi postavimo broj "4", tada će ista ćelija E2 biti među rezultatima pretrage. Kako se ovo moglo dogoditi? Samo ta ćelija E2 sadrži adresu ćelije A4 kao formulu, koja uključuje samo željenu cifru 4.
Ali kako odsjeći takve i druge očigledno neprihvatljive rezultate pretraživanja? Upravo u te svrhe postoji napredna pretraga u Excelu.
- Nakon otvaranja prozora Pronađite i zamijenite na bilo koji gore opisani način, kliknite na dugme "Opcije".
- U prozoru se pojavljuje niz dodatnih alata za upravljanje pretragom. Prema zadanim postavkama, svi ovi alati su u istom stanju kao i uobičajena pretraga, ali ako je potrebno, mogu se izvršiti podešavanja.
Podrazumevano, funkcije Osetljivo na velika i mala slova i Cele ćelije onemogućeno, ali ako označimo kućice pored odgovarajućih stavki, tada će se u ovom slučaju prilikom formiranja rezultata uzeti u obzir uneseni slučaj i tačno podudaranje. Ako unesete riječ s malim slovom, tada u rezultatima pretraživanja ćelije koje sadrže pravopis ove riječi velikim slovom, kako bi to bilo po defaultu, više neće biti uključene. Osim toga, ako je funkcija omogućena Cele ćelije, tada će samo elementi koji sadrže tačan naziv biti dodati rezultatima. Na primjer, ako unesete upit za pretraživanje "Nikolaev", tada ćelije koje sadrže tekst "Nikolaev AD" neće biti dodane u rezultate pretraživanja.
Podrazumevano se pretražuje samo aktivni Excel radni list. Ali, ako je parametar "Traži" preći ćeš na poziciju "u knjizi", tada će se pretraga izvršiti na svim listovima otvorene datoteke.
U parametru "Pregledaj" možete promijeniti smjer pretraživanja. Podrazumevano, kao što je gore pomenuto, pretraga se vrši red po red redom. Pomeranjem prekidača u položaj "po kolonama", možete postaviti redoslijed generiranja rezultata izdavanja, počevši od prve kolone.
U grafikonu "područje pretrage" određuje koji se specifični elementi traže. Podrazumevano, to su formule, odnosno podaci koji se, kada kliknete na ćeliju, prikazuju na traci formule. To može biti riječ, broj ili referenca ćelije. Istovremeno, program prilikom pretraživanja vidi samo vezu, a ne rezultat. O ovom efektu je bilo reči gore. Da biste pretraživali tačno po rezultatima, prema podacima koji su prikazani u ćeliji, a ne u traci formule, morate preurediti prekidač iz pozicije "Formule" na poziciji "Vrijednosti"... Osim toga, moguće je pretraživati po bilješkama. U tom slučaju pomaknite prekidač u položaj "Bilješke".
Možete još preciznije postaviti pretragu klikom na dugme "Format".
Ovo otvara prozor za format ćelije. Ovdje možete postaviti format ćelija koje će učestvovati u pretraživanju. Možete postaviti ograničenja za format broja, poravnanje, font, obrub, ispunu i zaštitu, jedan od ovih parametara ili njihovu kombinaciju.
Ako želite da koristite format određene ćelije, tada na dnu prozora kliknite na dugme "Koristite format ove ćelije...".
Nakon toga, pojavljuje se alatka za kapaljku. Pomoću njega možete odabrati ćeliju čiji format ćete koristiti.
Nakon što je format pretraživanja konfigurisan, kliknite na dugme "UREDU".
Postoje slučajevi kada ne trebate tražiti određenu frazu, već pronaći ćelije koje sadrže riječi za pretraživanje bilo kojim redoslijedom, čak i ako su odvojene drugim riječima i simbolima. Zatim ove riječi moraju biti istaknute s obje strane sa "*". Sada će rezultati pretrage prikazati sve ćelije u kojima se nalaze date riječi bilo kojim redoslijedom.
- Kada su postavke pretrage postavljene, kliknite na dugme Pronađite sve ili Find Next da odete na rezultate pretrage.
Kao što vidite, Excel je prilično jednostavan, ali u isto vrijeme vrlo funkcionalan skup alata za pretraživanje. Da biste napravili najjednostavnije škripanje, dovoljno je pozvati prozor za pretragu, u njega uneti upit i kliknuti na dugme. Ali, u isto vrijeme, moguće je prilagoditi individualnu pretragu s velikim brojem različitih parametara i dodatnih postavki.
