Dobar dan, dragi čitaoče!

U ovom članku želim pogledati tako moćan alat kao što je Find and Replace u Excelu. Ovo je vrlo moćan i višenamjenski alat koji će vam omogućiti brzo i efikasno pronalaženje potrebne informacije u radnim listovima i po potrebi zamijeniti tekst željenim.

Da biste pozvali prozor „Pronađi i zamijeni“, potrebno je odabrati određeni u kojem će se odabrati vrijednosti (u slučaju da nema odabira raspona, pretraga će se odvijati na cijelom radnom listu). Zatim u kontrolnoj tabli, kartici “Početna” u bloku “Uređivanje”, kliknite na ikonu sa padajućim menijem “Pronađi i odaberi” i ona postaje pristupačne opcije"Pronađi" i "Zamijeni". Ova opcija je dostupna i klikom na kombinacija Ctrl+F. U prozoru “Pronađi i zamijeni” dostupne su dvije kartice: “Pronađi”, ako tražite neke informacije, i “Zamijeni”, koristi se u slučajevima kada je potrebno zamijeniti tekst. Osim toga, možete koristiti naprednu funkciju pretraživanja klikom na ikonu „Opcije“.

Traži se u dokumentu

U prozoru „Pronađi i zamijeni“, na kartici „Pronađi“, zapišite argument za pretragu i kliknite na ikonu „Pronađi sve“, ovo je uobičajena pretraga.
Za detaljniju i širu pretragu potrebno je:

• U padajućem izborniku “Traži” zapišite lokaciju pretraživanja: radni list ili cijelu knjigu;
• U meniju „Pregled“ označite koja vam je opcija pretraživanja potrebna: po kolonama ili po redovima;
• U redu “Search area” postavljamo mjesta za pretragu: u vrijednostima, bilješkama ili formulama;
• Koristeći stavke “Cijela ćelija” ili “Razmotriti slučaj” ukazujete na potrebu da se uzme u obzir velika i mala slova znakova za pretragu i da li će se sadržaj u području pretraživati ​​u fragmentima ili u cijelosti.

Kada kliknete na ikonu „Pronađi sve“, prikazuje se lista sa navedene adrese sve ćelije koje su pronađene. A klikom na ikonu “Pronađi sljedeće” pojavit će se pretraga, isticanje jednog segmenta jednim klikom, sljedeći klik će pokazati sljedeći argument.

Zamjena pronađenih informacija

Da biste zamijenili argument potrebnim, otvorite prozor "Pronađi i zamijeni" u Excelu i odaberite karticu "Zamijeni". U polje “Pronađi” unesite vrijednost koja zahtijeva zamjenu, a u polje “Zamijeni” upišite u šta zapravo trebate da je promijenite. Po želji ili potrebi možete promijeniti parametre uz naprednu funkcionalnost, koju sam gore opisao.
Za tačnu i detaljnu zamjenu, potrebno je kliknuti na ikonu „Pronađi sljedeće“, zatim Excel odabire prvo podudaranje, a zatim, ako je potrebna zamjena, kliknite na „Zamijeni“, ako ne, ponovo kliknite na „Pronađi sljedeće“. Ako ste sigurni da ćete zamijeniti vrijednosti, jednostavno kliknite na “Zamijeni sve”, sistem će vam dati samo poruku o broju izvršenih zamjena. U slučaju da dođe do zamjene, možete otkazati radnje pomoću panela brz pristup klikom na ikonu “Otkaži” ili jednostavno kombinacijom vrućih dugmadi Ctrl tipke+ Z.

Pretraživanje po formatima podataka

Osim podataka i svih drugih simbola, Excel također može pronaći sve ćelije koristeći navedene formate i, kao rezultat, zamijeniti pronađeni format traženim.

