Gledajte videozapis na Excel zamjenskim znakovima
Postoje samo 3 Excel zamjenska znaka (zvjezdica, upitnik i tilda) i pomoću njih se može učiniti mnogo.
U ovom vodiču pokazat ću vam četiri primjera gdje su ti zamjenski znakovi u Excelu apsolutni spasitelji.
Zamjenski znakovi programa Excel - uvod
Zamjenski znakovi su posebni znakovi koji mogu zauzeti bilo koje mjesto bilo kojeg znaka (otuda i naziv - zamjenski znak).
U Excelu postoje tri zamjenska znaka:
- * (zvjezdica) - Predstavlja neograničeni broj znakova. Na primjer, Ex* može značiti Excel, Excels, Primjer, Stručnjak itd.
- ? (upitnik) - Predstavlja jedan jedini znak. Na primjer, Tr? Mp bi mogao značiti Trump ili Tramp.
- ~ (tilda) - Koristi se za identifikaciju zamjenskog znaka (~, *,?) U tekstu. Na primjer, recimo da želite pronaći točan izraz Excel* na popisu. Ako koristite Excel* kao niz za pretraživanje, dobit ćete bilo koju riječ koja ima Excel na početku, nakon čega slijedi bilo koji broj znakova (kao što su Excel, Excels, Odlično). Da bismo posebno tražili Excel*, moramo koristiti ~. Dakle, naš niz za pretraživanje bio bi excel ~*. Ovdje prisutnost ~ osigurava da Excel čita sljedeći znak onakav kakav jest, a ne kao zamjenski znak.
Napomena: Nisam naišao na mnogo situacija u kojima morate koristiti ~. Ipak, dobro je znati značajku.
Idemo sada kroz četiri izvrsna primjera gdje zamjenski znakovi obavljaju sve teške zadatke.
Zamjenski znakovi Excel - primjeri
Pogledajmo sada četiri praktična primjera gdje Excel zamjenski znakovi mogu biti izuzetno korisni:
- Filtriranje podataka pomoću zamjenskog znaka.
- Djelomično pretraživanje pomoću zamjenskih znakova i VLOOKUP -a.
- Pronađite i zamijenite djelomična podudaranja.
- Brojanje ćelija koje nisu prazne i sadrže tekst.
#1 Filtrirajte podatke pomoću Excel zamjenskih znakova
Zamjenski znakovi programa Excel dobro će vam doći ako imate ogromne skupove podataka i želite filtrirati podatke na temelju uvjeta.
Pretpostavimo da imate skup podataka kao što je prikazano u nastavku:
Možete upotrijebiti zamjenski znak zvjezdice (*) u filtru podataka da biste dobili popis tvrtki koje počinju s abecedom A.
Evo kako to učiniti:
- Odaberite ćelije koje želite filtrirati.
- Idite na Podaci -> Poredaj i filtriraj -> Filtriraj (Tipkovni prečac - Control + Shift + L).
- Kliknite ikonu filtra u ćeliji zaglavlja
- U polje (ispod opcije Filter teksta) upišite A*
- Pritisnite U redu.
Ovo će trenutno filtrirati rezultate i dati vam 3 imena - ABC Ltd., Amazon.com i Apple Stores.
Kako radi? - Kad dodate zvjezdicu (*) iza A, Excel bi filtrirao sve što počinje s A. To je zato što zvjezdica (koja je zamjenski znak programa Excel) može predstavljati bilo koji broj znakova.
Sada s istom metodologijom možete koristiti različite kriterije za filtriranje rezultata.
Na primjer, ako želite filtrirati tvrtke koje počinju abecedom A i u njoj sadrže abecedu C, upotrijebite niz A*C. To će vam dati samo 2 rezultata - ABC Ltd. i Amazon.com.
Ako koristite A? C umjesto toga, kao rezultat ćete dobiti samo ABC Ltd (jer je samo jedan znak dopušten između 'a' i 'c')
Bilješka: Isti se koncept može primijeniti i pri korištenju Excel naprednih filtara.
#2 Djelomično pretraživanje pomoću zamjenskih znakova i VLOOKUP -a
Djelomično traženje potrebno je kada morate tražiti vrijednost na popisu, a ne postoji točno podudaranje.
Na primjer, pretpostavimo da imate skup podataka kao što je prikazano u nastavku i želite potražiti tvrtku ABC na popisu, ali na popisu je umjesto ABC ABC Ltd.
U ovom slučaju ne možete koristiti uobičajenu funkciju VLOOKUP jer vrijednost pretraživanja nema točno podudaranje.
Ako koristite VLOOKUP s približnim podudaranjem, to će vam dati pogrešne rezultate.
Međutim, možete koristiti zamjenski znak unutar funkcije VLOOKUP da biste dobili prave rezultate:
Unesite sljedeću formulu u ćeliju D2 i povucite je za ostale ćelije:
= VLOOKUP ("*" & C2 & "*", $ A $ 2: $ A $ 8,1, FALSE)
Kako funkcionira ova formula?
U gornjoj formuli, umjesto da se koristi vrijednost za pretraživanje onakva kakva je, ona je s obje strane okružena zvjezdicom znaka Excel (*) - "*" & C2 & "*"
To Excelu govori da mora potražiti bilo koji tekst koji sadrži riječ u C2. Može imati bilo koji broj znakova prije ili poslije teksta u C2.
Dakle, formula traži podudaranje, a čim dobije podudaranje, vraća tu vrijednost.
3. Pronađite i zamijenite djelomična podudaranja
Excel zamjenski znakovi prilično su svestrani.
Možete ga koristiti u složenoj formuli, kao i u osnovnim funkcijama kao što je Find and Replace.
Pretpostavimo da imate podatke kao što je prikazano u nastavku:
U gornjim podacima, regija je unesena na različite načine (poput sjeverozapadnog, sjeverozapadnog, sjeverozapadnog).
To je često slučaj s podacima o prodaji.
Da bismo očistili ove podatke i učinili ih dosljednima, možemo koristiti zamjenske znakove Pronađi i zamijeni Excelom.
Evo kako to učiniti:
- Odaberite podatke u kojima želite pronaći i zamijenite tekst.
- Idite na početnu stranicu -> Pronađi i odaberite -> Idi na. Ovo će otvoriti dijaloški okvir Pronađi i zamijeni. (Također možete koristiti tipkovnički prečac - Control + H).
- Unesite sljedeći tekst u dijaloški okvir za pronalaženje i zamjenu:
- Naći što: Sjever*W*
- Zamijeniti s: Sjeverozapad
- Pritisnite Zamijeni sve.
To će odmah promijeniti sve različite formate i učiniti ga dosljednim sjeverozapadnom.
Kako ovo radi?
U polju Find smo koristili Sjever*W* koji će pronaći bilo koji tekst koji ima riječ Sjever i sadrži abecedu 'W' bilo gdje iza nje.
Stoga pokriva sve scenarije (sjeverozapadni, sjeverozapadni i sjeverozapadni).
Find and Replace pronalazi sve ove instance i mijenja ih u North-West te ih čini dosljednima.
4. Prebrojte ćelije koje nisu prazne i sadrže tekst
Znam da ste pametni i mislite da Excel već ima ugrađenu funkciju za to.
Ti si potpuno u pravu!!
To se može učiniti pomoću funkcije COUNTA.
ALI … Postoji jedan mali problem s tim.
Često ćete prilikom uvoza podataka ili korištenja tuđeg radnog lista primijetiti da postoje prazne ćelije, iako to možda nije slučaj.
Ove ćelije izgledaju prazne, ali u sebi imaju = ””. Nevolja je u tome što je
Problem je u tome što funkcija COUNTA ovo ne smatra praznom ćelijom (računa je kao tekst).
Pogledajte primjer ispod:
U gornjem primjeru koristim funkciju COUNTA za pronalaženje ćelija koje nisu prazne i vraća 11, a ne 10 (ali jasno vidite da samo 10 ćelija ima tekst).
Razlog je, kao što sam spomenuo, što ne smatra A11 praznim (dok bi trebao).
Ali Excel tako funkcionira.
Popravak je korištenje Excel zamjenskog znaka unutar formule.
Ispod je formula koja koristi funkciju COUNTIF koja broji samo ćelije koje sadrže tekst:
= COUNTIF (A1: A11, "?*")
Ova formula kaže Excelu da se računa samo ako ćelija ima barem jedan znak.
U ?* kombinirano:
- ? (upitnik) osigurava da je prisutan barem jedan znak.
- * (zvjezdica) ostavlja prostor za neograničeni broj dodatnih znakova.
Bilješka: Gornja formula funkcionira ako u ćelijama postoje samo tekstualne vrijednosti. Ako imate popis koji sadrži i tekst i brojeve, upotrijebite sljedeću formulu:
= COUNTA (A1: A11) -COUNTBLANK (A1: A11)
Slično, možete koristiti zamjenske znakove u mnogim drugim Excelovim funkcijama, poput IF (), SUMIF (), AVERAGEIF () i MATCH ().
Također je zanimljivo napomenuti da, iako možete koristiti zamjenske znakove u funkciji SEARCH, ne možete ih koristiti u funkciji FIND.
Nadam se da će vam ovi primjeri dati osjećaj svestranosti i moći Excel zamjenskih znakova.
Ako imate neki drugi inovativan način korištenja, podijelite ga sa mnom u odjeljku komentara.
Sljedeći Excel vodiči mogli bi vam biti korisni:
- Korištenje COUNTIF i COUNTIFS s više kriterija.
- Izrada padajućeg popisa u Excelu.
- Operater presijecanja u Excelu
