De tutorial legt uit hoe je tekens moet tellen in Excel. U leert de formules om het totale aantal tekens in een bereik te krijgen en alleen specifieke tekens in een cel of in meerdere cellen te tellen.
In onze vorige zelfstudie is de Excel LEN-functie geïntroduceerd, waarmee u het totale aantal kunt tellen van tekens in een cel.
De LEN-formule is op zichzelf nuttig, maar in samenwerking met andere functies zoals SUM, SUMPRODUCT en SUBSTITUTE kan het veel complexere taken aan. Verderop in deze tutorial gaan we enkele basis- en geavanceerde formules nader bekijken om tekens in Excel te tellen.
- Tel een totaal aantal tekens in een bereik
- Specifieke tekens in een cel tellen
- Specifieke tekens in een bereik tellen
Hoe alle tekens in een bereik te tellen
Wanneer komt tot het tellen van een totaal aantal tekens in verschillende cellen, een onmiddellijke oplossing die in me opkomt, is om het aantal tekens voor elke cel te krijgen en vervolgens die getallen bij elkaar op te tellen:
=LEN(A2)+LEN(A3)+LEN(A4)
Of
=SUM(LEN(A2),LEN(A3),LEN(A4))
De bovenstaande formules werken mogelijk goed voor een klein bereik. Om het totale aantal tekens in een groter bereik te tellen, kunnen we maar beter iets compacter bedenken, bijvoorbeeld de functie SUMPRODUCT, die de arrays vermenigvuldigt en de som van de producten retourneert.
Hier is de algemene Excel formule om tekens in een bereik te tellen:
En uw echte formule kan er ongeveer zo uitzien:
=SUMPRODUCT(LEN(A1:A7))
Een andere manier om tekens in een bereik te tellen, is door de LEN-functie te gebruiken in combinatie met SUM:
=SUM(LEN(A1:A7))
In tegenstelling tot SUMPRODUCT berekent de SOM-functie standaard geen arrays en moet u op Ctrl + Shift + drukken Enter om er een matrixformule van te maken.
Zoals aangetoond in de volgende schermafbeelding, retourneert de SUM-formule hetzelfde totale aantal tekens:
Hoe deze formule voor het tellen van bereiktekens werkt
Dit is een van de meest eenvoudige formules om tekens in Excel te tellen. De functie LEN berekent de tekenreekslengte voor elke cel in het opgegeven bereik en retourneert deze als een reeks getallen. En vervolgens telt SOMPRODUCT of SOM die getallen bij elkaar en retourneert het totale aantal tekens.
In het bovenstaande voorbeeld wordt een array van 7 getallen die de lengte van tekenreeksen in de cellen A1 tot en met A7 vertegenwoordigen opgeteld:
Hoe te tellen specifieke tekens in een cel
Soms hoeft u in plaats van alle tekens in een cel te tellen, alleen de exemplaren van een specifieke letter, cijfer of speciaal symbool te tellen.
Aan tel het aantal keren dat een bepaald teken in een cel voorkomt, gebruik de functie LEN samen met SUBSTITUTE:
Bekijk het volgende voorbeeld om de formule beter te begrijpen.
Stel dat u een database met geleverde artikelen bijhoudt, waarbij elk itemtype zijn eigen unieke identificatiecode heeft. En elke cel bevat verschillende items, gescheiden door komma’s, spatie of een ander scheidingsteken. Het is de taak om te tellen hoe vaak een bepaalde unieke identificatie in elke cel voorkomt.
Ervan uitgaande dat de lijst met geleverde artikelen in kolom B staat (beginnend in B2), en we tellen het aantal ‘A “exemplaren, de formule is als volgt:
=LEN(B2)-LEN(SUBSTITUTE(B2,"A",""))
Hoe deze Excel-formule voor het tellen van tekens werkt
Laten we, om de logica van de formule te begrijpen, deze in kleinere delen opsplitsen:
Als resultaat krijg je de telling van ‘verwijderd “-tekens, wat gelijk is aan een totaal aantal exemplaren van dat teken in de cel.
In plaats van het teken op te geven dat u in een formule wilt tellen, kunt u het in een cel typen en vervolgens naar die cel verwijzen in een formule. Op deze manier kunnen uw gebruikers elk ander teken dat ze in die cel invoeren, tellen zonder met uw formule te knoeien:
Hoofdletterongevoelige Excel-formule om specifieke tekens in een cel te tellen
Als u een niet-hoofdlettergevoelig aantal tekens nodig heeft, sluit u de functie HOOFDLETTERS in SUBSTITUTE in om het opgegeven teken vóór het uitvoeren van de vervanging. En zorg ervoor dat u de hoofdletters in de formule invoert.
Om bijvoorbeeld “A” en “a” items in cel B2 te tellen, gebruikt u deze formule:
=LEN(B2)-LEN(SUBSTITUTE(UPPER(B2),"A",""))
Een andere manier is om geneste vervangingsfuncties te gebruiken:
=LEN(B2)-LEN(SUBSTITUTE(SUBSTITUTE (B2,"A",""),"a","")
Zoals je kunt zien in de onderstaande schermafbeelding, tellen beide formules feilloos hoofdletters en kleine letters van het opgegeven teken:
In sommige gevallen , moet u mogelijk veel verschillende tekens in een tabel tellen, maar wilt u de formule niet elke keer wijzigen. Nest in dit geval de ene vervangingsfunctie in een andere, typ het teken dat u in een cel wilt tellen (D1 in dit voorbeeld) en converteer de waarde van die cel naar hoofdletters en kleine letters met behulp van de functies HOOFDLETTERS en LAGER:
=LEN(B2)-LEN(SUBSTITUTE(SUBSTITUTE(B2, UPPER($D$1), ""), LOWER($D$1),""))
U kunt ook zowel de broncel als de cel met het teken naar hoofdletters of kleine letters converteren. Bijvoorbeeld:
=LEN(B2)-LEN(SUBSTITUTE(UPPER(B2), UPPER($C$1),""))
Het voordeel van deze benadering is dat ongeacht of het hoofdletter- of kleine teken wordt ingevoerd in de cel waarnaar wordt verwezen, uw niet-hoofdlettergevoelige formule voor het tellen van tekens retourneert het juiste aantal:
Tel het aantal keren dat bepaalde tekst of substring in een cel voorkomt
Als je wilt tellen hoe vaak een specifieke combinatie van tekens (dat wil zeggen bepaalde tekst of subtekenreeks) voorkomt in een bepaalde cel, bijvoorbeeld “A2” of “SS”, en deel vervolgens het aantal tekens dat door de bovenstaande formules wordt geretourneerd door de lengte van de subtekenreeks .
Hoofdlettergevoelige formule:
=(LEN(B2)-LEN(SUBSTITUTE(B2, $C$1,"")))/LEN($C$1)
Hoofdlettergevoelige formule:
=(LEN(B2)-LEN(SUBSTITUTE(LOWER(B2),LOWER($C$1),"")))/LEN($C$1)
Waar B2 de cel is die de hele tekstreeks bevat en C1 de tekst (substring) die je wilt tellen.
Voor de gedetailleerde uitleg van de formule, zie Hoe specifieke tekst / woorden in een cel te tellen.
Hoe specifieke tekens ( s) in een bereik
Nu u een Excel-formule kent om tekens in een cel te tellen, wilt u deze misschien verder verbeteren om erachter te komen hoe vaak een bepaald teken in een bereik voorkomt. Hiervoor nemen we de Excel LEN-formule om een specifiek teken te tellen in een cel die in het vorige voorbeeld is besproken, en plaatsen het in de SUMPRODUCT-functie die arrays aankan:
In dit voorbeeld heeft de formule de volgende vorm:
=SUMPRODUCT(LEN(B2:B8)-LEN(SUBSTITUTE(B2:B8, "A","")))
En hier is nog een formule om tekens in het bereik van Excel te tellen:
=SUM(LEN(B2:B8)-LEN(SUBSTITUTE(B2:B8, "A","")))
In vergelijking met de eerste formule is het duidelijkste verschil het gebruik van SUM in plaats van SOMPRODUCT. Een ander verschil is dat het nodig is om op Ctrl + Shift + Enter te drukken, want in tegenstelling tot SUMPRODUCT, dat is ontworpen om arrays te verwerken, kan SUM arrays alleen verwerken als ze in een matrixformule worden gebruikt.
Als u niet wilt hardcoderen het teken in de formule, je kunt het natuurlijk in een cel typen, bijvoorbeeld D1, en naar die cel verwijzen in je formule voor het tellen van tekens:
=SUMPRODUCT(LEN(B2:B8)-LEN(SUBSTITUTE(B2:B8, D1,"")))
Hoe deze formule voor het tellen van tekens werkt
Zoals u zich wellicht herinnert, wordt de functie SUBSTITUTE gebruikt om alle exemplaren van het opgegeven teken (“A” in dit voorbeeld) te vervangen door een lege tekenreeks ( “”).
Vervolgens leveren we de tekstreeks die wordt geretourneerd door SUBSTITUTE aan de Excel LEN-functie zodat deze de stringlengte berekent zonder A “s. En dan trekken we dat aantal tekens af van de totale lengte van de tekstreeks. Het resultaat van deze berekeningen is een reeks karaktertellingen, met één karaktertelling per cel.
Ten slotte telt SUMPRODUCT de getallen in de array op en retourneert het totale aantal van het opgegeven karakter in het bereik.
Een niet-hoofdlettergevoelige formule om specifieke tekens in een bereik te tellen
Je weet al dat SUBSTITUTE een hoofdlettergevoelige functie is, waardoor onze Excel-formule voor het tellen van tekens ook hoofdlettergevoelig is.
Om ervoor te zorgen dat de formule hoofdlettergebruik negeert, volgt u de benaderingen die in het vorige voorbeeld zijn gedemonstreerd: Hoofdletterongevoelige formule om specifieke tekens in een cel te tellen.
In het bijzonder kunt u een van de volgende formules om specifieke tekens in een bereik te tellen waarbij hoofdletters / kleine letters worden genegeerd:
De onderstaande schermafbeelding toont de laatste formule in actie:
Zo kunt u tekens in Excel tellen met behulp van de LEN functie.Als u wilt weten hoe u woorden moet tellen in plaats van individuele tekens, vindt u in ons volgende artikel een paar handige formules, blijf op de hoogte!
In de tussentijd kunt u een voorbeeldwerkboek downloaden met het aantal tekens formule die in deze tutorial wordt besproken, en bekijk een lijst met gerelateerde bronnen aan het einde van de pagina. Ik dank u voor het lezen en ik hoop u snel te zien!
- Excel LEN-functie – formules om de lengte van een cel te berekenen, dwz het totale aantal tekens in een cel, met of zonder spaties.
- Hoe woorden in Excel te tellen – hoofdlettergevoelige en niet-hoofdlettergevoelige formules om totale of specifieke woorden in een cel of celbereik te tellen.
- COUNT- en COUNTA-functies – tel cellen met getallen en niet-lege cellen.
- AANTAL.ALS-functie – formules om cellen voorwaardelijk te tellen in Excel.
- AANTAL.ALS-functie – cellen tellen met meer dan één criterium.