Na primjer, postojala je potreba da se sve vrijednosti u kurzivnom tekstu zamijeni podebljanim tekstom. Da biste to učinili, morate otići na karticu "Zamijeni", omogućiti naprednu funkcionalnost pomoću gumba "Opcije" i u postavkama "Format" navesti parametre pretraživanja i parametar zamjene.
Kada kliknete na dugme „Format“, otvoriće se novi dijaloški okvir „Pronađi format“ i idite na karticu „Font“ u postavci „Typeface“, izaberite „Italic“.
Ove operacije se moraju obaviti dva puta, prvi put za pronalaženje i drugi put za zamjenu. Dugmad “Format” postavljena su jedno pored drugog, jedno iznad drugog.
Sada, kada kliknete na dugme "Zamijeni sve", formati će biti promijenjeni iz kurziva u podebljano.

Trikovi i primjeri korištenja mogućnosti Find and Replace uExcel

Koristim na primjer sopstveno iskustvo aplikacije. Vrlo često analiziram promet mojih članaka kako bih ih poboljšao faktori ponašanja i čitljivost. Da to uradim, postavljam izveštaj sa Yandex.Metrica koji izgleda ovako (članci se biraju nasumično):
Kao što vidite, servis prilaže i naziv stranice uz naslov članka, ali mi to uopće ne treba za analizu. Dakle, dio linije “ | Obriši Excel" Naći ću i izbrisati zamjenom, zamjenjujući samo razmak " " za zamjenu.
Sada kliknite na ikonu "Zamijeni sve" i pogledajte dijalog o izvršenim zamjenama. To je to, sve je urađeno kako treba! Pored ugrađenih mogućnosti pretraživanja i zamjene u panelu Upravljanje Excelom, još uvijek je moguće izvršiti ove operacije koristeći

Rad sa VB projektom (12)
Uvjetno formatiranje (5)
Liste i rasponi (5)
Makroi (VBA procedure) (63)
razno (39)
Greške i greške u Excelu (3)

Kako zamijeniti/ukloniti/pronaći zvjezdicu?

Vjerovatno se neko već susreo slična situacija- na listu u ćelijama su ispisane riječi/rečenice koje sadrže znak - * - zvjezdicu. Ponekad ga trebate ili pronaći, zamijeniti drugim simbolom ili ga jednostavno izbrisati. Na primjer, red kao " 496*120*45 " treba pretvoriti u ovo: " 496x120x45" I tu nastaje problem: simbol zvjezdice (kao upitnik) je servisni znak - zamjenjuje grupu bilo kojeg karaktera (a upitnik je bilo koji simbol). A ako pokušate zamijeniti samo zvjezdicu, Excel će zamijeniti SVE podatke u ćeliji. Sada pričamo o tome standardna zamjena - (Excel 2003 : Uredi -Zamijenite; Excel 2007-2010: tab Dom -Pronađite i označite -Zamijenite) - Ctrl+H. Zar se zaista ništa ne može učiniti? Može. Programeri su omogućili ovu opciju. Samo trebate staviti drugi simbol usluge ispred uslužnog znaka, koji govori Excelu da znak koji slijedi ne obavlja svoje servisne funkcije, već se ponaša kao regularni simbol. Ovaj znak se zove Tilda. Volim ovo - ~ . On obična tastatura ovaj znak se obično nalazi na dugmetu zajedno sa slovom E.

Dakle na terenu Nađi Okvir za pretragu bi trebao prikazati sljedeći tekst: " ~* "(na terenu Zamijenjeno od označava znakove koji trebaju zamijeniti zvjezdicu. Ako zvjezdicu treba ukloniti, ostavite polje praznim). I ne zaboravi Dodatne opcije zamjene (otvaraju se kada pritisnete dugme " Opcije" prozor za pretragu). Naime, potrebno je provjeriti/poništiti oznaku parametra Cela ćelija. U ovom slučaju, ovo polje za potvrdu mora biti poništeno.

Ako trebate pronaći samu tildu, onda je sve isto - samo naznačite dvije tilde: ~~

Po istom principu, ovaj simbol se koristi u formulama koje rade sa uslužnim simbolima (VLOOKUP, COUNTIF, SUMMELI i tako dalje.)

