Oglejte si video o Excelovih znakih z nadomestnimi znaki
Obstajajo samo 3 Excelovi nadomestni znaki (zvezdica, vprašaj in tilda) in z njimi je mogoče veliko narediti.
V tej vadnici vam bom pokazal štiri primere, v katerih so ti nadomestni znaki Excel absolutni reševalci.
Excelovi nadomestni znaki - uvod
Nadomestni znaki so posebni znaki, ki lahko zasedejo katero koli mesto katerega koli znaka (od tod tudi ime - nadomestni znak).
V Excelu so trije nadomestni znaki:
- * (zvezdica) - Predstavlja poljubno število znakov. Na primer, Ex* lahko pomeni Excel, Excels, Primer, Strokovnjak itd.
- ? (vprašaj) - Predstavlja en sam znak. Tr? Mp lahko na primer pomeni Trump ali Tramp.
- ~ (tilda) - Uporablja se za identifikacijo nadomestnega znaka (~, *,?) V besedilu. Recimo, da želite na seznamu poiskati natančno besedno zvezo Excel*. Če uporabljate Excel* kot iskalni niz, se prikaže vsaka beseda, ki ima Excel na začetku, ki ji sledi poljubno število znakov (na primer Excel, Excels, Odlično). Če želimo posebej iskati excel*, moramo uporabiti ~. Torej bi bil naš iskalni niz excel ~*. Tukaj prisotnost ~ zagotavlja, da Excel bere naslednji znak takšen, kot je, in ne kot nadomestni znak.
Opomba: Nisem naletel na veliko situacij, ko bi morali uporabiti ~. Kljub temu je dobro poznati funkcijo.
Zdaj pa pojdimo skozi štiri čudovite primere, ko znaki z nadomestnimi znaki opravijo vse težko.
Excelovi nadomestni znaki - primeri
Zdaj pa poglejmo štiri praktične primere, kjer so lahko nadomestni znaki Excela zelo koristni:
- Filtriranje podatkov z nadomestnim znakom.
- Delno iskanje z nadomestnimi znaki in VLOOKUP.
- Poiščite in zamenjajte delna ujemanja.
- Preštejte celice, ki niso prazne in vsebujejo besedilo.
#1 Filtrirajte podatke z uporabo nadomestnih znakov Excel
Nadomestni znaki Excel so uporabni, če imate ogromne nabore podatkov in želite filtrirati podatke glede na stanje.
Recimo, da imate nabor podatkov, kot je prikazano spodaj:
Z zvezdico (*) lahko v podatkovnem filtru dobite seznam podjetij, ki se začnejo z abecedo A.
Evo, kako to storiti:
- Izberite celice, ki jih želite filtrirati.
- Pojdite na Podatki -> Razvrsti in filtriraj -> Filter (Bližnjica na tipkovnici - Control + Shift + L).
- Kliknite ikono filtra v celici glave
- V polje (pod možnostjo Besedilni filter) vnesite A*
- Kliknite V redu.
Tako boste takoj filtrirali rezultate in dobili 3 imena - ABC Ltd., Amazon.com in Apple Stores.
Kako deluje? - Ko za A dodate zvezdico (*), bi Excel filtriral vse, kar se začne z A. To je zato, ker lahko zvezdica (ki je nadomestni znak Excel) predstavlja poljubno število znakov.
Zdaj z isto metodologijo lahko uporabite različne kriterije za filtriranje rezultatov.
Na primer, če želite filtrirati podjetja, ki se začnejo z abecedo A in v njej vsebuje abecedo C, uporabite niz A*C. Tako boste dobili samo 2 rezultata - ABC Ltd. in Amazon.com.
Če uporabljate A? C namesto tega boste kot rezultat dobili le ABC Ltd (saj je med »a« in »c« dovoljen samo en znak)
Opomba: Enak koncept je mogoče uporabiti tudi pri uporabi naprednih filtrov Excel.
#2 Delno iskanje z uporabo nadomestnih znakov in VLOOKUP
Delno iskanje je potrebno, če morate iskati vrednost na seznamu in ni natančnega ujemanja.
Recimo, da imate nabor podatkov, kot je prikazano spodaj, in želite poiskati podjetje ABC na seznamu, vendar je na seznamu namesto ABC ABC Ltd.
Običajne funkcije VLOOKUP v tem primeru ne morete uporabiti, ker iskalna vrednost nima natančnega ujemanja.
Če uporabite VLOOKUP s približnim ujemanjem, boste dobili napačne rezultate.
Vendar pa lahko v funkciji VLOOKUP uporabite nadomestni znak, da dobite prave rezultate:
V celico D2 vnesite naslednjo formulo in jo povlecite za druge celice:
= VLOOKUP ("*" & C2 & "*", $ A $ 2: $ A $ 8,1, FALSE)
Kako deluje ta formula?
V zgornji formuli je namesto uporabe iskalne vrednosti na obeh straneh obkrožena z zvezdico znaka Excel (*) - "*" & C2 & "*"
To Excelu pove, da mora poiskati katero koli besedilo, ki vsebuje besedo v C2. Lahko ima poljubno število znakov pred ali za besedilom v C2.
Formula torej išče ujemanje in takoj, ko dobi ujemanje, vrne to vrednost.
3. Poiščite in zamenjajte delna ujemanja
Znaki Excel Wildcard so precej vsestranski.
Uporabite ga lahko tako v kompleksni formuli kot v osnovnih funkcijah, kot sta Find and Replace.
Recimo, da imate podatke, kot je prikazano spodaj:
V zgornjih podatkih je bila regija vnesena na različne načine (na primer severozahod, severozahod, severozahod).
To se pogosto zgodi s podatki o prodaji.
Za čiščenje teh podatkov in njihovo usklajenost lahko uporabimo nadomestne znake Najdi in zamenjaj z Excelom.
Evo, kako to storiti:
- Izberite podatke, kjer želite najti, in zamenjajte besedilo.
- Pojdite na domačo stran -> Najdi in izberite -> Pojdi na. S tem se odpre pogovorno okno Najdi in zamenjaj. (Uporabite lahko tudi bližnjico na tipkovnici - Control + H).
- V pogovorno okno za iskanje in zamenjavo vnesite naslednje besedilo:
- Najdi kaj: Sever*W*
- Zamenjaj z: Severozahod
- Kliknite Zamenjaj vse.
To bo v hipu spremenilo vse različne oblike in postalo skladno s severozahodom.
Kako to deluje?
V polju Najdi smo uporabili Sever*W* ki bo kjer koli za besedilom, ki vsebuje besedo sever in vsebuje abecedo 'W'.
Zato zajema vse scenarije (severozahod, severozahod in severozahod).
Poišči in zamenjaj najde vse te primere in jih spremeni v severozahodno ter jih naredi dosledne.
4. Preštejte prazne celice, ki vsebujejo besedilo
Vem, da ste pametni in mislite, da ima Excel za to že vgrajeno funkcijo.
Popolnoma prav imaš!!
To lahko storite s funkcijo COUNTA.
Ampak … pri tem je en majhen problem.
Ko uvozite podatke ali uporabite delovni list drugih ljudi, boste velikokrat opazili, da so prazne celice, čeprav to morda ni tako.
Te celice so videti prazne, vendar vsebujejo = ””. Težava je v tem, da je
Težava je v tem, da funkcija COUNTA tega ne obravnava kot prazno celico (šteje jo kot besedilo).
Oglejte si spodnji primer:
V zgornjem primeru uporabljam funkcijo COUNTA za iskanje celic, ki niso prazne in vrne 11 in ne 10 (vendar lahko jasno vidite, da ima samo 10 celic besedilo).
Kot sem omenil, je razlog, da A11 ne šteje za praznega (čeprav bi moral).
Ampak tako deluje Excel.
Popravek je, da v formuli uporabite nadomestni znak Excel.
Spodaj je formula s funkcijo COUNTIF, ki šteje samo celice z besedilom:
= COUNTIF (A1: A11, "?*")
Ta formula Excelu pove, naj šteje le, če ima celica vsaj en znak.
V ?* kombinirano:
- ? (vprašaj) zagotavlja, da je prisoten vsaj en znak.
- * (zvezdica) naredi prostor za poljubno število dodatnih znakov.
Opomba: Zgornja formula deluje, če so v celicah samo besedilne vrednosti. Če imate seznam z besedilom in številkami, uporabite naslednjo formulo:
= COUNTA (A1: A11) -COUNTBLANK (A1: A11)
Podobno lahko nadomestne znake uporabite v številnih drugih Excelovih funkcijah, kot so IF (), SUMIF (), AVERAGEIF () in MATCH ().
Zanimivo je tudi, da čeprav lahko v funkciji SEARCH uporabite nadomestne znake, jih v funkciji FIND ne morete uporabiti.
Upam, da vam ti primeri dajejo občutek vsestranskosti in moči Excelovih nadomestnih znakov.
Če imate kakšen drug inovativen način uporabe, ga delite z mano v razdelku za komentarje.
Morda se vam bodo zdeli koristni naslednji vadnici programa Excel:
- Uporaba COUNTIF in COUNTIFS z več merili.
- Ustvarjanje spustnega seznama v Excelu.
- Operater preseči v Excelu
