Tekens tellen in Excel
Na deze tutorial kunt u het totale aantal tekens in een cel of bereik tellen, het aantal keren dat een specifiek teken of tekencombinatie in een cel of bereik voorkomt met beide hoofdlettergevoelig en ongevoelige alternatieven. De kennis die in deze tutorial is opgedaan, is echt een goede basis voor het creatief oplossen van Excel-problemen.
Tekens tellen in Excel
Als u wilt leren hoe u tekens in Excel kunt tellen, je moet de functie LEN gebruiken, met formule = LEN (cel) voor het tellen van het totale aantal tekens in een cel, of een combinatie van de functies SOMPRODUCT en LEN voor het tellen van het totale aantal tekens in een bereik met formule = SOMPRODUCT (LEN (bereik)). Bovendien wordt het tellen van een specifiek teken in een cel of bereik en een specifieke combinatie van tekens in een cel of bereik in detail uitgelegd.
- Totaal aantal tekens in een cel tellen
- Totaal aantal tekens in een bereik tellen
- Specifiek teken in een cel tellen
- Aantal Specifiek teken in een bereik
- Specifieke combinatie van tekens in een cel of bereik tellen
Totaal aantal tekens in een cel tellen
Totaal aantal tekens in een cel kan gemakkelijk worden gevonden met de Excel-functie LEN. Deze functie heeft slechts één argument, celverwijzing of tekst, waarbij het totale aantal tekens zou willen worden geteld:
=LEN(text)
=LEN(B3)
Deze functie telt het totale aantal tekens in een cel, inclusief spaties, leestekens, symbolen, ongeacht hoe vaak ze in een tekenreeks voorkomen.
Als we het totaal aantal tekens in een cel willen tellen, exclusief spaties, is de combinatie van formules LEN en SUBSTITUTE vereist. Laten we het onderstaande voorbeeld eens bekijken, de formule die spaties uitsluit ziet er als volgt uit:
=LEN(SUBSTITUTE(B3;" ";""))
De SUBSTITUTE-functie verandert in een gedefinieerde cel één teken / tekst met een ander teken / tekst. Als we spaties willen uitsluiten, met behulp van deze functie, zullen ze worden geëlimineerd, waarbij alle voorkomende spaties worden vervangen door een lege string. Na SUBSTITUTE ziet functietekst in een cel er als volgt uit:
= LEN (“Sunisshining”)
Daarna is het eenvoudig om het totale aantal tekens te tellen met de LEN-functie, met de laatste resultaat van 12 tekens.
Totaal aantal tekens in een bereik tellen
Voor het tellen van het totale aantal tekens in een gedefinieerd bereik is de combinatie van twee functies nodig, SOMPRODUCT en LEN. SOMPRODUCT-functiegebruik is een elegante oplossing wanneer we te maken hebben met meerdere cellen of arrays. Bekijk het onderstaande voorbeeld en een combinatie van de formules:
=SUMPRODUCT(LEN(range))
=SUMPRODUCT(LEN(B3:B6))
LEN-functie is gerelateerd aan cel, maar voor celbereiken gebruik van gesommeerde LEN-functies (= LEN (B3) + LEN (B4) + LEN (B5) + LEN (B6)) is niet de beste oplossing, aangezien we te maken kunnen hebben met enorme bereiken. In plaats daarvan vat de SOMPRODUCT-functie samen resultaten van de LEN-functie in het gedefinieerde bereik In formule-evaluatie ziet het resultaat er als volgt uit:
Getallen uit de array zijn LEN-functieresultaten van elke cel in het gedefinieerde bereik B3: B6, wat de uiteindelijke tekenresultaten oplevert van 75.
De alternatieve oplossing voor het omgaan met bereiken is het gebruik van de combinatie SOM en LEN. De syntaxis is bijna hetzelfde, met het enige verschil in het gebruik van accolades, om de functie SUM in matrixformule te converteren (sneltoets voor het maken van een matrix is CTRL + SHIFT + ENTER). In de onderstaande formule is de exacte syntaxis voor de combinatie SOM / LEN-functie:
={SUM(LEN(B3:B6))}
Specifiek teken in een cel tellen
Naast het totale aantal tekens is er in Excel ook de mogelijkheid om het aantal keren dat specifieke tekens voorkomen te tellen. Laten we eens kijken naar het voorbeeld van het tellen van het aantal afzonderlijke tekens in een specifieke cel. Voor dit doel is een combinatie van LEN- en SUBSTITUTE-functie nodig, zoals we deden in het vergelijkbare voorbeeld van het tellen van het aantal tekens in de cel zonder spatie. De syntaxis van de formule ziet er als volgt uit:
=LEN(cell)-LEN(SUBSTITUTE(cell;character;""))
Als we in een specifiek voorbeeld het aantal teken s in een gedefinieerde cel B3 ziet de formule er als volgt uit:
=LEN(B3)-LEN(SUBSTITUTE(B3;"s";""))
Laten we de logica van de functiecombinatie kort uitleggen. Het totale aantal tekens in een cel B3 wordt afgetrokken met het tekennummer in dezelfde cel, maar zonder een specifiek teken dat we willen tellen. Zoals vermeld in tutorial punt 1, wordt de functie SUBSTITUTE gebruikt om een tekenreeks in een gedefinieerde cel in vorm zonder specifiek teken te veranderen, waarbij dat teken wordt vervangen door een lege tekenreeks.
=SUBSTITUTE(cell;"character";"")
Het kan worden opgemerkt in het eindresultaat dat de functie geen hoofdletters telt, aangezien de LEN-functie hoofdlettergevoelig is. De oplossing voor het tellen van tekens zonder hoofdlettergevoelige criteria is het gebruik van de functie UPPER / LOWER, waarbij alle tekens worden vertaald naar hoofdletters / kleine letters en de functie niet hoofdlettergevoelig wordt.
In het onderstaande voorbeeld, de functie LAGER is genest in de SUBSTITUTE-functie, waarbij alle tekenreeksen in cel B3 in kleine letters worden veranderd, aangezien criteria zijn gedefinieerd als kleine letters, “s”:
=LEN(cell)-LEN(SUBSTITUTE(LOWER(cell);"lowercase character";""))
=LEN(B3)-LEN(SUBSTITUTE(LOWER(B3);"s";""))
Een andere oplossing om een niet-hoofdlettergevoelige functie te maken is het gebruik van de dubbel geneste SUBSTITUTE-functie in combinatie met de LEN-functie. In het verdere voorbeeld zal het telkarakter in een specifieke cel staan, omdat het soms niet praktisch is om elke keer te schrijven om een karakter in de formule te tellen, vooral als je te maken hebt met met complexe bereiken en formules.
Formule met dubbel geneste SUBSTITUTE-functie:
=LEN(cell)-LEN(SUBSTITUTE(SUBSTITUTE(cell;LOWER(character);"");UPPER(character);""))
=LEN(B3)-LEN(SUBSTITUTE(SUBSTITUTE(B3;LOWER(B6);"");UPPER(B6);""))
De formule ziet er misschien ingewikkeld uit, maar na uitleg zal alles duidelijk zijn. Laten we de formule stap voor stap evalueren.
Ten eerste willen we het tellen van kleine letters uit de tekst verwijderen:
=SUBSTITUTE(B3;LOWER(B6);"")
=SUBSTITUTE("Sun is shining ”;LOWER("S”);"")
De functie LOWER wordt geplaatst om het telkarakter in kleine letters te vertalen, en dan vervangt de functie + SUBSTITUTE kleine letters in een lege string. Na deze stap ziet het resultaat van de formule eruit als: “Sun i hining”.
In de volgende formulestap is het doel om het hoofdletterteken uit de gedefinieerde tekst / cel te verwijderen. Dit wordt opgelost door de geneste SUBSTITUTE-functie :
=SUBSTITUTE(SUBSTITUTE(B3;LOWER(B6);"");UPPER(B6);"")
We hebben het binnen SUBSTITUTE functie resultaat “Sun i hining” al uitgelegd, en zullen het in de onderstaande functie plaatsen maak de situatie duidelijker. In de eerste functie SUBSTITUTE is de kleine letter vervangen door een lege string, en in de tweede SUBSTITUTE functie is het tellen van hoofdletters vervangen door een lege string, wat resulteert in de tekst zonder de karakters “s” en “S”: “un i hining” .
=SUBSTITUTE("Sun i hining”;UPPER("S”);"")
De LEN-functie telt dan gewoon het aantal tekens van een gewijzigde tekst:
=LEN(SUBSTITUTE(SUBSTITUTE(B3;LOWER(B6);"");UPPER(B6);""))
=LEN("un i hining”)
In de laatste stap, geëvalueerd formule-resultaat, tekst zonder “s” en “S”, wordt afgetrokken door het totale aantal tekens in een gedefinieerde cel:
=LEN(B3)-LEN(SUBSTITUTE(SUBSTITUTE(B3;LOWER(B6);"");UPPER(B6);""))
=LEN("Sun is shining”) - LEN("un i hining”)
Specifiek teken in een bereik tellen
Wanneer we te maken hebben met celbereiken en arrays, is de SUMPRODUCT-functie nodig in combinatie met andere functies. Voor het tellen van de specifieke teken in een bereik, voor hoofdlettergevoelig tellen, wordt de combinatie van drie functies gebruikt: SOMPRODUCT, LEN en SUBSTITUTE. Voor hoofdletterongevoelige telling moeten aanvullende formules worden toegevoegd: UPPER / LOWER.
Eerst zullen we het tellen van het aantal keren dat teken ‘S’ voorkomt in een bepaald bereik (hoofdletterongevoelige versie) tellen. Een formule is bijna gelijk aan de formule die het tellen van tekens uitlegt. Het verschil komt van de LEN-functie genest in de SOMPRODUCT-functie en in het telgebied, in plaats van de cel is een gedefinieerd celbereik:
=SUMPRODUCT(LEN(B3:B6))-SUMPRODUCT(LEN(SUBSTITUTE(B3:B6;B9;"")))
Voor het tellen van hoofdletters en kleine letters in een bepaald bereik, kunnen we de gedetailleerd verklaarde functie gebruiken -ongevoelige karaktertelling in een specifieke cel in tutorial punt 3, met twee wijzigingen: de functie LEN nesten in de functie SUMPRODUCT en de cel vervangen door celbereik:
=SUMPRODUCT(LEN(B3:B6))-SUMPRODUCT(LEN(SUBSTITUTE(SUBSTITUTE(B3:B6;LOWER(B9);"");UPPER(B9);"")))
Onthoud dat wanneer u te maken heeft met celbereiken, u de SOMPRODUCT-functie moet gebruiken. Voor hoofdlettergevoelige tekens tellen in ra nge combinatie van functies is nodig: SUMPRODUCT, LEN en SUBSTITUTE, en voor niet hoofdlettergevoelig tellen: SUMPRODUCT, LEN, SUBSTITUTE en UPPER / LOWER functies.
Telspecifieke combinatie van tekens in een cel of bereik
Er is ook de mogelijkheid om een specifieke tekencombinatie in een gedefinieerde cel of bereik te tellen. In eerdere tutorial-punten hebben we het tellen van één teken in een cel of bereik behandeld met hoofdlettergevoelige / ongevoelige alternatieven. De formule voor het tellen van de combinatie van karakters is dezelfde, alleen moeten we deze delen door het aantal karakters in de karaktercombinatie.
Laten we eens kijken in het onderstaande voorbeeld voor het tellen van een specifieke combinatie van tekens in een cel (niet-hoofdlettergevoelige versie):
=(LEN(B3)-LEN(SUBSTITUTE(SUBSTITUTE(B3;LOWER(B6);"");UPPER(B6);"")))/LEN(B6)
Logica is hetzelfde als voor het tellen van een enkel teken, alleen moesten we de reguliere formule delen door het aantal specifieke tekens dat we tellen, eenvoudig met behulp van de formule: LEN (“in”). Zonder de formule te delen door LEN (“in”), zou het resultaat worden vermenigvuldigd met het aantal tekens in tekencombinaties (in ons voorbeeld met 2, aangezien “in” ”Heeft twee tekens)
Voor het tellen van een specifieke combinatie van tekens in een cel (hoofdlettergevoelige versie) ziet de formule er als volgt uit:
=(LEN(B3)-LEN(SUBSTITUTE(B3;B6;"")))/LEN(B6)
Het tellen van een combinatie van tekens in een bereik heeft dezelfde logica als het tellen van één teken, en voor het geval zal de gevoelige versieformule er als volgt uitzien:
=(SUMPRODUCT(LEN(B3:B6))-SUMPRODUCT(LEN(SUBSTITUTE(SUBSTITUTE(B3:B6;LOWER(B9);"");UPPER(B9);""))))/LEN(B9)
Reguliere formule uitgelegd in tu torials topic 4, is verdeeld met het aantal specifieke karakters dat we tellen, gebruikmakend van de functie LEN.
Als we de niet-hoofdlettergevoelige versie willen, dan ziet de syntaxis van de formule eruit als:
=(SUMPRODUCT(LEN(B3:B6))-SUMPRODUCT(LEN(SUBSTITUTE(B3:B6;B9;""))))/LEN(B9)
Heeft u nog steeds hulp nodig bij het formatteren van Excel of heeft u andere vragen over Excel? Maak hier contact met een live Excel-expert voor wat 1 op 1 hulp. Je eerste sessie is altijd gratis.