Ali. Uvek postoji ali. Neke formule sve simbole percipiraju odmah kao tekst i za njih ne postoji takva stvar kao što je simbol usluge. To uključuje funkciju FIND. U ovoj funkciji, za traženje datog znaka, ne morate stavljati drugi ispred njega, jer ona ne percipira zamjenske znakove kao takve. Prihvata apsolutno bilo koji znak kao tekst i striktno traži ono što je navedeno:
=PRONAĐI("~"; A1)
=PRONAĐI("*"; A1)
=PRONAĐI("?"; A1)
=PRONAĐI("~", A1)
=PRONAĐI("*", A1)
=PRONAĐI("?", A1)

Postoji funkcija slična njoj - TRAŽI. U ovoj funkciji je imperativ primijeniti isti princip kao i u svim gore navedenim – tj. eksplicitno naznačite Excelu da tražimo poseban znak:
=SEARCH("~~"; A1)
=SEARCH("~*"; A1)
=SEARCH("~?"; A1)
=SEARCH("~~", A1)
=SEARCH("~*", A1)
=SEARCH("~?", A1)

Uzgred, simboli zvjezdice i upitnika se također koriste u formulama VLOOKUP, HLOOKUP I MATCH. Najneprijatniji trenutak ovdje je da zvjezdica, upitnik ili sama tilda mogu biti unutar teksta u željenom nizu podataka:


i kao što možete vidjeti u ovom slučaju, podaci sa tildom se ne mogu pronaći. Da biste ih pronašli putem VLOOKUP-a (i drugih sličnih funkcija), trebate zamijeniti tildu s dvije. Savjetovao bih da to učinite ovako (koristeći primjer formula iz gornje tabele):
=VLOOKUP(SUBSTITUTE(F2 ;"~";"~~"); $A$2:$C$7 ;3;0)
=VLOOKUP(ZAMJENA(F2,"~","~~"),$A$2:$C$7,3,0)

I iako podaci sa zvjezdicom i upitnikom u većini slučajeva neće smetati, možete doći u situaciju da će tekst sa zvjezdicom biti povezan s netočnim rezultatom:

zašto VLOOKUP to radi bolje je pročitati ovdje: Kako pronaći vrijednost u drugoj tabeli ili snagu VLOOKUP-a. A u ovom članku ću samo dati formulu kako izbjeći takve probleme. Kao i kod tilde, koristimo funkciju SUBSTITUTE, ali sada zamjenjujemo tildu za zvjezdicu:
=VLOOKUP(SUBSTITUTE(D2 ;"*";"~*"); $A$2:$A$4 ;1;0)
=VLOOKUP(SUBSTITUTE(D2,"*","~*"),$A$2:$A$4,1,0)

Da li je članak pomogao? Podijelite link sa svojim prijateljima! Video lekcije

