Bekijk video over Excel-jokertekens
Er zijn slechts 3 Excel-jokertekens (asterisk, vraagteken en tilde) en hiermee kan veel worden gedaan.
In deze zelfstudie laat ik u vier voorbeelden zien waarin deze Excel-jokertekens absolute redders in nood zijn.
Excel-jokertekens - een inleiding
Jokertekens zijn speciale tekens die elke willekeurige plaats kunnen innemen (vandaar de naam - jokerteken).
Er zijn drie jokertekens in Excel:
- * (sterretje) - Het vertegenwoordigt een willekeurig aantal tekens. Ex* kan bijvoorbeeld Excel, Excels, Voorbeeld, Expert, enz. betekenen.
- ? (vraagteken) - Het vertegenwoordigt één enkel teken. Tr?mp kan bijvoorbeeld Trump of Vagebond betekenen.
- ~ (tilde) - Het wordt gebruikt om een jokerteken (~, *, ?) in de tekst te identificeren. Stel dat u bijvoorbeeld de exacte woordgroep Excel* in een lijst wilt vinden. Als u Excel* als zoekreeks gebruikt, krijgt u elk woord met Excel aan het begin gevolgd door een willekeurig aantal tekens (zoals Excel, Excels, Excellent). Om specifiek naar Excel* te zoeken, moeten we ~ gebruiken. Dus onze zoekreeks zou excel~* zijn. Hier zorgt de aanwezigheid van ~ ervoor dat Excel het volgende teken leest zoals het is, en niet als een jokerteken.
Opmerking: ik ben niet veel situaties tegengekomen waarin je ~ moet gebruiken. Toch is het een goed om te weten functie.
Laten we nu vier geweldige voorbeelden doornemen waarbij jokertekens al het zware werk doen.
Excel-jokertekens - voorbeelden
Laten we nu eens kijken naar vier praktische voorbeelden waar Excel-jokertekens erg handig kunnen zijn:
- Gegevens filteren met een jokerteken.
- Gedeeltelijk opzoeken met jokertekens en VERT.ZOEKEN.
- Vind en vervang gedeeltelijke overeenkomsten.
- Tel niet-lege cellen die tekst bevatten.
#1 Gegevens filteren met Excel-jokertekens
Excel-jokertekens zijn handig wanneer u enorme gegevenssets hebt en u gegevens wilt filteren op basis van een voorwaarde.
Stel dat u een dataset heeft zoals hieronder weergegeven:
U kunt het jokerteken sterretje (*) in het gegevensfilter gebruiken om een lijst te krijgen van bedrijven die beginnen met het alfabet A.
Hier is hoe dit te doen:
- Selecteer de cellen die u wilt filteren.
- Ga naar Gegevens -> Sorteren en filteren -> Filter (Toetsenbordsnelkoppeling - Control + Shift + L).
- Klik op het filterpictogram in de kopcel
- Typ in het veld (onder de optie Tekstfilter) EEN*
- Klik OK.
Hiermee worden de resultaten onmiddellijk gefilterd en krijgt u 3 namen: ABC Ltd., Amazon.com en Apple Stores.
Hoe werkt het? - Wanneer u een asterisk (*) toevoegt na A, filtert Excel alles dat begint met A. Dit komt omdat een asterisk (een Excel-jokerteken) een willekeurig aantal tekens kan vertegenwoordigen.
Met dezelfde methodologie kunt u nu verschillende criteria gebruiken om resultaten te filteren.
Als u bijvoorbeeld bedrijven wilt filteren die beginnen met het alfabet A en het alfabet C erin bevatten, gebruikt u de tekenreeks airco Dit geeft u slechts 2 resultaten - ABC Ltd. en Amazon.com.
Als je gebruikt A?C in plaats daarvan krijgt u alleen ABC Ltd als resultaat (omdat er slechts één teken is toegestaan tussen 'a' en 'c')
Opmerking: Hetzelfde concept kan ook worden toegepast bij het gebruik van Excel geavanceerde filters.
#2 Gedeeltelijke opzoeking met jokertekens en VERT.ZOEKEN
Gedeeltelijk opzoeken is nodig wanneer u naar een waarde in een lijst moet zoeken en er geen exacte overeenkomst is.
Stel dat u een gegevensset heeft zoals hieronder weergegeven, en u wilt het bedrijf ABC in een lijst zoeken, maar de lijst heeft ABC Ltd in plaats van ABC.
U kunt in dit geval de normale functie VERT.ZOEKEN niet gebruiken omdat de opzoekwaarde geen exacte overeenkomst heeft.
Als u VERT.ZOEKEN gebruikt met een geschatte overeenkomst, krijgt u de verkeerde resultaten.
U kunt echter een jokerteken gebruiken in de functie VERT.ZOEKEN om de juiste resultaten te krijgen:
Voer de volgende formule in cel D2 in en sleep deze voor andere cellen:
=VLOOKUP("*"&C2&"*",$A$2:$A$8,1,FALSE)
Hoe werkt deze formule?
In de bovenstaande formule, in plaats van de opzoekwaarde te gebruiken zoals deze is, wordt deze aan beide zijden geflankeerd door het Excel-jokerteken sterretje (*) - “*”&C2&”*”
Dit vertelt Excel dat het moet zoeken naar elke tekst die het woord in C2 bevat. Het kan een willekeurig aantal tekens voor of na de tekst in C2 hebben.
Daarom zoekt de formule naar een overeenkomst en zodra deze een overeenkomst krijgt, wordt die waarde geretourneerd.
3. Gedeeltelijke overeenkomsten zoeken en vervangen
Excel-jokertekens zijn behoorlijk veelzijdig.
Je kunt het zowel in een complexe formule gebruiken als in basisfunctionaliteit zoals Zoeken en Vervangen.
Stel dat u de gegevens heeft zoals hieronder weergegeven:
In bovenstaande gegevens is de regio op verschillende manieren ingevuld (zoals Noordwest, Noordwest, Noordwest).
Dit is vaak het geval bij verkoopgegevens.
Om deze gegevens op te schonen en consistent te maken, kunnen we Zoeken en vervangen gebruiken met Excel-jokertekens.
Hier is hoe dit te doen:
- Selecteer de gegevens waar u tekst wilt zoeken en vervangen.
- Ga naar Home -> Zoek & Selecteer -> Ga naar. Hiermee wordt het dialoogvenster Zoeken en vervangen geopend. (U kunt ook de sneltoets gebruiken - Control + H).
- Typ de volgende tekst in het dialoogvenster Zoeken en vervangen:
- Vind wat: Noord*W*
- Vervangen door: Noord West
- Klik op Alles vervangen.
Dit zal onmiddellijk alle verschillende formaten veranderen en het consistent maken met Noord-West.
Hoe werkt dit?
In het veld Zoeken hebben we gebruikt Noord*W* die elke tekst zal vinden die het woord Noord heeft en het alfabet 'W' ergens daarna bevat.
Het omvat dus alle scenario's (Noordwest, Noordwest en Noordwest).
Zoeken en vervangen vindt al deze instanties en verandert het in Noordwest en maakt het consistent.
4. Tel niet-lege cellen die tekst bevatten
Ik weet dat je slim bent en je denkt dat Excel al een ingebouwde functie heeft om dit te doen.
Je hebt helemaal gelijk!!
Dit kan met behulp van de COUNTA-functie.
MAAR… Er is een klein probleem mee.
Vaak wanneer u gegevens importeert of het werkblad van anderen gebruikt, zult u merken dat er lege cellen zijn, terwijl dat misschien niet het geval is.
Deze cellen zien er blanco uit, maar hebben ="" erin. Het probleem is dat de
Het probleem is dat de COUNTA-functie dit niet als een lege cel beschouwt (hij telt het als tekst).
Zie het voorbeeld hieronder:
In het bovenstaande voorbeeld gebruik ik de COUNTA-functie om cellen te vinden die niet leeg zijn en het retourneert 11 en niet 10 (maar je kunt duidelijk zien dat slechts 10 cellen tekst hebben).
De reden, zoals ik al zei, is dat het A11 niet als leeg beschouwt (terwijl het zou moeten).
Maar zo werkt Excel.
De oplossing is om het Excel-jokerteken in de formule te gebruiken.
Hieronder staat een formule die de AANTAL.ALS-functie gebruikt die alleen cellen telt die tekst bevatten:
=AANTAL.ALS(A1:A11,"?*")
Deze formule vertelt Excel om alleen te tellen als de cel ten minste één teken heeft.
In de ?* combinatie:
- ? (vraagteken) zorgt ervoor dat er minimaal één teken aanwezig is.
- * (sterretje) maakt ruimte voor een willekeurig aantal extra tekens.
Opmerking: De bovenstaande formule werkt als er alleen tekstwaarden in de cellen staan. Als je een lijst hebt met zowel tekst als cijfers, gebruik dan de volgende formule:
=AANTALLEN(A1:A11)-AANTALLEGE (A1:A11)
Op dezelfde manier kunt u jokertekens gebruiken in veel andere Excel-functies, zoals IF(), SUMIF(), AVERAGEIF() en MATCH().
Het is ook interessant om op te merken dat hoewel u de jokertekens in de ZOEK-functie kunt gebruiken, u deze niet kunt gebruiken in de FIND-functie.
Ik hoop dat deze voorbeelden je een idee geven van de veelzijdigheid en kracht van Excel-jokertekens.
Als je een andere innovatieve manier hebt om het te gebruiken, deel het dan met mij in het opmerkingengedeelte.
Mogelijk vindt u de volgende Excel-zelfstudies nuttig:
- AANTAL.ALS en AANTAL.ALS gebruiken met meerdere criteria.
- Een vervolgkeuzelijst maken in Excel.
- Intersect-operator in Excel
