Vaadake videot Exceli metamärkide abil
Exceli metamärke (tärn, küsimärk ja tilde) on ainult 3 ja nende abil saab palju ära teha.
Selles õpetuses näitan teile nelja näidet, kus need Exceli metamärgid on absoluutsed elupäästjad.
Exceli metamärgid - sissejuhatus
Metamärgid on erimärgid, mis võivad asendada mis tahes tähemärgi mis tahes koha (siit ka nimi - metamärk).
Excelis on kolm metamärki:
- * (tärn) - See tähistab suvalist arvu tähemärke. Näiteks Ex* võib tähendada Exceli, Exceli, Näite, Ekspertiisi jne.
- ? (küsimärk) - See kujutab endast ühte tegelast. Näiteks Tr? Mp võib tähendada Trumpi või Trampi.
- ~ (tilde) - Seda kasutatakse tekstis metamärgi (~, *,?) Tuvastamiseks. Oletame näiteks, et soovite loendist leida täpse fraasi Excel*. Kui kasutate otsingustringina Excelit*, annaks see teile sõna, mille alguses on Excel, millele järgneb suvaline arv tähemärke (nt Excel, Excels, Excellent). Exceli*otsimiseks peame kasutama ~. Nii et meie otsingustring oleks excel ~*. Siin tagab ~ olemasolu, et Excel loeb järgmist märki sellisena, nagu see on, ja mitte metamärgina.
Märkus: Ma ei ole kohanud palju olukordi, kus peate kasutama ~. Sellegipoolest on hea teada funktsioon.
Nüüd vaatame läbi neli vinget näidet, kus metamärgid teevad kogu raskuse.
Exceli metamärgid - näited
Nüüd vaatame nelja praktilist näidet, kus Exceli metamärgid võivad olla väga kasulikud:
- Andmete filtreerimine metamärgi abil.
- Osaline otsing metamärgi ja VLOOKUP abil.
- Osaliste vaste leidmine ja asendamine.
- Loendage teksti mitte sisaldavad tühjad lahtrid.
#1 Filtreerige andmeid Exceli metamärkide abil
Exceli metamärgid on abiks, kui teil on tohutult palju andmekogumeid ja soovite andmeid tingimuste alusel filtreerida.
Oletame, et teil on allpool näidatud andmekogum:
A -tähega algavate ettevõtete loendi saamiseks võite andmefiltris kasutada tärni (*) metamärki.
Siin on, kuidas seda teha.
- Valige lahtrid, mida soovite filtreerida.
- Avage Andmed -> Sorteeri ja filtreeri -> Filter (Klaviatuuri otsetee - Control + Shift + L).
- Klõpsake päise lahtris filtriikooni
- Tippige väljale (tekstifiltri suvandi alla) A*
- Klõpsake nuppu OK.
See filtreerib tulemused koheselt ja annab teile kolm nime - ABC Ltd., Amazon.com ja Apple Stores.
Kuidas see töötab? - Kui lisate tähe (*) järele A, filtreerib Excel kõik, mis algab tähega A. Seda seetõttu, et tärn (mis on Exceli metamärk) võib tähistada suvalist arvu tähemärke.
Sama metoodikaga saate tulemuste filtreerimiseks kasutada erinevaid kriteeriume.
Näiteks kui soovite filtreerida ettevõtteid, mis algavad tähestikuga A ja sisaldavad tähestikku C, kasutage stringi A*C. See annab teile ainult 2 tulemust - ABC Ltd. ja Amazon.com.
Kui kasutate A? C. selle asemel saate tulemuseks ainult ABC Ltd (kuna a ja c vahel on lubatud ainult üks märk)
Märge: Sama kontseptsiooni saab rakendada ka Exceli täpsemate filtrite kasutamisel.
#2 Osaline otsing metamärkide ja VLOOKUP abil
Osaline otsing on vajalik, kui peate loendist väärtust otsima ja täpset vastet pole.
Oletame näiteks, et teil on allpool näidatud andmekogum ja soovite otsida loendist ettevõtet ABC, kuid loendis on ABC asemel ABC Ltd.
Sel juhul ei saa tavalist funktsiooni VLOOKUP kasutada, kuna otsinguväärtus ei vasta täpselt.
Kui kasutate VLOOKUPi ligikaudse vastega, annab see teile valesid tulemusi.
Õigete tulemuste saamiseks võite funktsiooni VLOOKUP raames siiski kasutada metamärki.
Sisestage lahtrisse D2 järgmine valem ja lohistage see teiste lahtrite jaoks:
= VLOOKUP ("*" & C2 & "*", $ A $ 2: $ A $ 8,1, FALSE)
Kuidas see valem töötab?
Ülaltoodud valemis on selle asemel, et kasutada otsinguväärtust, külgneb see mõlemalt poolt Exceli metamärgi tärniga (*) - "*" & C2 & "*"
See ütleb Excelile, et ta peab otsima teksti, mis sisaldab sõna C2 -st. See võib sisaldada suvalist arvu märke enne või pärast teksti C2 -s.
Seega otsib valem vaste ja niipea, kui see saab vaste, tagastab selle väärtuse.
3. Leidke ja asendage osalised vasted
Exceli metamärgid on üsna mitmekülgsed.
Saate seda kasutada nii keerulises valemis kui ka põhifunktsioonides, näiteks Otsi ja asenda.
Oletame, et teil on järgmised andmed:
Ülaltoodud andmetes on piirkond sisestatud erineval viisil (näiteks Loode, Loode, Loode).
Sageli juhtub see müügiandmetega.
Nende andmete puhastamiseks ja järjepidevaks muutmiseks võime kasutada funktsiooni Otsing ja asendamine Exceli metamärkidega.
Siin on, kuidas seda teha.
- Valige andmed, kust soovite teksti leida ja asendada.
- Minge avalehele -> Otsi ja vali -> Ava. See avab dialoogiboksi Otsi ja asenda. (Võite kasutada ka kiirklahvi - Control + H).
- Sisestage otsingu ja asendamise dialoogiboksi järgmine tekst:
- Leia mida: Põhja*W*
- Asenda: Loe
- Klõpsake nuppu Asenda kõik.
See muudab koheselt kõiki erinevaid vorminguid ja muudab selle loodega ühtlaseks.
Kuidas see töötab?
Väljal Otsi oleme kasutanud Põhja*W* mis leiab kõik tekstid, millel on sõna Põhja ja mis sisaldavad tähestikku „W”.
Seega hõlmab see kõiki stsenaariume (Loode, Loode ja Loode).
Otsing ja asendamine leiab kõik need juhtumid ja muudab selle loodeks ning muudab selle järjepidevaks.
4. Loendage teksti sisaldavaid lahtreid
Ma tean, et olete tark ja arvate, et Excelil on selleks juba sisseehitatud funktsioon.
Sul on täiesti õigus!!
Seda saab teha funktsiooni COUNTA abil.
AGA … Sellega on üks väike probleem.
Kui impordite andmeid või kasutate teiste inimeste töölehte, märkate palju kordi, et lahtrid on tühjad, kuigi see ei pruugi nii olla.
Need lahtrid tunduvad tühjad, kuid sisaldavad = ””. Häda on selles, et
Probleem on selles, et funktsioon COUNTA ei pea seda tühjaks (loeb selle tekstiks).
Vaadake allolevat näidet:
Ülaltoodud näites kasutan funktsiooni COUNTA, et leida lahtreid, mis pole tühjad ja tagastab 11, mitte 10 (kuid näete selgelt, et ainult 10 lahtris on teksti).
Põhjus, nagu ma mainisin, on see, et see ei pea A11 tühjaks (kuigi peaks).
Kuid Excel töötab nii.
Paranduseks on Exceli metamärgi kasutamine valemis.
Allpool on funktsioon COUNTIF, mis kasutab ainult teksti sisaldavaid lahtreid:
= COUNTIF (A1: A11, "?*")
See valem käsib Excelil arvestada ainult siis, kui lahtris on vähemalt üks märk.
Aastal ?* kombinatsioon:
- ? (küsimärk) tagab vähemalt ühe märgi olemasolu.
- * (tärn) jätab ruumi suvalisele arvule lisamärkidele.
Märge: Ülaltoodud valem töötab, kui lahtrites on ainult tekstiväärtused. Kui teil on loend, mis sisaldab nii teksti kui ka numbreid, kasutage järgmist valemit:
= COUNTA (A1: A11) -COUNTBLANK (A1: A11)
Samamoodi saate metamärke kasutada paljudes teistes Exceli funktsioonides, näiteks IF (), SUMIF (), AVERAGEIF () ja MATCH ().
Samuti on huvitav märkida, et kuigi saate kasutada otsingufunktsioonis metamärke, ei saa te seda kasutada funktsioonis FIND.
Loodame, et need näited annavad teile elegantsi Exceli metamärkide mitmekülgsusest ja võimsusest.
Kui teil on mõni muu uuenduslik viis selle kasutamiseks, jagage seda minuga kommentaaride jaotises.
Järgmised Exceli õpetused võivad teile kasulikud olla:
- COUNTIF ja COUNTIFS kasutamine mitme kriteeriumiga.
- Excelis ripploendi loomine.
- Lõika operaator Excelis