("Donja traka":("textstyle":"static","textpositionstatic":"bottom","textautohide":true,"textpositionmarginstatic":0,"textpositiondynamic":"bottomleft","textpositionmarginleft":24," textpositionmarginright":24,"textpositionmargintop":24,"textpositionmarginbottom":24,"texteffect":"slide","texteffecteasing":"easeOutCubic","texteffectduration":600,"texteffectslidedirection":"left","texteffectslidedistance :30,"texteffectdelay":500,"texteffectseparate":false,"texteffect1":"slide","texteffectslidedirection1":"desno","texteffectslidedistance1":120,"texteffecteasing1":"easeOutCubic","texteffectduration1" ,"texteffectdelay1":1000,"texteffect2":"slide","texteffectslidedirection2":"desno","texteffectslidedistance2":120,"texteffecteasing2":"easeOutCubic","texteffectduration2":600,"texteffect5delay0 textcss":"display:block; padding:12px; text-align:left;","textbgcss":"display:block; position:apsolute; top:0px; lijevo:0px; širina:100%; visina:100% ; background-color:#333333; neprozirnost:0.6; filter:alpha(opacity=60);","titlecss":"display:block; pozicija:relativna; font:bold 14px \"Lucida Sans Unicode\",\"Lucida Grande\",sans-serif,Arial; color:#fff;","descriptioncss":"display:block; pozicija:relativna; font:12px \"Lucida Sans Unicode\",\"Lucida Grande\",sans-serif,Arial; boja:#fff; margin-top:8px;","buttoncss":"display:block; pozicija:relativna; margin-top:8px;","texteffectresponsive":true,"texteffectresponsivesize":640,"titlecssresponsive":"font-size:12px;","descriptioncssresponsive":"display:none !important;","buttoncssresponsive": "","addgooglefonts":false,"googlefonts":"","textleftrightpercentforstatic":40))

Funkcija REPLACE( ) , engleska verzija REPLACE(), zamjenjuje navedeni dio znakova u tekstualnom nizu drugim nizom teksta. "Navedeni dio znakova" znači da trebate navesti početnu poziciju i dužinu dijela niza koji treba zamijeniti. Funkcija se rijetko koristi, ali ima plus: olakšava umetanje nizova na određenu poziciju novi tekst.

Sintaksa funkcije

ZAMIJENITE(originalni tekst;start_pos;broj_znakova;novi_tekst)

Originalni tekst- tekst u kojem je potrebno zamijeniti neke znakove.
Start_pos- pozicija prijave Izvor_tekst, počevši od čega se znakovi zamjenjuju tekstom novi_tekst.
Broj_znakova- broj znakova u Izvor_tekst, koji se zamjenjuju tekstom novi_tekst.
Novi_tekst- tekst koji zamjenjuje znakove u Izvor_tekst.

Funkcija REPLACE() vs SUBSTITUTE()

Funkcija SUBSTITUTE() se koristi kada trebate zamijeniti definitivno tekst u tekstualnu liniju; REPLACE() funkcija se koristi kada trebate zamijeniti bilo koji tekst koji počinje sa određene pozicije.

Prilikom zamjene određenog teksta, funkcija REPLACE() je nezgodna za korištenje. Mnogo je zgodnije koristiti funkciju SUBSTITUTE().

Pustite u ćeliju A2 string unesen prodaja (januar). Za zamjenu riječi Januar, on februar, napišimo formule:

one. funkcija REPLACE() zahtijevala je izračunavanje početne pozicije riječi Januar(10) i njegovu dužinu (6). Ovo nije zgodno; funkcija SUBSTITUTE() obavlja posao mnogo lakše.

Osim toga, funkcija REPLACE() zamjenjuje, iz očiglednih razloga, samo jedno pojavljivanje niza, funkcija SUBSTITUTE() može zamijeniti sve pojave ili samo prvo, samo drugo, itd.
Objasnimo na primjeru. Pustite u ćeliju A2 string unesen . Napišimo formule:
=ZAMJENA(A2,10,6,"februar")
=ZAMJENA(A2, "januar","februar")
U prvom slučaju dobijamo liniju Prodaja (februar), profit (januar), u drugom - Prodaja (februar), profit (februar).
Nakon što ste napisali formulu =ZAMJENA(A2, "januar","februar",2) dobijamo konac Prodaja (januar), profit (februar).

Dodatno, funkcija SUBSTITUTE() je osjetljiva na velika i mala slova. Pošto je zapisao =ZAMJENA(A2, "JANUAR","FEBRUAR") dobijamo niz nepromenjen Prodaja (januar), profit (januar), jer za funkciju SUBSTITUTE(), "JANUAR" nije isto što i "Januar".

Upotreba funkcije za umetanje novog teksta u niz

Funkcija REPLACE() korisna je za umetanje novog teksta u niz. Na primjer, postoji lista proizvoda u obliku " ID-567(ASD)“, neophodno prije teksta A.S.D. umetnite novi tekst Micro da to radi" ID-567 (MicroASD) Da bismo to uradili, hajde da napišemo jednostavna formula:
=ZAMJENA(A2;8;0;"Mikro").

Excel ima moćan alat pretražite i zamijenite, što vam omogućava brzo pronalaženje potrebne informacije na listovima ili po cijeloj površini radna sveska. Osim toga, moguće je tražiti tekst i zamijeniti ga drugim.

Iskoristiti prednost dijaloški okvir Pronađite i zamijenite, Odaberite raspon ćelija u kojem želite pretraživati ​​(ako odaberete samo jednu ćeliju, Excel će pretražiti cijeli radni list). Idite na karticu Dom grupi uređivanje, izaberite Pronađite i odaberite -> Pronađite ( ili pritisnite prečicu na tastaturi Ctrl + F).

U dijaloškom okviru koji se pojavljuje Pronađite i zamijenite, možete koristiti karticu nađi, ako samo trebate pronaći neke informacije, bilo pomoću kartice zamijeni, ako želite zamijeniti tekst. Imajte na umu da se možete prebaciti na način napredne pretrage klikom na dugme Opcije.

Potražite informacije

Unesite podatke koje tražite u polje Nađi i navedite potrebne parametre pretrage:

• Odaberite lokaciju za pretragu sa padajućeg menija Traži(na listu papira, u knjizi)
• U padajućem meniju Pregledaj, molimo navedite Excel opcija prikaz po redovima ili kolonama.
• Set Region traži u formulama, vrijednostima ili bilješkama
• Korišćenje opcija Match case I Cela ćelija možete odrediti da li je pretraga osjetljiva na velika i mala slova i da li se pretražuje cijeli sadržaj ćelije ili samo dio.

Dugme Nađi sljedećeće pronaći i označiti jednu odgovarajuću ćeliju po kliku. Ako pritisnete dugme Nađi sve Excel prikazuje listu sa adresama svih pronađenih ćelija. Odabirom stavki na ovoj listi istaknut će se njihove odgovarajuće ćelije na radnom listu.

Zamjena informacija

Za zamjenu teksta u Excel ćelija drugi tekst, koristite karticu Zamijenite u dijaloškom okviru Pronađite i zamijenite. Na terenu Nađi unesite tekst koji želite da zamenite i u polje Zamijenjeno od tekst koji želite da promenite. Ako je potrebno, koristite napredne opcije zamjene opisane gore.

Kliknite na dugme Nađi sljedeće, neka onda Excel označi prvu ćeliju koja odgovara Zamijenite za zamjenu. Kada se klikne, Excel će označiti sljedeću odgovarajuću ćeliju. Da preskočite zamjenu, kliknite Nađi sljedeće. Da biste zamijenili sve stavke bez provjere, kliknite Zamijenite sve. Ako zamjena ne ide kako je planirano, možete poništiti radnju koristeći Ctrl + Z prečicu na tipkovnici.

Formati pretraživanja

Osim teksta, možete pronaći ćelije oblikovane na određeni način. Alternativno, također možete zamijeniti formatiranje ćelije nečim drugim. Pretpostavimo da trebate definirati sve ćelije sa podebljan tekst i zamijenite ih podebljanim i kurzivom. Idite na karticu Dom grupi uređivanje, izaberite Pronađite i odaberite -> Zamijeni ( ili pritisnite prečicu na tastaturi Ctrl + H). U dijaloškom okviru koji se pojavi idite na karticu Zamijenite i kliknite na dugme opcije, za prikaz naprednih opcija zamjene. Kliknite na gornje dugme Format.

U dijaloškom okviru koji se pojavi idite na karticu Font i odaberite Bold nacrt. Kliknite OK.

Slijedite iste korake za format zamjene ( donje dugme Format), jedina razlika će biti izbor stila Bold italic u dijaloškom okviru Pronađite format.

Kliknite na dugme Zamijenite sve Excel će zamijeniti tekst sa podebljano podebljanim kurzivom.

Trikovi za korištenje Find and Replace Excel

Zamjena prijeloma reda (Alt+Enter) u ćeliji

Pretpostavimo da imate tabelu sa ćelijama u kojima se podaci pomeraju kroz redove pomoću kombinacije tastera Alt+Enter i želite da zamenite znak za povlačenje reda (nosač) sa razmakom.

Da biste to učinili, pozovite dijaloški okvir Pronađite i zamijenite prečica na tastaturi Ctrl+H. U polju Nađi unesite Ctrl+J (vanjsko polje će ostati prazno), u polje Zamijenjeno od enter Prostor(ili bilo koji drugi znak).

Kliknite Zamijenite sve i, eto, sve naše kočije su se pretvorile u prostore!

Već smo pogledali koliko su brzi podaci u Excelu u grupi ćelija - u koloni ili nizu podataka. Ali kako to učiniti u jednoj ćeliji i po mogućnosti s formulom? Na primjer, imate listu automobila i svaki dan vam pošalju fajl sa novom listom automobila, morate provjeriti da li se takvi brojevi nalaze na vašoj glavnoj listi. No, kao i obično, brojevima automobila se dodaju razmaci ili kose crte, a najgora opcija je slovo O umjesto nule. Ako redovno izvodite istu operaciju, onda je ručno pritiskanje Ctrl + H svaki put nezgodno, plus morate zapamtiti koje znakove treba zamijeniti. Funkcije SUBSTITUTE i REPLACE u Excelu su odličan izlaz iz situacije.

Ove funkcije su malo zanemarene, ne znam zašto, ali sam siguran da su super korisne za obradu i kombinovanje velikih količina podataka, jer se lako mogu postaviti autoispravke. U nastavku ću vam reći detaljnije o svakom od njih i po čemu se razlikuju. Pogledajmo to koristeći primjer.

Postoji broj A 777 AA 01 koji vam je potreban da napravite formulu A777AA01.

Kako formula funkcionira prikazano je na prvoj slici. Pogledajmo kompoziciju u nastavku:

ZAMJENA (tekst, stari_tekst, novi_tekst, [broj_pojave])

• Tekst - adresa ćelije ili sam tekst u kojem trebate zamijeniti znakove, u primjeru A 777 AA 01
• Old_text - šta trebamo zamijeniti? Prostor.
• Novi_tekst - čime ga treba zamijeniti? Mi to mijenjamo u ništa, tj. na prazno u formuli, bit će ""
• Occurrence_number - nije potrebno popunjavati, ali atribut je vrlo koristan, službeni certifikat govori o "pojavljivanju simbola", ali ja ću objasniti jednostavnim jezikom. Ovdje možete odrediti broj karaktera u retku (razmak u našem slučaju). One. ako trebate samo zamijeniti razmak između 777 i AA (ovo je drugi razmak u broju), formula će izgledati ovako

=ZAMJENA(A:A;" ";"";2)

I na kraju ćete dobiti A 777AA 01

Ali po pravilu koristim ovakve konstrukcije da uklonim sve nepotrebne simbole koji su već dodani prilikom popunjavanja.

SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A:A;" ";"");".";"");"/";"")

