funkcija SEARCH( ) , engleska verzija SEARCH(), pronalazi prvo pojavljivanje jednog tekstualnog niza u drugom nizu i vraća početnu poziciju pronađenog niza.

Sintaksa funkcije

TRAŽI(search_text;lookup_string;[početna_pozicija])

Search_text- tekst koji treba pronaći.

Lookup_string- tekst u kojem se traži Search_text.

start_position je pozicija znaka u lookup_string od koje bi pretraživanje trebalo započeti. Ako je argument start_position izostavljeno, pretpostavlja se vrijednost 1.

U argumentu search_text možete koristiti - upitnik (?) i zvjezdicu (*). Znak pitanja odgovara bilo kojem karakteru; zvjezdica - bilo koji niz znakova. Ako želite da pronađete upitnik ili zvjezdicu u tekstu, stavite tildu (~) ispred njih.

Ako search_text nije pronađen, #VALUE!

Funkcija SEARCH() ne razlikuje velika i mala slova. Za pretraživanje osjetljivo na velika i mala slova, koristite funkciju FIND().

Primjeri

Formula =SEARCH("do";"Prvi kanal")će vratiti 8 jer pismo To je na 8. poziciji slijeva.

Pustite u ćeliju A2 string ušao Prvi kanal je najbolji. Formula =TRAŽI(CHAR(32),A2)će vratiti 7, jer razmak (šifra 32) je na 7. poziciji.

Formula =SEARCH("#???#";"Članak #123# ID")će tražiti u nizu " Članak #123# ID" je niz od 5 znakova koji počinje i završava sa #.

Da biste pronašli poziciju drugog pojavljivanja slova "a" u nizu "mama oprala okvir" koristite formulu =SEARCH("a","majka je oprala okvir",SEARCH("a","majka je oprala okvir")+1). Da biste utvrdili postoji li treće pojavljivanje slova "m" u nizu "mama oprala okvir" koristite formulu =IF(DLSTR(SUBSTITUTE("mama je oprala okvir";"m";"";3))=DLSTR("mama je oprala okvir");"Nema trećeg pojavljivanja m";"Postoji treće pojavljivanje m"; pojava m")

Formula =SEARCH("blago?";"izvještaj") vratiće 3, tj. riječ "izvještavanje" sadrži riječ od 5 slova, od kojih prva 4 blago(počevši od trećeg slova riječi izvještaj).

Funkcija FIND() naspram SEARCH()

Funkcija FIND() je osjetljiva na velika i mala slova i ne dozvoljava zamjenske znakove. Za pretraživanja bez obzira na velika i mala slova, kao i za pretraživanja koristeći zamjenski znakovi koristite funkciju SEARCH().

Odnos prema funkcijama LEFT() , RIGHT() i MID().

Funkcija SEARCH() se može koristiti zajedno sa funkcijama LEFT() , RIGHT() i MID().

Na primjer, u ćeliji A2 sadrži prezime i ime "Ivanov Ivan", zatim formulu =LEFTCHAR(A2,TRAŽI(CHAR(32),A2)-1)će izdvojiti prezime i =DESNO(A2,DUGA(A2)-TRAŽI(CHAR(32),A2))- ime. Ako postoji više od jednog razmaka između imena i prezimena, onda da bi gornje formule radile, koristite funkciju TRIM().

Osnovna svrha Excel uredskog programa je implementacija proračuna. Dokument ovog programa (Knjiga) može sadržavati mnogo listova sa dugim tabelama ispunjenim brojevima, tekstom ili formulama. Automatsko brzo pretraživanje omogućava vam da pronađete potrebne ćelije u njima.

Jednostavna pretraga

Da biste tražili vrijednost u Excel tabeli, na kartici Početna otvorite padajuću listu alata Pronađi i zamijeni i kliknite na stavku Pronađi. Isti efekat se može postići upotrebom prečice na tastaturi Ctrl + F.

U najjednostavnijem slučaju, u prozoru Find and Replace koji se pojavi, unesite vrijednost koju tražite i kliknite Pronađi sve.

Kao što vidite, rezultati pretrage su se pojavili na dnu dijaloškog okvira. Pronađene vrijednosti su podvučene crvenom bojom u tabeli. Ako umjesto "Pronađi sve" kliknete na "Pronađi sljedeće", tada će se prva pretraživati ​​prva ćelija sa ovom vrijednošću, a kada ponovo kliknete, druga.

Tekst se traži na isti način. U ovom slučaju, tekst za pretragu se upisuje u traku za pretragu.

Ako se podaci ili tekst ne pretražuju u cijeloj Excel tabeli, tada se prvo mora odabrati područje pretraživanja.

napredna pretraga

Pretpostavimo da želite da pronađete sve vrednosti između 3000 i 3999. U ovom slučaju, ukucali biste 3??? u string za pretragu. Divlja karta "?" zamjenjuje bilo koju drugu.

Analizirajući rezultate izvršene pretrage, može se primijetiti da je uz tačnih 9 rezultata program proizveo i neočekivane, podvučene crvenom bojom. Oni su povezani sa prisustvom broja 3 u ćeliji ili formuli.

Možete biti zadovoljni većinom dobijenih rezultata, zanemarujući pogrešne. Ali funkcija pretraživanja u programu Excel 2010 može raditi mnogo preciznije. Ovo je alatka Opcije u dijaloškom okviru.

Klikom na "Opcije" korisnik može izvršiti naprednu pretragu. Prije svega, obratimo pažnju na stavku "Područje pretraživanja", u kojoj je vrijednost "Formule" postavljena prema zadanim postavkama.

To znači da je pretraga izvršena, uključujući i one ćelije u kojima ne postoji vrijednost, već formula. Prisustvo broja 3 u njima dalo je tri netačna rezultata. Ako odaberete "Vrijednosti" kao područje pretraživanja, tada će se pretraživati ​​samo podaci i netačni rezultati povezani sa ćelijama formule će nestati.

Da biste se riješili jedinog preostalog netočnog rezultata u prvom redu, u prozoru za naprednu pretragu odaberite stavku "Cijela ćelija". Nakon toga, rezultat pretrage postaje 100% tačan.

Takav rezultat bi se mogao postići trenutnim odabirom stavke “Cijela ćelija” (čak i ostavljanjem vrijednosti “Formule” u “Području pretraživanja”).

Sada se okrenimo stavci "Traži".

Ako umjesto zadane "Na listu" odaberete vrijednost "U knjizi", onda nema potrebe da se nalazite na listu ćelija koje tražite. Snimak ekrana pokazuje da je korisnik pokrenuo pretragu dok je bio na praznom listu 2.

Sljedeća stavka u prozoru za naprednu pretragu je "Pretraži", koja ima dva značenja. Podrazumevano je postavljeno na "po redovima", što znači slijed skeniranja ćelija po redovima. Odabirom druge vrijednosti - "po kolonama", samo će se promijeniti smjer pretraživanja i redoslijed rezultata.

Kada pretražujete u Microsoft Excel dokumentima, možete koristiti i drugi zamjenski znak - "*". Ako se smatra "?" znači bilo koji znak, onda "*" ne zamjenjuje jedan, već bilo koji broj znakova. Ispod je snimak ekrana pretrage za Louisianu.

Ponekad pretraga mora biti osjetljiva na velika i mala slova. Ako je riječ louisiana napisana velikim slovom, rezultati pretraživanja se neće promijeniti. Ali ako u prozoru za naprednu pretragu odaberete "Razlikovanje velikih i malih slova", pretraga neće biti uspješna. Program će riječi Louisiana i louisiana smatrati različitim i, naravno, neće pronaći prvu od njih.

Search Varieties

Pronalaženje poklapanja

Ponekad je potrebno otkriti duple vrijednosti u tabeli. Da biste tražili podudaranja, prvo morate označiti raspon pretraživanja. Zatim, na istoj kartici Početak, u grupi Stilovi, otvorite alat za uslovno oblikovanje. Zatim uzastopno odaberite stavke "Pravila odabira ćelije" i "Duplicate vrijednosti".

Rezultat je prikazan na slici ispod.

Ako je potrebno, korisnik može promijeniti boju vizualnog prikaza odgovarajućih ćelija.

Filtracija

Druga vrsta pretrage je filtriranje. Pretpostavimo da korisnik želi pronaći numeričke vrijednosti u stupcu B između 3000 i 4000.

Kao što vidite, prikazuju se samo redovi koji zadovoljavaju uneseni uslov. Svi ostali su bili privremeno skriveni. Za povratak u početno stanje, ponovite korak 2.

Razmotrene su različite opcije pretraživanja koristeći Excel 2010 kao primjer. Kako pretraživati ​​u Excelu drugih verzija? Postoji razlika u prelasku na filtriranje u verziji 2003. U meniju "Podaci" izaberite redom naredbe "Filter", "Autofilter", "Stanje" i "Automatski filter korisnika".

Video: Pretražujte u Excel tabeli

Također članci o radu sa tabelama u Excel-u:

• Formatirajte tabele u Excel-u
• Kreiranje tabela u excelu
• Kreiranje pivot tabele u excelu
• Kako odabrati cijelu tabelu u excelu?

Među hiljadama redova i desetina kolona podataka ručno u Excel tabeli, gotovo je nemoguće nešto pronaći. Jedina opcija je korištenje neke vrste funkcije pretraživanja, a zatim ćemo pogledati kako pretraživati ​​u Excel tabeli.

Da biste tražili podatke u Excel tabeli, morate koristiti stavku menija "Pronađi i odaberi" na kartici "Početna", u kojoj je potrebno odabrati opciju "Pronađi" ili koristiti kombinaciju tipki "Ctrl + F" za pozivanje to.

Na primjer, pokušajmo pronaći traženi broj među podacima u našoj tablici, jer se prilikom traženja brojeva moraju uzeti u obzir neke suptilnosti pretraživanja. Potražićemo broj "10" u Excel tabeli.

Nakon što odaberete željenu stavku menija u okviru za pretragu koji se pojavi, unesite željenu vrijednost. Imamo dvije opcije za traženje vrijednosti u Excel tablici, ovo je pronalaženje svih podudaranja odjednom klikom na dugme "Pronađi sve" ili odmah pregledavanje svake pronađene ćelije klikom na dugme "Pronađi sljedeće" svaki put. Prilikom korištenja dugmeta "Pronađi sljedeće", također treba uzeti u obzir trenutnu lokaciju aktivne ćelije, jer će pretraga početi s ove pozicije.

Pokušajmo pronaći sve vrijednosti odjednom, dok će sve pronađeno biti navedeno u prozoru ispod postavke pretraživanja. Ako ostavite sve zadane postavke, onda rezultat pretrage neće biti baš onakav kakav smo očekivali.

Da biste ispravno pretražili podatke u Excel tabeli, kliknite na dugme "Opcije" i konfigurišite oblast za pretragu. Sada se željena vrijednost traži čak i u formulama koje se koriste u ćelijama za izračune. Pretragu moramo navesti samo u vrijednostima, a po želji možete odrediti i format podataka koje tražite.

Kada tražite riječi u Excel tabeli, također biste trebali uzeti u obzir sve ove suptilnosti i, na primjer, čak možete uzeti u obzir i mala slova.

I na kraju, pogledajmo kako tražiti podatke u Excelu samo u potrebnom području lista. Kao što možete vidjeti iz našeg primjera, željena vrijednost "10" nalazi se u svim stupcima podataka odjednom. Ako ovu vrijednost trebate pronaći, recimo, samo u prvoj koloni, morate odabrati ovu kolonu ili bilo koji raspon vrijednosti u kojem želite pretraživati, a zatim započeti pretraživanje.

Naša prva kolona ima samo dvije vrijednosti jednake '10', tako da bi korištenje opcije 'Pronađi sve' trebalo prikazati samo dva rezultata pretraživanja na listi.

U Microsoft Excel dokumentima, koji se sastoje od velikog broja polja, često je potrebno pronaći određene podatke, naziv reda i tako dalje. Vrlo je nezgodno kada morate pregledavati ogroman broj redova da biste pronašli pravu 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 Excelu

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 naprednog pretraživanja podataka.

Metoda 1: jednostavno pretraživanje

Jednostavna pretraga podataka u Excel-u omogućava vam da pronađete sve ćelije koje sadrže skup znakova unetih u okvir za pretragu (slova, brojevi, riječi, itd.) bez osjetljivosti na velika i mala slova.

1. Dok ste na kartici "Početna", kliknite na dugme "Pronađi i izaberi", koje se nalazi na traci u bloku alata "Uređivanje". U meniju koji se pojavi odaberite stavku "Pronađi ...". Umjesto ovih radnji, možete jednostavno otkucati kombinaciju tipki Ctrl + F na tastaturi.
2. Nakon što dođete do odgovarajućih stavki na traci ili pritisnete kombinaciju prečaca, prozor Find and Replace će se otvoriti na kartici Find. Trebamo je. U polje "Pronađi" unesite riječ, simbole ili izraze po kojima ćemo pretraživati. Kliknite na dugme "Pronađi sledeće" ili na dugme "Pronađi sve".
3. Kada kliknete na dugme "Pronađi dalje", prelazimo na prvu ćeliju koja sadrži unesene grupe znakova. Sama ćelija postaje aktivna.

   Pretraživanje i izlaz rezultata vrši se red po red. Prvo se obrađuju sve ćelije prvog reda. Ako nije pronađen nijedan podatak koji odgovara uslovu, program počinje tražiti u drugom redu, i tako dalje, dok ne nađe zadovoljavajući rezultat.

   Znakovi za pretragu ne moraju biti nezavisni elementi. Dakle, ako je izraz "desno" dat kao upit, tada će sve ćelije koje sadrže dati sekvencijalni skup znakova, čak i unutar riječi, biti predstavljene u izlazu. Na primjer, relevantan upit u ovom slučaju bi bila riječ "Na desno". Ako unesete broj "1" u tražilicu, tada će odgovor uključivati ​​ćelije koje sadrže, na primjer, broj "516".

   Da biste prešli na sljedeći rezultat, ponovo kliknite na dugme Find Next.

   Na ovaj način možete nastaviti sve dok prikaz rezultata ne počne na novom krugu.

4. Ako pri pokretanju postupka pretraživanja kliknete na dugme „Pronađi sve“, svi rezultati pretrage će biti prikazani u obliku liste na dnu prozora za pretragu. Ova lista sadrži informacije o sadržaju ćelija sa podacima koji zadovoljavaju upit za pretragu, adresi njihove lokacije, kao i listu i knjigu na koje se odnose. Da biste otišli na bilo koji od rezultata izdavanja, samo kliknite na njega lijevom tipkom miša. Nakon toga, kursor će skočiti na Excel ćeliju na koju je korisnik kliknuo.

Metoda 2: Pretraga unutar određenog raspona ć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.

1. Odaberite područje ćelija u kojem želimo pretraživati.
2. Ukucamo kombinaciju tipki Ctrl + F na tipkovnici, nakon čega će se pokrenuti poznati prozor "Pronađi i zamijeni". Dalje radnje su potpuno iste kao u prethodnoj metodi. Jedina razlika će biti u tome što se pretraga vrši samo u navedenom rasponu ćelija.

Metoda 3: Napredno pretraživanje

Kao što je gore spomenuto, tokom normalnog pretraživanja, apsolutno sve ćelije koje sadrže uzastopni skup znakova za pretraživanje u bilo kojem obliku, bez obzira na velika i mala slova, ulaze u rezultate problema.

Osim toga, u izlaz može ući ne samo sadržaj određene ćelije, već i adresa elementa na koji se odnosi. Na primjer, ćelija E2 sadrži formulu koja je zbir ćelija A4 i C3. Ovaj zbir je 10, a ovo je broj prikazan u ćeliji E2. Ali, ako u pretrazi postavimo broj "4", tada će ista ćelija E2 biti među izlaznim rezultatima. Kako se ovo moglo dogoditi? Samo ta ćelija E2 sadrži adresu ćelije A4 kao formulu, koja uključuje samo željeni broj 4.

Ali kako odsjeći takve i druge očigledno neprihvatljive rezultate pretraživanja? Upravo za ove svrhe postoji napredna Excel pretraga.

1. Nakon što otvorite prozor "Pronađi i zamijeni" na bilo koji gore opisani način, kliknite na dugme "Opcije".
2. U prozoru se pojavljuje niz dodatnih alata za upravljanje pretragom. Prema zadanim postavkama, svi ovi alati su u istom stanju kao i kod normalnog pretraživanja, ali možete izvršiti podešavanja ako je potrebno.

   Funkcije "Osjetljivo na velika i mala slova" i "Cijele ćelije" su prema zadanim postavkama onemogućene, ali ako označimo kućice pored odgovarajućih stavki, tada će se u ovom slučaju pri generiranju rezultata uzeti u obzir unesena velika i tačna podudarnost. Ako unesete riječ sa malim slovom, tada u rezultatima pretraživanja ćelije koje sadrže pravopis ove riječi velikim slovom, kao što bi to bilo po defaultu, više neće biti uključene. Osim toga, ako je omogućena funkcija "Cijele ćelije", tada će u izlaz biti dodati samo elementi koji sadrže tačan naziv. Na primjer, ako postavite upit za pretragu "Nikolaev", ćelije koje sadrže tekst "Nikolaev A.D." neće biti dodane u rezultate pretraživanja.

   Podrazumevano, pretraga se vrši samo na aktivnom Excel listu. Ali, ako parametar "Traži" prenesete na poziciju "U knjizi", pretraga će se izvršiti na svim listovima otvorene datoteke.

   U opciji Pregledaj možete promijeniti smjer pretraživanja. Prema zadanim postavkama, kao što je gore navedeno, pretraga se vrši redom, red po red. Pomicanjem prekidača na poziciju “Po kolonama” možete postaviti redoslijed generiranja rezultata pretraživanja, počevši od prve kolone.

   U koloni "Područje pretraživanja" određuje se među kojim se konkretnim elementima vrši pretraga. Po defaultu, 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 riječi gore. Da biste pretraživali tačno po rezultatima, prema podacima koji su prikazani u ćeliji, a ne u traci formule, potrebno je premjestiti prekidač sa pozicije “Formule” na poziciju “Vrijednosti”. Osim toga, moguće je pretraživati ​​po bilješkama. U tom slučaju, prekidač se prebacuje u položaj "Napomene".

   Možete još preciznije odrediti pretragu klikom na dugme "Format".

   Ovo otvara prozor Format Cells. Ovdje možete postaviti format ćelija koje će učestvovati u pretraživanju. Možete postaviti ograničenja za format broja, poravnanje, font, obrub, sjenčanje i zaštitu, jednu od ovih opcija ili njihovu kombinaciju.

   Ako želite koristiti format određene ćelije, tada na dnu prozora kliknite na dugme "Koristi format ove ćelije ...".

   Nakon toga se pojavljuje alat u obliku kapaljke. Pomoću njega možete odabrati ćeliju čiji format ćete koristiti.

   Nakon što je format pretraživanja konfigurisan, kliknite na dugme "OK".

   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 treba istaknuti s obje strane znakom "*". Sada će rezultati pretrage prikazati sve ćelije u kojima se ove riječi nalaze bilo kojim redoslijedom.

3. Kada su postavke pretrage postavljene, trebate kliknuti na dugme "Pronađi sve" ili "Pronađi sljedeće" da biste otišli na rezultate pretraživanja.

Kao što vidite, Excel program 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, postoji mogućnost postavljanja individualne pretrage s velikim brojem različitih parametara i dodatnih postavki.

Drago nam je što smo vam mogli pomoći da riješite problem.

Postavite svoje pitanje u komentarima, detaljno opišite suštinu problema. Naši stručnjaci će nastojati da odgovore što je prije moguće.

Da li vam je ovaj članak pomogao?

Značajan dio zadataka koji se rješavaju pomoću proračunskih tablica pretpostavljaju da korisnik već ima barem neke početne podatke za pronalaženje željenog rezultata. Međutim, Excel 2010 ima potrebne alate pomoću kojih možete riješiti ovaj problem naprotiv – odabrati prave podatke da biste dobili željeni rezultat.

"Traganje za rješenjem" je jedan od takvih alata koji je najpogodniji za "probleme optimizacije". A ako ga ranije niste morali koristiti, sada je vrijeme da ga popravite.

Dakle - počinjemo s instaliranjem ovog dodatka (pošto se neće pojaviti sam). Na sreću, sada se to može učiniti prilično jednostavno i brzo - otvorite meni "Usluga", a već u njemu "Dodaci"

Ostaje samo u koloni "Upravljanje" označiti "Excel dodatke", a zatim kliknuti na dugme "Idi".

Nakon ove jednostavne radnje, dugme za aktiviranje "Traži rješenje" će biti prikazano u "Podacima". Kao što je prikazano na slici

Pogledajmo kako se Excel 2010 pronalaženje rješenja koristi ispravno uz nekoliko jednostavnih primjera.

Primjer jedan.

Recimo da ste šef velikog proizvodnog odjela i trebate pravilno raspodijeliti bonuse zaposlenima. Recimo da je ukupan iznos bonusa 100.000 rubalja, a potrebno je da bonusi budu proporcionalni platama.

Odnosno, sada moramo odabrati ispravan koeficijent proporcionalnosti kako bismo odredili veličinu bonusa u odnosu na platu.

Prije svega, potrebno je brzo sastaviti (ako već ne postoji) tablicu u kojoj će biti pohranjene početne formule i podaci prema kojima će se moći dobiti željeni rezultat. Za nas je ovaj rezultat ukupna vrijednost premije. A sada, pažnja - ciljnu ćeliju C8 treba povezati sa željenom promjenjivom ćelijom na adresi E2 pomoću formula. Ovo je kritično. U primjeru ih povezujemo pomoću međuformula koje su odgovorne za obračun bonusa za svakog zaposlenog (S2:S7).

Sada možete aktivirati "Traženje rješenja". Otvara se novi prozor u kojem treba da navedemo potrebne parametre.

Ispod "" je naša ciljna ćelija. Ona može biti samo jedna.

"" su moguće opcije optimizacije. Ukupno, možete odabrati "Maksimalne", "Minimalne" ili "Specifične" moguće vrijednosti. A ako vam je potrebna određena vrijednost, morate je navesti u odgovarajućoj koloni.

"" - može postojati nekoliko ćelija za promjenu (cijeli raspon ili odvojeno specificirane adrese). Uostalom, s njima će raditi Excel, sortirajući opcije tako da se dobije vrijednost navedena u ciljnoj ćeliji.

"" - Ako trebate postaviti ograničenja, tada biste trebali koristiti dugme "Dodaj", ali to ćemo razmotriti malo kasnije.

"" - dugme za prelazak na interaktivne proračune na osnovu programa koji smo naveli.

Ali sada se vratimo na mogućnost promjene našeg zadatka pomoću gumba "Dodaj". Ova faza je prilično odgovorna (ne manje od konstrukcije formula), jer je ograničenje koje vam omogućava da dobijete ispravan rezultat na izlazu. Ovdje je sve urađeno što je zgodnije moguće, tako da ih možete postaviti ne samo za cijeli raspon odjednom, već i za određene ćelije.

Da biste to učinili, možete koristiti niz specifičnih (i poznatih svim korisnicima programa Excel 2010) znakova "=", ">=", "

Imamo tabelu koja bilježi obim prodaje određene robe u različitim mjesecima. Podatke je potrebno pronaći u tabeli, a kriterijumi pretrage će biti naslovi redova i kolona. Ali pretraga se mora izvršiti odvojeno u rasponu reda ili stupca. Odnosno, koristit će se samo jedan od kriterija. Stoga se ovdje ne može koristiti funkcija INDEX, već je potrebna posebna formula.

Pronalaženje vrijednosti u Excel tabeli

Da bismo riješili ovaj problem, ilustrujemo primjer na shematskoj tablici koji ispunjava gore opisane uvjete.

List s tablicom za traženje vrijednosti vertikalno i horizontalno:

Iznad same tabele je red sa rezultatima. U ćeliju B1 unosimo kriterij za traženi upit, odnosno naslov kolone ili naziv reda. A u ćeliji D1, formula za pretraživanje bi trebala vratiti rezultat izračunavanja odgovarajuće vrijednosti. Nakon toga, druga formula će raditi u ćeliji F1, koja će već koristiti vrijednosti ćelija B1 i D1 kao kriterij za pronalaženje odgovarajućeg mjeseca.

Pronalaženje vrijednosti u Excel redu

Sada ćemo saznati u kom maksimalnom obimu i u kom mjesecu je bila maksimalna prodaja Good 4.

Za pretraživanje po kolonama:

Pronađeno u kojem mjesecu i koja je bila najveća prodaja proizvoda 4 u dva kvartala.

Princip rada formule za pronalaženje vrijednosti u Excel redu:

Prvi argument funkcije VLOOKUP (Vertical Lookup) sadrži referencu na ćeliju u kojoj se nalazi kriterij pretraživanja. Drugi argument specificira opseg ćelija za traženje tokom pretraživanja. Treći argument funkcije VLOOKUP mora sadržavati broj stupca iz kojeg treba uzeti vrijednost u odnosu na red s imenom Product 4. Ali pošto ovaj broj ne znamo unaprijed, koristimo funkciju COLUMN da kreiramo niz stupaca brojevi za opseg B4:G15.

Ovo omogućava funkciji VLOOKUP da prikupi cijeli niz vrijednosti. Kao rezultat toga, memorija pohranjuje sve odgovarajuće vrijednosti za svaki stupac u redu 4. (i to: 360; 958; 201; 605; 462; 832). Nakon toga, funkcija MAX treba samo uzeti maksimalni broj iz ovog niza i vratiti ga kao vrijednost za ćeliju D1, kao rezultat izračunavanja formule.

Kao što vidite, dizajn formule je jednostavan i koncizan. Na osnovu toga možete pronaći druge pokazatelje za određeni proizvod na sličan način. Na primjer, minimalna ili prosječna vrijednost prodaje koristeći funkcije MIN ili AVERAGE za ovo. Ništa vas ne sprječava da primijenite ovaj kostur formule koristeći složenije funkcije za implementaciju najudobnije analize izvještaja o prodaji.

Kako dobiti naslove kolona od početka jedne ćelije?

Na primjer, koliko smo efektivno prikazali mjesec u kojem je bila maksimalna prodaja koristeći drugu formulu. Nije teško primijetiti da smo u drugoj formuli koristili kostur prve formule bez funkcije MAX. Glavna struktura formule: VLOOKUP(B1;A5:G14;COLUMN(B5:G14);0). Zamijenili smo funkciju MAX sa MATCH, koja koristi vrijednost dobivenu prethodnom formulom u prvom argumentu. Sada služi kao kriterij za pronalaženje mjeseca. I kao rezultat, funkcija MATCH vraća nam broj kolone 2, gdje se nalazi maksimalna vrijednost obima prodaje za proizvod 4. Nakon toga se u rad uključuje funkcija INDEX, koja vraća vrijednost po broju perioda i kolone iz raspona definiranog u njegovim argumentima. Pošto imamo broj kolone 2, a broj reda u rasponu u kojem se pohranjuju nazivi mjeseci u svakom slučaju će biti 1. Onda nam ostaje da pomoću funkcije INDEX dobijemo odgovarajuću vrijednost iz raspona B4 : G4 - februar (drugi mjesec).



Pronalaženje vrijednosti u Excel koloni

Druga varijanta zadatka će biti pretraga tabele koristeći naziv mjeseca kao kriterij. U takvim slučajevima, moramo promijeniti kostur naše formule: funkcija VLOOKUP je zamijenjena GLOOKUP-om, a funkcija COLUMN je zamijenjena ROW.

To će nam omogućiti da saznamo koliko i koji proizvod je imao maksimalnu prodaju u određenom mjesecu.

Da biste saznali koji je proizvod imao najveći obim prodaje u određenom mjesecu:

Princip formule za pronalaženje vrijednosti u Excel koloni:

U prvom argumentu funkcije HLOOKUP (Horizontalni pogled) specificiramo vezu do ćelije s kriterijem pretraživanja. Drugi argument sadrži referencu na raspon tablice koja se pregledava. Treći argument generira funkcija STRING, koja kreira niz brojeva linija od 10 elemenata u memoriji. Pošto imamo 10 redova u tabelarnom delu.

Zatim, funkcija HLOOKUP, koristeći svaki red za redom, kreira niz odgovarajućih vrijednosti prodaje iz tabele za određeni mjesec (jun). Zatim, funkcija MAX treba samo odabrati maksimalnu vrijednost iz ovog niza.

PAŽNJA! Kada koristite kostur formule za druge zadatke, uvijek obratite pažnju na drugi i treći argument funkcije pretraživanja HLOOKUP. Broj pokrivenih redova u rasponu navedenom u argumentu mora odgovarati broju redova u tabeli. Takođe, numerisanje treba da počne od drugog reda!

Zdravo prijatelji. Koliko često morate tražiti podudaranje u Excel tabeli za neku vrijednost? Na primjer, trebate pronaći adresu osobe u imeniku, ili u cjenovniku - cijenu robe. Ako dođe do takvih zadataka, ova objava je za vas!

Ove zahvate izvodim svaki dan i bez dolje opisanih funkcija zaista bi mi bilo teško. Uzmite u obzir i stavite ih na posao!

Excel tabela za pretraživanje, funkcije VLOOKUP i HLOOKUP

Ulogu ovih funkcija u životu običnog korisnika teško je precijeniti. Sada možete lako pronaći odgovarajući unos u tabeli podataka i vratiti vrijednost koja mu odgovara.

Sintaksa funkcije VLOOKUP je: =VLOOKUP( lookup_value; lookup_table; output_column_number; [vrsta_podudaranja]). Razmotrite argumente:

• Željena vrijednost je vrijednost koju treba tražiti. Ovo je obavezan argument;
• Tabela za pretragu- niz ćelija u kojima će biti pretraga. Kolona sa željenim vrijednostima mora biti prva u ovom nizu. Ovo je takođe obavezan argument;
• Izlazni broj kolone je redni broj kolone (počevši od prvog u nizu), iz kojeg će funkcija prikazati podatke ako se tražene vrijednosti podudaraju. Traženi argument;
• Vrsta mapiranja- odaberite "1" (ili "TRUE") za nestriktno podudaranje, "0" ("FALSE") - za potpuno podudaranje. Argument je opcioni, ako je izostavljen, pretraga će se izvršiti nestriktno podudaranje.

Pronalaženje tačnog podudaranja sa VLOOKUP-om

Pogledajmo primjer kako funkcija VLOOKUP radi kada je tip podudaranja FALSE, tražeći točno podudaranje. Niz B5:E10 sadrži osnovna sredstva određenog preduzeća, njihovu knjigovodstvenu vrijednost, inventarni broj i lokaciju. Ćelija B2 sadrži naziv za koji je potrebno pronaći inventarni broj u tabeli i smjestiti ga u ćeliju C2.

VLOOKUP funkcija u Excelu

Napišimo formulu: =VLOOKUP(B2;B5:E10;3;FALSE) .

Ovdje prvi argument ukazuje da u tabeli treba tražiti vrijednost iz ćelije B2, tj. riječ faks. Drugi argument kaže da je tabela za pretragu u opsegu B5:E10, a u prvoj koloni treba tražiti riječ "Fax", tj. u nizu B5:B10 . Treći argument govori programu da je rezultat proračuna sadržan u trećoj koloni niza, tj. D5:D10 . Četvrti argument je LAŽAN, tj. potrebno je potpuno podudaranje.

I tako, funkcija će primiti string "Fax" iz ćelije B2 i potražit će ga u nizu B5:B10 od vrha do dna. Kada se pronađe podudaranje (red 8), funkcija će vratiti odgovarajuću vrijednost iz stupca D, tj. sadržaj D8 . To je upravo ono što nam je trebalo, problem je riješen.

Ako željena vrijednost nije pronađena, funkcija će vratiti .

Pronalaženje nejasnog podudaranja pomoću VLOOKUP-a

Zahvaljujući ovoj opciji u VLOOKUP-u, možemo izbjeći složene formule da bismo pronašli željeni rezultat.

Niz B5:C12 prikazuje kamatne stope na kredite u zavisnosti od iznosa kredita. U ćeliji B2 označavamo iznos kredita i želimo da dobijemo stopu za takvu transakciju u C2. Zadatak je težak jer iznos može biti bilo koji i malo je vjerovatno da će odgovarati onima navedenim u nizu, traženje točnog podudaranja nije prikladno:

Zatim pišemo formulu nestriktnog pretraživanja: =VLOOKUP(B2,B5:C12,2,TRUE). Sada, od svih podataka prikazanih u koloni B, program će tražiti najbliži manji. Odnosno, za iznos od 8.000 će biti odabrana vrijednost 5000 i biće prikazan odgovarajući procenat.

Nestrogi VLOOKUP u Excelu

Da bi funkcija ispravno radila, morate sortirati prvi stupac tablice uzlaznim redoslijedom. U suprotnom, može dati pogrešan rezultat.

Funkcija HLOOKUP ima istu sintaksu kao VLOOKUP, ali traži rezultat u redovima, a ne u stupcima. Odnosno, skenira tabele ne odozgo nadole, već s leva na desno i prikazuje zadati broj reda, a ne kolonu.

Pronalaženje podataka pomoću funkcije BROWSE

Funkcija LOOKUP radi slično kao VLOOKUP, ali ima drugačiju sintaksu. Koristim ga kada tabela podataka sadrži nekoliko desetina kolona, ​​a da biste koristili VLOOKUP, potrebno je dodatno izračunati broj prikazane kolone. U takvim slučajevima, funkcija VIEW olakšava zadatak. Dakle, sintaksa je: =PREGLEDAJ( lookup_value; Array_for_search; array_to_display) :

• Željena vrijednost– podatke ili vezu do podataka koji se traže;
• Niz za pretraživanje- jedan red ili kolona u kojoj tražimo sličnu vrijednost. Ovaj niz mora biti sortiran uzlaznim redoslijedom;
• Niz za prikaz– raspon koji sadrži podatke za prikaz rezultata. Naravno, mora biti iste veličine kao niz za pretraživanje.

Sa takvom notacijom, dajete nerelativnu referencu na niz rezultata. I pokazujete direktno na to, tj. nema potrebe za prethodno izračunavanjem broja prikazane kolone. Koristimo funkciju VIEW u prvom primjeru za funkciju VLOOKUP (stalna sredstva, inventarni brojevi): =POTRAŽIVANJE(B2,B5:B10,D5:D10). Zadatak uspješno riješen!

VIEW funkcija u programu Microsoft Excel

Traži po relativnim koordinatama. Funkcije MATCH i INDEX

Drugi način traženja podataka je kombiniranje funkcija MATCH i INDEX.

Prvi od njih se koristi za traženje vrijednosti u nizu i dobivanje njenog rednog broja: MATCH( lookup_value; lookup_array; [Vrsta mapiranja] ). Argumenti funkcije:

• Željena vrijednost- traženi argument
• Lookup array- jedan red ili kolona u kojoj tražimo podudaranje. Potreban argument
• Vrsta mapiranja- Navedite "0" za tačno podudaranje, "1" za najbliže manje, "-1" za najbliže veće. Budući da funkcija pretražuje od početka liste do kraja, prilikom traženja najbližeg manjeg, sortirajte kolonu za pretragu u opadajućem redoslijedu. A kada tražite više - sortirajte uzlaznim redoslijedom.

Pozicija tražene vrijednosti je pronađena, sada je možete prikazati na ekranu pomoću funkcije INDEX( niz; linijski broj; [broj_kolone]) :

• niz- argument određuje iz kojeg niza ćelija želite odabrati vrijednost
• Broj linije– navedite serijski broj linije (počevši od prve ćelije niza) koju želite prikazati. Ovdje možete ručno napisati vrijednost ili koristiti rezultat izračuna druge funkcije. Na primjer, SEARCH.
• Broj kolone– neobavezni argument, specificiran ako se niz sastoji od nekoliko kolona. Ako je argument izostavljen, formula koristi prvi stupac tabele.

Sada kombinirajmo ove funkcije da dobijemo rezultat:

Funkcije MATCH i INDEX u Excelu

Ovo su načini pretraživanja i prikaza podataka u Excelu. Nadalje, možete ih koristiti u proračunima, koristiti ih u prezentaciji, izvoditi operacije s njima, specificirati ih kao argumente drugim funkcijama itd.

Osjećate li kako vaša znanja i vještine rastu i jačaju? Onda nemojte stati, nastavite čitati! U sljedećem postu razmotrit ćemo: biće teško i zanimljivo!

Funkcija TRAŽI u Excelu se koristi za određivanje lokacije teksta unutar nekog teksta i za 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

=SEARCH(pronađi_tekst, unutar_teksta, )- Engleska verzija

=SEARCH(tekst_pretraživanja, tekst_pretraživanja, [početna_pozicija])- ruska verzija

Argumenti funkcije

• pronađi_tekst (pretraži_tekst)– tekst ili tekstualni niz koji želite da pronađete;
• unutar_teksta (pregled_teksta)– tekst unutar kojeg pretražujete;
• ([početna_pozicija]) je numerička vrijednost koja označava poziciju s koje želite započeti pretragu. Ako ovaj argument nije naveden, funkcija će započeti pretragu od početka teksta.

Dodatne informacije

• Ako početna pozicija pretrage nije navedena, onda se pretraga teksta vrši na početku teksta;
• Funkcija ne razlikuje velika i mala slova. Ako vam je potrebna osjetljivost na velika i mala slova, koristite ;
• Funkcija može rukovati zamjenskim znakovima. U Excelu postoje tri zamjenska znaka - ?, *, ~.
  • znak "?" – odgovara bilo kojem pojedinačnom znaku;
  • znak “*” – odgovara svim dodatnim znakovima;
  • znak “~” se koristi ako 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)

Gornji primjer pokazuje 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 je funkcija neosjetljiva na velika i mala slova, nije bitno kako ćemo specificirati riječ za pretragu "vrsta", da li je "KIND", "Kind", "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 unutar teksta iz kojeg će se izvršiti pretraga. U gornjem primjeru, funkcija vraća vrijednost "1" kada traži riječ "dobro" u tekstu "Dobro jutro", počevši od prve pozicije.

Međutim, ako kažemo funkciji 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”, tada rezultat će 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: Pronalaženje riječi kada postoji više podudaranja u tekstu

Funkcija počinje da traži tekst sa početne pozicije koju možemo navesti kao argument, ili će automatski početi da traži od početka teksta. U gornjem primjeru tražimo riječ “dobro” u tekstu “Dobro jutro” sa početnom pozicijom pretraživanja “1”. U ovom slučaju, funkcija vraća “1”, budući da prva pronađena riječ “Dobro” počinje prvim znakom teksta.

Ako kažemo funkciji da započne pretragu, na primjer, od drugog znaka, tada će rezultat proračuna funkcije biti "8".

Primjer 4: Upotreba zamjenskih znakova u funkciji SEARCH u Excelu

Prilikom pretraživanja, funkcija uzima u obzir zamjenske znakove. 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.