IN u ovom slučaju uklanjamo razmak, tačku i kosu crtu u broju "A 777 AA/01."

REPLACE i REPLACE funkcije u Excelu

Funkcija REPLACE radi skoro sve isto kao i zamjena, ali to čini počevši od određenog znaka. Argumenti su:

REPLACE(stari_tekst, početna_pozicija, broj znakova, novi_tekst)

Primjer. Potrebno je prvo slovo broja automobila zamijeniti drugim. Od A777AA01 napravite B777AA01

Prvo opis argumenata:

• Stari_tekst - kao u SUBSTITUTE, tekst u kojem treba zamijeniti tj. A777AA01
• Start_position — od kojeg znaka treba zamijeniti, od prvog.
• Broj_znakova koliko znakova mijenjamo - jedan
• Novi_tekst - i u šta ga mijenjamo? Počevši od slova B

Veoma slicno , da budem iskren :)

REPLACE je mala misterija Excela, čak i na stranom internetu ima malo informacija o tome. Ukratko, jednostavno REPLACE mijenja bilo koji znak (čak i ako je veličine 2 bajta), REPLACE mijenja samo jednobajtne znakove (argument number_characters se mijenja u). Praktična primjena Za sebe nisam našao :) Ako neko može da mi kaže, biću zahvalan!

UPD. Kao što mi je rečeno, dvobajtni znakovi uključuju većinu jezika sa hijeroglifima!

ZAMJENA i ZAMJENA u Excelu. Šta još?

Obje funkcije razlikuju velika i mala slova! One. ako ste naznačili veliko slovo Ah, malo slovo neće biti zamijenjeno!

Funkcija SUBSTITUTE dostupna je samo u verzijama 2007 i novijim, budite oprezni ako krajnji korisnik koristit će više rane verzije!

Podijelite naš članak na svojim društvenim mrežama: